?
Solved

SSRS 2008 Report is converting varchar Parameters to int

Posted on 2012-08-27
7
Medium Priority
?
2,636 Views
Last Modified: 2012-09-29
I noticed that  SSRS 2008 Report was converting a text parameter in the report to an integer during execution.

It's possible to cast text parameter to a varchar like below. Looks like it resolves the issue.

Is there a better way to fix this issue??

DECLARE @myParam int //  text parameter in the report  converting  to an integer during //execution.
SET @myParam=125

declare @mStr varchar(50)

SELECT @mStr=CAST(@myParam as varchar(10))
print @mStr ----> 125
0
Comment
Question by:niceguy971
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 38339392
What are you doing with the parameter when you notice the conversion ? For example, are you trying to do something like Parameters!txtParam.value + " is a string" and getting a type-mismatch error ? Or not getting an error with Parameters!txtParam.value + 16 ?  

There must be a reason for SSRS thinking that you really mean it to be a int, not a text: are you actually passing it an int, by any chance? If so, type it as an int, and then use it as a text with the CStr function.

hth

Mike
0
 

Author Comment

by:niceguy971
ID: 38339625
Please check the link
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0a07b6f6-f427-48ad-af84-4da84a63af3f/

It describes the same issue.  I enter RS report parameter (when it prompts) when i run report
(and this parameter was defined as TEXT...)  but RS report treats my parameter as string.

Any comments??

Thanks
0
 

Author Comment

by:niceguy971
ID: 38339626
Sorry .....typo...but RS report treats my parameter as  Integer !!!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 16

Accepted Solution

by:
DcpKing earned 2000 total points
ID: 38340440
What exactly do you mean by "RS treats my parameter as integer" ? What is it that tells you this? For example, are you trying to do something that fails because of a type mismatch?  If so, what?

In the article that you mention (I hadn't seen it before - thanks!) Lola Wang says roughly what I did above, about the CStr function.

The person in the thread is supplying an nvarchar value to be compared with a text type; this may be the problem. The MS code may be relying on an algorithm for implicit conversion that fails under some circumstances. Is it a problem for you to cast both sides of your comparison with CStr ?

Mike
0
 

Author Comment

by:niceguy971
ID: 38447855
Mike, Your question "What exactly do you mean by "RS treats my parameter as integer" ?"--

I was not able to create new dataset in RS Report..I was getting an error in RS report (design time)..even the same query (the code) worked fine in Sql Server Management Studio.
The reason is described in the article above (the link above):  RS report  "..was converting a text parameter in the report to an integer.."... in my case during design time.  I use the following to resolve this issue:

declare @mStr varchar(50)
SELECT @mStr=CAST(@myParam as varchar(10))

I did not use CStr() to fix this issue.
I'm closing this question.
0
 

Author Closing Comment

by:niceguy971
ID: 38447856
Thanks
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38448063
Thanks for explaining! I assumed that it was a runtime thing. Good luck

Mike
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question