Problem with SQL command - MS SQL Server

Thean S
Thean S used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver

Commented:
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

Author

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 resolver

Commented:
Can I know if you want to return records on CURRENT DATE? Validate the data of this as well.
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 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 resolver

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

Author

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 resolver

Commented:
What is returned in the query?

Author

Commented:
tried with above query, but why it's not logging 0 (row count) in txt file?
HuaMin ChenProblem resolver

Commented:
Try to remove clause "with (nolock)" in the query.

Author

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

Author

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 resolver

Commented:
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

Author

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 resolver

Commented:
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

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

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.

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