November 10, 2006 at 6:40 pm
We recently converted our Access 2000 database to SQL Server 2005 Express.
We are running into issues with the way blank strings are handled.
In Access, our tables stripped all trailing blanks and did not allow single spaces as entries (converts them automatically to empty string "").
In SQL Server our tables have ANSI Padding Off and do allow single spaces as entries (allows both "" and " ").
Is there an easy way to set the tables so they convert a single space to the empty string ("" like Access)?
Additionally, we use ODBC prepared statements with bound string parameters to update our tables.
If we value the bound variable with an empty string, ODBC appears to be filling in the string with trailing spaces, and SQL Server stores the values as a single space. The latter is expected behavior.
Whereas if we use regular statements (non prepared) a "" is treated correctly as an empty string.
Because of this, all of our code that looks for blank entries "" now fails since in SQL Server blank entries can be "" or " ".
Is there any way to specify that the prepared statements with bound string parameters should not fill in trailing spaces?
Thanks for your help.
November 13, 2006 at 8:00 am
This was removed by the editor as SPAM
November 14, 2006 at 12:39 pm
Ok, we solved this one. Turns out, the SQL datatype of the parameter needs to be set to SQL_VARCHAR, rather than SQL_CHAR.
Didn't matter in Access, matters a LOT in SQL Server.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply