Prev: Update - Microsoft Responds to the Evolution of Community
Next: BUY FAKE PASSPORTS OF BELGIUM,FAKE BELGIAN PASSPORTS FOR SALE! FAK
From: Charles Buege on 12 May 2010 15:21
To Anyone Who Can Help Clear Up Some Confusion -

Hey there all. I've got a good one that I'm willing to put to a challenge to anyone who wants it. Here's my application: I'm creating an ASP page that is generating an XLS file on
our web server. The SQL Server (SQL Server 2000 running on a Win2k3 server) has been, and is still, running fine for a couple of years now. I'm fully at SP4 with all hot fixes and
Slammer protection installed. The SQL Server and the web server are both seperate boxes. Here is the connection string that I am using:

connFR.open "Provider=MSDASQL;Driver={SQL Server};Network=DBMSSOCN;Server=(ip address of server),1433;Database=7SFR;uid=(userid);pwd=(pwd);"

The table that is being read from the SQL Server has about 50 columns to it. Of these columns, on each record, I need to lookup on seperate tables values in other tables to fully
propogate the spreadsheet. For example, one of the field that is in the main table is PlantID. The PlantID number (a unique identifier) is then looked up in the Plant table to find
specifically what plant is being referenced. For each record, there are 7-8 fields that need to do a lookup like this. I'm using the same connFR from the connection document listed
above.

This program had been working fine for several weeks, but recently, I started getting this problem:


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

/admin/download.asp, line 156


Now, I'm not an expert on these topics, but I started looking at my code and everthing, and the message from SQL Server does not exist or access denied seemed odd to me, as the
spreadsheet had been started. It seems to get through about 90-95% of the records, then just hangs for about 20-30 seconds then gives this message. Each time, it hangs on about the
same record (within 5-6 records of each other) and, once I look at the spreadsheet, it is going access denied on different fields within the records. So, sometimes it hangs on the
plant lookup, sometimes on the Failure Description lookup, sometimes on the Technician Name lookup. There is no consistency to this. Now, I did think that we could be dealing with a
size issue, but I don't think this is the case as the spreadsheet is 50 columns by 900 rows.

Here are some other things that I tried in my effort of getting this working:

Tried DNS name instead of IP address
Tried not having the port number in the connection line
Removed Named Pipes from the SQL Server as a communication method and am only using TCP/IP now

So, now, I have gotten this program to work, but here's the odd part of what I had to do. I added this code into the loop for each record:

x = x + 1

if x = 25 then
x = 0

sleep = 3
connFR.commandtimeout = sleep + 3
connFR.Execute "WAITFOR DELAY '00:00:" & right(clng(sleep),2) & "'"

end if


By doing this, the program slows down every 25 iterations through the program for 3 seconds, and then continues on to the next record. If I simply comment this code out, then the
program hangs each and every time.

Anyone with any suggestions? Please feel free to e-mail me at cbuege(at)moreycorp.com and I'll provide any additional information to show what is going on.

Thanks in advance,
Charlie


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 13
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

From: Bob Barrows on 12 May 2010 16:46
Charles Buege wrote:
> To Anyone Who Can Help Clear Up Some Confusion -
>
> Hey there all. I've got a good one that I'm willing to put to a
> challenge to anyone who wants it. Here's my application: I'm
> creating an ASP page that is generating an XLS file on our web
> server. The SQL Server (SQL Server 2000 running on a Win2k3 server)
> has been, and is still, running fine for a couple of years now. I'm
> fully at SP4 with all hot fixes and Slammer protection installed.
> The SQL Server and the web server are both seperate boxes. Here is
> the connection string that I am using:
>
> connFR.open "Provider=MSDASQL;Driver={SQL
> Server};Network=DBMSSOCN;Server=(ip address of
> server),1433;Database=7SFR;uid=(userid);pwd=(pwd);"

http://www.aspfaq.com/show.asp?id=2126

>
> The table that is being read from the SQL Server has about 50 columns

Do you need all 50 columns from this table for the spreadsheet? Even if
you do, I suggest you read this:
http://www.aspfaq.com/show.asp?id=2096

> to it. Of these columns, on each record, I need to lookup on
> seperate tables values in other tables to fully propogate the
> spreadsheet. For example, one of the field that is in the main table
> is PlantID. The PlantID number (a unique identifier) is then looked
> up in the Plant table to find specifically what plant is being
> referenced. For each record, there are 7-8 fields that need to do a
> lookup like this. I'm using the same connFR from the connection
> document listed above.

Euwww - why aren't you simply joining these tables into a single
query??? Gad!

select m.PlantID, p.Plantname
, l2.Lookup2Value, ... , ln.LookupnValue
<rest of columns from Maintable>
FROM MainTable m join Plant p on m.PlantID=p.PlantID
join LookupTable2 l2 ON m.Lookup2ID = l2.Lookup2ID
.... join LookupTablen ln ON m.LookupnID = ln.LookupnID


>
> By doing this, the program slows down every 25 iterations through the
> program for 3 seconds, and then continues on to the next record. If
> I simply comment this code out, then the program hangs each and every
> time.
>
> Anyone with any suggestions?

My suggestions include:
1. Stop doing this "lookup" process. Let SQL Server do this heavy
lifting for you by creating a query that joins all the tables you need
and returns all the results you need in a single resultset.
2. Stop looping through recordsets. Use GetRows to suck the data into an
array, close the recordset and connection, and loop through the array.
The quicker you can get in and out of your database, the better.
See:
http://databases.aspfaq.com/database/should-i-use-recordset-iteration-or-getrows-or-getstring.html

As for the reason for your "access denied" errors, my guess, without
benefit of seeing your code, is that you might be using a method of
opening your subsequent recordsets that is defeating connection pooling.
http://support.microsoft.com/?kbid=328476 (the article talks about the
dangers of "turning off" pooling, but pooling can also be defeated with
the same results)

Forcing implicit connections to be used will defeat connection pooling.
One way to force implicit connections is to use a connection string to
open your recordsets even though an open connection object exists. I
know you said you are " ... using the same connFR ... ", but I've
encountered many posters who think that doing this:

sConn = "connection string"
cn.open sConn
set rs=createobject("adodb.recordset")
rs.activeconnection = sConn
rs.open "sql statement"

uses a single connection. It doesn't: two connections to the database
are opened.

Another possibility is that you are not using disconnected recordsets to
process these results, and since a connection can only process a single
resultset at a time, each subsequent recordset you open without closing
the prior recordsets is causing a new connection to be implicitly
spawned behind the scenes. The more I think about it, the more likely I
think this possiblity is. Anyways, this gets us back to my first
suggestion: only open a single recordset - let SQL do the heavy lifting.

--
HTH,
Bob Barrows


 | 
Pages: 1
Prev: Update - Microsoft Responds to the Evolution of Community
Next: BUY FAKE PASSPORTS OF BELGIUM,FAKE BELGIAN PASSPORTS FOR SALE! FAK