asked
Data corrupting on insert to Oracle table through a view
We recently changed from Win 2003, SQL Server 2005 SP3 (32 bit) to Win2008SP2, SQL Server 2005 SP3 (64 bit). We have a linked server to an Oracle 11g database and a view created to a table in the Oracle database. The view under 32bit resolved the oracle NUMBER type to a float but now resolves it to an nvarchar(384) and when using the view to insert rows to the Oracle table data in some (not but not all) these rows/columns get corrutped. We have tried upgrading the Oracle drivers to no avail. It seems that the problem exists in SQL Server. What suggestions would anyone have on how to correct this problem.
SQL Server 2005 R3 is not a version of SQL Server 2005. Do you happen to mean SQL Server 2008 R2 or SQL Server 2005 SP3?
I do not believe simply changing from 32-bit to 64-bit of SQL Server would have caused this problem. Is this view within SQL Server database, or in the source Oracle database? Did you verify the source table of the view did not get changed?
The view is in the SQL Server Database. THe Oracle table has been static for at least two years. The only change was the New 64 bit server/Op Sys/ and install of the 64 bit SQL Server. The first time we tried, we had not even recreated the view (just copied the database from the 32 bit to the 64 bit server) then when it corrupted the data with the insert into oracle_view Select * from Sql table we recreated the view and found the issue with the resolution of the Oracle NUMBER columns.
At the database level there is no difference between a 32-bit and 64-bit instance of SQL Server.
9 People are following this question.
