Connection Problem

I created a package with two oledb connections in the connection manager....which was running perfectly...later i deleted these connections and added two new connections....and applied these to task with the new connection..

Now i run the package i come up with the following error...connection not found

Error 1 Error loading test.dtsx: The connection "SQL-DEV.sql_dev" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:\documents and settings\myname\desktop\integration services project\ssis\test.dtsx 1 1

when i double click the error it goes to xml code...i dont know what need to be done...please help

Did you name the "new" connections the same as the old connections?|||No i didnt name them with the same name as the old once but the New connections are just the same as old connections...selecting server and database..|||Is "SQL-DEV.sql_dev" the name of a new connection or one of the ones you deleted?|||

Hi Sureshv,

The problem is the Connection Managers, for whatever reason, were not completely deleted. Their metadata is still stored inside the XML source for the SSIS files.

Naming the new Connection Managers the same as the old Connection Managers will not impact this error - Connection Managers are identified by GUIDs in SSIS. If you can identify the GUID of the deleted Connection Managers (perhaps by process of elimination), and comment them out inside the metadata, the package will stop throwing the error.

Hope this helps,
Andy

|||Andy,
ALL GUI screens represent a connection manager by its name, not GUID. This is precisely why I asked the question that I did. I imagined a scenario where the metadata was not cleaned up properly and when Sureshv picked the connection manager [name] in the drop down box, it was associated with a [now] defunct GUID, hence the resulting error.

In the end I think we can agree the likely culprit is bad metadata.|||

Hi Phil,

Thanks. I've seen some tasks respond differently to this scenario. Some of them pick up the changes automatically and some don't. I've also seen metadata corruption make this completely unpredicatable.

Yep - we agree this is the culprit. It's no fun to clean up a large package with corrupt metadat either - good luck Sureshv.

Andy

|||

In general when you delete a connection manager that is being used by a task; that task keeps pointing to the deleted connection manager GUID; untill you open the task and chose a diffrent one.

To the Original poster, make sure you revist all the tasks that were using those connection managers and refresh the connection metadata. If the package has logging enable and it was using one of the deleted CM; you have to refresh it as well.

|||i dont find the old connection name with in connection managers in the xml...So what do i do? i am afraid i will break my package...any more solutions?|||Did you check and correct the package logging?|||i did not set any package logging for this package..sqlsql

Connection Pooling Logic

We have our own home grown connection pooling method that is not too
resilient.
I want to know how you handle connections to a database i.e how may
connections do you open as your Web/App tier starts up to the database, what
is the max that you allow ?
Also in the event SQL Server is slow, how do you ensure that the Web/App
tier does not open more connections ?
We have been struggling with this today as our Web/App tier does not know
better when to back off or just open new connections if there is more load.
ThanksHi
You connection pool should open a new connection when there are no free
connections, therefore if the server is not responding well connections are
going to remain in use longer! If you define a maximum pool size this will
give you an upper limit rather than creating additional connections and
potentially exacerbating the problems. The application should handle the
error generated when new connections can not be created.
Alerting when the pool is at it's upper limit should inform you when the
system is having problems although other alerts on CPU/Memory used may be
triggered first.
John
"Hassan" wrote:
> We have our own home grown connection pooling method that is not too
> resilient.
> I want to know how you handle connections to a database i.e how may
> connections do you open as your Web/App tier starts up to the database, what
> is the max that you allow ?
> Also in the event SQL Server is slow, how do you ensure that the Web/App
> tier does not open more connections ?
> We have been struggling with this today as our Web/App tier does not know
> better when to back off or just open new connections if there is more load.
> Thanks
>
>

Connection Pooling Limit thru ODBC

Hello, Does anyone know if it's possible to limit the actual number of possible connections made to a database using the connection pooling services provided by odbc (let's assume odbc and not ole, as quite honestly i intend this for connecting to MSDE)? my understanding is that the connection limit provided with the pool strictly manages the number of connections within the pool versus the total number of connections.

Ideally I would like connection requests to be queued and offered when available.. which I would further like to specify in the code using the connection. what I mean by this is that I would like to explicitly flag the connection as unused (thereby skipping the wait for idle time), when I'm done with it.

why do I want to do such things? well, and here's where my problem may lie, I want to use MSDE (with it's reccomended 5 connection limit) on a single machine. Although there will only be a single user, there exist a whole slew of *different* apps that need to jump in, read/write data and leave again.

please excuse my ignorance if I'm attempting to get around this the wrong way. i look forward to a discussion.

abLook at SQL Server Properties - Connection TAB - Remote Server Connections.

[ ]'s|||Excellent, gotta love how simple that is! do you happen to know what happens when the max connections are used? is the connection request denied or simply qeued until a possible timeout? also do you know what i should do with a connection, once finished with it in a DLL for instance, to best take advantage of connection pooling? thanks

ab

Originally posted by PedroHenrique
Look at SQL Server Properties - Connection TAB - Remote Server Connections.

[ ]'s

Connection Pooling HELP !

I'm curious how connections are pooled under IIS. If you have several
applications setup under your website, does each one get its own pool? Or
is there only one pool created for all applications running under that
instance of IIS? What if two config files have connection strings which
specify a different number of connections in the pool...how is this
handled?
I'm curious as last night we noticed over 1500 connections to our SQL
Server...no wonder it is slammed. I'm wondering if we are getting multiple
pools of 100 connections and how to handle this...
I really appreciate any help on this....our live server is slammed and I'm
the one expected to come up with an answer ASAP.Every application gets its own pool. It would be stupid for one application
to wait for another to free up connections (think about running 2 websites
for 2 customers under the same IIS, neither would be happy to know his
application's performance - or lack of it :) - it being affected by the
running of the other).
Moreover, a pool is created for EVERY DISTINCT connection STRING, even if
the two connection strings are logically equivalent (you switch places of
fields, you add whitespace).
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uZz04fzVGHA.1736@.TK2MSFTNGP10.phx.gbl...
> I'm curious how connections are pooled under IIS. If you have several
> applications setup under your website, does each one get its own pool? Or
> is there only one pool created for all applications running under that
> instance of IIS? What if two config files have connection strings which
> specify a different number of connections in the pool...how is this
> handled?
> I'm curious as last night we noticed over 1500 connections to our SQL
> Server...no wonder it is slammed. I'm wondering if we are getting
> multiple pools of 100 connections and how to handle this...
> I really appreciate any help on this....our live server is slammed and
> I'm the one expected to come up with an answer ASAP.
>|||I understand about running two websites...but what about different
applications under the SAME website.
Hmmm I read on a MS website that the distinct connection string was not
true. That the only things that had to match was initial catalog,
connection timeout, user, and password. And it gave the project and various
connection string changes to show that the pool did not change. Here is the
link...
http://www.sql-server-performance.c...oling_myths.asp
It is hard to know what to believe. It is weird, even every MS developer
comes back with different answers.
Thank you so much for taking time to answer. I see a need to greatly reduce
our pool sizes.
"Gabriel Lacatus" <cyberdude@.nospam.nospam> wrote in message
news:uVJB8mzVGHA.4792@.TK2MSFTNGP14.phx.gbl...
> Every application gets its own pool. It would be stupid for one
> application to wait for another to free up connections (think about
> running 2 websites for 2 customers under the same IIS, neither would be
> happy to know his application's performance - or lack of it :) - it being
> affected by the running of the other).
> Moreover, a pool is created for EVERY DISTINCT connection STRING, even if
> the two connection strings are logically equivalent (you switch places of
> fields, you add whitespace).
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uZz04fzVGHA.1736@.TK2MSFTNGP10.phx.gbl...
>|||Pooling has changed over the years so there's a lot of out of date info
floating around. A pool is assigned per executable that has connections
open. Depending on your isolation setting in your IIS config, different
applications may or may not run in their own COM+ server process. Look at
the connections in sysprocesses and see if they are all for the same user or
for different users. Also make sure connections are being closed so they
are returned to the pool. If you have 1500 open connections and you don't
have 15 svchost processes running, either it's likely you are opening
connections past the pool size because MDAC doesn't think the connections
are reusable or you are not closing connections.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OzPouszVGHA.5364@.tk2msftngp13.phx.gbl...
>I understand about running two websites...but what about different
>applications under the SAME website.
> Hmmm I read on a MS website that the distinct connection string was not
> true. That the only things that had to match was initial catalog,
> connection timeout, user, and password. And it gave the project and
> various connection string changes to show that the pool did not change.
> Here is the link...
> http://www.sql-server-performance.c...oling_myths.asp
> It is hard to know what to believe. It is weird, even every MS developer
> comes back with different answers.
> Thank you so much for taking time to answer. I see a need to greatly
> reduce our pool sizes.
>
> "Gabriel Lacatus" <cyberdude@.nospam.nospam> wrote in message
> news:uVJB8mzVGHA.4792@.TK2MSFTNGP14.phx.gbl...
>

