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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply