crystalreporting
Instructor
I have a stored procedure that uses the SQL2000 TEXTCOPY function to insert a BLOB image. The stored procedure is;
When I call this from a simple query it works fine;
However I want to use this as part of a cursor/fetch next statement so I need to make the @filename and @whereclause dynamic using a variable called @InvoiceNumber. I tried;
But I get Incorrect Syntax errors. I've tried many combinations of single quotes, double quotes and ampersands instead of plusses but I'm completely lost...
Peter Shirley
Macola Consultant, PA and surrounding states.
Code:
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'C:\textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str
When I call this from a simple query it works fine;
Code:
exec sp_textcopy @srvname = '(local)',
@login = 'sa',
@password = 'notrealpassword',
@dbname = '[002]',
@tbname = 'ImageTable',
@colname = 'ImageField',
@filename = 'c:\Export\50.pdf',
@whereclause = " where OrderNumber = ' 50'",
@direction = 'I'
However I want to use this as part of a cursor/fetch next statement so I need to make the @filename and @whereclause dynamic using a variable called @InvoiceNumber. I tried;
Code:
exec sp_textcopy @srvname = '(local)',
@login = 'sa',
@password = 'notrealpassword',
@dbname = '[002]',
@tbname = 'ImageTable',
@colname = 'ImageField',
@filename = 'c:\Export\' + LTRIM(@InvoiceNumber) + '.pdf',
@whereclause = " where OurRef = '" + @InvoiceNumber + "'",
@direction = 'I'
But I get Incorrect Syntax errors. I've tried many combinations of single quotes, double quotes and ampersands instead of plusses but I'm completely lost...
Peter Shirley
Macola Consultant, PA and surrounding states.