problem in copying a table into another one

I am having problems to copy a table into another one

SELECT * INTO UserCopy FROM User WHERE User.ID IN (SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser)

with SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser I get : 1,2,3,10

now if I look the result in my table Usercopy I get the values for : 1,2,3,9,10

what can be the problem ? where does the 9 comes from

thank youwas the table empty when you started|||userCopy of course is empty because it is just created

user is full and category too
I want to extract datas from user depending on an ID list in category

thank you for helping|||I created table to test data and as I first though first
your logic is correct

If you return 1,2,3,10
that is what should be used to create your table.

Try to hardcode 1,2,3,10 and see what that returns.
or
just run the select instead of the into and what is returned.

Could you be looking at usercopy created by another owner.
Could you be selecting from a user a category table from another owner.|||ok I try ! and i come back :-)

thank you|||read the sticky at the top of the forum and posts what it asks you to give...

problem creating warm standby on 6.5

I have a 6.5 server with a production db on it. I created
a copy of that db on another server. I can apply the
backup dump from the prod server to the standby server
fine, but when I go to apply the logs from the prod
server, I get errors saying something like "log out of
sequence. dump was created at 11:00 pm, log at 10:00 am"
What's wrong?Can you should use the DUMP and LOAD commands that you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"whome" <anonymous@.discussions.microsoft.com> wrote in message
news:6fa701c40208$5d83f720$a401280a@.phx.gbl...
> I have a 6.5 server with a production db on it. I created
> a copy of that db on another server. I can apply the
> backup dump from the prod server to the standby server
> fine, but when I go to apply the logs from the prod
> server, I get errors saying something like "log out of
> sequence. dump was created at 11:00 pm, log at 10:00 am"
> What's wrong?|||I'm using the backup/restore tool in Sql Enterprise
Manager.
>--Original Message--
>Can you should use the DUMP and LOAD commands that you
are using?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"whome" <anonymous@.discussions.microsoft.com> wrote in
message
>news:6fa701c40208$5d83f720$a401280a@.phx.gbl...
>> I have a 6.5 server with a production db on it. I
created
>> a copy of that db on another server. I can apply the
>> backup dump from the prod server to the standby server
>> fine, but when I go to apply the logs from the prod
>> server, I get errors saying something like "log out of
>> sequence. dump was created at 11:00 pm, log at 10:00 am"
>> What's wrong?
>
>.
>|||It is very difficult to troubleshoot these things if we're don't know what
is going on. I recommend that you use ISQLw and execute DUMP and LOAD
command instead. Then you can post these commands here.
Or you could run SQLTrace and catch the SQL commands issued by Enterprise
Manager as you navigate the dialogs there...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message
news:79bf01c402bb$7eacf2e0$a401280a@.phx.gbl...
> I'm using the backup/restore tool in Sql Enterprise
> Manager.
> >--Original Message--
> >Can you should use the DUMP and LOAD commands that you
> are using?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >
> >
> >"whome" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:6fa701c40208$5d83f720$a401280a@.phx.gbl...
> >> I have a 6.5 server with a production db on it. I
> created
> >> a copy of that db on another server. I can apply the
> >> backup dump from the prod server to the standby server
> >> fine, but when I go to apply the logs from the prod
> >> server, I get errors saying something like "log out of
> >> sequence. dump was created at 11:00 pm, log at 10:00 am"
> >> What's wrong?
> >
> >
> >.
> >|||will do. thanks
>--Original Message--
>It is very difficult to troubleshoot these things if
we're don't know what
>is going on. I recommend that you use ISQLw and execute
DUMP and LOAD
>command instead. Then you can post these commands here.
>Or you could run SQLTrace and catch the SQL commands
issued by Enterprise
>Manager as you navigate the dialogs there...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:79bf01c402bb$7eacf2e0$a401280a@.phx.gbl...
>> I'm using the backup/restore tool in Sql Enterprise
>> Manager.
>> >--Original Message--
>> >Can you should use the DUMP and LOAD commands that you
>> are using?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >
>> >
>> >"whome" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:6fa701c40208$5d83f720$a401280a@.phx.gbl...
>> >> I have a 6.5 server with a production db on it. I
>> created
>> >> a copy of that db on another server. I can apply the
>> >> backup dump from the prod server to the standby
server
>> >> fine, but when I go to apply the logs from the prod
>> >> server, I get errors saying something like "log out
of
>> >> sequence. dump was created at 11:00 pm, log at 10:00
am"
>> >> What's wrong?
>> >
>> >
>> >.
>> >
>
>.
>

problem creating warm standby on 6.5

I have a 6.5 server with a production db on it. I created
a copy of that db on another server. I can apply the
backup dump from the prod server to the standby server
fine, but when I go to apply the logs from the prod
server, I get errors saying something like "log out of
sequence. dump was created at 11:00 pm, log at 10:00 am"
What's wrong?Can you should use the DUMP and LOAD commands that you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"whome" <anonymous@.discussions.microsoft.com> wrote in message
news:6fa701c40208$5d83f720$a401280a@.phx.gbl...
> I have a 6.5 server with a production db on it. I created
> a copy of that db on another server. I can apply the
> backup dump from the prod server to the standby server
> fine, but when I go to apply the logs from the prod
> server, I get errors saying something like "log out of
> sequence. dump was created at 11:00 pm, log at 10:00 am"
> What's wrong?|||I'm using the backup/restore tool in Sql Enterprise
Manager.
>--Original Message--
>Can you should use the DUMP and LOAD commands that you
are using?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"whome" <anonymous@.discussions.microsoft.com> wrote in
message
>news:6fa701c40208$5d83f720$a401280a@.phx.gbl...
created
>
>.
>|||It is very difficult to troubleshoot these things if we're don't know what
is going on. I recommend that you use ISQLw and execute DUMP and LOAD
command instead. Then you can post these commands here.
Or you could run SQLTrace and catch the SQL commands issued by Enterprise
Manager as you navigate the dialogs there...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message
news:79bf01c402bb$7eacf2e0$a401280a@.phx.gbl...
> I'm using the backup/restore tool in Sql Enterprise
> Manager.
> are using?
> message
> created|||will do. thanks
>--Original Message--
>It is very difficult to troubleshoot these things if
we're don't know what
>is going on. I recommend that you use ISQLw and execute
DUMP and LOAD
>command instead. Then you can post these commands here.
>Or you could run SQLTrace and catch the SQL commands
issued by Enterprise
>Manager as you navigate the dialogs there...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:79bf01c402bb$7eacf2e0$a401280a@.phx.gbl...
server
of
am"
>
>.
>

Problem copying tables to another server with Primary and Foreign Key constrains

How can I copy a table with a Primary Key to another server's table of
the same name without receiving the following error: (Error at
Destination for Row number 489. Errors encountered so far in this
task:1.The statement has been terminated. Violation of PRIMARY KEY
constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
'INDIVIDUALS'.)
I want to copy about 20 interrelated cascaded tables with Primary and
Foreign Keys to another server's tables with the identical structure
and not receive the above error. I suggested dropping the tables and
recreating them, but my co-worker feel that this would not work because
of the interrelationship of the tables.
I tried to manually delete a table that is part of this group and
received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
it is referenced by a FOREIGN KEY constraint.
THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
REPLICATED.
Any Suggestion?Some odd questions:
What field is the primary key? Is it the replication id? Is it an Identity
Field ? Some other field?
Instead of deleteing the target table you could try truncating it.
Your error says you are trying to load a duplicate key. The only answers are
Delete, Truncate, Drop or Remove Constraint.
What is the problem you are trying to solve? If it is just having a copy of
the database 'somewhere else', you could consider a backup and restore cycle.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"war_wheelan@.yahoo.com" wrote:
> How can I copy a table with a Primary Key to another server's table of
> the same name without receiving the following error: (Error at
> Destination for Row number 489. Errors encountered so far in this
> task:1.The statement has been terminated. Violation of PRIMARY KEY
> constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
> 'INDIVIDUALS'.)
> I want to copy about 20 interrelated cascaded tables with Primary and
> Foreign Keys to another server's tables with the identical structure
> and not receive the above error. I suggested dropping the tables and
> recreating them, but my co-worker feel that this would not work because
> of the interrelationship of the tables.
> I tried to manually delete a table that is part of this group and
> received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
> it is referenced by a FOREIGN KEY constraint.
> THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
> WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
> REPLICATED.
> Any Suggestion?
>|||What field is the primary key? Is it the replication id? Is it an
Identity Field ? Some other field? THE PRIMARY KEY VARIES FROM TABLE
TO TABLE.
Instead of deleteing the target table you could try truncating it. I
AM NOT A T-SQL PROGRAMMER SO IN NON PROGRAMMING TERMS - CAN I DROP THE
KEYS ON THE DESTINATION TABLE(S) I.E. PRIMARY AND FOREIGN KEYS THEN
COPY THE NEW TABLES TO THE DESTINATION? WOULD THIS RE-ESTABLISH THE
ORIGINAL PRIMARY AND FOREIGN KEYS WITHOUT CORRUPTING THE
DATABASE/TABLES AND THEIR RELATIONSHIPS?
Your error says you are trying to load a duplicate key. The only
answers are Delete, Truncate, Drop or Remove Constraint. SAME QUESTION
AS ABOVE, BUT PERHAPS THE REMOVE CONTRAINT OPTION WOULD WORK. NEW
QUESTION: WOULD SQL LET ME REMOVE A CONTRAINT WITH INTER-RELATIONSHIPS
TO OTHER TABLES OR WOULD IT FAIL BECAUSE OF THE INTER-RELATIONSHIPS.
What is the problem you are trying to solve? If it is just having a
copy of the database 'somewhere else', you could consider a backup and
restore cycle. THE PROLEM IS THAT THESE TABLES WOULD BE REPLICATE IF
REPLICATION WERE WORKING. REPLICATION HAS NOT BEEN WORKING FOR ABOUT A
MONTH SO THE PRIMARY AND SECONDARY DATABASES ARE OUT OF SYNC. IF THE
PRIMARY SERVER GOES DOWN WE WOULD BE MISSING A MONTHS WORTH OF CHANGES.
ERGO BACK TO MY ORIGINAL QUESTION (I want to copy about 20
interrelated cascaded tables with Primary and Foreign Keys to another
server's tables with the identical structure and not receive the above
error.
Thanks for you response. I am trying to get suggestions for my PART
TIME T-SQL programmer.

Problem Copying Table

I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem Copying Table

I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?
berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem Copying Table

I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem copying DTS jobs with Database Copy Wizard

I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriB
I dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB

Problem copying DTS jobs with Database Copy Wizard

I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB

Problem copying DTS jobs with Database Copy Wizard

I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB

problem copying database with Microsoft SQl Server Management Studio

I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).

I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:

Server user 'username' is not a valid user in database '123foo'

(123foo is the first database of all those on the server)

I need have it look only at my database which I have permissions for to copy it. How do I do this?

I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?

Thanks for any help!

I think you can use the import export option and specify them with primary keys and identities also other option is you can try scripting the tables and run the script in SSMS........

|||

Thats is some sort of Bug in SSMS. You will have to assign a valid database owner top the database as the existing database owner is no server principal on your computer, this can be either based on the fact that you deleted the server principal or as the most common reason, you restored a database backup which is from another computer or a certain point in time where the current database owner did not exist on the computer. To fix that use sp_changedbowner (described int he BOL) to change the db_owner.

Jens K. Suessmeyer

http://www.sqlserver2005.de

problem copying database with Microsoft SQl Server Management Studio

I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).

I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:

Server user 'username' is not a valid user in database '123foo'

(123foo is the first database of all those on the server)

I need have it look only at my database which I have permissions for to copy it. How do I do this?

I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?

Thanks for any help!

I think you can use the import export option and specify them with primary keys and identities also other option is you can try scripting the tables and run the script in SSMS........

|||

Thats is some sort of Bug in SSMS. You will have to assign a valid database owner top the database as the existing database owner is no server principal on your computer, this can be either based on the fact that you deleted the server principal or as the most common reason, you restored a database backup which is from another computer or a certain point in time where the current database owner did not exist on the computer. To fix that use sp_changedbowner (described int he BOL) to change the db_owner.

Jens K. Suessmeyer

http://www.sqlserver2005.de

problem copying database to new server!

