Mahadevan Business Intelligence: 2012

Tuesday, 16 October 2012

ODBC Connectivity Issue - Missing System DSN connection in SSIS

We often come across this issue when our working server and source server have different bits 32 or 64.

You might have created System DSN in your system, but when try to create connection manager, the DSN will be missing in ODBC list. To overcome this you need to create DSN connection in appropriate ODBC (32/64).

Perform the followings:

Open command window:

1. Navigate to C:\Windows\Sysos64\Odbacd32.exe

2. ODBC Connection wizard will appear.
3. Create a new system DSN there.
4. Now try creating Connection Manager in SSIS package

Thursday, 27 September 2012

Attach MDF file to a Database

Below is the query to attach MDF file to a database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

Attaching MDF file to a Database

Below is the query to attach mdf file to a database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

Wednesday, 26 September 2012

Attaching .MDF file to a Database

Below is the script to attach AdventureWorks.mdf file to a newly created database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

Wednesday, 27 June 2012

Call SSRS Report in ASP .NET with Parameters

The below steps help to call an SSRS report in Web Page designed using ASP.NET. The practice the below code we should install AJAX toolkit.

1. Create anew ASP wep page project.
2. Add a new web form.
3. Add th ebelow scripts in the WEB for designer:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SSRS2.aspx.cs" Inherits="SSRS_WEB.WebForm2" %>
<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
       <tr>
           <td>Start Date: </td>
           <td><asp:TextBox Width="180" runat="server" ID="StartDatePr"/></td>
           <td><asp:CalendarExtender runat="server" TargetControlID="StartDatePr"/></td>
           <td>End Date: </td>
           <td><asp:TextBox Width="180" runat="server" ID="EndDatePr"/></td>
           <td><asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="EndDatePr"/></td>
           <td><asp:Button Text="Show Report" ID="btnSubmit" runat="server" onclick="btnSubmit_Click" /></td>
       </tr>
   </table>
    </div>
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <rsweb:ReportViewer ID="MyReportViewer" runat="server" Font-Names="Verdana"
        Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote"
        WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="800px"
        Width="1000px">
        <ServerReport ReportServerUrl="" />
    </rsweb:ReportViewer>
    </form>
</body>
</html>

4. Add below code in respective .CS file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.Common;
using Microsoft.Reporting.WebForms;
namespace SSRS_WEB
{
    public partial class WebForm2 : System.Web.UI.Page
    {
       protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnSubmit_Click(object sender, EventArgs e)

        {
            //First
            MyReportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
            MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver_Sathya"); // Report Server URL
            MyReportViewer.ServerReport.ReportPath = "/SQLSSRS/Dashboard"; // Report Name
            MyReportViewer.ShowParameterPrompts = false;
            MyReportViewer.ShowPrintButton = false;
            ReportParameter[] parameters = new ReportParameter[2];
            parameters[0] = new ReportParameter("StartDate", StartDatePr.Text);
            parameters[1] = new ReportParameter("EndDate", EndDatePr.Text);
            MyReportViewer.ServerReport.SetParameters(parameters);
            MyReportViewer.ServerReport.Refresh();

        }
    }
}

On executing the page, you can see SSRS reoport called in Web page.

Monday, 18 June 2012

SQl query to database backup

The below query helps to take a backup of a DB:

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name IN ('SalesDW')

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END CLOSE db_cursor

DEALLOCATE db_cursor

SQL Query to drop all constraints in a table

DECLARE @sql nvarchar(255), @DB nvarchar(50), @Table nvarchar(50)SET @DB = 'source_1'SET @Table = 'Product'WHILE
EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @DB AND table_name = @Table)BEGIN

SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @DB AND table_name = @tableEXEC sp_executesql @sqlEND

Fastest Performing Rowcount SQL Query

Try executing the below queries in a table containing millins of records. Both the queries yields row count of the table, but the performance of Query 1 is more.

Query 1:

SELECT SUM(row_count) FROM Sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('AccountCommunications')
AND (index_id =0 or index_id =1);

Query 2:
Select COUNT(*) From dbo.AccountCommunications

Query to Backup and Restore Database

Below is the query to backup and restore a database:

BACKUP DATABASE [Source_1] TO DISK = N'E:\Backup\Source_1.bak' WITH
NOFORMAT
, COMPRESSION
.NOINIT
,NAME = N'Full bakup of Source_1'
, SKIP, STATS = 10
GO

--2.Optionally, determine the logical and physical names of the files in the backup set that contains the full database backup that you want to restore.
RESTORE FILELISTONLY FROM DISK = N'E:\Backup\Source_1.bak'
-- 3. Restore
RESTORE DATABASE [Source_3]FROM DISK = N'E:\Backup\Source_1.bak' WITH FILE = 1,
MOVE 'Source_1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source_3.mdf'
, MOVE 'Source_1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source_3_log.ldf',
NOUNLOAD, REPLACE,STATS = 10
GO;

Active requests in MS SQL Database

The below query helps user to determine the active requests in MS SQL Server:

SELECT SQL_handle, * from sys.dm_exec_requests
SELECT
* FROM sys.dm_exec_sql_text(0x020000009D72DA2080C94ACE2F4DF4A9452887663FF9A16D)-- Use SQL_Handle values

Generating XML or Text file from T-SQL Query Output

--ENABLE XP_CMDSHELL===========================================================

-- To allow advanced options to be changed.
EXEC
sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC
sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
--=============================================================================

--Create Pipe-Delimited Text file
EXEC
master..xp_cmdshell
'bcp "Select * from [Bids].dbo.Employee" queryout "E:\Work Area\FOF\Iteration24\Text.txt" -t"|" -c -T -x'
--Create Xml fileEXEC
master..xp_cmdshell
'bcp "Select * from [Bids].dbo.Employee for XML auto" queryout "E:\Work Area\FOF\Iteration24\Text.xml" -c -T -x'

--DISABLE XP_CMDSHELL================================================================

-- To allow advanced options to be changed.
EXEC
sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
--To enable the feature.EXEC
sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Batch Script to execute SQL command and Save the output as text file

Follow the below steps to execute SQL command and Save the output as text file through batch file:

Step 1: Prepare an SQL query and save the query as sql file (e.g., Test.sql).

Step 2: Create batch file as shown below:
--======================================================================
@ECHO OFF
SETLOCAL

REM Build an pipe delimited text file
SET PATH = C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3;
SqlCmd -S DDWSBDB01CS -d EDW20 -s"|" -E -i "C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3\Test.sql" -o "C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3\Test.Txt"
ECHO.

echo Completed Successfully at %date% %time%>>_date_.txt
--====================================================================

Step 3. Run the batch file, you can notice a text file created in the specified path with the SQL query output.

To get quotes delimited output use the following:
SqlCmd -S 192.2.200.99 -E -s'\t' -i "E:\Work Area\FOF\Iteration24\Employee.sql" -o "E:\Work Area\FOF\Iteration24\OutputQuotes.txt"

To get tab delimited output use the following:
SqlCmd -S 192.2.200.99 -i "E:\Work Area\FOF\Iteration24\Employee.sql" -o "E:\Work Area\FOF\Iteration24\OutputQuotes.txt"

How to Identify Foreign Keys Without Index

The below query helps you to identify the foreign key column in a Database\Tables without any index.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName

,t.name AS TableName

,fk.name AS ConstraintName

,c.name AS ColumnName

FROM sys.tables t

JOIN sys.columns c ON c.object_id = t.object_id

JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id

JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id

AND fkc.parent_column_id = c.column_id

LEFT JOIN sys.index_columns ic ON ic.object_id = fkc.parent_object_id

AND ic.column_id = fkc.parent_column_id

WHERE ic.object_id IS NULL

ORDER BY t.name

List SQL table hierarchically based on Foreign Key and Primary Key Relationship

The below query help us to list the table hierarchically based on Foreign Key and Primary key relationship:

WITH Fkeys AS (
    SELECT DISTINCT
         OnTable       = OnTable.name
        ,AgainstTable = AgainstTable.name
    FROM
        SYSFOREIGNKEYS fk
        INNER JOIN SYSOBJECTS onTable
            ON fk.fkeyid = onTable.id
        INNER JOIN SYSOBJECTS againstTable
            ON fk.rkeyid = againstTable.id
    WHERE 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        AND OnTable.Name <> AgainstTable.Name
    )
,MyData AS (
    SELECT
         OnTable = o.name
        ,AgainstTable = FKeys.againstTable
    FROM
        SYS.OBJECTS O
        LEFT JOIN FKeys
            ON o.name = FKeys.onTable
    WHERE 1=1
        AND o.type = 'U'
        AND o.name NOT LIKE 'sys%'
    )
,MyRecursion AS (
    -- base case
    SELECT
         TableName    = OnTable
        ,Lvl    = 1
    FROM
        MyData
    WHERE 1=1
        AND AgainstTable IS NULL
    -- recursive case
    UNION ALL SELECT
         TableName    = OnTable
        ,Lvl          = r.Lvl + 1
    FROM
        MyData d
        INNER JOIN MyRecursion r
            ON d.AgainstTable = r.TableName
)
SELECT
     ROW_NUMBER() OVER (ORDER BY MAX(lvl) ASC) AS SNO
     ,Lvl = MAX(Lvl)
    ,TableName
FROM
    MyRecursion
GROUP BY
    TableName
ORDER BY
     1 DESC
    ,2 DESC

T-SQL Query to get the SQL Agent job exectuted date time and duration

T-SQL Query to get the SQL Agent job exectuted date time and duration

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name,
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

T-Sql to Get the Database Restored Status

T-Sql to get the database restored status:

DECLARE @dbname SYSNAME
SET @dbname = 'ListSelect20'
SELECT Top 1
destination_database_name as 'Database Name',
[user_name] as 'Username',
CASE restore_type
WHEN NULL THEN 'NULL'
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log File'
WHEN 'V' THEN 'Verifyonly'
WHEN 'R' THEN 'Revert'
END as 'Restore Type',
Case [replace]
WHEN NULL THEN 'NULL'
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
END as 'Database Replaced',
restore_date as 'Date Restored'
,CASE WHEN CONVERT(VARCHAR(10), restore_date, 110) = CONVERT(VARCHAR(10), GETDATE(), 110) THEN 'Restored Today'
WHEN CONVERT(VARCHAR(10), restore_date, 110) = CONVERT(VARCHAR(10), GETDATE()-1, 110) THEN 'Restored Yesterday'
ELSE 'Restored Long Back: Needs immediate Restore' END AS RestoredDay
,CONVERT(VARCHAR(8), Restore_Date, 108) AS Restore_Time
FROM msdb..restorehistory
where destination_database_name = CASE
WHEN @dbname IS NOT NULL THEN @dbname
ELSE destination_database_name END
order by restore_date desc

SQL query to find the difference between two periods in hh:mm:ss format

DECLARE @StartTime DateTime = Getdate() -1, @EndTime DateTime = GetDate()

SELECTCONVERT(VARCHAR(6),DATEDIFF(SECOND, @STARTTIME, ISNULL(@ENDTIME,GETDATE()))/ 3600)

+':'+ RIGHT('0' + CONVERT(VARCHAR(6), (DATEDIFF(second, @STARTTIME, ISNULL(@ENDTIME,GETDATE())) % 3660) / 60), 2)

+':'+ RIGHT('0' + CONVERT(VARCHAR(5), DATEDIFF(second, @STARTTIME, ISNULL(@ENDTIME,GETDATE())) % 60),2) AS DURATION

Mahadevan Business Intelligence

Labels

Tuesday, 16 October 2012

ODBC Connectivity Issue - Missing System DSN connection in SSIS

Thursday, 27 September 2012

Attach MDF file to a Database

Attaching MDF file to a Database

Wednesday, 26 September 2012

Attaching .MDF file to a Database

Wednesday, 27 June 2012

Call SSRS Report in ASP .NET with Parameters

Monday, 18 June 2012

SQl query to database backup

SQL Query to drop all constraints in a table

Fastest Performing Rowcount SQL Query

Query to Backup and Restore Database

Active requests in MS SQL Database

Generating XML or Text file from T-SQL Query Output

Batch Script to execute SQL command and Save the output as text file

How to Identify Foreign Keys Without Index

List SQL table hierarchically based on Foreign Key and Primary Key Relationship

T-SQL Query to get the SQL Agent job exectuted date time and duration

T-Sql to Get the Database Restored Status

SQL query to find the difference between two periods in hh:mm:ss format

Total Pageviews

About Me