7405 Error

• mdevore

  Ten Centuries

  Points: 1293

  More actions

  July 29, 2009 at 1:29 pm

  #224054

  I'm trying to set up a procedure to run at server startup. It access another db on another instance. I can create the proc, execute it, and set it to autorun. When I restart the server, the proc is started, but I get this error:

  Error: 7405, Severity: 16, State: 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.

  It works fine as a stored proc when executed normally. I have set ANSI_NULLS and ANSI_WARNINGS on before creating the proc, in the middle of the proc, and several other things, yet always get this error only on startup. The interesting thing is that it will work on another server just fine at startup. They're at the same patch levels and I've verified all the settings that I can find match. Any suggestions?

  SQL2005 9.0.4226 Enterprise Edition

• Kaneabell

  Valued Member

  Points: 67

  More actions

  September 22, 2011 at 3:31 am

  #1385005

  Hi Guy,

  Add following command in your store procedure

  set ANSI_NULLS on

  set ANSI_WARNINGS on

  It should be OK.

  Good luck.

  Regards,

  Kaneabell