Error exporting in Excel !

I can export in PDF without problem.
But when I try to export in xls, I always have an error with the
dataset ?
Please I need help ... I have no idea what I must do to correct that.
Even with SP1 nothing changed.
I have the error message in french only, I hope you will understand (I
have delete the link for online support in the message)
Une erreur s'est produite lors du traitement du rapport.
(rsProcessingAborted)
Échec de l'exécution de la requête pour le dataset « DataSet1
».(rsErrorExecutingCommand)
ExecuteReader: CommandText property has not been initialized
If you need more info please tell me. I'm kind of lost ...Also .. The Query is dynamic ... it seems to be the problem ...
Any suggestion ...|||Rendering to Excel (or any other format) should have nothing to do with it.
Something in your query is evaluating to NULL or empty string. That it
happens when you render to Excel issue is probably coincidence.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JR" <jeanrenebaron@.hotmail.com> wrote in message
news:4a904277.0407050635.54359192@.posting.google.com...
> Also .. The Query is dynamic ... it seems to be the problem ...
> Any suggestion ...|||I have 3 reports. The user can choose between PDF or Excel for output
format.
I have the same problem with the 3 reports. PDF is OK, Excel always
crashes. Big coincidence I guess. I can't find what is wrong with my
programming.
It's look like the connection with the dataset is made BEFORE the
obtention of the parameters when I try to output in excel format. So
we have some null values in the query after (I guess) ..
On another environment I have this error
This report requires a default or user-defined value for the report
parameter 'rqtDataSet1'. To run or subscribe to this report, you must
provide a parameter value. (rsReportParameterValueNotSet)
I have no default value for the parameter of the report, if I put a
default value (the query), the excel file open but is blank, no value,
no header, nothing.
This is the difference in my code for PDF or EXCEL (objHTTP =MSXML2.ServerXMLHTTP)
If dictParameters("ViewAs") = "2" Then
sParams = sParams & "rs:Format=PDF"
Else
sParams = sParams & "rs:Format=EXCEL"
End If
If dictParameters("ViewAs") = 2 Then
objHTTP.setRequestHeader "Content-Type",
"application/x-www-form-urlencoded"
Else
objHTTP.setRequestHeader "Content-Type",
"application/vnd.ms-excel"
End If
This is sParams at the end
rqtDataSet1=SELECT%20CAST(MF.IS_COMP%20AS%20VARCHAR(1))%20AS%20MACH_TYPE%2CCAST(DAY(MF.CRET_ON)%20AS%20VARCHAR)%20%2B%20%27%20%27%20%2B%20Case%20Month(MF.CRET_ON)%20when%201%20then%20%27Jan%27%20when%202%20then%20%27Feb%27%20when%203%20then%20%27Mar%27%20when%204%20then%20%27Apr%27%20when%205%20then%20%27May%27%20when%206%20then%20%27Jun%27%20when%207%20then%20%27Jul%27%20when%208%20then%20%27Aug%
7%20when%209%20then%20%27Sep%27%20when%2010%20then%20%27Oct%27%20when%2011%20then%20%27Nov%27%20when%2012%20then%20%27Dec%27%20END%20%2B%20%27%20%27%20%2B%20CAST(YEAR(MF.CRET_ON)%20AS%20NVARCHAR(4))%20AS%20FOUND_ON%2CO.NAME%20AS%20OPER_NAME%2CT.NAME%20AS%20TEAM_NAME%2C%20MF.RFID%2CCASE%20WHEN%20MF.PICT%20IS%20NULL%20THEN%20%270%27%20ELSE%20%271%27%20END%20AS%20PICTURE%20FROM%20BMB1_Market.DBO.MACH
NES_FOUND%20MF%20INNER%20JOIN%20BMB1_Market.DBO.TEAMS%20T%20ON%20MF.TEAM_ID=T.ID%20INNER%20JOIN%20BMB1_Market.DBO.OPERATORS%20O%20ON%20T.OPER_ID=O.ID%20WHERE%20MF.CRET_ON%20BETWEEN%20CAST(%272004-07-01%27%20As%20Datetime)
%20AND%20CAST(%272004-07-06%27%20As%20Datetime)%20ORDER%20BY%20MACH_TYPE%20DESC%2CFOUND_ON%2COPER_NAME%2CTEAM_NAME%20&ReportTitle=Machines%20Found&lblFoundOn=Found%20On&lblOpName=Operator%20Name&lblTeamName=Team%20Name&lblMachRFID=Machine%20RFID&lblWPict=With%20Picture&lblCompet=Competitor&lblNotCompet=Not%20Competitor&lblMachSubTot=Machine%20Subtotal&lblNo=No&lblYes=Yes&lblTotal=Grand%20Total&Fro
ToFrom=01%20Jul%202004&FromToTo=06%20Jul%202004&ToTo=&POCGeo=&POCDist=&lblCupSoldFormat=&CupSoldMoreThan=&CupSoldLessThan=&Operators=&OperatorStatus=All&OperatorType=All&Customers=&CustStatus=&POCResp=&POCRespStatus=&POCRespType=&POC=&POCStatus=&MachineStatus=&MachineOwnership=&MachineOwnershipStatus=&MachineVersion=&MachineConf=&MachineConfSel=&ProductRecipeStatus=&ServingRecipeStatus=&RestStatus
&UserID=&LastName=&POCSince=&MachineType=All&LocalizationGrp=&Duration=&StartCalc=&ResultFormat=False&ByUserID=francisco.deolarte@.nestle.com&PrintedOn=06%20Jul%202004&lblFrom=From&lblTo=To&lblCriterias=Report%20Criteria&l
blPOCGeo=POC%20Geographical%20Location&lblPOCDist=POC%20Distribution%20Channel&lblCustomers=Customer&lblOperators=Operator&lblPOCResp=POC%20Responsible&lblPOC=POC&lblMachineOwnership=Machine%20Ownership&lblMachine=Machine&lblMachineStatus=Machine%20Status&lblMachineClass=Machine%20Classification&lblMachineBrand=Machine%20Brand&lblMachineModel=Machine%20Model&lblMachineConf=Machine%20Configuration&
blMachineVersion=Machine%20Version&lblMachineConfSel=Machine%20Configuration%20Selection&lblProductType=Product%20Type&lblServingRecipe=Serving%20Recipe&lblStatus=Status&lblType=Type&lblProductRecipe=Product%20Recipe&lblKPIRespImp=KPI%20Response%20Impact&lblDuration=Duration&lblDays=Days&lblConsolidated=Consolidated%20results&lblBy=By&lblUserID=User%20ID&lblPage=Page&lblPrintedOn=Printed%20on&lblA
=At&lblRole=Role&lblLastName=Last%20Name&lblStartCalc=Start%20Calculation&lblCupSoldMoreThan=More%20than&lblCupSoldLessThan=Less%20than&lblRestStatus=Restriction%20Status&lblMachType=Machine%20Type&lblMachFunc=Machine%20F
unction&lblMachInPoc=Machine%20in%20POC%20since&lblPOCLocGrp=POC%20Localization%20Group&rs:Format=EXCEL|||Some additional info
sUrl = http://(ServerName)/ReportServer?/BMB-MS/MachineFound&rs:Command=Render
objHTTP.Open "POST", sURL, False
'At the end
objHTTP.send sParams 'CRASH THERE objHTTP.status returns 500
When it returns 200, the function returns -> GetReport = objHTTP.responseBody
Is there a special setting I must include in my .rdl ?|||Why are you using URL Access instead of the SOAP API? It looks like you
might be hitting a parameter limit problem. You can also use POST with URL
Access but the SOAP API would be much better.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JR" <jeanrenebaron@.hotmail.com> wrote in message
news:4a904277.0407060357.31073bf7@.posting.google.com...
>I have 3 reports. The user can choose between PDF or Excel for output
> format.
> I have the same problem with the 3 reports. PDF is OK, Excel always
> crashes. Big coincidence I guess. I can't find what is wrong with my
> programming.
> It's look like the connection with the dataset is made BEFORE the
> obtention of the parameters when I try to output in excel format. So
> we have some null values in the query after (I guess) ..
> On another environment I have this error
> This report requires a default or user-defined value for the report
> parameter 'rqtDataSet1'. To run or subscribe to this report, you must
> provide a parameter value. (rsReportParameterValueNotSet)
> I have no default value for the parameter of the report, if I put a
> default value (the query), the excel file open but is blank, no value,
> no header, nothing.
>
> This is the difference in my code for PDF or EXCEL (objHTTP => MSXML2.ServerXMLHTTP)
> If dictParameters("ViewAs") = "2" Then
> sParams = sParams & "rs:Format=PDF"
> Else
> sParams = sParams & "rs:Format=EXCEL"
> End If
> If dictParameters("ViewAs") = 2 Then
> objHTTP.setRequestHeader "Content-Type",
> "application/x-www-form-urlencoded"
> Else
> objHTTP.setRequestHeader "Content-Type",
> "application/vnd.ms-excel"
> End If
> This is sParams at the end
> rqtDataSet1=SELECT%20CAST(MF.IS_COMP%20AS%20VARCHAR(1))%20AS%20MACH_TYPE%2CCAST(DAY(MF.CRET_ON)%20AS%20VARCHAR)%20%2B%20%27%20%27%20%2B%20Case%20Month(MF.CRET_ON)%20when%201%20then%20%27Jan%27%20when%202%20then%20%27Feb%27%20when%203%20then%20%27Mar%27%20when%204%20then%20%27Apr%27%20when%205%20then%20%27May%27%20when%206%20then%20%27Jun%27%20when%207%20then%20%27Jul%27%20when%208%20then%20%27Aug%
> 7%20when%209%20then%20%27Sep%27%20when%2010%20then%20%27Oct%27%20when%2011%20then%20%27Nov%27%20when%2012%20then%20%27Dec%27%20END%20%2B%20%27%20%27%20%2B%20CAST(YEAR(MF.CRET_ON)%20AS%20NVARCHAR(4))%20AS%20FOUND_ON%2CO.NAME%20AS%20OPER_NAME%2CT.NAME%20AS%20TEAM_NAME%2C%20MF.RFID%2CCASE%20WHEN%20MF.PICT%20IS%20NULL%20THEN%20%270%27%20ELSE%20%271%27%20END%20AS%20PICTURE%20FROM%20BMB1_Market.DBO.MACH
> NES_FOUND%20MF%20INNER%20JOIN%20BMB1_Market.DBO.TEAMS%20T%20ON%20MF.TEAM_ID=T.ID%20INNER%20JOIN%20BMB1_Market.DBO.OPERATORS%20O%20ON%20T.OPER_ID=O.ID%20WHERE%20MF.CRET_ON%20BETWEEN%20CAST(%272004-07-01%27%20As%20Datetime)
> %20AND%20CAST(%272004-07-06%27%20As%20Datetime)%20ORDER%20BY%20MACH_TYPE%20DESC%2CFOUND_ON%2COPER_NAME%2CTEAM_NAME%20&ReportTitle=Machines%20Found&lblFoundOn=Found%20On&lblOpName=Operator%20Name&lblTeamName=Team%20Name&lblMachRFID=Machine%20RFID&lblWPict=With%20Picture&lblCompet=Competitor&lblNotCompet=Not%20Competitor&lblMachSubTot=Machine%20Subtotal&lblNo=No&lblYes=Yes&lblTotal=Grand%20Total&Fro
> ToFrom=01%20Jul%202004&FromToTo=06%20Jul%202004&ToTo=&POCGeo=&POCDist=&lblCupSoldFormat=&CupSoldMoreThan=&CupSoldLessThan=&Operators=&OperatorStatus=All&OperatorType=All&Customers=&CustStatus=&POCResp=&POCRespStatus=&POCRespType=&POC=&POCStatus=&MachineStatus=&MachineOwnership=&MachineOwnershipStatus=&MachineVersion=&MachineConf=&MachineConfSel=&ProductRecipeStatus=&ServingRecipeStatus=&RestStatus
> &UserID=&LastName=&POCSince=&MachineType=All&LocalizationGrp=&Duration=&StartCalc=&ResultFormat=False&ByUserID=francisco.deolarte@.nestle.com&PrintedOn=06%20Jul%202004&lblFrom=From&lblTo=To&lblCriterias=Report%20Criteria&l
> blPOCGeo=POC%20Geographical%20Location&lblPOCDist=POC%20Distribution%20Channel&lblCustomers=Customer&lblOperators=Operator&lblPOCResp=POC%20Responsible&lblPOC=POC&lblMachineOwnership=Machine%20Ownership&lblMachine=Machine&lblMachineStatus=Machine%20Status&lblMachineClass=Machine%20Classification&lblMachineBrand=Machine%20Brand&lblMachineModel=Machine%20Model&lblMachineConf=Machine%20Configuration&
> blMachineVersion=Machine%20Version&lblMachineConfSel=Machine%20Configuration%20Selection&lblProductType=Product%20Type&lblServingRecipe=Serving%20Recipe&lblStatus=Status&lblType=Type&lblProductRecipe=Product%20Recipe&lblKPIRespImp=KPI%20Response%20Impact&lblDuration=Duration&lblDays=Days&lblConsolidated=Consolidated%20results&lblBy=By&lblUserID=User%20ID&lblPage=Page&lblPrintedOn=Printed%20on&lblA
> =At&lblRole=Role&lblLastName=Last%20Name&lblStartCalc=Start%20Calculation&lblCupSoldMoreThan=More%20than&lblCupSoldLessThan=Less%20than&lblRestStatus=Restriction%20Status&lblMachType=Machine%20Type&lblMachFunc=Machine%20F
> unction&lblMachInPoc=Machine%20in%20POC%20since&lblPOCLocGrp=POC%20Localization%20Group&rs:Format=EXCEL|||I'm not familiar with the SOAP API .. Cant someone give me some hints ?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message news:<uLi7Ud3YEHA.2480@.tk2msftngp13.phx.gbl>...
> Why are you using URL Access instead of the SOAP API? It looks like you
> might be hitting a parameter limit problem. You can also use POST with URL
> Access but the SOAP API would be much better.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.|||It is in the docs under "Web Services Programming".
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JR" <jeanrenebaron@.hotmail.com> wrote in message
news:4a904277.0407080039.71101e49@.posting.google.com...
> I'm not familiar with the SOAP API .. Cant someone give me some hints ?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:<uLi7Ud3YEHA.2480@.tk2msftngp13.phx.gbl>...
>> Why are you using URL Access instead of the SOAP API? It looks like you
>> might be hitting a parameter limit problem. You can also use POST with
>> URL
>> Access but the SOAP API would be much better.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.