-
Tried put following in beginning of your script?
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
-
I'll explain you the problem.
I have a 3rd party application runing against a SQL Server 2005 database.
I have to move the database to another server, but the application must not notice the server translation, so I moved the database to the second server and in the original one linked the other server and created synonyms to all the moved objects.
The problem is because the application explicity sets ANSI_WARNINGS OFF and I cannot get the source code or ask the vendor to change the code.
So I cannot SET ANSI_WARNINGS ON
Thanks, Ariel
-
Only app vendor can help you, but you said vendor doesn't exist anymore. Whatelse we can do? Only thing I can think of is renaming your server.
-
In your connection string, change "AnsiNPW=NO" to "AnsiNPW=Yes"
-
Solution for me
The solution was:
CREATE THE PROCEDURE LIKE THIS...
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE dbo.MyProcWhatever AS BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
...
COOL..
NOW CHECK IF THE CREATION WENT OK WITH THIS
SELECT OBJECTPROPERTY(OBJECT_ID('MyProcWhatever '), 'ExecIsAnsiNullsOn')
SELECT OBJECTPROPERTY(OBJECT_ID('MyProcWhatever'), 'IsAnsiNullsOn')
TO BE OK BOTH MUST RETURN 1!
COOL..
NOW EXECUTE THE PROCEDURE LIKE THIS...
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
EXEC dbo.MyProcWhatever
THAT SOLVED THE PROBLEM FOR ME..
THE MAIN THING WAS BEFORE CALLING THE PROCEDURE SETTING THE NULLS AND WARNINGS ON AGAIN.
-
The reason for turning the warnings off immediately is because the stored proc. is executed in VB6 to populate a record set object. If after the creation of sproc, NSI_WARNINGS, NOCOUNT, ANSI_NULLS are on, then the recordset object crashes. The article you sent me, talks about how to fix a problem creating a stored sproc. After the stored proc is created, the warnings, nocount and ANSI_NULLS can be turned off. I have other sprcos that work this way with different linked servers. For some reason this one does not.
