Running Query In Linked Server Through Trigger
Aug 2, 2006
i have set up
a linked server. i can query the linked server in query analyzer and
also do update/delete. but when i try to run the same query for linked
server through insert trigger, i get following error:
[OLE/DB provider returned message. [Microsoft][ODBC Sql Server Driver]Distributed transaction error].
btw, i am using Sql server 2000, SP4. main server is windows 2003 server and linked server is windows xp pro.
any suggestions will be appreciated.
View 1 Replies
ADVERTISEMENT
May 23, 2001
I have created a linked server using an ODBC connection to an Access database. The command I used for this was:
EXEC sp_addlinkedserver
@server = 'Testaccess',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'test'
GO
where 'Testaccess' is the name of the linked server and 'test' is the name of the ODBC connection. [The ODBC connection stores the name of the Access datbase.]
I can run sp_tables_ex and view the table_schem, table_cat etc etc
BUT when I try to run a query on the linked server, I get the following message:
"7312 - Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema."
I am attempting/using the following command specifying the linked server name.database name.owner.table name in the FROM clause:
select a.*
from testaccess.testdb.dbo.temptable a
Any ideas what I am doing wrong??
thanks
Jan
View 1 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Dec 30, 2002
I have a stored procedure that references another stored procedure on a linked server. I would like to check if the linked server is running prior to accessing the stored procedure. Everything I have tried returns either message 11 or 17 and terminates the stored procedure. I would like to do some cleanup before the termination. Is there any way to check this?
ben
View 2 Replies
View Related
Oct 29, 1999
CREATE TRIGGERReportRequestINSERT_trg
ONReportRequest_tbl
FOR INSERTAS
Can an insert trigger like this successfully insert into a linked server's table?
Replication is NOT an option. And I only need to do it in very limited circumstances anyway.
INSERT INTO
[172.22.202.180].MultiVendor.dbo.ReportRequest_tbl
(
ControlCustomerNumber,
UserID,
ReportName,
RequestedDate
)
SELECT
ins.ControlCustomerNumber,
ins.UserID,
ins.ReportName,
ins.RequestedDate
FROM
Inserted ins
View 1 Replies
View Related
Feb 26, 2007
I'm trying to create linked server to access DMX functions from SQL Server as per:
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
I create the link this query
EXEC master.dbo.sp_addlinkedserver
@server = N'KLSSQL01AnalysisServerLink',
@srvproduct=N'Analysis Services 2005',
@provider=N'MSOLAP',
@datasrc=N'kls-sql01',
@catalog=N'AnalysisServicesPredictorPrototype'
GO
SELECT * FROM OPENQUERY(KLSSQL01AnalysisServerLink, 'select node_caption, node_type from [Misuse Abuse Profile].content')
where [Misue Abuse Profile] is the Mining model
Provider options: Allow in process
I receive the follwing error:
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink".
I found this post but there was no resolution.
run openquery(mdx) through a linked server
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924869&SiteID=1
View 2 Replies
View Related
Sep 13, 2007
I have 2 database servers ( 2 hardware-servers : A and B ) and I ve written a trigger for update in server A to execute insert statement in server B through linked servers, is there other way to achieve this without linked servers?? All using T-SQL.
Best Regards
Joseph
View 4 Replies
View Related
Aug 24, 2004
I'm writing an insert trigger in one SQL Server database that is supposed to insert another record into a linked SQL Server database. I have the linked server set up and have been using it for a few weeks in queries and stored procedure with no problem. Now that I'm trying to use it within a trigger and it just bombs.
I'm getting the following message in one of my logs and I don't know what it means... "Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE". I've googled around, but can't really find anything. Any help would be appreciated.
Thanks,
Bryan
View 1 Replies
View Related
Oct 8, 2015
Used linked-server during logon trigger? is it possible? i know we can access tables, SPs, etc with in that server, if i want to check something in other server during logon trigger, can i do it?
View 0 Replies
View Related
Jul 23, 2005
Hei,We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separatedmachines).I am triing to connect them så that when one row is added to the table inthe database in main server - then the same row is added to the same tablein the second server database.I made the insert trigger on the table in the first server ( the secondserver is added as a linked server):-----------------------------------------------------------------------------------------create trigger ti_myTabe1 on myTable1 for insert asbegindeclare ........BEGINinsert into server2.myDatabase2.owner.myTable2(column1, column2, column3)SELECT column1, column2, column3FROM inserted insEND......end-----------------------------------------------------------------------------------------When I run the statement in "SQL Query Analyzer"on the first server:insert into Table1 values(va1,val2,val3)then error is coming:Server: Msg 7391, Level 16, State 1, Procedure ti_myTabe1 , Line 19The operation could not be performed because the OLE DB provider 'SQLOLEDB'was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].The straing thing is: if I run the statement in "SQL Query Analyzer"on thefirst server:insert into server2.myDatabase2.owner.myTable2 values(va1,val2,val3)then it works!But not inside the trigger!!! - What I am doing wrong ?Any idea is greatly appeciated.
View 2 Replies
View Related
Jul 23, 2005
Hi -We have two SQL 2000 Servers. We have the linked server setup and wecan perform updates and inserts between the databases. But when we adda trigger and insert something into a table, the database hangs. Thereare NO processes blocking or being block in either database. This ONLYoccurs when we have one OS as Windows 2000 Server and the other OS asWindows 2003 Server. This problem does not occur when both servers areWindows 2000. Has anyone ran into anything similar to this???*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com
View 1 Replies
View Related
Jul 3, 2006
What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.
Thank you
View 1 Replies
View Related
Jul 22, 2007
i did "Linked server" between To Servers , and it's Working.
---------------------------
For Example :
Server 1 =S1.
Server = S2.
i create table in S1 : name = TblS1
i create same table in S2 : name TblS2
and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2
/****************** trigger Code ***************
CREATE TRIGGER dbo.tr_cpD
ON dbo.TblS1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND
**************************************************
result is :
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
how i can execute this trigger
View 5 Replies
View Related
Sep 7, 2006
Hi,Looking for a quick and dirty on running an Access database as a linkedserver in SQL Server. Basically, the majority of my stuff is in SQLServer, but there is one lingering nightmare-of-an-Access-database noone wants to touch.I just want to create a linked server to use the Access db.So far I have not found a way to connect (under Security -LinkServers in SQL Server), though I tried all kinds of drivers, connectionstrings, etc. What do I put for Product Name, Data Source, ProviderString, (Location, Catalog)?Thanks a bunch.
View 1 Replies
View Related
Oct 26, 2006
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
View 1 Replies
View Related
Sep 1, 2006
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
View 1 Replies
View Related
Sep 23, 2005
How does one run the following command in the context of the sa account while logged in as themselves (not sa). The "SQL Authentication" user does not have access to master.
EXECUTE master.dbo.xp_sendmail @recipients = 'hi@hotmail.ca', @subject = 'Script fired!', @message = 'Hi'
View 1 Replies
View Related
May 8, 2001
Greetings all,
I have successfully been able to use a SELECT query to see tables on a remote (Linked) server. Now, I want to be able to join those records with a WHERE clause to the same tables I have on my local database. The idea here is to be able to see current data on my local database(which is not current data), which resides in the remote database(which is current data). Here is what I have so far:
SELECT top 10 * from LinkServer.MC_Card.webuser.POS_TX
But, I'm not sure how to SELECT from two tables. Would I do a SELECT from the local database WHERE all records = records on the remote database?
Not sure how to do this. Is this what would be considered a Distributed Query? And how would I make this work with joins like the existing joins I have to the tables in my local database?
This is new territory for me. Sorry if this is such a newbie question.
Thanks,
Bruce
View 1 Replies
View Related
Nov 7, 2006
I'm using MS SQL Server Express and I've added a linked server. (I created the linked server by right mouse clicking on Server Objects, selecting SQL and entering the SQL2 as the name.) Now I'm trying to query a linked table. The following query works.
SELECT * FROM SQL2.PA.dbo.Counties
This one doesn't:
Select * from OPENQUERY(SQL2,"SELECT * FROM SQL2.PA.dbo.Counties")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT * FROM SQL2.PA.dbo.Counties'.
Can you tell me why?
View 2 Replies
View Related
Apr 19, 2004
I have two tables in a linked server that I am trying to accomplish the following:
Server is called Medic, schema is informix, catalog is v001
table names are findet and findhdr
1) join indices (patkey and invno)
2) distinct count (finhdr.patid)
3) sum currency(findet.amount)
4) within a date range(findet.ar_date)
5) having specific criteria (findet.proccode = "99201 thru 99205")
Any help is greatly appreciated!
JT
View 2 Replies
View Related
Jan 25, 2008
Hi guys I need some help connecting to a LINKED SERVER. I have setup the LINKED SERVER via Enterprise Manager. This is what I did. I know this is a difficult thing to explain so I will try my best and attempt to give you a good understanding of the scenario.
1. I have three servers named DEVUK, DEVUS and DEVASIA.
2. DEVUK I can connect to via my computer and contains a databases called GOD.
3. I went into DEVASIA server and went to Administrative Tools > New ODBC Connection and called it DEVASIAUK.
4. Setup everything in there and tested the connection which completed successfully.
5. I am now on my computer where I have connected to DEVASIA and gone into Server Objects > Linked Servers.
6. I created a new LINKED SERVER and under GENERAL TAB have stated DEVASIAUK and Provider as SQL Server.
7. Security I have used NT_AUTHORITYand option BE MADE USING THE LOGINS CURRENT SECURITY CONTEXT.
I have the SQL Command: SELECT * FROM DEVASIAUK ... tblLocalTable
Am I doing this right? Or am I missing something here? I am new to Linked Servers but it shows it is executing a query but then it says Login Failed.
Appreciate any help, thanks, Onam
View 2 Replies
View Related
Sep 12, 2006
When you are inserting/altering a table and you expect values to be added, should you see the number of rows affected at the bottom of the query screen as the time goes by or not?
thx,
Kat
View 1 Replies
View Related
Mar 29, 2001
Is there a way to set four part reference used in distributed query to a short
name in local server, so for each query you do not have to type the LONG four
part ref. repeatedly to linked server objects ?
Thanks a lot.
-Steve
View 1 Replies
View Related
Apr 20, 2001
I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.
For example, the following statement works just fine:
SELECT *
FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")
But If I try to use:
DECLARE @DynamicSQL VARCHAR(1000),
@StartDate VARCHAR(10),
@EndDate VARCHAR(10)
SET @StartDate = '2000.01.01'
SET @EndDate = '2000.01.31'
SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'"
--SELECT @DynamicSQL
SELECT *
FROM OPENQUERY(OracleLinked, @DynamicSQL)
it does not work.
Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)
Then is there any way I can accomplish what I want to on the Remote server?
Thanks in advance for your help.
View 1 Replies
View Related
Apr 25, 2001
I have a delete query on a linked server that never ends. I can do a select where ID = x and it returns lickity split but when I do a delete where id = x it never comes back? Any Clues???
View 1 Replies
View Related
May 14, 2001
Hi,
I am now facing a problem related to the linked server. I created the connection between server A and B as linked servers. When I execute the following SQL statement on server A,
select * from B.database1.dbo.tableA where id ='12345'
I can get the results within couple of seconds. But the similar query would take several minutes if I switch the server name in the query from B to A and
run it on server B! The tables on server A and B actually have the same sizes and the same indexes.
Do you have any clues and suggestions on this issue?
Thanks in advance.
Keith
View 1 Replies
View Related
Jun 13, 2001
I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password
I can then see the tables in EM, but when I try to query them in QA I get this
Select * from db2db...customer
Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'
I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server
My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data
any ideas ? I think I need to enter something in the catalog box, but what ?
thanks in advance.
View 1 Replies
View Related
Jul 26, 2001
We want to read data from a DB2 view so we set up a linked server in SQL7 (ODBC via Neon's Shadow Direct) but when we issue the following type of query from Query analyzer:
select * from openrowset(my_db2link,'select * from test.myview where mycol = ''value''')
it just hangs, and we can't kill the process (the Enterprise manager 'KIll Process' button has no effect!)
- the only way we can get rid of these queries seems to be to stop/restart SQL Server
When we look at current activity in Enterprise manager the process seems to be waiting for a resource (either MISCELLANEOUS or PAGEIOLATCH)
The query works OK if I run it on the server using Shadow direct, so the error must be between SQL Server and Shadow direct
Has anyone seen this error before?
Thanks in advance, John
View 1 Replies
View Related
Oct 18, 2000
Three weeks ago we began a project that involved importing data from an AIX DB2 6 environment via a linked server configuration. Following the data import a second query was executed against the db2 environment using data that resides in the new table within SQL 7 in the join statement (a very basic example is provided below) This was all accomplished in sequence via a package.
SELECT F_NAME, L_NAME, PASSWD
FROM SQL7.LOCALSRV.dbo.NEWUSERTBL as new, DB2.SYSIBM.MASTERTBL.OLDUESRTBL as old
WHERE new.USER_ID = old.USER_ID
Originally we had no problems and while the execute time was not exactly speedy it was tolerable as we would revisit optimization after we established if what we were trying to accomplish was feasible. At the outset the first data import to build the local table was immeadeate and then the distributed query to retirieve more info to build another local table against the DB2 server took aproxiamtly 1 minute per user row returned. Currently we are looking at still having an immeadeate data import (a matter of seconds to build the first table)but now we are looking at more than 1 hour returned for 1 correesponding row of data off of DB2. We are utilizing the the IBM DB2 ODBC DRIVER.
Any input or suggestions as to what could be causing this or perhaps a more efficeint way to code the statement would be much appreciated. Thanks in advance.
Adrian
View 1 Replies
View Related
Oct 12, 1999
I am trying to run a select statements against linked server.
I have NT server running SQL7 Enterprice and Rumba2000. I used Rumba driver to configure a DSN (ODBC based) to a AS400/DB2. Then I created a linked server. Good news are : I can see a list of tables.
Bad news : when I am trying to run a select statement either through stored procedures or directly in SQL analyzer I am receiving error message Object 'linkedservername.databasename.tablename' doesn't exist.
What am I missing here?
View 2 Replies
View Related
Feb 10, 2003
SQL 2000
I have a process that calls several stored procs which access a database on a linked server.
code that fails:
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPO bp ON
em.er_bpoid = bp.bpo_id
Error message:
Server: Msg 913, Level 16, State 8, Line 1 Could not find database ID 6. Database may not be activated yet or may be in transition.
The database is accessible from query analyzer with a simple select from the linked server. Also if I change any letter in the ues.dbo.Employer em or ues.dbo.BPO bp part to a different case it works fine.
For example: -changed the BPO to BPo- this works!
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPo bp ON
em.er_bpoid = bp.bpo_id
Please help I can't figure this one out.
Thanks.
View 1 Replies
View Related
Jan 14, 2004
We are using linked servers, additionally, some of our tables are being merge replicated with identity ranges.
When I run a SELECT statement on a non-merged table looking for a constant value, I get the proper return.
When I run a SELECT statement on a merge-repl. table looking for a constant value like "5", it returns 0 records, execution plan says it's using a "Constant Internal table" - however, when I run the same select statement and change the where clause to ">4" it returns the row with "5" and whatever other rows match the criterion. What in the world is going on?
Thanks for your help.
View 5 Replies
View Related
