Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Altering SP's in Different Servers Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, January 6, 2010 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 10,255, Visits: 13,219
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
Posted Wednesday, January 6, 2010 9:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, January 7, 2010 6:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 10,255, Visits: 13,219
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

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 #843462
Posted Thursday, January 7, 2010 6:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425
But we are using 2005
Post #843467
Posted Thursday, January 7, 2010 6:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 10,255, Visits: 13,219
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

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 #843496
Posted Thursday, January 7, 2010 7:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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?
Post #843509
Posted Thursday, January 7, 2010 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 10,255, Visits: 13,219
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

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 #843522
Posted Thursday, January 7, 2010 9:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 »

Add to briefcase

Permissions Expand / Collapse