Archive for 'SQL' Category
Resolution to QUOTED_IDENTIFIER Error when using SqlCacheDependancy’s
At work we are building a medium sized ASP.NET web application. We are using ASP.NET forms authentication for the membership. I then decided to implement a custom role provider (implementing ProviderBase) based on our [Employee] application table. Implementing this was really easy! Along the way I realized that out database was going to get hammered [...]
SQLTRACE_BUFFER_FLUSH, Performance Metrics on SQL Server 2005
Tuning your SQL 2005 Database with built in metrics.
Easy way to kill MS SQL server transaction logs
I manage many SQL 2000 databases. And many times while performing maintenance I have run into the ‘transaction log full’ error. like this: Error: 9002, Severity: 17, State: 2 The log file for database ‘%.*ls’ is full. So like many people I resort to using the commands BACKUP LOG databasename WITH TRUNCATE_ONLY DBCC SHRINKFILE The [...]
Utilizing SQL 2005 Linked Server to connect to external systems via OLE DB
You can utilize MS SQL Server 2005′s Linked Servers ability to connect to any OLE DB provider. Here we are connecting to an iSeries (AS/400) DB2 using the IBMDA400 ole db provider Here is the command to add a linked server: EXEC master.dbo.sp_addlinkedserver @server = N’MYSERVER’, @srvproduct=N’IBMDA400′, @provider=N’IBMDA400′, @datasrc=N’MYSERVER’, @provstr=N’Transport Product=Client Access; Force Translate=0; DATA [...]
Creating a Period Date Range Table, with SQL!
You need a way to lump dated records into periodic chunks, like say for a timesheet system. Say your company’s payroll periods are bi-weekly (that’s every other week). So you need a way to distinguish the dated payroll data into two week timesheets. Sure you could spend three days figuring out a fancy algorithm, only [...]
HOWTO Get the last day of the month with SQL
Have you ever needed to find out what the last day of the month was with SQL. Lets say you needed to round up a date to the last day of the month… I found this necessary when writing a HR Benefits extract… They needed the benefits end date to be the last day of [...]