Database as a Parameter?!

I have 10 Databases with same query to extract same info from all databases.
One thing I can do is to create 10 different datasets to get my results or
join 10 queries in one big query.
But what I really would like to do is to create one dataset and pass a
database-name parameter so users can select the particular database from pull
down menu and view the results from that database. So basically it will be
running query for one database at a time. I tried to do it the same way you
create all parameters but of no use. I will appreciate any kind of help.
Example:
Select manufacturer, sales
From @.DatabaseName.dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
(DATEPART([Month], FDP.OrderDate) = @.ReportYear)
I can define and use @.ReportMonth and @.ReportYear but database name
parameter @.DatabaseName is not working.
Thanks in advance.Hi,
I'm using a similar method for maintaining many report servers on different
locations and each connecting to their own databases.
I use the below text as dataset source
= "Exec " & Parameters!LSQLServerName.Value & "." &
Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
Parameters!ParameterOne.Value
LSQLServerName parameter is the Linked SQL Server. So even on the same
location you can change your sql server machine.
Note that you will not need this parameter if your reporting database is on
the same server with your datasource. Otherwise you have to declare a linked
server on the server running reporting server databases.
DatabaseName parameter is the catalog name in the database server.
Then you pass the query (or sp with parameters)
Actually you send at the end a similar query:
" Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
I hope this helps.
Eralper Yilmaz
http://www.eralper.com
http://www.kodyaz.com/default.aspx
"GJ" wrote:
> I have 10 Databases with same query to extract same info from all databases.
> One thing I can do is to create 10 different datasets to get my results or
> join 10 queries in one big query.
> But what I really would like to do is to create one dataset and pass a
> database-name parameter so users can select the particular database from pull
> down menu and view the results from that database. So basically it will be
> running query for one database at a time. I tried to do it the same way you
> create all parameters but of no use. I will appreciate any kind of help.
> Example:
> Select manufacturer, sales
> From @.DatabaseName.dbo.product FDP
> Where
> (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> I can define and use @.ReportMonth and @.ReportYear but database name
> parameter @.DatabaseName is not working.
> Thanks in advance.|||Hi Eralper,
Thank you for the quick reply. But I think I am confused now as how or where
to write those statements.
The way I have been using reporting server is to create a dataset, create a
datasource in the dataset (or use a shared datasource), either write text
query or exec SP there. Then write the parameters used in the query in the
'Report Parameters' tab in VS.NET.
I am not sure if I followed it correctly, but if you are saying to create a
new DATASOURCE.. and write the source as:
= "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
In my situation, that is giving me errors.
On the other hand, running this " Exec
[SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
solve my problem as here we are giving DBName (Pubs) and that is static.
What I am trying is ... create a pull-don menu in the report and give the
user an option to select the particular database. Then the query is run and
results are returned for the query for the particular database selected.
I may not have understood your explanation but am still confused.
Thank you for your time and patience.
GJ
"eralper" wrote:
> Hi,
> I'm using a similar method for maintaining many report servers on different
> locations and each connecting to their own databases.
> I use the below text as dataset source
> = "Exec " & Parameters!LSQLServerName.Value & "." &
> Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> Parameters!ParameterOne.Value
> LSQLServerName parameter is the Linked SQL Server. So even on the same
> location you can change your sql server machine.
> Note that you will not need this parameter if your reporting database is on
> the same server with your datasource. Otherwise you have to declare a linked
> server on the server running reporting server databases.
> DatabaseName parameter is the catalog name in the database server.
> Then you pass the query (or sp with parameters)
> Actually you send at the end a similar query:
> " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> I hope this helps.
> Eralper Yilmaz
> http://www.eralper.com
> http://www.kodyaz.com/default.aspx
>
> "GJ" wrote:
> > I have 10 Databases with same query to extract same info from all databases.
> > One thing I can do is to create 10 different datasets to get my results or
> > join 10 queries in one big query.
> >
> > But what I really would like to do is to create one dataset and pass a
> > database-name parameter so users can select the particular database from pull
> > down menu and view the results from that database. So basically it will be
> > running query for one database at a time. I tried to do it the same way you
> > create all parameters but of no use. I will appreciate any kind of help.
> > Example:
> >
> > Select manufacturer, sales
> > From @.DatabaseName.dbo.product FDP
> > Where
> > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> >
> > I can define and use @.ReportMonth and @.ReportYear but database name
> > parameter @.DatabaseName is not working.
> >
> > Thanks in advance.|||He is using the technique of having an expression as the query source (not
the data source). If you use the generic query designer (not the graphical
one) you can put an expression in, just as he shows you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:0FC916A0-A499-4131-8F49-CC26CDEA2911@.microsoft.com...
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or
where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create
a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create
a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET
doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run
and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on
different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is
on
> > the same server with your datasource. Otherwise you have to declare a
linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all
databases.
> > > One thing I can do is to create 10 different datasets to get my
results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database
from pull
> > > down menu and view the results from that database. So basically it
will be
> > > running query for one database at a time. I tried to do it the same
way you
> > > create all parameters but of no use. I will appreciate any kind of
help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.|||Hi,
Your desired sp is below.
Select manufacturer, sales
From @.DatabaseName.dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
(DATEPART([Month], FDP.OrderDate) = @.ReportYear)
First of all, create the @.DatabaseName parameter in the report parameters
screen
Then go to Data tab, and write the following statement
Select manufacturer, sales From OneOfYourDatabases.dbo.product FDP
Replace OneOfYourDatabases with a database name. And execute the query.
Running this query will populate the fields list for this dataset so you can
use these fields in the Layout tab. (You can also add field names later on
the Field Tab if necessary)
The third step will be rearrainging the query. Replace it with :
= "Select manufacturer, sales
From " & Parameters!DatabaseName.value & ".dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportMonth.value & "')
and
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportYear.value & "')"
Lastly, click "Refresh" button (somehow this solved problems a few times),
then save.
If I'm not wrong, these four steps should work as you want.
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is on
> > the same server with your datasource. Otherwise you have to declare a linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all databases.
> > > One thing I can do is to create 10 different datasets to get my results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database from pull
> > > down menu and view the results from that database. So basically it will be
> > > running query for one database at a time. I tried to do it the same way you
> > > create all parameters but of no use. I will appreciate any kind of help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.

Database Access from Assembly

I have an assembly that I call to supply parameters to a report. Within the assembly, I make a connection to SQL server to supply the parameter.
The report runs fine from my local machine, but when I deploy to the report server, I get nothing back.
Is this a permission issue on the DLL, and if so, how do I get around it?
Thanks!
Dave
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Did you read this article and apply proper settings in the .config files of
report server?
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
You will also need to assert security permissions inside the custom
assembly. Every call that requires certain permissions (e.g. opening a
database connection) will fail on the server unless the required security
permissions for that call (documented in MSDN) have been asserted.
Example for opening a connection to a SQL Server:
...
SqlClientPermission permission = new
SqlClientPermission(PermissionState.Unrestricted);
try
{
permission.Assert(); // Assert security permission!
SqlConnection con = new SqlConnection("...");
con.Open();
...
}
You might also want to check out these links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_0so6.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAssert.asp
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%23C2jfaQXEHA.4000@.TK2MSFTNGP09.phx.gbl...
> I have an assembly that I call to supply parameters to a report. Within
the assembly, I make a connection to SQL server to supply the parameter.
> The report runs fine from my local machine, but when I deploy to the
report server, I get nothing back.
> Is this a permission issue on the DLL, and if so, how do I get around it?
> Thanks!
> Dave
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Data validation for datetime parameter in SSRS

Hi,

I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:

Public Function Validate( ByVal startdate As String) As Boolean
If IsDate(startdate) = True Then
Return True
Else
Return False
End If
End Function

And my report has a textbox which has the expression property set to;

=Code.Validate(Parameters!startdate.Value)

the textbox on the report has to display if the entered date is valid or not.

But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.

Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.

Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

The function below checks for the Start and End date ranges .

Function:

Function CheckDateParameters(StartDate as Date, EndDate as Date) as Integer
Dim msg as String
msg = ""
If (StartDate > EndDate) Then
msg="Start Date should not be later than End Date"
End If
If msg <> "" Then
MsgBox(msg, 16, "Report Validation")
Err.Raise(6,Report) 'Raise an overflow
End If
End Function

Steps:

1.) Go the Report Parameters and add a parameter with the datatype is string.

2.) Check the Hidden checkbox and Allow blank value ckeckbox.

