Linked server SQL select into

8500 pts.
Tags:
SQL Server
SQL Server Agent Jobs
I'm trying to create a stored procedure that will trigger every 15 minutes via a server agent job. I need to pull data back from a linked server to our SQL server. The tables have the same columns/fields just different names. I know the linked server logic works because I get the data if I run this code. [code]select * FROM OPENQUERY(MyLinkServer, ' select WCHTIK,WCHSRC,WCHIDTE,WCHITIME from extranet.WCTLH')[/code] If I run this code below I get this error. iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid. Valid tokens: , FROM INTO [code] Declare @TimeDifference as INT SET @TimeDifference = -15 DECLARE @NewDateTime Datetime set @NewDateTime = DATEADD(mi,@TimeDifference, getdate()) DECLARE @CompDate varchar(10) set @CompDate = convert(varchar(10), @NewDateTime, 20) select @CompDate DECLARE @CompTime varchar(8) set @CompTime = CONVERT(VARCHAR(8), @NewDateTime, 108) select @CompTime declare @strSQL as NVARCHAR(4000) set @strSQL = '' set @strSQL = @strSQL + 'insert into WebHeaderControl ' set @strSQL = @strSQL + '(WebHeaderTicketNumber,WebHeaderSource,' set @strSQL = @strSQL + 'WebHeaderInDate,WebHeaderinTime)' set @strSQL = @strSQL + 'select * FROM OPENQUERY(MyLinkServer, ' set @strSQL = @strSQL + '''select WCHTIK,WCHSRC,' set @strSQL = @strSQL + 'WCHIDTE,WCHITIME ' set @strSQL = @strSQL + 'from extranet.WCTLH ' set @strSQL = @strSQL + 'WHERE WCHIDTE = ' + @CompDate + ' and WCHITIME >= ' + @CompTime + ''')' exec(@strSQL)[/code] I'm not an SQL expert and this is my first attempt at using a linked server.. Any ideas? Thanks in advance Sorry for the code being messed up. Did not see the CODE button in IE9

Software/Hardware used:
SQL Server 2000

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    If you copy all of your [ set @strSQL = ] lines into Notepad, and start deleting all characters except the actual INSERT statement values, you end up with a series of text file lines something like this:
    'insert into WebHeaderControl '
    '(WebHeaderTicketNumber,WebHeaderSource,'
    'WebHeaderInDate,WebHeaderinTime)'
    'select * FROM OPENQUERY(MyLinkServer, '
    '''select WCHTIK,WCHSRC,'
    'WCHIDTE,WCHITIME '
    'from extranet.WCTLH '
    'WHERE WCHIDTE = ' + @CompDate + ' and WCHITIME >= ' + @CompTime + ''')'
    By jumping to the end of each line and deleting end-of-line, the lines can be concatenated into a single string. And when the resulting doubled apostrophes from the end and beginning of each line are removed, you build a copy of what gets built into @strSQL. One thing that lets you see is that there is no white-space between the third and fourth lines. I'd expect some kind of syntax error to result, but that might not be the cause.   Once you have the full string and have substituted values for your variables, Can you run the resulting INSERT statement?   Tom  
    125,585 pointsBadges:
    report
  • TomLiotta
    I added a comment, but it's not showing up for some unknown reason. I guess we'll wait until morning to see if it gets released. -- Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Second try to put the comment in still gives nothing. Sorry; not much I can do. -- Tom
    125,585 pointsBadges:
    report
  • ToddN2000
    Thanks for the tip Tom, that fixed it... Ran into a related issue but found a work around for it.
    8,500 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following