Parameter Name Getting Garbled
Sep 3, 2004
I have a function that calls a sproc, and passes in a bunch of parameters. One of the parameters is named @ProcessorEmail. Occasionally, the parameter name apparently gets garbled at runtime, as the following error occurs:
System.Data.SqlClient.SqlException: @ProcessorEm??. is not a parameter for procedure gUser
The "@ProcessorEm" part is always fine, then there will be various characters in place of the "ail" part.
Any ideas?
View 10 Replies
ADVERTISEMENT
Jan 31, 2007
Hi experts,
Here I got some problems with my application. (ASP & English Version SQL Server 2000)
As we are using English MSSQL Server 2000, we got some new functions and we have to facilitate support of Chinese characters in the DB. I have set the collation for those Chinese fields already and those queries or Stored Procs for Chinese are working fine, ONLY if I execute them in Enterprise Manager. Chinese characters can be displayed in the relevant tables.
However here comes the big problem and I got really frustrated. As we will provide user interface in ASP pages, we 'll let users to insert the information which will be sent to the DB. If there's Chinese characters in the query string, the Chinese characters added in the DB would be garbled.
e.g. EXECUTE proc_TestChinese 'XYZ', 'test123' (assume XYZ be those Chinese words)
I am wondering if there's any way I can solve this problem. Should I add special handling for these Chinese words? I have set the ASP pages in UTF-8 or Big5 encoding but it doesn't help. Hope you experts can show me the way out of the mess. Thanks in advance!
Manfred
View 4 Replies
View Related
Mar 20, 2008
My environment is
Windows 2003 Server(japanese)+SP2 and SQL Server 2005(English)+SP2
I've been having a problem with Reporting Service.When i export report
to PDF file,the text(mixed japanese and english)gets all garbled and
unreadable in Vista OS.but in windows xp every things is well.i tried
upgrade adobe reader to the recently version ,but there is no help.
this is a known bug?or i missed something?
any ideas
Thanks
View 6 Replies
View Related
Apr 17, 2008
Hi
I am using SSRS 2005 on a development server. The access is over https.
Whenever a TextBox of a report contains a vbCrLf and this report is
exported to PDF this text is garbled. Text not containing CR LF is ok.
The text is still latin characters but has nothing to do with the text intended to show.
Character spacing is broken. Sometimes more than one character are rendered
over each other.
I do not have the same problem on a production SSRS box.
The only difference is that the development box has .NET framework 3.5 installed
and production is still on 2.0
Is this a known issue?
Maybe there is already a patch?
orbit
View 4 Replies
View Related
Aug 11, 2015
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
View 4 Replies
View Related
Feb 19, 2007
Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
View 3 Replies
View Related
Aug 21, 2015
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
View 5 Replies
View Related
Jul 29, 2015
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
View 2 Replies
View Related
Jan 30, 2007
Hi,
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
Thanks.
Kong
View 6 Replies
View Related
Oct 15, 2007
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
View 7 Replies
View Related
Aug 28, 2015
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies
View Related
Apr 17, 2008
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
View 2 Replies
View Related
Aug 11, 2005
Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
View 5 Replies
View Related
Oct 18, 2015
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature available in any other updated version of ssrs?
View 2 Replies
View Related
Jan 8, 2007
I have two parameters both are related to each other.
second parameter should get filled based on the selection of the first one which is project.
the first paramater is project, once the project is selected it should bring the all the contracts related to that project.
Please is it possible...
Thank you very much for all the helpful info.
View 5 Replies
View Related
Jul 2, 2007
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
View 1 Replies
View Related
Feb 4, 2008
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
Thanks
View 4 Replies
View Related
Aug 10, 2015
I am using report builder 3.0.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
View 5 Replies
View Related
Dec 3, 2015
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
View 3 Replies
View Related
Apr 22, 2015
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
View 3 Replies
View Related
Jan 13, 2014
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
View 5 Replies
View Related
Jan 11, 2007
Hi
In 2000 is it possible to list second parameter based on selection
in the first parameter list
Cheers
View 10 Replies
View Related
Apr 17, 2008
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".
I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.
The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...
Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null
if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions
...later as part of an else it does the following...
Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end
...and before the return it's value is inserted to different tables.
View 5 Replies
View Related
Nov 8, 2007
I have created a menu report listing available reports and am setting the Jump to settings of each text box in the list to take the user to the relevent report. But I am having a problem setting up drill down to reports with multivalued parameters.
What I want to happen when the user drills down is for the report to open up with no parameter values selected. This works for single value parameters where I have set Null as the default value. But I have not been able to figure out how to do this with MVPs. Does anybody know if this is possible?
As a workaround I have been using Jump to URL (rather than Jump to report), which has no problem opening the report without having to pass any values for the MPV. But I do not like using this as I use different DEV/UAT and PROD environments so would have to amend the URL for each environment.
Seems like this should be possible... Any idea?
Cheers, Mike
PS: I should add I can Jump to reports with MVP without specifying a parameter in development studio but not when the report is published.
View 6 Replies
View Related
Apr 30, 2007
Hi All.
I have a parameter (hidden) that gets its value using an expression base on another parameter.
When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.
When I change the parent parameter again, the value of the child parameter does not seem to change.
How can I make this parameter change automatically when the parent is changed ?
Any help will be appreciated.
Thanks,
Neil
View 3 Replies
View Related
Apr 10, 2007
Hi,
I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.
The idea is the create snapshot nightly and then display the report filtered based on parameters to users.
I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,
Right now I defined it this way :
Left =Fields!RegionCode.Value
Operator =
Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)
I do this for the 4 parameters that are passed, if they are null, make then equals to the field.
I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.
Thanks
View 5 Replies
View Related
Sep 25, 2006
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
View 2 Replies
View Related
Mar 15, 2008
I know i can accomplish this by setting a session variable on page load on the server side (Session["UserName"]= User.Identity.Name) and then use a session parameter, but I was wondering how to do it without using session variables, i was hoping the following code would work, but it doesn't. Thanks for any ideas.
" Name="UserName" Type="String" />
View 3 Replies
View Related
Jul 24, 2007
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
View 11 Replies
View Related
Jul 27, 2006
Hi everyone,
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original
- sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]
(
@status varchar(25),
@course varchar(100),
@datesmalldatetime
)
AS
SELECT
A1.uLastName,
A1.uFirstName,
A2.mName,
A3.tStatus,
A3.tScore,
A3.tStartDate,
A3.tCompleteDate,
A4.cName
FROM VSALCP.dbo.[User] as A1
INNER JOIN FSDLMS.dbo.Student as A5
ON A1.uID = A5.stID
INNER JOIN FSDLMS.dbo.Transcript as A3
ON A3.t_FK_stID = A5.stID
INNER JOIN VSALCP.dbo.Member as A2
ON A2.mID = A1.u_FK_mID
INNER JOIN FSDLMS.dbo.Course as A4
ON A4.cID = A3.t_FK_cID
Where @status = A3.tStatus ...
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @date smalldatetime )AS SELECT A1.uLastName, A1.uFirstName, A2.mName, A3.tStatus, A3.tScore, A3.tStartDate, A3.tCompleteDate, A4.cName FROM VSALCP.dbo.[User] AS A1 INNER JOIN FSDLMS.dbo.Student AS A5 ON A1.uID = A5.stID INNER JOIN FSDLMS.dbo.Transcript AS A3 ON A3.t_FK_stID = A5.stID INNER JOIN VSALCP.dbo.Member AS A2 ON A2.mID = A1.u_FK_mID INNER JOIN FSDLMS.dbo.Course AS A4 ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ...
"If status = ALL select * status"
Thanks for taking a look!
View 3 Replies
View Related
Mar 17, 2006
Hi,
I am not sure this is the correct place to post this
question, but here it is. I am trying to pass some parameters to SSIS
from a report using the report parameter, then SSIS will create the
datareaderdest and return to the report to use.
Anyone have any idea, guidance or leads please share it out. Thanks in advance.
Daren
View 9 Replies
View Related
Apr 21, 2008
Is it possible to include a text/string as suffix(append to the end of textbox) to an report parameter prompt string ? Something like this
Enter Date: [ Textbox ] format:mm/dd/yyyy
Enter Age:[Textbox ] e.g 50
I need to include text like the one that I have highlighted in blue. Could someone help me out?
View 7 Replies
View Related
Jan 9, 2007
Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.
I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?
Thanks!
Terry B
View 1 Replies
View Related
