VFP insert into SQL Server text data type columns

[kprogrammer]

I am trying to insert a new row into an SQL Server 2000 table from VFP 8.0 The table includes two text data type columns. When I have small strings of data, the insert will populate the text columns, but when I try to insert larger character strings (80,000 characters), no data is stored in the text columns. The other columns in the table char and varchar columns are populated. I am using an ODBC connection to the SQL Server table.

Does anyone have any idea what I am missing/doing wrong?

Kirk

 

[Mike Lewis]

Kirk,

How are you doing this? Remote view? SQL pass-through? If the latter, can you post your code?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kprogrammer]

I am using SQL passthrough. The called process adds the connection. The cleanstring method does just what it implies, cleans up the string for passing to SQL and then adds the required enclosing quotes.

c_erdtl and c_memtxt are the strings going to the text columns. They are build by a series of FGETS against system files so that "*" can be added where line breaks were removed.

c_erdetl=ALLTRIM(omed.cleanstring(c_erdetl))
c_memtxt=ALLTRIM(omed.cleanstring(c_memtxt))
p_cMsg=LEFT(p_cMsg,60)
p_cCode=LEFT(p_cCode,60)
c_place= LEFT("IN: "+ALLT(p_cModul)+" LINE:"+ALLT(STR(p_nLine)),100)
p_cUser=LEFT(p_cUser,50)

c_str= "INSERT INTO tblerrorlog " + ;
"(er_product,er_datetime,er_user,er_errnum,er_msg,er_code,er_place,er_detl,er_memdump)" + ;
"VALUES " + ;
"(" + ;
"'&p_cAppName.'" + ;
",'"+TTOC(DATETIME())+"'"+ ;
",'&p_cUser.'" + ;
",'"+ALLT(STR(p_nErNo))+"'"+ ;
",'&p_cMsg.','&p_cCode.'"+ ;
",'&c_place.'" +;
","+c_erdetl+ ;
","+c_memtxt+ ;
")"

omed.sqlexecute(c_str)

 

[Mike Lewis]

Kirk,

I'm not sure, but I think this is a limitation of INSERT in T-SQL. It doesn't allow you to directly insert values in a column more than one page in size (1 page = 8192 KB). You have to do it indirectly, using the TEXTPTR() function.

The way I usually insert text (and image) data is to use the TEXTCOPY utility. This is not documented in BOL, but there is some sample code somewhere in the SQL Server directory structure. If you can't find it, let me know and I'll see if I can dig something out.

I'm not an expert in any of this ... there might well be a simpler solution.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kprogrammer]

Thanks for the info. I'll look into this. Another contact also told me to look into limits on the length of SQL statements.

Kirk

 

[Mike Lewis]

Kirk,

Another contact also told me to look into limits on the length of SQL statements.

That's unlikely to be a problem on the VFP side. On SQL Server, I believe the limit is something like 64 K, which presumably is not a problem either.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)