April 11, 2005 at 10:00 pm
I am working with a database that was created in Access. I've used DTS to import it into MS SQL and I'm getting an error message. I'm a SQL newbie and I'm totally lost as to how to correct this. Any help is appreciated!
Error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Code responsible for the causing the error message:
set Category = Server.CreateObject("ADODB.Recordset") Category.ActiveConnection = MM_newsmanager_STRING Category.Source = "SELECT tblNM_NewsCategory_1.CategoryValue AS ParentCategoryValue, tblNM_NewsCategory.CategoryID, tblNM_NewsCategory.CategoryValue, tblNM_NewsCategory.ParentCategoryID, tblNM_NewsCategory.CategoryDesc, tblNM_NewsCategory.CategoryLabel, tblNM_NewsCategory.CategoryImageFile FROM (tblNM_NewsCategory RIGHT JOIN tblNM_News ON tblNM_NewsCategory.CategoryID = tblNM_News.CategoryID) LEFT JOIN tblNM_NewsCategory AS tblNM_NewsCategory_1 ON tblNM_NewsCategory.ParentCategoryID = tblNM_NewsCategory_1.CategoryID GROUP BY tblNM_NewsCategory_1.CategoryValue, tblNM_NewsCategory.CategoryID, tblNM_NewsCategory.CategoryValue, tblNM_NewsCategory.ParentCategoryID, tblNM_NewsCategory.CategoryDesc, tblNM_NewsCategory.CategoryLabel, tblNM_NewsCategory.CategoryImageFile HAVING tblNM_NewsCategory.ParentCategoryID LIKE '" + Replace(Category__nmcatvalue, "'", "''") + "' ORDER BY tblNM_NewsCategory_1.CategoryValue, tblNM_NewsCategory.CategoryValue" Category.CursorType = 0 Category.CursorLocation = 2 Category.LockType = 3 Category.Open() Category_numRows = 0
As I said, I am new at this and I have no idea what I need to change in this code. Any help will be greatly appreciated!!
April 11, 2005 at 11:17 pm
Ezara,
DTS has a tendency to create text or ntext columns by default, which are so-called LOB (Large Object) datatypes in SQL Server. These datatypes can hold large amounts of data (up to 2 GB) but have certain restrictions, among them, the fact that they can not be compared to one another or to a literal.
Apparently your query is trying to compare two such columns. Can you run the following query in the database that you run the selet query in to indicate which columns are BLOB or CLOB (binary LOB or character LOB) columns. Please return the result set.
select object_name(id) as TableName
, name as ColumnName
, type_name(xtype) as DataType
from syscolumns
where object_name(id) in ('tblNM_NewsCategory', 'tblNM_News')
and type_name(xtype) in ('text', 'ntext', 'image')
order by TableName
What I'll suggest is that you either convert the columns to varchar in the DTS package when creating the table or in the query itself.
Scott Thornburg
April 11, 2005 at 11:27 pm
I ran the query and here are the results:
tblNM_News ItemMemo ntext
tblNM_News ItemDesc ntext
tblNM_News ExtraDetailMemoLabel1 ntext
tblNM_News ExtraDetailMemoLabel2 ntext
tblNM_News ExtraDetailMemoLabel3 ntext
tblNM_News ExtraDetailMemoLabel4 ntext
tblNM_News ExtraDetailMemoLabel5 ntext
tblNM_News ExtraDetailMemo1 ntext
tblNM_News ExtraDetailMemo2 ntext
tblNM_News ExtraDetailMemo3 ntext
tblNM_News ExtraDetailMemo4 ntext
tblNM_News ExtraDetailMemo5 ntext
tblNM_NewsCategory CategoryDesc ntext
April 12, 2005 at 1:27 am
Yup.
The problem is in the GROUP BY clause. You are grouping by tblNM_NewsCategory.CategoryDesc, but this is a ntext column. Such is not allowed by SQL Server. You have two options:
1) In the DTS import, change the datatype of the table you are creating to be varchar(x), where x is the maximum length of this column. This presumes that the column is not too wide, i.e., not more than a few thousand characters (the total width of the table must be less than 8060 bytes), excluding the ntext columns.
2) OR, in your SQL query, change the group by clause to be something like GROUP BY ... convert(varchar(x), tblNM_NewsCategory.CategoryDesc), where x again is greater than / equal to the maximum number of chartacters of CategoryDesc in your table. You'll also have to do the same substitution in the SELECT clause of the query.
Item 2 is probably easier to implement, but Item 1 is the way I would do it in order the make the datatypes clean. Kind of depends if this is a one-off or on-going code. All this presumes that CategoryDesc is not long (at least not > 8000 characters).
As a second note, the query itself maybe able to be cleaned up some. For example, is it really the case that tbl_News can have a category that's not in tblNM_NewsCategory? That's what the RIGHT JOIN implies. If you can turn this into an inner join, then you can move the having clause into a where clause (move efficient). You also may not need the group by at all, since it primarily serves to remove duplicates. Anyway, if you want to discuss this point more, we can.
Hope this helps
Scott Thornburg
April 12, 2005 at 11:13 pm
Many thanks for your help! I used option 1. It cleared up the problem I was having with this particular segment of code.
I did not write the query, I am just trying to make this application work on MS SQL. I am definately open to any suggestions you have to streamline the query. From my very inexperienced newbie eyes, it does appear that the only category that tbl_News and tblNM_NewsCategory have in common is the category labeled "CategoryID".
This is my first foray into SQL and I feel so lost at times (even with the pile of SQL books at my elbow!)
Again, thank you for your help with everything. It is greatly appreciated!
Ezara Penning
April 12, 2005 at 11:59 pm
You're quite welcome. Glad I could help.
As far as cleaning up the query, could you execute the following and return the results
select tblNM_News.CategoryID, count(*)
from tblNM_News
where NOT EXISTS (select * from tblNM_NewsCategory
where tblNM_NewsCategory.CategoryID = tblNM_News.CategoryID)
group by tblNM_News.CategoryID
This tells us if there are any entries in tblNM_News that have either a null categoryID or a categoryID value that does not exist in tblNM_NewsCategory.
Also, execute:
select nc1.ParentCategoryID, count(*)
from tblNM_NewsCategory nc1
where NOT EXISTS (select * from tblNM_NewsCategory nc2
where nc2.CategoryID = nc1.ParentCategoryID)
group by nc1.ParentCategoryID
This tells us if there are either null ParentCategoryID's or values that are not set up as CategoryIDs.
With the results of these two queries, the original SELECT statement can probably be optimized.
April 13, 2005 at 9:07 pm
Queries have been run. Results are below.
First Query Results:
CategoryID | (No column name)
37 4
Second Query Results:
ParentCategoryID | (No column name)
0 8
Viewing 7 posts - 1 through 6 (of 6 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