BCP Export and Text Qualified Columns

 Bcp is a pretty efficient way of doing a simple extract of a table or query to a delimited flat file, something which still seems a pretty common task even in today's world of fancy ELTL tools and processes, though it does have it's quirks and limitations.

One of these limitations is that there's no easy way to text qualify string values in columns for cases where the file delimiter might be a valid character in the column value. If there's flexibility around the allowable column delimiter then this isn't too much of a problem, but sometimes the value for the delimiter is set by an 3rd party system or process.

About the Code

This PowerShell script will take an array of tables, determine the column metadata, text qualify and string columns with double quotation marks, and then extract the data to a specified flat file. 

If you want a different text qualifier then change the text "CHAR(34)" in the column query to the qualifier you need. This also uses a pipe delimiter, but this can be changed in the $params variable by altering the value "-t|" to the value you need. These could both be parameters to the script as well.

Parameters

For the parameters:

• $Tables is a string array of the tables to extract. It needs to be an array but could just be a single value if only 1 table needs to be extracted
• $Server is the SQL Server host and instance name if a named instance
• $Database is the database where the table lives
• $OutPath is the folder to extract the files to. The files will have the same name as the table, and a .txt extension. The extension could also be paramaterised or just manually changed on line 42
• $Schema is the schema of the table, default is dbo

 The Script

param(

[Parameter(mandatory=$true)]

[string[]]$Tables,

[Parameter(mandatory=$true)]

$Server,

[Parameter(mandatory=$true)]

$Database,

[Parameter(mandatory=$true)]

$OutPath,

[Parameter(mandatory=$false)]

$Schema = "dbo"

)

$bcp = "bcp.exe"

if(!(Test-Path -Path $outPath))

{

throw "Output path doesn't exist"

exit -1

}

 

$tm_start = (Get-Date) # Just used to get extract timings

foreach($t in $Tables)

{

[string]$columnQuery = "select stuff((

select ',' + case when system_type_name like '%char%' then 

                     'QUOTENAME(' + name + ', CHAR(34))'

when system_type_name = 'geometry' then  

                    '[' + name + '].STAsText() as ' + '[' + name + '_wkt]'

else QUOTENAME(name) end as [text()]

from sys.dm_exec_describe_first_result_set ('select * from $Schema.$t', null, 0)

for xml path (''), type).value('.[1]', 'nvarchar(max)')

,1, 1, '')

as collist"

$metadata = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $ColumnQuery

    

$query = "select {0} from {1}.{2}" -f $metadata.collist, $Schema, $t

    #write-host $query

$outFile = [System.IO.Path]::Combine($OutPath, $("$t.txt"))

$params = "`"$query`"", "queryout", "`"$outFile`"", "-S", $Server, "-d", $Database, "-T", "-c","-t|", "-C", "65001"

#Write-Host $params

$b = & $bcp $params

#$b

}

 

# Extract time

$tm_end = Get-Date

[timespan]$span = $tm_end - $tm_start

$span.Seconds

Powershell Scripts and Passwords

 One of the struggles I have with automating things with PowerShell is securely managing the passwords. Using Windows credentials from the service/automation account is probably the best option, but not always possible so the question becomes how to securely store the password somewhere that the script can use it but it's generally secure from anyone discovering the script.

The  most practical way I've discovered so far is to encrypt the password using the account that will run the automation on the server where the automation will run, and then save this encrypted password in a config file that the script will read. For jobs run by the SQL Agent this would mean encrypting the password using the SQL Agent service account on the SQL Server.

Encrypting the Password

The password gets encrypted using the following basic format

Read-Host | ConvertTo-SeccureString -AsPlainText -Force | ConvertFrom-SecureString

instead of using Read-Host to read the string from the console you can also hard code the password into the command, e.g.

'password' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString

or read the password into a variable and then convert, e.g.

$pwd = Get-Content c:\temp\test.txt
$pwd | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString

The resulting long string of gibberish can be added into a file to be read in by the actual script at runtime.

Decrypting the Password

In order to use the password it has to be decrypted by the same user on the same machine as where the encryption was done. Using a different user or different machine will result in the password not being decrypted correctly.

Decrypting is a bit more complex, as follows

$password = (Get-Content $SecretFile) | ConvertTo-SecureString
$decrypted = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))

The first line reads in the encrypted password from the path in the variable $SecretFile, e.g. c:\temp\test_encrypted.txt and converts it back into a PowerShell SecureString object.

The next line uses the .Net Marshal class functions to convert the secure string object back into plain text.

Final Thoughts

This isn't perfect, as anyone that can access the server and user can decrypt the password so maintaining good security around admin permissions on servers and well protected service accounts is a must. But it's far better than having plain text passwords lying around in scripts.

A Spatial Christmas Treat

A quick fun one to finish the year. This has been posted in a few places over time, and I can't find the original person who did it (possibly Michael Coles?)

Simply run the script in SSMS, then switch to the Spatial results tab to see the image.

USE tempdb
GO

-- Prepare the scene
CREATE TABLE #ChristmasScene (
	item VARCHAR(32),
	shape GEOMETRY
	);

--Put up the tree and star
INSERT INTO #ChristmasScene
VALUES (
	'Tree',
	'POLYGON((4 0, 0 0, 3 2, 1 2, 3 4, 1 4, 3 6, 2 6, 4 8, 6 6, 5 6, 7 4, 5 4, 7 2, 5 2, 8 0, 4 0))'
	),
	(
	'Base',
	'POLYGON((2.5 0, 3 -1, 5 -1, 5.5 0, 2.5 0))'
	),
	(
	'Star',
	'POLYGON((4 7.5, 3.5 7.25, 3.6 7.9, 3.1 8.2, 3.8 8.2, 4 8.9, 4.2 8.2, 4.9 8.2, 4.4 7.9, 4.5 7.25, 4 7.5))'
	)

--Decorate the tree
DECLARE @i INT = 0,
	@x INT,
	@y INT;

WHILE (@i < 20)
BEGIN
	INSERT INTO #ChristmasScene
	VALUES (
		'Bauble' + CAST(@i AS VARCHAR(8)),
		GEOMETRY::Point(RAND() * 5 + 1.5, RAND() * 6, 0).STBuffer(0.3)
		)

	SET @i = @i + 1;
END

--Christmas Greeting
INSERT INTO #ChristmasScene
VALUES (
	'M',
	'POLYGON((0 10, 0 11, 0.25 11, 0.5 10.5, 0.75 11, 1 11, 1 10, 0.75 10, 0.75 10.7, 0.5 10.2, 0.25 10.7, 0.25 10, 0 10))'
	),
	(
	'E',
	'POLYGON((1 10, 1 11, 2 11, 2 10.8, 1.25 10.8, 1.25 10.6, 1.75 10.6, 1.75 10.4, 1.25 10.4, 1.25 10.2, 2 10.2, 2 10, 1 10))'
	),
	(
	'R',
	'POLYGON((2 10, 2 11, 3 11, 3 10.5, 2.4 10.5, 3 10, 2.7 10, 2.2 10.4, 2.2 10, 2 10),
                 (2.2 10.8, 2.8 10.8, 2.8 10.7, 2.2 10.7, 2.2 10.8))'
	),
	(
	'R',
	'POLYGON((3 10, 3 11, 4 11, 4 10.5, 3.4 10.5, 4 10, 3.7 10, 3.2 10.4, 3.2 10, 3 10),
                 (3.2 10.8, 3.8 10.8, 3.8 10.7, 3.2 10.7, 3.2 10.8))'
	),
	(
	'Y',
	'POLYGON((4 11, 4.2 11, 4.5 10.6, 4.8 11, 5 11, 4.6 10.5, 4.6 10, 4.4 10, 4.4 10.5, 4 11))'
	),
	(
	'C',
	'POLYGON((0 9, 0 10, 1 10, 1 9.8, 0.2 9.8, 0.2 9.2, 1 9.2, 1 9, 0 9))'
	),
	(
	'H',
	'POLYGON((1 9, 1 10, 1.2 10, 1.2 9.6, 1.8 9.6, 1.8 10, 2 10, 2 9, 1.8 9, 1.8 9.4, 1.2 9.4, 1.2 9, 1 9))'
	),
	(
	'R',
	'POLYGON((2 9, 2 10, 3 10, 3 9.5, 2.4 9.5, 3 9, 2.7 9, 2.2 9.4, 2.2 9, 2 9),(2.2 9.8, 2.8 9.8, 2.8 9.7, 2.2 9.7, 2.2 9.8))'
	),
	(
	'I',
	'POLYGON((3.2 9, 3.2 9.2, 3.4 9.2, 3.4 9.8, 3.2 9.8, 3.2 10, 3.8 10, 3.8 9.8, 3.6 9.8, 3.6 9.2, 3.8 9.2, 3.8 9, 3.2 9))'
	),
	(
	'S',
	'POLYGON((4 9, 4 9.2, 4.8 9.2, 4.8 9.4, 4 9.4, 4 10, 5 10, 5 9.8, 4.2 9.8, 4.2 9.6, 5 9.6, 5 9, 4 9))'
	),
	(
	'T',
	'POLYGON((5 9.8, 5 10, 6 10, 6 9.8, 5.6 9.8, 5.6 9, 5.4 9, 5.4 9.8, 5 9.8))'
	),
	(
	'M',
	'POLYGON((6 9, 6 10, 6.25 10, 6.5 9.5, 6.75 10, 7 10, 7 9, 6.75 9, 6.75 9.7, 6.5 9.2, 6.25 9.7, 6.25 9, 6 9))'
	),
	(
	'A',
	'POLYGON((7 9, 7 10, 8 10, 8 9, 7.75 9, 7.75 9.3, 7.25 9.3, 7.25 9, 7 9),(7.25 9.5, 7.25 9.8, 7.75 9.8, 7.75 9.5, 7.25 9.5))'
	),
	(
	'S',
	'POLYGON((8 9, 8 9.2, 8.8 9.2, 8.8 9.4, 8 9.4, 8 10, 9 10, 9 9.8, 8.2 9.8, 8.2 9.6, 9 9.6, 9 9, 8 9))'
	);

--Admire the scene
SELECT *
FROM #ChristmasScene -- Tidy up the pine needles and put away the decorations

DROP TABLE #ChristmasScene

 

This should run really quickly and all going well you'll see a cute Christmas image.

For more of this stuff, the fantastically talented Michael J Swart has some more examples on his blog, https://michaeljswart.com/?s=spatial

SQL Agent Permissions on RDS

 RDS for SQL Server now supports running SQL Agent, which is mighty handy. However it does have a couple of quirks which require you to run things in a slightly different manner. 

The thing that caught me out for a bit was user permissions. These are documented but kind of hidden in the documentation (or I just wasn't reading it properly) so I'm going to try and clarify how they work here.

Here's the short version:

• By default, only the admin user can see the SQL Agent
• They can only see jobs which they've created
• Other users can be granted permissions to use the agent and create jobs
• Extra permissions can be granted to view jobs for all users
• These extra permissions need to be added and removed as required, otherwise the user will get an error on login

I'll repeat that last point

• These extra permissions need to be added and removed as required, otherwise the user will get an error on login

 

 Some more detail

Here's what the Object Explorer looks like when I log in as my default user. This user is a member of the server processadmin role, but still can't see the SQL Agent

So first step is to add my user to the SQLAgentUserRole role in msdb

 

use msdb
go
alter role SQLAgentUserRole add member [my user]

Now I can create jobs, but still can't see jobs created by other users

Next step is I need to grant my user alter rights on theSQLAgentOperatorRole role role in msdb. Note that I'm not adding my user to the role, but I'm granting my user permission to add myself to the role when needed. Also note that you can't grant yourself these permissions so you need to do this with the admin login.

 

use msdb
go
grant alter on role::SQLAgentOperatorRole to [my user]

Now when I need to see all jobs I can add myself to this group first

 

use msdb
go


alter role SQLAgentOperatorRole add member [my user]

I still can't edit the jobs, only the job owner or a sysadmin can do that, which means that usually you'll need to go in as the admin account. Even with the admin account you still need to be added to the SQLAgentOperartorRole role to see all jobs. Here's the Agent view now once my user is a member of that group

When finished you need to remove yourself from the group

 

use msdb

go

alter role SQLAgentOperatorRole drop member [my user]

If you don't do this then you get this message when logging in "Execute permission was denied on the object 'xp_regread'. When you get this it also disables you from seeing the SQL Agent. Removing from the group and refreshing will bring it back, but it's kind of annoying.

What's also annoying is that this will trigger if you refresh the server view, using F5 or using the refresh icon.

Something else to watch out for is using groups for these permissions. I was stuck for a while trying to work out why I kept getting the xp_regread error and found that a group I was a member of had the permissions assigned, so even when I removed my user from the Operator group, the permissions were still applied via the AD group