"Trunc log on checkpoint" & "select into/bulk copy" on sqlserver 2005

Hi

I need to set both these options on a staging database where fast bulk inserts (non-logged inserts) need to be done and I want the log to be truncated automatically on checkpoints. Simple & BULKLOGGED recovery models seem to be mutually exclusive. Somewhere in the documentation, I read the with Simple recovery model, non-logged bulk inserts can be turned on. I tried using sp_dboption also. Can't get it to work. Setting one seems to turn off the other.

Looking for some pointers here ...

Thanks,

-chiraj.

There will always be log whether the database is in simple/bulk logged.
Have you read this:
http://msdn2.microsoft.com/en-us/library/ms175987.aspx|||

Thanks for responding. I know there will always be a log. My point is why are these 2 options mutually exclusive. Having both options turned on would allow me to do continuous bulk inserts without having to worry about log filling up.

Thx,

-chiraj.

|||You can only have 1 mode set at anytime. So, just set it to Simple, do your dataload, then set it back to Full.sql

"Trunc log on checkpoint" & "select into/bulk copy" on sqlserver 200

Hi

I need to set both these options on a staging database where fast bulk inserts (non-logged inserts) need to be done and I want the log to be truncated automatically on checkpoints. Simple & BULKLOGGED recovery models seem to be mutually exclusive. Somewhere in the documentation, I read the with Simple recovery model, non-logged bulk inserts can be turned on. I tried using sp_dboption also. Can't get it to work. Setting one seems to turn off the other.

Looking for some pointers here ...

Thanks,

-chiraj.

There will always be log whether the database is in simple/bulk logged.
Have you read this:
http://msdn2.microsoft.com/en-us/library/ms175987.aspx|||

Thanks for responding. I know there will always be a log. My point is why are these 2 options mutually exclusive. Having both options turned on would allow me to do continuous bulk inserts without having to worry about log filling up.

Thx,

-chiraj.

|||You can only have 1 mode set at anytime. So, just set it to Simple, do your dataload, then set it back to Full.

"Trunc log on checkpoint" & "select into/bulk copy" on sqlserver 200

Hi

I need to set both these options on a staging database where fast bulk inserts (non-logged inserts) need to be done and I want the log to be truncated automatically on checkpoints. Simple & BULKLOGGED recovery models seem to be mutually exclusive. Somewhere in the documentation, I read the with Simple recovery model, non-logged bulk inserts can be turned on. I tried using sp_dboption also. Can't get it to work. Setting one seems to turn off the other.

Looking for some pointers here ...

Thanks,

-chiraj.

There will always be log whether the database is in simple/bulk logged.
Have you read this:
http://msdn2.microsoft.com/en-us/library/ms175987.aspx|||

Thanks for responding. I know there will always be a log. My point is why are these 2 options mutually exclusive. Having both options turned on would allow me to do continuous bulk inserts without having to worry about log filling up.

Thx,

-chiraj.

|||You can only have 1 mode set at anytime. So, just set it to Simple, do your dataload, then set it back to Full.

"select where in (select)" behaves strangely

I have noticed that selects that have a "where in (select...)" behave in a way I would not expect them to.

For example:

if object_id('x') is not null drop table x

if object_id('y') is not null drop table y

create table x(x1 int); create table y(y1 int)

insert x values (1); insert x values (2)

insert y values (1); insert y values (2)

select * from x where x1 in (select x1 from y)

/*

x1

--

1

2

*/

I would expect a syntax error here, since table y does not contain a column called x1. When it does contain a column called x1, the query behaves as I would expect it to:

if object_id('x') is not null drop table x

if object_id('y') is not null drop table y

create table x(x1 int); create table y(y1 int, x1 int)

insert x values (1); insert x values (2)

insert y values (1, 3); insert y values (2, 3)

select * from x where x1 in (select x1 from y)

--(0 row(s) affected)

Is this behavior by design?

Thanks,

Ron Rice

Yes, this is basically by design. There was a similar discussion here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1260288&SiteID=1

(search was corelated rowland)

"Select top (n) from <table>" missing from Management Studio

In sql 2000 one could right-click a table (under Enterprise Manager) and have it return the "top n" rows in the table. That feature seems to be missing from Management Studio. Am I correct or is it just hiding somewhere?

TIA,

Barkingdog

No, its definitely not there, you will have to tweak the SQL Statement by clicking the appropiate item in the iconbar.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

>>you will have to tweak the SQL Statement by clicking the appropiate item in the iconbar.
>>

What "item" are you referring to? The only way I can see to do this is open a new Query Editor pane and type the sql directly.

TIA,

Barkingdog

|||If you open a table you will have an icon with "SQL" written on it, you can change to the SQL Statement whcih was executed to have the data displayed, which is normally SELECT * From SomeTable and can change it the way you want to query the data.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

"Select All" option visible in Designer but gone on Report Manager

Hello,

I have several reports that use parameters that I have defined as multi-value. When I am designing the reports I see the "(Select All)" option listed first in each parm control that has the multi-value property set true. However, when the same reports are deployed to our server and viewed through Report Manager, the "(Select All)" option is missing. I need the "(Select All)" to be present.

I've seen some discussion of a service pack. Could this be the problem? What do we need to do to get our "(Select All)" back?

Thanks,

BCB

Hello BCB,

You'll need to apply SP2 to the server with Reporting Services on it. It seems that your local machine already has it applied.

Jarret

|||

Hello Jarret,

Thanks for your response. Can you tell me how to identify the service packs that have been applied to our SQL Server, if any? I checked the SQL Server properties and found a Version property that reads 9.00.2047.00. However, the number doesn't mean anything to me.

How can I tell if SP2 has been applied?

Thanks,

BCB

|||

BCB,

RTM - 2005.90.1399
SP1 - 2005.90.2047
SP2 - 2005.90.3042

This link will tell you which version number corresponds to which service pack:

http://support.microsoft.com/default.aspx/kb/321185

You can also go to the properties of the EXE file to get the version number. For example:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{InstanceID_of_RS}\Reporting Services\ReportServer\bin\ReportingServicesService.exe

Jarret

|||

I have the same scenarios (but it's not a problem)

I applied SP2 (AND SP2a) on my local client, so my VS2005 has <Select All> in the Designer, and local Preview

however, our SSRS servers haven't been applied SP2 but only SP1, so no <Select All> there

My local .exe version is 3050 (ie. anything > 3042 is SP2) since SP2a is .3050 :)

|||Thanks to all for the very helpful responses...

"Select all" as default for a multi-value parameter

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

...Does anybody know how to accomplish this?

(...or the reason why this is impossible?)

Stefano

StefanoEbitAET wrote:

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||

Thank you very much:

I had already tried this solution before -without success- and quitted...

after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).

Thanks again

Stefano

|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..

Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)

"Select all" as default for a multi-value parameter

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

...Does anybody know how to accomplish this?

(...or the reason why this is impossible?)

Stefano

StefanoEbitAET wrote:

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||

Thank you very much:

I had already tried this solution before -without success- and quitted...

after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).

Thanks again

Stefano

|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..

Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)

"Select all" as default for a multi-value parameter

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

...Does anybody know how to accomplish this?

(...or the reason why this is impossible?)

Stefano

StefanoEbitAET wrote:

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||

Thank you very much:

I had already tried this solution before -without success- and quitted...

after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).

Thanks again

Stefano

|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..

Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)

"Select all" as default for a multi-value parameter

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

...Does anybody know how to accomplish this?

(...or the reason why this is impossible?)

Stefano

StefanoEbitAET wrote:

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||

Thank you very much:

I had already tried this solution before -without success- and quitted...

after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).

Thanks again

Stefano

|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..

Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)