A bit of syntax that I can never remember off the top of my head! SQL 2005 ‘Transaction Log is Full on database xxx’

DBCC SHRINKFILE('dbname_log', 1)
BACKUP LOG dbname WITH TRUNCATE_ONLY DBCC SHRINKFILE('dbname_log', 1)

Without linking the servers you can do one off queries against another server using the OPENDATASOURCE or OPENROWSET functions. You will need to first enable “Ad Hoc Distributed Queries” as this is disabled by default- this can be achieved using sp_configure (if you’re logged in as sa)

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Once active you can use the function like this;

SELECT
    *
FROM
    OPENDATASOURCE('SQLOLEDB', 'Data Source=<servername>;User ID=<user>;Password=<password>').<dbname>.<dbo>.<tablename>

There is also an OPENROWSET function;

SELECT
    source.*
FROM
    OPENROWSET('SQLOLEDB', 'Data Source=<servername>;User ID=<user>;Password=<password>', 'SELECT * FROM <dbname>.<dbo>.<tablename>') as source

Thanks to Richard who dug this little nugget up from the interwebs.

So I did a post for SQL 2000 aaages ago showing how to do this- it’s a lot easier in SQL 2005 but i always forget the syntax as i rarly have to use it.

Open a new query editor on your selected database server then run this;

use <database_name>;
/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Then right click your database user (under security for the given database) and tick the new db_executor role. You’re done!

I always seem to remember the access syntax for this rather than the sql server version–

update
    hachette_digitalideas_import
set
    harrietId = u.Id
from
    hachette_digitalideas_import h
    left outer join user_group u
        ON (h.email = u.email or h.[full name] = u.name) AND u.btype = 'u'

As opposed to the following in access;

update
    hachette_digitalideas_import
    left outer join user_group u
        ON (h.email = u.email or h.[full name] = u.name) AND u.btype = 'u'
set
    harrietId = u.Id

In Summary: I basically wanted to take the data in a table, then linked data from another table concatenated into a comma separated string, and attached as a varchar column on the end of the first table!

I have the mis-fortune of working on a terrible intranet system based on technology built by a company I won’t actually name- so for the purpose of this post, lets say they are called Source. It’s basically a big buggy CMS powered by some aging and creaky classic asp files and lashing of very frail javascript (this isn’t a legacy system- it was installed in the company around a year ago!). It allows you to build forms and data grids, but is quite restrictive with how you attach these to the database- you cannot for example use a stored procedure as a data source- it has to be a table or a view.

We have an internal app to allow staff to order with a table structure like so;

OrderHeader -> OrderDetail -> Publisher -> Warehouse

On the order header view page we need to know which warehouses are involved, which means a query going right down the chain from product on the order line, to the publisher, down to which warehouse that publisher is based at- because we can only tie to a single table or view I had a problem unless i wanted loads of repeated rows- after some googling I found CROSS to be what I was after;

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Based on the above post I was able to build SQL like so;

SELECT
	OrderHeader.id,
	OrderHeader.user_id,
	OrderHeader.OrderDate,
	Users.name,
	WareHouses = LEFT(o.list, LEN(o.list)-1)
FROM
	OrderHeader INNER JOIN
	User ON OrderHeader.UserId = User.UserId LEFT OUTER JOIN
CROSS APPLY
(
	SELECT distinct
		CONVERT(VARCHAR(50), WareHouse.Name) + ',' AS [text()]
	FROM
		WareHouse
		INNER JOIN Publishers ON WareHouse.id = Publishers.WarehouseId
		INNER JOIN OrderDetailLine ON Publishers.PublisherID = OrderDetailLine.PublisherID
	WHERE
		OrderDetailLine.OrderHeaderId = OrderHeader.OrderHeaderId
	ORDER BY
		CONVERT(VARCHAR(50), WareHouse.Name) + ','
	FOR XML PATH('')
) o (list)

This bought me back each row, with a comma separated list of the warehouse names in the end column which i could then filter on!

In SQL Server 2005 onwards you can have conditional Order By clauses in your stored proc’s- for example

SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO

CASE (Transact-SQL).