Nicholson, Karen
knicholson at gpsx.net
Tue Oct 7 10:50:14 CDT 2003
Help, since we were talking about casting and converting, somewhat. I have an sp put together to take data that was imported from a csv file. All fields are in a table stored as varchar 255. It is choking on the ones that need to be converted to money. This is my syntax: (I followed the online books syntax, I thought, for converting varchar to moola. I have highlighted where I think it is choking and included the error message). Thanks. CREATE PROCEDURE gps_convert AS drop table gps_archive_request_data create table gps_archive_request_data( jrnlsrc_id char(4), batch_no int, journal_no int, journal_seqno int, co_no smallint, period_no tinyint, period_year smallint, dist_gl int, dist_sub int, branch_no int, tran_amt money, tran_date smalldatetime, trantype_id char(6), post_date varchar(25), change_user int, change_date smalldatetime, invoice_no int, invline_no smallint, reference varchar(20), invtry_qty smallmoney, revdet_no smallint, costctr_id varchar(6), gl_posted_date smalldatetime, payctrl_no int, payline_no smallint, revsum_no int, job_no int, jobline_no smallint, cust_no int, site_no int, site_branch_no int, ) insert gps_archive_request_data( jrnlsrc_id, batch_no, journal_no, journal_seqno, co_no, period_no, period_year, dist_gl, dist_sub, branch_no, tran_amt, tran_date, trantype_id, post_date, change_user, change_date, invoice_no, invline_no, reference, invtry_qty, revdet_no, costctr_id, gl_posted_date, payctrl_no, payline_no, revsum_no, job_no, jobline_no, cust_no, site_no, site_branch_no ) select convert(char(4), jrnlsrc_id), convert(int, batch_no), convert(int, journal_no), convert(int, journal_seqno), convert(smallint,co_no), convert(tinyint, period_no), convert(smallint, period_year), convert(int, dist_gl), convert(int, dist_sub), convert(int, branch_no), cast(tran_amt as money), /* This is choking*/ convert(smalldatetime,tran_date), convert(char(6),trantype_id), convert(varchar(25),post_date), convert(int,change_user), convert(smalldatetime,change_date), convert(int,invoice_no), convert(smallint, invline_no), convert(varchar(20), reference), cast(invtry_qty as smallmoney), /*This is choking*/ convert(smallint,revdet_no), convert(varchar(6), costctr_id), convert(smalldatetime,gl_posted_date), convert(int,payctrl_no), convert(smallint,payline_no), convert(int,revsum_no), convert(int, job_no), convert(smallint,jobline_no), convert(int, cust_no), convert(int,site_no), convert(int, site_branch_no) from gps_gl_archive This is my error message: Changed language setting to us_english. Server: Msg 293, Level 16, State 1, Procedure gps_convert, Line 36 Cannot convert char value to smallmoney. The char value has incorrect syntax.