help required on MS SQL query

Thean S
Thean S used Ask the Experts™
on
Hello,

I have created SQL job using below script to execute every 30 minutes interval from 12:00:00 AM and 11:59:59 PM. The job will create new CSV file on shared folder for every 30 minutes. Instead of creating new CSV file (multiple CSV) every time is it possible in SQL to concat (append) new data on existing file like below? If its possible in MS SQL, can you please share the sample script.

Like: data should be appended like below on single file:

Execution DateTime: 12102018 20:00
Count: 10

Execution DateTime: 12102018 20:30
Count: 10

Execution DateTime: 12102018 21:00
Count: 15

Execution DateTime: 12102018 21:30
Count: 10
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\Count_' + FORMAT(getdate(), 'ddMMyyyy_HHmm') + '.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from DBName.dbo.IB where Notes = ''BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
As you are using XP_CMDSHELL then it should be easy. Create a temp file in BCP and then append it to your final.csv. So you may just issue two more commands:

1. BCP out to some temporary file (temp.csv)
2. COPY final.csv+temp.csv final.csv
3. DEL temp.csv

Author

Commented:
Thanks pcelba.

I am not SQL scripting expert, can you please help me to modify above script with your solution.
You may add just four more lines:
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\Count_' + FORMAT(getdate(), 'ddMMyyyy_HHmm') + '.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from DBName.dbo.IB where Notes = ''BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

DECLARE @Copycmd VARCHAR(500) = 'COPY E:\FileShare\Count_Final.csv+' + @FileName + ' E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Copycmd

DECLARE Delcmd VARCHAR(500) = 'DEL ' + @FileName
EXECUTE master..xp_cmdshell @Delcmd

Open in new window

Empty E:\FileShare\Count_Final.csv file must exist prior to the first execution of the SQL job. You may create it by following command in CMD.EXE:

COPY nul E:\FileShare\Count_Final.csv
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I am getting below error while executing above query

Msg 155, Level 15, State 2, Line 8
'VARCHAR' is not a recognized CURSOR option.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Delcmd".
Of course, one missing @ …

Look for
DECLARE Delcmd VARCHAR(500) =  …

and update it to
DECLARE @Delcmd VARCHAR(500) =

Author

Commented:
thanks.

but the attached csv file is not showing date time in report. i want to display execution datetime in report. as i said i will schedule job to run every 30 minutes...i want to show this execution date time in report with count :)
Count_Final.csv
Ah OK, you don't need all the CSVs concatenated but just one info file which will contains the times and counts concatenated...

Author

Commented:
Yes, you're correct

Author

Commented:
data should be appended like below on single CSV file:

Execution DateTime: 12102018 20:00
Count: 10

Execution DateTime: 12102018 20:30
Count: 10

Execution DateTime: 12102018 21:00
Count: 15

Execution DateTime: 12102018 21:30
Count: 10
Then try this:
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd1

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\Count_Final.csv'

Open in new window

BTW, the date range is fixed in your query so it should always return the same number.

Author

Commented:
Now the count is NOT showing in attached CSV file.
Count_Final.csv
OK, what returns this command in SSMS?

SELECT count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

Do you really have database named DBname?

Author

Commented:
I am getting row count 3 for above select query. Yes I have DB with name of DBname (It's TEST env)

3 rows exist for above query condition in DB.

Author

Commented:
when I execute below query in SSMS, I am getting NULL output. But output (count) should be 3.
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd1

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\Count_Final.csv'

Open in new window

Output:
Untitled.png
The null output is from XP_CMDSHELL. It does not produce any visible output in this case.

If you would like to see the count then look at the output CSV file.
To see what is generated just execute in SSMS:
EXECUTE master..xp_cmdshell 'TYPE E:\TEMP\Count_Final.csv'
If you would like to suppress the NULL output in SSMS then add no_output clause to XP_CMDSHELL:
EXECUTE master..xp_cmdshell 'ECHO. >>E:\TEMP\Count_Final.csv', no_output

Author

Commented:
EXECUTE master..xp_cmdshell 'TYPE E:\TEMP\Count_Final.csv'

Open in new window

executed above code in SSMS, nothing is generated in CSV file. the file is blank.
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd1

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\Count_Final.csv'

Open in new window

this code should generate count 3, but it's showing message as ECHO is on.
Untitled.png
First of all the code in your previous post cannot produce output you are showing in the image attached. The time format is different and the ECHO should not appear on the output.

Please delete the E:\FileShare\Count_Final.csv file and try the code (from your previous post) again in SSMS.

Optionally add
PRINT ISNULL(CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Or better, try the following code in SSMS:
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from dbo.T1 

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\Count_Final.csv', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Open in new window

And also look at the output file E:\FileShare\Count_Final.csv.

Author

Commented:
1. deleted old Count_Final.csv from E:\FileShare
2. created new csv file using COPY nul E:\FileShare\Count_Final.csv
3. after executing below code, i can see Count: 5960063 in CSV file. In SSMS output, its showed Count: 5960063
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from dbo.T1 

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\Count_Final.csv', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Open in new window

4. Replaced
count(*) from dbo.T1

Open in new window

with
count(*) from dbo.T1 where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

Open in new window

, in CSV file there is no count, but in SSMS output, its showed Count: 3
5. executed below code in SSMS, i got row count 3 in SSMS output
select count(*) from dbo.T1 where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

Open in new window

6. please find the attached CSV file for execution output result for point 3 and 4.

Please correct me If i have missed any steps. My only concern is why it's not showing row count 3 in CSV file because in point 5, its returing row count 3 but this is not reflecting in CSV file.
Count_Final.csv

Author

Commented:
In where condition I have hard coded date and time. Can you please suggest how to pass getdate():12:00 AM and getdate():11:59 PM in above code?

like : CreatedOn between 'getdate() 00:00:00.000' and 'getdate 23:59:00.000'

I am not able to see row count 3 due to date format issue?

Appreciate your help to modify code with getdate()
Interesting!  The query from my post counts rows in dbo.T1 table which was in my test database and which also is in your Test database occasionally...

And it worked for you  when there was no additional condition.  Once you added the WHERE clause then it stopped working and returned nothing because no such columns are in your dbo.T1 table...

So first of all you have to replace the dbo.T1 by correct table from your database.

SELECT @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

So the final script is:
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\Count_Final.csv', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Open in new window

We may tune the date range once it stores Count 3 into the output file.

Author

Commented:
off course I have replaced my table with dbo.T1  before execution and before posting above steps :)

Author

Commented:
Final script also not logging row count in CSV file. Note that, I am replacing correct table name and DB name while execution.

for below query I am getting row count 3 in SSMS.
select count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

Open in new window

I am not sure why main query (below query) is not logging row count:3 in CSV file.
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\Count_Final.csv', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Open in new window

CSV output:image.png

Author

Commented:
Hi Pcelba, did you get a chance to review above code once again?
Hi, the count isn't recorded because the command  "SELECT @Reccnt = count(*) from …. " fails most likely for whatever reason. Thus the @Reccnt contains NULL and the output is empty.

So we have to investigate why this happens...

If this command works in SSMS:
select count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'

Open in new window


Then it must also work the same command which stores the COUNT(*) into a variable in SSMS:
DECLARE @Reccnt int
select @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'
PRINT 'Result is below:'
PRINT @Reccnt

Open in new window

If the result does not show any number just the text Result is below: then something is wrong and you should observe some error message in SSMS.

Author

Commented:
DECLARE @Reccnt int
select @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'
PRINT 'Result is below:'
PRINT @Reccnt

Open in new window

Its working in SSMSUntitled.png

Author

Commented:
why it's not working with ECHO command?
The ECHO command is same as the previous one and it works for me... because the single digit is followed by space. This is some undocumented ECHO command inconsistency.

So you should extend the previous code to:
DECLARE @Reccnt int
select @Reccnt = count(*) from DBName.dbo.IB where Notes = 'BO' and CreatedOn between '2018-11-22 00:00:10.000' and '2018-11-22 23:59:10.000'
PRINT 'Result is below:'
PRINT @Reccnt
DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + ISNULL(CAST(@Reccnt AS varchar(10)), 'Unknown Reccnt') + ' >>E:\FileShare\Count_Final.csv'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

Open in new window

And look into the output .csv file.
You may display the CSV file in SSMS directly:
EXECUTE master..xp_cmdshell 'TYPE E:\FileShare\Count_Final.csv'

Open in new window

Author

Commented:
Perfect, it's working. You're the awesome :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial