November 2, 2011 at 11:41 am
Hi,
I am encountering an issue with Linked Server...
Recent Past:
We have a single DB server having multiple databases.
We were fetching data from main db and insert specific data for particular department into respective db.
Now we moved our main db on a different serve.
Out task is to fetch data from main db on new server and insert specific data for particular department into respective db.
Scenario:
- ServerA (SQL 2005) - Main db
- ServerB (SQL 2005) – Department specific dbs
- Created a Linked Server <LNKSVRB>on ServerA for fetching data from ServerB
- Added domain\user1 with impersonation in Linked server as this user has permission on new server for reading data
- ServerA - In databaseV have a view:
- View1: SELECT Col1, Col2 FROM [LNKSVRB].Database1.dbo.Table1
PS: Prior to new server this query was: SELECT Col1, Col2 FROM Database1..Table1
ServerA – In databaseD have a view and stored procedure
ViewSP1: SELECT * FROM database..View1
SP1: only select statement: SELECT * FROM ViewSP1
SSIS:
In data flow of package1.dtsx
-OLE DB Source > SQL Command > Execute SP1
-OLE DB destination: ServerA. databaseD..Table1
Issue: This ssis was executing fine before I added Linked server in database.View1
After adding link server when I tried to test my ssis package.. it shows meta data issue… when I fixed that (click on OK button) all columns disappear when I checked in column mapping tab.
When I run the following Select statement in databaseD:
SELECT * FROM Views I got an error message:
SQL Execute Error
Executed SQL statement: SELECT * FROM Views
Error Source: .Net SqlClient Data Provider
Error Message: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
Please help me in resolving this issue…
Thanks
November 4, 2011 at 7:45 am
Seems like your problem is that the user which you are using on SSIS to connect to the database, doesn't have permissions to access the linked server.
But I think your real problem is that you are using a linked server, period. SSIS is perfectly designed to avoid the need to use linked servers. You have several solutions to avoid the need. You can have multiple different sources that you merge together, you can create temp tables and download the data across and use from one local server, etc...
November 4, 2011 at 8:36 am
You'r are right by saying that we can use ssis to load data in temp table.... we have this option as a last resort...
We are working on it...
Will share findings sooon... meanwhile if anyone has any idea/solution then pls share with us.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply