June 21, 2002 at 11:14 am
All,
I've set up a linked server so that I can use a stored proc to process some data from a remote SQL Server (both servers are SQL Servers: the linked server is SS2K and the source server is SS7.0). I've tested all the login mappings and everything works fine on that end. The proc that I've modified to process this data is erroring out on its creation with the following error:
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."
Below is the piece of code where its erroring out:
CREATE PROCEDURE Test_Proc AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
Declare@var1DateTime,
@var2VarChar(255)
CREATETABLE #TempLogHistory
(Col1 VARCHAR(8),
Col2 VARCHAR(8),
Col3 VARCHAR(8),
Col4 DATETIME,
Col5 VARCHAR(255))
CREATENONCLUSTERED INDEX [OI_#TempLogHistory_A]
ON[dbo].[#TempLogHistory]([Col1], [Col2])
CREATENONCLUSTERED INDEX [OI_#TempLogHistory_B]
ON[dbo].[#TempLogHistory]([Col3], [Col1], [Col4])
INSERTINTO #TempLogHistory
(Col1, Col2, Col3, Col4, Col5)
SELECT *
FROM OPENQUERY(RMTSVR, 'SELECT Col1, Col2, Col3, Col4, Col5
FROM rmtdb.dbo.rmttbl
WHERECol4 < @var1
ORDERBYCol4, Col1, Col2, Col3')
The script chokes on the line where I do the INSERT in to the temp table. Why isn't it recognizing that I've already set ANSI_NULLS and ANSI_WARNINGS to ON?
Thanks in advance,
Darias
June 24, 2002 at 1:10 am
Hi i think you don't need the 3 part syntacs in the insirt into. in stead of FROM rmtdb.dbo.rmttbl you could just use rmttbl. You already have a openquery so the 3 part is not needed.
Also i have discovered that with sending the openquery in a string with exec has better performance ( sql 7 to sybase that was)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply