SQL Server Performance

List out SP result columns into a Table

Discussion in 'SQL Server 2005 General Developer Questions' started by sonnysingh, Jul 31, 2008.

  1. sonnysingh Member

    Hi All
    I need to list out the SP results columns in a table and for this using following SQL with Linked server..

    select * into TEMP_Table from openquery(LOCAL,'Exec Sales.dbo.HalfHourlySales 1,''07/31/08''')
    When this query run first time results set stay in cache and to clear cache i use DBCC FLUSHPROCINDB (@intDBID) command before run SQL second time. It is undocumented SP and I guess, it is not good idea to use in SP.
    So, I need to know alternative way to list out the SP result columns in to a Table.
    Thanks in Advance

  2. Adriaan New Member

    What is the reason for the DBCC call? Let SQL worry about its cache.
    If you're worried about te source data getting locked on the remote server, then instead of select into, use this instead:

    create table #temp_table (column_definitions)

    insert into #temp_table
    select * from openquery(
    parameters)
    Obviously you need to know the data types for the columns as returned by the sproc, but that shouldn't be a problem.
  3. sonnysingh Member

    Reason for the DBCC to clear the results set fron the cache as I mentioned that when I run the SP second time after change the values in the tables that using in the SP and it's return same results rather new values that either modify or newly inserted. when I use DBCC commands results set start reflecting right.
    But I need to have right result without using DBCC command. or I say also without using LinkedServer.
    So, in brief I want to capture columns name into table returned by the procedure.
    Thanks in Advance
    sonny
  4. Adriaan New Member

    Do you want an empty dataset, just to create the temp table correctly?
    If the remote sproc can return datasets with varying column definitions, then you should consider redesigning the remote sproc to get more predictable results.
    Other than that, try passing a date parameter like 01/01/1800 which should not return any data. Or add an optional parameter to the remote sproc to make it return an empty dataset.
  5. Madhivanan Moderator

    Also try
    select top 0 * into TEMP_Table from openquery(LOCAL,'Exec Sales.dbo.HalfHourlySales 1,''07/31/08''')
  6. Adriaan New Member

    Madhivanan, I doubt that the TOP 0 is considered by OPENQUERY, so the data will be retrieved anyway.
  7. Madhivanan Moderator

    [quote user="Adriaan"]
    Madhivanan, I doubt that the TOP 0 is considered by OPENQUERY, so the data will be retrieved anyway.
    [/quote]
    Yes it is. Thanks [:)]
    The only way in this case I think is just return 1 row and then populate other rows
    select * into TEMP_Table from openquery(LOCAL,'set rowcount 1 Exec Sales.dbo.HalfHourlySales 1,''07/31/08'' set rowcount 0')
  8. Adriaan New Member

    Brilliant!
  9. Madhivanan Moderator

    [quote user="Adriaan"]
    Brilliant!
    [/quote]
    Thanks [:)]
  10. sonnysingh Member

    Hi
    Can we have column Lists without using linked Server ( openquery) as I am still having error ...now even simple select statetment is giving error. user have db_owner rights.
    ERROR: Msg 7357, Level 16, State 1, Line 1
    Cannot process the object "Exec Sales.dbo.HalfHourlySales 1,'07/31/08'". The OLE DB provider "SQLNCLI" for linked server "LOCAL" indicates that either the object has no columns or the current user does not have permissions on that object.
    Thanks....



  11. Adriaan New Member

    Is the linked server set up with Windows authentication or SQL authentication?
    Edit:
    Does the error go away if you run the query without the SET ROWCOUNT statements?
  12. sonnysingh Member

    SQL Authentication... I have use SET NOCOUNT ON and OFF as I am using Insert Statement in SP. But I really do not want to use Linked Server as I have said that using DBCC command which is save to use in SP and also need to give sysadmin rights to application users. I would definitely would like to use alternative solution to avoid all these hassle.
    So, Please any alternative solution?

    Thanks in Advance..

  13. Adriaan New Member

    Does the SP always return the same set of columns?
    Then you already know the structure for the temp table, and you can simply use CREATE TABLE instead of the SELECT INTO syntax - completely avoiding OPENQUERY.
    Does anyone know for sure whether results from OPENQUERY will be cached?
    This is all a bit silly - I presume you need the data at this end anyway. Another option would be to have the remote sproc dump the data to a file on a network share, then import the dump locally. That should certainly prevent the data from being cached.
  14. sonnysingh Member

    Yes, but can be change as per user requirements. I believe that OPENQUERY cached as when DBCC command not used every time run the SP, result sets are same. If any column add or remove from SP, it's still bring out same result sets but if DBCC command use in SP it start giving expected result set.
    I can't use recommended solution as this functionality use to create report template and UI provide to users with all the report columns and they can select them as require to display in the report. So, SP's columns list can be dymanic as need to display on UI for selection.
    so any solution except openquery for linked server as i wnat to avoid linked server (openquery)..
    Thanks in Advance
  15. sonnysingh Member

    Can we achieve following ouput without using Linked Sever (openquery). Plz let us know if anyone does this?
    select * into TEMP_Table from openquery(LOCAL,'Exec Sales.dbo.HalfHourlySales 1,''07/31/08''')
    Thanks in Advance
  16. Adriaan New Member

    You can do an ad-hoc connection with OPENDATASOURCE - check BOL.
  17. sonnysingh Member

    I don't want to use any functions... Any other way Please?
    Thanks !
  18. Adriaan New Member

    Without functions? Whyever not!?

Share This Page