i want to run a transaction across mulitpule instances of sqlserver with out linked server.distributed trnasaction can do it with link server , can it do it with out linked server.
View 4 Repliesi want to run a transaction across mulitpule instences of sqlserver with out linked server.
distributed trnasaction can do it with link server , can it do it with out linked server.
We have an Enterprise setup with multiple domains.
We a Domain A with SQL server 2008R2 say SQL-A and Domain B with SQL server 2008R2 ( SQL-B). It will be like SQL-A will always access SQL-B. Reverse is not required.
In Domain A we have a .Net Application which accesses SQL-A to perform CRUD operations which ultimately come to SQL- B since all SQL tables exist in SQL- B only.
Need to know the right way to implement Linked Server from SQL-A to SQL-B, so that Domain issues as well as DML execution work smooth.
I need not worry about DDL in SQL-A And SQL-B as it will taken care of by network and H/W team. I just need to focus on as developer.
I have an SSIS job that is pumping to a SQL Server Destination, hundreds of gigabytes of raw text files. Today I received this strange error ? Also, how would I make the data tasks more stable and robust so that this doesn't cause package failure (retries, or something?)
[SQL Server Destination [4076]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I have a linked server setup in dev environment which is pointing to ORACLE server of a third-party. I m on  sql server 2008 r2 and  using BIDS 2008 for building an SSIS package which would pull data from oracle and do a look up in SQL server table to get a final  extract. Now the query which pulls data from a single table from linked server is like the one below
select * from [oracle linked server ]..dbo.tablename a
left join  mySQLSERVERtable b
on a.id=b.idÂ
Note:I can execute this query on SSMS as  the linked server is setup -[oracle linked server ] and I have sql  oledb connection manager for dev environment already configured in my ssis package .
My questions are do I need to create a SSIS CONNECTION manager specifically for linked server in my ssis package ?
Are there any best practices which we follow when using linked servers in this context ? I am thinking will the join suffer performance issue and what kind of exceptions I need to write when the linked server connection is broken.
I have multiple xml data file in a directory say C:XMLData abc1.xml, abc2.xml, abc3.xml etc.
Need to loop through each file in ssis with Foreach loop container, and get the file name say abc1, and load the data of abc1.xml to abc1 table in sql server DB.
Next iteration will pick up the abc2.xml and find the abc2 table in sql server DB then insert the data in abc2 table.
While each iteration, xml source should also point each xsd file correspondingly.
 Tables are already created in DB
I solved my problem up to getting the file name from ech iteration and assigned file name to variable, in oledb destination data access mode I select Table or view name variable, then corresponding table will get selected for data insertation.
Just wanted to know how can I read each xsd file for each xml data files while iteration.Â
I have one excel sheet contains 50 sub sheets with different names on it. Is it possible can i load all sheets into SQL using SSIS?
View 2 Replies View RelatedIn short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
When running a linked server to Oracle using the MSDAORA driver using in-process option checked, I'm getting the following error:
"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"
This then causes the instance to crash.
In the Event Log, it shows:
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
When using the out-of-process, it shows the column names in the query results window, but doesn't show any data then immediately in the message window it shows:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSDAORA" for linked server "smtest" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSDAORA" for linked server "smtest".
We're using:
SQL Server 2005 SP1 with hot fixes (9.0.2153) 32-bit on 64-bit o/s Windows 2003 R2 Standard x64 Edition Sevice Pack 1
We use lots of linked servers to Oracle on other servers, and can't find anything different on this one.
Appreciate any thoughts, insights!
thanks,
Steve
Hi, all
In domain A I have Server1(sql 7),
Server2(sql 2000).
In domain B I have Server3(sql 2000) with two instances(Red and Blue). Both instances' ports are not 1433.
My goal is adding Server3Blue as a linked server in both Server1 and Server2.
In Server2 I added Server3Blue as a linked server successfully by specified alias and port number.
But I failed in Server1.
I got 'unable to connect to specified server' error.
Any idea?
Thanks in advance,
I am trying to perform a distributed query however have a situation I haven't dealt with before the linked server I add to do the query is a named instance (DVD_NASDOMINO). How would I specify this in a query as in the FROM part in a sql statement. I tried the obvious DVD_NASDOMINO.qlsdat.dbo.stmenqry and DVD_NAS.DOMINO.qlsdat.dbo.stmenqry.
Both returned errors in the query.
Thanks for the help.
System: Win 2003, SQL Server 2005, Using an AD win account that is not a member of the Admin group on the server.
Error message from Management Studio query window:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "IBMDADB2" for linked server "Sname".
Event messages associated with this error:
App Event ID: 19036
The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.
Sys Event ID: 10016
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {2206CDB0-19C1-11D1-89E0-00C04FD7A829}
to the user domainuser SID (S-1-5-21-126051702-1034962659-2130403006-7826). This security permission can be modified using the Component Services administrative tool.
I€™m getting this error message when trying to run an openquery statement through a linked server to DB2. (SELECT * FROM OPENQUERY(Sname, 'SELECT * FROM tablename€™))
The linked server has a remote login and password that it uses to connect to DB2.
I found this from another post on how to fix this error:
Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security
Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account
Under Security - Access permissions: Allow System: Local Access and Remote Access.
After completing these steps I still get the error message but the events are no longer generated. I€™ve also tried different variations of these steps. The only way I can get this to work is to either make the AD user a member of the Admin group on the server or by putting in a user account that has admin rights to the server in the MSDAINITIALIZE properties €“ Identity Tab €“ Run this App as this User.
Can someone please tell me the steps that I€™m missing?
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
IDÂ key1Â key2Â key3Â key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.Â
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
Hi,
There was a distributed trasaction set up and functioning fine, But gave the following error recently.
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions
[OLE/DB provider returned message: Distributed transaction error]
Could anyone please help why is this error and how can be rectified.
Thanks in advance
John Jayaseelan
Hey all,
I've been experiencing an issue related to linked server. Here is the scene:
- Application A queries View V located in SQL Server S1
- View V accesses Table T located in SQL Server S2 using a linked server
The problem is:
- Application A start the transaction and it simply gets locked. It throws no errors nor exceptions.
Important:
- S1 and S2 are in different machines
- When S1 = S2, then the application successfully access View V.
Any suggestions?
I have a problem in running this query on linked servers, which has distributed partitioned view.
SQLTransaction transaction;... public void DeleteAllData() { try { connection.Open(); transaction = connection.BeginTransaction(); { Console.WriteLine("Deleting All Data..."); string cmdText = ""; cmd = new SqlCommand(cmdText, connection, transaction); cmd.CommandText = "delete from NEW_ORDER where NO_O_ID > 0"; cmd.ExecuteNonQuery(); cmd.CommandText = "delete from ORDER_LINE where OL_NUMBER > 0"; cmd.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Sucessful"); } } catch (Exception e) { Console.WriteLine("Failed in Method DeleteAllData"); throw e; } finally { connection.Close(); } }
The error returned was:
Failed in Method DeleteAllDataSQL Exception caught: Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SERVER_B". A nested transaction was required because the XACT_ABORT option was set to OFF.OLE DB provider "SQLNCLI" for linked server "SERVER_B" returned message "Cannotstart more transactions on this session.".
I realized that this problem may be eliminated if I'm using stored procedures on the database, and SET XACT_ABORT ON before any transaction.
Please could anyone help me.
Dear all,
We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:
1) Provider: Microsoft Jet 4.0 OLE DB Provider
2) Product Name: Access
3) Data source: X:XXXXX.mdb
4) Provider string: ;pwd=YYYYYY;
5) Collation Compatible: False
6) Data Access: True
7) Rpc: False
8) Rpc Out: False
9) Use Remote Collation: True
10) Collation Name:
11) Connection Timeout: 0
12) Query Timeout: 0
We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.
============
Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.===========
We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?
Thanks and regards,
William
I just installed a SQL Server 2005 Express SP2 instance on a server with an existing SQL Server 2000 SP3 installation. (I need SQL Server 2005's INSERT from an EXEC capability). It's working great now except for one thing: I can't link any other SQL servers! I've already successfully added and queried a linked Oracle server, but attempting to add a linked SQL server gives me the following error, no matter which SQL provider I try to use:
Code Snippet
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
Additional information:
--> An exception occured while executing a Transact-SQL statement or batch (Microsoft.SqlServer.Express.ConnectionInfo)
--> Cannot create an instance of OLE DB provider "SQLNCLI" for linked server "SERVERNAME". (Microsoft SQL Server, Error: 7302)
The technical details reveal the error source as "sp_testlinkedserver".
I've scoured the net and premier support for advice on this problem, but the little I found wasn't helpful. I've tried reinstalling the SQL Native Client, but it didn't help. I've tried uninstalling SQL Server 2005 Express completely (including management studio and native client), rebooting, and reinstalling everything, with no luck. The server (obviously) does not have a firewall enabled. I've tried stopping the SQL 2000 Server installed on the system, no help. If I create a test .UDL file on the system, pointing to any of the SQL servers I'm trying to link, clicking the "Test Connection" button returns successful. I'm also able to connect to and query the servers directly from the SQL 2005 Express Management Studio. I just can't add them as linked servers.
The server is running Windows 2003 SP1, SQL Server 2000 SP3, and SQL Server 2005 Express SP2 (the default "SQLExpress" named instance). I've tried setting up SQL 2005 Express to run under the network service account and under a domain account used by the other SQL 2000 servers.
From other SQL 2000 servers, I can connect and link to the SQL Server 2005 Express instance successfully. I can also successfully add linked SQL servers in SQL Server 2000 instance installed on the same server. Only adding linked SQL servers in 2005 Express seems to be broken.
Anyone have any other suggestions? I'm totally baffled. Thank you so much for any helpful advice.
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
Is there a way to read data from a linked server,within a transaction, without using DTC?The data on the linked server is static, thereforethere is no need for two-phase commit. There isno need for locking data on the linked server, becauseit is not being updated (either from the remote server,or from the local server).I don't want to run DTC because:1.) there have been security-related flaws with DTC inthe past2.) the application doesn't do distributed updates, andbecause the data on on the remote server is static,there is really no data integrity exposure withoutDTC.I cannot specify "WITH (NOLOCK)" on the select fromthe linked server:Server: Msg 7377, Level 16, State 1, Line 6Cannot specify an index or locking hint for a remote data source.I tried setting the isolation level:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDbut that seems to have no effect on the requirement to use DTC.I still get the message:MSDTC on server 'LOCALSERVER' is unavailable.Is there some other way around this? Is it possible to provide someconnection string parameter, in the linked server setup, that wouldspecify the "READ UNCOMMITTED" isolation level for the linked server,so that DTC wouldn't be necessary.(In other words, can I tell SQL Server, "trust me, this won't hurt"?)Environment: SQL Server 2000 sp4The SQL does something like:declare @x char(4), @k int, @rc1 int, @rc2 intset @k=123BEGIN TRANselect @x=xfrom remoteserver.remotedatabase.dbo.tablewhere k=@kupdate localdatabase.dbo.table1set x=@xwhere k=@kset @rc1=@@errorupdate localdatabase.dbo.table2set x=@xwhere k=@kset @rc2=@@errorif (@rc1 = 0 AND @rc2=0) COMMIT TRANelse ROLLBACK TRAN
View 2 Replies View RelatedI have a database containing my own tables and data and I wanted tobe able to query this against an accountancy program which has an ODBCdriver. This was never a problem with MS Access and Jet but I hit Jet'slimitations and have moved to SQL.Creating my own SQL database was no problem, but I was unsure of thebest way to be able to be able to have my SQL tables and my accountancysoftware tables appearing in the same Access front end.I created a linked server to the accountancy program. This wassuccessful in that I could see all the tables below the linked server inenterprise manager.My problem occurs when I try to bring the data from these tablesinto my SQL database.I create a new view in my database:-SELECT *FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')(My linked server is called 'SAGE_SERVER' and I am trying to retrieveall columns from the STOCK table.)I then try to save this view and get the following errors.ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operationcould not be performed because the OLE DB provider 'MSDASQL' was unableto begin a distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DBProvider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].Thanks in advance,Marcus Thornton.
View 1 Replies View RelatedHello, I've a problem with a software developed in C# with the framework 2.0. This is the error I receive : The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.". If I try directly to restart the process, it works fine. Is there someone who can help me ? This is the process 1. In C# --> Call of a Query : select from the linked server (db in sql 2005) and insert into a table SQL 2005 2. In the C# --> using (TransactionScope scope = new TransactionScope()) and insert in a table in SQL 2005 which is link server Thank in advance.
View 1 Replies View Related
Hi,
I have two servers S1,S2.
I am currently connect to S1 but I want to Query a table from S2 without creating a linked server.
Is it possible.Can someone please post some information on how this can be accomplished.
ETL, Lookup ,Stored procedure anything is ok. I am using SQL Server 2005.This is a very urgent requirement.
Please help!
Thanks,
I have created a SSIS Package which does the incremental update using CDC Controls.
The design is similar to any standard CDC incremental package.
It has a CDC Start which sets the Mark Processing Range, a data flow and a Mark Processed Range.
The issue that i'm facing is that the CDC Source control time-out but i can still see rows moving from CDC Source to Splitter and target table. After the rows are transferred, the Data Flow task Fails which leads to package failure.
This results in Mark Processed Range not being executed.
So my query is
1. Why is CDC Source being time-out?
2. What can i do so that all three i.e Mark Processing Range, data flow and Mark Processed Range execute successfully or nothing does.
I am trying to use sql transaction in foreach loop where I am passing 1 ID @ a time .I want to rollback only current ID.1st task in foreach is execute sql task with begin tran and next is data flow task with 2 source and destination for 2 different tables (if anyone fails then rollback from all tables). After data flow is another execute sql task with rollback tran on failure (logical condition on pipe is OR). The rollback works but only rollback 1 table although I want to rollback on current iteration ID of foreach from both tables.
View 3 Replies View RelatedI created an SSIS package for a client that does data importing. When I run the pacakge from Visual Studio there is an error window showing all the errors and warnings. A good example of an error is if the import file is in the wrong format.When there is a error or warning can I write the error log to a file OR notify someone of the errors so they can make corrections and rerun the package OR any ideas that the client can find out what went wrong and then make corrections accordingly? Thanks
View 1 Replies View RelatedI am currently having publisher(database A), subscriber (database B) and distributor on the same instance for a test environment that is using a uni directional transactional replication. Now I need to setup another unidirectional transactional replication in the same test instance but for a different database. Publisher database is D and subcsirber database is E.
View 4 Replies View RelatedI have a package running on Server A. I am copying data from Server B to server C. Does the data move from server B to server A for processing first and then from server AÂ to server C?
View 5 Replies View RelatedAs per this link [URL] we can execute packages only when we install Integration Services (DTEXEC works only after that).
As per this link [URL] we can execute packages from DB Server if integration services is not present on same machine and packages will execute from server from which they were started (i.e. DB Server via job).
My scenario, I have DB server installed on machine A and SSIS server on machine B. I am creating sql agent job on machine A and pointing to packages stored in file system on SSIS server B. My confusion is since we did not install integration services on machine A (DB Server), how packages execute from here? Will the packages run from server A but use DTEXEC of server B? If I am not wrong SQL Agent Job also uses DTEXEC to execute the package so how does it work without integration services installed?Â
I am using sql 2005 beta
View 23 Replies View RelatedI need to create a linked server that can access more than one database.
I assume, the only wat this can be done by creating two separate links from the local server using Microsoft OLE DB Provider for SQL server. But the
problem is the Server Name. I cannot use the same servername twice and
if I use a name that is not an exisiting SQL server name I get an error
that "server not found".
So, how do I addlink the same server a mulitple times to access different databases in the server?
Thanks.
Ranjit