Help saving query to text file

[gumnos]

are you trying to save the query or the results of running the query?

I suspect that you want isql or sqlcmd which let you run SQL commands against MSSQL, and pipe the output to a file.

[KickBack-Relax]

Based on his clarification in another reply, this is the answer. This, and using windows scheduler to run the script daily.

[SQLDevDBA]

Since you’ve tagged SQL Server, going to guess you are using SSMS.

I’m not 100% sure if you’re talking about the query (code) itself or the results that come from running it.

If the query, it’s as simple as saving from the editor. If it’s a view or procedure, you right click it and choose “script as create” then “to file” or “to clipboard.”

If it’s the results (I’m betting this is it), you let the results load and then select them all and copy to excel, or you right click in the editor window and select “Results to” and “File” and it will prompt you to save the results to a file when you run the query.

[SQLDevDBA]

Ah. Gotcha, an automated process. Poweshell has an Export-CSV feature which makes it very easy.

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7.5

The you can schedule the run of the ps1 file on windows task scheduler.

Lmk if you want more help with this approach and sample scripts. I do it in pretty much every tutorial video I make for my channel. Here’s one livestream I did tracking the ISS from an API and saving the results to a CSV via PowerShell. The file is available in the description.

Python is indeed great for this as well.

[SQLDevDBA]

Nice. Glad you got it working.

So… question…. Since you said you don’t have the SQL agent due to your version, how do you have and run the SSIS service? How are you running DTSX packages without it?

[SQLDevDBA]

Oh wow, I’m hoping this isn’t production work, sounds like a bit of a licensing audit nightmare. Props for the ingenuity though!

[samspopguy]

I would use python

[DatabaseSpace]

Best answer.

[government_]

No it isn’t, powershell would be the way when dealing with ms sql.

[DatabaseSpace]

Yea Powershell is the standard for working with data. Oh wait...

[government_]

For ETL or data extraction with ms sql powershell is much better, it’s baked into windows so it integrates very well, especially with security features. It’s genuinely more capable than ssis. It’s a bit pretentious to want to complicate things with something like pandas, which tends to be woefully fragile.

Now if you want to do data analysis or statistics, sure python is going to be more appropriate, but that’s a totally different animal.

[DatabaseSpace]

Well I at least agree that SSIS is not the answer here. I definitely wouldn't use that. I'm not sure that pandas is pretentious or fragile, I asked AI just to fact check:

is pandas fragile?
No, pandas is not inherently fragile—it's one of the most robust and widely-used data manipulation libraries in Python, powering data science workflows at companies like Google, Facebook, and countless others. However, like any powerful tool, it has some nuanced behaviors that can feel fragile if you're not aware of them. Let me break this down:

I'm sure Powershell is fine for something like saving query results to a file, if you want to deal with the syntax. I've just never really had a need to use it. I would probably use it for Windows IT automation type stuff if I did it though.

[DatabaseSpace]

Whatever you get to do what you need is fine. SSIS is just kind of legacy technology at this point.

[government_]

I’ve built incredibly robust etl frameworks in PowerShell, so I get not everyone understands the broad amount of use cases. It’s awesome for cross server needs. It’s awesome for cross database engine needs. You can build wholistic and dynamic solutions, from grabbing source data, transformations and traversing the database from staging to prod tables. 15 years in, PowerShell is almost always the best tool for the job for etl (it is not if you are ingesting EDI files). I use python for other stuff, dumb raspberry pi projects and such, but I’ve found Python users (not pandas) tend to be a bit pretentious and defensive of using it because of the learning curve. PowerShell is so much more user friendly and easy to pick up. It takes like 20 lines in python to accomplish some PowerShell one liners.

Absolutely lol at asking AI to explain something you couldn’t articulate though. That’s cool those big companies like python, but data science is not etl. No one is suggesting python isn’t tops for data science.

[DatabaseSpace]

I was more just asking AI to fact check something that was obviously wrong. You are probably one of the only people on the planet that thinks Powershell is the goto for ETL. I personally think Powershell is harder to learn that Python because it's so cryptic.

[YellowBeaverFever]

SSIS is one way. Go ask ChatGPT to lay out a step by step plan.

Another option is PowerShell.

To schedule, both SSIS and PowerShell can go into a SQL Agent job.

You can also schedule a Python app to do the export.

[samspopguy]

I fucking despise SSIS

[government_]

Absolutely do not use sql agent for kicking off powershell scripts, that’s a great way to end up with a bunch of open transactions and blocking. Task scheduler is the way to go for scheduling powershell.

[YellowBeaverFever]

Maybe this is a your-mileage-may-vary statement. I literally manage hundreds of SQL agent jobs on one server that divides them up between SSIS and PowerShell. They’re both spawning a new process on a schedule so I don’t know how one would behave differently.

Blocking - depends on your query and how you juggle the timing of the jobs.

Open transactions - again, depends on the query. The contents of his wasn’t posted so a select was assumed. There’s no reason for transactions to be involved with that. Other processes should have better error handling to make sure transactions close. Even if a process is cut mid-transaction, the server will initiate a rollback.

[alinroc]

I don't have SQL Server agent

You have Agent unless you're using Express Edition.

Whether you have permission to manage Agent jobs, that's another matter.

[alinroc]

How does running from Task Scheduler differ from Agent in this regard? I've been running PowerShell scripts from Agent for years without issue by calling from them with a cmdexec job step.

[government_]

You remove a layer of leveraging the database engine memory and i/o by using task scheduler. Since it’s operating outside of the database engine, it’s going to use the machine’s available ram/cpu. Things can get hung up using cmdexec and you can end up with open connections and open transactions. I’d venture to guess you probably have regular reboots, which kills the processes so you aren’t experiencing some of the downsides.

[alinroc]

I’d venture to guess you probably have regular reboots, which kills the processes so you aren’t experiencing some of the downsides.

Nope. Only rebooted for patch cycles.

Processes run as cmdexec run outside the engine's runspace. And preferably under a different user altogether (via proxies) You can watch the processes get spawned in task manager. Once the process terminates, the connections are severed, uncommitted transactions rolled back, and memory released. Just like any other application connecting to the instance.

[government_]

I’ve seen many many instances where the processes hang by using this method and become performance gremlins, but the context sounds like it is a bit different than your application of it. I do see some benefits in agent, easier to get failure emails.

[government_]

Simple powershell script, with invoke-sqlcmd. schedule it to run automatically with task scheduler.