Ensuring All Connections Are Closed.
Feb 22, 2007
In a previous post I asked the question relating to moving a file assoicated with a connection. It appears I need to close the connection first.
On advice, in a script task I created the following:
Dim dtsConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager
For Each dtsConnection In Dts.Connections
Dim LiveConnection As Object = dtsConnection.AcquireConnection(Nothing)
Dts.Events.FireInformation(0, "", "Connection Name : " + dtsConnection.Name, "", 0, False)
dtsConnection.ReleaseConnection(LiveConnection)
dtsConnection.Dispose()
Next
Dts.TaskResult = Dts.Results.Success
RetainSameConnection is set to true.
The dispose line is something I've added just to try - I've tried it without this line as well.
The next component then moves the file and fails complaining that the file is in use.
What can I do?
Regards
Guy
View 2 Replies
ADVERTISEMENT
Apr 16, 2007
I have a page that I have 3 connections. I've made sure that each of these are closed when they are not being used and opened just right before being used. I keep getting the error "There is already an open DataReader associated with this Command which must be closed first." This error might show up as being produced by a dataadapter or sqldatareader...I have many. I've even tried to make separate connections as some have mentioned for each...leaving me with 15+ connections. I have added "MultipleActiveResultSets=True" to the connection strings as some have mentioned. I just don't know where to go from here...
Is it possible that the problem lies in multiple instances of this page being opened? Also, the data refreshes every 15 seconds. I really need this to work, but I have no clue on how to fix this problem. The error is easy to reproduce by opening up multiple instances, but some of the times is doesn't give an error at all?!
View 4 Replies
View Related
Feb 14, 2008
Hi all
I have inherited an horrific load of mad commentless code.
It was working fine but I have just moved it from SQL Server 2000 to 2005.
No its has ground to a halt due to un-closed connections.
I therefore assume that SQL 2000 must have auto closed conncetions but 2005 doesn't (or something similar).
I am about to wade into the code and find the places where connections are opened but not closed.
This is going to take time
In the interests of getting my site back up and running does anyone know how to set 2005 to autoclose connections?
any help much appreciated.
ta
C
View 3 Replies
View Related
Mar 31, 2014
I have been investigating the number of connections activeinactive to a certain database server and I have stumbled across an application which seems to not be clearing its database connections.For one instance of a client there was >70 sql connections which eventuated from the closing and reopening one 1 screen in the culprut app. Once the application was closed all of the connections are recycled but its evident that within the application itself it is not correctly reusing already existing open connections.
I have raised a point with the main programmer that we need to investigate more into how the application is managingot managing its ADO .NET connections to SQL.
I am starting with doing some reading here URL... and I was hoping to get some more information about the possible impact of excessive sql connections on the SQL Server itself. Our organization is quite lucky in that our SQl Servers are Overspecced given their workload, bearing that in mind I would like to dig a bit deeper to get some stats if I can to highlight the scope of the issue to the managementprogrammers.Our SQL server peaks at 6500 processes and a good 70% of those are due to this applications mis-management of its sql connections.
View 6 Replies
View Related
Jan 23, 2008
For our database application we get our primary keys by calling a stored procedure that pass's in the table name and outputs the next primary key number assigned to that table.
ALTER procedure [dbo].[sp_getNextKey]
@TableName char(100),
@NextKey T_ID output
as
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin transaction
select @NextKey = NextKeyValue from T_KeyGenerator where TableName like @TableName
update T_KeyGenerator set NextKeyValue = NextKeyValue + 1 where TableName like @TableName
commit transaction
I then take that primary key, and other data, and insert that into the desired table.
The Problem I am having is that my user's keep getting "cannot insert duplicate key in table".
So I assume that I do not have the transaction set right, or missing something.
I need it to lock the row in the t_keyGenerator table so that no other users can view that row until I update it with the new value and commit the transaction.
Any help would be greatly appreciated
View 8 Replies
View Related
Dec 25, 2007
i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?
View 1 Replies
View Related
May 2, 2007
More of a general SOAP service call question.
Does anybody have any experience/advice on how to ensure that SOAP service call success/failures are returned to the calling app?
Consider a client that calls a SOAP service during which the client goes down and is unable to receive the SOAP response, the work having been done by the service. Similarly, the SOAP service may perform the task but a failure in the return makes the client think the process failed.
What would be the best way to ensure that the client is notified to avoid the call having to be made again?
Are there middleware tools that can be used to provide a form of message queuing for SOAP service calls?
Thanks
View 1 Replies
View Related
Jul 24, 2007
Hi,
I am using web service to synchronize data between SQL Server 2005 on a desktop and a SQL Server Mobile Edition on a mobile device.
Initially, when i added the data source to my mobile application, my .sdf only has 3 records. I am then supposed to retrieve 97 more records from the desktop server when my mobile application runs. After performing merging, I clicked (on my device emulator) on the .sdf deployed under my mobile application and the query analyzer showed all 100 records.
Does this mean my mobile database has been successfully updated with all 100 records? If not, how do I check that? The reason why I am asking this is that the file size of this .sdf with 100 records is still the same as the initial .sdf with only 3 records. How is this possible?
Am I missing anything or getting some concepts wrong? Please enlighten. Thanks.
View 4 Replies
View Related
Jul 28, 2015
what are some common techniques for ensuring an isolated temp table scope? For example, if 2 different sprocs happen to crud a temp table with the same name? I'm guessing that big SQL shops establish a standard for this early on to avoid conflicts between sprocs.
View 3 Replies
View Related
Oct 30, 2015
I have a SQL Server 2008 instance that is running on "LiveServer" our production database (ProdDB) - and we need to upgrade to 2014. In order to do some upgrade testing, I spun up a VM with the same version of SQL server on the test VM (TestServer), did a backup of the production DB from the live server, and restored it to TestServer under a different name (ProdDBUA).
I then installed SQL2014 Upgrade advisor onto TestServer, and ran it, checking all the boxes (reporting services etc..) and it all came back clean - no issues whatsoever - not a single warning even. I'm under the impression that stored procs/functions etc... all reside within the DB, so a backup will include those. Is that correct?
The problem is, I know I have stored Procs, functions and views that use deprecated joins in that LiveServer.ProdDB. What do I need to do/configure/check in order to make sure that the Upgrade Advisor is actually checking through all that T-SQL that has deprecated code? I want to have a list to give to my report writers of procs/functions/views that need to be rewritten prior to the upgrade going live.
If there is a modification that needs to be run on the TestServer.ProdDBUA, a cursor to change the path etc. DB is running in Compatibility mode 90.
View 4 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
Sep 12, 2007
thx but this code not needed anymore
sry
View 1 Replies
View Related
Jun 7, 2008
I have an sp, which has 2 select statements, so iam using a sqldatareader and binding the data to a dropdown.
the first binding is fine, but when i say dataReader.NextResult(), It is null.It says the reader is closed. Can any one tell a work around for this.
thanx in advance,
Anil Kumar.
View 6 Replies
View Related
May 5, 2004
i am running a java program in tomcat to connect SQL Server,using the Microsoft's jdbc driver ,as the following code :
import java.sql.*;
class Bkjz{
ResultSet rs=null;
String spde;
String condition;
Connection con=null;
Statement sql;
public String getSpde(){
return spde;
}
public void setSpde(String spde){
this.spde=spde;
}
public ResultSet Searchsjk(){
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=zkbm","zkbm","zkbm");
sql=con.createStatement(ResultSet.TYPE_SCROLL_SENS ITIVE,ResultSet.CONCUR_READ_ONLY);
if(spde.equals(""))
condition="select CRCC,CRNM,SPDE,SPNM from SPCR where EMTP='5'group by SPDE,SPNM,CRCC,CRNM";
else
condition="select CRCC,CRNM,SPDE,SPNM from SPCR where SPDE='"+spde+"'"+"and EMTP='5' group by SPDE,SPNM,CRCC,CRNM";
rs=sql.executeQuery(condition);
//con.close();
if(!rs.next())
{
return null;
}
else
{
rs.previous();
return rs;
}
}
catch(Exception e){
message="exception!!! "+e.toString();
System.out.println(e);
return null;
}
}
}
public class Bk{
public static void main(String args[]){
Bkjz bbb=new Bkjz();
bbb.setSpde("1020110");
try{
ResultSet rr=bbb.Searchsjk();
while(rr.next()){
System.out.println(rr.getString("CRCC"));
}
}
catch(Exception e){
System.out.println(e);
}
}
}
without con.close(),it can return Resultset ,but when includeing con.close(),an Exception tell me:Object has been closed, in other programms i've close connection,but it never throws this Exception,why? thanks
View 1 Replies
View Related
Jan 27, 2007
I have the Function, that fires from onLoad even of one of the asp:Label controls on my main page.
Here is it's code:
SqlConnection conn = new SqlConnection(); conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["UkraineConnectionString"].ToString(); SqlCommand comm = new SqlCommand("SELECT [Greeting] FROM [Misc]", conn);
try { conn.Open(); } catch { Response.Write("Error opening connection in Page_Load of default.aspx to retrieve the greetings"); }
string MyGreet = (string)comm.ExecuteScalar();
Greetings.Text = MyGreet;
try { conn.Close(); } catch { Response.Write("Error closing connection in Page_Load of default.aspx after retrieving the greetings"); } }
When it gests to conn.Open() in the debugging mode I see that the ServerVersion = 'conn.ServerVersion' threw an exception of type 'System.InvalidOperationException'.
The most interesting thing is that it used to work before.
Here is the connection string it retrieves fine.
"Data Source=MDM1;AttachDbFilename=|DataDirectory|Ukraine.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"
As I said it used to work, but one day I tried to access the web site and it said this error that I get, that the connection is closed. Then I was using the SQL Server Express. When in the Visual studio if I would run this same site in debug, everything was working fine. I decided to uninstall the SQL Express and installed the SQL Server.
If I open the SQL Server Managment Studio in the Server name field I see MDM1(this is the name of the PC, but probably it is the same name for the Server. Well, in the MAnagment Studio it conects fine to the MDM1 so it is no probably the naming problem. Ithink it has something to do with permisssions.
If someon can - please help. Thanks.
View 3 Replies
View Related
Jan 30, 2007
Hello,
I have built a system, that uses a .dll file for all SQL operations. So a program looks something like this:
using myDLL;
....
SQLDBCON mSQL = new SQLDBCON();
and here comes the rest of the program.
My question is, the dll file has all stored procedures and when you declare mSQL as shown above, then it opens a thread to the database for that user.
Is that thread properly closed when the site has finished loading?
in my .aspx page i dont have a function like mSQL.CloseDB();
and if i try to add something like this to the dll file
~SQLDBCON {
m_local_con.Close();m_local_con.dispose();
}
i get a error message says something that this is not allowed.
Just want to know if my thread in the dll file is properly closed?
View 9 Replies
View Related
Jan 13, 2000
That is an app ACCESS 2000 wih Named Pipe ODBC to SQL Server 6.5.
After 10 minutes without use this app, the connection closed !
Have-you idea for correct this probleme ?
Regards
Alain
View 2 Replies
View Related
May 3, 2006
I'm getting object closed when returning a recordset from a stored procedure. I've tested the select statement in Query Analyzer and a value is getting returned. So, I'm not sure if my code for my stored procedure is incorrect?
Here is the code for the procedure:
CREATE Procedure dbo.GetRepEmailByZip
@sessionid varchar(50),
@zip varchar(5)
AS
Begin Transaction
INSERT INTO dbo.SupportRequests(firstname,lastname,schoolname, address,city,state,zip,phone,email,currentcustomer ,implementationtype,producttype,comment)
(SELECT FirstName,LastName,SchoolName,Address,City,State,Z ip,Phone,Email,CurrentCustomer,ImplementationType, ProductType,Comment
FROM dbo.Temp_ContactInfo
WHERE sessionid = @sessionid)
--If Transacation fails, stop execution of procedure, return error code and Rollback Transaction
IF @@ERROR<>0 OR @@RowCount = 0
BEGIN
ROLLBACK TRANSACTION
--return value
RETURN 1
END
--If Transaction succeeds, commit transaction, continue and process the select statement
COMMIT TRANSACTION
SELECT r.email
FROM PostalCodes p
INNER JOIN TerritoryList z ON p.ZipID = z.ZipID
INNER JOIN RepList r ON r.RepID = z.RepID
WHERE p.ZipCode = @zip
GO
This is the code I'm calling to execute the procedure and return the recordset:
set GetRepEmail = Server.CreateObject("ADODB.Command")
With GetRepEmail
.ActiveConnection = MM_DBConn_STRING
.CommandText = "dbo.GetRepEmailByZip"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4)
.Parameters.Append .CreateParameter("@sessionid", 200, 1,50,usrid)
.Parameters.Append .CreateParameter("@zip", 200, 1,5,zip)
set RepEmail = .Execute()
End With
Dim x, y
x = RepEmail.RecordCount
y = RepEmail.State
Response.Write(x)
Response.Write("<br>")
Response.Write(y)
'Response.Write(RepEmail("email"))
Response.End()
I'm getting "Operation is not allowed when the object is closed", which is occuring on the following line:
x = RepEmail.RecordCount
I'm trying to determine if this is problem within my procedure or in the application code.
Thanks in advance for any help.
View 4 Replies
View Related
Jan 5, 2006
Wierd problem here
As one user, when i select * from sys.conversation_endpoints I can see all (I assume) conversations in all states specifically DO, DI and CD
However when I change to another user I see only DI
Why is this?
If it is a permissions issue what permission do I have to grant to a user to see all conversations in sys.conversation.endpoints?
View 1 Replies
View Related
Jun 18, 2001
Hello all,
I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.
So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.
Has anyone got any ideas on how to get round this?
Thanks
Dave Sykes
View 2 Replies
View Related
Oct 15, 2007
I have gathered from reading online that I need to create a 2nd connection to SQL Server if I want to insert records into the database while in a "while (reader.Read())" loop.
I am trying to make my code as generic as possible, and I don't want to have to re-input the connection string at this point in my code. Is there a way to generate a new connection based on the existing open one? Or should I just create 2 connections up front and carry them around with me like I do for the 1 connection now?
Thanks.
View 7 Replies
View Related
Jan 5, 2013
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table
CaseID,
CaseNumber,
CaseName
CaseChild Table
CaseID, FK to CaseInfo
ChildPartyID, FK to PartyID in Party table
ProgramClosureDate
Party Table
ID,
PartyID,
Firstname,
LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
FROM CaseInfo ci JOIN
CaseChild cc ON ci.CaseID = cc.CaseID JOIN
Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL
ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
View 5 Replies
View Related
Jul 23, 2005
I have written a stored proceedure for MSSQL that needs to run for hours ata time. I need to execute it from C++ code. The current code does:nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)followed shortly after by aFree_Stmt_Handle(hstmt) //roughlyThe stored proc currently dies with the statement handle, not fullypopulating the table I need it to.I need to either know when the proc finishes so I can close the handle afterthat, or allow the proc to run independently on the server no matter whatthe program is doing (is exited, etc), either of these is fine.Please Help! Thanks in advance!Joseph
View 2 Replies
View Related
Jul 20, 2005
Can anybody tell me why a) when running a stored proc from an asp page toreturn a recordset the command succeeds if the sp queries an existing tabledirectly, but b) if the stored proc populates results into a differenttable, temporary table, global temp table, or table variable, then queriesone of these, the asp page reports that the recordset object is closed. Ifusing a table, I have set grant, select, update, delete permissions for theasp page user account, so it doesn't appear to be a permissioning issue. Ifrun in Query Analyser the sp runs fine of course.Abridged asp code is as follows:StoredProc = Request.querystring("SP")oConn.ConnectionString = "Provider=SQLOLEDB etc"oConn.Openset oCmd = Server.CreateObject("ADODB.Command")oCmd.ActiveConnection = oConnoCmd.CommandText = StoredProcoCmd.CommandType = adCmdStoredProcoCmd.Parameters.Refresh'code here that populates the parameters of the oCmd object correctlySet oRs = Server.CreateObject("ADODB.Recordset")With oRS.CursorLocation = adUseClient.CursorType = adOpenStatic.LockType = adLockBatchOptimistic'execute the SP returning the result into a recordset.Open oCmdEnd With' Save data into IIS response objectResponse.ContentType = "text/xml"oRs.Save Response, adPersistXML'the line above fails with stored procs from example B below, reporting "notallowed when object is closed", but works with example ASP Example A - this one works fineCreate Proc spTestA ASSELECT ID FROM FileListGOSP Example B - this one doesn't work from ASP but runs fine in QACreate Proc spTestB ASDECLARE @Results Table (ID TinyInt)INSERT INTO @Results SELECT ID FROM FileListSELECT ID FROM @ResultsGOI can see the SP executing using profiler when the asp page is called forboth sp's above, so it doesn't appear to be a problem with the execution.It's something to do with returning the result set from the table variable.Thanks,Robin Hammond
View 1 Replies
View Related
Jun 30, 2015
The application server gets below error while the job is being run intermittently:
An error occurred while performing connection management
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:319)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1839)
[Code] ....
There is no error reported in SQL logs.
View 6 Replies
View Related
Aug 7, 2007
I have this stored procedure on SQL 2005:
USE [Eventlog]
GO
/****** Object: StoredProcedure [dbo].[SelectCustomerSoftwareLicenses] Script Date: 08/07/2007 16:56:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]
(
@CustomerID char(8)
)
AS
BEGIN
DECLARE @Temp TABLE (SoftwareID int)
INSERT INTO @Temp
SELECT SoftwareID FROM Workstations
JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID
WHERE Workstations.CustomerID = @CustomerID
UNION ALL
SELECT SoftwareID FROM Notebooks
JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID
WHERE Notebooks.CustomerID = @CustomerID
UNION ALL
SELECT SoftwareID FROM Machines
JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID
WHERE Machines.CustomerID = @CustomerID
DECLARE @SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)
INSERT INTO @SoftwareInstalls
SELECT SoftwareID, COUNT(*) AS Installs FROM @Temp
GROUP BY SoftwareID
DECLARE @rowid int
SET @rowid = (SELECT COUNT(*) FROM @SoftwareInstalls)
WHILE @rowid > 0 BEGIN
UPDATE SoftwareLicenses
SET Installs = (SELECT Installs FROM @SoftwareInstalls WHERE rowid = @rowid)
WHERE SoftwareID = (SELECT SoftwareID FROM @SoftwareInstalls WHERE rowid = @rowid)
DELETE FROM @SoftwareInstalls
WHERE rowid = @rowid
SET @rowid = (SELECT COUNT(*) FROM @SoftwareInstalls)
END
SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses
JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID
WHERE SoftwareLicenses.CustomerID = @CustomerID
ORDER BY Software.Software
END
When i execute it in a Query in SQL Studio it works fine, but when i execute it from an ASP page, i get following error:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/administration/licenses_edit.asp, line 56
Here the conection:
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.ConnectionTimeout = Session("ConnectionTimeout")
OBJdbConnection.CommandTimeout = Session("CommandTimeout")
OBJdbConnection.Open Session("ConnectionString")
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")
SQLStmt.CommandText = "EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
RS.Close
Can anyone help please?
It this because of the variable tables?
View 7 Replies
View Related
Dec 7, 2007
I am using MS JDBC driver 2005 1.2 and in-house written connection pooling that was working fine for a number of years. This combination worked OK with SQLserver 2000. But recently we switched to SQLServer 2005 (x64) on WIN Server 2003 Standard x64 eddition. Everything seems work OK during business hours, however, after business hours when there are lesser users and connection stays idle for sometime, I am getting the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(Unknown Source)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.freeConnection(OnlineTransactionManager.java:420)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:707)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:688)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.finalize(OnlineTransactionManager.java:399)
at java.lang.ref.Finalizer.invokeFinalizeMethod(Native Method)
at java.lang.ref.Finalizer.runFinalizer(Finalizer.java:83)
at java.lang.ref.Finalizer.access$100(Finalizer.java:14)
Any help would be appreciated
thanks in advance
View 6 Replies
View Related
Jul 24, 2015
I have included in the attached SQL:
Declare @table1 table
(
cnsmr_id int,
complainid int,
complaintstat varchar(10)
)
Insert into @table1
[Code] ...
The query should return only cnsmr_id=2 since all the complaints is closed (blank/NULL) i have achieved this using having but is there is more performance way of doing this?
View 5 Replies
View Related
Sep 25, 2006
We have been having a problem with service broker for quite a while now and searching on these forums and more generally on the web has not yielded any kind of answer...
Our application utilises service broker within a single database (there is no cross database, cross instance or cross server communication). There are approximately 12 queues which are used to varying degrees. A few of the queues have a throughput rate in the order of up to 100s per minute at peak periods.
In some scenarios we are able to make use of persistent conversations but the majority of messages are sent on their own conversation or in small batches on their own conversation.
Some time ago we found that the database was growing in size more than expected due to a build up of data in the service broker meta data internal tables, exposed via the sys.conversation_endpoints system view.
We identified a problem in the application that was preventing some conversations from being closed and have now fixed this.
However, we are still experiencing a build up in the service broker tables and sys.conversation_endpoints shows this is now due to a very large number of conversations in the "CLOSED" state.
I know that conversations are kept around to prevent replay attacks but thought they were supposed to be cleared after about 30 minutes. This is certainly not happening as we currently have conversations that were opened on the 18th September, a full week ago. We currently have about 350,000 closed conversations and this figure is increasing!
We have tried restarting the SQL Server instance with no effect.
I have been using a script to loop through all closed conversations and get rid of them using the "WITH CLEANUP" clause but I'm loath to create a scheduled task that does this in the background when service broker should be doing the job itself.
Has anybody experience this problem and, even better, have any idea how to solve it?
Daniel
View 12 Replies
View Related
Jun 26, 2007
This is my code:1 If Session("ctr") = False Then
2
3 Connect()
4
5 SQL = "SELECT * FROM counter"
6 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"
7 dbRead()
8
9 If dbReader.HasRows = True Then
10
11 dbReader.Read()
12 hits = dbReader("hits")
13 hits = hits + 1
14 dbClose()
15
16 SQL = "UPDATE counter SET hits=" & hits
17 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"
18 dbExecute()
19
20 Else
21
22 SQL = "INSERT INTO counter(ipaddress,hits)"
23 SQL = SQL & " VALUES('" & Request.ServerVariables("REMOTE_ADDR") & "',1)"
24 dbExecute()
25
26 End If
27
28 Session("ctr") = True
29
30 End If
1 Public Sub Connect()
2 Conn = New SqlConnection("Initial Catalog=NURSETEST;User Id=sa;Password=sa;Data Source=KSNCRUZ")
3 If Conn.State = ConnectionState.Open Then
4 Conn.Close()
5 End If
6 Conn.Open()
7 End Sub
8
9 Public Sub Close()
10 Conn.Close()
11 Conn = Nothing
12 End Sub
13
14 Public Sub dbExecute()
15 dbCommand = New SqlCommand(SQL, Conn)
16 dbCommand.ExecuteNonQuery()
17 End Sub
18
19 Public Sub dbRead()
20 dbCommand = New SqlCommand(SQL, Conn)
21 dbReader = dbCommand.ExecuteReader
22 End Sub
23
24 Public Sub dbClose()
25 SQL = ""
26 dbReader.Close()
27 End Sub
View 2 Replies
View Related
May 3, 2008
the class code:
Dataase.cs: using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Xml.Linq; using System.Data.SqlClient; using System.Data.Common; using System.Web; /// <summary> /// Summary description for DataBase /// </summary> public class DataBase { private SqlConnection con=new SqlConnection(); private void Open() { if (con==null) { con = new SqlConnection("Data Source=58.17.30.81;Initial Catalog=a1230192748;Persist Security Info=True;User ID=a1230192748;Password=***"); } if (con.State == System.Data.ConnectionState.Closed) { con.ConnectionString = "Data Source=58.17.30.81;Initial Catalog=a1230192748;Persist Security Info=True;User ID=a1230192748;Password=****"; con.Open(); } } public void Close() { if (con != null && con.State != System.Data.ConnectionState.Open) con.Close(); } public DataBase() { // // TODO: Add constructor logic here // } public string liuyan(string id,string sign) { string com=string.Empty; switch(sign) { case "xiaobiaoti": com="Select subject from liuyan where liuyanid='"+id+"'"; break; case "def_message": com="Select message from liuyan where liuyanid='"+id+"'"; break; } SqlCommand myCommand=new SqlCommand(com,con); Open(); try { SqlDataReader sdr=myCommand.ExecuteReader(); if (sdr.Read()) { return sdr[0].ToString(); } else { return ""; } sdr.Close(); //what i have written.} catch (Exception ex) { HttpContext.Current.Response.Write("<script>alert('error:" + ex.Message + "')</script>"); return ""; } finally { myCommand.Dispose(); Close(); } } }
it was instantiated once in aspx.cs code.I invoke liuyan(string id,string sign) twice.The first one is OK and the second one makes an exception.
View 3 Replies
View Related
Jan 11, 2007
Hi all,
can anyone tell where this error is popping up im my SQL because i can't see why i am getting this error.
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bksb_Diag_ESOL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bksb_Diag_ESOL]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bksb_Diag_StuDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bksb_Diag_StuDetails]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bksb_StuRecs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bksb_StuRecs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bskb_Diag_Assessments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bskb_Diag_Assessments]
GO
CREATE TABLE [dbo].[bksb_Diag_ESOL] (
[Stu Ref No] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[English First Lang] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Other Lang Work] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Other Lang Home] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Other Lang Friends] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bksb_Diag_StuDetails] (
[Stu Ref No] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Group Ref No] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dob] [smalldatetime] NULL ,
[Maths] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[English] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bksb_StuRecs] (
[StuRefNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bskb_Diag_Assessments] (
[Stu Ref No] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Assessment] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Section Name] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Curric] [nvarchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Total Score] [decimal](18, 0) NULL ,
[Out Of] [decimal](18, 0) NULL ,
[Answers] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [smalldatetime] NULL
) ON [PRIMARY]
GO
Regards
Liam
View 4 Replies
View Related
Dec 21, 2004
never seen this before, but I keep getting an error > "Operationg is not allowed while the object is closed" I thought maybe I was getting this due to an error in my stored procedure, however the procedure runs fine. Could this be an error in my procedure that it's just not catching? Or is it more likely to be in my VB code? Anyone know in general why this happens? Thanks.
View 1 Replies
View Related
