January 6, 2010 at 6:48 am
Dear Friends,
I have text of an stored procedure,I want to execute it in different server
from my server For Eg:
I have SP as
ALTER PROC uspGetUser
AS
SELECT * FROM [User]
I have to run this query in vijay\sql2005 which is my friends server from my server ie; ningaraju\sql2005
Is there an way to accomplish this task? If please do the need full
Thanks in advance
January 6, 2010 at 7:16 am
If you are using SSMS you need to connect to the other server and then run the statement in the context of the correct database. First you need rights to connect to the other server, then you need rights to create/alter objects in the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 6, 2010 at 9:02 pm
Thanks Jack,
But I don't want to do it manually... So i need query which alters the SP in
all different servers at once..
January 7, 2010 at 6:05 am
If you have SSMS 2008 you can run a multi-server query, see this blog post[/url]. If not you could do a batch file or cmdexec job that uses sqlcmd to connect to each server and run the same code. Or you could use PowerShell to run the same query against each server. For sqlcmd and PowerShell you can save the query in a text file and load the query from there. With PowerShell you can use a file or table to store the servers to connect to and load those up when the script starts.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2010 at 6:25 am
But we are using 2005 🙂
January 7, 2010 at 6:56 am
ningaraju.n (1/7/2010)
But we are using 2005 🙂
That's why I said "IF". MY SQL Servers are all running 2005, but I have the 2008 tools (SSMS) which allows me to use the mult-server query functionality because it is a client functionality. That is also why I mentioned sqlcmd and PowerShell as either of those will work as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2010 at 7:11 am
Then what shall i do.. No options at all?:hehe:
January 7, 2010 at 7:22 am
Options:
1. sqlcmd - create a batch file that uses sqlsmd to connect to each server that executes a saved sql script against each server
2. PowerShell - create a script that either has hard-coded connection information for each server or that reads the server information from a table or file and then executes a saved sql script against each connection.
3. Buy Toad for SQL Server[/url] from Quest as it has multi-server query capability.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2010 at 9:47 pm
Thanks a lot Jack,
can you give some examples on implementing my requirement through sqlcmd
or power shell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply