February 18, 2021 at 5:17 pm
Jeffery,
I don't know if the recipient of the data truly needs it sorted in order by the Row_ID field. I'll have to ask them or if it's nearly an identifier to help us identify a record that might have a problem.
I'm going to try and give your PowerShell ideas a try, too.
Thank you all for your suggestions.
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
February 18, 2021 at 5:31 pm
The problem is your header record...if you don't have an ORDER BY on the query then that row could be returned by SQL Server as the first row, the last row - or anywhere in the middle all depending on how SQL Server decides to process the results.
This construct:
SELECT ... FROM ...
UNION ALL
SELECT ... FROM ...
Is not guaranteed to return the first SELECT as the first row(s) - and the second SELECT as the last rows.
For this use case...the only order that matters is for the 'first' row to be the header and the rest of the rows to be the detail data. But if you had - for example - a claim header/detail scenario then you would want this:
9999|Header Row
9999|Claim1|Claim Header Data
9999|Claim1|Line1|Detail Data Line 1
9999|Claim1|Line2|Detail Data Line 2
9999|Claim2|Claim Header Data
9999|Claim2|Line1|Detail Data Line 1
9999|Claim2|Line2|Detail Data Line 2
...
And to ensure the data is written to the file in that order - you would need to use ORDER BY in the query. It could be worse...what if the end user wanted this?
9999|Header Row
9999|Claim1|Claim Header Data
9999|Line1|Detail Data Line 1
9999|Line2|Detail Data Line 2
9999|Claim2|Claim Header Data
9999|Line1|Detail Data Line 1
9999|Line2|Detail Data Line 2
...
9999|Trailer
The only way to associate Line1 to Claim1 is by position in the file. I would not recommend using this type of file - but I have seen where this type of request has been made, and it is doable using the technique I outlined. The ORDER BY on the separate columns will make sure the data is output in that specific order - the PS script then outputs *only* the record data...and the sort columns are ignored for that output.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 18, 2021 at 5:58 pm
SQL does not guarantee the order of the data unless there is an ORDER BY. This may work just fine - until it doesn't...
Ahhhh ... so true, thanks. I should have done a more thorough job rather than just "Here's a rough idea"
Personally I usually solve this type of problem like this:
SELECT[ExportData]
FROM
(
SELECT[Section] = 1,
[MyPKCol] = NULL,
[ExportData] = CONCAT('9999'
, '|', (SELECT TOP 1 CONVERT(char(11), FILE_DT, 113) FROM MyTable)
, '|', (SELECT COUNT(*) FROM MyTable)
)
UNION ALL
SELECT[Section] = 2,
MyPKCol,
[ExportData] = CONCAT('9999'
, '|', StringCol1
, '|', IntegerCol2
, '|', CONVERT(char(11), MyDateCol, 113)
, '|', ...
, Col85
)
FROM TableToExport
) AS T
ORDER BY [Section], MyPKCol
This should sort the output by SECTION (header first) and then my [MyPKCol] - a column(s) from your actual table so that the individual data rows are ordered. You may not need this, but it would guarantee that if you ran it twice the ordering would be identical.
Edit: Sorry Jeffrey, looking back at the previous page I now see that you already gave a very similar example.
February 18, 2021 at 6:53 pm
This should sort the output by SECTION (header first) and then my [MyPKCol] - a column(s) from your actual table so that the individual data rows are ordered. You may not need this, but it would guarantee that if you ran it twice the ordering would be identical.
Edit: Sorry Jeffrey, looking back at the previous page I now see that you already gave a very similar example.
I would say the order by section (in your method) is required to ensure that the header row appears first. If you have a trailer record - it would also be required...and as I showed - you can have other requirements for sorting that would be required.
As for my previous examples - they were just generic answers for different approaches. Using SSIS for this can be a bit tricky - because it requires creating multiple connection managers and setting those up appropriately...separate queries for each section and could require nested for-each loops.
With that said - the SSIS approach can simplify the SQL side (simpler queries) but at a cost of a more complexity in the package. If you are heavily vested in SSIS that approach might be better - but if you only use SSIS for basic import/export using the wizard then a SQL approach (with PS or BCP) might be better. It depends...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2021 at 5:29 am
With that said - the SSIS approach can simplify the SQL side (simpler queries) but at a cost of a more complexity in the package. If you are heavily vested in SSIS that approach might be better - but if you only use SSIS for basic import/export using the wizard then a SQL approach (with PS or BCP) might be better. It depends...
That sums it up very well. I'm definitely "heavily invested in SQL" and thus that is what I reach for first 🙂
February 22, 2021 at 7:45 pm
I have a working solution after trialing several suggestions from here.
First I tried the PowerShell ISE route by saving my query to a file with the concat method in it:
Invoke-Sqlcmd -ServerInstance localhost -Database SupplementalData -InputFile "D:\TestConcatScript.sql" | Out-File D:\test.txt
When I did that it created a file, but it had three extra lines at the top of the text file including a "Column1" header that was added, but was not a part of my script. It also had three '...' characters at the end of each line. It's as if it truncated the data, so this method did not quite work.
Second, I tried using "Query to Text" via SSMS method but I forgot about how that one adds the "execute time" at the bottom of the file, plus some blank lines at the top.
Third, I tried the SSMS "Export Data" option and pasted my query, but it errored out saying it needed a column name: "The data column at the position 1 does not have a name"
The Fourth try worked. I used the SSMS "Export Data" option again. Unchecked the option for "Column Names in First Data Row" added "as Col1" to each of the select statements so it looks like this below. Then set column delimiter to TAB (hoping I wouldn't need to remove them later) and then hit Run, and it created the file exactly as I need. Opened all 1.1 Million records in NotePad++ and a quick inspection looks like it's good to go. I could now save this Export Data SSIS package to use each month.
Thank you for all your tips. This will work. 🙂 I would like to keep trying the PowerShell method at some point because if I had a PowerShell script, I could probably pretty easily upload the file immediately via WinSCP command line after the file gets created (correctly).
Oh, I forgot to add, before I output this table to the text file I recreate the table ordering it by RowID, and so far it's come out with rowID in the correct order each time. I'll still have to ask the vendor what would happen if RowID was not in numerical order and if it would cause any harm.
Select CONCAT('9999', '|', (select TOP 1 replace(convert(varchar,[FILE_DT],106),' ','-') from MonthlyFile), '|', (select count(*) from MonthlyFile) ) as Col1
UNION ALL
SELECT CONCAT(
[SourceID]
, '|', [ROW_ID]
, '|', [CONTRACT_NUM]
, '|', [MBR_FIRST_NAME]
, '|', [MBR_LAST_NAME]
...
, '|', [81 More Columns]
)
FROM MonthlyData as Col1-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
February 22, 2021 at 8:04 pm
The reason your Powershell script didn't work as expected - is because you did not include the necessary items in the command:
$recordData = Invoke-SqlCommand -ServerInstance {your server here} -Query {Your Query/Procedure Here};
$recordData | Select -ExpandProperty RecordData | Out-File {your file name here};In this example - the query returns multiple columns and we need to only select one column. We take the data in $recordData - pipe it to a 'Select' where we only select the column RecordData and include the property -ExpandProperty. Even if the query only returns a single column - use 'Select' with -ExpandProperty to remove column headers.
I would need to see your script - but I am guessing your script includes additional output that is getting directed to out-file, which is why you are getting those additional rows. I tested the above and did not have any issues by embedding the SQL in the PS script - which is what I would recommend instead of having 2 scripts, one for PS and a separate one for SQL.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 23, 2021 at 6:22 am
First I tried the PowerShell ISE route by saving my query to a file ...
I'm not a fan of PowerShell. I use it very infrequently, and as a consequence I find the plethora of options means I spend far too long Googling for help rather than being productive. I also feel that that lack of knowledge leaves me exposed to "what I don't know" spoiling my day at some future point
Someone who uses it more regularly won't have those issues of course 🙂
What about the CMD SQL tools? (Old-School but ...)
SQLCMD -d MyDataBase -i "C:\Path\MyScript.SQL" -o "C:\Path\MyResults.TXT" -b -l 300 -S . -E >>"C:\Path\MyErrors.LOG"
That has plenty of options too, but they are all listed on a single DOC page
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
February 23, 2021 at 9:37 pm
Jeffery,
I had to slightly modify your PowerShell suggestion. The first line is as shown below and that 'works' (as in no error reported)
$recordData = Invoke-SqlCmd -ServerInstance localhost -Database MyDatabase -InputFile "D:\MyFile.sql";
The second line is giving me trouble:
$recordData | Select -ExpandProperty recordData | Out-File D:\Test2.txt;
PowerShell returns this error, and it keeps throwing this error until I break the process with CTRL + C:
Select : Property "recordData" cannot be found.
At line:1 char:15
+ $recordData | Select -ExpandProperty recordData | Out-File D:\Test2.t ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (System.Data.DataRow:PSObject) [Select-Object], PSArgumentException
+ FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
February 23, 2021 at 9:45 pm
What is the code in D:\MyFile.sql?
The 'Select' needs to select one of the available columns in $recordData - which will be defined by the query in D:\MyFile.sql. If you query was:
Select MyData = concat(...)
From someTable;
Then you would use:
$recordData | Select -ExpandProperty MyData | Out-File D:\Test.txt
This allows you to return multiple columns and only output the relevant column to the file. In my example(s) - I showed some example queries where you could get more complex sorting requirements and build your 'RecordData' column as a concatenated string. The PS script would then output just the 'RecordData' column - ignoring the additional columns returned from your query, but using those columns to sort the data as needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 23, 2021 at 9:46 pm
First I tried the PowerShell ISE route by saving my query to a file ...
I'm not a fan of PowerShell. I use it very infrequently, and as a consequence I find the plethora of options means I spend far too long Googling for help rather than being productive. I also feel that that lack of knowledge leaves me exposed to "what I don't know" spoiling my day at some future point
Someone who uses it more regularly won't have those issues of course 🙂
What about the CMD SQL tools? (Old-School but ...)
SQLCMD -d MyDataBase -i "C:\Path\MyScript.SQL" -o "C:\Path\MyResults.TXT" -b -l 300 -S . -E >>"C:\Path\MyErrors.LOG"
That has plenty of options too, but they are all listed on a single DOC page
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15%5B/quote%5D
Except - you have to execute SQLCMD from *something*. That would be either a DOS command window or Powershell - so if you are going to be using Powershell, why not just do everything in Powershell?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 23, 2021 at 11:27 pm
Except - you have to execute SQLCMD from *something*. That would be either a DOS command window or Powershell - so if you are going to be using Powershell, why not just do everything in Powershell?
... or a properly written call to xp_CmdShell. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2021 at 9:53 am
Except - you have to execute SQLCMD from *something*.
Haven't you got to run a PowerShell Script from "something"? (Ignorant question as I have only ever used PowerShell from CMD)
if you are going to be using Powershell, why not just do everything in Powershell?
Because for me, with lack of PowerShell experience, I find it is like "Hacking TECO" (for anyone old enough to remember that DEC editor). I spend ages fiddling around trying to find the correct options, and the script I wind up with is incredibly fragile (again almost certainly my lack of experience). We've had PowerScript scripts written for us by "experts in their field" for specific tasks, and without exception they have failed in the field through lack of interception of duff inputs / outputs or middle-data
But as I said I put all that down to lack of familiarity. For me, only needing to do that sort of process-control once in a while its just the wrong tool - way too complex for me to reliably build something that will be, and remain, bullet-proof in Production.
Typically a SQLCMD that I write would either be Windows Scheduler Task (assuming I couldn't do the same job from within SQL / SQL Agent), or part of a BATCH file (to get error handling / logging / etc) and that would probably be in a Windows Scheduler
Yeah, "Old School"
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply