using variables in for openrowset bulk update

  • Code below works great, problem is I need to introduce variables in the openrowset update statement (below). is there a way to put variables into this command?

    THanks

    THIS WORKS

    declare @d1 varchar(40),declare @recid varchar (12),

    declare dbcursor cursor for

    select image_id,other_id_number from KDOC_Pat_image_backload

    open dbcursor

    fetch next from dbcursor into @d1,@recid

    while (@@FETCH_STATUS =0)

    begin

    update images

    set image_data =

    (

    SELECT MyImage.*

    from Openrowset(Bulk 'C:\dba\Pictures\Imagetoproc\~TEMPNGADCD9AE8-679B-4610-879F-671064DB7063.zip', Single_Blob) MyImage

    )

    where images.image_id = 'ADCD9AE8-679B-4610-879F-671064DB7063'

     

    THIS DOESN'T

    declare @error varchar(50)

    declare @zipname varchar(60)

    declare @imageid varchar (40)

    declare dbcursor cursor for

    select 'C:\dba\Pictures\Imagetoproc\~TEMPNG' + convert(varchar(36),image_id) + '.zip' as zipname,image_id

    from KDOC_Temp_LoadPatimages where processed = 0

    open dbcursor

    fetch next from dbcursor into @zipname,@imageid

    while (@@FETCH_STATUS =0)

    begin

    update images

    set image_data =

    (

    SELECT MyImage.*

    from Openrowset(Bulk @zipname, Single_Blob) MyImage

    )

    where images.image_id = @imageid

    set @error = @@error

    print @error

    fetch next from dbcursor into @zipname,@imageid

    end

    close dbcursor

    deallocate dbcursor

  • Last year I had to do something similar with a csv file.  Openrowset can't contain sql expressions, only literal strings.  So dynamic sql is the way to go.  Something like:

    drop function if exists dbo.get_guid_zip_sql;
    go
    create function dbo.get_guid_zip_sql(
    @image_idchar(36))
    returns nvarchar(max)
    as
    begin
    return (
    select
    'update images ' +
    'set ' +
    'image_data=(select myimage.* from openrowset(bulk ''' + @image_id + '''.zip, single_blob) myimage) ' +
    'where ' +
    'images.image_id = ''' + @image_id + ''';')
    end
    go

    drop proc if exists proc_dyn_sql;
    go
    create proc proc_dyn_sql
    as
    set nocount on;

    declare
    @sqlnvarchar(max),
    @d1varchar(40),
    @recidvarchar(12);

    declare dbcursor cursor static forward_only
    for
    select
    image_id,
    other_id_number
    from
    KDOC_Pat_image_backload;

    open dbcursor;
    fetch next from dbcursor into @d1, @recid;

    while @@fetch_status=0
    begin
    select @sql=dbo.get_guid_zip_sql(@d1);
    exec sp_executesql @sql;

    fetch next from dbcursor into @d1, @recid;
    end

    close dbcursor;
    deallocate dbcursor;

    set nocount off;
    go

    exec proc_dyn_sql;

    • This reply was modified 4 years, 7 months ago by  Steve Collins. Reason: Pasted incomplete code by accident

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Ok got closer here is code below issue is there are 10 records, it only does 5 (cursor select shows 10).  Odd error message also

    Msg 7202, Level 11, State 2, Line 26

    Could not find server 'update images set image_data=(select myimage' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

     

    create function dbo.get_guid_zip_sql(@zipname varchar(100),

    @image_id char(36))

    returns nvarchar(max)

    as

    begin

    return (

    select

    'update images ' +

    'set ' +

    'image_data=(select myimage.* from openrowset(bulk ''' + @zipname + ''', single_blob) myimage) ' +

    'where ' +

    'images.image_id = ''' + @image_id + ''';')

    end

    go

    declare @error varchar(50)

    declare @zipname varchar(100)

    declare @imageid varchar (40)

    declare @sql nvarchar(max)

    declare dbcursor cursor for

    select zipfilename,image_id

    from KDOC_Temp_LoadPatimages where processed = 0

    open dbcursor

    fetch next from dbcursor into @zipname,@imageid

    while @@fetch_status=0

    begin

    select @sql=dbo.get_guid_zip_sql(@zipname,@imageid);

    exec sp_executesql @sql;

    fetch next from dbcursor into @zipname, @imageid

     

    print @sql

    exec @sql

    --print @error

    fetch next from dbcursor into @zipname,@imageid

    end

    close dbcursor

    deallocate dbcursor

  • odd part it did one gave this error on next row, so 5 updates and 5 errors

     

  • Hmm... Here's a segment of the code we're running in production:

    select @sql=
    'with history_cte as ( ' +
    'select ' +
    cast(@test_id as varchar(12)) + ' hf_id, ' +
    cast(@u_id as varchar(12)) + ' u_id, ' +
    'datafile.* ' +
    'from ' +
    'openrowset(bulk ''' + @bulk_filename + ''', ' +
    'data_source = ''wodmodecsv'', ' +
    'formatfile=''wodmode_history.fmt'', ' +
    'formatfile_data_source = ''wodmodecsv'', ' +
    'DATAFILETYPE = ''char'', ' +
    'fieldquote = ''"'', ' +
    'firstrow = 2) as datafile) ' +
    'insert wm.history_users_raw(hf_id, u_id, class_dt, workout, result, scaled, pukie, work, work_seconds, formatted_result, notes, workout_desc) ' +
    'output inserted.* into #history_users_raw ' +
    'select * from history_cte;'

    It's running on Azure SQL and the 'wodmovecsv' (which is an azure blob storage account) is registered as an external datasource.  I remember setting this up was tricky to get working.  A csv file is a nightmare that entails a format file that has it's own special formatting rules...  As I recall for some odd reason the DATAFILETYPE label must be all caps.  Maybe this helps?  I do not have instance of 2016 to test with right now.

    Also, does it matter the proc didn't do a complete rollback of the 5 records?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • The code you posted has two lines that say: "fetch next from dbcursor into @zipname,@imageid" within the cursor loop.  Please try with only one fetch (at the end of the loop).

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply