August 16, 2019 at 7:13 pm
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
August 16, 2019 at 8:40 pm
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;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2019 at 7:07 pm
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
August 19, 2019 at 7:20 pm
odd part it did one gave this error on next row, so 5 updates and 5 errors
August 19, 2019 at 8:05 pm
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
August 19, 2019 at 8:37 pm
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