Prev: Pivot Query Help
Next: Rebuild indexes via MP question?
From: hayko98 on 28 Jul 2010 13:26
On Jul 28, 9:53 am, hayko98 <vardan.hakop...(a)gmail.com> wrote:
> 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.- 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 on 28 Jul 2010 13:32
On Jul 28, 10:26 am, hayko98 <vardan.hakop...(a)gmail.com> wrote:
> On Jul 28, 9:53 am, hayko98 <vardan.hakop...(a)gmail.com> wrote:
>
>
>
>
>
> > 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.- 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 on 28 Jul 2010 17:52
hayko98 (vardan.hakopian(a)gmail.com) writes:
> 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?

With sp_serveroption. But I think we are getting close to the issue now.

Please run your queries again from SQLCMD (open from the command line.)

If that returns the results from the production server, run again from your
query window, but now with SQL Prompt disabled.



--
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 18:12
On Jul 28, 2:52 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> hayko98 (vardan.hakop...(a)gmail.com) writes:
> > 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?
>
> With sp_serveroption. But I think we are getting close to the issue now.
>
> Please run your queries again from SQLCMD (open from the command line.)
>
> If that returns the results from the production server, run again from your
> query window, but now with SQL Prompt disabled.
>
> --
> 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

Same results.It returns from Dev server again
From: Erland Sommarskog on 29 Jul 2010 04:59
hayko98 (vardan.hakopian(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, 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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Pivot Query Help
Next: Rebuild indexes via MP question?