Database compatibility changing to 100 when restoring SQL 2005 to SQL 2017

Question: Don't ask , but I'm restoring some SQL Server 2005 databases onto a SQL Server 2017 - and there is some database compatibility behaviour I was not expecting. When the database is attached to SQL Server 2005 - the database compatibility is 90. But when restored to SQL Server 2017 - the database compatibility is set at 100. Why is this happening? Answer: This is expected behaviour. i.e when changing from a lower version to a higher version - if the database compatibility of the source db is lower than the lowest compatibility of the target SQL Server version... Read more →

Connect SQL Server to AS400 with a Linked Server

Question: I need to set up a Linked Server between SQL Server 2016 and AS400 to extract data from a file and import the data onto a table on a SQL Server database. How do I set up the Linked Server? Answer:Setting up a Linked Server from SQL Server to AS400 can be tricky , as there a number of variables. But the method I've outlined below has repeatedly worked for me. Thy trickiest part - and normally most fails occur is in the Provider String . the details below have worked for me 1)Step 1 : Obtain the Microsoft... Read more →

How to create a Linked Server for SQL Server to PostgreSQL

Question: I need to create a SQL Server Linked Server connection from a SQL Server to a PostgreSQL database on Linux. The purpose is to check through a basic test from INSERT and SELECT statements for data insert and retrieval . I've checked through a few sites and there are different approaches - do you have a standardized method which you utilize for this purpose.? Answer: Increasingly I'm getting more requests within my own projects to create connectivity between different database server systems. It's not uncommon to link SQL Server with all other systems such as DB2 LUW, Oracle, MariaDB... Read more →

How to open Task Scheduler by cmd

Question: I'm on a Windows 2016 trying to open Windows Task Scheduler by cmdline - but it's not opening?Could you lete me know the cmd line reference for opening Windows Task Scheduler? Answer: To start Windows Task Scheduler on Windows 2016 use this cmd from the cmdline function: control schedtasks This will open a GUI similar to this one: Read More on Windows Administrative tasks Windows Server 2012 empty recycle bin (SQL Server DBA) Find Windows pagefile location through command line and How to write to a sql error to windows server event viewer (SQL ... Read more →

Alter database compatibility on all databases in a SQL Server Instance

Question: I have a SQL Server Instance with 102 databases at a database compatibility level of SQL Server 2012 (110). I've recently migrated all the databases onto a SQL Server 2016 instance - and the database compatibility level is still at 110. Is there a scripted way I can change the database compatibility level of all the user databases to SQL Server 2016 (130), rather than having to hand crank for every database? Answer: Hi - this is a common problem particularly during SQL Server migration work , which you could solve by generating a script with all the ALTER... Read more →