Change Image

I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..

simple sort the data by company name|||I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..

Just sort the data by company name|||what do you mean by sort data by company name?

Change Format of Dates

I don't know why my company did this, but dates are being stored in a char field within our database. Meanwhile, I've been setting up new pages using datetime.

That's just a little back story. My question is, is it possible to change the format of all dates in the table from yyyy/MM/dd to MM/dd/yyyy in the char field? I'm just trying to think of an easier way to change a thousand or so records instead of doing it manually.

Thanks.

Try this:

string temp ="2006/12/16";
string[] field = temp.Split(("/").ToCharArray());
string reversedate = field[2] +"/" + field[1] +"/" + field[0];
DateTime newdate = DateTime.Parse(reversedate);

|||

Or better still, run this SQL

Update table set newdatefield = parsename(replace(oldchardate, '/', '.'), 2) + '/' + parsename(replace(oldchardate, '/', '.'), 1) + '/' + parsename(replace(oldchardate, '/', '.'), 3)

|||

try this logic

print

convert(varchar(20),convert(datetime,'2002/02/23',111),101)

so you can just do update on your table date field content by

update YourTable
set datefield=convert(varchar(20),convert(datetime,datefield,111),101)

Thanks

|||

I ended up using this logic in a SQL statement:

datefield=right(datefield, 5)&'/'&left(datefield, 4)

sql

change data types

Hi,
The company I work is updating our software for
localization/internationalization. Unfortunately, when we built our backend
design, we did not foresee needing to go international. So almost all of
our text data types are not built for internationalization. Basically, the
only fields we really need to change are text and varchar, to ntext and
nvarchar. Are there any issues with running a SQL script as part of a
service pack, in order to change these data types in the tables?
If there are or aren't any problems, are there any articles someone can
point me to. Most articles I have found on localization deal specifically
with creating a new application.
So in summary, I need to know if there are any issues with changing a text
to an ntext and varchar to an nvarchar. Additionally, there may be some
char fields that need to be changed to nchar.
Any help/direction/articles would be very much appreciated. Thank you.
Paul
Well, one issue that pops to mind immediately is that if you have a
VARCHAR(8000) and it has 4001 characters or more, you're going to lose data.
NVARCHAR can only go to 4000, since every character takes two bytes.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Paul" <paul@.gotheta.com> wrote in message
news:ebUJn1sMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> The company I work is updating our software for
> localization/internationalization. Unfortunately, when we built our
backend
> design, we did not foresee needing to go international. So almost all of
> our text data types are not built for internationalization. Basically,
the
> only fields we really need to change are text and varchar, to ntext and
> nvarchar. Are there any issues with running a SQL script as part of a
> service pack, in order to change these data types in the tables?
> If there are or aren't any problems, are there any articles someone can
> point me to. Most articles I have found on localization deal specifically
> with creating a new application.
> So in summary, I need to know if there are any issues with changing a text
> to an ntext and varchar to an nvarchar. Additionally, there may be some
> char fields that need to be changed to nchar.
> Any help/direction/articles would be very much appreciated. Thank you.
> Paul
>

change case sensitivity after database set up

I have a SQL Server database hosted with a web hosting company. The
SQL Server was clearly set up to be case sensitive, however, I want
this particular database to be case-insensitive.

I have searched high and low, the best suggestion I can find is to
reinstall SQL Server and select case-insensitive. But since this is
the web host's SQL, that isn't an option here.

With default language I can use the sp_defaultlanguage to change to
British settings (for example). Is there something similar I can use
to make just this database case insensitive?

--

Popular uprising?
http://www.blairfacedlies.org/statue.htm

captain(underscore)flack(squirlything)hotmail(you know what)comIf you are using SQL 2000, you can change the default database collation
with ALTER DATABASE:

ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS

Note that a column collation is determined when the column is created so
changing the database setting will not affect existing tables. You'll
need to alter existing columns to the desired collation or recreate the
tables. The collation used for identifiers (table names, column names,
etc.) will take affect immediately.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Captain Flack" <captain.flack@.trumpton-firebrigade.dontevenbother>
wrote in message news:o7qhqv8tpvglr61idpqj24kkaqf8p1cruq@.4ax.com...
> I have a SQL Server database hosted with a web hosting company. The
> SQL Server was clearly set up to be case sensitive, however, I want
> this particular database to be case-insensitive.
> I have searched high and low, the best suggestion I can find is to
> reinstall SQL Server and select case-insensitive. But since this is
> the web host's SQL, that isn't an option here.
> With default language I can use the sp_defaultlanguage to change to
> British settings (for example). Is there something similar I can use
> to make just this database case insensitive?
>
>
> --
> Popular uprising?
> http://www.blairfacedlies.org/statue.htm
> captain(underscore)flack(squirlything)hotmail(you know what)com|||On Wed, 05 Nov 2003 13:36:34 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>If you are using SQL 2000, you can change the default database collation
>with ALTER DATABASE:
> ALTER DATABASE MyDatabase
> COLLATE SQL_Latin1_General_CP1_CI_AS
>Note that a column collation is determined when the column is created so
>changing the database setting will not affect existing tables. You'll
>need to alter existing columns to the desired collation or recreate the
>tables. The collation used for identifiers (table names, column names,
>etc.) will take affect immediately.

Thanks, I will test this out. It is SQL 2000 so it looks to be exactly
what I was looking for :)

--

Popular uprising?
http://www.blairfacedlies.org/statue.htm

captain(underscore)flack(squirlything)hotmail(you know what)com

change all char columns to varchar

Hi ,
Our company decided to convert all char columns to varchar in our next
software release.
We have lot (40+) of client databases which is more than 75GB .
Below the query I wrote for this ,but it's taking long long hours to
complete the upgrade.
Is there any better way to write this '
Thanks
--*******************************************************************
-- Script to convert all char columns to varchar
--*******************************************************************
declare @.tbname varchar(255)
declare @.column_name varchar(255)
declare @.is_nullable varchar(255)
declare @.character_maximum_length varchar(255)
declare @.Query varchar(8000)
declare c1 cursor for
select
collist.table_name,collist.column_name,collist.is_nullable,collist.character
_maximum_length from information_schema.columns
collist,information_schema.tables tablist
where collist.table_name=tablist.table_name and tablist.table_type='BASE
TABLE' and data_type='char'
open c1
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
while @.@.fetch_status=0
begin
if (@.is_nullable='No')
set @.is_nullable='NOT NULL'
else
set @.is_nullable='NULL'
set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
VARCHAR('+@.character_maximum_length+') '+@.is_nullable
exec (@.Query)
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
end
deallocate c1
GO
PRINT '** Script 07 -- 07.char to varchar.sql completed **'
goI think you can change the Cursor to table variable ... Add Set NOCOUNT ON
to remove the un-necessary verbose of "n rows affected" ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.character
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>|||Each ALTER TABLE statement will require updating every row in the table.
This will take quite some time if you have many char columns in a single
table because you'll be updating each row many times. Furthermore, this
will not trim trailing spaces from the char column and leave a lot of
other wasted space in the table.
You will be better off creating a set of tables with the new structure
and loading via DTS. You can trim spaces during the process. This
method will provide the additional flexibility of allowing you to keep
existing char datatypes where appropriate.
Another method is to create new tables using SELECT INTO. This can be a
bit tricky and is problematic with identity columns. Example below.
CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 char(10) NOT NULL,
Col3 char(10) NULL
)
INSERT INTO MyTable VALUES (1, 'a', 'b')
GO
SELECT
Col1,
-- need to specify ISNULL to create a NOT NULL
ISNULL(CAST(RTRIM(Col2) AS varchar(10)), '') AS Col2,
CAST(RTRIM(Col2) AS varchar(10)) AS Col3
INTO MyTable_New
FROM MyTable
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.chara
cter
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and
tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>

Centralised Database

Hi All,
I've been tasked at looking into a solution for a global data project for my
company.
The company uses some software that requires a local database, the local
databases are going to be located in Australia, US, & UK, the local database
s
will have exact copies of each others schema. They would like the UK &
Australia databases to update the US database (effectively making the US
database centralized).
Conceptually whats the best way of doing this? My thoughts would be to have
the 3 local databases and create an additional database to centralize the
three using DTS or replication... ...there would be no overlapping data in
any of the three databases (except identity based keys), however data would
be updated. The only caviate I have to adhere to is the centralized databas
e
must be no more than 30 minutes out of date (preferably no more than 15
minutes).
I've had a good year and half experience of being a DBA, but have never
really had to touch on replication and distributed data, so any thoughts or
views you have would be greatly appreciated.
Kind regards
BenI think your case is the simplest one, no data overlapping, you can use any
way to update data.
I would like use webservice, it is easy to control and no problem on any
firewall.
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:60AC4511-63FA-4709-A5BD-84A091750BD6@.microsoft.com...
> Hi All,
> I've been tasked at looking into a solution for a global data project for
my
> company.
> The company uses some software that requires a local database, the local
> databases are going to be located in Australia, US, & UK, the local
databases
> will have exact copies of each others schema. They would like the UK &
> Australia databases to update the US database (effectively making the US
> database centralized).
> Conceptually whats the best way of doing this? My thoughts would be to
have
> the 3 local databases and create an additional database to centralize the
> three using DTS or replication... ...there would be no overlapping data in
> any of the three databases (except identity based keys), however data
would
> be updated. The only caviate I have to adhere to is the centralized
database
> must be no more than 30 minutes out of date (preferably no more than 15
> minutes).
> I've had a good year and half experience of being a DBA, but have never
> really had to touch on replication and distributed data, so any thoughts
or
> views you have would be greatly appreciated.
> Kind regards
> Ben

Centralised Database

Hi All,
I've been tasked at looking into a solution for a global data project for my
company.
The company uses some software that requires a local database, the local
databases are going to be located in Australia, US, & UK, the local databases
will have exact copies of each others schema. They would like the UK &
Australia databases to update the US database (effectively making the US
database centralized).
Conceptually whats the best way of doing this? My thoughts would be to have
the 3 local databases and create an additional database to centralize the
three using DTS or replication... ...there would be no overlapping data in
any of the three databases (except identity based keys), however data would
be updated. The only caviate I have to adhere to is the centralized database
must be no more than 30 minutes out of date (preferably no more than 15
minutes).
I've had a good year and half experience of being a DBA, but have never
really had to touch on replication and distributed data, so any thoughts or
views you have would be greatly appreciated.
Kind regards
Ben
I think your case is the simplest one, no data overlapping, you can use any
way to update data.
I would like use webservice, it is easy to control and no problem on any
firewall.
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:60AC4511-63FA-4709-A5BD-84A091750BD6@.microsoft.com...
> Hi All,
> I've been tasked at looking into a solution for a global data project for
my
> company.
> The company uses some software that requires a local database, the local
> databases are going to be located in Australia, US, & UK, the local
databases
> will have exact copies of each others schema. They would like the UK &
> Australia databases to update the US database (effectively making the US
> database centralized).
> Conceptually whats the best way of doing this? My thoughts would be to
have
> the 3 local databases and create an additional database to centralize the
> three using DTS or replication... ...there would be no overlapping data in
> any of the three databases (except identity based keys), however data
would
> be updated. The only caviate I have to adhere to is the centralized
database
> must be no more than 30 minutes out of date (preferably no more than 15
> minutes).
> I've had a good year and half experience of being a DBA, but have never
> really had to touch on replication and distributed data, so any thoughts
or
> views you have would be greatly appreciated.
> Kind regards
> Ben

Centralised Database

Hi All,
I've been tasked at looking into a solution for a global data project for my
company.
The company uses some software that requires a local database, the local
databases are going to be located in Australia, US, & UK, the local databases
will have exact copies of each others schema. They would like the UK &
Australia databases to update the US database (effectively making the US
database centralized).
Conceptually whats the best way of doing this? My thoughts would be to have
the 3 local databases and create an additional database to centralize the
three using DTS or replication... ...there would be no overlapping data in
any of the three databases (except identity based keys), however data would
be updated. The only caviate I have to adhere to is the centralized database
must be no more than 30 minutes out of date (preferably no more than 15
minutes).
I've had a good year and half experience of being a DBA, but have never
really had to touch on replication and distributed data, so any thoughts or
views you have would be greatly appreciated.
Kind regards
BenI think your case is the simplest one, no data overlapping, you can use any
way to update data.
I would like use webservice, it is easy to control and no problem on any
firewall.
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:60AC4511-63FA-4709-A5BD-84A091750BD6@.microsoft.com...
> Hi All,
> I've been tasked at looking into a solution for a global data project for
my
> company.
> The company uses some software that requires a local database, the local
> databases are going to be located in Australia, US, & UK, the local
databases
> will have exact copies of each others schema. They would like the UK &
> Australia databases to update the US database (effectively making the US
> database centralized).
> Conceptually whats the best way of doing this? My thoughts would be to
have
> the 3 local databases and create an additional database to centralize the
> three using DTS or replication... ...there would be no overlapping data in
> any of the three databases (except identity based keys), however data
would
> be updated. The only caviate I have to adhere to is the centralized
database
> must be no more than 30 minutes out of date (preferably no more than 15
> minutes).
> I've had a good year and half experience of being a DBA, but have never
> really had to touch on replication and distributed data, so any thoughts
or
> views you have would be greatly appreciated.
> Kind regards
> Ben

Cell Data Security question

I have a cube that needs to filter out data for a group of users based on the company they work for.

I thought about using the following MDX expression in the Cell Data section in the Edit Roles window for this particular cube:

Ancestor([Dim OPCO].CurrentMember,[OPCO]) IS [COMPANY NAME]

The issue is that the users have to select there particular OPCO COMPANY Name in which ever tool they are using otherwise they will only see NA as the value for all measures.

I would like to filter the data somehow so they do not need to select the particular company they are working for. I would then setup different role groups based on the users company.

Is there some other way of accomplishing this type of task using a filter or scope command?

Thanks ahead of time

Steve Fibich

Hi Steve,

Once you have your roles in place, how about using member security to only permit the company users to see the member for their company? (And make this the default member for that particular role - I assume this is possible, but have yet to do it myself!).

An alternative is to have separate cubes for each company, which I can appreciate is a considerable amount of overhead.

Regards,

Will.

|||

Will,

This sounds like a good solution, how do you set the default propertie in the role. I tried the the following MDX but I get an error when I try to open the Cube.

Under default Member for that particular dimension

[Dim OPCO].[Opco Desc].MemberValue =[Company Name]

I am pretty new to MDX so if this statement is incorrect please let me know. Also is there a good place to get actual examples of MDX vs. the description provided through most of the MS MSDN help sites.

Thanks,

Steve

|||

Never mind I just found the MDX GUI Tool ....I am a little slow today.

Thanks for your help.

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.

> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.

> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1

> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.

> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.

> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.

> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1

> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.

> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>> Hi John ,
>> From your descriptions, I understood that your tempdb is growing
>> unexpected and allocation errors with DBCC CHECKDB. Have I understood
>> you? Correct me if I was wrong.
>> First of all, have you upgraded your SQL Server to latest update?
>> Then, you could perform stop SQL Server services, delete existing
>> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
>> recreated every time service is restarted.
>> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
>> is clean now.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>