SQL Query Syntax error

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

My requirement:
1.      I have to fetch number of records created in half an hour interval from 12am to 11:59pm in every 30 minutes.
2.      We have make it dynamic to run every 30 minute.
3.      This job will give a report for everyday and every 30 mints interval in csv format

To achieve above, I have created below SQL but it's giving error. Can you please help me to fix below error.
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\YourData.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from IB where Notes = ''M_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

Error:
NULL
Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'IB'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed
NULL
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Zakaria AcharkiAnalyst Developer
Distinguished Expert 2018

Commented:
Have you tried to use dbname.dbo.tablename in the from clause?

Author

Commented:
Thanks Zakaria, it's working.

But it's overwriting previous file. I need all files on shared folder from 00:00:10.000 to 23:59:10.000 with date time format

Like below:
YourData_12102018_2030.csv
YourData_12102018_2100.csv
YourData_12102018_2130.csv
YourData_12102018_2200.csv

Can you please advise how to modify query. Thanks in advance.
Analyst Developer
Distinguished Expert 2018
Commented:
You could format the getdate() using FORMAT(), something like :

DECLARE @dt VARCHAR(500) = 'E:\FileShare\YourData_' + FORMAT(getdate(), 'ddMMyyyy_HHmm') + '.csv'

Open in new window


You're welcome, glad I could help.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
Thanks a lot Zakaria.

One more question, is it possible in SQL to add all time frame data in single file instead of creating multiple files on shared folder.

Like: in single file i need all count starting from 0:00:10.000 to 23:59:10.000

file should contain data like below in single file:
DateTime: 12102018 20:30
Count: 3

DateTime: 12102018: 21:00
Count 10

DateTime: 12102018: 21:30
Count: 7

I am not SQL expert, here I am seeking your advise on this. If it's possible in SQL, can you please share the sample script
Zakaria AcharkiAnalyst Developer
Distinguished Expert 2018

Commented:
You're welcome @Thean,

I'm not sure, but you could open a new specific question for this and I'm sure other SQL experts could help you in this.

Author

Commented:
--

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