System upgrade to SQL SERVER 2017 with a period of time, times wrong u890 system in, the inspection found a grammar in 2008 r2 is not an error, but above the SQL server2012 version error, want to temporarily reduce the database to r2 2008, there were hundreds of G in database, introduces the method of online is the export script, and then execute, this approach is certainly not feasible, I wonder if there are no other good place? If there is a point charge is good way to solve,

CodePudding user response:

Try adjust the compatibility level directly, if the fix, then only derivative according to to 2008
The ALTER DATABASE database_name
The SET COMPATIBILITY_LEVEL=100
Of course, if statements can change, or change advice statement

CodePudding user response:

Compatibility level is 100, because from the old library upgrade, the default is 100, can be taobao sellers said, also said that is not the way to guide the script

CodePudding user response:

Ufida U890 SQL support only sql2000, sql2005 with sql2008

CodePudding user response:

Yes, 2018 when junction is year after year after year to upgrade to the SQL server 2017, so the problem in knot only met this year

CodePudding user response:

What is the details of the error message? The corresponding SQL syntax also stick?

CodePudding user response:

Actually export - import data is not so difficult as you think, do not use the means of SQL script, you can through the import tool or link server data transfer,
As long as the network and server IO performance on both sides ok good, should have no problem.

CodePudding user response:

Script like this:

 select hr_sys_setdict. Ctablecode From hr_sys_setdict, UFDATA_168_2019. Dbo. Hr_sys_setdict where hr_sys_setdict. Ctablecode like 'hr_ct %' and hr_sys_setdict. Ctablecode=UFDATA_168_2019. The dbo. Hr_sys_setdict. Ctablecode and hr_sys_setdict. Ctablecode not in (select distinct name From sysobjects where the name like 'hr_ct %' and xtype='V') 

An error message is this:
Message 1013, level 16, state 1, line 1
The object "in the FROM clause UFDATA_168_2019. Dbo. Hr_sys_setdict" and "hr_sys_setdict" with the same name, please use the relevant name to distinguish them,

The above script in SQL server 2008 r2 is not an error, feel free to test table name change

CodePudding user response:

Just in SQL2008 R2 environment test LZ SQL script, complains of oh,

 
- confirm that the database environment 
Select @ @ version 
--> Return to the Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)... 


- open the master database 
Use the master 

- create a table master. The dbo. Hr_sys_setdict 
The create table hr_sys_setdict (ctablecode varchar (50)) 


- create a database UFDATA_168_2019 
The create database UFDATA_168_2019 

- open UFDATA_168_2019 database 
Use UFDATA_168_2019 

- create a table UFDATA_168_2019. Dbo. Hr_sys_setdict 
The create table hr_sys_setdict (ctablecode varchar (50)) 


- open the master database 
Use the master 

- performs the query 
The select hr_sys_setdict. Ctablecode 
The From hr_sys_setdict, UFDATA_168_2019. Dbo. Hr_sys_setdict 
Where hr_sys_setdict. Ctablecode like 'hr_ct %' 
And hr_sys_setdict. Ctablecode=UFDATA_168_2019. Dbo. Hr_sys_setdict. Ctablecode 
And hr_sys_setdict. Ctablecode not in (select distinct name from sysobjects where the name like 'hr_ct %' and xtype='V') 

/* 
Msg 1013, Level 16, State 1, Line 19 
The objects "UFDATA_168_2019. Dbo. Hr_sys_setdict" and "hr_sys_setdict" in The FROM clause have The same exposed names. 
Use the correlation names to distinguish them. 
*/

CodePudding user response:

Recommend the following wording, in the name of the table after add alias a and b, SQL is not an error, also very easy to read.

 
The select a.c tablecode 
A, the From hr_sys_setdict UFDATA_168_2019. Dbo. Hr_sys_setdict b 
Where a.c tablecode like 'hr_ct %' 
And a.c tablecode=biggest tablecode 
And a.c tablecode not in (select distinct name from sysobjects where the name like 'hr_ct %' and xtype='V') 

CodePudding user response:

Thank moderator patient answer, I just test again, why should I test in 2008 r2 is to support the wording, but version verification is not support, because I have a lot of local database, some libraries may come up from the old version, the default level of compatibility with 80 and 90, SQL server 2008 r2 corresponding is 100, and the new database is compatible with the level of 100, after the test in the level of compatibility is not supported by the 90 and 100, shows that this kind of writing is the sqlserver2000 way,
If it is my own program, add an alias can deal with, but this is the friend u890 year knot tools code, don't change it, trouble,

CodePudding user response:

Advice consulting software provider, should be the solution.

CodePudding user response:

Database seemed to drop the class

CodePudding user response:

refer to the second floor netghost response:

compatibility level is 100, because from the old library upgrade, the default is 100, can be taobao sellers said, also said that is not the way to guide the script

To someone else, that no matter what method, can do it, you don't like to use the guide of the script, it will tell you not to use guide way of the script,
I use the BCP, for example, the use of SSIS are not guide the script, I write my own script processing, can also tell you not guide script
So the method is not important, the important thing is not support directly

CodePudding user response:

Can try to contact the friend, and see if I can get a new version, it is best to software upgrade, 2000, after all, the writing is too old

CodePudding user response:

Thanks for bosses to reply, I to the clean-up of the whole library all tables, first to record some don't need a few big historical data to filter out in the migration process, some of the temporary table in sync, stay in library history, and then write a script processing data synchronization, delete a foreign key, with foreign key constraints have since growth column, stopping the growth, stop trigger flip-flop, then synchronize data, synchronous finish one by one again, found a few tables, it is the foreign key associated with multistage need recursive delete it, I'm not use nocheck to disable the foreign keys, if is nocheck method cannot use truncate table, use the delete slow a lot, I wonder if you have hands without recursive delete foreign key code, so send a period of use, thanks!

CodePudding user response:

The old version of the friend is no longer supported, the two new patch cannot solve the years, the consideration now is script synchronization database solution,

CodePudding user response:

Personally think that directly relegated simpler
If it is just in knot function, also can consider to find a computer installed back to the previous version, decrease the junction function using years after years, with the profile tracking all processing operations, oneself write this script after finishing the new version of the available storage process, and then write a small program and replace function