DTS ActiveX Script Result: "Type Mismatch" error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS ActiveX Script Result: "Type Mismatch" error

Author

Topic

kwilliams

194 Posts

Posted - 2005-11-30 : 17:41:43

I've created an ActiveX Script within a SQL Server 2000 DTS Package that exports data from a DB table into an XML file (see below).

This job works great, but when I tried to use the code on another DB table, I receive a "Type Mismatch" error on some of the fields. I don't really understand why I'm receiving this, because the field's properties are the same as other fields that work fine. Here are a couple of examples of DB tables that are receiving this error:

First DB table
COLUMN NAME DATA TYPE LENGTH ALLOW NULLS
id int 4 no
title char 100 yes
description char 100 yes
...and so on

Second DB table
COLUMN NAME DATA TYPE LENGTH ALLOW NULLS
id int 4 no
title char 100 yes
description text 16 yes
other char 200 yes
...and so on

If anyone can help me to learn why I'm getting this error message, and how I can fix the problem, it would be greatly appreciated. Thanks.

***WORKING CODE SAMPLE***
DB Data (dbo.tblSample1):
id fname lname
1 Jane Doe
2 Joe Schmo
3 John Doe

ActiveX Script:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
	Dim objADORS
	Dim objXMLDoc

	Dim nodeRoot
	Dim nodeTemp
	Dim nodeSample

	'Create ADO and MSXML DOMDocument Objects
	Set objADORS = CreateObject("ADODB.Recordset")
	Set objXMLDoc = CreateObject("MSXML2.DOMDocument.4.0")

	'Run the stored procedure and load the Recordset
	objADORS.Open "SELECT * FROM tblSample1", _
		"PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=USERNAME;PWD=PASSWORD;DATABASE=DBNAME;" 

	'Prepare the XML Document
	objXMLDoc.loadXML "<root />"
	Set nodeRoot = objXMLDoc.documentElement

	'For each record in the Recordset
	While Not objADORS.EOF
		Set nodeSample = objXMLDoc.createElement("sample")
		nodeRoot.appendChild nodeSample

		Set nodeTemp = objXMLDoc.createElement("id")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("id").Value)
		nodeSample.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("fname")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("fname").Value)
		nodeSample.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("lname")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("lname").Value)
		nodeSample.appendChild nodeTemp

		objADORS.moveNext
	Wend

	objADORS.Close
	Set objADORS = Nothing

	'Save the created XML document
	objXMLDoc.Save "B:\sample1.xml"

	Main = DTSTaskExecResult_Success
End Function

XML Output:
<root>
<sample>
<id>1</id>
<fname>Jane</fname>
<lname>Doe</lname>
</sample>
<sample>
<id>2</id>
<fname>Joe</fname>
<lname>Schmo</lname>
</sample>
<sample>
<id>3</id>
<fname>John</fname>
<lname>Doe</lname>
</sample>
</root>

***NOT-WORKING CODE SAMPLE***
DB Data (dbo.tblSample2):
id title description other
1 Subject1 This is a test Other info
2 Subject2 This is a test Other info
3 Subject3 This is a test Other info

ActiveX Script:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
	Dim objADORS
	Dim objXMLDoc

	Dim nodeRoot
	Dim nodeTemp
	Dim nodeSample

	'Create ADO and MSXML DOMDocument Objects
	Set objADORS = CreateObject("ADODB.Recordset")
	Set objXMLDoc = CreateObject("MSXML2.DOMDocument.4.0")

	'Run the stored procedure and load the Recordset
	objADORS.Open "SELECT * FROM tblSample2", _
		"PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=USERNAME;PWD=PASSWORD;DATABASE=DBNAME;" 

	'Prepare the XML Document
	objXMLDoc.loadXML "<root />"
	Set nodeRoot = objXMLDoc.documentElement

	'For each record in the Recordset
	While Not objADORS.EOF
		Set nodeSample = objXMLDoc.createElement("sample")
		nodeRoot.appendChild nodeSample

		Set nodeTemp = objXMLDoc.createElement("id")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("id").Value)
		nodeSample.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("title")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("title").Value)
		nodeSample.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("description")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("description").Value)
		nodeSample.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("other")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("other").Value)
		nodeSample.appendChild nodeTemp

		objADORS.moveNext
	Wend

	objADORS.Close
	Set objADORS = Nothing

	'Save the created XML document
	objXMLDoc.Save "B:\sample2.xml"

	Main = DTSTaskExecResult_Success
End Function

XML Output:
<root>
<sample>
<id>1</id>
<title>Subject1</title>
<description>This is a test</description>
<other>Other info</other>
</sample>
<sample>
<id>2</id>
<title>Subject2</title>
<description>This is a test</description>
<other>Other info</other>
</sample>
<sample>
<id>3</id>
<title>Subject3</title>
<description>This is a test</description>
<other>Other info</other>
</sample>
</root>

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-30 : 23:15:47

quote:

Originally posted by kwilliams


First DB table
COLUMN NAME     DATA TYPE     LENGTH     ALLOW NULLS
id              int           4          no
title           char          100        yes
description     char          100        yes
...and so on

Second DB table
COLUMN NAME     DATA TYPE     LENGTH     ALLOW NULLS
id              int           4          no
title           char          100        yes
description     text          16         yes
other           char          200        yes
...and so on

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

I think the bolded column is where you are getting type mismatch error. In the first table the description is of datatype char of length 100 in second table its text.

Hope that helps

Thanks

Karunakaran

kwilliams

194 Posts

Posted - 2005-12-01 : 09:34:51

Hi karuna,

I really appreciate the quick response, but I was unclear with what my problem is. It's kind of hard to describe, but I'll do the best I can.

I have 2 DB tables that are receiving "Type Mismatch" errors through an ActiveX Script located in a DTS Package on SQL Server 2000. The first is receiving an error on a column that has the exact same properties as other columns that are not receiving this error. For instance:


COLUMN NAME    DATA TYPE    LENGTH    ALLOW NULLS
column1        int          4         no
column3        char         100       yes
column3        char         100       yes <--This column gets the error
column4        char         100       yes
...and so on

The second DB table is a bit more complex, but is having the same problem. Two of the columns are receiving the error. The first column that's getting the error has the exact same properties as other columns that aren't receiving the error. And the second column receiving the error has a "text" datatype, but when I change the datatype over to "char" to match the others, it still receives this error. For instance:


COLUMN NAME    DATA TYPE    LENGTH    ALLOW NULLS
column1        int          4         no
column3        char         100       yes
column3        char         100       yes <--This column gets the error
column4        text         100       yes <--This column gets the error
...and so on

If I was only getting an error on the second DB tables' column4 that contains a "text" datatype, I'd understand that the problem likely had to do with the datatype. But since the other 2 errors have to do with columns with the exact same properties and data as other columns that aren't receiving the errors, I'm really confused. If you or anyone can help me to solve this issue, it would be greatly appreciated. Thanks.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-12-01 : 11:40:05

quote:
Originally posted by kwilliams

XML Output:
<root>
<sample>
<id>1</id>
<title>Subject1</title>
<description>This is a test</description>
<other>Other info</other>
</sample>
<sample>
<id>2</id>
<title>Subject2</title>
<description>This is a test</description>
<other>Other info</other>
</sample>
<sample>
<id>3</id>
<title>Subject3</title>
<description>This is a test</description>
<other>Other info</other>
</sample>
</root>

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

This xml file is gets created as you want and still you are getting type mismatch error? Or I'am missing something?...

As far as I know, Type mismatch error comes when you are trying to import/export to datatypes which doesnt match or cannot do an implicit conversion.

Thanks

Karunakaran

kwilliams

194 Posts

Posted - 2005-12-01 : 12:34:08

quote:
This xml file is gets created as you want and still you are getting type mismatch error? Or I'am missing something?...

No, it's not creating an XML file because the ActiveX Script in the DTS Package is getting the error. When I try to execute the code, I receive a "Type Mismatch" error.

quote:
As far as I know, Type mismatch error comes when you are trying to import/export to datatypes which doesnt match or cannot do an implicit conversion.

I would understand that that's the problem if the datatypes were all "text" or "image", but I'm receiving errors for columns that have the identical properties as other columns with the same properties, including the datatype. So that's why I can't understand why I'm receiving this error.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-12-01 : 23:09:13

I tried the same with table structure and data you had posted


id int 4 no
title char 100 yes
description text 16 yes
other char 200 yes

You not working Activex code is also working fine for me...Instead of msxml4 I have used msxml6 since thats what I have.. So I dont see an issue in the code... If you can post the exact error message we can try out other options...Are you runing this in schedule or dts designer?

Karunakaran

kwilliams

194 Posts

Posted - 2006-01-03 : 14:55:12

Hello all,

Well, I've come up with a solution which also exposed some sort of bug within SQL Server 2000. If I changed one of the properties for those columns within the DB table itself, ran the DTS package, and changed the property back to what was failing before, and it then worked. I'm not sure why this would happen, but at least it's working. I guess I should report this on the MS site, but thanks for all of your help.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources