"use database" not working after "create database"

I didn't think you could use CREATE DATABASE within a transaction.
"ATS" <ATS@.discussions.microsoft.com> wrote in message
news:5F6A2B04-1923-4BDB-B036-FF1B7C445477@.microsoft.com...
> PRB: "use database" not working after "create database"
> Please help,
> I have the following query:
> set XACT_ABORT on
> begin transaction
> create database MY_DB
> use MY_DB
> commit transaction
> The "use" statement fails saying the database does not exists, but I get
no
> error on the "create". And when I go to the server, sure enough the DB is
not
> there, which it should not be if the TX rolled back. So what is wrong? If
the
> "create" is bad, why do I not see an error on it?I do not think you can use the use statement inside a transaction either
"Armando Prato" wrote:

> I didn't think you could use CREATE DATABASE within a transaction.
> "ATS" <ATS@.discussions.microsoft.com> wrote in message
> news:5F6A2B04-1923-4BDB-B036-FF1B7C445477@.microsoft.com...
> no
> not
> the
>
>

"Single Transaction" property of data source

I've got a monstrous beast of a report that has about 15 datasets. I've
figured the only way i can imporve performance is if I could somehow put some
of the parameterized data it pulls into a table that the other data queries
could reuse. (Is it possible to use a temp table? I would think not if the
queries are all in different sprocs). The only way I can see this happening
(from searching the newsgroups) is by making sure that everything is done as
a single transaction in the data source, but I can't find that property
anywhere?! Where do I set that property on the datasource, to do "single
transaction" pulls?
thanks a million if you can help me.
isamuMicrosoft MVPs are you there? I'd really really appreciate it if I could get
some help here. Thanks!
"isamu" wrote:
> I've got a monstrous beast of a report that has about 15 datasets. I've
> figured the only way i can imporve performance is if I could somehow put some
> of the parameterized data it pulls into a table that the other data queries
> could reuse. (Is it possible to use a temp table? I would think not if the
> queries are all in different sprocs). The only way I can see this happening
> (from searching the newsgroups) is by making sure that everything is done as
> a single transaction in the data source, but I can't find that property
> anywhere?! Where do I set that property on the datasource, to do "single
> transaction" pulls?
> thanks a million if you can help me.
> isamu|||bump.
"isamu" wrote:
> Microsoft MVPs are you there? I'd really really appreciate it if I could get
> some help here. Thanks!
> "isamu" wrote:
> > I've got a monstrous beast of a report that has about 15 datasets. I've
> > figured the only way i can imporve performance is if I could somehow put some
> > of the parameterized data it pulls into a table that the other data queries
> > could reuse. (Is it possible to use a temp table? I would think not if the
> > queries are all in different sprocs). The only way I can see this happening
> > (from searching the newsgroups) is by making sure that everything is done as
> > a single transaction in the data source, but I can't find that property
> > anywhere?! Where do I set that property on the datasource, to do "single
> > transaction" pulls?
> >
> > thanks a million if you can help me.
> >
> > isamu|||At the bottom of the Data Source dialog in report designer you will find a
checkbox "Use Single Transaction". This indicates the data sets that use
this data source should be executed in a single transaction. So, if all your
datasets use the same data source and you apply a single transaction - you
are almost there.
Although we don't guarantee a certain execution order across datasets of the
same data source (and it may change in future), you can assume at least for
RS2000 that the order of execution is the order of the <DataSet> elements as
defined in the RDL XML. Just try it.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:A4DF278F-A80A-42C0-A6D8-08B41D81F60F@.microsoft.com...
> bump.
> "isamu" wrote:
>> Microsoft MVPs are you there? I'd really really appreciate it if I could
>> get
>> some help here. Thanks!
>> "isamu" wrote:
>> > I've got a monstrous beast of a report that has about 15 datasets.
>> > I've
>> > figured the only way i can imporve performance is if I could somehow
>> > put some
>> > of the parameterized data it pulls into a table that the other data
>> > queries
>> > could reuse. (Is it possible to use a temp table? I would think not if
>> > the
>> > queries are all in different sprocs). The only way I can see this
>> > happening
>> > (from searching the newsgroups) is by making sure that everything is
>> > done as
>> > a single transaction in the data source, but I can't find that property
>> > anywhere?! Where do I set that property on the datasource, to do
>> > "single
>> > transaction" pulls?
>> >
>> > thanks a million if you can help me.
>> >
>> > isamu

"New transaction is not allowed because there are other threads running in the session." -

That is a SqlException I got at a...

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

Anyone an idea what THAT means? How do I cause it? How can I work around it?

I think you probably have an open connection somewhere. See if you can start the transaction after you close the connection.|||

I have about 25 open connections. Because that is - quess what - an ASP.NET application.

Once connection per page.

And every page has - one thread. No multi threading at all.

Plus it is legal for a thread to have multiple open connections.

|||

Have you enabled Multiple Active Resultsets (MARS)?

For connections with MARS enabled, manual transactions started via TSQL BEGIN TRAN must end (COMMIT or ROLLBACK) within the same scope that started the transaction

If your issue meets the the aboving conditions, you can try this work aroud:

To begin tansactions that are not required to be scoped under
MARS-enabled connections, use the client driver API constructs to begin and end transactions. If existing code relies on BEGIN TRAN operations without
corresponding COMMIT/ROLLBACK, MARS can be turned off until the application can be modified to use the client driver transaction APIs

|||Nope, no MARS. Actually that was (not is) old SQL Server 2000 code from .NET 1.1 that was traansported over. Worked flawlessly under .NET 1.1, and looks flawless to me still.

"model" system database transaction log

why would the transaction log of the model database have increased? this is a
template database what could be writing to it?
Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>

"model" system database transaction log

why would the transaction log of the model database have increased? this is
a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>

"model" system database transaction log

why would the transaction log of the model database have increased? this is a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
--
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication that
couldn't be send to the subscriber becouse of the above error.
Thanks
use sp_browsereplcmds to identiy this command. Then apply this command
on the subcriber.
Then update msreplication_subscripions with the xaxct_seqno for this
command. This will cause it to skip this command.

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication that
couldn't be send to the subscriber becouse of the above error.
Thanksuse sp_browsereplcmds to identiy this command. Then apply this command
on the subcriber.
Then update msreplication_subscripions with the xaxct_seqno for this
command. This will cause it to skip this command.

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution
command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication
that
couldn't be send to the subscriber becouse of the above error.
Thanks
Please enable logging as per this article:
http://support.microsoft.com/?id=312292 to get more details about the
failing command.
sp_browsereplcmds could also be used to find these details.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication that
couldn't be send to the subscriber becouse of the above error.
Thanks
use sp_browsereplcmds to identiy this command. Then apply this command
on the subcriber.
Then update msreplication_subscripions with the xaxct_seqno for this
command. This will cause it to skip this command.

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication that
couldn't be send to the subscriber becouse of the above error.
Thanksuse sp_browsereplcmds to identiy this command. Then apply this command
on the subcriber.
Then update msreplication_subscripions with the xaxct_seqno for this
command. This will cause it to skip this command.

"backup log {Database_Name} with no_log" issue

I issue the "backup log {Database_Name} with no_log" and
also "Dump transaction {Datebase_Name} with no_log". The
LDF file still got the same size. Any idea?It's usually best to leave the size of the .ldf file as is after backing up
a log because this saves sql server from having to go through the effort of
manually growing it whilst the database is in operation. This is because
having to grow the log file during operation slows down the performance of
SQL Server. This is why most production environments leave the .ldf file as
it is & simply truncate the "logical" log records from the file. There are
also other factors that come into play when considering recovery times as
well.
So, backing up a log & shrinking a .ldf file aren't two things you should
expect to happen automatically.
If you really want to shrink the .ldf file for some reason, there is a good
article on the topic in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"KL" <anonymous@.discussions.microsoft.com> wrote in message
news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> I issue the "backup log {Database_Name} with no_log" and
> also "Dump transaction {Datebase_Name} with no_log". The
> LDF file still got the same size. Any idea?
>|||Just to add to Greg's comments you should note that backingup a log file
will not shrink it. That will only happen with a DBCC SHRINKDATABASE or
SHRINKFILE command. It may not be able to actually shrink until the backup
is completed but that alone does not shrink the log file.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23LGr8z2xDHA.1616@.TK2MSFTNGP11.phx.gbl...
> It's usually best to leave the size of the .ldf file as is after backing
up
> a log because this saves sql server from having to go through the effort
of
> manually growing it whilst the database is in operation. This is because
> having to grow the log file during operation slows down the performance of
> SQL Server. This is why most production environments leave the .ldf file
as
> it is & simply truncate the "logical" log records from the file. There are
> also other factors that come into play when considering recovery times as
> well.
> So, backing up a log & shrinking a .ldf file aren't two things you should
> expect to happen automatically.
> If you really want to shrink the .ldf file for some reason, there is a
good
> article on the topic in SQL Server Books Online here:
> http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "KL" <anonymous@.discussions.microsoft.com> wrote in message
> news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> > I issue the "backup log {Database_Name} with no_log" and
> > also "Dump transaction {Datebase_Name} with no_log". The
> > LDF file still got the same size. Any idea?
> >
>