Re: Invalid character value for cast specification

#1

Actually, I did experiment with the other data types and
they do work. However, your answer does not explain the
problem.

If INT in a DTS does not equal an INT in a SQL Server
Database, then what the heck? Apparently, it doesn't
because it would not accept even 46678. I know the data
coming into the global variable is a 4 byte integer and I
must know for sure the what the size of the DTS data
types are.

Bill

Quote:

-----Original Message-----
Change your datatype from INT to INTEGER in the global
variables section.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Bill" <wrlucas (AT) it-pma (DOT) com> wrote in message
news:033e01c37010$c5b7a380$a101280a (AT) phx (DOT) gbl...
I have a package that executes perfectedly when the
global variables are created as the string data type.
It
consist of one task Execute SQL Task that extracts data
from a SQL Server 2000 database and sends it to an
Excel
spreadsheet.

However, the two database columns (associated with the
global variables) data type are int . When I use the
int
data type for the global variables, I get the error as
stated in the subject line. I am specifying integers
as
values.

So the global dialog screen looks like this

Name Type Value
gvDebtorOrg_ID int 46678
gvKeyDateType_ID int 16

Simply changing Type to string makes the package run
just
fine.

The where clause of the query string looks like:
WHERE (N.DebtorOrg_ID = ?) AND (N.KeyDateType_ID
= ?)
AND (OA.DefaultFlag = 1 )

.

#2

Hello Bill,

I would appreciate your patience while I am looking into this issue. I will
post my response at soon as I have update for you.

Thanks for posting to MSDN Managed Newsgroup.

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

#3

Michael,

Thanks, I am glad to know it wasn't me. Are the DTS data
types documented somewhere so that I can know for sure
what their sizes are?

Bill

Quote:

-----Original Message-----
Hi Bill,

From your detailed description, I have reproduced this
problem on my side.
Also I have performed an experiment for this issue
myself below:

1. Create a table called testable in the Northwind
database.

CREATE TABLE [dbo].[Testtable] (
[field1] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[field2] [int] NULL
) ON [PRIMARY]
GO

Put some data in the testable.

2 Create a new package using SQL Server Enterprise
Manager.

Use "Microsoft OLE DB Provider for SQL Server"
connection and "Execute SQL
Task" task. In the property of "Execute SQL Task" task,
I type the SQL
statement below:

Select * form testable
Where (testable.field2=?)

I also create a global variable called globaltest as
parameter.
Name Type Value
Globaltest int 28

3. Press Execute button.

Then the error "Invalid character value for cast
specification" occurred. I
also use Unsigned Int datatype for global variable and
the same error
occurred. It seems that this is a problem with DTS,
Thanks for picking it
out. Also, I am glad that you had method that you use
Integer instead of
Int to work this problem around.

Thanks for using MSDN newsgroup.

Best Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and
confers no rights.

.

#4

Dear Bill,

Considering the nature of this problem, I will go ahead and escalate this
issue so that a Microsoft PSS Support Professional will be engaged with you
to further work on this problem. We will post the Final outcome of this
issue in the Newsgroup for Community benefit.

Thanks for posting to MSDN Managed Newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

#5

Hi Bill,

This problem has been filed. Here is the summury of this problem.

If a global variable is assigned a data type of Int, an Invalid character
value for cast specification message is generated when the package runs. If
string or integer is used the package runs successfully.

Here is a simple repro:

1. Create a table called testable in the Northwind database.

CREATE TABLE [dbo].[Testtable] (
[field1] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[field2] [int] NULL
) ON [PRIMARY]
GO

Put some data in the testable.

2 Create a new package using SQL Server Enterprise Manager.

Use "Microsoft OLE DB Provider for SQL Server" connection and "Execute SQL
Task" task. In the property of "Execute SQL Task" task, type the SQL
statement below:

Select * form testable Where (testable.field2=?)

I also create a global variable called globaltest as parameter.

Name Type Value
Globaltest int 28

3. Press Execute button.

If you change the type to string or integer it works fine.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

#6

Has anyone else seen this error message when using a global variable
with a Date data type? I have. I wonder if this could be another DTS
bug. This error occurs for me when I use a Transform Data task, but
works fine using an Execute SQL task.

--
Posted via http://dbforums.com

#7

Looks like there is some transalation happening. Show us what you enter for
both SQL Statements

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"gary7584" <member42288 (AT) dbforums (DOT) com> wrote

Quote:

Has anyone else seen this error message when using a global variable
with a Date data type? I have. I wonder if this could be another DTS
bug. This error occurs for me when I use a Transform Data task, but
works fine using an Execute SQL task.

--
Posted via http://dbforums.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Invalid character value for cast specification

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Invalid character value for cast specification - 09-01-2003 , 08:33 PM

Re: Invalid character value for cast specification - 09-03-2003 , 05:26 AM

Re: Invalid character value for cast specification - 09-04-2003 , 02:46 PM

Re: Invalid character value for cast specification - 09-04-2003 , 11:53 PM

Re: Invalid character value for cast specification - 09-21-2003 , 10:05 PM

Re: Invalid character value for cast specification - 11-06-2003 , 05:03 PM

Re: Invalid character value for cast specification - 11-07-2003 , 06:16 AM