Download SQL Query
 
The Query :

select sys1.Name, sys1.DefaultSiteCode,

(select SUBSTRING(sys2.ServerNALPath, CHARINDEX(‘\\’, sys2.ServerNALPath) + 2,

CHARINDEX(‘”]’, sys2.ServerNALPath) – CHARINDEX(‘\\’, sys2.ServerNALPath) – 3 ) +

CASE sys2.Flags WHEN ‘1’ Then ‘ (Slow)’ WHEN ‘0’ THEN ” END + ‘; ‘ as ‘data()’

from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID

for XML path(”)) as ‘Site System’,

(select sys4.Value + ‘; ‘ as ‘data()’ from vSMS_BoundaryGroupMembers as sys3

left join vSMS_Boundary as sys4 on sys3.BoundaryID=sys4.BoundaryID where sys1.GroupID=sys3.GroupID

for XML path(”)) as ‘Boundary’, sys1.ModifiedOn, sys1.ModifiedBy

from vSMS_BoundaryGroup as sys1


Output :

Name
DefaultSiteCode
Site System
Boundary
ModifiedOn
ModifiedBy
Bahrain
EME
bahrain.contoso.com;
Bahrain – Wifi
26:20.0
contoso\admin1
India
APA
india.contoso.com;
India
06:35.0
contoso\admin2
London
EME
london.contoso.com;
London Extranet
52:51.0
contoso\admin3
New York
AME
newyork.contoso.com;
New York Wifi
55:54.0
contoso\admin4
Lima
SME
lima.contoso.com;
Lima Extranet
56:29.0
contoso\admin5
Moscow
EME
moscow.contoso.com;
Moscow
55:47.0
contoso\admin6
Advertisements

Join the conversation! 2 Comments

  1. why am i getting the following error when try to run from SQL:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘–’.
    Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword ‘for’

    Like

    Reply
  2. download the .sql file – will clear all the formatting confusion.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Category

Uncategorized

Tags

, , , , ,