May 8, 2008 at 1:45 pm
bo (5/8/2008)
Thank you for your posting, it is very helpful. Just want to point out when setting the return value of the function to the dataset parameter, it needs to reference the Code class(i.e. =Code.ReturnXML(....)).
good point.
If you're using the function in the Code tab of the report properties, it gets referenced as:
=Code.ReturnXML(...)
If you're using the function in an assembly, it gets referenced as:
=LibraryName.ClassName.ReturnXML(...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 1:50 pm
ok - adding the code.Return... enable it to run - however with no results.
Is this right for in my sp
C=my parameter name
Customer=label
Customer_Number=int value
INNER JOIN
@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')
please... anyone... thank you!
May 8, 2008 at 1:55 pm
lklein (5/8/2008)
My dataset Parameter code is: =ReturnXML(Parameters!C.Value,"C","Customer","Customer_Number") - C being the name of the multivalue parameter which is defined as string the the parameter details.
try changing that to =Code.ReturnXML(...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 1:57 pm
Yes I did that - can you read my last post - it runs now but doesn't return anything - should I have the [1] on there?
May 8, 2008 at 2:09 pm
More info: here's what I have in the Dataset Parameter Value
=Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")
but when I go look at the ExecutionLog the Parameter Passed is...
C=10165&C=10008&C=10162&C=10009&C=10007&C=10137&C=10179&C=10171&C=10002&C=10003&C=10001&C=10004&C=10154&C=10196&C=10005&C=10122&C=10153&C=10157&C=10006&C=10152&C=10125&C=10172&C=10214&C=10010&C=10159&C=10176&C=10055&C=10063&C=10126&C=10011&C=10012&C=10013&C=10020
I guess I would have expected it to either be not readable or in xml form...
Please - thank you...
May 8, 2008 at 2:13 pm
lklein (5/8/2008)
ok - adding the code.Return... enable it to run - however with no results.Is this right for in my sp
C=my parameter name
Customer=label
Customer_Number=int value
INNER JOIN
@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')
please... anyone... thank you!
As you wrote the query, you should have a parameter in your stored procedure defined as @C XML.
I don't see anything wrong with your query. If it still doesn't return anything, try adding this to your procedure. When you run the report, you'll see what you're sending it.
[Code]
declare @c1 varchar(max)
set @c1 = convert(varchar(max), @C)
RaisError (@c1, 16, 1)
[/Code]
At this point, you can copy the XML string to SSMS and debug your proc from there.
Also, you can run this to see if the XML string is processing correctly:
[Code]
select m.item.value('Customer_Number[1]', 'integer') CustomerNumber
from @C.nodes('/C/Customer') AS m(item)
[/Code]
Just to clarify:
C=Root
Customer=Node
Customer_Number=Element
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 2:22 pm
C=Root is the parameter name both in the report and the sp and there is only this param
Customer=Node is the displayed label name of the param
Customer Number=Element is the actual value of the param
I ran the select sample code and just ran it as the sp code then ran it in the report data and it returned nothing.
I would love for this to work. Thank you for trying to help me. Any other ideas?
May 8, 2008 at 2:33 pm
I think I found your problem (at least I hope so)...
you are calling:
=Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")
and in the procedure you are going:
INNER JOIN
@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')
I just ran a test, and found out that this is CaSe SeNsItIvE.
Change your select to:
INNER JOIN
@C.nodes('/C/CUSTOMER') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('CUSTOMER_NUMBER[1]','integer')
If you're getting no result set, then the improper case is in the nodes(). If you only get a NULL result, then it's in the value().
Most likely, it's in both.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 2:38 pm
Dang I wish that were it - it's all caps in both the sp and in the report pieces.
May 8, 2008 at 2:42 pm
lklein (5/8/2008)
Dang I wish that were it - it's all caps in both the sp and in the report pieces.
Is everything is spelled the same?
Well, can you:
1. Post the code you are doing with the ReturnXML(), and
2. Post the procedure code (at least the parameters and select statement), and
3. Post the XML string you're getting from the RaisError code above.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 2:48 pm
First off thank you so much for trying to help me... I've tried the passing and parsing out - that doesn't work. I'm wondering if there's a setting I need.
The raise error - I'm not sure how that works but it didn't return anything -
=Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")
and the sp:
ALTER proc [dbo].[spCUSTOMER_PROMO] @C XML
--, @INPUT_DATE DATETIME
AS
declare @INPUT AS table (
CUSTOMER VARCHAR(35),
CUSTOMER_NUMBER VARCHAR(10),
PREM NUMERIC(18,2),
PREM_VALID VARCHAR(25),
PREM_CODE VARCHAR(25),
FREIGHT NUMERIC(18,2),
FREIGHT_VALID VARCHAR(25),
FREIGHT_CODE VARCHAR(25) )
INSERT INTO @INPUT (CUSTOMER,CUSTOMER_NUMBER,PREM,PREM_VALID,PREM_CODE)
SELECT CS.CUSTOMER_NAME, TA.CUSTOMER_NUMBER,TD.TP_DISCOUNT_AMOUNT,
CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101),
TA.TP_PROMOTION_CODE
FROM TP_CUSTOMER_ASSOCIATIONS AS TA INNER JOIN
TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE INNER JOIN
CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER INNER JOIN
TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE INNER JOIN
@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')
WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE AND
--(CS.CUSTOMER_NUMBER IN (SELECT VALUE FROM dbo.FX_SPLIT(@C,','))) and
TP.TP_PROGRAM_ID IN ('PREMIUM')
INSERT INTO @INPUT (CUSTOMER,CUSTOMER_NUMBER,FREIGHT,PREM_VALID,PREM_CODE)
SELECT CS.CUSTOMER_NAME, TA.CUSTOMER_NUMBER,TD.TP_DISCOUNT_AMOUNT,
CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101),
TA.TP_PROMOTION_CODE
FROM TP_CUSTOMER_ASSOCIATIONS AS TA INNER JOIN
TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE INNER JOIN
CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER INNER JOIN
TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE INNER JOIN
@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')
WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE AND
--(CS.CUSTOMER_NUMBER IN (SELECT * FROM dbo.fnDStringToTable(@C,','))) and
TP.TP_PROGRAM_ID IN ('FREIGHT')
select * from @INPUT ORDER BY CUSTOMER
May 8, 2008 at 3:24 pm
As far as your XML code goes, I don't see anything wrong with what you're doing.
I'd really like to see the XML string that's being passed to the report.
How about trying this code out? (I consolidated your code into one select statement, and formatted it some to make it easier for me to read).
When you call this from your report, you should get the XML string that you're passing to the procedure. Can you copy it and put it up here?
[Code]
ALTER proc [dbo].[spCUSTOMER_PROMO] @C XML
--, @INPUT_DATE DATETIME
AS
declare @c1 varchar(max)
set @c1 = convert(varchar(max), @C)
RaisError(@c1, 16, 1)
declare @temp varchar(25) -- need something since two fields are never inserted into, but needed for output
select
CS.CUSTOMER_NAME CUSTOMER,
TA.CUSTOMER_NUMBER,
CASE WHEN TP.TP_PROGRAM_ID = 'PREMIUM' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END PREM,
CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101) PREM_VALID,
TA.TP_PROMOTION_CODE,
CASE WHEN TP.TP_PROGRAM_ID = 'FREIGHT' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END FREIGHT,
@temp FREIGHT_VALID,
@temp FREIGHT_CODE
FROM TP_CUSTOMER_ASSOCIATIONS AS TA
INNER JOIN TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE
INNER JOIN CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER
INNER JOIN TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE
INNER JOIN @C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')
WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE
AND TP.TP_PROGRAM_ID IN('PREMIUM', 'FREIGHT')
ORDER BY CS.CUSTOMER_NAME
-- =Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")
[/Code]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 3:34 pm
This is so frustrating... yikes - I get an error running that just in design view right?
here's the whole detail
===================================
An error occurred while executing the query.
10008 (Microsoft Report Designer)
===================================
10008 (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=50000&LinkId=20476
------------------------------
Server Name: x.x.com
Error Number: 50000
Severity: 16
State: 1
Procedure: spCUSTOMER_PROMO
Line Number: 7
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.ExecuteQuery()
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply