Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO/SQL Server Connections

Posted on 1998-07-13
4
Medium Priority
?
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

705 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