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 Complete Forum Thread with Replies
Related Forum Messages:
How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim
View Replies !
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 !
Join Cursor With Table Outside Of Cursor
part 1 Declare @SQLCMD varchar(5000) DECLARE @DBNAME VARCHAR (5000) DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA] OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME END CLOSE DBCur DEALLOCATE DBCur Part 2 SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS my question is, how to join the part 1 n part 2? is there posibility?
View Replies !
Include Column Description With List Of Table Properties
Hello --I am not yet an advanced SQL code writer.Can someone help me add the extended property 'Description' to a list ofcolumns resulting from this code?SELECTSUBSTRING(table_name,1,20) AS "Table",SUBSTRING(column_name,1,30) AS "Column",SUBSTRING(data_type,1,13) AS "DataType",character_maximum_length AS "Length",numeric_precision AS "Precision",numeric_scale AS "Scale"FROM information_schema.columnsWHEREtable_schema = 'DBO'ORDER BY table_name, ordinal_positionThe following codeSELECT objname AS ColName,value AS DescriptionFROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table','tablename', 'column', null)results in:ColName Description....name of column description of columnfor one table at a timeI want to include the value of 'Description' to the first set of code.Would it be simpler to use sysobjects and sysproperties for the Description?If so, can someone suggest how?This code gives me the table and comment for a column, but I can't figureout how to add the column to which the comment belongs.SELECT obj.name AS [Table], prop.value AS [Description]FROM sysobjects obj JOIN sysproperties prop ON obj.id = prop.idThanks for any help.Larry Mehl---Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.806 / Virus Database: 548 - Release Date: 12/5/2004
View Replies !
Capture Error Message And Include It In The Email
Hello, I have a SQL Task that executes some stored procedure. When a condition is met the stored procedure raises an error by calling RAISERROR (@ErrorMessage,16,1). Following the SQL Task I have Send Mail Task that sends an error email. I would like to know how to include the @ErrorMessage in the error email. Thank you, -Oleg
View Replies !
Putting Data Into Table B From Table A Without Using A Cursor
on SQL Server 2005, no SP. Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so: declare @HTR money declare @Uniqueid varchar(15) declare cur_HTR cursor for select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid open cur_htr fetch next from cur_HTR into @Uniqueid, @HTR while @@fetch_status = 0 begin update com_hpd_helpdesk_history set total_hours_to_resolve = @HTR where case_id_ = @Uniqueid and dateasat = @dateasat fetch next from cur_HTR into @Uniqueid, @HTR end close cur_htr deallocate cur_htr ... This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?
View Replies !
&&"RETURN Statements In Scalar Valued Functions Must Include An Argument&&" ERROR
Hi, I am trying to write a function which takes a string as input and returns the computed value. I need to use the output of this function as a coulmn in another select query. Here is the code (Example: @Equation = '(100*4)+12/272') create function dbo.calc(@Equation nvarchar(100)) returns float as begin return exec('SELECT CAST('+@Equation+' AS float)') end I am getting this error when i compile it "RETURN statements in scalar valued functions must include an argument" Any suggestions would be appreciated. Please respond Thanks
View Replies !
Table Name And Cursor
I am using MS-SQL Server 2K.... I have a stored procedure that contains a cursor. Right now, the table name for the cursor is "hard-coded". I would like to be able to "soft-code" the table name, because the table name may vary (although the fields would always be the same). Is this even possible? The only workaround I can think of is to use an EXEC on a real-time built SQL statement into a temp table and then cursor through the temp table. Is there a better way to do this? Thanks...
View Replies !
Cursor Or Temp Table
I just want to know which one is more efficient cursor or temp table for looping through a record set? I am basically looking for better performance and server utilization.
View Replies !
Cursor Or Temp Table
Hi Chaps, I want to write a stored procedure in which I will update the all calendar week values (stored in a table). And to achieve this I will have to loop through all the records present in calendar table. I am just wondering that should I use Cursor or TEMP table (caz of performance issues)? As it will be part of data warehouse so all the processing will be carried out at SERVER ( means no client) Can any one tell me that which would be the best solution ( Cursor or Temp table) in my case and WHY? Note: I am using SQL Server 2005 standard edition waiting for quick reply. regards, Anas
View Replies !
Cursor On A Temp Table
Hi all, I have a task of rewriting all the old procs which were build on cursors, the management does want me to remove the cursor completely since that would mean rewriting everthing.. they want to use a temp table and run a cursor on the temp table. Since there are many cursors in each proc. Any suggestions on to go about it. I am against the use of cursor any everywhere in it , even if it calls for rewriting the whole db again. I did some tests on running the old proc and the semi new proc , which runs a cursor from the temp table for the cursor query. And it even bad than the cursor only. what does this mean? Is it really a very bad idea to run cursor on a temp table? Necessity is the mother of all inventions!
View Replies !
How To Iterate Through Table Variable Without Using Cursor
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
View Replies !
Can't Reference Logical Table Within Cursor
Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this... DECLARE check_contact_fields CURSOR FOR SELECT field_id, column_name FROM contacts_fields OPEN check_contact_fields FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name WHILE (@@FETCH_STATUS = 0) BEGIN set @SQL = 'select ' + @column_name + ' into ##DeletedData from deleted' exec sp_executesql @SQL set @SQL = 'select ' + @column_name + ' into ##InsertedData from inserted' exec sp_executesql @SQL if (select * from ##DeletedData) <> (select * from ##InsertedData) select * from ##InsertedData FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name END CLOSE check_contact_fields DEALLOCATE check_contact_fields drop table ##DeletedData drop table ##InsertedData Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'deleted'. TIA
View Replies !
Inserting Cursor Information Into A Table
i'm currently writing an application whereby the user must be able to view the records within a table in chunks of 20, 50 or whatever. thus i need to be able to select the first 20 for example then once a 'next' button is pushed, the next 20 must be displayed. i was thinking of using a cursor to get all the records, moving absolutely to where the first row that should be displayed (fetch absolute 20 from cursor), then using the fetch next from cursor into a temporary table. is this possible and how would i got about doing it? (ie. code examples) thanx
View Replies !
Cursor From Diff Table -challenge
I have 17 tables with names as Breaktable1, Breaktable2,.... till Breaktable17 i make a query like this set nocount on Declare @sEventtable varchar(20) Declare @sstartzoneid varchar(20) Declare @ssttopzoneid varchar(20) Select @sstartzoneid ='1' Select @ssttopzoneid = '17' select @sstartzoneid = convert(smallint,@sstartzoneid) if @sstartzoneid<> 0 DECLARE Tablecursor FOR <-------> ( error here ) select status, sum(cost) FROM "breaktable " + @sstartzoneid where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003') group by status CLOSE tableCursor DEALLOCATE tableCursor can somebody help ????
View Replies !
Possible To Fetch Next From Cursor Into Table Variable?
Hello, I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like: Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table it does not work. any help would be great. thnx
View Replies !
Cursor To Store Value In Temp Table
Following sp gives wrong result whats wrong with following cursor? ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess] @ProjectBenefitID INT AS SET NOCOUNT ON DECLARE @ErrorMsgID INT DECLARE@ErrorMsg VARCHAR(200) DECLARE @TEMP_BENEFIT TABLE (ActualQuantity INT, ExpectedQuantity INT, ActualQulity VARCHAR(2000), ExpectedQulity VARCHAR(2000) ) DECLARE @AssessBenefitID INT DECLARE @ActualQuantity INT DECLARE @ExpectedQuantity INT DECLARE @ActualQuality VARCHAR(2000) DECLARE @ExpectedQuality VARCHAR(2000) DECLARE @AssessFlag CHAR DECLARE CUR_BENEFIT CURSOR FOR SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID OPEN CUR_BENEFIT FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID INSERT INTO @TEMP_BENEFIT (ActualQuantity , ExpectedQuantity , ActualQulity , ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality) FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag END CLOSE CUR_BENEFIT DEALLOCATE CUR_BENEFIT SELECT * FROM @TEMP_BENEFIT
View Replies !
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 !
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 !
'One Table' Record To Separate 'two Or Three Tables' Using Cursor?
I would like to 'one table' record to separate 'two or three tables' . I just know use the DTS , try to import and export again and agian. So trouble. Could you give me some suggestions for me? For example , 'Cursor' write in new table . But I try to SQL Server Books Online which is not suitable for me solving problems. One table separate two or three tables. Can you wirte the detail example for me? Thx a lot.
View Replies !
Cursor Count Loop - Update Table
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out. The table looks something like this. inv# SKU 1001 ABC123 1001 DEF456 1001 GHI789 1002 123DEF 1002 456GHI 1002 876HGT I need the cursor to go through and number each line, something like this. inv# SKU Line# 1001 ABC123 1 1001 DEF456 2 1001 GHI789 3 1002 123DEF 1 1002 456GHI 2 1002 876HGT 3 Any help is greatly appriciated. Thanks
View Replies !
Returning Multiple Results From A Cursor Into A Table
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates... But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render. In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do... /*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/ SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection); sda.Fill(dt); ... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned). So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment? Any help is greatly appreciated
View Replies !
Does Cursor Convert Table To Read/write?
Hello, Any help here much appreciated. I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write. i suspect this is when i run a cursor through the table. Is anyone able to confirm for me whether running a cursor changes a table's properties? Many thanks. Tim Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
View Replies !
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 !
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 !
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 !
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 Replies !
Increment In Select Query Without Cursor And Temp Table
I have: Select T0.Id_Roomtype, count(T0.ID_Room)as Total, convert(smalldatetime,'20000601') as ADate --- !!!! from rmRoom T0 where T0.id_Property = 1 group by T0.Id_Roomtype How to do: declare @x int set @x = 36901 Select T0.Id_Roomtype, count(T0.ID_Room)as Total, convert(smalldatetime,(set @x = @x+1)) as ADate --- !!! from rmRoom T0 where T0.id_Property = 1 group by T0.Id_Roomtype I am trying to increment value in Column ADate so to get new date for each row Is it possible without using cursor and temp table ? ht
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
|
TRACKER
