Strange SQL Server 2005 Problem
Kwitko
Sheriff of Banning (Retired)By the thing near the stuff Icrontian
I wrote a trigger that calls a stored procedure. The trigger and SP work fine in SQL Server 2000. When I run it 2005, I get a warning about ANSI NULLs, but the procedure runs through. The strange thing is that ANSI_WARNINGS is set to off, so I shouldn't be receiving any warnings.
I tried to set ANSI_WARNINGS OFF in the proc, but the warning still shows up. Normally a warning wouldn't be a problem, but it's affecting the operation of our ERP system and we need this trigger to fire.
I did a few tests. In the Query Analyzer I ran the update part of the procedure by typing
I've discovered some strange things. If I don't use variables in the procedure (which defeats the whole purpose) the procedure works fine. The moment I have variables for the update statement it gives a warning. Really odd. What's even more odd is that the procedure worked in an old test database. We copied it over to the live database and now it fails everywhere. I feel like I'm missing a setting somewhere but I can't figure it out!
Every search on Google has led me down a dead end. Help me Obi-Wan Kenobi, you're my only hope.
I tried to set ANSI_WARNINGS OFF in the proc, but the warning still shows up. Normally a warning wouldn't be a problem, but it's affecting the operation of our ERP system and we need this trigger to fire.
I did a few tests. In the Query Analyzer I ran the update part of the procedure by typing
SET ANSI_WARNINGS OFF UPDATE CUST_ORDER_LINE SET UNIT_PRICE = 150 WHERE CUST_ORDER_ID = 'TEST' AND LINE_NO = 2No warning. If I remove the ANSI_WARNINGS, I get the warning, but the line still updates. So I tried to add the line into my procedure. Of course it doesn't work.
I've discovered some strange things. If I don't use variables in the procedure (which defeats the whole purpose) the procedure works fine. The moment I have variables for the update statement it gives a warning. Really odd. What's even more odd is that the procedure worked in an old test database. We copied it over to the live database and now it fails everywhere. I feel like I'm missing a setting somewhere but I can't figure it out!
Every search on Google has led me down a dead end. Help me Obi-Wan Kenobi, you're my only hope.
0
Comments
We ran into an issue switching from SQL2000 to SQL2005 that had to do with importing records. The import is a flat file with a lot of spaces at the end of each record and in SQL2000 worked fine, but the same files failed on SQL2005. I only mention this because you state an error with NULLS. We had to reformat our import files for 2005 to not end in spaces. Probably not related at all to your problem, but it appears SQL2005 doesn't like NULLS in some instances.
Good luck!