DECLARE @Table TABLE(
INSERT INTO @Table EXEC sp_who2
where LOGIN like ‘%xxx%’
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.
- stored Procedures
- files – data, log and dump
- Stored Procedure Conventions
- Change audit and description
- Use of rowcount and @@error
As a simple example
Which is easier to read
create procedure usp_SelClients @cl int -1
select Client_name, HouseNo, Housename, Street, Town, Postcode from clients join addresses on clients.addressid = addresses.addressid where Clientid=@cl
Create Procedue usp_SelCleints @ClientId int -1
clients.addressid = addresses.addressid
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
- # 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!