March 12, 2023 at 10:43 pm
while still putting emphasis on what Jeff stated with regards to company data on a local PC I am still supplying what I consider would be the better approach to generate the files.
there is much to be said about this company data on local pc - almost any user that has access to any company data ends up with having it on their PC's - simple fact of opening a excel spreadsheet with sales results for example does put company data on the PC. this subject alone is out of scope on this thread (and it would be a very very long subject).
in my opinion any situation that required an output file to be generated from any SP being executed should normally (exceptions exist) be done outside SQL Server - being it through a SQL Server Agent job or through an external application.
solution below can be executed on a SSA job
<#
this script requires SQLSERVER PS Module to be installed - SQLPS shipped with SQL Server does not contain all required functionality
install-module SQLSERVER
Latest versions of SSMS now use the lastest PS module which should contain the required
note that if using a SQL Server Agent job Powershell step this WILL NOT work - running from SQL Agent should be done through a command line script step which kicks off normal powershell
the sql statements to execute can be a combination of multiple SQL Statements - directly selects, execution of SP's and so on
#>
Import-Module SQLSERVER
$ServerInstance = "xxxx" # sql server instance in the format of SERVER or SERVER\INSTANCENAME to connect to
<#
multiple recordsets returned
add the -OutputAs Dataset so we have multiple tables being returned and we can process each one individually
#>
$sql = "select serverproperty('MachineName') machinename
, serverproperty('ProductVersion') productversion
, serverproperty('InstanceName') instancename
, serverproperty('InstanceDefaultDataPath') instancedefaultdatapath
, serverproperty('InstanceDefaultLogPath') instancedefaultlogpath
, db_name() databasename
;
select name
from msdb.sys.databases;"
$ds = SQLSERVER\invoke-sqlcmd -ServerInstance $ServerInstance -Query $sql -OutputAs dataset
$ds.tables.table[0]|Export-Csv -Path "c:\temp\serverproperties.csv" -NoTypeInformation -Encoding ASCII
$ds.tables.table[1]|Export-Csv -Path "c:\temp\databasenames.csv" -NoTypeInformation -Encoding ASCII
# single recordset returned -- output directly to the file
$sql = "select name
, compatibility_level
, collation_name
, is_read_only
, state_desc
, snapshot_isolation_state_desc
, is_read_committed_snapshot_on
, recovery_model_desc
from sys.databases;"
$ds = SQLSERVER\invoke-sqlcmd -ServerInstance $ServerInstance -Query $sql|Export-Csv -Path "c:\temp\databasedetails.csv" -NoTypeInformation -Encoding ASCII
Thank you for this.
To be honest, I am not really sure how to even use this. I am a rookie when it comes to SS. I have been using Access for years but got fed up with its limitations so I decided to abandon Access and start using SS. No looking back since I started using SS. Its way better.
Is there a way to get the tables out to CSV using a stored procedure? Any help you can offer is greatly appreciated.
March 13, 2023 at 3:34 am
i found a way better way to do the same thing.
https://analytics4all.org/2018/03/10/ssis-lesson-1-export-to-csv/
it works and its safe. none of the headaches with the original approach I was trying earlier
March 13, 2023 at 3:10 pm
You're missing the fundamental concept of a tiered architecture. Display formatting is done in a separate layer from the database. Yes, I know in the old days with COBOL and Fortran, we did this sort of formatting for inputs and outputs in the program itself. That ended sometime in the early 1970s.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply