Linked server query
in [Server Configuration]
|
Prev: Pivot Query Help
Next: Rebuild indexes via MP question?
From: hayko98 on 29 Jul 2010 13:21 On Jul 29, 1:59 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > hayko98 (vardan.hakop...(a)gmail.com) writes: > > Same results.It returns from Dev server again > > OK. I thought that maybe SQL Prompt was messing things up. > > But I see now in a previous reply a thing I missed. You said: > > I run your script at PRD_SERVER and it returned PRD_SERVER. > > Then you did not follow the instructions. You were supposed to run the > command: > > EXEC('SELECT @@servername') AT PRD_SERVER > > on the DEV_SERVER. The EXEC() syntax permits you to run a command on a > linked server. You can also try: > > SELECT * FROM OPENQUERY(PRD_SERVER, 'SELECT @@servername') > > Also to be run on the DEV_SERVER. > > In any case, I would suggest that you should drop the definition of > PRD_SERVER on the DEV_SERVER and add it back: > > EXEC sp_droplinkedserver 'PRD_SERVER' > EXEC sp_droplinkedserver 'DEV_SERVER' > > You may also have to drop and recreate linked-server mappings. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I run both scripts on Dev server EXEC('SELECT @@servername') AT [PRD_SERVER] SELECT * FROM OPENQUERY( [PRD_SERVER], 'SELECT @@servername') Results: DEV_SERVER DEV_SERVER And when I tried to run EXEC sp_droplinkedserver 'PRD_SERVER' EXEC sp_droplinkedserver 'DEV_SERVER' Result: Could not find stored procedure 'sp_droplinkedserver'. Then I used EXEC sp_dropserver 'PRD_SERVER' Result: The server '[PRD_SERVER]' does not exist. Then I run sp_helpserver on DEV_SERVER: Name Network Name Status Id ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ PRD_SERVER NULL rpc,rpc out,data access,use remote collation 1 NULL 0 0 DEV_SERVER DEV_SERVER rpc,rpc out,use remote collation 0 NULL 0 0
From: Erland Sommarskog on 29 Jul 2010 17:07 hayko98 (vardan.hakopian(a)gmail.com) writes: > I run both scripts on Dev server > EXEC('SELECT @@servername') AT [PRD_SERVER] > SELECT * FROM OPENQUERY( [PRD_SERVER], 'SELECT @@servername') > > Results: > DEV_SERVER > DEV_SERVER Thus, the definition of PRD_SERVER is that it is a loopback server, which points back to the development server. Which is also what I have been suspecting for quite a while. > EXEC sp_dropserver 'PRD_SERVER' > > Result: > The server '[PRD_SERVER]' does not exist. Interesting. But maybe what you actually tried was: EXEC sp_dropserver '[PRD_SERVER]' Try it again, now without brackets. > Then I run sp_helpserver on DEV_SERVER: > > Name Network Name > Status Id > -------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------ > PRD_SERVER NULL rpc,rpc out,data access,use remote > collation 1 NULL 0 0 > DEV_SERVER DEV_SERVER rpc,rpc out,use remote > collation 0 NULL 0 0 There should definitely be somehing under Network Name; the fact that there is NULL, means this is a loopback server. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: hayko98 on 29 Jul 2010 18:20 On Jul 29, 2:07 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > hayko98 (vardan.hakop...(a)gmail.com) writes: > > I run both scripts on Dev server > > EXEC('SELECT @@servername') AT [PRD_SERVER] > > SELECT * FROM OPENQUERY( [PRD_SERVER], 'SELECT @@servername') > > > Results: > > DEV_SERVER > > DEV_SERVER > > Thus, the definition of PRD_SERVER is that it is a loopback server, which > points back to the development server. Which is also what I have been > suspecting for quite a while. > > > EXEC sp_dropserver 'PRD_SERVER' > > > Result: > > The server '[PRD_SERVER]' does not exist. > > Interesting. But maybe what you actually tried was: > > EXEC sp_dropserver '[PRD_SERVER]' > > Try it again, now without brackets. > > > Then I run sp_helpserver on DEV_SERVER: > > > Name Network Name > > Status Id > > -------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > ------------------------------ > > > PRD_SERVER NULL rpc,rpc out,data access,use remote > > collation 1 NULL 0 0 > > DEV_SERVER DEV_SERVER rpc,rpc out,use remote > > collation 0 NULL 0 0 > > There should definitely be somehing under Network Name; the fact that there > is NULL, means this is a loopback server. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank You Erland very much for your time and affort that you put to solve this issue.I will post the results of my upcoming research.Thank You again
From: hayko98 on 30 Jul 2010 13:21
On Jul 29, 3:20 pm, hayko98 <vardan.hakop...(a)gmail.com> wrote: > On Jul 29, 2:07 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > > > > > > hayko98 (vardan.hakop...(a)gmail.com) writes: > > > I run both scripts on Dev server > > > EXEC('SELECT @@servername') AT [PRD_SERVER] > > > SELECT * FROM OPENQUERY( [PRD_SERVER], 'SELECT @@servername') > > > > Results: > > > DEV_SERVER > > > DEV_SERVER > > > Thus, the definition of PRD_SERVER is that it is a loopback server, which > > points back to the development server. Which is also what I have been > > suspecting for quite a while. > > > > EXEC sp_dropserver 'PRD_SERVER' > > > > Result: > > > The server '[PRD_SERVER]' does not exist. > > > Interesting. But maybe what you actually tried was: > > > EXEC sp_dropserver '[PRD_SERVER]' > > > Try it again, now without brackets. > > > > Then I run sp_helpserver on DEV_SERVER: > > > > Name Network Name > > > Status Id > > > -------------------------------------------------------------------------- > > > ---------------------------------------------------------------------------- > > ------------------------------ > > > > PRD_SERVER NULL rpc,rpc out,data access,use remote > > > collation 1 NULL 0 0 > > > DEV_SERVER DEV_SERVER rpc,rpc out,use remote > > > collation 0 NULL 0 0 > > > There should definitely be somehing under Network Name; the fact that there > > is NULL, means this is a loopback server. > > > -- > > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > > Links for SQL Server Books Online: > > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Thank You Erland very much for your time and affort that you put to > solve this issue.I will post the results of my upcoming research.Thank > You again- Hide quoted text - > > - Show quoted text - Hi Erland, After 5 days of nightmare I got it. One of suggestions was drop and recreate linked server, which I did and did not get results that I was looking for. Then I decided to restart the DEV server .Then I dropped again and recreated new linked server. BINGO!!! It worked. Thank you again. |