April 3, 2012 at 12:22 am
Hi all,
I am in a new project where I am the only SQL Server resource. I have been provided SSMS for my development work. I need to write scripts for creating tables and Sps in development environments ans pass on the scripts to deployment teams to execute the scripts in other environments.
This is how i create my scripts:
Use db_name
go
Create table \procedure..
...
I store scripts like these with extension .sql and pass in on to deployment team
But since I am the only SQL Server resourse only I have SSMS installed while deployemyts teams use some other tools like SQl teradata assistant or Toad to execute scripts.In these tools "go" command does not work which is understood. I need to come up with a way in which scripts can be deployed by these team using whatever tool they are using without having them to make much change in the scripts. These deployemts team have no idea on databse sps or tables etc.
I wonder how to sort this out and how people go about such deployements.. Any suggestions??
April 3, 2012 at 2:12 am
You can add a stored procedure in your database which will have all the content of the script. Then you can provide the stored proc name to the deployment team who has to just run "exec procname" to run the script.
Alternative approach can be to use sqlcmd to execute the sql script on sql server instance, this would use the same sql script along with the server name and connection to it.
--Divya
April 3, 2012 at 4:00 am
Hi Divya,
even through SQl cmd I will have to connect to database for which Use dn_name ; go will be needed...
April 3, 2012 at 5:05 am
itskanchanhere (4/3/2012)
Hi Divya,even through SQl cmd I will have to connect to database for which Use dn_name ; go will be needed...
No, its a command prompt cmd which need to be executed specifying the path of your sql script and configurable connection for sql server instance.
--Divya
April 3, 2012 at 5:19 am
If deployment team has SQL Server and BIDS installed, you can create SSIS package and put your SQL Scripts in Execute SQL Task.
Deployment team just need to pass server name, credentials to execute the package. SSIS package will create tables, procs etc objects.
Thanks
April 3, 2012 at 5:36 am
Can someone tell me how should deployments ideally happen ?? I dont need any out of the box techniques..
April 3, 2012 at 5:46 am
There is no "ideal" deployment. In most systems I've worked on, I put the database into source control and then use comparisons between source control and a database to generate a TSQL script that will then be used for deployment. That's the simplified version. For a detailed discussion of the method you can download the free book, SQL Server Team-based Development[/url]. I wrote the chapters on deployments.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 3, 2012 at 8:08 am
itskanchanhere (4/3/2012)
In these tools "go" command does not work which is understood. I need to come up with a way in which scripts can be deployed by these team using whatever tool they are using without having them to make much change in the scripts. These deployemts team have no idea on databse sps or tables etc.
you can write your script with out the go and it will run just fine. go is a batch seperator in SSMS. i can change that BLABLA on my local machine (infact i may just do that)
USE DB_Name
CREATE TABLE Test(
id INT IDENTITY(1,1),
BLA VARCHAR(MAX)
)
the above will run just fine in a script. im not sure if you can create your
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 3, 2012 at 8:09 am
itskanchanhere (4/3/2012)
In these tools "go" command does not work which is understood. I need to come up with a way in which scripts can be deployed by these team using whatever tool they are using without having them to make much change in the scripts. These deployemts team have no idea on databse sps or tables etc.
you can write your script with out the go and it will run just fine. go is a batch seperator in SSMS. i can change that BLABLA on my local machine (infact i may just do that)
USE DB_Name
CREATE TABLE Test(
id INT IDENTITY(1,1),
BLA VARCHAR(MAX)
)
the above will run just fine in a script. try it out in a test environment and prove it to your self
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply