Error 409: The assignment operator operation could not take a text data type as an argumen

How can I make it work when I need to pull out a field which is text type using a stored procedure? Please help!!!Thanks
I am getting the following error
Error 409: The assignment operator operation could not take a text data type as an argument
===========my sp=================================
CREATE PROCEDURE [dbo].[sp_SelectABC]
(@.a varchar(50) output,
@.b text output
)
AS
set nocount on
select @.a=name, @.b= description from ABC

GONo, you can't do that.

You're going to have to replace it with

select name, description from ABC

error 3520 on sql insert statement from a text file (only happens in 1 location)

I have a program that uses the Microsoft ODBC text driver Driver={Microsoft Text Driver (*.txt; *.csv)} this is used to make a connection to the database and then execute an sql insert to insert data from a flat file into an access database. The code works at hundreds of locations but for some reason it does not work at 1. I have ran an sql trace and found the error 3520 which would mean the data in the text file does not match the table that it is being inserted into but I have checked that and have tried using the same data on my computer and it works fine. I have also ran mdac compenent checker and there are no mismatched files. Any ideas on how to correct this or if anyone else has ever even seen this please help...Moving thread to Data Access Forum.

error 30311: Value of type 'System.DBNull' cannot be converted to 'Date'

I'm creating an ISP for extractig data from a text file and put it in a database.

One of the fields in my textfile contains the value '0' or a date. If it's '0' it should be converted to the Null Value. The column in which it has to be saved is of type smalldatetime.

This is the code of my script where I want to check the value of the field in my textfile and convert it to a date or to Null.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.cdDateIn = "" Or Row.cdDateIn= "0" Then

Row.cdDateInCorr = CDate(DBNull.Value)

ElseIf Row.cdDateIn_IsNull Then

Row.cdDateInCorr = CDate(DBNull.Value)

Else

Row.cdDateInCorr = CDate(Row.cdDateIn)

End If

End Sub

The error that I get is:

Validation error. Extract FinCD: Conversion of cdDateIn [753]: Eroor 30311: Value of type 'System.DBNull' cannot ben converted to 'Date'.

How do I resolve this problem ?

Why not use a derived column? Expression follows: (Typing from memory)

cdDateIn == "" || cdDateIn == "0" || ISNULL(cdDateIn) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)cdDateIn

|||You can just set Row.cdDateInCorr_IsNull = true.
|||

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

|||

I've used the code JayH suggested and this works.

Phil, your code is a bit like chinese to me. This ISP I'm working on is my first project in .NET. But I will check this derived column thing.

Thanks four your help.

|||

Phil Brammer wrote:

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

Are you sure about this - the IsNull property is for evaluation and not for assigning true or false to it - I believe that it will reset itself when you leave the script task - or am I wrong?
|||

Simon Givoni wrote:

Phil Brammer wrote:

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

Are you sure about this - the IsNull property is for evaluation and not for assigning true or false to it - I believe that it will reset itself when you leave the script task - or am I wrong?

Yes, I'm sure. You can set the property to True and the null will stick. You cannot set it to False, but it will automatically become False if you set a value to the column. SP2 will raise an exception if you set it to False.

|||Ah yes you are right - here are the full explanation:

The IsNull property of columns in the Script Component now raises a warning when it is used incorrectly.
The code generated by the Script component contains a <columnname> and a <columnname>_IsNull property for each input and output column. The intention of the <columnname>_IsNull property is to allow the user to specify a Null value for the column by setting the value of the property to True. To specify a non-null value, the user should assign a value to the <columnname> property. In earlier versions, if you set <columnname>_IsNull to False, the property was silently reset to True. In SP2, the Script component raises a warning message about the incorrect usage.

Source: http://msdn2.microsoft.com/en-us/library/bb283536.aspx#BKMK_IntegrationServices