DTS Connection - ANSI_NULLS setting

Question

Post reply

DTS Connection - ANSI_NULLS setting

mila

SSC Enthusiast

Points: 175
More actions
November 18, 2003 at 4:47 am

#358684

I have following problem with DTS package:
I was created Stored Procedure DestViewCreator which create remote view to the remote table on local server. Arguments are @serverName and @dbName where table is stored.
CREATE PROCEDURE dbo.DestViewCreator @serverName varchar(255), @dbName varchar(255)
AS
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
execute ('
CREATE VIEW dbo.v_BranchOffice
AS
SELECT * FROM ' + @serverName + '.' + @dbName + '.[dbo].[BranchOffice]
')
GO
When I run this procedure thru QA procedure runs perfect, but from DTS Package I've got following message:
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.
In QA I can set ANSI_Nulls in Tools/Options/Connection Properties, but in DTS I haven't this property on Connection object. If @serverName is local SQL server name I haven't problem, DTS Step run on expected way.
Marke

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Just put the set statements before your procedure call,

eg:

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

EXEC dbo.DestViewCreator ...

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

mila SSC Enthusiast Points: 175 More actions · Answer 2

quote:
Just put the set statements before your procedure call,
eg:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
EXEC dbo.DestViewCreator ...
Hope this helps
Phill Carter
--------------------

We tried that but it didn't help. The same error remains.

jordanac Hall of Fame Points: 3653 More actions · Answer 3

Hi,

I have had this problem before and the solution was to create the procedure from QA not EM.

Do you have to create the proc from DTS? Could you not call the procedure from DTS?

If it aint broke don't fix it!

Andy.

mila SSC Enthusiast Points: 175 More actions · Answer 4

quote:
I have had this problem before and the solution was to create the procedure from QA not EM.
Do you have to create the proc from DTS? Could you not call the procedure from DTS?

The point is to create the view dynamically and this is only one step in a very complex DTS package. So, it has to be from DTS.

cmore SSCrazy Points: 2452 More actions · Answer 5

Does putting a GO before the execute statement work? Wondering if the connection settings are being ignored for the execute.

A year or two ago I had this exact same error, from a different client than DTS. Because they also did not have the settign in their connections manager, I had to set it programmatically. There was some wierd little trick to it. I can't remember for sure, but I think it essentially was setting the stuff via SQL statements, but I had to do it before anything else, and in it's own batch. Once I ran it, until I lost connection I was ok. Trying to run it in a batch with other stuff didn't work the same.

cmore SSCrazy Points: 2452 More actions · Answer 6

Still curious if seperating the settings statements in a seperate batch but on the same connection works. You could also try setting the default connection properties on the SQL server, but that will affect all connections that don't explicitly set those values.