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
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,
when location_3 ='c' then
when location_4 = '1' then location_5
when location_4 in('2','3') then name_last
when location_3 ='d' then name_last
when location_3 ='e' then
when location_4 = '1' then location_5
when location_4 in('2','3','4','5') then name_last
when location_3 ='f' then
when location_4 in('1','2','3') then name_last
when location_4 in('4','5') then location_5
when location_3 ='g' then
when location_4 in('1','2','4','5') then name_last
when location_4 = '3' then location_5
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:
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.
[UnauthorizedAccessException: Access is denied. (Exception from HRESULT:
[FileLoadException: Could not load file or assembly
'System.Web.RegularExpressions, Version=184.108.40.206, Culture=neutral,
PublicKeyToken°3f5f7f11d50a3a' or one of its dependencies. Access is
Go to the Reporting Services Configuration Manager and restore the encryption key with your backup.
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.
This problem occurs when “Prevent saving changes that require table re-creation” option is enabled.
Go into Tools -> Options -> Designers-> Uncheck “Prevent saving changes that require table re-creation”.
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’.
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.
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.
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:
- In the search box, type Command Prompt
- Navigate to the root directory – Type cd\ then hit enter
- 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!