by Rob » Wed, 03 Mar 2004 06:18:01
Curious to know if it's possible to create a join between
the inserted table in a trigger and a table in a linked
server database? I haven't been able to get this to work.
IE.
IF Exists (SELECT distinct B.CustNumb
FROM LinkedServerB.Database.dbo.Customers B
inner join inserted ON B.custid = inserted.custid)
Thanks in advance,
R
by Wayne Snyde » Wed, 03 Mar 2004 06:40:50
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
Quote:> Hello All,
> Curious to know if it's possible to create a join between
> the inserted table in a trigger and a table in a linked
> server database? I haven't been able to get this to work.
> IE.
> IF Exists (SELECT distinct B.CustNumb
> FROM LinkedServerB.Database.dbo.Customers B
> inner join inserted ON B.custid = inserted.custid)
> Thanks in advance,
> R
1. Problem inserting into DB2 from SQL Server via Linked Server
Here goes: (good luck understanding all this)
I have data in SQL Server that needs to be inserted into DB2. I have
installed the IBM DB2 Client Configuration Assistant on the SQL Server and
created a DSN. I can use the IBM Command Center to execute commands (both
reads and writes) successfully. I then created a Linked Server on SQL
Server that uses the Microsoft OLE DB provider for ODBC Connections in order
to connect to DB2. Using this method, I can run SELECT statements
successfully. When I try to do any writes (a DELETE for example), I get
this error message:
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I verified that the user on DB2 has appropriate permissions and have on the
Security tab of the linked server properties page specified that user with
its password. Personally, I think that the whole SQL Server-->OLE
DB-->ODBC-->DB2 route is kinda obtuse. I would love to get rid of ODBC
alltogether. Unfortunately, the Microsoft OLE DB Provider for DB2 only
comes with their Host Integration Server product (which we don't have and
wouldn't be able to install on the SQL Server anyway) and I don't have the
equivalent IBM OLE DB driver...
Any recommendations or suggestions on how I can get the writes working?
Thanks..
Peace,
Gary Hampson
SQL Server - Siebel DBA
Application Development Group - IS
Horizon Blue Cross Blue Shield of New Jersey
2. Data transfer from DB2 to SQL Server
3. JOIN queries across linked servers
4. Searching Text Documents in SQL
5. inner join in a linked server error
7. Linked Server giving Transaction Context error on join query
8. Joins to external dbs through linked servers
9. Update Linked server table with inner join
10. Only one join allowed using a Progress Linked Server
11. Insert trigger to linked SQL server fails
12. insert data into linked server