0

I have a local stored procedure that I want to distribute out to many other SQL Servers without having to log onto each one manually.

All servers can access the same network share if I want to go down the .sql and SQLCMD route.

Alternatively all servers are linked to a "Central" server using a the same login credentials.

Essentially I want to avoid using lengthy OPEN QUERY's because the syntax becomes a management nightmare when working with lengthy stored procedures.

Thoughts welcome/appreciated

2

You could simply create a Group wihtin the registered servers pane in SSMS and add all servers to that group. Then you are able to run a query against the whole group. Just run the Create procedure (or whatever you need) there and it will be executed on all servers in the group (that could have connected with success).

Be aware that select queries ran against a group come back with results in a merged resultset that basically shows all results from all servers merged together with a ServerName column added for the origin (that does not allow any filtering by servers). This can be configured more detailed in SSMS at

Tools -> Options -> Query Results -> SqlServer -> Multiserver Results

In case the tables or views on the different servers have a different definition, the merging of the result will fail (in case you use select *). In that case use a query with defined columnnames that allow SSMS to merge the result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.