SQL Server Management Studio – James Software Development Corner

SQL Server Management Studio – Using Row_Number, Partition, and filtering on Row_number

Let’s say you want to retrieve only the first row of each question type from the question type table. The 3rd SQL statement will give you your results, but let’s explain each of 3 SQL statements.

select RowNumber, question_type 
 from

…

SQL Server Management Studio – Retrieve one child row regardless of number of grand children from another child table with or without filtering

Let’s say you want to report on each child row, but you only want to see one row based on a grand child row with filtering on the grand child row. The following SQL will not work, but instead gets …

SQL Server Management Studio – Retrieve one parent row regardless of number of children when filtering on children

Let’s say you want to report on each parent row, but must join a child row to do filtering on the child row, but you only want to return one row per parent. The following SQL will not work, but …

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 …

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

…

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

…

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 …

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 …