How to fix error - The database cannot be opened because it is version 852. This server supports version 706 and earlier

Question:A vendor sent me some detached MDF files from a SQL Server and asked me to attach. I attempted the ATTACH without a log file option and got this error The database 'mydb' cannot be opened because it is version 852. This server supports version 706 and earlier. A downgrade path is not supported. The error message details are obvious i.e attempt to attach a database with a version level higher than the current version of the current SQL Server. The SQL error message says the current server is version 706 and attempting to connect a database of version 852.... Read more →

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 →