data type error from SQL server

any one familiar with the following error:
___________error___________
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'xxx_xx_xxx' to a column of data type int
___________error___________

any help will be greatly appreciated
thanks in advanceWhen does this error occur? Are you doing an insert, update? It just seems like you are trying to put a data type into a column that cannot be implicitly converted.|||rlopez,
thanks for your reply

Actually this is root of a bigger problem that I am having.
So at the base level what I am trying is check if a primary key is present in the database table or not via an ASP page.

I call a SP to execute a select command and determine the presence of an ID.

The call from the calling ASP page obtains this querying ID from another ASP page. (not sure if I make myself clear enough here)

The way this page sends ID to the querying ASP page is via a link whose syntax is as follows:
<a href=xyz.asp?id=123_456_789>123_456_789</a>

The Id in SP and DB is defined as varchar (16). I am not sure why "int" data type error is being thrown by the SQL server on the resulting ASP page.

Thanks Again|||It's a bit difficult to figure out what is going on without the underlying data structure. Can you email me or post the table schema and the stored procedure code that is involved?|||it got fixed
It required a change in the way the data was being retrieved in the ASP code.
I had to use request.Querystring to get it workin.

Thanks again

Data type error

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

You should be including single quotes around sAcct1 in the WHERE clause, such as "...WHERE Account < '" + @.User :: sAcct1 + "'"

I believe SQL Server is converting the value of your Account column to a numeric to match the datatype you are sending it. The error is occurring because you have data in that column that fails the conversion.
|||

wpwebster wrote:

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

Is it possible that Account column in tblGL table is numeric? if so, you make sure that you cast accordingly the values of acct1 from Acttbl table. Why are you casting it in the query as varchar and putting it in a string variable? would not be better to to use a data type that is consistent with tblGL.Account?

|||

Thanks for the input. It helped me get to the bottom of it. The Account field is a varchar(13) field although the accounts are in a format of something like 400.00 There was however several records I found where the Accocunt was NA, when I changed those through a derived column data flow control to a "0" it worked fine.

I still find it a bit puzzling that the WHERE clause worked when it was WHERE Account < '400.00' but wouldnt' work when it was WHERE Account < @.UserVariable

Making sure all Accounts looked like numbers did the trick though.

Thanks again for the input.

Regards

Bill