Loosing SQL connection while running ASP

We are running an ASP application. That is fairly simple.

It selects from one table (15.000 records), and updates into another table.
But when we execute the ASP script, it reports the following error, after
completing between 70%-80%. This varies each time it runs

"Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied. "

The application is running in ths following server environment

Server 1

Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)

Windows 2003 Web Server (IIS)

Server 2

Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)

Windows 2003 Standart server

Microsoft SQL Server 2000Jesper Carstensen (jesper@.swush.com) writes:
> We are running an ASP application. That is fairly simple.
> It selects from one table (15.000 records), and updates into another
> table. But when we execute the ASP script, it reports the following
> error, after completing between 70%-80%. This varies each time it runs
>
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied. "

So how exactly do you retrieve the rows and submit the updates? For
retrieval I would recommend client-side cursors.

Is the table you update on the same server as the one you read from?
In such case, the best may be to have all the updating login in a stored
procedure, unless the logic is too complex for SQL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are seeing that EXACT same error popup randomly on our web server
running Windows 2003 Server. Here's our setup:

Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates, etc.
Web Server 2 - Windows 2003 Server - All updates
SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates

Now, we used to have both our web servers be 2000 server. We've
recently updated the 2nd one to be 2003 server. Ever since we have
been getting that same error (below) at random times. We seem to think
that it may fail under heavy load.

"Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. "

We've done just about everything we've seen suggested here in the
groups and on Microsoft's site without any success. We can't nail down
what is causing this problem. If ANYBODY can help point us in the
right direction we'd greatly apprecaite it. What's odd is that I've
seen many posts reporting this same problem without any solutions
given. It seems that maybe there's some kind of issue with 2003 that
hasn't been documented or exposed yet.|||This would be my checklist.. (1 and 2 are probably ruled out given that the
2000 server works with no problem)

one clue you may have to help you is the amount of time the web server takes
to throw that error when you access the page. Is it trying to connect and
then giving up after a few seconds, or is it failing immediately without
attempting to handshake with the server.

1) Check the DB server loading, increase connection timeout if its heavy for
extended periods.
2) continuously ping the DB server from the web server(s) to see if there
are connection outages/packet loss.
3) Try a different(older) version of MDAC on the web server, fiddle with the
client settings.
4) Try varying the connection type (SQL logon Windows logon Named Pipes
TCP/IP)
5) Run IIS under a different isolation level, fiddle with the application
settings
7) trap the error in the connection code and attempt a re-connect (3 strikes
and your out).
6) try something else ...
8) get the 2000 server back out

hope you find a solution to the problem, let us know if you do. :)

Mr Tea
http://mr-tea.blogspot.com

"Don Kitchen" <donkitchen@.gmail.com> wrote in message
news:1106327170.237818.86870@.c13g2000cwb.googlegro ups.com...
> We are seeing that EXACT same error popup randomly on our web server
> running Windows 2003 Server. Here's our setup:
> Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates, etc.
> Web Server 2 - Windows 2003 Server - All updates
> SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates
> Now, we used to have both our web servers be 2000 server. We've
> recently updated the 2nd one to be 2003 server. Ever since we have
> been getting that same error (below) at random times. We seem to think
> that it may fail under heavy load.
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied. "
> We've done just about everything we've seen suggested here in the
> groups and on Microsoft's site without any success. We can't nail down
> what is causing this problem. If ANYBODY can help point us in the
> right direction we'd greatly apprecaite it. What's odd is that I've
> seen many posts reporting this same problem without any solutions
> given. It seems that maybe there's some kind of issue with 2003 that
> hasn't been documented or exposed yet.|||"Don Kitchen" <donkitchen@.gmail.com> wrote in message
news:1106327170.237818.86870@.c13g2000cwb.googlegro ups.com...
> We are seeing that EXACT same error popup randomly on our web server
> running Windows 2003 Server. Here's our setup:
> Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates,
etc.
> Web Server 2 - Windows 2003 Server - All updates
> SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates
> Now, we used to have both our web servers be 2000 server. We've
> recently updated the 2nd one to be 2003 server. Ever since we have
> been getting that same error (below) at random times. We seem to
think
> that it may fail under heavy load.
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied. "
> We've done just about everything we've seen suggested here in the
> groups and on Microsoft's site without any success. We can't nail
down
> what is causing this problem. If ANYBODY can help point us in the
> right direction we'd greatly apprecaite it. What's odd is that I've
> seen many posts reporting this same problem without any solutions
> given. It seems that maybe there's some kind of issue with 2003
that
> hasn't been documented or exposed yet.

I've seen the same problem and I think it's specifically a memory
problem. It may be a more general "resource" issue correlated with
memory but it is hard to pin down.|||I'm happy to report that we have found the fix to our problems.
Without going into too much detail here, I can say that 2 registry keys
that we created in Windows 2003 fixed the problem.

See these two threads for more info about it:

http://groups-beta.google.com/group...57109e74649c7c8

http://groups-beta.google.com/group...075084961f5cdb7

Loosing SQL connecting while running ASP

We are running a fairly simple ASP application, that connect to a MS SQL ser
ver.
It selects from one table (15.000 records), and updates into another table.
But when we execute the ASP script, it reports the following error, after co
mpleting between 70%-80%. This varies each time it runs
“Microsoft OLE DB Provider for SQL Server error '80004005' [DBNETLIB][ConnectionOpen
(Connect()).]SQL Server does not exist or access denied. “
The application is running in ths following environment
Server 1
Fujitsu-Siemens RX300 Server(Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)
Windows 2003 Web Server
Server 2
Fujitsu-Siemens RX300 Server(Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)
Windows 2003 standart server
Microsoft SQL server 2000I would recommend getting network traces on both the IIS machine and the
SQL Server while the ASP page is being called. The error you posted is
related to a new connection, not an existing query. Increasing the
Logintimeout in code may help, but it sounds like either a coding issue or
Name Resolution problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Same problem after installing IIS6.0,
after a lot of tries, I have switched DB connection to ODBC,
I have the problem with OLEDB, I did not face the problem with ODBC.|||

quote:

---

Originally posted by mrgray
Same problem after installing IIS6.0,
after a lot of tries, I have switched DB connection to ODBC,
I have the problem with OLEDB, I did not face the problem with ODBC.

---

hi guys , any leads with the problem ? because i am stuck with this issue to
o hosting my script and MS SQL on seperate server
i am also running win2k3 and IIS6
DEAD END

Looping through stored procedure inside another stored procedure and displaying the catego

I used to do this with classic asp but I'm not sure how to do it with .net.

Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category.

I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.

Just for an example..

Category Table:

ID Category
1 Saints
2 Falcons
3 Bucaneers
4 Chargers
5 Falcons

Player Table:

ID CategoryID Player News Player Last Updated
1 1 Reggie Bush Poetry in motion 9/21/2006
2 1 Drew Brees What shoulder injury? 9/18/2006
3 5 Michael Vick Break a leg, seriously. 9/20/2006

Basically I would need to display on a page:

Saints
Reggie Bush
Poetry in Motion

Falcons
Michael Vick
Break a leg, seriously.

So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.

I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.

try building a query with sub-queries based on a join within the store procedure. test it in the query manager first then before making it a SP

Looping database queries

Hi, newbie here:

I have created a small (5 fields) Access table which I will be
converting to MS-SQL Server format when the ASP code has been
completed. It is basically a collection of links to news releases from
mining companies. The group of people who will be browsing this
database want to know if the news release pertains to their area.
Sometimes the news release pertains to multiple areas if the mining
properties are scattered. Given the possibility of a one-to-many
relationship, ie one news release, many areas, I created an additional
table for the areas. I created the ASP code to pull down the news
release information, then loop through the area records such as:

set RSNewsRelease = Server.CreateObject("ADODB.Recordset")
NewsRelSQL = "Select date, company, title, newsreleaseID from
newsreleases;"

do while not RSNewsRelease.EOF
'display news release date, company and title
response.write RSNewsRelease(0).Value & RSNewsRelease(1).Value &
RSNewsRelease(2).Value

'loop through areas
set RSAreas = Server.CreateObject("ADODB.Recordset")
'run query
do while not RSAreas.EOF
'display areas
Loop
set RSAreas = nothing
Loop

In other words, the only way I could get the results I wanted was to
set the Recordset to nothing, then reset it with each iteration of the
outer loop.

Is there a better way to do this?

JulesJules (julian.rickards@.ndm.gov.on.ca) writes:
> I have created a small (5 fields) Access table which I will be
> converting to MS-SQL Server format when the ASP code has been
> completed. It is basically a collection of links to news releases from
> mining companies. The group of people who will be browsing this
> database want to know if the news release pertains to their area.
> Sometimes the news release pertains to multiple areas if the mining
> properties are scattered. Given the possibility of a one-to-many
> relationship, ie one news release, many areas, I created an additional
> table for the areas. I created the ASP code to pull down the news
> release information, then loop through the area records such as:

It would probably be more effecient to bring up all information in
in one query:

SELECT nr.date, nr.company, nr.title, a.area
FROM newsreleases nr
JOIN areas a ON nr.newslreaseid = a.newsrleaseid
ORDER BY nr.date, nr.company, nr.title

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:

> It would probably be more effecient to bring up all information in
> in one query:
>
> SELECT nr.date, nr.company, nr.title, a.area
> FROM newsreleases nr
> JOIN areas a ON nr.newslreaseid = a.newsrleaseid
> ORDER BY nr.date, nr.company, nr.title

I basically understand your code - nr and a are aliases. The only
problem I have with your example is that, as I understand it, if a
newsrelease pertains to 3 areas, then this SQL code will result in
three "entries" in the recordset array such as (simplified):

June IBM New President Toronto
June IBM New President Cleveland
June IBM New President New York

If this is correct, I then have to find a way to cycle through the
identical recordsets (identical except for the area field). OK, just a
sec, I could add the newsrelease id to the SELECT statement and then
do a:

do while "id is the same"
response.write location
recordset.movenext
loop

I won't be back at work until Monday so I will have to wait till then
to try this out.

Thanks,

Jules|||Jules (julian@.jrickards.ca) writes:
> I basically understand your code - nr and a are aliases. The only
> problem I have with your example is that, as I understand it, if a
> newsrelease pertains to 3 areas, then this SQL code will result in
> three "entries" in the recordset array such as (simplified):
> June IBM New President Toronto
> June IBM New President Cleveland
> June IBM New President New York

Yes, this is what you would receive.

> If this is correct, I then have to find a way to cycle through the
> identical recordsets (identical except for the area field). OK, just a
> sec, I could add the newsrelease id to the SELECT statement and then
> do a:
> do while "id is the same"
> response.write location
> recordset.movenext
> loop

Yes, doing that sort of logic is not very complicated.

There is something called the Shape Provider in ADO, so that you can
bring up two related recordsets in one query. ADO is not my home ground,
and I've only read about shape, so I'm not providing any example.
And for many purposes a non-normalized recordset like this one is
the simplest way to go.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Loop through records?

I am new to stored proc.
For eg, in ASP or VB6, we usually use a Do While ...rs.MoveNext...Loop to go through all the records in a table.
What is the equivalent of doing it in stored procs?
Please advise.
Thanks.Of course you can use cursor but it is much better to use rowset statemets - it depens on what do you want to do with your data.|||Thanks snail.

I just figured out cursors through trial and error by reading the books online.

i also just finished reading thru news groups.

many says that cursors are harmful and hogs down system resources? because this script will be run once a user logs into the system. what will be the alternative to cursors? there is an alternative way which i heard is to actually pass some data into temp tables?

pls advise.

here are the scripts i wrote:

CREATE procedure usr_clearworkerdp as
declare @.wid varchar(10)
declare workercursor cursor for
select worker_id from worker where datediff(year, date_of_clearance,getdate() ) = 0 or datediff(year, date_of_clearance,getdate() ) >0
open workercursor
fetch next from workercursor into @.wid
while @.@.FETCH_STATUS = 0
begin
update violations_committed set dp = '0' where worker_id = @.wid
fetch next from workercursor into @.wid
end
close workercursor
deallocate workercursor
GO|||If your update is as simple as the code you wrote, can't you just do this?

UPDATE worker
SET dp = '0'
WHERE DATEDIFF(year, date_of_clearance, GETDATE()) >= 0

There is nothing about your update that would require doing anything one row at a time.

Rob|||Originally posted by rgarrison
If your update is as simple as the code you wrote, can't you just do this?

UPDATE worker
SET dp = '0'
WHERE DATEDIFF(year, date_of_clearance, GETDATE()) >= 0

There is nothing about your update that would require doing anything one row at a time.

Rob

the date_of_clearance belongs to the worker table. the logic is to check the dates, then if more than a year, update dp column in the violations_committed table.|||ur update changes to : -

update b
set b.dp = 0
from worker a,violations_committed b
where DATEDIFF(year, a.date_of_clearance, GETDATE()) >= 0
and a.worker_id = b.worker_id

;)

Loooping through SELECT values in SQL

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In a SQL User-Defined Function, I am selecting a row which returns multiple values. I need to construct one single string out of those returned values. To do that, I am using CUROSR.

Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.

Is there a way, I can construct this String without using CURSORs??

Please advice. Thanks

PankajYou should be able to do something like this to concatenate the columns together:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1 + myColumn2 + myColumn3 + myColumn4
FROM
 myTable

Terri|||my mistake, the multiple values selected are from the same column (multiple rows)|||That's OK. you can still use the same method:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1
FROM
 myTable

Terri|||Even though the described SQL works this not a supported T-SQL approach. As per the documentation deom BOL on Select statement :
:: If the SELECT statement returns more than one value, the variable is assigned the last value returned.::

And such an approach is dangerous and can be removed in future builds or service packs. Donot rely on them.

Check out a thread posted by Umachander (MVP, SQL Server) on the same at the public.sqlserver.programmming newsgroup. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e7dcU%23jiAHA.2088%40tkmsftngp03

There are many other approaches to the same problem. Some of the solutions have been outlined at : http://www.extremeexperts.com/sql/articles/IterateTSQLResult.aspxsql