SQL Tips by Namwar Rizvi

March 29, 2010

Search anything in your Database schema right from SSMS

In my one of earlier articles here I wrote about a way to find any value in any database column. Today, I found a great free tool, from RedGate, to find dependencies of any object. The tool “SQL Search” is available here. This can be used for impact analysis, improving the performance by replacing “Select *” with actual column name etc.

The best part is that it installs as an SSMS plugin, making it very easy to use it.

I recommend every SSMS user to give it a try.

August 31, 2009

How to Install SQL Server 2005 Client Tools?

Filed under: Setup,SQL Server 2005 — namwar @ 10:21 PM
Tags: , , ,

Further to my earlier post  SQL Server Client Tools Setup Fails due to MSXML 6 , in which I mentioned a solution for SQL Server Client Tools setup failure, if you are still unable to install them then following steps may help you.

  1. Navigate to \Tools\Setup folder of your SQL Server Setup CD
  2. Launch SqlRun_Tools.msi

It will launch the installation wizard directly and in 90% percent of the cases you will be able to install the client tools successfuly.

August 30, 2009

How to Query Active Directory (AD) from SQL Server

There can be a scenario where you will want to query Active Directory, directly from your stored procedure. This can be achieved by creating a linked server to your target Active Directory and then querying it through OPENQUERY functionality. Following is an example to achieve this:

Step 1: Add Linked Server for Active Directory

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Step 2: Query the Active Directory

