Altering XML Schema Collection Problem

Hi,
I'm trying to add an extra element into a schema collection and keep getting
an error message:
"Msg 9455, Level 16, State 1, Line 1
XML parsing: line 3, character 1, illegal qualified name character
"
The SQL I'm using is:
alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
<xsd:element name="Employee">
<xsd:complexType>
<xsd:element name="HireDate" type="xsd:datetime" />
</xsd:complexType>
</xsd:element>
</xsd:schema>'
Can anyone help?
ThanksI don't know this stuff much however should not be a ">" in the end of the
second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
--
Ekrem Ã?nsoy
"John S" <js162@.newsgroup.nospam> wrote in message
news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> Hi,
> I'm trying to add an extra element into a schema collection and keep
> getting
> an error message:
> "Msg 9455, Level 16, State 1, Line 1
> XML parsing: line 3, character 1, illegal qualified name character
> "
> The SQL I'm using is:
> alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> <xsd:element name="Employee">
> <xsd:complexType>
> <xsd:element name="HireDate" type="xsd:datetime" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>'
> Can anyone help?
> Thanks|||It's so easy to miss the obvious!
Thanks
JS
"Ekrem Ã?nsoy" wrote:
> I don't know this stuff much however should not be a ">" in the end of the
> second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
> --
> Ekrem Ã?nsoy
>
> "John S" <js162@.newsgroup.nospam> wrote in message
> news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> > Hi,
> >
> > I'm trying to add an extra element into a schema collection and keep
> > getting
> > an error message:
> > "Msg 9455, Level 16, State 1, Line 1
> > XML parsing: line 3, character 1, illegal qualified name character
> > "
> >
> > The SQL I'm using is:
> >
> > alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> > <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> > <xsd:element name="Employee">
> > <xsd:complexType>
> > <xsd:element name="HireDate" type="xsd:datetime" />
> > </xsd:complexType>
> > </xsd:element>
> > </xsd:schema>'
> >
> > Can anyone help?
> >
> > Thanks
>sql

Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > 39 char fields (5816 total characters)
>> What are the definitions of these columns? Are they
>> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>> > 9 tinyint fields (size = 9 * 1 = 9)
>> > 4 int fields (size = 4 * 4 = 16)
>> > 5 datetime fields (size = 5 * 8 = 40)
>> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
>> Are any of these NULLable?
>>
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> > calculate my row size.
> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidates
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Number char(20)
CUSTNMBR char(45)
VENDORID char(45)
Attn_Name char(30)
Contact_Name char(30)
ContactPhone char(20)
BusinessName char(30)
AddressLine1 char(30)
AddressLine2 char(30)
City char(25)
State char(3)
ZipCode char(10)
Phone char(20)
Fax char(20)
ICC_Number char(20)
FedID_Number char(15)
Contracted tinyint
Start_Date datetime
InsAgentName char(30)
InsAgentAddress1 char(30)
InsAgentAddress2 char(30)
InsAgentCity char(25)
InsAgentState char(3)
InsAgentZipCode char(10)
InsAgentPhone char(20)
InsAgentFax char(20)
InsAgentContact char(30)
CargoInsName char(28)
CargoInsAmount decimal(19, 5)
CargoInsExpirationDate datetime
AutoLibInsName char(30)
AutoLibInsAmount decimal(19, 5)
AutoLibInsExpirationDate datetime
Agent_YN tinyint
Broker_YN tinyint
Carrier_YN tinyint
Shipper_YN tinyint
Consignee_YN tinyint
BillTo_YN tinyint
CreatedDate datetime
CreatedUserID char(20)
Billing_Rate decimal(19, 5)
Billing_Rate_Code char(5)
Rating char(5)
Color char(20)
Flagged tinyint
FlagDate datetime
FlagUserID char(20)
FlagReason char(50)
Active tinyint
Notes char(3000)
PayCode char(5)
PayRateFlat decimal(19, 5)
PayRatePercent decimal(19, 5)
PayRateLoaded decimal(19, 5)
PayRateUnloaded decimal(19, 5)
DefaultSalesperson char(20)
Directions char(2000)
FSType char(10)
FSRateAmount decimal(19, 5)
Weight decimal(19, 5)
AgentPayAccount int
CarrierPayAccount int
DropPayAmount decimal(19, 5)
PickupPayAmount decimal(19, 5)
ISType char(10)
ISRateAmount decimal(19, 5)
Latitude int
Longitude int|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.
> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of columns
> you provided below can't possibly be 8508 bytes unless (a) the column you're
> trying to add is a lot longer than you're letting on, (b) some of those data
> types are not correct, or (c) you've left out some columns.
>
>
> > all character fields are CHAR(####)
> > Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> >
> > Here is a list of the fields:
> >
> > Customer_Number char(20)
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> Just forget it. SQL is being stupid.
I am willing to bet that it is not. But I can't prove it unless you post an
honest result from EXEC sp_help.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
Is it possible this column is involved in a foreign key relationship, and
you are trying to implement the change through the GUI?
A|||> I can create this table from scratch with the columns/sizes I listed
> above.
> The table is created with no problem. I have to up the size of one of my
> CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
I also strongly recommend you familiarize yourself with the reasons we have
CHAR and VARCHAR. CHAR(2000) is not exactly a great choice here.
http://databases.aspfaq.com/database/what-datatype-should-i-use-for-my-character-based-database-columns.html
A|||Hi Matt
If you ALTER the length of fixed length columns, SQL Server may not reuse
the original space.
See my blog post on the subject:
http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:9EF8C9DF-A8DE-406D-B1BA-1C67338D8FB3@.microsoft.com...
>I am trying to alter a table and getting the following error.
> Server: Msg 1701, Level 16, State 2, Line 1
> Creation of table 'CustomerMaster' failed because the row size would be
> 8508, including internal overhead. This exceeds the maximum allowable
> table
> row size, 8060.
> I can create a new table with the same fields, but not alter an existing
> one.
> I have calculated the maximum row size as only 6004 after the changes.
> Here
> is what I used to calculate.
> 69 columns
> 39 char fields (5816 total characters)
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Num_Cols = 69
> Fixed_Data_Size = 5989
> Num_Var_Cols = 0
> Max_Var_Size = 0
> Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
> Row_Size = 5981 + 0 + 11 + 4 = 6004
>|||On Feb 7, 2:13 pm, Matt Soukup <MattSou...@.discussions.microsoft.com>
wrote:
> Just forget it. SQL is being stupid.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
> I can create this table from scratch with the columns/sizes I listed above.
> The table is created with no problem. I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
> CREATE TABLE to fail with the same error.
Aaron asked you twice for the results of sp_help, and you've refused
to provide that, instead opting for the "SQL is being stupid"
response. WTF? If we can see the table structure, then we know
exactly what you're working with, and can provide a proper answer
instead of guessing.
Here's another suggestion that you can write off as "stupid" - think
about using VARCHAR instead of CHAR for your column definitions.
You're very likely wasting a ton of space with these fixed-length
2000+ column lengths. Read up on the differences between CHAR and
VARCHAR, hopefully the documentation isn't too "stupid".|||This may very well be it; I'm glad I cntinue to learn things from you Kalen.
But the OP stated:
"This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
Yet is able to increase the length of a CHAR(2000) column to CHAR(5000). I
would think that if previous column modifications had caused the row size
threshold to be within 10 bytes of the current row size, that just about
*any* column length extension would cause the problem. I guess having the
actual table structure from sp_help, so understanding the order of the
columns (and a history of modifications made) would help us answer the
question better. Better still would be the result of your query against
sys.partitions/columns/system_internals_partition_columns.
In any case, the last statement in your blog entry is probably the most
helpful to the OP, though Tracy and I have both suggested it to no avail:
"So be careful when using large datatypes, especially if you want to make
them fixed length instead of variable length."
The shortest path is likely to rebuild the table. But the Directions and
Notes columns should be VARCHAR, not CHAR.
A
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Matt
> If you ALTER the length of fixed length columns, SQL Server may not reuse
> the original space.
> See my blog post on the subject:
> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx|||I didn't read the whole thread in detail initially, I was just tossing this
out as a possibility.
However, now that I am going back and reading over the posts, I don't see
anywhere that he said he had already altered the table changing a column
from 2000 to 5000 bytes. He said he would have to replace a 2000 byte column
with a 5000 byte column to get the original CREATE TABLE to fail. Has he
indicated he has done other ALTERs?
Yes, it would be nice to get more details from the OP, including WHY he
thinks he needs fixed length columns. Otherwise we just can't know. I agree
Directions and Notes should be variable length.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OpFCoZ4SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> This may very well be it; I'm glad I cntinue to learn things from you
> Kalen.
> But the OP stated:
> "This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
> Yet is able to increase the length of a CHAR(2000) column to CHAR(5000).
> I would think that if previous column modifications had caused the row
> size threshold to be within 10 bytes of the current row size, that just
> about *any* column length extension would cause the problem. I guess
> having the actual table structure from sp_help, so understanding the order
> of the columns (and a history of modifications made) would help us answer
> the question better. Better still would be the result of your query
> against sys.partitions/columns/system_internals_partition_columns.
> In any case, the last statement in your blog entry is probably the most
> helpful to the OP, though Tracy and I have both suggested it to no avail:
> "So be careful when using large datatypes, especially if you want to make
> them fixed length instead of variable length."
> The shortest path is likely to rebuild the table. But the Directions and
> Notes columns should be VARCHAR, not CHAR.
> A
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi Matt
>> If you ALTER the length of fixed length columns, SQL Server may not reuse
>> the original space.
>> See my blog post on the subject:
>> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
>|||> However, now that I am going back and reading over the posts, I don't see
> anywhere that he said he had already altered the table changing a column
> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
> column with a 5000 byte column to get the original CREATE TABLE to fail.
> Has he indicated he has done other ALTERs?
Here is what I am referring to:
I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.
My interpretation was that he had done that. Maybe I'm wrong. It's
ambiguous.
A|||Yes, that is what I'm referring to, and he explicitly says it is the CREATE
table and not ALTER.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23UIpXh6SHHA.2228@.TK2MSFTNGP03.phx.gbl...
>> However, now that I am going back and reading over the posts, I don't see
>> anywhere that he said he had already altered the table changing a column
>> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
>> column with a 5000 byte column to get the original CREATE TABLE to fail.
>> Has he indicated he has done other ALTERs?
> Here is what I am referring to:
> I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
> My interpretation was that he had done that. Maybe I'm wrong. It's
> ambiguous.
> A
>|||> Yes, that is what I'm referring to, and he explicitly says it is the
> CREATE table and not ALTER.
Ahh. You should know by now that I lack the ability to read entire
sentences. :-)
And you would think the caps would make words stand out more, but for me it
obscures them... I tend to focus on the meat *between* the keywords...
A

Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one
.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?

> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidate
s
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Number char(20)
CUSTNMBR char(45)
VENDORID char(45)
Attn_Name char(30)
Contact_Name char(30)
ContactPhone char(20)
BusinessName char(30)
AddressLine1 char(30)
AddressLine2 char(30)
City char(25)
State char(3)
ZipCode char(10)
Phone char(20)
Fax char(20)
ICC_Number char(20)
FedID_Number char(15)
Contracted tinyint
Start_Date datetime
InsAgentName char(30)
InsAgentAddress1 char(30)
InsAgentAddress2 char(30)
InsAgentCity char(25)
InsAgentState char(3)
InsAgentZipCode char(10)
InsAgentPhone char(20)
InsAgentFax char(20)
InsAgentContact char(30)
CargoInsName char(28)
CargoInsAmount decimal(19, 5)
CargoInsExpirationDate datetime
AutoLibInsName char(30)
AutoLibInsAmount decimal(19, 5)
AutoLibInsExpirationDate datetime
Agent_YN tinyint
Broker_YN tinyint
Carrier_YN tinyint
Shipper_YN tinyint
Consignee_YN tinyint
BillTo_YN tinyint
CreatedDate datetime
CreatedUserID char(20)
Billing_Rate decimal(19, 5)
Billing_Rate_Code char(5)
Rating char(5)
Color char(20)
Flagged tinyint
FlagDate datetime
FlagUserID char(20)
FlagReason char(50)
Active tinyint
Notes char(3000)
PayCode char(5)
PayRateFlat decimal(19, 5)
PayRatePercent decimal(19, 5)
PayRateLoaded decimal(19, 5)
PayRateUnloaded decimal(19, 5)
DefaultSalesperson char(20)
Directions char(2000)
FSType char(10)
FSRateAmount decimal(19, 5)
Weight decimal(19, 5)
AgentPayAccount int
CarrierPayAccount int
DropPayAmount decimal(19, 5)
PickupPayAmount decimal(19, 5)
ISType char(10)
ISRateAmount decimal(19, 5)
Latitude int
Longitude int|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.

> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of column
s
> you provided below can't possibly be 8508 bytes unless (a) the column you'
re
> trying to add is a lot longer than you're letting on, (b) some of those da
ta
> types are not correct, or (c) you've left out some columns.
>
>
>
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHA
R
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> I can create this table from scratch with the columns/sizes I listed
> above.
> The table is created with no problem. I have to up the size of one of my
> CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
I also strongly recommend you familiarize yourself with the reasons we have
CHAR and VARCHAR. CHAR(2000) is not exactly a great choice here.
http://databases.aspfaq.com/databas...se-columns.html
Asql

Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004
> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?

> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.
|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)
|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidates
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Numberchar(20)
CUSTNMBRchar(45)
VENDORIDchar(45)
Attn_Namechar(30)
Contact_Namechar(30)
ContactPhonechar(20)
BusinessNamechar(30)
AddressLine1char(30)
AddressLine2char(30)
Citychar(25)
Statechar(3)
ZipCodechar(10)
Phonechar(20)
Faxchar(20)
ICC_Numberchar(20)
FedID_Numberchar(15)
Contractedtinyint
Start_Datedatetime
InsAgentNamechar(30)
InsAgentAddress1char(30)
InsAgentAddress2char(30)
InsAgentCitychar(25)
InsAgentStatechar(3)
InsAgentZipCodechar(10)
InsAgentPhonechar(20)
InsAgentFaxchar(20)
InsAgentContactchar(30)
CargoInsNamechar(28)
CargoInsAmountdecimal(19, 5)
CargoInsExpirationDatedatetime
AutoLibInsNamechar(30)
AutoLibInsAmountdecimal(19, 5)
AutoLibInsExpirationDatedatetime
Agent_YNtinyint
Broker_YNtinyint
Carrier_YNtinyint
Shipper_YNtinyint
Consignee_YNtinyint
BillTo_YNtinyint
CreatedDatedatetime
CreatedUserIDchar(20)
Billing_Ratedecimal(19, 5)
Billing_Rate_Codechar(5)
Ratingchar(5)
Colorchar(20)
Flaggedtinyint
FlagDatedatetime
FlagUserIDchar(20)
FlagReasonchar(50)
Activetinyint
Noteschar(3000)
PayCodechar(5)
PayRateFlatdecimal(19, 5)
PayRatePercentdecimal(19, 5)
PayRateLoadeddecimal(19, 5)
PayRateUnloadeddecimal(19, 5)
DefaultSalespersonchar(20)
Directionschar(2000)
FSTypechar(10)
FSRateAmountdecimal(19, 5)
Weightdecimal(19, 5)
AgentPayAccountint
CarrierPayAccountint
DropPayAmountdecimal(19, 5)
PickupPayAmountdecimal(19, 5)
ISTypechar(10)
ISRateAmountdecimal(19, 5)
Latitudeint
Longitudeint
|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.

> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of columns
> you provided below can't possibly be 8508 bytes unless (a) the column you're
> trying to add is a lot longer than you're letting on, (b) some of those data
> types are not correct, or (c) you've left out some columns.
>
>
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.
|||> Just forget it. SQL is being stupid.
I am willing to bet that it is not. But I can't prove it unless you post an
honest result from EXEC sp_help.

> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
Is it possible this column is involved in a foreign key relationship, and
you are trying to implement the change through the GUI?
A

alter databse TestDB set TRUSTWORTHY on fails

Hi all,

When I try to execute the following command:

alter databse TestDB set TRUSTWORTHY on

I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'databse'.
Msg 195, Level 15, State 5, Line 1
'TRUSTWORTHY' is not a recognized SET option.

Can someone tell me why?

Thanks in advance.

in which version u r executing this query, makesure that it is SQL Server 2005. I thing u r running it on SQL Server 2000. SQL 2000 does not support this Option.

Madhu

|||

Hi Madhu,

Thanks for the quick response, but there is no SQLServer 2000 on my PC or my laptop. My laptop has Win XP and SQL Server 2005, and my PC has Vista Ultimate, with SQL Server 2005. The command was executed in Management Studio, which SQL Server 2000 does no have.

Thanks.

|||

which edition of sql server u have. i suppose SQL 2005 express edition. in that case it has some limitation with regards to Service Broker. Check whether u can enable broker on database or not.

Ref the link also :

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

See service broker, it says that it can only be a subscriber.

|||Standard edition. I see the "Trustworthy" option in database property, but it is disabled and set to False.|||

sobo1 wrote:

Hi all,

When I try to execute the following command:

alter databse TestDB set TRUSTWORTHY on

I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'databse'.
Msg 195, Level 15, State 5, Line 1
'TRUSTWORTHY' is not a recognized SET option.

Can someone tell me why?

Thanks in advance.

oh... god it is typo error... it is Database not databse

alter databse TestDB set TRUSTWORTHY on

run this alter database TestDB set TRUSTWORTHY on

Madhu

|||

Hi Madhu,

Thank you so much for catching the typo. It is interesting that Management Studio faild to detect it, and instead, blamed it on something else.

Thank again, I really appreciate it.

|||

sobo1 wrote:

I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'databse'.
Msg 195, Level 15, State 5, Line 1
'TRUSTWORTHY' is not a recognized SET option.

Can someone tell me why?

Thanks in advance.

SSMO has detected it and reported it correctly. but we did not notice that..

Madhu

|||

When I see the statement Incorrect syntax near 'databse'., I am thinking that the problem is near 'databse' and not 'databse' itself

Alter Database while "Suspect"

Hi

I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.

and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)

(i tried starting with -f -c, but no go)

thanks in advance
desCheck in your virus scan software to see if it ignores *.mdf, *.ndf and *.ldf. I am assuming this is on startup of the database? Say after recycling the SQL Server service, or rebooting the machine?

Also, you will want to do some diagnostics on the disk (check with hardware vendor), in order to make sure the disk has not suddenly gone bad.

Good luck, and let us know what happens.|||What had happened was the folder/drive I put the index file on was set to compress contents (accidentally)...I figure the windows compression had a hold on it...happened after rebooting the machine (I thought it took real long to build those indexes - now i know why!)

Disk seems fine though...

Thanks for advice
cheers
des

(what happened ultimately was db loss & a 12 hour snapshot delivery...eughh. wish i couldve just removed the index file somehow...)|||I thought I read it somewhere not to use compression on any files SQL touches, maybe with the exception of the error logs. You can lock out any interactive access to the data and log folders to prevent future mishaps.

Alter Column

Hi
How come this doesnt work?
ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.A default in SQL Server is handled as a constraint. So you would need someth
ing like:
ALTER TABLE tblname
ADD CONSTRAINT cnstname DEFAULT CURRENT_TIMESTAMP FOR columnname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Trask" <bob@.techset.net> wrote in message news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...

> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate
()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Bob,
You need to add a constraint to the column:
ALTER TABLE [tablename]
ADD CONSTRAINT <constraintname>
DEFAULT getdate() FOR [columnB]
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Trask wrote:
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate
()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Bob
CREATE TABLE Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME NOT NULL
)
INSERT INTO Test VALUES (1,'20040101')
GO
ALTER TABLE Test ADD CONSTRAINT
myconst_col2 DEFAULT GETDATE() FOR col2
GO
INSERT INTO Test VALUES (2,DEFAULT)
GO
SELECT * FROM Test
GO
DROP TABLE Test
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate
()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Cheers People, great help thanks!
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate
()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>

Alter Column

Hi
How come this doesnt work?
ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
A default in SQL Server is handled as a constraint. So you would need something like:
ALTER TABLE tblname
ADD CONSTRAINT cnstname DEFAULT CURRENT_TIMESTAMP FOR columnname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Trask" <bob@.techset.net> wrote in message news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>
|||Bob,
You need to add a constraint to the column:
ALTER TABLE [tablename]
ADD CONSTRAINT <constraintname>
DEFAULT getdate() FOR [columnB]
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Trask wrote:
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>
|||Bob
CREATE TABLE Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME NOT NULL
)
INSERT INTO Test VALUES (1,'20040101')
GO
ALTER TABLE Test ADD CONSTRAINT
myconst_col2 DEFAULT GETDATE() FOR col2
GO
INSERT INTO Test VALUES (2,DEFAULT)
GO
SELECT * FROM Test
GO
DROP TABLE Test
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>
|||Cheers People, great help thanks!
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>

Alter Column

Hi
How come this doesnt work?
ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.A default in SQL Server is handled as a constraint. So you would need something like:
ALTER TABLE tblname
ADD CONSTRAINT cnstname DEFAULT CURRENT_TIMESTAMP FOR columnname
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Trask" <bob@.techset.net> wrote in message news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Bob,
You need to add a constraint to the column:
ALTER TABLE [tablename]
ADD CONSTRAINT <constraintname>
DEFAULT getdate() FOR [columnB]
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Trask wrote:
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Bob
CREATE TABLE Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME NOT NULL
)
INSERT INTO Test VALUES (1,'20040101')
GO
ALTER TABLE Test ADD CONSTRAINT
myconst_col2 DEFAULT GETDATE() FOR col2
GO
INSERT INTO Test VALUES (2,DEFAULT)
GO
SELECT * FROM Test
GO
DROP TABLE Test
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Cheers People, great help thanks!
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>

Allowing Transformations when Creating Publication for Replication

I am at my wits end here. For Replication the Books Online clearly state:

"The option to allow transformations is set at the time you create a publication"

However, I cannot find any options that allow me to do this in the Create Publication Wizard.

Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.

Where am I going wrong?I'd actually like to know the EXACT same thing. I'm trying to use replciation and need only to do some transformations to the data, but as you mention that option is greyed out.

All my databases are missing

In EM that is, in QA if I use:

use master
select * from sysdatabases

I get:
(6 row(s) affected)

Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 42840.Trev@.Work (no.email@.please) writes:
> In EM that is, in QA if I use:
> use master
> select * from sysdatabases
> I get:
> (6 row(s) affected)
> Server: Msg 220, Level 16, State 1, Line 1
> Arithmetic overflow error for data type smallint, value = 42840.

Oops! I assume that this is the message that you get in Enterprise
Manager?

If you don't have SP3 installed, try to install it. The bug may have been
fixed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Trev@.Work (no.email@.please) writes:
>>In EM that is, in QA if I use:
>>
>>use master
>>select * from sysdatabases
>>
>>I get:
>>(6 row(s) affected)
>>
>>Server: Msg 220, Level 16, State 1, Line 1
>>Arithmetic overflow error for data type smallint, value = 42840.
>
> Oops! I assume that this is the message that you get in Enterprise
> Manager?
> If you don't have SP3 installed, try to install it. The bug may have been
> fixed.

No EM said nothing, just didn't list anything. There was nothing under
management either and the backups hadn't run.

I restarted the service and the databases re-appeared. There are some
that I had taken offline, these are now marked as offline/suspect.

SP3a is already installed. I wonder if there's a bug with taking
databases offline?

If I query sysdatabases in QA, it was OK until I included the version
column. The offline dbs had quite high numbers here, now showing 0 in
that column, most are showing 539. I don't know the significance of this
number.|||Trev@.Work (no.email@.please) writes:
> If I query sysdatabases in QA, it was OK until I included the version
> column. The offline dbs had quite high numbers here, now showing 0 in
> that column, most are showing 539. I don't know the significance of this
> number.

That's a version number for the database format. Anything but 539 sounds
highly suspcious.

What happens if you try to bring these databases online?

If these databases are production data, and you don't have a clean backup,
I think you need to open a case with Microsoft. Something appears to be
broken.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Trev@.Work (no.email@.please) writes:
>>If I query sysdatabases in QA, it was OK until I included the version
>>column. The offline dbs had quite high numbers here, now showing 0 in
>>that column, most are showing 539. I don't know the significance of this
>>number.
>
> That's a version number for the database format. Anything but 539 sounds
> highly suspcious.
> What happens if you try to bring these databases online?
> If these databases are production data, and you don't have a clean backup,
> I think you need to open a case with Microsoft. Something appears to be
> broken.

Hi Erland, thanks for responding.

I brought all the databases online, all now show 539 for the version
except one called "WebCat", this was never taken offline as it's used on
a daily basis, this one shows null :-\.

All databases are backed up daily on a schedule, the webcat one doesn't
matter if it loses data as it's re-created every night anyway (it's just
a catalogue of files on a particular web server). I might just drop that
database and re-create it.|||Stranger still,

Taking a database offline now sets version to null, I can't however take
"WebCat" offline as it says it's in use (it isn't according to process
info), this is the one where version is already null.|||Trev@.Work (no.email@.please) writes:
> Taking a database offline now sets version to null, I can't however take
> "WebCat" offline as it says it's in use (it isn't according to process
> info), this is the one where version is already null.

I have no idea what is going on with WebCat. I guess the reason that you
see NULL for the offline databases, is because this number is derived by
actually querying the database file itself, so if the database is offline,
the number cannot gotten hold off.

I checked a little further and found that version is in fact a
computed column:

version AS (convert(smallint,databaseproperty(name,'version') ))

At least here we see the source for the conversion error you had.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have had this issue several times, and discovered a post from Dan
Carollo in 2002
that led me to the fix.

http://groups-beta.google.com/group...728290bf2776908

Perhaps the version for WebCat got corrupted and if you run the command
in QA to bring it back online, it might fix that corruption.

alter database WebCat
set online

That is what I was able to do and Enterprise Manager works again. In
the past, I have just had to reinstall SQL replacing the Master
database which takes forever!

Michelle