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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy