collaboration-2-go

Home

Kontakt

RSS

Hauptnavigation

Collaboration-2-Go

Lösungen und Software-Entwicklung im Collaboration Umfeld, insbesondere Microsoft SharePoint, Microsoft Exchange und Microsoft Office.

Support

Mobil: +49 (152) 53 97 78 79
Mail: service@collaboration-2-go.de
Weitere Kontaktmöglichkeiten: Kontaktseite

SQL Server - Sequential IDs for each month
Created: 30.11.2012
Categories: SQL Server

      Sequential IDs for each month and each month begins with an ID=1

      Sample Table:


      CREATE TABLE [dbo].[Orders](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [OrderDate] [date] NOT NULL,
      [CustomID] [int] NOT NULL,
      [Title] [nchar](50) NOT NULL
      ) ON [PRIMARY]

Insert:


      BEGIN TRAN
        declare @currentMaxID int
        declare @newID int

        select @currentMaxID = max(CustomID)
        from [dbo].[Orders] WITH (UPDLOCK)
        where year(OrderDate) = year(getdate())
        and month(OrderDate) = month(getdate())

        if @currentMaxID is null
        begin
          set @currentMaxID = 0
        end
        set @newID = @currentMaxID + 1
        
        insert [dbo].[Orders]
        (OrderDate, CustomID, Title)
        values
        (getdate(), @newID, 'my title')
      COMMIT

      Recommendation: use the insert code within a stored procedure.

Send us a Comment!

Shrink all Databases
Created: 10.09.2012
Categories: SQL Server

If you want to shrink all your databases including logs you can use the following script:


      declare @dbname sysname
      declare @sql nvarchar(1000)

      declare db_cursor cursor for
      select name from master.dbo.sysdatabases
      where name not in ('tempdb', 'model', 'msdb', 'master')
      open db_cursor

        fetch next from db_cursor into @dbname
        while @@fetch_status = 0
        begin
          print @dbname
          select @sql = '
          ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE
          DBCC SHRINKDATABASE ([' + @dbname + '])
          ALTER DATABASE [' + @dbname + '] SET RECOVERY FULL'
          exec sp_executesql @sql
          fetch next from db_cursor into @dbname
        end

      close db_cursor
      deallocate db_cursor

      Caution: do not use the script in productive databases since you lose
      the logs for your current backup.

Send us a Comment!

SQL Server Access as a local Administrator
Created: 03.09.2012
Categories: SQL Server

      In case you have lost your passwords for all your SQL Server accounts here
      is a way that works in SQL Server 2008 R2 to get access:

      1.) Write a batch file:


        sqlcmd -Q "CREATE LOGIN [domain\user] from windows"
        sqlcmd -Q "EXEC sys.sp_addsrvrolemember
           @loginame = N'domain\user', @rolename = N'sysadmin'"

      2.) Replace values for domain\user as appropriate, remove linebreak in 2nd sqlcmd
      3.) Add this Script to your scheduled tasks
      4.) Run scheduled task as SYSTEM
      5.) Wait and see

      It shouldn't work in SQL Server 2012 anymore.

Send us a Comment!

SQL Server - Get Active Connections
Created: 16.05.2012
Categories: SQL Server

Get active connections using the following script:


        select * from sys.sysprocesses where dbid = DB_ID('WSS_Content')

In case you want to end an connection you can kill der connection using the id.

Send us a Comment!

SQL Server - Backup all Databases except System Databases
Created: 19.03.2012
Categories: SQL Server

      Backup all databases except system databases using the
      following script:


      declare @dbname sysname
      declare @basepath sysname
      declare @filename sysname
      declare @filedate varchar(8)

      set @basepath = 'c:\Backup\'
      select @filedate = convert(varchar(8), getdate(), 112)

      declare db_cursor  cursor for
      select name from master.dbo.sysdatabases
      where name not in ('tempdb', 'model', 'msdb', 'master')
        open db_cursor
        fetch next from db_cursor into @dbname

        while @@fetch_status = 0
        begin
          set @filename = @basepath + @filedate + '-' + @dbname + '.bak'
          print @filename
          backup database @dbname to disk = @filename with compression
          fetch next from db_cursor into @dbname
        end

        close db_cursor
      deallocate db_cursor

Ensure folder permissions for your service account!

Send us a Comment!