Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    30

    Unanswered: SQL 2005 vs SQL 2000

    I have another problem!
    I know,I know...I have problems continuously.

    I have this query.

    Code:
    DELETE FROM WeekRange
    INSERT INTO WeekRange
    SELECT 
    (case 
        
         when day([Date1])<=11 and month(Date1)=1 and (datename(weekday,dateadd(dd,1,[Date1]-day([Date1])+1)-1)='Monday' or datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Tuesday' or datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Wednesday' or datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Thursday' or datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Friday') 
                then convert(varchar,dateadd(dd,1,[Date1]-Day([Date1])+1),103)
    
    when day(Date1)<=4 and datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Tuesday'
         then convert(varchar,dateadd(dd,1,[Date1]-Day([Date1])+1)-1,103)
    
    when (day([Date1])<=11) and (datename(weekday,dateadd(dd,1,[Date1]-day([Date1])+1)-1)='Monday')
             then convert(varchar,DATEADD(wk, DATEDIFF(wk, 7, [Date1]), 7),103)
          
    when (day([Date1])<=11) and (datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Wednesday' or datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Thursday' or datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Friday') and (datediff(week,convert(varchar(6),[Date1],112)+'01',[Date1]+1)=0  or datediff(week,convert(varchar(6),[Date1],112)+'01',[Date1]+1)=1)
                then convert(varchar,dateadd(dd,1,[Date1]-day([Date1])+1)-1,103)
    
          
    
          when day([Date1])>=29 and datename(weekday,[Date1])='Monday' and datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)!='Thursday'
                then convert(varchar,dateadd(wk,datediff(wk,0,[Date1]),-7),103)
    
          when day([Date1])>=29 and datename(weekday,[Date1])='Tuesday' and datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)!='Thursday'
                then convert(varchar,dateadd(wk,datediff(wk,0,[Date1]),-7),103)
    
          when day([Date1])>=29 and datename(weekday,[Date1])='Wednesday' and datename(weekday,dateadd(dd,1,[Date1]-Day([Date1])+1)-1)='Thursday'
                then convert(varchar,dateadd(wk,datediff(wk,0,[Date1]),-9),103)
    
         when day(Date1)=28 and month(Date1)=2 and datename(weekday,Date1)='Monday' and day(dateadd(wk,datediff(wk,0,[Date1]),-7))=21 and datename(weekday,dateadd(wk,datediff(wk,0,[Date1]),-7))='Monday'
               then convert(varchar,dateadd(wk,datediff(wk,0,[Date1]),-7),103)
    
          
          when day(Date1)=28 and datename(weekday,Date1)='Monday' and datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Wednesday'
               then convert(varchar,dateadd(wk,datediff(wk,0,[Date1]),-7),103)
           
         when day(Date1)=30 and datename(weekday,Date1)='Wednesday' and datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Wednesday'
               then convert(varchar,dateadd(wk,datediff(wk,0,[Date1]),-7),103)
    
                
          else convert(varchar,DATEADD(wk, DATEDIFF(wk, 7, [Date1]), 7),103)
          end)firstday,
       (case 
        
          when day([Date1])>=24 and (datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Monday' or datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Tuesday' or datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Wednesday' or datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Thursday' or datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Friday' or datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Saturday') and (datediff(week,convert(varchar(6),[Date1],112)+'01',[Date1]+1)=0 or datediff(week,convert(varchar(6),[Date1],112)+'01',[Date1]+1)=4)
                then convert(varchar,dateadd(month,1,[Date1]-day([Date1])+1)-1,103)
    
          when day(Date1)=21 and datename(weekday,Date1)='Friday'
              then  convert(varchar,DATEADD(wk, DATEDIFF(wk, 4, [Date1]), 4),103)
    
    
          when ((31-day(Date1))<=10) and datediff(week,convert(varchar(6),[Date1],112)+'01',[Date1]+1)=3 and (datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Monday' or datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Tuesday' or datename(weekday,dateadd(month,1,[Date1]-day([Date1])+1)-1)='Wednesday')
                then convert(varchar,dateadd(month,1,[Date1]-day([Date1])+1)-1,103)
          
         when (day(Date1)=2 and datename(weekday,[Date1])='Wednesday') or (day(Date1)=3 and datename(weekday,Date1)='Thursday' and datename(weekday,Date1+1)='Friday')
              then convert(varchar,DATEADD(wk, DATEDIFF(wk, 4, [Date1]), 4),103)
          
         when day([Date1])<=3 and (datename(weekday,[Date1])='Wednesday' or datename(weekday,[Date1])='Thursday' or datename(weekday,[Date1])='Friday') and (day([Date1]-2)!=1 or datename(weekday,([Date1]-2))!='Monday')
              then convert(varchar,dateadd(week,datediff(week,0,[Date1]),11),103)
         
         when (31-day([Date1])<=1)
               then convert(varchar,dateadd(month,1,[Date1]-day([Date1])+1)-1,103)
        
        when day(Date1)=26 and datename(weekday,[Date1])='Tuesday' and month(Date1)=1 and (datediff(week,convert(varchar(6),[Date1],112)+'01',[Date1]+1)=4)
             then convert(varchar,dateadd(month,1,[Date1]-day([Date1])+1)-1,103)
    
         
         
    
          else convert(varchar,DATEADD(wk, DATEDIFF(wk, 4, [Date1]), 4),103)
          end)lastday
    
         FROM DocHdSal
    where year(Date1)=year(getdate());
    
    
    with temptable as
    (
    select row_number() over (partition by firstday,lastday order by firstday) as rownumber,*
    from WeekRange
    )
    delete from temptable where rownumber>1;




    In SQL 2005,this query runs with no problems BUT it doesn't execute in SQL 2000. WHY?????


    When delete this part of the code

    Code:
    with temptable as
    (
    select row_number() over (partition by firstday,lastday order by firstday) as rownumber,*
    from WeekRange
    )
    delete from temptable where rownumber>1;
    it execute normally.

    help me please!!

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Common table expressions and windowing functions was introduced in SQL Server 2005, and does thus not work in SQL Server 2000.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    30
    Quote Originally Posted by roac View Post
    Common table expressions and windowing functions was introduced in SQL Server 2005, and does thus not work in SQL Server 2000.


    I have a serious problem now!!!

    can I do something, to "convert" the code above?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Upgrade from an 11 year old technology to a 6 year old technology?

  5. #5
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Well, I'll say that you've learned a lesson. Develop on the same version and edition as you have in production, otherwise you can run into such problems, unless you know what features are supported on each version and edition. I've seen a lot of developers working on Developer Edition getting a surprise when they try to deploy their solutions to Standard Edition.

    As for your current problem, I do believe that SQL Server 2000 is not supported anymore, so you should consider upgrading. If upgrading is not an option:

    In a single transaction:
    * Read out all distinct rows to a temp table with holdlock, tablockx
    * truncate the table
    * Insert all the rows from the temp table
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  6. #6
    Join Date
    Jul 2011
    Posts
    30
    Quote Originally Posted by MCrowley View Post
    Upgrade from an 11 year old technology to a 6 year old technology?
    unfortunately,it is not on my hand.

  7. #7
    Join Date
    Jul 2011
    Posts
    30
    Quote Originally Posted by roac View Post
    Well, I'll say that you've learned a lesson. Develop on the same version and edition as you have in production, otherwise you can run into such problems, unless you know what features are supported on each version and edition. I've seen a lot of developers working on Developer Edition getting a surprise when they try to deploy their solutions to Standard Edition.

    As for your current problem, I do believe that SQL Server 2000 is not supported anymore, so you should consider upgrading. If upgrading is not an option:

    In a single transaction:
    * Read out all distinct rows to a temp table with holdlock, tablockx
    * truncate the table
    * Insert all the rows from the temp table

    you are right.
    I can say that is my mistake.I don't think at all that perhaps I have a such problem.


    I meet many problems during the creating of my report.
    I have huge of data and the queries run very slowly.

    Now, I am thinking to cut off the insert functions from the main query so, I will have better performance.


    how can I create an automatic insert?? I mean, a function that runs automatic every one statement is true, only from the system,not from anyone person.

  8. #8
    Join Date
    Feb 2004
    Posts
    492
    how can I create an automatic insert?? I mean, a function that runs automatic every one statement is true, only from the system,not from anyone person.
    after insert trigger? works both in sql 2000 and sql 2005.

  9. #9
    Join Date
    Jul 2011
    Posts
    30
    you are right!nice idea!

    I must apologize for my silly questions. this is a result about I am in confusion on the project I am running.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •