Rowset and remote server

• Tschernoster

  SSC Rookie

  Points: 32

  More actions

  March 26, 2004 at 7:16 am

  #107958

  We have two servers GS1 and LPT. LPT server is not linked on GS1.

  The following script works fine in Query Analyser on GS1

  SELECT * FROM OPENROWSET('SQLOLEDB','LPT';'globsys';

                'globsys','SELECT * FROM posta.dbo.sestavy')

  The same script in the stored procedure

  CREATE PROCEDURE dbo.zkouska AS

  SET ANSI_NULLS ON

  SET ANSI_WARNINGS ON

  SELECT * FROM OPENROWSET('SQLOLEDB','LPT';'globsys';

          'globsys','SELECT * FROM posta.dbo.sestavy')

   

  RETURN

  get this error message

  ERROR 7405: Heterogenous queries require the ANSI_NULLS

  and ANSI_WARNINGS options to be set for the connection.

  This ensures consistent query semantics.

  Can anyone help me

  Thanks

  Mirek

• Mithrandir

  SSCrazy

  Points: 2628

  More actions

  March 26, 2004 at 7:48 am

  #500639

  Use:

  SET ANSI_WARNINGS ON

  SET ANSI_NULLS ON

  GO

  CREATE PROCEDURE....

  ....

  To turn on These options before creating your SP