Copy Database Fails in SSMS - Invalid Object Name

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
CharlesHi
Could it be that you try to create an object (view) that contains another
object (another view) that does not exist yet?
"Charles Law" <blank@.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
"Uri Dimant" <urid@.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" <blank@.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 (blank@.nowhere.com) writes:
> 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.
The tools can only do as good as they can given the input. And in the input
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, esquel@.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

Copy database fails because of invalid object name in a view?

I'm getting the below error when attempting to copy a database. The view works just fine but the package keeps giving me the below error:

07 3:26:37 PM,4/11/2007 3:26:37 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "
CREATE VIEW [app].[vwbaseTransfer]
AS
Select a.app_id, a.app_year, u.first_name, u.middle_name, u.last_name, app_phoneAM, email, CONVERT(varchar(10), app_dob, 101) as app_dob, app_citizen,
CASE WHEN app_untilDate > GetDate() Then app_addr1 ELSE app_pAddr1 END AS app_addr1,
CASE WHEN app_untilDate > GetDate() Then app_addr2 ELSE app_pAddr2 END AS app_addr2,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr3,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr4,
CASE WHEN app_untilDate > GetDate() Then app_city ELSE app_pcity END AS app_city,
CASE WHEN app_untilDate > GetDate() Then app_state ELSE app_pstate END AS app_state,
CASE WHEN app_untilDate > GetDate() Then app_zipcode ELSE app_pzipcode END AS app_zipcode,
app_sex, app_racecode, app_accept_letter, app_acceptDate, app_orientation_date, app_ssn
FROM applicant a inner join uop_user u on u.id = a.app_id
where app_accept_letter is not null and app_acceptDate is not null

" failed with the following error: "Invalid object name 'applicant'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

Just out of curiosity, is applicant in a different schema than "app"?|||Same schema|||Maybe it is trying to copy the view before the applicant table?|||

jwelch wrote:

Maybe it is trying to copy the view before the applicant table?

Ooooo, I like that thought!|||That's not good. Anyone tried this before?|||Should I assume that this will not work and manually copy databases?|||

Bogey1 wrote:

Should I assume that this will not work and manually copy databases?

What task are you using?

Also, isn't there a better method of "copying" databases? (That is, a backup/restore process.) Are you going for just the schema, or data as well?|||

Bogey1 wrote:

That's not good. Anyone tried this before?

If you feel like wading through it, you could check your sys.sql_dependencies view to see if it has the dependency between the view and the table. Not sure if that is used to determine the order when db objects are copied, but it might be worth a shot.

|||

I'm using the copy database task. If I do a backup and restore then I manually must reset the logins, correct. I was hoping that a simple copy database would take care of this.

thanks.

Copy Database (SQL Management Object method)

I tried to copy a db instance from one SQL 2005 installation to another. The transfer/copy wizard in management studio showed nothing - a blank - in the "Destination database files" section. That struck me as bad, but the wizard would not let me add any files. Then when I tried to execute the transfer/copy, I got this error message:

Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->The PRIMARY filegroup must have at least one file.

I suppose I would not have had that error message, if the wizard allowed me to add a filegroup. What is causing this?

Are you copying or moving? Do you have to use SMO? If not, just make a backup of the database and restore it to the other server. If you are moving the database, detach the files and reattach them on the other server. Hope this helps.
Tim|||Run SP_HELPFILEGROUP on source server to see what are the contents displayed, as explained you can either take help of backup/restore of detach/attach method.

copy an object in the procedure

Hi.
Is there any simple way to copy an object ( table,view, procedure) and write
it with another name or different owner. A new object should have the same
CONSTRAINTS, IDENTITY column etc.. For tables I've tried to create a new
table and later add columns or change data type with ALTER, but there are to
many exceptions for ALTER command and it does not work properly. I need to
synchronize a structures of one table with another or create a copy if it
does not exist yet in sysobjects and ...all those things must be done
automatically in the stored procedure:)
Thanks for help.
Toosietoosie
I'd backup the database a nd restore with a new name
The next step is changing an owner of the objects
--This script changes all objects that don't have 'dbo' to 'dbo' owner.
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
After the objects have changed you can run a cusros through to alter
their names
SELECT 'ALTER TABLE '+t1.name+ ' new name'
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
"toosie" <toosie@.wp.pl> wrote in message
news:d2tjor$1pbi$1@.news2.ipartners.pl...
> Hi.
> Is there any simple way to copy an object ( table,view, procedure) and
write
> it with another name or different owner. A new object should have the
same
> CONSTRAINTS, IDENTITY column etc.. For tables I've tried to create a new
> table and later add columns or change data type with ALTER, but there are
to
> many exceptions for ALTER command and it does not work properly. I need to
> synchronize a structures of one table with another or create a copy if it
> does not exist yet in sysobjects and ...all those things must be done
> automatically in the stored procedure:)
> Thanks for help.
> Toosie
>

Cool Query Analyzer Trick

In Query Analyzer you can save a lot of time by using this trick instead of
typing all the column names of a table
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window
I work with people who are certified, have 10 years experience and none of
them knew this trick
Also if you know of any other not well known QA tricks let me know and I
will update my blog
http://sqlservercode.blogspot.com/
Here are some other nice shortcuts (at least in SQL2k and below)
Ctrl-R
Ctrl-E
F6
Highlight some text and hit Ctrl-U or Ctrl-L
Highlight some text and hit Shift+Ctrl+C
now try Shift+Ctrl+R
Keith
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||On Thu, 22 Sep 2005 13:28:03 -0500, Keith Kratochvil wrote:

>Here are some other nice shortcuts (at least in SQL2k and below)
>Ctrl-R
>Ctrl-E
>F6
>Highlight some text and hit Ctrl-U or Ctrl-L
Hi Keith,
I assume that you meeant Ctrl-Shift-U and Ctrl-Shift-L?
(Ctrl-U is change database and Ctrl-L is display estimated execution
plan).

>Highlight some text and hit Shift+Ctrl+C
>now try Shift+Ctrl+R
And to get to know all keyboard shortcuts, study the menu's. All
shortcuts are listed in the menu's.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You can also right click on the table and generate a Select, insert , Update
or Delete statement as well.
Andrew J. Kelly SQL MVP
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||As a follow on to Andrews tip, after generating an insert, delete or
update statement, press Ctrl-Shift-M...
Jacko