June 14, 2004 at 4:19 am
hi all,
i really have a problem here and will appreciate any kind of help!
SQL Server 2k + SP 3a is running on my machine.
i am trying to create a connection and "select" query using xp_execresultset and OPENROWSET: here is my code:
--------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE my_sp_exec
AS
declare @query nvarchar(4000)
select @query = N'select * from OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER=127.0.0.1;UID=my_uname;PWD=my_pass'',''SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname'')'
exec xp_execresultset @query, N'pubs'
-------------------------------------------------
when i execute the SP i recieve the following error No: 7405
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.
--------------------------------------------------
as you can see i activate the ANSI_NULLS and ANSI_WARNINGS at the begining of the SP. They are also activated in the connection options of the Query Analyzer. When i write the following queries, the result is always =1, which means they are switched on!
SELECT objectproperty(object_id('my_sp_exec'), 'IsAnsiNullsOn')
SELECT DATABASEPROPERTYEX ('master', 'IsAnsiWarningsEnabled')
SELECT DATABASEPROPERTYEX ('master', 'IsAnsiNullsEnabled')
But nevertheless when I run the SP alwas got the same error.
Please, save me from that madness!
10x in advance!
June 15, 2004 at 6:43 am
This is just a hunche but have you tried :
select @query = N'SET ANSI_NULLS ON; GO; SET ANSI_WARNINGS ON; GO; select * from OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER=127.0.0.1;UID=my_uname;PWD=my_pass'',''SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname'')'
exec xp_execresultset @query, N'pubs'
October 6, 2005 at 11:43 am
Read following KB on Microsoft for possible resolution:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply