Re: How to make a SQL run longer?

On Jun 27, 2:33 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Raj (jkama...@xxxxxxxxx) writes:
Thanks for replying. My challange is that I can pass only one SQL
statement at at time.

Huh? What environment is this?

Is there a function that I use like this:

select a, b, c
from table_a a, table_b where a.cid = b.cid and delay(0:0:1)

You could write one that calls xp_cmdshell and the uses a wait command
in the shell.

But hopefullly you can also access the database from a regular query
window. In such case you can start a transaction that locks one of the
tables in your query. After a minute you commit/rollback that transaction,
so that the other process is let go.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks. With your suggestion, I was able to do what I want. Here are
the steps I followed

1. Create the following SP in my SQL server.

CREATE PROCEDURE byroyalty1 @percentage int
AS
begin transaction

select au_id from titleauthor(tablockx)

WAITFOR DELAY '0:1:0'
commit transaction
GO

Note the exclusive lock for the table titleauthor

2. Execute this procedure from SQL Query analyzer just before I run my
application. Now my application has to wait longer atleast a minute
before it gets the resultset from the databse.

Thank you for your help.
Regards
Raj

.

Relevant Pages

  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Transactions - Infinite Loops
    ... This still doesn't sound like an infinite loop. ... > It was an unusual SQL Server response that we were unable to duplicate. ... > transaction if the wrong number of felds are being updated (i.e., ... > the query ran okay. ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Variable vs Temporary Table
    ... > logging to the transaction log for table variables). ... Pro SQL Server 2000 Database Design - ... >>> We have a query that declares a table variable, ...
    (microsoft.public.sqlserver.programming)
  • Re: Transactions - Infinite Loops
    ... It was an unusual SQL Server response that we were unable to duplicate. ... the end of the transaction, but the query seemed to work fine - records were ... the query ran okay. ... Now I am thinking that perhaps the transaciton log loop was cuased by some ...
    (microsoft.public.sqlserver.programming)