May 1, 2008 at 1:49 pm
I'm adept at MS Access but SQL is a horse of a different color. I need to write a query that will cycle through hundreds of databases on the server, test if the database is read-only (disqualifier), test if the database contains a table named "Calls" and setting @CallsFlag to true if it exists, test if the "Calls" table contains a field named "Subject" and setting @SubjectFlag to true if exists. I need to take additional action on each database depending on the combination of the two flags.
First I created a cursor for the databases:
SET NOCOUNT ON
DECLARE csrDatabases CURSOR FAST_FORWARD LOCAL FOR
Select name From sys.databases where is_read_only=0 Order By name
OPEN csrDatabases
Next I created my variables and captured the first database from the cursor:
DECLARE
@DatabaseName varchar(1000),
@DBName sysname,
@CallsFlag bit,
@SubjectFlag bit,
@var as varchar(5000)
FETCH NEXT FROM csrDatabases INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
Now test for the existence of the "Calls" table:
Set @DBName=@DatabaseName
Set @var=@DBName + '.dbo.Calls'
If OBJECT_ID(@var,N'U') is not null
SET @CallsFlag=1
ELSE
SET @CallsFlag=0
Up to this point all seems to run fine and accurate.
Next, test for the existence of the "Subject" field:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Calls' AND COLUMN_NAME ='Subject')
Set @SubjectFlag=1
Else
Set @SubjectFlag=0
Next take additional action on the identified databases
If @CallsFlag=1 and @SubjectFlag=1
Take some action
Else
Take no action
Finally, loop back:
FETCH NEXT FROM csrDatabases INTO @DatabaseName
END
CLOSE csrDatabases
DEALLOCATE csrDatabases
My problem is with the second half of the code that checks for the existence of the "Subject" field...I must have to specify the database name to use somehow but I don't know where/how to do this. I tried:
Use @DatabaseName
GO
but I received a syntax error at this point. Also, in my research I keep reading how cursors are "bad", what other method can I use to achieve this?
May 1, 2008 at 2:57 pm
your information schema check executes in context of the current database you are connected with !
If you are not connected to the @dbname, you are validating other data !
So
If OBJECT_ID(@var,N'U') is not null
does not evaluate the same data as
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Calls' AND COLUMN_NAME ='Subject')
unless you are connected to the db used in @var.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 1, 2008 at 3:18 pm
Yes, I understand that. In short, I need to know how to specify to use each database in the cursor. I can hard-code the database name (as in USE DATABASENAME and GO) but (USE @DBNAME and GO) creates a syntax error at that line. I found the code for the OBJECT_ID(@var...) but I can't find a way to do the same for INFORMATION_SCHEMA.COLUMNS I hope that makes more sense.
May 1, 2008 at 4:01 pm
You need to use EXEC or sp_executesql to be able to concatenate USE with @DatabaseName. However, that change in database context will only apply within the EXEC/sp_executesql statement, so you need to put your INFORMATION_SCHEMA query in the statement as well.
Here is some test code I knocked up based on what you have there. I'd make the @DatabaseName parameter nvarchar or sysname so you can eliminate the CAST function (sp_executesql only accepts nvarchar). This code will run standalone. You will need to bend it a little to fit with your final solution.
DECLARE @sql nvarchar(1000),
@DatabaseName varchar(1000),
@ParamDef nvarchar(1000),
@SubjectFlag bit
SET @ParamDef = '@SubjFlag bit OUTPUT'
DECLARE testcursor CURSOR FOR
SELECT name
FROM master.sys.databases
OPEN testcursor
FETCH NEXT FROM testcursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE [' + CAST(@DatabaseName AS nvarchar(1000)) + N']; '
+ N'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS '
+ N'WHERE TABLE_NAME=''Calls'' AND COLUMN_NAME =''Subject'') '
+ N'Set @SubjFlag=1 '
+ N'Else '
+ N'Set @SubjFlag=0'
EXEC sp_executesql @sql, @ParamDef, @SubjFlag = @SubjectFlag OUTPUT
-- do your testing on @SubjectFlag etc here
SELECT @SubjectFlag
FETCH NEXT FROM testcursor INTO @DatabaseName
END
CLOSE testcursor
DEALLOCATE testcursor
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 2, 2008 at 6:52 am
Thank you so much! If it's not too silly of a question, what is the purpose of the N' throughout your code sample? I haven't seen it before. I have no problem reading up on it myself if you can point me in the right direction. Thanks again.
May 2, 2008 at 7:56 am
The N' is telling the server to treat the data as UNICODE data (nvarchar or Nchar). Unicode character sets are used to represent and store international characters, etc...
I don't know if there's a need to have Nvarchar data in this case, but it doesn't hurt either.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 2, 2008 at 8:43 am
Thanks!
May 4, 2008 at 9:56 pm
As stated in my previous post, I use nvarchar because sp_executesql only accepts unicode data. From BOL:
[font="Arial"][ @stmt = ] stmt
Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not.
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 8, 2008 at 3:39 pm
You were all very helpful with my previous question so I'm hoping I can impose again. Can someone help me with the correct syntax for the following statement? Again, this has to do with specifying which database to use for the WITH statement. There will be other CTE tables but I figure "one not working" is enough for the moment :crying:
The WITH statement works correctly if I'm running it from a specific database, but I need to run this against multiple databases so it's necessary to pass in the database name. Problem is I can't seem to figure out how to concatenate the two statements correctly. I know that I'll need to use Exec sp_executesql to run this. @sql has been declared as nvarchar(), @DBName has been declared as sysname. I'm running SQL 2005. Thanks in advance.
SET @sql4= N'USE [' + CAST(@DBName as nvarchar(1000)) + N']';
WITH TotHourCount (ActivityDate, RepName, ProjectName, BHours, THours, FRHours, FNHours, TotHours) AS
(SELECT WrkDate, Employee, Campaign, BHours, THours, FRHours, FNHours, (BHours + THours + FRHours + FNHours) as TotHours FROM
(Select WrkDate, Employee, Campaign,
Sum(Case when Code=B then TtlTime Else 0 END) as BHours,
Sum(Case when code=T then TtlTime Else 0 END) as THours,
Sum(Case when code=FR then TtlTime Else 0 END) as FRHours,
Sum(Case when code=FN then Ttltime Else 0 END) as FNHours
From tblTimesheet Inner Join tblJobs on tblTimesheet.Account=tblJobs.[Account#]
Group By WrkDate, Employee, Campaign) t )
May 9, 2008 at 12:09 am
I'd suggest to get rid of the "dynamic" stuff.
( All time ref: www.sommarskog.se/dynamic_sql.html )
If you need the same proc in # databases, create it in each database !
( Unless it is a system-proc ! That would be the only reason to create "sp_"-prefixed procs in master db ! And make them transparant for user databases)
If you need it for "dynamic" cross database queries, I think you'd be better of actualy providing the known ones "hardcoded" .
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 9, 2008 at 6:48 am
I have read a lot about "not using dynamic" because of injection attacks however in this case it is unfortunately necessary. I need to build a report, for management, that summarizes information from hundreds of databases on the server. The databases themselves are dynamic in that after a span of time they are placed into READ ONLY and omitted from subsequent reports; they can be reactivated; new databases can be added. Management needs to be able to run this report "at will" so I will need to design an interface that will allow them to do so indirectly. They currently have a means to run the reports on each database individually however this is time consuming and then someone has to take that information and compile it into a single report--more time involved. This is unacceptable to management. Soooo dynamic it must be.
May 9, 2008 at 7:33 am
So the queries are known and fixed, just the db is unknown
and the executors have the needed rights to read the data.
- are this individualy single database actions (1)
or do you need consolidated results. (2)
If (1) :
- maybe having a sproc in master :crazy: prefixed "sp_" and being made transparant for all databases may help out.
if (2):
- create a consolidated db to capture the results of the individual databases and prepare the data if you can (sqljob).
This way your users only need read rights for your consolidation db.
and you get rid of your "dynamic" stuff because you can generate the job to suite your needs.
- if even the queries themself are full dynamic, your're sitting on a pile of dynamite, because they may launch a query that blocks/stalls your whole server.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 9, 2008 at 10:05 am
Hmmm, let me try to describe better...management wants to "push a button" and have something like this report spit out. There's no input from management, the parameters are totally hidden from them and being used simply to cycle through every database on the server. There are currently 3 different db structures (can't do anything about that) which is why in my original post I was looking to set flag values. I use a CASE statement based on the flags to hard code the correct table/field names into the SQL statement for each database. I just keep getting stuck on the need for "USE @DBName" throughout the process.
DBName ProjectName Date RepName BHours THours..... TotHours
DB1 (read only so not reported on, it's skipped)
DB2 10367A 1/1/08 John Doe 3.00 1.00........ 8.00
DB2 10367A 1/1/08 Jane Smith 8.00 0.00........ 8.00
DB2 10367A 1/2/08 Jane Smith 4.00 4.00....... 8.00
DB3 19756Z 1/2/08 John Doe 0.00 1.00....... 4.00
DB4 (read only so not reported on, it's skipped)
DB5 32765D 1/1/08 Pim Tu 0.25 4.50........ 10.00
You raise an interesting thought. Is it possible to have a calling procedure located somewhere that then calls a procedure stored in each database, returns the above data for that database and appends it to a combined table somewhere, then calls the procedure in the next database (and so on)? I could then base the report on that combined table. Heck, I could then allow user parameters for selecting "slices" of that data without concern for the integrity of the original data...
OK, first thing's first. How would I go about setting something like that up? As I said, I have very little knowledge of SQL so this is all a learning experience for me. I have no problem reading-up if you can send me in the right direction. In fact, the link you sent me to above is fantastic and I plan on reading his other articles on his website. Unfortunately BOL are not very friendly to someone like me; they seem to assume one just needs a refresher and not a detailed explanation of how things work.
May 9, 2008 at 1:33 pm
If the "mother proc" creates a temp table, and calls the "child procs" to populate it, that works quite well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply