move table from SQL2000 to SQL2005?

I need to move some tables from an SQL2000 database on one server to an
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice with
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> If you don't want to re-create the table definition (which you would have to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John|||Hi
"Dabbler" wrote:
> I don't think I can use BCP because both servers are hosted at Appliedi.net
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
> > Hi
> >
> > "Dabbler" wrote:
> >
> > > I need to move some tables from an SQL2000 database on one server to an
> > > SQL2005 database on another server(and in some cases back again). I don't
> > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > SQL2000.
> > >
> > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > because they're free ;)
> > >
> > > Thanks!
> >
> > If you don't want to re-create the table definition (which you would have to
> > script and run through SQLCMD if you did!) then you can populate the table
> > using BCP or if you have a linked server INSERT...SELECT
> >
> > John
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I don't think I can use BCP because both servers are hosted at Appliedi.net
> > so I don't have access to their file systems.
> >
> > Is there a way to simultaneously connect to two databases on two different
> > servers with T-SQL? that would allow me to use your "linked server"
> > suggestion.
> >
> > Thanks much!
> >
> > Michael
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > "Dabbler" wrote:
> > >
> > > > I need to move some tables from an SQL2000 database on one server to an
> > > > SQL2005 database on another server(and in some cases back again). I don't
> > > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > > SQL2000.
> > > >
> > > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > > because they're free ;)
> > > >
> > > > Thanks!
> > >
> > > If you don't want to re-create the table definition (which you would have to
> > > script and run through SQLCMD if you did!) then you can populate the table
> > > using BCP or if you have a linked server INSERT...SELECT
> > >
> > > John
> Use sp_addlinkedserver to create the linked server and use either OPENQUERY
> or four part names to run the query on the linked server.
> John|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. Of
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> but of course the install blocks because of my SQL Server Express install. Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> > SQL Server 2005 Express. I only have SSMS Express which is lite version of
> > SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> > but of course the install blocks because of my SQL Server Express install. Of
> > course all I really need is Enterprise Manager but I'm not an Enterprise,
> > just an independent developer ;)
> >
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although for
> your current issue it may not help.
> John|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John