[dba-SQLServer] SQL Server 2005 unresponsive

Hi John:

I have no idea why these problems are happening when connecting to large
data set but I believe that it is timing issues. I think all SQL DBs, when
transferring data just opens a door and pour.

Using ADO-OLE just removes a few layers of program interface therefore
allowing better and faster flow control but at one point it seems beyond MS
Access's ability to handle. I abandoned ODBC use because of that as there is
just not enough control or features. (I still have a raggedy old book on ADO
2.5 programming (my bible) and would recommend something similar.)

Another thing I have noticed is that complexity of the SQL script
contributed to this hanging especially if the sequel script string was being
passed to server... Sending just parameters to a SP seemed to solve a lot of
problems.

Another thing that will guarantee a crash is attempting to sychronize the
data flow or binding the data. MS Access can only handle limited datasets in
bound mode. 

Another possibility is to limit the amount of data being sent at any one
time. I did not work on any database the size that you have but maybe if you
pass the result data to temp table, not a view and then retrieve the data in
chunks. Ie. "select * from MyTempTable where recnum > 500000 and renumber <
10000000" and then again "select * from MyTempTable where recnum >= 1000000
and renum < 1500000" and you handle the appending at the Access end. That
might/should work.

I am sure what work arounds will work but the best solutions are probably at
the server end.   

Jim


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, September 04, 2009 10:12 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] SQL Server 2005 unresponsive

Jim,

Thanks for the response.  I have to agree, it does seem to be something ODBC
/ Access related in 
that pretty much everything I do directly inside of SQL Server works just
fine.  It is annoying that 
SQL Server would ALLOW an ODBC call from Access to cause it to go into
turtle mode though.

 > Have you tried .Net?

Oddly enough I have, but it was some time ago.  I was doing this stuff in
VB.Net / ADO, but I was so 
slow at the .Net development that when I needed to "get something done" I
just went back to Access. 
  I cannot make any valid comparisons however as back when I was doing the
.Net work I also was a 
complete nube at the SQL Server stuff as well, couldn't even write a SP.

I am taking a C# class now and will be moving squarely into C# in the
future.  I have pretty much 
made a decision to make that my future.  But in the here and now...

John W. Colby
www.ColbyConsulting.com


Jim Lawrence wrote:
> I think the issue is Access related and not MS SQL. Had exactly the same
> issues when connected to an Oracle DB. I ended up writing code that would
> save my results to a temporary table, in the Oracle DB and then would have
> Access just retrieve the table results. 
> 
> The single process step of gathering the data and downloading a recordset,
> via ADO would sometimes cause a hang up. If I used ODBC, it would always
> cause a hold up especially on larger data sets.
> 
> Have you tried .Net?
> 
> Jim

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com