variable

Question

variable

guru12

Ten Centuries

Points: 1068
More actions
August 20, 2010 at 3:43 am

#141667

Hi Every one..
I want to execute my stored procedure through variable,so i have written like this.it is throwing error like you have missed some paranthesis.my expression is
EXECUTE [GetAllChangesByDateTime] " +"'"+ (DT_WSTR,30) @[User::TableName] + "'" + "(DT_WSTR,30)(DT_DBDATE) @[User::StartDateTime] +"'" + "(DT_WSTR,10)(DT_DBDATE) @[User::EndDateTime] +"
can any one please correct my expression and reply back,its very urgent .need help
Regards..
guru12

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Please post the exact error message.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

I think it's your quotation marks. You need a double quote at the beginning of the expression. You also need to make sure that all of the non-numeric parameters to the stored procedure are enclosed in single quotes. Parameters should be separated by a comma. Once you get your quotes to match, you'll be able to evaluate the expression and see what it will look like when executed, and make corrections accordingly.

John

guru12 Ten Centuries Points: 1068 More actions · Answer 3

HI ....

Actual i tried like this

EXECUTE [GetAllChangesByDateTime] "+ (DT_WSTR,30) @[User::TableName] + "," + (DT_WSTR,30)(DT_DBDATE)

@[User::StartDateTime] + "," + (DT_WSTR,30)(DT_DBDATE) @[User::EndDateTime] +"

the error message is::

Error converting data type nvarchar to datetime.

i tried putting double quotations in front of execute and it is telling paranthesis is missing,so itried without double quotations.it is giving the above error.

Regards..
guru12

guru12 Ten Centuries Points: 1068 More actions · Answer 4

Hi Phil And John..

here is the exact error message

Error at Data Flow Task [OLE DB Source [16]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Incorrect syntax near ' + (DT_WSTR,30)(DT_DBDATE) '.".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Unclosed quotation mark after the character string ' + (DT_WSTR,30)(DT_DBDATE) '.".

(Microsoft Visual Studio)

how can i resolve it please make corrections and repost my statement

Regards..
guru12

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

What data type is your variable StartDateTime? Why are you converting it to a datetime data type and then to a string data type?

John

guru12 Ten Centuries Points: 1068 More actions · Answer 6

Hi John..

My startdatetime and enddatetime variables datatypes are object,so iam converting to string..

Regards..
guru12

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

OK, I may be wrong on this, and feel free to correct me if I am, but isn't an Object type variable for holding results sets? You use it for looping through the result set, and on each iteration, the values in the row are fed into scalar variables, which you use in your processing. Therefore I'm not sure whether trying to convert from an Object type to a Datetime type is a valid operation. But even if it is, why are you doing two conversions on the same value? Why don't you convert it straight to the data type you need?

John