ODBC/SQL Cursor Error
One of the people I have to support gets an error message when they are trying to use a SQL based application that was developed in house:
an error occured..microsoft odbc sql driver cursor type change. location frn main, load user () source is odbc
Unfortunately, the developer is not available to troubleshoot the issue and I do not understand it. I thought ODBC settings were static, so how could they cause a cursor to change?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
ODBC Inserting Against Open Cursor
Im receiving 'HY000 Connection is busy with results for another hstmt' when trying to insert. The reason is down to another hstmt with a cursor still open (as not all results have been fetched) against the same connection handle. My current design is one connect handle per file, with multiple statement handle depending on sql issued so i dont need to rebind. I'm guessing, but is there a sqlsetconnectattr/sqlsetstmtattr setting to which will allow me to insert/update/delete to a file with a open cursor that may include the result ??? sqlserver version 8
View Replies !
View Related
How To Clear 'ODBC 24000 Invalid Cursor State'
Hello experts. Excuse my SQL newbie status. I was writing a stored procedure to crunch some data against an MS SQL 2000 database. I didn't entirely know what I was doing so I was using Crystal Reports 11 as an "application layer" pulling against a stored procedure. Well I guess ODBC connections don't support temp table output from stored procedures, or at least I don't know how to do it. So I've rewritten my stored procedure so it inputs and outputs data using BCP, rather than just sending a simple select query. My problem is that I cannot get CR to add the stored procedure back in to the report. Everytime I try I get an 'ODBC 24000 error - Invalid Cursor State.' Where is this current set of records? How do I find it and close it? I've recreated my ODBC connection, every related table and SP to no avail. This is my crusial question. Less crucial but helpful: How do I write an SP so it can be called from a report program like CR and will return its result set to the application? What's a good, simple "application layer" I can use if I'm doing all the logic in native SQL stored procedures? ASP.NET looks like a pain in the ass, and SQL Reporting Services requires Visual Studio .NET 2003. Is Perl my answer, since I can build web interfaces and forms in my sleep?
View Replies !
View Related
Cursor Error . Please Help .
Hi Friends , When i am trying to store a table under a database , it gives me an error that 'Invalid Cursor Status ' and it does not allow me to store that table . The server is SQL Server 7.0 with Service pack 3. Can somebody help me in getting this resolved please ? Thanks Anita .
View Replies !
View Related
Cursor Error
We are getting an error while running a stored procedure, This is not the exact error but it is roughly: 'cannot complete cursor operation because table schema changed after cursor declaration' Any ideas as to what causes this? Thanks, Eddie
View Replies !
View Related
ERROR [HY000] Or ERROR [23000] At Random Times When Using Sql Native Client Via ODBC
The following (VB.Net) code causes exceptions at seemingly random times. Any suggestions? Not sure if the problem is in .Net's ODBC support or in Sql Native Client. If MARS is off, usually after less than 100 loops: Code Snippet System.Data.Odbc.OdbcException was unhandled ErrorCode=-2146232009 Message="ERROR [HY000] [Microsoft][SQL Native Client]Connection is busy with results for another command" Source="SQLNCLI.DLL" Turning MARS is on bypasses that error, so it will sometimes survive a thousand or so runs before hitting: Code Snippet System.Data.Odbc.OdbcException was unhandled ErrorCode=-2146232009 Message="ERROR [23000] [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'Number', table 'tempdb.dbo.#TempTable___..(shortened to fit).. _________000000002F3F'; column does not allow nulls. INSERT fails. Source="SQLNCLI.DLL" Code here: (Console Application, empty database, Visual Studio 2005, .Net 2.0, Windows XP,SQL Server 2005 or SQL Express 2005, Local or Remote) Code Snippet Module Module1 Sub Main() 'Dim connection As New SqlClient.SqlConnection("database=TestDB;server=.sqlexpress;Integrated Security=SSPI;") 'Doesn't crash 'Dim connection As New OleDb.OleDbConnection("Provider=SQLNCLI;database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;") 'Doesn't crash 'Dim connection As New Odbc.OdbcConnection("Driver={SQL Native Client};Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;") 'Crashes Dim connection As New Odbc.OdbcConnection("Driver={SQL Native Client};Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;MARS_Connection=yes") 'Crashes VBMath.Randomize() Dim run_count As Integer = 1 connection.Open() Console.WriteLine("Connected!!") While True 'connection.Open() Dim testcmd As New Odbc.OdbcCommand("CREATE TABLE [#TempTable] (Number int PRIMARY KEY)", connection) testcmd.ExecuteNonQuery() testcmd.Dispose() testcmd = Nothing Dim dtTemp As New DataTable Dim daTemp As New Odbc.OdbcDataAdapter("SELECT * FROM [#TempTable]", connection) daTemp.MissingSchemaAction = MissingSchemaAction.AddWithKey daTemp.Fill(dtTemp) Dim cbTemp As New Odbc.OdbcCommandBuilder(daTemp) Dim viewTemp As New DataView(dtTemp, Nothing, "Number", DataViewRowState.CurrentRows) Dim i As Integer For i = 1 To 1000 Dim test_number As Integer = CInt(Rnd() * 2000) If viewTemp.Find(test_number) = -1 Then 'Keep it unique Dim new_temp_row As DataRowView = viewTemp.AddNew() new_temp_row("Number") = test_number new_temp_row.EndEdit() End If Next daTemp.Update(dtTemp) 'daTemp.Dispose() 'daTemp = Nothing 'cbTemp.Dispose() 'cbTemp = Nothing 'dtTemp.Dispose() 'dtTemp = Nothing 'viewTemp.Dispose() 'viewTemp = Nothing Dim testcmd2 As New Odbc.OdbcCommand("DROP TABLE [#TempTable]", connection) testcmd2.ExecuteNonQuery() testcmd2.Dispose() testcmd2 = Nothing 'connection.Close() 'GC.Collect() 'Console.Write(".") run_count += 1 End While End Sub End Module Usually the ".Update" triggers the exception, but sometimes the other sql commands do it. Have tried various experiments with disposing of objects immediately when finished, but no effect. Opening and closing the connection each loop seems to delay the errors, but it still happens eventually.
View Replies !
View Related
Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Hi Everybody, On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help. Thanks, Arif Server Error in '/' Application. -------------------------------------------------------------------------------- ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.] Microsoft.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) +27 Microsoft.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +838 Microsoft.Data.Odbc.OdbcCommand.ExecuteNonQuery() +80 Calgary.venues.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootCalgarySitevenuesvenues.aspx.vb:32 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731
View Replies !
View Related
Why I Get This Error:Microsoft OLE DB Provider For ODBC Drivers Error '80040e14'
When i run the web site in Windows 2003 Server,and I click on the "view" hyperlink to view customer infor,I got the error message as below: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '/'. /bc/cust_info.asp, line 132 and the SQL statement in line 132 is as below: strsql1 = "select sum(unit_price*qty) as SubTotal from dailySchedule where customer_code='" & request.QueryString("login") & _ "' and [date]=" & dbldate1 where [date] is reserved word But,if I run the web in Windows 2000 Server,I don't get the error message as stated above. Pls help.Thanks.
View Replies !
View Related
ODBC Error: SQLState 28000 && Server Error 18452
We have an application that connects to SQL 2005 thru ODBC with the following string: driver=SQL Server;server=server1;database=db2005;uid=serveruser;pwd=pwd1; When our administrators login on the workstation the application works well. But when ordinary users login they get the following error. (We only have 1 domain) SQLState: 28000 SQL Server Error: 18452 Login failed for user ". The user is not associated with a trusted SQL Server connection. Then the standard SQL Server Login window pops up asking for the Login ID and Password. On the window the 'Use Trusted Connection' is checked and the name of the user on the workstation appears on the LoginID. What we do is uncheck the 'Use Trusted Connection' then login using the credentials above. Need help on this one please. Thanks.
View Replies !
View Related
ODBC Error: SQLState 28000 && Server Error 18452
I have an application that connects to SQL 2000 thru ODBC. When our administrators login on the workstation the application works well. But when ordinary users login they get the following error. SQLState: 28000 SQL Server Error: 18452 Login failed for user ". The user is not associated with a trusted SQL Server connection. I have restarted SQL Server and IIS to no avail. We are using SQL and Windows auth mode. Any ideas? This just started this morning.
View Replies !
View Related
Where To Put Error Routine When Using Cursor
Where is the proper place to put error trapping routines when using cursor statement. Say for example you do below: if @@error <> 0 error trap routine start cursor routine do process close cursor Will this placement trap any errors which occur during the cursor routine, or should the error trap be inside the cursor Thanks
View Replies !
View Related
Error Handling + Cursor
Hello group I need some help and gaining knowledge per the SQL Standards. I have a Stor Procedue which Cursor through say MASTER. This table have information/ Counm that guides me to multiple say two different table. i.e, Col1 in MASTER table have the information on what table I need to work on COL2 in MASTER Table give me what Field I have to work on and accordingly move the data Example : MASTER ID COL1 COL2 COL3 1 Address Street Nichols Rd. 1 Address Zip 90001 2 Address Street Michigan Ave 2 Address Zip 90002 3 Person FName James 3 Person LName Bond 4 Person FName Bill 4 Person LName Gates Address ID Street Zip 1 Nichols Rd. 90001 2 Michigan Ave 90002 Person ID FName LName 3 James Bond 4 Bill Gates For doing this I created a Store Procedure which Cursor thru MASTER table which for each row goes to Entity on which it has to work (MASTER Table : Col1 Value) and in that entity Its goes to the Field(MASTER Table: Col2 Value) and places the value of the Col3 in it. It is working fine and superb. Now, I try to introduce Error Handling such that incase in order to see if there is any error while doing this INSERT and UPDATE. SAMPLE QUERY: (Structure is Same but modified for readability) UPDATE @ENTITY SET @FIELD = @VALUE WHERE ID = @KEYID IF @@ERROR <> 0 BEGIN INSERT INTO dbo.ErrorLog VALUES( @KEYID + ' , '+ @ENTITY + ' , ' + @FIELD +' , ' + @VALUE ) END This query is working fine . If there is any error and for some reason if its not able to insert/update the data Its populates the ErrorLog Table and instead of failing the store proc goes for the next row and do teh necessary action Finally SProc does what it has too 1) Get the data in teh respective tabel and colunm 2) if it cannot insert/ update go for the next row and populate the error log table with what it was n't able to update/ insert SO Whats the problem? Good question , here is the issue or topic of discussion 1) What is the standard way of Error Handling. Did I atleast follow teh minimum Error Handling criteria. 2) I can see from teh table that, as required, its skipping the row into Error Log table whenevr its not able to do the required functions and going for next row 3) I introduce a Error and when I execute the Sproc it gives me Mesaage: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated. Also I see that "Query Completed with Errors" well I said whenever teh SPROC see error do some thing else, then why would it count it as error and give me this message? Why is the SPROC failing. What Should I do such that it counts or give when ever there is a genuine error. I mean it should not fail at all because I am saying there whenever there is an incapability of Insert/ Delete just simply skip the row and Insert that row in Error Log Table. Am I missing something in this Proc? If it will do that then is tehre any way or query or anything I have to do that handles the issue that Whennver its not able to do whatever I am asking it to do (Ex: If I am asking it to insert INTEGER in Character Field) then an error is raised then just skipp that row insert that row in erro log and go for next row and dont fail the SPROC. THANKS FOR THE PATIENCE FOR READING AND REPLYING.
View Replies !
View Related
Error On Update, But Not When Using Cursor
I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString). When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't). The problem thus cannot be with the function since it works just fine if I do like this: UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001 And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records). But when I do this it crashes: UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff. Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly. The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is). Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here). Is there a certain tweak I can do to make things work out, or should I code things differently? Thanks!
View Replies !
View Related
Error: Cursor Not Returned From Query
I'm a really beginner about sql2000.During my test I have created the following query. It's works ok until Ido't add the code included in section A, when I add it the i obtain theerror: Cursor not returned from queryAnyone can help me?Thanks Carlo M.set nocount onIF OBJECT_ID('storico_big') IS NULL --- section A begincreate table storico_big( data datetime,bcarrier varchar(20),bda CHAR(30),bzone char(50),bdur int) ;insert into storico_big --- section Aendselect top 10000adetdate,bcarrier,bda,bzone,bdurfrom pp_cdr (nolock)whereadetdate < :data_fin and adetdate > :data_in order by adetdateset nocount off------ end of query
View Replies !
View Related
Invalid Cursor State Error
I know other people have posted stuff like this before, but I am stillto see a definitive answer. I have created a table in Enterprisemanager called tblUsers. I later added a column in EM and saved thechange. I then noticed I had misnamed the column, so I renamed andwhen I tried to save the change I got the following message:-'tblUsers' table- Unable to rename column from 'fldepCode' to 'fldRepCode'.ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor stateThis is now regularly happening to me, and I am tired of having torecreate tables.I am using SQL Server 2000 with all the latest service packs andpatches. Has anyone any idea what is causing this and how I can cureit or avoid it?
View Replies !
View Related
Error: Could Not Create An Acceptable Cursor.
I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs. The error I get is: OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 16955, Level 16, State 2, Line 1Could not create an acceptable cursor. The full script I am running is: CREATE procedure [dbo].[proc_AuditServer] as /* ** Auditing Script for SQL Servers. ** ** D Maxwell, June 2007 ** ** This script takes configuration and job status information ** and writes it to a designated logging server. I'll describe ** each section in detail, below. We write to the local box first, ** Then upload everything to the logging server. ** ** This is the SQL 2005 version. */ /* ** We want to know exactly what server this is, so ** we get the server name, instance name, as well as ** SQL Version, Edition, and Service Pack level. */ truncate table admin.dbo.sql_servers insert into admin.dbo.sql_servers select convert(varchar(15), serverproperty('ServerName')), convert(varchar(25), serverproperty('InstanceName')), convert(char(9), serverproperty('ProductVersion')), convert(varchar(4), serverproperty('ProductLevel')), convert(varchar(20), serverproperty('Edition')), getdate() /* ** Now, having that, we get the list of databases, ** as well as thier creation dates and file names. */ truncate table admin.dbo.databases insert into admin.dbo.databases select convert(varchar(15), serverproperty('ServerName')), dbid, name, crdate, filename from master..sysdatabases where dbid > 4 order by dbid /* ** We need to know how the server is configured, so we ** can compare it to a list of preferred configuration ** values, as well as the defaults. I cut this out of ** sp_configure. */ truncate table admin.dbo.server_config insert into admin.dbo.server_config select convert(varchar(15), serverproperty('ServerName')), name, config_value = c.value, run_value = master.dbo.syscurconfigs.value from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs where type = 'C' and number = c.config and number = master.dbo.syscurconfigs.config and ((c.status & 2 <> 0 ) OR (c.status & 2 = 0) ) order by lower(name) /* ** The next configuration item we want to get is the ** list of jobs that run on the server. We're looking ** specifically for backup and other maintenance jobs. ** (Which will hopefully be named appropriately...) ** We use Neil Boyle's job report script for this. ** My comments and changes prefaced by a 'DM:' */ truncate table admin.dbo.jobs insert into admin.dbo.jobs select convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting j.job_id, -- DM: More unique than a name. convert(varchar(22), j.name) as job_name, case freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example + case freq_relative_interval when 1 then 'Every First ' when 2 then 'Every Second ' when 4 then 'Every Third ' when 8 then 'Every Fourth ' when 16 then 'Every Last ' end + case freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Week day' when 10 then 'Weekend day' end when 64 then 'Startup' -- When SQL Server starts when 128 then 'Idle' -- Whenever SQL Server gets bored else 'Err' -- This should never happen end as schedule , case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours when 1 then 'Runs once at:' when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds' when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes' when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours' end as frequency -- All the subsrings are because the times are stored as an integer with no leading zeroes -- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00) , substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at ,case freq_subday_type when 1 then NULL -- Ignore the end time if not a recurring job else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c where j.job_id = s.job_id and s.schedule_id = c.schedule_id order by j.name, start_at /* ** Now that we know what jobs we have, let's find out ** how they did recently. */ truncate table job_status insert into job_status select convert(varchar(15), serverproperty('ServerName')), job_id, run_status, run_date, run_time, run_duration from msdb..sysjobhistory where step_name = '(job outcome)' -- The last 90 days' worth. and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', '')) order by run_date desc /* ** If this server is already known to the audit server, ** we need to remove the existing data from the audit ** tables. */ declare @known bit set @known = (select count(*) from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('servername')))) /* ** Now we remove the existing information from the audit tables, ** if need be. */ if @known = 1 begin delete from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.databases where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.server_config where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.jobs where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.job_status where server_name = (select convert(varchar(15), serverproperty('ServerName'))) end /* ** Finally, we upload the new info from here to the audit server. */ insert into [192.168.0.126].AUDITDB.dbo.sql_servers select * from admin.dbo.sql_servers insert into [192.168.0.126].AUDITDB.dbo.server_config select * from admin.dbo.server_config insert into [192.168.0.126].AUDITDB.dbo.databases select * from admin.dbo.databases insert into [192.168.0.126].AUDITDB.dbo.jobs select * from admin.dbo.jobs insert into [192.168.0.126].AUDITDB.dbo.job_status select * from admin.dbo.job_status This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test. Any ideas as to what I should look at next? Thanks. -D.
View Replies !
View Related
Error Creating Cursor Handle
Can someone please help me with this stored procedure? When I execute it in ISQL/w, it works fine, but when one of my programmers tries to execute it from another program (Delphi3) he receives an error of “Error creating cursor handle”. Can someone tell me what this means? I don't really know anything about cursors... how can I be using them??? CREATE PROCEDURE EMPLOY_TEST AS declare @msg varchar(30) declare @newnum int insert dbo.employ_xref_id default values SELECT @newnum=(max(Idcolumn+1)) from employ_xref_id where IDCOLUMN between 0 and 9999 and not exists (select * from EMPLOY_XREF e where e.EMP_ID=(idcolumn+1)) select @msg=""+convert (varchar(10), @newnum) print @msg GO Thanks for any advice!! Toni Eibner
View Replies !
View Related
Urgent: Cursor Already Open Error
I get the following errors while running a SQL procedure. [ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The cursor is already open [ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'qst_Ruler_AllExpenses' already exists I am creating, opening and deallocating the cursor. Advice please.
View Replies !
View Related
Cursor Error After Upgrade To SQL2000
Hello: We have an old 16-bit app that runs fine in SQL 7.0 but on our test SQL 2000 box some reports generate the following error "System Message 10202 - The application fetch buffer size is less than the cursor select statement requires". Any ideas what this means, I am not much of a coder. The MS Knowledge base does not list much on this. Thanks,
View Replies !
View Related
Error From Stored Procedure With A Cursor
I receive the following intermittent error when executing a stored procedure: Msg 16942, Sev 16: Could not generate asynchronous keyset. The cursor has been deallocated. [SQLSTATE 42000] Msg 3624, Sev 16: Location: lckclass.cpp:111 Expression: m_status == ACTIVE || m_status == ORPHANED SPID: 17 Process ID: 203 [SQLSTATE 01000] The process uses a cursor to update a SQL7 table from another. This is not a consistent failure. Sometimes the procedure runs fine to completion. Has anyone else had the same problem??? We had a suggestion that it might be a tempdb problem??? Any ideas?
View Replies !
View Related
Cursor Is READ ONLY.----HELP!!!--error 16929.
CREATE PROCEDURE RemoveDuplicates AS Declare c_Work Cursor For SELECT storeid, stocknumber, invnumber FROM inventorytest ORDER BY storeid, stocknumber, invnumber -- declare variables for the columns -- data types made up Declare @storeid char(20) Declare @stocknumber char(10) Declare @invnumber char(12) -- declare a duplicate set of variables now for working with Declare @storeid_new char(20) Declare @stocknumber_new char(10) Declare @invnumber_new char(12) -- declare a counter variable for Transaction Commits Declare @Count int Open c_Work Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber BEGIN TRANSACTION While @@FETCH_STATUS = 0 BEGIN If @storeid = @storeid_new BEGIN If @stocknumber = @stocknumber_new BEGIN If @invnumber = @invnumber_new BEGIN DELETE FROM inventorytest Where Current Of c_Work SET @Count = @Count + 1 IF @Count = 1000 BEGIN Set @Count = 0 COMMIT TRANSACTION BEGIN TRANSACTION END End ELSE BEGIN SET @invnumber_new = @invnumber END END ELSE BEGIN SET @invnumber_new = @invnumber SET @stocknumber_new = @stocknumber END END ELSE BEGIN SET @invnumber_new = @invnumber SET @stocknumber_new = @stocknumber SET @storeid_new = @storeid END Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber END Close c_Work Deallocate c_Work COMMIT TRANSACTION ----When i execute this Sp i get "error 16929 cursor is READ ONLY" . It had worked a week ago and now it doesn't work. Can anyone help me out and its urgent. Thanks in advance.
View Replies !
View Related
The Cursor Does Not Include The Table ... Error?
We use a lot of virtual machines. I have a base VM with SQL 2005 installed. I rename this VM (i have multiple copies running) and run an old application (VB code, iterates through recordsets, etc.). I get: Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '2K3VM-DG' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. This makes sense, and I can fix it with sp_dropserver / sp_addserver [local]. Good. The next error is puzzling though: [Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor. Any suggestions on how to fix this?
View Replies !
View Related
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
View Related
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
View Related
Microsoft Cursor Engine Error '80040e38'
Hi, I have issue updating date field on table. Here is the situation. I create a new record onto table: dim fieldsArray(1) dim valuesArray(1) fieldsarray(0) = "sdate" valuesarray(0) = date fieldsarray(1) = "stime" valuesarray(1) = FormatDateTime(now(),vbshorttime) Recordset1.addImmediate fieldsarray, valuesarray Then on 1 other page I go back to this record to update 2 other Date and Time fields: Recordset1.fields.setValue "edate",date Recordset1.fields.setValue "etime",FormatDateTime(now(),vbshorttime) Recordset1.updateRecord it is then that I get the error: Microsoft Cursor Engine error '80040e38' Row cannot be located for updating. Some values may have been changed since it was last read. If I change the fields type to tinytext it is working otherwise nothing. I'm fighting with this for the last 2 days... PLEASE HELP... Fabrice
View Replies !
View Related
Error Creating Cursor Handle ** Update **
***Original message:*** (see update below) Can someone please help me with this stored procedure? When I execute it in ISQL/w, it works fine, but when one of my programmers tries to execute it from another program (Delphi3) he receives an error of “Error creating cursor handle”. Can someone tell me what this means? I don't really know anything about cursors... how can I be using them??? CREATE PROCEDURE EMPLOY_TEST AS declare @msg varchar(30) declare @newnum int insert dbo.employ_xref_id default values SELECT @newnum=(max(Idcolumn+1)) from employ_xref_id where IDCOLUMN between 0 and 9999 and not exists (select * from EMPLOY_XREF e where e.EMP_ID=(idcolumn+1)) select @msg=""+convert (varchar(10), @newnum) print @msg GO ***Update*** I have figured out that it is the 'select @newnum=(max(idcolumn+1))' that was causing the problem. Actually just the @newnum portion. I've changed that to just 'select newnum=(max(idcolumn+1))', but how can I get this value to be returned to the user without having the @newnum variable to use in a PRINT statement? CREATE PROCEDURE EMPLOY_TEST with recompile AS begin declare @msg varchar(30) insert dbo.employ_xref_id default values select newnum = (max(idcolumn +1)) from employ_xref_id where idcolumn between 100 and 999 and not exists (select * from employ_xref e where e.emp_id=(idcolumn +1)) end GO Thanks for any advice!! Toni Eibner
View Replies !
View Related
Error 1429: A Server Cursor Cannot Be Opened...
Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too).. We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed. This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute. Once in a while we will get a return from SQL .. "OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..." The OLE error code is 1429. An OLE Exception code 3604 is also returned. When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well. Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed. Here's the really fun part... Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME. Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console. Help?? Bill Kuhn - MCSE The Kuhn Group, Inc. http://www.kuhngroup.com
View Replies !
View Related
ODBC Error Returns Error, But Sql Is Fine
When I run some code through an odbc driver onto a SQL server 6.5 database, from vb an error is being returned along the lines of 22005 Syntax error converting 'string' to INT4 if I copy this code, either from vb or from sql trace, and run it in isql the code runs without any errors. The only thing I have found is about NULLs so I have removed these from the code that is passed through? Any more suggestions?
View Replies !
View Related
OLE DB Error: OLE DB Or ODBC Error: Class Not Registered.
I have been searching high and low for days and I am perplexed and appalled at the lack of information regarding what can not be an isolated incident. We are trying to get an Analysis Services 2005 to process on a x64 server. We have both versions of the Oracle client installed and .ora file in both places. I have seen a couple of posts regarding this issue, but they are 2 years old and MS was supposed to have resolved the 'parentheses' issue long ago. The test connection succeeds, and I can explore the data in the datasource view, but I cannot process the cube. All I get is the "OLE DB error: OLE DB or ODBC error: Class not registered" Microsoft Windows Server 2003 R2 Enterprise x64 Editon Service Pack 2 Installed Oracle Client 10g and ODAC home I tried a few things that were suggested in posts, but nothing has worked. I tried the cmd line starting SSMS from progra~2 instead of (x86). I tried renaming the directory with no parentheses. This is the second time we've tried to get it to work in 2 years. We'll probably give up again and stay with x86, um, because it works without all these silly mickey-mouse tricks. Most of our relational DBs are ORACLE so this is a showstopper for us as far as trying to go x64. Sorry for the attitude, but I'm really frustrated! If anyone can tell me a way to resolve this (or that it can't be resolved), I'd really appreciate it!
View Replies !
View Related
Error: Table Schema Changed After The Cursor Is Declared
Hi, I have a package which loads data from one sql server table to another. I am loading 15million records in that. Earlier I tested that package with smaller data (less than a million) and it worked fine. So, I put it in production to load that 15 million records. But strangely after loading over 1.5million records, the job aborted with error at destination. The log says 'the table schema has changed after the cursor is declared'. But there is no change made in both destination as well as source. In my package I am using a 'OLEDB Source' to read data from a SQL Server table, using 'Script component' making some changes and loading data to a sql server table using 'OLE DB Destination'. Both source and destination are in same server, but under different schema. Do you have any idea about the problem? Thanks.
View Replies !
View Related
Merge Replication Subscriber Error: Invalid Cursor State
These errors occur at the subscriber. First, "The merge process could not query the last sent and received generations" is generated, then immediately afterwards, "invalid cursor state" is generated. We are trying to pull a new subscription snapshot from the republisher. The subscriber has been added and removed from the replusher's pull subscriptions a number of times for testing. Now, we cannot get the subscriber to re-sync with the republisher. Any ideas?
View Replies !
View Related
ODBC Error
Hi there Any one can help me !!!!! If i add the user in the database userid table, it indicates the error as follows (given user name is not added in the userid table due to this error) [Microsoft][ODBC SQL Server Driver][SQL Server] The updatelineage function requires 2 arguments [Microsoft][ODBC SQL Server Driver][SQL Server] GETMAXVERS is not recognized [Microsoft][ODBC SQL Server Driver][SQL Server] The updatelineage function requires 2 arguments [Microsoft][ODBC SQL Server Driver][SQL Server] GETMAXVERS is not recognized Thanks in advance by yasin
View Replies !
View Related
ODBC Error
Hi All, I have a database using Access 2003 as a front end and SQL as a backend. I set up all permissions in SQL and linked all tables to the Access frontend. After putting the front end on the network for other users, they receive the following error: ODBC — connection to <name> failed. (Error 3151) What do I need to do please
View Replies !
View Related
ODBC Error
I recently rebuilt an NT 4.0 server, SP4, with IIS 4.0. The installation media and path that I used gave me ODBC version 3.50.33.30. Our web application works fine on another server that is identical except for ODBC version 3.510.3002.23. ODBC returns error : Procedure executed with 'EXEC'. No output parameters returned." in the first case. Does anyone have an idea what is going here? And second, how can I find what version of ODBC comes from what source? It appears that ODBC is installed/upgraded from a multitude of sources. Thanks in advance, Ed Molinari
View Replies !
View Related
ODBC Error
I just set up a w2k server running SQLServer 2000. I can connect to the server using the enterprise manager and make changes. However if I try to create an ODBC connection connection or connect using the Query Analyser I get a timeout. This even has the DBAs I know stumped.
View Replies !
View Related
ODBC Error Help!!!
Odbc Driver Error!!! An user receiving this error message when running a financial application. SQL Error! Query Timed out--ODBC SQL Server Driver. Timeout expired. Any idea about this error?
View Replies !
View Related
ODBC Error
We are using ASP to connect to a remote database running on SQL server 7.0. On our testmachine we get no trouble, maybe because the SQLserver and Webserver is running on the same machine. But when we upload our code to a webserver and make a copy of our database on a remote SQLserver we get this error : Microsoft OLE DB Provider for ODBC Drivers error '80040e37' [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Seksjon'. As we are working on a student project and our deadline is tomorrow, any help whatsoever would be of great assistance!!
View Replies !
View Related
Odbc Error For BCP
Hi, I am using this command to bcp data out on D drive of the sql server in text file and I receive the following ODBC error message. I used the same command from my (client machine) command prompt. It worked fine and created file on my local D drive. Could you please suggest me what should I check? Server is running on service pack 1. Use master exec xp_cmdshell 'bcp testdb..inv_master out d:inv_master_out.txt -c -S testserver -U sa -P *******' output ------------------------------------------------------------------- SQLState = 08001, NativeError = 1326 Error = [Microsoft][ODBC SQL Server Driver]Client unable to establish connection SQLState = 01000, NativeError = 1326 Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()). (4 row(s) affected) Thank you
View Replies !
View Related
Odbc Error
When I use the dsn to connect to the sybase system I get this error The setup routines for the sybase system 11 odbc driver could not be loaded due to system error code 127. It also gives the following message when I hit ok for the above message Driver's configdsn,configdriver or config translator failed could not load the setup or translator library. Any pointer could be highly appreciated.
View Replies !
View Related
Odbc Error
hi, i am getting following error, while connecting to sql server. The specified table is existing in the database. Unhandled database exception: invalid object name 'table' ODBC Retcode: -1 ODBC State: State:S0002,Native:208,ORigin:[microsoft]{odbc sql server driver][sql server] Thank you --Rk
View Replies !
View Related
ODBC ERROR
I got this error message while I was trying to backup the last transaction log before I start restoring a database that is marked SUSPECT. SQL Server is running on Windows 9x. Please how can I handle it because I can't even restore the db. Thanks for your help! Server: Msg 4060, Level 16, State 1, Line 0 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'Memphis. Login fails. Connection Broken
View Replies !
View Related
ODBC Error
Hello everyone, I am having a problem with and ODBC Connectin (Centura SQLBase). I have created my DNS and then use DTS to import my tables and data into SQL 7. All of the tables import fine but, when it starts to import data I get nothing except an error on each "copy data from table to table: [Intersolv][ODBC SQLBase driver][SQLBase]09727 GCI NTI Not initialized Is there anyone that might be able to help me? Thanks so much in advance. Kathrine
View Replies !
View Related
ODBC Error
Hi, I am running a query which gives me an ODBC Sql Server Driver error: Cannot sort a row of size 8130, which is greater than the allowable maximum of 8094. I was wondering if there is any workaround for this error? Is there a place where I can download the latest SQL Server drivers for SQL Server 7 Thank you. -Jay
View Replies !
View Related
|
TRACKER
