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

Running Query For Linked Server

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

Running A Distributed Query Against A Loopback Linked Server In SQL Server 2005 Is Not Supported

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

Check If Linked Server Is Running

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

Trigger/Linked Server

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

Error Running Openquery(mdx) Through A Linked Server

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

Trigger On Update No Linked Server

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

Problem Using A Linked Server In An Insert Trigger

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

SQL 2012 :: Using Linked-server During (logon Trigger)

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

Linked Server Referred By Insert Trigger

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

SQL 2000 Linked Server Hangs Due To Trigger

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

Update Trigger On Linked Server View

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

Trigger Executing Linked Server Stored Procedure

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

Error :Execute Trigger From Remote Server To Another Server By Linked Server

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

Running MS Access Db As A Linked Server On SQL Server

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

Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2

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

How To Kill A Long Running Query Running On A Background Thread.

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

Running Trigger T-sql As The Sa

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

Linked Server Query

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

Linked Server Query

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

Help With Linked Server Query

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

Query Linked Server?

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

Query Running In SQL Server 2000

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

Linked Server - Dist. Query

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

Query A Linked Server With Parameters

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

Linked Server Query Never Ends

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

Linked Server And Query Performance

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

Can See But Cannot Query DB2 Data Via Linked Server

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

Linked Server To DB2 Query Hangs

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

Linked Server / Distributed Query

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

Query Data From Linked Server

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

Linked Server Query Error

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

Linked Server Query Problem

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







Copyrights 2005-15 www.BigResource.com, All rights reserved