Database Error 8179
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2000 Forums SQL Server Development (2000) SQL code 8179: Could not find prepared statement Reply to Topic Printer Friendly Author Topic ings Starting Member 3 Posts Posted-03/06/2008: 12:28:31 Hi I am intermittently getting a problem with a query and get the SQL code 8179 "Could not find prepared statement with handle ..." and then the handle number.One minute my query is working OK, the next I get the error.Now the really dumb part - if I just add another carriage return anywhere at the end of a line in my query, save & re-run, the query works again.It doesn't happen on one specific query - it occurs randomly on any query.I'm accessing an SQL Server 2000 DB from an enterprise wiki, via (I think) a Java ODBC jTDS.Does anyone have any idea of what I could check?Thank you! spirit1 Cybernetic Yak Master Slovenia 11752 Posts Posted-03/06/2008: 13:15:23 check if you set the correct size of any of your paramters in the code.do you use stored procedures or build your sql in code?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com ings Starting Member 3 Posts Posted-03/07/2008: 05:16:06 I don't use stored procedures, and it also happens even when I'm not explicitly defining parameters. For example I have a very simple query:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;SELECT'string'+columnA+'string'+columnA+'string' AS ColumnA,columnB AS columnB,CASE WHEN columnC = 'Yes' AND columnD = 'Yes' THEN 'CD'WHEN columnC = 'Yes'THEN 'C'WHEN columnD = 'Yes'THEN 'D'ELSE 'none'ENDAS 'ColumnCD',FROM TableALEFT JOIN TableBON TableA.Key = TableB.KeyWHERE ColumnA = 'string' AND ColumnB = 'string'ORDER BY columnA, columnB;After I get the problem, I can solve it just (for example) by changing:SELECT'string'+....toSELECT'string'+...(with an extra carriage return - anywhere in the query). I'm guessing that by do
ElMetainy Guest Hello I have an application using ADO.NET (.NET version 1.1 (SP1) on Windows 2000 Server SP4) and SQL Server 2000 Standard Edition (SP3 on another Windows 2000 Server SP4). The application is a windows service, that has some commands that are executed thousands of times so I use the Prepare method to improve performance. The application worked fine for 3 months, then I started getting SqlExceptions 2 days ago with error 8179 (Could not find prepared statement with handle x) where http://www.sqlteam.com/forums/topic.asp?topic_id=98562 x is a number that changes for each exception. I restarted SQL server, same problem. When I commented the lines calling Prepare (please see below) everything worked fine again, but I want to know why?. The code below is an example, the exception is thrown with many statements at different parts of the program. Can anyone tell me why this error started http://www.pcreview.co.uk/threads/sql-server-error-8179-could-not-find-prepared-statement-with-handle-x.1247555/ occuring? What is the effect of not using prepare ? // Preparing the command SqlConnection dataConnection = GetConnection(); SqlCommand deleteAccountCommand = dataConnection.CreateCommand(); deleteAccountCommand.CommandText = "DELETE FROM [Accounts] WHERE [AccountId] = @id"; SqlParameter idParam = deleteAccountCommand.CreateParameter(); idParam.ParameterName = "@id"; idParam.SqlDbType = SqlDbType.Int; idParam.Size = 4; deleteAccountCommand.Parameters.Add(idParam); deleteAccountCommand.Prepare(); //Executing the command (in another place) this code is called many times deleteAccountCommand.Parameters[0].Value = id; deleteAccountCommand.ExecuteNonQuery(); // SqlException thrown here Best regards, Sherif Sherif ElMetainy, Nov 25, 2004 #1 Advertisements Elton W Guest Hi Sherif, I think you might fix your problem by change the size of the parameter. Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met. If you call an Execute method after calling Prepare, any parameter value that is larger than the value specified by the Size property is automatically truncated to the original specified size of the parameter, and no truncation errors are returned. >-----O
by FeedBurner SQLServer-DBA.com Links Recent Posts How to schedule Powershell Script with Task Scheduler Lots of NULL values can cause trouble ALTER INDEX REBUILD clears sys.dm_db_index_usage_stats 7 mistakes DBAs make http://www.sqlserver-dba.com/sql-server-error-tips/page/4/ on CV or resume 5 ways globalisation has impacted IT Is a LOG BACKUP allowed on a SIMPLE RECOVERY database How to turn AUTO_SHRINK off 7 Essential Steps to Recover a Corrupt SQL Server Database DBA vacancy at Travelers - UK based Get Last Windows boot up time with Powershell Powered by TypePad March 14, 2013 Database stuck RESTORING A DBA reported a database error database stuck RESTORING status. According to the DBA the restore had finished. On closer inspection the RESTORE command had not included the RECOVERY argument during the RESTORE process The RECOVERY option sends an instruction to roll back uncommitted transactions. When recovery completes the database switched to ONLINE sql database status I confirmed the database was in RESTORING database status , and then issued database error 8179 the RESTORE command WITH RECOVERY. : use master go select * from sys.master_files where db_name(database_id) = 'database_name' GO --extra command to complete restore RESTORE DATABASE database_name WITH RECOVERY Read More SQL Database Status with sys.databases SQL Server Database Status,monitoring and Restores SQL Server – Difference between restore RECOVERY and NORECOVERY Posted at 06:51 AM in Backup and Restore, SQL Server Error Tips | Permalink | Comments (0) | TrackBack (0) Tags: database in recovery, database stuck restoring, error restoring database, restoring database, restoring database sql, sql database status, sql server restoring February 14, 2013 Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database If connections exist on a database , and you attempt to detach a database with EXEC master.dbo.sp_detach_db @dbname = N'database_name' the detach will fail. Best practise is to identify the connection sources and communicate with the users, and closing the connection gracefully. Communicating with the users is not always possible . Instead of using the KILL command ,use this command to set the database into SINGLE_USER mode. The SINGLE_USER mode indicates for only one user to access the database. The ROL