Solved

ADO/SQL Server Connections

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

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

632 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