Solved

conversion failed when converting the nvarchar value '%' to data type int

Posted on 2009-05-19
5
1,179 Views
Last Modified: 2012-05-07
I've created a SP with select statement for use with crystal report. There is no cast/convert statement in the SP. This error occurred after i added in new columns into the view. Please help. Thanks

Here's the SP syntax :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go
 
 
 

-- =============================================

-- Author:		<Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[CST_Std Material Cost]
 

	@BP varchar(10),

	@ProcessVal char(1)
 

	

AS
 

If @ProcessVal = '1' /*With landed cost*/

BEGIN
 

	select * from  reporting.dbo.[Item Master With Cost] left join  reporting.dbo.[Supplier Price Catalogue]

	on ibitm = cbitm and ibmcu = cbmcu

	where ltrim(ibmcu) = rtrim(@BP) and a6prp5 <> ''
 
 

END
 

If @ProcessVal = '2' /*Without landed cost*/

BEGIN
 

	select * from  reporting.dbo.[Item Master With Cost] left join  reporting.dbo.[Supplier Price Catalogue]

	on ibitm = cbitm and ibmcu = cbmcu

	where ltrim(ibmcu) = rtrim(@BP)  and a6prp5= ''
 

END
 

If @ProcessVal = '3' /*All*/

BEGIN
 

	 	select * from  reporting.dbo.[Item Master With Cost] left join  reporting.dbo.[Supplier Price Catalogue]

	on ibitm = cbitm and ibmcu = cbmcu

	where ltrim(ibmcu) = rtrim(@BP)
 

END

Open in new window

0
Comment
Question by:sacred21
  • 3
5 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 20 total points
ID: 24428493
what are the datatypes of  ibitm , cbitm and ibmcu , cbmcu
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 20 total points
ID: 24428767
So what changed? New fields in dbo.report? What new fields? Did you change the stored procedure? You SELECT * FROM dbo.reports, so you select new fields. If The crystal report has no use for the new fields you're perhaps getting the error therefore.

What error do you get? Do you get it from SQL Server or from crystal report?

Bye, Olaf.
0
 

Author Comment

by:sacred21
ID: 24428770
ibitm & cbitm are float

ibmcu & cbmcu are nchar
0
 

Author Comment

by:sacred21
ID: 24428781
To Olaf,

The error was from MS-SQL. The complete error message is
"Database connector error 22018 : [Microsoft][SQL Native Client][SQL Server] conversion failed when converting the nvarchar value '%' to data type int"

The new fields are being added into the views ([Item Master With Cost] & [Supplier Price Catalogue] are views)
0
 

Accepted Solution

by:
sacred21 earned 0 total points
ID: 24428995
Anyway, i managed to solve the issue by removing the parameters and put them back into the SP and verfiy the crystal report. It works fine now.

Thanks all for your effort & attention.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 50
TSQL previous 5 25
sql2012 on vm use high  cpu even with simple query 7 14
SQL VIEW 7 23
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now