Archive for August, 2010

Clear down transaction logs

Posted by shawson in SQL Server on August 31, 2010

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

Posted by shawson in SQL Server on August 26, 2010

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

Posted by shawson in SQL Server on August 24, 2010

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

Posted by shawson in .net on August 18, 2010

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

Posted by shawson in .net, php on August 18, 2010

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

No Comments

ASP.net File Uploads with NeatUpload

Posted by shawson in .net, C#.net, IIS on August 17, 2010

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