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


creating table on all sql servers in the environment


creating table on all sql servers in the environment

Author
Message
Robin35
Robin35
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1733 Visits: 1290
Hi,

I have a requirement to create a table in database on all sql server instances in our environment.
We have a monitoring tool and it uses a sql instance.I can get the list of instances ( dev and prod ) from the monitoring tool. My concern is to connect to each and every instance and create a table.

With SQL agent i dont think its possible but i guess we can acheive this through SSIS.. Please post your valuable suggestions and let me know if you have any questions....

Thanks in advance...
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43985 Visits: 14925
You can create a server group in SSMS (Registered Servers tab), right-click on the group and select new query. Then the query will be run against all the servers at one time. As long as the database you are creating the table in has the same name it will work. You can see an article about mult-server queries here.

You could also use PowerShell, but I think a multi-server is query the way to go.



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
Robin35
Robin35
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1733 Visits: 1290
Jack Corbett (9/16/2013)
You can create a server group in SSMS (Registered Servers tab), right-click on the group and select new query. Then the query will be run against all the servers at one time. As long as the database you are creating the table in has the same name it will work. You can see an article about mult-server queries here.

You could also use PowerShell, but I think a multi-server is query the way to go.



Thanks Jack for your response. I need to drop and create this table and insert some values for every month....so its kind of automated process.....i believe we can achieve this through powershell....please post your ideas...thanks in advance...
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43985 Visits: 14925
Another option would be creating a master server and creating the job there. Start with this BOL topic



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
Robin35
Robin35
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1733 Visits: 1290
Jack Corbett (9/17/2013)
Another option would be creating a master server and creating the job there. Start with this BOL topic


Thanks Jack...

i found this website which is exactly i'm looking for..using powershell execute sql scripts on multiple sql instances..i'm posting here ....it might help some people in future...

http://bi-bigdata.com/2013/03/14/powershell-for-executing-a-sql-query-on-multiple-servers/

Thanks for all your help...
lptech
lptech
SSC Eights!
SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)

Group: General Forum Members
Points: 915 Visits: 3440
This can be done with an SSIS package, as documented in the article:

Looping through multiple servers in SSIS
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