Copy views from one database server to another

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, canadaFirst, 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
db wrote:
> 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
>
>
>
>|||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
"Jeffrey Williams" wrote:
> 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
> db wrote:
> > 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
> >
> >
> >
> >
> >
> >
> >
>|||access data from a remote server from within a query. Should a linked server
be a good idea.
--
ontario, canada
"db" wrote:
> 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
>
> "Jeffrey Williams" wrote:
> > 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
> >
> > db wrote:
> > > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
>> 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
>>
>> "Jeffrey Williams" wrote:
>> > 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
>> >
>> > db wrote:
>> > > 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
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> >|||db wrote:
> access data from a remote server from within a query. Should a linked server
> be a good idea.
>
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|||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" wrote:
> db wrote:
> > access data from a remote server from within a query. Should a linked server
> > be a good idea.
> >
> 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
>|||db wrote:
> 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.
>
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|||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
"Jeffrey Williams" wrote:
> db wrote:
> > 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.
> >
> >
> 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
>|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:
> db wrote:
> > 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.
> >
> >
> 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
>