Chrome requires https for input text fields

Possibly save 1 hour of your time: In October 2017, Chrome will be enforcing security warning for non https website that has input text fields. Your client may need to know this and decide to comply so their traffic will no face with not secure warning.

You will need to buy and bind the SSL certificate to your website.

See reference link for more details on how to setup for Azure website:

https://docs.microsoft.com/en-us/azure/app-service-web/app-service-web-tutorial-custom-ssl

Finally, you want to enforce https on your website so all HTTP requests will be redirect as a HTTPS request. One way to do that is through web.config but you will have to install URL Rewrite extension.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.webServer>
<rewrite>
<rules>
<!-- BEGIN rule ELEMENT FOR HTTPS REDIRECT -->
<rule name="Force HTTPS" enabled="true">
<match url="(.*)" ignoreCase="false" />
<conditions>
<add input="{HTTPS}" pattern="off" />
</conditions>
<action type="Redirect" url="https://{HTTP_HOST}/{R:1}" appendQueryString="true" redirectType="Permanent" />
</rule>
<!-- END rule ELEMENT FOR HTTPS REDIRECT -->
</rules>
</rewrite>
</system.webServer>
</configuration>

C 6.0 and the .NET 4.6 Framework

Advertisements

sql server seed identity reset

Possibly save 1 hour of your time: Sometimes after deleting some reference data in a table one want to change the identity to reseed at 1 or a certain value. The next time you insert a new record it will reseed starting at 1.

DBCC CHECKIDENT ('YourTableName', RESEED, 0);
GO

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

dacpac post deployment script not running

Possibly save 1 hour of your time: In your database project, you may set it to build as a dacpac. Then you can use this dacpac to deploy scripts to your database. However, after you setup a post deployment script, it still didn’t seem to execute after you deploy the dacpac to your target database. Here are some guidance:

  1. If you haven’t already, you can add post deployment script by right click any folder in your project and choose Add >> Script. This script should contain your script or point to another script using notation :r .\myfile.sql. Use doubt quotes to wrap file path/name with space.
  2. After add script, right click the file and ensure Build Action is set to PostDeploy.
  3. Now build the project.
  4. Go to bin folder and find the dacpac. You can right click on this file and unpack it somewhere. Once unpacked, please verify there is a file name postdeploy.sql with the script code inside. This should be it you script will be execute when you deploy your dacpac. Make sure you are targeting the right database and verify using the right database. I got burned once looking at a different staging database.

Command for deploying dacpac:

“C:\Program Files\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe” /Action:Publish /SourceFile:”Your Database.dacpac” /TargetDatabaseName:YourDatabaseName /TargetServerName:localhost /p:ExcludeObjectTypes=”ApplicationRoles;DatabaseRoles;Permissions;RoleMembership;Users;Credentials;ServerRoleMembership;ServerRoles” /p:BlockOnPossibleDataLoss=False

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

Error: SQL71006: Only one statement is allowed per batch. A batch separator, such as ‘GO’, might be required between statements.

Possibly save 1 hour of your time: This error happens when I am adding a post deploy script as part of my database project and then do a build.

When you add this script file to your project, you need to right click the properties and set Build Action to None. This should solve the issue.

Exam Ref 70-764 Administering a SQL Database Infrastructure

Caching big data in application using MemoryCache

Possibly save 2 hours of your time: I have only encountered this more recently in an application that deals with big dataset.
In this scenario, the application needs to provide a feature to download full datasets. And this dataset is huge (~75MB to ~125MB) in size. And there are many datasets.
It took awhile to query this data and do some calculation and send it back to the client app. So we decide to cache this data at the application level using System.Runtime.Caching.MemoryCache for subsequent faster performance. As you can see, the problem can become bigger as more datasets are downloaded and cached.
There are few options in no particular order:
  1. More expensive but easiest solution may be to increase memory on physical server to account for the size of the cache that can grow to.
  2. Recycle the application pool after reaching maximum virtual memory usage. See here for specifics.
  3. Set the CacheMemoryLimit on the MemoryCache object. This sets the amount of memory on the computer, in bytes, that can be used by the cache.

 

 

 

SSIS – Foreach Loop Container and Execute SQL Task

Possibly save 2 hours of your time: When you work on SSIS to do ETL, you may often encounter the need to loop through some records and process each record one at a time.
You can use the following steps:
  • Execute SQL Task – Get the records that need to be processed
  • Foreach Loop Container – Loop through each record
    • Execute SQL Task – Process one record at a time
ssis_foreachloop_1

Create two user variables:

  • i.e. CollectionIDs (Object) – One for collection of the IDs to be processed
  • i.e. CollectionID (Int32) – the current ID to be processed

 

The following will get the Full result set based on a select query. In this case as you can see on the SQLStatement, we select a primary key ID column from a table based on some criteria input. i.e. select ID from someTable i inner join anotherTable d on i.SourceID = d.SourceID where d.Title = ?

ssis_foreachloop_2

You may use a parameter input via Parameter Mapping in your query (i.e. select id from table where name = ‘someVariable’).

ssis_foreachloop_3

Now you want to store your Result Set into a user variable for looping through later.

ssis_foreachloop_4

In the Foreach Loop Editor, you can use Foreach ADO Enumerator, and select the collection user variable.

ssis_foreachloop_5

We want to a user variable to store the current processed id.

ssis_foreachloop_6

You can specify a query or stored procedure to process each record.

i.e. [schema].[usp_ProcessRecord] NULL, ?

The above example has two input variables where one is always NULL and the ? represents an input parameter.

ssis_foreachloop_7

In this example, the input is a large integer.

ssis_foreachloop_8

 

Regular Expressions (Regex)

Possibly save 1 hour of your time: There are many times where I have to do some sort of pattern matching and then extract certain parameters.

Regex can be helpful in matching some sort of field requirement or even better, it can help extract parameters from the matching pattern.

The Regex object has a Groups property in which if something matches, then the first array element returns the entire matching pattern. And within the pattern you may define multiple groups of patterns and so the second array element will return the first group pattern and third array element will return the second group pattern.

The following are two examples of usage.


[Test]
 public void TestRegex()
 {
 var text = "123-4567890";

//brackets are groups
 var regex = new Regex(@"(\d\d\d)-(\d\d\d\d\d\d\d)");
 var match = regex.Match(text);

Assert.That(match.Success, Is.True);
 Assert.That(match.Groups[0].Value, Is.EqualTo(text));
 Assert.That(match.Groups[1].Value, Is.EqualTo("123"));
 Assert.That(match.Groups[2].Value, Is.EqualTo("4567890"));
 }

[Test]
 public void TestRegex2()
 {
 var text = "DatasetName='Population',ParameterID=12)";

//* match zero or more times
 //+ match once or more

//brackets are groups
 var regex = new Regex(@",ParameterID=(\d+)");
 var match = regex.Match(text);

Assert.That(match.Success, Is.True);
 Assert.That(match.Groups[0].Value, Is.EqualTo(",ParameterID=12"));
 Assert.That(match.Groups[1].Value, Is.EqualTo("12"));

 }

Entity Framework transaction handling via SaveChanges

Possibly save 1 hour of your time: A common question that was asked is how to handle transaction within your application.
Fortunately, if you are using Entity Framework, it already does the heavy lifting work for you.
When you modify your entities in the application, it will track model and property changes behind the scene. And then when you call the following line of code, it will persists all updates to the database and resets change tracking in the object context.
objectContext.SaveChanges();

 

ObjectContext is the object that represents the database connection and its entities.

There may be situations when you may call this more than once in a request to create multiple transactions. I cannot think of a reason right now, but just remember that this acts like a commit transaction as each SaveChanges call will create a new transaction.

However, there are also cases where you may have more than one context to deal with. In that case you may pull data from one database and save them to another database. In this case, you may want to put both contexts’ SaveChanges calls in a TransactionScope to protect this as one atomic transaction.

References:

ObjectContext.SaveChanges Method ()

https://msdn.microsoft.com/en-us/library/bb336792(v=vs.110).aspx

Blogging source code on WordPress

Possibly save 4 hours of your time: If you are programmer who has started a blog, you may want to display your source code.  Then you may try to find plugins to help support better display of your code. However I find most plugins are hard to use and not intuitive.

WordPress does have original support for displaying code with colors based on language type.

public void DisplayWordpressCodeWithColor()
{
}

For supported language, please see list in the following link:

https://en.support.wordpress.com/code/posting-source-code/

Setup SQL Server Integration Services environment and SQL Agent Job to run deployed SSIS package

Possibly save 8 hours of your time: After you create SSIS package and build the project (bin folder should have .ispac file), the next step is to deploy that to a Microsoft SQL Server instance environment and have a scheduled job to run the package. It is not trivial to setup a proper environment. The steps below are targeting Microsoft SQL Server 2012.

Pre-requisites

  • Please make sure that you have SSISDB catalog under Integration Services Catalogs already created. If not, you can right click and choose Create Catalog.

ssisdb

  • A specific user or system user permission will be needed to run the job to execute the package. From here on we call this user the system user.
  • If the package requires read and/or write access to specific database, provide the following database role permissions as needed to the system user:
    • db_datareader
    • db_datawriter
    • SSIS
  • If the package requires execute permission for specific schema on a specific database, provide the following permissions as needed to the system user:
    • Execute
    • Alter (if using bulk insert tasks in SSIS)
  • For msdb system database, the system user needs to be added to Security > Users. Under Owned Schemas, check SQLAgentUserRole.
  • For SSISDB database, the system user needs to be added to Security > Users.
  • The database administrator who will be running the command script to create the job to run the package will need sysadmin role.
  • If you want to receive SSIS job notification on success or failure, you will need to setup an account under Management > Database Mail. Then right click SQL Server Agent, click Alert System and Enable mail profile and choose the account profile created.

Script Implementation

  • Please see at the bottom for the script. The scripts can be run in command prompt directly or embed in a .cmd script file first. Any word surround by %% are variables that you can set.
  • cmd Script will create
    • folder in SSISDB
    • Deploy packages via .ispac
    • run the following SQL script
  • SQL Script will create
    • delete previous existing job
    • create Credential
    • create Proxy
    • create Alerts
    • create SQL Agent Job via .sql
    • add package(s) to the job
    • add job schedule

Post-requisites

  • Setup environment variables if your packages requires specific configuration for this environment – Integration Services Catalog >> SSISDB >> Project >> Environments
  • Update Project Variable Environment
    • Right click project. Select Configure.
    • Under References, add the environment created previously.
    • Under Parameters, edit the same values as in the environment section to use environment variables.
  • Set project level permissions
    • At the SSISDB folder level, add system identity user account with read, execute objects and read objects permissions
    • At the projects level, add read and execute permission for the system identity user.
  • Setup working folder permissions for system user if using working folder (typically if data files are involved in the package, they need to be somewhere)

Script – create folder in SSISDB

set sqlcmdpath=”C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe”

REM Create folder in SSISDB

echo Creating folder in SSISDB if not exists…
echo %sqlcmdpath% -S %serverName% -d ssisdb -Q “if not exists(select 1 from internal.folders where name = ‘%folder%’) exec catalog.create_folder %folder%”
%sqlcmdpath% -S %serverName% -d ssisdb -Q “if not exists(select 1 from internal.folders where name = ‘%folder%’) exec catalog.create_folder %folder%”

echo *******************************************************

Script – deploy packages via .ispac file

set isdeploymentwizard=”C:\Program Files\Microsoft SQL Server\110\DTS\Binn\isdeploymentwizard.exe”

REM Deploy the ETL
echo ETL Deployment is running…
echo %isdeploymentwizard% /S /ST:File /SP:”yourProjectName.ispac” /DS:%serverName% /DP:/SSISDB/%folder%/%project%
%isdeploymentwizard% /S /ST:File /SP:”yourProjectName.ispac” /DS:%serverName% /DP:/SSISDB/%folder%/%project%
echo ETL Deployment Completed
echo *******************************************************

Note: If you get error 0xC0011008 such as “the package failed to load due to error error loading from xml”, ensure you are targeting the right version of database via project properties.

Script – to run the following SQL script in one file with passed in parameters

set sqlcmdpath=”C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe”

REM Create SQL Agent Job
echo Creating SQL Agent Job…
echo %sqlcmdpath% -S %serverName% -i %jobSQL% -v identity=”%id%” password=”%pass%” operator=”%oper%” environmentId=”%envId%”
%sqlcmdpath% -S %serverName% -i %jobSQL% -v identity=”%id%” password=”%pass%” operator=”%oper%” environmentId=”%envId%”
echo SQL Agent Job has been created

Delete previous existing job

USE [msdb]

GO
–Clean up SQL Agent Job

DECLARE @jobId BINARY(16)

SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N’Your Job Name’)

IF (@jobId IS NOT NULL)    EXEC msdb.dbo.sp_delete_job @jobId go

Create Credential

–Create Credential Note: change the credential name

use master

go

IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N’Your Credential Name’) DROP credential [Your Credential Name]

go

create credential [Your Credential Name] with identity = N’$(identity)’, secret = N’$(password)’go

Create Proxy

–Create Proxy Note: change the proxy name

USE msdb

GO

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N’Your Proxy Name’) EXEC dbo.sp_delete_proxy @proxy_name = N’Your Proxy Name’

GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N’Your Proxy Name’,@credential_name=N’Your Credential Name’, @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Your Proxy Name’, @subsystem_id=11GOEXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Your Proxy Name’, @login_name=N’$(identity)’

GO

Create Alerts

–Create Operator

USE [msdb]

GO

IF EXISTS(SELECT 1 FROM msdb.dbo.sysoperators WHERE name = N’Your Operator Name’) EXEC sp_delete_operator @name=N’Your Operator Name’

GO

EXEC msdb.dbo.sp_add_operator @name=N’Your Operator Name’,  @enabled=1,  @weekday_pager_start_time=90000,  @weekday_pager_end_time=180000,  @saturday_pager_start_time=90000,  @saturday_pager_end_time=180000,  @sunday_pager_start_time=90000,  @sunday_pager_end_time=180000,  @pager_days=0,  @email_address=N’$(operator)’,  @category_name=N'[Uncategorized]’

GO

Create SQL Agent Job

 

–Create SQLAgent Job

USE [msdb]

GO
BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Your Job Name’,  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=2,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N’No description available.’,  @category_name=N'[Uncategorized (Local)]’,  @owner_login_name=N’$(identity)’,  @notify_email_operator_name=N’Your Job Name’, @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Add package to the job

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Your Step Name’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’SSIS’,
@command=N’/ISSERVER “\”\SSISDB\Your Folder Name\Your Project Name\Your Package Name.dtsx\”” /SERVER “\”$(serverName)\”” /ENVREFERENCE $(environmentId) /Par “\”$ServerOption::LOGGING_LEVEL(Int16)\””;1 /Par “\”$ServerOption::SYNCHRONIZED(Boolean)\””;True /CALLERINFO SQLAGENT /REPORTING E’,
@database_name=N’master’,
@flags=0,
@proxy_name=N’Your Proxy Name’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Note: You can repeat this to add more packages as steps in your job.

Add Job Schedule
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Your Schedule Name’,  @enabled=1,  @freq_type=4,  @freq_interval=1,  @freq_subday_type=8,  @freq_subday_interval=12,  @freq_relative_interval=0,  @freq_recurrence_factor=0,  @active_start_date=20160616,  @active_end_date=99991231,  @active_start_time=0,  @active_end_time=235959,  @schedule_uid=N’d5de52a3-a8e8-49ca-92d0-fbbc3ee10098′

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’$(serverName)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:
GO