I have SQL querry that I want to run on a scheudle ever other day

Question

I have SQL querry that I want to run on a scheudle ever other day

Gary Henry 21

Hi

Not sure if this is the right place to ask so if not I hope someone can direct me...

I have a simple sql query that was written for me (see below) that I now need to run on a schedule and export the data to a .CSV file with Headers and save it so I can use it in another process. It looks like I can do this in poweshell but I do not know how to take the below script and either reference the script itself or put the script into powershell.

I only have ReadOnly access to the SQL database.

I can run this from the management console, but I have the need now to automate the process

USE lc_V3

SELECT

   Items.iID AS product_id,

   LTRIM(RTRIM(s.ProductNumber)) AS mpn

FROM Items

LEFT JOIN POS..Skus s ON Items.strSku = s.Sku

WHERE ISNULL(LTRIM(RTRIM(s.ProductNumber)), '') <> '';

Any help guidance or direction would be welcome

Gary

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Marcin Policht 90,150 MVP Volunteer Moderator

Step 1: Create the PowerShell script (Export-Query.ps1)

# Parameters
$SqlServer = "YOUR_SQL_SERVER_NAME"
$Database = "lc_V3"
$OutputFile = "C:\Exports\Products.csv"

# SQL Query
$Query = @"
SELECT
   Items.iID AS product_id,
   LTRIM(RTRIM(s.ProductNumber)) AS mpn
FROM Items
LEFT JOIN POS..Skus s ON Items.strSku = s.Sku
WHERE ISNULL(LTRIM(RTRIM(s.ProductNumber)), '') <> '';
"@

# Load SQL client
$connectionString = "Server=$SqlServer;Database=$Database;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = $connection.CreateCommand()
$command.CommandText = $Query

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$table = New-Object System.Data.DataTable

# Fill table with results
$adapter.Fill($table) | Out-Null

# Export to CSV with headers
$table | Export-Csv -Path $OutputFile -NoTypeInformation -Encoding UTF8

Write-Host "Export completed: $OutputFile"

Step 2: Run the script

Save the script as Export-Query.ps1.

Run it manually first:

  powershell.exe -ExecutionPolicy Bypass -File "C:\Path\Export-Query.ps1"

Step 3: Automate with Task Scheduler

Open Task Scheduler.
Create a new task → Trigger it (daily, hourly, etc.).

Action → Start a Program → Program: powershell.exe Arguments:

   -ExecutionPolicy Bypass -File "C:\Path\Export-Query.ps1"

If your SQL login uses SQL authentication instead of Windows Auth, change the connection string:

  $connectionString = "Server=$SqlServer;Database=$Database;User Id=USERNAME;Password=PASSWORD;"

Make sure the folder C:\Exports\ exists (or update $OutputFile path).

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Gary Henry 21 Reputation points

2025-09-30T01:43:25.0466667+00:00

THANK YOU so much.... I tried to do this but it did not work but I have some questions...I am afraid that my questions below are going to be very simplistic because I really do not know SQL.... so I am sorry!!!

I changed your script to enter the server and correct database name SQL password is reacted

I am not sure how to format the server and Database name - Note I am running this script on the same machine as the SQL server. This is what the logon screen looks like to the managemnet console

when I run the script I get these errors... Do I need to load a SQL PowerShell module or something else??

I cannot tell you how much I appreciate your help!
Marcin Policht 90,150 Reputation points MVP Volunteer Moderator

2025-09-30T11:35:40.4933333+00:00
The first error (connectionString : The term ... is not recognized) is because you didn't prefix the variable with $. It should be $connectionString = ..., not connectionString = .... The second error (Login failed. The login is from an untrusted domain...) is because your script was trying to use Integrated Security (Windows authentication) on a SQL login that isn't in your domain. You actually do have SQL credentials (PAReadOnly2 + password), so you need to use SQL authentication instead of Integrated Security.

