Reusing Connections
Jan 2, 2006
Every time my asp.net app needs to open a connection, it tries to establish a new connection with the mssql server. I´ve already set the max pool size property in the connection string. After that, my app raises an "time out"error saying it couldn´t obtain a connection from the pool. The problem is that I have a lot of iddle connections. With the Enterprise Manager I can see the status of the connections. They´re all the same "awaiting command". How can I reuse this connections? I know that the connection string must be the same for all connections and it is. I´ve set it in the web.config file. If I remove the max pool size property from the connection string I get a lot, I mean A LOT of connections with the sql server. Any ideas?
View 1 Replies
ADVERTISEMENT
Apr 19, 2008
Hi ,
I want to open and close sql connection only once and want to use in every function without open or close this connection in class file in asp.net 2003 .
how can it possible .
View 1 Replies
View Related
Nov 12, 2003
Hi all,
I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts.
Can anyone help
View 1 Replies
View Related
Feb 20, 2008
Okay, so yes, I am new to SQL server...
I have this SP below, and I am trying to reuse the value returned by the Dateofplanningdate column so that I don't have to enter the code for each additional column I create. I have tried temp tables and derived tables with no luck.
REATE Proc CreateMasterSchedule
as
Select
dbo.[MOP_Planning Overview].warehouse,
dbo.[MOP_Planning Overview].[Item Number],
dbo.[MOP_Planning Overview].[Planning Date],
CAST (Convert (char(10),[Planning Date], 110)as DateTime)as DateofPlanningDate,
(case when dbo.[MOP_Planning Overview].[Order Category]='101' AND CAST (Convert (char(10),[Planning Date], 110)as DateTime)
<= (CAST (Convert (char(10),(dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())-7),110) as DateTime)-1)then dbo.[MOP_Planning Overview].[Transaction Quantity - Basic U/M] else 0 end)as PriorInProc,
If I try to use DateofPlanningDate in the above case statement, I get the invalid column name error.
Basically, I just need a way to reuse the value returned by this column.
Can anyone help?
View 6 Replies
View Related
Oct 23, 2006
I was looking at a means of reusing dialogs.
The attempt I tried was looking up an existing dialog in the conversation_endpoints.
However on doing a scale test I would that the non blocking I was hoping wasn't happening. Even through I was giving each spid a new dialog by using a conversation_group_id related to the spid. I found that the following SQL was blocked by a transaction that contains a begin dialog. This suggests the locking on conversation_endpoints is too excessive.
select top 1 conversation_handle
from sys.conversation_endpoints ce
join sys.services s on s.service_id = ce.service_id
join sys.service_contracts c on c.service_contract_id = ce.service_contract_id
where s.name = 'jobStats'
and ce.far_service = 'jobStats'
and (ce.far_broker_instance = @targetBroker OR @targetBroker = 'CURRENT DATABASE')
and ce.state IN ('SO','CO')
and ce.is_initiator = 1
and (ce.conversation_group_id = @conversation_group_id )--or @conversation_group_id is null)
and c.name = @contractName
View 2 Replies
View Related
Apr 30, 2008
In the Package configurations wizard, I am trying to edit an existing configuration using the edit button. In the Configuration Filter, I get the list of several filters (the filters which were used for other packages). Whe I try to reuse an used filter, it is forcing me to set a new value and when I go back to SQL Server tables , I see the old value has got erased.
Can I not use an existing filter?. Do I need to use new filters for every new package?.
Thanks.
View 1 Replies
View Related
Mar 23, 2004
Hi,
I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?
Thanks,
S.
View 7 Replies
View Related
Aug 30, 2007
Hi
I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks
DECLARE @dialog_handle uniqueidentifier
select @dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'
IF @dialog_handle is NULL
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [initiator]
TO SERVICE 'target'
ON CONTRACT [portcontract];
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [Port] (@msg)
View 1 Replies
View Related
May 10, 2006
Is it possible to reuse a Lookup component which is configured with Full chaching?
My requirement is as follows....
A input file have 2 columns called CurrentLocation and PreviousLocation. In the dataflow, values of these two columns needs to be replaced with values from a look up table called "Location".
In my package i have added two LookUp components which replaces values of CurrentLocation and PreviousLocation with the values available in the table "Location". Is there any way to reuse the cache of first lookup component for second column also?
View 9 Replies
View Related
Oct 22, 2007
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
View 2 Replies
View Related
Aug 5, 2007
Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations?
when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation.
But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle?
this happens in both target and initator db.
I've tried to understand this by i don't.
Also for the moment i don't end conversations. But as i understand it this shouldn't matter.
Also the message successfully arives to the target and sys.transmission_queue is empty in both databases.
Neither queues have any error messages in them.
Thanx
View 1 Replies
View Related
Feb 1, 2007
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
Sachin
View 4 Replies
View Related
Mar 3, 2004
Hello,
I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object....put a file watch so that if the DB's change my params it re-pulls them again.
*nice*.....
Then I get the best of both worlds...caching...and no parameter writing...
Eric
View 4 Replies
View Related
Oct 26, 2005
I have 5 packages in a solution.
View 19 Replies
View Related
May 17, 2005
Hi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
View 1 Replies
View Related
Dec 27, 2007
Hi I have some simple questions of for MS SQL. Say I have a database with a table Called Company. In this table I have Employee's with these columns(EmployeeID<PK>, FirstName, LastName) Now I am inserting some data like this.EmployeeID FirstName, LastName------------------------------------------------------- 1 Bob Smith2 Joe Mitter Now Say I have this situation(this will look weird for this example but for another examples and the stuff I am working on it make sense). Say I have a new Employee Called Jessica White. Jessica must be the second record. So I need to insert it between the 1st and 2nd record. Like I said I am very noob at doing this stuff so I am not sure how I would do it. I first was going to my table and then went to modify. This brought up a blank grid with all my column names. I then started to add all my data in. I found out later to make my life easier I need to enter in certain spots null rows in it to help with formating(otherwise I would have had to figure out a way to do make null rows with c# code). I then went back to this grid and I tried to add a record before the data just like you if you used excel. I quickly found out you can not do that. This resulted me having to recreate the table and reput the all the data back in(this told me I was doing something very very wrong). I was then thinking of writing it with a query and I been playing around with it and still running into problems like say I had a table called test with a coloum called id<PK> and testss(yes bad names just playing around though).So if I did something like this:INSERT INTO testVALUES(2)INSERT INTO testVALUES(1)SELECT * FROM testNow I am trying to figure out how to insert something between rows 1 and 2. Would I have to drop the table or delete all the data and then run a saved copy of my script with the added change or what?My second question is what is a good site that has examples of all the commands(I don't want to read a book on it I have had a oracle sql course and well it was just boring and I did not learn too much.). I think the best way for me to learn is just continue doing my site and picking it up on the file and when I got a problem read about it and ask on the forums. Thanks
View 11 Replies
View Related
Jul 13, 2001
Very strange event. Installed new machine into NT 4 network, which has a PDC and a BDC. We have copies of all SQL databases on both the PDC and the BDC. I installed SQL 7 on new workstation. Throught Control Panel/ODBC connections I can reach the SQL files on bith the PDC and BDC. Howver when I try to use Enterprise Manager, I can connect to the tables on the PDC, but not the BDC. SQL lists both machines in the server group, but only allows me to access tables on PDC! Here is the Event information that I recived on the BDC:
The computer IVR1 tried to connect to the server NTBACKUPSERVER using the trust relationship established by the MASSCOM domain. However, the computer lost the correct security identifier (SID) when the domain was reconfigured.
How do I fix this?
View 2 Replies
View Related
Apr 14, 2000
HI all,
I have a problem here. I am having two computers both are loaded windows NT 4.0 AND SERVICE PACK 4 AND ALSO I INSTALLED SQL SERVER 7.0.
Now i want to connect those two servers , so pls anyone suggest me how i have to do.I connected both servers thru HUB.
Pls suggest me..
thank u..
--ram
View 1 Replies
View Related
Oct 9, 1998
I figure anyone who has done VB and SQL Server might have run into this.
>
> I am set up as a DBO on SQL Server with access to db1 and db2. My default
> is set to db2. From my local machine, I can log through VB(DSN-less) in
> to
> either one without a problem. BUT, if I go to another machine and try to
> log in through VB(DSN-less) to db1, it rejects my login. Here is my
> connect
> string :
>
> UID=xxxxxxx;PWD=pedro;DATABASE=db2;DRIVER={SQL
> Server};SERVER=Athens;DSN=``;
>
> The only thing that changes when I log into db1 is the database parameter-
> "DATABASE=db1".
>
> Am I doing something wrong here? Is there something that needs to be
> setup
> differently in SQL Server? Do I need to put something else into the
> connect
> string?
>
> This happens for the other developers as well, not just me.
>
> Any help would be GREATLY appreciated.
View 2 Replies
View Related
Apr 12, 2001
I am looking for some ideas on the following DTS challenge: I need to import the data from an Access 97 database into a set of tables(about 25). The location of the database can be anywhere on the users drive(s) and I will need to get the location of the database from a registry setting and use it to define the connection.
Thanks
View 1 Replies
View Related
Jun 11, 2004
We have a case where a user is using Excel sheet to connect the database on SQL2K and generally his connection runs in the upper 150 in numbers. He generally gets more than 150 connections open.
I know that it's not a good thing, but is there a way to limit it or even kill it after it reaches max?
What are the disadvantages of opening too many connections?
View 1 Replies
View Related
Sep 7, 2006
I'm getting the classic message "The timeout period elapsed prior to obtaining a connection from the pool" etc when connecting to my SQL Server 2005 Express from a .Net application.
Then I try connecting, simultaneously, from a simple ASP.net thing I wrote just for testing and this works fine. So, then the connection pool can't be full, can it? Or, does each application have its own pool??
View 6 Replies
View Related
Apr 16, 2004
Ok, yeah, I'm being lazy...
Any comments?
View 8 Replies
View Related
Feb 9, 2006
Hi
I have heard that SQL Server can support upto 10,000 users,
What if my application , connects to the sql & does not do antything then , why does the SQL server slow down with just 700 users.
I mean just the connection is established & NOTHING further is done.
Thanks
View 6 Replies
View Related
Nov 29, 2007
hi,
have been looking for ages for somewhere that might be able to answer my question so thought i'd try here.
anyhows, my place of work have goldmine 6.7 running on a server. the problem is that some of the client machines seem to be taking 2 connections to the server when logged into goldmine and some only 1.
when looking at the open sessions it shows a second for some users accessing pipesqlquery. all users show this but only some show it as a second connection.
please any ideas
View 5 Replies
View Related
Dec 21, 2007
Is there any type of performance gain to using DSN connections? Or any real advantage to using them over DSN-Less connections?
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
View 5 Replies
View Related
Jul 23, 2005
As most of you are aware, when you close a VB.NET connectrion to a SQLserver, the connection doesn't actually drop right then and there.From my tests, using VB.NET 2003 and SQL2K, the connection doesn'ttimeout and drop off for 6 1/2 minutes. How can one force thisconnection to immediately drop off with code?
View 12 Replies
View Related
Oct 25, 2005
HiI am a dba for sql server recently we are facing problems with the no ofconnections.we have a database called ( x ) every day almost million users is using thatdatabse after some time the cpu showing 100 percent utilization an errorthrowing like odbc error connection so in that case no one is able to connectto the server.when we pause it then restart the server for some time it looksgood then again it will go to 100 % utilization.but there is no blockingoccuringif anyone knows this problem please helpyour help would be appreciated.Thanks--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
View 4 Replies
View Related
Dec 26, 2006
Hi I would like to know about connections of sa, where SQL store thenumber of connections
View 1 Replies
View Related
Mar 28, 2007
im writing a service that will be on a app serverthis is one of many service's to process Recordsets from Sql Server...1 service may have 20 Processes to Completeexamplemain class PreProcess()method CleanNames();method updateNames();methodr ValidateContracts();Various things must be completedSuedo Example belowMy question isin the main class make my connection and keep it for all sub membersor connect in each memberA. This Way opens connection...closes connectmethod cleanNames()1 get connection2 get recordset3. Close Connection4 process Recordset5. Open Connection4 Commit Changes6. Close Connection()finished cleanames()ormethod cleanNames()1 get connection2 get recordset3 process Recordset4 Commit Changes5. Close Connection()finished cleanames()orthis final way is in the parent Class or Calling Functionsends in the connections holds it open till the service iscompleted of all jobs its Suppose to dothen releases the connectionmethod cleanNames(oconn) connection as parameter1 get recordset2 process Recordset3 Commit Changesfinished cleanames()ThanksDaveP
View 1 Replies
View Related
Jul 7, 2007
I have a c# winapp and use SqlHelper to CRUD operation wint an sql server 2005 database, every time mi data access layer opens an connections, I do Close() and Dispose() after the transaction, and I execute SP_WHO and the database server shows me that my winapp has generated a lot of connections, I dont understand why since i clean every connection, Also, I m not using pooling since my connection string. Has someone has faced this before??
Best Regards
Joseph
View 5 Replies
View Related
Jan 29, 2007
I have .net 2.0 framework and SQL V 8.0 on my web server.
I am trying to create a DSN and I am not given the OLEDB provider for SQL as an option for my list of providers. Is there a download that will add this ? What do I do to get it????
View 3 Replies
View Related
