Run scripts on multiple servers in SQL Management Studio

I have a number of scripts that I need to run on multiple servers and databases. Once I have created a Solution with them all in, how do I make them run in a specific sequence, and how can I switch the focus of the entire set of scripts to the other databases I want to run them on?

Thanks,

-Rob

There is no way to do this included in SQL Management Studio. Solutions in SMS aren't like VS solutions, in that you can execute code pages and objects. In SMS the solution is just a convenient place to store and organize the script files you are working with.

You would have to open each script seperately and execute it against the server and database you wanted in the order you wanted.

Sorry, it's going to be a manual process. There are some 3rd party tools that might help some. Check Red Gate and APEX products.

|||

You can do this by using a batch file to execute either OSQL or SQLCMD (preferred) - both of which can be used to execute script files. If you pass server and database names as parameters into your batch file you can then pass the parameter values to OSQL or SQLCMD.

I don't have any examples to hand, but the following links should set you on the right track:

http://msdn2.microsoft.com/en-us/library/ms162806.aspx

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

This is how we deploy changes to our databases in each of our environments - the only thing that changes between each environment is the server name parameter. We generally have one batch file per database and one SQL script per database object - one batch file can be used to execute hundreds of SQL script files if need be.

Chris

|||

Dan and Chris,

Thanks for your response on my question. Not the answers I was hoping to get, but thanks anyway!! I have used the DOS bat file method successfully in the the past, but it is a bit clunky and was hoping against hope that there would be a better method in the SQL management Studio. DO you think I may be looking in the wrong tool?

I can't believe that I am the only one who thinks that the lack of a tool or process to set up implementation of scripts is a serious gap in the SQL Management Studio.

Thanks again, Rob

|||

You have to remember that SQL Server is an RDBMS. Tools provide for development are extras that I find invaluable. Just look at how clunky the management and development tools that come with Oracle have been over the years.

On a good note, you may be able to make some progress by developing your procedures and sql code in Visual Studio instead of SMS. If you have VS 2005 professional edition you can create a sql server database project that will allow you to write t-sql code and execute it. You should be able to write a minor application that would execute the scripts for you against a list of databases and/or servers.

Sorry that there isn't an easier way to do it through SMS. Perhaps it will be a forthcoming feature. I'll suggest it on the product feedback group.

Run scripts on multiple servers in SQL Management Studio

I have a number of scripts that I need to run on multiple servers and databases. Once I have created a Solution with them all in, how do I make them run in a specific sequence, and how can I switch the focus of the entire set of scripts to the other databases I want to run them on?

Thanks,

-Rob

There is no way to do this included in SQL Management Studio. Solutions in SMS aren't like VS solutions, in that you can execute code pages and objects. In SMS the solution is just a convenient place to store and organize the script files you are working with.

You would have to open each script seperately and execute it against the server and database you wanted in the order you wanted.

Sorry, it's going to be a manual process. There are some 3rd party tools that might help some. Check Red Gate and APEX products.

|||

You can do this by using a batch file to execute either OSQL or SQLCMD (preferred) - both of which can be used to execute script files. If you pass server and database names as parameters into your batch file you can then pass the parameter values to OSQL or SQLCMD.

I don't have any examples to hand, but the following links should set you on the right track:

http://msdn2.microsoft.com/en-us/library/ms162806.aspx

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

This is how we deploy changes to our databases in each of our environments - the only thing that changes between each environment is the server name parameter. We generally have one batch file per database and one SQL script per database object - one batch file can be used to execute hundreds of SQL script files if need be.

Chris

|||

Dan and Chris,

Thanks for your response on my question. Not the answers I was hoping to get, but thanks anyway!! I have used the DOS bat file method successfully in the the past, but it is a bit clunky and was hoping against hope that there would be a better method in the SQL management Studio. DO you think I may be looking in the wrong tool?

I can't believe that I am the only one who thinks that the lack of a tool or process to set up implementation of scripts is a serious gap in the SQL Management Studio.

Thanks again, Rob

|||

You have to remember that SQL Server is an RDBMS. Tools provide for development are extras that I find invaluable. Just look at how clunky the management and development tools that come with Oracle have been over the years.

On a good note, you may be able to make some progress by developing your procedures and sql code in Visual Studio instead of SMS. If you have VS 2005 professional edition you can create a sql server database project that will allow you to write t-sql code and execute it. You should be able to write a minor application that would execute the scripts for you against a list of databases and/or servers.

Sorry that there isn't an easier way to do it through SMS. Perhaps it will be a forthcoming feature. I'll suggest it on the product feedback group.

Rule: Max Number of Lines Per SP

A client believes that "a stored procedure should never have more than about
50 lines of code in it."
No rationalle behind that thought.
When pressed - she stated, "I heard that in a class once a long time ago."
I think this person is an idiot.
Is there or was there ever a time when a such a rule made sense for
technical reasons?
I'm just trying to see how such a belief might possibly make sense.
Thanks.= u r idiot
Message posted via http://www.webservertalk.com|||>> A client believes that "a stored procedure should never have more
than about 50 lines of code in it." No rationalle behind that thought.
<<
Actually, there is both a rationale and a history. Return with me now
to the 1970's when Software Engineering was being born. We would put
bugs into code and give them to people debug, and measure them.
Fifty lines is about one page of hardcopy print out. We found that
code modules that can be seen in toto are measurably easier to maintain
than those that were split across pages or screens. The longer the
module got, the harder the bugs were to find (I cannot remember the
formula, but it was greater than linear). University of Maryland and
SEI had some of the studies, as I recall.
This pattern held across languages, which meant COBOL, FORTRAN, Jovial
and Assembler in those days, then later Pascal and C.
Notice that I said "module", so subroutines, paragraphs or procedures
count as modules depending on your language.
In the case of SQL Server, this was the rule because T-SQL was never
meant to an application language. It is a very simple one-pass
compiler that was used to "fill in the holes" in the early product by
giving you triggers for DRI and other thigns we do declarative today.
.|||Joe,
<<Actually, there is both a rationale and a history>>
YOU have an interesting rationalle. So does everyone else who responded to
my OP, and I agree with all of them to date.
The idiot manager had no rationalle floating around inside her head beyond
"I heard it in a class once." We talked at length... she really had nothing
more than that.
I just wanted to see if I was missing something... apparently not.
Thanks Everyone!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114720755.550812.61610@.g14g2000cwa.googlegroups.com...
> than about 50 lines of code in it." No rationalle behind that thought.
> <<
> Actually, there is both a rationale and a history. Return with me now
> to the 1970's when Software Engineering was being born. We would put
> bugs into code and give them to people debug, and measure them.
> Fifty lines is about one page of hardcopy print out. We found that
> code modules that can be seen in toto are measurably easier to maintain
> than those that were split across pages or screens. The longer the
> module got, the harder the bugs were to find (I cannot remember the
> formula, but it was greater than linear). University of Maryland and
> SEI had some of the studies, as I recall.
> This pattern held across languages, which meant COBOL, FORTRAN, Jovial
> and Assembler in those days, then later Pascal and C.
> Notice that I said "module", so subroutines, paragraphs or procedures
> count as modules depending on your language.
> In the case of SQL Server, this was the rule because T-SQL was never
> meant to an application language. It is a very simple one-pass
> compiler that was used to "fill in the holes" in the early product by
> giving you triggers for DRI and other thigns we do declarative today.
> .
>|||Guadala Harry wrote:
<nothing of importance>
Your choice of words and tone are troublesome...
David G.|||On Thu, 28 Apr 2005 13:03:19 -0700, Guadala Harry wrote:

>A client believes that "a stored procedure should never have more than abou
t
>50 lines of code in it."
>No rationalle behind that thought.
>When pressed - she stated, "I heard that in a class once a long time ago."
>I think this person is an idiot.
>Is there or was there ever a time when a such a rule made sense for
>technical reasons?
>I'm just trying to see how such a belief might possibly make sense.
>Thanks.
>
Hi Guadala,
As Joe indicated, there is some rationale for this rule. But it should
be treated as a rule of thumb. The point is to reduce complexity.
Suppose you have a query like this:
SELECT a.Col1,
b.Col2,
..
e.ColN
FROM Table1 AS a
-- Some useful comments here
INNER JOIN Table2 AS b
ON b.Col8 = a.Col4
INNER JOIN Table3 AS c
ON ...
...
WHERE d.Col18 = 'Asdf'
AND e.Col4 < b.Col8
....
ORDER BY a.Col1,
b.Col2,
...
c.Col8
It's very readable, but probably more than 50 lines. But yoou coould
squeeze it in a lot less lines:
SELECT a.Col1, b.Col2, ..., e.ColN FROM Table1 AS a
INNER JOIN Table2 AS b ON b.Col8 = a.Col4 INNER JOIN Table3 AS c
ON ... WHERE d.Col18 = 'Asdf' AND e.Col4 < b.Col8 ....
ORDER BY a.Col1, b.Col2, ..., c.Col8
But would it be easier to read'
A reverse example: back in my mainframe / Cobol days, creating a report
(or an external interface) required you to move all fields in the
records from the read file to the corresponding field in the records to
be written. Code like this:
OUTREC.FIELD1 = INREC.FIELD1;
OUTREC.FIELD2 = INREC.FIELD2;
...
OUTREC.FIELD138 = INREC.FIELD138;
WRITE FILE (OUTFILE) FROM (OUTREC);
I had a colleague who took the 50-lines rule way too literally - he
would break this routine in three subroutines, and call them after each
other:
CALL FILL_OUTREC_PART1;
CALL FILL_OUTREC_PART2;
CALL FILL_OUTREC_PART3;
WRITE FILE (OUTFILE) FROM (OUTREC);
Senseless overhead, IMO.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<<Your choice of words and tone are troublesome>>
And ?|||And there are some general rules of respect and courtesy that everyone
should follow when posting. I think everyone understands your frustration -
we've all been there in one form or another (and perhaps even been the
source of frustration at times), but this isn't the forum for venting your
anger especially with insults, deserved or not.
Mike
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:O7$gY2ETFHA.2124@.TK2MSFTNGP14.phx.gbl...
> <<Your choice of words and tone are troublesome>>
> And ?
>

Rule of thumb about number of attributes and data points?

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

Overtraining basically means that you have a model that fit your training data very well. But the model performs very badly on unseen data. For example, if you are training a decision tree, the alogrithm you use may generate a decision tree to fit your training data perfectly. This can be easily achived by keep creating split until each node only contains data points from one sepcific class. In general, this kind of decision does not generalize well on new data. Let's use an extreme case, say, you wish to predict the weather. You have collected the weather data of the past 10 years. And you accidently choose date as input, and the Decision Tree algorithm generate a tree like this:

date

|

-

| |

Nov 15, 2006 ... Nov 15, 2005 ...

| |

Rainny Sunny

This tree is 100% correct on historical data, but totally useless on predict weather data.

Basically, Over training is a common issue for all algorithms, and a lot of techniques have been applied to reduce to risk of overtraining. The most common traditional one is Occam's razor, which prefer to use simpler models. More recently, Bagging and Boosting are also commonly used to address overtraining problem.

In pratical application, you can validate whether or not your model is overfitting by reserving part of your data as indepent testing, and only use the remaining data to train your model, while validate the accuracy of your model with the testing data.

As to the ratio of attribute number vs data points, there is no absolute requirement, although most algorithms perform better with larger training data.

Good luck,

Rule of thumb about number of attributes and data points?

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

Overtraining basically means that you have a model that fit your training data very well. But the model performs very badly on unseen data. For example, if you are training a decision tree, the alogrithm you use may generate a decision tree to fit your training data perfectly. This can be easily achived by keep creating split until each node only contains data points from one sepcific class. In general, this kind of decision does not generalize well on new data. Let's use an extreme case, say, you wish to predict the weather. You have collected the weather data of the past 10 years. And you accidently choose date as input, and the Decision Tree algorithm generate a tree like this:

date

|

-

| |

Nov 15, 2006 ... Nov 15, 2005 ...

| |

Rainny Sunny

This tree is 100% correct on historical data, but totally useless on predict weather data.

Basically, Over training is a common issue for all algorithms, and a lot of techniques have been applied to reduce to risk of overtraining. The most common traditional one is Occam's razor, which prefer to use simpler models. More recently, Bagging and Boosting are also commonly used to address overtraining problem.

In pratical application, you can validate whether or not your model is overfitting by reserving part of your data as indepent testing, and only use the remaining data to train your model, while validate the accuracy of your model with the testing data.

As to the ratio of attribute number vs data points, there is no absolute requirement, although most algorithms perform better with larger training data.

Good luck,

|||Does SQLServer 2005 have any support for boosting or bagging?
|||SQL Server doesn't have built in support for Bagging and Boosting. One way you can determine "how much" data you need for your particular problem is to reserve a test set and then create models with different amounts on training data. For example 1000, 2000, 4000, 8000, 16000, etc. cases. Graph the accuracy results of the models and see where the accuracy "flattens out". The point where accuracy stops increasing with additional data is the "ideal" amount of data for that particular problem.

Rule of thumb about number of attributes and data points?

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

Overtraining basically means that you have a model that fit your training data very well. But the model performs very badly on unseen data. For example, if you are training a decision tree, the alogrithm you use may generate a decision tree to fit your training data perfectly. This can be easily achived by keep creating split until each node only contains data points from one sepcific class. In general, this kind of decision does not generalize well on new data. Let's use an extreme case, say, you wish to predict the weather. You have collected the weather data of the past 10 years. And you accidently choose date as input, and the Decision Tree algorithm generate a tree like this:

date

|

-

| |

Nov 15, 2006 ... Nov 15, 2005 ...

| |

Rainny Sunny

This tree is 100% correct on historical data, but totally useless on predict weather data.

Basically, Over training is a common issue for all algorithms, and a lot of techniques have been applied to reduce to risk of overtraining. The most common traditional one is Occam's razor, which prefer to use simpler models. More recently, Bagging and Boosting are also commonly used to address overtraining problem.

In pratical application, you can validate whether or not your model is overfitting by reserving part of your data as indepent testing, and only use the remaining data to train your model, while validate the accuracy of your model with the testing data.

As to the ratio of attribute number vs data points, there is no absolute requirement, although most algorithms perform better with larger training data.

Good luck,

|||Does SQLServer 2005 have any support for boosting or bagging?
|||SQL Server doesn't have built in support for Bagging and Boosting. One way you can determine "how much" data you need for your particular problem is to reserve a test set and then create models with different amounts on training data. For example 1000, 2000, 4000, 8000, 16000, etc. cases. Graph the accuracy results of the models and see where the accuracy "flattens out". The point where accuracy stops increasing with additional data is the "ideal" amount of data for that particular problem.