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?
K108 wrote: