I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:
> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (in
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
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()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>>I am not a DBA, so pardon me if my question is basic.
>> What is the easiest way to move SQL Server 2005 database from 1 machine
>> to another ?
>
>> If I backup the database, I can not restore it in another database or in
>> another machine, can I ?
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>> Is exporting the database the easiest way to move it to another machine
>> (in the new machine before importing the data I will still need to create
>> the database and run the database script to create all the tables and
>> stored procedures and views, right ?) ?
>> Thank you.
>>
>|||Did you solve this issue? If not, try going to tab 2 of the restore db page,
and clicking Overwrite Existing Database. That should work.
--
John
"fniles" wrote:
> Thank you all for the replies.
> I still want the original database on machine A, I just want another copy of
> it in another machine.
> So I backup the original database (say it's called MYDB) on machine A.
> I then copied the MYDB.bak file from machine A to machine B.
> In machineB, I then created a database called MYDB. Then I right click on
> the database and select "Restore". Under "Specify source and location of
> backup sets to restore" I selected "From device" and I pointed it to the
> MYDB.bak in machine B.
> I then got the error "Restore failed for server "serverB"
> System.data.SqlClient.sqlerror: The backup set holds a backup of a database
> other than the existing "MYDB" database. (microsoft.sqlserver.smo)
> What did I do wrong on the above steps ?
> Since I still need the original database and still using it, can I detach it
> after backing it up ?
> When I tried to copy the database from machine A to machine B, it works when
> I selected "Use the detach and attach method" but when I selected "Use the
> SQL Management Object method", I got an error with a view about an invalid
> table name HistTradesOrig, which exists in the database, and I am able to
> run the query fine.
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query "
> Create view [dbo].[Commission] AS
> SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
> FROM HistTradesOrig
> GROUP BY HistTradesOrig.email, HistTradesOrig.Status
> HAVING (((HistTradesOrig.Status)='F'))
> " failed with the following error: "Invalid object name 'HistTradesOrig'.".
> 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()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
> Operator: PFGBEST\sqladm
> Thank you.
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> >
> > "fniles" <fniles@.pfmail.com> wrote in message
> > news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
> >
> >>I am not a DBA, so pardon me if my question is basic.
> >> What is the easiest way to move SQL Server 2005 database from 1 machine
> >> to another ?
> >
> >
> >> If I backup the database, I can not restore it in another database or in
> >> another machine, can I ?
> >
> > sure you can; make sure you detach the DB first (after backup of course)
> > ... you might have to change the location of the physical files in the
> > restore dialogue options
> >
> > or you can use the "Copy Database" facility in SQL Server Management
> > Studio (select DB, right click for menu; Tasks->Copy Database
> >
> > or ... there are other options still ... but those are likely the easiest
> > ones
> >
> >> Is exporting the database the easiest way to move it to another machine
> >> (in the new machine before importing the data I will still need to create
> >> the database and run the database script to create all the tables and
> >> stored procedures and views, right ?) ?
> >>
> >> Thank you.
> >>
> >>
> >
> >
>
>
Showing posts with label machine. Show all posts
Showing posts with label machine. Show all posts
Thursday, March 22, 2012
Easiest way to move database from 1 machine to another ?
I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.
fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.
|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:
> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (in
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>
|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
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.DtsTransferProv ider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>
|||Did you solve this issue? If not, try going to tab 2 of the restore db page,
and clicking Overwrite Existing Database. That should work.
John
"fniles" wrote:
> Thank you all for the replies.
> I still want the original database on machine A, I just want another copy of
> it in another machine.
> So I backup the original database (say it's called MYDB) on machine A.
> I then copied the MYDB.bak file from machine A to machine B.
> In machineB, I then created a database called MYDB. Then I right click on
> the database and select "Restore". Under "Specify source and location of
> backup sets to restore" I selected "From device" and I pointed it to the
> MYDB.bak in machine B.
> I then got the error "Restore failed for server "serverB"
> System.data.SqlClient.sqlerror: The backup set holds a backup of a database
> other than the existing "MYDB" database. (microsoft.sqlserver.smo)
> What did I do wrong on the above steps ?
> Since I still need the original database and still using it, can I detach it
> after backing it up ?
> When I tried to copy the database from machine A to machine B, it works when
> I selected "Use the detach and attach method" but when I selected "Use the
> SQL Management Object method", I got an error with a view about an invalid
> table name HistTradesOrig, which exists in the database, and I am able to
> run the query fine.
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query "
> Create view [dbo].[Commission] AS
> SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
> FROM HistTradesOrig
> GROUP BY HistTradesOrig.email, HistTradesOrig.Status
> HAVING (((HistTradesOrig.Status)='F'))
> " failed with the following error: "Invalid object name 'HistTradesOrig'.".
> 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.DtsTransferProv ider.ExecuteTransfer()
> at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
> Operator: PFGBEST\sqladm
> Thank you.
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
>
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.
fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.
|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:
> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (in
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>
|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
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.DtsTransferProv ider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>
|||Did you solve this issue? If not, try going to tab 2 of the restore db page,
and clicking Overwrite Existing Database. That should work.
John
"fniles" wrote:
> Thank you all for the replies.
> I still want the original database on machine A, I just want another copy of
> it in another machine.
> So I backup the original database (say it's called MYDB) on machine A.
> I then copied the MYDB.bak file from machine A to machine B.
> In machineB, I then created a database called MYDB. Then I right click on
> the database and select "Restore". Under "Specify source and location of
> backup sets to restore" I selected "From device" and I pointed it to the
> MYDB.bak in machine B.
> I then got the error "Restore failed for server "serverB"
> System.data.SqlClient.sqlerror: The backup set holds a backup of a database
> other than the existing "MYDB" database. (microsoft.sqlserver.smo)
> What did I do wrong on the above steps ?
> Since I still need the original database and still using it, can I detach it
> after backing it up ?
> When I tried to copy the database from machine A to machine B, it works when
> I selected "Use the detach and attach method" but when I selected "Use the
> SQL Management Object method", I got an error with a view about an invalid
> table name HistTradesOrig, which exists in the database, and I am able to
> run the query fine.
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query "
> Create view [dbo].[Commission] AS
> SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
> FROM HistTradesOrig
> GROUP BY HistTradesOrig.email, HistTradesOrig.Status
> HAVING (((HistTradesOrig.Status)='F'))
> " failed with the following error: "Invalid object name 'HistTradesOrig'.".
> 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.DtsTransferProv ider.ExecuteTransfer()
> at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
> Operator: PFGBEST\sqladm
> Thank you.
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
>
Easiest way to move database from 1 machine to another ?
I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:
> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (i
n
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Sta
tus
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
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()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.Transf
erDatabasesUsingSMOTransfer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:
> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (i
n
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Sta
tus
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
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()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.Transf
erDatabasesUsingSMOTransfer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>
Easiest way to copy a MS-SQL Database from one machine to another
Can anyone recommend the easiest way to get a full copy of a database from
one server to another. The servers are not part of the same organization or
network.
one server to another. The servers are not part of the same organization or
network.
I have received a backup of the database created with enterprise manager but
am unable to restore it into a database of the same name on my server.
Thanks,
Kevin"Kevin" <noemail@.provided.com> wrote in message
news:tr_Zd.699401$8l.384177@.pd7tw1no...
> Can anyone recommend the easiest way to get a full copy of a database from
> one server to another. The servers are not part of the same organization
> or
> network.
> I have received a backup of the database created with enterprise manager
> but
> am unable to restore it into a database of the same name on my server.
> Thanks,
> Kevin
http://support.microsoft.com/defaul...kb;en-us;314546
Backup and restore is usually the easiest way - if you're having a problem
restoring a backup, then I suggest you post whatever error message you get.
You can also try restoring from Query Analyzer:
restore database MyDB from disk = 'c:\temp\db.bak'
Sometimes errors in QA are more informative than what EM tells you. See the
full RESTORE syntax in Books Online for moving file locations etc.
Simon|||Did you checked the paths to database files before restoring?
If you are using the EM restore database function, the database name
doesn't matter. You can name your new database as you would like to.
The only importand thing is, check the path, where the database should
be restored to.
If the backup was done on other server, probably the Db files was
located under different path (for example: C:\Program
Files\Database\database_name.mdf").
Lets say, that you try to restore it over database BUBU. In this case,
you can have totaly diff. path and of course filenames ... (for
example: "C:\Databases\BUBU_Data.mdf). Therefore, you'll get an error
message, that the files does not match. This is because why, database
reads information from backup about location of database ...
Greatings
Matik|||Simon,
I've tried restoring from QA, the directories for the other server and my
server are different so I am using the MOVE option for the data and log
files. The same problem occurs that I was getting using EM, it complains
that the logical file being restored is not part of my database. I've tried
this without a database called 'Test', as well as after creating one and
using the RESTORE FILELISTONLY but the error message is always the same:
here's the code and error message:
RESTORE DATABASE Test FROM DISK = 'C:\Backups\Project Recovery\Sample
Data\RebillingMarch102005.SQLBackup'
WITH NORECOVERY,
MOVE 'g:\mssql\data\rebilling_Data.MDF' TO 'C:\Program Files\Microsoft
SQL Server\MSSQL\Data\Rebilling_Data.mdf',
MOVE 'E:\MSSQL\log\rebilling_Log.LDF' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\Rebilling_Log.ldf'
response:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'g:\mssql\data\rebilling_Data.MDF' is not part of database
'Test'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Thanks,
- Kevin
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:42387d3d$1_3@.news.bluewin.ch...
> "Kevin" <noemail@.provided.com> wrote in message
> news:tr_Zd.699401$8l.384177@.pd7tw1no...
> > Can anyone recommend the easiest way to get a full copy of a database
from
> > one server to another. The servers are not part of the same organization
> > or
> > network.
> > I have received a backup of the database created with enterprise manager
> > but
> > am unable to restore it into a database of the same name on my server.
> > Thanks,
> > Kevin
> http://support.microsoft.com/defaul...kb;en-us;314546
> Backup and restore is usually the easiest way - if you're having a problem
> restoring a backup, then I suggest you post whatever error message you
get.
> You can also try restoring from Query Analyzer:
> restore database MyDB from disk = 'c:\temp\db.bak'
> Sometimes errors in QA are more informative than what EM tells you. See
the
> full RESTORE syntax in Books Online for moving file locations etc.
> Simon|||Instead of 'g:\mssql\data\rebilling_Data.*MDF' use
'rebilling_Data.*MDF' and
rebilling_Log.*LDF'
Madhivanan|||The first name after "MOVE" should be the logical file name, not the
physical. In your case, probably, "rebilling_Data" and "rebilling_Log"
are the logical names you need to use.
Wednesday, March 21, 2012
Dynamically using different schema names in Oracle
I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.
The views belong to different schemas e.g. user01.view01 and user02.view02.
When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.
I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01
The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:
"SELECT * FROM " + @.[SchemaName01] + ".view01"
The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.
The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...
If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||
Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?
-Jamie
|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.
By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.
Fridtjof
|||
Friedel wrote:
As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.
I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.
Once you have a working expression you can copy/paste it into the Expression property of your variable.
Its far from perfect I know, but it works!
By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.
-Jamie
sql
Dynamically using different schema names in Oracle
I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.
The views belong to different schemas e.g. user01.view01 and user02.view02.
When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.
I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01
The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:
"SELECT * FROM " + @.[SchemaName01] + ".view01"
The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.
The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...
If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||
Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?
-Jamie
|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.
By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.
Fridtjof
|||
As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.
I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.
Once you have a working expression you can copy/paste it into the Expression property of your variable.
Its far from perfect I know, but it works!
By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.
-Jamie
Subscribe to:
Posts (Atom)