I'm helping out Mohd with the following query and reached a sticking point.
How can you convert the SQL code that you find in CA Service Desk Manager (ITSM) stdlogs into SQL code that you can put into an MS SQL Server Query?
Can someone with SQL knowledge chime in?
Mohd was looking at this knowledge document TEC580653:
And wished to convert this "SDM stdlog" SQL into "MS SQL" SQL (if that makes sense):
SELECT count(*) FROM call_req, ca_contact WHERE ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND call_req.type = ? AND call_req.assignee = ca_contact.contact_uuid AND ca_contact.userid = ?) Input (<string>I|<string>Kyle_R)
Looking at the TEC document, it has this to say about converting stdlog SQL to executable SQL:
The SQL statement starts with the "(SELECT". This long string would need to be manipulated to run directly in SQL Server.
Service Desk executes all SQL queries within a Stored Procedure due to the usage of Microsoft's SQL OLEDB API calls. As a result, the query would need to be run using the following syntax to ensure accurate results:
exec sp_executesql N'SELECT call_req.open_date,
call_req.id, call_req.ref_num, call_req.id
FROM call_req WHERE ( call_req.priority IN (0,1,2) )
AND ( call_req.assignee IS NULL AND call_req.group_id IS NULL )
AND call_req.active_flag = 1 AND call_req.type = @P1
ORDER BY call_req.open_date DESC',N'@P1 nvarchar(1)',N'I'
And the above example does run perfectly in MS SQL.
It is an example of running ***ONE*** variable at at time with the "exec sp_executesql" command.
I can get this one variable behaviour to work - with the client's query - with this syntax:
exec sp_executesql N'SELECT count(*) FROM call_req, ca_contact
WHERE ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL )
AND call_req.type = @P1 AND call_req.assignee = ca_contact.contact_uuid',
You'll notice that the section for "call_req.type = ?" has been replaced by "call_req.type = @P1" and that the value for the variable has been SET and DECLARED in the final line. (It doesn't explicitly say "SET" or "DECLARE" but as near as I can tell, that's what it is doing. Don't shoot me please - I'm not a DBA.)
Where I am getting hung up on is adding in the ***SECOND*** variable. That is, the part of the client's code that contains this:
. . . ca_contact.userid = ?) Input (...|<string>Kyle_R)
Can anyone expand my working one variable case into the two ore more variable case?
Much appreciated! If we get this nutted out, I'll add it into a knowledge document.