Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

View creation on Linked Server Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 3:50 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:59 PM
Points: 179, Visits: 827
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
Post #1452026
Posted Monday, May 13, 2013 5:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1452058
Posted Monday, May 13, 2013 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1452073
Posted Monday, May 13, 2013 6:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:59 PM
Points: 179, Visits: 827
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
Post #1452075
Posted Monday, May 13, 2013 9:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 4:56 PM
Points: 282, Visits: 887
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
Post #1452171
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse