Mahadevan Business Intelligence

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

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

Total Pageviews

About Me