Archive for the ‘SSMS’ Category

SSMS TSQL Short Cuts

Posted: May 3, 2012 in SSMS
Tags:

SSMS allows you to create your own shortcut keys:
Under the Menu Tools Select Options
Under Environment >> Keyboard there is a list of shortcut commands.
A Couple are pre-populated Alt_F1 sp_help; Ctrl+1 sp_who; Ctrl+2 sp_lock;
I have added the following

ctrl+F1 master..xp_fixedDrives;
Shows how much space is available on the fixed drives

Ctrl+3 Select * from sys.databases;
Lists all databases

Ctrl+4 SELECT name AS ‘File Name’ , physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’ FROM sys.database_files;
Shows how much space is available in SQL Data files – uses the context of the current database.

Ctrl+5 DBCC SQLPERF(logspace);
Shows TLog sizes and space used.

Ctrl+6 dbcc Loginfo();
Each row represents a Virtual Log File.

Ctrl+7 Exec xp_msver;
Details server edition etc

Ctrl-8 SELECT database_name, backup_start_date, name, [description], [user_name], [type] FROM msdb.dbo.backupset where type != ‘l’ order by backup_start_date desc; Show backup history (not TLogs)

Ctrl+0 EXECUTE xp_instance_regRead N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’BackupDirectory’; EXECUTE xp_instance_regRead N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’defaultData’; EXECUTE xp_instance_regRead N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’defaultLog’;
Shows the default file locations for Data, Logs, and Backups

Advertisements

When SSMS starts up it copies the files from here:
%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\sql
in my case on a 64bit machine
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\sql

To your user folder (this is an example for me, my login is lynchs)
C:\Users\lynchs\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

The first thing I did was go into %ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems and copy all the templates out somewhere. I’ve never used them but I guess I might.

NOTE
You should leave the file SQLFile.sql – this is your default New Query file.
I also left Sql.vsdir although I’m not sure what that one is for/

Then I went into C:\Users\lynchs\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql and deleted all of it.

Now I have no templates. I do have my own TSQL Library and so I set up a robocopy job to populate my templates directory from my library. It looks like this:
robocopy “H:\IT\Delivery\DBA\SQLTools\TSQL” “C:\Users\lynchs\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql” /R:2 /W:2 /Mir *.sql

I choose to do it this way because the templates directory is more volatile. I’ve already mentioned how it tries to copy files from %ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems. I am also more likely to accidently delete the file from templates in SSMS than I am from my own library (which could be set to readonly).

Remember if you want ot update or reorganise the templates you do the work in your TSQL Library and robocopy will then modify the templates folder. I have robocopy set to run on log-on.

Rename a SQL Server

Posted: October 3, 2010 in DBA, SSMS

Working with VMs a server can be cloned with SQL Server installed. The server is renamed but SQL server maintains its original name.

Run this script in SSMS:

sp_dropserver ‘old server name’

sp_addserver ‘new server name’, ‘local’

Restart the SQL Server service then test the rename worked:

Select @@servername;

Make sure the SQL Agent is running – it will stop when the sql service stops. If you restart sql server service through the SQL configuration control panel it should restart the sql agent for you.

Error Message in SSMS

TITLE: Microsoft SQL Server Management Studio

——————————

Cannot show requested dialog.
——————————

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

——————————
There is no row at position 0. (System.Data)

Problem is to do with system table spt_values being deleted.

Run this script to repair – the location may be different on your server.
C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\install\u_tables.sql

SSMS short cuts

Posted: January 25, 2010 in SQL 2005, SSMS

Although I have been using SSMS for a number of years now I didn’t realise you could create your own short-cuts until I saw it mentioned on the UK SQL Server User Group: http://sqlserverfaq.com/
SSMS allows you to create your own short cut keys:

Under the Menu Tools Select Options

Under Environment >> Keyboard there is a list of shortcut commands.

A Couple are pre-populated Alt_F1 sp_help; Ctrl+1 sp_who; Ctrl+2 sp_lock;

I have added:
Ctrl+F1: master..xp_fixedDrives; Shows how much space is available on the fixed drives

Ctrl+3: Select * from sys.databases; Lists all databases

Ctrl+4: SELECT name AS ‘File Name’ , physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’ FROM sys.database_files; Shows how much space is available in SQL Data files

Ctrl + 5: DBCC SQLPERF(logspace); shows log files, the size of the file and % free space.
I have since added a couple more. One shows server waits and another shows server activity in a bit more details than sp_who2. I think I’ll keep these as floating short cuts and change them depending on what I am working on or investigating.

I ran a query which resulted in this:
An error occurred while executing batch. Error message is: The directory name is invalid.

The reason for this was an invalid temp directory. The system was pointing at c:\temp but the directory did not exist. I re-pointed all the temp and tmp environment variables at the real temp directory and all was well again.