Error in sql stored procedure

I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks.

ALTERprocedure [dbo].[USP_Account_Search_Mod]

@.ClientCode VARCHAR(7)=''

,@.DebtorName VARCHAR(25)=''

,@.DebtorNumberINT= 0

,@.AccountNumber VARCHAR(30)=''

,@.ReferenceNumber VARCHAR(30)=''

,@.TierINT= 0

,@.Status VARCHAR(5)=''

,@.UserIDINT

,@.MonthDateTime=NULL

,@.FromDateDateTime=NULL

,@.ToDateDateTime=NULL

,@.OriginalMindecimal= 0

,@.OriginalMaxdecimal= 0

,@.CurrentMindecimal= 0

,@.CurrentMaxdecimal=0

,@.lstAmountSelect VARCHAR(3)

,@.IsActivebit= 1

AS

DECLARE

@.SQLTier1Select VARCHAR(2000)

,@.SQLTier2Select VARCHAR(2000)

,@.Criteria VARCHAR(2000)

,@.SQL VARCHAR(8000)

,@.CRI1 VARCHAR(100)

,@.CRI2 VARCHAR(100)

,@.CRI3 VARCHAR(100)

,@.CRI4 VARCHAR(100)

,@.CRI5 VARCHAR(100)

,@.CRI6 VARCHAR(200)

,@.CRI7 VARCHAR(500)

,@.CRI8 VARCHAR(500)

,@.CRI9 VARCHAR(500)

SELECT @.CRI1=''

,@.CRI2=''

,@.CRI3=''

,@.CRI4=''

,@.CRI5=''

,@.CRI6=''

,@.CRI7=''

,@.CRI8=''

,@.CRI9=''

,@.Criteria=''

SELECT @.DebtorName=REPLACE(@.DebtorName,'''','''''');

Print @.DebtorName

if(SELECT UserTypeIDFROM dbo.tbl_Security_UsersWhere UserID= @.UserID)= 3AND @.ClientCode=''

return(-1)

IFLEN(@.DebtorName)> 0

SET @.CRI1=' AND Name like '+'''%'+ @.DebtorName+'%'''

IF @.DebtorNumber> 0

SET @.CRI2=' AND Number = '+CAST(@.DebtorNumberAS VARCHAR(7))

IFLEN(@.AccountNumber)> 1

SET @.CRI3=' AND AccountNumber like '+'''%'+ @.AccountNumber+'%'''

IFLEN(@.ReferenceNumber)> 0

SET @.CRI4=' AND Account like '+'''%'+ @.ReferenceNumber+'%'''

IFLEN(@.ClientCode)> 1

SET @.CRI5=' AND Customer = '+''''+ @.ClientCode+''''

SET @.Status=RTRIM(@.Status)

IF((@.StatusNotIN('ALL','ALA','ALI'))AND(LEN(@.Status)>1))

BEGIN

IF(@.Status='PAID')

SET @.CRI6=''

IF(@.Status='CANC')

SET @.CRI6=' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'

END

--PRINt @.CRI6

IFLEN(CONVERT(CHAR(8), @.Month, 112))> 0

BEGIN

IF(LEN(CONVERT(CHAR(8), @.FromDate, 112))> 0ANDLEN(CONVERT(CHAR(8), @.ToDate, 112))> 0)

BEGIN

SET @.CRI7=' AND Received BETWEEN '+''''+CONVERT(CHAR(8), @.FromDate, 112)+''''+' AND '+''''+CONVERT(CHAR(8), @.ToDate, 112)+''''

END

ELSE

BEGINSET @.CRI7=' AND DATEPART(mm, Received) = DATEPART(mm, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''+') AND DATEPART(yy, Received) = DATEPART(yy, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''

END

END

IF @.lstAmountSelect='ALL'

SET @.CRI8=''

elseIF @.lstAmountSelect='DR'

BEGIN

SET @.CRI8=' AND OriginalBalance >= '+convert(Varchar,@.OriginalMin)+'AND OriginalBalance<='+convert(Varchar,@.OriginalMax)+' AND CurrentBalance >= '+convert(Varchar,@.CurrentMin)+'AND CurrentBalance<='+convert(Varchar,@.CurrentMax)

END

ELSEIF @.lstAmountSelect='OLC'

BEGIN

SET @.CRI8=' AND OriginalBalance < CurrentBalance '

END

ELSEIF @.lstAmountSelect='OGC'

BEGIN

SET @.CRI8=' AND OriginalBalance > CurrentBalance '

END

ELSEIF @.lstAmountSelect='OEC'

BEGIN

SET @.CRI8=' AND OriginalBalance = CurrentBalance '

END

SELECT @.Criteria= @.CRI1+ @.CRI2+ @.CRI3+ @.CRI4+ @.CRI5+ @.CRI6+ @.CRI7+ @.CRI8

--PRINT @.Criteria

--PRINT @.CRI7

if @.Status='ALL'OR @.Status='ALA'OR @.Status='ALI'--All Period

BEGIN

if(@.Status='ALL')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'

END

if(@.Status='ALA')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'

END

if(@.Status='ALI')--All Inactive

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'

END

END

ELSEIF @.Status='PAID'

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'

END

ELSE

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

END

SELECT @.SQL=CASE @.Tier

WHEN 0THEN @.SQLTier1Select+' UNION '+ @.SQLTier2Select+'ORDER BY NAME ASC'

WHEN 1THEN @.SQLTier1Select+'ORDER BY NAME ASC'

WHEN 2THEN @.SQLTier2Select+'ORDER BY NAME ASC '

END

PRINT @.SQL

--SELECT @.SQL

EXEC(@.SQL)

Could you help us help you better by opening your stored procedure up in a query analyzer (like SQL 2005 QA or SQL Express Managment Studio) and parsing the query to determine what line number and what the syntax error is?

That is a lot of code and would be difficult for us to go over line by line to determine what the issue is.

|||

I do not have SQL Query Analyzer. Here is the error I get when I saw in event viewer

Transaction (Process ID 64) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction., Error Source:.Net SqlClient Data Provider.

|||

there's a lock on one of the tables your trying to do an insert on or update on. Talk with your DBA about that error and let him/her know what the Process ID is to help track it down.

Out of curiosity, what environment are you writing your stored procedures in?

|||

I'm writing my stored procedure in SQL Server Management Studio Express

|||

In SQL Server Management Studio Express you can parse your query (stored procedure syntax) and it will either tell you the syntax error along with the line number or tell you that the command was successful, which means you can run (execute) it and thus create your stored procedure.

Error in sample PrinterDeliveryProvider.cs?

The Deliver method in the sample returns false if the delivery is successful
and never sets the Notification.Retry flag. Both seem to contradict the
documentation. Please confirm that the sample is wrong and the
documentation is right.
Thanks,
- SteveThat is true. The documentation is correct. The printer delivery extension
sample should be changed to reflect the documentation.
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"Stephen Walch" <swalch@.proposion.com> wrote in message
news:OpQ$ZirXEHA.3512@.TK2MSFTNGP12.phx.gbl...
> The Deliver method in the sample returns false if the delivery is
successful
> and never sets the Notification.Retry flag. Both seem to contradict the
> documentation. Please confirm that the sample is wrong and the
> documentation is right.
> Thanks,
> - Steve
>|||One more thing. To make the sample more accurate, I would recommend changing
the retry flag to a success flag, like:
public bool Deliver(Notification notification)
{
bool success = false;
// Set the status of the notification to pending
notification.Status = "Processing...";
try
{
// Build user data
Setting[] userSettings = notification.UserData;
SubscriptionData subscriptionData = new SubscriptionData();
subscriptionData.FromSettings(userSettings);
// Print the report
PrintReport(notification, subscriptionData);
// If delivery is successful return false
success = true;
}
catch (Exception e)
{
// Set the status of the notification if an error occurs
// and attempt retry
notification.Status = "Error: " + e.Message;
success = false;
}
finally
{
// Finally, save the notification information
notification.Save();
}
return success;
}
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"Stephen Walch" <swalch@.proposion.com> wrote in message
news:OpQ$ZirXEHA.3512@.TK2MSFTNGP12.phx.gbl...
> The Deliver method in the sample returns false if the delivery is
successful
> and never sets the Notification.Retry flag. Both seem to contradict the
> documentation. Please confirm that the sample is wrong and the
> documentation is right.
> Thanks,
> - Steve
>|||Don't you also want to set the Retry flag?
catch (Exception e)
{
// Set the status of the notification if an error occurs
// and attempt retry
notification.Status = "Error: " + e.Message;
notification.Retry = true;
success = false;
}|||Hi,
During to build "Sample Printer Delivery Extension", the document write:
2.Issue the following command to create the sample delivery extension
Microsoft.Samples.ReportingServices.PrinterDeliverySample.dll.
csc /t:library /out:Microsoft.Samples.ReportingServices.PrinterDelivery.dll
*.cs /r:Microsoft.ReportingServices.Interfaces.dll
During to build "Sample Printer Delivery Extension", the
How should I Implement this command? Please give me advice, thanks for your
time!
Angi|||CSC is the C# compiler which ships with the .Net Framework. It should be
installed on your computer if you installed the .Net Framework v. 1.1.4322.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:uCWemPUbEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi,
> During to build "Sample Printer Delivery Extension", the document write:
> 2.Issue the following command to create the sample delivery extension
> Microsoft.Samples.ReportingServices.PrinterDeliverySample.dll.
> csc /t:library
/out:Microsoft.Samples.ReportingServices.PrinterDelivery.dll
> *.cs /r:Microsoft.ReportingServices.Interfaces.dll
> During to build "Sample Printer Delivery Extension", the
> How should I Implement this command? Please give me advice, thanks for
your
> time!
> Angi
>|||You can also build it by opening the .SLN file in Visual Stidio.NET 2003.
-Steve
"angi" <angi@.microsoft.com> wrote in message
news:uCWemPUbEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi,
> During to build "Sample Printer Delivery Extension", the document write:
> 2.Issue the following command to create the sample delivery extension
> Microsoft.Samples.ReportingServices.PrinterDeliverySample.dll.
> csc /t:library
/out:Microsoft.Samples.ReportingServices.PrinterDelivery.dll
> *.cs /r:Microsoft.ReportingServices.Interfaces.dll
> During to build "Sample Printer Delivery Extension", the
> How should I Implement this command? Please give me advice, thanks for
your
> time!
> Angi
>

Error importing data from oracle database to an SQL database

Hi!

When i was importing a database table from an Oracle Database to a SQL database table, the wizard returns this error:

Could not connect source component.

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error 0xc0204018: DTS.Pipeline: The "output column "PE_ACTIVE" (34)" has a precision that is not valid. The precision must be between 1 and 38.
(SQL Server Import and Export Wizard)

I tried to change the destination type field to others than the default (decimal), but noting works.

Anyone can help me please?

Thx a lot

Cristovao

I'm currently working on a project that pull data from an Oracle DB and have had not problems pulling data and loading it into SQL 2005.
As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.
As far as the PE_ACTIVE output column error, I can't tell what the error is. If you let me know how field is defined in Oracle, I may be able to help you. Also what version of Oracle are you using?
Larry
|||Cristovao,

Could you tell us what is the source (Oracle) data type and precision and what is the suggested destination (SQL Server) type and precision?

Thanks.|||

Hi

Thx a lot for your answer.

I am sending to you the data that you asked me:

Type of the source fied: Number (1)
Suggested destination : Decimal (1)
Oracle version: 8.1.7
Best Regards,
Cristovao

|||The value 1 should be a valid precision.

Could you do another check for me? Please select the failing table on the Select Source Tables and Views" page and click "Edit..." on it. In the grid that shows up, find your "PE_ACTIVE" column and take a look at the values for Size, Precision and Scale.
There should be only the precision with the value 1.|||Hi Bob!

Thx again for your interest...

Yes, i have checked and in fact there are only the precision with the value 1, but the error occurs... |||Well, it sounds intriguing.

Can you tell me what OLE DB driver you used; Microsoft OLE DB for Oracle or Oracle OLE DB?

I was able to copy a Number(1) column without any problem, using the MS driver.

Also, what version of SSIS do you have installed?
Thanks.|||

Hi Bob!

Well, i used the Oracle Provider For OLE DB... After have read your post i tried the Microsoft OLEDB Provider For Oracle, and the data have been imported, returning this warning:
TITLE: SQL Server Import and Export Wizard

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

BUTTONS:

OK
I suppose it is normal.

Thanks a lot for your help.

Best Regards,
Cristovao

|||I am glad you were able to make it work.

Yes, I believe this warning can be safely ignored in this case.|||Hi all!

Same problem that I had, but the problem was that Oracle did not have a size or scale specified. SSIS automatically treats all of there as numerics, and with no specification on the precision or scale, it blows up. I have found no way to set it, and have tried both the Microsoft and Oracle drivers. Nada!

The good news....just use a Data Reader connection with the .Net Oracle Client. Works like a champ!!!!!!!!

Scott|||

Larry_Pope wrote:

As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

Thanks for this Larry - AlwaysUseDefaultCodePage=TRUE worked a treat!

-Jamie|||Here is the latest issue I found. Some Oracle tables do not have a precision or scale assigned to the column when the type is set to NUMBER. This causes an error when using an OLEDB connection. Has anyone found a way to get around this? The DataReader does it fine, but I don't think it is as fast as the DataReader.

Thanks!

Scott Barrett|||Any luck on thos problem, Scott?|||Here is the issue. When using SSIS against Oracle 8i or any database imported from 8i, when a column was meant to be what we call an INT, they call NUMBER and do not set the precision or scale. Oracle 9i and up does this by default. This will cause SSIS to not be able to bring in data from those columns. So we now have two solutions: Use the Data Reader Source and your done, or use the OLE DB Source and only using the Advanced Editor, enter the source and manually create each output column manually. If you are pulling from a complete table instead of a query, you can manually change the datatype in the advanced editor to a DT_I4 and it will then work.

Hope this helps you out!

Scott Barrett
Moffitt Cancer Center
|||I have installed sql server 2005 evaluation version and have oracle 8i.
But I can't find OLE DB Source property of AlwaysUseDefaultCodePage to set it to True.
Can someone help me and show me directions.

Thanks,
Mitja

Error importing data from oracle database to an SQL database

Hi!

When i was importing a database table from an Oracle Database to a SQL database table, the wizard returns this error:

Could not connect source component.

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error 0xc0204018: DTS.Pipeline: The "output column "PE_ACTIVE" (34)" has a precision that is not valid. The precision must be between 1 and 38.
(SQL Server Import and Export Wizard)

I tried to change the destination type field to others than the default (decimal), but noting works.

Anyone can help me please?

Thx a lot

Cristovao

I'm currently working on a project that pull data from an Oracle DB and have had not problems pulling data and loading it into SQL 2005.
As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.
As far as the PE_ACTIVE output column error, I can't tell what the error is. If you let me know how field is defined in Oracle, I may be able to help you. Also what version of Oracle are you using?
Larry|||Cristovao,

Could you tell us what is the source (Oracle) data type and precision and what is the suggested destination (SQL Server) type and precision?

Thanks.|||

Hi

Thx a lot for your answer.

I am sending to you the data that you asked me:

Type of the source fied: Number (1)
Suggested destination : Decimal (1)
Oracle version: 8.1.7
Best Regards,
Cristovao

|||The value 1 should be a valid precision.

Could you do another check for me? Please select the failing table on the Select Source Tables and Views" page and click "Edit..." on it. In the grid that shows up, find your "PE_ACTIVE" column and take a look at the values for Size, Precision and Scale.
There should be only the precision with the value 1.|||Hi Bob!

Thx again for your interest...

Yes, i have checked and in fact there are only the precision with the value 1, but the error occurs...

|||Well, it sounds intriguing.

Can you tell me what OLE DB driver you used; Microsoft OLE DB for Oracle or Oracle OLE DB?

I was able to copy a Number(1) column without any problem, using the MS driver.

Also, what version of SSIS do you have installed?
Thanks.|||

Hi Bob!

Well, i used the Oracle Provider For OLE DB... After have read your post i tried the Microsoft OLEDB Provider For Oracle, and the data have been imported, returning this warning:
TITLE: SQL Server Import and Export Wizard

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

BUTTONS:

OK
I suppose it is normal.

Thanks a lot for your help.

Best Regards,
Cristovao

|||I am glad you were able to make it work.

Yes, I believe this warning can be safely ignored in this case.|||Hi all!

Same problem that I had, but the problem was that Oracle did not have a size or scale specified. SSIS automatically treats all of there as numerics, and with no specification on the precision or scale, it blows up. I have found no way to set it, and have tried both the Microsoft and Oracle drivers. Nada!

The good news....just use a Data Reader connection with the .Net Oracle Client. Works like a champ!!!!!!!!

Scott|||

Larry_Pope wrote:

As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

Thanks for this Larry - AlwaysUseDefaultCodePage=TRUE worked a treat!

-Jamie|||Here is the latest issue I found. Some Oracle tables do not have a precision or scale assigned to the column when the type is set to NUMBER. This causes an error when using an OLEDB connection. Has anyone found a way to get around this? The DataReader does it fine, but I don't think it is as fast as the DataReader.

Thanks!

Scott Barrett|||Any luck on thos problem, Scott?|||Here is the issue. When using SSIS against Oracle 8i or any database imported from 8i, when a column was meant to be what we call an INT, they call NUMBER and do not set the precision or scale. Oracle 9i and up does this by default. This will cause SSIS to not be able to bring in data from those columns. So we now have two solutions: Use the Data Reader Source and your done, or use the OLE DB Source and only using the Advanced Editor, enter the source and manually create each output column manually. If you are pulling from a complete table instead of a query, you can manually change the datatype in the advanced editor to a DT_I4 and it will then work.

Hope this helps you out!

Scott Barrett
Moffitt Cancer Center|||I have installed sql server 2005 evaluation version and have oracle 8i.
But I can't find OLE DB Source property of AlwaysUseDefaultCodePage to set it to True.
Can someone help me and show me directions.

Thanks,
Mitja

Error importing data from oracle database to an SQL database

Hi!

When i was importing a database table from an Oracle Database to a SQL database table, the wizard returns this error:

Could not connect source component.

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error 0xc0204018: DTS.Pipeline: The "output column "PE_ACTIVE" (34)" has a precision that is not valid. The precision must be between 1 and 38.
(SQL Server Import and Export Wizard)

I tried to change the destination type field to others than the default (decimal), but noting works.

Anyone can help me please?

Thx a lot

Cristovao

I'm currently working on a project that pull data from an Oracle DB and have had not problems pulling data and loading it into SQL 2005.
As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.
As far as the PE_ACTIVE output column error, I can't tell what the error is. If you let me know how field is defined in Oracle, I may be able to help you. Also what version of Oracle are you using?
Larry|||Cristovao,

Could you tell us what is the source (Oracle) data type and precision and what is the suggested destination (SQL Server) type and precision?

Thanks.|||

Hi

Thx a lot for your answer.

I am sending to you the data that you asked me:

Type of the source fied: Number (1)
Suggested destination : Decimal (1)
Oracle version: 8.1.7
Best Regards,
Cristovao

|||The value 1 should be a valid precision.

Could you do another check for me? Please select the failing table on the Select Source Tables and Views" page and click "Edit..." on it. In the grid that shows up, find your "PE_ACTIVE" column and take a look at the values for Size, Precision and Scale.
There should be only the precision with the value 1.|||Hi Bob!

Thx again for your interest...

Yes, i have checked and in fact there are only the precision with the value 1, but the error occurs...

|||Well, it sounds intriguing.

Can you tell me what OLE DB driver you used; Microsoft OLE DB for Oracle or Oracle OLE DB?

I was able to copy a Number(1) column without any problem, using the MS driver.

Also, what version of SSIS do you have installed?
Thanks.|||

Hi Bob!

Well, i used the Oracle Provider For OLE DB... After have read your post i tried the Microsoft OLEDB Provider For Oracle, and the data have been imported, returning this warning:
TITLE: SQL Server Import and Export Wizard

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

BUTTONS:

OK
I suppose it is normal.

Thanks a lot for your help.

Best Regards,
Cristovao

|||I am glad you were able to make it work.

Yes, I believe this warning can be safely ignored in this case.|||Hi all!

Same problem that I had, but the problem was that Oracle did not have a size or scale specified. SSIS automatically treats all of there as numerics, and with no specification on the precision or scale, it blows up. I have found no way to set it, and have tried both the Microsoft and Oracle drivers. Nada!

The good news....just use a Data Reader connection with the .Net Oracle Client. Works like a champ!!!!!!!!

Scott|||

Larry_Pope wrote:

As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

Thanks for this Larry - AlwaysUseDefaultCodePage=TRUE worked a treat!

-Jamie|||Here is the latest issue I found. Some Oracle tables do not have a precision or scale assigned to the column when the type is set to NUMBER. This causes an error when using an OLEDB connection. Has anyone found a way to get around this? The DataReader does it fine, but I don't think it is as fast as the DataReader.

Thanks!

Scott Barrett|||Any luck on thos problem, Scott?|||Here is the issue. When using SSIS against Oracle 8i or any database imported from 8i, when a column was meant to be what we call an INT, they call NUMBER and do not set the precision or scale. Oracle 9i and up does this by default. This will cause SSIS to not be able to bring in data from those columns. So we now have two solutions: Use the Data Reader Source and your done, or use the OLE DB Source and only using the Advanced Editor, enter the source and manually create each output column manually. If you are pulling from a complete table instead of a query, you can manually change the datatype in the advanced editor to a DT_I4 and it will then work.

Hope this helps you out!

Scott Barrett
Moffitt Cancer Center|||I have installed sql server 2005 evaluation version and have oracle 8i.
But I can't find OLE DB Source property of AlwaysUseDefaultCodePage to set it to True.
Can someone help me and show me directions.

Thanks,
Mitja

Error importing data from oracle database to an SQL database

Hi!

When i was importing a database table from an Oracle Database to a SQL database table, the wizard returns this error:

Could not connect source component.

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error 0xc0204018: DTS.Pipeline: The "output column "PE_ACTIVE" (34)" has a precision that is not valid. The precision must be between 1 and 38.
(SQL Server Import and Export Wizard)

I tried to change the destination type field to others than the default (decimal), but noting works.

Anyone can help me please?

Thx a lot

Cristovao

I'm currently working on a project that pull data from an Oracle DB and have had not problems pulling data and loading it into SQL 2005.
As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.
As far as the PE_ACTIVE output column error, I can't tell what the error is. If you let me know how field is defined in Oracle, I may be able to help you. Also what version of Oracle are you using?
Larry|||Cristovao,

Could you tell us what is the source (Oracle) data type and precision and what is the suggested destination (SQL Server) type and precision?

Thanks.|||

Hi

Thx a lot for your answer.

I am sending to you the data that you asked me:

Type of the source fied: Number (1)
Suggested destination : Decimal (1)
Oracle version: 8.1.7
Best Regards,
Cristovao

|||The value 1 should be a valid precision.

Could you do another check for me? Please select the failing table on the Select Source Tables and Views" page and click "Edit..." on it. In the grid that shows up, find your "PE_ACTIVE" column and take a look at the values for Size, Precision and Scale.
There should be only the precision with the value 1.|||Hi Bob!

Thx again for your interest...

Yes, i have checked and in fact there are only the precision with the value 1, but the error occurs...

|||Well, it sounds intriguing.

Can you tell me what OLE DB driver you used; Microsoft OLE DB for Oracle or Oracle OLE DB?

I was able to copy a Number(1) column without any problem, using the MS driver.

Also, what version of SSIS do you have installed?
Thanks.|||

Hi Bob!

Well, i used the Oracle Provider For OLE DB... After have read your post i tried the Microsoft OLEDB Provider For Oracle, and the data have been imported, returning this warning:
TITLE: SQL Server Import and Export Wizard

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

BUTTONS:

OK
I suppose it is normal.

Thanks a lot for your help.

Best Regards,
Cristovao

|||I am glad you were able to make it work.

Yes, I believe this warning can be safely ignored in this case.|||Hi all!

Same problem that I had, but the problem was that Oracle did not have a size or scale specified. SSIS automatically treats all of there as numerics, and with no specification on the precision or scale, it blows up. I have found no way to set it, and have tried both the Microsoft and Oracle drivers. Nada!

The good news....just use a Data Reader connection with the .Net Oracle Client. Works like a champ!!!!!!!!

Scott|||

Larry_Pope wrote:

As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

Thanks for this Larry - AlwaysUseDefaultCodePage=TRUE worked a treat!

-Jamie|||Here is the latest issue I found. Some Oracle tables do not have a precision or scale assigned to the column when the type is set to NUMBER. This causes an error when using an OLEDB connection. Has anyone found a way to get around this? The DataReader does it fine, but I don't think it is as fast as the DataReader.

Thanks!

Scott Barrett|||Any luck on thos problem, Scott?|||Here is the issue. When using SSIS against Oracle 8i or any database imported from 8i, when a column was meant to be what we call an INT, they call NUMBER and do not set the precision or scale. Oracle 9i and up does this by default. This will cause SSIS to not be able to bring in data from those columns. So we now have two solutions: Use the Data Reader Source and your done, or use the OLE DB Source and only using the Advanced Editor, enter the source and manually create each output column manually. If you are pulling from a complete table instead of a query, you can manually change the datatype in the advanced editor to a DT_I4 and it will then work.

Hope this helps you out!

Scott Barrett
Moffitt Cancer Center|||I have installed sql server 2005 evaluation version and have oracle 8i.
But I can't find OLE DB Source property of AlwaysUseDefaultCodePage to set it to True.
Can someone help me and show me directions.

Thanks,
Mitja

Error importing data from oracle database to an SQL database

Hi!

When i was importing a database table from an Oracle Database to a SQL database table, the wizard returns this error:

Could not connect source component.

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error 0xc0204018: DTS.Pipeline: The "output column "PE_ACTIVE" (34)" has a precision that is not valid. The precision must be between 1 and 38.
(SQL Server Import and Export Wizard)

I tried to change the destination type field to others than the default (decimal), but noting works.

Anyone can help me please?

Thx a lot

Cristovao

I'm currently working on a project that pull data from an Oracle DB and have had not problems pulling data and loading it into SQL 2005.
As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.
As far as the PE_ACTIVE output column error, I can't tell what the error is. If you let me know how field is defined in Oracle, I may be able to help you. Also what version of Oracle are you using?
Larry|||Cristovao,

Could you tell us what is the source (Oracle) data type and precision and what is the suggested destination (SQL Server) type and precision?

Thanks.|||

Hi

Thx a lot for your answer.

I am sending to you the data that you asked me:

Type of the source fied: Number (1)
Suggested destination : Decimal (1)
Oracle version: 8.1.7
Best Regards,
Cristovao

|||The value 1 should be a valid precision.

Could you do another check for me? Please select the failing table on the Select Source Tables and Views" page and click "Edit..." on it. In the grid that shows up, find your "PE_ACTIVE" column and take a look at the values for Size, Precision and Scale.
There should be only the precision with the value 1.|||Hi Bob!

Thx again for your interest...

Yes, i have checked and in fact there are only the precision with the value 1, but the error occurs...

|||Well, it sounds intriguing.

Can you tell me what OLE DB driver you used; Microsoft OLE DB for Oracle or Oracle OLE DB?

I was able to copy a Number(1) column without any problem, using the MS driver.

Also, what version of SSIS do you have installed?
Thanks.|||

Hi Bob!

Well, i used the Oracle Provider For OLE DB... After have read your post i tried the Microsoft OLEDB Provider For Oracle, and the data have been imported, returning this warning:
TITLE: SQL Server Import and Export Wizard

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

BUTTONS:

OK
I suppose it is normal.

Thanks a lot for your help.

Best Regards,
Cristovao

|||I am glad you were able to make it work.

Yes, I believe this warning can be safely ignored in this case.|||Hi all!

Same problem that I had, but the problem was that Oracle did not have a size or scale specified. SSIS automatically treats all of there as numerics, and with no specification on the precision or scale, it blows up. I have found no way to set it, and have tried both the Microsoft and Oracle drivers. Nada!

The good news....just use a Data Reader connection with the .Net Oracle Client. Works like a champ!!!!!!!!

Scott|||

Larry_Pope wrote:

As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

Thanks for this Larry - AlwaysUseDefaultCodePage=TRUE worked a treat!

-Jamie|||Here is the latest issue I found. Some Oracle tables do not have a precision or scale assigned to the column when the type is set to NUMBER. This causes an error when using an OLEDB connection. Has anyone found a way to get around this? The DataReader does it fine, but I don't think it is as fast as the DataReader.

Thanks!

Scott Barrett|||Any luck on thos problem, Scott?|||Here is the issue. When using SSIS against Oracle 8i or any database imported from 8i, when a column was meant to be what we call an INT, they call NUMBER and do not set the precision or scale. Oracle 9i and up does this by default. This will cause SSIS to not be able to bring in data from those columns. So we now have two solutions: Use the Data Reader Source and your done, or use the OLE DB Source and only using the Advanced Editor, enter the source and manually create each output column manually. If you are pulling from a complete table instead of a query, you can manually change the datatype in the advanced editor to a DT_I4 and it will then work.

Hope this helps you out!

Scott Barrett
Moffitt Cancer Center|||I have installed sql server 2005 evaluation version and have oracle 8i.
But I can't find OLE DB Source property of AlwaysUseDefaultCodePage to set it to True.
Can someone help me and show me directions.

Thanks,
Mitja

Error importing data from oracle database to an SQL database

Hi!

When i was importing a database table from an Oracle Database to a SQL database table, the wizard returns this error:

Could not connect source component.

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error 0xc0204018: DTS.Pipeline: The "output column "PE_ACTIVE" (34)" has a precision that is not valid. The precision must be between 1 and 38.
(SQL Server Import and Export Wizard)

I tried to change the destination type field to others than the default (decimal), but noting works.

Anyone can help me please?

Thx a lot

Cristovao

I'm currently working on a project that pull data from an Oracle DB and have had not problems pulling data and loading it into SQL 2005.
As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.
As far as the PE_ACTIVE output column error, I can't tell what the error is. If you let me know how field is defined in Oracle, I may be able to help you. Also what version of Oracle are you using?
Larry|||Cristovao,

Could you tell us what is the source (Oracle) data type and precision and what is the suggested destination (SQL Server) type and precision?

Thanks.|||

Hi

Thx a lot for your answer.

I am sending to you the data that you asked me:

Type of the source fied: Number (1)
Suggested destination : Decimal (1)
Oracle version: 8.1.7
Best Regards,
Cristovao

|||The value 1 should be a valid precision.

Could you do another check for me? Please select the failing table on the Select Source Tables and Views" page and click "Edit..." on it. In the grid that shows up, find your "PE_ACTIVE" column and take a look at the values for Size, Precision and Scale.
There should be only the precision with the value 1.|||Hi Bob!

Thx again for your interest...

Yes, i have checked and in fact there are only the precision with the value 1, but the error occurs...

|||Well, it sounds intriguing.

Can you tell me what OLE DB driver you used; Microsoft OLE DB for Oracle or Oracle OLE DB?

I was able to copy a Number(1) column without any problem, using the MS driver.

Also, what version of SSIS do you have installed?
Thanks.|||

Hi Bob!

Well, i used the Oracle Provider For OLE DB... After have read your post i tried the Microsoft OLEDB Provider For Oracle, and the data have been imported, returning this warning:
TITLE: SQL Server Import and Export Wizard

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

BUTTONS:

OK
I suppose it is normal.

Thanks a lot for your help.

Best Regards,
Cristovao

|||I am glad you were able to make it work.

Yes, I believe this warning can be safely ignored in this case.|||Hi all!

Same problem that I had, but the problem was that Oracle did not have a size or scale specified. SSIS automatically treats all of there as numerics, and with no specification on the precision or scale, it blows up. I have found no way to set it, and have tried both the Microsoft and Oracle drivers. Nada!

The good news....just use a Data Reader connection with the .Net Oracle Client. Works like a champ!!!!!!!!

Scott|||

Larry_Pope wrote:

As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

Thanks for this Larry - AlwaysUseDefaultCodePage=TRUE worked a treat!

-Jamie|||Here is the latest issue I found. Some Oracle tables do not have a precision or scale assigned to the column when the type is set to NUMBER. This causes an error when using an OLEDB connection. Has anyone found a way to get around this? The DataReader does it fine, but I don't think it is as fast as the DataReader.

Thanks!

Scott Barrett|||Any luck on thos problem, Scott?|||Here is the issue. When using SSIS against Oracle 8i or any database imported from 8i, when a column was meant to be what we call an INT, they call NUMBER and do not set the precision or scale. Oracle 9i and up does this by default. This will cause SSIS to not be able to bring in data from those columns. So we now have two solutions: Use the Data Reader Source and your done, or use the OLE DB Source and only using the Advanced Editor, enter the source and manually create each output column manually. If you are pulling from a complete table instead of a query, you can manually change the datatype in the advanced editor to a DT_I4 and it will then work.

Hope this helps you out!

Scott Barrett
Moffitt Cancer Center|||I have installed sql server 2005 evaluation version and have oracle 8i.
But I can't find OLE DB Source property of AlwaysUseDefaultCodePage to set it to True.
Can someone help me and show me directions.

Thanks,
Mitja