DataType Problem

Hi,

I want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?

Thanks in Advance.

You can use byte array (byte[]) for BLOB data type (text/ntext/image) in SQL Server:)

DataTime as parameters: Help Needed.

Hello all,
I need to generate a report based on dates(from and to). Is there
anyway I can include a dropdown of datetime in the parameters of the RS
interface.
for example like from mm/dd/yyyy to mm/dd/yyyy.
I have RS 2003 EE with SP1 and have no plans of installing SP2.
Any help will be appreciated.
Thanks a lot
RaviRavi,
You need to create a list of dates as a DS, and then add two params to the
report (well, just add them to the DS for the report itself) and configure
the Params to be query selections from the Dates DS.
Using Adventurewroks DB, the following suffices for selecting particular
records of SalesOrderID, ModifiedDate columns from the SalesOrderDetail
table, by date.
DataSets:
DSDates:
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
ORDER BY ModifiedDate
DS1:
SELECT SalesOrderID, ModifiedDate
FROM dbo.SalesOrderDetail
WHERE (ModifiedDate > @.STARTDATE AND ModifiedDate < @.ENDDATE)
Form:
Table with DS1 as it's source.
Remember to edit the params in Report-Report Parameters, for both @.STARTDATE
AND @.ENDDATE, to be;
From Query; Dataset:DSDates;ValueField:ModifiedDate;LabelField:dateselect.
If you leave out the dateselect column from the first DS then the dropdown
will default to 00:00:00 time, and looks awful.
Hope this helps,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||Ravi,
In addiditon to my last reply, if you want to make 'intelligent' parameters,
then your end date should be greater than your start date.
To achieve this, complete tasks as per my previous reply and add the
following;
Create a further DS called DSDates2.
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
HAVING (ModifiedDate > @.STARTDATE)
ORDER BY ModifiedDate
and change the source for the @.ENDDATE parameter to point to DSDates2.
You will then find the End Date drop down is disabled until the Start Date
is selected.
If you wish, you can set a default for the Start Date as being the first
date found in the table (Use Top 1 selected from DSDates) for extra
useability.
Hope this assists further,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||thanks logicalman,
will try that first thing on monday.
Ravi

Datasets that rely on parameters

Hi,
I'm trying to create a report under the following conditions:
1. I have a central database that has stored procedures that require @.db as
the parameter so you can specify where the data is coming from.
2. The report I have only wants data from a particular record, so I created
a second dataset to use in a dropdown list.
3.That second dataset uses a stored proc that requires a parameters @.id and
@.db
The problem is that the columns returned from the stored proc has the @.id
are not recognized when I try to specify them as the "value field" and the
"label field" in the Report Parameters Dialog box.
I suspect this is because it needs to know what the @.db is.
1. How do I get the second stored proc to be recognized by the Report
Parameters Dialog box?
2. How do I pass the @.db to the second stored proc without user intervention?
thanks,
-TrishIf I understand you correctly, what you are trying to do is called cascading
parameters. Search books on line for that and see if it answers you
question.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:FCF8157D-2E15-4FF7-B70D-DFB3955C9FE8@.microsoft.com...
> Hi,
> I'm trying to create a report under the following conditions:
> 1. I have a central database that has stored procedures that require @.db
> as
> the parameter so you can specify where the data is coming from.
> 2. The report I have only wants data from a particular record, so I
> created
> a second dataset to use in a dropdown list.
> 3.That second dataset uses a stored proc that requires a parameters @.id
> and
> @.db
> The problem is that the columns returned from the stored proc has the @.id
> are not recognized when I try to specify them as the "value field" and the
> "label field" in the Report Parameters Dialog box.
> I suspect this is because it needs to know what the @.db is.
> 1. How do I get the second stored proc to be recognized by the Report
> Parameters Dialog box?
> 2. How do I pass the @.db to the second stored proc without user
> intervention?
> thanks,
> -Trish

Datasets in custom code

Hi guys,

is it possible to access my report's datasets from within custom code or pass them as parameters to a custom assembly? I want the dataset itself, not just fields, so i can programmatically look through it for data.

Thanks!

sluggy

This is not directly supported.

There are however some alternative approaches:

* add a list reportitem to the report and inside the list make a call to your custom code/custom assembly function and pass in the fields as parameter values. This will call your function for every row of data of the dataset bound to the list.

* or a more complex but also more powerful option is to use the new CustomReportItem feature in RS 2005 and implement a custom processing control which can examine the processed DataValues inside the CustomReportItem. A starting point with a sample is available here: http://blogs.msdn.com/chrishays/archive/2005/10/04/CustomReportItemSample.aspx

-- Robert

|||Thanks Robert, i will check that solution out, although it is probably overkill for the particular scenario i was dealing with

datasets and parameters

I am trying to put up together a report with several parameters. The first
parameter is independent, the second one, though depends of this first one.
Is there a way to link them somehow, so that when i pick my first parameter,
the second dataset I'm using for my second parameter will be populated based
on the value of my first parameter.
Simple example if it's still not clear: i have a table with 3 columns, 10
rows. My 1st param is the 1st col, my 2nd param is the 2nd col. Initially the
report brings back all 10 rows. There are 2 distinct values in the 1st col,
and 3 distinct values in the 2nd col - one distinct value on the 1st col is
associated with 2 values in 2nd col, one with the other remaining.
I NEED TO HAVE MY SECOND PARAM POPULATED WITH 2 values WHEN I PICK one value
of the first parameter and 1 value when I pick the other one.
Thank you,
kowalskylook up Cascading Parameters in books online or searching the same in this
newsgroup.
"kowalsky" wrote:
> I am trying to put up together a report with several parameters. The first
> parameter is independent, the second one, though depends of this first one.
> Is there a way to link them somehow, so that when i pick my first parameter,
> the second dataset I'm using for my second parameter will be populated based
> on the value of my first parameter.
> Simple example if it's still not clear: i have a table with 3 columns, 10
> rows. My 1st param is the 1st col, my 2nd param is the 2nd col. Initially the
> report brings back all 10 rows. There are 2 distinct values in the 1st col,
> and 3 distinct values in the 2nd col - one distinct value on the 1st col is
> associated with 2 values in 2nd col, one with the other remaining.
> I NEED TO HAVE MY SECOND PARAM POPULATED WITH 2 values WHEN I PICK one value
> of the first parameter and 1 value when I pick the other one.
> Thank you,
> kowalsky

Dataset Query using Parameters

OK. I've got a tough one here. I am attempting to create a parameter .aspx
page that will pass in start date, end date and multiple storeIDs to the
report. A section of my query in the report dataset looks like this:
WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
@.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
The problem is at the end with the @.paramStore. It works if you pass it
just one StoreID. The syntax becomes ...StoreID IN ('1')
When you try to pass it more than one storeid, it blows up. The syntax
becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
convert '1,2' to datatype int. Is there a way to take these leading and
trailing apostrophes off or can you think of a workaround? Thanks.You have to do a dynamically generated SQL statement, like this:
= "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
@.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
(" & @.paramStore & "))"
It builds the SQL statement on the fly, so you won't be able to use the
query designer after this. I may not have put the quotes in properly, but I
hope you get the idea.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> OK. I've got a tough one here. I am attempting to create a parameter
> .aspx
> page that will pass in start date, end date and multiple storeIDs to the
> report. A section of my query in the report dataset looks like this:
> WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> The problem is at the end with the @.paramStore. It works if you pass it
> just one StoreID. The syntax becomes ...StoreID IN ('1')
> When you try to pass it more than one storeid, it blows up. The syntax
> becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> convert '1,2' to datatype int. Is there a way to take these leading and
> trailing apostrophes off or can you think of a workaround? Thanks.|||Thanks Jeff but I can't seem to get this to work. It changes my quotation
marks to brackets and comes up with error "Identifier expected."
"Jeff A. Stucker" wrote:
> You have to do a dynamically generated SQL statement, like this:
> = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
> (" & @.paramStore & "))"
> It builds the SQL statement on the fly, so you won't be able to use the
> query designer after this. I may not have put the quotes in properly, but I
> hope you get the idea.
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > OK. I've got a tough one here. I am attempting to create a parameter
> > .aspx
> > page that will pass in start date, end date and multiple storeIDs to the
> > report. A section of my query in the report dataset looks like this:
> >
> > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> >
> > The problem is at the end with the @.paramStore. It works if you pass it
> > just one StoreID. The syntax becomes ...StoreID IN ('1')
> >
> > When you try to pass it more than one storeid, it blows up. The syntax
> > becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> > convert '1,2' to datatype int. Is there a way to take these leading and
> > trailing apostrophes off or can you think of a workaround? Thanks.
>
>|||My advice in this situation is to back up and make sure you can create the
appropriate string.
Create a report that has the report parameters and a textbox and nothing
else. In the textbox put in the expression. Now you should be able to copy
and paste the result into query analyzer and it should work. Sometimes just
seeing the result will let you know what you are doing wrong.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:7236F404-0AB1-44C6-97A1-A601A1E2B738@.microsoft.com...
> Thanks Jeff but I can't seem to get this to work. It changes my quotation
> marks to brackets and comes up with error "Identifier expected."
> "Jeff A. Stucker" wrote:
> > You have to do a dynamically generated SQL statement, like this:
> >
> > = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> > @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID
IN
> > (" & @.paramStore & "))"
> >
> > It builds the SQL statement on the fly, so you won't be able to use the
> > query designer after this. I may not have put the quotes in properly,
but I
> > hope you get the idea.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> > news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > > OK. I've got a tough one here. I am attempting to create a parameter
> > > .aspx
> > > page that will pass in start date, end date and multiple storeIDs to
the
> > > report. A section of my query in the report dataset looks like this:
> > >
> > > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> > >
> > > The problem is at the end with the @.paramStore. It works if you pass
it
> > > just one StoreID. The syntax becomes ...StoreID IN ('1')
> > >
> > > When you try to pass it more than one storeid, it blows up. The
syntax
> > > becomes ...StoreID IN ('1,2') and an error comes up saying that it
cannot
> > > convert '1,2' to datatype int. Is there a way to take these leading
and
> > > trailing apostrophes off or can you think of a workaround? Thanks.
> >
> >
> >

Dataset parameters question.

I have a report, which contains student details and the courses to
which the student is registered.
I have 2 datasets, one which retrieves the student data, and another
paramaterized report which retrieves all courses for this student.
What I need to do is set this parameter to be the student_id of the
current student.
e.g. I want the report to be displayed as:
Student Details
Student ID: S1000
Name: AN Other Address: 1 22nd Street
Courses:
Math
Chemistry
Physics
Spanish
I can select the correct dataset and field in the textbox for course
name, but the report keeps asking me to enter a student id. I want it
to use the student id of the current student.
Does anyone know how I go about this?
Appreciate any help.Donâ't you use a student id parameter to filter the student? This one should
be used to filter courses.
If youâ're not filtering the students, you may have more than one student. In
this case you can use a single dataset that joins student and courses, and
group by students.
"DJ" wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.
>|||IF USING A SUBREPORT
Your Student data is in your main report, and your Course data (including
StudentID) is in your subreport. Your subreport is placed inside the list
(or table or whatever) that iterates on the student data.
In the main report, right-click on the subreport and select Properties.
Then go to the Parameters tab and map the subreport parameters to the
appropriate fields.
IF NOT USING A SUBREPORT
You might be able to do this without a subreport. Just join the course
table to the student table in your dataset. Put everything inside a table
(or nested lists if it's a freeform report). You can put rectangles and
lists inside table cells, which is cool. Map everything to the same dataset.
Then set outer grouping levels at the student level, and inner grouping or
details at the course level.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DJ" <superdj@.hotmail.com> wrote in message
news:72abb98.0501070456.d13dd9d@.posting.google.com...
>I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||I have tried this using a parameterized sub report, and set the
parameter to be the student id. The dataset that retrieves the student
data is simply 'select * from students' so this should bring back all
the students and the sub report should bring back all the courses for
these students.
But still it asks me to enter a student id!
DJ wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||In your parent report parameters, check for a student_id field. You might
have specified one earlier that needs to be deleted. The data source for
your subreport should be something like "select * from courses where
student_id = @.student_id". Then you can link the parameters in the parent
and child reports.
On the other hand, you could do the whole thing in a single report, with a
data source like "select * from students inner join courses on
students.student_id = courses.student_id". Then you put the fields in a
table with student info in the header rows, and group info in the detail
rows.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<superdj@.hotmail.com> wrote in message
news:1105349201.298466.198340@.c13g2000cwb.googlegroups.com...
>I have tried this using a parameterized sub report, and set the
> parameter to be the student id. The dataset that retrieves the student
> data is simply 'select * from students' so this should bring back all
> the students and the sub report should bring back all the courses for
> these students.
> But still it asks me to enter a student id!
>
> DJ wrote:
>> I have a report, which contains student details and the courses to
>> which the student is registered.
>> I have 2 datasets, one which retrieves the student data, and another
>> paramaterized report which retrieves all courses for this student.
>> What I need to do is set this parameter to be the student_id of the
>> current student.
>> e.g. I want the report to be displayed as:
>> Student Details
>> Student ID: S1000
>> Name: AN Other Address: 1 22nd Street
>> Courses:
>> Math
>> Chemistry
>> Physics
>> Spanish
>> I can select the correct dataset and field in the textbox for course
>> name, but the report keeps asking me to enter a student id. I want it
>> to use the student id of the current student.
>> Does anyone know how I go about this?
>> Appreciate any help.
>

dataset parameters eliminated

I just discovered that whenever I make a change to a dataset, all the dataset parameters get automatically toasted off the face of the planet. The report parameters remain intact, but their mappings in the Parameters tab of the Dataset dialog are deleted.

Is this a bug? Is there a preventative measure I could take? I'm looking at a coupla tedious hours work to put 'em all back in to all my reports, and I don't want this to happen again.

I'ma go find the espresso machine.My specific case is this :

All of my reports have a db parameter that specifies the database name, plus one or more parameters that are passed into a stored procedure residing on that database. Therefore, an average parameter list & mappings would look like this :

@.db =Parameters!db.Value @.Ledger_Txn =Parameters!Ledger_Txn.Value @.Fiscal_Period =Parameters!Period.Value

And then the sp call looks something like this :

declare @.sp nvarchar(255)
set @.sp = @.db + '.dbo.fancy_financial_report_sp'
exec @.sp @.Ledger_Txn, @.Fiscal_Period

I had to modify my sp calls to handle the case where the database name has a period (.) in it, so it would look like this :

set @.sp = '[' + @.db + '].dbo.fancy_financial_report_sp'

Every report I made this change to, however, had all of the aforementioned Report Parameter-to-SP-parameter mappings toasted.

And then I proceeded to spend a coupla hours replacing all my parameters while listening to Bob Dylan's Infidels repeatedly. I suppose Blood On the Tracks would have been more appropriate.

|||The issue may be that Reporting Services discovers parameters, so when you're dynamically setting the stored procedure it cannot discover them. Try hard-coding the sproc to see if you still have the problem.
I have seen this happen when there are print statements or multiple recordsets in the sproc too.
Are you performing the code inside the RS dataset or in the sproc? Try passing the database in as a parameter to a central sproc instead of doing it in the report, if this is the case.sql

Dataset Parameters and Expressions in Report Connection String

I am working with RS 2005 and have run into a problem with passing a server and database name as parameters into a server report.

Here's what the connection string looks like in these reports:

="Data Source = " & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!DBName.Value

I have default values set for both parameters, so testing usually works fine also. If I run a report with "normal" parameters or no parameters, the report runs fine with the supplied connection string.

The problem occurs when I try to add a parameter to the report that uses a dataset to populate a list of choices. I get the following error when trying to run a report in this situation:

"Error during processing of the ConnectString expression of datasource 'dbConnection'"

I don't get any build errors, just the message above in the report canvas.

Any direction/assistance anyone can provide would be greatly appreciated.. thanks in advance.

Very simple solution, it turns out... just make sure the ServerName and DBName parameters appear above all other parameters in the report definition.

Dataset error when deployed to Report Manager

Hi
I've got a custom assembly which performs validation on parameters
prior to submitting the query to the database. My dataset which
retrieves the report data is in Text format and calls an appropriate
validation method on free-text parameters, such as dates and numbers.
When validation passes, the code returns a string formatted for use in
the query, and when it fails it returns a string value that the stored
procedure recognises as a dummy value and returns without SELECTing any
data. My custom assembly sets a user-friendly error message as a
property, which is displayed on the report in place of any data.
I've designed a number of reports using this method and it works well
in the Designer environment. However, I've deployed a few of them to
Report Manager and am getting the following error when pressing "View
Report":
An error has occurred during report processing. (rsProcessingAborted)
Cannot set the command text for data set 'Report'.
(rsErrorSettingCommandText)
Error during processing of the CommandText expression of dataset
'Report'. (rsQueryCommandTextProcessingError)
An example of one of the simple Datasets is:
="EXEC spAgentSettlementsDetailedRpt " &
Code.Validator.ResetValidator &
"@.p_StartDate_IN = '" +
Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
" &
"@.p_EndDate_IN= '" +
Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
Parameters!p_EndDate_IN.Value) + "', " &
"@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
&
"@.p_WS_Code_IN = " +
Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
Parameters!p_AgentID_IN.Value, false)
I'm happy to provide the code within the assembly if it would be of any
help in debugging this problem.
In deploying to the server, I have copied the assembly (dll) to the
\bin directory on the server and uploaded the rdl file as per normal.
Any help would be greatly appreciated. Thank you.
AshleyI've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
Ashley.Manos@.gmail.com wrote:
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley|||I've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley

Data-Driven Subscriptions: Detecting Success

I am using Data-Driven Subcriptions to send a report to different
recipients using different parameters.
I would like to know if Reporting Services can:
-- update a database on successful distribution of report.
Simplest example: I have bit in table of recipients that determines
whether their report should be run and sent. After report is sent
(successfully), I want sql to set bit to 0.
Is this possible?
DanielThere is nothing built into Report Service that would allow you to do this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<dangordo@.gmail.com> wrote in message
news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
>I am using Data-Driven Subcriptions to send a report to different
> recipients using different parameters.
> I would like to know if Reporting Services can:
> -- update a database on successful distribution of report.
> Simplest example: I have bit in table of recipients that determines
> whether their report should be run and sent. After report is sent
> (successfully), I want sql to set bit to 0.
> Is this possible?
> Daniel
>|||Similar question from different angle: Sometimes distribution ecounters
errors. Is reportserverservice_*.log the only place success and errors
are recorded? Any advice/code on parsing that file?
I am prepared to create mini application to accomplish what i need.
Should I be investigating the rs utility? an application in C#?
Thanks.
Daniel Reib [MSFT] wrote:
> There is nothing built into Report Service that would allow you to do
this.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> <dangordo@.gmail.com> wrote in message
> news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
> >I am using Data-Driven Subcriptions to send a report to different
> > recipients using different parameters.
> >
> > I would like to know if Reporting Services can:
> >
> > -- update a database on successful distribution of report.
> >
> > Simplest example: I have bit in table of recipients that determines
> > whether their report should be run and sent. After report is sent
> > (successfully), I want sql to set bit to 0.
> >
> > Is this possible?
> >
> > Daniel
> >|||Yes, the log file is the only place that we record specific information
about a Data driven subscription failure. You could parse for the
information, but we do not guarantee that the format will be the same from
release to release (a QFE or SP could change it as well).
I'm not sure of how useful it would be anyway. Are you seeing some of the
subscriptions fail, while others succeed? Generally they will either all
succeed or all fail.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<dangordo@.gmail.com> wrote in message
news:1105456778.305024.60080@.f14g2000cwb.googlegroups.com...
> Similar question from different angle: Sometimes distribution ecounters
> errors. Is reportserverservice_*.log the only place success and errors
> are recorded? Any advice/code on parsing that file?
> I am prepared to create mini application to accomplish what i need.
> Should I be investigating the rs utility? an application in C#?
> Thanks.
> Daniel Reib [MSFT] wrote:
>> There is nothing built into Report Service that would allow you to do
> this.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> <dangordo@.gmail.com> wrote in message
>> news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
>> >I am using Data-Driven Subcriptions to send a report to different
>> > recipients using different parameters.
>> >
>> > I would like to know if Reporting Services can:
>> >
>> > -- update a database on successful distribution of report.
>> >
>> > Simplest example: I have bit in table of recipients that determines
>> > whether their report should be run and sent. After report is sent
>> > (successfully), I want sql to set bit to 0.
>> >
>> > Is this possible?
>> >
>> > Daniel
>> >
>

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)