Monday, March 12, 2012
Help with 2 queries / Join problem
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Help with 2 queries / Join problem
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If I had to do it, I'd probably start with the following:
SELECT DISTINCT employee,
(SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
= maintable.employee GROUP BY employee) as ActiveProjectCount,
(SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
TotalProjectCountPerEmployee
FROM projectdb AS maintable
which would yield something like:
Joe510
Mary78
BrianNull1
I would then look at that Null, say Naaaah... and go about doing it right :)
FN
dwight0 wrote:
> I am having a problem with a query,
> I am not sure if i would use a join or a subquery to complete this
> problem.
> I have two queries, and i need to divide one by the other, but i cant
> seem to get any
> type of join to work with them.
> Here is the situation.
> I have a projectDB table that has a list of different projects for
> each employee to work on.
> Each project has an employee assigned to it.
> The start date is null until the employee starts to work on it.
> I want to find how many percent of all their projects that each
> employee is working on.
> In other words:
> I want to divide query A by query B to see how many percent of
> projects each employee is working on.
>
> Query A count of projects that are being worked because they have a
> date per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
> Query B: Total amount of project per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> Any ideas?|||Oh, I may also look at that first subquery and wonder why the heck I put
a GROUP BY clause when I didn't need one.
FN
fn wrote:
> If I had to do it, I'd probably start with the following:
> SELECT DISTINCT employee,
> (SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
> = maintable.employee GROUP BY employee) as ActiveProjectCount,
> (SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
> TotalProjectCountPerEmployee
> FROM projectdb AS maintable
> which would yield something like:
> Joe 5 10
> Mary 7 8
> Brian Null 1
> I would then look at that Null, say Naaaah... and go about doing it
> right :)
> FN
>
> dwight0 wrote:
>> I am having a problem with a query, I am not sure if i would use a
>> join or a subquery to complete this
>> problem.
>> I have two queries, and i need to divide one by the other, but i cant
>> seem to get any
>> type of join to work with them.
>> Here is the situation.
>> I have a projectDB table that has a list of different projects for
>> each employee to work on.
>> Each project has an employee assigned to it.
>> The start date is null until the employee starts to work on it.
>> I want to find how many percent of all their projects that each
>> employee is working on.
>> In other words:
>> I want to divide query A by query B to see how many percent of
>> projects each employee is working on.
>>
>>
>> Query A count of projects that are being worked because they have a
>> date per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>>
>>
>> Query B: Total amount of project per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>>
>> Any ideas?
Help with 2 queries / Join problem
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?
On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Help with 2 queries / Join problem
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, March 9, 2012
Help w/ Not enough storage is available to complete this operation.
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
************************************************** **************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
************************************************** **************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
************************************************** **************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
************************************************** **************************************************
I really need to get this data loaded... Someone PLEASE HELP!!
> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP
|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
Help w/ Not enough storage is available to complete this operation.
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************************************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************************************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************************************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************************************************************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
--
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > BTW, it's not a limitation on the size of my DB.. I've still got over
> > 20 GB of unused space allocated to me on the server farm.
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
Help w/ Not enough storage is available to complete this operation.
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************
************************************
************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************
************************************
************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************
************************************
************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_
trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************
************************************
************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive
?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hoste
d
> SQL Server provided by someone. If so, have you brought the error to thei
r
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.