SQL query producing empty file

[deesloop]

I'm trying to connect to a remote SQL server and run a query
It's this query here

https://social.msdn.microsoft.com/forum ... age-by-sql

Now I presume it connects ok as the initial errors were sorted
However the file that gets written is 0K
Run the query locally o nthe detination server runs fine

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|0
Comment=STRINGLIST
ContinueOnError=INTEGER|0
Events=FOLDER
ExternalName=STRING|"Task10"
Hide=INTEGER|0
ID=INTEGER|1909129044
LocalVariables=STRING|"""SQLPERFORMANCE="""""""""""
LogOnAsUser=INTEGER|1
Name=STRING|"get vcenter CPU usage"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""SQLPERFORMANCE"" with value """""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"1"
varkind=STRING|"1"
varname=STRING|"SQLPERFORMANCE"

[Actions\Action2]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER

[Actions\Action2\Params]
assignvar=STRING|"1"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DSN=VCenter;Description=Vcenter;Trusted_Connection=Yes;APP=RoboTask;WSID=DINGDC;DATABASE=ecm"""
override=STRING|"0"
password=STRING|"2005620872218982164521896"
sql=STRING|"""USE ECM"",""DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); "",,""SELECT TOP(512) SQLProcessUtilization AS [SQL Server Process CPU Utilization], "",""               SystemIdle AS [System Idle Process], "",""               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],"",""               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]"",""               "",""FROM ( "",""	  SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, "",""			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') "",""			AS [SystemIdle], "",""			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', "",""			'int') "",""			AS [SQLProcessUtilization], [timestamp] "",""	  FROM ( "",""			SELECT [timestamp], CONVERT(xml, record) AS [record] "",""			FROM sys.dm_os_ring_buffers "",""			WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' "",""			AND record LIKE '%<SystemHealth>%') AS x "",""	  ) AS y "",""ORDER BY record_id DESC;"""
timeout=STRING|"120"
varname=STRING|"{SQLPERFORMANCE}"

[Actions\Action3]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file C:\vcentersql.txt"
Params=FOLDER

[Actions\Action3\Params]
encode=STRING|"0"
fileexists=STRING|"0"
filname=STRING|"C:\vcentersql.txt"
line0=STRING|"{SQLPERFORMANCE}"
linecount=STRING|"1"
suppress=STRING|"0"

[Events]
Event1=FOLDER

[Events\Event1]
Enabled=INTEGER|-1
EventID=STRING|"E_GENERAL_SHEDULER"
Params=FOLDER
UnigueID=INTEGER|16445953

[Events\Event1\Params]
id=STRING|"3Z3LJ0OD"
periodicity=STRING|"0"
startdate=STRING|"42026"
time0=STRING|"10808000"
time1=STRING|"25208000"
time2=STRING|"39608000"
time3=STRING|"54008000"
time4=STRING|"68408000"
time5=STRING|"82808000"
timecount=STRING|"6"

[Oleg]

1. I see that you are using 3 SQL expressions.

• - Use
  - Declare
  - Select

SQL Query action allows only one expression. Unfortunately RoboTask has no actions that cal run some sequence of SQL expressions in one DB Session. SQL Query action opens the session for expression and closes it after execution.
I think that you have to create stored procedure and use the expression like:
exec myStoredProcedure

2. Rows affected is only a number: how many rows affected by this query. It can be 0 or more than 0.

3. I think you want to get some text ("SELECT TOP(512)...") and save the lines into a file.
It this case you have to use the DataSet loop.
Algorithm will be like this

Code: Select all

text=''
Dataset loop
   text = {text}{DBValues}{EOL}   
end loop
Write text file ({Text})

[deesloop]

So it cannot return the results of the SQL query?
It can only come back and say that x rows are involved in the results?

[deesloop]

Apologies Oleg.
I used the Dataset loop and got the results appended to a text file.
Brilliant - thanks for the pointer