Difference between revisions of "Data Export from MS SQL Server"

From SmartHCM Wiki
Jump to navigation Jump to search
 
Line 5: Line 5:
 
declare @sql varchar(8000)
 
declare @sql varchar(8000)
  
SELECT @sql =' bcp "select  u.BADGENUMBER EMPLOYEE , CONVERT(VARCHAR, CHECKTIME,110) CHECK_DATE, CONVERT(VARCHAR, CHECKTIME,108) CHECK_TIME,c.CHECKTYPE from [att_db].[dbo].[CHECKINOUT] c, att_db.dbo.USERINFO u where u.USERID=c.USERID AND c.CHECKTIME > DATEADD(MONTH, -1, GETDATE())" queryout D:\TOM-FILE\Attendance-Data.txt -t -c -T'
+
SELECT @sql =' bcp "select  u.BADGENUMBER EMPLOYEE , CONVERT(VARCHAR, CHECKTIME,110) CHECK_DATE, CONVERT(VARCHAR, CHECKTIME,108) CHECK_TIME,c.CHECKTYPE from [att_db].[dbo].[CHECKINOUT] c, att_db.dbo.USERINFO u where u.USERID=c.USERID AND c.CHECKTIME > DATEADD(HOUR, -1, GETDATE())" queryout D:\TOM-FILE\Attendance-Data.txt -t -c -T'
  
 
exec master..xp_cmdshell @sql
 
exec master..xp_cmdshell @sql
 
</pre>
 
</pre>

Latest revision as of 06:25, 20 September 2021

Schedule following job on MS SQL Server for every hour. It take attendance data from central attendance server (ZKTeco iClock Automatic Data Master Server ADMS) for last one hour and export to text file which we can upload into SmartHCM using SmartHCM Attendance Utility file based feature;


declare @sql varchar(8000)

SELECT @sql =' bcp "select  u.BADGENUMBER EMPLOYEE , CONVERT(VARCHAR, CHECKTIME,110) CHECK_DATE, CONVERT(VARCHAR, CHECKTIME,108) CHECK_TIME,c.CHECKTYPE from [att_db].[dbo].[CHECKINOUT] c, att_db.dbo.USERINFO u where u.USERID=c.USERID AND c.CHECKTIME > DATEADD(HOUR, -1, GETDATE())" queryout D:\TOM-FILE\Attendance-Data.txt -t -c -T'

exec master..xp_cmdshell @sql