DELETE FROM OPENQUERY

Question: How can I DELETE using OPENQUERY? Normally for a SELECT from OPENQUERY , I'll do something like : SELECT col1 ,col2 FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Can a similar method be used but for DELETE? Answer: To use DELETE , it's a similar process to the SELECT but using the DELETE syntax. Taking the SELECT example above - you'll do the following. Note:this example will delete ALL the rows in the linked server table . : DELETE FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Read more on OPENQUERY,Linked Servers and AS400 SQL Server – Datetime date range , AS400... Read more →

Checking compatibility for a SQL Server database migration to Azure with sqlpackage.exe

The SSMS installation comes with a command line utility called sqlpackage.exe . Commonly used to export sql server databases to Azure. On a typical default installation the path will be like : C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe One of it’s features is the compatibility checks ,generating a report of potential issues The basic structure for using the compatibility checks is : 'sqlpackage.exe /Action:Export /ssn:< server_name > /sdn:< database_name > /tf:< target_file > /p:TableData=< schema_name.table_name > > < output_file > 2>&1' Argument Description < server_name > source server name < database_name > source database name < target_file > file name and... 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 →

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

Linking SQL Server & PostgreSQL via Linked Server Read more →