|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Copy Database Fails in SSMS - Invalid Object Namea database. Instead of generating a database script, I just tried to copy it, using the wizard in SSMS. It fails in the same way my generated script did earlier. I have discovered that if I send the log output to a text file instead of the windows event log I get much more detailed information (I wish it would say that on the screen, given that event log is the default; it would saved me hours). I also realise now that Copy Database just scripts the database and then executes it, which is why I am getting the same problem here as I was when I scripted the database separately and then ran the script. The output log says 'invalid object name' when creating a view in the script, and it picks out an object - another view, for example - that the first view is dependent upon. Initially, it would complain about views named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another view. So, I naturally thought that it didn't like the 'dbo.' prefix. However, once I had removed all the prefixes, it started to complain about MyView1 again, so that obviously wasn't the real problem. The strange thing is that this used to work. I have copied this database before (perhaps a month ago) without problem, but I can't begin to think what might have changed to cause it to fall over now. Could it be something to do with permissions, or context? I connect using the database owner's login, but I have tried others (all of which are in the sysadmin group), but to no avail. Can anyone suggest what could be going on, or how I can get closer to the real cause? TIA Charles Hi
Could it be that you try to create an object (view) that contains another object (another view) that does not exist yet? Show quote "Charles Law" <bl***@nowhere.com> wrote in message news:eYgkq4MCIHA.2004@TK2MSFTNGP06.phx.gbl... > This is a carry on from my earlier post about generating scripts to > recreate a database. > > Instead of generating a database script, I just tried to copy it, using > the wizard in SSMS. It fails in the same way my generated script did > earlier. > > I have discovered that if I send the log output to a text file instead of > the windows event log I get much more detailed information (I wish it > would say that on the screen, given that event log is the default; it > would saved me hours). I also realise now that Copy Database just scripts > the database and then executes it, which is why I am getting the same > problem here as I was when I scripted the database separately and then ran > the script. > > The output log says 'invalid object name' when creating a view in the > script, and it picks out an object - another view, for example - that the > first view is dependent upon. Initially, it would complain about views > named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another > view. So, I naturally thought that it didn't like the 'dbo.' prefix. > However, once I had removed all the prefixes, it started to complain about > MyView1 again, so that obviously wasn't the real problem. > > The strange thing is that this used to work. I have copied this database > before (perhaps a month ago) without problem, but I can't begin to think > what might have changed to cause it to fall over now. Could it be > something to do with permissions, or context? I connect using the database > owner's login, but I have tried others (all of which are in the sysadmin > group), but to no avail. > > Can anyone suggest what could be going on, or how I can get closer to the > real cause? > > TIA > > Charles > > Hi Uri
Well that was what I thought originally, but now I am not so sure. It will frequently be the case that a view uses another view or another object. Therefore, the Copy Database operation in SSMS must be able to create objects in the correct order. Either that, or it mustn't care if an object does not exist yet. Either way, Copy Database would be useless if it couldn't work out how to copy a database without hitting this problem, wouldn't you say? Charles Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:O4nelCNCIHA.5328@TK2MSFTNGP05.phx.gbl... > Hi > > Could it be that you try to create an object (view) that contains another > object (another view) that does not exist yet? > > > > "Charles Law" <bl***@nowhere.com> wrote in message > news:eYgkq4MCIHA.2004@TK2MSFTNGP06.phx.gbl... >> This is a carry on from my earlier post about generating scripts to >> recreate a database. >> >> Instead of generating a database script, I just tried to copy it, using >> the wizard in SSMS. It fails in the same way my generated script did >> earlier. >> >> I have discovered that if I send the log output to a text file instead of >> the windows event log I get much more detailed information (I wish it >> would say that on the screen, given that event log is the default; it >> would saved me hours). I also realise now that Copy Database just scripts >> the database and then executes it, which is why I am getting the same >> problem here as I was when I scripted the database separately and then >> ran the script. >> >> The output log says 'invalid object name' when creating a view in the >> script, and it picks out an object - another view, for example - that the >> first view is dependent upon. Initially, it would complain about views >> named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another >> view. So, I naturally thought that it didn't like the 'dbo.' prefix. >> However, once I had removed all the prefixes, it started to complain >> about MyView1 again, so that obviously wasn't the real problem. >> >> The strange thing is that this used to work. I have copied this database >> before (perhaps a month ago) without problem, but I can't begin to think >> what might have changed to cause it to fall over now. Could it be >> something to do with permissions, or context? I connect using the >> database owner's login, but I have tried others (all of which are in the >> sysadmin group), but to no avail. >> >> Can anyone suggest what could be going on, or how I can get closer to the >> real cause? >> >> TIA >> >> Charles >> >> > > Charles Law (bl***@nowhere.com) writes:
Show quote > The output log says 'invalid object name' when creating a view in the The tools can only do as good as they can given the input. And in the input> script, and it picks out an object - another view, for example - that > the first view is dependent upon. Initially, it would complain about > views named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on > another view. So, I naturally thought that it didn't like the 'dbo.' > prefix. However, once I had removed all the prefixes, it started to > complain about MyView1 again, so that obviously wasn't the real > problem. > > The strange thing is that this used to work. I have copied this database > before (perhaps a month ago) without problem, but I can't begin to think > what might have changed to cause it to fall over now. Could it be > something to do with permissions, or context? I connect using the > database owner's login, but I have tried others (all of which are in the > sysadmin group), but to no avail. in this case is the view sys.sql_dependencies, which by its design cannot be wholly reliable. To wit, references are stored by object_id. So guess what happens if you try: create view a as select n = 12 go create view b as select n from a go exec sp_depends b go drop view a go create view a as select n = 123 go exec sp_depends b go drop view a, b By dropping a and recreating it, the information that b depends on a is lost. One way out is to create all views WITH SCHEMABINDING. Had b in the example above been defined WITH SCHEMABINDING, it would not be possible to drop or alter a without first dropping b. Then again, this measure may be to harsh for you. If you are on SQl 2005 SP2, there is a way out for you: you can use sp_refreshsqlmodule to refresh the dependency information for a view or a procedure. You would have to run it for all your views before you script. Another option is to simply maintain a build-script the loads the views in the right order. Note that this script would not be an SQL script - it would be a script in VB-script, Perl, C# or even a BAT file that reads the files with the view definition and load them to SQL Server. The files would of course be retrieved from the version-control system. In SQL 2008, currently in beta, they have a new style for reference information, so that information is held by name and not by object id. Thereby dependencies are not lost when an object id is dropped. I have not tested whether the tools actually make use of this features. Personally, I find that the new feature has several shortcomings compared to the old reference information, particularly with regards to information on column level. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics