I got this error message when I tried to DTS a table from a server with 2008 SQL Enterprise SP1 (10.0.2531) over to a server with 2005 SQL Standard 9.0.4294. Looks like the 2005 server cannot create the table because of the varcharmax datatype. Error 0xc0021210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].Table1" failed with the following error: "Column, parameter, or variable #5: Cannot find data type varcharmax". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.AMF

Check the compatibility level of the target database, it seems to me since the Varchar(max) was introduced in SQL Server 2005 this is what prevents the package from running successfully.Arthur My Blog

Try Arthur's idea and see if you can change compatibility to 2005. Or you can use text instead of varchar(max).Russel Loski, MCT

Where do I change the compatibility level? I did the DTS from SSMS, right clicked on Database-> Tasks-> Export DataAMF

Where do I change the compatibility level? I did the DTS from SSMS, right clicked on Database-> Tasks-> Export DataAMF

Where do I change the compatibility level? I did the DTS from SSMS, right clicked on Database-> Tasks-> Export Data In SSMS (Management studio). You would check and change the compatability level in the properties of the database (options).Russel Loski, MCT

Where do I change the compatibility level? I did the DTS from SSMS, right clicked on Database-> Tasks-> Export Data In SSMS (Management studio). You would check and change the compatability level in the properties of the database (options).Russel Loski, MCT

Do not change (it might had been set to comply with an application) it just check it. Use sp_dbcmptlevel 'DatabaseName' Levels are: 60 = SQL Server 6.0 65 = SQL Server 6.5 70 = SQL Server 7.0 80 = SQL Server 2000 90 = SQL Server 2005 Then, if the level is below 90 then use text as Russ suggested.Arthur My Blog

Do not change (it might had been set to comply with an application) it just check it. Use sp_dbcmptlevel 'DatabaseName' Levels are: 60 = SQL Server 6.0 65 = SQL Server 6.5 70 = SQL Server 7.0 80 = SQL Server 2000 90 = SQL Server 2005 Then, if the level is below 90 then use text as Russ suggested.Arthur My Blog

Do not change (it might had been set to comply with an application) it just check it. Use sp_dbcmptlevel 'DatabaseName' Levels are: 60 = SQL Server 6.0 65 = SQL Server 6.5 70 = SQL Server 7.0 80 = SQL Server 2000 90 = SQL Server 2005 Then, if the level is below 90 then use text as Russ suggested.Arthur My Blog

The compatibility level for the 2008 SQL Enterprise server is already SQL Server 2005 (90). Does the text datatype have the same storage capacity as the varcharmax field? I believe our developers use varcharmax field due to the amount of data we are storing in these varcharmax fields.AMF

The compatibility level for the 2008 SQL Enterprise server is already SQL Server 2005 (90). Does the text datatype have the same storage capacity as the varcharmax field? I believe our developers use varcharmax field due to the amount of data we are storing in these varcharmax fields.AMF

The compatibility level for the 2008 SQL Enterprise server is already SQL Server 2005 (90). Does the text datatype have the same storage capacity as the varcharmax field? I believe our developers use varcharmax field due to the amount of data we are storing in these varcharmax fields.AMF

Please check the target (SQL Server 2005), not the source.Arthur My Blog

Please check the target (SQL Server 2005), not the source.Arthur My Blog

Please check the target (SQL Server 2005), not the source.Arthur My Blog

The compatibility level for the source is also SQL Server 2005 (90).AMF

The compatibility level for the source is also SQL Server 2005 (90).AMF

The compatibility level for the source is also SQL Server 2005 (90).AMF

Wait, just realized you create a table using SSIS package?Arthur My Blog

Yes, this is a new target table, which should not have any issue since this is a brand new table.AMF

Hmm, I would do that using the Data Export / Import Wizard and save as package, all-in-all this is not typical for SSIS to create tables (my DBA soul cringes), another approach is to use this technique: SELECT * INTO NewTable FROM existingdb.dbo.MyOldTableArthur My Blog

I normally create a new temp table before moving the data to the actual table. Creating a new temp table is less hassle than transferring data over to the final table. However, due to the existence of the varcharmax datatype, I am now experiencing an issue in creating this new temp table. This never happened before when moving data from 2005 SQL to 2005 SQL.AMF

Got you, thanks, now I see what is going on, I frankly do not see why you would have this issue then, can you try running the create SQL statement in SSMS as it would be submitted from BIDS and get back to us on what happened?Arthur My Blog

I'm not sure how you can see the actual create statement submitted by BIDS when you do everything thru DTS, but I scripted the source table (2008 SQL Enterprise) and it ran fine when I ran it in the target db (2005 SQL Standard), outside of SSIS. I guess the answer is yes to your question, it ran fine.AMF

Not in BIDS, use SSMS - create new query window, copy and paste the create SQL into it and F5.Arthur My Blog

Yes it did run fine thru SSMSAMF

Are you sure you connected to the target database then executed the CREATE TABLE1 SQL? Arthur My Blog

Yes, I've been doing this many years, I know what I'm doing AMF

Instead of exporting data from 2008 to 2005 try to import to 2005 from 2008.( which i mean to use import export wizard of 2005 ) This can be due to varcharmax datatype used in SMO . which is believe is enhanced in 2008 not in 2005 . Just give a try to check if it works !!Cheers , Varun

Cool, that works. Thanks Varun!AMF