| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2009-02-16 : 07:51:34
|
| Hi I am building a app which will do data sync between 2 servers.i ma stuck with a problem where i have Structural difference i.e. column order diff or column missing.does some one have any SP or script which can fix such issues. or a way to fix it. please...========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-02-16 : 08:30:52
|
| If there is no result, no structural differenceSELECT * FROM SERVER_NAME.DB_NAME.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TABLE1'EXCEPT SELECT * FROM SERVER_NAME.DB_NAME.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TABLE2'MadhivananFailing to plan is Planning to fail |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-02-16 : 09:21:01
|
| Thanks madhivananthis will help me identify but how do i fix column order differences. by updating the syscolumns and set colorder value.. but SQL 2005 does not allow it.========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-16 : 09:22:22
|
quote:
Originally posted by khalik
Thanks madhivananthis will help me identify but how do i fix column order differences. by updating the syscolumns and set colorder value.. but SQL 2005 does not allow it.========================================Project Manager who loves to code.===============Ask to your self before u ask someone
fix order difference? do you mean ordinal position? why should you be concerned about ordinal positions. you can always specify columns in the order you want in select lists. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-02-16 : 09:27:02
|
| Hi Visakhi am reading the data from a txt file and inserting into actuall table using bulkinsert.if there is any structural diff like in server table colA is in 2 position and client same column is in 4th position. i need to make sure the local table also has the same structure as server========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-16 : 09:34:41
|
| the order of columns in txt should be same as order of columns in table. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-02-16 : 09:37:27
|
| they are not... and i need to fix it....i have around 300 client using my app and few of them have different structure... by this i want to make sure all have same modela nd my script work smothly.========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-02-16 : 09:45:14
|
| dump it into staging table first then you will have control over ordinal position... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-16 : 09:47:03
|
quote:
Originally posted by khalik
they are not... and i need to fix it....i have around 300 client using my app and few of them have different structure... by this i want to make sure all have same modela nd my script work smothly.========================================Project Manager who loves to code.===============Ask to your self before u ask someone
cant you use ssis so that you've more control over mapping of columns? |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-02-16 : 10:16:55
|
| i cannot install SSIS in all my client location. and most important i have very bad net connectivity. look at all these SSIS was not a best option. i wanted some thing which can continue from where it has stopped and no major installations.========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-02-17 : 04:22:58
|
| or is they a way to speciy the column list in bulk insert. i am loading the data from csv file. while reading can i give the format file. if so how?========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
bjoerns
Posting Yak Master
154 Posts |
Posted - 2009-02-17 : 05:18:09
|
| You can create a view selecting the columns from your original table in the order of the csv-file, then bulk insert into the view. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-02-17 : 05:33:52
|
| good idea, but i have to create many views as i am sync lot of table data. which i canot create many views========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-17 : 09:23:46
|
| why? dont you have permission to create views? |
|
|
|
|
Fix Structural Diff