Archive for the ‘Uncategorized’ Category

It’s been a long time since I’ve posted on here, mainly because I no longer work primarily as a DBA but more as a SQL Developer, I was also looking after thousands of instances so came across a lot of issues. However, this one had me stuck for a few days. We have a developer who wanted to run ‘R’ and a 2016 instance which should have let him, but no dice.

The Problem

When running the following ‘R’ test script:

EXEC sp_execute_external_script
@language =N’R’,
@script=N’OutputDataSet<-InputDataSet',
@input_data_1 =N’SELECT 1 AS hello'<
WITH RESULT SETS (([hello world] int not null));
GO

I got the following error:
Msg 39021, Level 16, State 1, Line 1 Unable to launch runtime for ‘R’ script.
Please check the configuration of the ‘R’ runtime. Msg 39019, Level 16, State 1, Line 1 An external script error occurred: Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

The Solution

I was running on SQL Server 2016 (13.0.4001.0) with no previous ‘R’ installations or CTP instalattions.
The solution was to uninstall and install the dll.
In my case the path to it was C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt

So, first I opened up dos with admin rights and ran:
“C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt” /uninstall

After that I ran
“C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt” /install

Note: Each time you run the uninstall or install script it will stop and start your SQL instance

This was for a default instance. I think, if you are using a named instance you need to add: /instance:InstanceName after the /install flag

 

Advertisements

No SQL

Posted: July 31, 2013 in Uncategorized

And when I say No SQL I mean No SQL. This isn’t a SQL post at all. I haven’t been keeping this blog up-to-date as I’ve been writing Crime Novels instead.

My first book, Salazar, is out now.

Reviews:

‘Combines the appeal of a colourful and fresh lead character with an intriguing Parisian setting at a fascinating moment in time. A promising debut.’
Chris Ewan, author of Safe House and Dead Line

‘An intelligent, moody novel, brimming over with well-drawn characters and unexpected insights. But more than anything else, Lynch gives us a Paris that feels real; his knowledge of the city in that particular time is staggeringly evocative, and makes Salazar an exceptional novel.’
Heath Lowrance, author of City of Heretics and The Bastard Hand

‘Superbly evoking the atmosphere of 1930s Paris, Salazar is a taut and engaging mystery novel from a great new writer. Lynch has created a classic anti-hero. Still carrying the scars of war, he’s the kind of man driven to do the right thing, regardless of the cost to himself in this engrossing debut novel.’
Nick Quantrill, author of Broken Dreams and The Late Greats

‘An absorbing twist on the hardboiled detective. Lynch has invented a whole new landscape: Paris-noir in the dissolute 1930s.’
Gary Corby, author of The Pericles Commission and The Ionia Sanction

Book description:

The debut novel from Seth Lynch…

Paris. 1930. An English detective haunted by his experiences of the Great War, Salazar whiles away the days playing chess and taking on as little work as possible. When the alluring Marie Poncelet hires him to find a missing man, Gustave Marty, it’s a case he’ll soon wish he’d refused.

Because finding a missing man isn’t anything like finding a man who doesn’t want to be found. And Gustave Marty has covered his tracks with a smokescreen that will push Salazar beyond the limits of physical endurance and to the edge of insanity.

As he’s drawn ever deeper into the shadowy underbelly of the City of Light, Salazar’s closed, structured world is blown apart by the arrival of a friend from his pre-war youth, the beautiful Megan Fitzwilliam, whose tenderness and love of life is a stark contrast to the brutal violence that lies within him.

When that violence threatens to engulf them both, Salazar must seek redemption or lose that which has finally made his life worth living.


And if you’ve read this far why not hop over to Amazon and read th esmaple chapter?

Get Salazar on Amazon UK or on Amazon USA

I don’t like the system generated maintenance plans so use this instead.

I normally go from one step to the next with total disregard as to it’s outcome. This is because I check job histories and logs each morning so if update stats or integrity checks failed I will know about it and fix it then. I also want my backups to run regardless – they stay on disk for 3 days but are also copied to tape so if a problem with integrity creeps in and isn’t noticed for > 3 days (I am on holiday) then if I can’t fix it I can use tape backups to repair almost as easily as disk backups. If this doesn’t suit you you could add an extra step which takes a backup but does not delete old backups – you just need the space for this if you can’t check for a while.

The most unsatisfying thing with this is detecting read only databases – it is easy in 2005 but haven’t found a satisfactory easy method for 2000.

I have a separate job for system dbs where NOT IN is replaced with IN. This runs at a different time of the evening

 

Step 1: Update Usage and Checkpoint

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

 select [name]

 From master..sysdatabases

 WHERE [Name] not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

 Order by [name];

OPEN dbCursor

 FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = ‘dbcc updateusage ([‘ + @dbName + ‘]) WITH NO_INFOMSGS;’;

Exec (@sql);

Set @sql = ‘Use ‘ + QuoteName(@dbName) + ‘ Checkpoint;’;

Exec (@sql);

Use Master;

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 2: Integrity Check

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name]

From master..sysdatabases

WHERE [Name] not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = ‘dbcc CHECKDB ([‘ + @dbName + ‘]) WITH NO_INFOMSGS;’;

Exec (@sql);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 3: Update Stats

USE Master;

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

/* 1048 represents REadonly databases but it is no the only code that represents Readonly

you will need to check your dbs*/

select [name]

From master..sysdatabases

WHERE Name not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

AND status <> 1048

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec (‘Use [‘ + @dbName + ‘] EXEC sp_MSforeachtable ”UPDATE Statistics ? With Sample 50 Percent”’);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 4; Backup databases

USE Master;

DECLARE @rootDir as Varchar(255);

SET @rootDir = ‘E:\Backup’; /*Enter path to user backups here*/

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name] From master..sysdatabases

WHERE Name not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @BakCommand as varchar(1024)

SET @BakCommand = ‘master.dbo.xp_sqlmaint N”-S . -D ‘ + @dbName + ‘ -BkUpDB “‘+ @rootDir + ‘” -BkUpMedia DISK -CrBkSubDir -DelBkUps 3DAYS -BkExt “BAK””’;

–Print @BakCommand

EXEC (@BakCommand);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

http://lqqsql.blogspot.com/2009/09/sql-server-2000-maintenance-plan-non.html

Note on the start

Posted: September 15, 2009 in Uncategorized

This blog is mainly for me so I don’t loose the scripts and such that I use and carry on a usb key mainly.
If any script was written by a 3rd party I will try to reference them but some of these I have had for years and never took a note of where I got them.
Most are straight forward anyway and just evolved over time.
Some are straight forward things they I keep forgetting like how to reattach a database – I just find that when I am doing this I like to have a copy of the script in front of me.
If you find it helpful then good – if not well boo hoo:-)

I am a DBA looking after Production and Development Servers.
I was a developer years ago. Was a Developer/DBA for 2 years and then, since 2006 just a DBA.

http://lqqsql.blogspot.com/2009/09/note-on-start.html