Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Where do I get SQL2000 Upgrade?


I have tried numerous searches, but cannot find where/how to get SQL2000 so I may upgrade my SQL7 Enterprise Manager on my local machine.

I need to perform some importing from my database (hosted by ISP) to my local machine, but SQL7 doesn't allow this function.

Where do I find SQL2000 for download, or other?


have I been told correctly that SQL2000 Enterprise Manager allows remote connectivity?

thanks very much
1 Solution
Anthony PerkinsCommented:
If you are in the US or Canada this link should help:

SQL Server 7's Enterprise Manager (EM) has the capability to connect to SQL 7 database servers over a LAN or the Internet.  SQL Server 2000's version of the same tool allows connection to SQL 7 and SQL 2000 servers and has some enhancements and bug fixes.  Both version contain DTS which some people use to import and export data through a visual design process.  The unfortunate thing about EM is that it doesn't ship with the Personal/Desktop editions of SQL Server.  It is installed from the MS SQL Standard/Enterprise/Data Center edition CD-ROMs.

It sounds like you ghave a desktop edition of SQL Server on your local computer and you're trying to import the data from a remote computer of the internet.  My first question would be: Does your ISP allow any access to the SQL Server over the internet?  If so. you should be able to get the data out w/o SQL EM.
4adAuthor Commented:
acperkins, thanks for the link. I'll check it out.

rherguth, yes you are correct with what I am trying to do. (import the data to my local desktop edition of SQL)

And yes, my ISP allows internet connection to the SQL server. I must have a limited function EM because it came with the shipment of Visual Interdev6, and I can perform views and create tables on my local machine, but the version of SQL 7 cannot connect to my ISP's SQL 2000 server.

Is there another method you're thinking of?

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

If you can connect directly to the remote SQL Server, you can use sp_addlinkedserver to get at the data easily using a 4 part name.  Open up SQL Query Analyzer and connect to the desktop server.  Execute something like this with your corrected parameters:

sp_addlinkedserver @server = N'LinkServer',
    @srvproduct = N' ',
    @provider = N'SQLOLEDB',
    @datasrc = N'ServerNetName',
    @catalog = N'Northwind'

You may also need to execute sp_addlinkedsrvlogin if the SQL account you're using locally doesn't match the name and password of the remote computer.  For instance, you're logged in as sa locally, but need to use an account called 4ad on the remote computer.

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    [ , [ @rmtpassword = ] 'rmtpassword' ]

Finally access the remote database using a 4 part name:
SELECT * FROM LinkServer.Northwind..Shippers

When you're done use sp_dropserver and sp_droplinkedsrvlogin

These commands all work with SQL 7 or 2000
Also, I think you'd want to use SELECT INTO so the tables are created and populated with data from the remote computer.
4adAuthor Commented:
ok, thanks.

The addlinkedserver seems to have worked so far. The only parameters I changed were

   @datasrc = N'ServerNetName',
   @catalog = N'Northwind'

...that's the only two, correct?

However, the

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
   [ , [ @useself = ] 'useself' ]
   [ , [ @locallogin = ] 'locallogin' ]
   [ , [ @rmtuser = ] 'rmtuser' ]
   [ , [ @rmtpassword = ] 'rmtpassword' ]

is very new looking to me. Which of the parameters do I change?

is it:

   [ , [ @rmtuser = ] 'rmtuser' ]
   [ , [ @rmtpassword = ] 'rmtpassword' ]

? I'd hate to really mess things up.

4adAuthor Commented:
hmm. I get:

" Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'rmtsrvname'. "

4adAuthor Commented:
and when I am logged in,

how do I view (or import) my stored procedures? and tables, etc...

thanks very much!
Correct on the sp_addlinkedserver params.

Change the 'rmtsrvname' to 'LinkServer'.

The tables can be imported using SELECT INTO which will create the table and copy the data.

For stored procedures and functions, set Query Analyzer to display results as text instead of a grid and type:

EXEC LinkServer.<databasename>.dbo.sp_helptext <stored procedure name>

Copy and paste the resultant text into Query Analyzer and the SP will be created.  This assumes you know the names of the SPs and Functions on the remote server.  Any Functions you see won't work with SQL 7, however.  Functions are a SQL 2000 feature.

To find out what's in the remote database:
-- Tables
SELECT name FROM LinkServer.<databasename>.dbo.sysobjects so WHERE xType IN ('U') ORDER BY name
-- Views
SELECT name FROM LinkServer.<databasename>.dbo.sysobjects so WHERE xType IN ('V') ORDER BY name
-- Stored Procedures
SELECT name FROM LinkServer.<databasename>.dbo.sysobjects so WHERE xType IN ('P') And name NOT LIKE 'dt_%' ORDER BY name
-- Functions that return a Table datatype
SELECT name FROM LinkServer.<databasename>.dbo.sysobjects so WHERE xType IN ('TF') ORDER BY name
-- Functions that return a rowset
SELECT name FROM LinkServer.<databasename>.dbo.sysobjects so WHERE xType IN ('IF') ORDER BY name
-- Functions that return a scalar value
SELECT name FROM LinkServer.<databasename>.dbo.sysobjects so WHERE xType IN ('FN') ORDER BY name

If there are a lot of objects, you may want to script something up, or use VB and SQL-DMO, or get a copy of SQL 2000 EM, or use replication, or have the ISP send you a copy of the DAT and LOG files.
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
Please award me the points for my efforts
No comment has been added to this question in more than 257 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: rherguth http:#8134737

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now