| Author |
Topic |
|
terrenceleungsc
Starting Member
2 Posts |
 Posted - 2007-09-22 : 04:36:56
|
| Dear All,I am using the backend SQLServer with Powerbuilder, I write a stored procedure and tried to create a datawindow to use that procedure but failed, it returns error code 22005 -Invalid Character value for cast specification. I tried to change any setting in ODBC but still failed.Is anyone can give help? Thanks in advance.Terrence. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-22 : 04:53:28
|
| "Invalid Character value for cast specification"Might be part of the way that powerBuilder talks to SQL Server, but outside of that the message implies a datatype conversion that is failing.A date offered as a text string, but in a format that SQL Server can;t handle - or the date is genuinely invalid. That sort of thing.(Notwithstanding the fact that the message says "Invalid Character value for cast specification." which perhaps implies trying to convert text, or a number larger than 255, to a character.)Kristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-24 : 01:59:37
|
| Can you post the procedure code?MadhivananFailing to plan is Planning to fail |
|
|
|
terrenceleungsc
Starting Member
2 Posts |
Posted - 2007-09-24 : 09:36:12
|
| I have successfully created the stored procedure (sp) in database, the heading of the sp is like this:ALTER PROCEDURE [dbo].[sp_upd_ac_for_tb] @as_test char(5)AS declarethe original sp can be executed in server and it can be viewed in powerbuilder (PB). In PB, when a new datawindow is being created and even the source of data sp can be selected, but error msg comes out, it said, 'SQLSTATE = 22005 [Microsoft][ODBC SQL Server Driver] Invalid character value for cast specification 1 executed dbo.sp_upd_ac_for_tb;1 @as_test=:as_test'then no datawindow can be created.In fact, I have tried different data type such as datetime, int, char(5), etc. There is no success.I have also tried to change different parameters in the ODBC connection, but the error still comes out.Terrence. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-24 : 09:40:13
|
| Why dont you post the full code used in the procedure?MadhivananFailing to plan is Planning to fail |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
lnduncan
Starting Member
6 Posts |
Posted - 2009-02-06 : 09:07:03
|
| I hope I can add to this topic. I am having the same problem as "terrenceleungsc". I cannot see where anyone has solved this issue. My code is a little different, but the result is the same.My codeSQLServer with Powerbuilder, using a stored procedure to create a datawindow to use that procedure. One parameter is passed to the procedure from the datawindow and this is a numeric datatype. The procedure will return multiple rows with two columns. One column is a numeric, the second is a varchar(255). My procedure:CREATE PROC dbo.am_features_sp ( @asset_id numeric = NULL )AS BEGIN/***********************************************************Procedure Name: am_features_sp*Database: Asset*Server: DS077001TEST*SQL-Builder4.3*File Path:* H:\ciim\Asset\am_features.PRO*********************************************************/CREATE TABLE #temp_features( feature_id numeric, feature_desc varchar(255))DECLARE@asset_type_id numericSELECT @asset_type_id = asset_type_idFROM dbo.am_assetsWHERE asset_id = @asset_idIF @@ROWCOUNT = 0 INSERT INTO #temp_features SELECT feature_id, feature FROM dbo.am_featuresELSE INSERT INTO #temp_features SELECT af.feature_id, af.feature FROM dbo.am_features af, dbo.am_valid_asset_type_feature avaf WHERE af.feature_id = avaf.feature_id AND avaf.asset_type_id = @asset_type_id SELECT * FROM #temp_featuresI hope someone can be of helpThanks so much |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-06 : 09:18:37
|
| not sure if that's case, but you've missed putting precision and scale values for numeric fields. |
|
|
|
lnduncan
Starting Member
6 Posts |
Posted - 2009-02-06 : 09:41:32
|
| Thank you for your suggestion, but that didn't make any difference. This is our first attempt at using MSSQLServer, all of our other apps are using Sybase. Precision and Scale were never an issue with Sybase. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-06 : 09:47:19
|
| how will you be executing the sp? what will be typical examples of values passed from app? |
|
|
|
lnduncan
Starting Member
6 Posts |
Posted - 2009-02-06 : 09:52:28
|
| The sp will be called from a PowerBuilder datawindow. It generates this code "execute dbo.am_features_sp;1 @asset_id = :asset_id". If I run this code directly through a database utility, it works as it is coded. The only issue is when the PB code tries to execute it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-06 : 10:03:33
|
| are you sure its passing only valid numeric value to sp? |
|
|
|
lnduncan
Starting Member
6 Posts |
Posted - 2009-02-06 : 10:18:18
|
| Yes, it is a Long datatype in the PB code. The value I am passing can be a numeric or a NULL value. |
|
|
|
lnduncan
Starting Member
6 Posts |
Posted - 2009-02-06 : 10:29:31
|
| Thank you so much for your help. A solution was found on Sybase's site. Needed the parameters StripParmNames='Yes' and CallEscape='No' |
|
|
|
ASD
Starting Member
1 Post |
Posted - 2009-03-06 : 11:09:43
|
| Hi lnduncan, Can you please elaborate, where do add/set these parameters(StripParmNames='Yes' and CallEscape='No')? I am getting the same error.... Thank you in advance. |
|
|
|
lnduncan
Starting Member
6 Posts |
Posted - 2009-03-06 : 11:29:55
|
| This was set on the Connect to the databaseThis.DBParm = "AppName = 'Assetman', Host = ' + is_computer_name + 'ConnectString='Provider=MSDASQL;Driver=SQL Server;Server=DS077001TEST;Database=Asset;UID=;PWD=;StripParmNames='Yes' and CallEscape='No'"Connect Using This ; |
|
|
|
|
Invalid Character value for cast specification