PowerShell 3.0 Get-Content new parameter

I’ve been working on a PowerShell script which will be used to build out and configure our new SQL Server AGs. As part of the build, I wanted to install Ola Hallengren’s Maintenance solution and Adam Machanic’s sp_WhoIsActive, as those are standard in our production environment.

I have a function which reads a list of .sql files into an array, then iterates through the array reading in each file into a string using get-content, and then calls a function to execute the string.

So far, so good. That works great for my scripts which configure MIN/MAX memory, setup Database Mail so we get our alerts, create our server management jobs, and correct tempdb. However, 3 of the scripts were not running. No errors, just failing silently.

At one of my coworker’s suggestion, I wrote the contents of the string imported from the .sql file to the script log. It looked fine, nothing had been truncated. Curiouser and curiouser! I then copied the string from the log into SSMS. AHA!

Get-content removes line feed, pulling the entire file into a single long line. That first ‘–‘ commented out the remainder of the script!
After a bit more googling, and running get-help, I discovered that PowerShell 3.0 and greater have a new parameter for get-content, -Raw, which preserves carriage returns. Hooray!

Happily, I now only have one script which will need to be run by hand; I’ll just have to solve that problem another day :)

Happy coding!

2015
02/10
CATEGORY
PowerShell
Comments Off on PowerShell 3.0 Get-Content new parameter

#TSQL2sday – Something New Learned

sql-tuesday1

 

 

 

 

 

 

Today’s installment of #TSQL2sday is brought to you by Chris Yates (b/t); the subject is ‘Something New Learned’.

My first #TSQL2sday!

Our production environment includes a couple SQL Server 2008 R2 n+1 clusters, to which we recently applied SP3. The upgrade failed on one of the instances due to a primary key violation. My coworker uninstalled the patch, restored msdb from backup, and was able to get the production instance running again (and some much-needed sleep).

When I arrived to work in the morning, I resolved to have a look.

I found the error in the SQL Server log: a pk violation against dbo.syscategories. When I compared the syscategories data on this instance against other instances in the cluster, I found one the category names in the 1 – 99 range on the other instances matched one of the categories above 99 matched one of the names above 99 on this instance. I then checked the script the patch failed on. The patch script first delets all categories having a category_id < 100, then re-adds them, leaving everything >= 100 as-is; the primary key is category_type and name. SQL Server expects that all records having category_id <= 99 are system created, and that no one will ever alter one.

To recover, I first verified the user-added category was not used. I then setup a test environment using a restored copy of original msdb from the problem-instance. I then used sp_delete_category to remove the same-named category, and verified that SP3 installed correctly.

I compared the Categories data shown in the GUI to the data in the table; the value causing the pk violation showed up twice in the GUI. Microsoft uses constants to represent system-entered values.

As a result of this, we altered our process to always take a backup of the system databases before applying a patch. We were lucky; the backup had run automatically not long before the maintenance window started. Not really something new; more a reminder to follow best-practice :)

2014
11/10
CATEGORY
#tsql2sday
3 comments

Check user rights

Multiple DBA’s, each with their own style of database and user management, has left my employer with a combination of users and groups across several instances containing multiple databases. I had a request to move a database from a soon-to-be-deprecated instance, and thought this would be a perfect opportunity to begin the rather daunting task of combining users with similar rights into groups. In order to insure the user experience was the same before and after moving them into an AD group, I ran the following script to verify rights:

USE nancy_test;

EXECUTE AS USER = ‘mydomain\username’;

SELECT *
FROM fn_my_permissions(NULL, ‘Database’)
ORDER BY subentity_name, permission_name ;

REVERT;
GO

Execute as user allows you to impersonate another user. Fn_my_permissions lists permissions granted to the executing user. Executing as another user requires ‘impersonate’ rights in the database.

Happy Querying!

2012
05/01
CATEGORY
security
TAGS
Comments Off on Check user rights

Hello World …

I’m alive!

Posts will mostly be about SQL Server and my adventures in database land, with occasional forays into running.

~N

2012
04/08
CATEGORY
Uncategorized
Comments Off on Hello World …