Leogal
asked on
How to Capture metrics in a SQL script
I am using the code below to capture metrics from a sql script whereby the count of duplicate records is written out to a metrics file.
the problem is that I am apparently incorrect on my syntax... as the second to last closing quote has an error showing in the sytax checker, ' Incorrect syntax near ')' . Expecting AS, ID or QUOTED ID.
if I change the code to the following I get a new error:
error : Msg 8155, Level 16, State 2, Line 13 No column name was specified for column 1 of 'i'.
A co-worker used this code fine in AS400 SQL and he wants me to adapt it to sql server. I am trying but not successfully. I understand that the syntax is not exactly the same between the two sql flavors but I need help.
Can someone help me to fix this syntax?
metrics-file-layout.docx
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp, 'Extract', 'Purch Site', 'Number of Purch Site records extracted',
(select count(*) from [dbo].[WorkItemPurch] ) ) ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp,
'Extract',
'Purch Site',
'Duplicates found',
(SELECT COUNT(*) FROM ( select count(*) from [dbo].[WorkItemPurch]
group by ItemID
having count(*) > 1 ) ))
the problem is that I am apparently incorrect on my syntax... as the second to last closing quote has an error showing in the sytax checker, ' Incorrect syntax near ')' . Expecting AS, ID or QUOTED ID.
if I change the code to the following I get a new error:
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp, 'Extract', 'Purch Site', 'Number of Purch Site records extracted',
(select count(*) from [dbo].[WorkItemPurch] ) ) ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp,
'Extract',
'Purch Site',
'Duplicates found',
(SELECT COUNT(*) FROM ( select count(*) from [dbo].[WorkItemPurch]
group by ItemID
having count(*) > 1 ) as i ))
;
error : Msg 8155, Level 16, State 2, Line 13 No column name was specified for column 1 of 'i'.
A co-worker used this code fine in AS400 SQL and he wants me to adapt it to sql server. I am trying but not successfully. I understand that the syntax is not exactly the same between the two sql flavors but I need help.
Can someone help me to fix this syntax?
metrics-file-layout.docx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A simple change in second INSERT would resolve the issue -
CHANGE -
select count(*) from [dbo].[WorkItemPurch]
TO
select count(*) AS CountVal from [dbo].[WorkItemPurch]
Anoo
CHANGE -
select count(*) from [dbo].[WorkItemPurch]
TO
select count(*) AS CountVal from [dbo].[WorkItemPurch]
Anoo
What ScottPletcher provided is a solution, I agree 100%.
Out of curiosity, did you try the solution that I provided and if so did you face any issues on that ?
Out of curiosity, did you try the solution that I provided and if so did you face any issues on that ?
Open in new window