View creation on Linked Server

Question

View creation on Linked Server

Ness

SSCarpal Tunnel

Points: 4282
More actions
May 13, 2013 at 3:50 am

#277378

Hi All,
I have had an issue in SQL 2008 SP1 when creating a view in a db bases on a linked server via SQLCMD. It appears to have an issue creating a view that points back to the first server. I.e. Run SQLCMD from Server 1 to create a view on Server 2. The view on Server 2 points to a table that resides on Server 1.
The linked server is set up and has RPC from/to and data access set
Has anyone seen this before? Any ideas?
Many thanks
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

can't help that much yet;

you didn't mention any specific error you get back, and it might be nice to see the actual command you are using in sqlcmd to create the view;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

also, to create a view on Server 2, FROMserver 1, you need to do one of two things:

either the SQLcmd needs to actually connect to server 2, and not server 1

--sqlcmd -H<Host name if on different machine> -S<SQL server name\instance name> -U<username if needed> -P<password> -E -i<input file for execution> -o<output file for results>

sqlcmd -HServer2 -S<SQL server name\instance name> -U<username if needed> -P<password> -E -i<input file for execution> -o<output file for results>

or

you need to use a command featuring EXECUTE AT on server 1

example:

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'

GO

EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl

(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 3

Hi,

Thanks for the response and apologies about the vagueness. I'll try to be a bit more specific.

The SQLCmd is getting the sql from a networked file using:

sqlcmd -S [Server2] -d [DBName] -b -i "\\NetworkScriptLoc\testview.sql"

The testview.sql just contains

If not exist...

Create view as

Select c2, etc from Server1.DbName.dbo.TableName

and the error is the old favourite - "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors". However, other scripts creating other objects work even when this fails and they are all using the same technique and service account. The only difference is that the view has a reference back to the server initiating the request.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

arnipetursson SSCertifiable Points: 6557 More actions · Answer 4

Looks like you are running into the "double hop" problem.