Split a string using a table Variable

Was just playing around with Table variables and thought of a practical problem lot of people approached to me with. Thought its worth documenting on how I do it so somebody can reuse the trick.

Given a set of names you want to split the First name and Last Name identifying the first Space as delimiter between the two parts of the name.

The way I do it to use a table variable to store all the names to process
and then use a set of string functions to calculate the charindex and select substrings based on the requirement as below.

DECLARE @strInput TABLE ( Name VARCHAR(256) )

INSERT INTO @strInput
( Name )
VALUES ( ‘Abc 123’ ),
( ‘Ade Abc’ ),
( ‘Xyz Abc’ ),
( ‘123 Abc’ ),
( ‘123 xyz abc’ )
SELECT LEFT(Name, CHARINDEX(‘ ‘, Name) – 1) AS FristName ,
SUBSTRING(Name, CHARINDEX(‘ ‘, Name) + 1,
DATALENGTH(Name) – CHARINDEX(‘ ‘, Name)) AS LastName
FROM @strInput

The result as below:

Simple to begin with and there are a number things you can achieve using this base logic.

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , , | 1 Comment

Passed Exam 70-451: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008

Passed Exam 70-451: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008.

This now takes my skillset to a new level on Dev aspects of sql server Microsoft Certified IT Professional (MCITP): Database Developer 2008

I could never debate further on the exam pattern and topics covered and share almost the same experience as Brent Ozar

However having gone through many blogs and lots of experts sharing their pain points around the exams; I was mentally prepared on what to expect.

With this I have completed my pre-requisites for MCM.

Posted in MCITP, MCM, SQL Server | Tagged , | Leave a comment

Two main tables in a distribution database to facilitate replication

Somebody asked me this question: “What are the two main tables in a distribution database when you configure replication

The two main tables in a distribution database to facilitate replication are those that contain the information on where to start the replication from and to where this is to be sent to. Meaning one table to store source information and another to store destination information on the distribution database.

1. MSpublications or IHpublications ( for non-SQL Server publication )
2. MSsubscriptions or IHsubscriptions ( for subscription to a publication from a non-SQL Server Publisher )

Posted in Replication, SQL Server, Tricks | Tagged | Leave a comment

Passed 70-433 TS: Microsoft SQL Server 2008, Database Development

Passed Exam 70-433 TS: Microsoft SQL Server 2008, Database Development This morning.

The only book I referred in preperation for the exam was the:

MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 – Database Development

http://www.microsoft.com/learning/en/us/Book.aspx?ID=13159&locale=en-us

The book included code samples, ebook (useful for kindle or study on your PC/Laptop) and practice tests on a disk.

Also included an evaluation version of the sql server and 15 % discount on the prometric exam.

Watch this space for my next step forward i.e., Exam 70-451

 

Posted in MCM, MCTS, SQL Server | Tagged , , | Leave a comment

Installing Full Text Search on SQL Server

Was just playing around with Full text search and was trying to enable full text search on one of my table on AdventureWorksDW2008 database. Noticed the full text search was greyed out and didnt remember if I ever installed full text search with my sql server.

Here is what the Full text index looked like on the table:

Full text index DisabledNow this is what I use to query the serverproperties to see if the Full text search is installed on the sql instance. A result of ‘0’ (Zero) means Full Text search is not installed on the server. A resulf of ‘1’ (one) means Full text search is installed on the sql server.

Serverproperty('IsFullTextInstalled')Now that I know Full text is not installed on the server. Time to Install Full text search.

Launch the sql server setup from the SQL installation Disk.

Choose the Installation page on the left hand side of the SQL Server installation center and Click on ‘New Installation or add features to an existing installation.’ option as highlighted below:

SQL Server installation centerThe sql server setup launches the ‘System Configuration Check’ and checks the setup support roles as shown below; Go ahead and click OK to proceed if all the rules have passed the check, otherwise you will need to take appropriate actions for the rules that failed the checks.

System Configuration checkGo ahead and click install on the next page to install the setup support files further.

InstallOnce done the system configuration checker kicks in again and performs a check against a different set of rules, review and click ‘Next’ if all seem ok.

 

System Configuration CheckGo ahead and select ‘Add feature to an existing instance of SQL server 2008 R2′ (that is because I have 2008 R2 installed ) and the instance name. ( in my case’ Training’) and click Next.

Add features to existing sql server 2008 R2On the feature selection page notice that Full-Text search is available under the ‘Database Engine services’ under ‘Instance Features’.

Instance FeaturesSelect Full-Text Search and you should see ‘Server Configuration’ page added to the left hand side of the setup window, we will get to that in a minute. Click ‘Next’ to continue.

Full-Text SearchSystem Configuration checker checks the Installation Rules this time and provides us the status of each check and gives a go-no go signal. Click ‘Next’ to go further.

System Configuration CheckerThe Next page computes the space required for the installation of the Full-Text Search feature; Review and click next to proceed.

Disk Space RequirementsThe next is the server configuration page, where the ‘SQL Full-Text Filter Daemon Launcher’ Service Account name password needs to be defined, and its startup type needs to be defined. Set appropriate domain account and click ‘Next’

SQL Full-Text Filter Daemon LauncherThe Next page gives you an option to handle the Error Reporting to send information to Microsoft. Click ‘Next’ to proceed.

Error ReportingThe system configuration checker runs once last check on the Installation configuration rules and provides the status to decide to proceed with the install. Review and click ‘Next’

system configuration checkerOn the next page review the installation features and settings collected and their summary and click ‘Install’ to begin installation.

Install

Then Next page provide you the installation progress and the installation should be fairly quick. wait for the completed successfully message and click ‘Close’

ProgressFinishNow Open the query window on the instance and check the result for the serverproperty(‘IsFullTextInstalled’) to confirm installation.

Installed

 

 

 

 

 

Posted in Full-Text Search, SQL Server | Tagged , | 1 Comment

Microsoft Assessment and Planning (MAP) Toolkit – Version 6.5 – Part 1

MAP provides detailed readiness assessment reports and executive proposals with extensive hardware and software information, and actionable recommendations to help organizations accelerate their IT infrastructure planning process, and gather more detail on assets that reside within their current environment.

This post is about my experience installing and using MAP V6.5; I would do this in parts to keep it more easier to consume for the readers.

Part 1 – Installing MAP Toolkit V6.5

Installing MAP Toolkit is very straight forward, all you need to know is it install sql server express to maintain the inventory and few bits to bear in mind highlighted below.

First things first Download the MAP Toolkit Version 6.5 if you haven’t already done so.

Run the Install file downloaded above and follow through the steps as below.

The first screen you see when running the executable is the welcome page and there is a little tick box at the bottom of the page allowing you to check for Automatically download and update device compatibililty and reference information. Tick if you want to and read the privacy statement (I really did not read it, but when you get a moment, contents of the privacy statement are outside the scope of this post) and click ‘Next’

MAP ToolkitRead the License Agreement and Accept the terms ( Contents of License agreement outside the scope of this post) and click ‘Next’

MAP Toolkit

Browse and Select the installation path or leave it at the default location; You might need to change the path if installing on a production monitoring server to appropriate path.

MAP Toolkit

On the Next Page you should see options to Download and Install or Install from previous downloaded installation files for SQL Server Express; This is where the MAP will store the inventory we capture when we start using MAP. Choose appropriate option and click ‘Next’.

Lets say you do not have the downloaded installation files for SQL Server Express, in which case you select Download and Install and click ‘Next’ as below:

MAP Toolkit

The Next page is SQL Server Express License Agreement. Agree and Click ‘Next’

MAP Toolkit

Next Page gives you an option to share your hardware, configuration and usage of the Tool with Microsoft; Read through carefully and Opt as per your preference and click ‘Next’

MAP Toolkit

Next page summarizes the options you selected and is ready to install, Click ‘Install’ when you are ready.

MAP Toolkit

Now this will download the SQL server Express, Install it to default location and Install MAP tool as shown in the next three snapshots.

MAP Toolkit

MAP Toolkit

MAP Toolkit

Once MAP Toolkit is installed the last page provides you an option to open the MAP Toolkit, Opt and Click ‘Finish’ to Open MAP

MAP Toolkit

When MAP Toolkit is opened for the first time you see a screen like the one below; which prompts you to create a new inventory database to store the inventory data you capture using MAP.

MAP Toolkit

Specify a name ‘MAPInventory’ and a brief description and click OK.

MAP Toolkit

There is also a button to Manage at the bottom (as highlighted in prev snapshot) of the Create database page which provides you an option to Import , Export, Edit and view the summary of the MAP inventory databases. This makes more sense when we are revisiting MAP later once this is setup. More on this in the next part of this post.

MAP Toolkit

This is it, that is how long you take to install MAP. The next page shows you all the possible aspects of your infrastructure you can analyze and inventorize. More on this in the next part.

MAP Toolkit

Hope you found it useful, and enjoy discovering great stuff about your infrastructure you never knew about.

References:-

Read more on the Microsoft Technet about MAP Toolkit

Read the What’s New from the MAP Toolkit version 6.5

Download MAP Toolkit Version 6.5

Posted in SQL Server | Tagged , | Leave a comment

Script to find when a table was last modified

I have had a question from one of my collegues, how to find when was a table last modified.

Here is what I suggest to use to find when was tables schema changed and when was the last user update/delete on the table.

Script to find the last schema change date of a table, you will need to supply table name in the where clause. Run this query in the target database.

SELECT  name AS TableName ,
modify_date AS Last_schema_Change
FROM    sys.objects
WHERE   name = ‘<table>’

Script to find when the table data was last changed, here you will need to specify the database name and the table number.

SELECT  OBJECT_NAME(OBJECT_ID) AS TableName ,
last_user_update ,
user_updates ,
index_id
FROM    sys.dm_db_index_usage_stats
WHERE   database_id = DB_ID(‘<databasename>’)
AND OBJECT_ID = OBJECT_ID(‘<tablename>’)

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , | Leave a comment

SQL Server configurations List

Was just checking sql server configuration of my sql installation via t-sql and found a way of listing which sql server configurations can be changed dynamically.

Here is the list of SQL Server configurations with their descriptions, that can be changed without needed a sql server restart.

name description
access check cache bucket count Default hash bucket count for the access check result security cache
access check cache quota Default quota for the access check result security cache
Ad Hoc Distributed Queries Enable or disable Ad Hoc Distributed Queries
affinity mask affinity mask
affinity64 mask affinity64 mask
Agent XPs Enable or disable Agent XPs
allow updates Allow updates to system tables
backup compression default Enable compression of backups by default
blocked process threshold (s) Blocked process reporting threshold
clr enabled CLR user code execution enabled in the server
cost threshold for parallelism cost threshold for parallelism
cross db ownership chaining Allow cross db ownership chaining
cursor threshold cursor threshold
Database Mail XPs Enable or disable Database Mail XPs
default full-text language default full-text language
default language default language
default trace enabled Enable or disable the default trace
disallow results from triggers Disallow returning results from triggers
filestream access level Sets the FILESTREAM access level
ft crawl bandwidth (max) Max number of full-text crawl buffers
ft crawl bandwidth (min) Number of reserved full-text crawl buffers
ft notify bandwidth (max) Max number of full-text notifications buffers
ft notify bandwidth (min) Number of reserved full-text notifications buffers
index create memory (KB) Memory for index create sorts (kBytes)
in-doubt xact resolution Recovery policy for DTC transactions with unknown outcome
max degree of parallelism maximum degree of parallelism
max full-text crawl range Maximum  crawl ranges allowed in full-text indexing
max server memory (MB) Maximum size of server memory (MB)
max text repl size (B) Maximum size of a text field in replication.
media retention Tape retention period in days
min memory per query (KB) minimum memory per query (kBytes)
min server memory (MB) Minimum size of server memory (MB)
nested triggers Allow triggers to be invoked within triggers
network packet size (B) Network packet size
Ole Automation Procedures Enable or disable Ole Automation Procedures
optimize for ad hoc workloads When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.
PH timeout (s) DB connection timeout for full-text protocol handler (s)
precompute rank Use precomputed rank for full-text query
query governor cost limit Maximum estimated cost allowed by query governor
query wait (s) maximum time to wait for query memory (s)
recovery interval (min) Maximum recovery interval in minutes
remote admin connections Dedicated Admin Connections are allowed from remote clients
remote login timeout (s) remote login timeout
remote proc trans Create DTC transaction for remote procedures
remote query timeout (s) remote query timeout
Replication XPs Enable or disable Replication XPs
server trigger recursion Allow recursion for server level triggers
show advanced options show advanced options
SMO and DMO XPs Enable or disable SMO and DMO XPs
SQL Mail XPs Enable or disable SQL Mail XPs
transform noise words Transform noise words for full-text query
two digit year cutoff two digit year cutoff
user options user options
xp_cmdshell Enable or disable command shell

Script to get this list of configurations is as below:

SELECT  name ,
description
FROM    sys.configurations
WHERE   is_dynamic = 1
ORDER BY name

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged | Leave a comment

Read SQL Server error Log and SQL agent log

Often DBA’s need to quickly look at sql server error log and agent logs to find something speicific, and it takes awful lot of time to go through a huge error log file; and you want to get the information quickly;

This is where you can use my script to my information in different ways using a simple query.

xp_readerrorlog is an extension to the sp_readerrorlog ( accepts 4 paramenters) and is extended to provide more filters and search conditions, with 7 parameters;

Paramteres are as below:

  1. Value of error log file you want to read: 0 = current log, 1 = Archive #1, 2 = Archive #2, etc…
  2. Log file type: 1 or NULL = sql server error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for, eg:- you want to search for database
  4. Search string 2: String two you want to search for to further refine the results; eg:- you want to filter and display only the error messages.
  5. Search from start time : this parameter can be used to filter out the log and fetch log only starting at the start time; best used with end time to see errors over a datetime
  6. Search to end time: this parameter is specified to read the error log upto end time
  7. Sort order for results: N’asc’ = ascending, N’desc’ = descending

To Read the current sql server error log file and search for a specific word (for eg:- Databasename) in the log file use

EXEC xp_readerrorlog 0, 1,’Databasename’,’error’
GO

This scans through the current sql server error log and searches for ‘Databasename’ and filters the results by ‘error’; very handy.

To read the current sql server error log file and see logs over the last 30 mins of time run the below query:

DECLARE @start DATETIME, @end DATETIME
SET @start=DATEADD(MINUTE,-30,GETDATE());
SET @end=GETDATE();

EXEC xp_readerrorlog 0, 1,NULL,NULL,@start,@end,’Desc’
GO

This reads the current sql server error log and returns the sql server error logs for the last 30 mins; perfect if you want to use this like a snipet or a webpart on your dashboard to display current error messages.

To Read the sql server agent error log all you need to do is specify the second parameter as 2.

EXEC xp_readerrorlog 0, 2
GO

Posted in SQL Server, sql server error log, T-SQL Scripts, Tricks | Tagged , , , | Leave a comment

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

This error message is raised when your user account does not have the db_owner role of a database meaning you are not the owner of the database; and you try to open the properties dialog box of a database in SSMS 2008.

Microsoft SQL Server, Error 7983

Microsoft SQL Server, Error 7983

User ‘<User>’ does not have permission to run DBCC showfilestats for database ‘<Database Name> ‘.

Cause:

This happens when you right click on a database in SSMS and click properties of the database; In response SQL Server 2008 runs the DBCC SHOWFILESTATS command to fetch the UsedSpace property and the AvailableSpace property. Because the user is not the db_owner, the user does not have permission to run the DBCC SHOWFILESTATS command. Therefore, the user cannot open the Properties dialog box in SSMS 2008.

Workaround:

UsedSpace and AvailableSpace properties do not appear any where on the database properties page, so you are stuck on an error which doesn’t really mean anything to you.

As a workaround you can get away by using T-sql to query database properties

for eg:- use this to see the database properties if you know what you need to look at.

SP_HELPDB <databasename>

if you are looking at highlevel information of all database just use this t-sql

SP_HELPDB

Often you will need to look at database properties like database create date; you can get this information using the below t-sql.

SELECT
name AS [Database Name],
create_date AS [DB Creation Date]
FROM sys.databases

Resolution:

The fix for this issue was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

979065 Cumulative update package 7 for SQL Server 2008 Service Pack 1

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released

Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Refer to http://support.microsoft.com/kb/980037 for more details.

 

Posted in SQL Server, T-SQL Scripts, Tricks, Troubleshooting | Tagged , , | Leave a comment