[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Query, save results to csv file, using a sql job

Posted on 2016-09-27
12
Medium Priority
?
329 Views
Last Modified: 2016-11-03
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.
0
Comment
Question by:jnikodym
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 13

Expert Comment

by:Dustin Saunders
ID: 41818559
You could do this pretty easily with Powershell.

function GetSQL ($query)
{
    $Connection = new-object system.data.sqlclient.sqlconnection
    $Connection.ConnectionString = "server='servername';database='database';trusted_connection=true;"
    $Connection.open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $Command.CommandText = $query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $Command
    $Command.Connection = $Connection
    $data = New-Object System.Data.DataTable
    $SqlAdapter.Fill($data) | Out-Null
    $Connection.Close()
    return, $data
}

$query = "SELECT * FROM Table"
$outFile = "C:\Path\out.csv"
$result = GetSQL $query
$result | Export-CSV $outFile -NoType

Open in new window


Just schedule that guy and off you go.
0
 

Author Comment

by:jnikodym
ID: 41818577
So, do i only need to change the $query = line and the $outFile = line?

Do i set that up as a job in SQL?
0
 
LVL 13

Expert Comment

by:Dustin Saunders
ID: 41818591
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.ConnectionString = "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.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:jnikodym
ID: 41818619
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?
0
 
LVL 13

Expert Comment

by:Dustin Saunders
ID: 41818627
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
0
 

Author Comment

by:jnikodym
ID: 41818636
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: (:) , ParentContainsErrorRecordException
FullyQualifiedErrorID   : Unauthorizedaccess
0
 
LVL 13

Expert Comment

by:Dustin Saunders
ID: 41818649
Ok, you'll need to enable powershell on the system.

In ISE, open a new tab (the white page) and paste this:
Set-ExecutionPolicy RemoteSigned

Open in new window


Then go back to the script and try it again.
0
 

Author Comment

by:jnikodym
ID: 41818679
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.
0
 
LVL 13

Accepted Solution

by:
Dustin Saunders earned 2000 total points
ID: 41818691
The quotes are a standard part of the CSV format, when you import a csv it'll ignore them.  but if you want the file to not have them we'd have to fudge the format with something like:

function GetSQL ($query)
{
    $Connection = new-object system.data.sqlclient.sqlconnection
    $Connection.ConnectionString = "server='server';database='database';trusted_connection=true;"
    $Connection.open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $Command.CommandText = $query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $Command
    $Command.Connection = $Connection
    $data = New-Object System.Data.DataTable
    $SqlAdapter.Fill($data) | Out-Null
    $Connection.Close()
    return, $data
}

$query = "SELECT * FROM table"
$outFile = "C:\Path\out.csv"
$result = GetSQL $query
$result | ConvertTo-Csv -Delimiter "|" -NoTypeInformation | % {$_.Replace('"','')} | Out-File $outFile

Open in new window

0
 

Author Closing Comment

by:jnikodym
ID: 41818714
Thank you for your help
0
 
LVL 13

Expert Comment

by:Dustin Saunders
ID: 41818721
No problem, glad to help.
0
 

Expert Comment

by:Steve Brown
ID: 41871963
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.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question