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 →

INSERT BULK in Profiler trace

In a Profiler trace you can see INSERT BULK statements , which are a mechanism for interface between SQL Server and a client bulk copy API.For example : .Net SqlBulkCopy & ODBC Bulk Copy methods. The Profiler trace whill display the INSERT BULK statement but the FROM part , meaning you cannot view the values. The INSERT BULK statement specifies the target tables \ columns including other meta data information - NULL management , triggers etc The tricky bit from a troubleshooting per, spective is you cannot view the values in the INSERT BULK statement. The values are transfered in... 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 →

Migrate SQL Server Reporting Services database from SSRS 2012 to SSRS 2016

Question: I need to migrate and upgrade an SSRS 2012 database to an SSRS 2016 . Could you supply me with a general framework for a migration path . Answer:This is a basic framework and you may need to build in extra some extra steps to prepare and validate your particular upgrade and migration path from SQL Server 2012 to SQL Server 2016. Back up ReportServer database on source SSRS 2012 Back up ReportServerTempDB on source SSRS 2012 Backup encryption keys on source SSRS 2012 Stop SSRS 2016 service on target server Restore ReportServer database Note: You should see informational... Read more →

When to use SQL Server Change Data Capture

There’s a number of things to consider in deciding on Change Data Capture (CDC) and deciding on the ideal use cases for CDC is very much dependant on the specific requirements. So it is important to understand in detail the project requirements. This should be weighed up against other solutions such as Triggers Some initial thoughts for discussion: For Auditing – CDC is an appropriate tool , as it can present the complete history of the data. Deciding on whether you’d rather use triggers will depend on reviewing the data in the change table and assessing whether it’s appropriate for... Read more →