February 3, 2022 at 8:44 pm
Different how? Different filters? different columns?
IF @Selection = 1
BEGIN
SELECT...
END
IF @Selection = 2
BEGIN
<other> SELECT...
END
Different filters would be a whole lot easier. Maybe use COALESCE() to ignore some empty parameter values and build the filter string?
February 3, 2022 at 8:47 pm
Would I be able to give each row a Label?
,CASE WHEN datepart(hour, dt.dt_stamp) < 8 THEN 1
WHEN datepart(hour, dt.dt_stamp) < 16 THEN 2
ELSE 3
END as 'HrReported'
Instead of having a 1,2,3 I want something like 'MidNightTo8:00am' for 1.
Thx.
Yes - you can return whatever values you want from the expression as long as all of them resolve to the same data type.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 3, 2022 at 8:47 pm
how could I wrap the S/P in Powershell to accomplish that task?
February 3, 2022 at 8:56 pm
how could I wrap the S/P in Powershell to accomplish that task?
I can think of a couple of ways - pull the full list, loop over each item - check old vs new category and when new category found, create new file and output to the new file.
Or - output full results to a variable and loop through each possible category. Filter the results from the variable on output...
Probably several other approaches available...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 3, 2022 at 8:58 pm
Thanks..
Could you crave out a template to get me started?
February 3, 2022 at 9:00 pm
Thanks..
Could you crave out a template to get me started?
Sorry - I am not in a position right now where I can write any code. These are all things you have done before in the previous scripts we have worked on, and you can easily google for the rest of it.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 4, 2022 at 8:58 pm
Thanks for comments and understand..
Jeffrey,
Would this method work to get my 3 output files?
function ConnectToDB {
# define parameters
param(
[string]
$servername,
[string]
$database
)
# create connection and save it as global variable
$global:Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$servername';database='$database';trusted_connection=tue;integrated security='true'"
$Connection.Open()
Write-Verbose 'Connection established'
}
# function that executes sql commands against an existing Connection object;
function ExecuteSqlQuery {
# define parameters
param(
[string]
$sqlquery
)
Begin {
If (!$Connection) {
Throw "No connection to the database detected. Run command ConnectToDB first."
}
elseif ($Connection.State -eq 'Closed') {
Write-Verbose 'Connection to the database is closed. Re-opening connection...'
try {
# if connection was closed (by an error in the previous script) then try reopen it for this query
$Connection.Open()
}
catch {
Write-Verbose "Error re-opening connection. Removing connection variable."
Remove-Variable -Scope Global -Name Connection
throw "Unable to re-open connection to the database. Please reconnect using the ConnectToDB commandlet. Error is $($_.exception)."
}
}
}
Process {
#$Command = New-Object System.Data.SQLClient.SQLCommand
$command = $Connection.CreateCommand()
$command.CommandText = $sqlquery
Write-Verbose "Running SQL query '$sqlquery'"
try {
$result = $command.ExecuteReader()
}
catch {
$Connection.Close()
}
$Datatable = New-Object "System.Data.Datatable"
$Datatable.Load($result)
return $Datatable
}
End {
Write-Verbose "Finished running SQL query."
}
}
# 2 BEGIN EXECUTE (CONNECT ONCE, EXECUTE ALL QUERIES)
ConnectToDB -servername 'srvr1' -database 'cost'
ExecuteSqlQuery -sqlquery 'Qry1' | export-csv -Path c:\temp\file1.csv -NoTypeInformation #
ExecuteSqlQuery -sqlquery 'Qry2' | export-csv -Path c:\temp\file2.csv -NoTypeInformation #
ExecuteSqlQuery -sqlquery 'Qry3' | export-csv -Path c:\temp\file3.csv -NoTypeInformation #
# 3 CLEANUP
$Connection.Close()
Remove-Variable -Scope Global -Name Connection
February 5, 2022 at 4:00 pm
Not sure if that will work or not - you need to test and validate. I personally wouldn't setup code to execute SQL queries - I would use the built-in function Invoke-SqlCmd.
The way you have the query defined - you should only need to call it once to get all data returned.
$queryResults = Invoke-SqlCmd ...;
Then something like this would be one option (probably better options are available):
$queryResults | ? {$_.Category -eq 1} | Export-Csv ...;
$queryResults | ? {$_.Category -eq 2} | Export-Csv ...;
$queryResults | ? {$_.Category -eq 3} | Export-Csv ...;
Or - modify your query to accept a parameter where you pass in the category to be returned:
Invoke-SqlCmd ... -Query "Execute dbo.YourQuery @Category = 1" | Export-Csv ...
Or - loop over the query results and output row by row...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 7, 2022 at 1:17 am
something like this?
#Variables - details of the connection, stored procedure and parameters
$connectionString = "server=ServerName;database='DatabaseName';trusted_connection=true;";
$storedProcedureCall = "exec dbo.GetFileNames @param1";
$param1Value = "SomeValue";
#SQL Connection - connection to SQL server
$sqlConnection = new-object System.Data.SqlClient.SqlConnection;
$sqlConnection.ConnectionString = $connectionString;
#SQL Command - set up the SQL call
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$sqlCommand.Connection = $sqlConnection;
$sqlCommand.CommandText = $storedProcedureCall;
$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);
#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet)
#Close SQL Connection
$sqlConnection.Close();
#Get single table from dataset
$data = $dataSet.Tables[0]
#File creation variables
$folderLocation = "C:\NewFiles\";
#Loop through each row of data and create a new file
#The dataset contains a column named FileName that I am using for the name of the file
foreach($row in $data)
{
$fullFileName = $folderLocation + $row.FileName + ".txt"
$newFile = New-Item $fullFileName -ItemType file
February 7, 2022 at 7:21 pm
Can't answer that question - have you tested it and does it work as expected? All I can do is provide suggestions - you need to implement a solution that works for your requirements and is something you can support and maintain.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 7, 2022 at 7:44 pm
Heh... wow... this post sure has taken a turn. What ever happened to "Get records by week"?
--Jeff Moden
February 7, 2022 at 8:04 pm
It started with grouping records by week to see the amount of data, and once I saw that I needed it to break down by day in 8 hour increments. Now with the CASE selection I'm trying to figure out a way to dump that amount of information in 3 separate files by hours.
I'm looking to P?S to do that but not sure I'm on the right track and how to get information from SP over to P/S.
Thanks.
February 7, 2022 at 8:41 pm
It started with grouping records by week to see the amount of data, and once I saw that I needed it to break down by day in 8 hour increments. Now with the CASE selection I'm trying to figure out a way to dump that amount of information in 3 separate files by hours. I'm looking to P?S to do that but not sure I'm on the right track and how to get information from SP over to P/S.
Thanks.
So who is going to consume the 3 CSV files that you want and what tool will they be using to consume them?
Also, is it against the law for you to use XP_CmdShell? And what are the actual rules for what you're calling CSV formatting with regards to double quotes and "embedded" delimiters in the data? Can it it TAB separated instead of COMMA separated?
--Jeff Moden
February 7, 2022 at 9:54 pm
BTW - don't use this:
$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);
.AddWithValue defaults to nvarchar data type and will cause all sorts of performance issues.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 8, 2022 at 12:09 am
I'm not opposed to xp_cmdshell, and was wanting the csv files to be pipe(|) delimited. These 3 files will be used in a P/S import
process to SQL as kind of a poor-man-replication. The source which is collecting the information is being updated\inserted so heavily
I'm going to run this new process for previous day to update a backup/report server.
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply