-1

enter image description here

The below query works fine in SQL Management Studio and in Altiris. When we try to add the code to our custom reporting tool written in C#, we receive the error above.

Declare @AcroPro        int
Declare @AcroStan       int

set @AcroPro = (select COUNT(distinct guid) from vAdobe_Acrobat_Pro where [Display Name] like '%Professional')
set @AcroStan = (select COUNT(distinct guid) from vAdobe_Acrobat_Stan where [Display Name] like '%Standard')

select distinct v2.name 'Software Product', t2.[Purchased Licenses], CASE 

      When t2._resourceguid = '8708D8B8-51A0-4386-B4A1-620CC63DEF0D'
      Then @AcroPro
      When t2._resourceguid = '37997FB9-9936-42CF-A34C-D68214C353CF'
      Then @AcroStan
      End [Active Licenses], CASE 

      When t2._resourceguid = '8708D8B8-51A0-4386-B4A1-620CC63DEF0D'
      Then t2.[purchased licenses] - @AcroPro
      When t2._resourceguid = '37997FB9-9936-42CF-A34C-D68214C353CF'
      Then t2.[purchased licenses] -@AcroStan
      End [Compliance] from vComputer v1

inner join Inv_SoftwareProduct_InstallationInfo t1 on v1.Guid = t1._computerResourceGuid
inner join Inv_SoftwareProduct_ComplianceInfo t2 on t1._ResourceGuid = t2._ResourceGuid
inner join vSoftwareProduct v2 on t2._ResourceGuid = v2.guid

where v2.name in ('Adobe Acrobat Professional', 'Adobe Acrobat Standard')

and v1.IsManaged = '1'
sstan
  • 35,425
  • 6
  • 48
  • 66
user3009669
  • 51
  • 1
  • 8
  • 1
    Then apparently, the error has something to do with your custom reporting tool. But since you haven't posted any code related to that, there isn't much we can do for you. – sstan Jul 15 '16 at 17:11
  • Maybe you can try adding `GO` to the end and/or beginning of the query. – BJones Jul 15 '16 at 17:26
  • Use profiler to capture the actual SQL you're sending to the server. It should explain the error you get. – James Z Jul 15 '16 at 17:34

1 Answers1

2

Your reporting tool might be sending the same set of SQL commands twice in the same query batch, in which case those variables would be re-declared.

Mark Cidade
  • 98,437
  • 31
  • 224
  • 236
  • Thank you for the responses. We made an adjustment to where we were making the SQL call in C# and it's working now. – user3009669 Jul 18 '16 at 17:33