OLE DB Source Parameters Ignored When Subquery?
Feb 20, 2006
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.
Can anyone shed some light on this?
Thanks,
Laurence
View 4 Replies
ADVERTISEMENT
Jul 23, 2005
Hello all, I have a stored procedure with a parameter that I need touse in a subquery, but my query doesn't work.The stored procedure is some as:CREATE PROCEDURE dbo.Test@valore varchar(100)ASSELECT * FROM TABELLAWHERE i_campoID IN (@valore)ORDER BY i_campoID DESCGOHow can I do, without using EXECUTE command?
View 4 Replies
View Related
Apr 23, 2012
I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)
Example
table1
id - autonumeric
id_user - id from user
dates - date of register
table2
id - user id
name - user name
query1
SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user
FROM Table1
WHERE (((Table1.datess) Between [begining] And [ending]))
GROUP BY Table1.id_user
ORDER BY Table1.id_user;
subquery (query2)
SELECT query1.id_user, query1.CuentaDeid_user, Table2.name
FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;
This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?
View 3 Replies
View Related
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Jun 29, 2001
Hello,
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
Thank you
View 1 Replies
View Related
Nov 15, 2007
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.
What else do I need to do to set this up?
Thank you for your help!
cdun2
View 10 Replies
View Related
Sep 5, 2006
I want to query my excel source sheet with a paramater so i create a sql statement like
select * from xxx where name like 'fred%'
Then I want to parameterise it with
select * from xxx where name like @Name or select * from xxx where name like ?
both allow me to map the Parameter neither syntax works in the data flow or preview but ? still works in the build query window !
Anyone know the correct Param declaration for the Excel Source (i believe it should be oledb @Name declaration ?
is this a bug or am i missing something here ...
Cheers
Colin
View 3 Replies
View Related
May 11, 2007
Hi,
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!
View 5 Replies
View Related
Mar 10, 2008
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 ?
Any examples would be really appreciated
Thanks
Smx
View 13 Replies
View Related
Jan 12, 2007
Hi there!
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?
Thanks, Torsten
View 3 Replies
View Related
Jan 21, 2008
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.
View 3 Replies
View Related
Dec 27, 2006
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.
R
View 1 Replies
View Related
Mar 14, 2008
Hello All,
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.
Any thoughts?
Thanks,
Raymond
View 4 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Feb 8, 2007
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:
<soap:Body>
<GetXml xmlns="myreports.ws">
<root >
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
</root>
</GetXml>
</soap:Body>
My question is: how do I pass the xml to the web service from reporting services.
I have seen examples with simple types. They do it with this query:
<Query>
<SoapAction>myreports.ws/GetXml</SoapAction>
<Method Namespace="myreports.ws" Name="GetXml"></Method>
<Parameters>
<Parameter Name="Parameter1">
<DefaultValue>some value</DefaultValue>
</Parameter>
</Parameters>
<ElementPath IgnoreNamespaces="True">
GetXmlResponse{}/GetXmlResult{}/diffgram{}/NewDataSet/Element{@Attribute1,@Attribute2}
</ElementPath>
</Query>
But there are no example on using xml directly instead of parameters. Can anyone help me? Where do I put my xml?
View 3 Replies
View Related
Jul 5, 2006
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..
View 8 Replies
View Related
Apr 4, 2007
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?
Thank you.
View 3 Replies
View Related
Apr 4, 2006
Hi Guys,
(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...
View 23 Replies
View Related
Nov 29, 2007
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
"usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'"
which typically evaluates as
usp_ValveStatusForDay '18 Oct 07','Report_Name'
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))
IF @month = '1' SET @month = 'Jan'
IF @month = '2' SET @month = 'Feb'
IF @month = '3' SET @month = 'Mar'
IF @month = '4' SET @month = 'Apr'
IF @month = '5' SET @month = 'May'
IF @month = '6' SET @month = 'Jun'
IF @month = '7' SET @month = 'Jul'
IF @month = '8' SET @month = 'Aug'
IF @month = '9' SET @month = 'Sep'
IF @month = '10' SET @month = 'Oct'
IF @month = '11' SET @month = 'Nov'
IF @month = '12' SET @month = 'Dec'
SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00'
SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59'
--delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end)
exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h'
END
View 8 Replies
View Related
Dec 4, 2005
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
View 7 Replies
View Related
May 21, 2007
Hi guys,
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:
- despesaTableAdapter (from myDataSetTableAdapter)
- despesaBindingSource (Windows.Forms.BindingSource)
- RelatorioDataSet (from myDataSet)
- relatorioDataSet1 (from myDataSet)
Any help will be very appreciated.
Tks and rgds,
Luis Antonio
View 1 Replies
View Related
Feb 13, 2008
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.
View 1 Replies
View Related
Jul 9, 2006
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.
Am I missing an obvious workaround?
View 7 Replies
View Related
Jul 16, 2009
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:
<Query>
<Method Namespace="http://machine/webservices" Name="HelloUser">
<Parameters>
<Parameter Name="user">
<DefaultValue>Craig</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>
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?
View 5 Replies
View Related
Jun 7, 2007
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.
Thanks very much in advance for your help.
Regards,
Steve
View 7 Replies
View Related
Oct 26, 2007
Hi,
I'm trying to use query parameters with an Oracle OLEDB Source in a data
flow task and I'm having problems.
I've tried formatting the query each of the following ways...
--
select
frq_code,
frq_name,
update_frq,
uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?
--
Parameters cannot be extracted from the SQL command. The provider might not
help to parse parameter information from the command. In that case, use the
"SQL command from variable" access mode, in which the entire SQL command is
stored in a variable.
Additional information
---> Provider cannot derive parameter information and SetParameterInfo has
not been called. (Microsoft OLE DB Provider for Oracle).
View 7 Replies
View Related
Nov 29, 2010
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:
<Query>
<Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">
<Parameters>
<Parameter Name="Criteria" Type="XML">
<DefaultValue>
[code]....
View 17 Replies
View Related
Jan 30, 2008
Hi All,
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:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:HelpDeskImportBook2.xls;Extended Properties=HDR=YES;EXCEL 8.0;HDR=YES";
Does anyone know why it appears that my connection string looks like it is being cut off and does anyone know how I can correct the problem?
Thanks,
Scott
View 8 Replies
View Related
Jan 19, 2007
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.
What's wrong in the workflow described?
View 3 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
