January 16, 2006 at 8:11 am
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]
January 17, 2006 at 5:54 am
January 17, 2006 at 1:19 pm
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
January 17, 2006 at 6:47 pm
I'm with Jules - what is the purpose? Perhaps we can guide you around that instead?
January 18, 2006 at 8:42 am
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]
January 18, 2006 at 9:23 am
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
January 18, 2006 at 9:32 am
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]
January 18, 2006 at 2:29 pm
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
January 19, 2006 at 7:30 am
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
January 19, 2006 at 8:50 am
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]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply