January 24, 2003 at 9:32 am
Can anyone tell me how to get around the limitation of using ntext in a stored proc? I am trying to create a SP in SQL Server that reads in a field from the database of type ntext, but when I try to create the stored procedure below I am given the following error:
Error 2739: The text, ntext, and image data types are invalid for local variables.
I cannot change the data type because this database belongs to another company we are developing for. We are trying to compare the length of a file's contents with that of a field in the database and only use the one which is larger. The stored procedure I am writing is below:
CREATE PROCEDURE spCompareFile2Field @input varchar(128), @output varchar(128), @tbl varchar(32), @fld varchar(32), @where varchar(256) = ''
AS
set nocount on
declare @fso int, @ifile int, @ofile int, @ret int, @oret int, @line varchar(8000), @fblen int, @dblen int, @filecont ntext, @dbfld ntext
set @filecont = ''
create table #fbody (fcontent ntext NULL)
create table #dbody (dcontent ntext NULL)
exec sp_oacreate 'scripting.filesystemobject', @fso out
exec sp_oamethod @fso, 'opentextfile', @ifile out, @input, 1
exec sp_oamethod @fso, 'createtextfile', @ofile out, @output, 1
exec @ret = sp_oamethod @ifile, 'readline', @line out
while(@ret = 0)
begin
set @filecont = @filecont + @line
exec @ret = sp_oamethod @ifile, 'readline', @line out
end
insert into #fbody values (@filecont)
insert into #dbody exec('select ' + @fld + ' from ' + @tbl + ' ' + @where)
select @fblen = (select datalength(fcontent) from #fbody)
select @dblen = (select datalength(dcontent) from #dbody)
select @fblen as fblen, @dblen as dblen
if(@fblen > @dblen)
begin
exec('update ' + @tbl + ' set ' + @fld + '=''' + @filecont + ''' ' + @where)
print 'table was updated with contents of the input file'
end
else
begin
select @dbfld = (select dcontent from #dbody)
exec @oret = sp_oamethod @ofile, 'write', NULL, @dbfld
print 'file was updated with contents of the database field'
end
drop table #fbody
drop table #dbody
GO
Any help would be greatly appreciated!
January 24, 2003 at 9:55 am
Unfortunately you cannot declare a variable for use of those types for use. You will have to use a char or varchar type if possible for your data.
January 24, 2003 at 10:00 am
I kinda figured that... however, I did come up with an idea you may be able to help me with. I noticed you can pass ntext datatypes so I decided to call a SP with all the same parameters. This SP will just pull the field and then call a second SP passing the field (which is ntext). Here let me show you what I was think...
CREATE PROCEDURE spGetDBField @input varchar(128), @output varchar(128), @tbl varchar(32), @fld varchar(32), @where varchar(256) = ''
AS
set nocount on
declare @cmd varchar(1024)
set @cmd = 'spCompareFile2Field ''' + @input + ''', ''' + @output + ''', exec(select ' + @fld + ' from ' + @tbl + ' ' + @where + '), ''' + @tbl + ''', ''' + @fld + ''', ''' + @where + ''''
exec(@cmd)
GO
The problem is, I don't know how to pull the field and pass it in the call to the second SP. Any suggestions?
By the way, thanks for responding so fast!
January 24, 2003 at 1:52 pm
Unfortunately, you hit on the one thing we all have tried to figure out. You can put the data into a varchar(8000) but if it goes over 8000 you cannot pass in. You also cannot concatinate strings into an SP input/output variable. Sorry, I think most everyone has come across and not been able to use this.
Edited by - antares686 on 01/24/2003 1:56:38 PM
January 24, 2003 at 1:56 pm
Thanks for your help. I guess I'll just try and change the functionality.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply