SQL Server 2012: SELECT Machine Name, Server Name, Edition, Product Level, Product Version, Licence Type

Description

This post shows how to SELECT property information for a SQL Server.



Solution

The SELECT statement below retrieves Machine Name, Server Name, Edition, Product Level, Product Version, and License Type.


SELECT
SERVERPROPERTY('MACHINENAME') as [Machine Name],
SERVERPROPERTY('SERVERNAME') as [Server Name],
SERVERPROPERTY('EDITION') as [Edition],
SERVERPROPERTY('PRODUCTLEVEL') as [Product Level] ,
SERVERPROPERTY('PRODUCTVERSION') as [Product Version],
SERVERPROPERTY('LICENSETYPE') as [License Type]



The results of the query are shown below.

References

MSDN (2014).  SERVERPROPERTY (Transact-SQL).  Retrieved January 8, 2014 from http://msdn.microsoft.com/en-us/library/ms174396.aspx

How to Associate SQL Server Login with Existing Database User

Description

Sometimes a SQL Server Login  is created and corresponding Database User is created at the same time.  However, sometimes the Database User is not associated with a SQL Server Login and needs to be.  For example, this could happen during an database environment migration or if when the Active Directory User Account associated with SQL Server Login is deleted and a new one is created.


Solution

The steps below can be used to lookup database principals and then associate a Login with a User.

1.  Run the SELECT query below to view all database principals, including:
- DATABASE_ROLE
- SQL_USER
- WINDOWS_USER

SELECT * FROM sys.database_principals


2.  Run  ALTER USER query below to alter the database user and associate a login with it.  In the example below, a Windows Login is being associated with a database user.

ALTER USER "DOMAIN\Username"
WITH
LOGIN = "DOMAIN\Username"

(Microsoft TechNet, 2014)


References

Bertrand, A. (November, 2013).  Script to Set the SQL Server Database Default Schema For All Users.  Retrieved January 7, 2014 from
http://www.mssqltips.com/sqlservertip/3098/script-to-set-the-sql-server-database-default-schema-for-all-users/

Microsoft TechNet (2014).  ALTER USER (Transact-SQL).  Retrieved January 7, 2014 from http://technet.microsoft.com/en-us/library/ms176060.aspx

Tharaka MTR (May, 2013).  How to Fix Orphaned SQL Users.  Retrieved January 7, 2014 from http://www.codeproject.com/Articles/594134/How-to-Fix-Orphaned-SQL-Users

SQL Server Reporting Services (SSRS) Report Pagination InteractiveSize

Description


When viewing a deployed, SQL Server Reporting Services (SSRS) report, the default pagination does not provide a pleasant user experience because viewing more than a handful of rows of report data requires that the user page right and page left.

This characteristic is a result of the default InteractiveSize Properties of the report.  TechNet describes InteractiveSize Properties as, "InteractiveHeight and InteractiveWidth are used by the HTML rendering extension to provide the equivalent of PageHeight and PageWidth. Because the HTML rendering extension dynamically resizes a report to accommodate drilldown, drillthrough, and show/hide features, the report server uses different properties to support pagination on dynamic pages." (Microsoft TechNet, 2013)

Solution

To display all rows of data on the first page of the SSRS report, set InteractiveSize properties (Width and Height) to 0.

After updating the properties, Save the report, then Deploy the report.

Refresh the report in the browser and now all of the report data will display on the page. If viewing the report in SharePoint using a Report Viewer Web Part, then you may need to adjust the Height and Width of the Report Viewer Web Part to eliminate any scroll bars that appear as a result of the data being displayed on one page.

References

Microsoft TechNet (2013).  Controlling Report Pagination.  Retrieved November 29, 2013 from http://technet.microsoft.com/en-us/library/ms156282%28v=sql.90%29.aspx

Error: SQL Server: Unable to Shrink Transaction Log, "Could not locate file for database..."

Description

You attempt to shrink a transaction log file associated with a SQL Server database, but you receive in error:

"Could not locate file 'Transaction Log Name' for database
'Database Name' in sys.database_files. The file either does not exist, or was dropped."



Solution


1)  Run SP_HelpFile to get the correct name of the transaction log file.

USE Database Name
EXEC sp_helpfile

(TechNet, 2013)

This will return the following information about the database files, including the associated transation log(s):  Name, FileId, FileName, FileGroup, Size, MaxSize, Growth, Usage



2)  Run the Transact-SQL statement again, using the correct Transaction Log Name.  You can find this in the Name column of the SP_HelpFile query results set.

USE [Database Name]
 GO
ALTER DATABASE Database Name SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(Transaction Log Name, 1)
ALTER DATABASE Database Name SET RECOVERY FULL WITH NO_WAIT
 GO

(TechNet, 2013)



3)  Verify that there are configured, scheduled, and enabled SQL Server Backup jobs in place for:

a) Full Database Backup
b) Transaction Log Backup *Important for Full Recovery Model*



Reference

TechNet (2013).  Backup Under the Full Recovery Model.  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms190217%28v=sql.105%29.aspx

TechNet (2013).  DBCC SHRINKFILE (Transact-SQL).  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms189493.aspx

TechNet (2013).  Recovery Models and Transaction Log Management.  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms366344%28v=sql.105%29.aspx

TechNet (2013).  sp_helpfile.  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/aa933456%28v=sql.80%29.aspx

Error: Microsoft SQL Server: Cannot connect to (local)

Description

Error when attempting to log into SQL Server Management Studio:  "Cannot connect to (local)."

Solution

As the error message indicates, the SQL Server is not accessible.  The issue may be caused by one of the following:

1)  Issue with Server Connection Configuration

Start > All Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager

Verify the correct protocols are configured (this will vary based on type of environment).  Refer to Microsoft TechNet article, Choosing a Network Protocol, for further assistance with this topic.

2)  Issue with Service Account Authentication
Start > Administrative Tools > Services
Verify that the SQL Server (MSSQLServer) Service is running.  If it is not running, try to start it.  Make sure the Service Account credentials are properly configured and that the account is not locked out.

References

Microsoft TechNet (August, 2011).  Choosing a Network Protocol.  Retrieved August 29, 2011 from http://technet.microsoft.com/en-us/library/ms187892.aspx.

Error: Microsoft SQL Server Shared Feature Directory (INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR)

Description


During an installation of Microsoft SQL Server 2008 R2 x64 Standard Edition on a Windows Server 2008 R2 Service Pack 1 x64 Standard Edition operating system, I encountered issues on the Feature Selection screen.


Issue #1 - While the installation is 64bit, the Shared Feature Directory is hard coded as x86 - this is wrong.


Issue #2 - Error:  "The INSTALLSHAREDWOWDIR command line value is not valid. Please ensure the specified path is valid and different than the INSTALLSHAREDDIR path."

Solution



Aside from the regular disclaimer about don't modify the registry unless you have backups and know what you are doing, I want to also mention that the solution that worked for me does not apply to all situations.  For example, if I experienced this issue while installing on an Itanium processor, then the solution would be different (Microsoft Support, 2011).

So, having said that, the solution for this particular situation was the following:



1.  Open the Registry Editor

2.  Export the following key (as a backup)

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-518\Components\0D1F366D0FE0E404F8C15EE4F1C15094]

3.  Delete the key.

4.  Close the Registry Editor.

5.  Open the Command Prompt (Start > Run > CMD)

6.  Type the following command:

[Path to Setup.exe]\setup.exe /action=install /INSTALLSHAREDDIR=”C:\Program Files\Microsoft SQL Server\" /INSTALLSHAREDWOWDIR=”C:\Program Files (x86)\Microsoft SQL Server\" (MSDN, 2011)

7.  Press Enter.  The SQL Server Setup launches from the beginning.

Notes


"INSTALLSHAREDDIR" is the 64-bit shared component files directory with a path of "Program Files\Microsoft SQL Server" (Microsoft Support, 2011)


"INSTALLSHAREDWOWDIR" is the 32-bit shared component files with a path of "Program Files(x86)\Microsoft SQL Server" (Microsoft Support, 2011)


When specifying the the directories, there are known issues with putting a "\" at the end of the path.  For this reason, omit the symbol.


Examples:
Good - Program Files\Microsoft SQL Server
Bad - Program Files\Microsoft SQL Server\ 



Reference


Microsoft Support (2011). You receive an error message if you change the "Shared component directory" path on the Feature Selection screen when you install SQL Server 2008 on a computer that is running an Itanium-based version of Windows. Retrieved May 17, 2011 from http://support.microsoft.com/kb/955458.


MSDN (2011). How to: Install SQL Server 2008 from the Command Prompt. Retrieved May 17, 2011 from http://msdn.microsoft.com/en-us/library/ms144259(SQL.100).aspx.


MSDN (2011). Feature Selection. Retrieved May 17, 2011 from http://msdn.microsoft.com/en-us/library/ms143786.aspx.


MSDN (2011). File Locations for Default and Named Instances of SQL Server. Retrieved May 17, 2011 from http://msdn.microsoft.com/en-us/library/ms143547(v=SQL.105).aspx.

Error: SharePoint Server 2010 Installation, SharePoint Configuration Failed

Description:

After configuring the operating systems and SQL Server instance in a multi-tiered server farm, I installed the SharePoint Server 2010 prerequisites and server binaries on the first Web front end.  Next, I opened PowerShell and began creating a new farm from the command line.  After only the first step, I received the error shown below.

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Windows\system32> New-SPConfigurationDatabase -DatabaseName "FarmName_Configuration_Database_01" -DatabaseServer "FarmDBSQLAlias" -A
dministrationContentDatabaseName "FarmName_Content_Database_01" -Passphrase (ConvertTo-SecureString "FarmPassPhrase" -AsPlaintext -Force) -FarmCredentials (Get-Credential)
...
New-SPConfigurationDatabase : CREATE FILE encountered operating system error 3(The system cannot find the path specifie
d.) while attempting to open or create the physical file 'I:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\
MSSQL\Data\FarmName_Configuration_Database_01_log.LDF'.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
At line:1 char:28
...

So, I navigated to the log directory: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS and opened up the current PSCDiagnostics log file.


CTRL + F "error" brought me to this error:

...
01/20/2011 17:46:26 10 ERR Exception: System.Data.SqlClient.SqlException: CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'I:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\FarmName_Configuration_Database_01_log.LDF'.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
...

Solution:

After viewing the log file message, I immediately knew what the cause of the error was.  The SQL Server default path for LDF was not accessible because it hadn't been created yet.  During the SQL Server 2008 R2 installation, I specified a particular default drive letter for MDF and LDF.  After the installation, I changed the default path location for LDF's to a different drive letter, however, the new folder path (I:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data) didn't exist yet!

The fix was simply to go to the I: drive letter and create the necessary folder paths that I had set as the default LDF location for the SQL instance.

Note:  When you install SQL Server 2008 R2 and you encounter the screen that asks for the default locations, be sure to set the MDF first and then the LDF second.  The reason is that if you do the MDF path second, the installation wizard will reset your LDF location back to the MDF setting.  It's just one of those things!