marked transactions

I am running the following script in Query Analizer:
begin transaction xxxx with mark
update logmarks set logmark=2
commit transaction xxxx
go
The script runs without error and the logmarks table is updated correctly,
but when I check the logmarkhistory table to see if a row was inserted there
is nothing in there. What am I doing wrong or why does this happen?
Thanks in advance for any help.The transaction information will be stored in the logmarkhistory table
only if there is a active log backup chain.sql

Map One generic Input column to multiple Destination column

I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".

I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.

But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.

So how do I go about doing this the smart way?

thanks in advance!

Hi,

You can use "Copy Column" transformation component to copy one input column to multiple output columns. If you have to perform some computing between original and new columns, you can use "Derive Column" transformation.

Jean-Pierre Riehl

http://blog.djeepy1.net

http://www.bewise.fr

|||Sorry not very elegant, this is more work than creating all the output columns one by one. I want to create one DataType_NULL Column which I want to reuse to map to the destination columns.

|||For what you are describing I would probably just call the stored procedure from inside the script component. As you have seen, the OLE Command doesn't really support this, so script, Copy Column, or Derived Column are the only way to do this that I am aware of.

many BULK INSERTs and error "cannot obtain a lock"...

Hi,
I still have problem with my script that perform a sequence of
many
data files with a BULK INSERT in a loop and rarely (about 10% of
times) fails with "cannot obtain a lock" (see the original post
below).
Since this happens also on a dedicate server without any other
activity
(both sql server and the O.S. are idle apart for the BULK INSERT
loop)
I start thinking the cause could be in the ODBC connection and cursor
that are
constantly re-used during the whole loop (the odbc connection is
in autocommit) and so
A friend of mine suggested me that I could try to enforce a checkpoint
could a ' cursor.execute("CHECKPOINT") ' statement issued before
any BULK INSERT
solve my issue?
TIA!
bye,
PiErre
on 2007-02-16 PiErre wrote:
> Subject: suggestion to avoid error "cannot obtain a lock"
> Hi,
> I have a script (actually the same python script
> that runs locally on several ms sql 2000sp4
> server - connecting via mxodbc)
> that every night tries to bulk insert about 20 files
> of various size (from less than 10 MB
> to more than 300 MB - about 900 MB in total )
> and sometimes (about 5% of the times)
> the importation fails (randomly on
> one or more of the servers) with the error like
> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
> The SQL Server cannot obtain a LOCK resource at this time...
>
> There is no other activity on the db server at the error time and
> so I cannot detect what is the cause of the resource lock.
> The db is normally configured with recovery-model set to simple
> and autoshrink enabled (not my decision
> and I must provide clear evidence to have
> that "standard" changed), but even after
> disable it the errors still appear.
>
> What can I do to detect the actual lock cause?
> More important: what can I do to avoid
> such errors? I heard something
> about to set a checkpoint after every bulk insert
> but I don't know how to do that...
>
> Thanks in advance for your help!
> bye,
> PiErreTry specifying TABLOCK on the BULK INSERT statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PiErre" <siggy2@.supereva.it> wrote in message
news:1178184835.364450.30740@.y80g2000hsf.googlegroups.com...
> Hi,
> I still have problem with my script that perform a sequence of
> many
> data files with a BULK INSERT in a loop and rarely (about 10% of
> times) fails with "cannot obtain a lock" (see the original post
> below).
> Since this happens also on a dedicate server without any other
> activity
> (both sql server and the O.S. are idle apart for the BULK INSERT
> loop)
> I start thinking the cause could be in the ODBC connection and cursor
> that are
> constantly re-used during the whole loop (the odbc connection is
> in autocommit) and so
> A friend of mine suggested me that I could try to enforce a checkpoint
> could a ' cursor.execute("CHECKPOINT") ' statement issued before
> any BULK INSERT
> solve my issue?
> TIA!
> bye,
> PiErre
> on 2007-02-16 PiErre wrote:
>> Subject: suggestion to avoid error "cannot obtain a lock"
>> Hi,
>> I have a script (actually the same python script
>> that runs locally on several ms sql 2000sp4
>> server - connecting via mxodbc)
>> that every night tries to bulk insert about 20 files
>> of various size (from less than 10 MB
>> to more than 300 MB - about 900 MB in total )
>> and sometimes (about 5% of the times)
>> the importation fails (randomly on
>> one or more of the servers) with the error like
>> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
>> The SQL Server cannot obtain a LOCK resource at this time...
>> There is no other activity on the db server at the error time and
>> so I cannot detect what is the cause of the resource lock.
>> The db is normally configured with recovery-model set to simple
>> and autoshrink enabled (not my decision
>> and I must provide clear evidence to have
>> that "standard" changed), but even after
>> disable it the errors still appear.
>> What can I do to detect the actual lock cause?
>> More important: what can I do to avoid
>> such errors? I heard something
>> about to set a checkpoint after every bulk insert
>> but I don't know how to do that...
>> Thanks in advance for your help!
>> bye,
>> PiErre
>|||On 3 Mag, 12:08, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> Try specifying TABLOCK on the BULK INSERT statement.
>
There are good chance that your hint was EXACTLY what I was looking
for...
Thanks a lot!
bye,
PiErre

Manually Grow database files

I'm looking for a way to manually perform the same functionality that
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-WillWill
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
There's noting inherently "risky" with ALTER DATABASE.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
Imagine you are working in the SQL Server dev team for MS. You have implemented code that expend the
size of a database file. You now have to determine the TSQL command which will invoke your command.
Should it be some DBCC command? Or some ALTER DATABASE? What I'm trying to say is that the command
is just an interface to the functionality within SQL Server. MS are in fact moving away from DBCC
and system stored procedures in favor of DDL.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size?
No, it is the same functionality in the engine in the end. It will actually be perceieved quicker
because you don't have one or several persons waiting for the grow (because you grow before it is
full).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang

Managment Studio Scripting options?

Am I missing something on options somewhere? With Enterprise Manager when you choose to script a stored procedure you had the option to say include a drop statement and security statements. In Management Studio I don't see these options. Just script a create OR a drop. Not both. I think this is a giant step backwards for the tool if it isn't present or can't be configured.Nevermind...I found it. It was in the Generate Scripts wizard off the database. Too bad this wasn't easily exposed from the context menu on specific object (like a stored procedure).|||

This seems like a lot of work to get a script with the options I want.

Why can't the IDE just drop script options in the query window like Query Analyzer did in the past?

manager

hi I just install sql server express
I also download and run the managment script. but unfortunately manager wont
acces to the sqlserver.
where I can find tutorial /help for my problem?
thanks
Since Express is still in beta, you'll want to check in the Community
Newsgroups:
http://communities.microsoft.com/new...r2005&slcid=us
--Brian
(Please reply to the newsgroups only.)
"jamal" <patron@.wi.rr.com> wrote in message
news:3y5Ne.41984$3S5.8305@.tornado.rdc-kc.rr.com...
> hi I just install sql server express
> I also download and run the managment script. but unfortunately manager
> wont acces to the sqlserver.
> where I can find tutorial /help for my problem?
> thanks
>

Management Studio: Fatal Scripting Error

I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
|||This was addressed in Bug ID FDBK44155.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:ucmK$k1VGHA.5592@.TK2MSFTNGP09.phx.gbl...
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.

Management Studio scripting options

Hi there,
Is there apossibility to set the options for script creation in SQL
Server 2005 Management Studio? I want to create a script with a drop
and create statement...
Regards
Roman
No there isn't, all you can do is one or the other at the moment. It was
somehow missed from the released version. Lots of people have complained
and I am sure it will be back in witht he next sp.
Andrew J. Kelly SQL MVP
"Roman Mellenberger" <roman.mellenberger@.info-research.ch> wrote in message
news:v9b4s1hgm6pta45ukfoh9ucnjgs8ubdl6t@.4ax.com...
> Hi there,
> Is there apossibility to set the options for script creation in SQL
> Server 2005 Management Studio? I want to create a script with a drop
> and create statement...
> Regards
> Roman
|||Perhaps Bill's utility can be useful?
http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Roman Mellenberger" <roman.mellenberger@.info-research.ch> wrote in message
news:v9b4s1hgm6pta45ukfoh9ucnjgs8ubdl6t@.4ax.com...
> Hi there,
> Is there apossibility to set the options for script creation in SQL
> Server 2005 Management Studio? I want to create a script with a drop
> and create statement...
> Regards
> Roman

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!