Print Multiple Copies

Is there any way to send a quantity to the print que to deterimine the
number of copies to print?
I need to deliver a copy of a report to each department that is
reflected on areport. Let's say for example, I have a report that has
3 departments with line items to be produced by each department, can
query the count of departments and send this count to the print que to
get this report to print three copies?
This report is being developed in SRS2000.
Thanks to all who respond!On Apr 26, 11:35 am, ba_wvu <bander...@.allin.com> wrote:
> Is there any way to send a quantity to the print que to deterimine the
> number of copies to print?
> I need to deliver a copy of a report to each department that is
> reflected on areport. Let's say for example, I have a report that has
> 3 departments with line items to be produced by each department, can
> query the count of departments and send this count to the print que to
> get this report to print three copies?
> This report is being developed in SRS2000.
> Thanks to all who respond!
As far as I know, there is not an option to do this. That said, you
can group by Department as part of the table/matrix control and select
'Page break at end' as part of the grouping properties. That way you
would only need to print one copy and each Department would print on
its own page(s). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||if the departments are fixed,then what you can do is to create a subscription
and give the dept ids. so it reaches all the dept.
See if you can use this, if possible.
Amarnath
"ba_wvu" wrote:
> Is there any way to send a quantity to the print que to deterimine the
> number of copies to print?
> I need to deliver a copy of a report to each department that is
> reflected on areport. Let's say for example, I have a report that has
> 3 departments with line items to be produced by each department, can
> query the count of departments and send this count to the print que to
> get this report to print three copies?
> This report is being developed in SRS2000.
> Thanks to all who respond!
>|||Thank you both for your responses. In a response to your comments - I
currently have the report page breaking on the grouped departments,
but the floor manager wants to see all items, not just a particular
departments items, on the shop order. And the departments are not
always fixed, orders can always have different departments. So I'm a
little stuck. Any additional comments/responses are appreciated.

Primary key on combination of nullable fields, at least one not-null

I have a case where a table has two candidate primary keys,
but either (but not both) may be NULL. I don't want to store
a copy of the concatenated ISNULL'ed fields as an additional
column, though that would work if necessary. Instead, I tried
the following (this is a related simplified example, not my
real one):
CREATE FUNCTION ApplyActionPK(
@.IP int = NULL,
@.DNS varchar(64) = NULL
)
RETURNS varchar(74) -- NOT NULL
AS
BEGIN
declare @.val varchar(74)
set @.val = str(ISNULL(@.IP, 0), 10)
set @.val = @.val + ISNULL(@.DNS, '')
return @.val
-- Also tried "return str(ISNULL(@.IP, 0), 10)+ISNULL(@.DNS, '')"
-- Also tried "return ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
-- ... and other things...
END
GO
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as dbo.ApplyActionPK(ComputerID, DNS), -- PK value
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target)
)
SQL Server always complains that the primary key constraint cannot be
created over a nullable field - even though in no case will the 'Target'
field be NULL.
Please don't explain that I should store an IP address as a string.
Though that would suffice for this example, it doesn't solve my
actual problem (where there are four nullable fields, two of which
are FKs into other tables).
What's the reason for SQL Server deciding that the value is NULLable?
What's the usual way of handling such alternate PKs?
Clifford Heath.On Tue, 26 Apr 2005 15:49:23 +1000, Clifford Heath wrote:

>I have a case where a table has two candidate primary keys,
>but either (but not both) may be NULL. I don't want to store
>a copy of the concatenated ISNULL'ed fields as an additional
>column, though that would work if necessary. Instead, I tried
>the following (this is a related simplified example, not my
>real one):
(snip)
Hi Clifford,
I don't really understand the above - you say that you don't want to store
the concatenated ISNULL'ed columns, then you present a UDF (user-defined
function) that concatenates the ISNULL'ed columns and add a computed
column with the result of that UDF...

>What's the reason for SQL Server deciding that the value is NULLable?
The computed column is based on a UDF. The arguments to the UDF can be
NULL. From that, SQL Server concluded that the result might be NULL as
well. SQL Server won't check the source of the UDF for this, so regardless
of what you change in the UDF, the problem will persevere.

>What's the usual way of handling such alternate PKs?
One way around this would be to to change the table def as follows:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Another way is to include a surrogate key as primary key, and to declare
the Act, Target combination as a UNIQUE constraint. Or even omit the
computed column, ann declare (Act, IP, DNS) as UNIQUE constraint. The way
SQL Server treats NULL values in a UNIQUE constraint is not as I would
like it to be, but it is exactly what is needed for this case.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||A primary key must be non-nullable, by definition. Create another table
for the entity identified by IP/DNS and then reference that table's key
in ApplyAction. Unfortunately, SQL Server doesn't support
ANSI-compliant UNIQUE and CHECK constraints so it is much harder than
it should be to guarantee integrity.
CREATE TABLE Devices (network_address VARCHAR(64) PRIMARY KEY,
ip_address VARCHAR(15) NULL, dns_address VARCHAR(64) NULL, CHECK
(network_address IN (ip_address,dns_address) AND
COALESCE(ip_address,dns_address) IS NOT NULL) /* Key must be either IP
or DNS */)
GO
/* Views enforce nullable unique constraints */
CREATE VIEW devices_ip_address
WITH SCHEMABINDING
AS
SELECT ip_address
FROM dbo.Devices
WHERE ip_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_ip_address
ON devices_ip_address (ip_address)
GO
CREATE VIEW devices_dns_address
WITH SCHEMABINDING
AS
SELECT dns_address
FROM dbo.Devices
WHERE dns_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_dns_address
ON devices_dns_address (dns_address)
GO
David Portas
SQL Server MVP
--|||Clifford Heath (no@.spam.please.net) writes:
> What's the reason for SQL Server deciding that the value is NULLable?
Probably not a very good one. This is accepted in SQL 2005:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(IP,'')+ISNULL(DNS,'') persisted,
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Your UDF did not fly, because it had problems with determism. Not the
PERSISTED keyword, this is new for SQL 2005.
Unfortunately, the above is useless, as is Hugo's suggestion. Because
of the data-type precedence rules in SQL Server, DNS will be converted
to integer. Here is a version, ugly as it is, that works in SQL 2000:
create table ApplyAction4( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction4 PRIMARY KEY(Act, Target),
)

> What's the usual way of handling such alternate PKs?
Normally, I would go with an artificial primary key, typically an
identity column, and then have a UNIQUE constraint on (Act, IP, DNS).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hugo Kornelis wrote:
> I don't really understand the above - you say that you don't want to store
> the concatenated ISNULL'ed columns, then you present a UDF (user-defined
> function) that concatenates the ISNULL'ed columns and add a computed
> column with the result of that UDF...
Without having checked, I assumed that the UDF would be called whenever
a value was desired. I assume you're telling me that the value will be
computed at INSERT or UPDATE and stored, not computed when needed?

> The computed column is based on a UDF. The arguments to the UDF can be
> NULL. From that, SQL Server concluded that the result might be NULL as
> well. SQL Server won't check the source of the UDF for this, so regardless
> of what you change in the UDF, the problem will persevere.
However it *does* check the UDF for determinism. Plus, the return value
is defined to be VARCHAR, not VARCHAR NULL - which you can't declare :-(
so I'd expect SQL Server to enforce that a non-null value was returned.

> Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),

It appears I was close. Erland's version is identical except for using
CONVERT instead of STR, and is preferable to yours.

> Another way is to include a surrogate key as primary key
Didn't want to do that. I like to have PRIMARY declared on my natural
keys, and use unique constraints on the synthetic key, if any. Plus,
our code generator prefers things that way, though it works both ways.
:-)

> The way
> SQL Server treats NULL values in a UNIQUE constraint is not as I would
> like it to be
Nor is it what's documented in BOL :-(. Been there, fallen over that...|||Erland Sommarskog wrote:
> Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
Bingo! Convert() rather than Str().
I don't suppose I'm the only one surprised that these aren't equivalent?
Thanks everyone,
Clifford.|||On Wed, 27 Apr 2005 14:35:20 +1000, Clifford Heath wrote:

>Hugo Kornelis wrote:
>Without having checked, I assumed that the UDF would be called whenever
>a value was desired. I assume you're telling me that the value will be
>computed at INSERT or UPDATE and stored, not computed when needed?
Hi Clifford,
Yes and no :-)
Normally, a computed column is not computed at INSERT and UPDATE time and
not stored in the database; instead, the expression is evaluated when data
is read from the table. But this changes when you include the computed
column in an index - as soon as you do that, the expression will be
evaluated on INSERT and UPDATE and the result will be stored.
As far as I know, this behaviour is not different when the computed column
is based on a UDF.

>It appears I was close. Erland's version is identical except for using
>CONVERT instead of STR, and is preferable to yours.
Yep, you was. And so was I :-) Somehow, somewhere along the line I left
out the STR (which was included in your original version). I'm glad Erland
noticed that!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Clifford Heath (no@.spam.please.net) writes:
> Erland Sommarskog wrote:
> Bingo! Convert() rather than Str().
> I don't suppose I'm the only one surprised that these aren't equivalent?
I will have to admit that I have banged my head against that one as
well. But if you look at the syntax for str(), it's all clear:
STR ( float_expression [ , length [ , decimal ] ] )
Anything with float in it is imprecise and indeterministic, and a computed
column with a float expression in it - directly or indirectly - cannot be
indexed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Primary key on combination of nullable fields, at least one not-null

I have a case where a table has two candidate primary keys,
but either (but not both) may be NULL. I don't want to store
a copy of the concatenated ISNULL'ed fields as an additional
column, though that would work if necessary. Instead, I tried
the following (this is a related simplified example, not my
real one):
CREATE FUNCTION ApplyActionPK(
@.IP int = NULL,
@.DNS varchar(64) = NULL
)
RETURNS varchar(74) -- NOT NULL
AS
BEGIN
declare @.val varchar(74)
set @.val = str(ISNULL(@.IP, 0), 10)
set @.val = @.val + ISNULL(@.DNS, '')
return @.val
-- Also tried "return str(ISNULL(@.IP, 0), 10)+ISNULL(@.DNS, '')"
-- Also tried "return ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
-- ... and other things...
END
GO
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as dbo.ApplyActionPK(ComputerID, DNS), -- PK value
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target)
)
SQL Server always complains that the primary key constraint cannot be
created over a nullable field - even though in no case will the 'Target'
field be NULL.
Please don't explain that I should store an IP address as a string.
Though that would suffice for this example, it doesn't solve my
actual problem (where there are four nullable fields, two of which
are FKs into other tables).
What's the reason for SQL Server deciding that the value is NULLable?
What's the usual way of handling such alternate PKs?
Clifford Heath.On Tue, 26 Apr 2005 15:49:23 +1000, Clifford Heath wrote:
>I have a case where a table has two candidate primary keys,
>but either (but not both) may be NULL. I don't want to store
>a copy of the concatenated ISNULL'ed fields as an additional
>column, though that would work if necessary. Instead, I tried
>the following (this is a related simplified example, not my
>real one):
(snip)
Hi Clifford,
I don't really understand the above - you say that you don't want to store
the concatenated ISNULL'ed columns, then you present a UDF (user-defined
function) that concatenates the ISNULL'ed columns and add a computed
column with the result of that UDF...
>What's the reason for SQL Server deciding that the value is NULLable?
The computed column is based on a UDF. The arguments to the UDF can be
NULL. From that, SQL Server concluded that the result might be NULL as
well. SQL Server won't check the source of the UDF for this, so regardless
of what you change in the UDF, the problem will persevere.
>What's the usual way of handling such alternate PKs?
One way around this would be to to change the table def as follows:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Another way is to include a surrogate key as primary key, and to declare
the Act, Target combination as a UNIQUE constraint. Or even omit the
computed column, ann declare (Act, IP, DNS) as UNIQUE constraint. The way
SQL Server treats NULL values in a UNIQUE constraint is not as I would
like it to be, but it is exactly what is needed for this case.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||A primary key must be non-nullable, by definition. Create another table
for the entity identified by IP/DNS and then reference that table's key
in ApplyAction. Unfortunately, SQL Server doesn't support
ANSI-compliant UNIQUE and CHECK constraints so it is much harder than
it should be to guarantee integrity.
CREATE TABLE Devices (network_address VARCHAR(64) PRIMARY KEY,
ip_address VARCHAR(15) NULL, dns_address VARCHAR(64) NULL, CHECK
(network_address IN (ip_address,dns_address) AND
COALESCE(ip_address,dns_address) IS NOT NULL) /* Key must be either IP
or DNS */)
GO
/* Views enforce nullable unique constraints */
CREATE VIEW devices_ip_address
WITH SCHEMABINDING
AS
SELECT ip_address
FROM dbo.Devices
WHERE ip_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_ip_address
ON devices_ip_address (ip_address)
GO
CREATE VIEW devices_dns_address
WITH SCHEMABINDING
AS
SELECT dns_address
FROM dbo.Devices
WHERE dns_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_dns_address
ON devices_dns_address (dns_address)
GO
--
David Portas
SQL Server MVP
--|||Clifford Heath (no@.spam.please.net) writes:
> What's the reason for SQL Server deciding that the value is NULLable?
Probably not a very good one. This is accepted in SQL 2005:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(IP,'')+ISNULL(DNS,'') persisted,
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Your UDF did not fly, because it had problems with determism. Not the
PERSISTED keyword, this is new for SQL 2005.
Unfortunately, the above is useless, as is Hugo's suggestion. Because
of the data-type precedence rules in SQL Server, DNS will be converted
to integer. Here is a version, ugly as it is, that works in SQL 2000:
create table ApplyAction4( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction4 PRIMARY KEY(Act, Target),
)
> What's the usual way of handling such alternate PKs?
Normally, I would go with an artificial primary key, typically an
identity column, and then have a UNIQUE constraint on (Act, IP, DNS).
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hugo Kornelis wrote:
> I don't really understand the above - you say that you don't want to store
> the concatenated ISNULL'ed columns, then you present a UDF (user-defined
> function) that concatenates the ISNULL'ed columns and add a computed
> column with the result of that UDF...
Without having checked, I assumed that the UDF would be called whenever
a value was desired. I assume you're telling me that the value will be
computed at INSERT or UPDATE and stored, not computed when needed?
> The computed column is based on a UDF. The arguments to the UDF can be
> NULL. From that, SQL Server concluded that the result might be NULL as
> well. SQL Server won't check the source of the UDF for this, so regardless
> of what you change in the UDF, the problem will persevere.
However it *does* check the UDF for determinism. Plus, the return value
is defined to be VARCHAR, not VARCHAR NULL - which you can't declare :-(
so I'd expect SQL Server to enforce that a non-null value was returned.
> Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
It appears I was close. Erland's version is identical except for using
CONVERT instead of STR, and is preferable to yours.
> Another way is to include a surrogate key as primary key
Didn't want to do that. I like to have PRIMARY declared on my natural
keys, and use unique constraints on the synthetic key, if any. Plus,
our code generator prefers things that way, though it works both ways.
:-)
> The way
> SQL Server treats NULL values in a UNIQUE constraint is not as I would
> like it to be
Nor is it what's documented in BOL :-(. Been there, fallen over that...|||Erland Sommarskog wrote:
> Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
Bingo! Convert() rather than Str().
I don't suppose I'm the only one surprised that these aren't equivalent?
Thanks everyone,
Clifford.|||On Wed, 27 Apr 2005 14:35:20 +1000, Clifford Heath wrote:
>Hugo Kornelis wrote:
>> I don't really understand the above - you say that you don't want to store
>> the concatenated ISNULL'ed columns, then you present a UDF (user-defined
>> function) that concatenates the ISNULL'ed columns and add a computed
>> column with the result of that UDF...
>Without having checked, I assumed that the UDF would be called whenever
>a value was desired. I assume you're telling me that the value will be
>computed at INSERT or UPDATE and stored, not computed when needed?
Hi Clifford,
Yes and no :-)
Normally, a computed column is not computed at INSERT and UPDATE time and
not stored in the database; instead, the expression is evaluated when data
is read from the table. But this changes when you include the computed
column in an index - as soon as you do that, the expression will be
evaluated on INSERT and UPDATE and the result will be stored.
As far as I know, this behaviour is not different when the computed column
is based on a UDF.
>> Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
>It appears I was close. Erland's version is identical except for using
>CONVERT instead of STR, and is preferable to yours.
Yep, you was. And so was I :-) Somehow, somewhere along the line I left
out the STR (which was included in your original version). I'm glad Erland
noticed that!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Clifford Heath (no@.spam.please.net) writes:
> Erland Sommarskog wrote:
>> Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
> Bingo! Convert() rather than Str().
> I don't suppose I'm the only one surprised that these aren't equivalent?
I will have to admit that I have banged my head against that one as
well. But if you look at the syntax for str(), it's all clear:
STR ( float_expression [ , length [ , decimal ] ] )
Anything with float in it is imprecise and indeterministic, and a computed
column with a float expression in it - directly or indirectly - cannot be
indexed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

'PRIMARY' filegroup is full problem

Hi
I have a script that creates a table in a database and populates it using
bulk copy from a comma-delimited file.
The number of rows of data to be entered is fairly large (approximately 24
million rows of data).
However, last night this fell over with the error message:

quote:

---

>Could not allocate space for object 'myTableName' in database

---

'myDatabase'

quote:

---

>because the 'PRIMARY' filegroup is full.

---

Using the Enterprise manager, I click on the database symbol and choose
"properties". The size of the database is '8132 MB' and it claims the space
available is '0.00 MB'.
However, on both the data file and the log file, I've got the following
properties set:
Automatically Grow File: YES
By Percent: 10%
Maximum file size: Unrestricted file growth
I've checked the disk and there's ~50 GB of free disk space, and it's fairly
contiguous space too.
Is my problem just that I need to increase the % growth from the current
value of 10% to something higher? I ask only because I have another
database that is smaller (but comparable in size) and this sort of data
import works with a growth size set of just 5%.
A second related question is whether there is a way to identify what % of
the database size is taken up by one particular (existing) table? If I knew
this, I'm sure I could answer the first question definitively myself.
Thanks in advance
GriffIt probably timed out before it could allocate the new space for the file.
You should never rely on autogrow and always ensure you have plenty of free
space in the database and log files. If you know your going to do a large
load you should check beforehand and manually grow the files before you
attempt the load. I would increase it an try again.
Andrew J. Kelly
SQL Server MVP
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OUSGyfO4DHA.2332@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> I have a script that creates a table in a database and populates it using
> bulk copy from a comma-delimited file.
> The number of rows of data to be entered is fairly large (approximately 24
> million rows of data).
> However, last night this fell over with the error message:
>
> 'myDatabase'
> Using the Enterprise manager, I click on the database symbol and choose
> "properties". The size of the database is '8132 MB' and it claims the

---

space

quote:

---

> available is '0.00 MB'.
> However, on both the data file and the log file, I've got the following
> properties set:
> Automatically Grow File: YES
> By Percent: 10%
> Maximum file size: Unrestricted file growth
> I've checked the disk and there's ~50 GB of free disk space, and it's

---

fairly

quote:

---

> contiguous space too.
> Is my problem just that I need to increase the % growth from the current
> value of 10% to something higher? I ask only because I have another
> database that is smaller (but comparable in size) and this sort of data
> import works with a growth size set of just 5%.
> A second related question is whether there is a way to identify what % of
> the database size is taken up by one particular (existing) table? If I

---

knew

quote:

---

> this, I'm sure I could answer the first question definitively myself.
> Thanks in advance
> Griff
>
>

---

|||Andrew
Thanks for your response. Can I ask though why one should never rely on the
"auto-grow" feature? Is it one of those features that "does not always do
what it says on the tin"...
Cheers
Griff|||The reason is just what you (probably) have got -- in an application you
have too large a data load, that grows the db size for which it needs such a
long time that your application times out.
To look at the size of a table run
sp_spaceused TableName, true
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:u521Q9O4DHA.488@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Andrew
> Thanks for your response. Can I ask though why one should never rely on

---

the

quote:

---

> "auto-grow" feature? Is it one of those features that "does not always do
> what it says on the tin"...
> Cheers
> Griff
>
>

---

|||Quentin is correct. Also growing the db is an expensive process and
something you don't want to happen when the users are making updates in the
db if you can help it. It is better to manually (or schedule it) grow the
db during off peak times so as not to impact the users.
Andrew J. Kelly
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:%236Wzg7Q4DHA.1704@.tk2msftngp13.phx.gbl...

quote:

---

> The reason is just what you (probably) have got -- in an application you
> have too large a data load, that grows the db size for which it needs such

---

a

quote:

---

> long time that your application times out.
> To look at the size of a table run
> sp_spaceused TableName, true
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:u521Q9O4DHA.488@.TK2MSFTNGP12.phx.gbl...
> the
do[QUOTE]
>

---

'PRIMARY' filegroup is full problem

Hi
I have a script that creates a table in a database and populates it using
bulk copy from a comma-delimited file.
The number of rows of data to be entered is fairly large (approximately 24
million rows of data).
However, last night this fell over with the error message:
>Could not allocate space for object 'myTableName' in database
'myDatabase'
>because the 'PRIMARY' filegroup is full.
Using the Enterprise manager, I click on the database symbol and choose
"properties". The size of the database is '8132 MB' and it claims the space
available is '0.00 MB'.
However, on both the data file and the log file, I've got the following
properties set:
Automatically Grow File: YES
By Percent: 10%
Maximum file size: Unrestricted file growth
I've checked the disk and there's ~50 GB of free disk space, and it's fairly
contiguous space too.
Is my problem just that I need to increase the % growth from the current
value of 10% to something higher? I ask only because I have another
database that is smaller (but comparable in size) and this sort of data
import works with a growth size set of just 5%.
A second related question is whether there is a way to identify what % of
the database size is taken up by one particular (existing) table? If I knew
this, I'm sure I could answer the first question definitively myself.
Thanks in advance
GriffIt probably timed out before it could allocate the new space for the file.
You should never rely on autogrow and always ensure you have plenty of free
space in the database and log files. If you know your going to do a large
load you should check beforehand and manually grow the files before you
attempt the load. I would increase it an try again.
--
Andrew J. Kelly
SQL Server MVP
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OUSGyfO4DHA.2332@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a script that creates a table in a database and populates it using
> bulk copy from a comma-delimited file.
> The number of rows of data to be entered is fairly large (approximately 24
> million rows of data).
> However, last night this fell over with the error message:
> >Could not allocate space for object 'myTableName' in database
> 'myDatabase'
> >because the 'PRIMARY' filegroup is full.
> Using the Enterprise manager, I click on the database symbol and choose
> "properties". The size of the database is '8132 MB' and it claims the
space
> available is '0.00 MB'.
> However, on both the data file and the log file, I've got the following
> properties set:
> Automatically Grow File: YES
> By Percent: 10%
> Maximum file size: Unrestricted file growth
> I've checked the disk and there's ~50 GB of free disk space, and it's
fairly
> contiguous space too.
> Is my problem just that I need to increase the % growth from the current
> value of 10% to something higher? I ask only because I have another
> database that is smaller (but comparable in size) and this sort of data
> import works with a growth size set of just 5%.
> A second related question is whether there is a way to identify what % of
> the database size is taken up by one particular (existing) table? If I
knew
> this, I'm sure I could answer the first question definitively myself.
> Thanks in advance
> Griff
>
>|||Andrew
Thanks for your response. Can I ask though why one should never rely on the
"auto-grow" feature? Is it one of those features that "does not always do
what it says on the tin"...
Cheers
Griff|||The reason is just what you (probably) have got -- in an application you
have too large a data load, that grows the db size for which it needs such a
long time that your application times out.
To look at the size of a table run
sp_spaceused TableName, true
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:u521Q9O4DHA.488@.TK2MSFTNGP12.phx.gbl...
> Andrew
> Thanks for your response. Can I ask though why one should never rely on
the
> "auto-grow" feature? Is it one of those features that "does not always do
> what it says on the tin"...
> Cheers
> Griff
>
>|||Quentin is correct. Also growing the db is an expensive process and
something you don't want to happen when the users are making updates in the
db if you can help it. It is better to manually (or schedule it) grow the
db during off peak times so as not to impact the users.
--
Andrew J. Kelly
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:%236Wzg7Q4DHA.1704@.tk2msftngp13.phx.gbl...
> The reason is just what you (probably) have got -- in an application you
> have too large a data load, that grows the db size for which it needs such
a
> long time that your application times out.
> To look at the size of a table run
> sp_spaceused TableName, true
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:u521Q9O4DHA.488@.TK2MSFTNGP12.phx.gbl...
> > Andrew
> >
> > Thanks for your response. Can I ask though why one should never rely on
> the
> > "auto-grow" feature? Is it one of those features that "does not always
do
> > what it says on the tin"...
> >
> > Cheers
> >
> > Griff
> >
> >
> >
>