Sql Server Alignment problem: fail

Showing posts with label fail. Show all posts

Thursday, March 29, 2012

Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I call
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa0400225912014:57:14.380
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa03017225912014:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg Holmes
You might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes
|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do you
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it go
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did was
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it go
> up by 2s. I also added an auto incrementing field to the table and you can
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>
|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attention
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>
|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
|||No, now that I'm using a text query in ASP.NET instead of a stored procedure,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>

Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I cal
l
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg HolmesYou might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do yo
u
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it g
o
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did wa
s
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it
go
> up by 2s. I also added an auto incrementing field to the table and you ca
n
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
>|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attentio
n
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
>|||No, now that I'm using a text query in ASP.NET instead of a stored procedure
,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for
).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column
.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>sql

Thursday, February 9, 2012

All it takes is one noise word to ruin query!

The presence of a single noise word is causing query to fail with this
error: "Execution of a full-text operation failed. A clause of the query
contained only ignored words." But the full text clause contains three
words and only one of them is a noise word. Please see below. Is this a
known problem?
Thanks
Timo
QUERY ONE EXCERPT:
This succeeds:
declare @.keywordclause varchar(2000)
set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
FORMSOF(INFLECTIONAL,risk)'
...
{select statement}
where contains(mycolumn,@.keywordclause) or contains(myothercolumn,
@.keywordclause)
QUERY TWO EXCERPT
This fails:
declare @.keywordclause varchar(2000)
set @.keywordclause= 'FORMSOF(INFLECTIONAL,disease) AND
FORMSOF(INFLECTIONAL,risk) AND FORMSOF(INFLECTIONAL,your)'
...
where contains(mycolumn, @.keywordclause) or contains(myothercolumn,
@.keywordclause)
Timo,
Unfortunately, yes this is a well known and a "by design" issue. There are
several workarounds, one is to remove all noise words (except for a single
space) from your language-specific noise file (US English = noise.enu) and
run a full population. Another is to encapsulate your search strings in
double quotes as this will allow the noise word to be truly ignored. For
more information on this see, SQL 2000 BOL topic "Full-text Search
Recommendations" and KB article 246800 (Q246800) "INF: Correctly Parsing
Quotation Marks in FTS Queries" at
http://support.microsoft.com//defaul...b;EN-US;246800 for more
details.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Timo" <Timo@.unspam.biz> wrote in message
news:#hK$w7EcFHA.1568@.TK2MSFTNGP10.phx.gbl...
> The presence of a single noise word is causing query to fail with this
> error: "Execution of a full-text operation failed. A clause of the query
> contained only ignored words." But the full text clause contains three
> words and only one of them is a noise word. Please see below. Is this a
> known problem?
> Thanks
> Timo
>
> QUERY ONE EXCERPT:
> This succeeds:
> declare @.keywordclause varchar(2000)
> set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk)'
> ...
> {select statement}
> where contains(mycolumn,@.keywordclause) or contains(myothercolumn,
> @.keywordclause)
> QUERY TWO EXCERPT
> This fails:
> declare @.keywordclause varchar(2000)
> set @.keywordclause= 'FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk) AND FORMSOF(INFLECTIONAL,your)'
> ...
> where contains(mycolumn, @.keywordclause) or contains(myothercolumn,
> @.keywordclause)
>
|||Thanks for the reply, John, but I don't understand yet. Perhaps I'm
misreading the KB article, or it could be that the KB article does not
reflect the MSFT code base; it reads:
"Notice that proximity, generation, and weighted terms do not use double
quotation marks."
Here's the algorithm excerpted verbatim from the article:
Replace all double quotes (clears the text and any improper quotations)
If the text string contains one of the key words "NEAR", "FORMSOF", or
"ISABOUT", the parsing is complete
Else
Surround any instances of 'and' or 'and not' with quotes
Surround any instances of 'or' or 'or not' with quotes
Surround the entire string with quotesI'm using the FORMSOF generation
function:
declare @.keywordclause varchar(2000)
set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
FORMSOF(INFLECTIONAL,risk)'
So I *shouldn't* be using double-quotation marks, right?
My workaround has been to create my own noisewords table and eliminate from
@.keywordclause any term that appears in my noisewords table.
Regards
Timo
"John Kane" <jt-kane@.comcast.net> wrote in message
news:#qdAANIcFHA.2768@.tk2msftngp13.phx.gbl...
> Timo,
> Unfortunately, yes this is a well known and a "by design" issue. There are
> several workarounds, one is to remove all noise words (except for a single
> space) from your language-specific noise file (US English = noise.enu) and
> run a full population. Another is to encapsulate your search strings in
> double quotes as this will allow the noise word to be truly ignored. For
> more information on this see, SQL 2000 BOL topic "Full-text Search
> Recommendations" and KB article 246800 (Q246800) "INF: Correctly Parsing
> Quotation Marks in FTS Queries" at
> http://support.microsoft.com//defaul...b;EN-US;246800 for more
> details.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Timo" <Timo@.unspam.biz> wrote in message
> news:#hK$w7EcFHA.1568@.TK2MSFTNGP10.phx.gbl...
>
|||Timo,
Both single and double quotes are useful depending upon your requirements.
As for double quotes, I was referring to the following in "Full-text Search
Recommendations":
You also may encounter Error 7619, "The query contained only ignored words"
when using any of the full-text predicates in a full-text query, such as
CONTAINS(pr_info, 'between AND king'). The word "between" is an ignored or
noise word and the full-text query parser considers this an error, even with
an OR clause. Consider rewriting this query to a phrase-based query, ...
Specifically, note the different results from the above re-written query:
select * from pub_info where CONTAINS(pr_info, 'between AND king')
-- returns: Execution of a full-text operation failed. A clause of the query
contained only ignored words.
select * from pub_info where CONTAINS(pr_info, '"between AND king"')
-- returns: 0 rows as king is not in the pub_info table, and no error.
Your solution also works as there are many means to the same solution!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Timo" <Timo@.spamspamspam.org> wrote in message
news:#q2sV2McFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply, John, but I don't understand yet. Perhaps I'm
> misreading the KB article, or it could be that the KB article does not
> reflect the MSFT code base; it reads:
> "Notice that proximity, generation, and weighted terms do not use double
> quotation marks."
> Here's the algorithm excerpted verbatim from the article:
> Replace all double quotes (clears the text and any improper quotations)
> If the text string contains one of the key words "NEAR", "FORMSOF", or
> "ISABOUT", the parsing is complete
> Else
> Surround any instances of 'and' or 'and not' with quotes
> Surround any instances of 'or' or 'or not' with quotes
> Surround the entire string with quotesI'm using the FORMSOF
generation
> function:
> declare @.keywordclause varchar(2000)
> set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk)'
> So I *shouldn't* be using double-quotation marks, right?
> My workaround has been to create my own noisewords table and eliminate
from[vbcol=seagreen]
> @.keywordclause any term that appears in my noisewords table.
> Regards
> Timo
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:#qdAANIcFHA.2768@.tk2msftngp13.phx.gbl...
are[vbcol=seagreen]
single[vbcol=seagreen]
and[vbcol=seagreen]
query[vbcol=seagreen]
three[vbcol=seagreen]
a
>
|||you might want to check out
http://www.indexserverfaq.com/noise.htm
The forms of inflectional search is functionally equivalent to a freetext
search. You might get what you are looking for using FreeText search and
this query won't bomb if a noise word is in your search condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Timo" <Timo@.unspam.biz> wrote in message
news:%23hK$w7EcFHA.1568@.TK2MSFTNGP10.phx.gbl...
> The presence of a single noise word is causing query to fail with this
> error: "Execution of a full-text operation failed. A clause of the query
> contained only ignored words." But the full text clause contains three
> words and only one of them is a noise word. Please see below. Is this a
> known problem?
> Thanks
> Timo
>
> QUERY ONE EXCERPT:
> This succeeds:
> declare @.keywordclause varchar(2000)
> set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk)'
> ...
> {select statement}
> where contains(mycolumn,@.keywordclause) or contains(myothercolumn,
> @.keywordclause)
> QUERY TWO EXCERPT
> This fails:
> declare @.keywordclause varchar(2000)
> set @.keywordclause= 'FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk) AND FORMSOF(INFLECTIONAL,your)'
> ...
> where contains(mycolumn, @.keywordclause) or contains(myothercolumn,
> @.keywordclause)
>
|||Thanks for the link, Hilary. We've opted to strip the noiswords out of the
search argument using TSQL with own noisewords table. So far, the parsing
doesn't seem to be taxing the CPU. Perhaps the page you sent me to could add
a YMMV disclaimer instead of panning this approach in such absolute terms
;-)
Timo
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ejECfeQcFHA.228@.TK2MSFTNGP12.phx.gbl...
> you might want to check out
> http://www.indexserverfaq.com/noise.htm
> The forms of inflectional search is functionally equivalent to a freetext
> search. You might get what you are looking for using FreeText search and
> this query won't bomb if a noise word is in your search condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
|||Your point is noted! Its not an application breaker, but you will get
better performance by doing this client side.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Timo" <Timo@.spamspamspam.org> wrote in message
news:O1IZpgacFHA.1152@.tk2msftngp13.phx.gbl...
> Thanks for the link, Hilary. We've opted to strip the noiswords out of
> the
> search argument using TSQL with own noisewords table. So far, the parsing
> doesn't seem to be taxing the CPU. Perhaps the page you sent me to could
> add
> a YMMV disclaimer instead of panning this approach in such absolute terms
> ;-)
> Timo
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ejECfeQcFHA.228@.TK2MSFTNGP12.phx.gbl...
>

All DB maintenance plans fail

MS SQL 2000 SP3 on a Windows 2003 SP1 Server. Both SQL services are running
under an Administrator account. The database maintenance plans to backup
the server databases were also created when that account was logged onto the
server.
When I originally setup the maintenance plan backups with the wizard they
worked fine, but now they do not. I have repeatedly deleted them and tried
other domain admin accounts and local admin accounts without any luck. When
I review the log under the jobs it says access denied. I have attached the
DB Maintenace Plan......txt log data to show what it is recording.
===========================
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'DATABASE' as 'MYDOMAIN\Administrator' (trusted)
Starting maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:06:04 AM
[1] Database Manufacturing Tracking: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 54 secs **
[2] Database Northwind: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[3] Database pubs: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'QualityProposed'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[4] Database QualityProposed: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'QualityTracking'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[5] Database QualityTracking: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[6] Database Manufacturing Tracking: Database Backup...
Destination: [\\.\Tape0]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC SQL
Server Driver][SQL Server]Access is denied due to a password failure
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[7] Database Northwind: Database Backup...
Destination: [\\.\Tape0]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC SQL
Server Driver][SQL Server]Access is denied due to a password failure
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[8] Database pubs: Database Backup...
Destination: [\\.\Tape0]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC SQL
Server Driver][SQL Server]Access is denied due to a password failure
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[9] Database QualityProposed: Database Backup...
The backup was not performed since data verification errors were found.
[10] Database QualityTracking: Database Backup...
The backup was not performed since data verification errors were found.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:08:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Jordon,
Sounds like you are trying to put the database into single user mode which
it doesn't like.
Remove the Check Database Integrity option and you can run the Backup,
Re-index and re-calc stats elements in multi user mode.
Cheers
Andy
"Jordan" <none@.here.com> wrote in message
news:OjgzTJ05GHA.696@.TK2MSFTNGP06.phx.gbl...
> MS SQL 2000 SP3 on a Windows 2003 SP1 Server. Both SQL services are
> running under an Administrator account. The database maintenance plans to
> backup the server databases were also created when that account was logged
> onto the server.
> When I originally setup the maintenance plan backups with the wizard they
> worked fine, but now they do not. I have repeatedly deleted them and
> tried other domain admin accounts and local admin accounts without any
> luck. When I review the log under the jobs it says access denied. I have
> attached the DB Maintenace Plan......txt log data to show what it is
> recording.
>
> ===========================> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'DATABASE' as 'MYDOMAIN\Administrator' (trusted)
> Starting maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:06:04 AM
> [1] Database Manufacturing Tracking: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 54 secs **
> [2] Database Northwind: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [3] Database pubs: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'QualityProposed'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database QualityProposed: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'QualityTracking'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [5] Database QualityTracking: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [6] Database Manufacturing Tracking: Database Backup...
> Destination: [\\.\Tape0]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Access is denied due to a password failure
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [7] Database Northwind: Database Backup...
> Destination: [\\.\Tape0]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Access is denied due to a password failure
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [8] Database pubs: Database Backup...
> Destination: [\\.\Tape0]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Access is denied due to a password failure
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [9] Database QualityProposed: Database Backup...
> The backup was not performed since data verification errors were found.
> [10] Database QualityTracking: Database Backup...
> The backup was not performed since data verification errors were found.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:08:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>|||I have also tried to break up all the actions into single jobs. For
instance on the backups I had the maintenance schedule just backup the
non-system databases at 2 in the morning and they still failed.
"Andrew Copeland" <andycuk@.community.nospam> wrote in message
news:%23oH4ud05GHA.4608@.TK2MSFTNGP03.phx.gbl...
> Jordon,
> Sounds like you are trying to put the database into single user mode which
> it doesn't like.
> Remove the Check Database Integrity option and you can run the Backup,
> Re-index and re-calc stats elements in multi user mode.
> Cheers
> Andy
> "Jordan" <none@.here.com> wrote in message
> news:OjgzTJ05GHA.696@.TK2MSFTNGP06.phx.gbl...
>> MS SQL 2000 SP3 on a Windows 2003 SP1 Server. Both SQL services are
>> running under an Administrator account. The database maintenance plans
>> to backup the server databases were also created when that account was
>> logged onto the server.
>> When I originally setup the maintenance plan backups with the wizard they
>> worked fine, but now they do not. I have repeatedly deleted them and
>> tried other domain admin accounts and local admin accounts without any
>> luck. When I review the log under the jobs it says access denied. I
>> have attached the DB Maintenace Plan......txt log data to show what it
>> is recording.
>>
>> ===========================>> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> 'DATABASE' as 'MYDOMAIN\Administrator' (trusted)
>> Starting maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:06:04 AM
>> [1] Database Manufacturing Tracking: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 54 secs **
>> [2] Database Northwind: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [3] Database pubs: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'QualityProposed'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [4] Database QualityProposed: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'QualityTracking'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [5] Database QualityTracking: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [6] Database Manufacturing Tracking: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [7] Database Northwind: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [8] Database pubs: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [9] Database QualityProposed: Database Backup...
>> The backup was not performed since data verification errors were
>> found.
>> [10] Database QualityTracking: Database Backup...
>> The backup was not performed since data verification errors were
>> found.
>> Deleting old text reports... 0 file(s) deleted.
>> End of maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:08:02 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>|||Jordon,
Your order database is still in use so you really need to ensure that all
actions are stopped as the ALTER DATABASE command issued here needs to
change the database into single user mode.
Can you check the monitor to see what is using the database out of hours?
Regards
Andy
"Jordan" <none@.here.com> wrote in message
news:e4YwwaB6GHA.3732@.TK2MSFTNGP05.phx.gbl...
>I have also tried to break up all the actions into single jobs. For
>instance on the backups I had the maintenance schedule just backup the
>non-system databases at 2 in the morning and they still failed.
>
> "Andrew Copeland" <andycuk@.community.nospam> wrote in message
> news:%23oH4ud05GHA.4608@.TK2MSFTNGP03.phx.gbl...
>> Jordon,
>> Sounds like you are trying to put the database into single user mode
>> which it doesn't like.
>> Remove the Check Database Integrity option and you can run the Backup,
>> Re-index and re-calc stats elements in multi user mode.
>> Cheers
>> Andy
>> "Jordan" <none@.here.com> wrote in message
>> news:OjgzTJ05GHA.696@.TK2MSFTNGP06.phx.gbl...
>> MS SQL 2000 SP3 on a Windows 2003 SP1 Server. Both SQL services are
>> running under an Administrator account. The database maintenance plans
>> to backup the server databases were also created when that account was
>> logged onto the server.
>> When I originally setup the maintenance plan backups with the wizard
>> they worked fine, but now they do not. I have repeatedly deleted them
>> and tried other domain admin accounts and local admin accounts without
>> any luck. When I review the log under the jobs it says access denied.
>> I have attached the DB Maintenace Plan......txt log data to show what
>> it is recording.
>>
>> ===========================>> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> Server 'DATABASE' as 'MYDOMAIN\Administrator' (trusted)
>> Starting maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:06:04 AM
>> [1] Database Manufacturing Tracking: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 54 secs **
>> [2] Database Northwind: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [3] Database pubs: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'QualityProposed'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [4] Database QualityProposed: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'QualityTracking'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [5] Database QualityTracking: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [6] Database Manufacturing Tracking: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [7] Database Northwind: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [8] Database pubs: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [9] Database QualityProposed: Database Backup...
>> The backup was not performed since data verification errors were
>> found.
>> [10] Database QualityTracking: Database Backup...
>> The backup was not performed since data verification errors were
>> found.
>> Deleting old text reports... 0 file(s) deleted.
>> End of maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:08:02 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>>
>|||I managed to track it down to the owner name on the tasks being
Administrator@.mydomain.com rather than MYDOMAIN\Administror.
The funny thing is that the services start as MYDOMAIN\Administrator and I
log in as that to manage maintenace plans but the individual tasks in the
plan all have that @.mydomain.com user. Changing these to the x\x format
took care of the problem
Thanks .
"Andrew Copeland" <andycuk@.community.nospam> wrote in message
news:eUkdSfT6GHA.4132@.TK2MSFTNGP05.phx.gbl...
> Jordon,
> Your order database is still in use so you really need to ensure that all
> actions are stopped as the ALTER DATABASE command issued here needs to
> change the database into single user mode.
> Can you check the monitor to see what is using the database out of hours?
> Regards
> Andy
> "Jordan" <none@.here.com> wrote in message
> news:e4YwwaB6GHA.3732@.TK2MSFTNGP05.phx.gbl...
>>I have also tried to break up all the actions into single jobs. For
>>instance on the backups I had the maintenance schedule just backup the
>>non-system databases at 2 in the morning and they still failed.
>>
>> "Andrew Copeland" <andycuk@.community.nospam> wrote in message
>> news:%23oH4ud05GHA.4608@.TK2MSFTNGP03.phx.gbl...
>> Jordon,
>> Sounds like you are trying to put the database into single user mode
>> which it doesn't like.
>> Remove the Check Database Integrity option and you can run the Backup,
>> Re-index and re-calc stats elements in multi user mode.
>> Cheers
>> Andy
>> "Jordan" <none@.here.com> wrote in message
>> news:OjgzTJ05GHA.696@.TK2MSFTNGP06.phx.gbl...
>> MS SQL 2000 SP3 on a Windows 2003 SP1 Server. Both SQL services are
>> running under an Administrator account. The database maintenance plans
>> to backup the server databases were also created when that account was
>> logged onto the server.
>> When I originally setup the maintenance plan backups with the wizard
>> they worked fine, but now they do not. I have repeatedly deleted them
>> and tried other domain admin accounts and local admin accounts without
>> any luck. When I review the log under the jobs it says access denied.
>> I have attached the DB Maintenace Plan......txt log data to show what
>> it is recording.
>>
>> ===========================>> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> Server 'DATABASE' as 'MYDOMAIN\Administrator' (trusted)
>> Starting maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:06:04 AM
>> [1] Database Manufacturing Tracking: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 54 secs **
>> [2] Database Northwind: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [3] Database pubs: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'QualityProposed'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [4] Database QualityProposed: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'QualityTracking'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [5] Database QualityTracking: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [6] Database Manufacturing Tracking: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password
>> failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [7] Database Northwind: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password
>> failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [8] Database pubs: Database Backup...
>> Destination: [\\.\Tape0]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Access is denied due to a password
>> failure
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [9] Database QualityProposed: Database Backup...
>> The backup was not performed since data verification errors were
>> found.
>> [10] Database QualityTracking: Database Backup...
>> The backup was not performed since data verification errors were
>> found.
>> Deleting old text reports... 0 file(s) deleted.
>> End of maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:08:02 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>>
>>
>

Thursday, March 29, 2012

Alternating INSERTs fail

Alternating INSERTs fail

Thursday, February 9, 2012

All it takes is one noise word to ruin query!

All DB maintenance plans fail

All DB maintenance plans fail

All DB maintenance plans fail

Sql Server Alignment problem

Blog Archive

About Me