Archive for the ‘SQL Server’ Category

Migrating On-Premises SQL Server Database to Azure SQL Database

Today I was trying to migrate an On-Premises SQL Server 2016 Database to Azure SQL Database using .bacpac file option i.e. Export Data-Tier Application, but bad luck! I got this error every time I tried, and to my surprise, all credentials and configurations were correct.

The error that I got was: “Deployment fails with ‘Unable to connect to master or target server ‘xyz’. You must have a user with the same password in master or target server ‘xyzdb“. I really couldn’t get it why it’s happening as everything was correct. Whether I try to do it using Azure Portal or SSMS, the result remain same – Error.

Somehow I figured it out that I have been using SQL Server Management Studio ver. 13.0 and I need to have the latest SSMS with me. I used SQL Server 2016 and SSMS version for the same. Hence, I downloaded the SQL Server Management Studio ver. 17.4, which is for SQL Server 2017. And guess what??? It did work without any error at the very first try. 🙂

So, the above error message was completely misleading. Just have the latest SSMS with you, doesn’t matter if you are using SQL Server 2014/2016. And, you will be able to migrate SQL Server DB to Azure SQL DB.

Hope this helps you out! 🙂



What is UniqueIdentifier in SQL Server

In simple words, the UniqueIdentifier is one of the data types that the SQL Server offers to the developers.

The question is What is does and Where we should use it? Yes, that’s very interesting.

The UniqueIdentifier is a data type which can contain a unique hexadecimal value up to 36 characters. More technically, it’s a 16-byte GUID.

We can either define a column or a variable with this data type. A column or local variable of UniqueIdentifier data type can be initialized to a value in the following ways:

  • By using the NEWID or NEWSEQUENTIALID function. It’s recommended you use NEWSEQUENTIALID function. I’m not gonna discuss why? All I can say is NEWSEQUENTIALID is faster than the NEWID due to its internal working.
  • By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid UniqueIdentifier value.

Read more…

How to Get Business Intelligence Project Templates in VS 2012, 2013 and 2015

Yes. I agree. It’s a bit confusing.

I downloaded the SSDT Tools installer for Visual Studio 2013 and, of course, I assumed it’s for SQL Server 2012 but it was for the SQL Serve 2014. I just banged my head against the wall. Ouch! It hurts…

So, what does it mean? Can I do a BI project using VS 2013 and SQL Server 2012 or SQL Server 2008/R2?

Yes. We can do. Let me explain it.

If you install SSDT BI Tools for Visual Studio 2012, it means the Microsoft, by default assumes you’re going to work with the SQL Server 2012 version. You will get all BI project templates in the Visual Studio 2012. So, here it’s smooth. No confusion, no problem at all.

If you install SSDT BI Tools for Visual Studio 2013, the Microsoft assumes you’re going to work with the SQL Server 2014 version. The VS 2013 has been designed keeping SQL Server 2014 in mind. I got confused while installing SSDT BI tools as I had VS 2013 and SQL Server 2012 setup on my machine and SSDT installer showed me it’s installing for the SQL Server 2014. I was in a fix to continue or not, but then I decided to go on.

Read more…

Using OLE DB Data Source in SSIS

A data source in SSIS package is something which helps us to connect to the source of data like RDBMS, CSV File, Excel File, etc. and pull the data out.

A data source is used when we’re using Data Flow task. I’m sure you know about the Data Flow task, which is widely used, almost in every package. 🙂

There are various data sources such as ADO.NET, OLE DB, Flat File, Excel File, Raw File, etc. We choose a data source based on our requirement, from where we’re going to pull the data out.

Today, we’ll be talking about OLE DB Source, which can connect to almost all kinds of existing data sources, whether it’s SQL Server or Oracle or SAP NetWeaver or Access, etc. OLE DB uses the Connection Managers behind the scene in order to connect to a data source. It’s not Data Source but a Connection Manager decides what kind of driver you’re going to use and what database or data source you’re connecting to.

Read more…

Tags: ,

How to Export Data into Different Spreadsheet in Same Excel File in SSIS

We’re going to discuss how to export data into different spreadsheet in the same Excel file over here. We’re going to use SQL Server Integration Services 2012 and AdventureWorks2012 database. However, if you’re using earlier version like SSIS 2008 R2, It’ll be working perfectly fine. So, no worries!

I assume that you know how to create a package, what is Data Source, Data Destinations, etc. in SSIS.

Create a package and drag and drop a Data Flow task on Control Flow tab.


Read more…

How to Implement One-to-One Relationship in SQL Server

Today, we’re going to discuss how to implement One-to-One relationship between two tables in SQL Server. So, what does it mean? That’s pretty simple. We can simply go to Database Diagram option and drag Primary Key from master table and drop that on Foreign Key on child table…and that’s it. What’s the big deal???

Yes. It will definitely implement Foreign Key constraint but the cardinality will be One-to-Many by default. Here we get the problem. I wish to implement One-to-One cardinality rather One-to-Many. I tried a lot with available options but in vain. The SQL Server doesn’t provide any option where you can simply go and change the cardinality between tables.

Let’s discuss how to achieve this in SQL Server.

I’ve got two tables, named 1.) Customer, and 2.) CustomerContact as you can see in below image.


Relationship Read more…

Data Sources, Data Transformations and Data Destinations in SSIS 2012

I’ve always come across with pretty common questions while dealing with SSIS training sessions – What are Data Sources, Data Transformations and Data Destinations in SSIS package? Why do we use them? Where do we use them? Do we use them all the time? blah…blah…

So, let’s discuss a little bit about them.

I’m sure every package developer comes across requirements where we need to import/export data from one data source to another data source. And, the very basic control/tool that SSIS provides with us is Data Flow task. I assume we are aware of why we use Data Flow task. Yet, let me briefly explain it.

The Data Flow task is a tool which is used to define activities involved into import/export data from one source to another. We need to make connection with the data source, pull the data out, work on the data like cleaning or validating it and then push into data destination. Here comes the need for Data Sources, Data Transformations and Data Destinations.

The Data Sources such as ADO NET, OLE DB, Flat File, Raw File, Excel Source, etc. help us to connect to the data source, from where we are going to pull the data out. These Data Sources use connection managers internally to define the type of data provider being used, server name, database name and login credentials, etc. in order to connect to a data source.

Read more…