Solved

ADO/SQL Server Connections

Posted on 1998-07-13
4
239 Views
Last Modified: 2010-03-19
I have a question about Connections to the database using ADO and SQL Server. The situation:
I create one ADO-connection object and open this connection to a SQL Server. Then I often use the Execute method of the connection object to execute SQL statements. I never use a command object. My problem: although I have only one connection object I can observe that sometimes additional connections to the database are created and after execution of one SQL statement are deleted. Why? Is there any method to prevent this? In one code sequence I insert a object into the database inside a stored procedure and in the next  statement I update this object. For this second statement there is always a second connection created. Is in this situation guaranteed that the second statement is always executed after the first statement? The @@IDENTITY variable never works in a SQL statement of ADO. I always get NULL. Only inside a stored procedure it works. Why? Are the changing connections the reason for this behaviour? The additional connections need extra licenses. I get sometimes the me!
ssage that I have not enough licenses. So I think this is another problem.
( Versions: ADO 1.5C and SQL Server 6.5 with SP4 and NT with SP 3)
0
Comment
Question by:moosach
4 Comments
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091730
as far as I know If you have a per-seat license mode - you can have as many connections from one workstation as you want -they are considered one license from legal point of view. You are gettting nulls for  @@IDENTITY because this global variable is connection specific.

0
 
LVL 4

Accepted Solution

by:
tomook earned 100 total points
ID: 1091731
As spiridonov said, if you are licensed per seat, this is not an issue. You should be able to allow as many connections as you want, what is limited is the number of logged in users.

Now as to the "extra" connections. ADO/RDO/OLE-DB/ODBC drivers will create connections as needed based on certain run time conditions. For example, one case where an extra connection will show up is when any result set exceeds 100 rows. One connection is used to retrieve the keyset, and the other the data. There really is nothing to be done about this, unless you can get your result sets small.
0
 
LVL 1

Author Comment

by:moosach
ID: 1091732
Thanks !
0
 
LVL 1

Expert Comment

by:mativare
ID: 1091733
You can avoid certain unexpected connection problems, by using
Microsoft Transaction Server, it pools connections,use three-tier
application.Separate you current front-end into two, one is Activex.dll, this makes connectios when you call methods from
your display application.
Now register your activex.dll as MTS package and add few lines of code(Objectcontext)to abort or accept transactions.
Hope it helps in a future.
0

Featured Post

Technology Partners: 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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 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