read_committed_snapshot take forever to turn off

I have turned on the database option read_committed_snapshot
alter database mydb set read_committed_snapshot on
this only took 1 sec.
But now I'm trying to turn it off.
alter database mydb set read_committed_snapshot off.
It already ran about 2 hours and it is still running.
Please help.
Thanks.
Pingx
Likely, it is an open transaction. The ALTER DATABASE topic in Books Online
says "In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no
active connections to the database except for the connection executing the
ALTER DATABASE command. ".
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:823B6EB2-3417-4B32-8C1F-DD4F42A1BA58@.microsoft.com...
> Perhaps it is a refresh problem? Or perhaps you have some open transaction
> which holds the transition?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>
|||You guys are right. I killed some sessions and the command finished.
Thanks
"Pingx" wrote:

> I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>

read_committed_snapshot take forever to turn off

I have turned on the database option read_committed_snapshot
alter database mydb set read_committed_snapshot on
this only took 1 sec.
But now I'm trying to turn it off.
alter database mydb set read_committed_snapshot off.
It already ran about 2 hours and it is still running.
Please help.
Thanks.
PingxPerhaps it is a refresh problem? Or perhaps you have some open transaction which holds the
transition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>|||Likely, it is an open transaction. The ALTER DATABASE topic in Books Online
says "In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no
active connections to the database except for the connection executing the
ALTER DATABASE command. ".
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:823B6EB2-3417-4B32-8C1F-DD4F42A1BA58@.microsoft.com...
> Perhaps it is a refresh problem? Or perhaps you have some open transaction
> which holds the transition?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>>I have turned on the database option read_committed_snapshot
>> alter database mydb set read_committed_snapshot on
>> this only took 1 sec.
>> But now I'm trying to turn it off.
>> alter database mydb set read_committed_snapshot off.
>> It already ran about 2 hours and it is still running.
>> Please help.
>> Thanks.
>> Pingx
>>
>>
>|||You guys are right. I killed some sessions and the command finished.
Thanks
"Pingx" wrote:
> I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>

RDA - multi table error for tracking

Hello,

I receive an error message when I try to Push data that the table is not tracked. However, when I try to turn on the tracking option it gives me an error that the table is a multi query table and therefore cannot be tracked. Here is my code to Pull the table.

string TPDAPull = string.Format("SELECT Table1.Field1,Table1.Field2, Table1.Field3, Table1.Field4, from Table1 Left Join Table2 on Table1.Field1 =Table2.Field1 WHERE Table2.Field12='{0}'", this.FindWorker(var));

rda.Pull("Table1", TPDAPull, rdaOleDbConnectString, RdaTrackOption.TrackingOn);

This table does not have a primary key. I was wondering what can I do in this situation? I do not want to Pull the whole table. Any suggestions would be greatly appreciated. I am working in VS 2005, NCF2.0, C#, WM5.0.

Thanks in advance!

What about using a subquery?|||

You could make a single table view and pull this instead (but the view must reference only ONE table).

A primary is required if using RDA tracking. See http://msdn2.microsoft.com/en-us/library/ms172971.aspx

"A primary key must be defined on the updatable recordset returned by the SELECT statement. "

|||

Thank you Erik and Graham for your replies. Erik I was wondering, can I use SubmitSql instead of Rda.push? The problem I am encountering is that I cannot change the schema of the SQL Server 2000 tables. Please let me know.

Thanks in advance!

|||Yes, of course you could use SubmitSQL, it can be used for any SQL statement that does not return rows (INSERT, EXEC sproc, UPDATE etc.)|||

Thanks Erik! I appreciate all your help.

RDA - multi table error for tracking

Hello,

I receive an error message when I try to Push data that the table is not tracked. However, when I try to turn on the tracking option it gives me an error that the table is a multi query table and therefore cannot be tracked. Here is my code to Pull the table.

string TPDAPull = string.Format("SELECT Table1.Field1,Table1.Field2, Table1.Field3, Table1.Field4, from Table1 Left Join Table2 on Table1.Field1 =Table2.Field1 WHERE Table2.Field12='{0}'", this.FindWorker(var));

rda.Pull("Table1", TPDAPull, rdaOleDbConnectString, RdaTrackOption.TrackingOn);

This table does not have a primary key. I was wondering what can I do in this situation? I do not want to Pull the whole table. Any suggestions would be greatly appreciated. I am working in VS 2005, NCF2.0, C#, WM5.0.

Thanks in advance!

What about using a subquery?|||

You could make a single table view and pull this instead (but the view must reference only ONE table).

A primary is required if using RDA tracking. See http://msdn2.microsoft.com/en-us/library/ms172971.aspx

"A primary key must be defined on the updatable recordset returned by the SELECT statement. "

|||

Thank you Erik and Graham for your replies. Erik I was wondering, can I use SubmitSql instead of Rda.push? The problem I am encountering is that I cannot change the schema of the SQL Server 2000 tables. Please let me know.

Thanks in advance!

|||Yes, of course you could use SubmitSQL, it can be used for any SQL statement that does not return rows (INSERT, EXEC sproc, UPDATE etc.)|||

Thanks Erik! I appreciate all your help.