Select query issues

    General discussion

  • Hi All,

    I got a request to create region & country wise report for hotfix compliance. I tried to join & filter the data from code of Rep ID 172 with a custom table having the region and country info. But it didn't worked.

    So, I wrote a query for the same from scratch and its working fine for the total list. Then I started Computing with the data taken and the query was un manageable, So I populated the reuslt list into a temp table. (Create #temptabl.... insert into #temptabl (.......) select *******)
    And I got all the results successfully. Till now I was using Sql management studio. Seeing the results I went ahead and put the whole query into Report. The first row in the data section of report is coming as "No matching records found.".
    The select query which is providing values to the insert statement is giving me a blank line I believe. Is there any solution for the same.
    one more question I would like to put in the air is that is it an advisable practice to use #temp tables. I used them because in Sql its session specific. But i don't know how that will work in a multi user SCCm environment.

    The version of sccm used in my organisation is RTM.

    Thanks for Your time,

    Sudeesh R

    Monday, June 8, 2009 9:30 AM

All replies

  • You should provide the SQL query statement you've written so that we can assist you.
    The ConfigMgr webreports do display exactly what can be seen in SQL management studio.
    Monday, June 8, 2009 11:37 AM
  • i've this exact problem.  interestingly enough, it didn't manifest until i started using two temp tables.  i'm going to drop back to one just to see if it changes the output any.  will respond when i know.

    ::marcus _
    Thursday, August 20, 2009 1:51 PM
  • alright, i made a few modifications, and it still does it.  i guess i just never noticed it before.  what a real pain!

    ::marcus _
    Thursday, August 20, 2009 1:57 PM
  • Hi,

    SET NOCOUNT ON at the starting of query and
    SET NOCOUNT OFF just before the select6 query which you want to show in the Report.

    Cheers, Sudhiiiii.....
    Monday, August 31, 2009 3:38 PM
  • Sudhi - thanks!  that worked!
    ::marcus _
    Monday, August 31, 2009 5:57 PM