pre-execute failure

I just started getting this error.

[DTS.Pipeline] Error: component "User Type" (377) failed the pre-execute phase and returned error code 0x8007000E.

It wasn't happening before. Does anyone know what it means?

Hi Jim,
Without more information it is probably impossible to say.

What type of component is it?
What are you using it for?
How have you configured it?
When do you get the error - when the package starts or when the data-flow starts?
What inputs does it take?

etc...etc...

Regards
Jamie|||

Excelent questions.
This s a dataflow component. All of it's input is from a table. It seems like just rearranging the dataflow components in the work flow eliminates the problem. Since it isn't happening any longer, I can't do a better job answering your questions.

I don't understand it.

|||Incidentally, that error is Out Of Memory, so there could have been some transient cause.... Please let us know if you do come across a repro.sql

Precision and Scale in a calulated column

How do I set Precision and Scale in a calulated column?

I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please?

Thanks,

Scott

hi Scott,

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE #t (

d decimal(18,4) DEFAULT 1234.123,

D2 AS CONVERT(decimal(8,2), d)

);

GO

INSERT INTO #t DEFAULT VALUES;

SELECT * FROM #t;

GO

DROP TABLE #t;

--<

d D2

--

1234.1230 1234.12

regards

|||

Either using a CREATE TABLE or ALTER TABLE statement, and add the precision,scale to the computed column declaration.

Here is an example:

CREATE TABLE #MyTable
( RowID int IDENTITY,
Cost decimal(6,2),
Quantity int,
Total AS cast(( Cost * Quantity ) AS decimal(8,4))
)

INSERT INTO #MyTable VALUES ( 5, 10 )
INSERT INTO #MyTable VALUES ( 2.5, 5 )
INSERT INTO #MyTable VALUES ( 4.55, 5 )

SELECT * FROM #MyTable

DROP TABLE #MyTable

|||

Thank you for helping.

I'm using the Management Studio. Is there a way to accomplish this in the formula line?

Thanks again.

Scott

|||

Thanks for helping.

I tried the following:

***********************************

Use SIR
ALTER TABLE dbo.Table_1
ALTER COLUMN Results
nPRECISION(6,2)

Go

************************************

Recieved the following message:

************************************

Msg 4928, Level 16, State 1, Line 2
Cannot alter column 'Results' because it is 'COMPUTED'.

What else might I try?

|||

You need to first DROP the computed column, then ADD it back, and you MUST include the computation formula.

Do something like this:

USE SIR;

ALTER TABLE dbo.Table_1
DROP COLUMN Results;

ALTER TABLE dbo.Table_1
ADD COLUMN Results cast( ( [put formula here] ) AS decimal(6,2)));

Possible?: Count(*) returned by EXEC

Hi all,

I have a stored procdure which does a select and returns the records
directly -i.e. Not in output parameters e.g:

CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS

SELECT *
FROM MyTable
WHERE [Name]=@.ProductName

In another stored procedure I need to do the following:

SELECT COUNT(*)
FROM MyTable
WHERE [Name]=@.ProductName

As the select queries are actually a lot more complex that this, I'd
rather not duplicate the select code in 2 sp's to save the maintenance
effort - I'm looking for a way to execute the first procedure from the
second and just count the records returned - something like:

SELECT Count(*)
FROM EXEC up_SelectRecs @.ProductName

Any way to achieve this?

Thanks all

--James"James" <Jamesmitchard@.yahoo.co.uk> wrote in message
news:19d01a84.0501261535.1d7c6dd7@.posting.google.c om...
> Hi all,
> I have a stored procdure which does a select and returns the records
> directly -i.e. Not in output parameters e.g:
> CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS
> SELECT *
> FROM MyTable
> WHERE [Name]=@.ProductName
> In another stored procedure I need to do the following:
> SELECT COUNT(*)
> FROM MyTable
> WHERE [Name]=@.ProductName
> As the select queries are actually a lot more complex that this, I'd
> rather not duplicate the select code in 2 sp's to save the maintenance
> effort - I'm looking for a way to execute the first procedure from the
> second and just count the records returned - something like:
> SELECT Count(*)
> FROM EXEC up_SelectRecs @.ProductName
> Any way to achieve this?
> Thanks all
> --James

See here:

http://www.sommarskog.se/share_data.html

If you have SQL 2000 (you didn't mention which version you have), a
table-valued UDF would probably work well in your case:

select * from dbo.MyFunc(@.ProductName)
select count(*) from dbo.MyFunc(@.ProductName)

Simon

Possible tempdb problems

I have a simple TOP X select statement that is returning
one less than the TOP X number. If I check the records
affected verses records returned it is always 1 less. I
ran DBCC CHECKDB on all databases and I got some errors
when I ran it on tempdb. I can't set single user access to
tempdb so I can't repair it. Any suggestions? Here are
the errors:
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB
processing.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:106) 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 8905, Level 16, State 1, Line 1
Extent (1:3096) 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:3176) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 3 allocation errors and 0 consistency errors
not associated with any single object.
CHECKDB found 3 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 ).Restart SQL Server, and tempdb will re-create itself from scratch.
"Brian Tax" <brian.tax@.milliman.com> wrote in message
news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
> I have a simple TOP X select statement that is returning
> one less than the TOP X number. If I check the records
> affected verses records returned it is always 1 less. I
> ran DBCC CHECKDB on all databases and I got some errors
> when I ran it on tempdb. I can't set single user access to
> tempdb so I can't repair it. Any suggestions? Here are
> the errors:
> Server: Msg 8999, Level 16, State 1, Line 1
> Database tempdb allocation errors prevent further CHECKDB
> processing.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:106) 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 8905, Level 16, State 1, Line 1
> Extent (1:3096) 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:3176) in database ID 2 is marked allocated in
> the GAM, but no SGAM or IAM has allocated it.
> CHECKDB found 3 allocation errors and 0 consistency errors
> not associated with any single object.
> CHECKDB found 3 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 ).|||We rebooted twice yesterday and it was still doing the
same thing. Now this morning the SELECT TOP X was working,
but it still gave errors in tempdb.
>--Original Message--
>Restart SQL Server, and tempdb will re-create itself from
scratch.
>
>"Brian Tax" <brian.tax@.milliman.com> wrote in message
>news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
>> I have a simple TOP X select statement that is returning
>> one less than the TOP X number. If I check the records
>> affected verses records returned it is always 1 less. I
>> ran DBCC CHECKDB on all databases and I got some errors
>> when I ran it on tempdb. I can't set single user access
to
>> tempdb so I can't repair it. Any suggestions? Here are
>> the errors:
>> Server: Msg 8999, Level 16, State 1, Line 1
>> Database tempdb allocation errors prevent further
CHECKDB
>> processing.
>> Server: Msg 8906, Level 16, State 1, Line 1
>> Page (1:106) 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 8905, Level 16, State 1, Line 1
>> Extent (1:3096) 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:3176) in database ID 2 is marked allocated in
>> the GAM, but no SGAM or IAM has allocated it.
>> CHECKDB found 3 allocation errors and 0 consistency
errors
>> not associated with any single object.
>> CHECKDB found 3 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 ).
>
>.
>