SQL Server 2005 issue

I am using jchem3.1.7 and MS SQL Server 2005 with the MS jdbc driver 1.0.809.102.





I can connect to the db OK but when I try to create a new table using default parameters (ie only a new table name entered), I get the following error:





SQL warning


SQLState: S0001


Message: sp_cursoropen/sp_cursorprepare: The statement can only be a batch or a stored procedure with a single select, without FOR BROWSE, COMPUTE BY, or variable assignments.


Vendor: 16938








When I look at the database using the MS SQL Server Management Studio, the table has not been created. If I simply remove "SelectMethod=cursor" from the jdbc connection URL, the table creation works fine - although with direct rather than cursor based selection.





Any ideas what I'm doing wrong?

I fixed my problem using the jTDS1.2 jdbc driver instead of the MS jdbc driver - cursormode selection works fine with this driver.





One side effect, when creating a new table using jTDS driver with jcman, the fieldtype for cd_structure is set to null rather than image in the CREATE TABLE statement. This throws an error about the null fieldtype.


ie

Code:

cd_structure null NOT NULL,

instead of

Code:

cd_structure image NOT NULL,

Simply changing the fieldtype back to image before creating the table seems to work.

Dear Support,





I've got the same problem, ie.





"java.sql.SQLException: The definition for column 'cd_structure' must include a data type."





However I can't use a graphical interface, so, is there any other way to set the column type at table generation?





Thanx!

Hi,





Could you tell us your JChem, SQL Server and JDBC driver version ?





The latest 1.1 and 1.2 drivers from Microsoft should work fine with the latest JChem version:





http://msdn2.microsoft.com/en-us/data/aa937724.aspx





Please note that we do not officially support third party drivers.





Best regards,





Szilard

Hi,





JChem: 3.2.4


SQL: MSSQL 2000 SP4 (FreeBSD)


JDBC: jtds-1.2





>The latest 1.1 and 1.2 drivers from Microsoft should work fine with


> the latest JChem version:


I've tried also the Microsoft one, but got:





--


Cannot connect:


java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.


--





I thought that the problem is the mode of the authentication:





http://sqlserver2000.databases.aspfaq.com/why-do-i-get-login-failed-for-user.html


http://mssqlonline.blogspot.com/2007/04/fix-error-18452-login-failed-for-user.html





However, the admin of the server told me that the server already in mixed mode..


----





So, I would like to go back to the jTDS driver:





http://jtds.sourceforge.net/





Using this without any table:





jcman t --driver net.sourceforge.jtds.jdbc.Driver --dburl jdbc:jtds:sqlserver://<ip>:1433/kemoinfo --proptable JChemProperties -l <user> -p <passwd> > log





I've got:





--


Error executing statement:


CREATE TABLE JChemProperties(


prop_name varchar(200) NOT NULL PRIMARY KEY,


prop_value varchar(200), prop_value_ext null)


Cannot connect:


java.sql.SQLException: The definition for column 'prop_value_ext' must include a data type.


at chemaxon.jchem.db.DatabaseProperties.createPropertyTable(DatabaseProperties.java:536)


at chemaxon.jchem.Command.main(Command.java:485)


--





jTDS is an open source driver, so it would be really a good thing to be able to use JChem with it.. Any ideas?








Thx a lot!

Hi,





We have managed to reproduce and fix the problem.





The next minor (update) release is in a late testing phase, but we will try to include the fix.


I will let you know about this.


In the worst case we issue bugfix releases about every month.





Thank you for the bug report.





Best regards,





Szilard

Good news: the fix will be included in the next minor release (JChem 3.2.6), which is expected to come out in a few days.





Szilard