Resolved – A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘$space.ForegroundColor

3 Replies

I was working on some Powershell automation for a weekly process for one of my colleagues and set up the powershell script to run via a SQL 2014 agent job.  After some initial permissions errors I ran into this error:

Executed as user: <Agent Account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘$space.ForegroundColor = $host.ui.rawui.ForegroundColor ‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Exception setting “ForegroundColor”: “Cannot convert null to type “System.ConsoleColor” due to enumeration values that are not valid. Specify one of the following enumeration values and try again. The possible enumeration values are “Black,DarkBlue,DarkGreen,DarkCyan,DarkRed, DarkMagenta,DarkYellow,Gray,DarkGray,Blue,Green,Cyan,Red,Magenta,Yellow,White”.”  ‘.  Process Exit Code -1.  The step failed.

  This was really confusing at first because there was certainly no code that manipulated the console output – I was running from an agent job.  After a little testing I found the error to be with a piece of code within my script which DOES manipulate the console output – specifically the native CLS function.  As that was obviously part of the process of me testing the code and not anything I was going to need for the piece of automation I was working on removing the “CLS” from my script resolved the problem.

The error message was really misleading here, and I hope that it is addressed by Microsoft in the future.  It references a specific line of code in an agent job step, but for me that line corresponded with an echo statement.  At the least it should indicate that the error is in a called piece of code, not in the source script, and preferably shouldn’t generate an error at all, as all the output from the job step is in plain text.

 

Speaking at SQL Saturday Sydney 2017

1 Reply

I’m very happy to once again be making a trip across the Tasman and will be speaking about Increasing your SQL Server performance at SQL Saturday Sydney on February 18th.  Check out the list of speakers here:

SQL Saturday Sydney 2017

This will be my second time presenting at the Sydney event after an enjoyable trip there last year.  If you are in the Sydney area I look forward to seeing you there.

Speaking at SQL Saturday Melbourne 2017

Leave a reply

I’m very happy to once again be making a trip across the Tasman and will be speaking about adding Powershell to your DBA toolkit at SQL Saturday Melbourne on February 11th.  Check out the list of speakers here:

SQL Saturday Melbourne 2017

It looks like the Melbourne team have once again pulled together a great list of speakers and it will be a fantastic days SQL learnings.  If you are in the Melbourne area I look forward to seeing you there.

How to Force a Database to A Specific Database_ID

2 Replies

I walked into the office this morning and got asked a strange question:  “Does a database you detach and re-attach have the same database id before and after the detach?”.  While it may appear that it does in a simple test, it actually doesn’t – it just takes the next available id.  If you detach database id 5 and then re-attach it, it will retake id 5, and this will mostly work – unless other databases have been removed from the server at some point in the past and there are ‘holes’ in the list of available id’s.  So…”How do you force a database to have a specific database id?”

First of all, you can get the current database_ID of all your databases by running:

select name, database_id from sys.databases

What you will likely notice is that the numbers go from 1(master) to roughly the number of databases on your instance. SQL Server assigns the next available database id. What that means is that if you have a clean install database id’s 1-4 will be taken by the system databases and the first database id you create will be 5 and the second 6 and so on. The exception is when you delete\detach a database – this creates a hole which is the next assigned number.  So if you have created databases with ID’s 5-10 and detach the database with id 7, the next database you attach or create will take database_id 7.

The upshot of all this is that if you ever want to assign a specific database id to a new database you need to do the following:

  1. If all your database id’s are sequential and the next available number is the id you want – just create the database.
  2.  If all your database_id’s are sequential and the database_id is already assigned, detach the database that is currently using it, create the database you want to take that ID and then re-attach the database you dropped.
  3. If you want to assign a number that is greater than the currently available database_ids, simply create databases until the next available id is the number you are after, then create the database you want to take that id, then remove all the filler databases you created to get there.

I’m still unsure what the usecase for this is – but now you know how to do it.

SQL Saturday Auckland

Leave a reply

SQL Saturday Auckland is on October 15th and is being hosted in the Microsoft Office in the Auckland Viaduct.  I’m fortunate enough to have been selected as a speaker which will mean dusting off my HA and DR talk which I last presented back at the start of the year in SQL Saturday Sydney, and adding some new content to it.

It’s a talk I enjoy giving, because although it is very much an entry level talk it gives people an introduction to technologies they may not be familiar with or use in their own SQL environment, and usually generates some good questions and discussions about why you would choose one technology rather than another.

So if you happen to be in Auckland next weekend come along and get some great free SQL training:  http://www.sqlsaturday.com/571/eventhome.aspx

 

SQL Sentry Plan Explorer

Leave a reply

I’m all for spreading good news as far and wide as possible.  So I wanted to make sure everyone knows that the good folks at SQL Sentry have made the pro edition of SQL Sentry plan Explorer FREE!  See this blog post for details.

Plan Explorer is a great tool, and we were lucky enough to have Sofia Ng take us through some uses for it at one of our Nelson SQL Server User Group Lightning Talks last year.  Now with all the pro features in there there’s even more reason to check it out if you haven’t already.

 

Alan Featherston(TradeMe) On Online OLTP

Leave a reply

Register for Nelson SQL Server User Group here:

http://www.meetup.com/Nelson-SQL-Server-User-Group/events/233048026/

Summary

SQL Server In-Memory OLTP can give you the opportunity to radically speed your applications. The presentation offers a quick intro to how it works, what’s new and some guidelines about good use case scenarios.

Abstract

SQL Server Hekaton, aka In-Memory OLTP, was released in SQL Server 2014, two years later SQL Server 2016 introduces some changes that might finally help Microsoft reach wider adoption. Every DBA needs to understands how this technology works (not only Microsofts take but the state of the art of in-memory RDBMS) and what to expect when a new project might benefit from it.

Goals:

  -A basic under the hood understanding of In-Memory OLTP

  -How to monitor and possibly troubleshoot potential issues

  -Understand usage patters

  -What’s new in 2016