May 13, 2011 at 5:58 pm
I'm not sure which forum I needed to post this question in so I decided to start here.
I've created a script that I manually run every week in SSMS that produces a csv file that looks like this...
"128","1117083","04/19/2011","705.25","Check No","990589"
I'm being pressed to figure out how to let our accounts payable people create this file whenever they want. They would need to be able to edit the dates and change one of the parameters in the where clause. If I need to create 2 scripts so that all they have to change is the date range, I can do that. I want to do this without giving them access to SQL tools.
Is there a way I can create a batch file that does this? Are there any other tools that I could give them other than installing SQL tools for them? If the batch file prompted them for a date, that would be awesome but if I need to have them modify the date in the batch file, that's cool too.
I'm not looking for a step by step response to this thread. If there's an article that discusses this, please post the url. I'm having a hard time figuring out how to google for an answer to this task.
TIA,
John
May 13, 2011 at 8:31 pm
This could be done using a batch file and calling out to SQLCMD. However, you would have to get that installed on each client's desktop.
You could also use a Powershell scripts - but again, this is going to have to be installed on each client's desktop.
You could also use SSRS and create a report. Then, they can export the report in any of several formats - but most likely Excel would be ideal and is probably what they want the data in anyways.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 16, 2011 at 4:42 pm
Actually Excel isn't the format that they need it in. Our AP people will only open that file momentarily to make sure the amounts tie to a report in their application. When it ties out, they will close it and upload it for a computer/server to process it.
I need to use the double quotation mark as a text delimiter for every field (while using commas to separate each field). I noticed that when I export into a csv file, the double quotation mark is only used around fields where a comma is part of the data. Is there anyway to force the double quotation mark around every field?
I think using SSRS would be the best way to get this done but I need to figure out how to format the data with double quotes. The reason why I think SSRS would be best in this case is because the person that normally would run this uses a thin client to connect to a Citrix session.
If I can't get the SSRS method to work, I'll have to look into SQLCMD.
May 16, 2011 at 8:15 pm
The only way I can think of to guarantee that format would be to create your query with a single column where you concatenate every column into the result.
SELECT quotename(column1, """") + "," + quotename(column2, """") + "," + ...
FROM ...
WHERE ...
Then, in the report you would just display a single column and instruct the user to export to a text file when done.
A bit kludgy - but should work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply