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.

run MDX query in SQL Server Agent issue

Hello,

We have 'Create global cube' MDX query running fine in Management studio, but it can not run in job agent, error massage:
[136] Job testcube reported: Microsoft.AnalysisServices.Xmla.XmlaException: CREATE GLOBAL CUBE statement. FILENAME|C:\dw\cube\monthly\mycube.cub|DDL|<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Create AllowOverwrite="true"><ObjectDefinition><Database><ID>mycube</ID>
<Name>mycube</Name>
<Cubes><Cube><ID>mycube</ID><Name>mycube</Name>
<ScriptErrorHandlingMode>IgnoreAll</ScriptErrorHandlingMode>
<MeasureGroups><MeasureGroup>
<ID>Fact mycube</ID><Name>Fact mycube</Name>
<Source xsi:type="MeasureGroupBinding"><DataSourceID>mycube</DataSourceID><CubeID>mycube</CubeID><MeasureGroupID>Fact mycube</MeasureGroupID><Persistence>All</Persistence></Source><IgnoreUnrelatedDimensions>true</IgnoreUnrelatedDimensions><Measures><Measure><ID>Count Orders</ID><Name>Count Orders</Name><Visible>true</Visible><AggregateFunction>Sum</Aggregat

Anyone could help with? any suggestions?

Thanks in advance.

Is this the entire error message? And can you post your MDX? How is your job set up that calls this?

Thanks,

~Shari

|||Thanks for the response.

Following are the job step set up.
We created a new job and new step.
step name:testcube
Type: SQL Server Analysis Services Query
Run as: SQL Agent Service Account
server: my_server_name
database: mycube
commond:
CREATE GLOBAL CUBE mycube
Storage 'C:\DW\cube\monthly\mycube.cub'
FROM [mycube]
(
MEASURE [mycube].[Count Orders],
DIMENSION [mycube].[All Languages],
DIMENSION [mycube].[All Order Methods],
DIMENSION [mycube].[All Periods],
DIMENSION [mycube].[All Products],
DIMENSION [mycube].[All Reasons]
)

The message is entire error message which I can see, but seems already truncated.

Am I missing something?

Thanks.
|||

The error might be caused by lack of permissions on the Analysis Services server with the SQL Agent Service account.

Try to temporarily make SQL Agent Service an administrator for Analysis Services (start SQL Management Studio, right click on the AS server, Properties, Security tab). If that fixes the error, then you'll need to create (or edit) a role that allows querying the cube and has the SQL Agent Service as member.

|||Thank you very much Adrian for the suggestion.

We add administrator to Analysis Services, but the error is still the same.

What else I can try?

Thanks.
|||The error must be hidden in the log file for your job (process). It appears that the real problem is hidden deeper in the error.|||

Try running the create global cube ... MDX from within the Management Studio, cube you are referring to. Let me know if the cube gets built without error.

Thanks,

Shari

|||Thanks Shari for the reply.

The MDX "create global cube" query works fine in Management Studio, and it is created cub file.
The log file contents in SQL Server Agent are same as log file viewer.

What else I can check and try?

Thanks a lot.

|||Hi SQLexperts,

We created a domain\sqladmin user on the sql server, granted all privileges to this user and added this user to Analysis Service user group, but we still get the same error. We stuck on this problem a while, anyone could give us some suggestions? or another way to work around?

Any one have successed run MDX on SQL Agent, I would be grateful for that information.
Appreciate the help.
|||

Another way that I create the global cubes is through a SSIS package. Are you familiar with creating packages? I run the global build each night to create "fresh" files for offline use.

I am wondering now though if it is a permission issue, maybe one of the services is runniing locally but needs Network access and should be run as a Network Service.

~Shari

|||We still not solve this issue yet.
I have tried SSIS, no luck.
Which control flow you use? Analysis Service Execute DDL task or Processing task?

Could you kindly give me direction?

Appreciate any help.

|||

OLAP_user ... email me directly

hopefully you copied my email address already

|||Hello Sharil,

Unfortunately I do not know where can copy your email.

Thanks.

|||

When you tested your create cube statement from SSMS, were you logged in as the domain\sqladmin account? I find that logging in as the service account is sometimes the easiest way to track down some of these issues when a taks works for me, but fails when it is scheduled.

Aslo, have you checked that this user has access to the 'C:\DW\cube\monthly' folders?

|||

We use the following steps in the package to create nightly refreshes of the global cubes"

1. Execute SQL task - this has MDX that creates a MDX statement for each sales rep.

2. For Each loop - loops through each sales rep's MDX to create a single global file per rep

It is a pretty simple package. And if you are just creating one static global cube then you wouldn't need the ForEach loop. You would just need Execute SQL Task. And some notification as far as success or failure.

Does this help?

~Shari

run MDX query in SQL Server Agent issue

Hello,

We have 'Create global cube' MDX query running fine in Management studio, but it can not run in job agent, error massage:
[136] Job testcube reported: Microsoft.AnalysisServices.Xmla.XmlaException: CREATE GLOBAL CUBE statement. FILENAME|C:\dw\cube\monthly\mycube.cub|DDL|<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Create AllowOverwrite="true"><ObjectDefinition><Database><ID>mycube</ID>
<Name>mycube</Name>
<Cubes><Cube><ID>mycube</ID><Name>mycube</Name>
<ScriptErrorHandlingMode>IgnoreAll</ScriptErrorHandlingMode>
<MeasureGroups><MeasureGroup>
<ID>Fact mycube</ID><Name>Fact mycube</Name>
<Source xsi:type="MeasureGroupBinding"><DataSourceID>mycube</DataSourceID><CubeID>mycube</CubeID><MeasureGroupID>Fact mycube</MeasureGroupID><Persistence>All</Persistence></Source><IgnoreUnrelatedDimensions>true</IgnoreUnrelatedDimensions><Measures><Measure><ID>Count Orders</ID><Name>Count Orders</Name><Visible>true</Visible><AggregateFunction>Sum</Aggregat

Anyone could help with? any suggestions?

Thanks in advance.

Is this the entire error message? And can you post your MDX? How is your job set up that calls this?

Thanks,

~Shari

|||Thanks for the response.

Following are the job step set up.
We created a new job and new step.
step name:testcube
Type: SQL Server Analysis Services Query
Run as: SQL Agent Service Account
server: my_server_name
database: mycube
commond:
CREATE GLOBAL CUBE mycube
Storage 'C:\DW\cube\monthly\mycube.cub'
FROM [mycube]
(
MEASURE [mycube].[Count Orders],
DIMENSION [mycube].[All Languages],
DIMENSION [mycube].[All Order Methods],
DIMENSION [mycube].[All Periods],
DIMENSION [mycube].[All Products],
DIMENSION [mycube].[All Reasons]
)

The message is entire error message which I can see, but seems already truncated.

Am I missing something?

Thanks.
|||

The error might be caused by lack of permissions on the Analysis Services server with the SQL Agent Service account.

Try to temporarily make SQL Agent Service an administrator for Analysis Services (start SQL Management Studio, right click on the AS server, Properties, Security tab). If that fixes the error, then you'll need to create (or edit) a role that allows querying the cube and has the SQL Agent Service as member.

|||Thank you very much Adrian for the suggestion.

We add administrator to Analysis Services, but the error is still the same.

What else I can try?

Thanks.
|||The error must be hidden in the log file for your job (process). It appears that the real problem is hidden deeper in the error.|||

Try running the create global cube ... MDX from within the Management Studio, cube you are referring to. Let me know if the cube gets built without error.

Thanks,

Shari

|||Thanks Shari for the reply.

The MDX "create global cube" query works fine in Management Studio, and it is created cub file.
The log file contents in SQL Server Agent are same as log file viewer.

What else I can check and try?

Thanks a lot.

|||Hi SQLexperts,

We created a domain\sqladmin user on the sql server, granted all privileges to this user and added this user to Analysis Service user group, but we still get the same error. We stuck on this problem a while, anyone could give us some suggestions? or another way to work around?

Any one have successed run MDX on SQL Agent, I would be grateful for that information.
Appreciate the help.
|||

Another way that I create the global cubes is through a SSIS package. Are you familiar with creating packages? I run the global build each night to create "fresh" files for offline use.

I am wondering now though if it is a permission issue, maybe one of the services is runniing locally but needs Network access and should be run as a Network Service.

~Shari

|||We still not solve this issue yet.
I have tried SSIS, no luck.
Which control flow you use? Analysis Service Execute DDL task or Processing task?

Could you kindly give me direction?

Appreciate any help.

|||

OLAP_user ... email me directly

hopefully you copied my email address already

|||Hello Sharil,

Unfortunately I do not know where can copy your email.

Thanks.

|||

When you tested your create cube statement from SSMS, were you logged in as the domain\sqladmin account? I find that logging in as the service account is sometimes the easiest way to track down some of these issues when a taks works for me, but fails when it is scheduled.

Aslo, have you checked that this user has access to the 'C:\DW\cube\monthly' folders?

|||

We use the following steps in the package to create nightly refreshes of the global cubes"

1. Execute SQL task - this has MDX that creates a MDX statement for each sales rep.

2. For Each loop - loops through each sales rep's MDX to create a single global file per rep

It is a pretty simple package. And if you are just creating one static global cube then you wouldn't need the ForEach loop. You would just need Execute SQL Task. And some notification as far as success or failure.

Does this help?

~Shari

Run in Query Window in Management Studio

I thought we could run sqlcmd commands directly in the Query Editor within
Management Studio by clicking on the SQLCMD mode
So for example I tried this
sqlcmd -d userdb -i test.sql
in the query editor and it failed. However it succeeds when I run it from
the command prompt
Thankssqlcmd is not a sqlcmd command. It is a command understood by the *Operating System* command
interpreter, which *starts* sqlcmd. SSMS does understand sqlcmd commands, like :r and :Connect
provided you set SSMS in that mode (check the Query menu).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:OTiUrmrOIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I thought we could run sqlcmd commands directly in the Query Editor within Management Studio by
>clicking on the SQLCMD mode
> So for example I tried this
> sqlcmd -d userdb -i test.sql
> in the query editor and it failed. However it succeeds when I run it from the command prompt
> Thanks

Run in Query Window in Management Studio

I thought we could run sqlcmd commands directly in the Query Editor within
Management Studio by clicking on the SQLCMD mode
So for example I tried this
sqlcmd -d userdb -i test.sql
in the query editor and it failed. However it succeeds when I run it from
the command prompt
Thankssqlcmd is not a sqlcmd command. It is a command understood by the *Operating
System* command
interpreter, which *starts* sqlcmd. SSMS does understand sqlcmd commands, li
ke :r and :Connect
provided you set SSMS in that mode (check the Query menu).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:OTiUrmrOIHA.4712@.TK2MSFTNGP04.phx.gbl...[vb
col=seagreen]
>I thought we could run sqlcmd commands directly in the Query Editor within
Management Studio by
>clicking on the SQLCMD mode
> So for example I tried this
> sqlcmd -d userdb -i test.sql
> in the query editor and it failed. However it succeeds when I run it from
the command prompt
> Thanks[/vbcol]

run a new server registration

Hi:

My pc is windows 2003 server and I have installed SQL SERVER 2005 EXPRESS.

From Management Studio I made a New Server Registration with Windows Authentication.

I can't run the new server, its icon appears with a white dot (instead of green when a server is running or red when the

server is stopped)

I go to server properties and click the "Test" button and I get the next message error:

Testing the registered server failed. Verify the server name, credentials, an database, an then click Test again.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may becaused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL NetworkInterfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

what could be wrong?

How can I run the server?

Thanks!

Make sure Named Pipes and TCP/IP network are enabled.

You can set from Sql Server Configuration Manager.

|||Is the new registered SQL a remote instance? Make sure the server name is correct, and SQL Browser service is running on the SQL box.|||I have enabled remote connections at the surface area configuration tool but when I register the new server at Management Studio I get the same message box when I test this new server.

The type of server I want to register is Database Engine

The icon appears with a white dot again.

At the surface area configuration tool I can't start SQL Server Browser. Start, Stop, Pause and Resume buttons are disabled.

The default database engine server is running, its name is server1\sqlexpress.

Why I can't register another server?|||

yes

Named Pipes and TCP/IP network are enabled

|||You can start SQL Browser from services.msc or "SQL Server Configuration Manager". Is?the?new?server?you?want?to?register?a?remote?server??If?so?you?can?take?a?look?at?this?post:
http://forums.asp.net/thread/1289341.aspx

Rules and defaults

Hi,

Can any one provide me information on creating rules and defaults in SQL Server2005, when i tried right clicking rules or defaults in management studio instead of new option i'am getting only refresh option.

This feature was working smoothly in sql2000.....I'am trying this on Microsoft SQL Server2005 Standard Edition.

thanks in advance

Mat

Rules and defaults are deprecated in 2005. You should now use constraints, which you define in the table designer. Rules and defaults are still supported, but you have to create them manually by writing the code yourself. You cannot create them in Management Studio because you should not be using them any more.

This comes from the 2000 documentation
"Rules are a backward-compatibility feature that perform some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column."
"Defaults, a backward compatibility feature, perform some of the same functions as default definitions created using the DEFAULT keyword of ALTER or CREATE TABLE statements. Default definitions are the preferred, standard way to restrict column data because the definition is stored with the table and automatically dropped when the table is dropped."

And this from the 2005 documentation
"CREATE RULE will be removed in a future version of Microsoft SQL Server. Avoid using CREATE RULE in new development work, and plan to modify applications that currently use it. We recommend that you use check constraints instead."
"CREATE DEFAULT will be removed in a future version of Microsoft SQL Server. Avoid using CREATE DEFAULT in new development work, and plan to modify applications that currently use it. Instead, use default definitions created using the DEFAULT keyword of ALTER TABLE or CREATE TABLE."