3.) From Default Values choose Non-Queried radio button and then press the FX button and paste this code.

=CODE.CheckDateParameters(<parameterStartdate>.Value,<parameterEnddate>.Value)

Then press OK.

Hope this helps......|||

In the sample that you provided, if we enter an improper date as 02/31/2003 [mm/dd/yyyy], then the report execution fails and an error is thrown right away "error occured during the processing of report parameter". I was talking of handling such errors.

I feel that reporting services, initially validates the entered value matches the datatype of the parameter and then proceeds with execution of any VB code and finally renders the report.

When I said validation, I wanted a functionality similar to the client side validation in an asp page, where improper date formats like 12/31/235668 etc can be taken care of.

Data type of parameter passing to a stored procedure

Hi,
I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.

The exact error message that occurs is:

ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.

The error occurs in the following code line:
.parameters.Append cmd.CreateParameter ("@.EMedical", advarwchar, adParamInput)

I need to know what to write instead of advarwchar?
Thanks in advance1) memo and varchar isn't the same!
2) you should specify a size for varchar type!
.parameters.Append cmd.CreateParameter ("@.EMedical", advarwchar, adParamInput, 1000)
or other size instead of 1000

Data Type in Parameter Mapping for an Execute SQL Task

Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type.

The input parameter is define as @.Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message:

