SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Altering SP's in Different Servers


Altering SP's in Different Servers

Author
Message
ningaraju.n
ningaraju.n
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 425
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
Jack Corbett
  Jack Corbett
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30780 Visits: 14917
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ningaraju.n
ningaraju.n
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 425
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..
Jack Corbett
  Jack Corbett
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30780 Visits: 14917
If you have SSMS 2008 you can run a multi-server query, see this blog post. 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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ningaraju.n
ningaraju.n
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 425
But we are using 2005 :-)
Jack Corbett
  Jack Corbett
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30780 Visits: 14917
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ningaraju.n
ningaraju.n
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 425
Then what shall i do.. No options at all?Hehe
Jack Corbett
  Jack Corbett
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30780 Visits: 14917
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 from Quest as it has multi-server query capability.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ningaraju.n
ningaraju.n
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 425
Thanks a lot Jack,


can you give some examples on implementing my requirement through sqlcmd
or power shell
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search