How to convert "SDM stdlog" SQL into "MS SQL" SQL?

[Kyle_R]

Hello Everyone,

 

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:

CA Service Desk Manager (Service Desk) and MS SQL Server Performance

 

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',

N'@P1 nvarchar(1)',N'I'

 

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.

 

Thanks, Kyle_R.

[Legacy User]

Hi Kyle,

 

you may need to use a more elaborate syntax - here's a working example based on a sample in the Transact-SQL reference:

 

use mdb;
go

declare @SqlStr nvarchar(500);
declare @ParmDef nvarchar(500);

set @SqlStr = N'select count(*) from dbo.call_req where type = @P1 and status = @P2';

set @ParmDef = N'@P1 nvarchar(1), @P2 nvarchar(12)';

execute sp_executesql
@SqlStr
,@ParmDef
,@P1 = N'I'
,@P2 = N'OP'
;

 

Cheers,

James

[Legacy User]

For this query:

 

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)

 

Follow this logic...

 

For every '?' , you will need to replace '?' with what you see in the

Input (<string>I|<string>Kyle_R)

in that sequence.

 

So to be able to run this directly in Query Analyser, that query will become:

 

 

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 = 'I' AND call_req.assignee = ca_contact.contact_uuid AND ca_contact.userid = 'Kyle_R')

 

Hope this helps...

 

Regards,

Brian

[Kyle_R]

Thanks Brian.

 

The first part of that query runs fine.

Can you see if you can get the final statement to run?:

 

. . . ca_contact.userid = 'Kyle_R'

 

As I can only get errors, even when just using it by itself.

 

(Maybe use ServiceDesk as a contact, as that will be in most systems, for your test.)

 

 

Otherwise, I'll go with James' method, even though it is more complex.

 

Thanks, Kyle_R.

[Legacy User]

This works on my sandbox.  In your sample you have a surplus ')' at the end of the statement:

 

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 = 'I'

AND call_req.assignee = ca_contact.contact_uuid

AND ca_contact.userid = 'servicedesk'

 

In general terms it shouldn't be too hard to write some Perl that parses the 'Input' section from the original query and puts quotes around anything that's defined as 'string' before substituting it for the relevant question mark - and I imagine that's pretty much what the query processor does with that string.  Dates might be a little harder to deal with (they almost always are), but you might just get lucky and find that the format they're supplied in matches the default format for SQL.

[Kyle_R]

Thanks James!

 

Kyle_R.

[Kyle_R]

Thanks James,

 

I meant to reply earlier.

 

That works. Thank you.

 

A nice bit of code there. Does my noodle in looking at it , but it works.

 

I need to have a crack at extending it to more variables, and then plugging in specific search terms to really understand it.

 

I was hoping to avoid a "huge" programme like that, but as you say, it may be necessary. And done right I think it could even be easier to read.

 

My last thing that I'm hoping on, is that the code "behaves in a similar way" to code that SDM would pass over and that SQL would build. This is because it needs to be a rough proxy in order to test performance of a specific query. Guess I'll find out.

 

Thanks again.

 

Kyle_R.

[Legacy User]

I think doing a substitution as above will be easier - and there's no reason why it shouldn't work :-)

Cheers,

james