Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:

I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.

Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:

I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.

sql

Out-of-Memory error on Sort Transformation

Trying to determine if anyone else has encountered this problem and, if so, have you obtained a resolution:

I have a data flow initiated from a Foreach Container. Inside the Data Flow there is a Source Script transformation connected to a Sort transformation with an OLE DB Destination.

The Sort transformation has the sort keys defined and the "Eliminate Duplicates" option checked.

The first pass into the Data Flow is successful. The 2nd pass fails in the Sort:

Information: 0x4004300A at Load TC03, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Load TC03, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Load TC03, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Load TC03, DTS.Pipeline: Execute phase is beginning.
Warning: 0x80004005 at Load TC03, Sort [1506]: Unspecified error
Error: 0xC020821B at Load TC03, Sort [1506]: A worker thread in the Sort transformation stopped with error code 0x80004005. A catastrophic error was encountered while sorting a buffer.
Error: 0xC020821B at Load TC03, Sort [1506]: A worker thread in the Sort transformation stopped with error code 0x%1!8.8X!. A catastrophic error was encountered while sorting a buffer.
Error: 0xC0208296 at Load TC03, Sort [1506]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.
Error: 0xC0047022 at Load TC03, DTS.Pipeline: The ProcessInput method on component "Sort" (1506) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load TC03, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x80004005.
Error: 0xC0047039 at Load TC03, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load TC03, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Information: 0x40043008 at Load TC03, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Load TC03, hlp_ISOTypeLossTC03 [379]: The final commit for the data insertion has started.
Information: 0x402090E0 at Load TC03, hlp_ISOTypeLossTC03 [379]: The final commit for the data insertion has ended.
Information: 0x40043009 at Load TC03, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Load TC03, DTS.Pipeline: "component "hlp_ISOTypeLossTC03" (379)" wrote 5388 rows.
Task failed: Load TC03
Warning: 0x80019002 at Foreach TC03 File: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at ISOTypeLoss: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ISOTypeLoss.dtsx" finished: Failure.

Does this ring a bell with anyone?

ThanksI think this is a known issue that they are currently looknig into.
its not necassarily a problem with the Sort, its more caused by the Foreach loop (I think).

Check out thread "For Loop container - out of memory condition"

-Jamie|||

Hi Raul,

Yes, this is a known issue. We've fixed it for the next CTP. In the meantime, the only workaround I'm aware of is to put the data flow task in a separate package and use an Execute Package task to schedule the execution of the data flow task.
regards,
ash

|||What volume of data do you get this error on?

It would be interesting to see if ExtraSort component (http://www.ivolva.com/extrasort.html) would hit the same problem.|||Very small - about 300 rows|||Ash:

I have the June CTP installed and am getting the same error:

Warning: 0x80004005 at Load V6 Policy, Sort [4166]: Unspecified error

Error: 0xC020821B at Load V6 Policy, Sort [4166]: A worker thread in the Sort transformation stopped with error code 0x80004005. A catastrophic error was encountered while sorting a buffer.

Error: 0xC020821B at Load V6 Policy, Sort [4166]: A worker thread in the Sort transformation stopped with error code 0x%1!8.8X!. A catastrophic error was encountered while sorting a buffer.

Error: 0xC0208296 at Load V6 Policy, Sort [4166]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.

Error: 0xC0047022 at Load V6 Policy, DTS.Pipeline: The ProcessInput method on component "Sort" (4166) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x80004005.

Error: 0xC0047039 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Another poster suggested a thread for "For Loop container - out of memory condition" - I searched this forum and it returned no results.

Any update as to whether the fix made the June CTP?

|||

Raul Villaronga wrote:

Any update as to whether the fix made the June CTP?

It didn't. It'll be in the next CTP.|||Yes, this was caused by a bug in the sort transform. It was not correctly releasing some resources when the dataflow completed. The forloop just exposes the problem because the sort doesn't get freed between executions.|||FYI - Still getting this in September CTP.
|||Both memory issues I had with June CTP including the catastrophic failure of the sort in a loop and the data viewer consuming memory have been fixed in September CTP.

Out-of_process error when trying to process a dimension

In my dsv I have a table called OrderDetail. This table has a salescodeid column in it. I have a second table called SalesCodeDetail. There is a relationship between these two tables based on the salescodeid. The Id is the primary key in the SalesCodeDetail table. In my cube I have a dimension of OrderDetail. When I try to add the salescode description from the salescodeDetail table as an attribute in my OrderDetail dimension I get this error.

Here is a more detailed description of this same problem.

I have a fact table that is joined to a dimension table 'Customer' by the customer number. Customer Number is the primary key in the 'Customer' view. I have a second Dimension table called 'SalesRep' which is referenced to the fact table through the customer. The join is SalesRepId, which is primary key on the SalesRep view, and SalesRepId which is on the 'Customer' view but not a key. Also, this relationship between SalesRep and customer does exist in the DSV.

When I try to process this relationship I get the following error: 'OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000.'

The SQL behind the processing is doing an 'OPENROWSET' which is causing the problem. If I take the 'OPENROWSET' out of the query when I run in Query Analyzer, the query runs fine.

So, I need to modify the way AS is creating this processing query.

|||

So, let me check my understanding of the problem. You have a fact table that references the Customer dimension. The Customer dimension holds a SalesRep key which points to the SalesRep dimension. The fact table is assigned a referenced relationship to the SalesRep dimension using the Customer dimension as the intermediate dimension in that relationship. Is that correct?

If so, do you have the SalesRep dimension flagged to be materialized?

Either way, could you post the SQL generated that is causing the error?

Thanks,
Bryan Smith

|||

Yes, you're understanding the problem correctly. Yes, the SalesRep dimension is flagged to be materialized.

Here is the SQL

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS [dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
OPENROWSET
(
N'SQLNCLI.1',
N'',
N'[dbo].[vw_OMSBPCustomer]'
)
AS [dbo_vw_OMSBPCustomer_2]
WHERE
(

(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)

Now if I take this exact SQL and run it in query analyzer I get basically the same error:

Msg 7430, Level 16, State 3, Line 1

Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.

If I remove the OPENROWSET section as below

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],

[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId]

AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],

[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS

[dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]

FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],

-- OPENROWSET

-- (

-- N'SQLNCLI.1',

-- N'',

-- N'[dbo].[vw_OMSBPCustomer]'

-- )

--

[omswrite].[dbo].[vw_OMSBPCustomer] AS [dbo_vw_OMSBPCustomer_2] ****different database here

WHERE

(

(

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]

)

)

The customer table and the fact table are in different databases, not sure if that is causing some problems, but don't think it should.

|||

Deselecting the Materialize option should eliminate the problem becase the join will not be performed. If performance drops when querying the Sales Rep data, you may want to try using the .NET provider on your data sources.

B.

|||

This worked. Thanks very much for taking the time to help!

sql

Out-of_process error when trying to process a dimension

In my dsv I have a table called OrderDetail. This table has a salescodeid column in it. I have a second table called SalesCodeDetail. There is a relationship between these two tables based on the salescodeid. The Id is the primary key in the SalesCodeDetail table. In my cube I have a dimension of OrderDetail. When I try to add the salescode description from the salescodeDetail table as an attribute in my OrderDetail dimension I get this error.

Here is a more detailed description of this same problem.

I have a fact table that is joined to a dimension table 'Customer' by the customer number. Customer Number is the primary key in the 'Customer' view. I have a second Dimension table called 'SalesRep' which is referenced to the fact table through the customer. The join is SalesRepId, which is primary key on the SalesRep view, and SalesRepId which is on the 'Customer' view but not a key. Also, this relationship between SalesRep and customer does exist in the DSV.

When I try to process this relationship I get the following error: 'OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000.'

The SQL behind the processing is doing an 'OPENROWSET' which is causing the problem. If I take the 'OPENROWSET' out of the query when I run in Query Analyzer, the query runs fine.

So, I need to modify the way AS is creating this processing query.

|||

So, let me check my understanding of the problem. You have a fact table that references the Customer dimension. The Customer dimension holds a SalesRep key which points to the SalesRep dimension. The fact table is assigned a referenced relationship to the SalesRep dimension using the Customer dimension as the intermediate dimension in that relationship. Is that correct?

If so, do you have the SalesRep dimension flagged to be materialized?

Either way, could you post the SQL generated that is causing the error?

Thanks,
Bryan Smith

|||

Yes, you're understanding the problem correctly. Yes, the SalesRep dimension is flagged to be materialized.

Here is the SQL

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS [dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
OPENROWSET
(
N'SQLNCLI.1',
N'',
N'[dbo].[vw_OMSBPCustomer]'
)
AS [dbo_vw_OMSBPCustomer_2]
WHERE
(

(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)

Now if I take this exact SQL and run it in query analyzer I get basically the same error:

Msg 7430, Level 16, State 3, Line 1

Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.

If I remove the OPENROWSET section as below

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],

[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId]

AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],

[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS

[dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]

FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],

-- OPENROWSET

-- (

-- N'SQLNCLI.1',

-- N'',

-- N'[dbo].[vw_OMSBPCustomer]'

-- )

--

[omswrite].[dbo].[vw_OMSBPCustomer] AS [dbo_vw_OMSBPCustomer_2] ****different database here

WHERE

(

(

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]

)

)

The customer table and the fact table are in different databases, not sure if that is causing some problems, but don't think it should.

|||

Deselecting the Materialize option should eliminate the problem becase the join will not be performed. If performance drops when querying the Sales Rep data, you may want to try using the .NET provider on your data sources.

B.

|||

This worked. Thanks very much for taking the time to help!

Outer Joins and Substring Error

My problem exists on the last line - TOAD doesn't seem to like the two sets of parenthesis together. Am I supposed to put something in between? It's OK in the line above because the substring is on the other side.

SELECT distinct fp.active_fl, fp.empl_id, fp.proj_mgr_name, pm.project, fp.subctr_id, rd.CONT_CRNCY
FROM planner.rbws_proj_mgr pm, finance.fin_proj fp, finance.rbws_detl rd
WHERE SUBSTR(fp.proj_id, 1, 9) = pm.project (+)
AND pm.PROJECT = substr(rd.PROJ_ID,1,9) (+)Operative word being "think", I don't think that any version of TOAD can use Oracle outer join syntax in Microsoft SQL Server.

-PatP|||Oracle 8i or less no doubt

SELECT DISTINCT fp.active_fl
, fp.empl_id
, fp.proj_mgr_name
, pm.project
, fp.subctr_id
, rd.CONT_CRNCY
FROM planner.rbws_proj_mgr pm
, finance.fin_proj fp
, finance.rbws_detl rd
WHERE SUBSTR(fp.proj_id, 1, 9) *= pm.project
AND pm.PROJECT *= substr(rd.PROJ_ID,1,9)

Is that the old SQL Server syntax? I thankfully don't remember and have no desire to look it up...speaking of desiree...where is she?

Where is my Desire?

Outer Join problems

This may or may not be due to my lack of knowledge on the use of outer join. I am getting the error:

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'IncidentId'.

From my query:

SELECT Incident_Incidents.RelatedIncidentId,

Incident_Incidents.RelatedType,

Incident.Subject,

Incident.RecordId

From Incident_Incidents, Incident

left outer join IncidentId on Incident.IncidentId = Incident_Incidents.IncidentId

where Incident.IncidentId = 12838

Any ideas?

SELECT

Y.RelatedIncidentId,

X.RelatedType,

X.Subject,

X.RecordId

From

Incident_Incidents X

LEFT OUTER JOIN Incident Y

ON

X.IncidentId = Y.IncidentId

where X.IncidentId = 12838

|||Sorry not to clear on what you've got going on here, Tried running it with no luck|||

Here is a sample of some real code; I might of have mistaken some of your field name. It looks like your trying to left outer join on a column name instead of a table name, which will not work.

SELECT

P.iPLANID

FROM

PLANS P

INNER JOIN Units M

ON M.iPlanid = P.iPlanid

|||

my guess:

SELECT Incident_Incidents.RelatedIncidentId,

Incident_Incidents.RelatedType,

Incident.Subject,

Incident.RecordId

From Incident_Incidents

left outer join Incident on Incident.IncidentId = Incident_Incidents.RelatedIncidentId

where Incident.IncidentId = 12838

Outer Join Issues in Sql 2005

Hi,

The following query works fine in Sql 2000 database, but it throws an error ('Query contains an outer-join request that is not permitted.') while executing the query on Sql 2005 database. We have already set the compatibility level of the Sql 2005 database to 'Sql Server 2000(80)'. But still we are getting the error.

select top 2 *
from table1 t1, table2 t2
where t2.col8 =* t1.col1
and t2.col9 =* ( select max(t21.col9) from table2 t21
where t21.col8 =* t2.col8 )

We are planning to convert all our sql 2000 databases to sql 2005 databases. We have used this kind of outer join(=* or *=) in many places in our application. It is also working fine many places in 2005 database, but throw errors in some places only.

Please let me know if any one knows the reason for the error in some places alone. So that we can give priority to those kind of query and change the outer join to 'SQL-92 compliant'. We know that this kind of outer joins are NOT going to be supported in future version of Sql and we are going to change all such outer join. But now we do not have much time to change all such outer join. It will be a great help if any one have answer for this.

Thanks in advance,

Sankar N

Is this error related only to the statements including an outer join on a subquery?

Regards

|||

You will have to modify the query to use the OUTER JOIN syntax. There is no way to make it work in SQL2005 for all cases. The compatibility level doesn't help in this case due to changes in query plan generation in SQL2005 (there are lot of changes, some of the syntax evaluation is more strict, new optimizations etc). The old proprietary *= and =* syntax has lot of ambiguities and it can produce incorrect results. It is best to convert all statements with old syntax or do it on a case-by-case basis which might be problematic.

sql

Out of Range Error When Updating A Decimal Value

I am trying to update a database that has decimal(4,1) in a field. It can hold 100, but when I change a value in a gridview to 100 and update it, I get the error below. Any reason wny?

Parameter value '100.0' is out of range.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.ArgumentException: Parameter value '100.0' is out of range.

Source Error:

Line 1841: int returnValue;
Line 1842: try {
Line 1843: returnValue = command.ExecuteNonQuery();
Line 1844: }
Line 1845: finally {

Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs Line: 1843

Stack Trace:

[ArgumentException: Parameter value '100.0' is out of range.]
 System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) +4085
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +11

5
 StatisticsDALTableAdapters.StatisticsTableAdapter.UpdateStatistics(Nullable`1 ID, Nullable`1 PlayerID, Nullable`1 FG, Nullable`1 TTP, Nullable`1 FT, Nullable`1 RPG, Nullable`1 DEFR, Nullable`1 OFFR, Nullable`1 APG, Nullable`1 SPG, Nullable`1 BPG, Nullable`1 Turnovers, Nullable`1 PPG, String SYear, String EYear, String StatisticsType) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs:1843
 YourHoopSpace.BLL.StatisticsBLL.UpdateStatistic(Int32 ID, Int32 playerID, Nullable`1 fg, Nullable`1 ttp, Nullable`1 ft, Nullable`1 rpg, Nullable`1 defr, Nullable`1 offr, Nullable`1 apg, Nullable`1 spg, Nullable`1 bpg, Nullable`1 turnovers, Nullable`1 ppg, String sYear, String eYear, String statisticsType) in f:\MyProfile\App_Code\BLL\StatisticsBLL.cs:128

Is this the problem?

int returnValue;

Try decimal in your code. 

|||

HI~

How do you pass the parameters?

Here is sample code:

string str_test = TextBox1.Text; SqlConnection conn =new SqlConnection("Server=T-YOUNGF\\SQLEXPRESS;Database=pubs;uid=sa;pwd=pwd"); SqlCommand cmd =new SqlCommand("update decimal_test3 set column1=@.column1", conn); cmd.Parameters.AddWithValue("@.column1", 100.0); conn.Open();int i = cmd.ExecuteNonQuery(); Response.Write(i.ToString() +" row(s) has updated"); conn.Dispose();

And table:

create table decimal_test3 (column1decimal (4,1));

|||The problem was on my update I needed a decimal of (4,1) instead of (3,1) which I changed in the table not the stored procedure. Why does it need 4 instead of 3 to store 100?|||

decimal of (4,1) can hold upto 999.9

decimal of (3,1) can hold upto 99.9

So 100 is invalid.See decimal and numeric for details.

|||Thank you. I looked at that page, but really couldnt figure it out but now I know.sql

Out of range datetime value error when inserting using select...union

Hi all,
I am getting this error when insert values from one table to another in
the first table the values are varchar (10). In the second they are
datetime. The format of the data is mm/dd/yyyy to be easily converted
to dates. The conversion in this case is implicit as indicated in SQL
Server documentation. Here is my query:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description
UNION
SELECT program_id + 100000, program_name, start_date, end_date,
campaign_id AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.programs
UNION
SELECT execution_id + 200000, execution_name, start_date,
end_date, program_id + 100000 AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.executions
UNION
SELECT wave_id + 300000, wave_name, start_date, end_date,
mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL AS
ListID
FROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executions
WHERE mysqlfactiva.dbo.waves.execution_id =
mysqlfactiva.dbo.executions.execution_id

I am referencing programs table two times. If I just select this all I
get all data I need. When doing insert I get a message:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. The statement has been terminated.

If I execute just first part of the query before first union, I insert
data fine:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description

As soon as I use union I get the above error. This is very strange
since even when I execute the query using first union where the dates
come from the same table 'programs' I get the error. Why I can insert
from programs first time and can's second time?

Any help will be appreciated.

Thanks,
Stan(suslikovich@.yahoo.com) writes:
> I am getting this error when insert values from one table to another in
> the first table the values are varchar (10). In the second they are
> datetime. The format of the data is mm/dd/yyyy to be easily converted
> to dates. The conversion in this case is implicit as indicated in SQL
> Server documentation. Here is my query:

Note that the interpretation is dependent on the settings for DATEFORMAT
and LANGUAGE.

> INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
> ListID)
> SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
> mysqlfactiva.dbo.campaigns.campaign_name AS Name,
> MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,
> MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
> NULL AS ParentID,
> NULL AS ListID
> FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs

So the data type of mysqlfactiva.dbo.programs.start_date is varchar(10)?

Excuse me, if I am nosy, but a MIN on that columns appears somewhat
funny to me. If there is a date in early January you will use that
date, no matter which year it is in?

Better would be

MIN (CASE WHEN isdate(start_date) = 1 THEN start_date END

> As soon as I use union I get the above error. This is very strange
> since even when I execute the query using first union where the dates
> come from the same table 'programs' I get the error. Why I can insert
> from programs first time and can's second time?

In the first query, you are only taking the first value, in the
second query you are taking all the values. This query should give
you the bad data:

SELECT * FROM mysqlfactiva.dbo.programs.start_date
WHERE isdate(start_date) = 0 OR isdate(end_date) = 0

By the way, I seem recall that MySQL has a very liberal view on what
is a good date...

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Out of memory error when running large report

I have built a large report but when I try to execute inside of the Report
Designer, I get the following error and then an abort:
[An error has occurred during report processing
Exception of type System.OutOfMemoryException was thrown]
I have applied SRS SP1 and here is the MSD info:
Microsoft Visual Basic .NET 69457-005-1826837-18410
Microsoft SQL Server Report Designer Version 8.00.878.00
Running on XP SP2 with 1.25 gig RAM
Is there a work-around for this?
Thanks for any help!http://support.microsoft.com/?kbid=840709
This article may help you.
| From: "Mike Harbinger" <MikeH@.Cybervillage.net>
| Subject: Out of memory error when running large report
| Date: Thu, 6 Jan 2005 18:47:55 -0800
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69.106.50.114
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10|||Thanks for the reponse Brad, but I am not getting the error from the report
server but from inside of the .Net Visual Studio Reporting Services
designer.
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:g7L9ZjO9EHA.3200@.cpmsftngxa10.phx.gbl...
> http://support.microsoft.com/?kbid=840709
> This article may help you.
>
> --
> | From: "Mike Harbinger" <MikeH@.Cybervillage.net>
> | Subject: Out of memory error when running large report
> | Date: Thu, 6 Jan 2005 18:47:55 -0800
> | Lines: 18
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 69.106.50.114
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38909
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have built a large report but when I try to execute inside of the
> Report
> | Designer, I get the following error and then an abort:
> |
> | [An error has occurred during report processing
> | Exception of type System.OutOfMemoryException was thrown]
> |
> |
> | I have applied SRS SP1 and here is the MSD info:
> | Microsoft Visual Basic .NET 69457-005-1826837-18410
> | Microsoft SQL Server Report Designer Version 8.00.878.00
> |
> | Running on XP SP2 with 1.25 gig RAM
> |
> | Is there a work-around for this?
> |
> | Thanks for any help!
> |
> |
> |
>

Out of Memory Error when running a Select Statement

Hi all,

In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB.

This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error.

The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total.

Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory?

Regards

Mark:

You might want to temper opening large tables with Management Studio or other tools. If your table contains 40 million rows of data and each row of data is on average 1000 bytes per row, opening this table might take 40 GB of memory to open and display the data. Be careful about viewing all rows of large tables.

Dave

|||

Cheers Dave,

I have used Management Studio, but you are probably right about the memory requirement.

Thanks

Out of memory error

I'm trying to generate a very large report. My server's got 32 gigs of memory though, so memory shouldn't be an issue. However, when I try to generate my report, I get the following outofmemory error message in the error log on the server:

EventType clr20r3, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 mscorlib, P5 2.0.0.0, P6 4333ab80, P7 116e, P8 29, P9 system.outofmemoryexception, P10 NIL.

The w3wp.exe process maxes out at ~1,250 MB. I imagine I just have to change a setting somewhere to allow this to grow beyond that, but I can't seem to find that setting. What do I have to do?

Thanks!

-Carl

Were you able to resolve this issue?

Anyone have any ideas?

|||I am receiving this same error in the desktop development studio - system.outofmemory was thrown with 1044612 rows. It runs fine in the "Data" tab and in management studio but when I try to preview the report in the "Preview "tab I get this error. Any resolution for this?|||

I am having the same problem - the query runs perfectly fine in Management studio, or the Data tab - but when the report is generated it gives out of memory error.

Is there a resolution, or a workaround (would a custom ITemporaryStorage class resolve this issue - I can't find any example code or discussion on this except MSDN api docs)

Out of memory error

I'm trying to generate a very large report. My server's got 32 gigs of memory though, so memory shouldn't be an issue. However, when I try to generate my report, I get the following outofmemory error message in the error log on the server:

EventType clr20r3, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 mscorlib, P5 2.0.0.0, P6 4333ab80, P7 116e, P8 29, P9 system.outofmemoryexception, P10 NIL.

The w3wp.exe process maxes out at ~1,250 MB. I imagine I just have to change a setting somewhere to allow this to grow beyond that, but I can't seem to find that setting. What do I have to do?

Thanks!

-Carl

Were you able to resolve this issue?

Anyone have any ideas?

|||I am receiving this same error in the desktop development studio - system.outofmemory was thrown with 1044612 rows. It runs fine in the "Data" tab and in management studio but when I try to preview the report in the "Preview "tab I get this error. Any resolution for this?|||

I am having the same problem - the query runs perfectly fine in Management studio, or the Data tab - but when the report is generated it gives out of memory error.

Is there a resolution, or a workaround (would a custom ITemporaryStorage class resolve this issue - I can't find any example code or discussion on this except MSDN api docs)

Out of memory error

I'm trying to generate a very large report. My server's got 32 gigs of memory though, so memory shouldn't be an issue. However, when I try to generate my report, I get the following outofmemory error message in the error log on the server:

EventType clr20r3, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 mscorlib, P5 2.0.0.0, P6 4333ab80, P7 116e, P8 29, P9 system.outofmemoryexception, P10 NIL.

The w3wp.exe process maxes out at ~1,250 MB. I imagine I just have to change a setting somewhere to allow this to grow beyond that, but I can't seem to find that setting. What do I have to do?

Thanks!

-Carl

Were you able to resolve this issue?

Anyone have any ideas?

|||I am receiving this same error in the desktop development studio - system.outofmemory was thrown with 1044612 rows. It runs fine in the "Data" tab and in management studio but when I try to preview the report in the "Preview "tab I get this error. Any resolution for this?|||

I am having the same problem - the query runs perfectly fine in Management studio, or the Data tab - but when the report is generated it gives out of memory error.

Is there a resolution, or a workaround (would a custom ITemporaryStorage class resolve this issue - I can't find any example code or discussion on this except MSDN api docs)

sql

Out Of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.

---

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

---

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

---

Content of the .asp file is

<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

---

Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.

What should i do to solve this problem
Plz Help me out in this

Thanks in advance
Dinesh

I can't tell exactly what is going on here, but it seems to be limited to the web access method, since it works correctly from the batch file. So I would limit my search to that area - you might want to try turning on garbage collection:

http://support.microsoft.com/kb/911716

Buck Woody

Out of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.

---

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

---

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

---

Content of the .asp file is
<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

---

Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.

What should i do to solve this problem
Plz Help me out in this

Thanks in advance
Dinesh
You need to troubleshoot from ASP side. It is unlikely this has anything to do with SQL or the batch file itself.

Out Of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.

---

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

---

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

---

Content of the .asp file is

<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

---

Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.

What should i do to solve this problem
Plz Help me out in this

Thanks in advance
Dinesh

I can't tell exactly what is going on here, but it seems to be limited to the web access method, since it works correctly from the batch file. So I would limit my search to that area - you might want to try turning on garbage collection:

http://support.microsoft.com/kb/911716

Buck Woody

Out of Memery Error while reading records

Hi,

I'm using CR9 with PostgreSQL.

While trying to run a report for a particular date, it returns

"Failed to open rowset"

"Out of memory while trying to read tuples"

But while running the same thing as a sql query from Postgres by limiting the records count it's working fine. So I think the problem is with the crystal reports.

Is there any way to limit the no of records? Also I tried to limit the records in CR RAS. It's not working.

Any idea, pls. help me

ThanksWhat's the maximum size of the table/ Max no of records Crystal Reports Support?

OR

Is it depends on the physical size of the machine.

I caught by the error "Out of Memory while reading tuples". The same table is working while filtering the records by another field which returns smaller no of records.

Have any idea, pls share with me.

Thankssql

Otain error details from a DTS package through T-SQL. Was(xp_sendmail on script

SQL Server 2000

Good day all,

I've been asked to create a DTS package that will execute 2 other DTS packages (yeah, I know... personally I want to write a sp for it *shrug*) and send an e-mail out reporting a failure and failure details.

So I have a couple of questions for you;

How do I pick up the error number (and description?) from a failed DTS and pass it to my sendmail task?
Is it possible to send anything other than plain text e-mails?
Can I change the e-mail priority to "high"?

I appreciate any help you can offer me :)USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
DROP PROCEDURE sp_dtsFailureEmail
END
GO

CREATE PROCEDURE sp_dtsFailureEmail
@.dtsName varchar(64)
, @.errorNumber int = 0
AS

IF @.errorNumber <> 0 BEGIN
DECLARE @.message varchar(2000)
, @.subject varchar(255)
, @.errorDesc nvarchar(255)

SET @.errorDesc = (SELECT description FROM sysmessages WHERE error = @.errorNumber)
SET @.subject = '***DTS FAILURE*** ' + @.dtsname + ' ***DTS FAILURE***'
SET @.message = 'DTS Package:' + Char(9) + Char(9) + @.dtsName
+ Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
+ Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @.errorNumber)
+ Char(13) + 'Error Description:' + Char(9) + @.errorDesc

PRINT ''
PRINT @.subject
PRINT ''
PRINT @.message
PRINT ''

EXEC xp_sendmail
@.recipients = '<enter your e-mail address>'
, @.subject = @.subject
, @.message = @.message
END
ELSE BEGIN
PRINT 'No Error'
END
GO

SELECT 1/0

EXEC sp_dtsFailureEmail '<enter dtsName>', @.@.Error

DROP PROCEDURE sp_dtsFailureEmail

I'm getting close, but I still don't know how to pass the error number from a DTS to... anywhere!!|||EXEC xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E '
SELECT @.@.Error

Returns no error number when the DTS fails... So this suggests that this cannot be done!
Any ideas?|||As you know I know stuff all about DTS but you are not getting much help here.

1) Should you change the title of the thread? The problem does not really have anything to do with xp_sendmail.
2) Have you tried:

Declare @.error_return AS INT
EXEC @.error_return = xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E '
SELECT @.error_return ??
3) Have you considered setting up a global variable, trapping the error in the DTS script and setting the global. Then test the global when the DTS script has finished.

HTH|||1) Wilco... my q2&3 were sp_sendmail though..

2) I'm just working on that! It returns a bit value declaring whether there was an error or not.

3) Never used global variables in DTS before - will look up if I run into a dead end with point 2.

Here's my current working code.

USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
DROP PROCEDURE sp_dtsFailureEmail
END
GO

CREATE PROCEDURE sp_dtsFailureEmail
@.dtsName varchar(64)
AS

DECLARE @.message varchar(8000)
, @.subject varchar(255)
, @.errorDesc nvarchar(255)

-- SET @.errorDesc = (SELECT description FROM sysmessages WHERE error = @.errorNumber)
SET @.subject = '***DTS FAILURE*** ' + @.dtsname + ' ***DTS FAILURE***'
SET @.message = 'DTS Package:' + Char(9) + Char(9) + @.dtsName
+ Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
-- + Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @.errorNumber)
-- + Char(13) + 'Error Description:' + Char(9) + @.errorDesc

PRINT ''
PRINT @.subject
PRINT ''
PRINT @.message
PRINT ''

-- EXEC xp_sendmail
-- @.recipients = '<enter your email>'
-- , @.subject = @.subject
-- , @.message = @.message
-- END
GO

DECLARE @.err bit
DECLARE @.dts varchar(64)
DECLARE @.cmd varchar(1024)

SET @.dts = 'test'
SET @.cmd = 'DTSRun /S "SQL-LIVE-IT038" /N "' + @.dts + '" /W "0" /E'

EXEC @.err = xp_cmdshell @.cmd
IF @.err <> 0 BEGIN
EXEC sp_dtsFailureEmail @.dts
END

DROP PROCEDURE sp_dtsFailureEmail

Returns:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)
Error string: Divide by zero error encountered.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147217900 (80040E14); Provider Error: 8134 (1FC6)
Error string: Divide by zero error encountered.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.
NULL

***DTS FAILURE*** test ***DTS FAILURE***

DTS Package: test
Failure Date: Sep 5 2007 12:16PM

This raises another question: Is it possible to retrieve the output information from xp_cmdshell?|||This raises another question: Is it possible to retrieve the output information from xp_cmdshell?You mean stuff printed to the window? No.

How come? Are you hoping to get the details?

As said I don't know DTS but can you get the details within the package? If so then you can write all this to a table and check that.|||You mean stuff printed to the window?

Yep.

How come? Are you hoping to get the details?

Yep.

Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth ;)|||GeorgeV on DTS: Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth ;)

Strong candidate for my new sig ;)

That, for me, is DTS through and through.|||Yeeeeappp...
I'd love to rewrite the whole script in T-SQL; but can't...

I don't know how to do absolutely everything the DTS does
It's classed as a "major change" and I really cba going through all that just to do it.

A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.|||A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.To be fair unless you write bullet proof dynamic sql or have some sort of config file T-SQL will fair no better. I imagine Pat would start muttering something about "contracts" upon reading this. You change the contract between two systems and you can't really expect things to run smoothly.|||Actually, to resolve this an interim DTS package was created which produces a "clean" file. DTS packages remember the mapping.

The 3rd party product cost a total of $99 - we got what we paid for :p