I have a table in SQL server which has two columns with Text Data Type. I am running VFP 8.0, no service packs. SQL 2000. MDAC v2.5
I use CursorAdpater with ODBC to retrieve and update the data. Prepared property of CursorAdapter is set to False.
I get the following error when I try to execute the same SQL a few times over and over.
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
I got the following trace from SQL Profiler.
When I run it for the first time, second time and third time...I get this and SQL doesn't fail.
exec sp_executesql N'SELECT tblTest.ID, tblTest.TextField1,
tblTest.TextField2
FROM tblTest WHERE tblTest.SegmentID = @P1 ', N'@P1 float',
3.000300000000000e+004
But when I get the following, SQL fails.
declare @P1 int
set @P1=-1
exec sp_prepare @P1 output, N'@P1 text', N'SELECT tblTest.ID,
tblTest.TextField1, tblTest.TextField2, FROM tblTest
WHERE tblTest.SegmentID = @P1 ', 1
select @P1
I have the following questions.
1. Why it fails only sometimes?
2. Why VFP/ODBC trying to Prepare the SQL statement evenif CursorAdapter.Prepared = .F.. Notice that in first few attempts, it doesn't try to Prepare.
3. The error message makes no sense because tblTest.SegmentID field is of type intger.
Any ideas?
Thank you.
- Jayesh