View Message Thread (13 replies)
| Linked server query |
 |
From: hayko98
Date Posted: 7/26/2010 4:40:00 PM
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
Date Posted: 7/27/2010 12:10:00 AM
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
Date Posted: 7/27/2010 1:07:00 PM
On Jul 26, 11:07 pm, Jeroen Mostert < jmost...@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
Date Posted: 7/27/2010 3:46:00 PM
hayko98 ( vardan.hakopian@gmail.com) writes:
> On Jul 26, 11:07 pm, Jeroen Mostert < jmost...@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@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
Date Posted: 7/28/2010 10:56:00 AM
On Jul 27, 2:43 pm, Erland Sommarskog < esq...@sommarskog.se> wrote:
> hayko98 ( vardan.hakop...@gmail.com) writes:
> > On Jul 26, 11:07 pm, Jeroen Mostert < jmost...@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...@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.
From: hayko98
Date Posted: 7/28/2010 11:29:00 AM
On Jul 28, 9:53 am, hayko98 < vardan.hakop...@gmail.com> wrote:
> On Jul 27, 2:43 pm, Erland Sommarskog < esq...@sommarskog.se> wrote:
>
>
>
>
>
> > hayko98 ( vardan.hakop...@gmail.com) writes:
> > > On Jul 26, 11:07 pm, Jeroen Mostert < jmost...@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...@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.- Hide quoted text -
>
> - Show quoted text -
One more thing.I have SQL Promt (from RED Gate) instaled and just
recived this warrning massage:
Server ' PRD_SERVER is not configured for DATA ACCESS.
SQL Promt could not retrive all schema information for PRD_SERVER
How do i configure for DATA ACCESS?
From: hayko98
Date Posted: 7/28/2010 11:34:00 AM
On Jul 28, 10:26 am, hayko98 < vardan.hakop...@gmail.com> wrote:
> On Jul 28, 9:53 am, hayko98 < vardan.hakop...@gmail.com> wrote:
>
>
>
>
>
> > On Jul 27, 2:43 pm, Erland Sommarskog < esq...@sommarskog.se> wrote:
>
> > > hayko98 ( vardan.hakop...@gmail.com) writes:
> > > > On Jul 26, 11:07 pm, Jeroen Mostert < jmost...@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...@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.- Hide quoted text -
>
> > - Show quoted text -
>
> One more thing.I have SQL Promt (from RED Gate) instaled and just
> recived this warrning massage:
>
> Server ' PRD_SERVER is not configured for DATA ACCESS.
> SQL Promt could not retrive all schema information for PRD_SERVER
>
> How do i configure for DATA ACCESS?- Hide quoted text -
>
> - Show quoted text -
Sorry a lot of misspelled words
One more thing. I have SQL Prompt (from RED Gate) installed and just
received this warning massage:
Server ' PRD_SERVER is not configured for DATA ACCESS.
SQL Prompt could not retrieve all schema information for PRD_SERVER
How do I configure for DATA ACCESS?
From: Erland Sommarskog
Date Posted: 7/28/2010 3:58:00 PM
From: hayko98
Date Posted: 7/28/2010 4:14:00 PM
From: Erland Sommarskog
Date Posted: 7/29/2010 3:01:00 AM
hayko98 ( vardan.hakopian@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, esquel@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
Would you like to track this thread?
By adding this News Thread to your Favorites Area you can refer to it later with just a click of the mouse.
Also you can optionally be notified instantly whenever there are any replies
posted to this Thread. To add this Thread to your Favorites Area just click on the red arrow next to the subject of the thread above
 .
|
