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 I
do't add the code included in section A, when I add it the i obtain the
error: Cursor not returned from query

Anyone can help me?

Thanks Carlo M.

set nocount on

IF OBJECT_ID('storico_big') IS NULL -- section A begin
create table storico_big( data datetime,
bcarrier varchar(20),
bda CHAR(30),
bzone char(50),
bdur int) ;
insert into storico_big -- section A
end

select top 10000
adetdate,bcarrier,bda,bzone,bdur
from pp_cdr (nolock)
where
adetdate < :data_fin and adetdate > :data_in order by adetdate
set nocount off

-- end of queryIW2FIV (carlo.merlini[NONROMPERE]@.libero.it) writes:
> I'm a really beginner about sql2000.
> During my test I have created the following query. It's works ok until I
> do't add the code included in section A, when I add it the i obtain the
> error: Cursor not returned from query

Apparently you are using some environment unknown to me. At least I
don't recognize the message.

> IF OBJECT_ID('storico_big') IS NULL -- section A begin
> create table storico_big( data datetime,
> bcarrier varchar(20),
> bda CHAR(30),
> bzone char(50),
> bdur int) ;
> insert into storico_big -- section A
> end

Since there are several apparent syntax errors here, it would have been nice
if you environment had returned the errors from SQL Server, rather than
bitching about the missing cursor.

It can be a good to run the query from Query Analyzer to get better
error diagnostics.

I don't really want to suggest a correction, because I can't understand
what you are trying to do. It appears that in the same batch that you first
want to create a table, insert into it, and then select data from another
table.

Possibly you want to insert the data from the SELECT statement into
storico_big, but in such case you should

1) get rid of that extraneous end
2) add an explicit column list to the INSERT statement.

However, I have a feeling that if you insert data into the table, the
client environment will still complain about a missing cursor...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> IW2FIV (carlo.merlini[NONROMPERE]@.libero.it) writes:
> > I'm a really beginner about sql2000.
> > During my test I have created the following query. It's works ok until I
> > do't add the code included in section A, when I add it the i obtain the
> > error: Cursor not returned from query
> Apparently you are using some environment unknown to me. At least I
> don't recognize the message.
I would suspect that it's some data layer (such as ADO, ADO.NET, DAO,
etc), which has at least two different methods of performing work in
the database - one for performing data retrieval and another (possibly
on another object, if the layer is object oriented) which allows data
manipulation.

For instance, ADO.NET has ExecuteReader and ExecuteNonQuery methods on
it's command object.

Damien|||Damien (Damien_The_Unbeliever@.hotmail.com) writes:
> I would suspect that it's some data layer (such as ADO, ADO.NET, DAO,
> etc), which has at least two different methods of performing work in
> the database - one for performing data retrieval and another (possibly
> on another object, if the layer is object oriented) which allows data
> manipulation.
> For instance, ADO.NET has ExecuteReader and ExecuteNonQuery methods on
> it's command object.

Obviously IWZFIV is not using ADO .Net. ADO .Net does work with cursors
at all, as far as a I know. A more substantial clue is the condition:

adetdate < :data_fin and adetdate > :data_in order by adetdate

Apparently IWZFIV is using some form of embedded SQL.

Anyway, I would not really describe ADO .Net as providing different methods
for different purposes. If all you want is minimalism, you can do every-
thing with ExecuteReader. The other methods, ExecuteNonQuery, ExecuteScalar
and DataAdapter.Fill can be seen as convenience methods implemented on
top of ExecuteReader. (OK, this is not really true. There are some
fine differences when there are multiple error messages and result sets
interleaved.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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 1
Could 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.Check DB_option to see whether local and global cursors are default, SP1 for SQL2005 is older one and latest is SP2.

Also see this http://support.microsoft.com/kb/302477 that talks about hotfix.|||OK, I went back and read that article again. It says this happens "If a cursor is created on a system stored procedure that returns schema information from a remote server..." I'm sorry, but I'm still learning to program in SQL and I don't see where I'm doing that. Is it in the DELETE FROM statement? And that article talks about SQL 2000 SP2. I'm running SQL 2000 SP4 on that box. Would I still need the hotfix?

I tried this with a server running SQL2005 SP2 (Version 9.00.3159), and got the same error.

I also tried setting the cursor option on the databases involved on both servers, to both global and local, in all 4 configurations. Still getting the same error.

Any other ideas?