Rachit's Blog : SQL

Missing Microsoft.SqlServer.SmoEnum Dll

rachitp — Wed, 29 Jul 2009 16:12:09 GMT

I like this SSB (Sql Server Broker) Admin tool but every time I refer this to somebody they come back to me with the questions that some of the DLLs are missing and they can’t launch the application. You should be able to find all the missing Dlls on...(read more)

SQL Server Power Tools

rachitp — Fri, 06 Mar 2009 15:38:01 GMT

The SSMS Tools Pack is a “must have” for any developer who works with the SQL Server 2005 and above. I thank a lot Mladen for making this tool available. Go give it a try! http://www.ssmstoolspack.com...(read more)

Better error message please...SQL Error '...is not a recognized table hints option'

rachitp — Mon, 03 Nov 2008 21:29:00 GMT

I was getting the following error in a stored procedure that I had written. Nothing fancy in it as far as concern with using any new features of SQL 2005.

Msg 321, Level 15, State 1, Line 5

"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function,

ensure that your database compatibility mode is set to 90.

I googled about it and most of the places (like this) the answers are about checking the database compatibility. I looked at our database's configuration and everything seemed fine to me.

Then I realized that I had used the dynamic sql in the stored proc and between two lines of the sql statements, I had forgotten the "space" and that's why I was getting that error. How the hell on the earth that has to do with the error message above?

Why can't SQL management studio give better (correct) error message? Come on, am I asking for too much here?

Change Server Url in Email for Reporting Services

rachitp — Sat, 02 Aug 2008 11:24:00 GMT

When you schedule something to run in the Reporting Services and when the report gets emailed to the requestors, by default, in the email, you'll see http://servername/reportserver/report....

So what happens that if you are on the network (which is most likely if the reporting services is used for a corporate), you may get some DNS error that "servername" machine not found.

You can change the "servername" to full qualified domain name i.e. servername.domain.com

Go to your Reporting Server machine and do the following:

Open RSWebApplication.config (C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager) and change the value for key "ReportServerUrl" to "server.domain.com". Save changes.
Open RSReportServer.config (C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer) and change the value for key "Services/UrlRoot" to "server.domain.com/reportserver". Save changes.

That's it. I personally would restart the IIS server and the SQL Server Reporting Services under Administrator Tools/Services. YMMV.

Hope this helps somebody.

Learning SQL Server Broker

rachitp — Fri, 16 May 2008 23:48:37 GMT

Working on some project, I have started delving a lot with Microsoft SQL Server Broker for 2005. I can see that the initial learning curve is very very steep and it's very easy to give up on SSB initially. But if you are like myself, learning SSB, be patient. IMHO, the possible reasons of "not liking" SSB initially are as follows:

Not having a decent GUI to develop it. (We are used to VS.Net, here you have only SSMS (SQL Server Management Studio (you can use Notepad or TextPad) and .sql file)
No Project template/Wizard available (at least I don't know at the time of writing this) in SSMS.
Very hard to debug when you try to implement the SSB on the distributed (remote) servers, especially when you deal with stored procs within SSB.

Despite of all above reasons, there are compelling reasons to use SSB if you are looking for performance, queuing (alternative to MSMQ) and scalability.

I sincerely hope that Microsoft makes the using of SSB easier in the SQL Server 2008. Really...

In future, I'll write up a few articles about the configuration, security, etc. related to SSB. So, stay tuned.

Search in All Stored Procedures Views

rachitp — Thu, 27 Mar 2008 12:38:07 GMT

For me, the following SQL syntax turned out very handy when I wanted to see where either a table or a view or a stored procedure is being referenced. I've so many stored procedures in one of the database(used/created by other users) and it became hard to follow.

Hope this helps!

select * from sys.all_sql_modules

where definition like '%SearchText%'