How to block users updating records I have selected/read

We know that, with the default behavior, records are getting exclusively locked when we perform modifications against data, disallowing other users to access them. Until we complete the transaction, exclusive locks will be held by SQL Server, making sure that no one not only modify but execute SELECT statements too. However default settings does not hold locks obtained for SELECT statements blocking other users reading data you have read. But what if you need it?

If you want to make sure that no one modify records which you have read for your operation, you need to change the default Isolation Level and select data within a Transaction. The Isolation Level determines the behavior of concurrent users who read or write. Default Isolation Level is READ COMMITTED which locks records using shared locks for reading but does not hold them until the transaction is completed. In order to keep the obtained shared locks until the transaction is completed, Isolation Level has to be brought up to REPEATABLE READ Isolation Level.

REPEATABLE READ keeps acquired shared locks for read data till the end of transaction. This makes sure that no one can modify records we have read and avoid a concurrency issue called Inconsistency Analysis.

Here is the way of doing it.

Open a new connection and execute the following;

-- First connection
-- setting the isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- start a transaction
BEGIN TRAN

 -- executing a select query
 SELECT * 
 FROM Sales.SalesOrderHeader
 WHERE YEAR(OrderDate) = 2011;

Then try to modify a record related to above query with another connection.

-- Second connection

-- Try to retirve data
-- This works
SELECT * 
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;

-- Try to update a record
-- This will NOT work
UPDATE Sales.SalesOrderHeader
 SET ShipDate = DATEADD(dd, 1, shipdate)
WHERE SalesOrderID = 43660;

As you see, second connection will not be able to modify records until the transaction related to first connection is done.

SQL Server Brain Basher of the Week #017 - Alternative names for objects

Microsoft SQL Server does not force you with hard rules for naming objects such as tables but some recommendations. Your tables, your procedures, you functions can be named as you want and we generally use Pascal notation for naming objects (for more info on notations: Camel notation, Pascal Notation, Hungarian Notation). Sometime this leads to have very lengthy names and not user-friendly in some context. The Brain Basher of the week is based on it;

Can I have alternative names for my objects?

This can be achieved with Synonym Objects. It allows us to add another name on objects that are exist in either local or remote server. For an example, assume that you have a table called dbo.SalesOrderHeaderForAsiaRegion and you want to have a shorter name for this, a synonym can be created on this as AsiaSales. Another example is, assume that you need to access a table called Customer exist in your database and link with another table called Customer in a different database in another instance, a synonym can be created for second Customer table exist in second instance on first instance.

Here is a sample code for this;

USE AdventureWorks2014;
GO

-- Check existing table
SELECT * FROM Sales.SalesOrderHeader;
GO

-- Create synonym on it
CREATE SYNONYM SalesHeader
FOR Sales.SalesOrderHeader;
GO

-- Checking synonym created
SELECT * FROM SalesHeader;

For more info on Synonym:

https://msdn.microsoft.com/en-us/library/ms177544.aspx

https://msdn.microsoft.com/en-us/library/ms187552.aspx

Can I create a permanent table inside the tempdb?

Microsoft SQL Server comes mainly with five system databases: master, model, msdb, tempdb and resources. In addition to that some more databases are created with different implementations such as replications. During a discussion, a question related to tempdb came up: What do not we maintain a permanent table inside the tempdb if we need to maintain some data not related to business-related database?

First thing we need to understand is, how these system tempdb works. When SQL Server services is restarted, tempdb gets recreated using the template which is model. Since it is getting recreated, we lose all our works we have done with tempdb before the restart. Considering this fact, should we create a table inside tempdb, other than temporary tables that are prefixed with either # or ##?

Here is an example, this code creates a table inside the tempdb and then it shuts down the service.

-- Connecting with tempdb
USE tempdb;
GO

-- Creating a table (permanent)
CREATE TABLE dbo.TestTable
(Id int);
GO

-- Query and see, this works
SELECT * FROM dbo.TestTable;

-- Sending shutdown singnal
SHUTDOWN WITH NOWAIT;

Once manually restarted, we can query the same table and see, result is shown below.

-- Connecting with tempdb
USE tempdb;
GO

-- Query and see, this works
SELECT * FROM dbo.TestTable;

Do you still want to create permanent tables inside the tempdb?

Looking for a Hadoop Cluster for testing? Let's configure Hortonworks HDP Sanbox

The latest buzzword in IT, or more particularly in data analytic is Big Data. It does not come alone, it always comes with Hadoop which offers distributed storage and processing. Everyone loves to do some experiments with new technologies, or popular technologies, hence everyone loves to do the same with Big Data and Hadoop. But setting it up is not an easy task and cloud HDP subscriptions offered by various vendors are not so flexible in terms of trial-period given. However, if you really want, you can use Sanboxes offered by some vendors for testing Hadoop implementations.

One of the HDP cluster I used for testing is HDInsight. Since the trail is limited, searched for alternatives and found the Sandbox offered by Hortonworks. This sandbox is configured as a self-contained virtual machine and it can be simply used without connecting to cloud. It does not come with multiple nodes, means that all Name Node, Job Tracker, Data Node, etc. are in same virtual machine. You will not be able to get the exact picture of distribution but you can do everything you need to do with Hadoop with this.

Here are the steps for configuring HDP sandbox.

Visit http://hortonworks.com/. Click on Hortonworks Sandbox under Get Started menu that is the top menu.

This takes you to a Download and Install page. At this moment, HDP 2.2.4 is the stable and reliable version, but it offers HDP 2.3 - Preview too. HDP 2.2.4 comes in three flavors; VirtualBox, VMWare and HyperV. Download the best suited for you.

Make sure you download the Install Guides too. Configuration and usage is same for all three types, for this post, I will assume that you download VirtualBox virtual machine which is Sandbox_HDP_2.2.4.2_VirtualBox.ova. Once this is downloaded, you need to import it, it is fairly straight forward, all instructions are given with the Installation Guide, follow it for importing into your VirtualBox environment (or VMWare or HyperV).

After importing, all you have to do is, click on Start button. If your machine is ready for running virtual machines, it should start without any issue, however it is very common to see following error message with most of VMs;

Failed to open a session for the virtual machine Hortonworks Sandbox with HDP 2.2.4.2.

VT-x is not available. (VERR_VMX_NO_VMX).

Result Code: E_FAIL (0x80004005)
Component: Console
Interface: IConsole {8ab7c520-2442-4b66-8d74-4ff1e195d2b6}

There can be two reasons for that. Once is, not enabling Visualization in BIOS. Second can be, incompatibility with other virtual environments. If Virtualization is not enabled in your machine, boot the machine with BIOS and enable it. If you still get the same error, and if you are running with Windows 8, make sure you disable HyperV. This thread discusses the same, follow it: http://h30434.www3.hp.com/t5/Desktop-Hardware/How-to-Enable-Intel-Virtualization-Technology-vt-x-on-HP/td-p/3198063.

And this video shows how to disable HyperV for addressing the same error: https://www.youtube.com/watch?v=Y56boAsdptw.

Once everything is done, you should be able to start it and you should see a scree like below.

As it says, there are two ways of accessing this; you can press ALlt+F5 for logging, user id is root and password is hadoop.

Once login you can continue with your commands for working with Hadoop.

In addition to that, GUI is given too. As the first screen explains, open a browser and go for http://127.0.0.1:8888/.

Then click on http://127.0.0.1:8000 for opening hue (Hadoop User Experience). It allows you to do your Hadoop work easily.

Generate comma-separated value as a part of GROUP BY

This is not a new thing but it is a common requirement with many implementations, so making a post on it.

Assume that you need to get some values in the group as a comma-separated value instead of running them in an aggregate function. There are  many ways of handling this, some are complex and some are straight-forward. If it is not a complex GROUP BY and table is relatively small, following is the best I have used.

Let's say, we have an EmployeeTelephoneNumbers table like below;

EmployeeId	PhoneNumber
1	0777222333
2	0777343565
2	072345345
2	0112456456
3	071578976
3	01154672121

And need to produce a result like this;

EmployeeId	PhoneNumber
1	0777222333
2	0777343565, 072345345, 0112456456
3	071578976, 01154672121

As usual, we have to use GROUP BY and perform a different logic for forming the second column without using an aggregate function. For that, first we need to use is FOR XML clause, which allows us to convert the tabular resultset into an XML. It has different modes such as RAW, AUTO, EXPLICIT and PATH and PATH5 has to be used with this.

For more info on PATH: https://msdn.microsoft.com/en-us/library/ms189885.aspx

Next is STUFF function. This function inserts a string into another string deleting the given length.

For more info on STUFF function: https://msdn.microsoft.com/en-us/library/ms188043.aspx

What we have to do is, use FOR XML PATH for generating an XML that contains all values for grouping and using it as a string. Then STUFF will be used for removing the extra characters.

Here is a sample code.

USE tempdb;
GO

-- creating the table
CREATE TABLE dbo.EmployeeTelephoneNumbers
(
 EmployeeId int not null
 , PhoneNumber varchar(200) not null
);
GO

-- Inserting sample records
INSERT INTO dbo.EmployeeTelephoneNumbers
 VALUES (1, '0777222333'), (2, '0777343565'), (2, '072345345'), (2, '0112456456'), (3, '071578976'), (3, '01154672121');
GO

-- Generate the result
SELECT 
 EmployeeId
 , STUFF((SELECT ', ' + PhoneNumber
   FROM dbo.EmployeeTelephoneNumbers
   WHERE EmployeeId = t.EmployeeId
   FOR XML PATH('')), 1, 2, '') PhoneNumbers
 
FROM dbo.EmployeeTelephoneNumbers t
GROUP BY EmployeeId;

Different query patterns you see with HiveQL when comparing with TSQL

In recent past......... a casual conversation turned to a technical conversation......

Friend: It is a surprise to see an MVP studying hard on Open Source, anyway good see you in the club :).

Me: This does not mean that I will be completely moving to Open Source, but I like to lean some additional things and get involved with a good implementation.

Friend: Okay, you have been studying HIVE for last few weeks, tell me three types of queries you have not seen with SQL Server?

Me: Yes, have seen few, let me remind three, you can interchange blocks in SELECT like FROM clause before SELECT, one CREATE TABLE statement for creating table and loading data, and of course duplicating table with CREATE TABLE LIKE :).

Conversation continued with more interesting topics related to two different platforms, will make more posts on my-usage of open source components such as Hadoop, Pig and HIVE for solutions I work on, but thought to make a post on this three items;

There are no big differences between TSQL (or standard SQL) and HiveQL queries, but some are noticeable if you are an experienced TSQL developer. If you have used MySQL, you will not see much as HiveQL offers similar patterns.

Let me elaborate three things I mentioned with my reply, that will surely encourage you to start studying on HIVE if you have not started using it.

1. 

SELECT statement of SQL Server always start with SELECT clause and then FROM;

SELECT Col1, Col2
FROM Table1
WHERE Col3 = 1;

HiveQL allows the same but it can be written like this too;

FROM Table1
SELECT Col1, Col2
WHERE Col3 = 1;

2.

If we need to create a table with SQL Server and load data, there are two ways with pros and cons.

CREATE TABLE NewTable
(
 Col1 int
 , Col2 varchar
);

INSERT INTO Table2
SELECT Col1, COl2
FROM OldTable;

-- or

SELECT Col1, Col2
INTO NewTable
FROM OldTable;

But HIVE offers an easy way of doing it.

CREATE TABLE NewTable
STORED AS RCFile
AS SELECT Col1, Col2 FROM OldTable;

3.

When we need to duplicate a table structure, this is what we do with SQL Server;

SELECT Col1, Col2
INTO NewTable
FROM OldTable
WHERE 1=0;

and HiveQL facilitates this;

CREATE TABLE NewTable LIKE OldTable;

If I am not mistaken, some of these are supported with APS. Not only that HDInsight supports HIVE in all the ways, let's try to understand the usage of HIVE and combining it with SQL Server implementations with future posts.

ETL Data Flow Architectures

ETLing is the way of getting data from one or more sources into your reporting environment or to a repository that holds enterprise-wide data for all reporting and analytical requirements. It is a common and well-known term in data warehousing because a data warehouse solution is impossible to exist without an ETL implementation.

ETL stands for extract, transform and loading, describing the data flow. Generally we extract data from sources, do validations on extracted data, and load the destination, most of the time, destination is a data warehouse. If the ETL solution is very small and less complex, data flow is always from sources to destination without any middle components. However if it is not, the architecture related to data flow is different and it includes additional components. The architecture of the data flow can be determined by considering following;

• The number of data sources to be accessed.
• The amount of data to be accessed and transferred in a single execution.
• The type of loading, whether it is complete refresh or incremental loading.
• Complexity of validations to be applied (transformations).
• Data generation frequency at sources.
• The extraction windows.
• The accessibility of data sources.

Considering above factors, there are three possible ETL data flow architectures;

Single-stage ETL architecture

The single-stage ETL architecture is suitable for small and less complex data flows. With this architecture, transformation is done at the extraction and in-flight. 

Combining First Name, Middle Name and Last Name into Full Name at the extraction and finding duplicates of Customer Entity are some examples for transformations performed with this architecture.

Two-stage ETL architecture

This data flow architecture maintains an additional environment called Staging. It can be a database (in most cases), set of text files or even Excel files. The staging is used as a temporary container for extracted data and it is implemented based on few factors such as different data acquisition windows, identifying modifications done with previously loaded data and auditing purposes. For more info o staging refer my post: Do we need a staging database for warehousing projects?.

Transformation on extracted data can be done in several stages. It can be performed at the extraction from sources and staging, and in-flight between sources and staging, and between staging and destination.

Three-stage ETL architecture

This extends two-stage architecture by introducing another layer called Landing Zone. 

This helps to reduce the workload (including transformation at extraction) with the sources. We can extract data as available in the source without adding any complexities for extraction queries, minimizing time it takes for completing the extraction. Then possible transformation can be applied to Landing Zone or later stages. Sometime, sources pushes data (via reports) without letting us to connect with sources. In a scenario like that, Landing Zone is the place where all source files are placed.

SQL Server Brain Basher of the Week #016 - Views

During last 3-4 months, I have been continuously asking this question from many to understand how the fundamentals are known to everyone. A strong knowledge on fundamentals and concepts is the key for building efficient applications. Not only that, it takes us in the right path when solving problems. This question is based on Views, general views. Unfortunately, as per my rough calculation, only 30-40% were able to give the correct answer confidently. Here is the Brain Basher of the week;

Can we improve the performance of a SELECT query by creating a general view?

The answer I always expect is No but many say Yes. Of course, you can argue on this saying that it improves the performance in certain situations, but let me take that addressed later in this post.

Let's try to understand What is a View. Remember, View is just a definition, it is not a container that can hold data. We create views using either a single table or combining multiple tables. When they are called for data, the query written in the view is executed, just like we execute an ad-hoc SELECT statement. Therefore, theoretically, there cannot be any sort of improvements in terms of data retrieval with a View.

When I say No as the answer, the obvious question comes up: Why we need Views then?

Views help us in many ways. Main thing is security. It allows us to open only required columns from a table for outsiders without opening the entire table. For an example, a table that contains sensitive data such as Salary can be hidden by creating a view on top of the table with required columns and opening (or granting permission) the View to users instead of table. In addition to that, it reduces the complexity of a query with multiple joins. A SELECT query required by users (or developers) that needs many joins and filters can be made as a simple SELECT by creating a view with same query and let users to use the view instead of the query.

Okay, cannot we really improve the performance of it? Not with general Views but Index Views. A general View can be converted as an Index View by introducing a clustered index on it. Index View holds data based on the query written and when it is called, engine does not need to retrieve data from base tables and perform any calculations added to the query. However, the question discussed is not about Index View.

What is "High Water Mark" in ETLing?

When extracting data from one database to another in continuous fashion, two common techniques are used; delete the destination and reload with extraction or load the destination as an incremental load. First technique is straight forward, but for the second, changes have to be identified, it is commonly done with a datatime column in the source that holds either created date or last modified date.

What is the link between this and "High Water Mark"? The High Water Mark is used for indicating the highest water level for a tide or flood. Same theory can be applied for our scenario, considering datetime column in the source as High Water Mark.

User needs sysadmin permission to the instance but he should be denied permission on some databases

If someone wants to manage the SQL Server instance completely and perform any activity in the server, she or he should be a member of sysadmin server-level role because it bypasses all security checks. But what if you need to someone be a member of sysadmin server-level role and restrict him to some databases? Is it possible?

Although it is not a common scenario, a requirement like this can be popped up and we may struggle to find a solution. How can we do it? If User1 is a member of sysadmin server-level role, we have no way of restricting User1 accessing a database. In order to handle the scenario, User1 needs to be given all permissions offered with sysadmin role manually without adding him to sysadmin role and limit his permission only for required databases. Yes, it is possible to address individual permission with individual statements up to some extent but it is time consuming and makes the whole process complex. It becomes more and more complex and increases the maintenance cost if more users have to be managed in similar way. The best solution is, grant Control Server permission to User1.

The Control Permission is similar to Sysadmin fixed server role but not identical. It offers all the permissions offered with Sysadmin role but there are some limitations with some objects such as DBCC commands and some system stored procedures. However, a requirement like above can possibly managed with it.

For more info on this: https://msdn.microsoft.com/en-us/library/ms188659.aspx

Let's test this. Assume that Jane and Joe are database administrators and they need full permissions on the instance. However, Joe should not be able to access AdventureWorksDW2014 database. Let's try to handles this scenario with Control Permission though it does not offer a 100% working solution.

Below code creates logins for Jane and Joe; Jane is in Sysadmin fixed server role and Joe has Control Server permission.

USE master;
GO

-- Create Jane login
CREATE LOGIN Jane
WITH PASSWORD = '123'
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
GO

-- Add Jane to Sysadmin role
ALTER SERVER ROLE sysadmin ADD MEMBER Jane;
GO

-- Create Joe Login
CREATE LOGIN Joe
WITH PASSWORD = '123'
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
GO

-- Grant Control Server permission to Joe
GRANT CONTROL SERVER TO Joe;
GO

Let's check permission assigned for both. Below query confirms that both have same permissions.

EXECUTE AS LOGIN = 'Jane';
SELECT * FROM sys.fn_my_permissions(null, null);
REVERT;
GO

EXECUTE AS LOGIN = 'Joe';
SELECT * FROM sys.fn_my_permissions(null, null);
REVERT;
GO

Now we need to restrict Joe to AdventureWorksDW2014 database. For testing purposes, let's deny permission to both Jane and Joe.

-- deny permission to AdventureWorksDW2014
USE AdventureWorksDW2014;
GO

-- We do not need User account in the database
-- because both Jane and Joe have admin rights
-- But let's create them
CREATE USER Jane FOR LOGIN Jane;
CREATE USER Joe FOR LOGIN Joe;
GO

-- This gives deny permission to Jane
ALTER ROLE db_denydatareader ADD MEMBER jane
ALTER ROLE db_denydatawriter ADD MEMBER Jane
GO

-- This gives deny permission to Joe
ALTER ROLE db_denydatareader ADD MEMBER Joe
ALTER ROLE db_denydatawriter ADD MEMBER Joe
GO

Let's see how this works now. Below code shows that Jane can still access the database (tables particularly) but Joe cannot access.

-- Jane tryint to access AdventureWorksDW2014
-- Jane will be able to access because she is in sysadmin
-- role and system bypasses security checks.
EXECUTE AS LOGIN = 'Jane';
SELECT * FROM AdventureWorksDW2014.dbo.DimProduct;
REVERT;
GO

-- Joe trying to access AdventureWorksDW2014
-- Joe will NOT be able to access because he is not in syadmin 
-- role and system will check for permissions
EXECUTE AS LOGIN = 'Joe';
SELECT * FROM AdventureWorksDW2014.dbo.DimProduct;
REVERT;
GO

Have we solve the problem with this solution completely? Since we just restrict table reading and writing, we have not addressed it fully but I believe that it is managed up to some extent now.

Can Joe change his User Account in the AdventureWorksDW2014 with high permissions?

Can Joe upgrade himself as Sysadmin?

As you see, many things are not possible with Joe's account. However this needs to be thoroughly tested as I have not used this in production.

I did not use my HDInsight cluster but my credit is over

Have you noticed this? Or have you already experienced this? Are you going to complain on it? Then wait.............

Microsoft Azure HDInsight billing is different with other billing methods (Once I saw that it is same for all Azure offers, but I am not sure). Billing starts once the cluster is created with the storage and you will be charged continuously even though you do not use it. I know that this annoys you but that is the fact unfortunately.

For clarifying this, I checked with my account, this is what I see;

When click on View more details;

This clears shows that I am being charged for the cluster I have created even though it is not being used.

How do we stop charging? There is no way of stopping, only possible way is, once done with the cluster, drop it and if you want it again, re-create it (This annoys more, I know that).

I prefer to have some kind of facility that allows us to enable/disable but I cannot see something like that and did not see any upcoming similar facility with searches made.

If you want to know about pricing on this, have a look on this page: http://azure.microsoft.com/en-gb/pricing/details/hdinsight/

How to navigate HDInsight cluster easily: CloudXplorer

Once HDInsight cluster is created for Big Data implementations, navigating the cluster (or Azure Blob Storage) via logical folders and accessing files inside, writing and copying files in and out is something we really require. For that, rather than navigating via Azure Management Portal, an application like Windows File Explorer is very beneficial. There are few tools available for accessing the cluster, some can be installed as an add-on to Visual Studio too. However I found that CloudXplorer is a good tool for accessing it, hence thought to write a post on how to use it.

Let me create a HDInsight cluster. First of all, a Storage is required, below screen shows creating a storage called azure01dineshhdistorage.

And then creating a Cluster  called azure01dineshhdinsight.

Once created, need to download CloudXplorer from http://download.cnet.com/CloudXplorer/3000-18500_4-75940217.html and install it. After installation, tool can be opened and add the storage account.

In order to connect with Windows Azure Account, we need to get the Name of our storage used for hdinsight configured and the Primary Access Key of it. Follow below steps for that;

1. Open Windows Azure Management Portal.
2. Click on Storage on left panel for opening all storage added.
3. Select the one configured for hdinsight and click on Manage Access Keys button on bottom panel.
4. Get Storage Account Name and Primary Access Key copied.

Next steps are;

1. Open CloudXplorer installed.
2. Click on Accounts icon in the ribbon.
3. Click on New button and select Windows Azure Account.
4. Paste copied Storage Account Name and Primary Access Key to Name and Secret Key text boxes.

5. Click OK to save setting. Select the account added and click OK again to access the cluster.

