copying data accross databases for backup

I am not an expert in SQL
I am faced with the task of copying certain attributes froma table in a
database to create a table in another database to have a sort of refined
backup database.I have to do it through a script to run in Sqlserver query
analyser I have created a query of sort ->
select attrib1, attrib2. into back_up_database_table From
Actual_database..Actual_table where <some condition>
I am logged to query analyser using the backup_database
I am promted to use the command sp_addlinkedserver for the actual database
however still i am getting the error SQLserver doesnot exist or access denie
d
How can I use an accross databes script
Can any help
Thanks in advance
AbhishekWhat kind of a DB are you copying from? Is it a SQL to SQL or access to SQL
?
"adg" wrote:

> I am not an expert in SQL
> I am faced with the task of copying certain attributes froma table in a
> database to create a table in another database to have a sort of refined
> backup database.I have to do it through a script to run in Sqlserver query
> analyser I have created a query of sort ->
> select attrib1, attrib2. into back_up_database_table From
> Actual_database..Actual_table where <some condition>
> I am logged to query analyser using the backup_database
> I am promted to use the command sp_addlinkedserver for the actual database
> however still i am getting the error SQLserver doesnot exist or access den
ied
> How can I use an accross databes script
> Can any help
> Thanks in advance
> Abhishek
>|||When selecting from a table on a linked SQL Server, keep in mind that the
server name prefix and object owner (typically DBO) are required. For
example, if you have a linked server called SERVERNAME:
select attrib1, attrib2. into back_up_database_table From
SERVERNAME.Actual_database.DBO.Actual_table where <some condition>
"adg" <u17748@.uwe> wrote in message news:5a7c9fe20d792@.uwe...
>I am not an expert in SQL
> I am faced with the task of copying certain attributes froma table in a
> database to create a table in another database to have a sort of refined
> backup database.I have to do it through a script to run in Sqlserver query
> analyser I have created a query of sort ->
> select attrib1, attrib2. into back_up_database_table From
> Actual_database..Actual_table where <some condition>
> I am logged to query analyser using the backup_database
> I am promted to use the command sp_addlinkedserver for the actual database
> however still i am getting the error SQLserver doesnot exist or access
> denied
> How can I use an accross databes script
> Can any help
> Thanks in advance
> Abhishek|||I was copying a sqlserver databases Thanks JT giving the fullname of the
database has solved the problem.Its working fine.
Thanks again
JT wrote:
>When selecting from a table on a linked SQL Server, keep in mind that the
>server name prefix and object owner (typically DBO) are required. For
>example, if you have a linked server called SERVERNAME:
>select attrib1, attrib2. into back_up_database_table From
>SERVERNAME.Actual_database.DBO.Actual_table where <some condition>
>
>[quoted text clipped - 13 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200601/1

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'
Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'VMSGRECIPIENTS2'
>
>
|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'VMSGRECIPIENTS2'
>
>|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>> We have been using the 'Copy SQL Server Objects' with success for some
>> time to copy an entire database to another server. Recent changes to our
>> database infrastructure cause DTS to fail:
>> 1) We have a check constraint which uses a user function (which refers to
>> the table on the check constraint
>> 2) We have a view which refers to another view
>> DTS fails on both. DTS fails on the check constraint and stops. if I
>> remove the check constraint it will fail on the view. See errors below.
>> Any hints how to solve this would be appreciated.
>> Jonathan Orgel
>> Errors:
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'dbo.SRS_NumberRWSubjects'
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'VMSGRECIPIENTS2'
>>
>

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object n
ame
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object n
ame
'VMSGRECIPIENTS2'Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object
name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object
name
> 'VMSGRECIPIENTS2'
>
>|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>