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.

Connection string for the Ms-SQl Server using ASP.net

Hello,

I am doing the web based project for the final year of my couse
in Information Technology using Asp.net and ms-sql server.But to start
with that I need to connect my web pages to the database.
So Can anyone tell me how to connect my page web to the ms-sql
server using Asp.net(Connection String)?.It will be better if someone
give me the code for the connection string along with the explaination
so that I don't have problem understanding it.

Any kind of help will be appreciated.

Thank You.

Suchen Chodankar.Hi
Check out

http://msdn.microsoft.com/library/d.../SecNetHT03.asp
http://msdn.microsoft.com/library/d.../SecNetch12.asp
http://msdn.microsoft.com/library/d...ml/secmod83.asp
http://www.connectionstrings.com/

John

"Suchen Chodankar" <suchen.chodankar@.gmail.com> wrote in message
news:295fd3c8.0503112318.57e54ca3@.posting.google.c om...
> Hello,
> I am doing the web based project for the final year of my couse
> in Information Technology using Asp.net and ms-sql server.But to start
> with that I need to connect my web pages to the database.
> So Can anyone tell me how to connect my page web to the ms-sql
> server using Asp.net(Connection String)?.It will be better if someone
> give me the code for the connection string along with the explaination
> so that I don't have problem understanding it.
> Any kind of help will be appreciated.
> Thank You.
> Suchen Chodankar.

Connection string for big uploading files

I have a webpage where I want to upload pdf files to my database (arround 2 MB). Putting them inside the database is the choice based on audit definitions the client has.

The problem is that for big file uploads, I get the "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."error.

I believe the solution is expanding the connection timeout time in the SQL connection. But I don't want to do that in web.config, as a small timeout is good for every page except this one.

What I was thinking about was something like, on button command:

- "create new connection string"

- use that connection string to upload the file using the datatables and tableadapters

- turn back to the default connection string.

How do I explicitly tell the system to "use THIS connection string on the next task" ?

Alternatively, another way of doing is also appreciated!

Doesn't sound like a database timeout. However, if it were, then you control the command timeout on the sqlcommand object, not the sqlconnection. It has nothing to do with the connection string.|||

How do you access the sqlcommand object? Because I do not explicitly declare any sqlcommand, open, close, etc; I declare a TableAdapter object, a DataTable Object and then access the query method of that datatable, so basically two lines to execute the query (that is actually in a tableadapter in app_code). How can one change the command timeout using this method?

|||No idea, I never use table adapters. IMHO they suck, and serve no real purpose other than to try and abstract out something that isn't that hard to begin with. If you want something done right...|||

In the Solution Explorer, go toyourDataSet.xsd->openyourDataSet.Designer.cs, then locate the TableAdapter class, you'll see it has a member like:

private System.Data.SqlClient.SqlCommand[] _commandCollection;

Then in some event (may be InitCommandCollection) you can set the property of the SqlCommands, for example:

private void InitCommandCollection() {
this._commandCollection = new System.Data.SqlClient.SqlCommand[1];
this._commandCollection[0] = new System.Data.SqlClient.SqlCommand();
this._commandCollection[0].Connection = this.Connection;
this._commandCollection[0].CommandText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate," +
" ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, \r\n " +
" ShipPostalCode, ShipCountry\r\nFROM Orders";
this._commandCollection[0].CommandType = System.Data.CommandType.Text;
this._commandCollection[0].CommandTimeout = 6000;
}

connection string based on current user

Hi All,

His there any way to build a connection string to sql server based on the current user credential?

I mean, intead of using

user id=Admin
password=adminPass

Is it possible to do something like

user id= Context.User.ID
pass=?

I'm asking cause, iI don't want to use impersonisation in my code.
So that I can be sure logged user only sees what they can

(I'm a newbie, so this whole thing may makes no point
(thanks for clarifying

Yes this is definitely possible. You have to create a class which implements IIdentity class and IPrincipal. Check out the article:

http://msdn2.microsoft.com/en-us/library/ms172766(VS.80).aspx

|||

Thanks for the tip.
I've difficulty to see how to make it work.

I'm developping a web control so i don't really have any control on the the login process.
This confuse me, Does the IIdentity tecnics still apply? how?
Is there something like:
Context.IIdentity that I can write on my connection string?
(maybe i'm just not understanding the flow
(thanks for any help

|||

Hi tomypow,

Based on my understanding, connection string is just some xml configuration file so definitely we cannot write any code within it. And by the way, I don't think use different connectin string for different logged in user is a good idea. If you have 10,000 users in your site, you will have to create 10,000 connectin strings--so, in my opinion, I would suggest you use role management in your application instead.

Role management helps you to manage authorization, allowing you to specify the resources users in your application are allowed to access. Role management lets you treat groups of users as a unit by assigning users to roles such as manager, sales, member, and so on. In Windows, you create roles by assigning users to groups such as Administrators, Power Users, and so on.

After you have established roles, you can create access rules in your application. For example, your site might include a set of pages that you want to display only to members. Similarly, you might want to show or hide a part of a page based on whether the current user is a manager. With roles, you can establish these types of rules independent from individual application users. For example, you do not have to grant individual members of your site access to member-only pages; instead, you can grant access to the role of member and then simply add and remove users from that role as people sign up or allow their memberships to lapse.

I would suggest you read some materials on msdn :http://msdn2.microsoft.com/en-us/library/5k850zwb.aspx

Hope my suggestion helps

|||

Thanks Bo Chen.
It's all very clear now.
I will definitively use the role based approach.

connection string based CREATE CUBE supported in AS 2005.

Hi,

I am trying to use the CREATECUBE functionality in AS 2005 through
connection string property, but getting following error:

Microsoft OLE DB Provider for Analysis Services 2005:
The following system error occurred: Unspecified error .

csSourceDSN=PROVIDER=MSOLAP;DATASOURCE=WOTTRANSUBHSXP\DEV;INITIAL
CATALOG=National;

CREATE CUBE [National] (
DIMENSION [Line],
LEVEL [All Line] TYPE ALL,
LEVEL [Line],
LEVEL [Brand],
LEVEL [Item Name],
DIMENSION [Date],
LEVEL [All Date] TYPE ALL,
LEVEL [Year] TYPE YEAR,
LEVEL [Quarter] TYPE QUARTER,
LEVEL [Month] TYPE MONTH,
DIMENSION [Market],
LEVEL [All Market] TYPE ALL,
LEVEL [Market],
DIMENSION [State],
LEVEL [All State] TYPE ALL,
LEVEL [State],
LEVEL [Outlet],
MEASURE [Quantity] FUNCTION SUM FORMAT '#,#',
MEASURE [Cost] FUNCTION SUM FORMAT 'Standard',
MEASURE [Revenue] FUNCTION SUM FORMAT 'Standard'
)

INSERT INTO [National](
[Line].[Line],
[Line].[Brand],
[Line].[Item Name],
[Date].[Year],
[Date].[Quarter],
[Date].[Month],
[Market].[Market],
[State].[State],
[State].[Outlet],
[Measures].[Quantity],
[Measures].[Cost],
[Measures].[Revenue]
)
OPTIONS ATTEMPT_ANALYSIS
SELECT
[National].[Line:Line],
[National].[Line:Brand],
[National].[Line:Item Name],
[National].[Date:Year],
[National].[Date:Quarter],
[National].[Date:Month],
[National].[Market:Market],
[National].[State:State],
[National].[State:Outlet],
[National].[Measures:Quantity],
[National].[Measures:Cost],
[National].[Measures:Revenue]
FROM [National]
WHERE [Line:Line] = 'Microwaves'
AND [Date:Year] = '1994'

I also tried using CREATE GLOBAL CUBE statement:
"CREATE GLOBAL CUBE [National123] Storage 'C:\\National1.cub' FROM
[National] ( MEASURE [National].[Quantity], DIMENSION
[National].[Line] ( LEVEL [Line], LEVEL [Brand], LEVEL [Item
Name] ) )";

Any idea if we can still use CREATECUBE & INSERTINTO properties in AS
2005 or we need to switch to CREATE GLOBAL CUBE?

Any help in this regard would be appreciated.

Thanks,
Santosh.

Hi Santosh,

No, you can't use this syntax to create a local cube from an AS2005 server cube any more. The CREATE GLOBAL CUBE syntax will work, as will using XMLA to create your local cube.

Chris

|||

Hi Chris,

Has this thing been documented somewhere on msdn?

I need a reference in order to put in my document, which would substantiate this change.

Thanks,
Santosh.

|||

No, the only place that it's documented as far as I know is the chapter on local cubes in 'MDX Solutions' second edition, which I updated from the first edition. I got the information that CREATE CUBE is no longer supported direct from the dev team.

Chris

|||Thanks Chris. Even this reference is good enough for me.

Connection Problem, MS Personal Website Starter Kit

I have had a local Website up and running based on the MS Personal Website Starter Kit using Visual Studio 2005 Professional and SQL Server Express.

I am now trying to get the databases to run on SQL Server 2005 Standard Edition but can't get the connection to work.

I have changed the connection string in the Web.Config file from:

connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient"

To

connectionString="Data Source=.\scastle;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient"

But am getting the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

Line 5: Sub Application_Start(ByVal sender As [Object], ByVal e As EventArgs)

Line 6: AddHandler SiteMap.SiteMapResolve, AddressOf Me.AppendQueryString

Line 7: If (Roles.RoleExists("Administrators") = False) Then

Line 8: Roles.CreateRole("Administrators")

Line 9: End If

I am able to connect OK in both Server Management Studio (Server Name: SCastle, Windons Authentication) and clicking Connect to Database in Server Manager in Visual Studio (Server Name: (local)).

Have I got the connection string wrong or do I need to set up additional permissions in the database?

Thanks for your help.

Stephen

Hi,

the user instance feature only work with the Sql Server Express Edition. If you want to use the database with the Standard edition you will have to attach the database to the Server instance (to make it server controlled) and then change the connectionstring to the following (assuming that the local server holds an instance of the name scastle)

connectionString="Data Source=.\scastle;Integrated Security=True;Initial Catalog=YourDBnameafterattachment providerName="System.Data.SqlClient"

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens

Thanks for the response. I have tried adding the following in line with your advice but still get the same error:

<connectionStrings>

<add name="Personal" connectionString="Data Source=.\scastle;Integrated Security=True;Initial Catalog=Personal" providerName="System.Data.SqlClient"/>

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=.\scastle;Integrated Security=True; Initial Catalog=ASPNetDB"/>

</connectionStrings>

Any idea what is wrong?

Thanks

Stephen

|||You should then also check of you enbled remote connections (see the screencast on my site for more information)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks again Jens.

Problem has been solved by changing Data Source from ".\scastle" to "(local)".

Stephen