[dba-VB] Connection closed

jwcolby jwcolby at colbyconsulting.com
Sat Dec 8 09:45:57 CST 2007


Susan,

Thanks for the reply.  I don't have a clue and no time to screw around with
it more.

I was trying to use a stored procedure to migrate data from a "horizontal"
to a "vertical" schema.  In the end I just resorted to VB.Net.  I have a
class with a bunch of prewritten functions that allows me to just
instantiate and call my class to do things including executing action
queries directly out on SQL Server.  It took me about 1/2 hour to just redo
the whole process, replace the stored procedure with a new function in a
form in VB.Net and voila, I am crunching away doing the conversion.

At first it was whizzing through the fields of the horizontal table,
throwing their data and field ID into the vertical table.  It has slowed
down now as the number of records in the vertical table has grown.  I was
reading just the other night about all the work SQL Server has to do to
maintain a balanced tree and I think that is what I am seeing now.  I have
processed 18 fields from the horizontal table and pulled 142 million pieces
of actual data out of those 18 fields.  There are 584 fields to process in
the horizontal table so this could take till next week to process.  And then
once the process is finished I have to build an index on the table.

All in order to discover whether it will be faster to execute a given query
using this vertical schema.  It should be interesting.


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Saturday, December 08, 2007 9:24 AM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Connection closed

John, this is just a wilda*s guess because I've seen this happen to me, but
it may be the syntax you're using and you're just getting one of the less
the helpful error messages.

The interesting word is "available" -- my guess is that one of your
parameters isn't the right type of something obscure like that, but I really
am only guessing.

Susan H.



> Any ideas out there? The ExecuteNonQuery complains that the connection 
> is closed.  If I break at that point (the catch) and do a lcnn.open it 
> says the connection is open.  Further it tells me the name of the 
> server, the table name etc.  Doesn't sound closed.

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com




More information about the dba-VB mailing list