How to script SPs in EM without SETs

Question

Post reply

How to script SPs in EM without SETs

JacekO

SSCertifiable

Points: 6610
More actions
January 16, 2006 at 8:11 am

#166564

When you script your SPs the EM adds
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
at the begining of each script.
Is there a way to configure EM not to do it?

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 1

can i ask why you want to do this?

Jules

www.sql-library.com[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

without using e3nterprise manager, here is a copy and paste solution:

declare

@isql varchar(2000),

@procname varchar(64)

create table #procResults(procid int identity(1,1) primary key,ProcText varchar(4000) )

declare c1 cursor for select name from sysobjects where xtype='P'

open c1

fetch next from c1 into @procname

While @@fetch_status <> -1

begin

insert into #procResults(ProcText)

exec sp_helptext @procname

insert into #procResults(ProcText)values ('GO')

fetch next from c1 into @procname

end

close c1

deallocate c1

select * from #procResults

drop table #procResults

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ian Yates SSCoach Points: 19738 More actions · Answer 3

I'm with Jules - what is the purpose? Perhaps we can guide you around that instead?

JacekO SSCertifiable Points: 6610 More actions · Answer 4

I typed a long reply and the whole went blank on me when I posted so I have to give a short explanation. We got mulitple people working on the development. each with their on copy of DB. Then they create the scripts usnig EM and we save them in our control/version management software. We build the database from the scripts and we want to have standard way of handling NULLs. For some reasons so me of the scripts had diffrent settings and the whole thing did not work as expected. So I figured out if we eliminate the SET commands in the script files than is should eliminate the suprises bacause we can control the settings globally and not per SP basis. But it looks like it is hardcoded and not configurable - at least I can not find a way to get rid of it.

I have found that the status field in sysobjects stores the settings used when the SP was created but I can figure out all the values (I guess it is bitwise). If I had the description of possible values this could be a way around to at least find out if any procedures do not follow the standard.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

the set operations are not part of the sp itself and only really ensure that the settings underwhich the sp was create are still in place. Sounds to me like some of your developers are not using ansi nulls or quote identifier. This would make their scripts are incompatible. You need them to use these settings in the same way or you wont really be able to mange your code.

Jules

www.sql-library.com[/url]

JacekO SSCertifiable Points: 6610 More actions · Answer 6

This is the problem. They write the code and when they script their settings at the time of creation are reflected in the SP script ( the SET options are in the script file) . We don't want that. Actually searching the status field of the sysobjects may be the answer to make sure everyone has the same settings. If I can get some info about that field's possible values.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 7

but surely there is a possibilty that a script they create will be dependent on the settings they have on their dev db. If they (the developers) have different settings then it is possible you will be putting incompatible code into production.

You can view settings by right clicking on db going to options tab check that ansi null and quote identifier tick boxes are the same in all dev dbs. You can set up model db so that all new dbs are created with the same settings. If these settings are the same you shouldnt have a problem and it will force you developers to right compatible code.

Hope this is helpful,

Jules

www.sql-library.com[/url]

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Here is a BIG problem:

quote: We got mulitple people working on the development. each with their on copy of DB.

Why do they each have their own copy of the DB? When one person makes a change, it will affect all, but no one will know it until it goes into production. How can developer A know if their stuff will work when Developer Z can be making changes to the database structure?

-SQLBill

JacekO SSCertifiable Points: 6610 More actions · Answer 9

This is not really that complicated. No one is allowed to make database structure changes on their own. We got a process in place that takes care of this.

We also have a test environment for integration testing after the developers are done with their own unit testing. The code must be unit tested (on the developers individual DB) before it makes to the test box. The road to production is VERY long. There is SQA in the queue as well.

We are FDA regulated so everything must be by the book, we go thru the hoops in order to ensure the quality.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]