Open Query and Max Date - 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 Programming

Open Query and Max Date

Author

Topic

vamsidhar_n
Starting Member

4 Posts

Posted - 2010-02-16 : 12:12:07

Hi I need to pull current data from a server to server using a linked server . The below query does not work b'coz of the max date

declare @dtmMaxDate as smalldatetime
select @dtmMaxDate=max(DateLoaded) from DestinationTable (nolock)
SELECT * from openquery(LinkedServer,'select * from DataBase.dbo.SourceTable
Where DateLoaded > @dtmMaxDate')

I know i can do like the below way but the source table is huge so i think this is not correct way are there any suggestions on this how can i do

declare @dtmMaxDate as smalldatetime
select @dtmMaxDate=max(DateLoaded) from DestinationTable (nolock)
SELECT * from openquery(LinkedServer,'select * from DataBase.dbo.SourceTable') where DateLoaded > @dtmMaxDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 12:38:57

then you need to use dynamic sql to get value of variable appended correctly to query inside openquery

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

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 13:00:31

[code]
declare @dtmMaxDate as smalldatetime
select @dtmMaxDate=max(DateLoaded) from DestinationTable (nolock)
DECLARE @strLocalSQL nvarchar(4000),
@strRemoteSQL nvarchar(4000)
SELECT @strRemoteSQL = 'select * from DataBase.dbo.SourceTable
Where DateLoaded > ''' + CONVERT(varchar(23), @dtmMaxDate, 126)+ ''''
SELECT @strLocalSQL = 'SELECT * from openquery(LinkedServer, ' + REPLACE(@strLocalSQL , '''', '''''') + ')'
SELECT [Remote SQL Syntax] = @strRemoteSQL
SELECT [Local SQL Syntax] = @strLocalSQL
-- EXEC (@strLocalSQL)
[/code]
Note: Don't use NOLOCK, you run the risk that you are using a MAX date that is not committed, and thus there will be "gaps" in the data you retrieve from the far end which, given that you are presumably relying on MAX(DateLoaded) being safely the highest value you have ever seen is a very high risk strategy.

vamsidhar_n
Starting Member

4 Posts

Posted - 2010-02-16 : 13:42:21

Hi Kristen thanks for your reply but the statment SELECT * from openquery(LinkedServer, @strSQL)
is not working throwing syntax error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:44:13

see
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

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

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 13:57:11

Sorry, forgot some quotes. I've changed it. Check the [SQL Syntax] output is OK before trying to use the OPENQUERY line

vamsidhar_n
Starting Member

4 Posts

Posted - 2010-02-16 : 16:26:45

Hi Kriten and visakh thank you for trying to help me out but Kriten there are no errors in the statement i have checked the out put but when i use it along with open query there it is erroring out.
Visakh your thing is almost working but in the example you are looking for N''''VINET'''''' it works but in my situation i am passing variable .when i do like this N''''02/11/2010'''''' it works but not working when i pass it as variable like N''''@Date'''''' I know it is for sure issue with quotes but not getting it right when i pass as variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 23:50:52

quote:
Originally posted by vamsidhar_n

Hi Kriten and visakh thank you for trying to help me out but Kriten there are no errors in the statement i have checked the out put but when i use it along with open query there it is erroring out.
Visakh your thing is almost working but in the example you are looking for N''''VINET'''''' it works but in my situation i am passing variable .when i do like this N''''02/11/2010'''''' it works but not working when i pass it as variable like N''''@Date'''''' I know it is for sure issue with quotes but not getting it right when i pass as variable.

for variable you dont have to pass like N'''@Variable''' just do like
second example

ie.


DECLARE @remotesql nvarchar(4000),
        @localsql  nvarchar(4000)

SELECT @remotesql = 'select * from DataBase.dbo.SourceTable
Where DateLoaded > ' +  dbo.quotestring(@dtmMaxDate)
SELECT @localsql  = 'SELECT * from openquery(LinkedServer, ' +
                     dbo.quotestring(@remotesql) + ')'

PRINT @localsql
EXEC (@localsql)

Please note that dbo.quotestring is a user defined function used and code is available in link posted in earlier article

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-17 : 01:38:37

Print the variable before executing it
Also single quotes play a major role in constructing the proper query
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 02:15:38

I have modified my example above to EXEC the OPENQUERY statement and take care of any embedded quotes in the Remote SQL

Local & Remote SQL is displayed - so you can try running it manually to test. If it works remove the Debug SELECT statements and activate the EXEC statement

Here's a link to the code:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139955#547885

vamsidhar_n
Starting Member

4 Posts

Posted - 2010-02-18 : 09:56:29

Hi Thanks guys got it working i have followed visakh's advice ,thanks every one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:57:34

Welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources