Consolidation Query: live

Showing posts with label live. Show all posts

Wednesday, March 7, 2012

Continue SP after Database Access Failure

Over night we take a copy of various live SQL databases onto another SQL
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:

> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:

> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>

Saturday, February 25, 2012

CONTAINS search not working on live server

Friday, February 24, 2012

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
JamesIt is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||has a full population been done?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > I'm dealing with a company that have 3 web servers a test, staging and
> live.
> > All these servers are sql server 2000 and are situated offsite. I do not
> > have dirrect access to them, I have to send them email with scripts when
> > things need changing.
> >
> > The website works on the test and the staging but for some reason the
> search
> > doesn't work on the live server. The search uses CONTAINS and the
catalog
> > seem to be set up correctly on all three servers. The search works in
the
> > sence that it doesn't error but all it returns is zero rows.
> >
> > Any clues?
> >
> > Cheers
> > James
> >
> >
>|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > has a full population been done?
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > I'm dealing with a company that have 3 web servers a test, staging and
> > live.
> > > All these servers are sql server 2000 and are situated offsite. I do
not
> > > have dirrect access to them, I have to send them email with scripts
when
> > > things need changing.
> > >
> > > The website works on the test and the staging but for some reason the
> > search
> > > doesn't work on the live server. The search uses CONTAINS and the
> catalog
> > > seem to be set up correctly on all three servers. The search works in
> the
> > > sence that it doesn't error but all it returns is zero rows.
> > >
> > > Any clues?
> > >
> > > Cheers
> > > James
> > >
> > >
> >
> >
>|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> > I've been assured it has.
> >
> > Is there a system query that will tell me the number of rows in the
> catalog?
> >
> > Cheers
> > James
> >
> > "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> > news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > has a full population been done?
> > >
> > > --
> > > Hilary Cotter
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > >
> > > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > Hi
> > > >
> > > > I'm dealing with a company that have 3 web servers a test, staging
and
> > > live.
> > > > All these servers are sql server 2000 and are situated offsite. I do
> not
> > > > have dirrect access to them, I have to send them email with scripts
> when
> > > > things need changing.
> > > >
> > > > The website works on the test and the staging but for some reason
the
> > > search
> > > > doesn't work on the live server. The search uses CONTAINS and the
> > catalog
> > > > seem to be set up correctly on all three servers. The search works
in
> > the
> > > > sence that it doesn't error but all it returns is zero rows.
> > > >
> > > > Any clues?
> > > >
> > > > Cheers
> > > > James
> > > >
> > > >
> > >
> > >
> >
> >
>|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:
> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> > try this
> >
> > select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
> >
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> > > I've been assured it has.
> > >
> > > Is there a system query that will tell me the number of rows in the
> > catalog?
> > >
> > > Cheers
> > > James
> > >
> > > "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> > > news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > > has a full population been done?
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > >
> > > > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > > > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > > Hi
> > > > >
> > > > > I'm dealing with a company that have 3 web servers a test, staging
> and
> > > > live.
> > > > > All these servers are sql server 2000 and are situated offsite. I do
> > not
> > > > > have dirrect access to them, I have to send them email with scripts
> > when
> > > > > things need changing.
> > > > >
> > > > > The website works on the test and the staging but for some reason
> the
> > > > search
> > > > > doesn't work on the live server. The search uses CONTAINS and the
> > > catalog
> > > > > seem to be set up correctly on all three servers. The search works
> in
> > > the
> > > > > sence that it doesn't error but all it returns is zero rows.
> > > > >
> > > > > Any clues?
> > > > >
> > > > > Cheers
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Wednesday, March 7, 2012

Continue SP after Database Access Failure

Saturday, February 25, 2012

CONTAINS search not working on live server

CONTAINS search not working on live server

Friday, February 24, 2012

CONTAINS search not working on live server

CONTAINS search not working on live server

Consolidation Query

Blog Archive

About Me