SQL Server Function to Get number of Days in month

by ebarcza 11/4/2011 10:12:00 AM
CREATE FUNCTION [dbo].[fn_GetDaysInMonth] ( @pDate    DATETIME )
RETURNS INT
AS
BEGIN
    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND
                                YEAR(@pDate) % 100 != 0) OR
                               (YEAR(@pDate) % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END
 
END

Tags:

SQL SERVER

Cannot be opened because it is version 655. This server supports version 612 and earlier

by ebarcza 10/12/2011 9:42:00 AM

In Visual Studio when I tried to access a SQL MDF on a project a colleague sent me, I received the error "Cannot be opened because it is version 655. This server supports version 612 and earlier". This was due to the fact that I had SQL Express 2005 installed and the MDF was created in SQL Express 2008. The fix for this is simply to install SQL 2008 and tell visual studio to use the new instance name. This can be set through the options menu. Menu>Tools>Options>Database Tools>Database Connections. Hope this helps somebody!

Tags:

SQL SERVER

Cache Using ObjectDataSource

by rbellantoni 8/26/2010 7:02:00 AM

I needed to bind a large dataset to a gridview, it also contained numerous calculations that slowed down the database call, so I wanted to implement caching for the gridview I was using. 

So I used the default Caching on the ObjectDataSource. Which works great and well except for when you want to programatically refresh the data (say a new filter to be added). Turns out you can actually do it.

 

First make sure that in Page Load you set the CacheKeyDependency if it isn't set (you can also manually set it on the aspx page as a property of the ObjectDataSource):

        if (!IsPostBack)
        {
            //Create CacheKeyDependency if it doesn'tnot exists
            if (Cache[ObjectDataSource1.CacheKeyDependency] == null)
            {
                Cache[ObjectDataSource1.CacheKeyDependency] = new object();
            }
        }

In your code whenever you want the Select() of your ObjectDataSource to fire or just rebind from the database make this call:

Cache[ObjectDataSource1.CacheKeyDependency] = new object(); 

This allows you to programatically renew/refresh your datasource at will while still retaining the Cache abilities for paging/sorting/grouping etc.

Tags:

.NET | .NET WCF | BlogEngine.NET | SQL SERVER

SQL Function return table with all days in month

by ebarcza 4/8/2010 11:22:00 AM

CREATE FUNCTION [dbo].[GetDaysInMonth] (@CurrentDate datetime)
/*
    Return table
*/
RETURNS @DaysInMonthTable table
([ValidDate] [DateTime]
)
as
begin


declare @BOM smallDateTime
declare @EOM smalldateTime
declare @tempDate smallDateTime
SET @BOM = dbo.StartOfMonth(@CurrentDate)
SET @EOM = DATEADD (mm , 1, dbo.StartOfMonth(@CurrentDate) ) - 1

WHILE @BOM <= @EOM 
BEGIN 
 INSERT INTO @DaysInMonthTable (ValidDate)  
 VALUES(@BOM) 
 SET @BOM = @BOM + 1   
END

return
end

 

usage =

Select * from dbo.GetDaysInMonth(GetDate())

Tags:

SQL SERVER

SQL Server decimal Scale and Precision

by ebarcza 3/25/2010 4:23:00 AM
I always forget this... so I'm bloggin it!

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 555.44 has a precision of 5 and a scale of 2.

Tags: ,

SQL SERVER

unable to start t-sql debugging Could not attach to SQL Server process

by ebarcza 2/25/2010 9:20:00 AM
When trying to debug a sproc using SQL Server debugging I received this error:
SQL Debugging: Unable to start T-SQL Debugging. Could not attach to SQL Server process

Executing the following solved the problem
sp_addsrvrolemember 'Domain\Name', 'sysadmin'.

If you're not part of a domain then the insert the machine name for domain; ServerName\username

Tags: , ,

SQL SERVER

Unable to load client print control issue

by rbellantoni 2/24/2010 4:43:00 AM

If you have ever run into this problem you understand how frustrating it can be, one of the fixes is to go and download this SP and install it to fix it.

http://www.microsoft.com/downloads/details.aspx?familyid=8a166cac-758d-45c8-b637-dd7726e61367&displaylang=en

 

Another one we tried was to go to SQL Server pull down the rsclientprint.cab files and install them on the local machines (located in: Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin) and delete the old RLL and dll files

 

Then we also tried to uninstall a few windows updates that installed killbits ActiveX Killbits (KB956391)

If anyone else has tried anything that worked let us know!

Tags:

.NET | ASP.NET AJAX | SQL SERVER

Cannot access temporary tables from within a function

by ebarcza 12/29/2009 7:40:00 AM

use a table variable instead. Works like a chahm

So instead of this:

 

create table #TempFees
(
 DealCompPriceID int,
 DealCompID int,
 Tier tinyint,
 FeeName varchar(50),
 FeeType varchar(50),
 Vol int,
 estprice decimal(18,6),
 FeeTypeID int,
 rate decimal(18,6),
 tierActual int,
 ActualPrice decimal(18,6),
 pct decimal(18,6)
)

Use this:

declare @TempFees table
(
 DealCompPriceID int,
 DealCompID int,
 Tier tinyint,
 FeeName varchar(50),
 FeeType varchar(50),
 Vol int,
 estprice decimal(18,6),
 FeeTypeID int,
 rate decimal(18,6),
 tierActual int,
 ActualPrice decimal(18,6),
 pct decimal(18,6)
)

 

 

Tags:

SQL SERVER

SQL Server add an identity key to existing table

by ebarcza 10/15/2009 11:54:00 AM

Say you got a table with 1000 records in then realize you did something real stupid(like I did) and not create a key or keyable compound fields. Welp you have to add an identity key but there's no easy way to back fill the entire 1000 records... Until now

step 1.) Script the table to create a new one with a slightly different name.

step 2.) add an identity key, seed it to 1

step 3.) insert all records from the old table into the new one. SQL server will fill the identity column just like you need them

step 4.) drop the old table without a key

step 5.) rename the new table

step 6.) go have a beer! you're done

 

Tags: ,

SQL SERVER

Saving changes is not permitted - Management Studio

by ebarcza 10/12/2009 11:12:00 AM
Save not permitted in SQL Server 2008 - Management Studio
For those new to SQL Server 2008, you have probably got this message the first time you tried to change a table design.

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require a table to be re-created"

When trying to change the columns in some tables, add columns or change null settings, you'll get the "Saving changes is not permitted" error and something like tables have to be dropped and re-created. The only choice you have is to click cancel, or to choose to save the message to a text file, not very useful.

The solution is pretty easy: Tools -> Options -> Designers, and uncheck the option "Prevent saving changes that require table re-creation".

Tags:

SQL SERVER

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About the author

Name of author Author name
Something about me and what I do.

E-mail me Send mail

Calendar

<<  January 2018  >>
MoTuWeThFrSaSu
25262728293031
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

Recent comments

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2018

Sign in