3,196 reputation
1614
bio website lobsterpot.com.au
location Adelaide, Australia
age 39
visits member for 2 years, 6 months
seen 12 hours ago

Owner / Principal at LobsterPot Solutions

Adelaide (Australia) based SQL MVP

On twitter @rob_farley

Blog at http://sqlblog.com/blogs/rob_farley
(Old blog at http://msmvps.com/blogs/robfarley)


Apr
16
answered MySQL query using left join on two inline views not returning rows without values in second view
Apr
12
revised one to zero or one relation strategy
added 400 characters in body
Apr
12
answered one to zero or one relation strategy
Apr
6
answered Insert / Edit Rows by Selecting FK row
Mar
30
answered How to insert default values to a table with not null columns?
Mar
29
comment Index Tuning on Joined Query
Yes. Otherwise, your index is not enough to solve the query, and lookups would be needed. If it thinks too many lookups need to be done, it could well figure the index simply isn't good enough and divert to using the Clustered Index.
Mar
28
answered Index Tuning on Joined Query
Mar
20
comment Query databases on different servers
That should work just fine.
Mar
19
answered Query databases on different servers
Mar
17
comment Generate Agent Job Report
Try msdb.dbo.sysjobhistory and go from there
Mar
15
comment SQL Job step run as different sql account
A SQL job step already has a connection to the SQL instance. So you need to change the existing security context, rather than making a new connection with a password. If you want to create a new connection, you could use a sqlcmd command, or PowerShell script, which could then use a SQL login and password as parameters within the script. But you should consider what you need to do, and whether a stored proc with its own context will suffice.
Mar
15
comment SQL Job step run as different sql account
A stored procedure that has been created using the EXECUTE AS option will handle the privilege escalation, and not allow someone to change part way through. If there's no stored proc available, then impersonating a different login will do the trick, like with EXECUTE AS at the start of the script.
Mar
14
answered SQL Job step run as different sql account
Mar
8
comment SQL Server 2012 Not matching Join Query
I took it to mean inexact matches, rather than complete non-matches. He describes having VICKS in one and ACTION VICKS 500 in the other.
Mar
7
answered SQL Server 2012 Not matching Join Query
Mar
1
comment GO After every T-SQL statement
And if you want to be really evil, change the SSMS setting on a colleague's machine to SELECT.
Mar
1
answered Will creating an index improve performance on a full scan
Feb
27
answered Spatial index not used
Feb
16
comment Get results ordered by a specific column in a looping way
As I wrote in a comment on the question, MySQL doesn't support ROW_NUMBER(). This would be very hard to do using a single operation in MySQL. Your best bet would be to populate a temporary table with the rows, updating them as you go to create a rownum, before querying them back. It would be badly inefficient, and you may prefer to do it in your application, by looping through the categories and fetching rows as you need.
Feb
15
answered Get results ordered by a specific column in a looping way