SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Blank report w/ headers


Blank report w/ headers

Author
Message
maravig
maravig
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 60
I have a report with a prompt for collection that produces the headers, but no data. I've been going in circles. Here is my code:
Dataset1
select distinct v_R_System.Netbios_Name0 as 'Computer Name',
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 as 'IP Address',
v_R_System.User_Name0 as 'User Name',
v_R_User.Full_User_Name0 as 'Full User Name',
v_R_System.AD_Site_Name0 as 'Site'
from v_R_System
inner join
v_RA_System_IPAddresses on
v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceId
join
v_R_User on
v_R_User.User_Name0 = v_R_System.User_Name0
join
v_GS_NETWORK_ADAPTER_CONFIGURATION on
v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID = v_RA_System_IPAddresses.ResourceID
left join v_FullCollectionMembership on
v_FullCollectionMembership.resourceid = v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID
join v_Collection on
v_Collection.CollectionID = v_FullCollectionMembership.CollectionID
where (v_FullCollectionMembership.CollectionID = @collid ) AND v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0
NOT LIKE 'fe%' AND IPAddress0 NOT LIKE '%::%' AND IPAddress0 NOT LIKE '169%'
group by
v_R_System.Netbios_Name0,
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0,
v_R_System.User_Name0,
v_R_User.Full_User_Name0,
v_R_System.AD_Site_Name0
order by v_R_System.Netbios_Name0

Dataset2
SELECT CollectionID, Name FROM v_Collection order by Name



Thanks for any help
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165953 Visits: 22826
What do you expect us to do with that? The only thing I can offer is to format the code, but not much more.

select distinct
S.Netbios_Name0 as 'Computer Name',
N.IPAddress0 as 'IP Address',
S.User_Name0 as 'User Name',
U.Full_User_Name0 as 'Full User Name',
S.AD_Site_Name0 as 'Site'
from v_R_System S
join v_RA_System_IPAddresses I on I.ResourceID = S.ResourceId
join v_R_User U on U.User_Name0 = S.User_Name0
join v_GS_NETWORK_ADAPTER_CONFIGURATION N on N.ResourceID = I.ResourceID
left join v_FullCollectionMembership M on M.resourceid = N.ResourceID
join v_Collection C on C.CollectionID = M.CollectionID
where (M.CollectionID = @collid )
AND N.IPAddress0 NOT LIKE 'fe%'
AND N.IPAddress0 NOT LIKE '%::%'
AND N.IPAddress0 NOT LIKE '169%'
group by
S.Netbios_Name0,
N.IPAddress0,
S.User_Name0,
U.Full_User_Name0,
S.AD_Site_Name0
order by S.Netbios_Name0

The only thing that seems to be an issue, is that the LEFT JOIN is being converted to an INNER JOIN by your WHERE clause. However, that's your only filter, so there's no reason to make it an (LEFT) OUTER JOIN.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
maravig
maravig
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 60
What else do you require? screenshots? the Parameter settings? My apologies, I'm a SQL coding newbie
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165953 Visits: 22826
maravig - Friday, February 24, 2017 9:56 AM
What else do you require? screenshots? the Parameter settings? My apologies, I'm a SQL coding newbie
There's no way to know why are you not getting results. The query seems fine, but you might be missing data. Although, you have a DISTINCT and GROUP BY which shouldn't be needed anyway.
You need to be sure that you should get results with the parameters that you're using.
You're not using v_RA_System_IPAddresses or v_Collection so there's no reason to have them in the query unless the DB is missing Foreign Keys.


SELECT S.Netbios_Name0 as 'Computer Name',
N.IPAddress0 as 'IP Address',
S.User_Name0 as 'User Name',
U.Full_User_Name0 as 'Full User Name',
S.AD_Site_Name0 as 'Site'
,M.CollectionID
FROM v_R_System S
JOIN v_R_User U on U.User_Name0 = S.User_Name0
JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION N on N.ResourceID = S.ResourceID
JOIN v_FullCollectionMembership M on M.resourceid = S.ResourceID
where /*(M.CollectionID = @collid )
AND */N.IPAddress0 NOT LIKE 'fe%'
AND N.IPAddress0 NOT LIKE '%::%'
AND N.IPAddress0 NOT LIKE '169%'
ORDER BY S.Netbios_Name0;

If you get any results, then use a value from CollectionID column as a parameter, remove the column and uncomment the WHERE clause.
If you don't get any results, it's because you don't have data available.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
maravig
maravig
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 60
ok, Thank you for responding!
maravig
maravig
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 60
I whittled the query down to the essentials. A couple of days ago, I had it working, but the report always displayed the exact the same data regardless of the collection selected.

maravig
maravig
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 60
Ok, finally got it working. Thank you for all your help and guidance.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search