error with storedproc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

error with storedproc

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-03-07 : 06:51:18

Thank you in advance for your assistance on this
I have successfully created the following procedure:

CREATE PROCEDURE ti_symprodF0902
@jn char(12),
@lt varchar(3),
@fy char(2)

AS
select * from openquery(symprod_etldev,'select * from proddta.f0902 where gbmcu = ''@jn'' and gblt = ''@lt'' and gbfy = ''@fy'' ')

I then ran the procedure:

ti_symprodF0902 ' 100244710','AA','11'

but receive the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev" returned message "ORA-01722: invalid number".
OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev" returned message "ORA-01722: invalid number".
Msg 7320, Level 16, State 2, Procedure ti_symprodF0902, Line 8
Cannot execute the query "select * from proddta.f0902 where gbmcu = '@jn' and gblt = '@lt' and gbfy = '@fy' " against OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev".

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2011-03-07 : 06:58:35

Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

More Info
http://ora-01722.ora-code.com/

-------------------------
/R..

tariq2
Posting Yak Master

125 Posts

Posted - 2011-03-07 : 07:12:00

Okay thanks for your reply, but I don't quite understand.

I have three variables which are defined respectively as:
char(12)
char(2)
char(2)

so why shouldnt the below work

ti_symprodF0902 1002447101,AA,11

Thanks

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2011-03-07 : 07:18:02

Try this
..
select * from openquery(symprod_etldev,'select * from proddta.f0902 where gbmcu = '+@jn+' and gblt = '+@lt+' and gbfy = '+@fy+' ')

-------------------------
/R..

tariq2
Posting Yak Master

125 Posts

Posted - 2011-03-07 : 07:37:59

Gi, thank you for your ideas on this, much appreciated
The following does not quite work:

CREATE PROCEDURE ti_symprodF0902
@jn char(12),
@lt char(2),
@fy char(2)

AS
select * from openquery(symprod_etldev,'select * from proddta.f0902 where gbmcu = '+@jn+' and gblt = '+@lt+' and gbfy = '+@fy+' ')

When run, It yields the following message:

Msg 102, Level 15, State 1, Procedure ti_symprodF0902, Line 7
Incorrect syntax near '+'.

There is a squiggly red line under @jn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources