Archive for the ‘MS SQL Server’ Category

Free e-book Introducing Microsoft SQL Server 2016

June 7, 2016

Introducing Microsoft SQL Server 2016
Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Stacia Varga, Denny Cherry, Joseph D’Antoni



Microsoft SQL Server Developer Edition is now free

April 19, 2016

Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.

Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support.

SQL Server 2014 Developer Edition offers the full feature set of SQL Server 2014 Enterprise Edition, and allows you to build almost any kind of data solution on top of SQL Server. SQL Server 2014 delivers mission critical performance across all workloads with in-memory with in-memory built-in, faster insights from any data with familiar tools, and a platform for hybrid cloud enabling organizations to easily build, deploy, and manage solutions that span on-premises and cloud. It also delivers peace of mind with the fewest security vulnerabilities of any enterprise database six years in a row. [1] To learn more about the value proposition of SQL Server 2014, read the datasheet.

SQL Server 2016 Developer Edition, when released later this year, will also be free. To learn more about the exciting new features in SQL Server 2016, read the datasheet.

SQL Server Developer Edition does not include a licensed OS, such as a license for Windows 10 included on a new laptop. 90 to 180 day free trials of Windows and Windows Server are available on the TechNet Eval Center.

For customers needing a comprehensive database development solution, we also offer Visual Studio Professional and Visual Studio Enterprise subscriptions. Visual Studio subscriptions provide additional benefits, including:

  • Past and current versions of SQL Server (including Enterprise edition)
  • Past and current versions of Windows and Windows Server for dev/test
  • A monthly Azure credit of $50 to $150 to use for running dev/test workloads, including Azure SQL Database, SQL Server running in Azure Virtual Machines, and much more
  • 2 or 4 incidents with Microsoft Technical Support
  • Visual Studio Professional or Visual Studio Enterprise, for state-of-the-art database development
  • Source code management/version control, work item management, builds, and more using Team Foundation Server and Visual Studio Team Services
  • And much more…

Click here to download SQL Server 2014 Developer Edition from Dev Essentials. You will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition.

Click here for the original article

SQL Server Error: The ‘DbProviderFactories’ section can only appear once per file

July 10, 2015

You ever get the error: The ‘DbProviderFactories’ section can only appear once per file.

DbProviderFactores error

When trying to do something like create a new table with SQL Server Management Studio ?

New Table

I don’t know why, but one (or more) of the many maching.config files on your computer does, indeed, have TWO DbProviderFactories sections in it.  TWO of my machine.config files were like that, but the one causing this error was the one located here:


TWO DbProviderFactores

NOTE!  You’ll have to open your text editor as Administrator or you’ll be forbidden from saving changes to the file(s)!

Original Source

Creating a linked server with Microsoft SQL Server 2008 and HiT OLE DB providers

June 8, 2015

You can use HiT OLEDB Server/400 or HiT OLEDB Server/DB2 and Microsoft SQL Server 2008 to manage data stored on DB2 and integrate it with SQL Server. Using the Distributed Query features that Microsoft SQL Server 2008 offers, you can join data stored on native databases, with tables stored on DB2, MS Access files and almost any type of file. To use this functionality, you will need to create a Linked Server, and a connection to your remote database.

If you are trying to create a linked server to DB2/400, you will need HiT OLEDB Server/400. For DB2 running on other platforms, please use HiT OLEDB Server/DB2 instead.

Before creating the linked server, you need to set the “Allow InProcess” provider option. This is to allow Microsoft SQL Server 2008 to use the HiT OLEDB provider DLL by loading it directly into the Microsoft SQL Server 2008 address space.

Setting a Provider Option from SQL Server Management Studio

  1. From the menu Start\Programs\Microsoft SQL Server 2008, click on SQL Server Management Studio.
  2. Select Providers from the Server Objects/Linked Server section.
  3. Right-click on the HITOLEDB provider name, then choose the Properties option.
  4. Make sure that the the Allow inprocess option is checked.

Creating a Data Link File using the HiT Toolbox

The easiest way to create a linked server is to use an UDL file, also know as data link file. This will allow you to specify all connection parameters in one file and simply refer to the file for all future connections. A data link file can be created with the HiT Toolbox. For more information about creating and configuring a data link file, look under “Creating a Data Link in the Toolbox” in HiT OLEDB ToolBox Help.

Once you have created the data link file, here are a few things to note when creating a linked server

  • In the linked server property, data source field, type the name of the UDL file including the .UDL extension. For example, if you created a data link in the HiT OLEDB ToolBox and called it “MyAS400”, you should enter “MyAS400.udl” in the linked server property data source field.
  • In the linked server property, product name field, type the name of the OLE DB data source to add as a linked server. For example “OLEDB for AS400”.
  • When a data link file is created, by default, it is placed as follows:
    Windows XP: C:\Program Files\Common Files\System\Ole DB\Data Links Windows 2008, Vista or Windows 7: C:\ProgramData\HiT Software\Data Links
    In HiT OLEDB version 5.0, if you keep the UDL file in the default location, SQL Server is smart enough to know where to look for it and there is no need to specify the path. However, if you are using an older version of the HiT OLEDB provider or if you moved the file to a different location, you will need to specify the complete path to the file unless using the default location in Windows XP environment.
  • We recommend using the udl file for the connection. However, if you need to run INSERT, UPDATE or DELETE statements in addition to SELECT statements, please add the following into the linked server provider string:
    Port Number=8471; Rowset Locate=True;

Then just follow steps 1 through 6 below to finish.

If you choose not to use a data link file to create a linked server, use the steps below.

Creating a Linked Server using SQL Server Management Studio

  1. From the menu Start\Programs\Microsoft SQL Server 2008, click on SQL Server Management Studio.
  2. Select Linked Server from the Server Objects
  3. Right-click on Linked Server, New linked Server and insert data in the configuration window.
  4. In the General tab insert the required parameters:

Linked Server:   Insert the reference name for this linked server. It’s preferable to use a name without spaces in order to avoid problems when using it in an SQL statement.

Provider Name: Select HiT Software OLEDB Provider for DB2/400 if you are trying to connect to the IBM DB2 for i (iSeries/AS400). If you are trying to connect to DB2 on platforms other than iSeries/AS400, please choose HiT Software OLEDB Provider for DB2 instead.

Product Name:   Insert the name of OLEDB data source to add as linked server.

Data Source:      Insert the IP address of your DB2 server or the name of a data link (UDL) file.

Provider String: Insert the connection string with the necessary parameters.


                Libraries=My_libraries; Init Timeout=90; Port Number= 8471; Host Code Page=037;Rowset Locate=True;

Please note that the parameters for provider string are cases sensitive.

  1. Select the Security For information on setting security parameters, click on Help.
  2. Use the Server Options tab to view or specify the following options:
  • Set Collation Compatible as TRUE if you want the WHERE and ORDER BY clauses on character fields to be resolved by DB2 and not by SQL Server. For a detailed description of the Collation Compatible option, look up Collation Compatible in the HiT Software knowledge base.
  • Check RPC Out to call a DB2 stored procedure using Query Analyzer and T-SQL.

After this operation, the tables on the linked server are available for all features that are compatible with Microsoft SQL Server 2008.

You can use either Four Part Name Query or OpenQuery with linked server. For example, for a Linked Server called AS400, open a new query window in SQL Server Management Studio and run either of the following:

Four Part Name Queries:

Select * from AS400.ServerName.MyLib.Customers Update AS400.ServerName.MyLib.Customers Set FIRSTNAME = ‘John’ Where ID=5 Delete from AS400.ServerName.MyLib.Customers Where ID=10

AS400 is the Linked Server Name ServerName is the IBM DB2 for i (AS/400) Server Name MyLib is the IBM DB2 for i Library Name Customers is the Table Name


Select * from OpenQuery (AS400 , ‘Select * from MyLib.Customers’ ) UPDATE Openquery (AS400 , ‘Select * from MyLib.Customers Where ID=5’ ) Set FIRSTNAME = ‘John’ Delete Openquery (AS400 , ‘Select * from MyLib.Customers Where ID=10’ )

Tips for SQL tuning for SharePoint

September 24, 2014

Part 1 – MAX DOP:

Part 2 – Shrink DB:

Part 3 – TEMPDB:

Enable ‘xp_cmdshell’ on Microsoft SQL Server

March 18, 2014

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.

Microsoft SQL Server Date Function

March 4, 2014

SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’
First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)’First Day of Current Week’
Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’
First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’
Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’
First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)’First Day of Current Month’
Last Day of Current Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))’Last Day of Current Month’
First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))’First Day of Last Month’
Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))’Last Day of Last Month’
First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)’First Day of Current Year’
Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))’Last Day of Current Year’
First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))’First Day of Last Year’
Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))’Last Day of Last Year’

Failed to decrypt protected XML node “DTS:Password” with error …

February 17, 2014

This issue occurs mainly due to security related issues. Follow the steps below in order to rectify the issue:

In Visual Studio, go to the Control Flow Tab

  • Right-click on an empty area inside the window, select properties.
  • Set Protection Level to EncryptSensitiveWithPassword
  • Set PackagePassword to “myPassword”
  • From the Connection Managers:
  • Double click connection
  • Click “All”
  • Under Security Area:
  • Set Password to your connection’s password
  • Set Persist Security Info to True
  • Save the package
  • Connect to SQL Integration Services in SQL Manager
  • Under Stored Packages:
  • Right-Click MSDB
  • Select Import Package
  • Set Package Location to File System
  • Set Package Path to the location of your dtsx file
  • Leave the rest as defaults
  • Click OK
  • Enter “myPassword” into the Package Password dialog
  • You have now successfully imported the package.
  • In order to create an SQL Job:
  • In the job step:
  • Set the Type as SQL Server Integration Services Package
  • Set the Package Source as SSIS Package Store
  • Set the Server as the database server where you stored your package in the steps above
  • Click the button for the package and choose your package.
  • Click OK
  • Enter “myPassword” into the Package Password dialog
Success. You may now schedule and execute the job.

SSIS Error : Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”

February 13, 2014

A SSIS Package Execution Error

Got this when scheduling a SSIS package via SQL Server Agent

Description: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”

The developer had a saved the package with a stricter security level.
I solved by changing the ProtectionLevel property to DontSaveSensitive
(The solution was using SSIS Package configuration files)

Read more:

MS SQL Server 2012 unable to find the requested .Net framework data provider

February 6, 2014

There are multiple machine.config files in your \Windows\\Framework\vXXXX\Config directories.  Check both of the v2 and v4 machine.config files for the following (four files total you’re looking at here).
There was an EXTRA, self terminated <DbProviderFactories> tag in two of my files.  I had all four open at once comparing them, and noticed in two of them, the V4 I believe, but check them all.  Essentially it was like this and very easy to miss:

<add name=”Odbc Data Provider” invariant=”System.Data.Odbc” … />
<add name=”OleDb Data Provider” invariant=”System.Data.OleDb” … />
<add name=”OracleClient Data Provider” invariant=”System.Data … />
<add name=”SqlClient Data Provider” invariant=”System.Data … />
<add name=”IBM DB2 for i .NET Provider” invariant=”IBM.Data … />
<add name=”Microsoft SQL Server Compact Data Provider” … /></DbProviderFactories>

Notice the CLOSING tag at end of the <DProviderFactories> last item, in this case the “Microsoft SQL Server Compact Data Provider”, and then there’s another SELF CLOSING <DBProviderFactories> tag.  Clean this up by keeping the one after the last item and deleting the self closing one at the end.
I rebooted, walked away, came back and it worked . . . so be SURE to reboot, walk away and come back or might not work.