May 18, 2015 at 10:21 am
hi,
i 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.
and please tel me some other ways also.
yours sincerley
May 18, 2015 at 10:24 am
rajemessage 14195 (5/18/2015)
hi,i 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.
and please tel me some other ways also.
yours sincerley
You can't have a transaction across multiple servers when the servers can't talk to each other. You could maybe make this work by having transactions on each database and controlling them from a third process but that sounds like a recipe for disaster to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 18, 2015 at 11:10 am
You can do it with a powershell script. Are you not wanting to link the servers for a specific reason? Like security vulnerabilities?
May 19, 2015 at 5:08 am
try SQL server's feature --- Central management . you can register all the server and perform transaction without logging to that server
basic condition is that your SQL server needed to talk with each other ( in same domain - or accessible from the server on which you are creating the central management server group.
other option is
make batch file
txt file with all server name
>sql file with sql script you need to perform
and output file to store the output
and write the script to excute the script to run against all the server in TXT file
WINNERS NEVER QUIT AND QUITTERS NEVER WIN
sqlserversdba.wordpress.com
May 19, 2015 at 10:58 am
Basically i want to run a transaction between two different instance of server,
so that i can rollback task done at both the instances when ever i want.
so please tel me is it possible to do it using batch file or script file with out making linked server
yours sincelrey
May 19, 2015 at 10:59 am
can i get any example or link
yours sincelrey
May 19, 2015 at 11:13 am
rajemessage 14195 (5/19/2015)
Basically i want to run a transaction between two different instance of server,so that i can rollback task done at both the instances when ever i want.
so please tel me is it possible to do it using batch file or script file with out making linked server
yours sincelrey
Without the use of linked servers, you would need to use some sort of application or applet.
If you wrote a powershell script - that could suit the "applet". Creating an ssis package could also server as an applet (imho).
But basically, you need some means of notifying back to one server or the other that something was wrong so a "watcher" mechanism needs to be in place.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2015 at 5:36 pm
rajemessage 14195 (5/19/2015)
Basically i want to run a transaction between two different instance of server,so that i can rollback task done at both the instances when ever i want.
To be honest, that's a bit like throwing spaghetti at the wall. My recommendation is to (of course) make sure that the data that you're going to include in the transaction is totally validated as correct and then pass it to a staging table (or tables) on the remote instance. Then have a stored procedure on the remote instance that does it's own transaction and call that stored procedure. That way, you don't have cross server transactions. If the remote proc fails, it can easily do a rollback on the remote server. Either way, the remote proc can send back a status as to whether it was successful or not without you having to go through the pains of or maintain a distributed transaction.
--Jeff Moden
May 20, 2015 at 12:09 am
The Open Query Is also an option for you i guess
google something on Openquerry
WINNERS NEVER QUIT AND QUITTERS NEVER WIN
sqlserversdba.wordpress.com
May 20, 2015 at 12:19 am
Almighty (5/20/2015)
The Open Query Is also an option for you i guessgoogle something on Openquerry
OpenQuery uses linked servers to query across servers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2015 at 12:31 am
oops sorry then Wrong suggestion
but then try the other one
go with ssis or
go with batch file
WINNERS NEVER QUIT AND QUITTERS NEVER WIN
sqlserversdba.wordpress.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply