PROBLEM WITH Heterogeneous queries

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

Transact-SQL (2000)

PROBLEM WITH Heterogeneous queries

Author

Topic

msbolton
Starting Member

12 Posts

Posted - 2003-10-14 : 05:40:21

I am having a real problem using openquery on a linked server. I have used QA to get around the ANSI_NULLS, ANSI_WARNINGS problems when I initially set the procedure up.

Whay I know need to do is dynamicaaly generate a stored procedured on a daily basis that I can alter the date parameters being passed to the openquery.

I have tried to set ANSI_NULLS and ANSI_WARNINGS ON in a number of different ways but to no avail.

Here an example of what I am trying to achieve:

Create procedure sp_a_procedure
as
declare @sqlstring as varchar(4000)

@sqlstring = 'insert into atable (field1, field2) from openquery(linked_server, ''select field3, field4 from linked_table where date1 between '''''+fnc_mondaydate(current_timestamp)+''''' and '''''+fnc_fridaydate(current_time)+''''')'

exec(@sqlstring)
go

The functions change the dates to the Monday and Friday dates respectivly.

The above example is one that I have tried to just generate a sql statement that I can execute.

I have tried including the SET ANSI_NULLS ON, etc statement in the stored procedure but to no avail. I keep getting ERROR 7405 saying that I need to set the nulls and warnings on first.

If anyone has any ideas on how to achieve this or even an alternative way of doing what I need to I appreciate any help you can offer.

Thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-14 : 08:13:03

Can you take the functions out of the equation?....ie...resolve/determine the values PRIOR to inclusion in the OPENQUERY statement.

msbolton
Starting Member

12 Posts

Posted - 2003-10-14 : 08:24:10

Andrew,

Even if the @sqlstring contained static dates you still get the same error message. I need the functions to allow the dates to be generated dynamically.

At the moment I have a version of this query that runs with static dates. This was created by using QA to create the procedure. I believe this works because the ANSI_NULL value is set to NO as default. I want to get away from having to edit this query manually so that it will extract the data for the current period.

mr_mist
Grunnio

1870 Posts

Posted - 2003-10-15 : 03:05:42

Why do you need to use dynamic sql for this?

-------
Moo. :)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-17 : 07:43:46

I think you miss my point....you don't have to have the functions passed into the openquery
my point was that you can/should do

set @sql2 = "select field3, field4 from linked_table where date1 between "'" +fnc_mondaydate(current_timestamp)+ "'" and "'" +fnc_fridaydate(current_time)+ "'"

and then pass @sql2 into the openquery....you'll still get the same result...dynamic sql.
i was trying to point out that maybe the functions were causing a problem...ie are the functions only installed on the local server....yet you are (may be) asking the remote server to execute them....again...remove as many variables from the problem and hopefully the solution becomes apparent.

Anyway....why don't you set ANSI_NULLs off...since you say that a QA generated version works...seems to have ANSI_NULL = NO

Is it possible that there's a difference between the 2 servers in terms of it's "system defaults (nulls,warnings,etc)" and the difference is the root of the problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources