March 27, 2016 at 2:09 pm
Just upgraded to SQL 2014 development environment. Please tell me how I can convert back to the old way of stored procedure scripts? When I generate an ALTER script now it does this crazy: -
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Checklist_CreateTask]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
for all script generation, even those ad-hoc scripts. All apostrophes are doubled up and so this makes Stored Proc edits really difficult.
When I generate a CREATE script I get this: -
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Checklist_CreateTask]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Checklist_CreateTask] AS'
END
GO
Then an ALTER script is generated.
This is just not good for some of my historic script running code... I have tried to amend the default script generation back to SQL 2012, SQL 2010 etc but it still generated stored proc scripts in this way... any ideas?
March 27, 2016 at 2:34 pm
In SSMS, if you go to Tools / Options / SQL Server Object Explorer / Scripting and set 'Check for Object Existence' to 'false', does that fix it for you?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 27, 2016 at 3:13 pm
Sort of. However, I have code that I have written to manage how the scripts used to work for procs for 2012. This is what I get now: -
2012 Script did this: -
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddExtraShift]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddExtraShift]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddExtraShift]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[AddExtraShift]
Note - only one GO statement which is fine and the CREATE is within a sp_executeSQL which means that it is ready to run as a script.
2014 Script does this: -
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddExtraShift]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddExtraShift]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddExtraShift]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[AddExtraShift] AS'
END
GO
ALTER PROCEDURE [dbo].[AddExtraShift] ....
Completely useless! My script now creates procedure stubs (as the GO is after the Create). I then have an ALTER statement that has not been created to run as part of an EXEC statement (apostrophes are singular) so I have do complete re engineer my running code.
At present I have backing up my DB, restoring it to a platform with SQL 2012 management studio and generate script from there!
March 27, 2016 at 3:41 pm
May I ask what the purpose is behind these scripts? Are you using them to generate a dev database, or something similar? There may be other ways to do what you need ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 27, 2016 at 3:54 pm
The scripts are used to generate client databases. I have a software as a service product which runs on some servers. Each server holds about 25 to 30 databases. Clients register for the product and the software automatically generates a SQL database which is based on a template DB and a series of scripts to bring it up to version. The scripts are stored in a source DB against a version number etc. I have written some .NET code to load the scripts into the source DB in order. I have a job that is running every couple of minutes which looks to see if someone has registered for the product. If they have, a DB is created and the script is executed in order giving the user an up to date database.
My issue is that as part of the DB creation script I have to embed a "USE [DBNAME]" at the top of any script I run. This is fine until I get to the procs in the new way SQL 2014 generates PROC scripts as it uses an ALTER statement. As we all know the ALTER or CREATE statement has to be the first line of any script.
The scripts generated before 2014 get around this by using the sp_executesql command. However, this is omitted from these new scripts which means that they will fail as soon as I place by "USE [DBNAME]" before the script (as they are no longer the first line). I cannot use a GO statement as this is a runtime script running from the source DB.
It is not a biggy - I will just have to amend the scripts manually as part of my load - it just means the load time will be longer and there is always the chance of errors creeping in!
March 27, 2016 at 4:18 pm
matt 25763 (3/27/2016)
The scripts are used to generate client databases. I have a software as a service product which runs on some servers. Each server holds about 25 to 30 databases. Clients register for the product and the software automatically generates a SQL database which is based on a template DB and a series of scripts to bring it up to version. The scripts are stored in a source DB against a version number etc. I have written some .NET code to load the scripts into the source DB in order. I have a job that is running every couple of minutes which looks to see if someone has registered for the product. If they have, a DB is created and the script is executed in order giving the user an up to date database.My issue is that as part of the DB creation script I have to embed a "USE [DBNAME]" at the top of any script I run. This is fine until I get to the procs in the new way SQL 2014 generates PROC scripts as it uses an ALTER statement. As we all know the ALTER or CREATE statement has to be the first line of any script.
The scripts generated before 2014 get around this by using the sp_executesql command. However, this is omitted from these new scripts which means that they will fail as soon as I place by "USE [DBNAME]" before the script (as they are no longer the first line). I cannot use a GO statement as this is a runtime script running from the source DB.
It is not a biggy - I will just have to amend the scripts manually as part of my load - it just means the load time will be longer and there is always the chance of errors creeping in!
Sounds like you've done quite a bit of work to get this operating exactly how you want it, so I understand your pain.
(I should know whether this works, but I can't remember right now.) Have you tried connecting to your 2014 DBs from SSMS 2012?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 27, 2016 at 7:48 pm
Please see the screenshot below.
http://oi68.tinypic.com/xcn3o2.jpg
When generating scripts you can play with these options in the Advanced Settings.I always have this options set
"Check For Object Existence"=False.This makes sure that the script does not generate any of those crazy dynamic sql statements the ones you just highlighted in the post.
"Continue Scripting on error"=True/False.Depends on how heavy the script is and the number of objects involved.But usually I set it to True.
"Script DROP and CREATE"=I always set the third option in the list.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 28, 2016 at 2:23 pm
Thanks for your reply. I appreciate what you are saying, but I need re-runnable scripts which don't produce any errors. So I need to have the checks before any object creations or before I drop any objects. When I deploy an update I need to know about any errors in the script as these will be issues that need to be acted on.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply