Archive for August, 2010

Clear down transaction logs

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)

No Comments

Cross server queries in SQL Server

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.

No Comments

Grant Execute Permissions for all sprocs on SQL 2005

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!

2 Comments

Running multiple ASP.net membership sites under the same domain

We have a bunch of seperate microsites hosting book extras content all running in sub domains for a publishers website. Each site is secured is independantly secured with .net membership. A colleague of mine (Richard!) noticed after putting up the second site, that logging into one, also gave access to the other.

The fix was simple; Because cookies are domain-wide, the first auth cookie was being set, and then picked up by all the other sites on that domain. The fix, as detailed in the Microsoft Patterns and Practices Forms Authentication doc, is to just specify a distinct cookie name per application.

Use unique name and path attribute values on the
element as follows.

<forms name="YourAppName"
path="/FormsAuth" ... />

No Comments

Dummy Image Generator

’nuff said! http://dummyimage.com/

No Comments

ASP.net File Uploads with NeatUpload

File Uploads are a fickle thing, and have been.. well.. always. ASP.net has it’s own default max file size, IIS is also put under strain while processing large files and trapping excpetions when file’s are too large, or providing progress bars during the process is a fiddly process.

I’ve recently started using NeatUpload which is an HttpModule which takes care of uploads, streaming the data straight to storage on the file system, or sql- taking a load off of IIS and also offering progress bars!

It’s a fairly old project, which has only recently gone up on codeplex- check it out!

,

2 Comments