Execute SQL Task With No Rows Affected
May 20, 2007
Hi,
I used with Execute SQL Task for update a table in Oracle DB.
I saw that when the command has no rows for updeting, the task fails.
Here is my command:
update tableName set fieldA=sysdate where fieldB is Null
and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.
I tried to play with the ResultSet with no success.
Please your advice.
Thank you in advance
Noam
View 4 Replies
ADVERTISEMENT
Feb 27, 2007
hi frnds,
im very new to SSIS package .my package consists of Single Exceute task.
In Single Exceute SQL task i had 3 seperate queries to get the count of rows of 3 tables.
the Query goes like dis ...
select count(*) AS precheckcount1 from new_main_dts where cust_nbr like '875%'
like dis for another 2 tables i had written with the alias name precheckcount2 and 3
i mapped the variable user::precheckcount1 to precheckcount1 in the resultSet and for other two alias name i did the same.
while executing the package the error is thrown : [Execute SQL Task] Error: An error occurred while assigning a value to variable "precheckcount2": "Unable to find column precheckcount2 in the result set.".
please help me its very urgent
View 3 Replies
View Related
Dec 5, 2006
I use business logic layer, TableAdapter(middle tier) and StoredProcedure(backend). In my stored procedure,I set as follows:set nocount offWhat and where should I do in order to get the rows affected count in the business logic layer(C#/VB code)?Thank you.
View 2 Replies
View Related
Dec 20, 2006
I'm doing an executenonquery() and the value I'm getting back is -1 This is confusing me, as sometimes the row has updated when I look at it in sql manager and then I look at it again and it seems to have reverted.Would it be possible to point me in the right direction for the cause and how to rectify / debug this issue. I'm using a stored procedure and I'm slightly uncertain how to check exactly what sql server has been told to insert ;) Thanks!
View 1 Replies
View Related
May 6, 2008
After executing a restore command. I got the following result.
Anyone know why is displaying rows affected. I saw the executiong plan and it shows several execution plans? Its the first time i see it. Can anyone explain what is going on?
restore database test_GG_ATRECORDING_QAT2
from disk = 'f:a.bak'
with move 'a' to 'f:MSSQL$INAQATdataa2.mdf',
move 'a_log' to 'f:MSSQL$INAQATdataa2_log.LDF'
Processed 11016 pages for database 'test_GG_ATRECORDING_QAT2', file 'a' on file 1.
Processed 1 pages for database 'test_GG_ATRECORDING_QAT2', file 'a_log' on file 1.
(1 row(s) affected)
(7 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(11 row(s) affected)
(7 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
RESTORE DATABASE successfully processed 11017 pages in 2.330 seconds (38.732 MB/sec).
View 3 Replies
View Related
May 28, 2004
Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?
Any help would be appreciated!
Brian
View 1 Replies
View Related
Mar 13, 2006
I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...
SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application.
So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.
View 2 Replies
View Related
Dec 29, 2004
Hello,
I am using a cursor to navigate on data...of a table....
inside the while @@fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.
I declared a dynamic cursor but it does not work.
Does anyone know how I can do this??
Thanks :)
View 7 Replies
View Related
Aug 31, 2015
I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
View 2 Replies
View Related
Jul 23, 2007
When I use ExecuteNonQuery() with the stored procedure below it returns -1. However, when i tried to get rid of the if/else statements and just leave one insert statement for testing purposes, ExecuteNonQuery() returns the correct affected rows which is 1. So it seems like ExecuteNonQuery() doesn't work when the INSERT statement is inside the IF..ELSE. Can anybody help me with this problem? I haven't tried using @@RowCount because I really want to use ExecuteNonQuery() to do this because I don't want to rewrite my DAL. Thanks in advance -- With if/else ExecuteNonQuery returns -1ALTER PROCEDURE [dbo].[SP_AddObjectContribution] @ObjectId int, @FanId int, @DateContributed DateTime, @Notes nvarchar(512), @ObjectType intASBEGIN BEGIN TRAN IF @ObjectType = 2 BEGIN INSERT INTO FighterContributions (FighterId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 3 BEGIN INSERT INTO FighterPhotoContributions (FighterPhotoId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 4 BEGIN INSERT INTO OrganizationContributions (OrganizationId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 5 BEGIN INSERT INTO EventContributions (EventId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 6 BEGIN INSERT INTO FightContributions (FightId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 7 BEGIN INSERT INTO FightPhotoContributions (FightPhotoId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END COMMIT TRANEND -- Without if/else ExecuteNonQuery returns 1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@ObjectId int,
@FanId int,
@DateContributed DateTime,
@Notes nvarchar(512),
@ObjectType int
AS
BEGIN
BEGIN TRAN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@ObjectId, @FanId, @DateContributed, @Notes)
IF @@ERROR <> 0
BEGIN
ROLLBACK RETURN
END
COMMIT TRAN
END
View 7 Replies
View Related
Mar 15, 2004
I have a mixed mode account setup with exec permissions granted on my stored procedures. I am running an sp that is editing a member, and if i run it using my query analyzer with the same login, my sp writes to the db. however, if i'm calling it through my web app (asp.net) it doesn't. here is my sp code:
USE DATABASE
--DROP PROC sp_EditMember
GO
--Create the stored procedure
CREATE PROCEDURE sp_EditMember
@member_id smallint,
@last_name nvarchar(50), --not nullable
@first_name nvarchar(50),
@spouse_name nvarchar(50),
@street_address nvarchar(50),
@city nvarchar(35),
@state nvarchar(5),
@zip_code nvarchar(15),
@zip_4 nvarchar(4),
@area_code nvarchar(10),
@phone_number nvarchar(20),
@email nvarchar(50),
@child_1 nvarchar(30),
@child_2 nvarchar(30),
@child_3 nvarchar(30),
@child_4 nvarchar(30),
@child_5 nvarchar(30),
@member_status nvarchar(20),
@member_exp nvarchar(10),
@plaques_st nvarchar(10)
AS
BEGIN TRAN
UPDATE Members
SET last_name = @last_name, first_name = @first_name,
spouse_name = @spouse_name, street_address = @street_address,
city = @city, state = @state, zip_code = @zip_code, zip_4 = @zip_4,
area_code = @area_code, phone_number = @phone_number, email = @email,
child_1 = @child_1, child_2 = @child_2, child_3 = @child_3,
child_4 = @child_4, child_5 = @child_5,
member_status = @member_status, member_exp = @member_exp,
plaques_st = @plaques_st
WHERE member_id = @member_id
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE COMMIT TRAN
GO
--------------------------------------
on my app side- i've already ensured the datatypes match up and the sizes are all ok (except for member_id, the variable is an int and i pass it as a small_int- the number is ranging from 1000-10000).
any troubleshooting on this?
thanks in advance,
sudeep.
View 1 Replies
View Related
Jun 1, 2004
Hi,
I've added multiple records with same info during practice. Now I"m trying to delete those records from SQL Server DB. but it says
"Key column information is insufficient or incorrect. To many rows were affected by update."
What to do, to delete these records?
Appreciated..
View 2 Replies
View Related
May 15, 2015
I have a file which has some wind data that i am trying to import into a sql data base through bulk insert. if the script works as it supposed i should see 144 rows impacted but i see 0 rows affected.Â
BULK INSERT TOWER.RAWINTERFACE_1058 FROM 'C:Temp900020150427583.txt'Â
WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char',FIELDTERMINATOR=' ',ROWTERMINATOR='
',FIRSTROW=172)
The code works if the file is large but if its small 0 rows are affected. and also if i remove the header rows then the file works again. want to understand what is going on here. i am including the screen shot of the file in notepad++. I have tried changing the row terminator to ' ' , ' ' and also tried to change the codepage but nothing seems to work. No error file is being generated either, if i give a error file option.Â
View 7 Replies
View Related
Apr 11, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 1 Replies
View Related
Apr 5, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 5 Replies
View Related
Nov 7, 2007
Hi,
Using VS.NET 2008 Beta2, and SQL Server 2005.
I have a gridview bound to a linq data source, and when trying to update a row, I get an exception that no rows were modified.
The query generated is:
UPDATE [dbo].[package]
SET [owner_id] = @p5
WHERE ([package_id] = @p0) AND ([title] = @p1) AND ([directory] = @p2) AND ([owner_id] = @p3) AND ([creation_date] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [20006]
-- @p1: Input String (Size = 22; Prec = 0; Scale = 0) [Visual Studio.NET 2005]
-- @p2: Input String (Size = 26; Prec = 0; Scale = 0) [MSI_Visual_Studio.NET_2005]
-- @p3: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10000]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/07/2007 12:00:00 a.m.]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10001]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
If I run it manually on sql server, it fails until the directory column is removed. The type is varchar(50), with a uniqueness constraint. However, this is same type as the title column, which doesn't have this problem.
Thanks,
Jessica
View 5 Replies
View Related
Feb 8, 2000
I am using SQLDMO to implement a tool to do basic management/script running for a MSDE database. The problem i am having is getting the number of rows affected from insert,update,delete statements. Help and MSDN seem to imply that this info is returned in a QueryResults object, but it is always empty unless the query is a select statement. I tried using ExecuteImmediate, ExecuteWithResults, and ExecuteWithResultsAndMessages methods and the info is not in any resultset, message, or property.
mikem
View 2 Replies
View Related
Apr 11, 2004
Hi,
i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?
Below is the normal way i did in vb.net, but how to check for the return value. Please help.
========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========
Thank you.
View 12 Replies
View Related
Nov 29, 2013
I am trying to do a bulk import of data from XML into sQL.
My query returns no errors but no data gets imported.
Here is my XML
?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>
[code]....
View 2 Replies
View Related
May 6, 2015
I need to remove "rows affected" text from results as shown below from posted Sp. I am using set nocount on but its not working as expected.
Create Procedure DailyCheckList
As
SET NOCOUNT ON
Declare @EmailSub varchar(500),@dt varchar(100),@Msg varchar(max),@M varchar(max)
set @dt= convert(varchar(20),GETDATE(),107)
[Code].....
View 1 Replies
View Related
Jun 13, 2007
Hello. I was using the new sys.dm_db_index_operational_stats function which is nice for seeing counts of insert/update/delete actions per table index, bla bla bla... Anyways, question, can I do the same thing with Profiler? meaning, can I trace stored procs and sopmehow see the proc exec WITH each table it does actions against? Not talking about filtering on table names in the text, talking I just want to run an application, which uses all stored procs, and see every table used by that execution of the proc, and also the number of rows inserted,updated,deleted.... If so, which Profiler events/columns must I flick on to gather that? Thanks, Bruce
View 4 Replies
View Related
Jun 21, 2007
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
View 5 Replies
View Related
Dec 6, 2006
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
View 5 Replies
View Related
Apr 19, 2007
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
View 5 Replies
View Related
Jul 1, 2015
I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
View 3 Replies
View Related
Mar 12, 2008
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?
Thanks.
View 7 Replies
View Related
Dec 2, 2005
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View 7 Replies
View Related
Mar 28, 2007
In the Control flow tab, I have an Execute SQL Task that outputs full Result set into a variable of an object type. Now how can I write the contents of the Full Result Set into a text file using Script Task. I also want to format the following way while I output into a file:
Column Name 1 : Column Value
Column Name 2: Column Value and so on
I tried writing the contents of the Object Variable into a file, but the file had an output of single word: System.__ComObject.
Code for Writing the Full Result Set into a Text File
Dim RSsqloutput as String = Dts.Variables("objVariable").Value.ToString
Dim strVal as String = "File completed on " & Now() & vbCrLf & "------------------------------------------------------" & vbCrLf
oLogFile.WriteAllText("C:MyFile.txt", strValue)
oLogFile.WriteAllText("C:MyFile.txt", rsSQLOutput)
I went through this link that explains how to write XML Result Set into a File, But this doesn't help as it writes in XML format.
Would you please give me a hint of code how I can go upon.
View 7 Replies
View Related
Jun 14, 2006
Hi
I have an application like fetching records from the DataBase(MS Access 2000) and results i have to use in Script Task. At present i have used the record fetching query,connection string in Script itself. I would like to use in Independently. Is there any Tools like (Control Flow Tools like Execute SQL Task) are there to fetch the result set from Acccess and can use the fetching results in Script Task....
Thanks & Regards
Deepu M.I
View 5 Replies
View Related
Jun 25, 2007
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
View 1 Replies
View Related
Sep 21, 2006
Hi everyone,
For first time I'm testing this task and surprisingly, when I try "Edit Package" option:
1)The DTS host failed to load or save the package properly
2)The selected package cannot be opened
3)Error HRESULT E_FAIL has been returned from a call to a COM component
But after these messages you can see all the tasks but they haven't name!!
It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.
I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ?¿
Any guidance or idea about this?
View 5 Replies
View Related
Jun 12, 2007
An Execute SQL task takes 1 min to run a statement "insert into Mytable select * from view_using_joins"
Output: 10,225 rows affected.
But a Dataflow task configured to fetch data from the same view_using_joins into MyTable takes hours to do the same.
Could you please explain why is it so ?
Thanks
Subhash Subramanyam
View 14 Replies
View Related
Oct 13, 2015
I am facing an issue that Data flow task failing after loading 29000 rows out of 2lakhs rows.
I am loading data from .csv file to OLE DB Destination.
This data flow task is placed inside For each loop container.
is this issue because of any performance issue in SSIS packages such as buffer size.
find the error below:
DFT Load Data from FlatFile:Error: The conditional operation failed.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.Â
The "DER Add Calc Columns" failed because error code 0xC0049063 occurred, and the error row disposition on "DER Add Calc Columns.Outputs[Derived Column Output].Columns[M_VALUE_NUM]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "DER Add Calc Columns" (48) failed with error code 0xC0209029 while processing input "Derived Column Input" (49). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[code]....
View 8 Replies
View Related
