Multi Parameter with Boolean Field

Hello,

Can anyone say me how i can make a Report with Parameter Boolean field,and as Default Value true and False. ( Both ).

With Multivalue and in the Query = Field in (@.BoolPara) have i a Error in the Query.

Thanks

Hi,

It is not possible to have a Both clause by default. Although, here is a workarround:

In case of using a Bool report parameter, I use an integer report parameter and set its available values as follow:
Both -1
True 1
False 0

Defaultvalues: 1

In the were clause set the format as below:
SELECT *
FROM SampleBoolTable
WHERE (BoolCol = ABS(@.BoolParameter) OR @.BoolParameter = -1)

So when you now render your report, you will see the options True, False and Both. When selecting Both, a dummy filter is used so everything is shown.

NOTE: The ABS function is necessary to avoid the comparison between -1 and a BIT column.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

multi excel files to sql server

I am following the instruction in
http://www.sqldts.com/default.aspx?6,103,246,0,1 to loop thru a directory an
d
get multi excel files to sql.
excel files: same layout, in the same folder and going to the same sql table
I am using the transform data task for excel -> sql step. the problem is
that the package can only process the first file (the initial that I use to
setup the excel connection). The package dies on the 2nd file. I can see the
2nd file being pickup by the loop because the correct file name is in the
excel connection property and the global variable is updated to reference th
e
2nd file as well.
When I goto the transform data task properties and preview source, I get an
error. Error Source: Microsoft JET Database Engine.
Error Description: 'xxx$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long
the file names are not too long. I changed it to be a.xls, b.xls, c.xls etc.
so what's wrong?a bit more info:
I noticed that there were always two selections (2 xxx$) in the transform
data task properties -> table/view dropdown. the one selected by default is
always wrong. I have to select the other one and re-do the transformation
then the whole thing would work again... but only once on one file..... I
would error back here again on the 2nd file...
How can I make the package see the correct file in the table/view dropdown'
"christy" wrote:

> I am following the instruction in
> http://www.sqldts.com/default.aspx?6,103,246,0,1 to loop thru a directory
and
> get multi excel files to sql.
> excel files: same layout, in the same folder and going to the same sql tab
le
> I am using the transform data task for excel -> sql step. the problem is
> that the package can only process the first file (the initial that I use t
o
> setup the excel connection). The package dies on the 2nd file. I can see t
he
> 2nd file being pickup by the loop because the correct file name is in the
> excel connection property and the global variable is updated to reference
the
> 2nd file as well.
> When I goto the transform data task properties and preview source, I get a
n
> error. Error Source: Microsoft JET Database Engine.
> Error Description: 'xxx$' is not a valid name. Make sure that it does not
> include invalid characters or punctuation and that it is not too long
> the file names are not too long. I changed it to be a.xls, b.xls, c.xls et
c.
> so what's wrong?

MSX/TSX question between SQL 2000 and 2005

Hi,
I have a SQL Server 2005 as a master server, I want to make a SQL Server
2000 default instance a target server, but it failed and the error message i
s
'The master server version 9.00.2047 is not compatible with the target serve
r
version 8.00.818'. Can we have a SQL 2000 server as a target server when we
have a SQL 2005 as a master server? If so, how?
Thanks for your help.So far i have not seen any documentation on mingling 2k and 2k5 MSX and
TSX services. I believe you would have to have both instances running
on the MSX server and have 2 complete MSX db's running.
Hong Wang wrote:
> Hi,
> I have a SQL Server 2005 as a master server, I want to make a SQL Server
> 2000 default instance a target server, but it failed and the error message
is
> 'The master server version 9.00.2047 is not compatible with the target ser
ver
> version 8.00.818'. Can we have a SQL 2000 server as a target server when w
e
> have a SQL 2005 as a master server? If so, how?
> Thanks for your help.

MSX/TSX question between SQL 2000 and 2005

Hi,
I have a SQL Server 2005 as a master server, I want to make a SQL Server
2000 default instance a target server, but it failed and the error message is
'The master server version 9.00.2047 is not compatible with the target server
version 8.00.818'. Can we have a SQL 2000 server as a target server when we
have a SQL 2005 as a master server? If so, how?
Thanks for your help.So far i have not seen any documentation on mingling 2k and 2k5 MSX and
TSX services. I believe you would have to have both instances running
on the MSX server and have 2 complete MSX db's running.
Hong Wang wrote:
> Hi,
> I have a SQL Server 2005 as a master server, I want to make a SQL Server
> 2000 default instance a target server, but it failed and the error message is
> 'The master server version 9.00.2047 is not compatible with the target server
> version 8.00.818'. Can we have a SQL 2000 server as a target server when we
> have a SQL 2005 as a master server? If so, how?
> Thanks for your help.

MSSQL.1, .2, .3....?

For some reason Sql 2005, by default, creates different folder paths (such as MSSQL.1, MSSQL,2, etc) for different services.

1. Is there a utiltiy that can tell me what service is stored in what folder?

2. I noticed that Integraiton Servcies does not seem to have a seperate ("MSSQL.x") folder. Why?

Any help much appreciated,

Barkingdog

"Is there a utiltiy that can tell me what service is stored in what folder?"

Yes, SQL Server Configuration Manager. Select SQL Server 2005 Services after you start the tool. Right-click on a particular service and select properties. Select the Service tab and the path is in the Binary Path attribute.

" I noticed that Integraiton Servcies does not seem to have a seperate ("MSSQL.x") folder. Why?"

My guess is that the sql directory structure is this way because SSIS does not require several other OS files as do the other services. But thats a guess. It is what it is :)

HTH,

Derek

MSSQL.1 directory

Hi,
I just did a fresh install of SQL 2005, default instance, to a new server. I
specified the root of D:\ as the target for the install, however I ended up
with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
MSSQL.1 the norm in 2k5?
Thanks in advance.
That's the norm with 2005. I think they like seeing their name in print. I
don't know the intention of MSSQL.1.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
> Hi,
> I just did a fresh install of SQL 2005, default instance, to a new server.
I
> specified the root of D:\ as the target for the install, however I ended
up
> with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
> MSSQL.1 the norm in 2k5?
> Thanks in advance.
|||New naming convention. Install another SQL component (Analysis services,
Reporting Services) and you will see a MSSQL.2 folder. Upgrades and service
packs will also create new folders for newer versions, at least for the
binaries. It certainly makes rollbacks easier.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
> Hi,
> I just did a fresh install of SQL 2005, default instance, to a new server.
> I
> specified the root of D:\ as the target for the install, however I ended
> up
> with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
> MSSQL.1 the norm in 2k5?
> Thanks in advance.
|||Cool, thank you both.
"Geoff N. Hiten" wrote:

> New naming convention. Install another SQL component (Analysis services,
> Reporting Services) and you will see a MSSQL.2 folder. Upgrades and service
> packs will also create new folders for newer versions, at least for the
> binaries. It certainly makes rollbacks easier.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
>
>
|||if you want to know what MSSQL.1 corresponds to you can go into :
HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCE NAMES\SQL

MSSQL.1 directory

Hi,
I just did a fresh install of SQL 2005, default instance, to a new server. I
specified the root of D:\ as the target for the install, however I ended up
with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
MSSQL.1 the norm in 2k5?
Thanks in advance.That's the norm with 2005. I think they like seeing their name in print. I
don't know the intention of MSSQL.1.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
> Hi,
> I just did a fresh install of SQL 2005, default instance, to a new server.
I
> specified the root of D:\ as the target for the install, however I ended
up
> with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
> MSSQL.1 the norm in 2k5?
> Thanks in advance.|||New naming convention. Install another SQL component (Analysis services,
Reporting Services) and you will see a MSSQL.2 folder. Upgrades and service
packs will also create new folders for newer versions, at least for the
binaries. It certainly makes rollbacks easier.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
> Hi,
> I just did a fresh install of SQL 2005, default instance, to a new server.
> I
> specified the root of D:\ as the target for the install, however I ended
> up
> with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
> MSSQL.1 the norm in 2k5?
> Thanks in advance.|||Cool, thank you both.
"Geoff N. Hiten" wrote:

> New naming convention. Install another SQL component (Analysis services,
> Reporting Services) and you will see a MSSQL.2 folder. Upgrades and servi
ce
> packs will also create new folders for newer versions, at least for the
> binaries. It certainly makes rollbacks easier.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
>
>|||if you want to know what MSSQL.1 corresponds to you can go into :
HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCE NAMES\SQL

MSSQL.1 directory

Hi,
I just did a fresh install of SQL 2005, default instance, to a new server. I
specified the root of D:\ as the target for the install, however I ended up
with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
MSSQL.1 the norm in 2k5?
Thanks in advance.That's the norm with 2005. I think they like seeing their name in print. I
don't know the intention of MSSQL.1.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
> Hi,
> I just did a fresh install of SQL 2005, default instance, to a new server.
I
> specified the root of D:\ as the target for the install, however I ended
up
> with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
> MSSQL.1 the norm in 2k5?
> Thanks in advance.|||New naming convention. Install another SQL component (Analysis services,
Reporting Services) and you will see a MSSQL.2 folder. Upgrades and service
packs will also create new folders for newer versions, at least for the
binaries. It certainly makes rollbacks easier.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
> Hi,
> I just did a fresh install of SQL 2005, default instance, to a new server.
> I
> specified the root of D:\ as the target for the install, however I ended
> up
> with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
> MSSQL.1 the norm in 2k5?
> Thanks in advance.|||Cool, thank you both.
"Geoff N. Hiten" wrote:
> New naming convention. Install another SQL component (Analysis services,
> Reporting Services) and you will see a MSSQL.2 folder. Upgrades and service
> packs will also create new folders for newer versions, at least for the
> binaries. It certainly makes rollbacks easier.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:A30BA7DB-EB9B-45F2-B37D-E1949D0FE0B4@.microsoft.com...
> > Hi,
> > I just did a fresh install of SQL 2005, default instance, to a new server.
> > I
> > specified the root of D:\ as the target for the install, however I ended
> > up
> > with D:\MSSQL.1\MSSQL as my physical directory. Did I miss something or is
> > MSSQL.1 the norm in 2k5?
> >
> > Thanks in advance.
>
>|||if you want to know what MSSQL.1 corresponds to you can go into :
HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCE NAMES\SQL