SQL Server Trigger - MQ SQL Linked Server Table

SQL Server Trigger - MQ SQL Linked Server Table

Post by Rock » Sat, 02 Aug 2003 13:31:13



SQL Server 7
MQ SQL 3.2

Hi,

I am trying to update a table on a MQ SQL lined server, the trouble is the
MQ SQL driver does not seem to support the owner part, any suggestions? Or a
link to some examples would be good.

Thanks for any help in advance

 
 
 

SQL Server Trigger - MQ SQL Linked Server Table

Post by Prasad Koukuntl » Sat, 02 Aug 2003 16:12:02


Please provide your code. Usually, you need to use the

four part (linked server name.database_name.owner_name.table_name)
notation when dealing with tables on a linked server.

The following is from BOL,

Identifying a Data Source Using a Linked Server Name
After a linked server is defined, a four-part name in the form
linked_server_name.catalog.schema.object_name can be used in Transact-SQL
statements to reference data objects in that linked server. The table
describes the parts of a four-part name.

      Part name Description
      linked_server_name Linked server referencing the OLE DB data source
      catalog Catalog in the OLE DB data source that contains the object
      schema Schema in the catalog that contains the object
      object_name Data object in the schema

Microsoft? SQL ServerT uses the linked server name to identify the OLE DB
provider and the data source. The catalog, schema, and object_name
parameters are passed to the OLE DB provider to identify a specific data
object. When the linked server refers to an instance of SQL Server, catalog
refers to a database and schema refers to an owner ID.

This illustration shows how a four-part SQL Server name resolves to an
object in the OLE DB provider.

HTH

Prasad


Quote:> SQL Server 7
> MQ SQL 3.2

> Hi,

> I am trying to update a table on a MQ SQL lined server, the trouble is the
> MQ SQL driver does not seem to support the owner part, any suggestions? Or
a
> link to some examples would be good.

> Thanks for any help in advance


 
 
 

1. update trigger on linked server (sql server 7.0)

i tried this (the error follows) :

please help. thanks.

----------------------------------------------------------------------------
--------

CREATE TRIGGER tr_UpdateLinked ON [tblInvVendors]

FOR UPDATE

AS

update lk

set lk.vendorname = inserted.vendorname,
lk.addr1 = inserted.addr1,
lk.addr2 = inserted.addr2,
lk.city = inserted.city,
lk.state = inserted.state,
lk.zip = inserted.zip,
lk.telno = inserted.telno,
lk.faxno = inserted.faxno,
lk.contactname = inserted.contactname,
lk.active = inserted.active

from BRSQL.GYMCDS.DBO.tblInvVendors lk,  inserted

where lk.vendorid = inserted.vendorid

return

-----------------------------------------
here is the error:

ole/db provider returned message : only 1 transaction can be active on this
session.
could not start a transaction for oledb provider sqloledb

--------------------------------------------

an insert statement from isql works fine.

2. MSG 2503, Level 16, State 1

3. link server from Oracle to SQL server occurs blocking on SQL server

4. Error 5173

5. SQL server select from Oracle linked server dual table

6. No zero before the point in formula fields ?

7. LINKED SERVERS : How to link MySQL server on SQL Sever 2000

8. Paradox Printing over Novell

9. Linking SQL Server version 7.0 to SQL Server 2000

10. LINKING ACCESS TO SQL SERVER DATABASE WITHOUT SQL SERVER INSTALLED (2)

11. LINKING ACCESS TO SQL SERVER DATABASE WITHOUT SQL SERVER INSTALLED

12. link SQL SERVER 7.0 and SQL SERVER 2000?

13. SQL 6.5 Remote Server to SQL 7 Linked Server