how do I replace @prompt with a list of PC's? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

how do I replace @prompt with a list of PC's?

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-06 : 18:19:21

someone had written this code on our sccm server (under reports)

select v_R_System.Netbios_Name0, v_GS_PC_BIOS.Manufacturer0, v_GS_PC_BIOS.SerialNumber0,v_GS_COMPUTER_SYSTEM.Model0
FROM v_R_System JOIN v_GS_PC_BIOS on v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
Where v_GS_PC_BIOS.SerialNumber0 = @serial

So you enter a serial number and it returns the computer name for that serial number. fun stuff.

but I have a list of about 800 machines so I don't want to run this report over 800 times. how would I modify this code so instead of a @Prompt I can just enter in a list of 800 computer names?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 18:25:02

change

v_GS_PC_BIOS.SerialNumber0 = @serial

to

v_GS_PC_BIOS.SerialNumber0 in ( query to get 800 machine ids here)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-06 : 18:31:19

thank you but that gives me a syntax error. Incorrect Syntax near: v_GS-PC_BIOS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 18:33:19

show your query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-06 : 18:45:28

select v_R_System.Netbios_Name0, v_GS_PC_BIOS.Manufacturer0, v_GS_PC_BIOS.SerialNumber0,v_GS_COMPUTER_SYSTEM.Model0
FROM v_R_System JOIN v_GS_PC_BIOS on v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

v_GS_PC_BIOS.SerialNumber0 in
(
'MXL71205PR',
'2UA7400P83',
'MXL70403JN'
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 19:09:20

quote:
Originally posted by mqh7

select v_R_System.Netbios_Name0, v_GS_PC_BIOS.Manufacturer0, v_GS_PC_BIOS.SerialNumber0,v_GS_COMPUTER_SYSTEM.Model0
FROM v_R_System JOIN v_GS_PC_BIOS on v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
WHERE
v_GS_PC_BIOS.SerialNumber0 in
(
'MXL71205PR',
'2UA7400P83',
'MXL70403JN'
)

you missed a WHERE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-07 : 09:38:09

That worked!! Awesome, thank you :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 09:47:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources