September 29, 2004 at 9:43 am
Howdy.
I have two SQL Servers, one running the latest version of SQL 2000 ("Archival") and one runing the latest version of SQL 2000 ("Production"). Archival and production are linked servers. On Archival we have a stored procedure that takes data from production, creates a database from a portion of it, copies data and defines stored procedures.
This stored procedure works fine with SQL 7.0 as the Archival box and SQL 2000 as the Production box, but fails when both boxes are SQL 2000.
The stored procedure that creates the databases and moves data uses dynamic SQL to do so because the database, table names, and stored procedure names change depending upon what month and subset of data are being moved. A snippet of the stored procedure follows:
EXEC('CREATE TABLE ' + @ServerDb + '.tblFileNumber (TitleworxFileNumber varchar (32) NOT NULL, RegionID varchar (12) NOT NULL, OfficeID varchar (12) NOT NULL)')
EXEC('ALTER TABLE ' + @ServerDb + '.tblFileNumber ADD CONSTRAINT [PK_tblFileNumber] PRIMARY KEY NONCLUSTERED (TitleworxFileNumber) WITH FILLFACTOR = 90')
EXEC('CREATE INDEX IX_tblFileNumber_OfficeID ON ' + @ServerDb + '.tblFileNumber (OfficeID) WITH FILLFACTOR = 90')
EXEC('CREATE INDEX IX_tblFileNumber_RegionID on ' + @ServerDB + '.tblFileNumber (RegionID) WITH FILLFACTOR = 90')
EXEC('INSERT ' + @ServerDb + '.tblFileNumber (TitleworxFileNumber, OfficeID, RegionID) SELECT TitleworxFileNumber, OfficeID, RegionID FROM UNICRON.WinTitleWorxSQL.dbo.tblFileNumber')
When I attempt to run this procedure I receive the error:
Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
I'm executing the query from Query Analyzer. I've issued SET ANSI_NULLS ON and SET ANSI_WARNINGS ON both inside of Query Analyzer prior to executing the stored procedure and prior to defining the stored procedure in question. I've attempted to set ANSI_NULLS ON and SET ANSI_WARNINGS on inside the stored procedure as well, but that hasn't helped.
I'm going to guess that dynamic SQL executes on a different connection than the calling stored procedure, and that my connection settings aren't propagating to the spawned connection. At that point I attempted to set defaults for the database and the server in the hope that the connection would use them but I was not successful.
What is the best way to set these settings for dynamic SQL execution inside a stored procedure?
Thanks in advance.
September 30, 2004 at 12:41 am
did you try setting these options within the dynamic SQL??...something like :
EXEC('SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT ' + @ServerDb + '.tblFileNumber (TitleworxFileNumber, OfficeID, RegionID) SELECT TitleworxFileNumber, OfficeID, RegionID FROM UNICRON.WinTitleWorxSQL.dbo.tblFileNumber')
September 30, 2004 at 10:51 am
I forgot to mention that does work. I was hoping there'd be an easier way around it, but at this point I believe it is my only option.
(There's over 500 lines of T-SQL in this stored procedure.)
Thanks for the reply!
September 30, 2004 at 2:54 pm
Hi,
Have had quite a bit of struggle with these settings myself. I thought I'd just mention a few things that you may or may not be aware of:
- Both ANSI-settings can be set at a server or database-level. This is of course not something you should do if you're not absolutely sure that it won't create any problems.
- When creating a procedure where you need to control the settings (ex. dynamic SQL), then ANSI_NULLS must be set outside the procedure, before the "CREATE". ANSI_WARNINGS, however, must be set inside the procedure-body.
- I'm using sp_execute most of the time for the dynamic sql, and that seems to inherit the settings of the surrounding procedure. Of course, this may be different for "EXECUTE", but that would seem a bit strange.
Good luck!
September 30, 2004 at 3:58 pm
In addition you must use the alter statement. You cannot just simply add the code into an existing SP. So everytime you need to update that SP you have to do it from query analyzer with the alter command.
Edward M. Sokolove
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply