help required on MS SQL query

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

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

Select allOpen 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

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) =

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...

Yes, you're correct

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'

Select allOpen in new window

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

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?

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.

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'

Select allOpen in new window

Output:

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

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

Select allOpen 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'

Select allOpen in new window

this code should generate count 3, but it's showing message as ECHO is on.

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')

Select allOpen in new window

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

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')

Select allOpen in new window

4. Replaced

count(*) from dbo.T1

Select allOpen 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'

Select allOpen 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'

Select allOpen 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

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')

Select allOpen in new window

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

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

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'

Select allOpen 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')

Select allOpen in new window

CSV output:

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'

Select allOpen 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

Select allOpen 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.

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

Select allOpen in new window

Its working in SSMS

why it's not working with ECHO command?

You may display the CSV file in SSMS directly:

EXECUTE master..xp_cmdshell 'TYPE E:\FileShare\Count_Final.csv'

Select allOpen in new window

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