SQL Task - Passing In A Parameter - Now Rows Returned
Mar 29, 2007
Hi,
am trying to do something which I thought would be simple to do in SSIS, several hours am still struggling with it. Not sure if this a bug or a restriction of the product. Or if im hitting some kind of compatability issue because im trying to get to a Oracle database.
Have a sql task which passes in a parameter, I then query my Oracle database and am trying the result (single row) into another variable.
Variable:
Variable Name = Subsystem
Scope= Package
Value = pgc
Data Type = string
SQL:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?
Have also tried:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?0
Result Set = Single Row
Parameter Mapping:
VariableName = User:ubsystem
Direction = Input
Data Type=Varchar
Parameter Name= 0
Parameter Size= -1 (have also tried 3 - length of variable)
Oracle Table:
SQL> desc sys_subsystem
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBSYSTEM_ID NOT NULL CHAR(3)
SUBSYSTEM_DS NOT NULL VARCHAR2(40)
....
....
...
The Error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "SubsystemName": "Single Row result set is specified, but no rows were returned.".
I have another SQL Task that performs an update on this same table and I also pass in the same variable but it works?
SQL:
UPDATE sys_subsystem
SET as_process_fg = 'X'
WHERE subsystem_id = ?0
Parameter Mapping Variable Name Direction Data Type Parameter Name User::Variable2 Input LONG 0
When executing the SSIS Package I get the Following Error
SSIS package "Test.dtsx" starting.
Error: 0xC002F210 at Update Company ID and Name, Execute SQL Task: Executing the query "Update NewFile
Set CompanyID = 'S',
CompanyName = 'SA',
CustomerName = 'SA TEST',
CustomerCode = ?
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am trying to exectue SQL task as below by passing a parameter
If I try....
@v1 datetime
set @v1 = convert(datetime, ? ,103)
it fails with below error
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
however the below code works well
delete from t1 where last_update = convert(datetime, ? ,103)
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything
create table #temp ( zoneid bigint ) insert into #temp values(100000000000000000) insert into #temp values(100000000000000000) insert into #temp values(100000000000000000) insert into #temp values(100000000000000000) insert into #temp values(200000000000000000) insert into #temp values(200000000000000000)
I have a SSIS Execute SQL Task that calls a stored procedure with a date parameter. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. Setting ByPass Prepare to True does not affect the outcome. I also used the query directly in the SQL task itself to no avail. Executing the query in Query Analyzer works. Any assistance would be greatly appreciated.
64 bit SQL 2005 running on Windows Server 2003 X64
I have an exececute SQL task (in the control flow obviously)
SELECT MAX(last_update) AS OrdersLastUpdateFROM orders
This task executes successfully and I can see that my user variable called "User:tmOrdersLastUpdate" populates correctly in the "variables" pane.. ALL GOOD.
The next step of the Control flow is a dataflow task
Details
DataFlow Source = DataReader Source (MySQL .NET connector) DataFlow Dest = local SQL Server OLE DB.
In the DataFlow Source the DataReader SQLCommand property is Select * from orders where last_update >= @User:tmOrdersLastUpdate
I've tried every conceivable permutation and I can't get SSIS to itnerpret the variable as such...it always gets passed to the server as a literal.
How do I pass a user-defined global variable to the WHERE clause in a DataRader object?
Hi, this might be a simple one, but I have been stuck on it for days. I am just getting into SSIS and have been muddling through it for the rest of this package but I am stuck on this. I am using this SQL Execute Task to run some lookup queries and then call the sp_send_dbmail stored procedure. I have this placed in a For Each loop container. I am using a SELECT DISTINCT Branch FROM table1 into an Object parameter, and passing that into the loop container. I am then using a Input Parameter into this SQL Execute Task of type String. I have run a Script task right before this step to ensure that the variable is populated and correct. Any assistance would be greatly appreciated!
Here is the SQL Execute Task :
DECLARE @SQL varchar(2400), @emaillist varchar(200), @branchMgrEmail varchar(100), @officeMgrEmail varchar(100), @branchMgrEmpNo varchar(5), @officeMgrEmpNo varchar(5), @subjectline varchar (100), @Today varchar(10), @BranchNumber varchar(2) SET @BranchNumber = ? SET @Today = convert(char(8),getdate(),1) SET @SQL = 'SELECT rtrim(CONVERT(char(10), PostedDate, 101)) AS Posted_Date, CAST(Branch AS CHAR(2)) AS Branch, CAST(Department AS CHAR(2)) AS Department, CAST(InvoiceNumber AS CHAR(7)) AS Invoice_Number FROM onbase.dbo.MHC_IncompleteRepairOrders WHERE Branch = ' + @BranchNumber + 'AND HardCardCount = 0 AND WorkAuthCount = 0 AND QualityControlCount = 0 AND MiscDocsCount = 0' SET @subjectline = @Today + ' - Repair Order Validation Notification for Branch #' + @BranchNumber SET @branchMgrEmpNo = (SELECT branchempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber) SET @officeMgrEmpNo = (SELECT officeempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber) SET @branchMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @branchMgrEmpNo) SET @officeMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @officeMgrEmpNo) SET @emaillist = @branchMgrEmail + '; ' + @officeMgrEmail BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients = 'email@email.com', --@recipients = @emaillist, --@copy_recipients = 'email@email, @attach_query_result_as_file = 0, @subject = @subjectline, @body = 'This email contains a list of Stuff
This is a copy of the ERROR message that I am receiving. Any suggestions?
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Send email to Branch Mgr and Office Mgr
I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:
Variable name: User::serverName
Direction: Input
DataType: Varchar
Parameter Name:0
and the sql is the following:
CREATE LOGIN [?ASPNET] FROM WINDOWS
But I get the error:
Executing the query "CREATE LOGIN [?ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:
1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )
2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
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 .
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.
How can I pass the value of the count(*) to outside the execute. I need the value to continue with the sp
set @sql= 'declare @res int select @res=count(*) from t where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
???????? set @result=exec (@sql) ????????????? Something like this
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
The code below returns 0 rows. The statement is intended as generic statement that would return all records between a particular start and end date and on each date only between a specific start and end time. It works perfectly if the end date is greater than the start date. Unfortunately, if the start and end dates are equal (i.e, return all records on that one date and only between the specified start and end times) then no records are returned. BTW, it is also looking for matching datetimes in two tables and there really is matching data in the two tables for that particular date and times. Can anybody help resolve this?
SELECT DISTINCT t1.* FROM [DbName].[SchemaName].[TableName1] AS t1,
[DbName].[SchemaName].[TableName2] AS t2
WHERE t1.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME)
AND t2.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar, t1.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
CONVERT(DATETIME,CONVERT(varchar, t2.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
I have a qry/dataset in a report. If the qry returns no data the report will simply show a the headers/footers and no data in the detail section. However, I need to display all zeros if the detail section ie '0', if no data is returned. How can I do that? I have SSRS 2005.
Hello and thank you taking a moment. I have created a simple login page where the user will pass credentials. I have a sqldatasource that will query a database to see if the user exists. What I would like to do is have the user click a button after entering their credentials. When the button is clicked I would like the SqlDatasource SelectCommand to fire and if there are rows returned then redirect the user to a new page. I know I can do this with ADO and a datareader with the HasRows property. But what I would eventually like to do is cache the data and then bind the cached data to a control(like a dataview) on the page the user is redirected to. If anyone can tell me how to get the Select command to fire on a button click I would be eternally grateful. Any help will be greatly appreciated.
I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?
I have a large table with approx 250000 rows in sql 2000. I need toreturn this from an asp page but the query to return this amount ofdata causes the asp to time out before the query completes.Is there any way in ado (or another way useable from asp / vb) that Ican run the query and then fetch a number of rows at a time? I can thene.g. pass the first batch of rows back to the client and then call forthe second batch.Any thoughts appreciated.
Hi,I have a select statment that correctly returns zero rows at times. Iwould like to be able to return the value 0 (a single row with thevalue 0) whenever the logic returns zero rows.something like thisIf no.of.rows.returned = 0 thenoutput 0elseoutput query resultsend ifCan anyone poing me in the right direction to do this?many thanks,yohan
I running a query that returns a information from many tables. I need a way to compare the row returned to the previous row. This query currently returns the requested information for everytime an item placed. I need the query to return only the most recent order information. I can do this by doing a basic loop comparing the jobmatl.item and po.order_date. If the jobmatl.item match between the current row and last row compare the dates and keep the oldest.
Declare @assembly ItemType
SET @assembly = '1110-2014'
SET @assembly = ISNULL(@assembly, '%')
SELECT DISTINCT jobmatl.item , item.description , item.drawing_nbr , jobmatl.matl_qty , itemloc.qty_on_hand , vendaddr.name , po.order_date FROM jobmatl with (nolock) inner join item with (nolock) on item.item = jobmatl.item inner join job with (nolock) on jobmatl.job = job.job inner join poitem with (nolock) on item.item = poitem.item inner join po with (nolock) on poitem.po_num = po.po_num inner join vendaddr with (nolock) on vendaddr.vend_num = po.vend_num inner join itemloc with (nolock) on itemloc.item = item.item WHERE jobmatl.job < ' 990000' AND job.item LIKE @assembly ORDER BY jobmatl.item , po.order_date
I have a report that calls a stored procedure. I want to display the number of records that the stored procedure returned. I can't seem to find an expression that will do this. Is there an expression that will display the record count for a dataset?
When I set the SecondOperand to /* the output is a concatenated text string of DeviceID and UserID values. I'm trying to get just the DeviceID but perhaps my understanding of XPath syntax is wrong. I've tried setting the SecondOperand to the following:
The following query only brings back too much data in the table - there are duplicates and the lab it shows is wrong. The problem is: AND a.calLab = f.ID in the Where statement. Some equipment does not need calibration, so, a.calDueDate = 0 AND a.calLab would be NULL or 0. tblLabs ID field has 1 - 18 (Labs) - no 0 and of course no null I need to get the rest of the data. Any suggestions? Thanks..... Zath
SELECT a.assignedID, b.Manufacturer, a.modelNumber, a.serialNumber, e.equipmentType, a.Description, c.Location, d.Status, a.modifiedDate, a.modifiedBy, a.Notes, a.Picture, f.LabName, a.calibrationRequired, a.calDate, a.CalDueDate, a.assetNumber, a.ID FROM tblEquipments a, tblManufacturers b, tblLocation c, tblStatus d, tblEquipment_Type e, tblLabs f WHERE a.manufacturer = b.manufacturerID AND a.location = c.locationID AND a.Status = d.statusID AND a.EquipmentType = e.ID AND (a.calLab = f.ID or a.calLab Is NULL or a.calLab = 0) ORDER BY a.ID
hello, i have a stored procedure SELECT CommentID, UserName, CommentingDate FROM Comm WHERE PictureID = @PictureID ORDER BY CommentingDate DESC witch shows me the users who commented a Picture with PictureID = x I need to add two rows at that stored procedure, one to show the number of total comments at that picutre (like counting the number of rows returned) and the second to show count the DISTINCT users who commented that picture I tryied with COUNT but i have to use GROUP BY and i don't think this is good... I hope you understand... please help me, thanks
Is there a way to limit the number of rows returned by a SqlDataReader? I know I can do it by modifying the Stored Procedure, but I'd rather not modify a procedure that is used in multiple apps. I'm hoping there is something easy like setting SqlDataReader.RowsReturned = 100, but that might be too easy to hope for.
I have my SQLDataSource configured as shown in the picture. I ran the Execute Query and input an ID I know is in the database and it returned nothing. I ran into this probelm on another part of my site and i got it working by using 2 SQLDataSources, but im trying to keep the amount of code down.
As a part of my unpaid internship, I am creating a ASP.NET interface for a MS SQL Server 2000. The table I am having problems with has over 750,000 rows by 26 columns. There isn't a primary key. It stores a transaction dump from another primitive Database server.
Problem:
When perform a query I get one set of results. I run the same query again after a short wait and the rows returned are in a different order. The majority of the rows returned are the same ones returned in the previous query. Only, some rows may be missing and the order may change.
I really wasn't surprised by some duplicate rows. I am confused why they are showing up in a different order and the above mentioned inconsistancy in results.
I thought I was having problems with my Repeater Control; however, SQL Query Analyzer returns the same results.
