June 8, 2011 at 6:40 am
Hi all,
I have a specific task to copy a series of databases nightly from a SQL 2005 instance to a 2000 (not my choice not my decision and nothing I can do about it!)
I have an SSIS package that does the job - it uses the "Transfer SQL Server Objects" task in a loop to copy each database - it even irons out the many 'idisyncrasies' (aka bugs) that this task has (and don't get me started on them).
However one of the databases uses many varchar(max) fields and of course 2000 will not recognise these and the package errors ("Either the object or one of its properties is not supported on the target server version."). Fair enough - but what can I do about it!
The problem is somewhat complicated in that the source DB does not have a fixed set of tables - the application using the DB creates new tables on the fly. Therefore my initial thought of using, saving and then calling a package created via the Import wizard is scuppard.
I have a niggling thought that I am missing something obvious here... but not sure what! I am trying to avoid some method of scripting the table structures via a .NET "Script task" and then converting the varchar(max) to text before running the script on the destination DB and looping through some dataflow task...
Any ideas much appreciated!
June 8, 2011 at 6:50 am
You'll have to convert varchar(max) to text to get it into SQL 2000. A data conversion point in you data flow should be able to do that for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2011 at 7:04 am
Perhaps it is what it is - I am not shooting the messenger! - but the core task of copying a DB to another server (albeit 2005 -- > 2000 and accepting the restricted environment I am working in) really shouldn't be this tricky IMO.
Given that i want to 'simply' maintain the structure and data of existing tables the process seems to be made for a built in task.
Instead I seem to have to get a hold of the table definations in the source manually (via .net Scripting task?), recreate them in the destination and then, one by one, loop through using a data flow to transfer the data (converting varchar(max) to text).
June 9, 2011 at 7:15 am
Backwards compatibility has limitations, especially on new data types.
What would you do if you had to convert CLR data type columns to SQL 2000? Varbinary maybe? Still wouldn't give you anything useful in many cases. You could do a conversion to varchar for HierarchyID and you'd at least get something you could query, though not efficiently. But what about the geo types?
There are advantages, sometimes significant ones, to new features, but there are pains associated with them too. This is one of them, and there's no "that was easy" solution to some of them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2011 at 7:26 am
dwill (6/8/2011)
Perhaps it is what it is - I am not shooting the messenger! - but the core task of copying a DB to another server (albeit 2005 -- > 2000 and accepting the restricted environment I am working in) really shouldn't be this tricky IMO.
So when should it be tricky? Any time you data from one source to a difference source (2005 != 2000) you have to do a bit of work.
Tell me, when you go 2008 to 2005 and you have no where to put date or time fields, what would you do?
Instead I seem to have to get a hold of the table definations in the source manually (via .net Scripting task?), recreate them in the destination and then, one by one, loop through using a data flow to transfer the data (converting varchar(max) to text).
Why you want to do any of that?
Select Cast(MyvarcharMaxCol as Text) as MyvarcharMaxCol from myTable
That'll do it at source for you.
It's not something to get all hot and bothered about - you can't expect everything to remain backwards compatible... (or forwards in the case of 2000...) 😉
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply