connections how many to use

hi,

i have an application for which i m using a connection which stays throughout the life of the application. this is a normal sqloledb connection.

but now i stumbled across datashape which i have to use for this complex query.

now the problem is that i will have to use another connection, as the provider is different.

what shud i do? 2 connections 4 a single user? i will need both the connections as while saving i need the shape connection again
and for browsing will need another.

thnxDoesn't sound like you have a choice.
Shouldn't be a problem unless you have msde or personal edition.|||but then with the number of users growing wouldnt that slow down things.
i also want to make sure performance isnt affected.

unless i solve this query: http://dbforums.com/t715674.html, i will have to use datashape.|||What type of application are we talking about? When using ASP(.NET) or VB(.NET) there is a thing called connection-pooling, and what it basically does is "cache" your connection to the database so each time you create it it doesn't really get created, it just gets re-enabled. This depends on one single thing; that the connectionstring is the same throughout your application. If you have 10 users using one connstring an 1 user using another both connections have to physically be created each time that 1 user has made a pagehit.

So what I'm basically saying is: avoid different connectionstrings if you can.|||well i m using the same connection for all users, m using VB 6 sp5, have defined a connectionstring in a bas module and that same string is used to connect so i can use pooling there.

But the problem is that the application requires me to connect to the d/b using MSDATASHAPE for fetching records, now i need to connect to the d/b to validate certain things on the fly like lookup and search.

can i use the msdatashape connection to use any other provider for executing normal sql queries?|||You might have troublt getting connection pooling to have any affect if the connections are made from the client.
If you have a remote DB access layer then it would - but then you probably wouldn't be holding connections.|||uh you should drop the shape thing and look into sql server analysis services.

with this, you can render an mdx statement in a view-- and you can have it take any shape you want.|||i didnt knw that.
do u knw any place where i can begin that?

also the problem with shape is that i have to then use different recordsets for viewing/editing etc for each child recordset.
thats not good at all is it?

Connection Timeout

Is there any configuration option that controls how long
a user stays connected to mssql server? I have an
application that logs into mssql server and then uses
that one login for all work never logging out, after
about 7 days(fairly consistently) the connection to the
mssql gets killed and the application has to be
restarted. I was just curious if there is some kind of
parameter within mssql server that kills logins after x
number of days?
Thanks,
CarlHi,
There is no parameter in SQL server which will kill the user after x number
of days.
Rather you can write ur own code based on the login_time in
master..sysprocesses table and compare with getdate() for each process id
available in the list.
If any of the SPID is older then x days you can use
KILL SPID to kill the user. SPID also you can take it from
master..sysprocesses tables
Thanks
Hari
MCDBA
"z-man" <anonymous@.discussions.microsoft.com> wrote in message
news:817d01c4076d$e655aa20$a601280a@.phx.gbl...
> Is there any configuration option that controls how long
> a user stays connected to mssql server? I have an
> application that logs into mssql server and then uses
> that one login for all work never logging out, after
> about 7 days(fairly consistently) the connection to the
> mssql gets killed and the application has to be
> restarted. I was just curious if there is some kind of
> parameter within mssql server that kills logins after x
> number of days?
> Thanks,
> Carl
>|||There isn't anything like that built into SQL Server. You
could write your own procedure to do something like that but
nothing built into SQL Server.
If the application is on PC, server other than SQL Server,
you may want to look into network connectivity issues. You
may want to check the event logs where the application is
and see if you can find any clues in the logs.
-Sue
On Thu, 11 Mar 2004 05:36:47 -0800, "z-man"
<anonymous@.discussions.microsoft.com> wrote:

>Is there any configuration option that controls how long
>a user stays connected to mssql server? I have an
>application that logs into mssql server and then uses
>that one login for all work never logging out, after
>about 7 days(fairly consistently) the connection to the
>mssql gets killed and the application has to be
>restarted. I was just curious if there is some kind of
>parameter within mssql server that kills logins after x
>number of days?
>Thanks,
>Carl|||There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
But, this is controlled by the OS, not SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||That's a good point...and looking at the keepalive settings
could be an option. After thinking about it more and that if
this happens about every 7 days, it may more likely be
related to a weekly process or something similar that is
hosing the connection. I think I'd go through the logs and
look for activity around the time the connection gets hosed.
-Sue
On Thu, 11 Mar 2004 21:02:41 GMT, kevmc@.online.microsoft.com
(Kevin McDonnell [MSFT]) wrote:

>There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
>But, this is controlled by the OS, not SQL.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
>