Schott SQL

Tales of a Lazy DBA–MySQL, SSIS, and “0” Dates

noreply@blogger.com (Peter Schott) — Fri, 16 Dec 2011 01:59:00 +0000

We’ve recently been tasked with converting a lot of MySQL Data into our system from a multi-tenant MySQL Database. We don’t have a fixed schedule to actually execute the imports because they’ll be on a “per customer” basis. Well, that sounded like a great task for SSIS. I set up the ODBC driver, connected, set the initial “sql_mode” options for our connections to (‘mssql, allow_invalid_dates’) and started to work.
First problem we ran into with an ADO.NET connection to MySQL and writing a SELECT * from schema.table was when we hit a MySQL “Date” column containing a value of ‘0000-00-00’. SSIS threw an error, not sure what to do. Thanks to some others who have solved this problem, I realized that within the MySQL Select statement, we could do something like:

CASE date_created WHEN '0000-00-00' THEN NULL else date_created END as date_created

to pass those dates over as NULL. That solves the implicit conversion to datetime (SQL 2005) and avoids the invalid dates. I ran something similar for a “Time” column to:

CAST(Time_Created as char(8) ) as Time_Created

So that solved one particular table export with about 20 or so CAST and CASE statements. Needless to say, I wasn’t looking forward to doing this for another 500 tables with a total of almost 6000 columns.
I finally set up a really basic query to generate most of the SELECT statements we would need to pull our MySQL data across without too much pain. Admittedly, a small step and I’d still need to copy/paste when I set up each new ADO.NET source, but it worked reasonably well. I’m adding the code snippet for MySQL here in case anyone else has a similar problem.

select
CONCAT(CASE WHEN ordinal_position = 1 THEN 'SELECT ' ELSE ', ' END,
 CASE Data_Type WHEN 'date'
 THEN CONCAT('CASE ',column_name,' WHEN ''0000-00-00'' THEN NULL else '
    ,column_name,' END as ',column_name)
 WHEN 'time' THEN CONCAT('CAST(',column_name,' AS CHAR(8) ) as ',column_name)
 ELSE column_name END, CASE WHEN Ordinal_Position <> MaxOrd THEN ''
 ELSE CONCAT('
    FROM MySchema.', c.table_name) END
    ) as Select_Column_Name
from information_schema.columns as c
JOIN (select table_name, MAX(ordinal_position) as MaxOrd
        from information_schema.columns
        WHERE Table_Schema = 'MySchema'
        GROUP BY Table_Name) as t1
    ON c.Table_Name = t1.Table_Name
where table_schema = 'MySchema'
order by c.table_name, ordinal_position LIMIT 0, 50000;

I’d love to hear other ideas if anyone has encountered this before and come up with a more elegant solution for translating “0” Date or Time data from MySQL into SQL Server.

Installing SQL Server, Sharepoint, PowerPivot on a single server

noreply@blogger.com (Peter Schott) — Thu, 15 Sep 2011 21:13:00 +0000

I've been trying to set up a single virtual machine for a proof of concept using SQL Server Denali CTP3, Sharepoint 2010, and PowerPivot. I want to get Project "Crescent" running through this as well to prove out some report concepts. I've had a lot of trouble finding the right steps to get everything installed and working correctly on a single box, especially since my Sharepoint knowledge right now is practically non-existent when it comes to BI. Every time I tried to get the Sharepoint / PowerPivot integration working, I hit a roadblock or managed to corrupt my Sharepoint install.

I had the opportunity to speak with Brian Knight ( blog | twitter ) and he pointed me to PowerPivotPro.com which, in turn, led me to PowerPivotGeek.com. They had a page of instructions for single-server installs with a pointer to an MS whitepaper and a private cached file of the whitepaper just in case. This gave me the information needed to get PowerPivot working on my VM.

A couple of notes from my personal experience:

Install Sharepoint SP1 right after installing Sharepoint 2010. SP1 is required when you're using Denali
Do NOT configure Sharepoint until the directions tell you to do so.
Use a Named Instance in order to get PowerPivot working.
If you plan to use Crescent, make sure that you set up SSAS with the "Tabular Data" option (or whatever the final name ends up being).

Once again, thanks to Brian for pointing me towards the right solution. Thanks to PowerPivotGeek for hosting these files. I imagine the instructions would work for Virtual Server, Virtual PC, VMWare, VirtualBox, or similar Virtual Machine apps. I hope this is helpful to anyone else trying to set up their own VM for testing out the Microsoft BI stack.

Service Broker–WAITFOR and Activated Queues

noreply@blogger.com (Peter Schott) — Wed, 29 Jun 2011 18:45:00 +0000

We’ve implemented Service Broker for handling a small portion of the transactions we want to catch and apply into our Operational Data Store. Mostly we wanted to make sure we accounted for actual DELETE operations and handled those records correctly. We can use Change Data Capture at this time on our source systems and Service Broker seemed to fit the bill. We still handle the remaining inserts and updates through SSIS.

While running Service Broker, we noticed a pretty constant CPU hum on the receiving server. That seemed odd, but we had a lot of trouble tracking it down. Regular Profiler traces didn’t show any running TSQL, “normal” Service Broker traces weren’t showing much, either. We just saw SQL Server running at a pretty constant 20% even with nothing seemingly happening.

I want to give a public thank you to Mark Hill (twitter) for doing a little extra digging and catching the root cause of this. In our tinkering with Service Broker, Queues, Activation, Stored Procs, and such we missed some very important information along the way.

We had written a stored procedure that would be used on the Receiving side of our Service Broker queue to run as the “Activated” proc. Inside the stored procedure, we included a WHILE loop to process anything that was in the queue. If nothing, exit. That seemed pretty simple – if something comes into the queue, process it and stop when nothing is left. End of story, right?

Sadly, this is where we missed a small, but important, fact about Service Broker. If you have an activated stored procedure that doesn’t use the WAITFOR command, Service Broker, we execute that stored proc as many times as possible looking for something to process. We looked at it, turned off the Activation on the queue and saw the CPU drop to nothing. We re-activated the queue and saw the CPU shoot up again. We tweaked the stored procedure after that and added a WAITFOR command with a timeout of 60000. After doing this, we saw the stored procedure run to process everything in the queue, then go idle, which was exactly the intended behavior in the first place.

I’m not going to pretend to be an expert on Service Broker. We had tried to code with the intention of being able to use this stored procedure as an Activated stored procedure or as one called from an external process to work through the queue. While that may be possible, it was unnecessary in our actual usage. Adding a WAITFOR command around our queue processing eliminated our extra, non-essential CPU usage and stopped trying to execute a stored procedure for no reason.

Resources

Pro SQL Server 2008 Service Broker ( Amazon | Apress )

WAITFOR (Also see this performance article – it’s the little details that get you)

Quickly script permissions in SQL 2005+

noreply@blogger.com (Peter Schott) — Thu, 17 Feb 2011 22:15:00 +0000

I wanted an easy way to generate a script to recreate all DB object and schema permissions for a database. Searching online, I found several different examples, but none quite did everything I needed. I put this together from some examples and added it to my toolkit. This script will generate the appropriate T-SQL to grant permissions to DB Objects and Schemas.

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

I know that a lot of you may have something that does this already, but figured it can’t hurt to have another snippet available to generate permission statements. If you make any enhancements, let me know. I’d love to keep this up to date so it’s helpful to others.

SQL 2008 R2 – SSIS Data Export Oddities

noreply@blogger.com (Peter Schott) — Mon, 07 Feb 2011 22:27:00 +0000

I don’t know if anyone else has had these sorts of issues, but we work quite a bit with conversions of various source data into our SQL Server database. I recently was handed a backup from SQL 2008 R2. I figured that wasn’t a huge problem. Restore onto my local test box, use SSIS to push over to a SQL 2005 instance so it can work its way to Production. No big deal, right?

The first time I tried this using the SQL Native Client 10.0 on both sides, I ran into an errors with the mappings. I can’t quite figure that one out because from what I can see, there’s nothing at all in the source database using a feature that would not be available in SQL 2005.

I tried exporting to an MS Access MDB file. No luck from SQL 2008 R2 with the default settings because of an invalid size error on a varchar to longtext conversion.

I was able to successfully export using SNAC from SQL 2008 R2 to a SQL 2008 instance. So from there I thought I could upload directly to the SQL 2005 instance. No such luck using SNAC – again. I was able to export from there to an Access MDB file and pull that into SQL 2005. I don’t quite get why that worked, but figure I’ve got some odd mapping in the XML files defining the defaults that I’m missing.

I was recently asked to repeat this task and figured there had to be a better way. This time I restored the DB to my R2 instance – no issues. I then used the SNAC client to access my R2 instance, but set up an OLEDB connection to my SQL Server 2005 target. For some reason, the mappings are just different enough that this worked with no issues. I was able to transfer directly. I now wish I’d tried that the first time, but I’d already blown a couple of hours on it.

If anyone else has encountered that and knows why SNAC from 2008 R2 doesn’t seem to work directly to SNAC on 2005, I’d love to know the reasons as well. If not and you encounter something similar, maybe trying the OLEDB connections will work for you.

SQL 2008 Merge and PK/FK Constraints

noreply@blogger.com (Peter Schott) — Mon, 17 Jan 2011 18:24:00 +0000

We ran into this issue a while back. SQL 2008 has a “feature” that will not allow inserts into a table on either side of a Foreign Key relationship. If you attempt to insert into a table involved in the FK relationship, you get an error something like:

The target table 'TableName' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'ConstraintName'.

This is documented in Connect 435031. There is one workaround documented in the ticket – drop the FK constraint, run the MERGE query, then re-create the constraint. Happily, this bug is still open as the requirement to drop a constraint just to run a MERGE statement kind of defeats the purpose of putting a constraint on the table in the first place. While dropping the constraints will work, there is another possible workaround. I was able to run this without any issues while leaving my FK Constraint in place.

Create a temp table that matches the definition of the table into which you want to perform your insert.
Instead of inserting into your base table like you normally would in a merge, write your code to INSERT #Tablename at the beginning of the MERGE process. You’ll still use your main table and staging table inside the MERGE INTO … USING query.
After the Merge, insert into the main table using the values in your Temp table.

Here’s a shell of an example. Line 11 below contains the main difference to the MERGE query from a “normal” MERGE.

   1: --1: Create Temp table

   2: CREATE TABLE #MyFactTable(

   3:     ID INT NULL

   4:     , CustomerName VARCHAR(100) NULL

   5:     , SourceID INT NULL

   6:     , OutputAction VARCHAR(100) NULL

   7: );

8:

   9: --2: INSERT into the temp table instead of your normal target table

  10: --   Merge query will be the same otherwise

  11: INSERT INTO #MyFactTable (ID, CustomerName, SourceID, OutputAction)

  12: SELECT so.ID, so.CustomerName, so.SourceID, so.output_action

  13: FROM (

  14:     MERGE INTO dbo.MyFactTable AS t

  15:     USING Staging.MyFactTable AS s

  16:     ON ( s.ID = t.ID

  17:         AND s.NewLoad = 0 )

18:

  19:     WHEN MATCHED AND ( s.SourceID <> t.SourceID )

  20:             AND s.NewLoad = 0

  21:     THEN UPDATE

  22:         SET RecordState = 0

  23:         , UpdatedDate = getdate()

24:

  25:     WHEN NOT MATCHED BY TARGET AND s.NewLoad = 0 THEN

  26:         INSERT (ID, CustomerName, SourceID)

  27:         VALUES (s.ID, s.CustomerName, s.SourceID)

  28:         OUTPUT $action AS OutputAction

  29:             , ID

  30:             , CustomerName

  31:             , SourceID

  32:         ) AS so (OutputAction, ID, CustomerName, SourceID)

  33:         WHERE OutputAction = 'UPDATE'  ;

34:

  35: --3: Perform the final insert into your target table

  36: INSERT INTO MyFactTable (ID, CustomerName, SourceID)

  37: SELECT DISTINCT ID, CustomerName, SourceID

  38: FROM #MyFactTable ;

39:

  40: --4: Clean up your temp objects.

  41: DROP TABLE #MyFactTable ;

I’d love to hear if anyone else has a workaround for this that doesn’t involve dropping and recreating the FK constraint. If this is an issue for you, please vote for the Connect Ticket.

Book Review: Windows Phone 7 Plain & Simple

noreply@blogger.com (Peter Schott) — Tue, 14 Dec 2010 04:00:00 +0000

Overall, Windows Phone 7 Plain & Simple is a good book for people new to Windows Phone 7 and especially for people new to Smart Phones. Michael does a great job illustrating how to use the phone and get up to speed quickly. He walks quickly through the general operation, setting up the phone, and entering text in the first couple chapters. The next several chapters discuss using the most popular features of WP7: calling, e-mail, text messages, calendar, browsing the web, and using the maps/navigation. Next we learn more about music, video, taking pictures and videos, and using the Marketplace. We end with a quick session on using WP7's Office Hub for documents, spreadsheets, and OneNote. This is followed by a discussion on using the Zune software to synchronize files between your phone and your PC. (Michael doesn't mention the Mac software to do this. It's capable, but more limited.)

Having followed the progress of WP7, I found that I knew a lot of the information in this book. There were still a couple interesting bits of information that were news to me, but not many. Michael didn’t write this for people like me who have followed WP7 for some time. He wrote for people who aren’t really familiar with smartphones and especially with the changes that WP7 brings. For those people, this will be a useful read and a quick reference while they get used to their device. I plan to show this to my family members who have WP7 devices and aren't quite sure what to do with them.

The positives:

The information is very well presented, easy to follow, and broken out into logical sections.
Michael's casual writing is well-suited for this book.
Readers will not feel intimidated by their new phone and are encouraged to try things out.
Michael presents some information that you may not easily find by reading articles online.

The negatives:

I don't see this as a book that will be a long-lasting reference book. Once the reader has used the phone for a month or so, the lessons from this book should be second-nature.
WP7 already has two planned updates at the time of this review. A minor update in January 2011 that will reportedly enable Copy/Paste functionality and a larger one in February 2011. The errata for this book will need to be updated to take these changes into account as some information may be outdated.

Conclusion:
If you can find Windows Phone 7 Plain & Simple for a reasonable price and are new to the platform, pick it up. It's a short, but informative, read. If you've followed WP7 for a while, this is not likely the book for you as you'll know most of the information here already. You may still want to pick up a copy to share with family or friends who are new to the phone. It could save you "support" calls.

Please visit the Windows Phone 7 Plain & Simple page at O’Reilly for more information about this book.

Disclaimer: I received a free copy of this book in electronic format in return for providing an honest review. I was not compensated in any other way.

Book Review: Cooking For Geeks

noreply@blogger.com (Peter Schott) — Tue, 30 Nov 2010 06:00:00 +0000

(I know this isn’t SQL-related, but I think this may be a great read for people with similar interests.)

This is not a cookbook. If you’re looking for a new collection of recipes, this is not the book for you. If you like shows like Good Eats or enjoy seeing how food is prepared and served, you’ll almost certainly love Cooking for Geeks. If you like experimenting in the kitchen and knowing why food turns out the way it does, pick up this book!

As a geek, I loved Jeff’s analogy: Recipes are code. Follow a recipe as written and you generally get good results. Forget the where clause and you could have unrecoverable errors. Introduce your own changes and you could get something great or you could get something horrible that requires a lot of cleanup. Recipes may have bugs or need corrections. Perhaps there’s more than one way to the same result. Oh, and don’t forget to comment your recipe. Otherwise you might not be able to recreate something fantastic.

Each chapter of Cooking for Geeks deals with different concepts, each with their own scientific background. Common utensils, ingredients, time/temperature, baking, additives (chemicals), and even some geeky fun with hardware or unusual cooking techniques – all are included in a way that not only gives some neat recipes, but the science behind the recipes.

To me, the most interesting parts were on baking and the chemical reactions that take place as heat is applied. It was great reading exactly why food turns out with all of its various nuances. That science got me thinking about ways to tweak the outcome of various recipes I follow and was just fun to read.

You can get your own copy of Cooking for Geeks direct from O’Reilly.

Disclaimer: I received an electronic review copy of this book, though I’d likely have wanted to read and review this anyway.

SSIS, OLEDB Data Sources, and Table Variables

noreply@blogger.com (Peter Schott) — Thu, 28 Oct 2010 16:00:00 +0000

I ran across an interesting problem when trying to tune some SSIS data source performance issues the other day. I had noticed that we were doing a pretty large select out of a table filtered on a handful of date-related columns. We then pulled in most of the data from the table. Well, SQL Server saw this query and apparently figured that since we needed most of the columns, a table scan would be the most efficient way to get that data. Since in real life, we only need a very small percentage of those rows, I decided to rewrite the select to pull in the PK values first, then pull in the data based on that subset.

My first attempt was to use a Temp Table. Sadly, using a temp table does not work in SSIS Data Sources, even if you put that inside a stored procedure. Disappointed, but not deterred, I decided to use a Table Variable. I quickly made the changes and tested them with some values I knew would returned quite a few results. Great – no worries. I was still using a stored procedure at the time and used some of our standard code to start it off so had no issues.

Ultimately, I didn’t want to write a bunch of stored procedures that I would need to maintain outside of SSIS just for this particular load. We had the code already inside of the OLE DB Sources, just not optimized as much as I’d like. I started pulling out the old queries, adding a table variable, populating it with the necessary PK values, the using that to filter out the main data set. Save, deploy, run, see major speed increase.

Then, I looked at the actual insert/update counts. Yes, it ran much faster, but in the process, it didn’t pick up any results. I was pretty confused because the step was successful, the code ran as expected through SSMS, there were no errors or warnings. I re-verified that running that same code got results in SSMS; it did. I started doing some searching to see what might be happening and found this post by Dustin Ryan.

In short, it seems that if you have row counts being returned (default behavior), you return a number of rows affected when you insert the table variable, which results in a “Done in Proc” type message, which in turn tells SSIS that the data flow is done and moves on to the next step. The workaround? Add SET NOCOUNT ON; to the beginning of your OLEDB Source query (or make sure this is in your stored proc – we tend to put that in our procs by default).

Once again, thanks to Dustin Ryan for his post on this topic. There’s a good possibility I’d still be struggling with the root cause of this without that assistance.

Review: Windows PowerShell 2.0 Best Practices - Introduction

noreply@blogger.com (Peter Schott) — Sat, 24 Jul 2010 05:00:00 +0000

A while back I received a review copy of Windows PowerShell 2.0 Best Practices by Ed Wilson (blog | twitter) and the Windows PowerShell Teams at Microsoft. Having finally found the time to sit down and really digest the information within, I thought I’d do a multi-part review of the book. Ed Wilson has compiled an excellent resource not only to show off what PowerShell 2.0 can do, but also to provide practical examples and several useful tips for best practices not only in using PowerShell, but in writing reusable and efficient code.

This book is broken down into 5 major sections:

Introduction
Planning
Designing
Testing and Deploying
Optimizing

I want to give a quick overview of what’s contained in each chapter, though I’m not going to include the code from the book. You may be able to get it from the main page for the book, but ideally I think this book is well worth buying. You get a PDF file of the book and all of the code included in the book as well. If you plan to do a lot of PowerShell scripting, this book is well worth the money. I will say that this book is not designed to teach PowerShell. Ed Wilson really does expect that readers have a working knowledge of PowerShell. He builds on that knowledge to show how to write PowerShell scripts with these best practices in mind.

Chapter 1 contains an overview of PowerShell 2.0. Ed explains some of the differences between PowerShell 1.0 and 2.0, focusing on the new remoting capabilities, WMI enhancements, changes to the way some cmdlets work, and why they have changed the original behavior. He touches on some of the differences between VBScript and PowerShell and why PowerShell might be a better choice than VBScript, despite people’s familiarity with the latter. One of the key advantages of PowerShell over VBScript is that PowerShell was designed to be run from a prompt or as a script. VBScript must be run through something that calls the script. This command-line interaction allows people to work with the script in a much more interactive way and even use native Windows commands with which people are already familiar. Ed closes the chapter by reminding readers of the minimum requirements for PowerShell 2.0, informing readers from where they may obtain the latest version of PowerShell and suggesting where PowerShell 2.0 should be installed (pretty much everywhere).

Chapter 2 is where readers become more familiar with PowerShell 2.0 capabilities. We’ve already read about the promise of PowerShell 2.0, but now we can start to see it in action. Ed starts by introducing readers to the PowerShell 2.0 interactive command line. The first command shows users running processes inside the PowerShell environment. Ed then shows some of the easiest cmdlets to run and remember, followed by the “most important” cmdlets: Get-Help, Get-Command, and Get-Member. I agree that these three cmdlets are key. You can always find out more about what commands are available, how to run them, and what properties are available. Ed then lists several useful cmdlets to enhance filtering, grouping, sorting, and such. Chapter 2 ends with a good introduction to WMI, Remoting, Services, and the Event Log from a PowerShell perspective.

Chapter 3 concentrates on Active Directory. Ed reminds us that what we typically call “AD” is really a legacy term now and is actually “Active Directory Service Interfaces” or ADSI. We tend to think of AD in terms of users and domains, but it contains much more now than it used to. Ed jumps right in by showing readers how to reference ADSI Users, Groups, and Computers with some quick examples of how to reference each within PowerShell. There’s then a quick “Inside Track” by James Turner showing how PowerShell can be used to automate some common administrative tasks around user accounts. James tells us to use the right tool for the job as well. While the LDAP functionality allows more authentication methods, the WinNT functionality allows more object types and more familiar syntax. The bulk of Chapter 3 really concentrates on building a functional script to create and update Users within ADSI. The reader is guided from the more simple scripts to query data through reading a file to automate the creation and updating of user accounts. We also get to see several ways to accomplish this task to introduce the reader to reading from a connection to Excel.

Chapter 4 builds on what we’ve already learned from Chapter 3 to work within ADSI. Ed starts by helping us become familiar with the AD schema through PowerShell. As always, Ed actually puts to use the “Best Practices” that he wants his readers to learn. His first script includes a full Help example and several functions, followed by a breakdown of each function. I found it helpful to look through the original script while Ed explains the details so I could keep track of how each function fit into the bigger picture.

Ed then demonstrates how to query AD for information, going beyond merely listing the results – this is PowerShell after all and the results can be full objects. He gives a pretty good list of ADSI-related properties, shows how to display results that list results, then moves on to true queries – ADO and OLEDB queries. Of course, his overall goal is to really use PowerShell effectively. ADO and OLEDB are useful, but we also want to be efficient. Therefore, the reader is introduced to the DirectorySearcher class and PowerShell 2.0’s [ADSISearcher] to simplify calling DirectorySearcher. Along the way, we see the “FindOne()” method to limit the results to a single row. That is useful to see what data we can expect. Some familiarity with ADSI can really help when going through these chapters.

We’re led from these queries to actually managing user accounts. First, the reader needs to understand ADSI User Account Control values. He then shows using the Bitwise AND operation to compare the various bits set and determine properties of the various user accounts. Ed shows how to find and display disabled user accounts in a short script using bitwise AND and color coding. Readers are then shown how to move objects within AD without too much code. The final example details how to find missing values within AD. These are the values that are expected, but not always completed. Once again, I found it very useful to see the whole script while Ed explains each step of the script. Be sure to include the backtick if you want to type these scripts yourself and try them.

Conclusion

The Introductory section of Windows PowerShell 2.0 Best Practices is well-written and serves its purpose of getting the readers ready to move through the rest of the book. If I have any complaints about this section it’s that it uses ADSI pretty heavily. For people who don’t work directly with ADSI on a regular basis, a lot of the examples are harder to follow. While still possible to see the best practices for PowerShell, it’s easy to get lost in trying to figure out what Ed is doing with ADSI. The Planning section in chapters 5-8 seem to be less focused on a specific set of functionality and much more on how to plan and use PowerShell effectively. I plan to post a review of those chapters in the near future.

I’d appreciate feedback on this review. Too lengthy? Too short? Let me know what’s good and what’s not good. I’d like to write a useful and honest review for those considering this book.

Powershell for Database Developers

noreply@blogger.com (Peter Schott) — Tue, 13 Apr 2010 17:52:00 +0000

I recently presented for the SQLPASS AppDev Virtual Chapter on "Powershell for Database Developers". I promised that I'd get the files used in the demos up to my blog shortly so wanted to make good on that promise. First, some of these depend on having either SQLPSX or the SQL 2008 Snap-in loaded in your Powershell environment. Some of those demos won't work unless that's set up. I think I've got some posts on that already, but if you need help with it, leave me a comment or shoot me a message.

I know that the SQL ISE portions of my demo require SQLPSX. Once again, I highly recommend that you download and install these modules for your use. You don't necessarily need them in your startup profile, but they are useful if you work with SQL Server.

I'd also mentioned Red-Gate in my presentation. Here's one link for more details on Doing Schema Compares.

You can access all of my files used for this presentation on Skydrive.

Powershell: Writing Text Files

noreply@blogger.com (Peter Schott) — Wed, 17 Mar 2010 00:08:00 +0000

I was recently trying to generate a bunch of data that I needed to pass through one of our in-house DLLs in order to use some custom encryption algorithms. Thanks to the wonderful Powershell community, I found several examples on using custom DLL methods. However, I ultimately wanted this data to end up in SQL Server. My first attempt used an “Invoke-SQLCmd” Cmdlet from the SQLPS snap-in. However, generating several million rows took a while to insert. I knew that I didn’t want to queue all of this data in memory, so was inserting one row at a time. I stopped this process after a while because it was too time-consuming.

My next attempt was to push these values to a text file using a variable pointing to a file and then using add-content to write each line to the file. This took longer than doing the SQL inserts one at a time.

I remembered coming across an article by Linchi Shea titled Add-Content and Out-File are not for performance. After finding the link to that article, I found that he recommends using System.IO.StreamWriter to write out files in the most performant manner. I definitely recommend that you check out his article for his summary and examples. For my part, I can say that it took me the better part of 6 hours to generate and populate 10 million rows. I just generated 20 million into a file in < 15 minutes!

I’m including a mockup of my code below in case anyone is interested. I’m definitely not a Powershell expert so as always, be careful if you copy any of this code. My first attempt at directly inserting into SQL Server worked, but threw an error at about 3.5 million rows. As the time was already pretty lengthy by then, I stopped the process and moved on to writing the text file, resuming where I’d left off. That ran for hours until I finally killed it and looked for a better way. The uncommented code is what finally ended up working in a timely manner.

I started by loading my DLL. I called it’s “EncryptValue” function (against just the integer – that’s a longer story) and populated both out to a table. There are several reasons for doing this, but this gave me a good working set for a test project and definitely lays some groundwork for the future. If anyone has any ideas on how I can speed this up, feel free to leave me some comments. If I find some, I’ll post an update.

[Reflection.Assembly]::LoadFile("C:\MyDLL.dll")

# First attempt – insert directly into SQL Server.
#foreach ($i IN 1..10000000) { invoke-sqlcmd –query '
#("insert dbo.MyTable VALUES(" + "$i" + ",'" + [MyDLLNamespace]::EncryptValue("$i") + "')") -serverinstance "localhost" }

#Attempt #2 – Write to file with add-content
#$file = New-Item -type File "C:\Values.txt"
#foreach ($i IN 1..10000000) { add-content $file ("$i" + "," + [MyDLLNamespace]::EncryptValue("$i") )  }

$file = New-Object System.IO.StreamWriter "E:\Values.txt";

foreach ($i IN 10000001..30000000) { $file.Writeline("$i" + "," + [MyDLLNamespace]::EncryptValue("$i") )  }

$file.close();

Powershell Links – 2010-03-06

noreply@blogger.com (Peter Schott) — Sun, 07 Mar 2010 04:19:00 +0000

Here are some of the more interesting Powershell links I came across recently, along with some thoughts about them.

Powershell, String Encryption, and GPG – SQLServerCentral.com brought this one to my attention. Chad Miller discusses working with Oracle through Powershell, and the subject of encrypting the connection strings came up. There are times that we need to connect using something other than Windows authentication. Storing an encrypted connection string makes a lot of sense in those cases. I don’t normally work with Oracle, but the idea of passing and saving encrypted connection strings makes a lot of sense. I do a lot of work against SQL Servers that require SQL Logins so plan to investigate this more thoroughly in the near future.

Powershell Cheatsheet – If you don’t follow the #Powershell hashtag on Twitter, you may have missed out on this useful set of Powershell tips and tricks, including a way to set your current location to a UNC path. This is cool because it’s something you cannot do with a standard command prompt without mapping a drive. There are other tips here that make this well worth bookmarking if you are getting started with Powershell.

The T-SQL Hammer – This is taken from Chad Miller’s blog, but is a great reminder that just because your typical DBA knows T-SQL and thinks in T-SQL does not make that the best solution for everything. If you’re wondering why a DBA might want to learn Powershell, give this a read.

Powershell Help Brower – This script uses Primal Forms and Powershell to build a Treeview Help browser for Powershell. I tested this out and the only entry that seemed to give me fits was trying to get help for Get-ChildItem. That constantly threw an error for me. However, the rest was very nice – an easy way to browse the available commands and see the help for them.

As with anything else you find on the Internet, don’t just copy these scripts and run them. Check them out carefully and be sure you know what’s going to happen. (Someday I’m just gonna copy Buck Woody’s disclaimer for scripts and give attribution. :) )

DDL Schema Change Auditing on SQL Server 2005 / 2008

noreply@blogger.com (Peter Schott) — Mon, 08 Feb 2010 20:03:00 +0000

I’ve run across this before so wanted to write up a solution to keeping track of schema changes we’ve used. We trust our team to make DDL changes, additions, drops in order to get their job done. We also trust them to save those scripts into source control as they are run so we can use them when we release software. However, there are times that people make changes and forget to save the script or just think that it’s a temporary change to test and forget about it. With that in mind, we decided to take advantage of SQL Server 2005’s DDL triggers.
First, we created a database called [Audit] on our development server.
Next, we created a table to store the logs.

USE Audit
GO
CREATE TABLE [dbo].[DDL_Audit](
[DDL_Audit_ID] [int] IDENTITY(1,1) NOT NULL,
[Event_Type] [varchar](100) NULL,
[Database_Name] [varchar](100) NULL,
[SchemaName] [varchar](100) NULL,
[ObjectName] [varchar](100) NULL,
[ObjectType] [varchar](100) NULL,
[EventDate] [datetime] NULL,
[SystemUser] [varchar](100) NULL,
[CurrentUser] [varchar](100) NULL,
[OriginalUser] [varchar](100) NULL,
[EventDataText] [varchar](max) NULL
) ON [PRIMARY]
GO
GRANT INSERT ON DDL_Audit TO public
GO

Note in this example, I granted INSERT permissions to public to avoid needing to give our team any other access to that database. I didn’t want them to read/write rows in that table if I could avoid it. After that, I ran a script in SSMS using Text output to step through all of our databases and generate the trigger create code.

sp_msforeachdb 'SELECT ''use ?
GO
SET ANSI_PADDING ON
GO
CREATE TRIGGER trg_DDL_Monitor_Change
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
SET ANSI_PADDING ON
declare @EventType varchar(100)
declare @SchemaName varchar(100)
declare @DatabaseName varchar(100)
declare @ObjectName varchar(100)
declare @ObjectType varchar(100)
DECLARE @EventDataText VARCHAR(MAX)
SELECT 
@EventType = EVENTDATA().value(''''(/EVENT_INSTANCE/EventType)[1]'''',''''nvarchar(max)'''')  
,@DatabaseName = EVENTDATA().value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''',''''nvarchar(max)'''')  
,@SchemaName = EVENTDATA().value(''''(/EVENT_INSTANCE/SchemaName)[1]'''',''''nvarchar(max)'''')  
,@ObjectName = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectName)[1]'''',''''nvarchar(max)'''')
,@ObjectType = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectType)[1]'''',''''nvarchar(max)'''')   
,@EventDataText = EVENTDATA().value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''',''''nvarchar(max)'''')
insert into Audit.dbo.DDL_Audit (Event_Type, Database_Name, SchemaName, ObjectName, ObjectType
, EventDate, SystemUser, CurrentUser, OriginalUser, EventDataText)
select @EventType, @DatabaseName, @SchemaName, @ObjectName, @ObjectType
, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()
, @EventDataText
GO
'''

We took the results of that script and removed any databases that did not matter for purposes of tracking changes. We ran that and created the appropriate triggers on all of the databases. That left us with the notification messages. We handled this through a SQL Server Agent job using Database Mail.

IF EXISTS (SELECT 'x' FROM Audit.dbo.DDL_Audit
WHERE Event_Type NOT LIKE '%statist%'
AND SystemUser NOT IN (service account list here)
AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101))
BEGIN
DECLARE @email_from nvarchar(100)
, @email_address nvarchar(200) 
, @TheSubject nvarchar(255)
SELECT @Email_Address = 'myemail@myemail.com'
SET @email_from = 'DevServer@server.com'
select @email_address as 'To:' ,  @email_from as 'From:'
set @TheSubject = 'Recent Schema changes on ' + RTRIM(@@SERVERNAME)
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>DevDB Schema Change</H1>' +
N'<table border="1">' +
N'<tr><th>Database_Name</th><th>SchemaName</th>' +
N'<th>ObjectName</th><th>Event_Type</th><th>ObjectType</th>' +
N'<th>EventDate</th><th>SystemUser</th><th>CurrentUser</th><th>OriginalUser</th><th>EventDataText</th></tr>' +
CAST ( ( SELECT td = Database_Name,       '',
td = SchemaName, '',
td = ObjectName, '',
td = Event_Type, '',
td = ObjectType, '',
td = EventDate, '',
td = SystemUser, '',
td = CurrentUser, '',
td = OriginalUser, '',
td = EventDataText
FROM Audit.dbo.DDL_Audit
WHERE Event_Type NOT LIKE '%statist%'
AND SystemUser NOT IN (serviceaccount)
AND EventDataText not like '%ALTER%INDEX%REBUILD%'
AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101)
ORDER BY Database_Name, ObjectType, ObjectName, EventDate, Event_Type
FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default' ,
@recipients=@email_address,
@subject = @TheSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
END

We scheduled this code in a job step that runs daily at 10am. Any changes in that time frame were then sent to a team of people on a daily basis. If no changes were made, no email was sent. Hopefully this will give someone some ideas on one way to audit changes to their DBs. I make no claims that this is the best way, but it worked well for us.

You can download the script file containing all of this code using the link below.

Powershell Community Extensions

noreply@blogger.com (Peter Schott) — Sat, 30 Jan 2010 20:11:00 +0000

While with the time and knowledge we can write a lot of the things we’ll use in Powershell, it’s often not worth it to re-invent the wheel. There’s a group of Powershell coders who have put together the Powershell Community Extensions – a set of Powershell aliases, functions, and other useful code to make working with Powershell a little easier.

For example, there’s a built-in function to split a string. Functions and DLLs are included to read and write ZIP files. There are even some Cmdlets written to allow easy reading and writing from the clipboard or MSMQ. While not all of this will be immediately useful, it’s definitely worth having around.

Installation should be pretty straightforward. Make sure all instances of Powershell are closed. The recommended install is performed using the MSI file. If you want the latest version, you’ll have to unzip the file into your Documents\Windows Powershell\Modules folder. After that, you can import the module using “Import-Module PSCX”. There is supposed to be a way to customize what you actually import as well so you don’t load items you’ll never use. The details should be on the Powershell Community Extensions site.

While I’m at it, I’d like to give props to John D. Cook who has written a short pamphlet called Day 1 With Powershell which lists a lot of little things that he wished he’d known before starting. There is some good, basic information in it, including configuration, some basics behind Powershell decisions (like = vs –eq), and some pointers to other Powershell sites. I’d recommend it if you’re just getting started with Powershell as it could give you some pointers on how to proceed past the basics.

Powershell – Comparison Operators

noreply@blogger.com (Peter Schott) — Wed, 27 Jan 2010 19:30:00 +0000

I’m writing this one so I remember these operators. Powershell doesn’t use standard operators such as =, <, <>, !=, etc. These operators are used in the following manner.

Operator	Conventional Operator
-eq	=
-ne	<> or !=
-gt	>
-ge	>=
-lt	<
-le	<=
-contains	e.g., 1,2,3 –contains 1 is true
-notcontains	e.g., 1,2,3 –notcontains 1 is false
-not !	e.g., –not ($a –eq $b) Logical Not operator
-and	Logical AND e.g., ($age –ge 21) –and ($gender –eq “M”)
-or	Logical OR e.g., ($age –le 21) –or ($InSchool –eq $true)

I think this is one of the areas that is going to trip me up quite a bit. In Powershell, “=” is always used to assign. “<” and “>” are used for redirection. The names make sense to me in a way, but I can tell I’ll be spending some time with these before I’m completely comfortable with this concept.

The above are the base comparisons. If you prefix them with “i” the operator becomes case-insensitive. If you prefix an operator with “c”, the operator will be case-sensitive. Thus we would get the following:

  "johnson" -eq "Johnson" #True

  "johnson" -ieq "Johnson" #True

  "johnson" -ceq "Johnson" #False

Where-Object

This is a useful Cmdlet to filter out results from the pipeline, but this should also be used with care. The initial object will still contain everything with which it started. The results will then be filtered. If you have a way to filter these results prior to passing them through the pipeline, you’ll often have better performance. The following will find all instances of Firefox running on the machine:

  Get-Process | where-object {$_.name -eq 'firefox'}

We could then pass this through the pipeline to operate on that specific object. Where-Object can be abbreviated with a “?”.

If, ElseIf, Else

This is a pretty basic statement for Programming and seems to make sense in the way it was implemented in Powershell.

If (condition) {#do something}

ElseIf (next condition) {#do something else}

Else (final condition) {#final action}

As in most uses of If, the process will work through until it finds a condition that evaluates to $true. Once it hits that, it will execute the command(s) inside the corresponding curly braces and exit out of the If.

Switch

Sometimes it doesn’t make sense to write out a bunch of IfElse lines to keep checking conditions. Switch can help shorten that. A simple example would be something like this.

$Value = 15
Switch($Value)
{
  1 {"Value is 1"}
  2 {"Value is 2"}
  15 {"Value is 15"}
  default {"Value is $_"}
}

Powershell interprets these in order and will evaluate each one in turn. The default line tells Powershell what to do if no match is found. If you want Powershell to stop evaluating when it finds a match, add a ;break inside the {} following the matching condition. Otherwise, it could hit the same matching condition and evaluate each one it finds. This may be desirable at some times when you want to act on all matching statements. Other times you may want to short-circuit the process.

Another advantage of the Switch statement is that you can evaluate expressions instead of just the values. In the example above, the behavior behind the scenes is performing {$_ –eq 1} and {$_ –eq 2}, etc for the lines in the switch. You can substitute your own expressions in here easily to change the evaluations.

Switch supports character comparisons (defaulting to case-insensitive equals), but can do other types by default with a parameter on the Switch statement (e.g., –regex, –case, –wildcard, etc.)

The biggest drawback for Switch is that it would be considered a “blocking” component in the pipeline. It needs to wait for all results before it operates. If you just want to do some filtering on the results, you would be better off using Where-Object or filtering up front.

Powershell – Quite note on Objects

noreply@blogger.com (Peter Schott) — Wed, 27 Jan 2010 00:15:00 +0000

I’m sure this comes as no surprise to anyone who’s dabbled in Powershell, but just about everything in Powershell has an Object underneath. The results we see on screen come from those objects and it’s only when those results are formatted, output, captured, etc that they cease to be an object. This drives much of the power in Powershell. You can set a variable to the contents of an object or part of an object and use that later.

Typically objects will have properties and methods. Properties define what an object “is” – e.g., Red, Small, Named, etc. Methods define what an object can do – e.g., Write, Read, Slice, Mix, etc. There are quite a few internal methods and properties that are common across the internal Powershell objects. You can find these by piping the object to the “Get-Member” cmdlet.

  $host | Get-Member

You can add your own properties and methods to objects easily by calling the Add-Member Cmdlet. The most direct way to do this seems to be by piping the object to the Add-Member Cmdlet.

  $host | Add-Member –Member NoteProperty –Name PropertyName –Value PropertyValue

As Powershell is based on MS technologies, it makes sense that you can use the .NET Framework to work with objects. I will admit that I’m not as familiar with the wide variety of .NET objects so will not try to go into detail. For one example, here’s a way to call the .NET framework to look up a DNS name by IP address.

  [system.Net.Dns]::GetHostByAddress("207.46.19.190")

Perhaps a more useful class would be System.Environment, first by querying its Static members:

  [System.Environment] | Get-Member -Static

You can see that there are a lot of members that are useful. If we choose one and don’t enter valid parameter data, we can even get useful error messages at times. For example, the following will return an error with valid parameters in the message:

  [system.environment]::GetFolderPath("z")

We can tell by the error message, that some valid parameters would be Desktop, Programs, MyDocuments, Home, etc. Entering a valid parameter in this case will then return the path stored in the Environment for that variable.

You can even define your own objects using the New-Object Cmdlet. With no parameters, this creates an empty object. You can create objects of various types by passing the type as the first parameter to the New-Object cmdlet. If you set a variable to an object, you need to be careful about type casting. Setting a variable to a string containing a date will not necessarily cause that variable to be an object of type System.DateTime. It will most likely be a System.String. You may need to explicitly cast the object as the type you desire or force the value to be of the type you desire in order to cast the object as the correct/desired type.

You can call on COM objects, .NET objects, Powershell objects, Assemblies, and probably more than I’ve listed here as long as you know the name or way to call those objects. I’m still learning this are of Powershell, but hope to expand on this in future posts.

Powershell – Piping

noreply@blogger.com (Peter Schott) — Wed, 13 Jan 2010 15:00:00 +0000

One of the most often-used features I’ve seen so far in Powershell has been the concept of piping the results of commands into other commands to ultimately return something formatted, limited, or otherwise morphed into the desired output. The easiest examples might be something like:

dir | more
dir | sort-object length –desc | format-table -autosize

The first command returns a listing of the files in the current folder one screen at a time, pausing for you to read each screen and tell the command when to advance. The second is a string of commands to get a listing of files, sort by the file size from largest to smallest, then auto-size each column for a best fit based on the data. Because each result set is passed as an object through the pipeline, you don’t have to handle the format of the text or other conversions. Powershell handles the objects and consumes them until you reach the end. By default Powershell appends a hidden “| Out-Default” command to display the results on screen. If you want to see more options for output, you can run:

Get-Command Out*
Get-Command Export*

Obviously there are a lot of other commands that can be used in the pipeline, but these seem to be especially useful if you want to output the results to something other than the screen. Also note that if you convert your results along the way using Format-Table or Out-String, you will change the results from an object into an array of text. (Out-String is the only output command that you can insert into a pipeline and not stop the pipeline. All other "Out-" commands will terminate the pipeline.)

Blocking vs. Non-Blocking

As with other languages, some Cmdlets may be “blocking” other parts of the pipeline. For example, a sort cannot pass its results on until the entire result set has been sorted. The “more” Cmdlet in Powershell is also blocking, but the equivalent “Out-Host –paging” will do the same thing without blocking. When to use various commands is an important consideration if you pipe multiple Cmdlets together, especially if you will be dealing with large objects or result sets.

Filtering

Filtering is accomplished by piping your resultset to Cmdlets such as Where-Object, Select-Object, ForEach-Object, or Get-Unique.

Where-Object allows you to examine all objects and return just those matching your criteria. This is very similar to a WHERE clause in SQL Server.
Select-Object acts in a manner similar to the SELECT clause of a SQL Statement and allows you to pick which properties are displayed. It can also handle things such as “TOP 5” or “Distinct” (not exact commands), but can also do some interesting handling of values in the array. For example, you can choose to display every other line of the result set or the first, last, and middle rows by examining the array. (Note that this will create a new object in a lot of cases because you're filtering the columns.)
ForEach-Object operates on each result in the resultset to run commands against them.
Get-Unique eliminates duplicates in the resultset.

Filtering would be considered a Blocking operation in many cases and can sometimes be done more efficiently through the native commands or Cmdlets. For example, if you need to pull back just the files of type “.txt”, you may be better off filtering that out in your dir command rather than passing the entire resultset of all files to the Where-Object Cmdlet.

Tee-Object
It’s worth mentioning this Cmdlet because it could be really useful in troubleshooting. A simple example would be:

dir | Tee-Object -variable t1 | Select-Object Name,Length | Tee-Object -variable t2 | Sort-Object Length –descending

This would pipe the output of the “dir” command into a variable called $t1. It would then pass the output to Select-Object to limit the results down to Name and Length, then pass that resultset to a variable called $t2. Finally, it would output your results ordered by length descending. You would now have two variables to examine results as you stepped through the pipeline. If you are getting unexpected results, this would be very valuable in troubleshooting. You can also use “-filepath” instead of “-variable” to store results to a file instead of a variable.

Conclusion
There is a lot of information available for piping commands because this is one of the true strengths of Powershell. I'm not going to go into a lot of details at this point. There will be more examples in future posts and there are lots of other posts detailing the process. Piping takes a set of results and passes that to the next command, and to the next, and to the next, until it gets to the end of the pipeline. At that point, the results are returned, stored, or discarded (if you chose to output to Out-Null). You can store these results along the way for comparison or even store them in a file for later analysis.

I plan to spend a pretty significant amount of time familiarizing myself with the various uses for the pipeline. Because it's used so heavily, this is going to be a key component to understanding and getting the most benefit from Powershell.

Powershell – Arrays & Hash Tables

noreply@blogger.com (Peter Schott) — Sun, 10 Jan 2010 04:00:00 +0000

I don’t have too much to say about Arrays at this point, but as I’m partly blogging this for my own education, I’ll put down what I’m learning. At this point, I’m expecting the arrays to be useful when trying to build my example project. One piece will be to step through existing files to look for certain flags or traits to tell me to do further processing.

Arrays

First of all, any variable that stores the results of a command with multi-line output will result in an array. This includes commands such as dir and ipconfig. Arrays start with position [0]. You can create an array by using comma-separated values such as $Variable = 1,2,3,4 or by using $Variable = @(1).

The first cool thing I saw was that using negative positions in an array steps through the array in reverse. That means if I have an array of $Variable = @(0,1,2,3,4), that $Variable[1] will be 1. $Variable[-1] will be 4. I can see this being very useful when you need to step backwards through the results. This is very intuitive to me and I wish this were easier to implement in other scenarios. However, stepping through the array backwards could be tricky because it could result in creating a copy of the array. For small arrays this wouldn’t necessarily be a problem, but with large arrays, I could see performance becoming an issue. If you need to reverse the entire array, you can simply use [array]::Reverse($Variable) to reverse the array elements in place. (This actually changes the array stored in the variable.)

You can easily specify to return multiple elements from the array using $Variable[0,4,-12] which would return the 1st element, 4th element, and the element 12th from the end.

Adding an element is pretty easy. You can type:
@Variable += @(NewElement)
However, this will create a new copy of the array in order to add another element because arrays in Powershell cannot be resized. This may be something to consider when working with large arrays.

Hash Tables

Hash tables seem to be pretty much what you’d expect. They store key/value pairs such as “Name”, “SQL Server 2008 Enterprise Edition” stored together. You define these in a manner very similar to that of arrays:
$Variable = @{Name=”SQL Server”; Version=”10.0"; Edition=”Enterprise”}
That will define a new hash table containing 3 keypairs. Name, Version, and Edition. Each will have a Name and Value property that will display when you display the variable. You can display just the value of a particular keypair with $Variable.Name or $Variable.Version.

Adding a new keypair is easy as well. Use:
$Variable.NewName = “New Value”

Similarly, you can remove a keypair with:
$Variable.Remove(“Name”)

Hash Tables are used with the Format-Table command as well. You can customize the columns returned by manipulating their respective hash table values: Expression, Width, Label, and Alignment. Use get-help Format-Table for more information.

By default, copies of an array or hash table are actually copies of a pointer to the values. That means that if you just set the variable with the normal assign of $Var1 = $Var2, you’ll end up with pointers. Modifying either of those variables will affect the other(s). Modifying the array/hash table or using the .Clone() command on either will make a new copy.

Finally, if you need to strongly type an array, you can do so by putting the datatype before the array definition:
[int[]]$MyArray = @(0,1,2,3)

This will ensure that all values stored in the array are of that datatype. Any attempts to add/remove values to the array that are not of that datatype will raise an error.

There are a lot of uses for arrays and hash tables. This just scratches the surface of what can be done, but it should be enough to get you started.

Powershell – Variables

noreply@blogger.com (Peter Schott) — Sat, 09 Jan 2010 06:51:00 +0000

I’ve just started to play around with variables in Powershell. I used them a little bit before this, but am in the process of figuring out basic commands around variables. First thing to note is that variables are not case-sensitive. That means that a variable called “$MyVariable” is the same as “$MYVARIABLE” or “$mYvARIABLE”. You can declare a variable and assign a value easily using something like:

$variable = value

That pretty much covers the majority of basic assignments. You can also use the Set-Variable command to not only set a variable, but change some options such as making a variable read-only or making it into a constant. Once a constant has been declared/set, you cannot delete it. It will be cleaned up when the Powershell session ends. The only somewhat tricky point around variables is using special characters. All variables should be declared within {}’s if you need to use special characters.
It’s easy to list all of the variables and their values using:

dir variable:
dir variable: | Format-Table Name, Value, Description -autosize

The first command lists all variables and their values. The second lists the name, value, and a Description property as well and then auto-sizes everything accordingly. Useful if you want to set other properties of your variables besides name and value. You can also use that latter command with a “-wrap” parameter to show the definitions of the system-level variables.

If you want to use Windows Environment variables, you’ll want to reference the “env:” virtual drive within Powershell. These are the variables such as your Path or Windows folder that are used within Windows. Any changes you make to these variables are only set within the scope of your Powershell session.

By default, variables stay within the scope of the function or session, but you can override this with $private to limit a variable to a scope, $local to use default scoping and enable variables to be read by sessions the current session creates, $script to allow the variable to work anywhere in the current script, and $global to allow the variable to be used anywhere, even external functions and scripts.

Variable Types and Attributes

Working with variables is easy with the defaults because they are weakly or loosely typed. If I assign a string to a variable, that variable contains a string. If I assign a bit value, the variable contains a Byte. That means that variables can change types easily as the script runs. However, that also means that if I’m expecting a variable to contain a date value and it somehow gets a string or floating point value, I could be very surprised. Powershell allows for strong data typing of variables as well. If you define a variable in this manner:

[datetime]$myDate = "2009-12-25"

You will explicitly declare a variable of type DateTime. If you then attempt to set the variable to a string, you’ll get an error message. The variable types are standard .NET types with a handful of specific Powershell types.

You can set other properties using the Attributes for a variable and can even clear out the strong Typing using an Attributes.Clear() command against the variable. Attributes can be used to set constraints around a variable such as whether or not the variable can store $Null values, check constraints, or even RegEx patterns for the data stored in the variable.

This is by no means a definitely explanation of everything around variables in Powershell, but it’s a good start for most people. You can always run get-help About_Variables within Powershell for more details or get-help Set-Variable to read more about setting variables. I may update or post a follow-up if something else pertinent about variables comes up. I’d love to know a way to list out variable datatypes from within Powershell, but haven’t found a way yet.

Powershell – Modules and Profiles

noreply@blogger.com (Peter Schott) — Fri, 08 Jan 2010 20:36:00 +0000

Probably not technically correct, but pretty close. I’ve been trying to use SQLPSX for Powershell, recently updated to v2.0. For the longest time, I’ve tried various methods to import the modules and couldn’t get them to import successfully. I knew that this was likely due to lack of knowledge so spent a little time tracking down the root cause of my problems.
After quite a bit of searching, I finally figured out why I’ve been having such a hard time loading Powershell modules. By default, the Modules reside in
%USERPROFILE%\Documents\WindowsPowershell\Modules
I verified that this was set within Powershell with quite a bit of different code snippets. For some reason, this folder was never created in my Documents folder. I manually created a folder called “WindowsPowershell” and another inside that called “Modules” and then extracted each of SQLPSX’s module folders into that newly created directory and I finally could use external modules. I restarted Powershell and used

import-module SQLServer

I got a warning that Powershell could not had to set my execution policy to allow remotely signed scripts. That’s not too hard to do, but I couldn’t import new modules without doing that. I ran

Set-ExecutionPolicy remotesigned

and set the default to “Y”. (This is a local test machine so I’m not as concerned about changing this setting.) I re-ran my import-module command and the modules finally imported.

Of course, now I’d like to automatically load these commands whenever I run Powershell, as well as customize the default Powershell environment to pull in the various SQL Powershell modules used in the SQLPS environment that ships with SQL Server 2008. I found this MSDN article that discusses profiles and how to work with them. You can create a default profile easily with:

new-item -path $profile -itemtype file –force
notepad $profile

This will create a new profile for all users and all shells on that machine. The second command will open up that new file for editing. Place whatever commands you want to run on startup in this file. For example, this article discusses how you can get all of the behavior of the default SQLPS mini-shell. While not optimal, it looks like a great way to get the full power of Powershell w/ the new SQL functionality.

Getting Started with Powershell

noreply@blogger.com (Peter Schott) — Fri, 08 Jan 2010 05:41:00 +0000

I’m in the process of learning PowerShell. This seems to be the first scripting language that we can use at the server/desktop level that has some serious backing from Microsoft. I remember VBScript, CScript, Batch files, and similar, but it seemed that if I wanted to run a script, I had to find workarounds or plug-ins to do what I wanted inside of some other language and then mix and match those together. Powershell seems to drive a lot of processes behind the scenes. That being said, here’s a quick summary of some important things I’m learning in the process.

1. Powershell can call executables directly, but needs an explicit path to those executables if they aren’t in the system path. This means that you may need to use .\MyProgram.exe instead of just MyProgram.exe.

2. get-help command - I can see myself spending a lot of time with this to figure out syntax and uses for various commands. As I’ve played around with it tonight, I’m really impressed by how much information is available without needing to leave PowerShell. Reminds me a lot of the “man” pages in *IX, but with more examples and in a language I can understand.

3. get-command –verb verb – Another useful command to list all Powershell Cmdlets containing that verb.

4. Parameters can be abbreviated as long as there are enough characters to uniquely ID the parameter. An error message will be thrown if there aren’t enough. Probably better to let auto-complete handle the parameter name in that case to avoid possible ambiguity.

5. Common Parameters. There are apparently several common parameters that should be available for most Cmdlets. I can see a lot of use for –ErrorVariable and –OutVariable. These are designed to capture error or output details, respectively.

6. Aliases. I can see these being both helpful and a little painful. Helpful in that a lot of familiar commands such as dir and ls are aliases, but painful in forgetting that I’ve set one and seeing some underlying Cmdlet change. I guess I have some qualms from my days of using a pretty customized BASH shell and forgetting a lot of the basics that were used to create that shell in the first place. Just running the get-help on Alias returned several screens of information. Example command to run to see CmdLet followed by its aliases:
dir alias: | Group-Object definition

And just as I figured, there’s a way to export the aliases and re-import them from a file later so you don’t need to define them each time you restart Powershell. Export-Alias and Import-Alias

7. Virtual Drives. PowerShell has a lot of different virtual drives set up to access Aliases, Registry settings, physical drives, and others. I’ll be exploring those more as I need them. I’m halfway assuming that one of these tied to a server would be related to AD structures, but that may just be a whole new set of Cmdlets.

I think this may be a good place to stop for the day. It seems the next section deals with functions which are reminding of DOS Batch file parameters at first glance. I hope that is not the case, but I’ll know more about that tomorrow. So far I’ve gotten a good handle on some of the basics and learned some ways to help myself during scripting. That’s a great start.

Learning to work around DB Pro

noreply@blogger.com (Peter Schott) — Wed, 11 Nov 2009 18:14:00 +0000

I’ve been trying to get a multi-database solution working correctly with MS Visual Studio Team System for Database Professionals (aka DBPro or DataDude). Anyone who has tried to tie multiple co-dependent databases together within DB Pro has experienced some of the pain points in working with these projects. Hopefully these notes can help you to some extent.

Pain Point #1: Circular References

This is the largest challenge that we face in our environment. DB Pro doesn’t really understand that DB A can reference DB B which can then reference DB A. You can easily add a Database Reference to another project in your solution by right-clicking the “references” sub-folder and adding a new Database Reference. However, once you’ve done that, you can’t add one from that project back to the original. I understand you don’t want to get caught in an endless loop, but it would be nice to have some sort of Max Level of Recursion set that would stop the program from getting caught in that.

That being said, there is a way around this. You can generate a .DBSchema file for your existing database using the instructions from this site. Note that you may need to tweak the options a little bit. I think the “ModelType” parameter has been deprecated in GDR2. Save that DB Schema file to some central folder and repeat for each database you want to reference. Once done, go back into your projects and add a reference to each of those files. Because these are DBSchema files and not the actual database projects, DB Pro will recognize these and you’ll be able to add a reference as needed to each database.

Remember that these schema files will not be kept up to date automatically. As you build your DB Projects, you’ll need to update those schema files in some way.

Pain Point #2: System Objects

This isn’t a hard problem to work around and there are a lot of hints out there about this. The easiest way to handle references to the system objects is by adding a reference to the included DBSchema files created when you installed DB Pro. These can generally be found in %ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer. There should be sub-folders for 2000, 2005, and 2008. Simply include these references in your projects and you should be set.

This may not always help you if you reference another database referencing the system objects, though. I ran into this problem and had to find yet another workaround, which leads me to…

Pain Point #3: “Unresolved References”

When all else fails and you can’t actually get around the “Unresolved Reference” warnings due to the use of temp tables, referencing another database that references the system objects, or some other reason you have a final recourse. That is to tweak the file properties to ignore certain error messages or warnings that occur for that file. It’s not a great solution because you could miss some valid warnings, but it can help you get around those warnings that keep popping up when you try to build or deploy. To add an exception just find the file, select it, and edit its properties. The last option is to “Suppress Warnings” and that will let you bypass the warnings that normally occur when DB Pro can’t figure out what you are trying to do. This should really only be turned on for code that is definitely working in your environment and the code should be reviewed carefully when enabling this option.

Finally, I don’t necessarily claim that any of these are best practices. We’re learning here and trying to figure out the best way to work around the limitations and quirks in DB Pro to get it working in our environment. I’m more than happy to learn new and better ways to do things so feel free to share your tips or to correct me where I’m wrong.

SSIS and XML Output

noreply@blogger.com (Peter Schott) — Thu, 01 Oct 2009 20:09:00 +0000

I was pretty surprised by this and am in pretty good company. SSIS 2005 and 2008 do not have an XML Destination adapter. We have another "opportunity" from Microsoft to work out a way to export XML data. I'll disagree that this is a good idea, despite those saying that it's easy to work around in a script component. I'm not a great SSIS Script writer.

My problem - I want to take a simple SQL query that uses FOR XML AUTO to generate XML and put that into a file. If you set this as the only part of your source query, you end up with an output of DT_Image. Converting that to text results in a long string of numbers. While I was amused at the result, it didn't help me generate the necessary XML for our partners.

I came up with a relatively simple workaround that works well for me because I'm only dealing with one XML Data Set at a time. I put something like the following in my OLEDB Source:

DECLARE @XMLOutput XML
SET @XMLOutput = (SELECT * FROM MyTable FOR XML AUTO)

SELECT CAST(@XMLOutput as VARCHAR(MAX)) as XMLResults

I was then able to pipe that into a Delimited Text File destination with a CRLF Delimiter and no column headers. That generated results I could pass on to our partners. While it's not the most elegant solution, it was much easier to me than trying to write and maintain a script component just to handle XML.

Data Loads

noreply@blogger.com (Peter Schott) — Thu, 17 Sep 2009 20:41:00 +0000

One of the things I really like about SQL Server Integration Services is that it can load large data sets quickly, especially if you need to look up values along the way. However, there are times that things behave in strange manners that seem hard to explain. We were recently trying to load about 64 million rows into a warehouse-type database and kept hitting a bottleneck once we got past around 30-35 million rows. No matter what I tried, the load would run at about 2 million rows per minute, gradually slowing down until it reached the 10,000 rows per second load speed. I found several helpful posts, including some pointers to check the MaxRowBufferSize and similar settings. Tweaking these helped someone who was on a memory-bound machine with a very similar problem. I tried that and saw my initial load times improve, but still slowed down to a crawl at around the same point.

I learned more about looping through (aka "shredding") a recordset of CustomerID values to try to go through that set. I set up a FOR Loop to loop through 5 million records at a time, I checked for processes running against the table. Nothing seemed to help. I had posted information on Twitter, had a co-worker looking at the package with me, and even got some replies from Paul Randal. (Thank you for the assist, Paul - sorry I wasn't checking my tweets more regularly that time.)

In the process above, I was challenged trying to set the CustomerID in some dynamic fashion on my source query and eventually resorted to treating the SQL Command as an expression and hacking in my variables into the WHERE clause. It worked, but definitely felt awkward. I am more than open to some suggestions about how to use SSIS variables inside of a FOR loop as part of the OLEDB Source Command. I also learned a little more about watching variable values - like needing to have a breakpoint set before they'll be available. And my co-worker pointed me to a wonderful discussion showing that DtExecUI runs only in 32-bit mode. I'd been using that as a quick/easy way to get my parameters plugged in and to keep an eye on the general progress of the load.

About the same time that Paul suggested checking my index fragmentation levels, I remember seeing a very similar behavior pattern several years ago when a co-worker was trying to update a column in the middle of a somewhat wide clustered index on a multi-million row table. The server was thrashing all over the place, trying to re-order millions of rows as this command took place. I checked the table and sure enough, there was a wide clustered index on the table that would cause exactly that behavior. I don't know why I didn't check it before other than thinking that I'd just created a test table and no indexes on it. In retrospect, that was poor planning on my part. For the future, I'll remember to check clustered indexes before I try to load millions of (unsorted) rows into a new table.

It was a somewhat humbling experience, mostly because one of the basic things I know about loading lots of data was also something that I just completely ignored when doing all of my troubleshooting. Still, a little humility is a good thing and I've now re-learned several programming techniques in the process. The best part about this was seeing a 6 hour load process reduced down to 30 minutes, including dropping and re-creating the clustered index. That's going to make a noticeable difference in our nightly processes.