locked
problem creating a stored procedure RRS feed

  • Question

  • I'm trying to execute this stored procedure below on my home pc that is running sql server 2005 express edition.

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE readImageFromfile2 @filename nvarchar(128)=NULL, @table nvarchar(128)=NULL,@column nvarchar(128)=NULL, @where nvarchar(2000)=NULL
    --Reads an image (binary data) from a file into an image column appending it to the existing data
    AS
    SET NOCOUNT ON
    IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
     BEGIN
     PRINT 'readImageFromfile reads an image from a file into an image column'
     PRINT 'Usage:'
     PRINT 'EXEC readImageFromfile FileName, tableName, columnName, WhereCondition'
     PRINT ''
     PRINT 'For example: EXEC readImageFromfile2 ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
     RETURN
     END
    DECLARE @i int, @size int, @blocks int, @cmd varchar(8000)
    create table #filedetails(altname varchar(30),size int,createdate varchar(32),createtime varchar(32),lastwrittendt varchar(30),lastwrittentime varchar(32),
    lastaccessdt varchar(30),lastaccesstime varchar(32),attributes int)
    insert into #filedetails exec master..xp_getfiledetails @filename
    set @size=(select size from #filedetails)
    if @size=null
     BEGIN
     PRINT 'File error.'
     RETURN
     END
    drop table #filedetails
    SET @blocks=@size/8000+1
    --start dynamic SQL declaration
    SET @cmd='DECLARE @ptrval binary(16), @fso int, @file int, @i int,@j int, @hr int, @buffer varbinary(8000) '
    SET @cmd=@cmd+' EXEC @hr = sp_OACreate ''ADODB.Stream'', @file OUT '
    SET @cmd=@cmd+' EXEC @hr = sp_OAMethod @file, ''Open'' '
    SET @cmd=@cmd+' EXEC @hr = sp_oasetproperty @file, ''Type'', 1 '
    SET @cmd=@cmd+' EXEC @hr = sp_oasetproperty @file, ''LoadFromFile'','''+@filename+''''
    SET @cmd=@cmd+' SELECT @ptrval = TEXTPTR('+@column+') FROM '+@table+' '+@where+' '

     SET @cmd=@cmd+'set @j='+str(@blocks)+' while @j>0 begin '
     SET @cmd=@cmd+' exec @hr = sp_oamethod @file, ''read'',  @buffer out , 8000 '
     SET @cmd=@cmd+'set @i=(select DATALENGTH('+@column+') from '+@table+' '+@where+') '
     SET @cmd=@cmd+'UPDATETEXT  '+@table+'.'+@column+' @ptrval @i 0 @buffer '
     SET @cmd=@cmd+'set @j=@j-1 end '

    --execut dynamic SQL
    SET @cmd=@cmd+' EXEC @hr = sp_OAMethod @file, ''Close'' '
    SET @cmd=@cmd+'EXEC @hr = sp_OADestroy @file EXEC @hr = sp_OADestroy @fso'
    exec( @cmd)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    when exexuting the above stored procedure the following message is generated:

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'sp_oasetproperty'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.


    I have executed this stored procedure under the sample database from microsoft named
    adventureworks. With this procedure I'm trying to reconstruct all my jpeg files that are stored in one of my sql tables. -- Any help is most appreciated.

    Friday, March 13, 2009 4:57 PM

Answers

  • Then you need to mention MASTER.DBO.ProcedureName

    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by Cire1507 Friday, March 13, 2009 5:44 PM
    Friday, March 13, 2009 5:33 PM

All replies

  • Check if this database have  sp called sp_oasetproperty. adventureworks is by default Case sensitive.  Check the procedure name is correctly mentioned.

    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Friday, March 13, 2009 5:14 PM
  • I have checked under stored procedures for the AdventureWorks database but I don't see
    the stored procedure sp_oasetproperty. I see this procedure under the
    Master database under the folder named System Stored Procedures. Thanks.

    Friday, March 13, 2009 5:29 PM
  • Then you need to mention MASTER.DBO.ProcedureName

    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by Cire1507 Friday, March 13, 2009 5:44 PM
    Friday, March 13, 2009 5:33 PM
  • Yes of course you are right.... Sorry, many thanks for your help!!!
    Friday, March 13, 2009 5:44 PM