Connection is busy with results for another hstmt

I am working on Sql Server 2000 and Delphi 5. When I try to open a table :

Table1.open;

I get the following error message :

Connection is busy with results for another hstmt.

I am new to Sql Server so please keep the explanation simple !!!

I have already read this got something to do with forward-only, read-only cursor. But I don't know what a forward-only, read-only cursor mean. Please explain !!

Thanks
KarenHowdy

A cursor collects a record set from a database. The cursor has a pointer that can move around in the record set to read a particular row etc. A forward only cursor can only read down ( i.e. forward ) through the record set, so if you are trying to go backwards through the record set it may not let you.

I dont know anything about Delphi, but from SQL Books on Line :

----------

SQLTables
When restricted to the current database, SQLTables executes the Transact-SQL procedure sp_tables to report table catalog data for Microsoft SQL Server.

The following table shows SQLTables parameter mapping for sp_tables stored procedure execution.

SQLTables parameter name sp_tables parameter name
CatalogName table_qualifier
SchemaName table_owner
TableName table_name
TableType table_type

SQLTables can be executed on a static server cursor. An attempt to execute SQLTables on an updatable (dynamic or keyset) cursor will return SQL_SUCCESS_WITH_INFO indicating that the cursor type has been changed.

SQLTables reports tables from all databases when the CatalogName parameter is SQL_ALL_CATALOGS and all other parameters contain default values (NULL pointers). SQLTables does not make use of sp_tables in this special case.

To report available catalogs, schemas, and table types, SQLTables makes special use of empty strings (zero-length byte pointers). Empty strings are not default values (NULL pointers).

The SQL Server ODBC driver supports reporting information for tables on linked servers by accepting a two-part name for the CatalogName parameter: Linked_Server_Name.Catalog_Name.

SQLTables returns information about any tables whose names match TableName and are owned by the current user.

Example
// Get a list of all tables in the current database.
SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL,0);
// Get a list of all tables in all databases.
SQLTables(hstmt, (SQLCHAR*) "%", SQL_NTS, NULL, 0, NULL, 0, NULL,0);
// Get a list of databases on the current connection's server.
SQLTables(hstmt, (SQLCHAR*) "%", SQL_NTS, (SQLCHAR*)"", 0, (SQLCHAR*)"",
0, NULL, 0);

Cheers,

SG

Connection information in a table trigger?

Is it possible to log or insert into a table the connection information (Application & Login) from an table trigger?

We have tough problem where data in a particular table is getting 'wiped-out' (rows are getting set to all NULLs) and we are unable to correlate this with any particular piece of software. My hope is that we can write an table trigger that can log or create an row in a temp table or the like to allow us to track this down to the offending application so that we can finallly get rid of the problem entirely.

Thanks,

You really should be using Profiler for this kind of activity. It will be able to give you more information than a TRIGGER. Profiler will allow you to capture the entire query (or queries) that are being sent to the server.

A TRIGGER would only allow you to log parameter and system values -NOT the actual query.

|||

Agree with Arnie for the most part, you can use profiler for this most likely. You would want to get really granular and track statements inside procedures too. You might also filter on the name of the table. Profiler can be kind of noisy/picky, so it might take a few tries, but it is the greatest thing to have in a crisis

As far as a trigger, you can get some of this information from sys.sysprocesses (or master.dbo.sysprocesses for 2000 and earlier.) You could join some of the values with the values in the inserted and deleted tables to see what is happening at a granular way. What I might do is to add a trigger that does:

if exists (select * from inserted where columnIDon'tWantSetToNull is null)

begin

raiserror ('DON''T DO THIS!',16,1)

rollback transaction

end

insert into log

select inserted.key, sysprocesses.columns
from inserted
join sysprocesses

on sysprocesses.spid = @.@.spid

If the operation was not in a transaction, you will get a log row, but your data will certainly not be hosed. If your application/process doesn't just ignore errors, you can track it down that way.

|||

Thank you both for your help. I'm not very good/handy with profiler, but I'll give it a try if I don't get anywhere with it, I'll try with an trigger on the table and the sysprocess table information.

George

|||

You definitely need to get good with profiler. In my opinion, it is the greatest thing about SQL Server, and for a person who has worked only with SQL Server for 15 years, that is saying something. Diagnosing problems with SQL Server is so much easier than pretty much any other programming tool, simply because I can see, immediately what the "heathen" user is trying to do to it and stop them.

Of course it has made it easy to simply force the DBA to prove that the database is not the culprit first since it is so easy, but that is another story

|||

How do I see the parameter values sent in a parameterized query in Profiler?

George

|||Profiler will show you the entire query, with parameters placed in the correct locations.

Connection Failed on VWD 2005 EE and SQL Server 2005 EE

Hi!
I have this problem:
When I try to open database table under database explorer in VWD 2005EE, it says that it failed to attempt connection due to user instanceand the connection will be closed.

When I try to right click on my database's file name under databaseexplorer and click connection to set the connection setting and clickon "Test Connection", it says the same thing.

This happen sometime, but after I restart my computer, I am able to access my database file again.

Anybody have any idea what's going on?

thanks !Maybe some other open connection is accessing the database file, so new connections are prevented?

connection error with SQL server 2000

Hello All,
I am working in vwd using C#. In my web develpent I am Connecting to the Sql server 2000's pubs table and displaying result in GridView Control.
My Coding is

SqlConnection con =new SqlConnection("Server=local host;uid=sa;pwd=**secret**;database=pubs");
string str ="select * from authors";
 SqlDataAdapter da =new SqlDataAdapter(str, con);
 DataSet ds =new DataSet();
 da.Fill(ds);
 GridView1.DataSource = ds;
 GridView1.DataBind();

his is a very simple Code.
Now I got a error which is

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: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)

Can you tell me what is the problem here

rajkumar sharma

Connect using TCP/IP, or use the Surface Area Configuration tool to enable remote connections using named pipes.

|||

I will be thankful if you explain me in some detail.

rajkumar sharma

Connection error to SqlServer

When opened my table :
Unspecified error
(MS Visual Database Tools)

what is this error?

please help me.

Please give more details about what you are doing when you encounter this error.

Mike

|||when I opened Microsoft SQL Server Managment Studio (Express Edition) and choose a table to open (by right click on it and select open table) this error message was appeared.
here is the detailed error:

===================================

Unspecified error
(MS Visual Database Tools)

Program Location:

at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.BrowseTable(Object dsRef, Object pServiceProvider)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.OpenTableNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

And

when i draw diagram for my database, this error message was appeared:
-
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (.Net SqlClient Data Provider)|||

This seems like a problem specific to Management Studio Express so I'm going to move this thread to the tools forum.

Mike

|||I could not find solution for error that send to last posts and

Other part of error when right click on server name and select properties :
-
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (.Net SqlClient Data Provider)

Connection error to SqlServer

When opened my table :
Unspecified error
(MS Visual Database Tools)

what is this error?

please help me.

Please give more details about what you are doing when you encounter this error.

Mike

|||when I opened Microsoft SQL Server Managment Studio (Express Edition) and choose a table to open (by right click on it and select open table) this error message was appeared.
here is the detailed error:

===================================

Unspecified error
(MS Visual Database Tools)

Program Location:

at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.BrowseTable(Object dsRef, Object pServiceProvider)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.OpenTableNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

And

when i draw diagram for my database, this error message was appeared:
-
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (.Net SqlClient Data Provider)|||

This seems like a problem specific to Management Studio Express so I'm going to move this thread to the tools forum.

Mike

|||I could not find solution for error that send to last posts and

Other part of error when right click on server name and select properties :
-
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (.Net SqlClient Data Provider)

Connection error to SqlServer

When opened my table :
Unspecified error
(MS Visual Database Tools)

what is this error?

please help me.

Please give more details about what you are doing when you encounter this error.

Mike

|||when I opened Microsoft SQL Server Managment Studio (Express Edition) and choose a table to open (by right click on it and select open table) this error message was appeared.
here is the detailed error:

===================================

Unspecified error
(MS Visual Database Tools)

Program Location:

at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.BrowseTable(Object dsRef, Object pServiceProvider)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.OpenTableNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

And

when i draw diagram for my database, this error message was appeared:
-
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (.Net SqlClient Data Provider)|||

This seems like a problem specific to Management Studio Express so I'm going to move this thread to the tools forum.

Mike

|||I could not find solution for error that send to last posts and

Other part of error when right click on server name and select properties :
-
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (.Net SqlClient Data Provider)

Connecting via Radio Link

Could somebody tell me as to what configuration changes has to be made in the SQL Server 2000, if I want to connect to a table in the database using Internet options, i.e., I have the SQL Server running on a machine which uses a real IP address, and I want to connect to it (SQL Server)using DSN from another location. In the 2nd location, I use internet using dialup/DSL/Cable Broadband.

--
The following error is shown, when I try to connect to the SQL Server using SQL Server authentication using login ID and password:

Connection failed:
SQL State:'01000'
SQL Server Error: 10060
[Microsoft][OBDC SQL Server Driver][TCP/IP Sockets]ConnectionOpen[Connect()]
Connection failed:
SQL State:'08001'
SQL Server Error: 17
[Microsoft][OBDC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
-----

Thanxs in advance to anyone who can help me solve the problem.You can use the Client Network Utility.

Go To Client Network Utility. Go to Alias Tab.
Click on ADD. Provide a Alias Name, and the Server IP address or the URL as Server Name under the Connection Parameters Section. (Netowrk Libraries can be Named Pipes)

Thats it. When you go to Enterprise Manager, you can register that server by its Alias name.

Connecting variable to database field

Greetings

what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database?

here is the script itself

thanks

km

Public Function AddList(list As String) As String
Dim total As Double
total = 0

If Len(list) > 0 Then
Dim s As Object
s = Split(list, ",")

Dim i As Integer
For i = LBound(s) To UBound(s)
s(i) = Replace(s(i), ":", ".")
total = total + CDbl(s(i))
Next
End If

AddList = total
End Function

km6589,

This would be better served in a stored procedure or function on the database side. That would solve your problem. Just run the SP in an Execute SQL Task.

If you don't care about overhead you could do this:

1) Drop a data flow task in the design.

2) Open the data flow task and add an OLE DB Source.

3) Open the OLE DB Source and add a connection if it isn't already there.

4) For the data access mode, choose SQL Command.

5) Add your SQL statement that lists the field you are trying to cleanup.

6) Add a Recordset Destination and feed the recordset into a variable of type object.

7) Access the variable for your recordset.

|||

thanks

went ahead and wrote some sql to clean this up on the DB side

km

Connecting variable to database field

Greetings

what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database?

here is the script itself

thanks

km

Public Function AddList(list As String) As String
Dim total As Double
total = 0

If Len(list) > 0 Then
Dim s As Object
s = Split(list, ",")

Dim i As Integer
For i = LBound(s) To UBound(s)
s(i) = Replace(s(i), ":", ".")
total = total + CDbl(s(i))
Next
End If

AddList = total
End Function

km6589,

This would be better served in a stored procedure or function on the database side. That would solve your problem. Just run the SP in an Execute SQL Task.

If you don't care about overhead you could do this:

1) Drop a data flow task in the design.

2) Open the data flow task and add an OLE DB Source.

3) Open the OLE DB Source and add a connection if it isn't already there.

4) For the data access mode, choose SQL Command.

5) Add your SQL statement that lists the field you are trying to cleanup.

6) Add a Recordset Destination and feed the recordset into a variable of type object.

7) Access the variable for your recordset.

|||

thanks

went ahead and wrote some sql to clean this up on the DB side

km

Connecting to SQL server Over Internet/ Phone line etc.

Hi,
I am have an application that is running on table PC and connecting to SQL server using wireless network. But I want to be able also connected to the SQL server from other locations. Is there a way to do that? Without changing all my routines with web se
rvices? I am looking for a way for example to change basically MyConnection if possible.
Any help/ Article will be greatly appreciated
Thanks
Lisa
In order for the application to work you need to be authenticated on the
wireless network. This really has nothing to do with SQL Server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin,
Well the application work in 802.11g network.. but I want now it be able to work outside the wireless network say view phone line connection or Internet. That is my question. Any help will be appreciated.
Regards
Lisa
|||Hi Lisa,
The same rules apply. If you're going to allow access via phone line,
then as long as the application can recover from a dropped line, there's no
reason why you couldn't do this. There may be some tcp parameters that you
might need to adjust to account for the slow line speed. You could also
experiment with changing the packetsize in SQL to 512.
Across the internet, you have much more bandwidth. The connection quality
and stability relies on the ISP provider.
Hope this helps
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.