I was using following query
select top 10 * from [sqlserver02\sqlserver02].mydatabase.dbo.aspnet_membership (nolock)
whereas “sqlserver02″ was a remote serever and I found following errro
Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.
Then I remove (nolock) from the query and it worked.
Does any one know why remote serever doesn’t allow (nolock)?
Accessing Different Database on Different SQL Servers
I was trying to Access two databases on two different servers. I was usring four part name to SQlServer
“[sqlserver02].MyDatabase.dbo.aspnet_membership” but it was not letting me do that and was showing following error message.
Msg 7202, Level 11, State 2, Line 1
Could not find server ’sqlserver02′ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Though server was present physically and name was 100% correct. You might think I must be using four part sql server name like this “[sqlserver02\sqlserver02].MyDatabase.dbo.aspnet_membership”.
I tried above but it didn’t work, I googled it and found I should if “sqlserver02″ is being added to my sys.servers I tried to add using
Following Stored procedure which adds the server in sys.servers
sp_addserver ’sqlserver02′,’local’
and I got the message
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server ’sqlserver02′ already exists.
Then I used following select query
Select * from sys.servers
which returned all servers and I found that sqlserver02 name was different it was sqlserver02_Alias. I used “[sqlserver02_Alias].MyDatabase.dbo.aspnet_membership” and everthing worked fine.