In this case we want to extract a two digit month for the past month !

RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MONTH, dateadd(m,-1,GETDATE()))), 2)

Thanks to my friend Louis on this one that saved me !

Tagged with:  

After running a query to move the tempdb and its log to another drive you may get the following error :

From my end it was that i’ve forgot to resize the file before moving it , so to way to make it work was in command line to go to the sql server bin folder and start it with the following switch :

– sqlservr.exe -T3608

That will allow you to login SQL and fix it !

Enjoy 🙂

Tagged with:  

The main goal of this article is realy simple, we had to install SQL Management Sutdio onto our Citrix Terminal Server and it wasn’t as easy as it should have. First we received an error on the requirement of the Visual Studio 2008 SP1 , a lot of Windows / Microsoft Update later, still no luck for the install, here how to make it happend :

  • First we need a Cd of SQL server 2008+
  • Mount the CD on the server
  • In Command line :
<pre>msiexec /i D:\x64\Setup\vs_shell.msi PATCH=D:\x64\Setup\VS90sp1-KB945140.msp /qb (where D:\ is the location of your SQL media)</pre>
  • You can now launch the SQL Management Studio installation, for our case we installed the Express version to just get the console

Ref: http://support.citrix.com/article/CTX128280

Tagged with:  

All you have to do is to modify the path in the script and run it in a new query !


USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'e:\tempdb01.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'f:\tempdb01.ldf')
GO

NB: You have to restart SQL services once your done !

Tagged with:  

How to add file and log to SQL tempdb

On 12 February 2014, in SQL, by Himselff

As Microsoft best practice , we need one tempdb file per CPU and normaly on different disk, here how to do it !

In Query Analyser :

  • Pour le fichier :

USE [master]
GO
ALTER DATABASE [tempdb]

ADD FILE (
 NAME = tempdb04,
 FILENAME = 'k:\MSSQL\Data\tempdb04.ndf',
 SIZE= 20GB,
 FILEGROWTH = 0
 );
GO

  • Pour le log :

USE [master]
GO
ALTER DATABASE [tempdb]

ADD LOG FILE (
 NAME = tempdblog04,
 FILENAME = 'l:\MSSQL\Data\tempdblog04.ldf',
 SIZE= 10GB,
 FILEGROWTH = 0
 );
GO

Tagged with:  

select * from TABLE where DATETIME_FIELD between CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))

Tagged with: