Reading database timed out

Today, I did an UPDATE statement on SQL Server Management Studio on a few 
records.
Now, from a VB.NET program when I tried to get those records that I updated, 
when I go thru the datareader, when it gets around those records that I 
updated, it timed out.
Even though getting the same query thru SQL Server Management Studio is very 
fast.
What can I do to fix it ?
Thank you.

This is the UPDATE statement that I did
UPDATE tickdata1min set openprice = openprice *10,highprice = highprice *10,
lowprice = lowprice *10,closeprice = closeprice *10 where symbol = 'YMM0' 
AND SEQUENCENUMBER >= '20100511-0950'
AND SEQUENCENUMBER <= '20100511-1038' AND CLOSEPRICE < 100

This is the Stored Procedure that I use
exec GetData1Min @Symbol='YMM0',@SeqNumLow='20100511-1000'

Using command = New SqlCommand(sql, connection)
Dim reader As SqlDataReader

command.CommandType = CommandType.Text
command.CommandTimeout = 300
reader = command.ExecuteReader()

While reader.Read
    outputData = ""
    currentSequenceNumber = reader(0)    -> timed out when it gets to 
SEQUENCENUMBER >= '20100511-0844'

If I do
exec GetData1Min @Symbol='YMM0',@SeqNumLow='20100511-1040', it won't timed 
out.
If I do GetData1Min for other symbols that I didn't update today, it won't 
timed out.
It only timed out on the symbols that I did an "UPDATE" statement thru SQL 
Server Management Studio.

************************************************************************************************

