0

I have a table in a SQL Server 7.0 database with columns like:

Column_1      varchar(10)     null
Column_2      int             null
Column_3      varchar(15)     null
Column_4      float(53)       null
Column_5      float(53)       null
Column_6      text(16)        null
Column_7      text(16)        null
Column_8      varchar(25)     null
Column_9      datetime        null

and I need to move it to a table in SQL Server 2005. What is the quickest and easiest way? I would generally just INSERT (cols...) SELECT cols.. FROM OtherDatabase.dbo.table but I can't do that because it is SQL Server 7.0.

RacerX
  • 2,566
  • 3
  • 23
  • 21
  • 2
    Import it from 2k5's Management Studio (right click db, task, import) and follow the wizard – Alex K. May 13 '11 at 15:11
  • @Alex K., I get the following error trying to connect to the SQL Server 7.0 server: `Cannot connect to YourServerName. Additional information: This version of Microsoft SQL Server Management Studio can only be used to connect to SQL Server 2000 and SQL Server 2005 servers (ConnectionDlg)` I'm running SQl Server 2005 Enterprise. – RacerX May 13 '11 at 16:07

1 Answers1

1

Although I haven't tried this (don't have a SQL 7.0 install handy :-)) you should be able to do something like below to setup a linked server and just get the data from a four part name query.

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/7b24b699-f8b9-48e8-ba78-b037fd3b1d21/

Sankar Reddy
  • 1,499
  • 9
  • 10
  • I used this `sp_addlinkedserver @server=YourServerName` which worked with no errors. However when I did this: `select * from YourServer.YourDatebase.dbo.YourTable` I got this error: `Msg 18456, Level 14, State 1, Line 0 Login failed for user 'MyUserName'.` – RacerX May 13 '11 at 16:17
  • I created a new user with the same password on each server/database and when connected using this new user the select from the old database works. – RacerX May 13 '11 at 20:38