?
Solved

OLEDB & temp table in stored PROCEDURE

Posted on 2006-06-06
6
Medium Priority
?
268 Views
Last Modified: 2008-01-16
Hi Guys ,

my problem is bit complex , but i hope i will manage to describe all the problem...

I have ASP site , with many DB connection that open and close all the time ,
with few big stored procedure in the DB.

Till now i was working with ODBC and DNS to access the DB with no problem.
month ago i started to get the error :
 Driver-s SQLAllocHandle on SQL_HANDLE_DBC failed , on one of our server ,
at its look like it happen 1-2 time day , usually it give this error for few minute ,
and then its back to normal , sometime i have to restart to make it ok again.

*in one of the time it happen i check the DB and there wasnt many connection active
(sp_who2 = 30-40  - i saw  it working with much more then that )
and i check from another server that connect to same DB , and the site was working ok from there.

I try to read about this problem , and i understand that the basic thing to do is to check connection pooling
(active ) , and try working with OLEDB instead ODBC DNS connection.

My problem are :
1) maybe someone have idea on how to solve the " Driver-s SQLAllocHandle on SQL_HANDLE_DBC failed" error ?
Or

2) I try to change to OLEDB connection and its working OK ,
the problem start with Stored procedure that i also create temp table and then make select statement.
for example :

declare @x int
declare @c int

SELECT   members.mid  INTO aa FROM members WHERE   mid =10024 -- drop table aa
drop table aa

select @x = '10023'
select @c = '10025'
 
select @c,@x

will give me the  error : Item cannot be found in the collection corresponding to the requested name or ordinal.
 when i try to show the value of @c in the site ( Response.Write "@c=" & tempRS1103(0)

but if i replace the order of the select and the create table it will work OK, and from query analyzer - the both ok...
this  example will work ok:

declare @x int
declare @c int

select @x = '10023'
select @c = '10025'

select @c,@x

SELECT   members.mid  INTO aa FROM members WHERE   mid =10024 -- drop table aa
drop table aa

please advise....

Thx

Eli
 
 



0
Comment
Question by:vestele
  • 3
  • 3
6 Comments
 
LVL 8

Accepted Solution

by:
Julianva earned 1000 total points
ID: 16842337
the problem is connection pooling , i had a similar issue once , it happens when there are too many connections made to sqlserver by the web server. I increased the
maximum worker threads to a higher number , its normally set to 255 by default and it worked for me. hope its helps in your case.

 
0
 

Author Comment

by:vestele
ID: 16842404
where do i change the max connection to DB ?
in ODBC i have only pool/dont pool and max for hold connection

Thx

Eli
0
 
LVL 8

Expert Comment

by:Julianva
ID: 16842446
maximum worker threads

go to enterprise manager

right click on the server - properties - processor tab - you will see the maximum worker threads option
there is also a connections tab - you can also set that but that is set to unlimited by default.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vestele
ID: 16845507
I change it to 500 threads ,
and i guess i see in the next few days if i get the error again.

Thx


Eli
0
 

Author Comment

by:vestele
ID: 16849592
Hi Julianva  ,

Just to update u ,
i just checked and its happen again at night :
strCategory=Microsoft OLE DB Provider for ODBC Drivers
strDescription=[Microsoft][ODBC Driver Manager] Driver-s SQLAllocHandle on SQL_HANDLE_DBC failed

Any Idea ?

0
 
LVL 8

Expert Comment

by:Julianva
ID: 16849921
How much of memory do you have on sql server
What operating system you are using and version of sql server

Try to update the odbc driver

You can also use sql profiler to trace any process that might be causing the error

I am thinking that it cannot be your code because you havnt changed anything it can only be sql server configuration or hardware like the memory.

depending on the operating system and sql server version, you can get sql server to use more ram than the default which is about 1.7 gig.

also please check the database properties - option tab - that the auto close and auto shrink options and not checked

auto close - opens and closes connection and that causes sql server to use more resources. having these options checked is not good practice.






0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question