SSIS package "DCLoading.dtsx" starting.
Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging

update DCA_HFStaging set
[dbo].[Control_ID] = P0 where [Control_ID] is null
" failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Control_ID
Warning: 0x80019002 at DCLoading: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DCLoading.dtsx" finished: Failure.

Any help?this link might help: http://www.sqlis.com/default.aspx?58|||Thanks for the response. The link you posted is helpful, but does not include the example that I need. I want to set an Int32 variable as intput parameter, but I don't find any integer type in the parameter mapping. And, if I set the SSIS variable as string type, in the parameter mapping, I set the input parameter as varchar, it will return error said the parameter length can't be defined.

Data type conversion .. Varchar to GUID

Hi,

I have a parameter @.name varchar(50)

but the table has a field with datatype GUID

how I can convert varchar to guid before insertion...

Thanks

As long as the format of the guid is correct, it should be no problem:

create table test
(
columnName uniqueidentifier
)
go
declare @.guid varchar(50) --char(36) would be better
set @.guid = 'E5F61BB0-38BA-4116-841D-C7E5AAA137A2'

insert into test
select @.guid
go

select *
from test

columnName

E5F61BB0-38BA-4116-841D-C7E5AAA137A2

declare @.guid varchar(50) --char(36) would be better
--invalid guid format:
set @.guid = 'E5F61BB038BA4116841DC7E5AAA137A2'

--failure
insert into test
select @.guid

Msg 8169, Level 16, State 2, Line 6
Conversion failed when converting from a character string to uniqueidentifier.

Data Truncation in sp parameter

It seems that there is no error message if the length of the parameter
value exceeds parameter datalength
Create Procedure #test (@.data varchar(10))
as
Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
The result is
This is te
Why does SQL Server not raise any error?
MadhivananThat is what is called implicit conversion. Read BOL on this
Regards
R.D
--Knowledge gets doubled when shared
"Madhivanan" wrote:

> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||Hi
You just "selected" the data.SQL Server cuts off the result. Try doing
insertion and you will get the error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(2))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129800867.933647.36380@.g49g2000cwa.googlegroups.com...
> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||But when you give width 10, there is no error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(10))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
Madhivanan|||well,because @.data is already varchar(10) (sql server cuts off) and your
column had declared as varchar(10) , what's problem?
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129802989.571260.209840@.f14g2000cwb.googlegroups.com...
> But when you give width 10, there is no error
> Create Procedure #test (@.data varchar(10))
> as
> create table #t (col varchar(10))
> insert into #t Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
>
> Madhivanan
>|||> But when you give width 10, there is no error
That's right, because the parameter already made your data 10 characters
long, which fits just nicely in a VARCHAR(10) column.
You realize that not all data validation *has* to happen within the
database, right?|||Well
My question is why does SQL Server doesnt raise an error when the
length of value is nore than the parameter length?
I expect the same error that happend in this case
Declare @.t table(data varchar(10))
insert into @.t values('This is testing')
select data from @.t
Server: Msg 8152, Level 16, State 9, Line 2
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
Madhivanan