Linked server SQL select into

Register

Forgot Password

Site FAQ

Home > IT Answers > SQL Server > Linked server SQL select into

ToddN2000

3,910 pts.

Linked server SQL select into

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

ASKED: March 22, 2013 3:57 PM

UPDATED: March 22, 2013 4:32 PM

RELATED QUESTIONS

Help

Approved Answer - Chosen by ToddN2000 (Question Asker)

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

ANSWERED: Mar 23, 2013 0:08 AM (GMT) by ToddN2000