March 12, 2006 at 10:17 am
Hi, Have no problem running the openquery code below. Declare @query varchar(8000) Declare @pID varchar(20) Declare @pLINE varchar(5) Declare @oDESIREDRCVDATE varchar(10) Declare @pSTATUSX varchar(1) Declare @pSTATUSC varchar(1) Set @pID='NYDELLTEST' Set @pLINE='1' Set @oDESIREDRCVDATE='2006-03-25 00:00:00.000' Set @pSTATUSX='X' Set @pSTATUSC='C' set @query = 'UPDATE OPENQUERY(VMFG, ''SELECT DESIRED_RECV_DATE from PURC_ORDER_LINE WHERE PURC_ORDER_ID=''''' + @pID + ''''' AND LINE_NO=''''' + @pLINE + ''''''')' set @query = @query + 'SET DESIRED_RECV_DATE = '''+@oDESIREDRCVDATE + '''' exec(@query) If however you need to include a second table for the update selection criteria (join on a field) what is the syntax or is this not possible ? Thnaks, Steve. |
March 13, 2006 at 11:27 am
I don't know your answer, but I experienced being in your shoes once before. I ended up speaking with the company which made the ODBC driver. In that case (it was connecting to a MySql database) I was told that that feature wasn't supported.
Good luck,
Paul
- Paul
http://paulpaivasql.blogspot.com/
March 13, 2006 at 12:01 pm
The "command" is sent directly to the ODBC source without parsing from sql server.
March 14, 2006 at 7:53 am
I would use a join probably.
... from PURC_ORDER_LINE POL Inner Join OtherTable OT ON OT.SomeField = POL.SomeField.
Then use the values from OT to populate the POL update.
March 14, 2006 at 7:56 am
Do you have the correct syntax ? Have not been able to get this to run without error.
March 14, 2006 at 8:06 am
Since you didn't post the syntax you used, how would we know!
Check out the join syntax with BOL. Typically, a simple join syntax is [Inner],[Outer Left],[Outer Right] Join ON FirstTable.fieldname = SecondTable.fieldname
ps. I added the alias' which would then need to be added to the rest of the statement or SQL will return Ambigious statements.
March 14, 2006 at 8:11 am
If you have not done an openquery update with multiple tables then you do not know what were talking about. This has nothing to do with a standard join, or update.
March 14, 2006 at 9:54 am
What ODBC driver is it? Oracle, Mysql ,...
March 14, 2006 at 7:23 pm
Yes, the syntax of the join inside the query string you execute via OpenSQL must be for the target DBMS - which I guess is not SQL Server... Some DBMSs are crippled and do not support some really obvious query syntax (eg MySQL - last time I worked with it anyway... Don't flame me! :blush
Is this second table in your local MSSQL server or on the target DBMS?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply