April 24, 2009 at 3:57 am
i have to create a stored procedure like this one that have two parameters one is text and second is path for an image to be inserted in the DB
[font="Arial"]
Create procedure InsertImageToDB
(@Text text,@Path var char (max))
as
insert st_Table
(Text,Imge)
select @Text ,Bulk Column FROM OPENROWSET(BULK'"+@Path+"',SINGLE_BLOB) AS X
[/font]
but i get this error
Cannot bulk load because the file ""+@Path+"" could not be opened. Operating system error code 123(error not found).
April 24, 2009 at 6:00 am
You cannot use dynamic queries with openrowset. You will need to create the entire statement using a variable and then execute the variable. below are two examples:
SAMPLE 1
--------------------------------------------------------------------------------------
declare @sqlcursor as varchar(1000)
set @sqlcursor = 'select LTRIM(RTRIM(name)) as name from sysdatabases where name not in (''''tempdb'''',''''master'''',''''model'''',''''msdb'''',''''pubs'''') and status not in (4194832) order by name'
EXEC('DECLARE sqlCursor CURSOR FOR SELECT name FROM OPENROWSET(''SQLOLEDB'',''Data Source=localhost;Trusted_Connection=yes;Integrated Security=SSPI'', '''+@sqlcursor+''')
DECLARE @dbs AS VARCHAR(200)
--print @@FETCH_STATUS
--print @@CURSOR_ROWS
OPEN sqlCursor
FETCH NEXT FROM sqlCursor into @dbs
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM sqlCursor into @dbs
select @dbs as db
END
CLOSE sqlCursor
DEALLOCATE sqlCursor')
--------------------------------------------------------------------------------------
SAMPLE 2
--------------------------------------------------------------------------------------
DECLARE @QUERY AS VARCHAR(200)
DECLARE @OR AS VARCHAR(1000)
declare @server as varchar(100)
declare @dbs as varchar(100)
set @dbs='master'
set @server='localhost'
SET @QUERY ='select convert(varchar(100),DATABASEPROPERTYEX (''''' + @dbs + ''''', ''''Recovery'''')) as recovery_model'
--SET @query = 'select @@version'
SET @OR = 'SELECT recovery_model FROM OpenRowset(''SQLOLEDB'',''Data Source=' + @server + ';Trusted_Connection=yes;Integrated Security=SSPI'',''' + @QUERY + ''')'
EXEC (@OR)
May your fish always be bigger
Than the holes on your net.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply