Tag: SQL Server | Page 3

How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT

After deploying an SSIS package built with Visual Studio 2017, we encountered a surprise when running it via a Command Prompt:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
 
C:\Windows\system32>"D:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /File "D:\PROG\bin\SSIS_Extract.dtsx" /Conf "D:\PROG\bin\SSIS_Extract.dtsConfig"
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
 
Started:  5:04:56 PM
Error: 2018-11-26 17:04:56.20
   Code: 0xC001700A
   Source: SSIS_Extract
   Description: The version number in the package is not valid. The version number cannot be greater than current version number.
End Error
Error: 2018-11-26 17:04:56.20
   Code: 0xC0016020
   Source: SSIS_Extract
   Description: Package migration from version 8 to version 6 failed with error
0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".
End Error
Error: 2018-11-26 17:04:56.21
   Code: 0xC0010018
   Source: SSIS_Extract
   Description: Error loading value "<DTS:Property xmlns:DTS="www.microsoft.com/
SqlServer/Dts" DTS:Name="PackageFormatVersion">8</DTS:Property>" from node "DTS:
Property".
End Error
Could not load package "D:\PROG\bin\SSIS_Extract.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source: SSIS_Extract
Started:  5:04:56 PM
Finished: 5:04:56 PM
Elapsed:  0.203 seconds
 
C:\Windows\system32>

Pay attention to this section of error message:

Description: Package migration from version 8 to version 6 failed with error
0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".

What’s the real problem? It turns out that the SQL Server version in the production server is older than the version of the tool. The package was built with SSIS designer for Visual Studio 2017 (version 8), however the target database is SQL Server 2012 (version 6). So what do we need to do?

In the past, we needed to install an older version of Business Intelligence Development Studio that matches the SQL Server version we’re targeting. Because of this, many developers still have the habit of having several versions of Visual Studio installed on their machines just to handle every version of SSIS package. Fortunately with the newer of Visual Studio with SSDT, we can target the latest SQL Server (2017) down to 2012.

How do we target an older version of SQL Server?

Before we begin with the steps, make sure your Visual Studio 2017 has the SQL Server Data Tools installed. Otherwise, check the SSDT download at the bottom of this post.

How to target a specific version of SQL Server in SSIS

In your project (e.g., Temp1), right-click on the project name and click Properties (or press Alt-Enter).
Once the Property Page is opened, click on the Configuration Properties and check the TargetServerVersion.
Click on the drop-down list and select the correct SQL Server version on the machine where you want to deploy your SSIS package and click OK.
Rebuild your SSIS package and re-deploy the package.

This time you will be able to run the package without any problems.

Update 2 May 2019: Since the posting of this article, apparently Microsoft removed support for SQL Server 2012 in SSDT version 15.8.1, but added it back in version 15.9.0. Check your SSDT version if you don’t see an option to target SQL Server 2012.

Make sure you pay attention to the notes marked as Important. For example, before installing SSDT for Visual Studio 2017 (15.9.1), you need to uninstall Analysis Services Projects and Reporting Services Projects from Tools, Extensions and Updates and close all Visual Studio instances.

Download

SSDT for VS 2017 Installer

How to Use SQL Server Express to Store ASP.NET Session State

ASP.NET Session State SQL Server Mode provides a good way to have a persistent session, especially for server farm configuration. Here are the steps to quickly configure the environment to do so using SQL Server Express edition (because sometimes, you don’t need the full-blown version of SQL Server just to track sessions).

Steps

Download and install SQL Server Express.
Open the file, select install and follow the instructions.
On the Instance Configuration screen, you can change the Named instance if you want to, but for this example, it’s left with the default SQLExpress.
On the Server Configuration screen, make sure SQL Server Browser is set to Automatic so you can connect from a remote machine.
Keep moving along the screens and wait until the installation process completed. Make sure all components are successfully installed and click on Close button.

Create ASPState Database in SQL Server

In your DB server, open the Command Prompt (Admin) and type in the following command:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql -ssadd -d [DATABASENAME] -S [SERVERNAME]\SqlExpress -sstype c -E

If you use a custom database name (instead of the default ASPState by using option -sstype p), then at the end of the process, you’ll see the following message:

To use this custom session state database in your web application, please specifiy it in the configuration file by using the 'allowCustomSqlDatabase' and 'sqlConnectionString' attributes in the <system.web>\<sessionState> section.

So you have to change your default connectionString from:

<sessionState mode="SQLServer" cookieless="false" timeout="480" sqlConnectionString="Data Source=[SERVERNAME]\SqlExpress;User Id=MySession;Password=MySessionPassword" />

To:

<sessionState mode="SQLServer" cookieless="false" timeout="480" sqlConnectionString="Data Source=[SERVERNAME]\SqlExpress;Initial Catalog=[DATABASENAME];User Id=MySession;Password=MySessionPassword" allowCustomSqlDatabase="true" />

If you can’t still see the difference, there are two additions on the sessionState element. The first one is the addition of Initial Catalog=[DATABASENAME] under sqlConnectionString and the second one is at the end of markup, allowCustomSqlDatabase=”true”.

How to Show Field Values as Columns in SQL Server

One of the most asked questions by users when they pull data using T-SQL is whether or not there’s a way to present the values of a field they’re interested in as columns instead. The answer is of course it’s “Yes.” You can achieve this by using PIVOT relational operator if you use MSSQL 2005 or later.

Without PIVOT

USE [MyDB];

--WITHOUT PIVOT
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type]
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type]
ORDER BY CostCenter;

With PIVOT

USE [MyDB];

--WITH PIVOT
SELECT CostCenter, Desktop, Laptop FROM (
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type] 
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type] 
) t1
PIVOT
(SUM(Total) FOR [Type] in ([Desktop], [Laptop])) t2
ORDER BY CostCenter;

How to reset SA password on Microsoft SQL Server

If you happen to forget your sa password, you can still recover it as long as you have access to the server. Here are the steps to do it.

Steps

Launch Sql Configuration Manager under Configuration Tools folder.
Look for your SQL Server instance (the default is MSSQLSERVER) and stop the service. You can click the stop button while having the SQL Server (MSSQLSERVER) row highlighted or you can right-click on it and select Stop.
Launch the Command Prompt.
Next, we want to run the SQL Server in a single-user mode by adding “/m” parameter with the client application name:
net start MSSQLSERVER /m"SQLCMD"
Then we need to connect to the database on the machine using a trusted connection:
sqlcmd -E -S localhost
If you’re connecting to a database on a local machine, you can substitute “localhost” with a “.” (dot), which makes it look like so:
sqlcmd -E -S . or sqlcmd -E -S.
The two are identical except the former is easier to read. Another note is if you’re using SQL Server Express, you need to add “\SQLEXPRESS” after the period. You can see the difference on the example below.
After starting the sqlcmd, type the following SQL statement after the prompt. There is a difference in role assignment between SQL Server 2008 and SQL Server 2012:
For SQL Server 2008 or Older
```
                CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                GO
                sp_addsrvrolemember 'tempUser', 'sysadmin'
                GO
                
```
For SQL Server 2012 or Later
```
                CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                GO
                ALTER SERVER ROLE sysadmin ADD MEMBER tempUser
                GO
                
```
For SQL Server 2012 or newer, use ALTER SERVER ROLE should be used instead of sp_addsrvrolemember as this system stored procedure will be removed in a future version of Microsoft SQL Server.
You can type “exit” to quit SQLCMD.
Restart the Sql Server service to get out of the single-user mode:
net stop MSSQLSERVER followed by net start MSSQLSERVER
Launch SQL Server Management Studio and connect to the local database using the new login you just created.
Expand on Security, then expand on Logins.
Right-click on user sa and select Properties. Enter the new password and click OK. And you’re done.

Now you can login to the database using the sa login and the new password you set. For security purpose, make sure you delete the tempUser afterwards.

Ways to Upsert a Record in SQL Server

To continue the previous post, this article demonstrates ways to do Upsert (update and insert) and how MERGE statement in SQL Server 2008 (or later) can be more efficient to perform the two operations at once.

First we’ll create a table for this demo.

CREATE TABLE dbo.GroupInfo (
    Id    int unique not null
  , App   varchar(100)
  , DB    bit
)

We want to do update if the Id is found on the table and insert if it’s a new Id number.

1. Conventional way of doing it is by using IF EXISTS statement.

CREATE PROCEDURE [dbo].[p_UPSERT1]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM dbo.GroupInfo WHERE Id = @ID)
  UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
  WHERE Id = @ID
ELSE
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
        @ID
    , @APP
    , @DB
  )
  SET NOCOUNT OFF;

2. Second way of doing it is by taking advantage of the @@ROWCOUNT.

CREATE PROCEDURE [dbo].[p_UPSERT2]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
WHERE Id = @ID
IF @@ROWCOUNT = 0
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  )
SET NOCOUNT OFF;

3. The third and probaby the best way by using MERGE to perform INSERT and UPDATE operations on a table in a single statement.

CREATE PROCEDURE [dbo].[p_UPSERT3]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
MERGE INTO dbo.GroupInfo AS tgt
USING
  (SELECT @ID) AS src (id)
  ON tgt.Id = src.id
WHEN MATCHED THEN
  UPDATE        
    SET
        App = @APP
      , DB = @DB
WHEN NOT MATCHED THEN
  INSERT (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  );
SET NOCOUNT OFF;

Now, you can analyze the execution plan of each stored procedure on your own to compare them.

EXEC p_UPSERT1
  @ID = 1
, @APP = 'App 1'
, @DB = 0
GO

EXEC p_UPSERT2
  @ID = 2
, @APP = 'App 2'
, @DB = 0
GO

EXEC p_UPSERT3
  @ID = 3
, @APP = 'App 3'
, @DB = 0
GO

How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT

How to target a specific version of SQL Server in SSIS

Download

Further Reading

How to Use SQL Server Express to Store ASP.NET Session State

Steps

Create ASPState Database in SQL Server

Further Reading

How to Show Field Values as Columns in SQL Server

Without PIVOT

With PIVOT

Further Reading

How to reset SA password on Microsoft SQL Server

Steps

For SQL Server 2008 or Older

For SQL Server 2012 or Later

Further Reading

Ways to Upsert a Record in SQL Server

Further Reading

Recent Posts

How to target a specific version of SQL Server in SSIS

Download

Further Reading

Steps

Create ASPState Database in SQL Server

Further Reading

Without PIVOT

With PIVOT

Further Reading

Steps

For SQL Server 2008 or Older

For SQL Server 2012 or Later

Further Reading

Further Reading

Recent Posts

Tags