Copy data from table to another server?

[Lukus]

Hi guys,

something that might be useful for SQL users;

i want to move data from one table in production out to our reporting server.
basically the contacts table.

so:

 
SELECT * INTO ServiceCenter.dbo.contactsm1 
FROM <server_name>.servicecenter_reporting.dbo.contactsm1

<server_name> of course being my reporting server name.
should be as easy as that, but i get the following error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Which is odd, because the two servers are linked, and have specified logons to use for each other.

does anyone have any experience in this?

Thanks!!

cheers,
Lukus

[Artur]

I think it is pure MS SQL Server management problem, and has nothing to do with HPSM.

What authentication you use for both SQL servers (Windows, Mixed)?
The easiest way is to use mixed and create the same user on both servers.
Or have the same NT user on both servers

BTW: you can use Data transformation services (click on DB, Tasks --> Import data)
or ConnectIt - as well.

regards
Artur

[Lukus]

yeah, pure SQL thing... just thought this sorta thing might be useful for our SQL section of the forum

both Servers are in "both"... i've ben through a couple of MSDN fixes, but to no avail.

the only reason i wanted to do a straight copy in SQL is for speed... and it's not a regular sort of thing, just when i need soem fresh data i guess.

thanks for the ideas, i'll post back if i manage to fix it

[Lukus]

so, i didn't ever manage to figgure out why i couldn't just run this straight from one to the other. But if anyone was interested, i managed to get a nice easy data move by:
right click on the db you want to post from,
tasks -->
Export data

and follow the prompts in the wizard.
yep easy as that. which make my original question even more confusing!

hope this helps someone

Cheers,
Lukus