SQL University

I was going through a blog roll and realized that I should share this information. A great effort by Jorge Segarra! Visit this link if you really want to get started on SQL Server from basics.

I just hope that the series will continue.

Enjoy!

A little surprise.

I was checking my blog y’day to see what I had written the last, and when. Well I was little surprised to see my blog hits were pretty high on 8/21 and the article – Sybase to SQL Server 2005 migration had highest views. This encourages me to finish my pending series of the migration experience. So keep watching further on this topic from me. Thanks for reading my posts, and btw do comment or ask me questions.

Cheers!

Haven’t blogged for long!

Hi Folks,

It has been quite long that I had blogged and you would see such notes (maybe 3?) in my blog. Somehow, I keep pausing quite often and I hate to do this. No excuses this time, but yes it has been quite busy 3 months for me. I had been into a migration, performance assessment and tuning, database design and so on. I shall share all my experiences, learning’s and views of this 3 month journey.

It would start off like this

- Migration experiences

- Performance baseline | benchmark, and tuning

- Identifying bottlenecks and finding ways to fix

So keep a watch on what I write and I would be interested to know your comments too.

Happy reading.

INFORMATION_SCHEMA is not a valid object in AdvWorks..!?

Sometime today, I wanted to have AdventureWorks database on my new workstation SQL Server instance. So I went ahead and download AdventureWorks database installable from here and I don’t have to talk about this website. I quickly scanned through the bunch of files that were available and found what I wanted. Well, what next – downloaded 27M file and installed it, and queried my favorite query against the new database “select * from information_schema.tables”.

Woooo, I got this error and kept getting this error

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘information_schema.tables’.

My, my what am I into, and I was confused for few moments but didn’t panic at least . So as usual I got into digging mode to find out what is it all about. Here are the few top reasons I could think and went finding out serially (although not a big deal reasons, but these matter a lot).

- Is this object really there in the database? Yes, verified through the object explorer.

- Is the object name correct, and the schema name valid? Yes, verified through the object explorer, and common sense along with years of  experience says that this metadata view is existing.

Then why on the earth I am not able to access? Something put me thinking about the case sensitivity of the object name and yes my thinking was correct. I immediately changed the query to “select * from INFORMATION_SCHEMA.TABLES”. Viola, it works great but I thought how many queries should I keep writing to address case-sensitivity problem.  Now back to fixing mode, and the options I have are

- Write the queries which will have case sensitive object names. Not possible, and it will make me crazy.

- Change the collation of the database from case-sensitive to case-insensitive. Well not possible because there are several objects (tables, indexes,….) in the database that are dependent on this collation. If I have to change the collation then there should be changes in each object.

Oh, but, how did it happen that I have got a sample database installable which is case-sensitive  in the first place? Did I miss something while I chose the file that needs to be downloaded?

Answers to my pondering questions were straight. Yes I downloaded the installable of the case-sensitive database and also I realized I can’t change the collation too. Due to time factor and since I was running out of patience I had to drop the “wrong” database, and I had to install the “right” database in my system. Well I am happy now that I have right database up and running for me to dig on something new which I shall blog soon.

Learning lesson – look for the right database installable before getting into a “waste-of-time” mess.

Hope my experiences gives you guys an idea how my life is going on with so many (?) things happening around .

Cheers and enjoy your happenings too!

Testing linked servers

Its been scripting day, although not too complicated scripting. One of my team mates came up to me asking “Is there any easy way out to test linked servers configured in an instance?”. I said there is a way, but not sure if it would be easy or hard way out. Well, at the end with little tussle around on the script, I realized I should keep it simple and here is the bunch of t-sql statements I have written.

I have built this script from an original script written by someone (thanks to his ideas!).

/*Script to Test Linked Servers connectivity*/

BEGIN
SET NOCOUNT ON
/*Variable Declaration */
DECLARE @ServerName sysname
DECLARE @msg VARCHAR(500)
DECLARE @status INT ;
/*Table variable to capture the linked server status*/
DECLARE
@LinkedSrvrStatus TABLE
(
LineItem INT IDENTITY(1,1)
, LinkedSrvName VARCHAR(200)
, LinkedSrvStatus TINYINT
, TestComments VARCHAR(800)
)

– Get the list of Linked Servers
DECLARE LnkdsrvrCrsr
CURSOR FAST_FORWARD FOR
SELECT name FROM sys.servers
WHERE is_linked=1 AND name NOT IN (”) –Not in Clause can be used to exclude some Test Linked Servers

OPEN LnkdsrvrCrsr
FETCH NEXT FROM LnkdsrvrCrsr INTO @ServerName

WHILE @@FETCH_STATUS = 0
BEGIN
– The Below query will test the connectivity of the linked server
BEGIN TRY
EXEC master..sp_testlinkedserver @ServerName
/*PRINT ‘YIPEE! THERE IS A LINKED SERVER OF SUCH KIND AND CONNECTIVITY IS THROUGH’ */ — Debugging statement, and the content of the statement could be changed appropriately.
INSERT INTO @LinkedSrvrStatus (LinkedSrvName, LinkedSrvStatus, TestComments) VALUES (@ServerName,1,’YIPEE! THERE IS A LINKED SERVER OF SUCH KIND’)
END TRY

– Checking Status and Sending Alert Mail to Team
BEGIN CATCH
PRINT ‘NO SUCH LINKED SERVER OR CREDENTIALS ARE INCORRECT!’
INSERT INTO @LinkedSrvrStatus (LinkedSrvName, LinkedSrvStatus, TestComments) VALUES (@ServerName,0,’NO SUCH LINKED SERVER OR CREDENTIALS ARE INCORRECT!’)
END CATCH

FETCH NEXT FROM LnkdsrvrCrsr INTO @ServerName
END
CLOSE LnkdsrvrCrsr
DEALLOCATE LnkdsrvrCrsr
END

/*Select linked server status from the table variable*/
SELECT * FROM @LinkedSrvrStatus

I hope this script helps someone out there.

Cheers!

Long running sessions

Hi,

One of the DBA’s in my shop wanted to know which query do I use for finding long running sessions in SQL Server 2005. Here is the one I shared, and often use while doing performance tuning (snipped from SSQA.net).

select r.session_id, s.host_name, s.program_name, s.host_process_id,
r.status, r.wait_time,wait_type,r.wait_resource,
substring(qt.text,(r.statement_start_offset/2) +1,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)/2)
as stmt_executing,r.blocking_session_id, r.cpu_time,r.total_elapsed_time,r.reads,r.writes,
r.logical_reads, r.plan_handle
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s
where r.session_id > 50 and r.session_id=s.session_id
order by r.session_id, s.host_name, s.program_name, r.status

I hope you guys could also make use of this script, and there are lots sitting here.  Thanks to SQL Master of SSQA.net for his efforts in writing these queries and I keep using one of these kinds quite often.

Cheers and enjoy!

Why am I not able to connect to SQL Server 2008?

Although I am little slow and late to the party of testing SQL Server 2008 features, I realized “Its better late than never!”.

Well, with this I hit into one of the first issues where in I had to connect to a SQL Server 2008 running on a remote (remote in here means a different box), and I was not able to. I was getting this error “Named Pipes Provider, error: 40 – Could not open a connection to SQL Server” constantly.  I was little confused and I went into analytical mode to understand the problem. Here is a quick list I did to ensure that I logon to this SQL Server 2008 instance.

1. Checked if I was able to ping the ip address of the host.

2. Check if SQL Server service is up and running. I usually don’t let any services start off automatically while I do installation, and I thought I had some company.

3. Tried checking if the option of “Allow Remote Connections” was ON. Well, here is the first hit I received where in I was searching for SAC (Surface Area Configuration Tool). In SQL Server 2008 this is discontinued, and to my surprise I couldn’t find pleasing reason to stop this tool. Now one will find it by Right Click Instance -> Click Facets -> New window with dropdown box of options where in one should choose Suraface Area Configuration. I was not comfortable using it, because I don’t have an exclusive tool to use this, instead everything looks pushed into one corner (“Facet”).  Now that is a different reason to grumble about.

To find out what is wrong while trying to connect I checked the configurations options under SAC and found everything looks good and set to go. But, what on the earth is happening that the instance is not letting me in from my machine?

Also here is a link which talks more about – Where is the SAC tool?

5. Checked the Windows Firewall status.

4. Checked the Configuration Manager to see the protocols that are enabled and disabled. And there it was, both the named pipes and TCP/IP were disabled. As soon as I enabled these protocols (its not necessary you will want to enable both to connect) I was able to get through the connectivity and I was set to start using the new instance.

There is an interesting blog entry that talks exclusively about the connectivity issues which you guys may want to look and keep it in handy.

Okay moral of the story or this exercise. I told myself its just common sense, that you couldn’t go and check the protocols instead you were running through many steps and spending away valuable time. Thank God, this didn’t happen in prod environment! Silly me, if I had to do these in prod boxes .  Sometimes, thinking at 20,000 ft level (which I tend to do often these days), I am forgetting my basics.

Happy reading, and cheers!

Running Profiler on SQL Server 2000

Sometime last week I was asked by one my of team guys, on how to run profiler on SQL Server 2000 without providing sysadmin rights to the login which would run the profiler. Well here are couple of thoughts I had shared with my teammate.

1. In SQL Server 2005 and SQL Server 2008 you could run the trace /profiler by providing explicit permissions to run the traces.  The syntax is as such

USE master
GO
GRANT ALTER TRACE TO username;
GO

2. In SQL Server 2000 this isn’t possible and one has to give sysadmin permissions to the user that would run the profiler. Unfortunately, this has implications on security issues on any environment because of the kind of rights that are given. So, I tried recollecting on one of the ways I tried few years ago. Here is what I responded back (with all due respects to the original idea I had borrowed from),

- Create a new user with strong password and give  sysadmin rights

- Run the profiler from a batch file with the command profiler /Sserver /Uuser /Ppassword /Tx

- The batch file had the user id and the password that was created for the profiler operations

- Converted the batch file into an executable so that the password is not exposed

To get the details of the parameters that could be included with the profiler, in the command prompt use Profiler /? and it would give you bunch of options.

I hope this type of stuff would help you guys too!

Where was I, last week?

I was out on attending Tech Ed 2009 at Hyderabad, AP, India. My feedback on the sessions is mixed, but a good and great thing that happened to me was that I could establish larger network with the SQL Server community consisting of industry experts, product teams, evangelists and others. It was a good experience where I learnt more on what is in the world of SQL Server on different topics. Soon you will see more posts on what I want you guys know about all I have learnt from the session.

So stay tuned!

Cheers!

SQL Server 2008 Developer Training Kit

Guys,

There is SQL Server 2008 developer training kit available for download here. The toolkit would be useful for who want to know about what could be done with SQL Server 2008 on development. Here is quick overview of what MSFT intends on providing this kit.

Overview and Benefits
The training kit offers the following benefits:

• Learn how to build web applications that exploit the unique features and capabilities of SQL Server 2008.
• Provides a comprehensive set of presentations, demos and hands-on labs
• Designed for web developers who are already familiar with SQL Server application development.
• Easy to download and install and works on free editions of SQL Server 2008 and Visual Studio 2008.

Intended Audience
The training kit is designed for the following technical roles:

• Web developers who build applications for the Microsoft platform.
• Microsoft evangelists, technical specialists and consultants.

Download and enjoy learning SQL Server 2008.

Cheers!