Solved

ADO/SQL Server Connections

Posted on 1998-07-13
4
223 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now