I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
Paul G
Software engineer.
Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.
|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
Paul G
Software engineer.
"Jens" wrote:

> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>

problem copying database to new server!

I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn´t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>

problem copying database to new server!

I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:

> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>

Problem copying data from column in same table

I have a column that I would like to copy data to. The column definitions a
re exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my co
lumn remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it affec
ted.
Thanks, IrisYour SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||That's correct, I'd rather update. I'll have to start back over since my te
st table now has all those null values in it like Jim mentioned. Thanks.
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||That's correct :)
Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Thats what it should show. You are updating every existing row, and populat
ing CustomerID1 with the value from CustomerID.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:ut408SeHGHA.
3408@.TK2MSFTNGP12.phx.gbl...
I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||At a minimum you should have certain fields defined as not null (CustomerID
for example). There should also be some way of identifying unique records,
even in a history table (although not in an audit table). Usually when I ha
ve worked with history tables they would have a date field that would be par
t of the primary key. When multiple entries were allowed on the same date,
some sort of sequence field was used to insure uniqueness.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:OYAFhQeHGHA.
528@.TK2MSFTNGP12.phx.gbl...
Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris

Problem copying a database for testing purposes

I want to make a database copy on the same computer for testing purposes.
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...

quote:

---

> I want to make a database copy on the same computer for testing purposes.
> In BOL I found in the Transact-SQL Reference:
> It may be necessary to create a copy of an existing database on the same
> computer for testing purposes. In this case, the database files for the
> original database already exist, so different file names need to be
> specified when the database copy is created during the restore operation.
> Make a copy of a database using BACKUP and RESTORE:
> This example uses both the BACKUP and RESTORE statements to make a copy of
> the Northwind database (I changed the destination path)
> BACKUP DATABASE Northwind
> TO DISK = 'c:\Northwind.bak'
> RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\Northwind.bak'
> WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This works fine, using SQLserver 2000 Developer with the Query analyzer.
> I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> .ldf also disapeared)
> Then I tried the production database:
> BACKUP DATABASE Vineadb
> TO DISK = 'C:\Vineadb.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This didn't work, giving the message:
> Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file

---

1.

quote:

---

> Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
> part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
> file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
> is terminating abnormally
> What could be the reason?
> Is there a solution?
> I am a Newbe on sqlserver
> Vineadb.bak is created (15MB)
> testdb.ldf and testdb.mdf are not created.
>
>

---

|||Jasper, using
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
[QUOTE]
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
purposes.[QUOTE]
of[QUOTE]
> 1.
(1.612[QUOTE]
not[QUOTE]
logical[QUOTE]
DATABASE[QUOTE]|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Jasper, using
> RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> I get a Grid with:
> . LogicalName
> . Physical Name
> . Type
> . FileGroupName
> . Size
> 1.
> VineaDb_Data
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> D
> PRIMARY
> 16449536
> 2.
> VineaDb_Log
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> L
> NULL
> 104595456
> This is the path to the production database,
> not to the just made C:\VineaDb.bak backup.
> Could this be the reason?
> Then I ask myself, why does it take these files, not the backup.
> Remember, ... I am a newbie on SQLServer.
> Henk.
> -- Original Message --
> From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 14, 2004 1:07 AM
> Subject: Re: Problem copying a database for testing purposes
>
> purposes.
copy[QUOTE]
> of
analyzer.[QUOTE]
and[QUOTE]
file[QUOTE]
1.[QUOTE]
> (1.612
> not
> logical
> DATABASE
>
>

---

|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> copy
> analyzer.
> and
> file
> 1.
is[QUOTE]
>

---

|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...[QUOTE]
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
1.[QUOTE]

Problem copying a database for testing purposes

I want to make a database copy on the same computer for testing purposes.
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> I want to make a database copy on the same computer for testing purposes.
> In BOL I found in the Transact-SQL Reference:
> It may be necessary to create a copy of an existing database on the same
> computer for testing purposes. In this case, the database files for the
> original database already exist, so different file names need to be
> specified when the database copy is created during the restore operation.
> Make a copy of a database using BACKUP and RESTORE:
> This example uses both the BACKUP and RESTORE statements to make a copy of
> the Northwind database (I changed the destination path)
> BACKUP DATABASE Northwind
> TO DISK = 'c:\Northwind.bak'
> RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\Northwind.bak'
> WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This works fine, using SQLserver 2000 Developer with the Query analyzer.
> I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> .ldf also disapeared)
> Then I tried the production database:
> BACKUP DATABASE Vineadb
> TO DISK = 'C:\Vineadb.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This didn't work, giving the message:
> Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
1.
> Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
> part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
> file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
> is terminating abnormally
> What could be the reason?
> Is there a solution?
> I am a Newbe on sqlserver
> Vineadb.bak is created (15MB)
> testdb.ldf and testdb.mdf are not created.
>
>|||Jasper, using
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > I want to make a database copy on the same computer for testing
purposes.
> >
> > In BOL I found in the Transact-SQL Reference:
> > ( . . . )
> > This example uses both the BACKUP and RESTORE statements to make a copy
of
> > the Northwind database (I changed the destination path)
> > ( . . . )
> > This works fine, using SQLserver 2000 Developer with the Query analyzer.
> > I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> > .ldf also disapeared)
> >
> > Then I tried the production database:
> > BACKUP DATABASE Vineadb
> > TO DISK = 'C:\Vineadb.bak'
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> > RESTORE DATABASE TestDB
> > FROM DISK = 'C:\Vineadb.bak'
> > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.mdf',
> > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.ldf'
> > GO
> >
> > This didn't work, giving the message:
> >
> > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
> 1.
> > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
(1.612
> > MB/sec).
> >
> > (2 row(s) affected)
> >
> > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
not
> > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
logical
> > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
DATABASE
> > is terminating abnormally|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> Jasper, using
> RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> I get a Grid with:
> . LogicalName
> . Physical Name
> . Type
> . FileGroupName
> . Size
> 1.
> VineaDb_Data
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> D
> PRIMARY
> 16449536
> 2.
> VineaDb_Log
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> L
> NULL
> 104595456
> This is the path to the production database,
> not to the just made C:\VineaDb.bak backup.
> Could this be the reason?
> Then I ask myself, why does it take these files, not the backup.
> Remember, ... I am a newbie on SQLServer.
> Henk.
> -- Original Message --
> From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 14, 2004 1:07 AM
> Subject: Re: Problem copying a database for testing purposes
>
> > What is the output of
> >
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > I want to make a database copy on the same computer for testing
> purposes.
> > >
> > > In BOL I found in the Transact-SQL Reference:
> > > ( . . . )
> > > This example uses both the BACKUP and RESTORE statements to make a
copy
> of
> > > the Northwind database (I changed the destination path)
> > > ( . . . )
> > > This works fine, using SQLserver 2000 Developer with the Query
analyzer.
> > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
and
> > > .ldf also disapeared)
> > >
> > > Then I tried the production database:
> > > BACKUP DATABASE Vineadb
> > > TO DISK = 'C:\Vineadb.bak'
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > > RESTORE DATABASE TestDB
> > > FROM DISK = 'C:\Vineadb.bak'
> > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.mdf',
> > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.ldf'
> > > GO
> > >
> > > This didn't work, giving the message:
> > >
> > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
file
> > 1.
> > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
1.
> > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> (1.612
> > > MB/sec).
> > >
> > > (2 row(s) affected)
> > >
> > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
> not
> > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> logical
> > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> DATABASE
> > > is terminating abnormally
>
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make a
> copy
> > of
> > > > the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with the Query
> analyzer.
> > > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
> and
> > > > .ldf also disapeared)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
> file
> > > 1.
> > > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
> 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> > (1.612
> > > > MB/sec).
> > > >
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
is
> > not
> > > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> > logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> > DATABASE
> > > > is terminating abnormally
> >
> >
> >
> >
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make
> > > > copy of the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with Query analyzer.
> > > > I deleted the testdb database (in the Enterprise manager)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for db 'Vineadb', file 'VineaDb_Data' on file
1.
> > > > Processed 1 pages for db 'Vineadb', file 'VineaDb_Log' on file 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 sec
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
> > > > is not part of database 'TestDB'.
> > > > Use RESTORE FILELISTONLY to list the logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5
> > > > RESTORE DATABASE is terminating abnormally

Problem copy a table from SQLServer 2000 data base to SQLExpress database using SMO.

I am trying to to copy a table from a SQLserver 2000 database to a new table in as SQLExpress database. The following is the code I used.

Private Sub btnCopyTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyTable.Click

Try
mFromDatabase = New Database(mFromSrv, Me.cboSelectFromDatabase.Text)
mToDatabase = New Database(mFromSrv, Me.cboSelectToDatabase.Text)
'mToDatabase.Create()
Dim xfr As New Transfer
xfr.CopyAllTables = False
'xfr.Options.WithDependencies = False
'xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = Me.cboSelectToDatabase.Text
xfr.DestinationServer = mFromSrv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
xfr.TransferData()
Catch ex As Exception
Dim errMsg As String
errMsg = ex.ToString
Throw
End Try

End Sub

When I do this I get the following error on the xfr.TransferData line.

Microsoft.SqlServer.Management.Smo.SmoException: The Integration Services component is not installed or you do not have permission to use it. > System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.SqlServer.Management.Smo.Transfer.CreateDtsTransferProvider()

=== Pre-bind state information ===
LOG: User = ACER-1\phowatt
LOG: DisplayName = Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
(Fully-specified)
LOG: Appbase = file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/
LOG: Initial PrivatePath = NULL
Calling assembly : Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91.
===
LOG: This bind starts in default load context.
LOG: Using application configuration file: D:\VS2005_Projects\AnalyzeProjects\Forms\bin\Forms.vshost.exe.config
LOG: Using machine configuration file from C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider.DLL.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider/Microsoft.SqlServer.DtsTransferProvider.DLL.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider.EXE.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider/Microsoft.SqlServer.DtsTransferProvider.EXE.

End of inner exception stack trace
at Microsoft.SqlServer.Management.Smo.Transfer.CreateDtsTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.GetTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Forms.frmTransfer.btnCopyTable_Click(Object sender, EventArgs e) in D:\VS2005_Projects\AnalyzeProjects\Forms\frmTransfer.vb:line 149

I used the following references with this code.

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SmoEnum

Microsoft.SqlServer.SqlEnum

SMO uses SSIS to perform data transfer operations, but SQL Server Express doesn't install the SSIS engine, so the transfer operation fails because the service SMO relies on to perform the operation doesn't exist on the machine you're running on. On this page of my blog - http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/10/31/24756.aspx - I described how to build a table from an existing one. Perhaps you can use this code to build your destination tables, and then use Transact-SQL to copy the data from the source to the destination database.|||I also have SQL Server 2005. If I install it will that install SSIS? Is there another way to install the SSIS engine? Or maybe your recommended approach is the better approach in any case.|||The full versions (Developer, Standard, Enterprise) of SQL Server allow you to install SSIS, which I believe will solve your problem (based on the error messages in your first post.)|||I have written myself a little application that enables me to set the various copy properties and option properties and then display the resulting scripts so that I can see how the various copy and option property settings affect the outcome. Since there is little documentation about SMO you cannot depend on your intuition about how these different property settings might affect the resulting script. Also there is IMO a shortcomming in the scripting of views. The problem occurs when there are views that are based on other views. If the underlying views are not created first then there will be an error when using the script when trying to create a view that uses another view and that other view has not yet been created. You might think that setting the CopyAllViews to True and setting the Options.WithDependencies to True would be how to deal with that but it appears that the Options.WithDependencies only affects scripts for tables. It appears that if you try to copy a database from one server to another and there are views like I have described above the Transfer will fail and it seems that there is no way to set the options to avoid this problem. So it appears that copying a database using Transfer will work only if there are no conditions in the source database that Transfer cannot handle.

problem connection Reporting services if change development server

Dear All..

I have develop reporting services 2005 with cube access in Server A.
because any problem in server A. i have copy all solution in server B.
all report in reporting services solution in server B cannot open mdx statement that i have write in RS solution in server A.
If i click data tab in one of report in RS, the mdx script have i write is gone and RS open wizard to make new mdx script again.
How to solve this problem.

any one help me...

thanks

Yudhi
Hai ALL,

I found what problem it is,
the problem is different of SP (service pack). i use service pack 2 in server A and service pack 1 in server B.

thanks all