Cursors, i completed a group by query with help and sent it along to requestor..

  • but now said requestor is upping the request. i am needing to create a cursor to go thru all the database on an sql instance and then test if the table is in the database and if so then run query and output the results somewhere that he can load up into a spread sheet. also, need to figure out on the output file to include the name of the database. like c:\temp\db_bob.csv or whatever i need to output the file as.

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TABLE1')

    BEGIN

    SELECT col_date / 10000 AS col_yr, SUM(col_amt) AS Expr1

    FROM TABLE1

    WHERE (col_type = 'R')

    GROUP BY col_date / 10000, col_amt

    ORDER BY col_yr

    END

  • Your best bet is to run this from sqlcmd and output to a file or use PoSh to do this. T-SQL doesn't give an easy way to save results to a file.

     

  • thanks i will do some more investigating using PoSh. still working on my cursor to get all the database names and pass them thru to the query part

    Really appreciate input on this.

  • Nah... it's easier than that.  Create a stored procedure that does the work, give the spreadsheet user privs to execute it, and have them execute the stored procedure from their spreadsheet with their login.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • and my part of the project got simpler sort of. they no longer want the output to a file. just send results to a temp table and issue a select * from #temptable and they would copy the results to a spreadsheet that multiple users will be pasting the data into.

    they have about 30 SQL servers to run it on with unknown number of sql databases to gather the info from.

    got this far into said script, I am still working on it but so far it is not producing the temptable and i am going to add a if table1 exists logic so i don't query databases without the table1 in them.

    DECLARE @TABLESCRIPT VARCHAR(MAX), @DATABASENAME NVARCHAR(128);

    set @DATABASENAME = ''

    WHILE @DATABASENAME IS NOT NULL

    BEGIN

    SET @DATABASENAME = (SELECT MIN(QUOTENAME(name))

    FROM master.dbo.sysdatabases

    WHERE QUOTENAME(name) > @DATABASENAME)

    SET @TABLESCRIPT = '(SELECT '''+@DATABASENAME+''' AS ref_database, ust_date / 10000 as ref_yr, SUM(ust_amt) as ref_amt, COUNT(*) AS ref_count INTO #TEMPTABLEE

    FROM ' +@DATABASENAME+'..[TABLE1]

    WHERE (ust_type = ''R'') and (ust_date / 10000 in (2009, 2008, 2007, 2006, 2005))

    GROUP BY (ust_date / 10000))'

    EXEC(@TABLESCRIPT)

    END

    SELECT * FROM #TEMPTABLE

  • A couple things. Look for state=0 in databases, as some could be offline, restoring, etc. Think about dbid>4, to ignore master/msdb/model/tempdb.

    Also, think about Jeff's idea of embedding this in a proc. That will make things easier. Ideally, you put the proc in all dbs and then someone can execute it in that db, or you can call it from a loop.

  • Jeff Moden wrote:

    Nah... it's easier than that.  Create a stored procedure that does the work, give the spreadsheet user privs to execute it, and have them execute the stored procedure from their spreadsheet with their login.

    This is the one and only situation where I've used VIEW's.  Instead of giving users' privileges I give them read access to views which they ODBC link to Excel pivot tables and/or the MS Query to get raw data.  Views are queries which get executed upon request so whenever they refresh the embedded query they get the latest data.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    Jeff Moden wrote:

    Nah... it's easier than that.  Create a stored procedure that does the work, give the spreadsheet user privs to execute it, and have them execute the stored procedure from their spreadsheet with their login.

    This is the one and only situation where I've used VIEW's.  Instead of giving users' privileges I give them read access to views which they ODBC link to Excel pivot tables and/or the MS Query to get raw data.  Views are queries which get executed upon request so whenever they refresh the embedded query they get the latest data.

    Views will definitely work here but, man, some of the things that users ask for just aren't well suited (especially performance-wise) to an "all-in-one" query required by a view especially if they want to filter on aggregated results (of course, a materialized/index view can do the trick there).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thank you all. i got it over to the requestor and he ran it and said it was perfect as it gave him everything he needed.

    so thank you all again. Life Savors 🙂

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply