SP error with linked server - 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

General SQL Server Forums

New to SQL Server Administration

SP error with linked server

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-09-12 : 10:58:27

Hey guys,

I have an issue that is driving me insane!

A Vendor has given us a tool (SP) to output some data from a database on another server but whenever I execute the SP I get the following error message:

Msg 7405, Level 16, State 1, Line 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.

The SP creates a linked server which I think causes the issue. I have tried adding the two options as suggested by the error message when executing the SP but it doesn't help.

Has anyone else come across this?

Junior DBA learning the ropes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 11:54:22

did you try enabling it inside proc or outside?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-09-12 : 11:59:23

I've tried it in both. My SP uses an OLEDB connection which could be the issue. The SP is executed as the following:


DECLARE	@return_value int
EXEC	@return_value = [dbo].[storedprocedure]
		@SRCDBNAME = N'databasename',
		@PORTFOLIOLIST = N'ALL',
		@DestinationFolder = N'D:\Temp',
		@SourceDbConnection = N'Provider=SQLOLEDB;Data Source=server\instance;Initial Catalog=databasename;User Id=UserName;Password=*****'

SELECT	'Return Value' = @return_value

GO

Junior DBA learning the ropes

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-09-14 : 10:53:47

I think I may have fixed this.

In the end I had to go through the code supplied by the vendor and by chance noticed they had SET ANSI_WARNINGS OFF in one of the SPs.

Frustrating considering they denied it was anything to do with them and was instead a server problem on our end!

I now officially hate vendors!

Junior DBA learning the ropes

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 14:03:57

With ANSI WARNINGS = ON a SUM of a column that includes NULL values will generate a warning message. An Application may confuse this as an additional resultset which it was not expecting and Barf!

So it may be there with good reason ...

If you run the SProc manually, outside an application, you would no doubt just ignore the warning message you saw.

However, its a crap way to get Aggregate functions to NOT raise warning messages - its easy enough to say

SELECT SUM(MyColumn)
FROM MyTable
WHERE MyColumn IS NOT NULL

or some other variation-on-a-theme, so carry on hating vendors (unless you want to buy our stupendous eCommerce package of course !)

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-09-16 : 04:25:19

Thanks for the info Kristen.

I've let the Vendor know about the change I made so they were going to investigate. The output of the SP is a set of CSV files and isn't invoked from any application so I'm hoping it'll be ok.

Junior DBA learning the ropes

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-16 : 08:11:37

quote:
Originally posted by Kristen

With ANSI WARNINGS = ON a SUM of a column that includes NULL values will generate a warning message. An Application may confuse this as an additional resultset which it was not expecting and Barf!

So it may be there with good reason ...

If you run the SProc manually, outside an application, you would no doubt just ignore the warning message you saw.

However, its a crap way to get Aggregate functions to NOT raise warning messages - its easy enough to say

SELECT SUM(MyColumn)
FROM MyTable
WHERE MyColumn IS NOT NULL

or some other variation-on-a-theme, so carry on hating vendors (unless you want to buy our stupendous eCommerce package of course !)

This is why SET NOCOUNT ON should be used in Stored Procedures.

Kristen
Test

22859 Posts

Posted - 2011-09-18 : 03:21:54

... and Triggers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources