SQL Server - Copy views from one database server to another

Asked By d on 22-Nov-07 12:50 PM
Hi

I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.

What is the best method to do this?

Thanks

ontario






--
ontario, canada




Jeffrey Williams replied on 22-Nov-07 12:57 PM
First, why do you need to copy the views every day?  A view is just a
definition, and really shouldn't change on a daily basis.  With that
said, the process is:

Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005

Another option is to use SSIS on the 2005 box and create a package to
move the objects.  Search BOL for additional information on how to set
this up.

Jeff
d replied on 22-Nov-07 01:53 PM
Hi Jeffery

I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute

CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c

What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c

Can I do this directly by executing a query or have to use SSIS,
import/export or replication.




--
ontario, canada
d replied on 22-Nov-07 01:58 PM
access data from a remote server from within a query. Should a linked server
be a good idea.
--
ontario, canada
bass_player [SBS-MVP] replied on 22-Nov-07 11:13 PM
Views do not contain data, they just reference them.  Copying the views
will not copy the data.
Jeffrey Williams replied on 22-Nov-07 11:55 PM
I do not know what you are trying to accomplish.  Are you trying to move
data from one server to another?  Access data on server1 from server2?

If all you need to do is access data on a different server, then a
linked server might be the solution.  Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)

If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data.  This can also be done using
linked servers, but you have much more control using SSIS.

Jeff
d replied on 23-Nov-07 10:10 AM
I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.

The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.

--
ontario, canada
Jeffrey Williams replied on 23-Nov-07 11:44 AM
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS.  I would recommend SSIS because
you have many more options.

What version of SQL are you using?  If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system.  Using synonyms you could setup the
following:

Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object

And then access that object in code with:

Select <columns> From ServerA.ObjectA.

Jeff
d replied on 23-Nov-07 12:50 PM
On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)

--
ontario, canada
d replied on 23-Nov-07 12:51 PM
Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
--
ontario, canada