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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy