xp_execresultset PROBLEM! Help!

• EvgDzh

  Grasshopper

  Points: 21

  More actions

  June 14, 2004 at 4:19 am

  #162762

  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!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 15, 2004 at 6:43 am

  #510446

  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'

• JN-161845

  SSCrazy

  Points: 2052

  More actions

  October 6, 2005 at 11:43 am

  #595586

  Read following KB on Microsoft for possible resolution:

  http://support.microsoft.com/kb/816780