Filter by "all" or actual value issue

I have a string parameter bound to a dataset that contains a "UNION Select
'(All)' " statement to provide the user a way to return all values instead of
just a single value.
I have other integer parameters that use this exact syntax for filtering
(ALL) and they work fine. The syntax looks like this and it doesn't work and
I have no idea why:
Expression: =Iif( Parameters!PropCode.Label = "All", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
Operation: = Value: =True
Maybe it has to do with the "(" character? Maybe it has to do with it being
a string? Please advise how I might get past this issue.Did you try:
Iif( Parameters!PropCode.Label = "(All)", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
'|||Yes. That was a typo. That is actually what the filter looks like. This
doesn't work.
"Jihong.Liu" wrote:
> Did you try:
> Iif( Parameters!PropCode.Label = "(All)", True,
> Fields!PropertyCode.Value = Parameters!PropCode.Label)
> '
>sql

FileShareProvider delivery extension credential encoding

I receive the following error when I try to provide a username and password
to the FileShareProvider delivery extension through the data-driven
subscription query:
ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:23::
NotificationPolling processing item 951ca046-a4a7-4657-be85-04512f3e8787
ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: e ERROR:
DBUnProtectData failed: System.FormatException: Invalid character in a
Base-64 string.
at System.Convert.FromBase64String(String s)
at
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(Strin
g data)
ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: i INFO: Current
user: secret
ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: e ERROR:
Error occured processing notification. Invalid character in a Base-64
string.
ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Data Driven
Notification for activation id c0eac265-fe46-4de5-9b13-4745be82bd25 was
saved.
ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Status: Invalid
character in a Base-64 string.
ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: Notification
951ca046-a4a7-4657-be85-04512f3e8787 completed. Success: True, Status:
Invalid character in a Base-64 string., DeliveryExtension: Report Server
FileShare, Report: Invoice, Attempt 0
ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:24::
NotificationPolling finished processing item
951ca046-a4a7-4657-be85-04512f3e8787
It seems that the extension is trying to decode the username and password
from the query, but can't since they aren't encoded. If I hard-code the
username and password values in the subscription itself, everything works
fine.
I tried to use
Microsoft.ReportingServices.Diagnostics.DataProtection.DBProtectData to
encode the username and password before storing them in the database, but am
getting the following error:
System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed..
What do I need to do to make the extension happy?
JayIt's a know issue, the user name and password are not supported by the file
share delivery extension, can you make them not data driven - i.e. constant?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jay Hackney" <jayh@.nospam.intellinet.com> wrote in message
news:#wIsGe2aEHA.3804@.TK2MSFTNGP10.phx.gbl...
> I receive the following error when I try to provide a username and
password
> to the FileShareProvider delivery extension through the data-driven
> subscription query:
> ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:23::
> NotificationPolling processing item 951ca046-a4a7-4657-be85-04512f3e8787
> ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: e ERROR:
> DBUnProtectData failed: System.FormatException: Invalid character in a
> Base-64 string.
> at System.Convert.FromBase64String(String s)
> at
>
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(Strin
> g data)
> ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: i INFO: Current
> user: secret
> ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: e ERROR:
> Error occured processing notification. Invalid character in a Base-64
> string.
> ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Data Driven
> Notification for activation id c0eac265-fe46-4de5-9b13-4745be82bd25 was
> saved.
> ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Status: Invalid
> character in a Base-64 string.
> ReportingServicesService!notification!5d8!07/14/2004-17:20:23::
Notification
> 951ca046-a4a7-4657-be85-04512f3e8787 completed. Success: True, Status:
> Invalid character in a Base-64 string., DeliveryExtension: Report Server
> FileShare, Report: Invoice, Attempt 0
> ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:24::
> NotificationPolling finished processing item
> 951ca046-a4a7-4657-be85-04512f3e8787
> It seems that the extension is trying to decode the username and password
> from the query, but can't since they aren't encoded. If I hard-code the
> username and password values in the subscription itself, everything works
> fine.
> I tried to use
> Microsoft.ReportingServices.Diagnostics.DataProtection.DBProtectData to
> encode the username and password before storing them in the database, but
am
> getting the following error:
> System.Security.SecurityException: Request for the permission of type
> System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> failed..
> What do I need to do to make the extension happy?
> Jay
>

Files associated with SQL server

Hi all,
Is there any any to find the files that are associated with a SQL server Instance ? Does SQL server provide any API for that ?

Hi,

Each instance installs in a different directory. If you look at you default installation directory [C:\Program Files\Microsoft SQL Server], you will find a dierctory for each instance and for each feature (e.g. SSIS, SSAS, etc). These directories are called MSSQL.x, the x separating out the features and instances. So e.g. You have installed 2 instances of SQL Server, one of SSAS and one of SSRS, you directory structure would look something like:

C:\Program Files\Microsoft SQL Server\90 - SQL Binaries (SQL 2005).

C:\Program Files\Microsoft SQL Server\MSSQL.1 - SQL Database Engine Instance 1

C:\Program Files\Microsoft SQL Server\MSSQL.2 - SQL SSAS

C:\Program Files\Microsoft SQL Server\MSSQL.3 - SQL SSRS

C:\Program Files\Microsoft SQL Server\MSSQL.4 - SQL Database Engine Instance 2

There is nothing stopping users from moving these database files around so to check that you have the correct path you could execute

Code Snippet

select * from sys.database_files

from the instance and that will confirm the location of the database files.

|||

You can run OSQL (sql 2000) or SQLCMD (2005) command to get files details against a instance.

select *from sysaltfiles -- SQL Server 2000

select *From sys.master_files -- SQL Server 2005

Eg. (for SQL 2005)

C:>sqlcmd -SYourInstanceName -UUserName -PPassword -dMaster -q"Select Physical_Name From Sys.Master_Files"

Read more about SQLCMD in BOL (OSQL if its sql 2000)

Madhu

|||

Are you looking for user databases files, if refer to the Madhu's method in this case.

For all associated binary files then they are installed under Program Files (default installation) or any directory you might have chosen when installing as Custom.

|||Hi all,
Thanks for the reply.
Sorry all. I think i didnt post the question correctly.I want to know the files associated with the SQL server programmatically. I want to know whether microsoft provides any API for that or is there any other method to do the same ?
|||

Ah, yes. Have a look at SQL Server Management Objects (SMO), and I quote from BOL:

"SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management."

|||Hi
Thanks !!!. I will look into it.
|||

The above post is correct - and you can use this link at any time for the default locations:

http://msdn2.microsoft.com/en-us/library/ms143547.aspx

The nice thing about the link is that it is kept up to date as things change.

- Buck Woody

Files associated with SQL server

Hi all,
Is there any any to find the files that are associated with a SQL server Instance ? Does SQL server provide any API for that ?

Hi,

Each instance installs in a different directory. If you look at you default installation directory [C:\Program Files\Microsoft SQL Server], you will find a dierctory for each instance and for each feature (e.g. SSIS, SSAS, etc). These directories are called MSSQL.x, the x separating out the features and instances. So e.g. You have installed 2 instances of SQL Server, one of SSAS and one of SSRS, you directory structure would look something like:

C:\Program Files\Microsoft SQL Server\90 - SQL Binaries (SQL 2005).

C:\Program Files\Microsoft SQL Server\MSSQL.1 - SQL Database Engine Instance 1

C:\Program Files\Microsoft SQL Server\MSSQL.2 - SQL SSAS

C:\Program Files\Microsoft SQL Server\MSSQL.3 - SQL SSRS

C:\Program Files\Microsoft SQL Server\MSSQL.4 - SQL Database Engine Instance 2

There is nothing stopping users from moving these database files around so to check that you have the correct path you could execute

Code Snippet

select * from sys.database_files

from the instance and that will confirm the location of the database files.

|||

You can run OSQL (sql 2000) or SQLCMD (2005) command to get files details against a instance.

select *from sysaltfiles -- SQL Server 2000

select *From sys.master_files -- SQL Server 2005

Eg. (for SQL 2005)

C:>sqlcmd -SYourInstanceName -UUserName -PPassword -dMaster -q"Select Physical_Name From Sys.Master_Files"

Read more about SQLCMD in BOL (OSQL if its sql 2000)

Madhu

|||

Are you looking for user databases files, if refer to the Madhu's method in this case.

For all associated binary files then they are installed under Program Files (default installation) or any directory you might have chosen when installing as Custom.

|||Hi all,
Thanks for the reply.
Sorry all. I think i didnt post the question correctly.I want to know the files associated with the SQL server programmatically. I want to know whether microsoft provides any API for that or is there any other method to do the same ?
|||

Ah, yes. Have a look at SQL Server Management Objects (SMO), and I quote from BOL:

"SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management."

|||Hi
Thanks !!!. I will look into it.
|||

The above post is correct - and you can use this link at any time for the default locations:

http://msdn2.microsoft.com/en-us/library/ms143547.aspx

The nice thing about the link is that it is kept up to date as things change.

- Buck Woodysql

Files associated with SQL server

Hi all,
Is there any any to find the files that are associated with a SQL server Instance ? Does SQL server provide any API for that ?

Hi,

Each instance installs in a different directory. If you look at you default installation directory [C:\Program Files\Microsoft SQL Server], you will find a dierctory for each instance and for each feature (e.g. SSIS, SSAS, etc). These directories are called MSSQL.x, the x separating out the features and instances. So e.g. You have installed 2 instances of SQL Server, one of SSAS and one of SSRS, you directory structure would look something like:

C:\Program Files\Microsoft SQL Server\90 - SQL Binaries (SQL 2005).

C:\Program Files\Microsoft SQL Server\MSSQL.1 - SQL Database Engine Instance 1

C:\Program Files\Microsoft SQL Server\MSSQL.2 - SQL SSAS

C:\Program Files\Microsoft SQL Server\MSSQL.3 - SQL SSRS

C:\Program Files\Microsoft SQL Server\MSSQL.4 - SQL Database Engine Instance 2

There is nothing stopping users from moving these database files around so to check that you have the correct path you could execute

Code Snippet

select * from sys.database_files

from the instance and that will confirm the location of the database files.

|||

You can run OSQL (sql 2000) or SQLCMD (2005) command to get files details against a instance.

select *from sysaltfiles -- SQL Server 2000

select *From sys.master_files -- SQL Server 2005

Eg. (for SQL 2005)

C:>sqlcmd -SYourInstanceName -UUserName -PPassword -dMaster -q"Select Physical_Name From Sys.Master_Files"

Read more about SQLCMD in BOL (OSQL if its sql 2000)

Madhu

|||

Are you looking for user databases files, if refer to the Madhu's method in this case.

For all associated binary files then they are installed under Program Files (default installation) or any directory you might have chosen when installing as Custom.

|||Hi all,
Thanks for the reply.
Sorry all. I think i didnt post the question correctly.I want to know the files associated with the SQL server programmatically. I want to know whether microsoft provides any API for that or is there any other method to do the same ?
|||

Ah, yes. Have a look at SQL Server Management Objects (SMO), and I quote from BOL:

"SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management."

|||Hi
Thanks !!!. I will look into it.
|||

The above post is correct - and you can use this link at any time for the default locations:

http://msdn2.microsoft.com/en-us/library/ms143547.aspx

The nice thing about the link is that it is kept up to date as things change.

- Buck Woody

File System Task - relative location ?

hi,

I am using "File System Task" to copy files from one pre-defined location to another.

Is there a way to provide a "relative path" for the corresponding connections? If yes, how to set it up for deployment?

If not, I guess the option will be to update the "connection string" property for the file connections using package configurations.

thanks,
NiteshAnother option is to store the "root" location in a folder (it can be populated from a configuration) and you can then use property expressions on your file connection manager connection strings to use that variable.

-Jamie