SQL: Get Database Name From Physical Name

Here’s a SQL Function to get name of the database from it’s physical full name.

This function can be used in conjunction with RESTORE FILELISTONLY which can generate physicalname,  the parameter of this function, from a database’s backup file.

You can read more about RESTORE FILELISTONLY here.


create function get_db_name_from_physical_name
(@physicalname varchar(500))
returns varchar
as
begin

declare @dbName as varchar(500)

select @dbName =  DB_NAME(database_id) from sys.master_files where name = @physicalname

return @dbName
end

SQL: Save Result From RESTORE FILELISTONLY

RESTORE FILELISTONLY

is a Transact Statement in MS-SQL that returns a result set containing a list of the database and log files contained in the backup set in SQL Server.

Basically it contains meta-data pertaining to the database’s:

  • LogicalName
  • PhysicalName
  • Type
  • Size
  • MaxSize
  • etc.

This information is quite useful when performing Restore operation. For example, the physical name can be used to restore an existing database to a new database using the backup of the existing database by identifying and changing it’s physical name.

Here’s a quick script to create a table and store result from RESTORE FILELISTONLY operation. This table can then be used to select desired columns only like PhysicalName and LogicalName.


IF OBJECT_ID('tempdb..#Restore') IS NOT NULL DROP TABLE #Restore

CREATE TABLE #Restore (
LogicalName NVARCHAR(128),
PhysicalName NVARCHAR(260),
[Type] CHAR(1),
FileGroupName NVARCHAR(128),
Size NUMERIC(20,0),
MaxSize NUMERIC(20,0),
FileID BIGINT,
CreateLSN NUMERIC(25,0),
DropLSN NUMERIC(25,0),
UniqueID UNIQUEIDENTIFIER,
ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupID INT,
LogGroupGUID UNIQUEIDENTIFIER,
DifferentialBaseLSN NUMERIC(25,0),
DifferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint VARBINARY(32)
)

INSERT INTO #Restore
RESTORE FILELISTONLY FROM DISK='' -- database backup path here

 

 

 

Are you ready to work as a SQL Developer?

A SQL Developer is responsible for managing data in a relational database. Database Providers like Microsoft, Oracle and MySql have their own IDE for managing data. For example Microsoft has SQL Server Management Studio for this purpose and Oracle has Oracle Developer Studio. A SQL Developer makes use of these IDEs for managing data.

Today I would like to list down the most common tasks that every SQL Developer must be familiar with as these will often come up as a requirement in the course of their day to day to work. This list will also serve as a guideline for anyone who wishes to advance their career towards becoming a SQL Developer.

In no particular order, here’s the list of tasks:

  1. Connecting to different SQL Servers using Windows and SQL Server Authentication.
  2. Creating  new databases in many different ways.
  3. Backing up and restoring databases.
  4. Designing and Normalizing Tables as per need.
  5. Using Primary Keys, Identity Columns and Foreign Keys for table creation.
  6. Altering Tables and Columns.
  7. Properly using Data Types and Column Size Limits.
  8. Writing basic to advanced level queries for SELECT, UPDATE, INSERT and DELETE.
  9. Writing table JOIN statements.
  10. Filtering Rows using WHERE clauses and JOIN statements.
  11. String manipulation using functions like Replace, Stuff, Trim and Substring.
  12. Creating tables on the fly.
  13. Using Cursors for looping.
  14. Writing Stored Procedures.
  15. Writing Functions.
  16. Writing Triggers.
  17. Using Linked Servers.
  18. Adding Indexes on tables.
  19. Using aggregator functions like Sum, Count, Group By, Order By, Row_Number and Distinct.
  20. Accessing table and column information across databases using Information_Schema.
  21. Generating Scripts to re-create database objects like tables and functions.
  22. Importing Data from external sources (mainly csv files sand excel sheets).
  23. Creating and authorizing  Database Users.

Note: Many of these tasks can be completed in two different ways i.e. either using the GUI or using Structured Query Language.

Using the GUI

Writing Queries

While it may be easier to operate through a User Interface for a newbie as he doesn’t have to remember the necessary queries, but on the long run it is better and also important to be fluent in performing these tasks writing queries.

Once you get the hang of it, you will find that writing query is more easier and faster than operating through UI as you no longer have to worry about where a particular function is located to perform a task. You just have to write the query on the query editor window.

Conclusion

The intention for this post was to identify the common operations that a SQL Developer must be fluent with in order to work in any professional environment. While this is not an an inclusive list of tasks and I may have left out a few other essential tasks, but nevertheless, if you are familiar with the tasks listed above, surely you can call yourself a SQL Developer ;).

Connecting to SQL Server using RODBC package in R

RODBC is a useful package for making SQL queries to SQL Server database for R users.

To start using this package, first we need to make sure it is loaded into R studio.


is.installed <- function(mypkg) is.element(mypkg, installed.packages()[,1])

if(!is.installed('RODBC'))
{
install.packages("RODBC")
}

library(RODBC)

Now that the package is installed/loaded into our environment, let’s start making SQL queries.

First we will need to provide credentials for accessing our SQL Server Database for R. Mainly we will need to have correct info for:

  1. Server name
  2. Database name
  3. Login Id
  4. Password

Providing connection info:


conn <- odbcDriverConnect('driver={SQL Server};
server=SOVITHOME-PC\\RAY;
database=EmergencyRMDb;
uid=sa;
pwd=myPwd***')

Now the “conn” object holds connection for SQL Server. Using this object we can make different kinds of queries supported by RODBC. Let’s perform some common queries like:

  • SELECT statements
  • SELECT statements with WHERE Clause
  • UPDATE statements
  • SQL JOIN statements

# print row count of tables
sqlQuery(conn, "select count(*) from EQData", as.is=T)

# print name of columns
colnames(tblData)

# select all data from a table
tblData &lt;- sqlQuery(conn, "select * from EQData", as.is=T)
#preview data
head(tblData)

<img class="wp-image-542" src="http://sovitpoudel.com.np/wp-content/uploads/2017/03/result1-300x68.png" alt="" width="850" height="193"> Result from query


# Viewing columns and rows

# [x,y] -> x represents the row number and y represents the column number

tblData[,1:5] # view columns 1 through 5

tblData[2,] # view first two rows

tblData$Latitude[2] # view second value for specified column
# finding by matching row(s) value
tblData[tblData$Epicentre == "Kathmandu",]

# update tables
sqlQuery(conn, "update EQData set latitude = '27.75' where epicentre = 'kathmandu'")

SQL JOIN statements can also be easily performed using “merge” function.


# perform inner join
newT <- merge(tblA, tblB, by = "key_id")
dim(newT)

# perform outer join
newOuterT <- merge(tblA, tblB, by = "key_id", all = TRUE)
dim(newOuterT)

# perform left join
newLeftT <- merge(tblA, tblB, by = "key_id", all.x = TRUE)
dim(newLeftT)

# perform right join
newRightT <- merge(tblA, tblB, by = "key_id", all.y = TRUE)
dim(newRightT)

These are some common operations that we can easily perform using R and RODBC on SQL Server tables. As you can see, most of the common operations can be performed using built-in R functions for Data Frames on R. I am just making use of “sqlQuery” function provided by RODBC for making queries on the Server.

Please find the full list of RODBC supported queries at: https://cran.r-project.org/web/packages/RODBC/RODBC.pdf

Hope this was helpful. If you got stuck on any of the steps, above please do not hesitate to leave comments below.

Thanks!

Uploading Unknown Format Excel File To A SQL Database

Introduction

Usually it is expected from client side to upload only structured Excel sheets (sheet with defined Column Names and data types for Rows) to load to server. But sometimes you might also require to dump whatever is in the Excel directly to your database. While there is a tool that is provided by SQL Management Studio for importing data from Excel files, this tutorial covers how you can automate such task using C# and .NET framework.

Steps

Two important pieces for uploading excel file of unknown column format to SQL database are:

  1. Reading Data from Excel into a Data Table
  2. Creating Table in SQL Server based on the Data Table

Once these two steps are done, the final step is simply to bulk load the data table into the table in SQL server.

Reading Data from Excel into a Data Table

I am using Excel Data Reader nuget package for this purpose.


/// <summary>
/// Gets a data table based on provided Excel File
/// </summary>
/// <param name="pathToExcelFile"></param>
/// <returns></returns>
public static DataTable GetTableFromExcel(string pathToExcelFile)
{
FileStream stream = File.Open(pathToExcelFile, FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader;
if (Path.GetExtension(pathToExcelFile) == ".xls")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}

excelReader.IsFirstRowAsColumnNames = true;

//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
DataTable dt = null;
if (result != null && result.Tables.Count > 0)
{
dt = result.Tables[0];//get first sheet only
}
return dt;
}

Excel Data Reader package supports multiple sheets of Excel to be read into a Data Set at once and also allows other configurations such as: Supporting multiple file formats and Checking Column Names in First Row.

Creating Table in SQL Server based on the Data Table

Now, to generate a “Create Table” query, I found a very useful code on the Stack Overflow site for this purpose.


/// <summary>
/// Generates sql create table query for given data table
/// </summary>
/// <param name="tableName">name of the table to be created</param>
/// <param name="table">data table</param>
/// <returns></returns>
public static string GenerateCreateTableQuery(string tableName, DataTable table)
{
var sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
string columnType = table.Columns[i].DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqlsc += " int ";
break;
case "System.Int64":
sqlsc += " bigint ";
break;
case "System.Int16":
sqlsc += " smallint";
break;
case "System.Byte":
sqlsc += " tinyint";
break;
case "System.Decimal":
sqlsc += " decimal ";
break;
case "System.DateTime":
sqlsc += " datetime ";
break;
case "System.String":
default:
sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
break;
}
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
}
return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
}

Once we have the data and create table script, uploading to database is quite simple and can be achieved with following steps:

  1. Decide upon a table name
  2. Check if it exists or not in the server
  3. Create table if it doesn’t exist
  4. Bulk load data table into your newly created table.

 

7 Handy SQL Scripts for SQL Developers

Finally got my article published at Tuts+

I had written this article “7 Handy SQL Scripts for SQL Developers” referencing some of the common scripts that I most frequently use @ work. Some of these scripts were collected from different sites while some were developed by myself.

————————————————————————————

“A lot of things that we do depend upon the knowledge that we possess. If we are aware of what can be done, only then we can make smarter and more effective decisions. That is why it is always good to have quick tips and tricks handy in your pocket. This principle applies everywhere, including for MS-SQL developers.
Through this article I would like to share a few SQL scripts which have proven to be very useful for my daily job as a SQL developer. I’ll present a brief scenario about where each of these scripts can be used along with the scripts below. ……………………”

Read full article at:
http://code.tutsplus.com/tutorials/7-handy-sql-scripts-for-sql-developers–cms-25834—————————————————

Running Scripts Larger than 200 MB size in MySQL

This is a workaround for running a script from a very large file that you want to use to create/modify your database in MySQL.
After searching for possible workarounds, the easiest one that I found is to use a third-party tool called dbForge Studio for MySql. 

“dbForge Studio is a universal MySQL GUI tool that enables MySQL developers and administrators to create and execute queries, develop and debug MySQL routines, automate MySQL database object management in the convenient environment, and many others.”
More info on dForge Studio at: http://www.devart.com/dbforge/mysql/studio/

When you download this tool, make sure to download the Professional version which is a trial version instead of the Express (free version). Express version doesn’t support running files larger than 50 MB size.

Once you have dbForge Studio installed, connect to the database that you want to run the script for. Then, open your script file from File menu command. If the file bigger than 50 MB, then you will receive a message asking whether you want to execute the script directly instead of opening. Choose yes and the script will be executed at once.

SQL Improving Query Performance

1.     Use Indexes.
2.     Use All instead of UNION, OUTER UNION, EXCEPT and INTERSECT when you know there are no duplicate rows or when you know it does not matter whether duplicate rows are returned.
3.     Omit the ORDER BY clause unless necessary.
4.     Inline VIEWS or Temporary Tables?
      If the query is “long” and you are accessing the results from multiple queries, then use a Temporary Table to store the result from the query so that you can reuse it. Since a VIEW is just a select statement, the results need to be generated each time the view is called. Although subsequent runs of the view may be efficient because the results are stored in the cache, a temporary table actually stores the data.
5.     Use WHERE expressions with JOINS. JOINS without WHERE clause are often time consuming because of the ‘multiplier effect’ of the Cartesian Product.
6.     Replace multiple references to the DATE, TIME, DATETIME and ToDay functions in a single procedure. When the proc SQL ConstDateTime option or the SQLConstDateTime system option is set, proc SQL evaluates the date-time functions once through and uses the same result once throughout the whole query.

Cubism


“Errors in the metadata manager. The ‘Report Metrics’ (some name) measure group has more than one distinct count measure.”
 
When you create a new measure that uses ‘Distinct Count’, Business Intelligence Development Studio (BIDS) automatically creates a new measure group for you. But when you try to change the usage of an existing measure from a group to ‘Distinct Count’ that already has another ‘Distinct Count’ defined, you get this type of error (while deploying).
After digging a little into BIDS, I found out that cube in BIDS doesn’t allow multiple measures with ‘Distinct Count’ to exist in same measure group. For query performance reasons, this behavior is not encouraged by BIDS.
So, the way around it is to create a separate measure group for each measure that implements ‘Distinct Count’.
Furthermore, if you have too many measures that require this type of usage, you can create calculated measures to avoid having too many measure groups to display in the browser. You can hide all the ‘Distinct Count’ measures and then define calculated measures for each distinct count measure. Then assign the calculated members to specific measure group that you want to.