Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.
sql

Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.

Need to allow paging and exporting

I've got a .NET web app that has a report, and every time I click the paging or export button, the report refreshes to the parameter prompt. Same thing with clicking on the group tree.

I'm using version XI.

I've got the DB credentials fine, since my two Crystal XI books tell me how to do that...but I haven't found anything about paging and exporting.

Thanks!Duh...had to use:

If not page.ispostback....

thanks!sql

Need Parameter Optionally Omitted

Is it possible to have a parameter ignored? I have a report that I am
web-deploying with a large set of parameters, but a user may not wish to
include some for a given execution of the report. For example, I have a
Boolean checkbox that I cannot get the report to ignore. I have tried
toggling the following settings: allow null, allow blank, setting defaults,
not setting defaults. I then recast as char and used a true/false drop-down
but still could not get the report to optionally use it.
Thanks for any helpMike,
It sounds like you're using the Report Parameter to make a Query
Parameter to use in your SQL query. If that's the case, the problem may
be that when the parameter is null, your SQL query fails because it's
expecting a query parameter that doesn't exist.
If this is the case (and I can't be sure since you didn't give the
exact error) what you can do is write a function that checks all of
your parameters, performs whatever logic you need, and returns a sql
statement as a string. In your dataset you will have something like
=Code.GetSQL()
instead of the sql statement you have now.
Just make sure you're using the Generic Query Designer instead of the
Query Builder, or it'll have a fit.|||I am not getting an error, just incorrect results from the query when I
don't want the param used. If I delete the param, I get the results I would
expect if the param were ignored. In the grid colum 'Criteria' I add
'@.param'. Then from the menu Report\Report Parameters I add the addtional
attributes for the param as I mentioned earlier.
I am not a SQL power-user so I am tring for a modest report, accepting some
of the known limitations of the tool esp regarding use of params. I was
hoping to at least get basic function though.
Also, Is there a distinction between a report vs query paramter?
Many thanks!

Need help: Openxml failed for datasize greater than 120k

Hi,
Can anybody help me on what is going wrong, please?
I tried to pass an XML document as text parameter in a stored procedure
where openxml is being used to insert data into temp tables. If I pass any
XML document over 120k of size it goes in a loop and does not return any
error message. The SQL server is 2000 standard edition with service pack 3
running on windows 2003 server and it has all the latest versions of MSXML
(version 2, 3 and 4. All with latest service packs).
However if I try to execute it in my laptop (win XP professional, MSDE 2000
with service pack 3), it works fine. I tried with 500k XML data and it can
process.
Am I missing anything?
thanks
-Asir
Message posted via http://www.webservertalk.comHi
I assume you are calling SQLXML which uses MSXML? In which case make sure
that you have download the latest service pack (sp3). Without DDL, code or
example data it is not possible diagnose this problem. Check the differences
in the structure of the small files compared to the larger ones. You may wan
t
to try posting to microsoft.public.sqlserver.xml to see if anyone there has
more to add.
John
"Asir Sikdar via webservertalk.com" wrote:

> Hi,
> Can anybody help me on what is going wrong, please?
> I tried to pass an XML document as text parameter in a stored procedure
> where openxml is being used to insert data into temp tables. If I pass any
> XML document over 120k of size it goes in a loop and does not return any
> error message. The SQL server is 2000 standard edition with service pack 3
> running on windows 2003 server and it has all the latest versions of MSXML
> (version 2, 3 and 4. All with latest service packs).
> However if I try to execute it in my laptop (win XP professional, MSDE 200
0
> with service pack 3), it works fine. I tried with 500k XML data and it can
> process.
> Am I missing anything?
> thanks
> -Asir
> --
> Message posted via http://www.webservertalk.com
>|||My Code block is given below: From front end, ASP.Net(C#) I passed the XML
document as text to the stored procedure.
CREATE PROCEDURE p_xmlinsert
@.RequestId As Int,
@.Data1 As Int,
@.doc As text
As
Begin
Declare @.idoc Int
Declare @.TranCount Int,
@.Msg Varchar(256)
Set @.Msg = 'OK'
Set @.TranCount = @.@.TRANCOUNT
Exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
Into #Tmp1
FROM OPENXML (@.idoc, '/Data/Invoice',2)
WITH
( InvoiceNumber VarChar(64) '@.Invoice_Number',
Data1 Int '@.Data1',
Adderess1 VarChar(32) '@.Adder1',
Adderess2 VarChar(32) '@.Adder2',
City VarChar(32) '@.City',
State VarChar(32) '@.State',
Zip VarChar(32) '@.Zip',
Date VarChar(32) '@.Date',
Total VarChar(32) '@.Total'
)
SELECT *
Into #Tmp2
FROM OPENXML (@.idoc, '/Data/Invoice/Event',2)
WITH
( InvoiceNumber VarChar(64) '../@.Invoice_Number',
OrderNumber VarChar(32) '@.Order_Number',
OrderDate DateTime '@.Order_Date',
ContactName VarChar(64) '@.Contact_Name',
ContactEmail VarChar(64) '@.Contact_Email',
ContactPhone VarChar(24) '@.Contact_Phone',
ContactAddr1 VarChar(128) '@.Contact_Addr1',
ContactAddr2 VarChar(128) '@.Contact_Addr2',
ContactCity VarChar(32) '@.Contact_City',
ContactState VarChar(2) '@.Contact_State',
ContactZip VarChar(5) '@.Contact_Zip',
)
SELECT *
Into #Tmp3
FROM OPENXML (@.idoc, '/Data/Invoice/Event/Order_Detail',2)
WITH
(InvoiceNumber VarChar(64) '../../@.Invoice_Number',
OrderNumber VarChar(32) '../@.Order_Number',
ItemName VarChar(64) '@.Item_Name',
ItemDesc VarChar(256) '@.Item_Desc',
Quantity Decimal(18,2) '@.Quantity',
UnitPrice Decimal(18,2) '@.Unit_Price',
OrderTax Decimal(18,2) '@.Order_Tax',
OrderTotal Decimal(18,2) '@.Order_Total'
)
EXECUTE sp_xml_removedocument @.idoc
--Now Add data
If @.TranCount = 0
Begin Tran
Insert Into table1
(
ReqId,
Data1,
InvoiceNumber,
UnitAddress1,
UnitAddress2,
UnitCity,
UnitState,
UnitZip,
InvoiceDate,
InvoiceNetAmount,
InvoiceTaxAmount,
InvoiceTotalAmount,
CreatedDate
)
Select
@.RequestId,
Data1,
InvoiceNumber,
UnitAdder1,
UnitAdder2,
UnitCity,
UnitState,
UnitZip,
InvoiceDate,
0.0,
0.0,
Total,
GetDate()
From #Tmp1
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert1 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Insert Into table2
(
Table1Id,
OrderNumber,
OrderDate,
ContactName,
ContactEmail,
ContactPhone,
ContactAddress1,
ContactAddress2,
ContactCity,
ContactState,
ContactZip,
)
Select A.Table1Id,
OrderNumber,
OrderDate,
ContactName,
ContactEmail,
ContactPhone,
ContactAddr1,
ContactAddr2,
ContactCity,
ContactState,
ContactZip,
From #Tmp2 A,
Table1 B
Where A.InvoiceNumber = B.InvoiceNumber
And B.Data1 = @.Data1
And B.ReqId = @.RequestId
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert2 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Insert Into Table3
(
Table2Id,
ItemName,
ItemDescription,
Quantity,
UnitPrice,
SalesTax,
TotalPrice
)
Select B.Table2Id,
ItemName,
ItemDesc,
Quantity,
UnitPrice,
OrderTax,
OrderTotal
From #Tmp3 A,
Table1 B,
Table2 C
Where A.InvoiceNumber = B.InvoiceNumber
And B.Data1 = @.Data1
And B.ReqId = @.RequestId
And A.OrderNumber = C.OrderNumber
And B.Table1Id = C.Table1Id
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert3 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Update TableQueue Set Status = 'Ready' Where ReqId = @.RequestId and Status
= 'Pending'
If @.@.error<>0
Begin
Set @.Msg = 'Error: Update failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
If @.TranCount = 0
Commit Transaction
Select 3
Return
End
GO
Thanks
-Asir
Message posted via http://www.webservertalk.com

Need Help: How to schedule a report that has parameter?

Hi All:
I have created a report that is based on stored procedure and also has some
parameters. Manually after entering proper parameter values, I can run the
report properly.
My questions are:
1. How can I schedule this report?
2. While scheduling this report, is there anyway I can provide the
corresponding proper parameter values so that I can get my required report?
I would appreciate your help.
Thanks.
SamHave you try to use a subscription?
"Sue" wrote:
> Hi All:
> I have created a report that is based on stored procedure and also has some
> parameters. Manually after entering proper parameter values, I can run the
> report properly.
> My questions are:
> 1. How can I schedule this report?
> 2. While scheduling this report, is there anyway I can provide the
> corresponding proper parameter values so that I can get my required report?
> I would appreciate your help.
> Thanks.
> Sam
>|||Yes. But did not work out properly. Need help.
Here is my Case
+++++++++++++++++++++++++++++
I have a common report object residing on Reporting Service. The report
shows Monthly Activity for a Department. This report is based on a Stored
Procedure and has Department ID as a parameter.
Through an application different departments either can run this report or
create a schedule so that at the end of each month, any department can get
the report related to only its data.
How can I do that?
+++++++++++++++++++++++++
"Soan" wrote:
> Have you try to use a subscription?
> "Sue" wrote:
> > Hi All:
> >
> > I have created a report that is based on stored procedure and also has some
> > parameters. Manually after entering proper parameter values, I can run the
> > report properly.
> >
> > My questions are:
> >
> > 1. How can I schedule this report?
> >
> > 2. While scheduling this report, is there anyway I can provide the
> > corresponding proper parameter values so that I can get my required report?
> >
> > I would appreciate your help.
> >
> > Thanks.
> >
> > Sam
> >
> >

Need help with UPDATE with PARTITION & PARAMETER

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB, but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the each latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.|||

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far. I am working in SQL server 2005.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.|||

You are returning the results of a data manipulation - there may not be a 'match' between the resultset and the actual table.

My 'simple' recommendation is to:

capture the resultset in a @.Table variable, then use the [ID] value from that table variable to UPDATE the data, finally, returning the contents of the table variable with a SELECT.|||

Thanks for the reply. I posted this question a few too many times (was told it was deleted by administrator!).

Anyway, [ID] is not the primary key, [acmdtn_RECID] is. Any changes made to the master database simply add another record, and retain the old record, so historical queries can be run. So there can be dozens of records for each [ID]. What I want is the most recent record, for each [ID], as of a certain date.

I guess I just don't get why I can display the information, but I can't write back to the database based on that displayed information. It's a complex query, but there's no joins, or other tables involved, there can only be a 1:1 relationship between records in the table, and records in the resultset.