Can anyone Help Me??

Hi gurus,
I have been trying to export my mssql tables into mysql database using DTS. But I get a very strange error for some of my mssql tables which have "text" and "ntext" as field datatypes.The error message that i was gettins is,
"Query based insertion or updating of BLOB values is not supported".
But iam not using any BLOB values in my tables.
So if anyone has any solution for this please let me know.
Thanking you.Are using ODBC to connect the database in ur package?
Try to use SQL Oledb driver.
download latest service pack of SQL Server
download latest MDAC also,if it is not latest.|||I do not think this is going to work. when the error says blob it is talking about the text columns. avoid using text and ntext if at all possible. it usually not really needed. Find the max len of the exisiting data and perhaps change your data type.

Can any SQL Gurus help?

Hi, I've moved from an access db to SQL server 2000.

I have these two sql commands which work fine in access, but are giving me an error in SQL server.

sql statements:

sqltext = "UPDATE SQLPeople SET Answer='"& Request.Form("Answer")&"', Comments='"& Request.Form("Comments")&"', DateUpdated=" & FormatDateTime(Now, 2) & " WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "

sqltext1 = "SELECT RID FROM SQLPeople WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "
rsRecord.Open sqltext1, objConn, adOpenDynamic, adLockOptimistic

objConn.Execute(sqltext)

Error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference

Im a bit stumped =( Again, works fine in Access, but not SQL Server 2000i'd recommend rethinking your where statement for other reasons than sql server not liking it.

i think this page might help you
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

or
http://www.4guysfromrolla.com/webtech/tips/t122600-1.shtml

or passing the id in a field in the <FORM>

anyway, if you only want to change your sql, i think you just replace WHERE with HAVING and add GROUP BY and top

select top 1 MIN(RID) as minRID from People GROUP BY RID having Answer IS NULL and ReadingType = 1|||thanks for the help. I've checked those articles which both seem to create or capture a value for each record using identity@.@.. However, Im still not clear on how to update the record with that value in an ID column

i'm doing my initial insert, then a select for identity@.@. and i assume an update on the record with that value.

not too clear on how this is done, the update portion.

this is the example that is given on 4guys.
**********
Dim db,rcs,new_identity

'Create a database connection
Set db = Server.CreateObject("adodb.connection")
db.Open "DSN=MyDSN"

'Execute the INSERT statement and the SELECT @.@.IDENTITY
Set rcs = db.execute("insert into tablename (fields,..) " & _
"values (values,...);" & _
"select @.@.identity").nextrecordset

'Retrieve the @.@.IDENTITY value
new_identity = rcs(0)
***********

So not clear on how to update the record in question with the record value?

thanks,

j|||sorry, idont have time to look much into it right now, but i didnt use ADO for my updates..i did it in a stored procedure|||I would call a stored proc for this:

From your source code:

sqlstr = "exec my_sp"

And in the SP:
___
INSERT...

UPDATE ... WHERE idField = @.@.IDENTITY
___

This makes you be sure you update the last created id in your table.

If you need to make more transactions between INSERT and UPDATE:
__
INSERT...
SET @.mycurrentID = @.@.IDENTITY

...

UPDATE... WHERE idField = @.mycurrentID
__