error converting nvarchar to int

I have this stored procedure and am getting errors.
I filtered on Info = 'I' which should only have numeric values. I have some
info with Info = 'L' that does have alpha code.
I have tried the case below but am still having problems.
Any help appreciated
CREATE PROCEDURE NTF_UpdateInfoAfterPost
@.orderNo varchar(30),
@.rUser int
AS
--Used to delete the Info messages from all users.
--Typically used before creating a new info message
update Notify
Set
signed = 4,
rUser = @.rUser,
DateSigned = GetDate()
where (Info = 'I' and Info is not null) and orderNo = Case When
isNumeric(@.OrderNo) = 1 then @.OrderNo Else 0 End
Stephen K. MiyasatoHi
Try
update Notify
Set
signed = 4,
rUser = @.rUser,
DateSigned = GetDate()
where (Info = 'I' and Info is not null) and orderNo = Case When
isNumeric(@.OrderNo) = 1 then @.OrderNo Else '0' End
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:%23IxODu4gGHA.1792@.TK2MSFTNGP03.phx.gbl...
>
> I have this stored procedure and am getting errors.
> I filtered on Info = 'I' which should only have numeric values. I have
> some info with Info = 'L' that does have alpha code.
> I have tried the case below but am still having problems.
> Any help appreciated
>
> CREATE PROCEDURE NTF_UpdateInfoAfterPost
> @.orderNo varchar(30),
> @.rUser int
> AS
> --Used to delete the Info messages from all users.
> --Typically used before creating a new info message
> update Notify
> Set
> signed = 4,
> rUser = @.rUser,
> DateSigned = GetDate()
> where (Info = 'I' and Info is not null) and orderNo = Case When
> isNumeric(@.OrderNo) = 1 then @.OrderNo Else 0 End
> Stephen K. Miyasato
>

Error converting data type varchar to numeric.

DECLARE @.ENTITY nvarchar (100)

set @.ENTITY = 'AccidentDimension'

DECLARE @.FIELD nvarchar (100)

set @.FIELD = 'JurisdictionState'

DECLARE @.KEYID nvarchar (100)

SET @.KEYID = '1234567890'

DECLARE @.VALUE nvarchar (100)

SET @.VALUE = 'WI'

DECLARE @.WC_TABLE NVARCHAR(100)

SET @.WC_TABLE = 'WorkingCopyAdd' + @.ENTITY

DECLARE @.SQL1 NVARCHAR (1000)

SET @.SQL1 = 'INSERT INTO ' + @.WC_TABLE+ ' (Claim, '+ @.Field +') VALUES ('''+ @.KEYID +''', '''+@.VALUE+''')'

EXECUTE sp_executesql @.SQL1

Can somebody help me. I get this error:

Error converting data type varchar to numeric.

while executing this Dynamic TSQl Command

Odd; what is the data type of the "JurisdictionState" column. The other column should be fine, but also what is the data type of the "Claim" column.

|||

The statement you are executing is

INSERT INTO WorkingCopyAddAccidentDimension (Claim, JurisdictionState) VALUES ('1234567890', 'WI')

There is a bigger question than the varchar/numeric conversion, and that is why you are using dynamic SQL, but to answer the question you asked, it doesn't look like there should be a problem. Is this the exact code that is failing, or might it be failing on another @.KEYID value?

I suggest you look at www.sommarskog.se/dynamic_sql.html for more on dynamic SQL, even thought it's tangential to your specific question here.

Steve Kass

Drew University

http://www.stevekass.com

|||

Claim is of type: varchar

JurisdictionState is char of length 2

I seperated and inserted with each colunm at a time to know if its problem with Claim or JursidictionState. Well, both of the time I received same error.

|||The error may be caused by an incorrect default, as in teh following sample:

CREATE TABLE a(i int, j INT DEFAULT('acs'))
GO
INSERT a(i)VALUES(1)

also the error might be caused by a trigger. Do you have triggers on the table?|||

Well I had DEFAULT ' ' (A Blank / Space)

Is this the problem ?

|||

yes.. yes..

Always try to stick with the column datatype while giving the default value..

I am really wondering how SQL Server allows to create the default value with different datatype..

|||

CREATE TABLE WCAddAcciDim

([Claim] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' ',

[JurisdictionState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' '

)

GO

INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('12', 'IN')

INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('1234567890', 'WI')

select * from WCAddAcciDim

DROP TABLE WCAddAcciDim

Well I see no problem when I execute this.

And when I execute this below line, just this line, without use of Dynamic SQL

INSERT dbo.WorkingCopyAddAccidentDimension(Claim, JurisdictionState) VALUES ('1234567890', 'WI')

I get error.

Very Strange.

I created teh table WorkingCopyAddAccidentDimension same way as I did above, infact I copied those lines and rename the tabel name thats it.

What might be the Hidden error, Any idea please

|||

What is the error message you are getting… Verify the table schema using..

Sp_help WorkingCopyAddAccidentDimensiona

|||

Mani:

When I removed the Defaults I was able to update and Insert as wanted and required. However i have NULLS in the rest of colunm. This table has 68 Colunms and I have multiple tables around 6of similar number of colunms. Now I am using the data from these Staging/ WorkingCopy tables and Inserting it back to main Table. Where certain colunm cannot be null. If there is a null in certain colunm it will not allow me to insert it. Thast why I chose to make BLANK as a default in WorkingCopy Table.

Now that I have remove BLANK/ SPACE from teh default, is there any Standard way of Replacing these NULLS with the Blank /Space. Could you please suggest any way to do this?

What does MS SQL Standards have to say on this?

What I could think of is to replace each colunm with a space were ever there is NULL but I guess this is not the standard way of doing. Any suggestion or any modification on re-creating WorkingCopy Table with Defaults?

|||

You are in wrong direction, The default won’t help you here..

The default only activated when you have no entry on the INSERT statement. When you try to INSERT the NULL value the Default value will not be taken, rather it will store as NULL.

In single word, the DEFAULT value only stored when there is no value/no entry specified in the insert query…

As per the BOL,

Column definition

No entry, no DEFAULT definition

No entry, DEFAULT definition

Enter a null value

Allows null values

NULL

Default value

NULL

Disallows null values

Error

Default value

Error

So, you have to use the ISNULL function to fix your problem.

Code Snippet

Create table #Staging1

(

Id int,

Name varchar(10)

)

Insert Into #Staging1 Values(1, NULL);

Insert Into #Staging1 Values(1, 'test');

Go

Create table #Main

(

ID int NOT NULL,

Name varchar(10) NOT NULL DEFAULT ('')

);

--Will Work Fine

Insert Into #Main(ID)

Select ID From #Staging1

--Should Fail

Insert Into #Main(ID,Name)

Select ID,Name From #Staging1

--Will Work

Insert Into #Main(ID,Name)

Select ID,Isnull(Name,'') From #Staging1

|||

Excellent

Got it

Thanks a lot Mani

Error converting data type nvarchar to int.

With the stored procedure below if i do
DECLARE @.ProductName nvarchar(40),@.ProductID int
EXEC updateProduct
@.ProductName = ProductName,
@.ProductID = ProductID
why do i get error:- Error converting data type nvarchar to int.
---
ALTER PROCEDURE updateProduct
@.ProductID int,
@.ProductName nvarchar(40),
@.LastUpdate datetime
AS
UPDATE
Products
SET
ProductName = @.ProductName
WHERE
ProductID = @.ProductID AND
LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT > 0
-- This statement is used to update the DataSet if changes are done on the
updated record (identities, timestamps or triggers )
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
FROM Products
WHERE ProductID = @.ProductID
GOHi Patrick,
The order in which you pass the parameters looks wrong.
Just try it this way:
EXEC updateProduct
@.ProductID = ProductID,
@.ProductName = ProductName,
@.LastUpdate = LastUpdate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Patrick.O.Ige" wrote:

> With the stored procedure below if i do
> DECLARE @.ProductName nvarchar(40),@.ProductID int
> EXEC updateProduct
> @.ProductName = ProductName,
> @.ProductID = ProductID
> why do i get error:- Error converting data type nvarchar to int.
>
> ---
> ALTER PROCEDURE updateProduct
> @.ProductID int,
> @.ProductName nvarchar(40),
> @.LastUpdate datetime
> AS
> UPDATE
> Products
> SET
> ProductName = @.ProductName
> WHERE
> ProductID = @.ProductID AND
> LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT > 0
> -- This statement is used to update the DataSet if changes are done on t
he
> updated record (identities, timestamps or triggers )
> SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
> FROM Products
> WHERE ProductID = @.ProductID
> GO|||Thx but if i do :-
DECLARE @.ProductID int,
@.ProductName nvarchar(40),
@.LastUpdate datetime
EXEC updateProduct
@.ProductID = ProductID,
@.ProductName = ProductName,
@.LastUpdate = LastUpdate
It still gives the error...
I want results in the Query Analyzer!
"Chandra" wrote:
> Hi Patrick,
> The order in which you pass the parameters looks wrong.
> Just try it this way:
> EXEC updateProduct
> @.ProductID = ProductID,
> @.ProductName = ProductName,
> @.LastUpdate = LastUpdate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Patrick.O.Ige" wrote:
>|||Then try this way
EXEC updateProduct
@.ProductID = CAST(ProductID AS INTEGER),
@.ProductName = ProductName,
@.LastUpdate = LastUpdate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Patrick.O.Ige" wrote:
> Thx but if i do :-
> DECLARE @.ProductID int,
> @.ProductName nvarchar(40),
> @.LastUpdate datetime
> EXEC updateProduct
> @.ProductID = ProductID,
> @.ProductName = ProductName,
> @.LastUpdate = LastUpdate
> It still gives the error...
> I want results in the Query Analyzer!
> "Chandra" wrote:
>|||There are a number of problems with your script. The primary problem is
that you are about how to declare and use local variables as the
arguments for a stored procedure, as well as the correct syntax to use for
executing a stored procedure. The second
problem is that you must supply values for all stored procedure arguments
that do not have defaults. Try the script below.
set nocount on
go
use Northwind
go
create PROCEDURE mytest
@.ProductID int,
@.ProductName nvarchar(40),
@.LastUpdate datetime
AS
if @.ProductID is not null
select * from Products where ProductID = @.ProductID
else
select * from Products where ProductName = @.ProductName
go
DECLARE @.ProductName nvarchar(40),@.ProductID int
EXEC mytest
@.ProductName = ProductName,
@.ProductID = ProductID
EXEC mytest
@.ProductName = @.ProductName,
@.ProductID = @.ProductID
EXEC mytest
@.ProductName = @.ProductName,
@.ProductID = @.ProductID ,
@.LastUpdate = null
set @.ProductID = 2
EXEC mytest
@.ProductName = @.ProductName,
@.ProductID = @.ProductID ,
@.LastUpdate = null
go
drop PROCEDURE mytest
go|||This is not only pointless, it also will not work. Both you and OP have
failed to declare the variables ProductID, ProductName, and LastUpdate; all
three of these names are not valid variable names. Assuming the variables
were declared and used correctly, both @.ProductIDs (the variable and the
argument) are already defined as integer - the cast is pointless.
Lastly, the order of the arguments in the execute statement is **only**
important when the argument names are not used.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B7FD86EC-8A71-473A-A817-1CBB7B351EAA@.microsoft.com...
> Then try this way
> EXEC updateProduct
> @.ProductID = CAST(ProductID AS INTEGER),
> @.ProductName = ProductName,
> @.LastUpdate = LastUpdate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Patrick.O.Ige" wrote:
>
done on the|||On Thu, 12 May 2005 20:33:02 -0700, Patrick.O.Ige wrote:

>With the stored procedure below if i do
>DECLARE @.ProductName nvarchar(40),@.ProductID int
>EXEC updateProduct
>@.ProductName = ProductName,
>@.ProductID = ProductID
>why do i get error:- Error converting data type nvarchar to int.
(snip)
Hi Patrick,
In most cases, things like ProductID and ProdcutName (without preceding
@. and not enclosed in quotation marks) would refer to either table or
column names. But since you can't use either in an EXEC command, SQL
Server simply assumes that you omitted the quotation marks, but wanted
to write a string constant nonetheless. Run the following code to prove
this:
create proc test (@.a varchar(20))
as
select @.a
go
-- With quotes
exec test 'This is a test'
go
-- No quotes - but they are "assumed"
exec test SecondTest
go
-- It only works for single words
exec test Third try
go
drop proc test
go
So obviously, the value 'ProductID' is passed as value for the
@.ProductID parameter to your proc - and since @.ProductID is declared as
integer, SQL Server will attempt to conert it, and fail.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx guys for the replies
*** Sent via Developersdex http://www.examnotes.net ***

error code 0xC0202025

i need to export the contents of a sql server 2005 table to excel in ssis. there are two nvarchar(max) colums in my table. the data flow task in my package fails unless i remove these two columns from the table, then it works fine. the error code being returned is 0xC0202025. HELP!http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=412859&SiteID=1|||no luck converting the columns to nvarchar(4000) either.|||Did you recreate the metadata in the Excel Destination?|||yes.