jnikodym
asked on
SQL Query, save results to csv file, using a sql job
I have a query that i need to save the results to a csv file and have that happen on a scheduled time. I want to create a sql job that will do this and overwrite the file each time the job runs.
ASKER
So, do i only need to change the $query = line and the $outFile = line?
Do i set that up as a job in SQL?
Do i set that up as a job in SQL?
This is a powershell file, so you'd save the text as a .ps1 file and then you can schedule the job in Windows task scheduler.
You need to change $query, $outFile and then these 2
$Connection.ConnectionStri ng = "server='servername';database='database';trusted_connection=true; "
To schedule the job, schedule the process powershell.exe -file and then the path to the .ps1 script after it. i.e.:
powershell.exe -file c:\Scripts\thisScript.ps1
You also need to schedule it to run whether the user you create the task as is logged on or not.
You need to change $query, $outFile and then these 2
$Connection.ConnectionStri
To schedule the job, schedule the process powershell.exe -file and then the path to the .ps1 script after it. i.e.:
powershell.exe -file c:\Scripts\thisScript.ps1
You also need to schedule it to run whether the user you create the task as is logged on or not.
ASKER
i created the .ps1 file and setup the windows scheduled task. I ran the task, it says it was successful, but i don't have a .csv file in my folder. Do i need to remove the quotation marks around the query and file location?
Did you schedule the user to run the task with highest privileges?
Built in to windows is the Powershell ISE, can you open your .ps1 file with that and run it and see if it outputs any errors? (on server 2012+ just type in "ise" at the start menu to pull it up.
iseInstruction.png
Built in to windows is the Powershell ISE, can you open your .ps1 file with that and run it and see if it outputs any errors? (on server 2012+ just type in "ise" at the start menu to pull it up.
iseInstruction.png
ASKER
i pulled up the .ps1 file using "ise", and ran the file. I get an error that says:
File C:\files\customer.ps1 cannot be loaded because running scripts is disable on this system
CategoryInfo : SecurityError: (:) , ParentContainsErrorRecordE xception
FullyQualifiedErrorID : Unauthorizedaccess
File C:\files\customer.ps1 cannot be loaded because running scripts is disable on this system
CategoryInfo : SecurityError: (:) , ParentContainsErrorRecordE
FullyQualifiedErrorID : Unauthorizedaccess
Ok, you'll need to enable powershell on the system.
In ISE, open a new tab (the white page) and paste this:
Then go back to the script and try it again.
In ISE, open a new tab (the white page) and paste this:
Set-ExecutionPolicy RemoteSigned
Then go back to the script and try it again.
ASKER
Ok, we are getting there. That created the file. The problem is it is putting quotation marks around all of my data in the file. I would like it to put a pipe in between each column of data.
For example:
This is what it is doing
"Name", "Age", "Year"
"Smith", "55", "1965"
"Jones", "44", "1977"
I would like it to do this in the csv file
Name | Age | Year
Smith | 55 | 1965
Jones | 44 | 1977
I'm able to export the file like this in sql by changing the delimiter.
For example:
This is what it is doing
"Name", "Age", "Year"
"Smith", "55", "1965"
"Jones", "44", "1977"
I would like it to do this in the csv file
Name | Age | Year
Smith | 55 | 1965
Jones | 44 | 1977
I'm able to export the file like this in sql by changing the delimiter.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help
No problem, glad to help.
You could also achieve this using SQLCMD. Save your query as a .SQL file and then set a scheduled task to execute on the schedule(s) you want.
The SQLCMD would be something along the lines of
SQLCMD -S <servername> -E -d <databasename> -i <scriptfile> -h -1 -o <outputfile
This is something I have had to use on occasions to pass data between systems.
The SQLCMD would be something along the lines of
SQLCMD -S <servername> -E -d <databasename> -i <scriptfile> -h -1 -o <outputfile
This is something I have had to use on occasions to pass data between systems.
Open in new window
Just schedule that guy and off you go.