SQL Server Management Studio – Split a Table Column into Two Columns Before and After a Specific Character

Let’s say you have a column on your table named name_title that contains an employee’s name and their title separated by a comma as such: ‘John Doe, Manager’.

The LEFT method will grab everything to the left of the comma ‘,’ and call it Name.
The REPLACE method will grab everything to the right of the comma ‘,’ and call it Title.

Your search results will contain two columns as such:

Name    | Title
------------------
John Doe  Manager

Here is the T-SQL below:

SELECT LEFT(name_title, CHARINDEX(',', name_title) - 1) AS Name, REPLACE(SUBSTRING(name_title, CHARINDEX(',', SystemVname_titlealue), LEN(name_title)), ',', '') AS Title
FROM name_table where key = 1

SQL Server Management Studio – Order by different columns based on values in the different columns

Wrap your head around this one!

Here is an example of an order by in a SQL Statement that shows how specific you can get when ordering your data.
order by location_3,
case 
 when location_3 ='c' then
 case 
 when location_4 = '1' then location_5
 when location_4 in('2','3') then name_last
 else location
 end

when location_3 ='d' then name_last

when location_3 ='e' then
 case 
 when location_4 = '1' then location_5
 when location_4 in('2','3','4','5') then name_last
 else location
 end

when location_3 ='f' then
 case 
 when location_4 in('1','2','3') then name_last
 when location_4 in('4','5') then location_5
 else location
 end

when location_3 ='g' then
 case 
 when location_4 in('1','2','4','5') then name_last
 when location_4 = '3' then location_5
 else location
 end

else location
end

SQL Server Management Studio – Get metadata on mdf database files

In SQL Server Management Studio, you can run the following commands against your MDF database files to get metadata information.

--SQL Server DBCC CHECKPRIMARYFILE with Option = 0
--First, we will use it with zero to check if it is the primary file if so it will return 1 otherwise 0.
DBCC CHECKPRIMARYFILE('C:\Users\jrmoo\Downloads\MDF\Homeschool_Database.mdf',0) WITH NO_INFOMSGS
--Determine version of mdf file
DBCC CHECKPRIMARYFILE('C:\Users\jrmoo\Downloads\MDF\Homeschool_Database.mdf',2) WITH NO_INFOMSGS
--To check logical name, size, maxsize, status and physical name of all files
DBCC CHECKPRIMARYFILE('C:\Users\jrmoo\Downloads\MDF\Homeschool_Database.mdf',1) WITH NO_INFOMSGS
--Checks the logical name, status and path of all files
DBCC CHECKPRIMARYFILE('C:\Users\jrmoo\Downloads\MDF\Homeschool_Database.mdf',3) WITH NO_INFOMSGS

Here are the output results:

SQL Server Management Studio – Clear Find and Replace window history

In Regedit:

For SQL Server 2005 :
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\Find

For SQL Server 2008 :
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\Find

For SQL Server 2012 :
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\Find

Remove the Find and Replace items.

SQL Reports – Can’t access SQL Reports (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)


ERROR

[UnauthorizedAccessException: Access is denied. (Exception from HRESULT:
 0x80070005 (E_ACCESSDENIED))]

 [FileLoadException: Could not load file or assembly
 'System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral,
 PublicKeyToken°3f5f7f11d50a3a' or one of its dependencies. Access is
 denied.]
 System.Web.Configuration.RegexWorker..cctor() +0

SOLUTION

Go to the Reporting Services Configuration Manager and restore the encryption key with your backup.

RSC-001

RSC-002
RSC-003

Apply the changes and it should fix the issue. If you didn’t back up the encryption key when you installed the reporting server… well, I guess you are out of luck. You will have to delete the key and start over by deleting the the corrupted key (Click the Delete button) and redefine the connection and subscriptions on the report server.

Now when you go to your reports via the url in the browser, it should work.

http://localhost/Reports/Pages/Folder.aspx

RSC-004

 

 

SQL Server Management Studio – Error message when you try to save a table in SQL Server: “Saving changes is not permitted”.

This problem occurs when “Prevent saving changes that require table re-creation” option is enabled.

Solution:

Go into Tools -> Options -> Designers-> Uncheck “Prevent saving changes that require table re-creation”.

SQL Server Management Studio – Creating Foreign Key Relationships on Tables with Data

Using the Database Diagram, you can create a Primary/Foreign Key relationship between two tables. But if the tables already have data, the relationship will fail to establish. If you are sure you won’t have orphaned data, the resolution is to set the ‘Check Existing Data On Creation Or Re-Enabling’ = “No” within the Foreign Key Relationships dialog box. The dialog box can be accessed when creating the relationship or by right clicking the table on the diagram and choosing ‘Relationships’.

Check Existing Data On Creating Or Re-Enabling

SQL Server Management Studio – Studio hangs when trying to select my database hosted by ARVIXE

This post will give you step by step instructions on how to fix the issue in Microsoft SQL Server Management Studio that causes the studio to hang, spin, or loop forever when trying to select a database from the plethora of existing databases stored on your hosted site.

After logging in with the proper server name, user name and password, you will need to follow these instructions:

1) Click “View” and then “Object Explorer Details”. You could also hit the F7 key on your keyboard.
2) Double click on the “Databases” folder from the right panel.
3) Right-click on the column header “Policy Health State”.
4) Deselect the option “Collation”.
5) Click “Refresh”.

Now when selecting your database it won’t hang, even with subsequent instantiations of SQL Management Studio.

SQL Server Management Studio – How to determine Product Version, Edition, and Level

How to determine what version of SQL Server Management Studio you are running can be determined within SQL Server Management Studio by performing a SQL Query.

Here’s how:

1) Open Microsoft’s SQL Server Management Studio
2) Connect to a database server
3) Click ‘New Query’ from the toolbar.
4) Past the following query and execute:

 

SELECT SERVERPROPERTY('productversion') as ProductVersion
SELECT SERVERPROPERTY('edition') as EDITION
SELECT SERVERPROPERTY ('productlevel') as Level

Your results should look something like this:

SQL Server Management Studio – How to remove “Server name” items from history

  1. In the search box, type Command Prompt
  2. Navigate to the root directory  – Type cd\ then hit enter
  3. Type DEL /S SqlStudio.bin and hit enter. (This should find every instance of the file and delete it from the system).

Note: Multiple instances can exist if more than one version of SSMS is installed. If you want to delete individual instances of the file, then navigate to its folder and delete it manually via Windows Explorer. Just be aware that different Windows Operating systems store them in different paths.

For example, in Windows 10, SSMS 2012 is here:
C:\%UserProfile%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0

SSMS 2008 is here:
C:\%UserProfile%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell

Hope this helps!