linked server from sql mgmt studio express v9 to sql 2005

Hello- I am trying to create a linked server from my studio express instance to our discountasp sql '05 db.

I have followed the instructions here to create the linked server:



You can also define linked servers by using SQL Server Management Studio. In the Object Explorer, right-click Server Objects, select New, and select Linked Server. You can delete a linked server definition by right-clicking the linked server name and selecting Delete.


on site: http://msdn.microsoft.com/en-us/library/ms188279.aspx


This creation seemed to be a success.


however, when i try to copy table data from discountasp dbto my local server using this query:
INSERT INTO DriverReportsDHLPkgs
SELECT * FROM [sql2k503.discountasp.net].SQL2005_245451_KIM.dbo.DriverReportsDHLPkgs
WHERE StationCode = ?ABE?


Iget these errors:


OLE DB provider "SQLNCLI" for linked server "sql2k503.discountasp.net" returned message "Invalid authorization specification".


Msg 7399, Level 16, State 1, Line 1


The OLE DB provider "SQLNCLI" for linked server "sql2k503.discountasp.net" reported an error. Authentication failed.


Msg 7303, Level 16, State 1, Line 1


Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "sql2k503.discountasp.net".





Does anyone have any advice on how to fix this? I did connect to the discountasp server and my own server before attempting this.





Thanks much for any help.





Kevin

 

Thanks for your response Bruce. Is there another way you know of to move data from the remote server to my local server?

Thanks again.

KP

 

Thanks Bruce.

but how do i delete the records on the discountasp side to free up space?

thanks for your help

 

Bruce- i want to move specific records off the discountasp database onto my local. i believe the backup will prob backup full dbs, not giving me the choice to select which records.


isnt there an easier way to move these records in mgmt studio express? i selected the records and tried edit/copy, but i couldnt paste in the local db, the option was greyed out.





thanks for your help Bruce.

 

Bruce- thanks for the response.

I am running sql 2005 express, am I able to use the import/export wizard with that?

if so, how? (the option was not present in the database right click menu)


Thanks

 

Hi,
The only SQL Express version that included the wizards is 2008.
You can upgrade to it for free and it does run better, even lighter, than the previous express versions.
Salute,
Mark




Technical Evangelist for DiscountASP.NET
http://www.iis7test.com/webcasts/
http://weblogs.asp.net/markwisecarver/
http://blogs.windowsclient.net/wisecarver/default.aspx

(Microsoft IT Usability http://msitusability.multiply.com/)