SQLGeordie's Blog

Helping the SQL Server community……where i can!

Output SQL Server data from multiple tables to Tab Delimited text files using Powershell — December 20, 2013

Output SQL Server data from multiple tables to Tab Delimited text files using Powershell

tabI had a request this morning for something I though was actually very simple:

Client: “Can you extract all data for these particular tables including column headers to a tab delimited .txt file?”
Chris: “Sure, no problem, I’ll just run bcp querying sys.tables using a COALESCE loop to output the statements”
Client: “Top stuff, let me know when it’s done”

So, away I went generating my script which took a matter of minutes and run it…….where’s the column headers? Bugger, forgot that bcp doesn’t output column headers without doing some funky stuff by creating a header record in a separate file and merging that with the file of data.

With this in mind I knew creating a SSIS package (or using export data to generate – very manual unless I delved into the realms of BIML) could do this but I thought I’d have a look at powershell invoking sqlcmd.

Again, this all seemed to be going very well until I came to outputting the data to a tab delimited .txt file. As far as I’m aware Powershell does not have an Export-Txt so I had to look into how I can use the Export-Csv to actualy output to .txt tab delimited as opposed to comma separated and found the parameter -delimiter “`t” – Excellent!!! Added this in and run the script…………and the first row consisted of “#TYPE System.Data.DataRow” – wft!?!?!?!?!

Quick search on my search engine of choice showed that there is a parameter that you can pass in to remove this from the export -NoTypeInformation.

Run it again with -NoTypeInformation and everything worked as expected apart from all column headers and data had quotes (“) around them which was not part of the requirement. Unfortunately (as far as I know) there is no switch, parameter or the likes that does this so I had to change the Export-Csv to ConvertTo-Csv and run a Replace on ‘”‘ with ” which managed to do the trick.

I’ve included the script below which can be tailored to your needs:

$server = 'ServerInstanceHere'
$database = 'DBNameHere'
$path = 'c:\work\ToDelete\'
$query = "SELECT name FROM sys.tables WHERE name in (
 'TableNameHere_1',
 'TableNameHere_2'
 --etc etc
 )"
$queryToOut = "SELECT * FROM $TableName"

#Get list of table names to output data
$Tables = invoke-sqlcmd -query $query -database $database -serverinstance $server
foreach ($Table in $Tables)
{
 $TableName = $Table["name"]
 write-host -ForegroundColor Green "Creating File $TableName.txt"
 invoke-sqlcmd -query $queryToOut -database $database -serverinstance $server | `
 #Convert as opposed to Export to replace quotes if required
 ConvertTo-Csv -NoTypeInformation -delimiter "`t" | `
 ForEach-Object {$_ -Replace('"','')} | `
 Out-file $path$TableName.txt
 #Export-Csv -NoTypeInformation -delimiter "`t" -path $path$TableName.txt
}

Apologies for the formatting but the powershell script tag doesn’t seem to format it the way I’m wanting it to so here is a screen shot of the code:

POSH_TAB

Advertisements
Making sure your Triggers fire when they should — March 4, 2013

Making sure your Triggers fire when they should

As some of you may be aware, triggers are not my favourite thing in the world but like most things, it does have its place.

Whilst onsite with one of my clients, one of the processes fires a trigger on insert which ultimately runs a SSRS subscription to email a report. All sounding fairly feasible so far. However, this process is also used as part of a batch process overnight which would run a separate insert statement (actually another stored procedure in another job step) instead of the “onDemand” insert. Ok, still doesn’t sound like too much of an issue.

Now, they started experiencing occasional failures of this job during the day with the error relating to the fact that the SSRS subscription job was being called when it already was running. Interesting, this in theory shouldn’t ever happen because the process either ran the jobs based on the batch process or the one off onDemand.

Stepping through the process, it led me to an AFTER INSERT trigger. Upon opening it I spotted the issue straight away. Something that as I’ve found over the years as a consultant, a lot of DBA’s and developers have failed to understand that (from MSDN ):

These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.

So, the issue was that step 3 ran a procedure which ultimately ran an insert statement for the onDemand insert, step 4 ran a procedure to insert for the overnight batch process which as it happens doesn’t have any records to insert but will in fact fire the trigger to run the SSRS subscription again! There is a number of ways to fix this but I’ve tended to stick with a basic check of the “inserted” table for results and RETURN out if no records are there to process.

I’ve supplied a bit of test code below for people to try this out.

Lets create a test table and an audit table:

USE tempdb
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable]
(
	TestTableID INT IDENTITY(1,1),
	TestTableDescr VARCHAR(20)
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]
GO
CREATE TABLE [dbo].[AuditTrigger]
(
	AuditTriggerID INT IDENTITY(1,1),
	AuditTriggerDescr VARCHAR(20),
	DateCreated DATETIME
)
GO

INSERT INTO dbo.TestTable (TestTableDescr)
VALUES ('Test1'), ('Test2'), ('Test3');

SELECT * FROM dbo.TestTable;

Now lets create the trigger with no checking:

USE [TempDB]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]
GO

CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]
   AFTER INSERT
AS
BEGIN

	--Log the fact the trigger fired
	INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
	SELECT 'Trigger Fired', GETDATE()

END
GO

Test Inserting a record that exists:

--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test1';

SELECT  *
FROM    [dbo].[AuditTrigger];

Test Inserting a record that doesn’t exist:

--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';

SELECT  *
FROM    [dbo].[AuditTrigger];

You’ll now see that there are 2 entries in the AuditTrigger table due to the fact that the trigger fired even though no records were actually valid to insert.

So, lets amend the trigger to check for valid inserts:

USE [TempDB]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]
GO

CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]
   AFTER INSERT
AS
BEGIN

	--Check to see if any records were inserted
	IF NOT EXISTS (SELECT 1 FROM INSERTED)
		RETURN 

	--Log the fact the trigger fired
	INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
	SELECT 'Trigger Fired', GETDATE()

END
GO

and test the inserts again:

Test Inserting a record that exists:

--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test2';

SELECT  *
FROM    [dbo].[AuditTrigger];

Test Inserting a record that doesn’t exist

--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';

SELECT  *
FROM    [dbo].[AuditTrigger];

No record will have been inserted with the final insert statement!

Lets clean up our tempdb:

USE [TempDB]
GO

--Clean up
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]
GO

Hopefully this will help point out the misconception that triggers only fire when records are actually inserted 🙂

As per usual, I’d like to hear peoples thoughts/experiences on this topic.