Putting DDL statemnets (CREATE SCHEMA), in a transaction

Are T-SQL DDL Statements allowed within a transaction in SQL Server 2005?

Hi,

I'm working with some folks on a VS add-in that maps conceptual data models to physical DBMS implementations. Currently, the only target that doesn't generate a schema creation script inside a transaction is SQL Server 2005. Being a development project, having the CREATE SCHEMA script in a transaction would be useful, to avoid the need to clean out the parts of the DDL script that did run, and just start with a new iteration. When I asked why, they thought that SQL Server didn't allow DDL statements to run inside a transaction. Searching this, I could only find references that suggested you shouldn't (for performance reasons - but these are not an issue in this case).

The rules for this are likely in Books online, etc..., but searches I tried gave gave too many references, or too few.

If you know the answer or the exact reference, please pass that along. Also, if you know of an alternative way of removing a schema from a DB, without having to delete all the individual items within that schema first, please post that as well.

Thanks BRN..

Hi,

Here is some good info concerning your issue: http://www.sybase.com/detail?id=42077.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi and thanks for the quick response - I'll check it out. BRN..

|||

Hi,

Took a look at the SYBASE ref. That talks about MS having determined table create, alter, drop, not being allowed in a transaction due to 1) not wanting the DB structure altered 2) Performance.

As Mentioned, performance not an issue in this case. Also, I was hoping to find a MS document that stated their restrictions, and if they could be over-ridden, even if not recommended.

Still, more info than I had before, so thanks again. BRN..|||

Brian,

Not sure why you are consulting sybase documentation for SQL2005 issue.

The create schema statement can be run in a user transaction. You should be able to find most of the information @. http://msdn2.microsoft.com/en-us/library/ms189462.aspx

On the second issue, I assume you are asking if there is a way to drop a schema along with all objects contained in it in one shot. Unfortunately, this is not possible right now. You need to drop or move objects out of the schema before dropping it. We are considering this feature for a future release.

|||

Sameer,

Thanks for the link and the answer - I'll check out the link, and pass that along to the development team. The SYBASE reference was from an earlier reply, and the content was generic RDBMS, pointing out some preferences by specific vendors.

The reasons requiring schema objects to be dropped before dropping the schema are pretty easy to figure out - when a DB is populated. If there was a way to allow dropping a schema in one shot during the development phase, that would be helpful.

There might be reasons to allow dropping complete schemas in a production DB too; but that would be very dependant on how schemas (in the 2005 namespace seperate from ownership form), were utilized in the DB design. I'm looking at some of this in the project I mentioned.

Thanks again for the replies. BRN..

Push subscription: "schema and data"

If I make a database-structure change on the Publisher, will the structure
change replicate to the Subscriber (and update the structure there) ?
When pushing a new subscription, the wizard prompts if you would like to
push schema and data. Does 'schema' mean ... the database structure?
Thank you,
Bob
Use the sp_repladcolumn and sp_repldropcolumn stored procedures to replicate
schema changes for existing publications with subscriptions.
When you get the message - push schema and data, the schema does refer to
the table (and other objects) creation scripts - or as you put it database
structure.
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:eHTYxKGaEHA.3708@.TK2MSFTNGP10.phx.gbl...
> If I make a database-structure change on the Publisher, will the structure
> change replicate to the Subscriber (and update the structure there) ?
> When pushing a new subscription, the wizard prompts if you would like to
> push schema and data. Does 'schema' mean ... the database structure?
> Thank you,
> Bob
>
|||Thank you very much.
Just so that I am very clear, if I had made a manual change to the database
(not using the sp's you have pointed out) and I push out the subscription
without the database and schema because there is an existing database, then
the structure change would NOT replicate. Is this correct?
Thank you.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OTqXliGaEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Use the sp_repladcolumn and sp_repldropcolumn stored procedures to
replicate[vbcol=seagreen]
> schema changes for existing publications with subscriptions.
> When you get the message - push schema and data, the schema does refer to
> the table (and other objects) creation scripts - or as you put it database
> structure.
> "Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
> news:eHTYxKGaEHA.3708@.TK2MSFTNGP10.phx.gbl...
structure
>
|||its hard to tell what you mean.
If you make a schema change, and then create a publication and push it to a
subscription, yet the subscribers will get the schema change.
If you attempt to make a schema change to an existing publication and
subscription(s) you will be prevented from doing with an error message like
'This table is published for replication'.
The only way to do this is using the above mentioned replication stored
procedures.
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:ei7Tb4GaEHA.2488@.tk2msftngp13.phx.gbl...
> Thank you very much.
> Just so that I am very clear, if I had made a manual change to the
database
> (not using the sp's you have pointed out) and I push out the subscription
> without the database and schema because there is an existing database,
then[vbcol=seagreen]
> the structure change would NOT replicate. Is this correct?
> Thank you.
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OTqXliGaEHA.2516@.TK2MSFTNGP10.phx.gbl...
> replicate
to[vbcol=seagreen]
database[vbcol=seagreen]
> structure
to
>
|||The situation is this:
I had to delete my publication in order to make a database structure change
at the publisher ( I did get the error you mention).
I then re-created my publication and pushed a new subscription to my
subscriber. But I did not "check" the box to send schema and data.
So my subscriber indeed does not have the database change. This appears to
be my problem.
In the future it would appear I have three options:
1) update the databases at both the publisher and the subscriber.
Indicate that the schema and data do not have to be sent when pushing a new
subscription.
2) update the publisher and push the new database structure along with
the data, with the new subscription (which may take a while over the
Internet)
3) Just use those stored procedures and I am done!
Would you agree? Looks like I'll take curtain number 3...
thank you very much for your time and patience,
bob.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23gb6XBHaEHA.1488@.TK2MSFTNGP09.phx.gbl...
> its hard to tell what you mean.
> If you make a schema change, and then create a publication and push it to
a
> subscription, yet the subscribers will get the schema change.
> If you attempt to make a schema change to an existing publication and
> subscription(s) you will be prevented from doing with an error message
like[vbcol=seagreen]
> 'This table is published for replication'.
> The only way to do this is using the above mentioned replication stored
> procedures.
> "Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
> news:ei7Tb4GaEHA.2488@.tk2msftngp13.phx.gbl...
> database
subscription[vbcol=seagreen]
> then
> to
> database
?[vbcol=seagreen]
like[vbcol=seagreen]
> to
structure?
>
|||yes, I would try option 3.
It seems to me that in the past #3 has bitten me in certain situation like
when you have filters, but I have been able to repro it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OLSBwMHaEHA.1448@.TK2MSFTNGP12.phx.gbl...
> The situation is this:
> I had to delete my publication in order to make a database structure
change
> at the publisher ( I did get the error you mention).
> I then re-created my publication and pushed a new subscription to my
> subscriber. But I did not "check" the box to send schema and data.
> So my subscriber indeed does not have the database change. This appears
to
> be my problem.
> In the future it would appear I have three options:
> 1) update the databases at both the publisher and the subscriber.
> Indicate that the schema and data do not have to be sent when pushing a
new[vbcol=seagreen]
> subscription.
> 2) update the publisher and push the new database structure along with
> the data, with the new subscription (which may take a while over the
> Internet)
> 3) Just use those stored procedures and I am done!
> Would you agree? Looks like I'll take curtain number 3...
> thank you very much for your time and patience,
> bob.
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23gb6XBHaEHA.1488@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
> a
> like
> subscription
refer[vbcol=seagreen]
message[vbcol=seagreen]
there)
> ?
> like
> structure?
>

Push Subscription - will it overwrite existing data first?

If I push a new subscription to an existing target database, and I choose or
check the box to include schema and data, will replication first delete or
empty tables at the target before pushing a new?
I did this, that is to push the subscription and checked the box to send
schema and data and killed the process mid-stream. At the target I found
certain tables to be empty. I am concluding that replication will first
empty or delete tables at the target.
Am I correct?
thanks,
bob
Bob,
on the article properties of a table, on the snapshot tab there is the
option to control this behaviour. By default if you look at the bcp files
per article, they will start with a drop table then a create table and after
that the rows will be inserted. It looks like you caught the process in
between these batches.
HTH,
Paul Ibison

Push replication

I need to pull the schema only with no data, then push the data back without
deletes on the publisher, then delete the client data. Next time these
deletes would not be pushed up. Only new data.
SQL Server 2005 June CTP and SQL Mobile 2005
"msmith" wrote:

> I need to pull the schema only with no data, then push the data back without
> deletes on the publisher, then delete the client data. Next time these
> deletes would not be pushed up. Only new data.

Pull Merge - Schema Change Version

I have an issue with one of my subscribers getting the dreaded "The
Publisher has been restored from a backup whose schema change version is
different from the Subscriber." error on one of the publications. I have a
total of 6 publications and the error only occurs one of them. After
receiving the error I removed all the subscriptions and recreated them with
scripts that I've been using on all my subscribers. The same occur still
occurs on the same subscription. I've run out of ideas on how to solve
the issue.
Any help would be appreciated!
Thanks
Tina
Tina,
this error message is one of the things which have been solved in sp3, if
you are using merge replication with @.enabled_for_internet set to true (see
http://support.microsoft.com/default...ticle%3D319961).
HTH,
Paul Ibison
|||Hi Paul,
Unfortunately I have that parameter set to false and I'm on SP3.
Any other ideas?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23yK$AcWLEHA.988@.TK2MSFTNGP11.phx.gbl...
> Tina,
> this error message is one of the things which have been solved in sp3, if
> you are using merge replication with @.enabled_for_internet set to true
(see
>
http://support.microsoft.com/default...ticle%3D319961).
> HTH,
> Paul Ibison
>