help required on MS SQL query

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

Thean SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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
Thean SAuthor Commented:
Thanks pcelba.

I am not SQL scripting expert, can you please help me to modify above script with your solution.
pcelbaCommented:
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
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Thean SAuthor 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".
pcelbaCommented:
Of course, one missing @ …

Look for
DECLARE Delcmd VARCHAR(500) =  …

and update it to
DECLARE @Delcmd VARCHAR(500) =
Thean SAuthor 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
pcelbaCommented:
Ah OK, you don't need all the CSVs concatenated but just one info file which will contains the times and counts concatenated...
Thean SAuthor Commented:
Yes, you're correct
Thean SAuthor 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
pcelbaCommented:
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.
Thean SAuthor Commented:
Now the count is NOT showing in attached CSV file.
Count_Final.csv
pcelbaCommented:
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?
Thean SAuthor 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.
Thean SAuthor 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
pcelbaCommented:
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.
pcelbaCommented:
To see what is generated just execute in SSMS:
EXECUTE master..xp_cmdshell 'TYPE E:\TEMP\Count_Final.csv'
pcelbaCommented:
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
Thean SAuthor 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
pcelbaCommented:
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.
Thean SAuthor 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
Thean SAuthor 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()
pcelbaCommented:
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.
Thean SAuthor Commented:
off course I have replaced my table with dbo.T1  before execution and before posting above steps :)
Thean SAuthor 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
Thean SAuthor Commented:
Hi Pcelba, did you get a chance to review above code once again?
pcelbaCommented:
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.
Thean SAuthor 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
Thean SAuthor Commented:
why it's not working with ECHO command?
pcelbaCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
You may display the CSV file in SSMS directly:
EXECUTE master..xp_cmdshell 'TYPE E:\FileShare\Count_Final.csv'

Open in new window

Thean SAuthor Commented:
Perfect, it's working. You're the awesome :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.