Generate sqlserver table creation scripts from Powershell

NPG Ax143583; Edith Olivier by Lady Ottoline Morrell
I didn’t know you could do this – the ‘table’ object in the SQL provider has a ‘script’ method, so you can do the following:

$ServerInstance = "whatever"
$DatabaseName = "whatever"
foreach ($TableName in dir Sqlserver:\sql\$ServerInstance\databases\$DatabaseName\tables\) {
  $TableName.script()
}

It’s a rough equivalent to doing a right click >> ‘Script table as’ >> ‘Create’ in SSMS, except you can do it for some or all of the tables on a database.

I’m looking forward to playing with the ‘script’ method for other database objects.

Pic: Edith Olivier and the Palladian Bridge in the grounds of Wilton House, by Lady Ottoline Morrell [Public domain], via Wikimedia Commons

Posted in powershell

Sketchnotes from 24 Hours of Pass: 2014 launch edition

These are my sketchnotes from last week’s ’24 Hours of Pass’ on-line event. All the talks were useful and interesting, my notes perhaps are neither….but they do serve to jog my memory.

’24 Hours of Pass’ is an online event. 24 speakers talk about 24 sqlserver related topics over, surprisingly enough 24 hours. It’s very good and the next one, at the time of writing, is in September.

The ’24 Hours of Pass’ points of presence are:

Twitter: @24Hop
Website: 24 Hours of PASS

I’m going to put a bit of text around the works of art below, if only so that you can see where one starts and the other ends.

I’d like to emphasize that these are just my impressions. I may have misunderstood or written stuff down wrong or otherwise mucked up. The presenters slides should be available on the 24 Hours of PASS website – these will be much more authoritative….but will have fewer drawings of Terminators

SQL 2014: changes to the optimizer

First up was a great talk about changes to the optimizer in 2014 – specifically around the cardinality estimator. The way I understood it (which could be well wide of the mark – I can’t vouch for having recorded anything accurately….I may have been scanning or tweeting or wondering whether I could draw a penguin at a critical moment) was the the cardinality estimator has changed little or not at all since SQL 7.0.
SQL 2014 Changes to Query Plans

Next, a talk about some new features in SQL 2014. The main bits I took from this were:

  • buffer pool extension – I think I understand this to be a flash write cache
  • the use of Azure for either backups or AlwaysOn
  • the resource governor being extended to I/O

…and I can’t draw Arnie.
2014-features-743x1024

SQL 2014: Private and Hybrid Cloud Features

I had the vaguest idea of what Polybase was before the talk. My understanding now is that it allows Parallel Data Warehouse (and therefore the user) to talk to Hadoop seamlessly. I was particularly interested in the idea that you could have your Dimension tables in SQL and Fact table in Hadoop.
Polybase

SQL 2014: Migration to SQL Azure

The talk on migration to Azure was very interesting. I liked the idea of ‘the cloud continuum’. What I haven’t reflected in the Sketch are that there was a demo of the cloud GUI – well worth seeing.
Migration to sql azure

SQL 2014: Columnstore indexes

Columnstore indexes seem very cool. In principle data is stored on column-by-column (duh!) rather than row-by-row. This means that you can compress a gazillion times more effectively, and as long as people aren’t doing ‘select *’ from every table you won’t need to traverse as much data.

First talk was by Jimmy May:
columnstore - jimmy may

Second was by Sunil Agarwal:
Clustered columnstore

SQL 2014: Always On

I didn’t know much about Always On, so this filled in a lot of gaps…and there seem to have been a bunch of improvements in 2014
Always On 2014

SQL 2014: the Modern Data Warehouse

Finally, Dan Kogan’s talk about ‘the modern data warehouse’ – how ‘Big Data’ changes things, and Polybase bridges the gap bewteen the modern and the trad warehouse:
modern-data-warehouse-743x1024

Posted in sqlserver

T-sql to show SSRS permissions

Bristol_Boxkite_1911

I’m not great at T-sql, and I know that cursors are unfashionable in Microsoft world, but this gives me the output I want, as below:

Path Username GrantedPermissions
/Data Sources cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/Data Sources/Statszone BUILTIN\Administrators Content Manager
/Data Sources/Statszone cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/Data Sources/Statszone cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/zTest/By far the greatest team report cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/zTest/By far the greatest team report cfc\bbuck Browser
/zTest/By far the greatest team report cfc\rfaria Content Manager

drop table #ReportPermissions

create table #ReportPermissions
(Path varchar(100),
Username varchar(100),
GrantedPermissions varchar(100))

declare
UserWithPermissionsCursor cursor for
select -- ,roles.[RoleID]
rolename
-- ,users.[UserID]
,username
-- ,catalog.[PolicyID]
,path
FROM [ReportServerRep01].[dbo].[PolicyUserRole],
roles,
users,
catalog
where policyuserrole.RoleID = roles.roleid
and policyuserrole.UserID = users.userid
and policyuserrole.policyid = catalog.PolicyID
order by path, username, rolename

declare @rolename varchar(100)
declare @username varchar(100)
declare @path varchar(100)
declare @PermissionsString varchar(100)
declare @Savedusername varchar(100)
declare @Savedpath varchar(100)

open UserWithPermissionsCursor
FETCH UserWithPermissionsCursor INTO @rolename, @username, @path

WHILE 0 = @@fetch_status
BEGIN
if (@SavedUserName = @username AND @SavedPath = @path)

set @PermissionsString = @PermissionsString + ',' + @Rolename

else
begin
-- Output the line
insert into #ReportPermissions
(Path,
Username,
GrantedPermissions)
values
(@SavedPath,
@SavedUserName,
@PermissionsString)

-- Reinitialize variables
set @SavedPath = @path
set @SavedUsername = @username
set @PermissionsString = @rolename

end

FETCH UserWithPermissionsCursor INTO @rolename, @username, @path

END
close UserWithPermissionsCursor
deallocate UserWithPermissionsCursor

select * from #ReportPermissions

Pic: A Bristol Boxkite at Stonehenge. Glossing Wikimedia: It is a photograph created by the United Kingdom Government and taken prior to 1 June 1957 and HMSO has declared that the expiry of Crown Copyrights applies worldwide (see File:Bristol Boxkite 1911.jpg – Wikimedia Commons)

Posted in sqlserver

SSRS error: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater – simple fix

Memorials of Old Wiltshire - Raising the Leaning Stone at Stonehenge

Memorials of Old Wiltshire - Raising the Leaning Stone at StonehengeA quick post on a quick fix.

I installed Sql Server Reporting Services 2008 R2 on the report server, then installed Oracle client 11.2.0.4.

I copied across a valid tnsnames.ora file, and verified connectivity with both sqlplus and tnsping.

However, trying to connect to one of the Oracle databases from within SSRS I got:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

…helpfully highlighted in an angry red.

Fix is fairly straightforward – SSRS hadn’t picked up the change to %PATH% done by the Oracle install yet, so the fix was to re-start the SSRS service.

Pic: From an old out-of-copyright book called ‘Memorials of Old Wiltshire’

Posted in sqlserver

Powershell pattern matching to check if a string is hex

Hexadecimal-countingI’ve been playing with pattern matching in Powershell.

I was trying to use ‘-match‘ to ‘Check if the string is a hexadecimal number’ for the ‘Perl one-liners in Powershell’ page I’ve been working on.

I can’t pretend to entirely understand or explain pattern matching in Powershell, or in *nix, but this seems to work.

$X = "21e" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"

This says match the string against ‘any combination of the characters within the square brackets (i.e. the hex digits) to the same length as the original string’.

So, the square brackets contain the allowable characters.

The curly brackets give the number of characters.

I tried just doing:

$X = "21e" ; $X -match "[0123456789abcde]{$X.length}"

…but this didn’t seem to work.

Tests

I tried the following strings – they all seemed to come up with the right answer:

$ $X = "21e" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True
$ $X = "21edjhsd" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
False
$ $X = "21e34782348237847832748723" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True
$ $X = "21e34782348237847832748723f" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
False
$ $X = "21e34782348237847832748723acbdaaa" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True
$ $X = " " ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
False

….although I’m not sure whether this is right or wrong:

$ $X = "" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True

Picture: By mate2code (Own work) [Public domain], via Wikimedia Commons

Posted in powershell

Idiot’s guide to github

Screenshot from 2014-02-20 22:13:47

Screenshot from 2014-02-20 22:13:47I’ve been finding it difficult to get my head around github.

As is often the case in the end I found it easiest to do so through the command line.

I’d already created a repository which holds my firefox ‘New tab’ and home page

To amend it I did the following. I think.

Set up my githubcredentials:

git config --global user.name mattypenny
git config --global user.email mattypenny@gmail.com
git config --global credential.helper 'cache --timeout=36000'

Clone the repository locally

git clone https://github.com/mattypenny/start_page.git

This creates a folder with all the repository files in it locally

cd start_page

Then I made all my changes to the start page.

Then I did a local commit:

git commit -m 'New pics and added in all the picture credits'

This shows that git was pointing to the github repository as ‘origin’. This seems to have been retained from the clone command.

$ git remote -v
origin	https://github.com/mattypenny/start_page.git (fetch)
origin	https://github.com/mattypenny/start_page.git (push)

Then I pushed it all back to github

$ git push origin master
Username for 'https://github.com': mattypenny
Password for 'https://mattypenny@github.com': 
Counting objects: 17, done.
Delta compression using up to 2 threads.
Compressing objects: 100% (11/11), done.
Writing objects: 100% (11/11), 1.05 MiB | 172 KiB/s, done.
Total 11 (delta 1), reused 0 (delta 0)
To https://github.com/mattypenny/start_page.git
   9f6e3d5..40431e1  master -> master
Posted in Uncategorized

SSIS: Invalid access to memory location

Problem came up trying to connect to a remote Oracle source:

Started:  12:03:03
Error: 2013-10-09 12:03:03.84
   Code: 0xC0202009
   Source: <removed>
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x800703E6  Description: "Invalid access to memory location.".
End Error
Error: 2013-10-09 12:03:03.84
   Code: 0xC020801C
   Source: Copy Data
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "xxx" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2013-10-09 12:03:03.84
   Code: 0xC0047017
   Source: xxxx
   Description: component "xxx" (1) failed validation and returned error code 0xC020801C.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

Fix was to go into job step properties, select the ‘Execution options’ tab and click on ‘Use 32-bit runtime’.

I’m wondering whether we had an Oracle 32-bit install, or a 32-bit install that wasn’t entirely removed

Posted in sqlserver

get-parameter – get a list of PowerShell standard parameter names

Windows_PowerShell_iconThis is a work in progress – I’ll turn it into a function at some stage.

Anyway, it’s a common and a good practice to use Powershell’s standard verbs when naming your functions. You can retrieve these by typing

get-verb

at the command line.

I think it’s good to also use Powershell’s standard parameter names when you’re naming your parameters.

I couldn’t find an easy way of retrieving the list of parameteres but this approximates it:

$PARAMETERS = get-command | where-object {$_.CommandType -eq "Cmdlet" } | select Parameters
$PARAMETER_KEYS = foreach ($P in $PARAMETERS)
{
  foreach ($K in $($P.Parameters | select keys))
  {
    $K.Keys
  }
}
$PARAMETER_KEYS | sort-object -unique

The result, on Powershell 2.0 with sqlps loaded, is:

AbortOnError
AclObject
Action
Activity
Add
AdHocPolicyEvaluationMode
Adjust
After
Alias
AliasesToExport
All
AllMatches
AllowClobber
AllowRedirection
Amended
Any
Append
AppendPath
ApplicationArguments
ApplicationBase
ApplicationName
ArgumentList
Arguments
As
AsBaseObject
AsCustomObject
AsHashTable
AsJob
AsPlainText
AsSecureString
Assembly
AssemblyName
Associations
AsString
Attachments
Audit
Authentication
Author
Authority
AutoSize
Average
BackgroundColor
BaseDirectory
BasePropertiesOnly
Bcc
Before
Begin
BinaryPathName
BindingVariable
Body
BodyAsHtml
Breakpoint
BufferSize
CancelTimeout
CaseSensitive
Category
CategoryActivity
CategoryReason
CategoryResourceFile
CategoryTargetName
CategoryTargetType
Cc
Certificate
CertificateThumbprint
Character
ChildPath
Circular
Class
ClrVersion
Cmdlet
CmdletsToExport
CodeDomProvider
Column
Command
CommandLine
CommandName
CommandType
ComObject
CompanyName
CompilerParameters
Completed
Component
ComputerName
ConfigurationName
ConfigurationTypeName
Confirm
ConnectionTimeout
ConnectionUri
Container
Content
Context
Continuous
Copyright
Count
Counter
Credential
CssUri
Culture
CurrentOperation
Database
Date
Day
Days
Debug
Debugger
DedicatedAdministratorConnection
Definition
Delay
DelegateComputer
Delimiter
DeliveryNotificationOption
DependentServices
DependsOn
Depth
Descending
Description
Destination
Detailed
Dialect
DifferenceObject
DirectRead
DisableCommands
DisableNameChecking
DisableVariables
DisplayError
DisplayHint
DisplayName
DomainName
DotNetFrameworkVersion
Drive
EnableAllPrivileges
Encoding
EncryptConnection
End
EndTime
EntryType
Enumerate
ErrorAction
ErrorId
ErrorLevel
ErrorRecord
ErrorVariable
EventArguments
EventId
EventIdentifier
EventName
Examples
Exception
Exclude
ExcludeDifferent
ExcludeProperty
ExecutionPolicy
Expand
ExpandProperty
Expression
FileFormat
FileList
FileName
FilePath
FileVersionInfo
Filter
FilterHashtable
FilterScript
FilterXml
FilterXPath
First
Force
ForegroundColor
Format
FormatsToProcess
FormatTypeName
Forward
Fragment
From
Full
Function
Functionality
FunctionsToExport
Global
GroupBy
Guid
HashAlgorithm
Head
Header
HideComputerName
HideTableHeaders
HostName
Hour
Hours
Id
IdleTimeout
IgnoreProviderContext
IgnoreWarnings
IgnoreWhiteSpace
Impersonation
Include
IncludeChain
IncludeEqual
IncludeScriptBlock
Independent
Index
InitializationScript
InputFile
InputObject
InstanceId
IsAbsolute
IsValid
ItemType
Job
JobName
Keep
Key
Language
Last
LastStatus
Leaf
Line
List
ListAvailable
ListenerOption
ListLog
ListProvider
ListSet
LiteralName
LiteralPath
LoadUserProfile
Locale
Location
LogName
MaxBinaryLength
MaxCharLength
MaxEvents
Maximum
MaximumReceivedDataSizePerCommand
MaximumReceivedDataSizePerCommandMB
MaximumReceivedObjectSize
MaximumReceivedObjectSizeMB
MaximumRedirection
MaximumSize
MaxSamples
MaxSize
MemberDefinition
MemberType
Message
MessageData
MessageResourceFile
Milliseconds
Minimum
Minute
Minutes
Mode
Module
ModuleInfo
ModuleList
ModuleToProcess
ModuleVersion
Month
Name
Namespace
NestedModules
Newest
NewName
NewPassword
NoClobber
NoCompression
NoElement
NoEncryption
NoMachineProfile
NoNewline
NoNewWindow
NoQualifier
NoRecurse
NoServiceRestart
NotMatch
NoTypeInformation
Noun
Object
Off
Oldest
Online
OnType
OpenTimeout
OperationTimeout
Option
OptionSet
OUPath
OutBuffer
OutputAssembly
OutputModule
OutputSqlErrors
OutputType
OutputXml
OutVariable
OverflowAction
Paging
Parameter
ParameterResourceFile
Parent
ParentId
PassThru
Password
Path
PathType
Pattern
PercentComplete
Policy
Port
PostContent
PowerShellHostName
PowerShellHostVersion
PowerShellVersion
PreContent
Prefix
PrependPath
Priority
PrivateData
Process
ProcessorArchitecture
Prompt
Property
PropertyType
ProviderName
ProxyAccessType
ProxyAuthentication
ProxyCredential
PSDrive
PSHost
PSProvider
PutType
Qualifier
Query
QueryTimeout
Quiet
RawData
ReadCount
RecommendedAction
Recurse
RedirectStandardError
RedirectStandardInput
RedirectStandardOutput
ReferencedAssemblies
ReferenceObject
Registered
Relative
Remove
RemoveFileListener
RemoveListener
Repair
Replace
RequiredAssemblies
RequiredModules
RequiredServices
Resolve
ResourceURI
RestorePoint
RestorePointType
RetentionDays
ReturnResult
ReturnType
Role
RollbackPreference
Root
RunAs32
SampleInterval
Scope
Script
ScriptBlock
ScriptsToProcess
Second
Seconds
SecondsRemaining
SecondValue
SecureKey
SecureString
SecurityDescriptorSddl
SelectorSet
Sender
Separator
Server
ServerInstance
Session
SessionOption
SetSeed
SeverityLevel
Shallow
ShowError
ShowSecurityDescriptorUI
SimpleMatch
Skip
SkipCACheck
SkipCNCheck
SkipRevocationCheck
SmtpServer
Source
SourceId
SourceIdentifier
SPNPort
SqlName
Stack
StackName
Start
StartTime
StartupScript
StartupType
State
Static
Status
Step
Stream
Strict
String
StringData
Subject
SubscriptionId
Sum
Summary
SupportedCommand
SupportEvent
SuppressProviderContextWarning
SyncWindow
Syntax
TargetExpression
TargetObject
TargetObjects
TargetServerName
ThreadApartmentState
ThreadOptions
ThrottleLimit
Timeout
TimestampServer
TimeToLive
Title
To
TotalCount
Trace
TransactedScript
Type
TypeDefinition
TypeName
TypesToProcess
UFormat
UICulture
Unique
UnSecure
Uri
Urn
UseCulture
UseDefaultCredential
UseNewEnvironment
UserName
UseSsl
UseTransaction
UseUTF16
UsingNamespace
Value
ValueOnly
ValueSet
Variable
VariablesToExport
Verb
Verbose
Version
View
Visibility
Wait
WarningAction
WarningVariable
WhatIf
Width
WindowStyle
Word
WorkGroupName
WorkingDirectory
Wrap
Xml
XPath
Year
Posted in Uncategorized

Ubuntu netbook can’t see my nook

Ubuntu_cant_see_nookI had a problem yesterday getting my Ubuntu netbook (which is a Dell Inspiron 910 mini) to see my Nook.

After, mucking about with various unix commands, including:

mount
lsusb
dmesg | egrep -i connected
ls /dev/sd*

It turns out that Ubuntu couldn’t see the Nook, because the power was too low.

It *seems* that there was enough power to power the Nook on, but not enough for it to make itself visible to the netbook.

For future reference, the Nook shows in the above commands as follows:

mount

/dev/sdc on /media/matt/6663-3466 type vfat (rw,nosuid,nodev,uid=1000,gid=1000,shortname=mixed,dmask=0077,utf8=1,showexec,flush,uhelper=udisks2)
/dev/sdb on /media/matt/NOOK type vfat (rw,nosuid,nodev,uid=1000,gid=1000,shortname=mixed,dmask=0077,utf8=1,showexec,flush,uhelper=udisks2) 


lsusb

lsusb
Bus 001 Device 014: ID 2080:0003 Barnes & Noble NOOK Simple Touch

dmesg | egrep -i connected

nothing

ls /dev/sd*

diff with without
74,75d73
< sdb
< sdc
77,78d74
< sg1
< sg2
193d188
< vcs63
201d195
< vcsa63

Posted in Uncategorized

Access is denied. [0x80070005] When Changing Sql Server Agent Login

Trying to change the sql server agent to run as a non-admin user, I couldn’t get Sql Server Agent To work without giving it administrator privileges. I got:

---------------------------
WMI Provider Error
---------------------------
Access is denied. [0x80070005]
---------------------------
OK   
---------------------------

You have to grant two privileges – both the Agent User and the MSSQL user.

  • SQLServerSQLAgentUser$bigserver$inst01
  • SQLServerMSSQLUser$bigserver$inst01
Posted in Uncategorized