Struggling with query/output

Robert · February 19, 2019, 6:32pm

Hi all,
I'm having real trouble getting a query to output correctly.
I have a number of databases on different machines and have set them up as registered servers and can run simple queries against them all in one go with no problem.

In the DB there are 2 tables I'm working with - dbo.Job and dbo.error. The dbo.error logs errors against the date logged - and the dbo.job logs a qty against a date.

I need to output a single query with the headers -

Site (this is the registered server name) / Date / count of errors for that day / sum of jobs for that day. The closest code I have to what I need (which isn't working)!! is -

SELECT CAST(Date as date)
count(*) Errors ,
sum([Jobs]) Job
FROM [DATABASE].[dbo].[ERRORS],
[DATABASE].[dbo].[JOB]
where
Message in ('1','2')
and UserCode is not null

group by CAST(Date as date)
order by CAST(Date as date)

(the where statement here applies to the count bit)

I am trying to get (example) -

Server Date Errors Job
A 19/02 5 20
B 19/02 4 25

Also my next problem is when I try to set up a connection in excel to output this query, it only allows me to specify a single database to query. Is it possible to set up a connection against the registered servers instead of individually? As when the data comes in it creates a table for each where ideally it needs to be combined.

Thanks in advance guys

mike01 · February 19, 2019, 8:02pm

have you tried inserting the results into a temp table and then return that data?

Robert · February 19, 2019, 8:19pm

Hi Mike

Not sure how I'd do that. I've got the query returning something now, but the count and sum are returning massive results, way more than it should.

SELECT sum([DATABASE].[dbo].[JOB].JOBS) QTY,
(CAST([DATABASE].[dbo].[ERRORS].DATE as date)) Date,
count([DATABASE].[dbo].[ERRORS].ERRORSID) Errors

FROM [DATABASE].[dbo].[JOB], [DATABASE].[dbo].[ERRORS]

where
Message in ('1','2') AND
UserCode is not null

group by CAST([DATABASE].[dbo].[ERRORS].DATE as date)
order by CAST([DATABASE].[dbo].[ERRORS].DATE as date)

yosiasz · February 19, 2019, 8:28pm

you are not joining these two tables in any sort of way. maybe you are getting a Cartesian Product . Is that what you want?

mike01 · February 19, 2019, 8:31pm

I misread it. I thought you were getting errors when running it into an output statement. Instead, you have registered servers, but it can't run through all of them get cumulative data (at least that's how I understand it). You will probably need to setup an SSIS package to read a table that contains the information that is in your registered servers. That can iterate through the servers and provide cumulative data.

Robert · February 19, 2019, 8:41pm

Hi,
No it is not. I need a response back from each database on the registered server list as one query output. If I split these and run them separately, they both return the correct data individually -

SELECT
(CAST([DATABASE].[dbo].[ERRORS].DATE as date)) Date,
count([DATABASE].[dbo].[ERRORS].ERRORSID) Errors

FROM [DATABASE].[dbo].[ERRORS]

where
Message in ('1','2') AND
UserCode is not null

group by CAST([DATABASE].[dbo].[ERRORS].DATE as date)
order by CAST([DATABASE].[dbo].[ERRORS].DATE as date)

SELECT sum([DATABASE].[dbo].[JOB].JOB_Size) QTY,
(CAST([DATABASE].[dbo].[JOB].DATE as date)) Date

FROM [DATABASE].[dbo].[JOB]

group by CAST([DATABASE].[dbo].[JOB].DATE as date)
order by CAST([DATABASE].[dbo].[JOB].DATE as date)

But I only want one query not two. So the output of the single query would have the Date, Errors and Job Qty, grouped and ordered by date.

yosiasz · February 19, 2019, 9:00pm

--THIS PART TO USE SAMPLE DATA across 2 databases
use sqlteam
go

if exists(select 1 from sys.tables where name = '_ERRORS')
	begin
		drop table _ERRORS;
	end

create table _ERRORS(DATE datetime, ERRORSID int, 
Message char(1), UserCode varchar(50))
insert into _ERRORS
select create_date, 
       object_id,
	   case 
	     when object_id % 2 = 0 then 1
		 else 2
	   end,
	   name
 from sys.objects where object_id <= 98


--drop table ERRORS

use DBAdmin
go

if exists(select 1 from sys.tables where name = '_JOB')
	begin
		drop table _JOB;
	end

create table _JOB(JOB_Size int, DATE datetime)

insert into _JOB
select object_id,
       create_date
 from sys.objects where object_id <= 98


--THIS PART FOR YOU TO TRY
select *
 from (
		SELECT (CAST(e.DATE as date)) Date,
			   count(e.ERRORSID) Errors

		FROM sqlteam.[dbo]._ERRORS e

		where Message in ('1','2') 
		AND UserCode is not null
		group by CAST(e.DATE as date)

) a, (
SELECT sum(j.JOB_Size) QTY,
(CAST(j.DATE as date)) Date

FROM DBAdmin.[dbo]._JOB j

group by CAST(j.DATE as date)

) b

Robert · February 19, 2019, 9:21pm

Thanks for this. It runs without errors - but does not return any data.

yosiasz · February 19, 2019, 9:26pm

try this sample data. the sample data is just to emulate what could be in yours. since we do not have access to your database and since you did not provide sample table schema and sample data.

try this

use sqlteam
go

if exists(select 1 from sys.tables where name = '_ERRORS')
	begin
		drop table _ERRORS;
	end

create table _ERRORS(DATE datetime, ERRORSID int, Message char(1), UserCode varchar(50))
insert into _ERRORS
select '2014-02-20 20:48:35.270',	3,	2	,'sysrsc' union 
select '2009-04-13 12:59:11.093',	5,	2	,'sysrow' union 
select '2014-02-20 20:48:35.477',	6,	1	,'sysclo' union 
select '2009-04-13 12:59:11.077',	7,	2	,'sysall' union 
select '2003-04-08 09:13:38.093',	8,	1	,'sysfil' union 
select '2014-02-20 20:48:35.610',	9,	2	,'sysseo' union 
select '2014-02-20 20:48:35.367',	17,	2	,'syspri' union 
select '2014-02-20 20:48:35.563',	18,	1	,'sysdbf' union 
select '2014-02-20 20:48:35.243',	19,	2	,'sysfgf' union 
select '2014-02-20 20:48:35.440',	20,	1	,'sysdbf' union 
select '2014-02-20 20:48:35.567',	21,	2	,'syspru' union 
select '2014-02-20 20:48:35.570',	22,	1	,'sysbri' union 
select '2014-02-20 20:48:35.247',	23,	2	,'sysphf' union 
select '2014-02-20 20:48:35.250',	24,	1	,'syspru' union 
select '2009-04-13 12:59:11.390',	25,	2	,'sysfti' union 
select '2009-04-13 12:59:11.373',	27,	2	,'sysown' union 
select '2014-02-20 20:48:35.540',	28,	1	,'sysdbr' union 
select '2009-04-13 12:59:10.983',	29,	2	,'syspri' union 
select '2009-04-13 12:59:11.170',	34,	1	,'syssch' union 
select '2014-02-20 20:48:35.650',	35,	2	,'syscsr' union 
select '2014-02-20 20:48:35.697',	37,	2	,'sysext' union 
select '2009-04-13 12:59:11.310',	41,	2	,'syscol' union 
select '2014-02-20 20:48:35.560',	42,	1	,'sysxlg' union 
select '2014-02-20 20:48:35.560',	43,	2	,'sysxsr' union 
select '2009-04-13 12:59:11.200',	44,	1	,'sysnso' union 
select '2014-02-20 20:48:35.553',	45,	2	,'sysuse' union 
select '2009-04-13 12:59:13.013',	46,	1	,'syscer' union 
select '2014-02-20 20:48:35.550',	47,	2	,'sysrmt' union 
select '2014-02-20 20:48:35.550',	48,	1	,'syslnk' union 
select '2009-04-13 12:59:11.500',	49,	2	,'sysxpr' union 
select '2009-04-13 12:59:10.950',	50,	1	,'syssca' union 
select '2009-04-13 12:59:11.343',	51,	2	,'systyp' union 
select '2009-04-13 12:59:11.360',	54,	1	,'sysidx' union 
select '2009-04-13 12:59:11.360',	55,	2	,'sysisc' union 
select '2014-02-20 20:48:35.547',	56,	1	,'sysend' union 
select '2014-02-20 20:48:35.557',	57,	2	,'sysweb' union 
select '2009-04-13 12:59:12.467',	58,	1	,'sysbin' union 
select '2014-02-20 20:48:35.390',	59,	2	,'sysaud' union 
select '2009-04-13 12:59:11.170',	60,	1	,'sysobj' union 
select '2014-02-20 20:48:35.527',	62,	1	,'syscsc' union 
select '2014-02-20 20:48:35.530',	63,	2	,'syscsd' union 
select '2009-04-13 12:59:11.200',	64,	1	,'syscls' union 
select '2009-04-13 12:59:11.373',	65,	2	,'sysrow' union 
select '2009-04-13 12:59:12.860',	67,	2	,'sysrem' union 
select '2009-04-13 12:59:12.890',	68,	1	,'sysxmi' union 
select '2009-04-13 12:59:12.873',	69,	2	,'sysrts' union 
select '2009-04-13 12:59:12.873',	71,	2	,'syscon' union 
select '2009-04-13 12:59:12.903',	72,	1	,'sysdes' union 
select '2009-04-13 12:59:13.000',	73,	2	,'sysder' union 
select '2009-04-13 12:59:10.967',	74,	1	,'syssin' union 
select '2009-04-13 12:59:10.983',	75,	2	,'sysmul' union 
select '2009-04-13 12:59:12.577',	78,	1	,'sysgui' union 
select '2014-02-20 20:48:35.590',	79,	2	,'sysfoq' union 
select '2014-02-20 20:48:35.537',	80,	1	,'syschi' union 
select '2014-02-20 20:48:35.400',	82,	1	,'syscom' union 
select '2014-02-20 20:48:35.573',	84,	1	,'sysfts' union 
select '2014-02-20 20:48:35.403',	85,	2	,'sysfts' union 
select '2014-02-20 20:48:35.457',	86,	1	,'sysftp' union 
select '2014-02-20 20:48:35.470',	87,	2	,'sysxmi' union 
select '2014-02-20 20:48:35.583',	89,	2	,'sysfos' union 
select '2009-04-13 12:59:12.593',	90,	1	,'sysqna' union 
select '2009-04-13 12:59:12.593',	91,	2	,'sysxml' union 
select '2009-04-13 12:59:12.610',	92,	1	,'sysxml' union 
select '2009-04-13 12:59:12.610',	93,	2	,'sysxml' union 
select '2009-04-13 12:59:13.013',	94,	1	,'sysobj' union 
select '2009-04-13 12:59:13.030',	95,	2	,'sysasy' union 
select '2009-04-13 12:59:13.047',	96,	1	,'syssql' union 
select '2009-04-13 12:59:12.483',	97,	2	,'sysbin' union 
select '2014-02-20 20:48:35.320',	98,	1	,'syssof'  

--drop table ERRORS

use DBAdmin
go

if exists(select 1 from sys.tables where name = '_JOB')
	begin
		drop table _JOB;
	end

create table _JOB(JOB_Size int, DATE datetime)

insert into _JOB(DATE, JOB_Size)
select '2014-02-20 20:48:35.270',	3	union
select '2009-04-13 12:59:11.093',	5	union
select '2014-02-20 20:48:35.477',	6	union
select '2009-04-13 12:59:11.077',	7	union
select '2003-04-08 09:13:38.093',	8	union
select '2014-02-20 20:48:35.610',	9	union
select '2014-02-20 20:48:35.367',	17	union
select '2014-02-20 20:48:35.563',	18	union
select '2014-02-20 20:48:35.243',	19	union
select '2014-02-20 20:48:35.440',	20	union
select '2014-02-20 20:48:35.567',	21	union
select '2014-02-20 20:48:35.570',	22	union
select '2014-02-20 20:48:35.247',	23	union
select '2014-02-20 20:48:35.250',	24	union
select '2009-04-13 12:59:11.390',	25	union
select '2009-04-13 12:59:11.373',	27	union
select '2014-02-20 20:48:35.540',	28	union
select '2009-04-13 12:59:10.983',	29	union
select '2009-04-13 12:59:11.170',	34	union
select '2014-02-20 20:48:35.650',	35	union
select '2014-02-20 20:48:35.697',	37	union
select '2009-04-13 12:59:11.310',	41	union
select '2014-02-20 20:48:35.560',	42	union
select '2014-02-20 20:48:35.560',	43	union
select '2009-04-13 12:59:11.200',	44	union
select '2014-02-20 20:48:35.553',	45	union
select '2009-04-13 12:59:13.013',	46	union
select '2014-02-20 20:48:35.550',	47	union
select '2014-02-20 20:48:35.550',	48	union
select '2009-04-13 12:59:11.500',	49	union
select '2009-04-13 12:59:10.950',	50	union
select '2009-04-13 12:59:11.343',	51	union
select '2009-04-13 12:59:11.360',	54	union
select '2009-04-13 12:59:11.360',	55	union
select '2014-02-20 20:48:35.547',	56	union
select '2014-02-20 20:48:35.557',	57	union
select '2009-04-13 12:59:12.467',	58	union
select '2014-02-20 20:48:35.390',	59	union
select '2009-04-13 12:59:11.170',	60	union
select '2014-02-20 20:48:35.527',	62	union
select '2014-02-20 20:48:35.530',	63	union
select '2009-04-13 12:59:11.200',	64	union
select '2009-04-13 12:59:11.373',	65	union
select '2009-04-13 12:59:12.860',	67	union
select '2009-04-13 12:59:12.890',	68	union
select '2009-04-13 12:59:12.873',	69	union
select '2009-04-13 12:59:12.873',	71	union
select '2009-04-13 12:59:12.903',	72	union
select '2009-04-13 12:59:13.000',	73	union
select '2009-04-13 12:59:10.967',	74	union
select '2009-04-13 12:59:10.983',	75	union
select '2009-04-13 12:59:12.577',	78	union
select '2014-02-20 20:48:35.590',	79	union
select '2014-02-20 20:48:35.537',	80	union
select '2014-02-20 20:48:35.400',	82	union
select '2014-02-20 20:48:35.573',	84	union
select '2014-02-20 20:48:35.403',	85	union
select '2014-02-20 20:48:35.457',	86	union
select '2014-02-20 20:48:35.470',	87	union
select '2014-02-20 20:48:35.583',	89	union
select '2009-04-13 12:59:12.593',	90	union
select '2009-04-13 12:59:12.593',	91	union
select '2009-04-13 12:59:12.610',	92	union
select '2009-04-13 12:59:12.610',	93	union
select '2009-04-13 12:59:13.013',	94	union
select '2009-04-13 12:59:13.030',	95	union
select '2009-04-13 12:59:13.047',	96	union
select '2009-04-13 12:59:12.483',	97	union
select '2014-02-20 20:48:35.320',	98