generate sql script & SET options 
Author Message
 generate sql script & SET options

enterprise manager , right-click a stored procedure, all tasks, generate sql
script ...

it's my understanding that the t-sql generated (if run) would re-create the
sp *exactly* as it currently exists ...

in other words, a sp "knows" what options (ansi_nulls) were in effect at the
time it was created (irrelevant of what means were used to configure those
options on the connection used to create the sp) ...

... and so to exactly re-create the sp, before running the 'CREATE
PROCEDURE' sql the options must be set identically to how they were
originally set ...

is my understanding correct ?

if so, then when generating the t-sql, is enterprise manager determining the
appropriate 'SET' statements by comparing the options settings as they were
on sp creation versus the db's *current* global options settings (i'm
referring to right-click on db name > properties > options) ... ???



Mon, 15 May 2006 11:46:46 GMT
 generate sql script & SET options

Quote:
>>is my understanding correct ?

NO.

Quote:
>>a sp "knows" what options (ansi_nulls) were in effect at the
>>time it was created

No smart work there.
An SP is just what is inside the SP.

--
Roji. P. Thomas
SQL Server Programmer ;)
________________________


Quote:
> enterprise manager , right-click a stored procedure, all tasks, generate
sql
> script ...

> it's my understanding that the t-sql generated (if run) would re-create
the
> sp *exactly* as it currently exists ...

> in other words, a sp "knows" what options (ansi_nulls) were in effect at
the
> time it was created (irrelevant of what means were used to configure those
> options on the connection used to create the sp) ...

> ... and so to exactly re-create the sp, before running the 'CREATE
> PROCEDURE' sql the options must be set identically to how they were
> originally set ...

> is my understanding correct ?

> if so, then when generating the t-sql, is enterprise manager determining
the
> appropriate 'SET' statements by comparing the options settings as they
were
> on sp creation versus the db's *current* global options settings (i'm
> referring to right-click on db name > properties > options) ... ???



Mon, 15 May 2006 12:10:24 GMT
 generate sql script & SET options
Your understanding is correct. SQL Server remembers the SET options of the
connection, in which the stored procedure was created. And when you script
the stored procedure in Enterprise Manager, you will see those SET options
at the beginning of the stored procedure script.

So, it is important to maintain these SET options everytime and everywhere
you create this stored procedure. Otherwise, you will see different
execution plans and unpredictable results. For example:

The following two stored procedures basically have the same code, but behave
differently because they were created with different SET options:

SET ANSI_NULLS ON
GO

16, 1)
GO

SET ANSI_NULLS OFF
GO

16, 1)

Now, if you run these stored procedures as shown below, you will see that
only the second one raises an error, regardless of the running connection's
SET options:

EXEC PRC1 NULL
GO
EXEC PRC2 NULL
GO
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm



enterprise manager , right-click a stored procedure, all tasks, generate sql
script ...

it's my understanding that the t-sql generated (if run) would re-create the
sp *exactly* as it currently exists ...

in other words, a sp "knows" what options (ansi_nulls) were in effect at the
time it was created (irrelevant of what means were used to configure those
options on the connection used to create the sp) ...

... and so to exactly re-create the sp, before running the 'CREATE
PROCEDURE' sql the options must be set identically to how they were
originally set ...

is my understanding correct ?

if so, then when generating the t-sql, is enterprise manager determining the
appropriate 'SET' statements by comparing the options settings as they were
on sp creation versus the db's *current* global options settings (i'm
referring to right-click on db name > properties > options) ... ???



Mon, 15 May 2006 12:57:39 GMT
 generate sql script & SET options
Hi Vyas, and thanks for the response.

I notice in the SQL scripts that Enterprise Manager generates for various
SPs in the DB I am working on, that the script will SET various options ON
or OFF, then create the SP, and then finally SET the options to the opposite
of how it initially set them.

I find last part to be strange.  Enterprise Manager does not know anything
about the connection I might use to execute the script it has generated. It
does not know how the various options might be initially configured on that
connection. Just because "SET ANSI_NULLS ON" is a pre-requisite to properly
create the SP, doesn't mean that the final state of the connection should be
"SET ANSI_NULLS OFF".

Perhaps Enterprise Manager is "broken" in this regard?   Executing SQL such
as "SET <option>" only affect the options associated with the connection on
which the SQL is executed, correct?

(ALSO: How to write SQL to change the database-wide options ? -- the ones
you see in Enterprise Manager if you right-click the db-name, choose
Properties, and click the Options tab.)



Quote:
> Your understanding is correct. SQL Server remembers the SET options of the
> connection, in which the stored procedure was created. And when you script
> the stored procedure in Enterprise Manager, you will see those SET options
> at the beginning of the stored procedure script.

> So, it is important to maintain these SET options everytime and everywhere
> you create this stored procedure. Otherwise, you will see different
> execution plans and unpredictable results. For example:

> The following two stored procedures basically have the same code, but
behave
> differently because they were created with different SET options:

> SET ANSI_NULLS ON
> GO

passed',
> 16, 1)
> GO

> SET ANSI_NULLS OFF
> GO

passed',
> 16, 1)

> Now, if you run these stored procedures as shown below, you will see that
> only the second one raises an error, regardless of the running
connection's
> SET options:

> EXEC PRC1 NULL
> GO
> EXEC PRC2 NULL
> GO
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm



> enterprise manager , right-click a stored procedure, all tasks, generate
sql
> script ...

> it's my understanding that the t-sql generated (if run) would re-create
the
> sp *exactly* as it currently exists ...

> in other words, a sp "knows" what options (ansi_nulls) were in effect at
the
> time it was created (irrelevant of what means were used to configure those
> options on the connection used to create the sp) ...

> ... and so to exactly re-create the sp, before running the 'CREATE
> PROCEDURE' sql the options must be set identically to how they were
> originally set ...

> is my understanding correct ?

> if so, then when generating the t-sql, is enterprise manager determining
the
> appropriate 'SET' statements by comparing the options settings as they
were
> on sp creation versus the db's *current* global options settings (i'm
> referring to right-click on db name > properties > options) ... ???



Mon, 15 May 2006 21:37:56 GMT
 generate sql script & SET options
Hi John

Only two set options are actually saved with the stored procedure. SET
ANSI_NULLS and SET QUOTED_IDENTIFIER. The row for the proc in sysobjects
contains information about the value of these two options, and there are
OBJECTPROPERTY options to tell you what value of these two options is stored
with the procedure.
Because EM assumes you want to recreate the proc exactly the way it has been
stored, it will set these two options.

Even if you change the value of ANSI_NULLS or QUOTED_IDENTIFIER in the
connection, the procedure will run with the values in effect at the time the
proc was created.

Other SET options are not saved with the procedure, and the procedures
behavior may change depending on what the value of these other SET options
are when the procedure is executed.

Is that any help?

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com



Quote:
> Hi Vyas, and thanks for the response.

> I notice in the SQL scripts that Enterprise Manager generates for various
> SPs in the DB I am working on, that the script will SET various options ON
> or OFF, then create the SP, and then finally SET the options to the
opposite
> of how it initially set them.

> I find last part to be strange.  Enterprise Manager does not know anything
> about the connection I might use to execute the script it has generated.
It
> does not know how the various options might be initially configured on
that
> connection. Just because "SET ANSI_NULLS ON" is a pre-requisite to
properly
> create the SP, doesn't mean that the final state of the connection should
be
> "SET ANSI_NULLS OFF".

> Perhaps Enterprise Manager is "broken" in this regard?   Executing SQL
such
> as "SET <option>" only affect the options associated with the connection
on
> which the SQL is executed, correct?

> (ALSO: How to write SQL to change the database-wide options ? -- the ones
> you see in Enterprise Manager if you right-click the db-name, choose
> Properties, and click the Options tab.)



> > Your understanding is correct. SQL Server remembers the SET options of
the
> > connection, in which the stored procedure was created. And when you
script
> > the stored procedure in Enterprise Manager, you will see those SET
options
> > at the beginning of the stored procedure script.

> > So, it is important to maintain these SET options everytime and
everywhere
> > you create this stored procedure. Otherwise, you will see different
> > execution plans and unpredictable results. For example:

> > The following two stored procedures basically have the same code, but
> behave
> > differently because they were created with different SET options:

> > SET ANSI_NULLS ON
> > GO

> passed',
> > 16, 1)
> > GO

> > SET ANSI_NULLS OFF
> > GO

> passed',
> > 16, 1)

> > Now, if you run these stored procedures as shown below, you will see
that
> > only the second one raises an error, regardless of the running
> connection's
> > SET options:

> > EXEC PRC1 NULL
> > GO
> > EXEC PRC2 NULL
> > GO
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > What hardware is your SQL Server running on?
> > http://vyaskn.tripod.com/poll.htm



> > enterprise manager , right-click a stored procedure, all tasks, generate
> sql
> > script ...

> > it's my understanding that the t-sql generated (if run) would re-create
> the
> > sp *exactly* as it currently exists ...

> > in other words, a sp "knows" what options (ansi_nulls) were in effect at
> the
> > time it was created (irrelevant of what means were used to configure
those
> > options on the connection used to create the sp) ...

> > ... and so to exactly re-create the sp, before running the 'CREATE
> > PROCEDURE' sql the options must be set identically to how they were
> > originally set ...

> > is my understanding correct ?

> > if so, then when generating the t-sql, is enterprise manager determining
> the
> > appropriate 'SET' statements by comparing the options settings as they
> were
> > on sp creation versus the db's *current* global options settings (i'm
> > referring to right-click on db name > properties > options) ... ???



Mon, 15 May 2006 22:36:28 GMT
 generate sql script & SET options
Hi Kalen. Yes, that's very interesting ...

I was implementing an indexed view to use in a new SP (originally, I also
implemented indexed computed columns), and so I was changing various options
at the db-level (in EM) and at the QA connection-level.

Last night, I noticed that most of my UPDATE SPs had stopped working.  All
give the same error message :

" UPDATE failed because the following SET options have incorrect settings:
'ANSI_NULLS., ARITHABORT'. "

These UPDATE SPs have not been touched in months & months.

I went to EM > db-name > Properties > Options and turned-off all options
except "Auto create statistics" , "Auto update statistics" , "Torn page
detection".  (I believe this is the default.)

But my UPDATE SPs still fail ...

Here is how I am executing one of the SPs in QA:
------------------------------------------------------





------------------------------------------------------

here is the SQL generated by EM for one of these SPs :
--------------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[usp_WithdrawApplication]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[usp_WithdrawApplication]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE usp_WithdrawApplication
(


)
AS
SET NOCOUNT ON


StatusName = 'W'
BEGIN TRANSACTION



 BEGIN
 COMMIT
 END
ELSE
 BEGIN
 ROLLBACK
 END
GO

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


Quote:
> Hi John

> Only two set options are actually saved with the stored procedure. SET
> ANSI_NULLS and SET QUOTED_IDENTIFIER. The row for the proc in sysobjects
> contains information about the value of these two options, and there are
> OBJECTPROPERTY options to tell you what value of these two options is
stored
> with the procedure.
> Because EM assumes you want to recreate the proc exactly the way it has
been
> stored, it will set these two options.

> Even if you change the value of ANSI_NULLS or QUOTED_IDENTIFIER in the
> connection, the procedure will run with the values in effect at the time
the
> proc was created.

> Other SET options are not saved with the procedure, and the procedures
> behavior may change depending on what the value of these other SET options
> are when the procedure is executed.

> Is that any help?

> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com



> > Hi Vyas, and thanks for the response.

> > I notice in the SQL scripts that Enterprise Manager generates for
various
> > SPs in the DB I am working on, that the script will SET various options
ON
> > or OFF, then create the SP, and then finally SET the options to the
> opposite
> > of how it initially set them.

> > I find last part to be strange.  Enterprise Manager does not know
anything
> > about the connection I might use to execute the script it has generated.
> It
> > does not know how the various options might be initially configured on
> that
> > connection. Just because "SET ANSI_NULLS ON" is a pre-requisite to
> properly
> > create the SP, doesn't mean that the final state of the connection
should
> be
> > "SET ANSI_NULLS OFF".

> > Perhaps Enterprise Manager is "broken" in this regard?   Executing SQL
> such
> > as "SET <option>" only affect the options associated with the connection
> on
> > which the SQL is executed, correct?

> > (ALSO: How to write SQL to change the database-wide options ? -- the
ones
> > you see in Enterprise Manager if you right-click the db-name, choose
> > Properties, and click the Options tab.)



> > > Your understanding is correct. SQL Server remembers the SET options of
> the
> > > connection, in which the stored procedure was created. And when you
> script
> > > the stored procedure in Enterprise Manager, you will see those SET
> options
> > > at the beginning of the stored procedure script.

> > > So, it is important to maintain these SET options everytime and
> everywhere
> > > you create this stored procedure. Otherwise, you will see different
> > > execution plans and unpredictable results. For example:

> > > The following two stored procedures basically have the same code, but
> > behave
> > > differently because they were created with different SET options:

> > > SET ANSI_NULLS ON
> > > GO

> > passed',
> > > 16, 1)
> > > GO

> > > SET ANSI_NULLS OFF
> > > GO

> > passed',
> > > 16, 1)

> > > Now, if you run these stored procedures as shown below, you will see
> that
> > > only the second one raises an error, regardless of the running
> > connection's
> > > SET options:

> > > EXEC PRC1 NULL
> > > GO
> > > EXEC PRC2 NULL
> > > GO
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > http://vyaskn.tripod.com/
> > > What hardware is your SQL Server running on?
> > > http://vyaskn.tripod.com/poll.htm



> > > enterprise manager , right-click a stored procedure, all tasks,
generate
> > sql
> > > script ...

> > > it's my understanding that the t-sql generated (if run) would
re-create
> > the
> > > sp *exactly* as it currently exists ...

> > > in other words, a sp "knows" what options (ansi_nulls) were in effect
at
> > the
> > > time it was created (irrelevant of what means were used to configure
> those
> > > options on the connection used to create the sp) ...

> > > ... and so to exactly re-create the sp, before running the 'CREATE
> > > PROCEDURE' sql the options must be set identically to how they were
> > > originally set ...

> > > is my understanding correct ?

> > > if so, then when generating the t-sql, is enterprise manager
determining
> > the
> > > appropriate 'SET' statements by comparing the options settings as they
> > were
> > > on sp creation versus the db's *current* global options settings (i'm
> > > referring to right-click on db name > properties > options) ... ???



Tue, 16 May 2006 00:18:41 GMT
 generate sql script & SET options
Kalen, I'm finding in QA that no matter how I set ANSI_NULLS my SP still
fails:

Scenario #1

SET ANSI_NULLS OFF
GO





GO

Server: Msg 1934, Level 16, State 1, Procedure usp_WithdrawApplication, Line
11
UPDATE failed because the following SET options have incorrect settings:
'ANSI_NULLS.'.

Scenario #2

SET ANSI_NULLS ON
GO





GO

Server: Msg 1934, Level 16, State 1, Procedure usp_WithdrawApplication, Line
11
UPDATE failed because the following SET options have incorrect settings:
'ANSI_NULLS.'.

Here's the generate SQL script for the SP :

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[usp_WithdrawApplication]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[usp_WithdrawApplication]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE usp_WithdrawApplication
(


)
AS
SET NOCOUNT ON


StatusName = 'W'
BEGIN TRANSACTION



 BEGIN
 COMMIT
 END
ELSE
 BEGIN
 ROLLBACK
 END
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Tue, 16 May 2006 02:27:10 GMT
 generate sql script & SET options
wow. i found this in the msft kb ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305333&Produc...

this blows me away it's so opaque. how would anyone be expected to figure
this out?

they're talking about ARITHABORT ... but i think for the sake of confidence
you've got to get concerned about the configured state of all of the SET
options associated with a given connection ...

sounds like the best approach for an application (such as an asp.net
web-app) would be to always run the same batch of SET SQL statements
immediately before executing any SP --> always make sure that the SET
options are consistently configured before any SP is executed ...



Tue, 16 May 2006 03:20:34 GMT
 generate sql script & SET options
this is a viable solution -- at least you control your connection options
explicitly. in my DAL immediately after opening a connection i issue this
command:

SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS ON
SET NUMERIC_ROUNDABORT OFF

just to be sure :-))

also, it's recommended (by MS) to set arith abort at server level the moment
an indexed view is created in a db on the server. use something like this:

exec sp_configure N'user options', 64
reconfigure with override

the server level settings affects every (new) connection and helps if you
are using 3rd party tools to access objects in your db -- e.g. Analysis
Services' Analysis Manager

you can access this option through EM: Properties on Server, Connections
Tab -> Default connection options

HTH,
</wqw>



Quote:
> wow. i found this in the msft kb ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305333&Produc...
Quote:

> this blows me away it's so opaque. how would anyone be expected to figure
> this out?

> they're talking about ARITHABORT ... but i think for the sake of
confidence
> you've got to get concerned about the configured state of all of the SET
> options associated with a given connection ...

> sounds like the best approach for an application (such as an asp.net
> web-app) would be to always run the same batch of SET SQL statements
> immediately before executing any SP --> always make sure that the SET
> options are consistently configured before any SP is executed ...



Tue, 16 May 2006 10:06:42 GMT
 generate sql script & SET options
Hi Vlad, and thanks for the response.

I ran

exec sp_configure N'user options', 64
reconfigure with override

in an instance of QA connected to the server in question.  (The server with
a db that I created an indexed-view on.)

