Wahts the right way to specify a parameter in an Execute SQL Task when
the param is a varchar? should I use apostrophe's around the ? or not?
I have two SQL tasks .. one where the query is specified like :
WHERE FIA.BaseCurrency = ?
Which SSIS likes and doesn't complain about...(the but then I
have another query.. [below].. which SSIS complains about unless I have
apostrophes around the ?
So its like:
AND FIA.BaseCurrency = '?'
So.. which should it be?
The problem for me here is that when I DO put the apostrophes around the ? I get no results back from the query...
PJ
The problem query:
SELECT ForeignCurrency, RateFromFile AS YesterdaysRate
--The ISNULL just protects us from situation where a rate is not found,
-- which could occur if the last loaded rates file was missing the rate for the
-- foreign currency we are checking.
FROM inputrates IR
WHERE fileheaderid in (
-- this gives the ID's last set of rates loaded on
-- whatever was the previous day (ie on a monday the previous load day was friday)
SELECT top 1 MAX(ID)
FROM FileInputAttempts FIA
WHERE Source = '?'
AND FIA.BaseCurrency = '?'
AND status = 'SUCCESS'
AND CAST(FLOOR(CAST(LoadDate AS
float))AS datetime) <= CAST(FLOOR(CAST(Dateadd(day,-1,getdate()) AS
float))AS datetime)
Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)
order by MAX(loaddate) DESC
)
I have a SSRS report that I'm passing a dynamic parameter to another SSRS report. The datasourse is a SSAS cube. Can anyone tell me the if following syntax is correct for receiveing report. I'm having trouble creating the datasource for the second report. I'm new at SSRS so any help would be appreciated.
When I parse this code in management Studio I get the following An MDX Statement was expected. An MDX expression was specified.
'= [SELECT NON EMPTY { [Measures]].[Qty Paid]], [Measures]].[US Cost]], [Measures]].[Qty Recd]] } ON COLUMNS, NON EMPTY
Hi Help with syntax, I get the error in the line: myDA.Fill(ds, "t1") Function GetProductsOnDepartmentPromotionPaging(ByVal departmentId As String) Dim myConnection As New _ SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myDA As New SqlClient.SqlDataAdapter _ ("MM_SP_GetProductsOnDepartmentPromotion", myConnection)
' Add an input parameter and supply a value for it myDA.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int, 4) myDA.SelectCommand.Parameters("@DepartmentID").Value = departmentId Dim ds As New DataSet Dim pageds As New PagedDataSource myDA.Fill(ds, "t1") pageds.DataSource = ds.Tables("t1").DefaultView pageds.AllowPaging = True pageds.PageSize = 4 Dim curpage As Integer If Not IsNothing(Request.QueryString("Page")) Then curpage = Convert.ToInt32(Request.QueryString("Page")) Else curpage = 1 End If pageds.CurrentPageIndex = curpage - 1 lblCurrpage.Text = "Page: " + curpage.ToString() If Not pageds.IsFirstPage Then lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + _ "?Page=" + CStr(curpage - 1) End If If Not pageds.IsLastPage Then lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + _ "?Page=" + CStr(curpage + 1) End If list.DataSource = pageds list.DataBind() End Function
Can anyone help me out with using parameters in my SqlDataSource? I have my UpdateCommand set as follows: UpdateCommand="UPDATE Custom_ContainerRequest_ESTWEIGHTS SET WEIGHTRANGES = @WEIGHTRANGES WHERE ESTWEIGHTS_ID = @original_ESTWEIGHTS_ID" <UpdateParameters> <asp:Parameter Name="WEIGHTRANGES" Type="String" /> <asp:Parameter Name="original_ESTWEIGHTS_ID" Type="Int32" /></UpdateParameters> And the application is just hanging when I run it. (No error message.) If, however, I hardcode the values instead of using parameters, it works just fine everytime. UpdateCommand="UPDATE Custom_ContainerRequest_ESTWEIGHTS SET WEIGHTRANGES = 'test' WHERE ESTWEIGHTS_ID = 1" So I don't know if it's a single-quote issue, or if there's some other syntax I need, or what. One last note is that this is a DB2 database I'm using. But I'm sure that my connection is valid because I can read and update data...only not using parameters. Any help is greatly appreciated. Thanks,Billy
I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','
How do I pass my stored procedure value into my query? My first example works, in the second example my parameters are contained in the sql string. How do I expose parameter values to the inner sql string?
Thanks for any help.
Kevin
============================================== This works: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectTest] @StartDate datetime, @EndDate datetime AS BEGIN SET NOCOUNT ON; SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE DownloadDate BETWEEN @StartDate AND @EndDate END
This does not: INSERT INTO ReportTable (ItemDate, ProjectNo, ProjectName, Category, Type, Amount, RevExp) EXECUTE ('SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE [Project Number] > 0 AND DownloadDate BETWEEN @StartDate AND @EndDate ');
As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.
I can do this in two separate Execute SQL tasks as follows:
Task 1 syntax
DECLARE @maxDate datetime SELECT @maxDate = max(dtLastChangedDate) FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging SET PreviousFilterValue = CurrentFilterValue, CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue), DateSourceTableLastRead = GetDate(), RowsReturned= -1 WHERE SourceTableName = 'cancel'
Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0
UPDATE dbo.tblLogging SET RowsReturned= ? WHERE SourceTableName = 'cancel'
However I cannot make this work with a single SQL statement such as
DECLARE @maxDate datetime SELECT @maxDate = max(dtLastChangedDate) FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging SET PreviousFilterValue = CurrentFilterValue, CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue), DateSourceTableLastRead = GetDate(), RowsReturned= ? WHERE SourceTableName = 'cancel'
because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.
Is this behaviour by design, is it a bug, or is there something I've missed?
I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
Could someone please help me!!! I am using a multi-value parameter in SQL 2005 reports and am getting the following error message:
An error has occured during report processing. Qiery execution failed for dataset an expression of non-boolean type specified in a context where condition is expected, near ',' Incorrect syntax near keyword else.
The multi-value parameter works when it isn't run in the if, else clause i checked the where clause with a single paramter and it works OK. I don't understand what is causing this problem but I really need to fix it. Here is my query.
if @job_SubRep_ProjNo_param = '0'
Begin select Job_Job_No as job, Job_Job_Name as Job_title, cast(Job_Total_Fee as float) as fee, employee_first_name + ' ' + employee_surname as jl_name, cast(Job_Percent_Complete as float) as percentcomplete, cast(Job_Work_Done as float) as workdone, cast(Job_Invoicing as float) as job_Invoicing, job_WIP, Job_Expenditure as timecost, job_project_no, Job_Profit_Loss, cast(Job_Hours as float) as hours, job_expenses from job_tbl inner join project_tbl on job_project_no = project_no inner join employee_tbl on job_jl_empno = employee_no where project_pl_empno = @pl_employeeNo or job_jl_empno = @jl_subRep
end else select Job_Job_No as job, Job_Job_Name as Job_title, cast(Job_Total_Fee as float) as fee, employee_first_name + ' ' + employee_surname as jl_name, cast(Job_Percent_Complete as float) as percentcomplete, cast(Job_Work_Done as float) as workdone, cast(Job_Invoicing as float) as job_Invoicing, job_WIP, Job_Expenditure as timecost, job_project_no, Job_Profit_Loss, cast(Job_Hours as float) as hours, job_expenses from job_tbl inner join project_tbl on job_project_no = project_no inner join employee_tbl on job_jl_empno = employee_no where job_project_no in (@job_SubRep_ProjNo_param) or job_jl_empno = @jl_subRep
We're currently trying to evaluate SQLJDBC 2005 1.1 June CTP's support for database mirroring automatic failover. Unfortunately we're getting unexpected exceptions for calls that work fine w/ jtds that our blocking our ability to perform these evaluations without us making substantial changes to our codebase.
The first issue is with the name used when calling a stored procedure -- SP names that start with "dbo." give us the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. src:{call dbo.xyz(?,?,?,?,?,?,?,?)}
The call will work if we change the SQL statement to {call xyz(...)}. I don't understand why we would need to do this, especially given that the documentation for the driver shows call statements with the "dbo." prefix.
We're also having problems using named parameters with stored procedures (for both in and out parametes). Our code has parameter names of the form "@param" as is standard with TSQL (and is required when using jtds). However, this won't work with SQLJDBC -- it only seems to accept parameter names w/o the leading "@". Why is this so?
Finally, we were able to cause a NullPointerException within the driver due to an incorrectly built Properties object that contained an Integer for loginTimeout instead of a String: java.lang.NullPointerException at java.util.Hashtable.put(Hashtable.java:396) at java.util.Properties.setProperty(Properties.java:128) at com.microsoft.sqlserver.jdbc.SQLServerDriver.fixupProperties(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.mergeURLAndSuppliedProperties(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source) While this was due to a bug in our code I would think that such common errors would be better handled.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring")) Dim cmdSQL As New SqlCommand(strSQL, myConnection)
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
) ) ) ) ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql): select ID, FName, LName if(SUBSTRING(FirstName, 1, 4)= 'Mike') Begin Replace(FirstNam,'Mike','MikeTest') if(SUBSTRING(LastName, 1, 4)= 'Kong') Begin Replace(LastNam,'Kong,'KongTest') if(SUBSTRING(Address, 1, 4)= '1245') Begin ......... End End
end
Case Statement might be the solution but i could not do it.
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!!
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 .
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?
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.
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.
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
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
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?
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
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.
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.
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.
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _ & "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0")) buildDD(sql, ddlPerNames)
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?
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