This is the table
CREATE TABLE [dbo].[TickData1Min](
 [SequenceNumber] [char](13) NOT NULL,
 [CommodityCode] [char](10) NOT NULL,
 [MonthYear] [char](2) NULL,
 [Symbol] [char](12) NOT NULL,
 [OpenPrice] [decimal](16, 4) NULL,
 [HighPrice] [decimal](16, 4) NULL,
 [LowPrice] [decimal](16, 4) NULL,
 [ClosePrice] [decimal](16, 4) NULL,
 [Volume] [numeric](18, 0) NULL CONSTRAINT [DF_TickData1Min_Volume]  DEFAULT 
((0)),
 [Date] [datetime] NULL,
 CONSTRAINT [PK_TickData1Min] PRIMARY KEY CLUSTERED
(
 [Symbol] ASC,
 [SequenceNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = 
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

This is the Stored Procedure
ALTER Procedure [dbo].[GetData1Min]
(
 @Symbol VarChar(10),
 @SeqNumLow VarChar(13)
)
as Begin

 Select sequencenumber, openprice,highprice,lowprice,closeprice,volume
 From TickData1Min
 Where [Symbol] = @Symbol
 AND SequenceNumber >= @SeqNumLow
 Order By SequenceNumber
 OPTION (RECOMPILE)
End




0
fniles
5/11/2010 8:59:19 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
950 Views

Similar Articles

[PageSpeed] 51

fniles (fniles@pfmail.com) writes:
> Today, I did an UPDATE statement on SQL Server Management Studio on a few 
> records.
> Now, from a VB.NET program when I tried to get those records that I
> updated, when I go thru the datareader, when it gets around those
> records that I updated, it timed out. Even though getting the same query
> thru SQL Server Management Studio is very fast. 
> What can I do to fix it ?

It seems that you at some point started a transaction in SSMS, and then
forgot to roll it back or commit it.

Run COMMIT TRANSACTION in your query window until you get an error message,
tell you that there is no active transaction.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
5/11/2010 9:43:40 PM
Thank you

The database is pretty big at 9 gig, and the 1 of the table that I updated 
has about 13 million records in it.
Will the COMMIT TRANSATION run a long time for a table this big ?

Do I just type in COMMIT TRANSATION in the SQL Server Management Studio 
query window ?

> Run COMMIT TRANSACTION in your query window until you get an error 
> message,
> tell you that there is no active transaction.
What do i do when I get an error ?
What do you mean by "tell you that there is no active transaction" ?


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D75F15F359CCYazorman@127.0.0.1...
> fniles (fniles@pfmail.com) writes:
>> Today, I did an UPDATE statement on SQL Server Management Studio on a few
>> records.
>> Now, from a VB.NET program when I tried to get those records that I
>> updated, when I go thru the datareader, when it gets around those
>> records that I updated, it timed out. Even though getting the same query
>> thru SQL Server Management Studio is very fast.
>> What can I do to fix it ?
>
> It seems that you at some point started a transaction in SSMS, and then
> forgot to roll it back or commit it.
>
> Run COMMIT TRANSACTION in your query window until you get an error 
> message,
> tell you that there is no active transaction.
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
fniles
5/11/2010 11:50:25 PM
I think whatever problem I have, now is causing connection status to be 
suspended.
Now when I do sp_who, every few minutes I see a new connection to the 
database and the status is "suspended".
This new connection comes from the same VB.NET program that inserts data to 
the database.
After a while, the program will get the error
"Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool.  This may have occurred because all pooled connections were
in use and max pool size was reached."

What does the status "suspended" mean, and will it use up all the connection 
from the pool ?
How can I fix this ?

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D75F15F359CCYazorman@127.0.0.1...
> fniles (fniles@pfmail.com) writes:
>> Today, I did an UPDATE statement on SQL Server Management Studio on a few
>> records.
>> Now, from a VB.NET program when I tried to get those records that I
>> updated, when I go thru the datareader, when it gets around those
>> records that I updated, it timed out. Even though getting the same query
>> thru SQL Server Management Studio is very fast.
>> What can I do to fix it ?
>
> It seems that you at some point started a transaction in SSMS, and then
> forgot to roll it back or commit it.
>
> Run COMMIT TRANSACTION in your query window until you get an error 
> message,
> tell you that there is no active transaction.
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
fniles
5/12/2010 1:01:59 AM
I ran COMMIT TRANSACTION, no error.
After that it looks like all my problems were fixed.
Thanks !

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D75F15F359CCYazorman@127.0.0.1...
> fniles (fniles@pfmail.com) writes:
>> Today, I did an UPDATE statement on SQL Server Management Studio on a few
>> records.
>> Now, from a VB.NET program when I tried to get those records that I
>> updated, when I go thru the datareader, when it gets around those
>> records that I updated, it timed out. Even though getting the same query
>> thru SQL Server Management Studio is very fast.
>> What can I do to fix it ?
>
> It seems that you at some point started a transaction in SSMS, and then
> forgot to roll it back or commit it.
>
> Run COMMIT TRANSACTION in your query window until you get an error 
> message,
> tell you that there is no active transaction.
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
fniles
5/12/2010 1:40:57 AM
fniles (fniles@pfmail.com) writes:
> The database is pretty big at 9 gig, and the 1 of the table that I updated 
> has about 13 million records in it.
> Will the COMMIT TRANSATION run a long time for a table this big ?

The size of the table has nothing to do with it.

COMMIT TRANSACTION usually completes quickly. In any case, the question is
moot, because if you don't commit your changes, you will keep on blocking.

>> Run COMMIT TRANSACTION in your query window until you get an error 
>> message,
>> tell you that there is no active transaction.
> What do i do when I get an error ?

Stop running COMMIT TRANSACTION.

> What do you mean by "tell you that there is no active transaction" ?
 
If you say BEGIN TRANSACTION you start a user-defined transaction. Meaning
that evrrything you do from that point until you commit, while either be
persisted in full or not at all. Transactions are a fundamental concept in
the database world.

If you issue a second BEGIN TRANSACTION, that does not start a new 
transaction within the transaction, but it just increments a transaction
counter. This is useful when working with nested stored procedures. When
issue COMMIT TRANSACTION, this will only decrease the transaction counter,
and nothing else happens if the transaction counter still is > 0. Therefore
you need to run COMMIT TRANSCATION until you have matched all BEGIN 
TRANSACTION. The simplest way to verify this is to run COMMIT TRANSACTION
until you get an error message telling you that there is no transaction to
commit.

ROLLBACK TRANSACTION, on the other hand, is different. ROLLBACK always takes
effect and immediately slashes the transaction counter to 0.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
5/12/2010 7:42:30 AM
Reply:

Similar Artilces:

automatic time formatting
In attempting to run mail merge with word, if the time format has the am / pm in it, the contents of the cell are entered as a decimal. But if one can enter the time as (9:15) the contents in the mail merge appear as they were entered. trouble is that any time I enter a number such as above, it automatically formats to (hh:hh:ss am/pm) how do i turn off the automatic format! I have attempted to change the format to the custom (hh:mm)once the extended time is in the cell, but though it is selected and entered, the contents of the cell do not change. I have used excell mail ...

Problems opening a database
Hi all I'm trying to open a database that I created on another computer but when I try to open the database I get the following error. SOURCE FILE READ ERROR - BLOCK PADDEDSOURCE FILE READ ERROR - BLOCK PADDEDSOURCE FILE READ ERROR - BLOCK PADDED Any help will be greatly appreciated. Thanks Simon ...

Reading .wks file
Greetings...according to the Excel "help" file, as well as the file extension listing, I *should* be able to read an *.wks file, but Excel insists that it cannot. I am pretty sure the file was created in Microsoft Works. Is there a converter somewhere that I can download/install? Cheers - S2 Excel can read Works 2.0, not later. You have to save them in Works as excel files or Works 2.0 or get a commercial converter. -- Regards, Peo Sjoblom "Skip Stocks" <anonymous@discussions.microsoft.com> wrote in message news:AFC110E0-641D-4D87-9464-B930CC41CF02@microsoft....

File won't open as read only
I have a file that is in use, but another person opens it and it doeasn't display the "file is in use" message. Is there a setting or fix? Hi have you shared this file? -- Regards Frank Kabel Frankfurt, Germany John wrote: > I have a file that is in use, but another person opens it > and it doeasn't display the "file is in use" message. Is > there a setting or fix? The file is on a network share. The share has all the appropriate permissions. >-----Original Message----- >Hi >have you shared this file? > >-- >Regards >Frank Ka...

RMS 2.0 to POS2009 database conversion
I am trying to create a database in POS2009 from a .bck from RMS 2.0. It keeps bailing out during the create process. What am I missing? I have installed the Serv pck for POS2009. -- ce-thompson@ca.rr.com That can't be done directly. You need to follow the upgrade/migration process outlined in the Rapid Implementation Tools found on Partner Source. Robert Armstrong RMS Systems Inc. www.retail-pos.com "Curt Thompson" <cthompson@adctech.com.nospam> wrote in message news:91BDC244-B472-44B8-AE99-B43B94E29142@microsoft.com... >I am trying to create a da...

How to give other users read-only access to Calendar
I want to allow the group Everyone to have read-only access to a calendar in a certain mailbox. I can do this by granting the permission 'Full mailbox access' (under 'Mailbox rights', under 'Exchange Advanced', for the particular user). However this also allows people to to do everything (ie: they become read-write users). I notice that every mailbox in the system has 'Read permissions' granted to group Everyone. This does not allow other people to open items in the mailbox, but as I understand it, permits Exchange Server and Outlook to do shared meetin...

Read mail arn't marked as read anymore
After an SP upgrade of my Office 2000 the priviewed mail doesnt get marked as read anymore. I have tried to change the time (Tools->Options->Priview pane) from 2 -> 3 -> 4 seconds but nothing works. The only way to mark a mail as read is either to open it or right click it and chose Mark as read. Since I only use the priview pane this is very anoying for me. Is this a bug or has some setting changed with the SP? ...

reading confirmation
Good day, I have a problem with outlook. When they send a message to me that demands the shipment of a reading confirmation, even if I accept, the reading confirmation does not come received from the sender. Someone knows from what depends and in which way I can resolve the problem? Thanks for the eventual answers. Niki In news:eht7fo$251$1@fata.cs.interbusiness.it, Niki <nicola.pantaleo@yahoo.it> typed: > Good day, > > I have a problem with outlook. When they send a message to me that > demands the shipment of a reading confirmation, even if I accept, the > read...

"Unblock" feature should be optional when reading e-mail in CRM
When viewing e-mail messages in CRM, a line appears saying "Unblock" to allow the full message content to be read. Can this be made a configurable server or security setting? We are trying to reduce "clicks" as much as possible. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agre...

The memory could not be "read".
I'm at work yesterday afternoon responding to e-mail on my desktop, and Adobe pops up from the toolbar with a notice that there is an update available for Acrobat. I accept the download, keep plugging away at my e-mail, and install it when it's done. It runs through the install, tells me that I should restart, but I ignore it and keep working. It's near the end of the day and I'm going to be going home soon anyways. I forgot to turn it off when I left. Came in this morning, restarted it through the Start menu, and rebooted. Upon reaching the "Ctrl+Alt+Del" ...

run-time error 429
ok..I hope I am almost done bugging...but I finally got scanner, cash drawer, and printer working..hopefully...I havent got the pole display working as of yet..stil have weird symbols..no biggie...heres what I have done...I installed EPSON OPOS for .net and thats how I sort of got things to work...I went to go into Manager to test it all and I got this error Active X component cant create object... please help I am at final stages..I hope. never mind I foound a link and it worked...I found it after I came here.... "Angelique" wrote: > ok..I hope I am almost done bugging.....

how can i copy a document to a CD without making it read only?
HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? You can't. It is not the file, but the media, that is read only. Even CD-RW media does not allow editing a file on the CD. Copy te file from CD to HD, mak edits and if a CD-RW you should be able to burn the edited file back to the CD. hth "DON" wrote: > HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? ...

Unable to Read Japanese Email
I correspond with several Japanese users and can read emails from some of them without a problem, but emails from others are nothing but a series of ?????. In one case, I can read one email but not another from the same sender. Changing the Encoding doesn't help. Sending emails in Japanese to them without a problem...they can read it fine. Would appreciate any suggestions/solutions as I'm stumped. I'm using Outlook 2003 on a Windows XP Home operating system. Japanese language support is installed. ...

Transferring read e-mail to another folder
Hello I was wondering whether there was a way in which I could automatically transfer my e-mail that has been read into another folder, such as "Old e-mail" or something like that? Thanks ...

Outlook not marking read emails as read
Hi - We have 1 computer that is doing the oddest thing, anytime the customer reads the last email in his box, exit's out of email and then comes back in, that email is now marked as unread - it's the weirdest thing I have seen in a long time. I uninstalled office XP, rebooted, then reinstalled and applied the 2 service packs, hoping that would fix it, but it didn't. So now I'm stuck and was wondering if anybody out there has any thoughts on what to do.... thanks! Gerri Urban gurban@ci.broomfield.co.us ...

Suggested reading
Any suggested reading for Access 2003 VBA? I have both "Step by Step Access 2003" And "Microsoft Access 2003 VBA for Dummies" (how Ironic) and niether have been very helpful. Try this book. It's a winner: Access 2003 VBA Programmer's Reference by Patricia Cardoza, Teresa Hennig, Graham Seach, and Armen Stein http://www.amazon.com/Access-2007-Programmers-Reference-Programmer/dp/0470047038/ref=sr_1_1/104-1181757-2327103?ie=UTF8&s=books&qid=1185824619&sr=8-1 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.access...

Insert Database doesn't "see" half my queries?
I am trying to insert a database” from Access 03 into Word 03 using the database toolbar in word. When I click “Insert Database” in word, and then browse for my query, the wizard doesn’t “see” any of the queries in which I changed a field name (ex. Dept: Department). Does anyone know a workaround for this? Right now, it’s only showing about half my queries which is a little odd. Is there a different way to insert my database? Thank you! If you check Word Tools->Options->General->"Confirm conversion at open" and go through the whole insertion process aga...

Message(s) Not Being Marked As Read
I have "Mark message read after displayed for" '0' seconds checked. But in my Junk e-mail folder the messages don't get marked as read when I select them, only when I open them. I'm assuming this is because there isn't a the reading pane for the Junk e-mail folder even though I have "Show reading pane" checked in "Layout" a reading pane doesn't display in the Junk email right pane. Is this normal? I'd rather just click on the message instead of opening the message to mark it as read. James > I'd rather just c...

i remove the password and decrypte the database so i can use auto
How do i remove the password and decrypte the database so i can use automatic access data collection? it says i need to close and reopne but the database is already open! On Fri, 5 Feb 2010 00:23:01 -0800, brad <brad@discussions.microsoft.com> wrote: Read that message again. Hit Ctrl+C while it is open, then Paste into Notepad so you have it for future reference. It says much more than close and reopen. -Tom. Microsoft Access MVP >How do i remove the password and decrypte the database so i can use automatic >access data collection? >it says i need to close...

Controling READ ONLY and READ/WRITE mode when opening a project PS
Hello All, I was looking for a way to force users to select between READ ONLY and READ/WRITE instead of it defaulting to READ/WRITE when opening projects in MS Project 2007. Any suggestions would be a huge help. Thanks, Eric Eric -- Short of using custom software development, there is no way to force this issue with your PMs. If you want to try the custom software development route, then please repost your message in the microsoft.public.project.developer newsgroup. Otherwise, make this a training and performance issue with your PMs. Hope this helps. -- Dale A. Ho...

Unhide more than one worksheet at a time
I have a worksheet that contains 50 hidden worksheets. Is there a method or macro that somebody knows of where I can unhide more than one workseet at a time? Thanks, Dave =@===----¬----¬----¬.¸¸.·´¯`·.¸¸.·´¯`·.¸.·<º))))>< Dave, Try the following code: Sub AAA() Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets WS.Visible = True Next WS End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "DBavi...

read an ascii file with fopen
I try to open with fopen and read an ascii file, line by line, but get garbage - among the right data in the CString variable that is filled with this line data. Can someone copy&paste the right code how to so that? Thanks in advance. Mark "Mark" <mark@chasan.ar> wrote in message news:%23sPmEzsgGHA.2208@TK2MSFTNGP05.phx.gbl... > I try to open with fopen and read an ascii file, line by line, but get > garbage - among the right data in the CString variable that is filled with > this line data. > > Can someone copy&paste the right code how to so that?...

Need to break module password in Access2000 database
I am trying to maintain a corporate database, actually many of them, created by a guy who left the copmpany and refused to unlock them. I have a tool to reveal database passwords but it doesn't know that the module in the database has a password. Without access to the module code, I'm sunk. Can someone point me to a tool that will break it? I don't mind paying for the tool. Scott On Thu, 14 Feb 2008 13:55:28 -0800 (PST), "Scott M." <quanker@gmail.com> wrote: One tool is called Lawyer. In most jurisdictions making the source code of work performed in company time...

Read/Not read
Hello We have an exhange-server environment. The Boss' secretary need's to be able to read the Boss' mail, without the messages being marked as read, within outlook 2003. She can access the Boss mail, but all mail she reads is being marked as read, hence the boss can not figure out what he has seen/not seen. What is the solution? Thanx a lot /Jan Hi Towli. There is no way to marked as unread automatically, Just she should be tick the unread option on the pop up menu after she open the her boss e-mail. Once you right click button one of e-mail on the e-mail list, you w...

Programatically reading a XSD File
Hello, Let us say I have a schema file like this sample below. How would I using ..NET classes be able to read this XSD file and get all the values for each element, such as "name", "type", "minoccurs" etc.,? I would appreciate if somebody can help me with some sample code. Thanks for your help. Ganesh ********************* <?xml version="1.0" standalone="yes"?> <xs:schema id="Account_Did" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata&...