Declaring Scalar Variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Declaring Scalar Variables

Author

Topic

mjmj75
Starting Member

5 Posts

Posted - 2007-03-28 : 12:21:01

I am trying to get reports to run in SMS which is using SQL in the background. I copied a statement that runs correctly from one server which is listed below. When I posted this in another server, I received the error "Must declare the scalar variable "@Bulletin ID"
@CollID"

I don't understand why it won't work, I just copied the text from one server to the other.

Please HELP!

declare @Total int, /* total count collection membership */
@SMSInstall int, /* count installed by SMS */
@OtherInstall int, /* count installed externally */
@Missing int, /* count missing patch */
@NotRequired int, /* count not requiring patch */
@Required int, /* count requiring patch */
@Outstanding int /* count outstanding */

/* count non-obsolete clients */
select @Total=count(*)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
where IsNull(sys.Obsolete0,0)=0 and sys.Client0=1 and fcm.CollectionID=@CollID

/* patches installed by SMS */
/* patches installed by others */
/* patches required by systems */
/* v_GS_PatchStatusEx already filters out obsolete clients */
select @SMSInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is not null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is not null and ps2.LastState=105 then ps2.ResourceID
else null end),
@OtherInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is null and ps2.LastState=105 then ps2.ResourceID
else null end),
@Missing=count(distinct case
when ps1.LastState is not null and ps1.LastState!=105 then ps1.ResourceID
when ps1.LastState is null and ps2.LastState is not null and ps2.LastState!=105 then ps2.ResourceID
else null end),
@Required=count(distinct case
when ps1.ResourceID is null then ps2.ResourceID else ps1.ResourceID end)
from (select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID=@BulletinID and QNumbers=@QNumber and
UniqueUpdateID is not null) ps1
full outer join
(select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID=@BulletinID and QNumbers=@QNumber and
UniqueUpdateID is null) ps2
on ps1.ResourceID=ps2.ResourceID
join v_FullCollectionMembership fcm
on (ps2.ResourceID is null and ps1.ResourceID=fcm.ResourceID) or
(ps1.ResourceID is null and ps2.ResourceID=fcm.ResourceID) or
(ps1.ResourceID=fcm.ResourceID and ps2.ResourceID=fcm.ResourceID)
where fcm.CollectionID=@CollID

/* not requiring patch */
select @NotRequired=count(distinct fcm.ResourceID)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
join v_GS_SCANPACKAGEVERSION spv on fcm.ResourceID=spv.ResourceID
join (select upkg.PackageID, max(upkg.PackageVersion) as PackageVersion
from v_ApplicableUpdatesSummaryEx us
join v_UpdatePrograms upkg on us.UpdateID=upkg.UpdateID
where us.ID=@BulletinID and us.QNumbers=@QNumber and upkg.PackageType=1
group by upkg.PackageID) updpkg
on spv.PackageID0=updpkg.PackageID and spv.PackageVer0>=updpkg.PackageVersion
left join (select ResourceID
from v_GS_PatchStatusEx
where ID=@BulletinID and QNumbers=@QNumber) ps
on fcm.ResourceID=ps.ResourceID
where fcm.CollectionID=@CollID and
ps.ResourceID is null and IsNull(sys.Obsolete0,0)=0 and sys.Client0=1

/* outstanding computers */
Select @Outstanding=@Total-(@NotRequired+@Required)

select @Total as 'Computers in collection'
select @Required as 'Computers requiring update', 100*@Required/@Total as '% of Total'
select @SMSInstall as 'Computers updated by SMS', 100*@SMSInstall/@Total as '% of Total'
select @OtherInstall as 'Computers updated by external means', 100*@OtherInstall/@Total as '% of Total'
select @SMSInstall+@OtherInstall as 'Total computers updated', 100*(@SMSInstall+@OtherInstall)/@Total as '% of Total'
select @Missing as 'Computers missing update', 100*@Missing/@Total as '% of Total'
select @NotRequired as 'Computers not requiring update', 100*@NotRequired/@Total as '% of Total'
select @Outstanding as 'Outstanding computers', 100*@Outstanding/@Total as '% of Total'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-28 : 12:23:04

you have to declare the @BulletinID and @CollID in your code.

mjmj75
Starting Member

5 Posts

Posted - 2007-03-28 : 13:16:57

Forgive me, I am new to SQL scripting, could you please provide me an example?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-28 : 13:33:26

what is the data type for these 2 variable ? Integer ?


declare 
    @BulletinID  int,
    @ColID       int

mjmj75
Starting Member

5 Posts

Posted - 2007-03-28 : 14:03:15

Thanks now the report are generating. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources