MicrosoftHi All,
I created elastic job that store data of system table sys.syscomments. I am facing issue while inserting the output on the table by using elastic Job. i can see the job showing success but output data is not inserting on the table. I am executing the script select * from sys.syscomments for each databases on the server.
I am using the below script:-
EXEC jobs.sp_add_job @job_name ='job_1', @description='get data';
exec JOBS.sp_update_jobstep
@job_name= 'job_1',
@step_name ='JobStep',
@command= N'SELECT
ROW_NUMBER() OVER(PARTITION BY DB_NAME(o.parent_object_id) ORDER BY ID) AS Rownumber,
@@servername AS ServerName,
DB_Name() AS DatabaseName,
s.name AS SchemaName,
o.name AS ObjectName,
sc.ID AS ObjectID,
o.type_desc,
CONVERT(VARCHAR(max), text) AS Code,
getdate() as created_Date,
FROM sys.syscomments sc
JOIN sys.objects o ON sc.id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id',
@credential_name='ElasticJobUserCredential',
target_group_name='ShardServerGroup1',
@output_type='SqlDatabase',
@output_credential_name='ElasticJobMasterCredential',
@output_server_name='sharepool1.database.windows.net',
@output_database_name='output database',
@output_schema_name='dbo',
@output_table_name='output table',
@retry_attempts = 1;
Could you help me how is there any solution? Data is not inserting on the output table. table is showing blank after job succeeded.