connection pool problem using in Tomcat

I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000 under
Tomcat App server.
I wish to control the number of physical connections made to the database to
50. Can somebody guide me about this.
At the moment even with as low as 30 concurrent hits we see a very high
number of phyiscal connection to the database as high as 200.
I am using the following settings in SERVER.XML
<Resource name="jdbc/ECTDB" auth="Container"
type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
<ResourceParams name="jdbc/ECTDB">
<parameter><name>maxActive</name><value>30</value></parameter>
<parameter><name>maxIdle</name><value>20</value></parameter>
<parameter><name>maxWait</name><value>10000</value></parameter>
<parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe rDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.SQLServe rDataSource</value></parameter>
<parameter><name>user</name><value>..</value></parameter>
<parameter><name>password</name><value>..</value></parameter>
<parameter><name>driverName</name><value>SQLServer</value></parameter>
<parameter><name>serverName</name><value>127.0.0.1</value></parameter>
<parameter><name>portNumber</name><value>1433</value></parameter>
<parameter><name>databaseName</name><value>..</value></parameter>
<parameter><name>selectMethod</name><value>cursor</value></parameter>
<parameter><name>loginTimeout</name><value>60</value></parameter>
</ResourceParams>
Thanks a lot
| Thread-Topic: connection pool problem using in Tomcat
| thread-index: AcSMuy5kABS/93PSRZ6MtRi1273r1w==
| X-WBNR-Posting-Host: 202.134.106.86
| From: "=?Utf-8?B?a2FpY2FsbA==?=" <kaicall@.discussions.microsoft.com>
| Subject: connection pool problem using in Tomcat
| Date: Fri, 27 Aug 2004 21:55:03 -0700
| Lines: 30
| Message-ID: <22AD57F5-91A6-48C5-81BF-F9B84365D03F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6282
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000
under
| Tomcat App server.
|
| I wish to control the number of physical connections made to the database
to
| 50. Can somebody guide me about this.
|
| At the moment even with as low as 30 concurrent hits we see a very high
| number of phyiscal connection to the database as high as 200.
| I am using the following settings in SERVER.XML
| <Resource name="jdbc/ECTDB" auth="Container"
| type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
| <ResourceParams name="jdbc/ECTDB">
| <parameter><name>maxActive</name><value>30</value></parameter>
| <parameter><name>maxIdle</name><value>20</value></parameter>
| <parameter><name>maxWait</name><value>10000</value></parameter>
<parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe
rDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.
SQLServerDataSource</value></parameter>
| <parameter><name>user</name><value>..</value></parameter>
| <parameter><name>password</name><value>..</value></parameter>
| <parameter><name>driverName</name><value>SQLServer</value></parameter>
| <parameter><name>serverName</name><value>127.0.0.1</value></parameter>
| <parameter><name>portNumber</name><value>1433</value></parameter>
| <parameter><name>databaseName</name><value>..</value></parameter>
| <parameter><name>selectMethod</name><value>cursor</value></parameter>
| <parameter><name>loginTimeout</name><value>60</value></parameter>
| </ResourceParams>
|
|
| Thanks a lot
|
|
If you are using SelectMethod=direct, then it is possible that you are
seeing an increase in connections due to the creation of cloned
connections. These additional connections are necessary because each
statement requires its own connection. Your Tomcat settings have no
bearing on this particular behavior, as it is inherent in the JDBC driver
itself.
Are you using transactions in your code? Can you provide code that
reproduces the problem behavior?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb Simien ,
Yes, I use connection in my code and use a following function for getting
conncection.
public static Connection getConnection() throws Exception {
Connection dbConnection = null;
InitialContext ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
ConnectionPoolDataSource ds =
(ConnectionPoolDataSource) envCtx.lookup("jdbc/ECTDB");
Connection conn = ds.getPooledConnection().getConnection();
conn.setAutoCommit(false);
return conn;
}
Is this funciton cause the problem?
Thanks a lot.
kaicall
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: connection pool problem using in Tomcat
> | thread-index: AcSMuy5kABS/93PSRZ6MtRi1273r1w==
> | X-WBNR-Posting-Host: 202.134.106.86
> | From: "=?Utf-8?B?a2FpY2FsbA==?=" <kaicall@.discussions.microsoft.com>
> | Subject: connection pool problem using in Tomcat
> | Date: Fri, 27 Aug 2004 21:55:03 -0700
> | Lines: 30
> | Message-ID: <22AD57F5-91A6-48C5-81BF-F9B84365D03F@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6282
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000
> under
> | Tomcat App server.
> |
> | I wish to control the number of physical connections made to the database
> to
> | 50. Can somebody guide me about this.
> |
> | At the moment even with as low as 30 concurrent hits we see a very high
> | number of phyiscal connection to the database as high as 200.
> | I am using the following settings in SERVER.XML
> | <Resource name="jdbc/ECTDB" auth="Container"
> | type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
> | <ResourceParams name="jdbc/ECTDB">
> | <parameter><name>maxActive</name><value>30</value></parameter>
> | <parameter><name>maxIdle</name><value>20</value></parameter>
> | <parameter><name>maxWait</name><value>10000</value></parameter>
> <parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe
> rDataSourceFactory</value></parameter>
> <parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.
> SQLServerDataSource</value></parameter>
> | <parameter><name>user</name><value>..</value></parameter>
> | <parameter><name>password</name><value>..</value></parameter>
> | <parameter><name>driverName</name><value>SQLServer</value></parameter>
> | <parameter><name>serverName</name><value>127.0.0.1</value></parameter>
> | <parameter><name>portNumber</name><value>1433</value></parameter>
> | <parameter><name>databaseName</name><value>..</value></parameter>
> | <parameter><name>selectMethod</name><value>cursor</value></parameter>
> | <parameter><name>loginTimeout</name><value>60</value></parameter>
> | </ResourceParams>
> |
> |
> | Thanks a lot
> |
> |
> If you are using SelectMethod=direct, then it is possible that you are
> seeing an increase in connections due to the creation of cloned
> connections. These additional connections are necessary because each
> statement requires its own connection. Your Tomcat settings have no
> bearing on this particular behavior, as it is inherent in the JDBC driver
> itself.
> Are you using transactions in your code? Can you provide code that
> reproduces the problem behavior?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<zzuzMF7jEHA.2516@.cpmsftngxa10.phx.gbl>...
> If you are using SelectMethod=direct, then it is possible that you are
> seeing an increase in connections due to the creation of cloned
> connections. These additional connections are necessary because each
> statement requires its own connection. Your Tomcat settings have no
> bearing on this particular behavior, as it is inherent in the JDBC driver
> itself.
> Are you using transactions in your code? Can you provide code that
> reproduces the problem behavior?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
This is a little scary isn't it? If I have a J2EE app that starts out
pre-preparing (for subsequent re-use) N prepared statements per
connection and I have M concurrent clients using the app then we'll
require N*M physical database connections. Is that correct? That
number could be pretty huge for even a mid size app..
Is there any plans to address this in any future driver?
Grant M
|||grantmitchell7@.yahoo.com wrote:

> CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<zzuzMF7jEHA.2516@.cpmsftngxa10.phx.gbl>...
>
> This is a little scary isn't it? If I have a J2EE app that starts out
> pre-preparing (for subsequent re-use) N prepared statements per
> connection and I have M concurrent clients using the app then we'll
> require N*M physical database connections. Is that correct? That
> number could be pretty huge for even a mid size app..
Not to mention if you plan to do anything transactionally, or use temp tables,
which would be limited to one real connection, and not involve all the
under-the-covers connections involved in one logical connection.
I am confident that an upcoming version of the free MS driver will
address this. In the mean time you have the option of using commercial
drivers, or ensuring you run with selectMethod=cursor. As I recall, the
original poster actually already showed he was running with selectMethod=cursor...
Joe Weinstein at BEA
> Is there any plans to address this in any future driver?
> Grant M
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<4136035E.9000108@.bea.com>...[vbcol=seagreen]
> grantmitchell7@.yahoo.com wrote:
>
> Not to mention if you plan to do anything transactionally, or use temp tables,
> which would be limited to one real connection, and not involve all the
> under-the-covers connections involved in one logical connection.
> I am confident that an upcoming version of the free MS driver will
> address this. In the mean time you have the option of using commercial
> drivers, or ensuring you run with selectMethod=cursor. As I recall, the
> original poster actually already showed he was running with selectMethod=cursor...
> Joe Weinstein at BEA
Yes the transactional problem is alarming.. Does the driver transact
all the cloned connections for statement when the original connection
is transacted? Who knows but I suppose a test would be relativly
quick.
In regards to selectMethod=cursor - our tests show that result sets
generated with server side cursors can be up to 8 times more expensive
on the database (and slower) than forward only, 'client side' result
sets (depending on the characteristics of the cursor). So instead of
having N*M connections we might end up with N*M server side cursors.
I'm not sure which is worse..
Therefore I would argue that server cursor based result sets are a
major obstacle to scalability and should be avoided at all costs
(except possibly for singleton processes that need a table snapshot
like a report for example).
I hope you are right that an upcoming release resolves this although I
remain skeptical since my guesss is that changing this behavior would
mean a major structural change in the driver's architecture.
On this newsgroup there has been recent reference by Microsoft to a
"new" JDBC driver for SQL 2005. However when I tried to get more
details on this driver there was no response. Perhaps someone from
Microsoft could comment on the "new" driver, and specifically comment
on whether it resolve this serious issue..
Grant

Connection Pool gone berzerk.

Anyone suddenly having problems with connections not being reused after
applying the latest updates from MS? Specifically...
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB839645) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB841872) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB841873) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB840315) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB842526) Automatic update
Successful Wednesday, July 14, 2004 Cumulative Security Update for
Outlook Express 6 SP1 (KB823353) Automatic update
I'm using the Data Application Block and passing the SAME connection string
with only one exception: I manually connect and pass the connection object
rather than the connection string in a couple of routines...and, yes, the
connections are closed before exiting.
The strange thing is...all this worked great until the security updates
above were applied. No other changes.
Now, I run about 5-6 queries and, boom, I'm over the Max Limit default of
100.
Anyone else run into this?
Dave
btw, it's "berserk" not "berzerk".
"Dave Diehl" <d.diehl@.worldnet.att.net> wrote in message
news:uQoEzMcaEHA.2488@.tk2msftngp13.phx.gbl...
> Anyone suddenly having problems with connections not being reused after
> applying the latest updates from MS? Specifically...
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB839645) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB841872) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB841873) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB840315) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB842526) Automatic update
> Successful Wednesday, July 14, 2004 Cumulative Security Update for
> Outlook Express 6 SP1 (KB823353) Automatic update
>
> I'm using the Data Application Block and passing the SAME connection
string
> with only one exception: I manually connect and pass the connection object
> rather than the connection string in a couple of routines...and, yes, the
> connections are closed before exiting.
> The strange thing is...all this worked great until the security updates
> above were applied. No other changes.
> Now, I run about 5-6 queries and, boom, I'm over the Max Limit default of
> 100.
> Anyone else run into this?
> Dave
>

Connection Pool gone berzerk.

Anyone suddenly having problems with connections not being reused after
applying the latest updates from MS? Specifically...
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB839645) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB841872) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB841873) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB840315) Automatic update
Successful Wednesday, July 14, 2004 Security Update for Windows 2000
(KB842526) Automatic update
Successful Wednesday, July 14, 2004 Cumulative Security Update for
Outlook Express 6 SP1 (KB823353) Automatic update
I'm using the Data Application Block and passing the SAME connection string
with only one exception: I manually connect and pass the connection object
rather than the connection string in a couple of routines...and, yes, the
connections are closed before exiting.
The strange thing is...all this worked great until the security updates
above were applied. No other changes.
Now, I run about 5-6 queries and, boom, I'm over the Max Limit default of
100.
Anyone else run into this?
Davebtw, it's "berserk" not "berzerk".
"Dave Diehl" <d.diehl@.worldnet.att.net> wrote in message
news:uQoEzMcaEHA.2488@.tk2msftngp13.phx.gbl...
> Anyone suddenly having problems with connections not being reused after
> applying the latest updates from MS? Specifically...
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB839645) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB841872) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB841873) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB840315) Automatic update
> Successful Wednesday, July 14, 2004 Security Update for Windows 2000
> (KB842526) Automatic update
> Successful Wednesday, July 14, 2004 Cumulative Security Update for
> Outlook Express 6 SP1 (KB823353) Automatic update
>
> I'm using the Data Application Block and passing the SAME connection
string
> with only one exception: I manually connect and pass the connection object
> rather than the connection string in a couple of routines...and, yes, the
> connections are closed before exiting.
> The strange thing is...all this worked great until the security updates
> above were applied. No other changes.
> Now, I run about 5-6 queries and, boom, I'm over the Max Limit default of
> 100.
> Anyone else run into this?
> Dave
>

Connection managers - OLE DB

Hi There

Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?

Thanx

SeanDL wrote:

Hi There

Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?

Thanx

Sean,
In that situation each of the 6 tasks will use the same connection as far as I know. I guess this is why the connection managers are so called - its because they manage a single connection thereby enabling many tasks to use it.

I'm no DBA but I suspect that this is more efficient than opening multiple connections.

-Jamie|||Hi Jamie

In DTS it was definately better to use multiple connections when extracting large amounts of data from each connection.But no mention has been made of it in SSIS, i guess i could do some tests myself to see if it helps performance?

Thanx|||I'd be interested in seeing any metrics you get.

I don't claim to know much about DTS but I suspect that there are different reasons that multiple connections are recommended that outweigh the benefit of only having 1 connection to the server.
I also suspect that even if one connection object were used each task that used it would still have its own connection (I stand to be corrected here). This is not the case with SSIS.

-Jamie|||Sean, the reason connection managers are called that is to distinguish them from connections as named in DTS. The reason it was recommended to use multiple connections in DTS is because workflow would be serialized if pointing to the same connection. Connection managers actually generate a new connection everytime you call AcquireConnection so the workflow isn't serialized. Each task or transform etc. that calls AcquireConnection on the connection manager receives a new physical connection.
So, for SSIS, just use one connection manager. Also, consider using Data Sources and Data Source views for your OLEDB connections. I blogged about it a bit recently. That simplifies matters even more and makes your packages that much more portable.
Thanks,|||Kirk,
Right then, I'm a wee bit confused. I'll tell you why.

I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.

Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)

Any ideas?

-Jamie|||Hi Kirk

Thanx for the info, that is what i thought but i just could not find any documentation to verify it.

Although Jamies issue is a bit worrying because set rowcount's scope is definately within a connection/transaction?

Thanx|||Hi Kirk

Ok i am also still confused, my concurrent connections (5 of them) using the same conenction manager we all executing in parrellel, then i changed the connection manager to use the OLE DB for SQL Server NOT the native client that iw as using), now only 3 of the 5 tasks execute concurrently when 1 finishes the next one starts.Therefore it seems only 3 concurrent conenctions are allowed, i have checked BOL , but i cannot find where this setting is as i would like all tasks to run in parallel, if you know it would be greatly appreciated.

Thanx|||Check the MaxConcurrentExecutables property of the package by clicking on the package design surface and then looking at the properties. If it's set to -1, the heuristic used to determine max executables is n+2, where n is the number of processors.
Change it to a higher number and watch them all fly together. :)
K|||Thanx Kirk

Spot on , i did see that setting but i thought -1 was unlimited.Thanx A million works like a bomb !|||

Jamie Thomson wrote:

Kirk,
Right then, I'm a wee bit confused. I'll tell you why.

I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.

Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)

Any ideas?

-Jamie

OK, I've just built a simple mock-up to demo this and lo and behold it doesn't happen anymore. Setting rowcount in the Excute SQL Task didn't affect the dataflow.
I swear that when I did this before though it DID affect the data-flow...and I have witnesses to prove it. It occurred on April CTP whereas I am now using June CTP. perhaps that's why!!!

For now, ignore this. If it happens again I'll raise it.

Weird!!

-Jamie|||Did you have retain same connection set to true?
I don't have a good answer here. Without looking at the package, it's hard to know for sure.|||Yes, did so myself. Couldn't repro it either...

Connection limits to database by operating system

Can someone point me to documentation related to the number of connections a
database can have before there is a degradation in the connection?
I was told that if you have a database on a computer with a Windows 2000
operating system you can only have 5 connections to the database or the
connections will be slow or delayed to the point that the application using
the connection will not work. Because of this, the database should be on
Windows 2000 Server. This operating system allows more connections.
I am using SQL Server 2000. The database is on a system that makes
electrical measurements and stores the analysis. There are separate
applications on network computers using TCP/IP connections that access this
analysis data.
Is there a distinction between connecting to a database via a TCP/IP
connection and connecting to the database that is on the computer that the
database is on?
Thanks in advance!
Gloria
The degradation in performance you are describing looks more
like and MSDE vs. SQL Server issue. MSDE is throttled for an
average resource usage of 5 concurrent batches. You can find
more information on this in SQL Server books online under
DBCC CONCURRENCYVIOLATION
-Sue
On Tue, 11 Jan 2005 06:33:07 -0800, Gloria
<Gloria@.discussions.microsoft.com> wrote:

>Can someone point me to documentation related to the number of connections a
>database can have before there is a degradation in the connection?
>I was told that if you have a database on a computer with a Windows 2000
>operating system you can only have 5 connections to the database or the
>connections will be slow or delayed to the point that the application using
>the connection will not work. Because of this, the database should be on
>Windows 2000 Server. This operating system allows more connections.

Connection limits to database by operating system

Can someone point me to documentation related to the number of connections a
database can have before there is a degradation in the connection?
I was told that if you have a database on a computer with a Windows 2000
operating system you can only have 5 connections to the database or the
connections will be slow or delayed to the point that the application using
the connection will not work. Because of this, the database should be on
Windows 2000 Server. This operating system allows more connections.
I am using SQL Server 2000. The database is on a system that makes
electrical measurements and stores the analysis. There are separate
applications on network computers using TCP/IP connections that access this
analysis data.
Is there a distinction between connecting to a database via a TCP/IP
connection and connecting to the database that is on the computer that the
database is on?
Thanks in advance!
GloriaThe degradation in performance you are describing looks more
like and MSDE vs. SQL Server issue. MSDE is throttled for an
average resource usage of 5 concurrent batches. You can find
more information on this in SQL Server books online under
DBCC CONCURRENCYVIOLATION
-Sue
On Tue, 11 Jan 2005 06:33:07 -0800, Gloria
<Gloria@.discussions.microsoft.com> wrote:

>Can someone point me to documentation related to the number of connections
a
>database can have before there is a degradation in the connection?
>I was told that if you have a database on a computer with a Windows 2000
>operating system you can only have 5 connections to the database or the
>connections will be slow or delayed to the point that the application using
>the connection will not work. Because of this, the database should be on
>Windows 2000 Server. This operating system allows more connections.

Connection Leak

Hi,
I have troubles with a threaded application on W2003 server. It
seems to leave open connections behind time to time, it sums to hundreds over
a day (the application make thousands). It is using the SQLOLEDB provider,
MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
I put two Trace (print) statements : (1) Connection is opened. 2)
Connection is Closed.) I can see the two statements without any Exception.
But the problem is when I see on the Performance viewer (perfmon.exe/ perfmon.
msc->Sqlserver:General Statistics, User Connections) it shows Number of users
connected to the system is like thousands and more day by day ,Means the
number of open connections are in Hundreds…thousands and more . I am also
loosing the connections immediately with out any exception.
But still I am seeing open connection.
Please can anybody sort out my problem,
Is there a known bug like this? Is there a way to trace the SQLOLEDB provider?
Regards,
Rasheeds
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200612/1
"malkeshsheth via droptable.com" <u13567@.uwe> wrote in message
news:6b18cb6b1bfe5@.uwe...
> Hi,
> I have troubles with a threaded application on W2003 server. It
> seems to leave open connections behind time to time, it sums to hundreds
> over
> a day (the application make thousands). It is using the SQLOLEDB provider,
> MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
> I put two Trace (print) statements : (1) Connection is opened.
> 2)
> Connection is Closed.) I can see the two statements without any Exception.
> But the problem is when I see on the Performance viewer (perfmon.exe/
> perfmon.
> msc->Sqlserver:General Statistics, User Connections) it shows Number of
> users
> connected to the system is like thousands and more day by day ,Means the
> number of open connections are in Hundreds…thousands and more . I am also
> loosing the connections immediately with out any exception.
> But still I am seeing open connection.
> Please can anybody sort out my problem,
> Is there a known bug like this? Is there a way to trace the SQLOLEDB
> provider?
>
Sounds like OleDb Session Pooling.
|||"malkeshsheth via droptable.com" <u13567@.uwe> wrote in message
news:6b18cb6b1bfe5@.uwe...
> Hi,
> I have troubles with a threaded application on W2003 server. It
> seems to leave open connections behind time to time, it sums to hundreds
> over
> a day (the application make thousands). It is using the SQLOLEDB provider,
> MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
> I put two Trace (print) statements : (1) Connection is opened.
> 2)
> Connection is Closed.) I can see the two statements without any Exception.
> But the problem is when I see on the Performance viewer (perfmon.exe/
> perfmon.
> msc->Sqlserver:General Statistics, User Connections) it shows Number of
> users
> connected to the system is like thousands and more day by day ,Means the
> number of open connections are in Hundreds…thousands and more . I am also
> loosing the connections immediately with out any exception.
> But still I am seeing open connection.
> Please can anybody sort out my problem,
> Is there a known bug like this? Is there a way to trace the SQLOLEDB
> provider?
>
OOPS.
Sounds like you're using Connection Pooling (or whatever they call it in
OleDb)
See, eg
Support WebCast: ODBC Connection Pooling and OLEDB Session Pooling in
Microsoft Data Access Components
http://support.microsoft.com/kb/324686
David

Connection Leak

Hi,
I have troubles with a threaded application on W2003 server. It
seems to leave open connections behind time to time, it sums to hundreds ove
r
a day (the application make thousands). It is using the SQLOLEDB provider,
MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
I put two Trace (print) statements : (1) Connection is opened. 2)
Connection is Closed.) I can see the two statements without any Exception.
But the problem is when I see on the Performance viewer (perfmon.exe/ perfmo
n.
msc->Sqlserver:General Statistics, User Connections) it shows Number of user
s
connected to the system is like thousands and more day by day ,Means the
number of open connections are in Hundreds…thousands and more . I am also
loosing the connections immediately with out any exception.
But still I am seeing open connection.
Please can anybody sort out my problem,
Is there a known bug like this? Is there a way to trace the SQLOLEDB provide
r?
Regards,
Rasheeds
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200612/1"malkeshsheth via droptable.com" <u13567@.uwe> wrote in message
news:6b18cb6b1bfe5@.uwe...
> Hi,
> I have troubles with a threaded application on W2003 server. It
> seems to leave open connections behind time to time, it sums to hundreds
> over
> a day (the application make thousands). It is using the SQLOLEDB provider,
> MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
> I put two Trace (print) statements : (1) Connection is opened.
> 2)
> Connection is Closed.) I can see the two statements without any Exception.
> But the problem is when I see on the Performance viewer (perfmon.exe/
> perfmon.
> msc->Sqlserver:General Statistics, User Connections) it shows Number of
> users
> connected to the system is like thousands and more day by day ,Means the
> number of open connections are in Hundreds…thousands and more . I am als
o
> loosing the connections immediately with out any exception.
> But still I am seeing open connection.
> Please can anybody sort out my problem,
> Is there a known bug like this? Is there a way to trace the SQLOLEDB
> provider?
>
Sounds like OleDb Session Pooling.|||"malkeshsheth via droptable.com" <u13567@.uwe> wrote in message
news:6b18cb6b1bfe5@.uwe...
> Hi,
> I have troubles with a threaded application on W2003 server. It
> seems to leave open connections behind time to time, it sums to hundreds
> over
> a day (the application make thousands). It is using the SQLOLEDB provider,
> MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
> I put two Trace (print) statements : (1) Connection is opened.
> 2)
> Connection is Closed.) I can see the two statements without any Exception.
> But the problem is when I see on the Performance viewer (perfmon.exe/
> perfmon.
> msc->Sqlserver:General Statistics, User Connections) it shows Number of
> users
> connected to the system is like thousands and more day by day ,Means the
> number of open connections are in Hundreds…thousands and more . I am als
o
> loosing the connections immediately with out any exception.
> But still I am seeing open connection.
> Please can anybody sort out my problem,
> Is there a known bug like this? Is there a way to trace the SQLOLEDB
> provider?
>
OOPS.
Sounds like you're using Connection Pooling (or whatever they call it in
OleDb)
See, eg
Support WebCast: ODBC Connection Pooling and OLEDB Session Pooling in
Microsoft Data Access Components
http://support.microsoft.com/kb/324686
David

connection leak

Hi, in my web application i had a connection leak now that i solve this problem, i want to check if all connections are really closed.

I make a connection to an sql database.

I try on the database the sp_who procedue but i think, this is not that i can see if i colse all, connections

is there a other way to check or see if all connections are closed.

Enclosing the connection objects inside the "Using" block will make sure that the connection object is disposed off properly preventing any connection leaks. For e.g:

using(SqlConnection cn = new SqlConnection())

{

//DAL code

}

Check this out for details:

codebetter.com/blogs/john.papa/archive/2005/04/01/60984.aspx

Hope this helps,

Vivek

Connection Leak

Hi,
I have troubles with a threaded application on W2003 server. It
seems to leave open connections behind time to time, it sums to hundreds over
a day (the application make thousands). It is using the SQLOLEDB provider,
MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
I put two Trace (print) statements : (1) Connection is opened. 2)
Connection is Closed.) I can see the two statements without any Exception.
But the problem is when I see on the Performance viewer (perfmon.exe/ perfmon.
msc->Sqlserver:General Statistics, User Connections) it shows Number of users
connected to the system is like thousands and more day by day ,Means the
number of open connections are in Hundredsâ?¦thousands and more . I am also
loosing the connections immediately with out any exception.
But still I am seeing open connection.
Please can anybody sort out my problem,
Is there a known bug like this? Is there a way to trace the SQLOLEDB provider?
Regards,
Rasheeds
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200612/1"malkeshsheth via SQLMonster.com" <u13567@.uwe> wrote in message
news:6b18cb6b1bfe5@.uwe...
> Hi,
> I have troubles with a threaded application on W2003 server. It
> seems to leave open connections behind time to time, it sums to hundreds
> over
> a day (the application make thousands). It is using the SQLOLEDB provider,
> MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
> I put two Trace (print) statements : (1) Connection is opened.
> 2)
> Connection is Closed.) I can see the two statements without any Exception.
> But the problem is when I see on the Performance viewer (perfmon.exe/
> perfmon.
> msc->Sqlserver:General Statistics, User Connections) it shows Number of
> users
> connected to the system is like thousands and more day by day ,Means the
> number of open connections are in Hundredsâ?¦thousands and more . I am also
> loosing the connections immediately with out any exception.
> But still I am seeing open connection.
> Please can anybody sort out my problem,
> Is there a known bug like this? Is there a way to trace the SQLOLEDB
> provider?
>
Sounds like OleDb Session Pooling.|||"malkeshsheth via SQLMonster.com" <u13567@.uwe> wrote in message
news:6b18cb6b1bfe5@.uwe...
> Hi,
> I have troubles with a threaded application on W2003 server. It
> seems to leave open connections behind time to time, it sums to hundreds
> over
> a day (the application make thousands). It is using the SQLOLEDB provider,
> MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
> I put two Trace (print) statements : (1) Connection is opened.
> 2)
> Connection is Closed.) I can see the two statements without any Exception.
> But the problem is when I see on the Performance viewer (perfmon.exe/
> perfmon.
> msc->Sqlserver:General Statistics, User Connections) it shows Number of
> users
> connected to the system is like thousands and more day by day ,Means the
> number of open connections are in Hundredsâ?¦thousands and more . I am also
> loosing the connections immediately with out any exception.
> But still I am seeing open connection.
> Please can anybody sort out my problem,
> Is there a known bug like this? Is there a way to trace the SQLOLEDB
> provider?
>
OOPS.
Sounds like you're using Connection Pooling (or whatever they call it in
OleDb)
See, eg
Support WebCast: ODBC Connection Pooling and OLEDB Session Pooling in
Microsoft Data Access Components
http://support.microsoft.com/kb/324686
David|||Yes David,
i hope you are correct !!!! So do you tell me. Is there any such issue or my
program has any fault?
if my program is wrong then same connection leak problem has to shown on my
machine, but it shown on client machine. So can you suggest me to resolve
such issues in which aria I have to concentrate?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200612/1

Connection Issue

Hello EveryBody,
If SQL SERVER is configured as a Maximun concurrent user connections = 1 (I
know 0 = unlimited) in Connection tab of server property ? Who will able to
login to server ?
Because i have a similar case and not able to login with any other sql
server Login.
Is SQL SERVER admin (sa) will able to login ?
Pls let me know
I assume your single login is being used by Agent when it starts up so
nobody will be able to log in.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:88C5F9E7-30C7-4B59-A9F5-06BDBF159CA7@.microsoft.com...
> Hello EveryBody,
> If SQL SERVER is configured as a Maximun concurrent user connections = 1
> (I
> know 0 = unlimited) in Connection tab of server property ? Who will able
> to
> login to server ?
> Because i have a similar case and not able to login with any other sql
> server Login.
> Is SQL SERVER admin (sa) will able to login ?
> Pls let me know

Connection Issue

Hello EveryBody,
If SQL SERVER is configured as a Maximun concurrent user connections = 1 (I
know 0 = unlimited) in Connection tab of server property ? Who will able to
login to server ?
Because i have a similar case and not able to login with any other sql
server Login.
Is SQL SERVER admin (sa) will able to login ?
Pls let me knowI assume your single login is being used by Agent when it starts up so
nobody will be able to log in.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:88C5F9E7-30C7-4B59-A9F5-06BDBF159CA7@.microsoft.com...
> Hello EveryBody,
> If SQL SERVER is configured as a Maximun concurrent user connections = 1
> (I
> know 0 = unlimited) in Connection tab of server property ? Who will able
> to
> login to server ?
> Because i have a similar case and not able to login with any other sql
> server Login.
> Is SQL SERVER admin (sa) will able to login ?
> Pls let me know

connection history to SQL server?

Is there any way to see in Enterprise Manager a history of
connections (also via Enterprise Manager) to a db? I'm
trying to track down connections to our db that were done
by a consultant remotely through Enterprise Manager via
the Internet. The consultant had direct access and didn't
need to log onto our domain.
Thanks.Not unless you had SQL login auditing set to All or are auditing security
events on the server, in which case you should be able to see logons in the
NT Security event log
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09f301c3b372$868347a0$a001280a@.phx.gbl...
Is there any way to see in Enterprise Manager a history of
connections (also via Enterprise Manager) to a db? I'm
trying to track down connections to our db that were done
by a consultant remotely through Enterprise Manager via
the Internet. The consultant had direct access and didn't
need to log onto our domain.
Thanks.|||OK, so if I have SQL login auditing set to All where would
I see the events? In the server event log or somewhere in
Enterprise Manager?
>--Original Message--
>Not unless you had SQL login auditing set to All or are
auditing security
>events on the server, in which case you should be able to
see logons in the
>NT Security event log
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09f301c3b372$868347a0$a001280a@.phx.gbl...
>Is there any way to see in Enterprise Manager a history of
>connections (also via Enterprise Manager) to a db? I'm
>trying to track down connections to our db that were done
>by a consultant remotely through Enterprise Manager via
>the Internet. The consultant had direct access and didn't
>need to log onto our domain.
>Thanks.
>
>.
>|||In the SQL Error log (In Enterprise Manager this will be in the Management
subfolder for a server)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:660601c3b377$eb660e00$a601280a@.phx.gbl...
OK, so if I have SQL login auditing set to All where would
I see the events? In the server event log or somewhere in
Enterprise Manager?
>--Original Message--
>Not unless you had SQL login auditing set to All or are
auditing security
>events on the server, in which case you should be able to
see logons in the
>NT Security event log
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09f301c3b372$868347a0$a001280a@.phx.gbl...
>Is there any way to see in Enterprise Manager a history of
>connections (also via Enterprise Manager) to a db? I'm
>trying to track down connections to our db that were done
>by a consultant remotely through Enterprise Manager via
>the Internet. The consultant had direct access and didn't
>need to log onto our domain.
>Thanks.
>
>.
>|||Jasper,
Thanks for your responses. It didn't help me out, but I
had SQL login auditing set to Success (not All). When I
changed it to Success, I get this in the error
log "Configuration option 'show advanced options' changed
from 1 to 1. Run the RECONFIGURE statement to install..",
so I'm thinking something else is going on I have to
resolve.
I still need to catch our consultant connecting to the
server without first checking with us (which they are
supposed to do).
Brian
>--Original Message--
>In the SQL Error log (In Enterprise Manager this will be
in the Management
>subfolder for a server)
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:660601c3b377$eb660e00$a601280a@.phx.gbl...
>OK, so if I have SQL login auditing set to All where would
>I see the events? In the server event log or somewhere in
>Enterprise Manager?
>>--Original Message--
>>Not unless you had SQL login auditing set to All or are
>auditing security
>>events on the server, in which case you should be able to
>see logons in the
>>NT Security event log
>>--
>>HTH
>>Jasper Smith (SQL Server MVP)
>>I support PASS - the definitive, global
>>community for SQL Server professionals -
>>http://www.sqlpass.org
>>"Brian" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:09f301c3b372$868347a0$a001280a@.phx.gbl...
>>Is there any way to see in Enterprise Manager a history
of
>>connections (also via Enterprise Manager) to a db? I'm
>>trying to track down connections to our db that were done
>>by a consultant remotely through Enterprise Manager via
>>the Internet. The consultant had direct access and
didn't
>>need to log onto our domain.
>>Thanks.
>>
>>.
>
>.
>|||Yes, I should have mentioned, it's one of the options that requires a SQL
Service restart (don't know how practical that is for you). Another option
would be to use a server side trace capturing the audit login and logout
events. If they have a specific login, you can filter on that also.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:122401c3b37a$6ed460a0$a501280a@.phx.gbl...
Jasper,
Thanks for your responses. It didn't help me out, but I
had SQL login auditing set to Success (not All). When I
changed it to Success, I get this in the error
log "Configuration option 'show advanced options' changed
from 1 to 1. Run the RECONFIGURE statement to install..",
so I'm thinking something else is going on I have to
resolve.
I still need to catch our consultant connecting to the
server without first checking with us (which they are
supposed to do).
Brian
>--Original Message--
>In the SQL Error log (In Enterprise Manager this will be
in the Management
>subfolder for a server)
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:660601c3b377$eb660e00$a601280a@.phx.gbl...
>OK, so if I have SQL login auditing set to All where would
>I see the events? In the server event log or somewhere in
>Enterprise Manager?
>>--Original Message--
>>Not unless you had SQL login auditing set to All or are
>auditing security
>>events on the server, in which case you should be able to
>see logons in the
>>NT Security event log
>>--
>>HTH
>>Jasper Smith (SQL Server MVP)
>>I support PASS - the definitive, global
>>community for SQL Server professionals -
>>http://www.sqlpass.org
>>"Brian" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:09f301c3b372$868347a0$a001280a@.phx.gbl...
>>Is there any way to see in Enterprise Manager a history
of
>>connections (also via Enterprise Manager) to a db? I'm
>>trying to track down connections to our db that were done
>>by a consultant remotely through Enterprise Manager via
>>the Internet. The consultant had direct access and
didn't
>>need to log onto our domain.
>>Thanks.
>>
>>.
>
>.
>

connection from virtual ip address to firewall?

Hi,
We try to connect from a same named instance to a linked sql server in the
DMZ. The firewall has been set up to allow connections from the virtual ip
address of the instance to pass through and the linked server has been set
up correctly to connect through TCP/IP and the correct port. However, we
cannot connect and we see on the firewall that the requests are coming from
the active cluster node, not from the virtual ip address (even if we open a
Remote Desktop connection to the virtual server name). How can we solve
this?
Hans
This is how clustering works. Make sure you have the Node's IP defined for
access through the firewall.
Cheers,
Rod
"HVE" <eylenh@.hotmail.com> wrote in message
news:1e3yhnhwapvac.q9iklq79zipt$.dlg@.40tude.net...
> Hi,
> We try to connect from a same named instance to a linked sql server in the
> DMZ. The firewall has been set up to allow connections from the virtual ip
> address of the instance to pass through and the linked server has been set
> up correctly to connect through TCP/IP and the correct port. However, we
> cannot connect and we see on the firewall that the requests are coming
from
> the active cluster node, not from the virtual ip address (even if we open
a
> Remote Desktop connection to the virtual server name). How can we solve
> this?
> Hans

Connection from ASP- ODBC vs. SQLOLEDB

I am having a problem in an ASP site (HTTP/1.1:New application failed which
I believe has been caused by inefficient database connections/memory leaks).
One of the recent changes to the way connections are made is
changed connection string from
driver={SQL Server};server=production;uid=pub;pwd=pub;
database=pub
to
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pub;Data Source=Production
I thought that the OLE DB Provider is quicker/more efficient than the ODBC
driver for SQL Server, isn't it (although in one of the ASP file, I had to
change the CursorLocation used by ADO Recordset from adUseServer to
adUseClient to get a specific ASP file functioning).
Would I normally expect any other adverse outcome switching from ODBC to
SQLOLEDB ?Hi Patrick,
Thank you for using the newsgroup an it is my pleasure to help you with you
issue.
As for you question of when develope ASP application, which is the
recommended method to connect the database, from my experiece, for the
script, ADO is preferable.
1) ODBC is an API for database access that's both a formal and a de facto
industry standard. Besides being one of the most popular database
interfaces used by applications today, ODBC has gained status as the formal
call-level interface standard by American National Standards Institute
(ANSI) and International Organization for Standardization (ISO). SQL Server
provides a high-performance ODBC interface for all Windows-based
programming environments, and it can be distributed royalty-free with any
application. The SQL Server ODBC driver implements every function in the
ODBC 3 specification. In ODBC-speak, this makes it fully Level 2 (the
highest level) conformant.
RDO is an object interface that's closely tied to ODBC, which means that it
exposes all the functionality in the ODBC driver and is easily available to
Visual Basic programs. RDO supports building visual controls tied directly
to SQL Server data, which greatly reduces the amount of code that must be
written to display data on the screen.
2) OLE DB
OLE DB was first released by Microsoft in 1996 to provide a COM interface
to any tabular data source (that is, data that can be represented with rows
and columns). This includes data in spreadsheets and even text files. OLE
DB can be considered an object version of ODBC but is more powerful in that
it can access data from data sources beyond those that ODBC can access.
Unlike other object interfaces to SQL Server such as RDO, OLE DB doesn't
make programming a call-level interface like ODBC any easier. Also, because
OLE DB uses pointer data types extensively, it's only accessible from C and
C++.
3) ADO
ADO is a higher-level object interface on top of OLE DB that provides much
of the same functionality and performance. Because ADO is pointerless, it
can be accessed from scripting languages such as JScript and development
software such as Visual Basic, as well as from C and C++.
ADO is the recommended and supported interface for Internet applications
written using the Microsoft Visual InterDev development tool. Applications
written with Visual InterDev can call ADO from Active Server Pages (ASP)
and incorporate code written in VBScript or JScript.
For more information, I recommend you to ask you question in our related
newsgroup and the right engineer there will provide you detailed answer
about it:
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.publi
c.inetserver.asp.general
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||The ASP code is already using ADO (adodb.connection, adodb.recordset
objects). However, the connectionString used for opening a DB connection
has been changed from
driver={SQL Server};server=production;uid=pub;pwd=pub;database=pub
in old environment to
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pub;Data Source=Production
in new environment
The new provider is better than ODBC isn't it, in terms of the underlying
way ADO use to connect to SQL Server 2000?
Also, could any of the following have any effect on memory usage (e.g., poor
garbage collections in terms of not closing connections):
- SQL Server Client Network Utility- "Enable Shared Memory": I am not sure
whether this should be ticked or not. The Server is a Windows 2000 Server
SP4 which is a Domain Controller, running SQL Server 2000 Standard Edition,
ASP, ASP.NET as well as Microsoft Content Management Server 2002 SP1a. (The
application that has failed is the ASP application connecting using ADO)
- SQL Server Client Network Utility- Protocol: Should Named Pipes be used in
preference to TCP/IP
?
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:5JdW06MAEHA.320@.cpmsftngxa06.phx.gbl...
> Hi Patrick,
> Thank you for using the newsgroup an it is my pleasure to help you with
you
> issue.
> As for you question of when develope ASP application, which is the
> recommended method to connect the database, from my experiece, for the
> script, ADO is preferable.
> 1) ODBC is an API for database access that's both a formal and a de facto
> industry standard. Besides being one of the most popular database
> interfaces used by applications today, ODBC has gained status as the
formal
> call-level interface standard by American National Standards Institute
> (ANSI) and International Organization for Standardization (ISO). SQL
Server
> provides a high-performance ODBC interface for all Windows-based
> programming environments, and it can be distributed royalty-free with any
> application. The SQL Server ODBC driver implements every function in the
> ODBC 3 specification. In ODBC-speak, this makes it fully Level 2 (the
> highest level) conformant.
> RDO is an object interface that's closely tied to ODBC, which means that
it
> exposes all the functionality in the ODBC driver and is easily available
to
> Visual Basic programs. RDO supports building visual controls tied directly
> to SQL Server data, which greatly reduces the amount of code that must be
> written to display data on the screen.
> 2) OLE DB
> OLE DB was first released by Microsoft in 1996 to provide a COM interface
> to any tabular data source (that is, data that can be represented with
rows
> and columns). This includes data in spreadsheets and even text files. OLE
> DB can be considered an object version of ODBC but is more powerful in
that
> it can access data from data sources beyond those that ODBC can access.
> Unlike other object interfaces to SQL Server such as RDO, OLE DB doesn't
> make programming a call-level interface like ODBC any easier. Also,
because
> OLE DB uses pointer data types extensively, it's only accessible from C
and
> C++.
> 3) ADO
> ADO is a higher-level object interface on top of OLE DB that provides much
> of the same functionality and performance. Because ADO is pointerless, it
> can be accessed from scripting languages such as JScript and development
> software such as Visual Basic, as well as from C and C++.
> ADO is the recommended and supported interface for Internet applications
> written using the Microsoft Visual InterDev development tool. Applications
> written with Visual InterDev can call ADO from Active Server Pages (ASP)
> and incorporate code written in VBScript or JScript.
> For more information, I recommend you to ask you question in our related
> newsgroup and the right engineer there will provide you detailed answer
> about it:
>
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.publi
> c.inetserver.asp.general
> Hope this helps.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Patrick,
Thank you for your update.
In my last reply, I just want to say to choose the OLEDB, sorry for the
mistype. For you questions, I will answer them one by one
1)As for the ASP will use the ADO to connect the database and in my last
email, I said ADO is a higher-level object interface on top of OLE DB, so
the level of connect the database would be:
For the provider of OLEDB:
ASP -> ADO -> OLEDB -> Database
For the the provider of ODBC:
ASP -> ADO -> OLEDB -> ODBC -> Database
Also, if the database is SQL Server, you could get for more from OLEDB for
both of them are Microsoft technologies.
Please refer to the following link for detailed information
Choosing Your Data Access Strategy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoledb/htm
l/choosingcomponents.asp
2) Named Pipes vs. TCP/IP Sockets
In a fast local area network (LAN) environment, Transmission Control
Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are
comparable in terms of performance. However, the performance difference
between the TCP/IP Sockets and Named Pipes clients becomes apparent with
slower networks, such as across wide area networks (WANs) or dial-up
networks. This is because of the different ways the interprocess
communication (IPC) mechanisms communicate between
peers.
For named pipes, network communications are typically more interactive. A
peer does not send data until another peer asks for it using a read
command. A network read typically involves a series of peek named pipes
messages before it begins to read the data. These can be very costly in a
slow network and cause excessive network traffic, which in turn affects
other network clients.It is also important to clarify if you are talking
about local pipes or network pipes. If the server application is running
locally on the computer running an instance of Microsoft SQL Server?2000,
the local Named Pipes protocol is an option. Local named pipes runs in
kernel mode and is extremely fast.
For TCP/IP Sockets, data transmissions are more streamlined and have less
overhead. Data transmissions can also take advantage of TCP/IP Sockets
performance
enhancement mechanisms such as windowing, delayed acknowledgements, and so
on, which can be very beneficial in a slow network. Depending on the type
of
applications, such performance differences can be significant.TCP/IP
Sockets also support a backlog queue, which can provide a limited smoothing
effect compared to named pipes that may lead to pipe busy errors when you
are attempting to connect to SQL Server.
In general, sockets are preferred in a slow LAN, WAN, or dial-up network,
whereas named pipes can be a better choice when network speed is not the
issue, as it
offers more functionality, ease of use, and configuration options. You
could just choose which protocol to use depends on you production
environment.
3) As for Shared Memory Net-Library is used only for intra-computer
communications, you could let it as the default setting on the client
network utility.
Hope this helps!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

connection error

Hi

I am getting following error while connecting in sql server. i have already disabled firewall, enabled remote connections using pipe and tcp and made sure the sql server and sql browser services are running.

but still the problem persists...can any help me ?

Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

thanks in advance

Do you have a named instacne or a default one ? how are you connecting, you will have to provide some more information on your situation to make it easier forus to give you the right suggestions.

Jens K. Suessmeyer

http://www.sqlserver2005.de