QA gave me the message:

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Configuration option 'user options' changed from 64 to 0. Run the
RECONFIGURE statement to install.

But what is strange is that I go to EM > Server > Properties > Connections
and the ARITHABORT checkbox shows as unchecked.

(The box is not checked for either the "Configured values" or the "Running
values" cases.)


Quote:
> this is a viable solution -- at least you control your connection options
> explicitly. in my DAL immediately after opening a connection i issue this
> command:

> SET

ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF
Quote:
> IER,ANSI_NULLS ON
> SET NUMERIC_ROUNDABORT OFF

> just to be sure :-))

> also, it's recommended (by MS) to set arith abort at server level the
moment
> an indexed view is created in a db on the server. use something like this:

> exec sp_configure N'user options', 64
> reconfigure with override

> the server level settings affects every (new) connection and helps if you
> are using 3rd party tools to access objects in your db -- e.g. Analysis
> Services' Analysis Manager

> you can access this option through EM: Properties on Server, Connections
> Tab -> Default connection options

> HTH,
> </wqw>



> > wow. i found this in the msft kb ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305333&Produc...

- Show quoted text -

Quote:

> > this blows me away it's so opaque. how would anyone be expected to
figure
> > this out?

> > they're talking about ARITHABORT ... but i think for the sake of
> confidence
> > you've got to get concerned about the configured state of all of the SET
> > options associated with a given connection ...

> > sounds like the best approach for an application (such as an asp.net
> > web-app) would be to always run the same batch of SET SQL statements
> > immediately before executing any SP --> always make sure that the SET
> > options are consistently configured before any SP is executed ...



Tue, 16 May 2006 19:22:15 GMT
 generate sql script & SET options
the strange think is that i remeber EM {*filter*} on this the same way here. i
just turned advanced options with this:

EXEC sp_configure 'show advanced option', '1'

HTH,
</wqw>



Quote:
> Hi Vlad, and thanks for the response.

> I ran

> exec sp_configure N'user options', 64
> reconfigure with override

> in an instance of QA connected to the server in question.  (The server
with
> a db that I created an indexed-view on.)

> QA gave me the message:

> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Configuration option 'user options' changed from 64 to 0. Run the
> RECONFIGURE statement to install.

> But what is strange is that I go to EM > Server > Properties > Connections
> and the ARITHABORT checkbox shows as unchecked.

> (The box is not checked for either the "Configured values" or the "Running
> values" cases.)



> > this is a viable solution -- at least you control your connection
options
> > explicitly. in my DAL immediately after opening a connection i issue
this
> > command:

> > SET

ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF

- Show quoted text -

Quote:
> > IER,ANSI_NULLS ON
> > SET NUMERIC_ROUNDABORT OFF

> > just to be sure :-))

> > also, it's recommended (by MS) to set arith abort at server level the
> moment
> > an indexed view is created in a db on the server. use something like
this:

> > exec sp_configure N'user options', 64
> > reconfigure with override

> > the server level settings affects every (new) connection and helps if
you
> > are using 3rd party tools to access objects in your db -- e.g. Analysis
> > Services' Analysis Manager

> > you can access this option through EM: Properties on Server, Connections
> > Tab -> Default connection options

> > HTH,
> > </wqw>



> > > wow. i found this in the msft kb ...

http://www.***.com/ ;en-us;305333&Produc...

- Show quoted text -

Quote:

> > > this blows me away it's so opaque. how would anyone be expected to
> figure
> > > this out?

> > > they're talking about ARITHABORT ... but i think for the sake of
> > confidence
> > > you've got to get concerned about the configured state of all of the
SET
> > > options associated with a given connection ...

> > > sounds like the best approach for an application (such as an asp.net
> > > web-app) would be to always run the same batch of SET SQL statements
> > > immediately before executing any SP --> always make sure that the SET
> > > options are consistently configured before any SP is executed ...



Tue, 16 May 2006 21:57:30 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. Set existing int column, IDENTITY option using T-SQL script

2. Generate MS-ACCESS table using SQL Script generated by SQL Server

3. option to automatically generate a change script

4. SQL Script to generate SQL Scripts???

5. generate SQL scripts from a script or stored procedure

6. Setting printer options in a script

7. Making Script Generated Records the Found Set

8. SELECT & OPTION w/ SET COMMAND

9. Generate SQL script in SQL 2000 for SQL 6.5

10. spooling files & setting options

11. dynamically generated SQL options?

12. how generate database SQL Scripts from T-SQL?


 
Powered by phpBB® Forum Software