Wednesday 12 July 2017

Solution to SQL Agent Job "Error retrieving WMI variable WMI(TextData)"

I've been trying to implement SQL Agent Alerts on disk growth for a day now.

I've followed a few different guides on WMI alerting, but every time the alert fires, the SQL Job fails with the below error, and trying to play with different methods to ESCAPE the WMI tokens was unfruitful.

Unable to start execution of step 1 (reason: Error retrieving WMI variable WMI(variable name here): 0x80041002).  The step failed.

SQL Junkie Share's blog had the answer. I needed to go to my SQL Agent properties, and under Alert System tick "Replace tokens for all job responses to alerts". Thanks Akhil!

Monday 20 February 2017

Adding an identity column or column with a default constraint to a table that already has data

Today I had a coworker ask me to review code that had a select statement moving data out of a table, truncating the table, adding an new column with identity and a default constraint, then re-inserting the data into the table. His explanation was that he couldn't add an identity column or a column with a default constraint to a table that contained data.

I displayed to him that moving data out of the table isn't needed to add either a default constraint or identity.

Here's an example of how to do that:

Create a table containing existing data:

create table Customer (
Firstname nvarchar(200)
, Surname nvarchar(200)
)

insert into customer(
firstname
, surname
)
select 'Bill', 'Palmer'
union all select 'Sarah', 'Moulton'
union all select 'Wes', 'Davis'

select firstname
, surname
from customer

There's no problem with adding an identity column to an existing table, except perhaps the time it takes to run this statement - keep that in mind if you're dealing with a very large table.

alter table customer add id int identity(1,1)

select id
, firstname
, surname
from customer

That query will succeed, and return the results of a select statement that includes the new ID column, with values!

Adding a column with a default constraint to an existing table requires a little trick. For this example, let's assume we want to add a bit column to customer, indicating whether the customer is a VIP. 

If you do the usual command to add a new column with a default constraint, as below, you may be surprised.

alter table customer add isVIP bit default(0)

select id
, firstname , surname , isVIP
from customer


Adding the default constraint will succeed, but the returned result-set will have null values in the "isVIP" column for the existing rows. To populate existing rows with the default value, run this statement instead:

alter table customer add isVP bit default(0) not null

select id
, firstname
, surname
, isVP
from customer

The existing rows with be populated your default value, and return that value in your result-set. 

If you want this column to allow NULL values, run the below statement to set the column to nullable, keeping the recently created default constraint.

alter table customer alter column isVP bit null

Monday 13 February 2017

Identifying unencrypted SQL connections

I'm working on implementing encrypted connections within my team to protect against packet sniffing, however a few developers seem behind on implementing encrypted connections. The below query helped me identify those developers for follow-up, and gave me the information they needed to identify which application was making the unencrypted connections.

select sessions.login_name
, sessions.program_name
, sessions.host_name
, sessions.login_time
, sessions.last_request_start_time
, sessions.status
, sessions.cpu_time
from sys.dm_exec_connections as connections
  inner join sys.dm_exec_sessions as sessions
  on connections.session_id = sessions.session_id
where connections.encrypt_option = 'FALSE'
order by sessions.login_name


Resolving "Cannot create an instance of OLE DB provider "OraOLE.DB.Oracle" after changing the SQL Server Agent account.

After an SQL Server Agent service account change, scheduled jobs that call an Oracle linked server began failing with the error

Executed as user: <username>. Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name>. [SQLSTATE 42000] (Error 7302).  The step failed.

The stored procedures called by those jobs executed as expected when I executed them from my account.

No ACCESS DENIED issues came up in Process Monitor while attempting to make the connection.

After some websurfing, this MSDN blog suggested modifying the MSDAINITIALIZE security settings to give the agent account all permissions on both Launch and Activation Permissions, Access Permissions and Configuration Permissions.

After doing that, the error changed to

Executed as user: <username>. The OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name> reported an error. Access denied. [SQLSTATE 42000] (Error 7399)  Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name>. [SQLSTATE 42000] (Error 7350).  The step failed.

Progress! Searching for that error message took me to this SQL Server Central post, where a user suggested ticking "Allow inprocesses" for the Oracle linked server provider.


Success! The job now completed as expected.