SELECT * FROM OpenQuery
(
ADSI,   --Name of the linked server for Active directory, created in step 1
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn   --Fields you want to retreive
FROM ''LDAP://DC=TestDomain''    --Name of your Active Directory Domain
where objectClass = ''User'''         --Type of objects you want to query e.g. User, Person etc...
)

NOTE: The order in which columns will return will be exactly reversed of what you mentioned in your query. For example, in above query we have mentioned 
title, displayName…….,facsimileTelephoneNumber, sn but the resultset will return as
sn,facsimileTelephoneNumber……,displayName,title.
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.

April 13, 2009

SQL Server Client Tools Setup Fails due to MSXML 6

If you are trying to install SQL Server 2005 client tools and continuously getting failure message because of MSXML 6 installation which already exists on your machine then it may be due to the corrupted installation of MSXML 6 which blocks the installation of client tools. You can fix it as follows:

Go to Control Panel-> Administrative Tools->Add Remove Programs and try to remove it. If it removes smoothly then just install the Client Tools as before and you are done. 

OR

If you found that you are unable to uninstall the MSXML 6 because of some wiered error then you need a tool called Windows Install Clean up from here

Donwload it and remove the corrupt installation of MSXML 6. Once removed, you can now install the Client tools as usual.

April 5, 2009

How to create Custom Database Role and Manage its permissions

Filed under: How To,Query,SQL Server 2005,SQL Server 2008,TSQL — namwar @ 8:50 PM
Tags: , , ,

Custom database roles is the best way to manage object permission for the given database in structured way. Following script will demonstrate the steps necessary to create a new role and manage object permissions through it

--- Step 1 -------- Create Role ------------
USE [MyTestDB]
GO
CREATE ROLE [TestRole1] AUTHORIZATION [dbo]
Go

--- Step 2 -------- Assign Permissions to role  ------------
USE [MyTestDB]
GO
GRANT ALTER,EXECUTE, SELECT,INSERT,UPDATE,DELETE
ON SCHEMA ::dbo
TO TestRole1
Go

USE [MyTestDB]
GO
GRANT BACKUP DATABASE,BACKUP LOG To [TestRole1]
Go

--- Step 3 ---- Add user to our role --------------
USE [MyTestDB]
GO
EXEC sp_addrolemember N'TestRole1', N'SPUser7'
GO

-- ******  Create user *********************

--- Step 1 ---- Create Windows Login For SQL Server ------------
USE [master]
GO
CREATE LOGIN [TestDomain\SPUser7] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTestDB]
GO

--- Step 2 ---- Create Database User for our database --------------
USE [MyTestDB]
GO
CREATE USER [SPUser7] FOR LOGIN [TestDomain\SPUser7]
GO

January 15, 2009

How to use more than 4GB RAM on SQL Server?

SQL Server is an highly optimized and well tuned piece of code. It try to utilise Operating System resources as optimum as possible. Due to this, most DBA think that if they will increase RAM on the server then SQL Server will automatically start using it, right? Wrong, SQL Server can only use RAM more than 4GB if and only if,

  • You are running Enterprise Editions of Windows 2003 Servers orAdvanced/Datacenter version of Windows 2000
  • You have enabled Operating System to use the extra RAM you have installed. Please check here for the details and how to do it.
  • You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server
  • you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server

January 12, 2009

TSQL Function to convert decimal to Hex, Octal or any other base

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

Here it is:

USE [AdventureWorks]
GO;

IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
@val as BigInt,
@base as int
)
returns varchar(63)
as
Begin
/* Check if we get the valid base */
If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;

/* variable to hold final answer */
Declare @answer as varchar(63);

/* Following variable contains all
possible alpha numeric letters for any base
*/
Declare @alldigits as varchar(36);
Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

/* Set the initial value of
final answer as empty string
*/
Set @answer='';

/* Loop until your source value is greater than 0 */
While @val>0
Begin
Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
Set @val = @val / @base;
End

</code>

<code>/* Return the final answer */
return @answer;
End

January 3, 2009

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, you may need a quick script to find relationship between tables. I found a useful script to do that in a SQL Server newsgroup. With some minor update, here is the script which lists the name of the parent table, child table, Referred table, foreing key table and name of the foreign key constraint. I hope you will find it useful. All credit goes to original author.

Select

object_name(rkeyid) Parent_Table,

object_name(fkeyid) Child_Table,

object_name(constid) FKey_Name,

c1.name FKey_Col,

c2.name Ref_KeyCol

From

sys.sysforeignkeys s

Inner join sys.syscolumns c1

on ( s.fkeyid = c1.id And s.fkey = c1.colid )

Inner join syscolumns c2

on ( s.rkeyid = c2.id And s.rkey = c2.colid )

Order by Parent_Table,Child_Table

January 2, 2009

Two quickest ways to find file information of a given database

If you want to check the name of the files and their respective sizes for the given database then following are two quick tsql scripts:

1. Use sp_helpfile

This stored procedure returns the file information for the selected database e.g.

USE AdventureWorks;

GO

EXEC sp_helpfile;

GO

It will return the resultset similar to following

sp_helpfile

By default, this view returns a column called size but this is actually the size in 8KB pages therefore, to get the exact size in KB, as returned by sp_helpfile, multiply it by 8 as mentioned in above query.

January 1, 2009

Review of SQL Server 2005 Service Pack 3

Filed under: Information,SQL Server 2005 — namwar @ 8:58 PM
Tags: , ,

Microsoft has released the third service pack for SQL Server 2005. You can download it from here.

As many of us are still using SQL Server 2005 and has not moved to SQL Server 2008 yet therefore, I felt necessary to give you a breif overview of what has been offered in third service pack for SQL Server 200. Following is an outline:

1. SP3 contains all hotfixes for SP2 along with some new features.
2. If you are not ready to implement the SP3 yet then you can apply Cummulative update 11 for SQL Sever 2005 Service Pack 2 available here
3. DBCC commands will display all error messages in SP3 in contrast to SP2 where you are only able to see first 200 messages if you do not specify ALL_ERRORMSGS option. In SP3 ALL_ERRORMSGS option does not have any meaning.
4. Notification services (which has been discontinued in SQL Server 2008) of SQL Server 2005 will work with both SQL Server 2005 and SQL Server 2008 database engine after applying SP3
5. There is a small update in Replication area i.e. a stored procedure sp_showpendingchanges now has one additional parameter @show_rows. This is a flag which allows you to specify two additional columns in the resultset for more detailed information. Further information is available here
6. There are three updates in Reporting Services area:

* Report models can be created for Terra data databases. Further information is available here
* PDF rendering extension can now embed the font to display the document correctly on a computer where document fonts are not already installed. Further information is available here
* Sharepoint integrated mode has been optimized by reducing the number of calls to Sharepoint object model. Reports will render faster now.

I hope this overview will give you a brief idea about what is new in SP3.

Happy New Year to all readers and best wishes for 2009.

Next Page »

Create a free website or blog at WordPress.com.