Pulling data from SQL 200 TO SQL 2005

Hi,

I have created a linked server in 2005 in the hope I can import data via a simple query:

The local server (2005) is called MAGGIE and the remote server (2000) is called STARBASE, the linked server has worked fine, but for some reason it would accept the local server and I get the error:

Could not find server 'MAGGIE' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

The query I am using is below
SELECT TelVoutID 
FROM [MAGGIE].[WEBBILL].[dbo].[wbTelVoiceOut] 
WHERE TelVoutID NOT IN (SELECT TelVoutID from [GBLON3\STARBASE].[WEBBILL].[dbo].[wbTelVoiceOut])

Open in new window

trojan_ukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bradleys40Commented:
have you tried ssis
0
trojan_ukAuthor Commented:
Thanks bradleys40,

I'm very new to 2005 and not a DB developer anyway, but after your mention of SSIS, at least I now know were DTS went.

I have been looking through all the options in Data Flow (which I assume is where I need to be) but there are so many new components all I want is a SQL Task and a OLE DB connection like I used to use, could you point out which componets I am to use within SSIS to place a simple query to copy data from one database to another.

Many thanks

0
bradleys40Commented:
it took me a while to realise where DTS had gone but when i found ssis i was happy again,
it is fairly similar to DTS here is a site with a quick guide it includes using ole dB connection which gives more info than I have time list and would be mopre usefull
http://www.edumax.com/microsoft-sql-server-04-sql-server-a-practical-introduction-to-ssis-sql-server-a-practical-introduct.html
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

BrandonGalderisiCommented:
If you run the below query, what is returned in the srvname column?  Depending on how the linked server was added, you could have a name other than the physical machine name as the linked server name.


select * from master.sys.sysservers

Open in new window

0
trojan_ukAuthor Commented:
I get two servers returned;

GBLON3\STARBASE & MAGGIE
0
BrandonGalderisiCommented:
Are you trying to reference maggie from starbase?  Or Maggie from Maggie?

Because if you are trying to access it from StarBase then you need to add a linked server to go in the other direction as well.
0
trojan_ukAuthor Commented:
no from maggie.

The problem is I can set up an OLE DB source connection to STARBASE and a destination connection to MAGGIE, but I can't work out how to run a query between the two, or in fact if i'm using the correct components.

What I want to do is copy data from STARBASE.wbTelVoiceOut to MAGGIE.TelVoiceOut where the STARBASE.TelVoutID doesn't exist in MAGGIE.TelVoutID

Thanks
0
BrandonGalderisiCommented:
So if you run this from MAGGIE, where the linked server is setup and you have two records in sys.sysservers..... what do you get?
SELECT top 10 TelVoutID 
FROM [WEBBILL].[dbo].[wbTelVoiceOut] 
WHERE TelVoutID NOT IN (SELECT top 10 TelVoutID from [GBLON3\STARBASE].[WEBBILL].[dbo].[wbTelVoiceOut])

Open in new window

0
trojan_ukAuthor Commented:
I get a telvoutid column with the numbers 11-20
0
BrandonGalderisiCommented:
Ok... So it's working then?

I think when you were getting "Could not find server 'MAGGIE' in sysservers.", you were trying to run it from STARBASE.  In order to be able to run it from STARBASE, you have to add maggie as a linked server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trojan_ukAuthor Commented:
Brandon,

Thanks, as you said I think what I was doing was trying to add the query to the source rather then the destination.

Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.