What you have already (System.Data.SqlClient) works fine out-of-the-box with PowerShell. No need to install SQL Server PowerShell module unless you want extra cmdlets.

Here's a corrected version (with SQL authentication):

# Parameters $SqlServer = "ACESERVER\AMBERHOS" # same format you use in SSMS login screen $Database = "lc_V3_acepho" $OutputFile = "C:\Exports\Products.csv" # SQL credentials $SqlUser = "PAReadOnly2" $SqlPassword = "YOUR_PASSWORD_HERE" # SQL Query $Query = @" SELECT Items.iID AS product_id, LTRIM(RTRIM(s.ProductNumber)) AS mpn FROM Items LEFT JOIN POS..Skus s ON Items.strSku = s.Sku WHERE ISNULL(LTRIM(RTRIM(s.ProductNumber)), '') <> ''; "@ # Build connection string (SQL auth) $connectionString = "Server=$SqlServer;Database=$Database;User Id=$SqlUser;Password=$SqlPassword;" # Open connection and run query $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $command = $connection.CreateCommand() $command.CommandText = $Query $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command $table = New-Object System.Data.DataTable $adapter.Fill($table) | Out-Null # Export to CSV with headers $table | Export-Csv -Path $OutputFile -NoTypeInformation -Encoding UTF8 Write-Host "Export completed: $OutputFile"

To validate the Server Name format, from your SSMS login screen:

If you connect using ACESERVER\AMBERHOS → use that for $SqlServer.

If you connect using just ACESERVER → use that.

If you connect using localhost → then use localhost.

Basically: whatever string appears in Server name box in SSMS that works, use the same in $SqlServer.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Gary Henry 21 Reputation points

2025-09-30T12:33:34.3733333+00:00

Again, Thank you and thank your for the explanation..

I am still getting an error...

Unexpected token 'Export' in expression or statement.

Did I make a typo?
Marcin Policht 90,150 Reputation points MVP Volunteer Moderator

2025-09-30T12:46:58.1233333+00:00

Looks like you're missing double quotes at the end of $connectionString = "Server=$SqlServer;Database=$Database;User Id=$SqlUser;Password=$SqlPassword;"

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Gary Henry 21 Reputation points

2025-09-30T12:55:41.45+00:00

well.... you caught me :) it was there but I was tiring to troubleshoot this error
Gary Henry 21 Reputation points

2025-09-30T13:45:04.92+00:00

I figured out the error..... there was a ":" instead of a "

Was - Write-Host "Export completed: $OutputFile

Now - Write-Host "Export completed" $OutputFile

Once I fixed that... it works

thank you so much and I learned a lot on the process!!!!
Marcin Policht 90,150 Reputation points MVP Volunteer Moderator

2025-09-30T14:10:26.76+00:00

Glad to see

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Gary Henry 21 Reputation points

2025-10-01T16:11:12.4433333+00:00

I am having one little problem with the output of the script... It looks like the format has quotes around the fields and it is causing my import program throw errors. Certainly the second field may or not need quotes and it is OK it is imported as text but the first field is a KEY and it is causing small issues

is this something that can be tweaked in the script?

this is the output from SSMS

this is the output from the powershell script

thanks again!

Gary
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2025-10-01T21:03:24.3166667+00:00
It doesn't seem that Export-csv has an option to suppress the quotes.

But since it is only two columns you could replace the call to Export-Csv with this simple code:

$table.Columns[0].ColumnName + ',' + $table.Columns[1].ColumnName foreach ($row in $table.Rows) { $row.ItemArray[0].ToString() + ',' + $row.ItemArray[0].ToString() }

It does not write to a file, but you could redirect the output of the script to a file. (The reason I don't write to a file is that I am too lazy to look that up. I'm only on beginner level when it comes to PowerShell.)

Marcin may have a slicker solution.

Share via

I have SQL querry that I want to run on a scheudle ever other day

0 additional answers

Your answer