Linked server ISQL issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Linked server ISQL issue

Author

Topic

nbkr3bi
Starting Member

15 Posts

Posted - 2007-07-18 : 10:59:36

We have a job that uses isql and executes a stored procedure. This SP contains a query in which there is a join between the table in one of the database on the current server and one table of a database on a linked server.
When the proc is executed in query analyser, there are no issues.
However, when it runs through the job, it gives the following error:

Msg 7405, Level 16, State 1, Server XXXXXXXXXXX, Procedure XXXXXXXXXXXXX, Line 202
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.

Please guide as to how to fix this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-18 : 11:08:52

I've never seen that error, but you could try using osql.exe insteal of isql.exe. osql.exe is for SQL Server 2000. isql.exe is provided for backward compatibility. If this is for SQL Server 2005, then sqlcmd.exe should be used.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

nbkr3bi
Starting Member

15 Posts

Posted - 2007-07-19 : 06:25:45

We are using Sql server 2000.
We can't use osql since it completely disrupts the formatting. Also it has a limit of 256 characters on single like.

The command i am using is :
isql -UUID -PPwd -dDB -SServer -Q "StoreProc" -o D:\output.txt -n -w8000

Please suggest a soultion in isql

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-19 : 11:03:03

Is osql newer than isql?

ag_sql
Starting Member

3 Posts

Posted - 2007-07-19 : 11:17:16

Inside your stored procedure, right underneath the procedure declarating statement try setting
ANSI_NULLS and ANSI_WARNINGS to ON. I had that error long time ago and I believe setting those options will fix the problem.
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
Hope this helps!!
Aruna

ag_sql
Starting Member

3 Posts

Posted - 2007-07-19 : 11:20:19

My mistake, you need to set these options before the create procedure statement
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE xxxxxx
AS

nbkr3bi
Starting Member

15 Posts

Posted - 2007-07-20 : 03:13:36

Tried both. Didnt work.
Any other ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources