View creation on Linked Server

  • 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

  • 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!

  • 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!

  • 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

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

    Suggested readings:

    http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

    http://www.sqlservercentral.com/Forums/Topic1043330-359-1.aspx

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

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