how to run a transaction accross multipale instance of sqlserver without linked server

Question

how to run a transaction accross multipale instance of sqlserver without linked server

rajemessage 14195

Hall of Fame

Points: 3315
More actions
May 18, 2015 at 10:21 am

#300785

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

joshdbguy SSCertifiable Points: 7707 More actions · Answer 2

You can do it with a powershell script. Are you not wanting to link the servers for a specific reason? Like security vulnerabilities?

Almighty SSCrazy Points: 2001 More actions · Answer 3

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

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 4

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

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 5

rajemessage 14195

Hall of Fame

Points: 3315

May 19, 2015 at 10:59 am

#1799108

can i get any example or link

yours sincelrey

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Almighty SSCrazy Points: 2001 More actions · Answer 8

The Open Query Is also an option for you i guess

google something on Openquerry

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Almighty (5/20/2015)
The Open Query Is also an option for you i guess
google 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Almighty SSCrazy Points: 2001 More actions · Answer 10

oops sorry then Wrong suggestion

but then try the other one

go with ssis or

go with batch file