Adding a new SP problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding a new SP problems

Author

Topic

gduke
Starting Member

7 Posts

Posted - 2008-10-17 : 12:17:17

I am trying to run a simple create procedure script in sql 2000. My problem is every time I run the script the default settings are adding:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I need to turn this setting off. I need to do a code compare and this is causing me problems. Can anybody help?

Thanks,

gduke

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 12:29:56

Which tool are you using for adding SP's?

gduke
Starting Member

7 Posts

Posted - 2008-10-17 : 13:20:47

quote:
Originally posted by visakh16

Which tool are you using for adding SP's?

Query Analyzer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-17 : 13:23:12

This sounds like the "Generate SQL script" wizard in Enterprise Manager.

You'll need to use a text editor to modify the output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-17 : 13:24:02

Same goes for Query Analyzer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gduke
Starting Member

7 Posts

Posted - 2008-10-17 : 13:55:06

quote:
Originally posted by tkizer

Same goes for Query Analyzer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I don't think we are talking about the same thing. When I run this sp to put it IN THE DATABASE it is wrapping it with the SET QUOTED_IDENTIFIER and ANSI_NULLS in the database. I need to know how to turn this off.

--START SCRIPT

create procedure spAA3200BRpt
() as

declare @vFromZip Varchar(9)

go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 14:03:53

quote:
Originally posted by gduke

quote:
Originally posted by tkizer

Same goes for Query Analyzer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I don't think we are talking about the same thing. When I run this sp to put it IN THE DATABASE it is wrapping it with the SET QUOTED_IDENTIFIER and ANSI_NULLS in the database. I need to know how to turn this off.

--START SCRIPT

create procedure spAA3200BRpt
() as

declare @vFromZip Varchar(9)

go

Nope. i dont think so. Tara was exactly speaking about your problem. You seem to use some kind of wizard for writing code which appends the settings lines to code. thats why she suggested use of query analyser for coding.

gduke
Starting Member

7 Posts

Posted - 2008-10-17 : 14:15:26

I just coded this in Query Analyzer. I typed it in myself.

create procedure GREG
(
@pCono varchar(4)
) as

declare @vCategory int

set @vCategory = 2

go

I then went and viewed it in the database and it looks like this:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER procedure GREG
(
@pCono varchar(4)
) as

declare @vCategory int

set @vCategory = 2

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

How can I turn that off?

Thanks,

gduke

monkeybite
Posting Yak Master

152 Posts

Posted - 2008-10-17 : 14:23:28

if you're using isqlw, go to tools menu, then options, then connection properties tab. start unchecking boxes and then reconnect. this menu sets default properties on every connection thru iSQLw.

~ mb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources