MS SQL Server :: Error: Could Not Create An Acceptable Cursor.

Create View From Cursor
I have multiple locations that I want to create views for eachindividual location.I am using a cursor to create the views for each location. So, thecursor grabs site #1 then <should> create view_site_#1, then grab site#2 and <should> create view_site_#2.For some reason it doesn't like the view name with the @site in it.Any ideas of how to get this done?Here's the cursor...declare @site varchar(5)declare c_site cursor forselect station from VHAISLCAUDIA.VISN_SITEorder by stationopen c_sitefetch from c_siteinto @sitewhile (@@fetch_status = 0)beginCREATE VIEW Site_All_Data_+ @siteASSELECT *FROM dbo.[600_All_Suggested_Data]WHERE (Site = @site)Print 'View for ' + @site + ' Created'fetch next from c_site into @siteendclose c_sitedeallocate c_sitereturnend

View Replies !

Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?

Is Declare Cursor same as Create Cursor and if not what is the major difference?

View Replies !

How Do I Create A "Cursor" In SQL Server 7.0??
How do I create a "Cursor" in SQL Server 7.0 that compares an imported table against a exsisting table? Ex. Table1 is my existing table(Destination), Table2 is my imported table(source). I would like to update records in the Destination from the Source but I have to be aware of these three scenarios.

1. If the record exist in the Destination and the Source I will do nothing.
2. If the record exist in the Source and not in the Destination then I will add the record to the destination.
3. If the record exist in the Destination and not in the Source then I will write to the transaction Log.

Note: I am only concerned about updating one column in Destination which matches the only column in the source.

Someone Please help??

View Replies !

Create A Cursor Inside A Sproc
I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:

StudentInfo
StudentID StudentName DeptID
101         John               10
102         Alex               10
103        Beth                20
ClassInfo
ClassID  DeptID
901       10
902       10
225       20
I want to create a Sproc which will retreive the student's classes in DeptID 10

Following is the Sproc and cursor:

use master
go
Create PROCEDURE [dbo].[getEnclishClasses]
@StudentID int
AS
Declare @printInsertStatement nvarchar(100)
ECLARE NewRowID int

Declare classCursor CURSOR FOR
      SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo
      WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
      WHERE StudentID=@StudentID)

      DECLARE @ClassID INT
      DECLARE @DeptID INT

OPEN classCursor
FETCH NEXT FROM classCURSOR INTO
      @ClassID, @DeptID
WHILE (@@FETCH_STATUs=0)
BEGIN
      PRINT 'SET @newID = Scope_Identity()'
SET @printInsertStatement=
      (Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values('
      +CONVERT(NVARCHAR (10), @ClassID) + ','
      +CONVERT(NVARCHAR (2), @DeptID)+')'
      FROM [myTest].dbo.StudentInfo
      WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
      WHERE StudentID=@StudentID))

      PRINT @printInsertStatement
END
CLOSE classCursor
DEALLOCATE classCursor
EXEC getEnclishClasses 101

Here is what I try to get (text with actual data from the table):
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10)
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)

Here is what I had got (returning multiple lines, more than number of records I have):
Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.

View Replies !

How To Use Cursor To Create Re-occuring Numbers?
SELECT pid, lname, visit_date, quantity
FROM customer
ORDER BY pid

pid lname visit_date quantity
-------------------------------------------
23 wang 07/23/2006 100
23 wang 07/30/2006 140
23 wang 08/05/2006 130
23 wang 08/15/2006 135
23 wang 08/22/2006 110
34 linden 06/23/2006 99
34 linden 07/06/2006 110
34 linden 07/15/2006 120
34 linden 08/26/2006 99

How do I use cursor (or is there a better way) to create an additional column "index"? "Index" starts at 1 for every unique pid

index pid lname visit_date quantity
----------------------------------------------------------
1 23 wang 07/23/2006 100
2 23 wang 07/30/2006 140
3 23 wang 08/05/2006 130
4 23 wang 08/15/2006 135
5 23 wang 08/22/2006 110
1 34 linden 06/23/2006 99
2 34 linden 07/06/2006 110
3 34 linden 07/15/2006 120
4 34 linden 08/26/2006 99

Please let me know if I can explain the my question better. Thanks in advance

View Replies !

[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
I have a SQL statement stored in a SQL varriable (after a lot of conditions)

Code:

declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'

Now, I want to create a cursor to recalculate some values
I've tried:

Code:

declare cur_T cursor for exec(@sql) open cur_T

but it doesn't work.
Can I have another way to do that???

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 !

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 !

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 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 !

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 !

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 !

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

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 !

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 !

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 !

Close And Deallocate Cursor In Error Routine
I have an error trapping routine within a proc that uses cursors. The routine works but if I run the stored proc from Query analyzer more than once it complains the cursor has already been declared and is still open. Should I close and deallocate as part of my error routine?

View Replies !

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 !

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 !

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 !

Error: 5123 CREATE FILE Encountered Operating System Error 5A(Access Denied.)

HI ,

This is a problem I encountered when I had to detach a database file (type .mdf):

1) I went to the MS SQL Management Server Studi and detached my database file successfully from a connection called Workhorse.

2) I needed to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal. This was also successful

3) However when I tried reattaching the database file, I got this error:

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "CProgram FilesMSSQL ServerMSSQLData<databasename>.mdf'

Q) The database file and log file (ldf) exist in the correct directory so I don't know what happened. Can any one help?

Thanks much

Tonante

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 !

Cant Create New Database / CREATE DATABASE Permission Denied In Database Master (error 262)

I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.

CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet

View Replies !

Error 16916: A Cursor With The Name &"tentative_cursor&" Does Not Exist
Hi,

I am creating a stored procedure which contains the declaration of a cursor, fetch the cursor, close the cursor, deallocate cursor. I tried executing this code in SQL analyzer and found working. But when I use the same code inside a create stored procedure, it throws the following error message.

Error 16916: A cursor with the name "tentative_cursor" does not exist

As a result, I am not able to proceed in creation of stored procedure with the above SQL statements.

Can any one help me where the problem is..

My SQL statements..
---------
DECLARE tentative_cursor CURSOR
FOR select * from TbTentativeBookingTable where ExpTime < GETDATE()
GO
OPEN tentative_cursor
GO
DECLARE @transactionno BIGINT
...
...
FETCH ...
CLOSE tentative_cursor
---------

Thanks
-Sudhakar

View Replies !

Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors

declare Q cursor for
select systudentid from satrans

declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id

declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'

fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q

select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View Replies !

Error In Create Sp
hi every one

when i want to create a stored procedure that contain character " with a ado component , i receive this error message :

'Parameter object is improperly defined. inconsistent or incomplete information was provided.'

but if i create this procedure from query analyzer , this sp creates successfuly.

whyyyyyyyyyyyyyyyy? :mad:

View Replies !

Error On Create Trigger
I have the following
CREATE TRIGGER dbo.tgrCacheCustomers
ON dbo.Customers
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask 'C:DependencyFile.txt','SELECT top 1 CustomerId FROM customers'
and I get the following error that I dont understand:

Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.

Any ideas someone?

View Replies !

Create Constraint Error
Hi.I have a procedure with this in it (there are no other references toasset_number_bak_tmp_pk in the procedure and it calls nothing else written byme, just system calls or normal dml).create table #asset_bak(asset_number varchar(60) not null,asset_desc varchar(100) null,location varchar(40) null,constraint asset_number_bak_tmp_pk primary key clustered (asset_number))When I run the procedure, I get this message:(1 row(s) affected)Msg 2714, Level 16, State 4, Procedure updatenavharrierdb, Line 19There is already an object named 'asset_number_bak_tmp_pk' in the database.Msg 1750, Level 16, State 0, Procedure updatenavharrierdb, Line 19Could not create constraint. See previous errors.How can I find where else the system thinks this constraint exists?I tried this but it only finds it in one place (one row in the result set),i.e. my procedure:select sysobjects.name, syscomments.textfrom sysobjects, syscommentswhere sysobjects.id = syscomments.id and((lower(sysobjects.name) like '%asset_number_bak_tmp_pk%') or(lower(syscomments.text) like '%asset_number_bak_tmp_pk%'))Is this somehow a case where I need to do something dynamically, or purge someinformation? I thought temp tables and their crony constraints disappearedafter the procedure exited.thanksJeff Kish

View Replies !

@@ERROR && CREATE TABLE
Hi

I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:

DECLARE @ERRCODE INT

CREATE TABLE x
SET @ERRCODE = @@ERROR

This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.

Is there a way that I can capture the error using @@ERROR and still let the script run ?

Thanks in advance

Mickster

View Replies !

Create Procedure Error
hi. i'm trying to create a stored procedure but it keeps messing up and i have absolutely no clue why. here is what i have:

CREATE PROCEDURE sp_OfficeReportStats AS

------------------------------------------------------------
--NEW CASE
------------------------------------------------------------

--NC2
CREATE TABLE TempWorkDB
(
ProsAtty SMALLINT, Stat INT
)
GO

INSERT INTO TempWorkDB (ProsAtty, Stat)

SELECT DefendantCase.ProsAtty, COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey = DefendantEventPros.VBKey WHERE DefendantEventPros.EventID=2 AND DefendantEventPros.EventDate BETWEEN DATEADD(MONTH,-2,GETDATE()) AND GETDATE() GROUP BY DefendantCase.ProsAtty
GO

UPDATE OfficeReport SET NC2=TempWorkDB.Stat FROM TempWorkDB WHERE TempWorkDB.Prosatty=OfficeReport.ProsAtty
GO

UPDATE OfficeReport SET NC2=0 WHERE NC2 IS NULL
GO

DROP TABLE TempWorkDB
GO
this code works in query analyzer just fine but it says i have an error at TempWorkDB. I do not have a TempWorkDB in my database currently. Waht am I doing wrong? thanks for you help!

View Replies !

Error: Could Not Create Constraint
Please see the attached file.
I am a beginner, an example will be usefull for me.
Sorry for my english.

View Replies !

DTS Error: "Cannot Create Instance"
I am using MS SQL Server 7.0 (baseline) on my WinNT PC and am trying to create a DTS package in order to import MS Access data into a MS SQL Server database. When I get to the end of the DTS setup where I click the FINISH button, I get an error message stating "Cannot create an instance for the DTS package".

Other uses are able to create a DTS package from their Win95 PC's but for some reason I cannot.

Any suggestions would be greatly appreciated.

Jim

View Replies !

Create Table Error
Dear All,

I was trying to create a table with many columns. However, I got the following error. Have anyone seen this error before?

>>>>>
Warning: The table 'PDMS_USER' has been created but its maximum row size (9118) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
<<<<<

Thanks.

View Replies !

DTS Error: "Cannot Create Instance"
I am using MS SQL Server 7.0 (baseline) on my WinNT PC and am trying to create a DTS package in order to import MS Access
data into a MS SQL Server database. When I get to the end of the DTS setup where I click the FINISH button, I get an error
message stating "Cannot create an instance for the DTS package".

Other uses are able to create a DTS package from their Win95 PC's but for some reason I cannot.

Any suggestions would be greatly appreciated.

Jim

View Replies !

CREATE ASSEMBLY ERROR: Msg 701
Hello there,

i have the following problem.

I need to get some .dll's into MS SQL-Server 2005, that i need to get a own made .dll installed.

When i try to:

Code Block
CREATE ASSEMBLY SystemWeb
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
WITH PERMISSION_SET = UNSAFE;

i get the following message:

Code Block
Warning: The Microsoft .Net frameworks assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.runtime.remoting, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 701, Level 17, State 13, Line 1
There is insufficient system memory to run this query.

When i try to create one of the another assemblies ( system.enterpriseservices, system.runtime.remoting, system.design ) i get the same message.

I looked for this Error and found only this BUG-report:
BUG#: 58267 (SQLBUG_70)
Article ID: 274030
http://support.microsoft.com/kb/274030/en-us

But this does not solve my problem.

As far as i know we use MS SQL-Server 2005 without any Service-Packs.

Question:

is there any Table/View to find out the Versionnumber/Service-Pack

In the moment i am waiting for our admin to install SP2 for SQL-Server,
hoping that this will fix the problem.

Greetings

View Replies !