Connections...

Dear All,
We are getting a lot of sleeping (and orphan) connections
to our database, so I would like to create a script to
kill them based upon them being sleeping for over 2 hours.
Can anyone point me to the table that holds this info ?
I don't want the script as I will be trying that myself.
TIA
JimThe table is master.dbo.sysprocesses...
Sleeping is not bad... It simply means that the connection is open but not
currently doing any work... Most connections will be in this state most of
the time. Also consider the effect of connection pooling...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:351b01c48f4a$17717580$a601280a@.phx.gbl...
> Dear All,
> We are getting a lot of sleeping (and orphan) connections
> to our database, so I would like to create a script to
> kill them based upon them being sleeping for over 2 hours.
> Can anyone point me to the table that holds this info ?
> I don't want the script as I will be trying that myself.
> TIA
> Jim
>|||Thanks Wayne,
In our case its usually because the data objects have made
a new connection, so what we have is a lot of unused
connections by the same person. Its those it want to get
rid of.
Thanks
Jim
>--Original Message--
>The table is master.dbo.sysprocesses...
>Sleeping is not bad... It simply means that the
connection is open but not
>currently doing any work... Most connections will be in
this state most of
>the time. Also consider the effect of connection
pooling...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:351b01c48f4a$17717580$a601280a@.phx.gbl...
>> Dear All,
>> We are getting a lot of sleeping (and orphan)
connections
>> to our database, so I would like to create a script to
>> kill them based upon them being sleeping for over 2
hours.
>> Can anyone point me to the table that holds this info ?
>> I don't want the script as I will be trying that myself.
>> TIA
>> Jim
>>
>
>.
>|||If that's the case, either your programmers aren't properly cleaning up the
connections, or these connections are still in use by the data objects and
you'll cause headaches for your developers if you take this path.
Personally, I'd make them perform a code review and fix the problem - most
connection leaks are due to sloppy coding practices.
--
Michael D. Long
Microsoft MVP - Windows SDK
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:376e01c48f66$403ea520$a301280a@.phx.gbl...
> Thanks Wayne,
> In our case its usually because the data objects have made
> a new connection, so what we have is a lot of unused
> connections by the same person. Its those it want to get
> rid of.
> Thanks
> Jim
>
> >--Original Message--
> >The table is master.dbo.sysprocesses...
> >
> >Sleeping is not bad... It simply means that the
> connection is open but not
> >currently doing any work... Most connections will be in
> this state most of
> >the time. Also consider the effect of connection
> pooling...
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Mariner, Charlotte, NC
> >www.mariner-usa.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:351b01c48f4a$17717580$a601280a@.phx.gbl...
> >> Dear All,
> >> We are getting a lot of sleeping (and orphan)
> connections
> >> to our database, so I would like to create a script to
> >> kill them based upon them being sleeping for over 2
> hours.
> >>
> >> Can anyone point me to the table that holds this info ?
> >>
> >> I don't want the script as I will be trying that myself.
> >>
> >> TIA
> >> Jim
> >>
> >>
> >
> >
> >.
> >sqlsql

Connections...

Dear All,
We are getting a lot of sleeping (and orphan) connections
to our database, so I would like to create a script to
kill them based upon them being sleeping for over 2 hours.
Can anyone point me to the table that holds this info ?
I don't want the script as I will be trying that myself.
TIA
JimThe table is master.dbo.sysprocesses...
Sleeping is not bad... It simply means that the connection is open but not
currently doing any work... Most connections will be in this state most of
the time. Also consider the effect of connection pooling...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:351b01c48f4a$17717580$a601280a@.phx.gbl...
> Dear All,
> We are getting a lot of sleeping (and orphan) connections
> to our database, so I would like to create a script to
> kill them based upon them being sleeping for over 2 hours.
> Can anyone point me to the table that holds this info ?
> I don't want the script as I will be trying that myself.
> TIA
> Jim
>|||Thanks Wayne,
In our case its usually because the data objects have made
a new connection, so what we have is a lot of unused
connections by the same person. Its those it want to get
rid of.
Thanks
Jim

>--Original Message--
>The table is master.dbo.sysprocesses...
>Sleeping is not bad... It simply means that the
connection is open but not
>currently doing any work... Most connections will be in
this state most of
>the time. Also consider the effect of connection
pooling...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:351b01c48f4a$17717580$a601280a@.phx.gbl...
connections[vbcol=seagreen]
hours.[vbcol=seagreen]
>
>.
>|||If that's the case, either your programmers aren't properly cleaning up the
connections, or these connections are still in use by the data objects and
you'll cause headaches for your developers if you take this path.
Personally, I'd make them perform a code review and fix the problem - most
connection leaks are due to sloppy coding practices.
Michael D. Long
Microsoft MVP - Windows SDK
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:376e01c48f66$403ea520$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> In our case its usually because the data objects have made
> a new connection, so what we have is a lot of unused
> connections by the same person. Its those it want to get
> rid of.
> Thanks
> Jim
>
> connection is open but not
> this state most of
> pooling...
> (PASS) and it's
> message
> connections
> hours.

Connections...

Dear All,
We are getting a lot of sleeping (and orphan) connections
to our database, so I would like to create a script to
kill them based upon them being sleeping for over 2 hours.
Can anyone point me to the table that holds this info ?
I don't want the script as I will be trying that myself.
TIA
Jim
The table is master.dbo.sysprocesses...
Sleeping is not bad... It simply means that the connection is open but not
currently doing any work... Most connections will be in this state most of
the time. Also consider the effect of connection pooling...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:351b01c48f4a$17717580$a601280a@.phx.gbl...
> Dear All,
> We are getting a lot of sleeping (and orphan) connections
> to our database, so I would like to create a script to
> kill them based upon them being sleeping for over 2 hours.
> Can anyone point me to the table that holds this info ?
> I don't want the script as I will be trying that myself.
> TIA
> Jim
>
|||Thanks Wayne,
In our case its usually because the data objects have made
a new connection, so what we have is a lot of unused
connections by the same person. Its those it want to get
rid of.
Thanks
Jim

>--Original Message--
>The table is master.dbo.sysprocesses...
>Sleeping is not bad... It simply means that the
connection is open but not
>currently doing any work... Most connections will be in
this state most of
>the time. Also consider the effect of connection
pooling...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:351b01c48f4a$17717580$a601280a@.phx.gbl...
connections[vbcol=seagreen]
hours.
>
>.
>
|||If that's the case, either your programmers aren't properly cleaning up the
connections, or these connections are still in use by the data objects and
you'll cause headaches for your developers if you take this path.
Personally, I'd make them perform a code review and fix the problem - most
connection leaks are due to sloppy coding practices.
Michael D. Long
Microsoft MVP - Windows SDK
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:376e01c48f66$403ea520$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> In our case its usually because the data objects have made
> a new connection, so what we have is a lot of unused
> connections by the same person. Its those it want to get
> rid of.
> Thanks
> Jim
>
> connection is open but not
> this state most of
> pooling...
> (PASS) and it's
> message
> connections
> hours.

connections to SQL Server

Hi All,
I need to put a script together that would tell me how many connections to SQL Server there are at any given time. Which table would store this info?
Thanks.Define what you mean by connections, and which version of SQL Server you are targeting. One simplistic answer for SQL 2000 would be:SELECT Count(DISTINCT spid)
FROM master.dbo.sysprocesses
WHERE 50 < spid-PatP|||The information from the sp_who/sp_who2 stored procedures might also be useful...

ConnectionManagerOleDb usage in a Script Component

Hi,

I have a need to use a shared connection manager of type OLEDB inside a Script Component. I guess that the right way to get the OLEDB connection is to override the AcquireConnections method, case the shared connection manager to the ConnectionManagerOleDb interface and call the AcquireConnection method.

The problem is in the return value of the AcquireConnection method (it’s Object). My assumption is that in case of OLEDB connection usage, the AcquireConnection method returns some OLE DB COM interface. Does some of you tried to cast it to some interface/class exposed by DTS (or some standard COM interface from MDAC)?

PS I’m aware about the possibility to use ADO.NET connection managers, however it’s not a case for me because of DataReader Source limitations (OLE DB Source suits my needs much better).

I would really appreciate your answer. Thanks in advance!

Regards,

Dmytro

Hi,

I am not using override acquireconnection method for sharing connections. Instead of that I have created a global connection and created a property for opening the connection. Inside the property, if it is null I will open a new connection, otherwise I will return the existing connection. I have overriden the 'releaseconnection' method, to release that connection. It is working fine. Hope this would help u.

|||

Hi,

I am facing similar situation. Have you got a solution. It will be great if you can share the way you resolved it.

Thanks

|||

Hi,

Thanks for your reply! What is the type of the global connection that you created and what would happen if your package is executed in a transaction? Do you take transactions into consideration?

Thanks!

/Dmytro

|||

Hi,

Unfortunately I haven't found a way how to solve this issue yet. If I find it, I would definitely post a solution here.

/Dmytro

|||

Dmytro Kryvko wrote:

I have a need to use a shared connection manager of type OLEDB inside a Script Component.
...
My assumption is that in case of OLEDB connection usage, the AcquireConnection method returns some OLE DB COM interface.

This is correct, the object is the OLEDB COM object, namely OLEDB session created by IDBCreateSession::CreateSession call. For using it, see OLEDB reference in MSDN.

Note that these interfaces are not meant to be used from .NET. If you really need to use it, you should create a custom component in C++, rather than using script component.

Connection to Paradox

Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs = CreateObject("ADODB.RecordSet")
DB_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MYDBPath;Extended Properties=Paradox 5.x;"
strSQL = " UPDATE UMTest SET Field1 = 1.222 WHERE Col1ID = '" & Value1
& "' AND Col2 = '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.
Thanks.I don't do Paradox but to do any DML through Jet, you'd want to make sure
the table has a primary key defined. Also, you might want post this to a
paradox group.
http://www.thedbcommunity.com
-oj
"Yunus's Group" <yunusasmath@.gmail.com> wrote in message
news:1123266062.918633.324570@.g14g2000cwa.googlegroups.com...
> Hello,
> I am trying to connect to paradox database for the purpose of updating
> the record(s) in DTS ActiveX Script Task.
> I used the following connection string. But I get an error while
> connection. I have "UMTest.db" and "UMTest.px" files in the
> "C:\MYDBPath" in my local drive.
> Set objRs = CreateObject("ADODB.RecordSet")
> DB_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\MYDBPath;Extended Properties=Paradox 5.x;"
> strSQL = " UPDATE UMTest SET Field1 = 1.222 WHERE Col1ID = '" & Value1
> & "' AND Col2 = '" & Value2 & "'"
> objRs.Open strSQL, DB_STRING
> I get the following error.
> Error Source: Microsoft Jet Database engine.
> Error Description: No Value given for one or more required parameters.
> Connection string looks alright to me. I don't know what is other
> reason which is causing this error to happen.
> Thanks.
>|||First, are you sure that's it's bombing out on your connection? If
you've verified this (try setting your strSQL to "SELECT 1" and see if
it still bombs out), then you may need to update your Paradox driver.
I've had to interface with a Paradox db before, and nothing worked
until we got a new driver for it installed on the SQL Server.
Since we were simply extracting data from Paradox during a batch
process, we ultimately ended up buying a copy of Paradox, and doing a
limited install on the SQL Server (basically drivers only).
HTH,
Stu

Connection to Analysis Services 2005 using .NET 2.0

Hello,
I'm trying to use ASP.NET 2.0 to connect to a 2005 Analysis services database.
My script run on a IIS 6 web site under MS Server 2003 SP1.
For my test, the Analysis services database and the web site are on the same
server. My web site is configured as "Allowed anymous access" with the user
acount "IUSR_ServerName". The authenticated access is define to "Integrated
windows authentification".
When I execute my web page, the connection can't be established with de
database, and the following error is raised :
"
OpenEither the user, MyDomain\MyServer$, does not have access to the
MyDatabase database, or the database does not exist.
at
Microsoft.AnalysisServices.AdomdClient.AdomdConnec tion.XmlaClientProvider.Microsoft.AnalysisServices .AdomdClient.IExecuteProvider.ExecuteTabular(Comma ndBehavior
behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection
commandProperties, IDataParameterCollection parameters) at
Microsoft.AnalysisServices.AdomdClient.AdomdComman d.ExecuteReader(CommandBehavior
behavior) at
Microsoft.AnalysisServices.AdomdClient.AdomdComman d.System.Data.IDbCommand.ExecuteReader(CommandBeha vior
behavior) at System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at
_Default.getDataset(String p_mdxQuery)
"
This is my connection string : "Provider=MSOLAP;Data Source=MyServerIP;User
ID=MyID; pwd=MyPwd;Initial Catalog=MyCatalogue;Client Cache Size=25;Auto
Synch Period=10000".
Is there anybody who have a suggestion why the connection can not be
established ? or is anyone have a sample solution to access to an analysis
services database through a web site ?
The User ID and Pwd parameters on the connection string are only used
when connecting via http, so the are being ignored, Your ASP.NET site
will be trying to access SSAS using the identity defined in the
application pool (which by the look of it is using the default local
service account)
Your options as I see them are.
1) Change the identity of the application pool in IIS 6 to use a domain
account that has access to the cubes.
2) Configure SSAS for HTTP connections so that you can use the User ID
and Pwd parameters on the connection string
3) Set up SSAS to allow anonymous connections.
I have put these options in order from what I consider "best" to "worst"
from a security & performance standpoint. However this is just my
opinion, there may be factors in your particular situation that I am not
aware of that may cause you to prefer one of the options that I have
ranked lower.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***

Connection to Analysis Services 2005 using .NET 2.0

Hello,
I'm trying to use ASP.NET 2.0 to connect to a 2005 Analysis services databas
e.
My script run on a IIS 6 web site under MS Server 2003 SP1.
For my test, the Analysis services database and the web site are on the same
server. My web site is configured as "Allowed anymous access" with the user
acount "IUSR_ServerName". The authenticated access is define to "Integrated
windows authentification".
When I execute my web page, the connection can't be established with de
database, and the following error is raised :
"
OpenEither the user, MyDomain\MyServer$, does not have access to the
MyDatabase database, or the database does not exist.
at
Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Mi
crosoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(Command
Behavior
behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection
commandProperties, IDataParameterCollection parameters) at
Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBeh
avior
behavior) at
Microsoft.AnalysisServices.AdomdClient.AdomdCommand.System.Data.IDbCommand.E
xecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcT
able,
IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at
_Default.getDataset(String p_mdxQuery)
"
This is my connection string : "Provider=MSOLAP;Data Source=MyServerIP;User
ID=MyID; pwd=MyPwd;Initial Catalog=MyCatalogue;Client Cache Size=25;Auto
Synch Period=10000".
Is there anybody who have a suggestion why the connection can not be
established ? or is anyone have a sample solution to access to an analysis
services database through a web site ?The User ID and Pwd parameters on the connection string are only used
when connecting via http, so the are being ignored, Your ASP.NET site
will be trying to access SSAS using the identity defined in the
application pool (which by the look of it is using the default local
service account)
Your options as I see them are.
1) Change the identity of the application pool in IIS 6 to use a domain
account that has access to the cubes.
2) Configure SSAS for HTTP connections so that you can use the User ID
and Pwd parameters on the connection string
3) Set up SSAS to allow anonymous connections.
I have put these options in order from what I consider "best" to "worst"
from a security & performance standpoint. However this is just my
opinion, there may be factors in your particular situation that I am not
aware of that may cause you to prefer one of the options that I have
ranked lower.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***