The old "Invalid data for type 'numeric'" issue...

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

The old "Invalid data for type 'numeric'" issue...

Author

Topic

RBelknap
Starting Member

1 Post

Posted - 2011-04-01 : 16:33:59

I have Oracle 10g v10.2.0.3.0 (64 bit) installed on the same server that my SQL server 2008 is installed on.

I am using a linked server to try to pull data from Oracle tables to create a table in my SQL Server but am consistently getting the "Invalid data for type 'numeric'" error because two of the 25 or so fields I am pulling into my table are numeric.

I understand that there is supposed to be an upgrade to the version of Oracle that will resolve the issue, but its not really a quick option for me.

I was told that you can do a conversion of the datatype to get around the issue but I don't understand how to put that information into practice in regards to my query. Can I simply make the conversion in the SELECT..INTO that I am doing? Or do I have to string together several things (queries, stored procedures, etc).

Basically I am new to SQL server (from Access) with very little experience with sql experience let alone stored procedures or putting together multiple queries.

Any information would really be helpful.

Here is a basic select statement for what I am looking at doing

SELECT a.AGENTID, --numeric field
a.AGENTNAME, --varchar
a.AGENTDOLLARS, --numeric field
INTO mySQLServerTable
From myLinkedServer..MYSCHEMA.MYTABLE as a

Can I convert the two numeric fields in this type of query (if so an example of the code would really help) or do I need to do it elsewhere then run this query for it to work?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-01 : 18:29:58

You can try

SELECT AGENTID = convert(varchar(20),a.AGENTID), --numeric field
a.AGENTNAME, --varchar
AGENTDOLLARS = convert(varchar(20),a.AGENTDOLLARS), --numeric field
INTO mySQLServerTable
From myLinkedServer..MYSCHEMA.MYTABLE as a

but will probably have to use an openquery or openrowset to send the select statment to be executed on oracle and do the convert there.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources