Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142

    Post Unanswered: 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:

    Code:
     
    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •