Nothing special just want to put a culmination of information available out there into one useful code-snippet


Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Dim lo As ListObject
Dim lc As ListColumn
Dim lr As ListRow
Dim frm As New frmListRanges
Dim txt As String

For Each ws In wb.Worksheets
For Each lo In ws.ListObjects
txt = ""
For Each lr In lo.ListRows
txt = "{ "
For Each lc In lo.ListColumns
txt = txt & " " & CStr(lo.DataBodyRange.Cells(lr.Index, lc.Index).Value) & ","
Next lc
Trim (txt)
If StrComp(Right(txt, 0), ",", vbTextCompare) Then
txt = Left(txt, Len(txt) - 1)
End If
txt = txt & " }"
frm.AddNamedRange ("WS: " & ws.Name & " - LO: " & lo.Name & " " & txt)
Next lr
Next lo
Next ws

frm.Show


While doing some development found a need to find non-Maintenance Plan Jobs for doing some reporting. After couple minutes searching Google and some other sites, there is a huge lack of information or queries that effectively show how to obtain or tie system tables together to derive the literal SQL Agent Jobs.

That being said, here is a little script i created that will query the system databases and produce a list of SQL Agent jobs.

Code Snippet:


select *
from msdb.dbo.sysjobs as job
where job.job_id not in (select job_id from msdb.dbo.sysmaintplan_subplans)


LinkBack: Jason Yousef Blog

Found this nice little code snippet for researching the SQL Agent running account. Left a linkback to the original site, but it was seriously laggy due to some backend stuff.

DECLARE @SrvAccount varchar(100)
set @SrvAccount =''

EXECUTE master.dbo.xp_instance_regread
  N'HKEY_LOCAL_MACHINE',
  N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT',
  N'ObjectName',
  @SrvAccount OUTPUT,
  N'no_output'

SELECT 
  @@SERVERNAME as [MachineName]
  , @SrvAccount as SQLAgent_ServiceAccount

Well today, I started the fun filled adventure of managing data from/to a LinkedServer.

During this learning curve, I found plenty of information about how to query from and instert into a Linked server but most of them utilized the OpenQuery TSQL Statement to operate.

One failure of the concepts is to use the inline statement to access the Linked Server.

In my situation, i was linking to a MySQL database offsite. Below you will find the MSSQL statement patterns to used when utilizing query statements against the Linked Server.

Select:

Select {cols}
From {LinkedServer}...{table}
[Conditions]


Insert:

Insert Into {LinkedServer}...{table} [cols,...]
Values ([cols,...])


There are more commands you can use but the point is that the developer/dba is utilizing only the 'server' and 'table' portions of the source information. This being, as described by Microsoft, as '{server}.{database}.{schema}.{table}' is simply '{linkedserver}...{table}'. At least for MySQL, they do not broadcast schema information through general access this is mostly handled by the login credentials instead, as i understand the framework.

In summary, instead of building god-aweful string concatenations with double/triple/etc single quotes to try and get the Execute style expression to execute against the remote server, just use the dot system already in place. Will allow you to operate on the database/tables in the same way as you maybe/are comfortable with.