I need to use some parameters in an OLE DB Source like that:
select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= dbo.fkt_get_guelt_von (?)
and dbo.fkt_get_guelt_bis (posh_valid_to) >= dbo.fkt_get_guelt_bis (?)
The function returns a valid datetime. These parameters normaly works fine, but not with use as funtion-paramerters. I get feeble error messages (by trying to map the parameters) like that:
"Falsche Syntax in der Nähe von ')'. (Microsoft SQL Native Client)" - what means "Wrong syntax near ')'" and says nothing. If i replace the parameters with the mapped value (i.E. '200601') it works fine.
What is the reason for that? It there any solution or a workaround?
I will like to be able to call a DTS to which I pass in parameter the name and the path of a DBF file of which the function will be to create a table in my SQL server identical by the structure and the contents to the DBF. The problem is that I never know in advance the structure of the DBF. But, in DTS designer I am already obliged to inform the name and the structure of the target then to make the correspondences of column.
PS: I know how to pass parameters to a DTS via utility DTSRUN... / A... etc in order to provide him the name and the path of the DBF
Hello, I have a DataReader souce configured to an ADO.NET connection manager that uses a .NET ODBC Data Provider. The DSN configured in the connection manager uses the IBM DB2 ODBC Driver.
I have a package variable, the value of which I want to use in the WHERE clause of the query contained in the Data Reader source. The query is as follows:
SELECT T1."IRK_ACCT_CD", T1."IRK_COMPANY_NBR", T1."IRK_ACCT_NAME", T1."IRK_ADDRESS_1", T1."IRK_ADDRESS_2", T1."IRK_ADDRESS_3", T1."IRK_STATE_CD", T1."IRK_POSTAL_CD", T1."IRK_AR_TYPE", T2."RK502_ITEM_RE_NO", T2."RK502_TRANS_CD", T2."RK502_TRANS_TYPE", T2."RK502_ITEM_AMT", T2."RK502_ITEM_DT", T2."RK502_PURCH_ORD_NO", T2."RK502_GL_EFF" FROM "CBDBOW"."IRK_RECORD" T1, "CBDBOW"."RK502_OPEN_ITEMS" T2 WHERE T1."IRK_COMPANY_NBR" = T2."RK502_COMP_NO" AND T1."IRK_ACCT_NUMBER" = T2."RK502_ACCT_NO" AND T2."RK502_ITEM_DT" < ?
I'm not very sure as to how to map the package variable this way. I think for ODBC parameters, I need to represent the parameter with a question mark.
I am using an OLE DB Source with SQL command for the data access mode. I defined parameters, then added a complex query with subqueries. It seems like the parameters for a query are not being filled correctly (because I am getting too many rows returned). At one point I saw an error message the said something to the effect that the parameters would be ignored when there was a subquery in the SQL.
Firtsly - I am new to SSIS if my approach could be improved then I welcome suggestions.
Scenario: I have a large SSIS package that consolidates / summarizes work week information from several data sources. Currently each data flow task in the control flow calculates the from and to date that is filtered on, for example:
DECLARE @FromDT AS DATETIME SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)
DECLARE @ToDT AS DATETIME SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)
I would like to remove these statements that appear in most steps and replace them with a global variable that is used throughout the package. This statement would only appear once & it would make the package much easier to run after failure etc.
Problem: I am using Data Reader Source with the 'SQLCommand' property specified. It looks like parameters are only supported if an OleDB connection is used?
So I switched to an OleDB connection and no parameters are recognised in the string - a forum search reveals that parameters in sub queries are not always found properly. The solution to this problem appears to be, to set 'Bypass Prepare' to True but this is a property for the Execute SQL task, not the Data Flow Task source.
Questions:
Does the Data Reader Source control from Data Flow Source toolbox section support parameters? Can anyone suggest a fix to the OleDB Source issue with Parameters? Is there a better way to solve my problem e.g. Using Execute SQL Task instead of Data Flow tasks etc
Example SQL:
This SQL is an example of the SQL for the OleDB Data Source (within a Data Flow task)
------------------------------ --RADIUS LOGINS ------------------------------ DECLARE @FromDT AS DATETIME SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)
DECLARE @ToDT AS DATETIME SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)
DECLARE @Attempts AS BIGINT SET @Attempts = (SELECT COUNT(*) FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT)
DECLARE @Failures AS BIGINT SET @Failures = (SELECT COUNT(*) FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT AND Authen_Failure_Code IS NOT NULL)
DECLARE @Successes AS BIGINT SET @Successes = @Attempts - @Failures
DECLARE @OcaV1Hits AS BIGINT SET @OcaV1Hits = (SELECT COUNT(DISTINCT LoginName) FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT AND EAPTypeID = 25)
DECLARE @OcaV2Hits AS BIGINT SET @OcaV2Hits = (SELECT COUNT(DISTINCT LoginName) AS OcaV2Hits FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT AND EAPTypeID = 13)
SELECT @Attempts AS ConnectionAttempts, @Failures AS ConnectionFailures, (CAST(@Successes AS DECIMAL(38,2)) / CAST(@Attempts AS FLOAT) * 100) AS SuccessRate, @OcaV1Hits AS OcaV1Hits, @OcaV2Hits AS OcaV2Hits
Please remember, I'm new to SSIS - so be detailed in your response. Thanks for your help!
I have an OLE DB Source that talks to an Oracle Db and returns back some data. I need to be able to pass some parameters to my query from the SSIS variables.
How can accomplish this ? Do I need to create a stored proc in my Oracle DB..is that the only way I can pass parameters ? Is so how ?
I writed That CodeDim SqlCom As New SqlDataSource SqlCom.ConnectionString = "data source=localhost;initial catalog=Mansouria1.1;integrated security=True" SqlCom.DataSourceMode = SqlDataSourceMode.DataReader SqlCom.InsertCommandType = SqlDataSourceCommandType.StoredProcedure SqlCom.InsertCommand = "Add_Machine_Component" SqlCom.InsertParameters.Add("@Name", TextBox1.Text) SqlCom.Insert() and the store Procedure@Name Nvarchar(150)ASInsert into Machine_Component(Name) values (@Name)Go That error Message displayprocedure or Function 'Add_Machine_Component' expects parameter '@Name', which was not supplied.
Does anyone have any experience setting up paramters when using a Teradata data source? I am specifically looking for how to set up dates and multi-select drop down boxes as parameters. Using a fixed query against Teradata using SSRS is straight forward and is working well. Parameters, not so much.
I understand that the parser is having issues parsing my second command but I was wonder if anyone had stumbled on a work around for this:
-- OLEDB Source has no problem with this: SELECT * FROM some_table where last_update_dt = ?
-- OLEDB Source errors on this: SELECT * FROM some_table where Convert( char(12), last_update_dt, 112 ) = Convert( char(12), Cast( ? as DateTime), 112 )
The first SQL statement won't return the desired rows because of the wonderful time stamp, the second works well but the provider won't parse it. I have used the "SQL command from variable" to work around this but we have some tables with 200+ columns, so the only way to use the "SQL command from variable" is to do a select *, which I'm trying to avoid, for both performance reasons and company standards.
I am building a report that takes a web service as data source. This web service does not take primitive types as parameters. The signature is something like this:
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)
I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode?? Can you any one please help me in this regard and trouble shoot this problem..
In the data tab of my report (Reporting Services for SQL Server 2000), I'd like to run a query that requires parameters be passed to it. Is there a way to pass the parameters of the report to that query?
(I have searched this forum extensively, but still can't find the solution to this problem)
Here it is:
I have step in my ETL process that gets facts from another database. Here is how I set it up:
1) I have to package variables called User::startDate and User::endDate of data type datetime
2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine)
3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate)
When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping."
It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?"
To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well.
Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message.
This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...
This seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things).
Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is
This previews correctly and the resulting columns are available for mapping to a destination. So far so good.
By the way, is this the best way to call a stored procedure with parameters?
I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed.
Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4.
I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it.
At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table.
It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one?
ALTER procedure [dbo].[usp_ValveStatusForDay]
(
@dateTime DateTime,
@reportName VarChar(100)
)
AS
BEGIN
DECLARE @day VarChar(10)
DECLARE @month VarChar(10)
DECLARE @year VarChar(10)
DECLARE @start VarChar(25)
DECLARE @end VarChar(25)
SET @day = Convert(Varchar(10),DatePart(day, @dateTime))
SET @month = Convert(VarChar(10), DatePart(month, @dateTime))
SET @year = Convert(VarChar(10), DatePart(year, @dateTime))
Hi, I wish to create a user defined funtion in sqlserver2005 with optional parameter list. So at the time of function calling the parameters should be a optional one. How can i do this? please help me .
I am writing a set of functions and then a stored procedure to allow me to view some data in Reporting Services. I have written the first part of the function as shown ;
WHERE (datadate = @dt_src_date) AND (siteref = @vch_site_ref)
RETURN
END
I have then written a stored procedure which picks up the result set from the function and ultimately will deliver it to Reporting Services. The problem I have is that as soon as I run the CREATE PROCEDURE script, I get an error saying ;
Msg 216, Level 16, State 1, Procedure spWTRWeeklyTerrierData, Line 16
Parameters were not supplied for the function 'fnWTRTerrierData'.
How can I add parameters to the function sufficently so that I can run the Create procedure element of my code?
How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
I have three databases which have the identical data structure. When the user runs a report, he has to select a database. For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list? In my datasource, I have only specified the server name and no value for initial catalog.
In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.
Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.
I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.
I have an SP that is working, it creates a Union between 3 tables then creates a single table - I am using Visual studio Grid Control to view this by a browser, its working. at the bottom of the SP is a line of code that calls a function, the function queries a View. When I run the SP, SQL returns the data from the Union plus the data from the select statement on the Function. This all works but I can't get the data returned by the function query onto a browser.
I am doing it this was as the parameters in my SP are also used when calling the function, works really well. Is GTotal a return value?
Here is my function:
USE [LOGONs] GO /****** Object: UserDefinedFunction [dbo].[FX_AnnaulBudget1] Script Date: 23/09/2015 12:27:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
...and here is my SP:
USE [LOGONs] GO /****** Object: StoredProcedure [dbo].[USP_AnnualBudget] Script Date: 23/09/2015 12:57:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[USP_AnnualBudget]
I am building a webapp that calls an SSRS instance to display a report based on another servers web service.I can make a call from the ssrs server using this in the RDL:
This works no problem. but.. I want to have the user parameter "Craig" be a parameter passed in from the web app. If it were a regular SQL data source you would put @user in the query. How do you do it with XML text queries?
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.
Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:
I created a SSIS package to import an excel spreadsheet into my data warehouse. When I run the package it runs fine. When I created a SQL Job to run the package I get the following error:
Option "Source=D:HelpDeskImportBook2.xls;Extended" is not valid. The command line parameters are invalid.
Now if I look at my source connection string in the job it looks like the following:
Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used. I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works. ALTER PROCEDURE [dbo].[LinksFees_Record] @Date datetime = getdate AS INSERT INTO LinkSearchFees (LinkID, Price, [Date]) SELECT ID AS LinkID, SearchDayFee AS Price, @Date FROM Links WHERE (SearchDayFee > 0)
RETURN When I call the StoredProcedure the following exception occur: Conversion failed when converting datetime from character string. How can I fix it?
I have a CLR function that throws an error if one of the parameters is NULL. Am I using the IsNullable tag correctly or am I supposed to do this another way? The function simply formats decimal values using .NET culture information and returns a string. Thanks very much for any help. -- Erik
It's well known issue, that one can't use any dataset fields in a report header/footer directly. One of the approach is to create query-based parameter that basically equals =First(Fields!@FieldName@.Value, "@DataSetName@") and use that parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with EntityID param. Its header contains entity name that, according to the approach, is queried from the data source through the EntityName report parameter. There's important issue: the report is displayed in ReportViewer control, that is embedded into my application and entity ID parameter isn't ser by user in ReportViewer parameters area. Its default value is changed by the application with SetReportParameters() web method every time a user wants to view the report according to the entity the user is exploring in the application. But after the report has been rendered, its header always contains not actual (outdated) entity name. Nevertheless, the report body contains actual data (including entity name). If I alter entity ID parameter in ReportViewer or in web-based Report Manager and refresh report, header displays correct entity name.
