Cannot immediately select after executing DTS

#1

Hi,

I am using C# to execute a stored procedure to execute a DTS. The DTS
executes alright. It is a simple DTS to do a direct copy from excel to
SQL table.

The problem is when I attempt to retrieved the data inserted, it
returns nothing. After some extensive testing, I found out that I had
to wait for about half a minute or more before executing any select
statements.

Anyone had any idea how I can rectify this? Any help would be greatly
appreciated.

Thanks.

----
Jordan

#2

Sounds like the default timeout on a connection. When you pump from Excel
to SQL Server do you set "close Connection on completion" in the workflow
properties?

What locks do you see on the SQL Server table?

Have you tried "WITH (NOLOCK)

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Jordan" <jordanm (AT) 37 (DOT) com> wrote

Quote:

Hi,

I am using C# to execute a stored procedure to execute a DTS. The DTS
executes alright. It is a simple DTS to do a direct copy from excel to
SQL table.

The problem is when I attempt to retrieved the data inserted, it
returns nothing. After some extensive testing, I found out that I had
to wait for about half a minute or more before executing any select
statements.

Anyone had any idea how I can rectify this? Any help would be greatly
appreciated.

Thanks.

----
Jordan

#3

Hi,

I did check on the "Close connection on completion". The scenario is
applicable to the table used in the DTS. Selecting from other tables is
alright.

"Exec sp_lock" returns type of "TAB" and Mode of "IS". I assume that
statement is to check the lock. How do I make use of the WIth (NOLOCK).

Thanks.

---
Jordan

#4

SELECT <col list> FROM TABLE WITH (NOLOCK)

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Jordan" <jordanm (AT) 37 (DOT) com> wrote

Quote:

Hi,

I did check on the "Close connection on completion". The scenario is
applicable to the table used in the DTS. Selecting from other tables is
alright.

"Exec sp_lock" returns type of "TAB" and Mode of "IS". I assume that
statement is to check the lock. How do I make use of the WIth (NOLOCK).

Thanks.

---
Jordan

#5

"WITH (NOLOCK)" also returns no records.

Thanks.

---
Jordan

#6

If it returns no rows then are there rows at all?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Jordan" <jordanm (AT) 37 (DOT) com> wrote

Quote:

"WITH (NOLOCK)" also returns no records.

Thanks.

---
Jordan

#7

It returns only the records previously exists in the table. Not the one
currently being inserted via DTS.

---
Jordan

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Cannot immediately select after executing DTS

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Cannot immediately select after executing DTS - 07-11-2005 , 09:24 AM

Re: Cannot immediately select after executing DTS - 07-11-2005 , 01:42 PM

Re: Cannot immediately select after executing DTS - 07-12-2005 , 09:24 AM

Re: Cannot immediately select after executing DTS - 07-12-2005 , 02:09 PM

Re: Cannot immediately select after executing DTS - 07-13-2005 , 08:39 PM

Re: Cannot immediately select after executing DTS - 07-14-2005 , 12:19 AM

Re: Cannot immediately select after executing DTS - 07-14-2005 , 07:43 AM