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.
| Author |
Topic |
|
carterjd
Starting Member
4 Posts |
 Posted - 2010-07-07 : 05:14:40
|
| Hi - Urgent help needed please ! I am moving a database between MSSQL servers (but hosted by the same company (FASTHOSTS)and have spent days trying to export and import the site via various methods using different programs, asnd have finally found one that seems to work, keeping all the primary keys data intact (we used Data Pummp on the Maestro MS SQL Data Wizard software).The only problem is, when we export the dats, some of the the objects are changing from, for example the Datatype DATETIME to SMALLDATETIME. This is generating, fore example a fractional truncation error. Ive realised that by identifying and changing the type, this resolves the error Firstly, is there a way to prevent this happening? or will I simply have to manually specify on export what I want to change each to.Secondly, after changing the data to its original type, Im now receiving the following error when trying to add an item to the shopping cart, and have no idea whats causing it, as all seem to be set to "null" and not "not null" as the error message implies:Microsoft OLE DB Provider for ODBC Drivers error '80040e2f' [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'cartID', table 'interiorheaven.dbo.cart'; column does not allow nulls. INSERT fails. Im no database expert, and this last week has been a traumatic, now rather concerning learning curve, and urgently need to get this resolved so any help greatly appreciated.Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-07 : 05:31:14
|
Isn't it an option just to take a backup from the source database and then restore it to the destination database?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-07 : 12:08:45
|
| "Cannot insert the value NULL into column 'cartID', table 'interiorheaven.dbo.cart'; column does not allow nulls. INSERT fails. "At a guess I would say that the [cartID] column has lost its IDENTITY attribute in the transfer process - and is now just a plain INT, instead of automatically allocating an ID number.If you cannot do Backup/Restore as WebFred says then you need to Script the database on the Source server, pre-create it on the Destination server, and then move the data. That will keep column datatypes the same, but will also ensure you have all the other aspects of the database intact - indexes, defaults, constraints, FKeys, etc. etc. etc. which will all be lost if you just push the data across. |
|
|
|
carterjd
Starting Member
4 Posts |
Posted - 2010-07-07 : 12:27:02
|
| Many thanks for your responses so far. :)Yes, we've used the "data Pump" option which effectively copies and reproduces it on the other server. Using this option has produced the best results so far, with just these changes in data types happening. When we used the standard export and import options, the primary keys werent being cfreated and nothing worked at all.I was hoping the hosting company (FASTHOSTS) would just copy and paste the entire database across, but they are being really stubborn, refusing to do so so having to explore this route, but nothing seems to work. Theres an awful lot of elements to the Database to recreate manually, and having only dabbled with databases, wouldnt acrtualy know where to start with doing so, so dont really know what to do next.Is there any other software that should do this automatically that anyone knows of? Is this a common problem when moving databases around?Any suggestions most welcomed... |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-07 : 12:32:51
|
Your hoster can't "copy and paste" a database.Ask him for backing up the one database on the source server and restoring it on the other server.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
carterjd
Starting Member
4 Posts |
Posted - 2010-07-09 : 05:26:28
|
| Thanks Webfred, I wasnt sure if possible to do a copy/paste execrcise, but I note I can back up on one server using their backup facility, (but can only save to theire server so am unable to restore it elsewhere, but I shall ask/plead with them.Thanks |
|
|
|
carterjd
Starting Member
4 Posts |
Posted - 2010-08-03 : 04:30:28
|
| Update on this problem - now resolved. It seems that the data types were changing when exporting and importing data to a new database, and so as Webfred suggested, asking hoster to do a backup and restore (to the new server) was the solution. Data was unaffected and worked first time. Actually getting the hosting company to undertake this work was another matter and required many complaints for them to even look at it, but at least resolved now. Thanks for your help all. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-03 : 05:05:15
|
Glad you got it fixed."Actually getting the hosting company to undertake this work was another matter and required many complaints for them to even look at it"and there's the rub  I can't believe how helpful our hosting folk are. I tell them I have a gut feeling that our SQL box is "sluggish", in conversation about other (way more relevant! things) and they want chapter-and-verse immediately so they can try to diagnose a problem.No idea what we pay, and whether its expensive or not, but they are certainly excellent. It was a key consideration when we moved a year or so back - the cost of hosting is important, but actually its quite small compared to the "value" of the sites, so no point cutting off our noses to spite our faces |
|
|
|
|
|
|
|
|
URGENT - Export/Import data keeps changing