What is best way to run script against MS-SQL?

[Spacerat]

So i have a few script files that need to be run against a MS SQL DB the whole thing to create a file and then a batch file is a pain....

Dont want to manually run the individual files either

 

[Messugga]

How many is a few?

 

[Kosmik]

Create a ssis package and run from commandline if you want and it's complex.

 

[SauRoNZA]

I was going to say Crontab...but then saw it's MS not MY.

 

[Spacerat]

How many is a few?

can vary
I normally put all DDL statements for a single table in a script file.

Can be 20-30. But creating the DB has a lot. Just the basic deployment for a new DB is about 80 tables/ & 80 views etc. So at least 160 files.

In Firebird I can 'import' a script into another script and run the top one. Then all of them are run in the correct sequence.

 

[Spacerat]

Create a ssis package and run from commandline if you want and it's complex.

Hmm this needs to be able to run against Azure as well...

 

[[)roi(]]

If you're adverse to building your own; then maybe have look at something like RedGate ReadyRoll or ApexSQL Scripting

 

[Pho3nix]

Hmm this needs to be able to run against Azure as well...

Should still work as long as your sql box running the batch or package has internet access.

 

[Necropolis]

Is it a once-off run?

 

[Necropolis]

You could do this: https://msdn.microsoft.com/en-us/library/ms170572.aspx

 

[Spacerat]

If you're adverse to building your own; then maybe have look at something like RedGate ReadyRoll or ApexSQL Scripting

My problem is not so much the generation of the scripts, which is what Apex seems to do. The scripts are written by hand or generated from my UML tool. It is actually the deployment of the scripts that I want to find a solution to. I find MS SQL Studio pretty ****ty actually. Don't know why I have to leave the environment to Powershell or cmdline to deploy a script. Why not in the DB tool environment?

Is it a once-off run?

No, each software release requires one or more DB version updates. Each update usually have several script files that need to be run.

You could do this: https://msdn.microsoft.com/en-us/library/ms170572.aspx

I currently do this, but I find it a PITA. Doesn't make sense to have to go to cmdline to run a series of scripts if I could have done it straight out of the DB tool...

 

[Necropolis]

I currently do this, but I find it a PITA. Doesn't make sense to have to go to cmdline to run a series of scripts if I could have done it straight out of the DB tool...

What would your ideal solution to this issue be then?

 

[Kosmik]

You can use ssms to deploy ssis into an instance, not sure about azure though as I understand a lot of the extra features have been stripped besides the basic engine.

*edit* I see Azure does not but according to some devs on code project, AWS sql servers allow SSIS and agent if you really need it.

 

[prod]

Don't know why I have to leave the environment to Powershell or cmdline to deploy a script. Why not in the DB tool environment?

So you have all the scripts but you need to deploy/execute them on different environments? I honestly don't think SSMS should be at fault here, basic release management solves these type of things, unless I'm misunderstanding what the problem is.

 

[Urist]

Looks like an interesting thread but i`m not sure I understand the problem and the suggested solutions?
Why not use @@trancount in ssms?

 

[Pho3nix]

So you have all the scripts but you need to deploy/execute them on different environments? I honestly don't think SSMS should be at fault here, basic release management solves these type of things, unless I'm misunderstanding what the problem is.

As above. When you deploy your code rm tools should sort all that out.

 

[Spacerat]

What would your ideal solution to this issue be then?

Well, to date, I have used IBExpert (IBE) to manage Firebird databases locally (dev & test) and remotely (clients). When developing, I write the scripts in IBE because it has intellisense for the DB. Exactly like I do in SSMS/VS. As a rule, I create a script file per table/view/proc to keep things organised. These script files go into version control. Then I create a top-level migration script that imports and runs the other individual scripts in correct sequence. Right from IBE. E.g.

VersionX.sql would contain:

Import "Table1.sql";
Import "View1.sql";
Import "Proc21.sql";
etc.

Then all I need to do for deployment is, from IBE, connect to the DB and run the top-level script. Whether remote DB or local, doesn't matter. I can also interactively highlight parts of the top level script to run. This helps a lot when there is some kind of problem with one of the scripts.

Having to create a batch file is not so much as issue. But not having the ability to test parts of it interactively and having to go to command line to run it is a major pain. For me, at least. Or maybe I am just approaching the problem incorrectly...

So you have all the scripts but you need to deploy/execute them on different environments? I honestly don't think SSMS should be at fault here, basic release management solves these type of things, unless I'm misunderstanding what the problem is.

.
See above. My point is that SSMS cannot run a top-level script that includes all the individual scripts for a release. At least that is what I understand it cannot do.

Looks like an interesting thread but i`m not sure I understand the problem and the suggested solutions?
Why not use @@trancount in ssms?

Not sure how @@trancount solves the problem?

As above. When you deploy your code rm tools should sort all that out.

Not sure what rm tools are. Pls elaborate

 

[Necropolis]

Maybe have a look at this.

Redgate make some awesome tools.

 

[Pho3nix]

.
See above. My point is that SSMS cannot run a top-level script that includes all the individual scripts for a release. At least that is what I understand it cannot do.

Not sure what rm tools are. Pls elaborate

Release management tools.

SSMS can do what you are mentioning? Please try it, I'm sure I've done something like that before.

Alternatively, SQL Server database project would do all of what you want. Painful to use but creates a semi-decent release script to deploy with.

 

[Spacerat]

Maybe have a look at this.

Redgate make some awesome tools.

Yes will look into this. I have their .NET toolset. Expensive, but paid for itself within a week.

Release management tools.

SSMS can do what you are mentioning? Please try it, I'm sure I've done something like that before.

Alternatively, SQL Server database project would do all of what you want. Painful to use but creates a semi-decent release script to deploy with.

Hmm will check but all the googling I have done points to having to run a cmdline script. Does not seem that SSMS can do 'nested' script includes. I want to make it painless. It is already painful. Surely other ppl have the same scenario?