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.
| Author |
Topic |
|
chibione
Starting Member
6 Posts |
 Posted - 2008-07-23 : 13:55:46
|
| I am having a bit of trouble doing an INSERT-SELECT as a scheduled job.I'm selecting from an Oracle linked server table, and inserting into another, SQL Server linked server table.INSERT INTO [MSSQL_LINKED].[database].[dbo].[table1](col1, col2, col3, col4)(SELECT colA, colB, colC, colD FROM [ORA_LINKED]..[schema].[table2] )If I run this as a query in Management Studio, everything works nicely.But if I put this exact same query into a scheduled job as a T-SQL script, exactly like that... it won't go. The job history log shows a syntax error: Incorrect syntax near 'col4'.I managed to get around this by putting the statement inside a stored procedure, and calling it from the job as: exec myproc , and it works. But I am puzzled: why won't it work as a simple statement? Where is the syntax error?Hope you gurus can enlight me here.Regards! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-23 : 14:01:17
|
Or a problem with ownership.Under which context (user) does the job run?The query works with the user you use in SSMS.Alter the job and make the job run under same user account in you do in SSMS.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
chibione
Starting Member
6 Posts |
Posted - 2008-07-23 : 14:29:51
|
quote:
Originally posted by tkizer
If it works in a stored procedure and in SSMS, then I suspect the job issue is a bug. What does SELECT @@VERSION show?
The version is:Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-23 : 14:38:17
|
| If switching the job owner fixes the issue, then I still think it's a bug. You should not get a syntax error on that code regardless of who owns the job. chibione, not that I think this issue has been fixed in a later build, but it's always best to get to the latest build when working on issues like this. I'd suggest installing cumulative package of at least 7. I hear 9 is available and might be what I installed this morning (I'm off caffeine and my brain is mis-firing).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
chibione
Starting Member
6 Posts |
Posted - 2008-07-23 : 14:45:04
|
quote:
Originally posted by chibione
INSERT INTO [MSSQL_LINKED].[database].[dbo].[table1](col1, col2, col3, col4)(SELECT colA, colB, colC, colD FROM [ORA_LINKED]..[schema].[table2] )
I have tried variations of this as the script in the scheduled job:INSERT INTO [MSSQL_LINKED].[database].[dbo].[table1](col1, col2, col3, col4)(SELECT colA, colB, colC, colD FROM Localtable)INSERT INTO Localtable(col1, col2, col3, col4)(SELECT colA, colB, colC, colD FROM [ORA_LINKED]..[schema].[table2] )and they both work. The issue I see occurs only when I try to use both linked servers. Is this maybe a restriction ? |
|
|
|
chibione
Starting Member
6 Posts |
Posted - 2008-07-23 : 14:51:38
|
quote:
Originally posted by tkizer
If switching the job owner fixes the issue, then I still think it's a bug. You should not get a syntax error on that code regardless of who owns the job. chibione, not that I think this issue has been fixed in a later build, but it's always best to get to the latest build when working on issues like this. I'd suggest installing cumulative package of at least 7. I hear 9 is available and might be what I installed this morning (I'm off caffeine and my brain is mis-firing).
Thanks Peso and Tkizer. It doesn't seem to be a problem of job ownership, as I have a couple other similar jobs with the exact same privilege and ownership, although these access only one linked server at a time (INSERT INTO linked (SELECT FROM local) or INSERT INTO local (SELECT FROM linked)), not two as is what I'm trying to do now. It just strikes me as odd that the stored procedure approach does work, but the direct statement does not.For the time being this covers my purposes, but will talk to my admin to regarding the cumulative packages.Thank you guys again. |
|
|
|
chibione
Starting Member
6 Posts |
Posted - 2008-07-23 : 15:13:09
|
All right, just to let you know that it has worked.The problem ? One of the columns I'm inserting into is called Timestamp... Don't blame me, I didn't create that table In SSMS I am referring to it using double-quotes: "Timestamp", and it worked. I replaced them with brackets: [Timestamp]  . Seems like the SQL Server Agent is far more picky for syntax than SSMS.Anyways, thank you all for your help. Guess I should've posted this in the "New to SQL Server forum". |
|
|
|
|
|
|
|
|
INSERT-SELECT as job problem