Clearing SQL Server cache for performance testing

You can restart windows or the windows service, but the more elegant way is to use DBCC DROPCLEANBUFFERS.

To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

USE MyDatabaseName;
GO

CHECKPOINT;
GO

DBCC DROPCLEANBUFFERS;
GO

Import CSV Data into Sql Server Table

Getting a CSV file into a SQL Server table?

Found an even easier way to do it...

CREATE TABLE TelephoneNumbers
(TelephoneNumber NVarChar(15))
GO

BULK INSERT TelephoneNumbersFROM 'c:\TelephoneNumbers.csv'
WITH
(
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = '\n'
)
GO

The code is self explanatory; I was surprised how easy it was!'

T-SQL Simple Search & Replace on Table Content

This was thrown together in an attempt to save some time having to edit a hefty amount of text content. Simple amend the below script with your table, field, text you want replaced, what you want to replace it with and hey ho - you've hacked your content!

DECLARE @TABLE  VARCHAR(30) 
DECLARE @FIELD VARCHAR(30) 
DECLARE @WHERE VARCHAR(100) 
DECLARE @FIND VARCHAR(100) 
DECLARE @REPLACEWITH  VARCHAR(100) 
DECLARE @query VARCHAR(8000) 

SET @TABLE = 'MyTableName' 
SET @FIELD= 'MyField'
SET @FIND =  'text i want replacing' 
SET @REPLACEWITH = 'new text' 

BEGIN     
SET @query  = 
'UPDATE ' +  @TABLE +      
' SET ' +  @FIELD + 
'= REPLACE(CONVERT(varchar(8000),'     + 
@FIELD + '),''' +  @FIND + ''',''' + 
@REPLACEWITH  +''')'   

EXECUTE (@query) END 
GO

User is not associated with a trusted SQL Server connection

Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.

This is a common error when setting up SQL Server instances from scratch and takes just 30 secs to fix.

Basically the SQL server instance you are conecting to has been configured to operate in Windows Authentication Mode and doesn't allow the use of SQL accounts. So change the Authentication Mode of the SQL server from Windows Authentication Mode to Mixed Mode (Windows Authentication and SQL Server Authentication). To do this right click your SQL Server instance in Enterprise Manager/Management Studio, select security and you should see the option in there. Hope you didn't waste as much time as I did on this :)

ASP.Net SQL Server Registration Tool & Session State

The ASP.NET SQL Server Registration tool is used to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, or to add or remove options from an existing database. The tool can be found @ Windows\Microsoft.NET\Framework\VERSION\aspnet_regsql.exe.

Run the executable without arguments to load the wizard, then use the wizard to create or modify database elements for the membership, role management, profile, web parts personalization, and health monitoring features.

SQL Session State (ASPState)

ASP.Net Session State supports several different storage options for session data. To make use of SQL Server Session State you'll need to create the ASPState database on your SQL server instance; but this database isn't created by the aspnet_regsql wizard. Instead you'll need to run the executable with additional arguments. Use MSDN for a full list of arguments available, below is the minimum you'll need to get it installed.

aspnet_regsql.exe -ssadd -S {SERVER} -U {USER} -P {PASSWORD}

The -ssadd command-line option == add the session state database