On the Journey to be a SQL Server Professional: 2013

Discontinued Database Engine Features in SQL Server 2005 - 2012 - a checklist

An IE 11 problem on displaying tabs of a report on report manager

Problem:

I created a report using report builder 3 and saved it to report manager. However, when I view the report on report manager, I noticed that tabs are in separate rows as shown below. Notice: Show tabs in a separate row" is off.

Solution:

Uninstall IE 11 and resort to IE 10. It works as expected as below.

Another option is to add the URL (http://localhost/) to the compatibility view. You can find the option as below.

Several parts do not show up on Dataset Properties in Report Builder 3

Problem:

After the dataset is created, the next step is to create a dataset. Both are embedded in my case. However, I notice that three buttons (i.e.,Query Designer, Import, Refresh Fields) are missing under the query box on the Dataset Properties panel in report builder 3 as below.

Reason:

I had customized my desktop to 125% at one time, which causes the three buttons not showing up.

Solution:

Set the display to 100%.

What is FileTable? How to set it up and test it?

What is FileTable (or FileTables, Microsoft uses both interchangeably)?

FileTable is a special table storing file and directory in Windows share in the database.

It is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

What's for?

FileTable utilizes SQL Server for the storage and management of unstructured data that is currently residing as files on file servers.

Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server.

At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.

It provides support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

How to do it?

--Step 1: Enable filestream at the instance level

0 = Disables FILESTREAM support for this instance.

1 = Enables FILESTREAM for Transact-SQL access.

2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

--Step 2: Provide a FILESTREAM Filegroup at the Database Level

ALTER DATABASE myDatabase
ADD FILEGROUP FileTable_FG
CONTAINS FILESTREAM;

--Step 3: Provide a filename for filetables at the Database Level. Need to create the C:\SQLData directory

ALTER DATABASE myDatabaseADD FILE(
NAME= 'myFileTable_File',FILENAME = 'C:\SQLData\myFileTable_File')
TO FILEGROUP FileTable_FG;GO

--Step 4: Enable Non-Transactional Access and specify a Directory for FileTables at the Database Level

ALTER DATABASE myDatabase

SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'myFileTable_File' )

--Step 5: Create a FileTable at the Database Level

CREATE TABLE DemoFileTable AS FILETABLEWITH(FILETABLE_DIRECTORY = 'myFileTable_File',FILETABLE_COLLATE_FILENAME = database_default);GO

--Step 6: Test it

--a. type \\YourMachinName\ at the run or searching box, that should open the windows shares on your network--b. alternatively, click computer, then click network at the bottom of the left panel.--c. go to the following path and create some files: \\YourMachineNamec\MSSQLSERVER\myFileTable_File\myFileTable_File--d. go to SMSS and right-click the FileTable "DemoFileTable", choose "Select Top 1000 Rows"
/* The columns are provided by MS
SELECT TOP 1000 [stream_id] ,[file_stream] ,[name] ,[path_locator] ,[parent_path_locator] ,[file_type] ,[cached_file_size] ,[creation_time] ,[last_write_time] ,[last_access_time] ,[is_directory] ,[is_offline] ,[is_hidden] ,[is_readonly] ,[is_archive] ,[is_system] ,[is_temporary]FROM [myDatabase].[dbo].[DemoFileTable]*/--e. if the contents of the file is string-type data, you can convert it from binary to characters.

select convert(varchar(max),file_stream),name
FROM [myDatabase].[dbo].[DemoFileTable]

How to delete records in chunk to avoid blocking?

SET NOCOUNT ON

WHILE 1=1

BEGIN

DELETE TOP (1000)

FROM YourTable

WHERE FilterConditions

END

Visual Studio 2012 - Operation is not valid due to the current state of the object.

Problem:

When I open a package in VS 2012, I get an error message on the dialog box

"Operation is not valid due to the current state of the object."

Then I try to open/create a project instead, I get the same error message.

Solution:

In Control Panel, find "SQL Server Data Tools for Visual Studio 2012", choose repair (not uninstall). Fixed!

How to pause a screen in PowerShell? Use |More and the Spacebar

Scenario:

When I use Get-Command, the commands are too many to fit in one screen. The screen runs very faster to the end. I want to see the output screen by screen without using scrolling the mouse.

Solution:

Get-Command |More. Then use the Space Bar key instead of the Enter key for the next screen. If you use the Enter key, you will see one line per Enter after the 1st screen.

A code bug in a demo script from a SQL Server Training Company

The code sample is from LearnItFirst.com
Course 161 - Transact-SQL Programming: SQL Server 2008/R2
Chapter 4 - Ch04_15_Strings

Purpose of the code - to return the last index of a search term in a string.

-- This is the original. It does not work as intended. It has two flaws.

CREATE FUNCTION [dbo].[LastIndexOf](@InputString VARCHAR(MAX), @SearchTerm CHAR)

RETURNS INT

BEGIN

IF dbo.IsNullOrEmpty(@InputString) = 1 OR dbo.IsNullOrEmpty(@SearchTerm) = 1

RETURN 0

DECLARE @pos INT

SELECT @pos = LEN(@InputString) - CHARINDEX(@SearchTerm, REVERSE(@InputString)) + 1

IF @pos > LEN(@InputString)

SET @pos = 0

RETURN @pos

END

DECLARE @InputString VARCHAR(MAX) = 'Scott Whigham is a Whigham from Whigham, Georgia.'

DECLARE @SearchTerm VARCHAR(128) = 'gh'

SELECT CHARINDEX(@SearchTerm, @InputString)

SELECT REVERSE(@InputString)

SELECT dbo.LastIndexOf(@InputString, @SearchTerm)

--This is my version

--Flaw 1 - IN THE ORIGINAL: NEED TO CHANGE @SearchTerm to VARCHAR(Max) from a single character CHAR

--That's why the orginal code taking 'g' not 'gh' as the searching term and the last position of g at posision 46.

ALTER FUNCTION [dbo].[LastIndexOf](@InputString VARCHAR(MAX), @SearchTerm VARCHAR(max))

RETURNS INT

BEGIN

IF dbo.IsNullOrEmpty(@InputString) = 1 OR dbo.IsNullOrEmpty(@SearchTerm) = 1

RETURN 0

DECLARE @pos INT

--Flaw 2 - need to reverse the @SearchTerm as well, also no '+1'

SELECT @pos = LEN(@InputString) - CHARINDEX(REVERSE(@SearchTerm), REVERSE(@InputString))

IF @pos > LEN(@InputString)

SET @pos = 0

RETURN @pos

END

DECLARE @InputString VARCHAR(MAX) = 'Scott Whigham is a Whigham from Whigham, Georgia.'

DECLARE @SearchTerm VARCHAR(128) = 'gh'

SELECT CHARINDEX(@SearchTerm, @InputString), REVERSE(@InputString), REVERSE(@SearchTerm)

,CHARINDEX(reverse(@SearchTerm), REVERSE(@InputString))

,LEN(@InputString)

,LEN(@InputString)- CHARINDEX(reverse(@SearchTerm), REVERSE(@InputString))

,dbo.LastIndexOf(@InputString,@SearchTerm)

Revert vs Revoke - Confused?

They are totally two different animals:

Revert - Switches the execution context back to the caller of the last EXECUTE AS statement.

Revoke - Removes a previously granted or denied permission.

How to make the vertical query windows available?

Problem:

No option is available (Split will make horizontal query windows).

Solution

Step 1: Close all docs

Step 2: choose Tabbed Document

Step 3: New Query Window 1

Step 4: New Query Window 2

Step 5: At this point, the menu option "New Vertical Tab Group" should be available now

Step 6: Enjoy it!

How to pause a batch while executing? - timeout /t 60

Secnario:

I need to copy a .dll assembly to a couple of places and register it in in the Global Assembly Cache.

A .bat file was provided as below:

=====================

@echo off
copy "C:\TK463\Chapter19\Lesson3\Starter\TK463 Chapter 19\TK463.CalculateCheckSum\bin\Release\TK463.CalculateCheckSum.dll" "%ProgramFiles(x86)%\Microsoft SQL Server\110\DTS\PipelineComponents" /Y
copy "C:\TK463\Chapter19\Lesson3\Starter\TK463 Chapter 19\TK463.CalculateCheckSum\bin\Release\TK463.CalculateCheckSum.dll" "%ProgramFiles%\Microsoft SQL Server\110\DTS\PipelineComponents" /Y
echo.
"%ProgramFiles(x86)%\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\x64\gacutil" /u TK463.CalculateCheckSum
echo.
"%ProgramFiles(x86)%\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\x64\gacutil" /i "d:\ProgramFiles%\Microsoft SQL Server\110\DTS\PipelineComponents\TK463.CalculateCheckSum.dll"
echo.
"%ProgramFiles(x86)%\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\x64\gacutil" /i "d:\ProgramFiles(x86)%\Microsoft SQL Server\110\DTS\PipelineComponents\TK463.CalculateCheckSum.dll"
=====================

Solution:

In order to make it work, I need to make the following changes:

1. Change C:\TK463\... to C:\Users\CK\Downloads\...

2. Change "%ProgramFiles(x86)%\Microsoft SQL Server\110\DTS\PipelineComponents" to "D:\Program Files(x86)\Microsoft SQL Server\110\DTS\PipelineComponents", which is the DTS folder on my pc, note there is space between Program and Files(x86), similarly

3. Change "%ProgramFiles%\Microsoft SQL Server\110\DTS\PipelineComponents" to "D:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents"

4. I added timeout /t 60 at the end of the batch file to pause the message for 60 seconds.

An error message on opening the Master Data Manager website

Problem:

Oops, got the following when opening the Master Data Manager website.

Reason:

This error can occur when there are multiple versions of the .NET Framework on the computer that is running IIS, and IIS was installed after .NET Framework 4.0 or before the Service Model in Windows Communication Foundation was registered.

Solution - To register the correct version of ASP.NET

Open a command prompt as an administrator as follows:
1. From the Start menu, choose All Programs, and then choose Accessories.
2. Right-click Command Prompt, and then choose Run as administrator.
At the command prompt, type the following command to change to the Microsoft.NET\Framework64\v4.0.30319 folder, and then press Enter.
```
cd \Windows\Microsoft.NET\Framework64\v4.0.30319
```
At the command prompt, type the following command, and then press Enter.
```
aspnet_regiis.exe -iru
```
At the command prompt, type the following command, and then press Enter.
```
iisreset
```

Need to make the "NT Service\SQLAgent$SQL2012" a read permission on the needed file

Problem:

Try to automate an SSIS job in SQL Server agent. The SSIS Service account is used, which is NT Service\SQLAgent$SQL2012 (for a named instance SQL2012 in my case). Got an error on open a local flat file.

Solution:

Sharing the folder containing the file with the 'Everyone' group with a read permission as the virtual account 'NT Service\SQLAgent$SQL2012' is not possible to be explicitly selected.

Please note that, unlike the virtual account for SSAS, the virtual account for SQL Server Agent (i.e., SQLServerSQLAgentUser$MachineName$InstanceName) is not visible in the local user and group list (see this for details). So we cannot give the virtual account a read permission as we do for the virtual account for SSAS service account.

You may need to check the "32-bit Runtime" option when you validate or execute the packages in SSMS deployed from SSDT

Problem:

Everything runs fine in SSDT. Then I deploy the project to SSISDB Catalog in SSMS. But I keep getting error on validation or execution for one of the packages.

Reason:

The package needs to be run in 32-bit

Solution:

Check the "32-bit runtime" option for Execution as depicted below.

Similarly, if you run the package using DTExec, you need to use the 32-bit DTExec. In my case, the 32-bit DTExec is located at:

D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DtExec /Server "localhost\SQL2012" /ISServer "\SSISDB\TK 463 Chapter 11\TK 463 Chapter 10\Master.dtsx" /Par $ServerOption::LOGGING_LEVEL(Int32);1

BTW, the 64-bit is at D:\Program Files\Microsoft SQL Server\110\DTS\Binn

There is no such a 32-bit vs 64-bit problem with PowerShell. When I run the following ps file, it works fine as long as I change the server from localhost to localhost\SQL2012 (a named instance in my case)

=========================================================================

# Assign SSIS namespace to variable

$ssisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

# Load the SSIS Management Assembly

$assemblyLoad = [Reflection.Assembly]::Load($ssisNamespace + ", Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

# Create a connection to a SQL Server instance

$connectionString = "Data Source=localhost\SQL2012;Initial Catalog=master;Integrated Security=SSPI;"

$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString

# Instantiate the SSIS object

$ssis = New-Object $ssisNamespace".IntegrationServices" $connection

# Instantiate the SSIS package

$catalog = $ssis.Catalogs["SSISDB"]

$folder = $catalog.Folders["TK 463 Chapter 11"]

$project = $folder.Projects["TK 463 Chapter 10"]

$package = $project.Packages[“Master.dtsx”]

# Set package parameter(s)

$catalog.ServerLoggingLevel = [Microsoft.SqlServer.Management.IntegrationServices.Catalog+LoggingLevelType]::Verbose

$catalog.Alter()

# Execute SSIS package ($environment is not assigned)

$executionId = $package.Execute("false", $environment)

=========================================================================

Sometimes, we need to change the column data properties of the source table to solve the data consistency issue in SSIS

Scenario:

In completing Exercise 2 - Create Efficient Lookups on page 228 of the "Training Kit (Exam 70-463): Implementing a Data Warehouse" book, I encountered an error.

Checking on the error:

[Set default values [212]] Error: The "Set default values" failed because truncation occurred, and the truncation row disposition on "Set default values.Inputs[Derived Column Input].Columns[Gender]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Set default values" (212) failed with error code 0xC020902A while processing input "Derived Column Input" (213). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

So the error message complains the data truncation on Gender.

Checking on the derived column for gender - it is 1 byte! But the gender would be 'N/A' if not matching, it needs 3 bytes!

Solution:

There is no an easy way to change the gender to 3 bytes in SSIS. So I change it in SSMS:

ALTER TABLE stg.CustomerInformation

ALTER COLUMN Gender NChar(3)

ALTER TABLE stg.CustomerInformation

ALTER COLUMN MaritalStatus NChar(3)

After these changes, the package runs!

Change Run64BitRuntime to False when using ODBC Source with SSDT 2012

Problem:

When I run a package from an ODBC source, I get the following ODBC error:

Solution:

Change the Project Debugging Property "Run64BitRuntime" from the default TRUE to FALSE.

SQL Server 2012 Upgrade Advisor Needs a Separate Installation

Problem:

For some reason, the option 'Install Upgrade Advisor" on "SQL Server Installation Center" is dimmed or not available, even with all of the perquisites are met.

Solution:

Download it and manually install it.

http://www.microsoft.com/en-us/download/details.aspx?id=29065

Expand "Install Instruction" on the page - the Upgrade Advisor is close to the bottom, download it and install it.

Please note that after installation, there will be a separate menu under the Microsoft SQL Server 2012 folder for the SQL Server 2012 Upgrade Advisor. The "Install Upgrade Advisor" on Microsoft SQL Server 2012| Configuration Tools | SQL Server Installation Center or (x64) | Planning | Install Upgrade Advisor is still not disabled.

Why Partially Contained Databases and What Are Partially Contained?

What and Why?

When contained database was first introduced in SQL Server 2012 (Denali), SQL Server databases can become free of external dependencies on server-level metadata and settings, including login details. Thanks to the new database containment feature, DBAs finally get help dealing with the age-old problem of orphaned users.

There are three types of containment hinted at in SQL Server 2012 Books Online (BOL):

NONE. This default containment mode is what exists prior to SQL Server 2012. There are no boundaries defined or visible.
PARTIAL. With partially contained databases, you have the ability to define clearer boundaries between databases and the server, making it easier for metadata to be hosted within the databases. This, in turn, makes SQL Server databases more portable and less dependent on underlying hosts.
FULL. Fully contained databases are only alluded to in SQL Server 2012 BOL in a few locations and aren't currently available as an option. It's assumed, however, that full containment will enable greater database portability and potentially allow for strict enforcement of containment boundaries as a means of fully isolating databases from each other and from the underlying host.

However, the fully contained database features were not available. Even now, the fully contained functionality is not ready yet by SP1 (3339).

What are not contained?

Think about this way: what you cannot or should not do with the partially contained databases at this point. Following is a short list:

* Numbered procedures (a deprecated feature, several SPs with the same name but with different numbers)
* Schema-bound objects that depend on built-in functions with collation changes
* Binding change resulting from collation changes, including references to objects, columns, symbols, or types
* Replication, change data capture, and change tracking

-In addition, not all available features are contained for a particular application or database.

You can use the sys.dm_db_uncontained_entities and sys.sql_modules catalog views to find out info about the uncontained objects or features. Through these views, you can determine the containment status of applications and work out which objects or features you must replace or modify when transitioning to a more fully contained database.

What does it mean?

It means that we are actually working with the partially contained databases for now. The fully contained databases may be available in a future release.

A Workaround on Formatting the String returned by RAISERROR

Problem:

when I run anyone of the three below, the returned string in not formatted as desired.

--Option 1

RAISERROR ('Error in % stored procedure', 16, 0, N'usp_InsertCategories');

Msg 50000, Level 16, State 0, Line 2
Error in usp_InsertCategoriestored procedure

--Option 2

DECLARE @message AS NVARCHAR(1000) = 'Error in % stored procedure';

RAISERROR (@message, 16, 0, N'usp_InsertCategories s');

Msg 50000, Level 16, State 0, Line 2

Error in usp_InsertCategories stored procedure

--Option 3, in SQL Server 2014

DECLARE @message AS NVARCHAR(1000) = 'Error in % stored procedure';

SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');

RAISERROR (@message, 16, 0);

Msg 50000, Level 16, State 0, Line 4

Error in usp_InsertCategoriestored procedure

Notice there is a space and an 's' missing between usp_InsertCategories and stored procedure.

Workaround:

RAISERROR ('Error in % stored procedure', 16, 0, N'usp_InsertCategories'' s');

Msg 50000, Level 16, State 0, Line 1

Error in usp_InsertCategories' stored procedure

Want to show a number in % in SQL Server? - concatenate a string with % or use Format(aNumber, 'p', 'en-US')

Option 1:
- Need to use CAST or CONVERT to make the numeric into a varchar, then concatenate it with '%'

Option 2:
- Use the Format function in SQL Server 2012

When I tested it, option 2 is much slower than option 1. So if you just want to use it for the presentation purpose, option 1 is recommended.

SELECT custid, orderid, val,

CAST(CAST(100.0 * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) as VARCHAR) +'%' AS [pctcust_in_%],

FORMAT(CAST(100.0 * val / SUM(val) OVER() AS NUMERIC(5, 2)), 'p', 'en-US') AS [pcttotal_in_%]

FROM Sales.OrderValues;

Does the Intellisense bother you in some cases?

Press the Esc key if you do not want the undesirable suggestion.

An Error on Using the Performance Analysis of Logs (PAL)

Scenario:

Reading Tim Chapman's Chapter "Using PAL to analyze SQL Server performance" in the book "SQL Server MVP Deep Dives Volume 2" and playing with the PAL tool.

Problem:

See the screenshot for the error.

Solution:

In my case, I need to download and install " Microsoft Chart Controls for Microsoft .NET Framework 3.5"

The website helped me to solve the problem: http://pal.codeplex.com/ - see the "Required Products" section

Now it works. But my concern is that it takes too long to produce the final report with charts. I used the SQL Server 2008/R2 template with the PAL tool on my laptop, it takes about one hour.

Hold on, still error although the .html report is produced. It is half done.

Generating the HTML Report...Done

C:\Users\CK\Documents\PAL Reports\PAL_Microsoft_SQL_Server_2008___R2_PAL_ANALYS
IS_2013-05-21_04-42-53.htm1

WARNING: Please post errors or problems with this tool to the PAL web site
located at http://www.codeplex.com/PAL with the following error message and a
brief description of what you were trying to do. Thank you.

Invoke-Expression : Program 'PAL_Microsoft_SQL_Server_2008___R2_PAL_ANALYSIS_20
13-05-21_04-42-53.htm' failed to execute: %1 is not a valid Win32 application
At line:1 char:2
+ & <<<< 'C:\Users\CK\Documents\PAL Reports\PAL_Microsoft_SQL_Server_2008___R2_
PAL_ANALYSIS_2013-05-21_04-42-53.htm'.
At D:\Program Files\PAL\PAL\PAL.ps1:5213 char:22
+ Invoke-Expression <<<< -Command "&'$HtmlReportFilePath'"
+ CategoryInfo : ResourceUnavailable: (:) [Invoke-Expression], Ap
plicationFailedException
+ FullyQualifiedErrorId : NativeCommandFailed,Microsoft.PowerShell.Command
s.InvokeExpressionCommand

The Default Web Site is missing in IIS for Win 7

Problem:

When I try to create a virtual web directory under the Default Web Site in IIS, I found that the Default Web Site is missing. Only the MDS one is there. The default one was actually deleted when I played with Master Data Service in SQL Server 2012.

Solution:

Following the steps in this post (i.e., back the folders/files, uninstall IIS and Windows Process Activation Service from the Program/Feature in Control Panel)

http://forums.iis.net/t/1154336.aspx

It's up now!

But when I try to open the order page, I get this:

Reason:

I have Visual Studio installed before the newly re-installed IIS. I need to register the .ASP.NET version for the IIS.

Solution:

Open a command prompt and go to C:\Windows\Microsoft.NET\Framework64\v4.0.30319). Run aspnet_regiis -i.

Oops!

Found this one:

http://social.msdn.microsoft.com/forums/en-US/web/thread/2111c297-fab8-482b-9439-53b1464b782e

Open IIS manager, select Application Pools, select the application pool you are using, click on Advanced Settings in the right-hand menu. Under General, set "Enable 32-Bit Applications" to "True".

It finally shows up now!

Subscribe to: Posts ( Atom )