estimating tempdb usage

Is there any way to estimate how much space will be needed by tempdb?
I have a user executing a simple query similar to:
select orders.customerid, sum(quantity*unitprice) as amount
from orders inner join [order details]
on orders.orderid=[order details].orderid
group by orders.customerid
In other words, two thin (not too many columns) tables, equi-join,
summarizing and a group by. The only trouble is her two tables have
28000 rows and nearly 800 million rows. Her query dies after a few
hours when tempdb autogrows and runs out of disk space at 17 gig. I
know tempdb is used for work tables, joins, sorts, group bys, etc.
But knowing the columns sizes and number of rows, is there a way to
estimate how much tempdb will be needed? She's basically the only
user on the system.
By the way, this is SQL Server 2000.
Thanks,
ScottI guess it would be better to Grow the tempdb first, then run oyur query.
I had the same problem on SQL Server 6.5 to 2000 migration with the log
files (we had big tables as well) , the Autogrowth didn´t work. So we had to
create the database and transaction logs big enough to support migration
process before initiate it.
HTH
"scott parmelee" <s_parmelee@.hotmail.com> escreveu na mensagem
news:e14a8116.0311130819.725c0a4@.posting.google.com...
> Is there any way to estimate how much space will be needed by tempdb?
> I have a user executing a simple query similar to:
> select orders.customerid, sum(quantity*unitprice) as amount
> from orders inner join [order details]
> on orders.orderid=[order details].orderid
> group by orders.customerid
> In other words, two thin (not too many columns) tables, equi-join,
> summarizing and a group by. The only trouble is her two tables have
> 28000 rows and nearly 800 million rows. Her query dies after a few
> hours when tempdb autogrows and runs out of disk space at 17 gig. I
> know tempdb is used for work tables, joins, sorts, group bys, etc.
> But knowing the columns sizes and number of rows, is there a way to
> estimate how much tempdb will be needed? She's basically the only
> user on the system.
> By the way, this is SQL Server 2000.
> Thanks,
> Scott

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();
On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStrXing].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).
|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegro ups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=tr=ADing].ToString());
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnection
String].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=[/vb
col]
tr=ADing].ToString());[vbcol=seagreen]
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnecti
onString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

try
{
int result = command.ExecuteNonQuery();

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

connection.Close();If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"eggie5" <eggie5@.gmail.comwrote in message
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

>I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
>
I'm not using any fancy ado.net objects:
>
string sql= [whatever the user passes in]
>
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
>
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
>
>
try
{
int result = command.ExecuteNonQuery();
>
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
>
connection.Close();
>

|||oops sorry!

On Jun 3, 8:18 pm, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
>
This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"eggie5" <egg...@.gmail.comwrote in message
>
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...
>

Quote:

Originally Posted by

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?

>

Quote:

Originally Posted by

I'm not using any fancy ado.net objects:

>

Quote:

Originally Posted by

string sql= [whatever the user passes in]

>

Quote:

Originally Posted by

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStr ing].ToString());
connection.Open();

>

Quote:

Originally Posted by

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

>

Quote:

Originally Posted by

try
{
int result = command.ExecuteNonQuery();

>

Quote:

Originally Posted by

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

>

Quote:

Originally Posted by

connection.Close();

escaping a string

hi guys

please help me. i've never user stored procs before but here is my problem.

this is only what i am allowed to display but it shows my problem

declare @.suite varchar(10),@.company varchar(1)
set @.suite = 'brutus'

set @.company = 'A'
exec ('insert into tot (SuiteName,Company) values ('+@.suite+','+@.company+')')

when i exec the query it says that "brutus" is an invalid column name. i know that i need to insert a extra ' but i don't know how or what is the escape character. please help me.

Hi,

this should be accomplishedby this one here:

exec ('insert into tot (SuiteName,Company) values ('''+@.suite+''','''+@.company+''')')

But I would rather prefer not using dynamic sql in this case:

insert into tot (SuiteName,Company) values (@.suite,@.company)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

thanks that helps but when i do this

1. exec ('alter table tot add '+@.UnitT+' varchar(3) DEFAULT '' WITH VALUES')

2. alter table tot add @.UnitT varchar(3) DEFAULT ' ' WITH VALUES

not one work. in 1 i get the same problem and in 2 it tells met there is a problem with syntax before @.unit

|||

No, therefore you would need dynamic SQL, that will not function with the second opion I posted.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK THANKS

i used your first solution with the ''' multiple quotes and it works perfect. thanks

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Development
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
Sam
Are you on 7.0? If so, search KB for 2513, I found a few articles. If you are on 2000, I suggest you
open a case with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Development
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBCC
> CHECKCATAOG command against our QA and then our Production server, and all
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index and
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>
sql

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Development
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
SamAre you on 7.0? If so, search KB for 2513, I found a few articles. If you are on 2000, I suggest you
open a case with MS Support for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Development
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBCC
> CHECKCATAOG command against our QA and then our Production server, and all
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index and
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Developmen
t
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
SamAre you on 7.0? If so, search KB for 2513, I found a few articles. If you ar
e on 2000, I suggest you
open a case with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Developm
ent
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBC
C
> CHECKCATAOG command against our QA and then our Production server, and al
l
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index an
d
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>

Error21002 user name already exists

Hi I am trying to give database access to a second database for a user and am
getting the error (Error 21002 username allready exists). Anyhow seems that
I ran into the problem before and ran a command from query analyzer and it
seemed to fix it. Anyhow can not remember the command I used.
thanks.
--
Paul G
Software engineer.sp_dropuser?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> Hi I am trying to give database access to a second database for a user and
> am
> getting the error (Error 21002 username allready exists). Anyhow seems
> that
> I ran into the problem before and ran a command from query analyzer and it
> seemed to fix it. Anyhow can not remember the command I used.
> thanks.
> --
> Paul G
> Software engineer.|||Sounds like it may have been sp_change_users_login
See books online for details.
-Sue
On Wed, 17 Aug 2005 16:39:01 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:
>Hi I am trying to give database access to a second database for a user and am
>getting the error (Error 21002 username allready exists). Anyhow seems that
>I ran into the problem before and ran a command from query analyzer and it
>seemed to fix it. Anyhow can not remember the command I used.
>thanks.|||Hi,
U are searching for this
exec sp_addrolemember N'db_datareader', N'username
hope this helps u
from]
killer'|||Hi,
Seems that SID for the existing user inside the database is in mismatch with
Login inside syslogins. So goahead and use the
system stored procedure sp_change_users_login (see books online) as Sue
pointed out to fix the mis match.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> Hi I am trying to give database access to a second database for a user and
> am
> getting the error (Error 21002 username allready exists). Anyhow seems
> that
> I ran into the problem before and ran a command from query analyzer and it
> seemed to fix it. Anyhow can not remember the command I used.
> thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. the error I am getting now is 15023 user or role
already exists in the current database when I tried to give database access
of a second database to the user. I tried the sp_change_users_login
'AUTO_FIX','username' command. I am able to give the user access to one
dbase but not the other.
--
Paul G
Software engineer.
"Hari Prasad" wrote:
> Hi,
> Seems that SID for the existing user inside the database is in mismatch with
> Login inside syslogins. So goahead and use the
> system stored procedure sp_change_users_login (see books online) as Sue
> pointed out to fix the mis match.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> > Hi I am trying to give database access to a second database for a user and
> > am
> > getting the error (Error 21002 username allready exists). Anyhow seems
> > that
> > I ran into the problem before and ran a command from query analyzer and it
> > seemed to fix it. Anyhow can not remember the command I used.
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||it worked, thanks.
--
Paul G
Software engineer.
"Hari Prasad" wrote:
> Hi,
> Seems that SID for the existing user inside the database is in mismatch with
> Login inside syslogins. So goahead and use the
> system stored procedure sp_change_users_login (see books online) as Sue
> pointed out to fix the mis match.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> > Hi I am trying to give database access to a second database for a user and
> > am
> > getting the error (Error 21002 username allready exists). Anyhow seems
> > that
> > I ran into the problem before and ran a command from query analyzer and it
> > seemed to fix it. Anyhow can not remember the command I used.
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>

Error21002 user name already exists

Hi I am trying to give database access to a second database for a user and a
m
getting the error (Error 21002 username allready exists). Anyhow seems that
I ran into the problem before and ran a command from query analyzer and it
seemed to fix it. Anyhow can not remember the command I used.
thanks.
--
Paul G
Software engineer.sp_dropuser?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> Hi I am trying to give database access to a second database for a user and
> am
> getting the error (Error 21002 username allready exists). Anyhow seems
> that
> I ran into the problem before and ran a command from query analyzer and it
> seemed to fix it. Anyhow can not remember the command I used.
> thanks.
> --
> Paul G
> Software engineer.|||Sounds like it may have been sp_change_users_login
See books online for details.
-Sue
On Wed, 17 Aug 2005 16:39:01 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:

>Hi I am trying to give database access to a second database for a user and
am
>getting the error (Error 21002 username allready exists). Anyhow seems tha
t
>I ran into the problem before and ran a command from query analyzer and it
>seemed to fix it. Anyhow can not remember the command I used.
>thanks.|||Hi,
U are searching for this
exec sp_addrolemember N'db_datareader', N'username
hope this helps u
from]
killer'|||Hi,
Seems that SID for the existing user inside the database is in mismatch with
Login inside syslogins. So goahead and use the
system stored procedure sp_change_users_login (see books online) as Sue
pointed out to fix the mis match.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> Hi I am trying to give database access to a second database for a user and
> am
> getting the error (Error 21002 username allready exists). Anyhow seems
> that
> I ran into the problem before and ran a command from query analyzer and it
> seemed to fix it. Anyhow can not remember the command I used.
> thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. the error I am getting now is 15023 user or rol
e
already exists in the current database when I tried to give database access
of a second database to the user. I tried the sp_change_users_login
'AUTO_FIX','username' command. I am able to give the user access to one
dbase but not the other.
Paul G
Software engineer.
"Hari Prasad" wrote:

> Hi,
> Seems that SID for the existing user inside the database is in mismatch wi
th
> Login inside syslogins. So goahead and use the
> system stored procedure sp_change_users_login (see books online) as Sue
> pointed out to fix the mis match.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
>
>|||it worked, thanks.
--
Paul G
Software engineer.
"Hari Prasad" wrote:

> Hi,
> Seems that SID for the existing user inside the database is in mismatch wi
th
> Login inside syslogins. So goahead and use the
> system stored procedure sp_change_users_login (see books online) as Sue
> pointed out to fix the mis match.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
>
>

Error21002 user name already exists

Hi I am trying to give database access to a second database for a user and am
getting the error (Error 21002 username allready exists). Anyhow seems that
I ran into the problem before and ran a command from query analyzer and it
seemed to fix it. Anyhow can not remember the command I used.
thanks.
Paul G
Software engineer.
sp_dropuser?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> Hi I am trying to give database access to a second database for a user and
> am
> getting the error (Error 21002 username allready exists). Anyhow seems
> that
> I ran into the problem before and ran a command from query analyzer and it
> seemed to fix it. Anyhow can not remember the command I used.
> thanks.
> --
> Paul G
> Software engineer.
|||Sounds like it may have been sp_change_users_login
See books online for details.
-Sue
On Wed, 17 Aug 2005 16:39:01 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:

>Hi I am trying to give database access to a second database for a user and am
>getting the error (Error 21002 username allready exists). Anyhow seems that
>I ran into the problem before and ran a command from query analyzer and it
>seemed to fix it. Anyhow can not remember the command I used.
>thanks.
|||Hi,
U are searching for this
exec sp_addrolemember N'db_datareader', N'username
hope this helps u
from]
killer'
|||Hi,
Seems that SID for the existing user inside the database is in mismatch with
Login inside syslogins. So goahead and use the
system stored procedure sp_change_users_login (see books online) as Sue
pointed out to fix the mis match.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
> Hi I am trying to give database access to a second database for a user and
> am
> getting the error (Error 21002 username allready exists). Anyhow seems
> that
> I ran into the problem before and ran a command from query analyzer and it
> seemed to fix it. Anyhow can not remember the command I used.
> thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response. the error I am getting now is 15023 user or role
already exists in the current database when I tried to give database access
of a second database to the user. I tried the sp_change_users_login
'AUTO_FIX','username' command. I am able to give the user access to one
dbase but not the other.
Paul G
Software engineer.
"Hari Prasad" wrote:

> Hi,
> Seems that SID for the existing user inside the database is in mismatch with
> Login inside syslogins. So goahead and use the
> system stored procedure sp_change_users_login (see books online) as Sue
> pointed out to fix the mis match.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
>
>
|||it worked, thanks.
Paul G
Software engineer.
"Hari Prasad" wrote:

> Hi,
> Seems that SID for the existing user inside the database is in mismatch with
> Login inside syslogins. So goahead and use the
> system stored procedure sp_change_users_login (see books online) as Sue
> pointed out to fix the mis match.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E83131E-CB3C-4824-882B-2DE85A6B6975@.microsoft.com...
>
>

Error: The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it

Hi There,

I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.

=================

Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

===============

Can anybody please suggest me what I should do?

Thanks.

Is the database accessable and online ? i.e. : not suspect ?|||I get the same error message. I have a total of 10 databases & two of them give me the same error message when I try to backup using the maintenance utility. The d/b are used by the SharePoint Portal. If anyone has found a solution, please send my way. thank you|||

The immediate cause of the error is obvious: The fulltext catalog is offline.

What needs some work is figuring out why the catalog went offline and wnat to do about it.

The simplest courses of action are to either drop the catalog if it's not needed, or to rebuild it if it is needed. As soon as the rebuid starts, you should be able to successfully back up the database. You don't need to wait for the rebuild to complete.

|||

This can also happen for...
64bit 2005 Standard Cluster w/ SP1 (my configuration)
I found out that it was improperly reporting the "offline" full-text index. By improperly, I mean, that the error was reporting our current "Good" index when it should have been reporting one that had a different name that was in fact "offline".

Our problem:
You should look to see if you have any "old" indices from restores. Ours happened during a backup and restore from a single 32bit box moving to 64bit cluster. Took forever to find out how to get rid of the old index...Go to SQL Server Management Studio > <machine/instance> > <databasename> > Storage > Full Text Catalogs

...and delete the offending full text catalog definitions there. Mine didn't really exist (defined on a "d:\" drive that isn't even present in the new clustered system). You CANNOT make ad hoc queries to the db sys tables anymore in 2005. This will allow you to remove "hanging" full-text index definitions from the sys tables...allowing the backup to successfully kick off.

...our site Used Textbooks

|||Our error was:
"The backup of the file or filegroup "sysft_Keywords" is not permitted because it is not online."[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Sql Severity 16; Sql Message ID 3013.
This database was restored/converted from SQL2000. We did initially use SQL Full Text to perform searches before developing our own contextual tool.
Solution: In SQL Studio,Delete the items under the Full Text Catalogs==> Storage. This is the same ask12math's solution.|||

I'm thinking that solution is only relevant to the fully featured SQL Management Studio... is there any way to do this either in Studio Management Express or interactively? I've been using SQL Express (advanced version) for about a month now; last night's backup went fine, then today I get the "not permitted" error cited above. And only in this one database... the 15 or so others continue to backup normally.

BTW: I too restored these databases from SQL 2000 backups, and this one database had one column of one table marked for Full Text indexing at the time of the restore. I really don't need it, but apparently I didn't install the full text feature, so I can't remove it because I get an error that says the feature is not installed.

Any manual way around this problem? Thanks in advance.

Gordon

|||Are you sure that Express supports Full Text? ...and no as of about 6 months ago there was no way to remove full text index definitions manually (per Microsoft). That may have changed but if it has I am not aware of it. Good luck. My suggestion is to reimport the backup from 2000 after removing the definition (in 2000). Which probably isn't an option now because there is probably new data right? Let us know if you find anything.|||

k12math: Yes, you are right about the reimport... no longer an option because of a month of use. BUT: I did find a solution after several hours, so I'll share it with you!

1) Put the database into single user mode.

2) Start to detach the database. But before clicking OK for the detach, UNCHECK the box that says "Keep..." (the rest of the line is not immediately visible... what it says is, Keep fulltext catalogs. Get rid of that). Now OK the detach.

3) Reattach the database, and the backup works. Just like that.

Good luck everyone!

Gordon

|||This worked well for me. Good job Gordonh.

Neil.

Error: The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it

Hi There,

I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.

=================

Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

===============

Can anybody please suggest me what I should do?

Thanks.

Is the database accessable and online ? i.e. : not suspect ?|||I get the same error message. I have a total of 10 databases & two of them give me the same error message when I try to backup using the maintenance utility. The d/b are used by the SharePoint Portal. If anyone has found a solution, please send my way. thank you|||

The immediate cause of the error is obvious: The fulltext catalog is offline.

What needs some work is figuring out why the catalog went offline and wnat to do about it.

The simplest courses of action are to either drop the catalog if it's not needed, or to rebuild it if it is needed. As soon as the rebuid starts, you should be able to successfully back up the database. You don't need to wait for the rebuild to complete.

|||

This can also happen for...
64bit 2005 Standard Cluster w/ SP1 (my configuration)
I found out that it was improperly reporting the "offline" full-text index. By improperly, I mean, that the error was reporting our current "Good" index when it should have been reporting one that had a different name that was in fact "offline".

Our problem:
You should look to see if you have any "old" indices from restores. Ours happened during a backup and restore from a single 32bit box moving to 64bit cluster. Took forever to find out how to get rid of the old index...Go to SQL Server Management Studio > <machine/instance> > <databasename> > Storage > Full Text Catalogs

...and delete the offending full text catalog definitions there. Mine didn't really exist (defined on a "d:\" drive that isn't even present in the new clustered system). You CANNOT make ad hoc queries to the db sys tables anymore in 2005. This will allow you to remove "hanging" full-text index definitions from the sys tables...allowing the backup to successfully kick off.

...our site Used Textbooks

|||Our error was:
"The backup of the file or filegroup "sysft_Keywords" is not permitted because it is not online."[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Sql Severity 16; Sql Message ID 3013.
This database was restored/converted from SQL2000. We did initially use SQL Full Text to perform searches before developing our own contextual tool.
Solution: In SQL Studio, Delete the items under the Full Text Catalogs ==> Storage. This is the same as k12math's solution.|||

I'm thinking that solution is only relevant to the fully featured SQL Management Studio... is there any way to do this either in Studio Management Express or interactively? I've been using SQL Express (advanced version) for about a month now; last night's backup went fine, then today I get the "not permitted" error cited above. And only in this one database... the 15 or so others continue to backup normally.

BTW: I too restored these databases from SQL 2000 backups, and this one database had one column of one table marked for Full Text indexing at the time of the restore. I really don't need it, but apparently I didn't install the full text feature, so I can't remove it because I get an error that says the feature is not installed.

Any manual way around this problem? Thanks in advance.

Gordon

|||Are you sure that Express supports Full Text? ...and no as of about 6 months ago there was no way to remove full text index definitions manually (per Microsoft). That may have changed but if it has I am not aware of it. Good luck. My suggestion is to reimport the backup from 2000 after removing the definition (in 2000). Which probably isn't an option now because there is probably new data right? Let us know if you find anything.|||

k12math: Yes, you are right about the reimport... no longer an option because of a month of use. BUT: I did find a solution after several hours, so I'll share it with you!

1) Put the database into single user mode.

2) Start to detach the database. But before clicking OK for the detach, UNCHECK the box that says "Keep..." (the rest of the line is not immediately visible... what it says is, Keep fulltext catalogs. Get rid of that). Now OK the detach.

3) Reattach the database, and the backup works. Just like that.

Good luck everyone!

Gordon

|||This worked well for me. Good job

Gordonh.

Neil.

Error: The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it

Hi There,

I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.

=================

Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

===============

Can anybody please suggest me what I should do?

Thanks.

Is the database accessable and online ? i.e. : not suspect ?|||I get the same error message. I have a total of 10 databases & two of them give me the same error message when I try to backup using the maintenance utility. The d/b are used by the SharePoint Portal. If anyone has found a solution, please send my way. thank you|||

The immediate cause of the error is obvious: The fulltext catalog is offline.

What needs some work is figuring out why the catalog went offline and wnat to do about it.

The simplest courses of action are to either drop the catalog if it's not needed, or to rebuild it if it is needed. As soon as the rebuid starts, you should be able to successfully back up the database. You don't need to wait for the rebuild to complete.

|||

This can also happen for...
64bit 2005 Standard Cluster w/ SP1 (my configuration)
I found out that it was improperly reporting the "offline" full-text index. By improperly, I mean, that the error was reporting our current "Good" index when it should have been reporting one that had a different name that was in fact "offline".

Our problem:
You should look to see if you have any "old" indices from restores. Ours happened during a backup and restore from a single 32bit box moving to 64bit cluster. Took forever to find out how to get rid of the old index...Go to SQL Server Management Studio > <machine/instance> > <databasename> > Storage > Full Text Catalogs

...and delete the offending full text catalog definitions there. Mine didn't really exist (defined on a "d:\" drive that isn't even present in the new clustered system). You CANNOT make ad hoc queries to the db sys tables anymore in 2005. This will allow you to remove "hanging" full-text index definitions from the sys tables...allowing the backup to successfully kick off.

...our site Used Textbooks

|||Our error was:
"The backup of the file or filegroup "sysft_Keywords" is not permitted because it is not online."[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Sql Severity 16; Sql Message ID 3013.
This database was restored/converted from SQL2000. We did initially use SQL Full Text to perform searches before developing our own contextual tool.
Solution: In SQL Studio, Delete the items under the Full Text Catalogs ==> Storage. This is the same as k12math's solution.|||

I'm thinking that solution is only relevant to the fully featured SQL Management Studio... is there any way to do this either in Studio Management Express or interactively? I've been using SQL Express (advanced version) for about a month now; last night's backup went fine, then today I get the "not permitted" error cited above. And only in this one database... the 15 or so others continue to backup normally.

BTW: I too restored these databases from SQL 2000 backups, and this one database had one column of one table marked for Full Text indexing at the time of the restore. I really don't need it, but apparently I didn't install the full text feature, so I can't remove it because I get an error that says the feature is not installed.

Any manual way around this problem? Thanks in advance.

Gordon

|||Are you sure that Express supports Full Text? ...and no as of about 6 months ago there was no way to remove full text index definitions manually (per Microsoft). That may have changed but if it has I am not aware of it. Good luck. My suggestion is to reimport the backup from 2000 after removing the definition (in 2000). Which probably isn't an option now because there is probably new data right? Let us know if you find anything.|||

k12math: Yes, you are right about the reimport... no longer an option because of a month of use. BUT: I did find a solution after several hours, so I'll share it with you!

1) Put the database into single user mode.

2) Start to detach the database. But before clicking OK for the detach, UNCHECK the box that says "Keep..." (the rest of the line is not immediately visible... what it says is, Keep fulltext catalogs. Get rid of that). Now OK the detach.

3) Reattach the database, and the backup works. Just like that.

Good luck everyone!

Gordon

|||This worked well for me. Good job

Gordonh.

Neil.

Error: The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it

Hi There,

I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.

=================

Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

===============

Can anybody please suggest me what I should do?

Thanks.

Is the database accessable and online ? i.e. : not suspect ?|||I get the same error message. I have a total of 10 databases & two of them give me the same error message when I try to backup using the maintenance utility. The d/b are used by the SharePoint Portal. If anyone has found a solution, please send my way. thank you|||

The immediate cause of the error is obvious: The fulltext catalog is offline.

What needs some work is figuring out why the catalog went offline and wnat to do about it.

The simplest courses of action are to either drop the catalog if it's not needed, or to rebuild it if it is needed. As soon as the rebuid starts, you should be able to successfully back up the database. You don't need to wait for the rebuild to complete.

|||

This can also happen for...
64bit 2005 Standard Cluster w/ SP1 (my configuration)
I found out that it was improperly reporting the "offline" full-text index. By improperly, I mean, that the error was reporting our current "Good" index when it should have been reporting one that had a different name that was in fact "offline".

Our problem:
You should look to see if you have any "old" indices from restores. Ours happened during a backup and restore from a single 32bit box moving to 64bit cluster. Took forever to find out how to get rid of the old index...Go to SQL Server Management Studio > <machine/instance> > <databasename> > Storage > Full Text Catalogs

...and delete the offending full text catalog definitions there. Mine didn't really exist (defined on a "d:\" drive that isn't even present in the new clustered system). You CANNOT make ad hoc queries to the db sys tables anymore in 2005. This will allow you to remove "hanging" full-text index definitions from the sys tables...allowing the backup to successfully kick off.

...our site Used Textbooks

|||Our error was:
"The backup of the file or filegroup "sysft_Keywords" is not permitted because it is not online."[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Sql Severity 16; Sql Message ID 3013.
This database was restored/converted from SQL2000. We did initially use SQL Full Text to perform searches before developing our own contextual tool.
Solution: In SQL Studio, Delete the items under the Full Text Catalogs ==> Storage. This is the same as k12math's solution.|||

I'm thinking that solution is only relevant to the fully featured SQL Management Studio... is there any way to do this either in Studio Management Express or interactively? I've been using SQL Express (advanced version) for about a month now; last night's backup went fine, then today I get the "not permitted" error cited above. And only in this one database... the 15 or so others continue to backup normally.

BTW: I too restored these databases from SQL 2000 backups, and this one database had one column of one table marked for Full Text indexing at the time of the restore. I really don't need it, but apparently I didn't install the full text feature, so I can't remove it because I get an error that says the feature is not installed.

Any manual way around this problem? Thanks in advance.

Gordon

|||Are you sure that Express supports Full Text? ...and no as of about 6 months ago there was no way to remove full text index definitions manually (per Microsoft). That may have changed but if it has I am not aware of it. Good luck. My suggestion is to reimport the backup from 2000 after removing the definition (in 2000). Which probably isn't an option now because there is probably new data right? Let us know if you find anything.|||

k12math: Yes, you are right about the reimport... no longer an option because of a month of use. BUT: I did find a solution after several hours, so I'll share it with you!

1) Put the database into single user mode.

2) Start to detach the database. But before clicking OK for the detach, UNCHECK the box that says "Keep..." (the rest of the line is not immediately visible... what it says is, Keep fulltext catalogs. Get rid of that). Now OK the detach.

3) Reattach the database, and the backup works. Just like that.

Good luck everyone!

Gordon

|||This worked well for me. Good job

Gordonh.

Neil.

Error: The backup of the file or filegroup "sysft_XXXX_FT" is not permitted becaus

Hi There,

I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.

=================

Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

===============

Can anybody please suggest me what I should do?

Thanks.

Is the database accessable and online ? i.e. : not suspect ?|||I get the same error message. I have a total of 10 databases & two of them give me the same error message when I try to backup using the maintenance utility. The d/b are used by the SharePoint Portal. If anyone has found a solution, please send my way. thank you|||

The immediate cause of the error is obvious: The fulltext catalog is offline.

What needs some work is figuring out why the catalog went offline and wnat to do about it.

The simplest courses of action are to either drop the catalog if it's not needed, or to rebuild it if it is needed. As soon as the rebuid starts, you should be able to successfully back up the database. You don't need to wait for the rebuild to complete.

|||

This can also happen for...
64bit 2005 Standard Cluster w/ SP1 (my configuration)
I found out that it was improperly reporting the "offline" full-text index. By improperly, I mean, that the error was reporting our current "Good" index when it should have been reporting one that had a different name that was in fact "offline".

Our problem:
You should look to see if you have any "old" indices from restores. Ours happened during a backup and restore from a single 32bit box moving to 64bit cluster. Took forever to find out how to get rid of the old index...Go to SQL Server Management Studio > <machine/instance> > <databasename> > Storage > Full Text Catalogs

...and delete the offending full text catalog definitions there. Mine didn't really exist (defined on a "d:\" drive that isn't even present in the new clustered system). You CANNOT make ad hoc queries to the db sys tables anymore in 2005. This will allow you to remove "hanging" full-text index definitions from the sys tables...allowing the backup to successfully kick off.

...our site Used Textbooks

|||Our error was:
"The backup of the file or filegroup "sysft_Keywords" is not permitted because it is not online."[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Sql Severity 16; Sql Message ID 3013.
This database was restored/converted from SQL2000. We did initially use SQL Full Text to perform searches before developing our own contextual tool.
Solution: In SQL Studio, Delete the items under the Full Text Catalogs ==> Storage. This is the same as k12math's solution.|||

I'm thinking that solution is only relevant to the fully featured SQL Management Studio... is there any way to do this either in Studio Management Express or interactively? I've been using SQL Express (advanced version) for about a month now; last night's backup went fine, then today I get the "not permitted" error cited above. And only in this one database... the 15 or so others continue to backup normally.

BTW: I too restored these databases from SQL 2000 backups, and this one database had one column of one table marked for Full Text indexing at the time of the restore. I really don't need it, but apparently I didn't install the full text feature, so I can't remove it because I get an error that says the feature is not installed.

Any manual way around this problem? Thanks in advance.

Gordon

|||Are you sure that Express supports Full Text? ...and no as of about 6 months ago there was no way to remove full text index definitions manually (per Microsoft). That may have changed but if it has I am not aware of it. Good luck. My suggestion is to reimport the backup from 2000 after removing the definition (in 2000). Which probably isn't an option now because there is probably new data right? Let us know if you find anything.|||

k12math: Yes, you are right about the reimport... no longer an option because of a month of use. BUT: I did find a solution after several hours, so I'll share it with you!

1) Put the database into single user mode.

2) Start to detach the database. But before clicking OK for the detach, UNCHECK the box that says "Keep..." (the rest of the line is not immediately visible... what it says is, Keep fulltext catalogs. Get rid of that). Now OK the detach.

3) Reattach the database, and the backup works. Just like that.

Good luck everyone!

Gordon

|||This worked well for me. Good job

Gordonh.

Neil.

Error: The backup of the file or filegroup "sysft_XXXX_FT" is not permitted becaus

Hi There,

I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.

=================

Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

===============

Can anybody please suggest me what I should do?

Thanks.

Is the database accessable and online ? i.e. : not suspect ?|||I get the same error message. I have a total of 10 databases & two of them give me the same error message when I try to backup using the maintenance utility. The d/b are used by the SharePoint Portal. If anyone has found a solution, please send my way. thank you|||

The immediate cause of the error is obvious: The fulltext catalog is offline.

What needs some work is figuring out why the catalog went offline and wnat to do about it.

The simplest courses of action are to either drop the catalog if it's not needed, or to rebuild it if it is needed. As soon as the rebuid starts, you should be able to successfully back up the database. You don't need to wait for the rebuild to complete.

|||

This can also happen for...
64bit 2005 Standard Cluster w/ SP1 (my configuration)
I found out that it was improperly reporting the "offline" full-text index. By improperly, I mean, that the error was reporting our current "Good" index when it should have been reporting one that had a different name that was in fact "offline".

Our problem:
You should look to see if you have any "old" indices from restores. Ours happened during a backup and restore from a single 32bit box moving to 64bit cluster. Took forever to find out how to get rid of the old index...Go to SQL Server Management Studio > <machine/instance> > <databasename> > Storage > Full Text Catalogs

...and delete the offending full text catalog definitions there. Mine didn't really exist (defined on a "d:\" drive that isn't even present in the new clustered system). You CANNOT make ad hoc queries to the db sys tables anymore in 2005. This will allow you to remove "hanging" full-text index definitions from the sys tables...allowing the backup to successfully kick off.

...our site Used Textbooks

|||Our error was:
"The backup of the file or filegroup "sysft_Keywords" is not permitted because it is not online."[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Sql Severity 16; Sql Message ID 3013.
This database was restored/converted from SQL2000. We did initially use SQL Full Text to perform searches before developing our own contextual tool.
Solution: In SQL Studio, Delete the items under the Full Text Catalogs ==> Storage. This is the same as k12math's solution.|||

I'm thinking that solution is only relevant to the fully featured SQL Management Studio... is there any way to do this either in Studio Management Express or interactively? I've been using SQL Express (advanced version) for about a month now; last night's backup went fine, then today I get the "not permitted" error cited above. And only in this one database... the 15 or so others continue to backup normally.

BTW: I too restored these databases from SQL 2000 backups, and this one database had one column of one table marked for Full Text indexing at the time of the restore. I really don't need it, but apparently I didn't install the full text feature, so I can't remove it because I get an error that says the feature is not installed.

Any manual way around this problem? Thanks in advance.

Gordon

|||Are you sure that Express supports Full Text? ...and no as of about 6 months ago there was no way to remove full text index definitions manually (per Microsoft). That may have changed but if it has I am not aware of it. Good luck. My suggestion is to reimport the backup from 2000 after removing the definition (in 2000). Which probably isn't an option now because there is probably new data right? Let us know if you find anything.|||

k12math: Yes, you are right about the reimport... no longer an option because of a month of use. BUT: I did find a solution after several hours, so I'll share it with you!

1) Put the database into single user mode.

2) Start to detach the database. But before clicking OK for the detach, UNCHECK the box that says "Keep..." (the rest of the line is not immediately visible... what it says is, Keep fulltext catalogs. Get rid of that). Now OK the detach.

3) Reattach the database, and the backup works. Just like that.

Good luck everyone!

Gordon

|||This worked well for me. Good job

Gordonh.

Neil.

Error: The backup of the file or filegroup "sysft_XXXX_FT" is not permitted becaus

Hi There,

I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.

=================

Executing the query "BACKUP DATABASE [XXXX] TO [XXXX]
WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error:
"The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

===============

Can anybody please suggest me what I should do?

Thanks.

Is the database accessable and online ? i.e. : not suspect ?|||I get the same error message. I have a total of 10 databases & two of them give me the same error message when I try to backup using the maintenance utility. The d/b are used by the SharePoint Portal. If anyone has found a solution, please send my way. thank you|||

The immediate cause of the error is obvious: The fulltext catalog is offline.

What needs some work is figuring out why the catalog went offline and wnat to do about it.

The simplest courses of action are to either drop the catalog if it's not needed, or to rebuild it if it is needed. As soon as the rebuid starts, you should be able to successfully back up the database. You don't need to wait for the rebuild to complete.

|||

This can also happen for...
64bit 2005 Standard Cluster w/ SP1 (my configuration)
I found out that it was improperly reporting the "offline" full-text index. By improperly, I mean, that the error was reporting our current "Good" index when it should have been reporting one that had a different name that was in fact "offline".

Our problem:
You should look to see if you have any "old" indices from restores. Ours happened during a backup and restore from a single 32bit box moving to 64bit cluster. Took forever to find out how to get rid of the old index...Go to SQL Server Management Studio > <machine/instance> > <databasename> > Storage > Full Text Catalogs

...and delete the offending full text catalog definitions there. Mine didn't really exist (defined on a "d:\" drive that isn't even present in the new clustered system). You CANNOT make ad hoc queries to the db sys tables anymore in 2005. This will allow you to remove "hanging" full-text index definitions from the sys tables...allowing the backup to successfully kick off.

...our site Used Textbooks

|||Our error was:
"The backup of the file or filegroup "sysft_Keywords" is not permitted because it is not online."[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Sql Severity 16; Sql Message ID 3013.
This database was restored/converted from SQL2000. We did initially use SQL Full Text to perform searches before developing our own contextual tool.
Solution: In SQL Studio, Delete the items under the Full Text Catalogs ==> Storage. This is the same as k12math's solution.|||

I'm thinking that solution is only relevant to the fully featured SQL Management Studio... is there any way to do this either in Studio Management Express or interactively? I've been using SQL Express (advanced version) for about a month now; last night's backup went fine, then today I get the "not permitted" error cited above. And only in this one database... the 15 or so others continue to backup normally.

BTW: I too restored these databases from SQL 2000 backups, and this one database had one column of one table marked for Full Text indexing at the time of the restore. I really don't need it, but apparently I didn't install the full text feature, so I can't remove it because I get an error that says the feature is not installed.

Any manual way around this problem? Thanks in advance.

Gordon

|||Are you sure that Express supports Full Text? ...and no as of about 6 months ago there was no way to remove full text index definitions manually (per Microsoft). That may have changed but if it has I am not aware of it. Good luck. My suggestion is to reimport the backup from 2000 after removing the definition (in 2000). Which probably isn't an option now because there is probably new data right? Let us know if you find anything.|||

k12math: Yes, you are right about the reimport... no longer an option because of a month of use. BUT: I did find a solution after several hours, so I'll share it with you!

1) Put the database into single user mode.

2) Start to detach the database. But before clicking OK for the detach, UNCHECK the box that says "Keep..." (the rest of the line is not immediately visible... what it says is, Keep fulltext catalogs. Get rid of that). Now OK the detach.

3) Reattach the database, and the backup works. Just like that.

Good luck everyone!

Gordon

|||This worked well for me. Good job

Gordonh.

Neil.

error: string must be exactly one character long

hi all, i'm retreiving user input using textboxes and saving to a gridview. i'm getting this error and i dont know whats causing it.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

InsertCommand="INSERT INTO test101(Surname,Names,Regno)VALUES (@.Surname, @.Names, @.Regno)"

ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>"ProviderName=System.Data.SqlClient

ConflictDetection="CompareAllValues">

<InsertParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue="TextBox1.Text"Name="Surname"

PropertyName="Text"Size="50"Type=Char/>

<asp:ControlParameterControlID="TextBox2"DefaultValue="TextBox2.Text"Name="Names"

PropertyName="Text"Size="50"Type=Char/>

<asp:ParameterDefaultValue="TextBox3.Text"Name="Regno"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:GridViewID="GridView1"

runat="server"AutoGenerateColumns="False"AutoGenerateDeleteButton="True"DataKeyNames="ID"

AutoGenerateEditButton="True"AllowSorting="True"BackColor="LightGoldenrodYellow"

BorderColor="Tan"BorderWidth="1px"CellPadding="2"ForeColor="Black"GridLines="None"PageSize="20"

Height="374px"EmptyDataText="null"DataSourceID=SqlDataSource1Visible="False">

<Columns>

<asp:BoundFieldDataField="ID"HeaderText="ID"InsertVisible="False"ReadOnly="True"

SortExpression="ID"/>

<asp:BoundFieldDataField="Surname"HeaderText="Surname"SortExpression="Surname"/>

<asp:BoundFieldDataField="Names"HeaderText="Names"SortExpression="Names"/>

<asp:BoundFieldDataField="Registration"HeaderText="Registration"SortExpression="Registration"/>

<asp:BoundFieldDataField="Grade"HeaderText="Grade"SortExpression="Grade"/>

</Columns>

</asp:GridView>

and the code behind is:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlConnection conn =newSqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");

conn.Open();

GridView1.DataBind();

conn.Close();

}

publicvoid login1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

Response.Write("you have successfully being added to the database");

can anyone help?!!!

Check out this link:http://vbcity.com/forums/topic.asp?tid=148073

Good luck.