ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK AFTER n

Let's assume I issue the following command: ALTER DATABASE [dbname] SET
SINGLE_USER WITH ROLLBACK AFTER 60.
Can someone tell me if the follow assumptions are correct:
1) SQL Server will wait 60 seconds for all open transactions to commit or
rollback before putting the database in single user mode.
2) Any transactions not committed or rolled back within 60 seconds will
automatically be rolled back after 60 seconds.
3) Any transaction started after the above statement is issued but is not
committed or rolled back within the 60 seconds gets rolled back.
Thanks - Amos.
On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:

>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>SINGLE_USER WITH ROLLBACK AFTER 60.
>Can someone tell me if the follow assumptions are correct:
>1) SQL Server will wait 60 seconds for all open transactions to commit or
>rollback before putting the database in single user mode.
It will wait for _AT MOST_ 60 seconds for open connections to close. If
there are no open connections, the database is put in single user mode
immediately. If all other connections disconnect after 20 seconds, the
DB will be single user after 20 seconds.

>2) Any transactions not committed or rolled back within 60 seconds will
>automatically be rolled back after 60 seconds.
Yes. And all connections get disconnected.

>3) Any transaction started after the above statement is issued but is not
>committed or rolled back within the 60 seconds gets rolled back.
Only from connections that are already connected to the database
[dbname]. New connections are not accepted - you'll get this error:
Msg 952, Level 16, State 1, Line 1
Database 'dbname' is in transition. Try the statement later.
Hugo Kornelis, SQL Server MVP
|||Hugo,
That was very helpful - thank you.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:hu6jh2ls21ckm5hbv7i8et0a6irud11lcv@.4ax.com...
> On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>
> It will wait for _AT MOST_ 60 seconds for open connections to close. If
> there are no open connections, the database is put in single user mode
> immediately. If all other connections disconnect after 20 seconds, the
> DB will be single user after 20 seconds.
>
> Yes. And all connections get disconnected.
>
> Only from connections that are already connected to the database
> [dbname]. New connections are not accepted - you'll get this error:
> Msg 952, Level 16, State 1, Line 1
> Database 'dbname' is in transition. Try the statement later.
> --
> Hugo Kornelis, SQL Server MVP

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK AFTER n

Let's assume I issue the following command: ALTER DATABASE [dbname] SET
SINGLE_USER WITH ROLLBACK AFTER 60.
Can someone tell me if the follow assumptions are correct:
1) SQL Server will wait 60 seconds for all open transactions to commit or
rollback before putting the database in single user mode.
2) Any transactions not committed or rolled back within 60 seconds will
automatically be rolled back after 60 seconds.
3) Any transaction started after the above statement is issued but is not
committed or rolled back within the 60 seconds gets rolled back.
Thanks - Amos.On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>SINGLE_USER WITH ROLLBACK AFTER 60.
>Can someone tell me if the follow assumptions are correct:
>1) SQL Server will wait 60 seconds for all open transactions to commit or
>rollback before putting the database in single user mode.
It will wait for _AT MOST_ 60 seconds for open connections to close. If
there are no open connections, the database is put in single user mode
immediately. If all other connections disconnect after 20 seconds, the
DB will be single user after 20 seconds.
>2) Any transactions not committed or rolled back within 60 seconds will
>automatically be rolled back after 60 seconds.
Yes. And all connections get disconnected.
>3) Any transaction started after the above statement is issued but is not
>committed or rolled back within the 60 seconds gets rolled back.
Only from connections that are already connected to the database
[dbname]. New connections are not accepted - you'll get this error:
Msg 952, Level 16, State 1, Line 1
Database 'dbname' is in transition. Try the statement later.
--
Hugo Kornelis, SQL Server MVP|||Hugo,
That was very helpful - thank you.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:hu6jh2ls21ckm5hbv7i8et0a6irud11lcv@.4ax.com...
> On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>>SINGLE_USER WITH ROLLBACK AFTER 60.
>>Can someone tell me if the follow assumptions are correct:
>>1) SQL Server will wait 60 seconds for all open transactions to commit or
>>rollback before putting the database in single user mode.
> It will wait for _AT MOST_ 60 seconds for open connections to close. If
> there are no open connections, the database is put in single user mode
> immediately. If all other connections disconnect after 20 seconds, the
> DB will be single user after 20 seconds.
>>2) Any transactions not committed or rolled back within 60 seconds will
>>automatically be rolled back after 60 seconds.
> Yes. And all connections get disconnected.
>>3) Any transaction started after the above statement is issued but is not
>>committed or rolled back within the 60 seconds gets rolled back.
> Only from connections that are already connected to the database
> [dbname]. New connections are not accepted - you'll get this error:
> Msg 952, Level 16, State 1, Line 1
> Database 'dbname' is in transition. Try the statement later.
> --
> Hugo Kornelis, SQL Server MVP

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK AFTER n

Let's assume I issue the following command: ALTER DATABASE [dbname] SET
SINGLE_USER WITH ROLLBACK AFTER 60.
Can someone tell me if the follow assumptions are correct:
1) SQL Server will wait 60 seconds for all open transactions to commit or
rollback before putting the database in single user mode.
2) Any transactions not committed or rolled back within 60 seconds will
automatically be rolled back after 60 seconds.
3) Any transaction started after the above statement is issued but is not
committed or rolled back within the 60 seconds gets rolled back.
Thanks - Amos.On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:

>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>SINGLE_USER WITH ROLLBACK AFTER 60.
>Can someone tell me if the follow assumptions are correct:
>1) SQL Server will wait 60 seconds for all open transactions to commit or
>rollback before putting the database in single user mode.
It will wait for _AT MOST_ 60 seconds for open connections to close. If
there are no open connections, the database is put in single user mode
immediately. If all other connections disconnect after 20 seconds, the
DB will be single user after 20 seconds.

>2) Any transactions not committed or rolled back within 60 seconds will
>automatically be rolled back after 60 seconds.
Yes. And all connections get disconnected.

>3) Any transaction started after the above statement is issued but is not
>committed or rolled back within the 60 seconds gets rolled back.
Only from connections that are already connected to the database
[dbname]. New connections are not accepted - you'll get this error:
Msg 952, Level 16, State 1, Line 1
Database 'dbname' is in transition. Try the statement later.
Hugo Kornelis, SQL Server MVP|||Hugo,
That was very helpful - thank you.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:hu6jh2ls21ckm5hbv7i8et0a6irud11lcv@.
4ax.com...
> On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>
> It will wait for _AT MOST_ 60 seconds for open connections to close. If
> there are no open connections, the database is put in single user mode
> immediately. If all other connections disconnect after 20 seconds, the
> DB will be single user after 20 seconds.
>
> Yes. And all connections get disconnected.
>
> Only from connections that are already connected to the database
> [dbname]. New connections are not accepted - you'll get this error:
> Msg 952, Level 16, State 1, Line 1
> Database 'dbname' is in transition. Try the statement later.
> --
> Hugo Kornelis, SQL Server MVP

Allow null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).

Allow null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).

Allow database to assume primary role'

Can any one explain this..
Enable the 'Allow database to assume primary role'
Thanks
NOOR
Hi Noor,
This term is used in Logshipping.
Allow database to assume primary role :-
This lets the destination database become a new log shipping source database
and thus permits a possible future role reversal between the primary and
secondary servers. When you select this option, specify the secondary
server's transaction-log file share as the location for transaction-log
backups from the new source database.
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:O4anFj5eEHA.556@.tk2msftngp13.phx.gbl...
> Can any one explain this..
> Enable the 'Allow database to assume primary role'
> Thanks
> NOOR
>
|||Thanks Hari.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#WHrWKHfEHA.2812@.tk2msftngp13.phx.gbl...
> Hi Noor,
> This term is used in Logshipping.
> Allow database to assume primary role :-
> This lets the destination database become a new log shipping source
database
> and thus permits a possible future role reversal between the primary and
> secondary servers. When you select this option, specify the secondary
> server's transaction-log file share as the location for transaction-log
> backups from the new source database.
> Thanks
> Hari
> MCDBA
>
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:O4anFj5eEHA.556@.tk2msftngp13.phx.gbl...
>