Hi! I am running a SELECT query in SQL Server 6.5 and continue to get the following message (error?):
This command did not return data, and it did not return any rows
DB-Library Process Dead - Connection Broken
I'm running it directly from Enterprise Manager on the same box as the database itself. The problem seems to be specific to the query itself as minor changes work fine. There are lots of joins involved - the query joins 3 tables and a view, that view joins 3 tables and a sub-query, and that sub-query joins 2 more tables. Simple queries on the view seem to indicate that the view works fine.
I'm a web/DB developer, but not much of a DBA. Has anyone run into this problem and know what may be causing it? Please let me know if you need to see the code of the query and/or the view. (Will replies be emailed to me automatically? If not, please email to tmenier@ontrack.com.)
I have a stored procedure whose sole purpose is to assign a value to 26 variables using select statements (i.e. select @enroll = (select count(distinct upid)fromPATLOG). Some of the souces are tables and some are views.
After the 26 variables are assigned they are written to a table using a INSERT into Values (@enroll, @tbrconsents,...)
My problem is that when I run the SP I get the message
"This command did not return data, and it did not return any rows" "DB-Library Process Dead - Connection Broken"
and nothing is written to the table. I`m fairly new to SQL programming so I haven`t a clue where to start. The SP has run successfully four times intermittantly but I can`t get it to run consistantly.
HP Proliant DL385 G1, 142*6 GB AMD Opteron „¢ Processor 280, 2.40 GHz 4.00 GB RAM, DVD ROM Win2K3 R2, SP1 64bit
SQL Server Details:
SQL Server 2000 Enterprise Edition 32bit Service Pack4
Network Details:
Clients can access the server by Terminal Server and also by Direct Connectivity. The connectivity is provided by RF network Provider.
Application Details:
Application was designed by Power Builder 6.5.
Error Details: If I divide my clients connectivity to the server then it is divided into 3 types
TYPE A >> Users inside my LAN using a DNS server they give the server name in their Parameter file to connect to the Database Server.
TYPE B >> Users who are in WAN connect to the server by Terminal Servers for Data Input. Terminal Servers are inside my LAN.
TYPE C >> Users who are in WAN connect to the Server by Direct Connection for Reports and Print Out they give the IP address of the Database server directly in their Parameter files.
After every 6 to 7 days the ERROR DB Processes Dead comes to the TYPE C. The error Numbers are 100025 and 100005.
At the same time TYPE A AND TYPE B are not getting any errors.
For further diagnosis I have taken a client machine to Net Meeting and taken his desktop in control. I have connected the client machine to the database server by ISQL in command prompt.
From Backend I can see that the SQL server has allocated a SPID to the client machine. After I have executed
1>SELECT * FROM SYSDATABASES 2>GO
It will return me all data but it will not show me how many rows selected or EOF.
After some time it will give me the prompt (3>) but without EOF and after that if I give another query then it will give the below error
DB-Library: Attempt to initiate a new SQL Server Operation with results pending.
DB-Library: Unexpected EOF from SQL Server. General Network Error. Check Your Documentation. Net-Library error 10054: ConnectionCheckForData (CheckForData()) DB-Library: DBPROCESSES is dead or not enabled. DB-Library: DBPROCESSES is dead or not enabled. DB-Library: DBPROCESSES is dead or not enabled. DB-Library: DBPROCESSES is dead or not enabled.
At the same time if I give IP address of another server then it works and TYPE A and TYPE B don€™t get any problem.
To stop this error I have to restart the server after that for again seven days it works and again the same problem.
I cant find anything I have checked tempdb, cache size but it is normal no abnormal activities.
I have a question why does not the following nested transaction work?
begin tran insert into t1 values('A') begin tran insert into t2 values('1') commit insert into t3 values('B') begin tran insert into t2 values('2') commit rollback
I am writing a program using VC++ 6.0 and SQL 2000 and I am trying to use nested transactions. I have 1 outer transaction and the 2 inner transactions are in sepetrate function calls inside the outer transaction. I have something like this:
BEGIN TRANSACTION;
if (!functioncall1()) // commit if function suceeds, otherwise rollback { Rollback Transaction; return; }
if (!functioncall2()) // commit if function suceeds, otherwise rollback { Rollback Transaction; return; }
COMMIT TRANSACTION ;
Both functions contain a complete transaction inside the function call. If either function fails however, I want to do a rollback of the entire transaction. This is not happening though. If functioncall1 suceeds and the transaction in that function commits, then if I do a rollback during functioncall2, the transaction in functioncall1 is not rolled back. This seems to be directly opposite of the SQL help for transaction. Am I missing something obvious here?
Hello! Sorry if I choose wrong forum for this post. I have next scenario: Transaction1 Transaction2 Commit Transaction2
Transaction3 Commit Transaction3 Commit Transaction1 I wanna implement it in C# code (.NET 1.1, MS SQL 2000): IDbConnection connection = new OleDbConnection(connectionString); IDbTransaction transaction = null; connection.Open(); /* NOTE: I can't use something like this: * transaction outter = connection.BeginTransaction(); * transacrion inner = connection.BeginTransaction(); * // Here I'm getting an error: OleDB doesn't support parallel transactions, * // though I wanna create nested one. */ // So, I decided to turn implicit transactions mode on in hope it should help: IDbCommand bt = connection.CreateCommand(); bt.CommandText = " SET IMPLICIT_TRANSACTIONS ON; BEGIN TRANSACTION;"; bt.ExecuteNonQuery();
transaction = connection.BeginTransaction(); IDbCommand command = connection.CreateCommand(); command.Transaction = transaction; command.CommandType = CommandType.Text; command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description');"; command.ExecuteNonQuery(); command.CommandText = "SELECT @@TRANCOUNT;"; int transCount = (int)command.ExecuteScalar(); // It's equal to 2 here, seems to be OK.
transaction.Commit(); // Let's start the second "nested" transaction IDbTransaction transaction1 = connection.BeginTransaction(); IDbCommand command1 = connection.CreateCommand(); command1.Transaction = transaction1; command1.CommandType = CommandType.Text; command1.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description');"; command1.ExecuteNonQuery(); command1.CommandText = " SELECT @@TRANCOUNT; "; transCount = (int)command1.ExecuteScalar(); // WOW! Now it's already equal to 1 here. transaction1.Commit(); // Well, here I wanna close outter transaction, but... I'll get exception: There is nothing to commit here bt = connection.CreateCommand(); bt.CommandText = "Commit TRANSACTION"; bt.ExecuteNonQuery();
Well, I know that SQL Server has no support for nested transactions. Nesting of transactions only increments @@TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. And I can't use the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept.
Please help me: How can I implement required operations?
Can anyone verify for me whether SQL Server CE 2.0 does or does not support nested Transactions when using the SQLServerCe Data Provider? The SQL Server CE Books Online documentation definitely states that SQL Server CE supports nested Transactions, but the example provided uses ADOCE Data Provider. The error message that I get when trying to begin a new Transaction with an existing Transaction still uncommitted on the same SqlCeConnection is "SQL Server CE does not support parallel transactions". Is it not possible to nest Transactions with SQLServerCE Data Provider?
I've a very basic doubt with Nested transactions (across procedures) in SQL Server and i guess the given below sample code illustarates my doubt well more than my words ..
I've a Proc1 like this
create procedure sp_proc1 as begin begin tran sp_proc1 insert into tab1 values (1,2) exec sp_proc2 1 if <Some cdn statement> rollback tran sp_proc1 else commit tran sp_proc1 end
and called proc sp_proc2 is like this
create procedure sp_proc2 ( @val1 int ) as begin tran proc2 update tab2 set col1 = 5 IF <some cdn statement> begin rollback tran proc2 end else begin commit tran proc2 end
The pblm is when the 1st proc is executed and when the cdn statement in the 2nd proc is sucess, then it results with the error
Failed to retreive execution plan: Cannot roll back proc2. No transaction or savepoint of that name was found.
Hope I am posting in the right forum. If I understand correctly, a ROLLBACK TRAN statement rolls all transactions (if they are nested) back to the original BEGIN TRAN.
I have a situation when an SP uses a transaction, performs a series of operations inside that transaction, including a call to a different SP, which uses a distributed transaction. If the transaction inside the child SP fails and needs to be rolled back, I get a warning message saying that the tran count on the way out is less than that on the way in. No problem, since it's not fatal. But the problem manifests when I attempt to use the SQL Agent to schedule a job to run the parent SP. It fails on that warning message, interpreting it as an error.
I was thinking of disabling the distributed transaction inside the child SP, and just have the transaction in the parent SP, which, again if I understand correctly, should be escalated to a distributed transaction once the child SP is called. The child SP will raise an error (if it's a real error) and then the trnasaction in the parent SP will handle the rollback of everything.
The reason for this elaborate setup is that I need to cycle through a cursor (yes, I know, sloppy, can't see an alternative) in the parent SP, and each iteration begins and commits (or rolls back) a transaction.
This should be a fairly simple question. It's based on this error message:"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0." I get this when executing a stored procedure upon processing a form. This error happens when I intentionally provide input to the stored procedure that I know should cause it to error out. I catch the exception, and it contains the error message, but it also contains the above message added on to it, which I don't want. I won't post the entire stored procedure. But I'll list a digest of it (Just those lines that are significant). Assume that what's included is what happens when I provide bad input:BEGINBEGIN TRYBEGIN TRANSACTION RAISERROR('The item selected does not exist in the database.', 16, 1); COMMIT -- This won't execute when the RAISERROR breaks out to the CATCH block END TRY BEGIN CATCHROLLBACKDECLARE @ErrorSeverity INT, @ErrorMessage NVARCHAR(4000)SET @ErrorSeverity = ERROR_SEVERITY()SET @ErrorMessage = ERROR_MESSAGE() RAISERROR(@ErrorMessage, @ErrorSeverity, 1) END CATCH END Okay, so that works fine. The problem is when I execute this with an SqlCommand object, on which I've opened a transaction. I won't include the entire setup of the data (with the parameters, since those seem fine), but I'll give my code that opens the connection and executes the query: con.Open(); SqlTransaction transaction = con.BeginTransaction(); command.Transaction = transaction; try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); } finally { con.Close(); } I'm calling the stored procedure listed above (which has its own transaction), using a SqlCommand object on which I've opened a transaction. When there is no error it works fine. But when I give the stored procedure bad data, it gives me that message about the transaction count. Is there something I need to do in either my SQL or my C# to handle this? The entire message found in the Exception's Message is a concatenation of the message in my RAISERROR, along with the transaction count message I quoted at the beginning. Thanks, -Dan
Hi there, I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following: <code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction? Any help is greatly appreciated
Hi I have a problem, when i do a select records from the production server which is situated in melbourne,around 20,000 records need to be fetched but sunddenly after 13,000 records the connection is broken why is this heppening what is the cause for this how to overcome this pls let me know asap with the evidence thanks venkat
Hi , I am running a process from from my client having TCP/IP sockets connected to WIN NT sql server 7.0. I get a error msg [MICROSOFT][ODBC SQL SERVER DRIVER][TCP/IP SOCKETS]ConnectionCheckForData (null())). Connection Broken .
The same thing happened in other NT SQL server as well,but it is ran o.k if i run from the server itself. Checked Microsoft documents they advised me to use Named Pipes and to check the client and server network utility. Check every detail but still facing same errors. Can anyone provide an solution for this.
CREATE INDEX [NC_DateAcctTotal] ON [dbo].[BilledPackages] ([pu_date], [acct_no], [delete_flag], [billing_weight_charge]) WITH FILLFACTOR = 90 ON [PRIMARY]
::::: ERROR ::::
Server: Msg 823, Level 24, State 2, Line 1 I/O error (torn page) detected during read at offset 0x000006d5f2c000 in file 'E:MSSQLDataManagement_Data2_Data.NDF'. Connection Broken
While performing a restore, one of the transaction logs was apparently corrupt. So the database gets partially restored with an error saying :
Backup or restore operation successfully processed 1 pages in 0.505 seconds Processed 6 pages for database 'royal', file 'royal_Log' on file 1. Server: Msg 9004, Level 21, State 2, Line 1 The log for database ' ' is corrupt. ODBC: Msg 0, Level 16, State 1 Communication link failure
Can anyone give me some tips how to restore? any help appreciated. bye Eva
I'm having serious problem with SSIS on my development machine.
each time I try to add a new Connection manager i got this error :
TITLE: Microsoft Visual Studio ------------------------------ The new connection manager could not be created. ------------------------------ ADDITIONAL INFORMATION:
The connection manager 'OLEDB' is not properly installed on this computer. (Microsoft.DataTransformationServices.Design)
This happen for any type of Connection Manager, ADO.NET, FILE,SMTP. I've already tried to uninstall SSIS , but nothing change.
We are having a really big problem with a zombie process/transactionthat is blocking other processes. When looking at Lock/ProcessIDunder Current Activity I see a bunch of processes that are blocked byprocess 94 and process 94 is blocked by process -2. I assume -2 is azombie that has an open transaction. I cannot find this process tokill and it seems that this transaction is surviving databaserestarts. I know which table is locked up and when I run a select *from this table it never returns. Does anyone have any ideas as tohow to kill is transaction.Any help is appreciated.A. Tillman
my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL
this proc calls:
my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL
This second stored procedure just selects back an ID that is passed to it (to keep things simple).
If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation.
Connection Broken
If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.
It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.
we have tables with many image columns. We fill these image columns via ODBC and SQLPutData as described in MSDN etc (using SQL_LEN_DATA_AT_EXEC(...), calling SQLParamData and sending the data in chunks of 4096 bytes when receiving SQL_NEED_DATA).
The SQLPutData call fails under the following conditions with sqlstate 08S01
- The database resides on SQL Server 2000 - The driver is SQL Native Client - The table consists e.g. of one Identity column (key column) and nine image columns - The data to be inserted are nine blocks of data with the following byte size:
1: 6781262 2: 119454
3: 269 4: 7611
5: 120054
6: 269
7: 8172
8: 120054
9: 269 The content of the data does not matter, (it happens also if only zero bytes are written), nor does the data origin (file or memory).
All data blocks including no 7 are inserted. If the first chunk of data block 8 should be written with SQLPutData the function fails and the connection is broken. There are errors such as "broken pipe" or "I/O error" depending on the used network protocol.
If data no 7 consists of 8173 bytes instead of 8172 all works again. (Changing the 4096 chunk size length does not help)
Has anybody encountered this or a similar phenomenon?
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" this my sql script use [master]Go -- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO -- Switching to our databaseuse [DatabaseName]GO CREATE SCHEMA schemaname AUTHORIZATION usernameGO ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO /* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter' -- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter] -- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber] -- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
A colleague wants to insert many millions of records where thevalues are computed in a C++ program. He connects to thedatabase with ODBC, and does an INSERT for each row.This is slow, apparently because each INSERT is a separatetransaction. Is there a way to delay committing the datauntil several thousand records have been written? InsideSQL Server this is simple, but I don't see an equivalentwhen using ODBC. Or is there something better than ODBC?Or might it be faster to write values to a file and thenuse bulk insert? I would appreciate any thoughts on thisgeneral problem!Thanks,Jim
In one of my packages, I set the package-level property called TransactionOption=Required. During run-time I saw an error saying "[Execute SQL Task] Error: Failed to acquire connection "SQL_DW". Connection may not be configured correctly or you may not have the right permissions on this connection. ". When the property is changed to anything other than Required, it works fine (the calling package that calls this package is not involved in a transaction).
The machine running the packages is Windows Server 2003, and so is the database where the data lives. I verified that the machine containing the database does has Enable Network DTC Access checked in Control Panel -> Add/Remove Windows Components -> Application Server.
Hello,i have a doubt here, after a close the connection, i still can see the last stored procedure active in the LOCKS/PROCESSID in enterprise manager of SQL SERVER.I think i'm closing the connection well Try If Me.ID > 0 Then Dim strSql As String = "SP_CHANGECLASSCONTENTS" Dim sqlcommand As New SqlCommand(strSql, New SqlConnection(ConnStr)) sqlcommand.CommandType = CommandType.StoredProcedure sqlcommand.Parameters.Add("@PerformActivation", SqlDbType.Bit).Value = 0 sqlcommand.Parameters.Add("@PerformInactivation", SqlDbType.Bit).Value = 0 sqlcommand.Parameters.Add("@PerformDelete", SqlDbType.Bit).Value = 1 sqlcommand.Parameters.Add("@PerformUndelete", SqlDbType.Bit).Value = 0 sqlcommand.Parameters.Add("@PermanentDelete", SqlDbType.Bit).Value = CBit(PermanentDelete) sqlcommand.Parameters.Add("@Class_ID", SqlDbType.Int).Value = _id sqlcommand.Connection.Open() sqlcommand.ExecuteNonQuery() sqlcommand.Connection.Close() Else Err.Raise(10205, "CLASS", "CLASS ID is empty") End If Catch ex As Exception _err = ex.Message Return Err.Number End Try
I am getting an error in Replication between SQL Server 2005 and SQL Express when "Connecting to Subscriber". Detailed error message is given below. Do we need to increase the logintimeout for "Connecting to Subscriber". How can we increase it?
Message 2007-10-15 06:37:58.398 Startup Delay: 8503 (msecs) 2007-10-15 06:38:06.898 Connecting to Distributor 'ACR-MANGO' 2007-10-15 06:38:06.976 Initializing 2007-10-15 06:38:06.976 Parameter values obtained from agent profile: -bcpbatchsize 2147473647 -commitbatchsize 100 -commitbatchthreshold 1000 -historyverboselevel 1 -keepalivemessageinterval 300 -logintimeout 15 -maxbcpthreads 1 -maxdeliveredtransactions 0 -pollinginterval 5000 -querytimeout 1800 -skiperrors -transactionsperhistory 100 2007-10-15 06:38:06.991 Connecting to Subscriber 'ACR-ANJILISQLEXPRESS' 2007-10-15 06:38:46.133 Agent message code 20084. The process could not connect to Subscriber 'ACR-ANJILISQLEXPRESS'. 2007-10-15 06:38:46.148 Category:NULL Source: Microsoft SQL Native Client Number: 08001 Message: Unable to complete login process due to delay in opening server connection 2007-10-15 06:38:46.148 The agent failed with a 'Retry' status. Try to run the agent at a later time.
Suddenly, the sql server is down, in the log file, C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG, I found the record as follows,
2007-03-05 15:40:31.59 Logon Error: 17189, Severity: 16, State: 1. 2007-03-05 15:40:31.59 Logon SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 172.21.123.48]
I have just installed MS SQL Server 2005 Express edition along with the SSMSE. I am using windows authentication. When I open SSMSE it connects me to a 'local instance' of the server. From there I right click on Database and select to add a new Database and I get the error above. I think I only need to allow local connections to the engine but I have tried allowing remote conections with all varations of named pipes and TCP/IP. I am running on Windows XP SP2 (home).
