sql-server - SQL 查询在 SQL Server Management Studio 中运行良好，但是当我从 VBA 运行它时，它返回空白

Question

这是代码。有什么东西不能从 Excel 运行吗？我已经通过将文本输出到单元格并复制到 SQL Server Management Studio 并运行来测试 Excel 中的 SQL 代码，它工作正常。

提前致谢

Declare @GroupNumber as varchar(5)
Declare @GroupName as varchar(max)
Declare @ValuationDate as Date
Declare @GroupStartDate as Date
Declare @GroupAnniversayDate as Date
Declare @ValuationYRMO as varchar(6)
Declare @PlanCode as varchar(6)

--**************************Change Here*********
Set @GroupNumber = '01142'
Set @ValuationDate = '08-31-2019'
--**************************************************

Set @ValuationYRMO = convert(varchar(6),year(@ValuationDate)*100 + month(@ValuationDate))

--****************** Delete Temp Tables
IF OBJECT_ID('tempdb..#Census') IS NOT NULL
            drop table #Census
IF OBJECT_ID('tempdb..#RateTablePrepStep1') IS NOT NULL
            drop table #RateTablePrepStep1
IF OBJECT_ID('tempdb..#RateTablePrepStep2') IS NOT NULL
            drop table #RateTablePrepStep2
IF OBJECT_ID('tempdb..#RateTable') IS NOT NULL
            drop table #RateTable
IF OBJECT_ID('tempdb..##MembershipData') IS NOT NULL
            drop table ##MembershipData
IF OBJECT_ID('tempdb..#Membership') IS NOT NULL
            drop table #Membership
IF OBJECT_ID('tempdb..##ClaimData') IS NOT NULL
            drop table ##ClaimData
IF OBJECT_ID('tempdb..#Claims') IS NOT NULL
            drop table #Claims
IF OBJECT_ID('tempdb..##Completion') IS NOT NULL
            drop table ##Completion
IF OBJECT_ID('tempdb..#ValuationDates') IS NOT NULL
            drop table #ValuationDates
IF OBJECT_ID('tempdb..#PaymentStatus') IS NOT NULL
            drop table #PaymentStatus
IF OBJECT_ID('tempdb..#InvoiceData') IS NOT NULL
            drop table #InvoiceData
IF OBJECT_ID('tempdb..#AgentGroup') IS NOT NULL
            drop table #AgentGroup
IF OBJECT_ID('tempdb..#GroupEarnedPremium') IS NOT NULL
            drop table #GroupEarnedPremium
IF OBJECT_ID('tempdb..#EarnedPremium') IS NOT NULL
            drop table #EarnedPremium
IF OBJECT_ID('tempdb..#PaymentFrequency') IS NOT NULL
            drop table #PaymentFrequency
IF OBJECT_ID('tempdb..#InvoiceWOTax') IS NOT NULL
            drop table #InvoiceWOTax

--****************** Start Date and Anniversary Date
Set @GroupStartDate = (Select top 1 a.FirstEffectiveDate
                                    From [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a
                                    left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b on a.PolicyGroupId=b.PolicyGroupId
                                    where b.GroupNumber=@GroupNumber)
Set @GroupAnniversayDate = (Select top 1  a.RenewalDate
                                    From [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a
                                    left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b on a.PolicyGroupId=b.PolicyGroupId
                                    where b.GroupNumber=@GroupNumber)

--****************** Census
SELECT distinct b.GroupNumber,
                         Case when b.[Description] is null then pl.[name] else b.[description] end as [Group Name],
                         ps.[name] as [Group Status],
                         cc.[name] as [Group Country],
                         b1.GroupNumber as Subsidiary,
                         a2.PolicyId as [Certificate], 
                         ps2.[name] as [Certificate Status],
                         me.MemberId,
                         con.FirstName as [First Name],
                         con.LastName as [Last Name],
                         mt.[name] as [Member Type],
                         ms.[Name] as [Member Status],
                         con.DateOfBirth as [Date of Birth],
                         Case when @ValuationDate > = dateadd(year,year(@ValuationDate)-year(con.DateOfBirth),con.DateOfBirth)  
                                          then year(@ValuationDate)-year(con.DateOfBirth) 
                                          else year(@ValuationDate)-year(con.DateOfBirth) -1 end as Age,
                         ge.[name] as Gender,
                         pl.[Code],
                         pl.[Name] as [Plan Name],
                         po.[name] as [Plan Option]
  into #Census
  FROM [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a
  left join [Actuarial].CurrentMonth.[PolicyAdminPolicyCountry] pc on a.PolicyId=pc.PolicyId
  left join [Actuarial].[CurrentMonth].[GenericCountry] cc on pc.CountryId=cc.CountryId and pc.EndDate is null
  left join [Actuarial].[CurrentMonth].[GenericStatus] ps on a.StatusId=ps.StatusId
  left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b on a.PolicyGroupId=b.PolicyGroupId
  left join [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a1 on a.policyid=a1.ParentPolicyId
  left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b1 on a1.PolicyGroupId=b1.PolicyGroupId
  left join [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a2 on a1.policyid=a2.ParentPolicyId
  left join [Actuarial].[CurrentMonth].[GenericStatus] ps2 on a2.StatusId=ps2.StatusId
  left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyEligibility] pe on a2.policyid=pe.PolicyId
  left join [Actuarial].[CurrentMonth].[ProductConfigPlan] pl on pe.PlanId=pl.PlanId
  left join [Actuarial].[CurrentMonth].[PolicyAdminMemberEligibility] me on pe.PolicyEligibilityId=me.PolicyEligibilityId
  left join [Actuarial].[CurrentMonth].[GenericStatus] ms on me.StatusId=ms.StatusId
  left join [Actuarial].[CurrentMonth].[ProductConfigPlanOption] po on me.PlanOptionId=po.PlanOptionId
  left join [Actuarial].[CurrentMonth].[ProductConfigMemberType] mt on me.MemberTypeId=mt.MemberTypeId
  left join [Actuarial].[CurrentMonth].[PolicyAdminMember] mem on me.MemberId=mem.MemberId
  left join [Actuarial].[CurrentMonth].[ContactMgmtContact] con on mem.ContactId=con.ContactId
  left join [Actuarial].[CurrentMonth].[GenericGender] ge on con.GenderId=ge.GenderId

   where b.GroupNumber=@GroupNumber and pe.EndDate is null and me.EndDate is null
   order by b.groupnumber,b1.GroupNumber,a2.PolicyId

   select * from #census order by [GroupNumber],Subsidiary,[Plan Option],[Certificate]

我被困在如何进行。我已经用一个简单的前 10 名查询测试了我的记录连接，并且在 VBA 中运行良好。

这是我的 VBA 代码：

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset
    Dim ConnectionString As String
    Dim StrQueryBase As String
    Dim StrQueryBase2 As String
    Dim StrQueryBase3 As String
    Dim StrQueryGroupDate As String
    Dim StrQueryCensus As String
    Dim StrQueryCensus2 As String
    Dim StrQueryCensus3 As String
    Dim StrQueryTest As String

    ConnectionString = "Provider=SQLOLEDB; Data Source=bddc1didw1;Initial Catalog=Actuarial; Trusted_connection=Yes; Integrated Security='SSPI'"
    'Opens connection to the database
    cnn.Open ConnectionString

    'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 1500


    StrQueryBase = "Declare @GroupNumber as varchar(5) " & _
                    "Declare @GroupName as varchar(max) " & _
                    "Declare @ValuationDate as Date " & _
                    "Declare @GroupStartDate as Date " & _
                    "Declare @GroupAnniversayDate as Date " & _
                    "Declare @ValuationYRMO as varchar(6) " & _
                    "Declare @PlanCode as varchar(6) " & _
"Set @GroupNumber = '01142' " & _
"Set @ValuationDate = '08-31-2019'  " & _
"Set @ValuationYRMO = convert(varchar(6),year(@ValuationDate)*100 + month(@ValuationDate)) " & _
"IF OBJECT_ID('tempdb..#Census') IS NOT NULL " & _
            "drop table #Census " & _
"IF OBJECT_ID('tempdb..#RateTablePrepStep1') IS NOT NULL " & _
            "drop table #RateTablePrepStep1 " & _
"IF OBJECT_ID('tempdb..#RateTablePrepStep2') IS NOT NULL " & _
            "drop table #RateTablePrepStep2 " & _
"IF OBJECT_ID('tempdb..#RateTable') IS NOT NULL " & _
            "drop table #RateTable " & _
"IF OBJECT_ID('tempdb..##MembershipData') IS NOT NULL " & _
            "drop table ##MembershipData " & _
"IF OBJECT_ID('tempdb..#Membership') IS NOT NULL " & _
            "drop table #Membership "

    StrQueryBase2 = "IF OBJECT_ID('tempdb..##ClaimData') IS NOT NULL " & _
            "drop table ##ClaimData " & _
"IF OBJECT_ID('tempdb..#Claims') IS NOT NULL " & _
            "drop table #Claims " & _
"IF OBJECT_ID('tempdb..##Completion') IS NOT NULL " & _
            "drop table ##Completion " & _
"IF OBJECT_ID('tempdb..#ValuationDates') IS NOT NULL " & _
            "drop table #ValuationDates " & _
"IF OBJECT_ID('tempdb..#PaymentStatus') IS NOT NULL " & _
            "drop table #PaymentStatus " & _
"IF OBJECT_ID('tempdb..#InvoiceData') IS NOT NULL " & _
            "drop table #InvoiceData " & _
"IF OBJECT_ID('tempdb..#AgentGroup') IS NOT NULL " & _
            "drop table #AgentGroup " & _
"IF OBJECT_ID('tempdb..#GroupEarnedPremium') IS NOT NULL " & _
            "drop table #GroupEarnedPremium " & _
"IF OBJECT_ID('tempdb..#EarnedPremium') IS NOT NULL " & _
            "drop table #EarnedPremium " & _
"IF OBJECT_ID('tempdb..#PaymentFrequency') IS NOT NULL " & _
            "drop table #PaymentFrequency " & _
"IF OBJECT_ID('tempdb..#InvoiceWOTax') IS NOT NULL " & _
            "drop table #InvoiceWOTax " & _
"Set @GroupStartDate = (Select top 1 a.FirstEffectiveDate " & _
                                    "From [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a " & _
                                    "left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b on a.PolicyGroupId=b.PolicyGroupId "

StrQueryBase3 = "where b.GroupNumber=@GroupNumber) " & _
"Set @GroupAnniversayDate = (Select top 1  a.RenewalDate " & _
                                    "From [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a " & _
                                    "left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b on a.PolicyGroupId=b.PolicyGroupId " & _
                                    "where b.GroupNumber=@GroupNumber) "


   StrQueryCensus = "SELECT distinct b.GroupNumber, " & _
                         "Case when b.[Description] is null then pl.[name] else b.[description] end as [Group Name], " & _
                         "ps.[name] as [Group Status], " & _
                         "cc.[name] as [Group Country], " & _
                         "b1.GroupNumber as Subsidiary, " & _
                         "a2.PolicyId as [Certificate], " & _
                         "ps2.[name] as [Certificate Status], " & _
                         "me.MemberId, " & _
                         "con.FirstName as [First Name], " & _
                         "con.LastName as [Last Name], " & _
                         "mt.[name] as [Member Type], " & _
                         "ms.[Name] as [Member Status], " & _
                         "con.DateOfBirth as [Date of Birth], " & _
                         "Case when @ValuationDate > = dateadd(year,year(@ValuationDate)-year(con.DateOfBirth),con.DateOfBirth) " & _
                                          "then year(@ValuationDate)-year(con.DateOfBirth) " & _
                                          "else year(@ValuationDate)-year(con.DateOfBirth) -1 end as Age, " & _
                         "ge.[name] as Gender, " & _
                         "pl.[Code], " & _
                         "pl.[Name] as [Plan Name], " & _
                         "po.[name] as [Plan Option] " & _
  "into #Census " & _
  "FROM [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a " & _
  "left join [Actuarial].CurrentMonth.[PolicyAdminPolicyCountry] pc on a.PolicyId=pc.PolicyId " & _
  "left join [Actuarial].[CurrentMonth].[GenericCountry] cc on pc.CountryId=cc.CountryId and pc.EndDate is null "


   StrQueryCensus2 = "left join [Actuarial].[CurrentMonth].[GenericStatus] ps on a.StatusId=ps.StatusId " & _
  "left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b on a.PolicyGroupId=b.PolicyGroupId " & _
  "left join [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a1 on a.policyid=a1.ParentPolicyId " & _
  "left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyGroup] b1 on a1.PolicyGroupId=b1.PolicyGroupId " & _
  "left join [Actuarial].[CurrentMonth].[PolicyAdminPolicy] a2 on a1.policyid=a2.ParentPolicyId " & _
  "left join [Actuarial].[CurrentMonth].[GenericStatus] ps2 on a2.StatusId=ps2.StatusId " & _
  "left join [Actuarial].[CurrentMonth].[PolicyAdminPolicyEligibility] pe on a2.policyid=pe.PolicyId " & _
  "left join [Actuarial].[CurrentMonth].[ProductConfigPlan] pl on pe.PlanId=pl.PlanId " & _
  "left join [Actuarial].[CurrentMonth].[PolicyAdminMemberEligibility] me on pe.PolicyEligibilityId=me.PolicyEligibilityId " & _
  "left join [Actuarial].[CurrentMonth].[GenericStatus] ms on me.StatusId=ms.StatusId " & _
  "left join [Actuarial].[CurrentMonth].[ProductConfigPlanOption] po on me.PlanOptionId=po.PlanOptionId " & _
  "left join [Actuarial].[CurrentMonth].[ProductConfigMemberType] mt on me.MemberTypeId=mt.MemberTypeId " & _
  "left join [Actuarial].[CurrentMonth].[PolicyAdminMember] mem on me.MemberId=mem.MemberId " & _
  "left join [Actuarial].[CurrentMonth].[ContactMgmtContact] con on mem.ContactId=con.ContactId " & _
  "left join [Actuarial].[CurrentMonth].[GenericGender] ge on con.GenderId=ge.GenderId " & _
  "where b.GroupNumber=@GroupNumber and pe.EndDate is null and me.EndDate is null " & _
  "order by b.groupnumber,b1.GroupNumber,a2.PolicyId " & _
  "select * from #census order by [GroupNumber],Subsidiary,[Plan Option],[Certificate] "

  StrQueryCensus3 = StrQueryBase & StrQueryBase2 & StrQueryBase3 & StrQueryCensus & StrQueryCensus2 "

'
      ActiveSheet.Range("e1") = StrQueryCensus3




     rst2.Open StrQueryCensus3, cnn

    ActiveSheet.Range("e2").CopyFromRecordset rst2

标签： sql-servervbassms