I have a system where people can apply for registration and also a registration fee is involved. The screen where the application is captured has a couple of inserts into different tables. Each of these tables have triggers on them to transfer data to a linked server and they work fine. Today I just added another trigger on a different table but also part of this same save process. Now I am getting the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
The statement has been terminated.
/orisys.int.za/applicants/ApplicantInsert.asp, line 111
I do not know this error and think it might be time-out.
I am trying to use linked server objects inside a trigger and have some major problems.
Just to explain what i am trying to achieve:
My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.
To do that i created linked server object on the SQL 2000 side.
To test the linked server object i go:
Select * ServerB.Database.dbo.Table
This works perfectly and i get the results. I then test the same inside a stored procedure but i do some parameterised queries.
Select id from ServerB.Database.dbo.Table where id=@myId
This also works perfectly inside the stored procedure.
BUT NOW IT COMES THE FUN PART.
As soon as i place this inside the trigger it just doesnt work. My trigger has nothing else pretty much except for that. Here is a sample:
BEGIN SET NOCOUNT ON SET xact_abort ON
DECLARE @myValue nvarchar(50)
SET @myValue = '6357'
SELECT * from ServerB.Database.dbo.Table Where id = @myValue
END
It just gives me a timeout error. But what is even worse is that after this the whole database is crashed and i have to restart the database service to make it work.
I checked both servers and they have the service DTS for the distributed transaction on. No proxies, no firewals. Also i checked the servers configuration and they have RPC,RPC OUT and Data Access enabled.
I have tried everything over the last week and nothing has worked for me.
I've been working on this for a couple of days now, trying different scenarious.
Problem: I created a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to collect information and insert to a linked server server2.dbs2.owner.table2. When I run with the trigger active, SQL Analyzer just hangs in there "Executing Query Batch ..." indefinately. When I don't create the trigger, but run all parts manually from SQL Analyzer, it works fine.
What did I try: 1. create a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to insert into server1.dbs1.owner.table2. This worked fine. 2. create a stored proc to execute within the trigger on server1.dbs1.owner.table1 to insert to server2.dbs2.owner.table2. This just hangs in there "Executing Query Batch ..." indefinately.
What now? I have a suspicion that something is not working correctly with the triggers and the linked servers. Has anyone encountered a similar problem and what did you do to overcome this? I greatly appreciate all responses and suggestions. Thanks all.
I have two servers one on SQL Server 2000 one on SQL Server 7 I have setup the two servers so that they are linked and have added appropriate logins. How it works is a record is inserted into a database on SQL 2000 which has a trigger on it that send the record to a stored procedure on the SQL 7 server, from there this places the record into a table, which calls a trigger. Now this all works fine when I use the query analyser however when I don't use it, the record does not get inserted anywhere. Now I have stepped through it and it works up until the last trigger, if I remove that everything works fine. However the code in this trigger works fine, as when I use the quuery analyser everything works just as it should.
Does anyone have any suggestions as to how I can get this to work?
Can i write triggers beteen the linked servers.Here is the whole scenario. I have 3 servers. Server aaaa and server bbbb has replication in between them.Server aaaa is a publisher and server bbbb is the suscriber. I have another server cccc.So If i make any change on a table xxxx should effect the tablee xxxx on server aaaa and bbbb.So i am writing a trigger(for insert,update and delete on the table) xxxx on the server ccccc.So that trigger should take care of any DML(insert,update and delete) happend on table xxxx on server cccc and should effect on server aaaa and then the replication should take care of server bbbb.This want i am planning right now?Is it a good practice to implement in such a requirement? Please help me. Thanks.
I am currently trying to optimize an ETL process that pulls around 150 tables from a OLAP-esque system through a linked server. It's the AllScripts Touchworks EMR database, if that makes a difference. I am having a difficult time fitting an incremental load into a reasonable window of time. For instance, one table, which has around 40 columns, and a total of about 50 million rows, takes around an hour just to pull one day's worth of records when filtering on a single datetime field. The query is:
SELECT * FROM OPENQUERY([linkedserver], ' SELECT * FROM Works.dbo.Visit WHERE StartDttm BETWEEN ''2014-12-23'' AND ''2014-12-24'' ' )
Which, in the end, returns only about 30k records. However, in trying different approaches, I decided to test out bcp using the following code:
bcp Works.dbo.Visit out f:etlvisit.dat -n -S linkedserver.this extracted the entire table to flat file, but it only took about 15 minutes....add on another 3-4 to load that into the database on my end. Now, my ability to see what's happening on the remote server's end is very limited, but I know that the server has much higher specs than mine. They have 96GB memory vs. my 32GB, twice the cores I've got, etc. So how is this running faster by pulling the entire table over?
I figured that even in the worst case scenario of a full table scan, picking 30k rows out of the table that match a single criteria would certainly run faster than bringing the whole thing over. This isn't isolated to a single table, either. I finished running a test of the incremental extract/load vs. full extract/load via bcp (200GB of data). The incremental takes about 7 hours, bcp takes about 2 hours.
I have two SQL Server 2000 machines (server_A and server_B). I'veused sp_addlinkedserver to link them both, the link seems to behavefine. I can execute remote queries and do all types of neat thingsfrom one while logged onto the other.I'm working on a project to keep the data in the two systemssynchronized, so I'm using triggers on both sides to update eachother. For testing, I've created a simple, one-column table on bothservers, and also created a trigger on both tables. Consider thefollowing trigger code on server_A:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONINSERT INTO server_B.myDB.dbo.myTable SELECT * FROM insertedGOAnd also the following trigger code on server_B:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONINSERT INTO server_A.myDB.dbo.myTable SELECT * FROM insertedGOBefore you start screaming about the recursive relationship betweenthese triggers, I'm well aware of that issue, so I'm wrapping thetrigger logic with a login ID test. The servers are linked using aspecial login account, I'll call it 'trigger_bypass_login', so thetriggers look like this:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONIF SUSER_SNAME() <> 'trigger_bypass_login'INSERT INTO server_A.myDB.dbo.myTable SELECT * FROM insertedGOAlthough this logically works fine, there seems to be a compile issue,because I'm running into the error:The 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 inthe specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].What is strange is that I CONTINUE TO GET THE ERROR if I change thetrigger code to the following:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONIF 1=0INSERT INTO server_A.myDB.dbo.myTable SELECT * FROM insertedGOSo obviously, it has nothing to do with the actual inserting that theINSERT performs, but rather the fact that the trigger INSERTreferences the linked server/table.So, I moved the INSERT statement to a stored procedure, and it worksand I no longer get the error:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONIF SUSER_SNAME() <> 'trigger_bypass_login'EXEC myStoredProcedureGOIt works.. BUT, the stored procedure does not have access to the SQLServer 'inserted' trigger table. I've tried usingDECLARE CURSOR myCursor GLOBAL FOR SELECT * FROM insertedand then letting the stored procedure reference the cursor, but then Ihave to deal with the cursor data on a column-level basis, which isnot an option in this project because there are 100's of tables withmany columns, which might change over time.So it is of extreme importance that I use INSERT INTO ... SELECT tomove the row data in a generic fashion.I hope I have provided enough, yet not too much, information.I would really appreciate any suggestions anyone might have as to howI might handle this situation. Thanks.Hank
Error message: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA".
I uninstalled and reinstalled the AccessDatabaseEngine - no change.
The provider is displayed, so it's not that.
I verified temp and csv location folder permissions
I tried the -g512 startup option
The csv has a schema.ini as required
It was an 'in place' upgrade, so nothing else has change.
I have created a linked server that point to a firebird database, everything is fine, I can query the database with something like
Select * from MYLINKEDSERVER...MYTABLE
Now , can i use tablediff utility with this linked server. If yes, any example ..
I would say also that performance is not very important to me. this would be used at the end of the day to make some sort of replication by generating a sql script with datadiff, and run it on a local sql server database and a web based SQL database. The replication is not an option since i am using a shared web hosting and replication is disabled on it .
I have a SQL Server 2012 instance set up with a linked server to an Oracle Database. I need to build a stored procedure (single NVARCHAR(6) parameter in: @accountID) which executes a select statement against the Oracle DB.
Everything's working fine when testing with 'EXEC (''<oraclecodeblock>'') at LINKEDSERVERNAME' if I hard code the value of @accountID in <oraclecodeblock> but when I encapsulate it all within sp_executesql and attempt to parameterize @accountID it's throwing ORA-01722 'invalid number' errors on the Oracle DB. The parameterized value is NVARCHAR as are the account numbers.
Working: DECLARE @accountID VARCHAR(6); SET @accountID = '10842'; DECLARE @sql NVARCHAR(MAX); SET @sql = N'EXEC (''SELECT * from A.Accounts WHERE ID = ''''10842'''';'') AT LINKEDSERVER';
i didn't figure out how to add a firebird linked server via ODBC
my system :
win 7 pro 64 SQL server 2012 express 64 firebird 2.5.3 64 Official ODBC drivers 64
i have created the odbc source without problem, and checked that in visual studio and ssms import data wizard , both show me the data from firebird database but when i try to create a linked server , i have something like this message (the image is from the web)
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?
We have link-servers all over the place. The ideal naming convention should satisfy following:
- developers should not have to modify code when deploying between environments DEV->TEST->PROD. This rules out using actual server name as linked-name.
- the name should easily identify actual server without much mental translation. This rules out relevant but generic names like FAXSERVER or PAYSERVER
- also, if the name could state that it is a linked-server e.g starting with LINK_, it works when reading the code.
So, I was thinking the link-name should simply remove the environment :
e.g LINK_FAX_DB_V01
that way, on DEV box, the underlying sql data source could point to the dev server, while on test, it could point to test server etc. without having to change code, and also knowing which server it's pointing to by just adding a TST or PRD in front-of it.
I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
As a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't work that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
I'm having a few issues trying to get a linked server setup from a new SQL2012 installation to a Lotus Notes database. This linked server has previously been working fine on our old SQL2005 environment. All setup using the same details (ODBC driver name matches, account details etc). If we test the connection from the Lotus ODBC driver the test connection is successful, If I then create the Linked server to access this ODBC driver I receive an error :
Cannot initialize the data source object OLE DB Provider "MSDASQL" for linked server "LotusTest"
OLE DB provider "MSDASQL" for linked server "LotusTest" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetAttr failed" OLE DB provider "MSDASQL" for linked server "LotusTest" returned message "[Lotus][ODBC Lotus Notes]
Unable to find path to server. check your network connection is working.(Microsoft SQL Server, Error: 7303)We have just migrated to a SQL Server 2012 64 bit Enterprise from SQL Server 2005 32 bit Standard. We have a 64 bit Lotus client installed to provide the correct ODBC driver which has enabled the test connection direct from the ODBC driver to connect successfully. I'm just at a loss as to how to resolve this issue and get the linked server connecting.
I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.
Here is the code:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE ' + @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
The above Creates this when I expose it via a PRINT statement:
addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'
After I create the statement I use:
EXEC @AlterSQL
And this returns the following error:
Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244 Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...
select Portfolio from [TranscendDB].[dbo].[CMContactEvents] as CM inner join ( select N.SSN, A.ACCOUNTNUMBER from [DBServer1].[DB1].[dbo].[Account] AS A,
what is the best way to push records using linked server. below is my query on Source server
insert into LSDestserver.DB.dbo.tablename select * from #temp order by abc desc
I initially thought to pull records from Source server to LSDestserver but its a temp table. I don't know whether it is possible or not. Is there any other way we can achieve this in optimal way.
it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select * from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I have a HA Listener which is visible and can be connected to, it has a read only secondary on a different subnet so when connecting to it we use the applicationintent = readonly and multisubnetfailover = true.
Trying to connect it as a linked server is giving me problems. I tried putting the extra info into the provider string but keep getting the failure to initialise error. I am trying to link SQL2012 to a 2012 HA group but will also need to connect from a sql2008 server as well
