Variable on openquery - Insert statment

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

Variable on openquery - Insert statment

Author

Topic

Jimbojames30
Starting Member

8 Posts

Posted - 2014-11-06 : 18:35:39

Hi everyone

please can someone help me out, i have a linked server that i use a variable to get the last invoice date and only pull in the latest records that are after the last invoice date but i dont know how to insert that into a table?

DECLARE @TSQL varchar(MAX), @LastDate As Datetime, @LastRunDate As Varchar(6)
SET  @LastDate  = (SELECT MAX(OrderDate) FROM [SYNC_REPORT].[DPY_SyncReport_Prod].[Stage].[Sales])
Set @LastRunDate  =    ( select  right(convert(char(4), year(@LastDate)), 2) + right('000' + convert(varchar(3), datediff(dd, convert(datetime, '01/01/' + convert(char(4), year(@LastDate))), @LastDate+1)), 3) +100000 )


SET @TSQL = 'SELECT  * FROM OPENQUERY
(JDE812, 
''SELECT       
                  ''''250'''' As System,
                  sdmcu As Plant,
                  sdsrp2 As Franchise,
FROM         proddta.Mytable 
WHERE       sdkcoo = ''''10656'''' 
AND           sddcto IN (''''SO'''',''''C1'''',''''CF'''',''''S3'''',''''C3'''',''''CA'''') 
AND           sdmcu = ''''      UKD001'''' 
AND           sdivd > '''''+@LastRunDate+'''''
AND           sdsrp2 = ''''567'''' 
AND           sdnxtr <> 999 '')  '

I tried
Inert Into MyTable (a,b,c,d etc)
EXEC (@TSQL)

but i get msdtc on server xxxxx is unavaiable?

anyone help me out on how to get my linked server query into my table

many thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-11-06 : 23:27:58

Sample code

DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000), @var nvarchar(100) = 'Ram'
SET @LinkedServer = 'LinkedServer'
SET @OPENQUERY = 'INSERT INTO TestEMP SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT * FROM sample.dbo.EMP WHERE ENAME = ''''' + @Var + ''''''')'
EXEc(@OPENQUERY+@TSQL)