Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
I am trying to check for a null value in the Stored Procedure. I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value
I have a stored procedure that is using Like and a parameter to query by Social Security Number. Not all of my records have SSN's, so some are null.
When I run the stored procedure and use the "%" to show everything, it does not show any records that are null. I have tried to use the IsNull funtion to give the null values a value of 'none', but that did not help.
I am trying to set up a stored procedure to return details about an item in my database. Say I use the following declarations for a stored procedure:
@Parameter1 int output, @Parameter2 varchar(200) output, @Parameter3 int output
and then I used a query like:
select @Parameter1 = table.field1, @Parameter2 = table.field2, @Parameter3 = table.field3 from table where itemID = 7
to populate the output parameters the problem I am running into is that if @Parameter1 is null, then @Parameter2 and @Parameter3 also return as null. But, If i rewrite the declarations to:
@Parameter3 int output, @Parameter2 varchar(200) output, @Parameter1 int output
then, with the same query, @Parameter3 and @Parameter2 get the values they should, even if @Parameter1 is null
does anyone have any ideas?
I guess another question is, I am right now in the code itself (not the demonstration here) returning 6 values, since this procedure will only ever return one record, I decided to implement it as output variables, rather than returning a 1 record result set to the application. Am I better off just returning the 1 record result set then parsing it out into variables in the application?
I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission. How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?
At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.
I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0 ,@OriginalMax DECIMAL=0 AS DECLARE @CRI8 VARCHAR(500)SELECT @CRI1='' SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax' SELECT @Criteria = ......+ @CRI8 When I execute this stored procedure, I get the following error message. SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin'. Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
I have an SSIS Package. I am using script component to loop through input columns and their values. I am not able to do Null checking. The code is as below. In place of dashes , I want to do null checking but am not able to do. I tried vbNull, IsNull, TypeOf, System.dbNull but nothing is working. I guess am missing something here. Can anyone help me with this.
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
What is the T-SQL command to check for NULL or '' in a field in one statement? I would like to change the following code to be more readable (without the OR).
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck 2 @PID INTEGER = -1 OUTPUT, 3 @PCityFrom Char(3) = '', 4 @PCityTo Char(3) = '', 5 @PDateFrom DATETIME = '31 Dec 9999', 6 @PDateTo DATETIME = '31 Dec 9999', 7 @PIsExists BIT = 1 OUTPUT 8 AS 9 10 CREATE TABLE #TmpControlRequst 11 ( 12 IDINTEGER, 13 IsExistsCHAR(1) 14 ) 15 /*###Pseudo 16 1. Check the Date From and Date To 17 -- select all the value equal to parameter cityFrom and cityTo 18 -- insert the selection records into tmp table 19 --*/ 20 INSERT INTO #TmpControlRequst 21 (ID, IsExists) 22 SELECT ID, 23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 35 ELSE 0 END 36 FROM RequestTable 37 WHERE ID <> @PID 38 AND CityFrom = @PCityFrom 39 AND CityTo = @PCityTo 40 41 --======== FINAL RESULT 42 -- For tmp table:- 43 -- isExists = 1 ==> date lapse 44 -- isExists = 0 ==> date ok 45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting 46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1 47 ELSE 0 END 48 FROM #TmpControlRequst 49 WHEREIsExists = 1 50 51 SELECT @PIsExists 52 --========= 53 54 DROP TABLE #TmpControlRequst 55 56 --========= 57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks.
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
Hi, guys I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table. could you correct my code, if you know what is the problem?
thanks
My contract table DDL: ************************************************** ***
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
My insert stored procedure is: ************************************************** *****
create proc sp_insert_new_contract ( @contractDate[smalldatetime], @tuition [money], @studentId[char](4), @contactId[int]) as
if not exists (select studentid from student where studentid = @studentId) begin print 'studentid is not a valid id' return -1 end
if not exists (select contactId from contact where contactId = @contactId) begin print 'contactid is not a valid id' return -1 end begin transaction
/*Error Check */ if @@error !=0 or @@rowcount !=1 begin rollback transaction print ‘Insert is failed’ return -1 end print ’New contract has been added’
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.
Option 1: Presently implemented: Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text) Although ithis does what is needed it generates the following inner exception
ParamValue {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime} System.Data.SqlTypes.SqlDateTimeDayTicks <error: an exception of type: {System.Data.SqlTypes.SqlNullValueException} occurred> Integer IsNull True Boolean
Option 2: Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.DBNull.Value, dtDateFollowUp.Text) No exceptions, no problems that I have seen yet.
Option 3: Dim dtNull As System.Data.SqlTypes.INullable Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", dtNull, dtDateFollowUp.Text) No exceptions, no problems that I have seen yet.
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Hey Guys, I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table. Example User enters today's date and 5:30 AM and the search for all available techs to perform that task
the tables ddl is USE [Schedule] GO /****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[AllDays]( [ID] [int] NOT NULL, [DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF USE [Schedule] GO /***Appointments Table where trouble is ***** /****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Appointments]( [ID] [int] IDENTITY(1,1) NOT NULL, [Customer_ID] [int] NOT NULL, [Tech_ID] [int] NOT NULL, [StartTime] [datetime] NOT NULL, [EndTime] [datetime] NOT NULL, [App_Date] [datetime] NOT NULL, [Created_By] [int] NOT NULL, [Date_Created] [datetime] NOT NULL, [Sku_ID] [int] NOT NULL, [Comment_ID] [int] NOT NULL, CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO USE [Schedule] GO ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID]) REFERENCES [dbo].[Comments] ([ID]) GO ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID]) REFERENCES [dbo].[Techs] ([ID])
USE [Schedule] GO /****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Schedule]( [ID] [int] IDENTITY(1,1) NOT NULL, [Tech_ID] [int] NOT NULL, [AllDayID] [int] NOT NULL, [ShiftStartTime] [datetime] NOT NULL, [ShiftEndTime] [datetime] NOT NULL, CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO USE [Schedule] GO ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID]) REFERENCES [dbo].[AllDays] ([ID])
Plus the Techs Table which holds their ID's names etc I have a snapshot (http://webdivisions.net/images/relation.gif) of the relationship posted here if that can help
I have a long running procedure (batch job) which will take quite some time to complete. I need to run a fixed number (configurable value) of copies simultaneously, i.e., either two copies or three copies at a time. (I don't want to use Service Broker for this purporse now)
For this I need to know whether this procedure is currenly being executed, and how many copies. Now I'm maintaing a flag in a table where this will get updated once the procedure starts and ends. This is not 100% reliable and need a manual check occasionally.
I would like to know, whether there is an easier method to find whether this procedure is being executed currently ?
I have a store Procedure modify structTable but check syntax is error !
Please help me ? -------------------------------------------------------------------------------------------------------------------- IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P') BEGIN DROP PROCEDURE MODISTRUCT_TABLE END GO
CREATE PROCEDURE MODISTRUCT_TABLE @TABLE_NAME VARCHAR(60), @COLUMN_NAME VARCHAR(60), @COLUMN_TYPE VARCHAR(60), @COLUMN_SIZE INT(10) AS BEGIN IF EXISTS (SELECT @COLUMN_NAME FROM syscolumns) BEGIN ALTER TABLE @TABLE_NAME ALTER @COLUMN_NAME + ' ' + @COLUMN_TYPE+'('+ @COLUMN_SIZE +')' END ELSE BEGIN ALTER TABLE @TABLE_NAME ADD @COLUMN_NAME + ' ' + @COLUMN_TYPE+'('+ @COLUMN_SIZE +')' END END
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes