Re: DTS Error "Execution cancel by user" - Database Discussion Boards

First Prev Next

Basic question on SQL [modified]

niaher30-Oct-07 4:35

niaher

30-Oct-07 4:35

Hello. I have a very basic question (at least for most of you pros out there Smile | :)

). Are stored procedures executed in isolation - meaning that, while one SP is being executed no other SP or query can execute on the same record.

More specifically I would like to know if there is a chance that during the execution of the below SP the value of @@ROWCOUNT can be changed on line 16 by another SP or query (in which case the below SP is under the risk of returning a wrong value).

 1: CREATE PROCEDURE ProductsUpdate
 2: (
 3:         @ProductID int,
 4:         @Name varchar(40),
 5:         @Concurrency timestamp
 6: )
 7: AS
 8:     UPDATE
10:         Products
11:     SET
12:         ProductName = @Name
13:     WHERE
14:         ProductID = @productID AND
15:         Concurrency = @Concurrency
16:     -- Is it possible to have another SP or query change @@ROWCOUNT at this point?
17:     return @@ROWCOUNT

Also I was wondering about the same question but with generic queries, like below:

1: UPDATE
2:     Products
3: SET
4:     ProductName = @Name
5: WHERE
6:     ProductID = @productID AND
7:     Concurrency = @Concurrency;
8: -- Can any other query or SP get in here and alter @@ROWCOUNT?
8: SELECT @@ROWCOUNT

Thank you for your help Wink | ;)

Niaher

-- modified at 10:52 Tuesday 30th October, 2007

Re: Basic question on SQL

andyharman30-Oct-07 7:04

andyharman

30-Oct-07 7:04

Hi Niaher

The @@ROWCOUNT variable (and others like @@ERROR) are local to each database connection. Your stored procedure can use this to implement concurrency control.

Regards
Andy

Re: Basic question on SQL

niaher30-Oct-07 7:49

niaher

30-Oct-07 7:49

Thank you. So what about a generic query with multiple statements, does it have a single connection? Can I use them to implement my concurrency control?

Re: Basic question on SQL

andyharman30-Oct-07 9:50

andyharman

30-Oct-07 9:50

Yes you can use them for concurrency control. Which development language are you using?

Re: Basic question on SQL

niaher30-Oct-07 19:41

niaher

30-Oct-07 19:41

Using C#. Btw I know it is a very controversial question, but what is a better choice performance-wise, a stored procedure or a dynamic query? In my case I will have not more than 15 tables and my goal is to support concurrecy in insert, update and delete logic, be it in SPs or dynamic queries.

Niaher

Re: Basic question on SQL

andyharman31-Oct-07 3:20

andyharman

31-Oct-07 3:20

You're right - it is a very controversial question Big Grin | :-D

Performance should be very similar for this type of logic - SQL-Server will cache query plans for both methods.

Stored procedures encourage:

Encapsulation of data-access logic - so you can substantially change the underlying SQL at a later date.
Better security - You can deny direct access to the database tables, and force all access via the stored procedure layer.
Reduced susceptablity to SQL-Injection attacks.

The downside is that you need to write more code, and many programmers don't like writing SQL.

I normally use Stored Procedures in my projects (because I tend to work for clients whose the coding standards dictate that they should be used). I normally write code generators to do the boring stuff for me.

Regards
Andy

DTS Error "Execution cancel by user"

Vimalsoft(Pty) Ltd30-Oct-07 3:22

Vimalsoft(Pty) Ltd

30-Oct-07 3:22

i have a DTs that has steps, collecting info from Different tables and recreate a temp table for reporting. When i execute this DTS that has steps, i get the Error

"Execution Canceled by user"

and i did not cancel it.

Please help

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: DTS Error "Execution cancel by user"

Ghazi H. Wadi30-Oct-07 22:41

Ghazi H. Wadi

30-Oct-07 22:41

IF you are able to execute the DTS package manually using dtsrun.exe , then the problem relates to a bug addressed here
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q319058&[^]
Then see the workaround methods in the clickity above!

Cheers
Raz

/*
Ghazi Hadi Al Wadi, PMP, ASQ SSGB, DBA
*/

Re: DTS Error "Execution cancel by user"

Vimalsoft(Pty) Ltd31-Oct-07 19:50

Vimalsoft(Pty) Ltd

31-Oct-07 19:50

Thanks it worked for me

Re: DTS Error "Execution cancel by user"

Ghazi H. Wadi2-Nov-07 21:50

Ghazi H. Wadi

2-Nov-07 21:50

HTH!
Raz

/*
Ghazi Hadi Al Wadi, PMP, ASQ SSGB, DBA
*/

DateTime _Oct2007

r a j u u30-Oct-07 1:25

r a j u u

30-Oct-07 1:25

hi....
Now displaying date like 10-2007 but I want to display Oct-2007.in Sql Query..anybody know plz reply me..

Rajendran

Re: DateTime _Oct2007

soni uma30-Oct-07 1:38

soni uma

30-Oct-07 1:38

Try This,

select convert(varchar(50),getdate(),106)

Smile | :)

Re: DateTime _Oct2007

r a j u u30-Oct-07 1:52

r a j u u

30-Oct-07 1:52

hi soni Uma....Thanks for reply.....

I want to display only the month and year..(Oct-2007) if u know that plz reply me..

see the following code...thats i have used...but its displaying 10-2007....

select Cast(DatePart(month,RenewalBaseMonth) as varchar) + '/' +Cast(DatePart(year,RenewalBaseMonth) as varchar) as RenewalBaseMonth
from #temp21

Thanks

Rajendran

Re: DateTime _Oct2007 [modified]

Senu Gandhi30-Oct-07 2:07

Senu Gandhi

30-Oct-07 2:07

Hi,

Try this

select Cast(DateName(month,RenewalBaseMonth) as varchar(3)) + '/' +Cast(DatePart(year,RenewalBaseMonth) as varchar) as RenewalBaseMonth
from #temp21

Senthil

-- modified at 8:15 Tuesday 30th October, 2007

Re: DateTime _Oct2007

r a j u u30-Oct-07 2:14

r a j u u

30-Oct-07 2:14