Write recursive t-sql code to get Square root of integer number without using SQRT function

Create Function [dbo].[fn_SQRT](@v_Number bigint,@v_ID bigint)
Returns bigint
as
Begin
If (@v_Number != 1)
Begin
if (@v_Number%@v_ID = 0)
Begin
Set @v_Number = @v_Number/(@v_ID * @v_ID)

return @v_ID * dbo.fn_SQRT(@v_Number, @v_ID)
End
Else
Begin
return dbo.fn_SQRT(@v_Number, @v_ID + 1)
End
End
return 1
End
GO

select [dbo].[fn_SQRT](63297936, 2)
Go

--> 7956

Write t-sql code to generate Fibonacci series without using any inbuilt function

Declare @v_Number INT = 158919851
Declare @v_First INT = 0
Declare @v_Second INT = 1
Declare @v_Temp INT

Print @v_First

While(@v_Second < @v_Number)
Begin
    Set @v_Temp = @v_First + @v_Second
    Set @v_First = @v_Second
    Set @v_Second = @v_Temp

    Print @v_First
End

Write t-sql code to check if given number is Palindrome or not without using any inbuilt function

Declare @v_ActualNumber INT = 158919851
Declare @v_ReverseNumber INT = 0
Declare @v_TempNumber INT
Declare @v_Number INT

Set @v_TempNumber = @v_ActualNumber

While(@v_TempNumber > 0)
Begin
    if (@v_TempNumber >= 10)
    Begin
        Set @v_Number = @v_TempNumber%10

        Set @v_ReverseNumber = @v_ReverseNumber + @v_Number

        Set @v_TempNumber = (@v_TempNumber - @v_Number)/10

        Set @v_ReverseNumber = @v_ReverseNumber * 10
    End
    Else
    Begin
        Set @v_ReverseNumber = @v_ReverseNumber + @v_TempNumber

        Set @v_TempNumber = 0
    End
End

if (@v_ActualNumber = @v_ReverseNumber)
Begin
    Print 'Palindrome Number'
End
Else
Begin
    Print 'Not Palindrome Number'
End

Write t-sql code to get Square root of integer number without using SQRT function

Declare @v_ActualNumber INT
Declare @v_FinalSquareRootNumber INT
Declare @v_SquareRootNumber INT
Declare @v_ID INT

Set @v_ActualNumber = 63297936
Set @v_FinalSquareRootNumber = 1
Set @v_ID = 2

While(@v_ActualNumber <> 1)
Begin
    if (@v_ActualNumber%@v_ID = 0)
    Begin
        Set @v_SquareRootNumber = @v_ID

        Set @v_ActualNumber = @v_ActualNumber/(@v_SquareRootNumber * @v_SquareRootNumber)

        Set @v_FinalSquareRootNumber = @v_FinalSquareRootNumber * @v_SquareRootNumber
    End
    Else
    Begin
        Set @v_ID = @v_ID + 1
    End
End

Select @v_FinalSquareRootNumber

How to delete Team Project from Online Visual Studio

1) Login to online TFS account by accessing below link.
www.youracount.visualstudio.com

2) Click on Administer Account which is right side after your name.

3) It will show all team projects for your account. Click on “View the collection administration page”.

4) Right click the project which you want to delete and click on Delete.

5) It will ask for confirmation before deleting team project. Click on Delete Project.

6) Once the team project is delete. It will show status message for the team project is deleted.

Please free to comment if you have doubts or suggestion in this.

Could not update the metadata that indicates database is enabled for Change Data Capture.

Today, I came across below error when I was trying to enable CDC for database. I used below query to enable CDC on database.

Query:

Use Asif
Go

EXEC sys.sp_cdc_enable_db

Go

Error:

Could not update the metadata that indicates database is enabled for Change Data Capture.

The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user , error code 0x5.'. Use the action and error to determine the cause of the failure and resubmit the request.

Fix:

I fixed it by changing database owner from my user to sa. Below is query to change db owner to sa.

Query:
Use Asif

Go

EXEC sp_changedbowner 'sa'

Go

SQL Server Scenario Based Interview Question for Date

1.  How to get current month first date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current DateSelect @v_CurrentDate
-- Current month first date
Select DATEADD(DD,1,EOMONTH(@v_CurrentDate,-1))

Select DATEADD(DD,-DAY(@v_CurrentDate) + 1,CAST(getdate() as date))

Output:

2014-04-01

2. How to get current month last date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current Date
Select @v_CurrentDate

-- Current month last date
Select EOMONTH(@v_CurrentDate)

Select DATEADD(DD,-DAY(DATEADD(MM,1,@v_CurrentDate)),CAST(DATEADD(MM,1,@v_CurrentDate) as date))

Output:

2014-04-30

3. How to get next month first date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current Date
Select @v_CurrentDate

-- Next month first date
Select DATEADD(DD,1,EOMONTH(@v_CurrentDate))

Select DATEADD(DD,-DAY(DATEADD(MM,1,@v_CurrentDate)) + 1,CAST(DATEADD(MM,1,@v_CurrentDate) as date))

Output:

2014-05-01

4. How to get next month last date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current Date
Select @v_CurrentDate

-- Next month last date
Select EOMONTH(@v_CurrentDate,1)

Select DATEADD(DD,-DAY(DATEADD(MM,2,@v_CurrentDate)),CAST(DATEADD(MM,2,@v_CurrentDate) as date))

Output:

2014-05-31

Please feel free suggest if you have more question. We will include and also give credit for that.

How to read data from excel (*.xlsx) file using Microsoft.ACE.OLEDB.12.0

In our last post, we have seen how to get column header list from Excel using Open XML SDK 2.5. You can refer below link.

http://www.sqlscientist.com/2014/04/get-column-header-list-from-excel-using.html

Now, we will see how to read data from excel file Product.xlsx which is shown below and convert it to XML format to send to database.

C#.NET Code
protected void ReadExcel()
{
try
{
string path = Server.MapPath("Product.xlsx");

System.Data.OleDb.OleDbConnection oledbConn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
oledbConn.Open();

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandTimeout = 0;
cmd.Connection = oledbConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM [Sheet1$]";

System.Data.OleDb.OleDbDataAdapter oleda = new System.Data.OleDb.OleDbDataAdapter();
oleda = new System.Data.OleDb.OleDbDataAdapter(cmd);

System.Data.DataSet ds;
ds = new System.Data.DataSet("ProductList");
oleda.Fill(ds, "Product");

XElement xeProductList = XElement.Parse(ds.GetXml());

Response.Write(xeProductList);
}
catch (Exception ex)
{
throw ex;
}
}

Output XML

<ProductList>
<Product>
<ID>1</ID>
<Name>Asif Ghanchi</Name>
<Department>IT</Department>
<Salary>50000</Salary>
</Product>
<Product>
<ID>2</ID>
<Name>Ghanchi Asif</Name>
<Department>Production Support</Department>
<Salary>60000</Salary>
</Product>
</ProductList>

You can also download sample excel from below location.

1. Product.xlsx

Get column header list from Excel using Open XML SDK 2.5

There are many applications which are using to insert, update and delete data in database using Excel file. In this type of requirement, we also seen requirement where we need to get column header list from Excel.

To do that, I have used Open XML SDK 2.5. You can download it from below link.
http://www.microsoft.com/en-in/download/details.aspx?id=30425

Once you download and install Open XML SDK 2.5, you can follow below steps in Visual Studio web application to add DLLs.

1) Right click on References -> Add Reference

2) Go to Assemblies -> Framework -> Check "WindowBase" -> Click Ok

3) Go to Assemblies -> Extensions -> Check "DocumentFormat.OpenXml" -> Click OK.

Now, you can use below code to read column headers from excel Product.xlsx which is shown below and convert it to XML format.

C#.NET Code
try{
string path = Server.MapPath("Product.xlsx");

System.IO.Packaging.Package package = System.IO.Packaging.Package.Open(path, System.IO.FileMode.Open, System.IO.FileAccess.Read);

var document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(package);
var workbookPart = document.WorkbookPart;
var workbook = workbookPart.Workbook;
var sheet = workbookPart.Workbook.DescendantsSheet>().FirstOrDefault();
DocumentFormat.OpenXml.Spreadsheet.Worksheet ws = ((DocumentFormat.OpenXml.Packaging.WorksheetPart)(workbookPart.GetPartById(sheet.Id))).Worksheet;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = ws.GetFirstChildSheetData>();

var worksheetPart = (DocumentFormat.OpenXml.Packaging.WorksheetPart)workbookPart.GetPartById(sheet.Id);
var sharedStringPart = workbookPart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.ElementsSharedStringItem>().ToArray();
var rows = worksheetPart.Worksheet.DescendantsRow>();

if (rows.Count() > 0)
{
XElement xeColumnList = new XElement("ColumnList");
var cells = rows.FirstOrDefault().ElementsCell>();
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in cells)
{
XElement xeColumn = new XElement("Column");
if (cell.DataType != null && cell.DataType.Value == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
{
xeColumn.Add(new XElement("ColumnName", values[int.Parse(cell.CellValue.Text)].InnerText));
}
else
{
xeColumn.Add(new XElement("ColumnName", cell.CellValue.Text));
}
xeColumnList.Add(xeColumn);
}
package.Close();

Response.Write(xeColumnList.ToString());
}
else
{
package.Close();
}
}
catch (Exception ex)
{
throw ex;
}


Output XML
<ColumnList>
<Column>
<ColumnName>ID</ColumnName>
</Column>
<Column>
<ColumnName>Name</ColumnName>
</Column>
<Column>
<ColumnName>Department</ColumnName>
</Column>
<Column>
<ColumnName>Salary</ColumnName>
</Column>
</ColumnList>

This will be helpful when you want to update selected columns in Database from Excel. In this case, you can generate XML for column list and pass through procedure and update only selected columns to the database.

You can also download sample excel from below location.
1. Product.xlsx

Msg 40526, 'OPENXML' rowset provider not supported in this version of SQL Server

Today, I was trying to publish my database to Windows Azure SQL Database and found that OPENXML is not supported.

There are many others Transact-SQL Statements which are also not supported. You can get the list of them from below link.

http://msdn.microsoft.com/en-us/library/ee336253.aspx

Now, we will see how we can replace OPENXML using XQuery (or XML Data Type Methods) nodes() and value(). You can get more details regarding XQuery from below link.

http://technet.microsoft.com/en-us/library/ms190798.aspx

Below is logic which is used OPENXML and read column names from below XML.

Query:
Declare
@p_ColumnList XML = N'<ColumnList>
  <Column>
    <ColumnName>ID</ColumnName>
  </Column>
  <Column>
    <ColumnName>Name</ColumnName>
  </Column>
</ColumnList>', @vColumnListDoc int

Declare @ColumnListTable table(
ColumnName varchar(500)
)


-- Prepare XML Document
exec sp_xml_preparedocument @vColumnListDoc output, @p_ColumnList

Insert into @ColumnListTable(
ColumnName
)
  
Select
   ColumnName
From
   openxml(@vColumnListDoc,'/ColumnList/Column',2)
with(
   ColumnName varchar(500)
)
  
-- Remove XML Document 
exec sp_xml_removedocument @vColumnListDoc

Select * from @ColumnListTable
Output:

Now, we will see how we can implement same logic using XQuery (or XML Data Type Methods) nodes() and value().

Query:
Declare
   @p_ColumnList XML = N'<ColumnList>
  <Column>
    <ColumnName>ID</ColumnName>
  </Column>
  <Column>
    <ColumnName>Name</ColumnName>
  </Column>
</ColumnList>'

Declare @ColumnListTable table(
ColumnName varchar(500)
)
  
Insert into @ColumnListTable(
ColumnName
)Select
T.c.value('./ColumnName[1]','varchar(500)')
From
   @p_ColumnList.nodes('/ColumnList/Column') T(c)


Select *
From
   @ColumnListTable
Output:

It looks very similar as OPENXML with less steps. Hope this post will be useful for you also.

This post also can helpful for different type of questions as below.

1) How to use OPENXML to read data from XML?
2) How to read column names from XML?
3) How to use XQuery nodes() and value()?

Schedule Database Backup using Windows Task Scheduler

You may have seen my last past about to Auto synchronize Data from Production to Development using Script Daily.

On top of that, we also had requirement to schedule database backup one database is synchronized.

We also used windows task scheduler using batch file to do this task because we had SQL Server Express edition installed on our Server.

Batch File

Now, we will see how to create batch file which will execute SQL Server BACKUP DATABASE command to take backup of database from server to local file system.

We have also created query so it will create backup in below format.
Database_YYYYMMDD_hhmmss
(YYYY = Year, MM = Month, DD = Day, hh = hour, mm = minute, ss = second).

Query:
SQLCMD -S [Server Name] -U [User Name] -P [Password] -Q "Declare @vDisk nvarchar(500); Set @vDisk = N'V:\Asif Ghanchi\Asif_'+ replace(replace(replace(convert(varchar,getdate(),120),'-',''),' ','_'),':','') + '.bak'; BACKUP DATABASE [Asif] TO DISK = @vDisk WITH NOFORMAT, NOINIT, NAME = N'Asif-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"
You can get more details about SQLCMD from below link.
http://technet.microsoft.com/en-us/library/ms162773.aspx

Below are details of above parameters.

1. [Server Name] : You can get server name from you SQL Server Management Studio (SSMS) using below query

Query:
Select @@servername

2. [User Name] (Optional): If you want to connect SQL Server using SQL Authentication then you can define your user name here. Else it will connect using Windows authentication.
3. [Password] (Optional) : You can define SQL Server User Name Password here.

Scheduler
Now, we will see how to create windows "Task Scheduler" which will execute above batch file daily.

To do that, we need to follow below steps.
1. Go to Start -> Control Panel -> Administrative Tools -> Task Scheduler
2. Click on "Create Basic Task".

3. Enter Name and Description of Task and Click Next.

4. Choose appropriate schedule type and Click Next. I have choose "Daily".

5. Choose appropriate start time and recurring based on Schedule Type selected in last step. I have choose 11 AM for every day.

6. Choose action which you want to perform and Click Next. We have choose "Start a program" because we want to execute batch file for this task.

7. Browse batch file location from where you want to execute batch file for this task and Click Next.

8. Review Screen and Click Finish.

9. Below is task which is created in Windows Task Scheduler.

Hope it will also be useful for your requirement also.