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

Question

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

roy.tollison

SSCommitted

Points: 1930
More actions
July 15, 2020 at 4:03 pm

#3770275

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

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

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 1

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.

roy.tollison SSCommitted Points: 1930 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

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.

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

roy.tollison SSCommitted Points: 1930 More actions · Answer 4

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 5

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.

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

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.

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

roy.tollison SSCommitted Points: 1930 More actions · Answer 8

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 🙂