March 2, 2020 at 4:36 pm
Hello,
I'm performing a backup/restore operation from a SQL Server 2012 database server to a SQL Server 2016 database server.
The database is currently set to SQL Server 2008 compatibility within both database servers using the same 'SQL_Latin1_General_CP1_CI_AS' collation from the server level to column level.
Now when the database finished restoring on the 2016 instance, the datatypes within my views have changed from nvarchar to varchar.
The data for the view is being fed from an Oracle linked server.
I've never seen anything like this before. From what I can tell, all the settings match.
Any information you can provide me with would be much appreciated.
Thank you
March 2, 2020 at 5:41 pm
do your view definitions (ie create view/table) get controlled by the oracle server (i'm guessing heterogeneus data services) ?
there's a possibility (if this is the case) that oracle is not recognising the version of SQL and going back to var as a safety net?
are the data types switched immediately when you restore? that would be weird
also when you say the view has changed datatype, you mean the underlying tables? which is why i'm looking at the oracle aspect
MVDBA
March 3, 2020 at 12:59 pm
Hi Mike,
Yes the datatypes immediately get switched from nvarchar to varchar. I even dropped and recreated the view with the very same result.
From what I understand, the client is using Oracle 11.2 but I'll find out more from them.
Thank you for your reply.
March 3, 2020 at 2:30 pm
ok, lets be clear that we are on the same page
var and nvar are on the table. not the view. I think you are looking in the wrong place
dropping a view and recreating will not change the underlying tables
MVDBA
March 3, 2020 at 2:47 pm
I'm unsure what the data types are on the actual Oracle tables since i don't have access to that piece, but I'm going to find out.
The view is being created from Oracle via linked server.
Below is an example:
SELECT SBI, BOOKING_NO, ADMISSION_DATE, RELEASE_DATE, PED, LOCATION_04FEB09, PAROLE_DATE_SET, MAX_DATE, BOOKING_BEGIN_DATE, BOOKING_END_DATE, MODIFY_DATETIME
FROM OPENQUERY(<OracleDBName., 'Select * From
<schema name>.<table name>') AS name
March 3, 2020 at 3:08 pm
ok, now I think I understand - why not just use the convert(nvarchar(50) , myfield) in the view - but it won't help you if the oracle server is varchar
MVDBA
March 3, 2020 at 4:58 pm
I'm in the middle of doing the explicit conversion within the SSIS package, but I'm unsure why there are differences between the two.
I did just learn the Oracle database is 10G connecting to SQL Server 2016.
The datatypes align correctly within SQL Server 2012
March 3, 2020 at 5:23 pm
Now you are making less sense. How is SSIS involved?
are you saying that you are using a view within an SSIS package to get data into a table in SQL? if that's the case then don't drop and recreate the table - just truncate it and keep the data types
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply