Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ADO/SQL Server Connections

Posted on 1998-07-13
4
Medium Priority
?
252 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:Victor 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 300 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

927 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