.net application hangs in iis7.0 at peak time randomly

we have our own webserver in that internal applications are hosted in that one of the application will be used at peak time by around 300 employees

this application get hangs some times in peak time

server details as follows: windows server 2008 standard IIS 7.0 .net framework 4.0 sql server 2005

I have created one application pool for 2 .net internal applications in same configuration as specified above. the other application will not get hangs in peak time and running smooth with similar number of users.

I have one doubt is some one forcefully stopping the application to run slow and get hangs .

Please let us know what will be the exact solution as i am getting this issue since 2 years and i googled and done changes in queries, IIS settings and code standards. but still the same problem persists making my life disaster.

some times i get errors as 1. network tcp_errors server may busy or server is down 2. in event logs login failure for sa 3. some times CPU usage will be 100% at peak time

SQL report using financial periods

I have created a report for management that will total everything up by month with in a date range. Management has now decided that rather than by month they would like to go by period. we have 13 periods in a year each is 28 days except the last one is 29 or 30 depending on if its a leap year. The beginning of the first period is always 1-1-YYYY So now I will need to figure out what the beginning and end of each period is and total up each period. I am not really sure how to do this since every year the dates will change and they may want to look at periods from 2013 through the current period. the code and results I am currently using are enclosed

SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, finspecteddate), 0) AS 'Date' 
    ,COUNT(*) AS Lots
    ,sum(flotSize) as 'Lot Size'
    ,sum(LReject) 'Lots Rejected'
    ,sum(fnumreject) as Rejected
    ,sum(fsampleSize) as 'Sample Size'
    ,sum(BDueDate) as 'Before Due Date'
FROM 
    ReportData
WHERE 
    finspecteddate >= '01-01-2014' 
    AND finspecteddate <= '10-15-2014' 
GROUP BY 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, finspecteddate), 0) 
ORDER BY 
    date

migrating ms sql database to new server

Scenario: We have MS SQL 2005 installed on a windows 2003 server with 9GB database. We acquired a new Windows 2012 server and installed MS SQL 2014 on it.

My expertise level: Partial knowledge of SQL server and databases

What I need: I am given a task to migrate the 9GB database (SQL 2005) to a SQL 2014.. DB names and all should be identical to old one..

I would like to know the steps involved from experts, so I can achieve this job w/o any issues.

I am thinking of below steps:

1) Full Backup of 2005 database... 2) Create a new database with same name on the new server 3) Restore the full backup (taken on old server) on the new server 4) Change the compatibility level to suit to the new sql server

pls. let me know if this works or do I need to follow other approach. any queries, pls. feel free to ask.

sql_learner

DATEPART(ww,Date) with SET DATEFIRST

I´m using SQL Server 2005

I´m trying to get the week with DatePart(ww,date) function

My code

SELECT datepart(ww,'2012-01-08 00:00:00')

Return 2

But I want ...

Return 1

According with IS0-8601 and this table from this website

YEAR 2012 Week-01 From 2012-1-2 to 2012-1-8 ...

Am I wrong?

There is any trick with SET DATEFIRST 1, I´m trying without success.

Thanks for your time

How to change the date datatype format in SQL Server 2005

I am using SQL Server 2005 it is accepting datetime as '2014-10-31 20:46:00.0' only

What should I do to make the format as '30-10-2014 22:14'?

How to find static/constant values and expression used against a column(s) in a view in SQL server?

Is there any way to check what static/constant value(s) used for which column(s) in views, This needs to be checked in 100s of views so it will be very time consuming if we do it manually by reading a defination of a view.

Similarlly, Another part of question is, can we capture/find what expression/calculation used against computed/dependent column(s) of a view.

Example:

CREATE VIEW dbo.Test
select distinct top 100 percent 
    NULL AS C1, NULL AS C2, NULL AS C3, NULL AS C4,
    NULL AS C5, NULL AS C6, NULL AS C7, NULL AS C8,
    NULL AS C9, NULL AS C10, NULL AS C11, NULL AS C12,
    NULL AS C13, NULL AS C14, NULL AS C15, NULL AS C16,
    'Termination_Category_' + CGSTAT + '_' + RSSTAT as Employee, 

    CASE WHEN LEFT(RSSTAT,1)='I' OR CGSTAT = 'INVOLT' THEN 'Y' 
        ELSE 'N' END AS Invol,
    'N' AS Inactive,
    CASE WHEN CGSTAT = 'RETIRE' THEN 'Y'
        ELSE 'N' END As Retire,
    'Y' AS Manager
from dbo.xyzs
    inner join [dbo].[vw_Ref_Test2] 
        on dbo.xyzs.cgstat = [dbo].[vw_Ref_Test2].[CDCODE]
    left join [dbo].[vw_Ref_Test3] 
        on dbo.xyzs.RSSTAT = [dbo].[vw_Ref_Test3].[CDCODE]
where CGSTAT<>'' or RSSTAT<>''

I need below for the above view:

S.No. View_Name   Column_Name    Value
1.     dbo.Test     Employee      'Termination_Category_' + CGSTAT + '_' + RSSTAT
2.     dbo.Test     Manager        Y   
3.     dbo.Test     Retire         CASE WHEN LEFT(RSSTAT,1)='I' OR CGSTAT = 'INVOLT' THEN 'Y' 
                                       ELSE 'N' END AS Invol,
                                             'N' AS Inactive,
                                             CASE WHEN CGSTAT = 'RETIRE' THEN 'Y'
                                       ELSE 'N' END

Any usefull guidance will be really helpful.

query to get following output

this is my table

BatchName    SubBatch   records     
12032014     raw1_0     2  
12032014     raw1_0     2  
12032014     raw1_1     2  
12032014     raw1_1     2     
12302014     raw7_0    150  
12302014     raw7_0    150  

want to get output like BatchName SubBatch records

12032014 raw1_0 4

12032014 raw1_1 4 12302014 raw7_0 300