none
sqlDataAdapter returns 0 rows from SQL Views RRS feed

  • Question

  • Hello all,

    I am experiencing an interesting result when selecting from a sql view within my class. I get 0 rows returned, but it does return the proper columns. Details below.

    Code:

    public DataTable GetSQLResult(string sql)
     {
      DataTable dt = new DataTable();
      try
      {
      SqlConnection con = new SqlConnection(strConnection);
      SqlDataAdapter da = new SqlDataAdapter(sql, con);
      da.Fill(dt);
      
      return dt;
      }
      catch(Exception ex)
      {
      return dt;
      }
     }

     

    Variables

    • strConnection (values changed for security)
      • UID=sa;PWD=XXXXXX;Initial Catalog=databasename;Data Source=dbserver;
    • sql
      • select count(incidentid) as xCases, (datename(month,[createdon]) + datename(year,[createdon])) as 'xMonths' from filteredincident where accountidname like ('%"+CeriumInformation.GetSiteTitle()+"%') and createdon > dateadd(month,-11,GetDate()) group by (datename(month,[createdon]) + datename(year,[createdon])) ORDER BY MIN(createdon)

    Things I tried:

    • Simplfying my query to just "select top 10 * from filteredincident" returns same results
    • Query works as expected from with SQL Management Studio
    • Looking at the following article shows method should work as I am only selecting
      • http://support.microsoft.com/kb/316024
    • Doing a select directly from a table returns rows as expected.
    • Using SqlDataReader also returns 0 rows, columns do show

    I'm honestly at a lose here. Any help would be greatly appreciated.

    Thanks,

    Nate



    • Moved by eryang Friday, May 20, 2011 1:17 AM (From:.NET Base Class Library)
    Thursday, May 19, 2011 5:42 PM

Answers

All replies

  • Are you sure you are connecting to the correct database?

    Can you provide us with the script for the SQL view you are using?


    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Thursday, May 19, 2011 7:58 PM
  • Double checked, it is the correct database.
    As for the View, only changed the database name for security (see below). 
     
    USE [databasename]
    GO
    /****** Object: View [dbo].[FilteredIncident]  Script Date: 05/19/2011 14:35:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER view [dbo].[FilteredIncident] (
      accountid,
      accountiddsc,
      accountidname,
      accountidyominame,
      actualserviceunits,
      billedserviceunits,
      caseorigincode,
      caseorigincodename,
      casetypecode,
      casetypecodename,
      cerium_acknow_button,
      cerium_acknow_date, 
      cerium_acknow_dateutc,
      cerium_afterhours,
      cerium_afterhoursname,
      cerium_aux1,
      cerium_aux2,
      cerium_aux3,
      cerium_aux4,
      cerium_aux5,
      cerium_aux6,
      cerium_billingnotes,
      cerium_billingstatus,
      cerium_billingstatusname,
      cerium_casetype,
      cerium_casetypename,
      cerium_ceriumae,
      cerium_completeby, 
      cerium_completebyutc,
      cerium_contactemail,
      cerium_currentnotes,
      cerium_currentnotesbutton,
      cerium_currentnoteslong,
      cerium_currentnotessave,
      cerium_customerstatus,
      cerium_custref,
      cerium_dispatched,
      cerium_dispatchedname,
      cerium_hoursavaya,
      cerium_hourscisco,
      cerium_hourscustomer,
      cerium_hoursequip,
      cerium_hoursinternal,
      cerium_hoursinvoice,
      cerium_hourslec,
      cerium_hoursmonitor,
      cerium_hoursopen,
      cerium_hoursparty,
      cerium_hoursprogress,
      cerium_hoursscheduled,
      cerium_hoursverint,
      cerium_job_type,
      cerium_job_typename,
      cerium_partsordered,
      cerium_pid,
      cerium_po,
      cerium_previousstatus,
      cerium_previoustimestamp, 
      cerium_previoustimestamputc,
      cerium_prevlength,
      cerium_provisionedhours,
      cerium_vendor,
      contactid,
      contactiddsc,
      contactidname,
      contactidyominame,
      contractdetailid,
      contractdetailiddsc,
      contractdetailidname,
      contractid,
      contractiddsc,
      contractidname,
      contractservicelevelcode,
      contractservicelevelcodename,
      createdby,
      createdbydsc,
      createdbyname,
      createdbyyominame,
      createdon, 
      createdonutc,
      customerid,
      customeriddsc,
      customeridname,
      customeridtype,
      customersatisfactioncode,
      customersatisfactioncodename,
      description,
      followupby, 
      followupbyutc,
      importsequencenumber,
      incidentid,
      incidentstagecode,
      incidentstagecodename,
      invoke_address,
      invoke_billabletime,
      invoke_cause,
      invoke_city,
      invoke_contactname,
      invoke_customerrequesteddate, 
      invoke_customerrequesteddateutc,
      invoke_datescheduled, 
      invoke_datescheduledutc,
      invoke_descriptionofworkcompleted,
      invoke_descriptionofworkrequested,
      invoke_disposition,
      invoke_fax,
      invoke_labortime,
      invoke_maintel,
      invoke_number,
      invoke_partsordered,
      invoke_po,
      invoke_projectmanager,
      invoke_projectmanagername,
      invoke_resolution,
      invoke_resolutiontype,
      invoke_salesorderno,
      invoke_serviceordertype,
      invoke_serviceordertypename,
      invoke_shippingto,
      invoke_shippingvendor,
      invoke_solutionid,
      invoke_solutioniddsc,
      invoke_solutionidname,
      invoke_telephone,
      invoke_ticketno,
      invoke_totaltime,
      invoke_trackingnumber,
      invoke_traveltime,
      invoke_vendor,
      invoke_zip,
      isdecrementing,
      isdecrementingname,
      kbarticleid,
      kbarticleiddsc,
      kbarticleidname,
      modifiedby,
      modifiedbydsc,
      modifiedbyname,
      modifiedbyyominame,
      modifiedon, 
      modifiedonutc,
      new_state,
      new_statename,
      overriddencreatedon, 
      overriddencreatedonutc,
      ownerid,
      owneriddsc,
      owneridname,
      owneridtype,
      owneridyominame,
      owningbusinessunit,
      owninguser,
      prioritycode,
      prioritycodename,
      productid,
      productiddsc,
      productidname,
      productserialnumber,
      responsiblecontactid,
      responsiblecontactiddsc,
      responsiblecontactidname,
      responsiblecontactidyominame,
      severitycode,
      severitycodename,
      statecode,
      statecodename,
      statuscode,
      statuscodename,
      subjectid,
      subjectiddsc,
      subjectidname,
      ticketnumber,
      timezoneruleversionnumber,
      title,
      utcconversiontimezonecode
    ) with view_metadata as 
    select 
      Incident.AccountId,
      Incident.AccountIdDsc,
      Incident.AccountIdName,
      Incident.AccountIdYomiName,
      Incident.ActualServiceUnits,
      Incident.BilledServiceUnits,
      Incident.CaseOriginCode,
      CaseOriginCodePLTable.Value,
      Incident.CaseTypeCode,
      CaseTypeCodePLTable.Value,
      Incident.Cerium_acknow_button,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.Cerium_acknow_date,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.Cerium_acknow_date,
      Incident.Cerium_afterhours,
      Cerium_afterhoursPLTable.Value,
      Incident.Cerium_aux1,
      Incident.Cerium_aux2,
      Incident.Cerium_aux3,
      Incident.Cerium_aux4,
      Incident.Cerium_aux5,
      Incident.Cerium_aux6,
      Incident.Cerium_BillingNotes,
      Incident.Cerium_BillingStatus,
      Cerium_BillingStatusPLTable.Value,
      Incident.Cerium_CaseType,
      Cerium_CaseTypePLTable.Value,
      Incident.Cerium_CeriumAE,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.Cerium_CompleteBy,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.Cerium_CompleteBy,
      Incident.Cerium_ContactEmail,
      Incident.Cerium_currentnotes,
      Incident.Cerium_currentnotesbutton,
      Incident.Cerium_currentnoteslong,
      Incident.Cerium_currentnotessave,
      Incident.Cerium_CustomerStatus,
      Incident.Cerium_custRef,
      Incident.Cerium_Dispatched,
      Cerium_DispatchedPLTable.Value,
      Incident.Cerium_hoursavaya,
      Incident.Cerium_hourscisco,
      Incident.Cerium_hourscustomer,
      Incident.Cerium_hoursequip,
      Incident.Cerium_hoursinternal,
      Incident.Cerium_hoursinvoice,
      Incident.Cerium_hourslec,
      Incident.Cerium_hoursmonitor,
      Incident.Cerium_hoursopen,
      Incident.Cerium_hoursparty,
      Incident.Cerium_hoursprogress,
      Incident.Cerium_hoursscheduled,
      Incident.Cerium_hoursverint,
      Incident.Cerium_Job_Type,
      Cerium_Job_TypePLTable.Value,
      Incident.Cerium_PartsOrdered,
      Incident.Cerium_PID,
      Incident.Cerium_PO,
      Incident.Cerium_previousstatus,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.Cerium_previoustimestamp,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.Cerium_previoustimestamp,
      Incident.Cerium_prevlength,
      Incident.Cerium_ProvisionedHours,
      Incident.Cerium_Vendor,
      Incident.ContactId,
      Incident.ContactIdDsc,
      Incident.ContactIdName,
      Incident.ContactIdYomiName,
      Incident.ContractDetailId,
      Incident.ContractDetailIdDsc,
      Incident.ContractDetailIdName,
      Incident.ContractId,
      Incident.ContractIdDsc,
      Incident.ContractIdName,
      Incident.ContractServiceLevelCode,
      ContractServiceLevelCodePLTable.Value,
      Incident.CreatedBy,
      Incident.CreatedByDsc,
      Incident.CreatedByName,
      Incident.CreatedByYomiName,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.CreatedOn,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.CreatedOn,
      Incident.CustomerId,
      Incident.CustomerIdDsc,
      Incident.CustomerIdName,
      Incident.CustomerIdType,
      Incident.CustomerSatisfactionCode,
      CustomerSatisfactionCodePLTable.Value,
      Incident.Description,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.FollowupBy,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.FollowupBy,
      Incident.ImportSequenceNumber,
      Incident.IncidentId,
      Incident.IncidentStageCode,
      IncidentStageCodePLTable.Value,
      Incident.Invoke_Address,
      Incident.Invoke_BillableTime,
      Incident.Invoke_Cause,
      Incident.Invoke_City,
      Incident.Invoke_ContactName,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.Invoke_CustomerRequestedDate,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.Invoke_CustomerRequestedDate,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.Invoke_DateScheduled,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.Invoke_DateScheduled,
      Incident.Invoke_DescriptionofWorkCompleted,
      Incident.Invoke_DescriptionofWorkRequested,
      Incident.Invoke_Disposition,
      Incident.Invoke_Fax,
      Incident.Invoke_LaborTime,
      Incident.Invoke_MainTel,
      Incident.Invoke_Number,
      Incident.Invoke_PartsOrdered,
      Incident.Invoke_PO,
      Incident.Invoke_ProjectManager,
      Invoke_ProjectManagerPLTable.Value,
      Incident.Invoke_Resolution,
      Incident.Invoke_ResolutionType,
      Incident.Invoke_SalesOrderNo,
      Incident.Invoke_ServiceOrderType,
      Invoke_ServiceOrderTypePLTable.Value,
      Incident.Invoke_ShippingTo,
      Incident.Invoke_ShippingVendor,
      Incident.invoke_solutionid,
      Incident.invoke_solutionidDsc,
      Incident.invoke_solutionidName,
      Incident.Invoke_Telephone,
      Incident.Invoke_TicketNo,
      Incident.Invoke_TotalTime,
      Incident.Invoke_TrackingNumber,
      Incident.Invoke_TravelTime,
      Incident.Invoke_Vendor,
      Incident.Invoke_Zip,
      Incident.IsDecrementing,
      IsDecrementingPLTable.Value,
      Incident.KbArticleId,
      Incident.KbArticleIdDsc,
      Incident.KbArticleIdName,
      Incident.ModifiedBy,
      Incident.ModifiedByDsc,
      Incident.ModifiedByName,
      Incident.ModifiedByYomiName,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.ModifiedOn,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.ModifiedOn,
      Incident.New_State,
      New_StatePLTable.Value,
      dbo.fn_UTCToTzSpecificLocalTime(Incident.OverriddenCreatedOn,
    			us.TimeZoneBias,
    			us.TimeZoneDaylightBias,
    			us.TimeZoneDaylightYear,
    			us.TimeZoneDaylightMonth,
    			us.TimeZoneDaylightDay,
    			us.TimeZoneDaylightHour,
    			us.TimeZoneDaylightMinute,
    			us.TimeZoneDaylightSecond,
    			0,
    			us.TimeZoneDaylightDayOfWeek,
    			us.TimeZoneStandardBias,
    			us.TimeZoneStandardYear,
    			us.TimeZoneStandardMonth,
    			us.TimeZoneStandardDay,
    			us.TimeZoneStandardHour,
    			us.TimeZoneStandardMinute,
    			us.TimeZoneStandardSecond,
    			0,
    			us.TimeZoneStandardDayOfWeek), 
      Incident.OverriddenCreatedOn,
      Incident.OwnerId,
      Incident.OwnerIdDsc,
      Incident.OwnerIdName,
      Incident.OwnerIdType,
      Incident.OwnerIdYomiName,
      Incident.OwningBusinessUnit,
      Incident.OwningUser,
      Incident.PriorityCode,
      PriorityCodePLTable.Value,
      Incident.ProductId,
      Incident.ProductIdDsc,
      Incident.ProductIdName,
      Incident.ProductSerialNumber,
      Incident.ResponsibleContactId,
      Incident.ResponsibleContactIdDsc,
      Incident.ResponsibleContactIdName,
      Incident.ResponsibleContactIdYomiName,
      Incident.SeverityCode,
      SeverityCodePLTable.Value,
      Incident.StateCode,
      StateCodePLTable.Value,
      Incident.StatusCode,
      StatusCodePLTable.Value,
      Incident.SubjectId,
      Incident.SubjectIdDsc,
      Incident.SubjectIdName,
      Incident.TicketNumber,
      Incident.TimeZoneRuleVersionNumber,
      Incident.Title,
      Incident.UTCConversionTimeZoneCode
    from Incident
      left join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
      left join UserSettingsBase us on us.SystemUserId = u.SystemUserId
      left join OrganizationBase o on u.OrganizationId = o.OrganizationId
      left outer join StringMap CaseOriginCodePLTable on 
        (CaseOriginCodePLTable.AttributeName = 'caseorigincode'
        and CaseOriginCodePLTable.ObjectTypeCode = 112
        and CaseOriginCodePLTable.AttributeValue = Incident.CaseOriginCode
        and CaseOriginCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap CaseTypeCodePLTable on 
        (CaseTypeCodePLTable.AttributeName = 'casetypecode'
        and CaseTypeCodePLTable.ObjectTypeCode = 112
        and CaseTypeCodePLTable.AttributeValue = Incident.CaseTypeCode
        and CaseTypeCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap Cerium_afterhoursPLTable on 
        (Cerium_afterhoursPLTable.AttributeName = 'cerium_afterhours'
        and Cerium_afterhoursPLTable.ObjectTypeCode = 112
        and Cerium_afterhoursPLTable.AttributeValue = Incident.Cerium_afterhours
        and Cerium_afterhoursPLTable.LangId = us.UILanguageId )
      left outer join StringMap Cerium_BillingStatusPLTable on 
        (Cerium_BillingStatusPLTable.AttributeName = 'cerium_billingstatus'
        and Cerium_BillingStatusPLTable.ObjectTypeCode = 112
        and Cerium_BillingStatusPLTable.AttributeValue = Incident.Cerium_BillingStatus
        and Cerium_BillingStatusPLTable.LangId = us.UILanguageId )
      left outer join StringMap Cerium_CaseTypePLTable on 
        (Cerium_CaseTypePLTable.AttributeName = 'cerium_casetype'
        and Cerium_CaseTypePLTable.ObjectTypeCode = 112
        and Cerium_CaseTypePLTable.AttributeValue = Incident.Cerium_CaseType
        and Cerium_CaseTypePLTable.LangId = us.UILanguageId )
      left outer join StringMap Cerium_DispatchedPLTable on 
        (Cerium_DispatchedPLTable.AttributeName = 'cerium_dispatched'
        and Cerium_DispatchedPLTable.ObjectTypeCode = 112
        and Cerium_DispatchedPLTable.AttributeValue = Incident.Cerium_Dispatched
        and Cerium_DispatchedPLTable.LangId = us.UILanguageId )
      left outer join StringMap Cerium_Job_TypePLTable on 
        (Cerium_Job_TypePLTable.AttributeName = 'cerium_job_type'
        and Cerium_Job_TypePLTable.ObjectTypeCode = 112
        and Cerium_Job_TypePLTable.AttributeValue = Incident.Cerium_Job_Type
        and Cerium_Job_TypePLTable.LangId = us.UILanguageId )
      left outer join StringMap ContractServiceLevelCodePLTable on 
        (ContractServiceLevelCodePLTable.AttributeName = 'contractservicelevelcode'
        and ContractServiceLevelCodePLTable.ObjectTypeCode = 112
        and ContractServiceLevelCodePLTable.AttributeValue = Incident.ContractServiceLevelCode
        and ContractServiceLevelCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap CustomerSatisfactionCodePLTable on 
        (CustomerSatisfactionCodePLTable.AttributeName = 'customersatisfactioncode'
        and CustomerSatisfactionCodePLTable.ObjectTypeCode = 112
        and CustomerSatisfactionCodePLTable.AttributeValue = Incident.CustomerSatisfactionCode
        and CustomerSatisfactionCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap IncidentStageCodePLTable on 
        (IncidentStageCodePLTable.AttributeName = 'incidentstagecode'
        and IncidentStageCodePLTable.ObjectTypeCode = 112
        and IncidentStageCodePLTable.AttributeValue = Incident.IncidentStageCode
        and IncidentStageCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap Invoke_ProjectManagerPLTable on 
        (Invoke_ProjectManagerPLTable.AttributeName = 'invoke_projectmanager'
        and Invoke_ProjectManagerPLTable.ObjectTypeCode = 112
        and Invoke_ProjectManagerPLTable.AttributeValue = Incident.Invoke_ProjectManager
        and Invoke_ProjectManagerPLTable.LangId = us.UILanguageId )
      left outer join StringMap Invoke_ServiceOrderTypePLTable on 
        (Invoke_ServiceOrderTypePLTable.AttributeName = 'invoke_serviceordertype'
        and Invoke_ServiceOrderTypePLTable.ObjectTypeCode = 112
        and Invoke_ServiceOrderTypePLTable.AttributeValue = Incident.Invoke_ServiceOrderType
        and Invoke_ServiceOrderTypePLTable.LangId = us.UILanguageId )
      left outer join StringMap IsDecrementingPLTable on 
        (IsDecrementingPLTable.AttributeName = 'isdecrementing'
        and IsDecrementingPLTable.ObjectTypeCode = 112
        and IsDecrementingPLTable.AttributeValue = Incident.IsDecrementing
        and IsDecrementingPLTable.LangId = us.UILanguageId )
      left outer join StringMap New_StatePLTable on 
        (New_StatePLTable.AttributeName = 'new_state'
        and New_StatePLTable.ObjectTypeCode = 112
        and New_StatePLTable.AttributeValue = Incident.New_State
        and New_StatePLTable.LangId = us.UILanguageId )
      left outer join StringMap PriorityCodePLTable on 
        (PriorityCodePLTable.AttributeName = 'prioritycode'
        and PriorityCodePLTable.ObjectTypeCode = 112
        and PriorityCodePLTable.AttributeValue = Incident.PriorityCode
        and PriorityCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap SeverityCodePLTable on 
        (SeverityCodePLTable.AttributeName = 'severitycode'
        and SeverityCodePLTable.ObjectTypeCode = 112
        and SeverityCodePLTable.AttributeValue = Incident.SeverityCode
        and SeverityCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap StateCodePLTable on 
        (StateCodePLTable.AttributeName = 'statecode'
        and StateCodePLTable.ObjectTypeCode = 112
        and StateCodePLTable.AttributeValue = Incident.StateCode
        and StateCodePLTable.LangId = us.UILanguageId )
      left outer join StringMap StatusCodePLTable on 
        (StatusCodePLTable.AttributeName = 'statuscode'
        and StatusCodePLTable.ObjectTypeCode = 112
        and StatusCodePLTable.AttributeValue = Incident.StatusCode
        and StatusCodePLTable.LangId = us.UILanguageId )
      cross join dbo.fn_GetMaxPrivilegeDepthMask(112) pdm
    where Incident.DeletionStateCode in (0) 
      and
      (
      -- privilege check
      pdm.PrivilegeDepthMask is not null and
      (
      -- object owned by the user
      --
      Incident.OwningUser = u.SystemUserId
      -- role based access
      or 
      exists
    (
    	select 
    		1
    	where
    	(
    		-- deep security
    		((pdm.PrivilegeDepthMask & 0x44) != 0) and 
    		Incident.OwningBusinessUnit in (select biz.SubBusinessId from BusinessUnitMap as biz where biz.BusinessId = u.BusinessUnitId)
    	) 
    	or
    	(
    		-- global security
    		((pdm.PrivilegeDepthMask & 0x88) != 0) and 
    		Incident.OwningBusinessUnit is not null 
    	) 
    	or
    	(
    		-- local security
    		((pdm.PrivilegeDepthMask & 0x22) != 0) and 
    		Incident.OwningBusinessUnit = u.BusinessUnitId
    	)
    )
    
      -- object shared to the user
      or 
      Incident.IncidentId in 
      (
        select POA.ObjectId from PrincipalObjectAccess POA 
        join SystemUserPrincipals sup on POA.PrincipalId = sup.PrincipalId
          where sup.SystemUserId = u.SystemUserId and
            POA.ObjectTypeCode = 112 AND 
            ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
      )
      )
      )
    
     
    Ran sql profiler and confirmed that the database is being hit with the the following trace output. Change user and accountname for security.
    SQL:BatchStarting select count(incidentid) as xCases, (datename(month,[createdon]) + datename(year,[createdon])) as 'xMonths' from filteredincident where accountidname like ('%accountname%') and createdon > dateadd(month,-11,GetDate()) group by (datename(month,[createdon]) + datename(year,[createdon])) ORDER BY MIN(createdon) .Net SqlClient Data Provider  userX     4528 88 2011-05-19 13:42:11.437   


    Thursday, May 19, 2011 8:52 PM
  • BTW, that is a built in View that comes with MSCRM4.0.
    Thursday, May 19, 2011 9:37 PM
  •  

    Hi DevNate,

    Welcome!

    I think you can have a try to add a new item(DataSet) to your solution, then drop the view from Server Explorer, you can right click the viewTable to Preview data to see whether is there any records.

    Based on your code, I couldn't figure any errors out.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 20, 2011 6:47 AM
    Moderator
  • Thanks for looking at this. I have recieved my answer from the ASP.Net forums. Link below to the thread.

    http://forums.asp.net/t/1682435.aspx/1?sqlDataAdapter+returns+0+rows+from+SQL+View 

    • Marked as answer by DevNate Friday, May 20, 2011 4:42 PM
    Friday, May 20, 2011 4:42 PM