problem with bit datatype conversion

Hi All,

I have create following table and inserted few records

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_position](
[part] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NOT NULL,
[opt] [bit] NOT NULL
) ON [PRIMARY]

I am executing following procedure to update the table.

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=@.OPT,price=@.PRICE WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END

ex:

Code Snippet

exec PROC1 1,2.50,0

but I am getting following error

Code Snippet

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@.OPT".

I am struck up with this query .

Please Help me regarding this issue.

Regards
Gomaz

Code Snippet

CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )

AS

BEGIN

declare @.sSQL as nvarchar(max);

SET @.sSQL = 'update tbl_position set opt=' +cast(@.OPT as char(1) )+',price='+@.PRICE+' WHERE part = '+ @.part;

exec sp_executesql @.sSQL

END

sp_executesql execute query in different bacth. As you couldn't use opt=@.OPT

Problem When From Sequence file insert into Oracle Destination table in SSIS Package

Hi ,

i was used the Follwing DataFlow for my Package.using Oracle 8i

FalteFile Source -> Data Conversion >OLEDB Destination (Oracle Data table)

using above control flow to map the Source file to Destination . When i run the SSIS Package teh Folwing Error i got

"Truncation Occur maydue to inserting data from data flow column "columnName " with a length of 50 "

regarding this Error i i understood its for happening Data Length . so that i was changed the Source Column Length Exactly Match with the The Destination table.

still i am getting this Error. pls any one give me a solution . SHould i Change the DataType also?

pls give your suggestion

Thanks & Regards

Jeyakumar.M

chennai

At some point of your flow it is still defined as greater than 50. Check all outputs of all components and check external columns of your source as well.

-Jamie

|||

Hi Jamie

Thanks for your reply. all the Source column (Sequence file) and Destination Column (oracle Table ) length are same. But i didn't give Data type match for source and Destination .. Is it Required for Data Type to define in the Source column in Dataconversion control. pls Let me know.

Thanks For Your reply

Jeyakumar.M

chennai

|||

Data types in teh source are by and large determined by the OLE DB Provider. If you need them to be something else then drop a Data Conversion component into your flow.

-Jamie