Now we can see, copy, add, create files just like we do with Windows File Explorer. If need to see all items in the cluster, click on Report icon in the ribbon and list as you want.

SQL Server Brain Basher of the Week #015 - SSIS Failing package

Just like last Brain Basher, this week one also based on Integration Services. Let's try to understand a very common misconception on package failures.

If one of the tasks is failed during the execution, does it stop the execution of entire package?

Assume that you have configured two Script Tasks as below and the first one is configured to ForceExecutionResult to Failure.

When this package is executed, Script Task 1 gets failed. Does the execution continue with Script Task 2 or not? Let's execute and see.

As you see, the second task is not getting executed. If a question "Why" is asked, a common answer is "failure of previous task stops executing the package".

Yes, execution does not continue but it is not exact reason. The reason for this (and for many cases) is Precedence Constraint set between these two. Since it is set as Success, execution does not continue with the second task, hence it stops executing the package. However, if the Precedence Constraint is set as Completion, it executes the second task too.

Execution of the entire package at a failure of a task will be stopped if FailPackageOnFailure property is set as true for the task. It forces to stop the execution of entire package regardless of the Precedence Constraint set.

SQL Server Brain Basher of the Week #014 - SSIS Variables and Parameters

We have been using variables inside Integration Services packages for maintaining temporary values for various computations and passing external values to the package when executing. With SQL Server 2012, Parameters were introduced which is similar to variables and many still get confused with these two; what to be used and when and why they should be used, hence this week Brain Basher is based on Integration Services Variable and Parameters.

What is the difference between Variables and Parameters with Integration Services?

Variables are used to hold values temporarily for calculations and configurations. A common usage of variables is, maintaining one for Connection String of one of Connection Managers. Before SQL Server 2012, we were able to pass new values for these variables at the execution time using Configuration Files, however with SQL Server 2012 and later, it is not possible with Project-Level-Deployment.

Parameters are similar to variables. However,  they can be defined at Package Level, making them as Private to the package. They can also be defined at Project Level, making them as Global to the project. Once defined, parameters can be used for calculations and holding values for configurations. And values them can be passed at the execution time with Project Deployment Model.

The main difference between these two is, with modern deployment, variable is for internal calculations and not for getting external values at the execution time. Parameter can be used for assigning values to variables and value of it can be passed at the execution time.

SSRS: The parameter is missing a value

Are you experiencing the following error with one of your Reporting Services parameters? Specifically with a hidden parameter?

To overcome this issue, make sure you have a default value for the parameter. Here are the steps;

1. General Parameter Properties. Note that visibility is hidden.

2. Then set as source for the parameter as you want.

3. And set the default value as you want.

Once this is done, it will work without any issue.

SSRS: How to format datetime as Oracle YYYYMMDDHH24

If a SSRS report has to be designed for an Oracle database and datetime needs to be displayed with date and hour, there are many different ways for handling it. Oracle supports many different formatting on datetime and sometime values in the report need to be displayed as per Oracle format such as YYYYMMDDHH24. That was one of the specific requirements I had, so thought to share the way I did it assuming that it will be useful for you too.

With Oracle, following code returns 2015052813 if the date is 2015-05-28 and time is 1:30pm.

SELECT to_char(sysdate, "YYYYMMDDHH24") FROM dual;

If the same format has to be displayed with SSRS, here is the way to write the expression.

SQLSaturday #417 - Speakers and Sessions

Speaker: Pinal Dave

Pinal Dave is a Technology Evangelist for Pluralsight. He has written over 3,300 related articles on his blog at http://blog.sqlauthority.com. He has also authored 17 video courses and authored 11 books. He specializes in SQL Server performance tuning and has over 11 years of hands-on experience and holds a Master's of Science degree and a number of certifications, including MCTS, MCDBA, and MCAD (.NET). He has presented at many technology conferences and is consistently a top-rated speaker. 

Session I: Performance in 60 Seconds - SQL Tricks Everybody MUST Know

Data and Database are very important aspects of application development for businesses. Developers often come across situations where they face a slow server response, even though their hardware specifications are above par. This session is for all the Developers who want their server to perform at blazing fast speed, but want to invest very little time to make it happen. We will go over various database tricks which require absolutely no time to master and require practically no SQL coding at all. After attending this session, Developers will only need 60 seconds to improve performance of their database server in their implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. 

Session II: Indexes – The Unsung Hero

Slow Running Queries  are the most common problem that developers face while working with SQL Server. While it is easy to blame the SQL Server for unsatisfactory performance, however the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session.

Speaker: Nilabja Ball

Nilabja is a Premier Field Engineer for Microsoft Global Business Support (GBS), India. He has more than 8 years of SQL Server and BI experience. He is engaged with the customer either for proactive activities including health check, assessment and support or for reactive tasks to troubleshoot and fix complex and critical issues. He has an engineering degree from Visvesvaraya technological university and keen to learn new technologies.

Session I: SQL Server 2014 In-Memory Overview

Overview of the in-memory technologies which are included in SQL Server 2014. We will cover In-Memory OLTP (AKA "Hekaton"), In-Memory Data Warehousing (AKA "Apollo") as well as SSD Buffer Pool Extensions. This session will give understanding of the broad in-memory strategy as well as a basic understanding of the technologies involved.

Session II: NoSQL on Azure: An Introduction to DocumentDB

NoSQL data stores have gone mainstream. In this session, we will survey the expanding NoSQL landscape on Azure, providing a big-picture view of these technologies. Also spend a bit more time on DocumentDB, Microsoft’s document database-as-a-service, and Azure Search. This session is aimed at anybody who’s new to NoSQL or is curious about the NoSQL options that Azure provides today.

Speaker: Sammani Palansuriya 

Sammani Palansuriya is a Visual C#.net MVP in Sri Lanka with more than 5 years' experience in various aspects of .net technologies. Currently she is stationed as a Senior Software Engineer at Exilesoft and working on projects based on asp.net and azure technologies.

Session I: My Data in the Cloud: Microsoft Azure SQL Database

Microsoft Azure SQL Database (formerly SQL Azure) is another Microsoft Azure service that offers relational database-as-a-service with proven performance, scalability, data security and near-zero administration.

This discussion will give you an understanding about Azure SQL Database; what this service is and what it offers. It will also help you understand what sort of requirements will benefit from Azure SQL Database, and what factors you need to know in order to choose this service, along with pros and cons of choosing Azure SQL Database

Speaker: Prabhakaran Bhaskaran 

Prabhakaran is SQL Server consultant at Pythian, a company based in Ottawa, Canada, specializing in Remote database services. He is former SQL Server Support Engineer at Microsoft. He always loves to learn and share his knowledge.

Session I: Inside Transactional Replication
You can probably do a good job in configuring and maintaining transactional replication in your environment. You can get these things done with reasonable quality without knowing how SQL Server does things under the covers. However if you want to take your skills to the next level and troubleshoot complex replication issues, it's crucial to know how transactional replication works internally.

This session will answers questions like; How does the log reader know where to start the scan? How does it add the commands to the Distribution database? How many threads are involved in a replication process? How does distribution agent look for pending transactions to distribute to its subscribers? Knowing all these will help you to manage and troubleshoot the replication very effectively. Come lets learn together!

Speaker: Thurupathan Vijayakumar 

A versatile technical speaker with 5 years of industry experience in SQL Server technologies, software development and cloud computing.

Session I: Setting your first foot in the world of Business Intelligence

Have you ever wondered why we need a data warehouse when we have the good old stringent relational database scheme. Do you want to get to know the concepts of data warehousing and business intelligence? Then this is the session you have been looking for: A tour from OLTP to OLAP with Microsoft technologies. Gear up for the excitement!                                                                                                                                                                                             

                                                                                                                    .

.

Speaker: Indika Ariyasinghe 

Indika Ariyasinghe is an Associate Architect - Technology in Virtusa (Pvt) Ltd. He holds a Software Engineering Degree from University of Peradeniya, Faculty of Engineering and possesses 11 years total experience in software development including business intelligence.

Session I: Enterprise MS BI for everyone, The Journey So Far
Virtusa is a rapidly evolving company with diverse business intelligence needs spanning all level of the organization; from CxOs to Engineers (numbering around 8000 employees); from mobile devices to public area dashboards. In this session, I will share with you the challenges faced and how we have over come them so far, and plans for beyond future.                                                                                                                                                                                                                                     .

.                                                                                                                                                                              

                                            .

Speaker: Gogula Aryalingam 

Gogula is a database and business intelligence professional with 9+ years on the Microsoft data platform. He is an MCSE, MCT, former MVP and an MSc in IT. His passion is in building BI and data-based solutions, writing about things he works with and speaking about them. He loves teaching and working with the technical community. He has spoken at local and foreign events, along with regular stints at the local SQL Server user group. He was also an SME for Microsoft Certifications.

Session I: Telling Stories with Data

Data is all around us; it's in bits and pieces; it's in shapeless chunks. These data can tell us stories. Organizations have loads of these data lying around, but seldom make use of it. Modern data analytics methods however, give us a plethora of methods to access this data, mash them up and tell stories using them. Stories, that can help make effective decisions.

This session looks at how you could collect data from the various sources in your organization, combine them, process them and tell stories using the wealth of tools given to us by Microsoft. Words you would hear are self-service data analytics, big data and machine learning.

Speaker: Dinesh Priyankara

Dinesh Priyankara (MSc IT) is a SQL Server MVP in Sri Lanka with 14 years’ experience in various aspects of database technologies including business intelligence. He has done many local and foreign business intelligence implementations and has worked as subject matter expert on various database and business intelligence related projects. Currently he is stationed as a Senior Architect - Specialist at Virtusa (Pvt) Ltd. He teaches, consults and runs training programs for various institutes.

Session I: Big Data Analytics: The Microsoft Way
Have you already incorporated  “Big Data” into your analytical solution, but still feel you are going nowhere with it? Or are you planning to bring “Big Data” into your analytical solution, but not sure which way to go? Do you wonder about how to get it done, and what tools and methods that you need to use? Let us talk about it; let us understand the “Big Data” requirement; how to and the best way of bringing it into analytical stack using the Microsoft way, of course.

For more info: Visit http://www.sqlsaturday.com/417/EventHome.aspx

Guest Post: How much data will be loss when DBCC CHECKDB reports Consistency Errors?

This post is written by: Priyanka Chouhan

Every company or an individual, who use database systems, always wants to ensure the database integrity of the stored database. Therefore, you should always run the database consistency against your database, to know the accuracy and purity. In order to regularize the database maintenance routine, run DBCC CHECKDB daily, weekly, monthly. You might have thinking that how do you know what data potentially gets lost? Your next question could be about the performance enhancements after running the DBCC CHECKDB and also about the need of running DBCC CHECKDB without any performance implications.

At times, database system specifically mentions that using this option leaves the database in an unsupported configuration, but still if you proceed then there are chances of database inconsistency. This write-up lets you know the process of identifying the possible potential data loss when DBCC CHECKDB reports corruption in your SQL Server database.

Carefully read the output from DBCC CHECKDB: After running the DBCC CHECKDB, it will show the information in bulk to get you started. Read the output thoroughly, and remember the below mentioned information.

Identify the affected objects: You need to use the output of DBCC CHECKDB to identify the affected object. Usually DBCC CHECKDB does not recommend the REPAIR_ALLOW_DATA_LOSS option for every corruption case. When you identify errors it will give you an Object in ID value of the affected object, which can be used to find out the affected object and then you can get the infected table.

Identify the affected page: After knowing the information about the affected object, you can decide whether the infected data is critical or not. If you talk about an organization, then all the data can be considered as critical and need a treatment. For example, let’s assume that the identified data is critical and you need to minimize the data loss. By identifying the unprocessed page number from the output of DBCC CHECKDB, you can determine the corrupted page. Additionally, it will also explain you about the reference pages related to the corrupt page. Ultimately, it will provide you the complete information that is required to know about the corruption in the DBCC CHECKDB output.

Resolve the corruption: After successfully find out the corrupted page along with its index format. You do not need to restore from backup or run REPAIR_ALLOW_DATA_LOSS to repair; instead you can just disable and rebuild the index. The time taken is dependent upon the size of the index and the impact it has on the system. It's up to you to decide that either you will do it during or after working hours.

Since you have the latest backup and you are planning to restore your database from it, then drop this idea. It would be useless to restore data from the backup, as this restoring process will consume lots of time, in case you own a big database. Therefore, it is better to repair only known corrupted pages. Disabling and rebuilding the index is always a better way to make your complete database offline to resolve the corrupted non-clustered index. However, whenever the DBCC CHECKDB reported a bunch of errors, it is not always compulsory to take your database offline to fix it.

SQL Server Brain Basher of the Week #013 - Global Temporary Tables

Though we create temporary tables frequently for manipulating temporary data, global temporary tables are infrequently created. As you know, local temporary table is limited to the current session, and global temporary table is for all available sessions. Local temporary table will be automatically dropped when your session is disconnected and global temporary table will be remained until last connection that has referenced the table is disconnected. Now the question for the week is;

Global Temporary Table is global for the database or for the instance?

When this question is asked during my classes/workshops, most common answer for this is, it is global to the database. However, it is not the correct answer, It is global to the entire instance. Once a global temporary table is created, it can be accessed, modified and deleted by any user in any database hosted in the same instance.

Here is a sample code for that;

-- Connect with AdventureWorks database
USE AdventureWorks2014;
GO

-- Create global temporary table
CREATE TABLE ##TestTable
(
 Id int PRIMARY KEY
 , Name varchar(200)
);
GO

-- Query the table in the same session
SELECT * FROM ##TestTable;

Here is the result of SELECT.

Here is the result when accessing the table in a different session.

And here we are accessing the table from a different database. It works without any issue.