Linked server query
in [Server Configuration]
|
Prev: Pivot Query Help
Next: Rebuild indexes via MP question?
From: hayko98 on 26 Jul 2010 18:34 Hi, We have 2 servers: PRD and DEV (PRD has 2 extra weeks of data).This is my query: SELECT COUNT(*) FROM PRD_SERVER_name.PRD_DB_name.dbo.PRD_TABLE_name. When I connect to DEV Server and run this query, I am getting results of DEV_DB even thought my query points to PRD Server. Anybody can explain why is this happening? Thank You
From: Jeroen Mostert on 27 Jul 2010 02:07 On 2010-07-27 00:34, hayko98 wrote: > We have 2 servers: PRD and DEV (PRD has 2 extra weeks of data).This is > my query: > SELECT COUNT(*) FROM PRD_SERVER_name.PRD_DB_name.dbo.PRD_TABLE_name. > > When I connect to DEV Server and run this query, I am getting results > of DEV_DB even thought my query points to PRD Server. Anybody can > explain why is this happening? > Check the definition of the linked server "PRD_SERVER_name". It could have been accidentally created pointing to DEV. If that's not it, check your DNS or the hosts file of the server in question to see if the server name resolves properly. If that's not it, check for a really stupid mistake, like executing the wrong query. :-) -- J.
From: hayko98 on 27 Jul 2010 15:04 On Jul 26, 11:07 pm, Jeroen Mostert <jmost...(a)xs4all.nl> wrote: > On 2010-07-27 00:34, hayko98 wrote:> We have 2 servers: PRD and DEV (PRD has 2 extra weeks of data).This is > > my query: > > SELECT COUNT(*) FROM PRD_SERVER_name.PRD_DB_name.dbo.PRD_TABLE_name. > > > When I connect to DEV Server and run this query, I am getting results > > of DEV_DB even thought my query points to PRD Server. Anybody can > > explain why is this happening? > > Check the definition of the linked server "PRD_SERVER_name". It could have > been accidentally created pointing to DEV. If that's not it, check your DNS > or the hosts file of the server in question to see if the server name > resolves properly. If that's not it, check for a really stupid mistake, like > executing the wrong query. :-) > > -- > J. Thank You for responding.I check all your sugestions.They are all OK.I don't know what else to check.
From: Erland Sommarskog on 27 Jul 2010 17:43 hayko98 (vardan.hakopian(a)gmail.com) writes: > On Jul 26, 11:07�pm, Jeroen Mostert <jmost...(a)xs4all.nl> wrote: >> On 2010-07-27 00:34, hayko98 wrote >> > We have 2 servers: PRD and DEV (PRD has 2 extra weeks of data).This is >> > my query: >> > SELECT COUNT(*) FROM PRD_SERVER_name.PRD_DB_name.dbo.PRD_TABLE_name. >> > >> > When I connect to DEV Server and run this query, I am getting results >> > of DEV_DB even thought my query points to PRD Server. Anybody can >> > explain why is this happening? >>... > > Thank You for responding.I check all your sugestions.They are all OK.I > don't know what else to check. Run this command: EXEC('SELECT @@servername') AT PRD_SERVER If it now returns the name of the DEV server, the definition of PRD_SERVER leads back to the DEV server. If it returns PRD_SERVER, you have a copy of the development database on the production server. You may also be doing something really silly, but which we can spot, since you have obfuscated the names. -- 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 28 Jul 2010 12:53
On Jul 27, 2:43 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > hayko98 (vardan.hakop...(a)gmail.com) writes: > > On Jul 26, 11:07 pm, Jeroen Mostert <jmost...(a)xs4all.nl> wrote: > >> On 2010-07-27 00:34, hayko98 wrote > >> > We have 2 servers: PRD and DEV (PRD has 2 extra weeks of data).This is > >> > my query: > >> > SELECT COUNT(*) FROM PRD_SERVER_name.PRD_DB_name.dbo.PRD_TABLE_name. > > >> > When I connect to DEV Server and run this query, I am getting results > >> > of DEV_DB even thought my query points to PRD Server. Anybody can > >> > explain why is this happening? > >>... > > > Thank You for responding.I check all your sugestions.They are all OK.I > > don't know what else to check. > > Run this command: > > EXEC('SELECT @@servername') AT PRD_SERVER > > If it now returns the name of the DEV server, the definition of PRD_SERVER > leads back to the DEV server. > > If it returns PRD_SERVER, you have a copy of the development database on the > production server. > > You may also be doing something really silly, but which we can spot, since > you have obfuscated the names. > > -- > 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 your script at PRD_SERVER and it returned PRD_SERVER. My PRD_SERVER has 3 databases:PRD_DB1,PRD_DB2 and PRD_DB3.My DEV_SERVER has about 20 databases(2 of them we named PRD_DB1 and PRD_DB2).I tested my query couple of time and here are results: 1.SELECT COUNT(*) FROM PRD_SERVER.PRD_DB1.dbo.PRD_TABLE. Results from DEV_SEREVR's PRD_DB1 2.SELECT COUNT(*) FROM PRD_SERVER.PRD_DB2.dbo.PRD_TABLE. Results from DEV_SEREVR's PRD_DB2 3.SELECT COUNT(*) FROM PRD_SERVER.PRD_DB3.dbo.PRD_TABLE. Invalid object name PRD_SERVER.PRD_DB3.Msg 208, Level 16, State 1, Line 1 #3 Did not run becuse DEV_SERVER does not have PRD_DB3. |