[code language=”sql”]
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT *
FROM @Table
where LOGIN like ‘%xxx%’
[/code]

The MOST important thing about standards is to have some, keep them and enforce them. Even if they are not the “best” standards in the world at least everything is consistent.

Look for

Naming conventions

• Tables
• stored Procedures
• triggers
• files – data, log and dump
• Stored Procedure Conventions
• Change audit and description
• Parameters
• Variables
• Use of rowcount and @@error

As a simple example
Which is easier to read

create procedure usp_SelClients @cl int -1
as
 select Client_name, HouseNo, Housename, Street, Town, Postcode from clients join addresses on clients.addressid = addresses.addressid where Clientid=@cl

or
Create Procedue usp_SelCleints @ClientId int -1
as
       select
              Client_name,
              House_No,
              House_name,
              Street,
              Town,
              Postcode
       from
              clients
       join
              addresses
       on
              clients.addressid = addresses.addressid
       where
              Client_id=@ClientId

And that’s a small stored procedure .. what happens when you get a complicated select statement with multiple joins and conditions!

There are advantages and disadvantages of using either options, however in my opinion the advantages of permanent work tables outweigh the advantages of # tables for ETL but # tables win for TP systems

• permanent work tables are available for debugging
• permanent tables can be conditionally created – whereas temporary tables can’t

however,

• # tables are automatically cleaned up whereas you have to manually either recreate or truncate work tables
• # tables always live in tempdb (although you can also create permanent work tables there as well)
• # tables are unique to the session so never any risk of contention

As an alternative to creating a view over multiple tables, there is also the option of using the queryout parameter in BCP to select the data for extracting (same example as used in the view usage)

bcp “select cust.CustomerId, cust.CustomerForename, cust.CustomerSurname, cust.Telephone, addr.HouseNo, addr.Street, addr.Town, addr.county, Addr.postcode From tblCustomer cust Join tblAddress addr ON Cust.AddressId = addr.AddressId” queryout cust.dat -S(local) -T -c

Obviously it’s a lot longer than using a view – but sometimes you don’t have permissions to create views so it’s a usefull alternative

The easiest way of doing this is to create a view over the two tables and then bcp the content of the view out in your required format.

e.g. if you have customer details in a customer table and an address table and you want to extract the contents of the combined table in a tab delimited file

create view vw_Customer_Address as select cust.CustomerId, cust.CustomerForename, cust.CustomerSurname, cust.Telephone,  addr.HouseNo, addr.Street, addr.Town, addr.county, Addr.postcode
From tblCustomer cust
Join tblAddress addr
ON Cust.AddressId = addr.AddressId

 Then just bcp this table out

BCP mydb.dbo.vw_Customer_address out cust.dat -S(local) -T -c

Two ways to log jobs – both equally effective

You can either pipe the output if you are running batch (.bat) files or

In the jobs steps – advanced use the output file option.
If you have multiple job steps
 First step set the overwrite option
 Subsequent steps set the append option

Database dumps / log dumps
Make the database dump overwrite and then for log or differential dumps append so that you have a full job history in a single file

Don’t forget to include the logs directory in the server backup schedule!

One word of warning…Permissions – the account that SQLServerAgent is running under will need permission to create files in the log directory.
All jobs for a server should be placed in the same log directory – and don’t forget to occasionally check the contents of the log files – since that’s how you will find out about problems!