November 30, 2001 at 6:49 am
I recently upgraded my application (ASP w/COM backend) to SQL 2000 (SP1 installed) from SQL 7. The data access layer is ADO calling stored procedures. The problem I am seeing now is string parameters are not being trimmed any more when updating, the line below shows the code:
.Parameters.Append .CreateParameter("ModelNumber", adWChar, adParamInput, 50, m_varModelNumber)
this adds the parameter reserving 50 spaces for it. I pass in something like "MODEL1234" but when it is saved to the database it saves "MODEL1234" + 41 spaces,
keeping the 50 characters. This did not occur in SQL 7. I thought this was an issue with the ANSI_PADDING property but I have tried turning it on and off with no results.
This problem is causing major problems in my application, string comparisons are almost impossible now without manually trim all the values (urrrggggg!)
Any help or comments would be appreciated.
Thanks.
November 30, 2001 at 8:21 am
Not surre but I thought I read something in books online that if you originally create the table with ansi_padding on, it will not change unless you drop and recreate the table with ansi_padding off.
Let us know if it works.
Tom Goltl
November 30, 2001 at 8:33 am
The online books say that you can turn it off using the ALTER DATABASE command
ALTER DATABASE "Northwind"
SET ANSI_PADDING OFF
GO
This does change the property value but the behavior does not change!
November 30, 2001 at 9:45 am
December 4, 2001 at 2:14 pm
This a big problem for me. After working on an application for about a year, it is suddenly crashing all around me due to this issue. MS offers no solution other than manually recreating the entire database and then importing the data into each table. This is unacceptable to me. If anyone has any other suggestions they would be greatly appreaciated. Below is a link to this admited problem from MS:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q296559
December 4, 2001 at 2:24 pm
I don't see any way around either recreating or altering the application.
Steve Jones
December 4, 2001 at 4:45 pm
At least they are consistent - the AnsiPaddingStatus property of the DMO column object is read only.
One workaround would be to use views in place of the tables, trim all the columns you're having problems with. Not super cool, but doable.
Option two is to get good at reloading the data quickly. Not fun, but solves the problem. At least now you now the expense of making changes, can plan for in the future.
UDF's might provide an alternative to views. Same work or more I expect.
See if we can figure out where to patch the mdf at the byte level to make the change, see what happens. Basically create an db with only one table that is good, make a copy of it, then alter a column to see what changes. Binary compare might show what bit - lot of work and probably some risk.
Andy
December 5, 2001 at 8:05 am
I'm out on a limb here, but try setting the database compatibility level back to the 7.0 version. This might do something.
Tom
December 5, 2001 at 12:04 pm
Thanks for the suggestions. I have considered them all but still have not found a reliable solution yet. Still working on it though. I have tried changign the data types (nvarChar = varChar) (varChar = nVarChar) but this does not effect the "TrimTrailingBlanks" property which is the real trouble maker. I will not be much longer before I bite the bullet and manually recreate the whole database, not to mention going through each table a trimming any rows which were affected. If anyone has a script that creates a database based on another's structure, without using the ALTER statement, basically that reads then creates, not copy or alter one db from another that would be helpful.
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply