SqlException: Invalid column name 'id'

Hi,
It's weird, the column ID is the first column created, maybe something went wrong with the creation of the database. Can you try to create a new database and execute upgrade from the data source screen. After that, close the application and create a new session and see if the session is created correctly. If the session is created, try to import your previous sessions from access.

Hi David,
Thanks for your help.

I did many times what you said with no luck. Even on the server machine with "sa" account I get the same error.
It seems to me that the error states about an 'id' column in lowercase. I tried to rename all the uppercase ID column in the db with id (lowercase) and the error changes to:
---------------------------
---------------------------
System.Data.SqlClient.SqlException: Invalid column name 'ID'.
Invalid column name 'Data'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbTransaction dbTransaction, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.InsertConnection(Connection connection, IDbTransaction dbTransaction)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.SaveConnection(Connection connection)
---------------------------
OK
---------------------------

Here attached is the SQL script dumped from the regular database created by RDM. Maybe you can check it if contains errors.
I had the same problems with 5.8.0.0 beta.
Again thank you very much for support.

Eraldo

Hi,
Do you mean that if you connect with SQL Management Studio, and you try to run a query like "SELECT ID from Connections" you get the error ?

Hi again,
Can you try to use the blank database from this post ?




http://forum.devolutions.net/forum/messages.aspx?TopicID=247

No, I manually renamed the ID column in SQL Management Studio then tried to create a session in RDM. So I get the error in my second post.
Running the query you wrote works with the original db created by RDM.
It could be possible that RDM try to execute the query with the wrong case? I ask because of the error that states: Invalid column name 'id'.
edited by era on 5/21/2010

SQL Server is not case sensitive by default for the column name. Can you try to run both queries in the two different case ?

Hi David,
I'm sorry for late but I were at work, then I had to leave.. now I'm at home.

I tried your database and it worked.. There was only an error due to the ID column in the Connections table who was Nullable, so RDM couldn't run the upgrade.
So I made ID Not Null, did run the upgrade from inside RDM and all went ok: I've been able to add any session and I could import from my old Access db too.

Then I realized what was the problem after I found the difference between the 2 SQL database.
The first database (SQLdatasource) had Latin1_General_CS_AS collation while the new one (I downloaded from here) have Latin1_General_CI_AS collation.
I changed the SQLdatasource collation to Latin1_General_CI_AS and it magically worked too!
So, I think, what does fail is the initial db creation. RDM creates a db with Latin1_General_CS_AS and the upgrade tool don't ever change that, so I had the error on the (lowercase) id column.
Or maybe RDM doesn't specify the collation and my SQL Server 2005 set it default to CaseSensitive. And that's weird because I know my SQL server use Latin1_General_BIN by default and of course it's CaseSensitive too.

Does RDM specify the collation when it creates the database?

Eraldo

Hi,
No it does not specify the collation and I think you found the problem. I will fix it for the next release. Thank you very much for your help, this thread might help other people.

Hi David,
You're welcome, but really I have to thank you.
Good to know this can be fixed.