1 of 1 people found this helpful
yes, 'TYPE*' here is the model type number, which for all nsh script objects is 1.
you'd need to do a db query as the nsh script type is an 'option' associated w/ the script object that's stored in the db, and just to make it fun, type 2 is runscript, type 1 is 'run once' in the db, which is the inverse of the gui
Thanks Bill - Any hints as to which table this key information is tucked away in?
Think I found it...
option_value in dbo.depot_object_option_value
will post a full query when I get it all working correctly
Yeah – you’re on the right track. You don’t learn anything if I just give you the answer ☺
So here's what I've got... struggling to find the full 'File Location (//blfs/root_path/full/path/to/script/on/blfs.nsh)' but think the rest is correct so far
select distinct depot_object.depot_object_id, depot_object.name, file_server.root_path, depot_object_option_value.option_value, depot_object.data_type_id
from depot_object, file_server, file_location, depot_object_location, depot_object_option_value
where depot_object.name = 'script_name.nsh' AND depot_object.is_latest_version = '1' AND file_server.file_server_id = file_location.file_server_id AND depot_object.depot_object_id = depot_object_option_value.depot_object_id AND file_location.file_location_id = depot_object_location.file_location_id AND depot_object_option_value.option_value = '1' AND depot_object.data_type_id = '1025';
right now I'm specifying the depot_object.name to reduce query time + unnecessary processing, that condition will be removed later.
Had to make an assumption that depot_object.data_type_id is equal to the type of depot object, also assuming that '1025' = NSH Script.
And now... a more complete & optimized version... still trying to get filename+path... getting there
select distinct depot_object.depot_object_id as depotObjectID, depot_object.name as depotObjectName, file_server.root_path as rootPath, file_location.relative_path as relativePath, depot_object_option_value.option_value as scriptType, depot_object.data_type_id as depotType, JOB.name as jobName
on depot_object_option_value.depot_object_id = depot_object.depot_object_id
on JOB_DEPOT_OBJECT.depot_object_id = depot_object.depot_object_id
on depot_object_location.depot_object_id = depot_object.depot_object_id
on file_location.file_location_id = depot_object_location.file_location_id
on FILE_SERVER.file_server_id = file_location.file_server_id
on JOB.job_id = JOB_DEPOT_OBJECT.job_id
where depot_object.is_latest_version = '1' AND depot_object_option_value.option_value = '1' AND depot_object.data_type_id = '1025';
One final update here...
This is how NSH Script types map into the depot_object_option_value table (feel free to correct me if im wrong though!)
Type 1 NSH Script = option_value = 2
Type 2 NSH Script = option_value = 1
Type 3 NSH Script = option_value = 4
Type 4 NSH Script = option_value = 3
I forget, look in the blcli NSHScript addNSHScriptToDepot command
This might be what you need:
select script_name, location, case  when 1 then '2' when 2 then '1' when 3 then '4' when 4 then '3' end as 'type',  as 'charset', case  when 'N' then 'No' when 'Y' then 'Yes' end as 'allow_no_targets' from ( select do.name script_name, dov.option_id, dov.option_value, '//'+fs.host+fs.root_path+fl.relative_path location from depot_object do inner join depot_object_option_value dov on dov.depot_object_id = do.depot_object_id and dov.depot_object_version_id = do.depot_object_version_id inner join depot_object_location dol on dol.depot_object_id = do.depot_object_id and dol.depot_object_version_id = do.depot_object_version_id inner join file_location fl on fl.file_location_id = dol.file_location_id inner join file_server fs on fs.file_server_id = fl.file_server_id where do.object_type_id=1 and do.is_deleted=0 and do.is_latest_version=1 ) p pivot ( max(option_value) for option_id in (,,) ) pvt