Problem with SQL command - MS SQL Server

Hi

I am facing issue for logging row count in txt file. I have to log row count with date time in txt file, but below query is logging only date time not row count.

Below select query will return 0 row count.

select count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

Open in new window


Below query is not logging row count 0 in txt file. Please help me to find the root cause of this problem. I have to schedule job to run every 30 minutes using below query, the query output need to append (concat) single file as showed in the attached file
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

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

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

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

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

Open in new window


I am herewith attaching Count_Final.txt
Count_Final.txt
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.

HuaMin ChenProblem resolverCommented:
Try the way below to truncate the date by removing the time:
sselect count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, datecolumn))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

Open in new window

Thean SAuthor Commented:
Hi Huamin,

modified query but still its not loging row count in txt file. I am herewith atatching output txt file here...
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

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

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

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

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

Open in new window

Count_Final.txt
HuaMin ChenProblem resolverCommented:
Can I know if you want to return records on CURRENT DATE? Validate the data of this as well.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Thean SAuthor Commented:
I have validated only select query with current date, validation result is 0. In SSMS i can see count as 0. But I am not sure why this count is not reflecting on txt file :)

My requirement: I have to fetch count of cases created for every 30 min interval for current date. Once SQL query is ready, I am planning to put query in SQL job and schedule to run every 30 min interval from 00 AM to 11:59 PM. The txt output file help me to analyse count as per my business need.

I am expecting output report looks like below (the output file may txt or csv)
Execution DateTime: 11122018 08:30
 0
Execution DateTime: 11122018 09:00
7
Execution DateTime: 11122018 09:30
200
Execution DateTime: 11122018 10:00
 210
Execution DateTime: 11122018 10:30
212

Is there any other solution to achieve this instead of above command?
HuaMin ChenProblem resolverCommented:
Is it true that you expect to compare the date field by using date part only (and skip time part)? If yes, you can use my way in above.
Thean SAuthor Commented:
only current date part will OK for me instead of comparing both date and time part.....Can you please help me with updated query
HuaMin ChenProblem resolverCommented:
What is returned in the query?
Thean SAuthor Commented:
tried with above query, but why it's not logging 0 (row count) in txt file?
HuaMin ChenProblem resolverCommented:
Try to remove clause "with (nolock)" in the query.
Thean SAuthor Commented:
Removed with (nolock), stil it's not displaying row count 0 in output txt file.
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from databaseName.dbo.TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

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

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

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

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

Open in new window

Untitled.png
Thean SAuthor Commented:
its displaying row count without where condition...whats problem with where condition?
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from databaseName.dbo.TableName with (nolock)

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

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

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

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

Open in new window

Untitled.png
HuaMin ChenProblem resolverCommented:
Try to put the where condition. Whole query is like
SELECT @Reccnt = count(*) from databaseName.dbo.TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

Open in new window

Thean SAuthor Commented:
no luck, still it's not displaying row count 0 in output file.
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from databaseName.dbo.TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

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

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

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\count_BO.txt', no_output

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

Open in new window

Untitled.png
HuaMin ChenProblem resolverCommented:
Try

use YourSchemaName
go

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

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

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

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\count_BO.txt', no_output

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

Open in new window

Snarf0001Commented:
Okay, well for some reason it seems to be capping based on the length / structure of the actual string.
If your quantity is a single character (less than 10), it's not printing it.  If you add " rows" or something to the end, then it will start displaying.

I'll be damned if I can tell you "why", as I've not used this feature and can't find any explanation, but that should solve your problem.

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

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

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

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

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

Open in new window

pcelbaCommented:
Interesting. Almost the same solution was published in https://www.experts-exchange.com/questions/29128960/help-required-on-MS-SQL-query.html#a42753342 a few hours ago.

Thean S could explain why he is ignoring both these solutions.

The problem is in ECHO command which works differently when you use single digit as a parameter. Single digit followed by space is OK, single letter is also OK.
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.