locked
execute .sql file and bind results to datagrid RRS feed

  • Question

  • User1344924657 posted

    I am trying to execute a sqlscript with c# to bind the data to a gridview. I am currently getting the error of An exception occurred while executing a Transact-SQL statement or batch. My code and sqlscript is below.

    C# Code

    string directo = HttpRuntime.AppDomainAppPath;
    string scriptfile = Path.Combine(directo, "Uploads\\DashboardQuery.sql");

    string sqlConnectionString = @"Connection string";

    string script = File.ReadAllText(scriptfile);

    SqlConnection conn = new SqlConnection(sqlConnectionString);

    Server server = new Server(new ServerConnection(conn));

    server.ConnectionContext.ExecuteNonQuery(script);

    DataSet ds = server.ConnectionContext.ExecuteWithResults("SELECT DATEPART(wk, [Start_Date]) week, [Call_Center], " +
    "CASE WHEN (SUM(NCT) + SUM(TotalConferences)) != 0 THEN CAST((SUM(TotaliCareTransfers) + SUM(TotaliCareConferences))/(SUM(NCT) + SUM(TotalConferences)) * 100 as DECIMAL(6,2)) " +
    "ELSE 0 End as [GR] " +
    "FROM #XsferSummaryReport " +
    "GROUP BY DATEPART(wk, [Start_Date]), [Call_Center]");
    GridView gv = new GridView();
    gv.DataSource = ds;
    gv.DataBind();

    ****SQL Script***

    USE VOIP

    DECLARE
    @Start_Date DATETIME,
    @End_Date DATETIME,
    @PeripheralID INT,
    @SiteName NVARCHAR(40),
    @SkillGroupSkillTargetID NVARCHAR (10) ,
    @CALL_CENTER NVARCHAR (50),
    @FA NVARCHAR (50),
    @ADVOC_NAME NVARCHAR (50),
    @TM_LEAD_NAME NVARCHAR (50),
    @MGR_NAME NVARCHAR (50),
    @SkillName NVARCHAR (40),
    @AgentLogin BIGINT,
    @CallDisposition INT,
    @SoftPhoneCallDisposition NVARCHAR(1) ,
    @Variable9 NVARCHAR (40),
    @VOIP_SiteID INT,
    @NCH INT ,
    @SFXsfers NUMERIC ,
    @HFXsfers NUMERIC ,
    @NumberOfDays INT,
    @ops_date DATETIME,
    @MonitorDelta INT

    -- how many days to pull at a time
    SET @NumberOfDays = '7'
    --- how long do we wait in seconds before monitoring software kicks in
    SET @MonitorDelta = '5'

    -- this sets the date to yesterday, if you need to go back further in time change it here

    Set @Start_Date = '4/10/2016'
    --SET @Start_Date = Convert(NVARCHAR,GETDATE()-@NumberOfDays,101)

    -- sets the end date to 1 day past yesterday

    SET @End_Date = DATEADD(DD,@NumberofDays,@Start_Date)
    -- Create a temp table that the report will be populated into

    CREATE TABLE #XsferRpt (
    Start_Date DATETIME,
    PeripheralID INT,
    SiteName NVARCHAR(40),
    CALL_CENTER VARCHAR (50),
    SkillGroupSkillTargetID NVARCHAR (10),
    SkillName NVARCHAR(40),
    FA VARCHAR (50),
    ADVOC_NAME NVARCHAR (50),
    TM_LEAD_NAME NVARCHAR (50),
    MGR_NAME NVARCHAR (50),
    AgentLogin NVARCHAR(10),
    VOIP_SiteID INT,
    CallDisposition INT,
    --Delta INT,
    NCH INT,
    [1 - FCN Transfer] INT,
    [3 - FCN Non-ECR Transfer] INT,
    [5 – FCN Make Call] INT,
    [7 – FCN Conference] INT,
    [9 – FCN Disaster Recovery] INT,
    [2 – Transfer (non-ICM)] INT,
    [4 – Manual Non-ECR Transfer] INT,
    [6 – Make Call] INT,
    [8 – Conference (non-ICM)] INT,
    [Total iCare Transfers] INT,
    [%iCare Transfers] FLOAT,
    [0 – Basic Compliance] INT,
    [%Basic Compliance] FLOAT,
    [Hardphone Transfers] FLOAT,
    [%Hardphone Transfers] FLOAT,
    [Total iCare Conferences] FLOAT,
    [Total Hardphone Conferences] FLOAT,
    [% Hardphone Conferences] FLOAT,
    [Non iCare Basic Compliance] INT,
    [FallThru] INT
    )

    -- now pull all the relevent infomation from TCD into a temp table so we are not locking up TCD for the entire run.
    SELECT
    --CAST(CONVERT(VARCHAR,tcd.DateTime,101) as DateTime) as ops_date,
    tcd.DateTime,
    tcd.SkillGroupSkillTargetID,
    tcd.AgentPeripheralNumber as AgentLogin,
    tcd.CallDisposition,
    Left(tcd.Variable9,1) as SoftPhoneCallDisposition,
    Variable9,
    tcd.PeripheralID,
    tcd.VOIP_SiteID,
    sg.PeripheralName as SkillName,
    s.EnterpriseName as SiteName,
    h.FUNCTIONAL_AREA as FA,
    h.ADVOC_NAME,
    h.TM_LEAD_NAME,
    h.MGR_NAME,
    x.CallCenterName 'CALL_CENTER',
    x.CallCenterID,
    tcd.ICRCallKeyParent ,
    tcd.PeripheralCallType,
    tcd.RouterCallKey,
    tcd.RouterCallKeyDay,
    p.ClientType,
    ct.EnterpriseName as MCT,
    (tcd.TalkTime - tcd.ConferenceTime) as Delta
    INTO #tempXsferRpt
    FROM
    Termination_Call_Detail tcd
    INNER JOIN Skill_Group sg
    ON
    tcd.SkillGroupSkillTargetID = sg.SkillTargetID
    AND
    tcd.VOIP_SiteID = sg.VOIP_SiteID
    AND
    sg.PeripheralID = tcd.PeripheralID
    inner Join
    Peripheral p
    on
    sg.PeripheralID = p.PeripheralID
    and
    sg.VOIP_SiteID = p.VOIP_SiteID
    Left JOIN Service s
    ON
    tcd.ServiceSkillTargetID = s.SkillTargetID
    and
    tcd.VOIP_SiteID = s.VOIP_SiteID
    and
    tcd.PeripheralID = s.PeripheralID
    INNER JOIN Call_Type ct -- should this be a Left join?
    ON
    tcd.CallTypeID = ct.CallTypeID
    and
    tcd.VOIP_SiteID = ct.VOIP_SiteID
    INNER JOIN
    voipreporting.dbo.cro_centernodetoperipheralxref x
    on x.PeripheralID = tcd.PeripheralID
    and x.Voip_SiteID = tcd.VOIP_SiteID
    INNER JOIN
    CRO_FULLHIERARCHY h
    ON
    tcd.AgentPeripheralNumber = h.ADVOC_EPID
    and
    h.CallCenterID = x.CallCenterID
    WHERE
    tcd.DateTime Between @Start_Date and @End_Date
    AND tcd.PeripheralCallType in (1,2,4,13,15)
    And tcd.PeripheralID in (
    --start of collections
    5123,5135,5160,5176,5193,5222,5240,5246,5249,5250,5254,5255,5260,5303,5307,5312,
    --start of caare
    5184,5195,5198,5199,5224,5243,5252,5253,5256,5257,5258,5304,5306,5247,
    --start of ppd
    5106,5108,5117,5118,5133,5222,5257,5257, 5199, 5195


    )
    and h.CALLCENTERID in (
    --start of collections
    165665,180304,331455,331456,331457,331459,331463,331464,340367,345239,399300,415925,418949,622335,756812,884694,884774,1005160,1090133,
    1103386,1118843,1119605,1140125,622336,
    --start of care
    6004,6013,6096,106541,150868,161627,176482,202402,228312,246489,246498,246503,250406,258068,261045,285533,
    288100,310289,326386,326387,354514,625278,736052,1042047,1078969,1084278,1119531,1119603,1119604,1132704,1148764,1168651,1239854,
    --start of ppd
    1211634, 246499, 251000, 485014, 535059, 803912, 827712, 827972, 949655,1066546,1075082,1211634,1140126
    )
    and tcd.VOIP_SiteID in (1000,3500)
    and tcd.[DateTime] between h.EFF_DT and h.TERM_DT


    CREATE CLUSTERED INDEX xfr on #tempXsferRpt (CALLCENTERID, AgentLogin, DateTime)

    SELECT
    CAST(CONVERT(VARCHAR,DateTime,101) as DateTime) as ops_date
    ,CALL_CENTER
    ,FA
    ,ADVOC_NAME
    ,TM_LEAD_NAME
    ,MGR_NAME
    ,SkillGroupSkillTargetID
    ,VOIP_SiteID
    ,PeripheralID
    ,AgentLogin
    ,COUNT(*) as 'NCH'
    ,CallDisposition
    ,SiteName
    ,SkillName
    ,'0' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '0' AND CallDisposition in (13)
    THEN 1
    WHEN SoftPhoneCallDisposition = '0' AND CallDisposition in (30) And ICRCallKeyParent IS NULL
    THEN 1
    ELSE 0
    END)
    )
    ,'1' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '1' AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1

    ELSE 0
    END)
    )
    ,'2' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '2' AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    WHEN SoftPhoneCallDisposition IN ('7','8') AND CallDisposition IN ( '29')
    THEN 1
    ELSE 0
    END)
    )
    ,'3' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '3' AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'4' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '4' AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'5' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '5' AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'6' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '6' AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'7' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '7' AND CallDisposition IN (99)
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'8' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '8' AND CallDisposition IN (99)
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'9' = (SUM (CASE
    WHEN SoftPhoneCallDisposition = '9' AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'TotaliCareTransfers' = (SUM (CASE
    WHEN SoftPhoneCallDisposition in ('1','2','3','4','5','6','9')
    AND CallDisposition IN ('28', '29', '30', '34', '55')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'TotalHardPhoneConferences' = (SUM (CASE
    WHEN SoftPhoneCallDisposition NOT IN ('1','2','3','4','5', '6','7','8','9')
    AND CallDisposition IN (99)
    And PeripheralCallType IN ('1','2','4','13','15')
    AND Delta not between (-1 * @MonitorDelta) and @MonitorDelta
    THEN 1
    ELSE 0
    END)
    )
    ,'TotalHardPhoneTransfers' = (SUM (CASE
    WHEN SoftPhoneCallDisposition NOT IN ('1','2','3','4','5', '6','7','8','9')
    And PeripheralCallType IN ('1','2','4','13','15')
    AND CallDisposition IN (28,29,34,55)
    THEN 1
    ELSE 0
    END)
    )
    ,'TotaliCareConferences' = (SUM (CASE
    WHEN SoftPhoneCallDisposition IN ('7','8')
    AND CallDisposition IN (99)
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'NoniCareBasicCompliance' = (SUM (CASE
    WHEN Variable9 not like '0,,iCare'
    AND CallDisposition IN (13)
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    ELSE 0
    END)
    )
    ,'Fallthru' = (SUM (CASE
    WHEN CallDisposition not in ('6','13', '28', '29', '30', '34', '54', '55', '57')
    And PeripheralCallType IN ('1','2','4','13','15')
    THEN 1
    WHEN CallDisposition IN('13', '28', '29', '34', '54', '55', '57')
    AND SoftPhoneCallDisposition IN ('7','8')
    THEN 1
    ELSE 0
    END)
    )
    ,'Consult' = (SUM (CASE
    WHEN SoftPhoneCallDisposition IN ('1','2','3','4','5', '6','9')
    AND CallDisposition IN (13)
    THEN 1
    ELSE 0
    END)
    )
    INTO #tmpXsferRptSummary
    FROM #tempXsferRpt
    GROUP BY
    CAST(CONVERT(VARCHAR,DateTime,101) as DateTime),
    FA,
    SkillGroupSkillTargetID,
    AgentLogin,
    PeripheralID,
    VOIP_SiteID,
    SiteName,
    SkillName,
    CallDisposition,
    CALL_CENTER,
    ADVOC_NAME,
    TM_LEAD_NAME,
    MGR_NAME

    CREATE CLUSTERED INDEX XFRRPT on #tmpXsferRptSummary (ops_date, AgentLogin, SkillGroupSkillTargetID)


    INSERT INTO #XsferRpt
    SELECT
    [ops_date] as Start_Date,
    [PeripheralID] as PeripheralID,
    [SiteName] as SiteName,
    [CALL_CENTER] as CALL_CENTER,
    [SkillGroupSkillTargetID] as SkillGroupSkillTargetID,
    [SkillName] as SkillName,
    [FA] as FA,
    [ADVOC_NAME] as ADVOC_NAME,
    [TM_LEAD_NAME] as TM_LEAD_NAME,
    [MGR_NAME] as MGR_NAME,
    [AgentLogin] as AgentLogin,
    [VOIP_SiteID] as VOIP_SiteID,
    [CallDisposition] as CallDisposition,
    [NCH] as NCH,
    [1] as [1 - FCN Transfer],
    [3] as [3 - FCN Non-ECR Transfer],
    [5] as [5 – FCN Make Call],
    [7] as [7 – FCN Conference],
    [9] as [9 – FCN Disaster Recovery],
    [2] as [2 – Transfer (non-ICM)],
    [4] as [4 – Manual Non-ECR Transfer],
    [6] as [6 – Make Call],
    [8] as [8 – Conference (non-ICM)],
    [TotaliCareTransfers] as [Total iCare Transfers],
    CAST((TotaliCareTransfers/NCH) as Decimal(6,2)) as [%iCare Transfers],
    [0] as [0 – Basic Compliance],
    CAST(([0]/[NCH])as DECIMAL(6,2)) as [%Basic Compliance],
    [TotalHardphoneTransfers] as [Hardphone Transfers],
    CAST((TotalHardphoneTransfers/NCH) as Decimal(6,2)) as [%Hardphone Transfers],
    [TotaliCareConferences] as [Total iCare Conferences],
    [TotalHardPhoneConferences] as [Total Hardphone Conferences] ,
    CAST((TotalHardPhoneConferences/NCH) as DECIMAL(6,2)) as [% Hardphone Conferences],
    [NoniCareBasicCompliance] as [Non iCare Basic Compliance],
    [FallThru] as [FallThru]
    FROM
    #tmpXsferRptSummary


    SELECT
    Start_Date
    ,PeripheralID
    ,SiteName
    ,SkillName
    ,CALL_CENTER
    ,FA
    ,ADVOC_NAME
    ,TM_LEAD_NAME
    ,MGR_NAME
    ,VOIP_SiteID
    ,CAST(SUM(NCH) as NUMERIC) as NCH
    ,CAST(SUM([1 - FCN Transfer] + [3 - FCN Non-ECR Transfer] + [2 – Transfer (non-ICM)] + [4 – Manual Non-ECR Transfer] + [Hardphone Transfers])as Numeric) as NCT
    ,CAST(SUM([7 – FCN Conference] + [8 – Conference (non-ICM)] + [Total Hardphone Conferences])as NUMERIC) as TotalConferences
    ,SUM([1 - FCN Transfer] + [3 - FCN Non-ECR Transfer] + [2 – Transfer (non-ICM)] + [4 – Manual Non-ECR Transfer]) as TotaliCareTransfers
    ,SUM([7 – FCN Conference] + [8 – Conference (non-ICM)]) as TotaliCareConferences
    ,SUM([0 – Basic Compliance]) as TotalBasicCompliance
    ,SUM([Non iCare Basic Compliance]) as TotalNoniCareBasicCompliance
    ,SUM([Hardphone Transfers]) as TotalHardphoneTransfers
    ,SUM([Total Hardphone Conferences]) as TotalHardphoneConferences
    ,SUM(FallThru) as FallThru
    INTO #XsferSummaryReport
    FROM #XsferRpt
    GROUP BY
    Start_Date
    ,SiteName
    ,SkillName
    ,PeripheralID
    ,VOIP_SiteID
    ,CALL_CENTER
    ,FA
    ,ADVOC_NAME
    ,TM_LEAD_NAME
    ,MGR_NAME

    -- ok time to clean up the mess we made in SQL ,
    -- if the query ends unexpectedly or is stopped manually , this will need to be run to clean up.

    Drop Table #tempXsferRpt
    Drop Table #XsferRpt
    Drop Table #XsferSummaryReport
    Drop Table #tmpXsferRptSummary

    Friday, May 6, 2016 7:38 PM

All replies

  • User1559292362 posted

    Hi cgreene.gsu,

    Could you please provide the SQL script (such as table named Termination_Call_Detail, Skill_Group,Peripheral,Service,Call_Type,cro_centernodetoperipheralxref, CRO_FULLHIERARCHY,etc) and the data

    In addition, please check if the sqlscript could be executed on your database?

    and I search for web sites, and find some similar threads as below, please check if it helps.

    https://support.software.dell.com/reporter/kb/57392

    http://wiki.idera.com/pages/viewpage.action?pageId=2164658

    Best regards,

    Cole Wu

    Monday, May 9, 2016 9:30 AM
  • User-2057865890 posted

    Hi cgreene.gsu,

    I am currently getting the error of An exception occurred while executing a Transact-SQL statement or batch.

    What's the error message? Based on your sql script, you use temp table, ensure that the sql executes correct and display data in sql server.

    See ADO.NET Code Examples

    The code listings in this topic demonstrate how to retrieve data from a database by using ADO.NET technologies.

    Best Regards,

    Chris

    Tuesday, May 31, 2016 9:38 AM