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 |
|
TLRtone
Starting Member
3 Posts |
 Posted - 2004-11-27 : 20:20:12
|
I have been around the web for about a week trying to sort this one out so any help will be much appreciated! History: I had our club site on a shared server with a limited 200mB SQLDB size. We have outgrown this and have now moved to our own server (or are in the process of moving!). What I have done so far: 1. I created a new DB in MSDE on the new server ready for the transfer. (called 'soc_forums')2. I organised a backup of the SQLDB from our hosts and FTP'd it up to the new server. (The Database name was db86488568)3. I Restored the Database into MSDE on the new server. 4. I used sp_changeobjectowner to set owner back to dbo (from dbo86488568) for all the tables in the database. 5. I used a script I found on here (thanks Tara!) to rename the Logical names from 'db86488568' to 'soc_forums' for the data file and the log file. Where I am today: I now have the forum running on the site with all the data migrated. However, when I go to add a post to the forum (ie write to the new database) i get the following: Microsoft OLE DB Provider for SQL Server error '80004005' Invalid object name 'db86488568.dbo86488568.tblThread'. The connection string I am using is:"Provider=SQLOLEDB;Server=P15171753;Data Source=(local);Initial Catalog=soc_forums;User ID=sa;Password=*********;"I am a newbie with SQL setup etc so what am I missing?I have checked and double checked the MSDE tables and they don't reference the old database names or users anywhere (I deleted the user dbo86488568 when I changed the object owners). So where does my ASP pages get the 'db86488568.dbo86488568.tblThread' from? HELP???  |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-27 : 21:08:23
|
| Looks like something (probably the app) has the dbname and owner set to db86488568.dbo86488568. It won't be anythig to do with the connection string but something is fully qualifying the object.tblThread sounds like the table it is trying to insert the new post into so it's unlikely to be in a trigger or anything like that.Could be held in a table somewhere maybe.It's odd if it can retrieve the data but not post though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
TLRtone
Starting Member
3 Posts |
Posted - 2004-11-28 : 03:47:29
|
| thanks nr, it's got me too! I have checked the coding but cannot find it anywhereI can retieve data correctly but just not write to the DB. Nothing in the coding fully qualifies the oject so I am confused. It works with a new clean database but just not the restored one. I do not really want to start with a new database and lose 100,000 posts! I'll just have to delve deeper! Thanks for the pointers though nr. |
|
|
|
TLRtone
Starting Member
3 Posts |
Posted - 2004-11-28 : 04:12:05
|
| Just a quick thought - as I can set up a blank database with the correct table structure and it works, can I move the data in the tables easily from one database to the other? thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-28 : 04:37:29
|
| "can I move the data in the tables easily from one database to the other"Use "Import" on the RightClick of the database in Enterprise Manager (it might be under "All tasks")Sounds like something has got "db86488568.dbo86488568" hard wired in it - either in the Application or the Database.Do a global search on your application. For the database you might find it by searching:SELECT * FROM syscomments WHERE ctext LIKE '%db86488568.dbo86488568%'and possiblyselect nameFROM sysobjects OWHERE uid <> 1to look for anything which is NOT owned by dbo.Kristen |
|
|
|
|
|
|
From Hosted SQL to new Server with MSDE