Altering SP's in Different Servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Altering SP's in Different Servers

Altering SP's in Different Servers

Rate Topic

Display Mode

Topic Options

Author

Message

ningaraju.n

Posted Wednesday, January 06, 2010 6:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, 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

Post #842745

Jack Corbett

Posted Wednesday, January 06, 2010 7:16 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

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

Post #842774

ningaraju.n

Posted Wednesday, January 06, 2010 9:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, 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..

Post #843288

Jack Corbett

Posted Thursday, January 07, 2010 6:05 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #843462

ningaraju.n

Posted Thursday, January 07, 2010 6:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425

But we are using 2005 Smile

Post #843467

Jack Corbett

Posted Thursday, January 07, 2010 6:56 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

ningaraju.n (1/7/2010)

But we are using 2005 Smile

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.

Post #843496

ningaraju.n

Posted Thursday, January 07, 2010 7:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425

Then what shall i do.. No options at all? Hehe

Post #843509

Jack Corbett

Posted Thursday, January 07, 2010 7:22 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #843522

ningaraju.n

Posted Thursday, January 07, 2010 9:47 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425

Thanks a lot Jack,

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

Post #844081

« Prev Topic | Next Topic »

Permissions