Contact Me – Archive 5

Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1200 articles on the subject on his blog at He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.

I am proficient in Corporate Training. I have designed and implemented complex database architecture, and have also implemented strategies for database high availability and scalability. Furthermore, my core expertise lies in query tuning and performance optimization.

If you want to seek my expertise then drop me a line and tell me about your requirements by using the form below or send me email pinal “at” I value development community and will be happy to help you at any stage of project development, from design to deployment.

Copyright violation and Reproduction of blog: is trademark of Pinal Dave. Exact work “SQLAuthority”  or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave.

If any article published on this blog violates copyright please contact me, I will remove it right away.

Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, which ever is maximum in event of linking to blog (Link is must).


If you have any questions for faster response, Search It is possible that your question is already answered in one of the hundreds articles.

Community Rules

  • Do not post obscene, indecent, hateful, offensive, defamatory, abusive, harassing or profane material. They will removed.
  • Do not post junk mail, pyramid schemes, chain letters or advertisements.
  • Do not engage in personal attacks. We have zero tolerance for such incidents.
  • Vulgar and insulting nicknames will be removed. Nicknames attempting to impersonate other users will be removed.
  • Do not post anyone’s telephone number, street address, email address. or any other personal information.
  • Do not post copyrighted material.
  • Lively debate and opposing opinions are welcome, but please behave courteously.

pinal “at”
pinaldave “at”

258 thoughts on “Contact Me – Archive 5

    • HI,

      What is the best way to use unique value (for PKs) across the tables (must be unique in whole database) other than GUID/Uniqueidentifier. In the problem, I need Bigint as PK for all the tables and there is also a requirement to set it as default value for some columns (as like we use NewID()), so how it can be used in default values also.

      Please suggest,



    • Dear Pinal!
      Firstly, I just would like to say thank you so very much for this wonderful website! The tips and code segments I here found here helped me very often.

      I have a question: I want to get the drop code of a database object programmatically. This is not an option for me, since I have to do it programmatically.

      I’m aware that one can do this by right-clicking on the database object and selecting Script as -> DROP and CREATE To -> New Query Editor Window.

      Another way of doing it is to right-click on the database one is working on and then selecting Tasks -> Generate Scripts, and then selecting which database object you want to create scripts for. Once again, this is not an option, since it requires manual interaction.

      I know that when you use
      select object_definition(object_id(‘DBObjectName’)),
      you’ll get the actual code that creates the object, but it does not include the if exists(‘DBObjectName’)…drop(‘DBObjectName’) part.

      1. How do I get this extra if exists(‘DBObjectName’)…drop(‘DBObjectName’) part?

      2. Also, I want to get the definition query that creates tables (programmatically since the query
      select object_definition(object_id(‘DBObjectName’))
      cannot be used on tables, there must be another way that be used to get the table definition query. What query should I use?

      Any reply/replies will be very much appreciated.

      Thanks so much in advance!

      Kind regards


    • I have a production DB of 950 GB. It has been reporting I/O’s taking more than 15 seconds error. I tried everything . The drives are set up perfectly log and data different drives and temp is on its own RAID. It has 32 GB RAM, 2.5 TB disk space, 4 quad core processor sitting on a G5.
      Microsoft tracked it down to cluster size of the disk leading to misalignment in the HDD. Is there a comprehensive way to determine the stripe unit size and determine the appropriate cluster size of the HDD for sql data and log file.


    • If you used transaction you can rollback it

      Otherwise depends on the recovery model of the database you can get it from a log file

      If you have latest back, you can get from it too


  1. Hi Pinal,

    When we creating a cluster index and drop that index significantly reduced the unused space in tables .After doing this similar to all tables, database performance will increase or decrease ?.

    please explain me


  2. We are using SQL Server 2008 in our company, and facing a problem with GROUP BY GROUPING SETS

    The data are like,
    CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
    INSERT Sales VALUES(1, 2005, 12000)
    INSERT Sales VALUES(1, 2006, 18000)
    INSERT Sales VALUES(1, 2007, 25000)
    INSERT Sales VALUES(2, 2005, 15000)
    INSERT Sales VALUES(2, 2006, 6000)
    INSERT Sales VALUES(3, 2006, 20000)
    INSERT Sales VALUES(3, 2007, 24000)

    Now when I execute following query,
    SELECT EmpId, Yr, SUM(Sales) AS Sales
    FROM Sales

    It gives me following error,
    Incorrect syntax near ‘SETS’.

    I know I can use UNION ALL, but curious to know what is the problem with GROUP BY GROUPING SETS

    Will appriciate any help, thanks….


  3. i am facing a problem with join and max(lenght). Need a help to get a good perfomance in sql server .
    I have a table with phone calls logs to different countries and cities and i want to add a column with country name and city name depending in the called number.
    For example:
    Table 1
    00:12:56 346783049056
    01:45:23 344556578865
    02:12:45 3469I5354435
    04:34:34 336765757575
    05:23:23 3312369O3404
    06:34:34 3244123131335
    08:34:43 3220O4245453

    table 2

    Code—– Country
    34 Spain
    346 Spain mobile
    33 France
    336 France mobile
    32 Belgium
    324 Belgium mobile

    i need a join table that will give the calls with the exact country name, for example for the number 346783049056 the country name can be Spain and Spain mobile but 346 is correct.
    I try using self join and correlation using the length of the country code but performance is not good because my table1 have more than 1 million called numbers and table2 have the codes of all the world

    This the tjoin that i need from my example
    Time_stamp—Called_number— Country
    00:12:56 346783049056 Spain-mobile
    01:45:23 344556578865 Spain
    02:12:45 3469I5354435 Spain-mobile
    04:34:34 336765757575 France-mobile
    05:23:23 3312369O3404 France
    06:34:34 3244123131335 Belgium-mobile
    08:34:43 3220O4245453 Belgium

    i will appreciate any help


    • Fangu,
      Dont’ you have fixed characters that represent Country code ? In your case, you have CountryCode varying in length for Countries. E.g, 2 chars for Spain, 3 chars for Spain Mobile..
      Please clarify your business requirement


  4. Hi,

    I want to add a days into date by excluding saturday & sunday in a single query, I know it can be done by function but i want in a single query whether it would be possible…

    Kindly confirm

    consider daye is : 15-Dec-2009
    days :10
    output : 29-Dec-2009



    • Use this

      declare @input_date datetime
      set @input_date='15-Dec-2009'
      select max(dates) as date from
      select top 10 dateadd(day,number,@input_date) as dates from master..spt_values
      where type='p' and number >0 and
      datename(weekday,dateadd(day,number,'15-Dec-2009')) not in ('saturday','sunday')
      order by 1
      ) as t


  5. Hi

    Hi Pinal,

    I am ashok i want mirror concept through wizard(i configured through wizard sucessfully but when i stat mirroring its getting error) pls give me some suggestions


  6. Can u please send the best solution

    HOW TO MovE the all user databases .MDF & LDF files from (Default location )C:\ PFiles\Mssqlserver\ ….drive to D:drive in the tstsqlserver



    • A very nice example is there in BOL. Pasting the same below –

      USE master;
      ALTER DATABASE AdventureWorks
      NAME = Test1dat2,
      FILENAME = N’D:\t1dat2.ndf’

      Stop SQL Server, copy the files to the location specied. Start SQL


    • Another way of doing this is by right-clicking on the database you want to move (in SQL Server Management Studio) and then selecting “Tasks”->”Detach”.

      After moving the database files to the desired location, right-click on “Databases” (also in SQL Server Management Studio) and then selecting “Attach”.

      This way, you can move the database without the need for stopping and starting the SQL database engine.

      Have a great day! :)


  7. Hi Pinal,

    I am unable send attachemts from dataserver but without attacment can send mails.The problem arises only when there is an attachment. Same works fine in all the other servers with same configuration including the dbmail config.I am able access the same path with XP_CMDShell dir command.Any help would be greatly appreciated.

    sql server error:

    Msg 22051, Level 16, State 1, Line 0
    Failed to open loopback connection. Please see event log for more information.

    Event Log:

    The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user ‘ABC-SQL\sqlaccount’..


    I learnt lot of DBA stuff from your blog, it is amazing that you have experstise in almost all the areas of sql server.
    You do a great job helping DBAs like me and almost for all the issues i could find soultions from your blog.

    Thanks in Advance.



  8. Hi Pinal,

    I need to monitor a lock issue and is required to monitor blocked sentence, blocking sentence and duration as well, to realize this I try to use profiler but our hosting provider deny all remote access to the database, when I start profiler on the database server general immediately performance falls to an unacceptable level, any advice would be highly appreciated.

    Thanks & Regards,


    • Hello Gustavo,

      If you want to log all the details of deadlocks then best is use trace flag 1205 & 3604. Otherwise you can manually or by job, execute the below script to know blocking & blocked spid and statements:

      select t1.request_session_id as [Waiter spid], — spid of waiter
      t2.blocking_session_id [Blocker spid],
      db_name(resource_database_id) as [Database],
      t1.resource_type as [Resource type],
      case t1.resource_type when ‘OBJECT’ then object_name(t1.resource_associated_entity_id)
      else t1.resource_associated_entity_id end as [Blocking object],
      t1.request_mode as [Lock req mode],
      t2.wait_duration_ms as [Wait time],
      r.text as Waiter_batch,
      (case when exr.statement_end_offset = -1
      then len(convert(nvarchar(max), r.text)) * 2
      else exr.statement_end_offset end – exr.statement_start_offset)/2) as Waiter_stmt,
      p.text as Blocker_batch,
      (case when blk_exr.statement_end_offset = -1
      then len(convert(nvarchar(max), p.text)) * 2
      else blk_exr.statement_end_offset end – blk_exr.statement_start_offset)/2) as Blocker_stmt
      sys.dm_tran_locks as t1,
      sys.dm_os_waiting_tasks as t2,
      sys.dm_exec_requests exr
      cross apply sys.dm_exec_sql_text(exr.sql_handle) as r,
      sys.sysprocesses sp
      cross apply sys.dm_exec_sql_text(sp.sql_handle) as p,
      sys.dm_exec_requests blk_exr
      t1.lock_owner_address = t2.resource_address
      and exr.session_id = t1.request_session_id
      and sp.spid = t2.blocking_session_id
      and blk_exr.session_id = t2.blocking_session_id

      Pinal Dave


      • Hi Pinal,

        it wud b very helpful for me if give information about how to do Microsoft certfication.
        I have 4 yr Exp of (C#). Pls guide me .



  9. Hi Pinal,

    I am creating an SSIS package to import CSV file to a SQL table. The main problem is that the number of columns in this CSV file varies ( so i need to create table every time i need to import the file ) i.e. today there can be 5 columns tomorrow the number of columns may be 10. Is it possible to create the table as per the CSV file. Please help.



  10. Hello Rupesh,

    Instead of desiging a complex package to load a table of varing number of columns and alter table according to the source data, you can create a simple package to load all data from csv into a single column of a temporary table. Then split the data usgin substring or other customized functions and load into destination table. In t-sql this can be done by much less effort.

    Pinal Dave


  11. Hellow Sir,
    I want to use index.How can i get max(value) of column- Transno which is present in both tables :- table1 and table2

    Thanks in Advance


  12. Please let me know

    how to write a function to take input from user as integer and give an output as the number is even or odd

    Input 4 Output Even
    Input 5 Output Odd


    • @Ajay

      DROP FUNCTION Is_Even;
      RETURN CASE @A % 2 WHEN 0 THEN ‘Even’ ELSE ‘Odd’ END

      SELECT 4, dbo.Is_Even(4) UNION ALL
      SELECT 5, dbo.Is_Even(5);


  13. Hi Pinal.

    I have one question please clarify this. Is it wrong to declare a variable like DECLARE @@Sample AS NVARCHAR(50).

    I tried this in MSSQL 2005. I didn’t get any error. But i hope this is wrong but don’t know how ?

    Thanks & Regards
    Mohan Dass.


  14. Hi, i am new to sql server. i have just installed sql server 2005 and its giving

    ” TITLE: Connect to Server

    Cannot connect to MSSQLSERVER.


    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

    For help, click:





  15. I have installed default instance and if i run same settings on mindows xp it works fine. but on windows 7 they show error. now i along with 2005 i have also installed sql 2008, its showing same error but if i use server name: (local), then its connecting to database engine in sql 2008


  16. Dear Sir

    First, I want to say u as “Thanks a lot”. I downloaded SQL’s questions and answers from your web site. Sir, may I contact and to get guideline from you for a good SQL professional. I ever enter and see with u by online if you free time.


  17. HELLO Mr.DEv
    I have a Question
    I have 3 tables TECTA,Elearning and Curriculum
    I have a PERSON table in TECTA
    PersonID is a primary key in this table.
    My Boss said this primary key is used as a foreign key all these 3 databases
    I want to update the foreogn key tables whers Primary is equal to foreign jey.
    I can update the foreign key tabkles in tects by using your advice. thank u so much for that.
    COuld u help me like how can I update in other databases also at the same time.
    WITH CTE (updatePersonId,persondupeId,duplicateCount)AS
    SELECT personID AS updatePersonId,MIN(personID) OVER(PARTITION BY FirstName,LastName,MIddleName)AS persondupeId,
    ROW_NUMBER() OVER(PARTITION BY FirstName,LastName,MIddleName ORDER BY FirstName,LastName,MIddleName)AS duplicateCount
    From V_DuplicatePerson
    select updatePersonId,persondupeId into tempdupedata from CTE Where duplicateCount>1 AND updatePersonId!=persondupeId

    Please provide solution for this . thank u om much for ur help.
    I apprciate it.


  18. After a power outage, our SQL Server will allow Windows authentication but not SQL authentication. The server’s property is set to Mixed Auth mode and the registry reflects Mixed Auth … but the system administrator login does not work (or any other SQL authentications)… so the question is why? … and how can this be fixed?


  19. hi,
    We have MSSQL2000 STD ED server having 175 databases for MIS purpose, we need to migrate all these databases to MSSQL2005 STD ED, what is the best approach, besides manual backup restore?


  20. Hi Pinal,

    I was implementing the Data Auditing Feature in SQL Server 2008 and have a very Basic doubt “What does a Database Principal Name mean and how is it decided for a Server Principal Name” .

    If you see a row in the Auditing Log ,It has Database Principal Name, Session Principal Name and Database Principal Name. What does these mean and what is the clear distinction between them.

    Thanks Pinal in anticipation


    • This is the example code

      declare @input_date datetime

      set @input_date='01-Dec-2009'

      select count(dateadd(day,number,@input_date)) as no_of_sat_sun from master..spt_values
      where type='p' and number >=1 and number<day(dateadd(month,datediff(month,0,@input_date)+1,-1)) and
      datename(weekday,dateadd(day,number,@input_date)) in ('saturday','sunday')


  21. Pinal,
    I did a few different searches but couldn’t find anything associated with my question I have. Currently we have a few SQL 2005 servers that we are going to retire. We are getting new servers that will have SQL 2008 Enterprise installed on them. My question is this. Is it better to backup on the 2005 servers and then restore on the 2008 servers, or to script all the database objects and then use SSIS to copy all the data from all databases\tables over? We’re looking at about 35 total databases with only a few having more than 2gb of data.

    Any direction would be greatly appreciated.


    • Hi Mike,

      To migrate database, backup-restore is the best option with least overhead. Copy Database task in SSIS is also good option that also not need scripting of obejcts.

      Pinal Dave


      • Hello Mike,

        Just to add 2 cents to what have been said, I would recommend you to first run Database Upgrade Advisor before you move your database to higher version.

        This comment has nothing to do with your question directly, but indirectly it is still connected to your question. In any of the method you do, either by backup restore or inplace upgrade, I doubt if you will be prompted of any upgrade / compatibility issue during migration process.

        So better check the compatibility issues before you actually migrating databases.

        ~ IM.


  22. I want to Implement Live Database like if Someone Use My Sql Server or Any Entry inserted or Deleted or Any transation done on My Server Same Time it Will be Mail Me isn’t it Possible then Guide me How to Configure?????


    • Hi kalpesh,
      Which Sql Server are you using????If its Sql Server 2008 you can implement a data Auditing Feature that will log all actions by anyone and then you can create a job on the log file created to notify you by mail based on the frequency you decide.

      This is just an idea of implementing it, may be someone else can propose a better idea.


    • Hi Kalpesh,

      On a live database where many processes run that performs many update and changes. Firing mails on each n every change not seems practicle. If you explain your requirement in more details than we would be able to provide you better resolution.

      Pinal Dave


  23. Hey Dave,
    You are Really Great,No Words to Express your Service.
    and i’m just wondering if you can give me the Link where i can access to some Video Tutorials or any other Presentations so it will be Great help full to us.

    As i knew many people got benefited by you.
    you created lifes to many people.

    Keep it up

    and Heartful thanks!


  24. I am new to SQL and trying to figure out how the “nn%19759+1”
    is changing the result returned from the select statements listed below.
    Will appreciate any clue that help me understand the logic behind the query returning 3231 & 12542



    select GETDATE()
    = 2009-12-26 10:10:02.323
    select DATEPART(ms, getdate())
    = 323
    select DATEPART(ms, getdate())*10%19759+1
    = 3231
    select DATEPART(ms, getdate())*100%19759+1
    = 12542


  25. Hi Dave,
    Thanks for the explanantion, so what about the 19759, does it represent any thing special that you know of, (the number is used in reference material and code examples on Technet)




  26. Hello Sir,
    I have a situation where i need to create a batch file which will run at every 5:00 Pm and take a backup of my sql database and will keep it in my local hard disk.
    Please help me with the query that is to be run on command prompt which will take the backeup from say

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

    and paste it into

    D:\SqlBackups folder

    Thanks & Regards


    • Type these two lines in Notepad and save it as:


      ECHO This Script duplicates the Database

      COPY C:\LogData.mdb C:\Dup_LogData.mdb

      The first line is ignorable.

      I have used Scheduled Tasks utility found in Windows. Follow this:

      Start -> Programs – > Accessories -> System Tools -> Scheduled Tasks

      You can schedule a task / prgoram and it will execute at whatever time you choose. Choose Daily and then at the end of the Wizard you select Advance Settings from where you can select the exact time that you want your scrtipt / batch file to be executed. This batch file will copy the database file with the named duplicate file.

      Apply it in your case!


    • Hi Sumit,

      Do one thing, just paste the following in notepad and save as a batch file….

      Copy from here……………….
      @echo off

      Rem Taking the backup of your database
      set var=%date:~10,4%-%date:~4,2%-%date:~7,2%_%time:~0,2%-%time:~3,2%
      rem echo The variable is “%var%”

      rem md D:\Backup\%var%\

      osql -e -S Manish\SQLEXPRESS -U SA -P manishpassword -Q “Backup DATABASE Yourdatabase to DISK = ‘D:\Backup\DB%var%.bak’


      till Here ……………….

      Please note
      1. Save the above script in a batch file and simple execute once from command prompt, you can find the backup in D:\Backup\ folder. if this works properly you can schedule it by Start -> Programs – > Accessories -> System Tools -> Scheduled Tasks

      2. Manish\SQLEXPRESS is my sql server name and instance.

      3. manishpassword is my password, you can use yours.
      4. Yourdatabase is the name of your database.

      Please let me know if you face any more issue.


    • Dear Sir,

      I am actually new to sql server. I ‘ll be gratefull if you guide with me the full cmd prompt or xp_cmdshell query.

      Thanks & Regards
      Sumit Thapar


  27. Hi,

    Is there any way to find out

    The list of tables including their create date, row count, column names and keys. I can get these info by looking at each table and its property. But I want them all on one screen.



    • Hi Abdul,

      you can use this query in SQL 2008, SQL 2008

      select * From sys.tables

      and for the previous version you can do by

      select * From sys.objects where type=’u’



  28. Hi Dave!

    I just came across your blog and found it very useful. I have created a Windows Forms Application in VB.Net (using Visual Studio Pro 2008). It my project which I have to submit for my M.Sc. IT final semester.

    Well, to come to the main problem, I have created a database and a table in SQL Server 2005. I need to create a Setup of the project which must be able to set up the database on any machine along with the application itself. (The setup is running fine though on my system.)

    I think I need to attach the database programatically or create it on the fly when the application is installed. I don’t know how to either task.

    Can you help me with it? However, somewhere on your blog I have read an article on it and will try it. But I need a clear-cut solution which I can apply to my present problem.

    One more thing, will a user need to install SQL Server Express Ed. 2005? Will it alone do (along with the .Net Framework and Windows Installer)?


  29. Hello Dev,

    SQL Server is backend for any client-server application and installation of SQL Server instance can not be included into an application setup. You will have to install the server and create database saperately.

    Pinal Dave


  30. Dear Sir,
    I have a situation where i need to give a user multiple privileges. Privileges are stored in asp List box control and every privilege has a value associated with it. admin selects multiple privilege values for a user.

    Now in the database there is a table which has structure like UserId,PrivilegeId.
    for a single user there can be multiple Privileges stored.
    Now what i want to do is i want to send all the privileges separated by any character Value, create it into a string and then send this string as parameter to sql stored procedure. the idea was to use like the SPLIT function in that returns an array after splitting the string and then one by one insrt the valuesinto that table. i cant find any split function in sql server. nor i want to send an xml file to the database.

    Please Help


  31. Hi Pinal,

    I need to use sp_addlinkedserver to link two servers for fetching data from two different databases on diff. servers.

    So, that can design the report in reporting services.
    Could you please throw some light on the syntax and arguments as well.



  32. hi madhuri,

    –sp_addlinkedserver ‘OrcleBaan’, ‘Oracle’, ‘MSDAORA’, ‘BAAN’

    –OracleBaan is linkedServername
    –Oracle is ProductName
    –MSDAORA is Providername
    –BAAN is SQL*Net

    –sp_addlinkedsrvlogin ‘OrcleBaan’, false, null, ‘baandb’, ‘baandb’
    –OracleBaan is LinkedServerName
    –baandb userid
    –baandb Password

    sql for fetching data
    SELECT * from openquery([ashish\sql2005],
    ‘SELECT [eno]
    FROM [Replication].[dbo].[emp]’)

    —shish\sql2005 is a logical name linked server .


  33. Arun,
    Many thanks for your reply.
    I am using MS SQL, so which syntac will help me to pull the arguments required :
    @srvproduct = ] ‘product_name’ ]
    [ , [ @provider = ] ‘provider_name’ ]
    [ , [ @datasrc = ] ‘data_source’ ]
    [ , [ @location = ] ‘location’ ]
    [ , [ @provstr = ] ‘provider_string’ ]
    [ , [ @catalog = ] ‘catalog’ ].

    I have the server name its “psswarehouse”, but how to get the other details ?


  34. Madhuri,
    Try this,

    Use master
    EXEC master.dbo.sp_addlinkedserver @server = N’LinkARUN’,
    @srvproduct=N”, @provider=N’SQLOLEDB’,
    @datasrc=N’Arun’, @catalog=N’tabreed1′

    datasrc is the name of sql server,catalog is the name of data base .

    dont forget to add
    sp_addlinkedsrvlogin @rmtsrvname =
    , @useself =
    , @locallogin =
    , @rmtuser =
    , @rmtpassword =

    u can do this by sql server management after adding link server



  35. Madhuri,

    Use master
    EXEC master.dbo.sp_addlinkedserver @server = N’LinkARUN’,
    @srvproduct=N”, @provider=N’SQLOLEDB’,
    @datasrc=N’Arun’, @catalog=N’tabreed1′

    @datasrc = N’psswarehouse’
    @catalog = Name of database which u want to connect.

    dont forget to sp_addlinkedsrvlogin after connecting.
    U can do it from Sql Server management



  36. I have database A with multiple tables, and I want to copy the content of Table T1 into Table T2 in Database B. There are million records. From T2 to Table T3 on Database C there is transaction replication.

    So its T1-T2 – Insert into select * ……… 4 hours
    T2 – T3 – Transaction replicaiton. – 4 hours..

    What is the best option to reduce the time…

    Thanks in advance for your help.


  37. Hi,
    I am trying to set up a network Access DB on a mapped drive as a linked server in SQL 2008 and am getting the ‘Microsoft.Jet.OLEDB.4.0 for linked server…… is not a valid path’ message when I perform a ‘Test Connection’.

    Steps I have taken – set the path variable on the SQL Server to C:\Temp and set security to allow everyone full access, set security on the access database and containing folders (at the share level) to everyone with full access. Set the SQL Server service to run using my login (full network admin). Set SQL Server to use SQL Server & Windows authentication. Made a copy of the access db in C:\ on the SQL Server and set up a test linked server to this mdb.

    When I use SSIS on the SQL Server I can access both the local mdb on C: and the network version. This is logged on to the server using my own login and logging in to SSIS using Windows authentication.

    When I try this on my workstation using SSIS I can access the version on the SQL Server C: drive but not the network version.

    I need help with any steps I might be missing or what else I could try.

    Thanks in advance for any help.



  38. SELECT hirer.file_no,hirer.hirer_name,Starting,Closing,Mode,hirer.instalment as Due
    FROM hirer inner join employee on hirer.emp_code=employee.emp_code
    day(starting)in(select day(demand) from dbo.demand_range(’12/12/2007′,’12/20/2009′)))

    here i have used a function demand_range which only gives all dates between two dates

    day(starting)in(select day(demand) from
    the above compares if the day of starting date is the day of date generated by the function

    Now i want to select that date where the ist day matches with the date generated by function
    Please help me itz urgent


    • @feroz

      It might be easier to change the statement to use BETWEEN:

      WHERE starting BETWEEN ’12/12/2007′ AND ’12/20/2007′

      Though, this is different than what you have because it also checks month and year. To only check the day:

      WHERE DAY(starting) BETWEEN 12 AND 20

      From there it should be easier to match the new request.


  39. Dear Sir,

    I want to export data from SQLSERVER 2005 to Excel at some destination point as below, but when I pass parameter in @query variable, it is not working and showing message near to this line “where region=”’+ @region+”

    Pls solve this thing

    delcare @region char(5)
    select @region=’abcd’

    EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance.xls’,

    @query = ‘select *
    from cust_tmp
    where region=”’+@region+”’ /*problematic part as */
    order by region,cust_name’


      • create table cust_tmp
        ( cust_no char(5),
        cust_name varchar(50),
        region char(5)
        insert into cust_tmp values(‘c1′,’abcd’,’abcd’)

        insert into cust_tmp values(‘c2′,’abcd’,’abcd’)
        select *
        from cust_tmp

        declare @region char(5)
        select @region=’abcd’

        EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,

        @query = ‘select *
        from cust_tmp
        where region=”’+@region+”’/*problematic part */
        order by region,cust_name’

        –It Shows error message “Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ‘+’.

        /* If I used the following scripts it Works, I used variable @a and assign select statement into @a and after that
        I put it in front of @query. It work . but when I write select statement into @query. it doesn’t work */
        declare @region char(5),
        @a varchar(200)
        select @region=’abcd’
        select @a=’select *
        from cust_tmp
        where region=”’+@region+”’/*problematic part */
        order by region,cust_name’

        select @a
        EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,

        @query = @a

        –It works

        –Pls solve this issue.


  40. Sir,

    How can I compare uniqueidentifier in where clause of select statement.

    I am using this select statement in sp so I get an error.

    select Name From Area where ID = tablename.uniqueid

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier “tablename.uniqueid” could not be bound.

    Thank you.


  41. Dear Sir
    I have client application that use Sql Server as Database and use C# for Interface.
    my problem is that I dont want to see any person my database data and i want to install it on the one client and no have server.
    in Sql server Enterprise and developer edition has TDE or Encryption database but Express edition no have.
    my qusetion is :
    1- can i create encryption database in my computer and use it in clien in express edition (in client just have SELECT and no INSERT no UPDATE no DELETE) ???
    2- can i use developer edition in client in my package as database ?????
    my perpose just no want can any one see my data


  42. Hello Ali,

    TDE is to encrypt backup only, not to encrypt data in online database.
    To encrypt the data you will have to incorporate the code for encrypt (while storing) and decrypt (while retrieving) in C# application.
    The best method is through controlled authentication and authorization. Allow access only to valid users and grant permission only for limited objects and activities.

    Pinal Dave


  43. hi

    i want to retrieve the last 20 rows from the result set
    i can not use top 20 order by desc

    order by has to be asc.

    my query

    $sql2 = “select top 20 employeeid,employeename from hr_employee where isthere = 1 and dateofleaving >= ‘”.$month.’/01/’.$year.”‘ and dateofjoining <=
    '".$month.'/'.daysinmonth1($month,$year).'/'.$year."'".$department." and Employeeid < ".$prevempid." order by employeeid asc";


  44. Hi Pinal!
    I’m dba junior and i have problems when i want restrict permissions and access to our production dabatabases.

    This restriction will be to the developers, because then spoil the server configuration.

    I created a rol where i give to them only view definition and execute, but i have many problems when the want make some changes to the aplication’s sp.

    Plz help me with the restrict the permissions to the developers.

    I’ll be very thankful.

    Pd. SOrry wheter my english is bad.



      • Hi Brian, the issue is how control to the user (specifically developer), i can give to them execute and view definition, in each sp, view table, funtion; for each user and object in the database (With the Datatipe user access problem ).

        I want avoid this using a group.

        I do it this process making a rol, and i granted permissions(exec and view def.) for each object through a script (Type ) to this rol, all this under a schema created for this rol, later i added to the developers to this rol.

        I want know wheter this metod than i created is correctly, or if exist a correct metod, because the objects and the users quantity in the database is very highly, because i has and have problem with my metod.




        • @Jim,

          Roles with Higher permissions will over ride lower permissions.

          What I would suggest is the following

          1. See if the users attached to this new role are also member of any other role that has high privileges, if this is the case then you need to review this again.

          2. Make use of impersonation account,
          Execute as login = ‘developer_login_name’ and see what privileges does that user have.

          3. check permissions for the users,
          sp_helprotect @username = ‘name of the user’

          ~ IM.


  45. Please let me know how we can partion on day basis. And delete partition on daily basis.

    1) We have one current old data base which has 59 un-partitioned Tables with 300 days of data.
    Now we need write a script to make a new Database with same table definition but Portioned on txn_date(one partition a day) and load the data from current DB.
    We have txn_date field as date field in all tables.
    We are starting from txn_date from 01 Aug 2009 to create partitions on each day and name the partition as partition_MMDDYY to till today.
    So that we will have almost 180 partitions by the end of January 2010.
    2) After that we need to create 4 weeks of empty partitions i.e. 28 partitions from February 1st 2010 onwards.
    Same name standard: partition_MMDDYY.

    3) After one week we need to delete and drop all the partitions that are older than 180 days. (partion_MMDDYY) and REUSE the table space
    And we need to create one week of daily partitions.



    • Hi Ramesh,

      You can partition your table on daily basis. For that you need to create a partition function and a parition scheme. This partition function can be used for your all tables.
      To automatically create one new parition and delete the oldedst parition on daily basis, you need to implement the Sliding Window Partitioning that is explained with example here:

      But one table can be partitioned on the basis of one partition scheme. So you need to decide one partitioning period either daily or weekly.

      Pinal Dave


  46. Hi Pinal
    you say :
    Hello Ali,

    TDE is to encrypt backup only, not to encrypt data in online database.
    To encrypt the data you will have to incorporate the code for encrypt (while storing) and decrypt (while retrieving) in C# application.
    The best method is through controlled authentication and authorization. Allow access only to valid users and grant permission only for limited objects and activities.

    Pinal Dave
    First Thank you
    but I after sales my application (in package with sql server)
    i dont have any control over sql server
    because sql server instal in destination Computer (not on network)
    and i remember that sql server has sa User and any one can create user with all facilities and can use this user to modify or see my database !!!
    every application sales to one person and not support network and just instal in one pc or computer i want that no body can use my database
    can i do that???
    thank mr Pinal


  47. Hi Pinal,

    In SQL server error log only spid’s with datetime and event occured are maintain as history. How TO get loginname of that spid’s so dba can know which login user has done that event.

    Also, If Deadlock occur in night and if DBA was not present at that time, How can DBA know that Deadlock as occured and from which table/stored procedure/query ?



  48. Helo Parkshit,

    More details are recorded by default trace. The path of log files created by default trace is “C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Log\”

    To get the details of all deadlocks start the trace 1204 and 3605. After switching on these traces complete details of all deadlock will be recorded in notepad file.

    Pinal Dave


  49. Hello Sir,

    I got this error when i m creaing sdatabase on my laptop.
    i m using vindow vista on my laptop …

    Msg 262, Level 14, State 1, Line 1
    CREATE DATABASE permission denied in database ‘master’.

    Plz help me short out this problem…



  50. Hello Sir,

    I got this error when i m creating database on my sql server 2005.
    i m using Window’s vista ultimate.
    i dont have any service pack, is that required ??

    Msg 262, Level 14, State 1, Line 1
    CREATE DATABASE permission denied in database ‘master’.

    Plz help me short out this problem…



  51. Hi Pinal,

    In many books (even in your SQL Server interview question guide) , I have seen that it is mentioned

    “Delete is DML Command and can be rolled back. Truncate is DDL command and cannot be rolled back”.
    I know that for Oracle, the statement holds true but i feel that this is not case with MS Sql server 2000 or higher versions.

    My observation with MS Sql Server is:

    In SQL Server, by default AutoCommit is On.
    So without explicit transaction neither delete can be rolled back nor Truncate command.

    Within a transaction both Delete and Truncate can be rolled back.

    So where is the difference between the two commands?

    Request you to correct me if my understanding is wrong.

    Thanks in advance,


    • Hi Pramod,

      You are correct that:
      without explicit transaction neither delete can be rolled back nor Truncate command.
      Within a transaction both Delete and Truncate can be rolled back.

      The difference are in functionality like:
      we can use WHERE clause with delete but not with truncate.
      Truncate reset the identity but Delete not.
      Truncate does not fire the trigger (on delete event) but Delete does.
      Truncate can be used if table is referenced by other foreign key tables but Delete can be used.

      Pinal Dave


      • Hi Pinal,

        I agree with all other.

        The only reason for asking the question “Delete is DML Command and can be rolled back. Truncate is DDL command and cannot be rolled back” is that I have practically seen that this is not the case with SQL Server.

        But when an expert (yourself) is saying that, it made me think that I am missing some information and i should post a question and enhance my knowledge.



  52. Dear Pinal!
    We need to maintain history of changes made in data and that too applying some business logic and it is module specific also, At present this is done by comparing before and after values (two rows) in while loop. Please tell if we can use databse logs or is there any way to read those log files and generate a readable report like

    Or suggest if there is any other way to acive above kind of report without mannualy comapring tow rows (before and after),Thanks in advance.

    Best Regards


    • Hello Sach,

      Just to add 2 more cents to what SQL Genuis (Pinal) said, I would suggest you to look at slowly changing dimensions tasks available in SQL Server 2005 SSIS (BI Tool).

      I do not know, what your requirement is, if it is a feed, or real time update, if it is a feed, then I would definitely recommend using Slowly Changing dimension tasks, its very handy tasks, easy to configure it and it gives what you want.

      I have only used it for testing, I do not know, if it comes with any problems.

      ~ IM.


  53. Dear sir:

    I have a query Plz help me to go tru with tis query..

    I have a major as table name. tat table having QualificationsIDs fields, tis QualificationsIDs column contains many ids such as 12,20,48,23,

    I need to reterivew these table by selected categoriIDs


    If im selecting 20

    Rows having 20 as Qualification id should display.

    What should i do for this..
    help me to solve tis problem

    Thanks & Regards


    • @Rajesh

      Do you mean the one field has many ids in it? Is each id enclosed in two commas?

      FROM major WHERE QualificationsIDs LIKE ‘%,20,%’

      If not everyone is enclosed:

      FROM major WHERE
      QualificationsIDs LIKE ’20’
      OR QualificationsIDs LIKE ’20,%’
      OR QualificationsIDs LIKE ‘%,20’
      OR QualificationsIDs LIKE ‘%,20,%’


  54. Hi Pinal,

    I found this article and asked a question, but no reply yet. May be the discussion is old now.

    Anyway: Vishnu replied to one question:

    Milan you can use current databse name in a script..

    try like this..

    declare @DBName varchar(50)
    set @DBNAme=(select db_name())
    use @DBName

    Then on January 9, 2010 at 2:48 am I replied and asked the question below:

    “I have the same problem, I wanted to use current database name in a variable and then use that variable in USE command. I tried your commands but it didn’t work, I got an error:

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘@DBName’.

    What would be the reason, or is there any other way to do that.

    I want to run a script in all the databases in a server. ”

    I hope you will be able to reply with a solution.

    Thanks a lot and appreciated your work.

    DBA from Canada.


    • @Shoaib.

      How is Canada ( Freezing ????)

      Answering your question, one way of doing this is to use dynamic sql…

      declare @DBName varchar(50)
      set @DBNAme=(select db_name())

      — add these lines
      declare @sqlcmd1 nvarchar(4000)
      set @sqlcmd1 = ‘use ‘+@DBName
      Exec (@sqlcmd1)
      — continue your script

      ~ IM.


      • Hi Imran,

        We can not use “USE ” with dynamic query.

        It will not execute and we remain at same database as we get same DB by “DB_NAME()”

        USE statement will not work with dynamic query.

        Try to give to use different database from the same server.




        • Hello Tejas,

          “Use” can be used with dynamic SQL. All you have to do is just append your script after “Use” and database name as below,

          use master
          declare @DBName varchar(50)
          set @DBNAme= ‘msdb’

          declare @sqlcmd1 nvarchar(4000)
          set @sqlcmd1 = ‘use ‘+@DBName +’ select Db_name()’
          –print @sqlcmd1
          Exec (@sqlcmd1)



          • Hi Imran,

            That is true, then why don’t we use just
            DatabaseName.Schema.Tablename in dynamic SQL.

            What I mean is: after executing USE statement with dynamic SQL, Query analyzer will still stays in master database.

            So if I execute Dynamic SQL with USE statement and then in next statement I try to exeute from that database, it would not work.

            So, we can execute it by USE statement, but I think it is not of any use.

            Please suggest your ideas.




      • Hi Imran,

        Yes it is freezing, last week we had -20 C and lots of snow.

        Thanks for your reply, however Tejas is right, it didn’t work, I used even in a cursor. Eventhough the value changed for every database (database name is being printed) but it doesn’t change the value of DB_name or current database name, i mean my script return same value for all databases on the server. If I run the script against all database individually then it gives different result for each DB.

        Pinal suggested to use SP_msforeachdb, but the problem is same, i have a big script and it is returning same result.

        Thanks again for your time guys.



  55. I like your blog a lot. I have gone through lot of your articles. Your examples are really simple and superb.

    I have one request. I could not find any queries anywhere with tables and data. I either see just solutions but the want questions for it so that i can do it by myself.

    To practice more queries i need a list of queries and their solutions. Could you help me in this?. I want to practice more and more but all i see is one simple example for each of the queries.

    Plz it would be a great help for many of new starters and will be a revision for many experienced.


  56. I like your blog very much. I just like your degrees. If I will have any query regarding SQL, I will definitely contact you.

    Thank You…


  57. Hi,

    Can you tell me how to fine the store procedure execution history. means i need to know which user has executed the storeprocedure and wht time it has been executed with some more information. Can it be possible to get the details.



  58. hi Tejas shah And Imran,
    we can use The “use’ clause in Dyanamic sql.

    Try this,

    use Database
    –select Name from sys.databases
    –select * from sys.database_files
    @databaseName varchar(50),
    @sqlqry nvarchar(max) ,
    @sqlqry1 nvarchar(max)
    Create table #temp( Db varchar(250) , Name nvarchar(225) ,size int,FileType int)
    set @sqlqry = ”
    Set @sqlqry1 = ”
    Declare c Cursor for select Name from sys.databases
    open c
    Fetch Next from c into @databaseName
    While @@fetch_status =0
    Set @sqlqry = ‘ Use ‘ + @databaseName + ‘ ‘
    Set @sqlqry1 = ‘ Insert into #temp (Db, name,size,FileType)
    select ”’ + @databaseName + ”’ Db ,name,size,Type from sys.database_files’
    Print @sqlqry1
    Set @sqlqry = @sqlqry + @sqlqry1
    Print @sqlqry
    Exec sp_executesql @sqlqry
    Fetch Next from c into @databaseName
    close c
    Deallocate c
    select * from #temp
    Drop table #temp


    • Hi Arun,

      Thanks a lot for your response and time.

      It worked, I had to modify your script though. Because there were some single quotes and double quotes so I had to change. But it worked.

      I really appreciated all of you guys:

      Imran, Arun, Tejas and best of all Pinal.

      Take care.


      DBA from Canada


  59. Hi, my name is Ruben, im a computer engineer, from dec. of 2008, and i like to be part of your blog, recently im working a lot with sql server 2000 and sql server 2005 and im learning by myself microsoft visual studio 2008, i hope you like to let me join of your team, by the way im from baja california, mexico. see you later. thanks for the attention.


  60. Hello All,
    I am working on a project where we are migrating from SQL Server 2005 on Win2k3 To SQL Server 2008 on Win2k8.
    We are noticing significant increase in latency/ response time from the databases in SQL Server 2008.
    We have not changed the code at all.
    We have confirmed that we do not have any fragmentation
    General SQL Performance counters between 2k5 and 2k8 seem to be comparable.
    We ensured we used the same servers/hardware for the tests and hence have eliminated any hardware or network issues.
    Tests involve calling into SQL Server from C# using simple ADO.Net connections.

    Has anybody seen these kind of performance issues and do you have any suggestions or fixes for them?



    • Hello Zafar,

      Use the SP_SPACEUSED [schema.table] system stored procedure to know space used by a table. Here you get size in KB. To know the page number divide this value by 8 as size of a page is 8 kb.

      Pinal Dave


      • Pinal Thanks for replying

        i have an image of size 3801K, when i save the image, it gives me the size 3904 (which i get using the SP_SPACEUSED [schema.table].
        that means that 3904/8 = 488 pages
        but the actual size of the image is 3801 and size returned from table using SP_SPACEUSED is 3904 (which means 96 bytes overhead is also there)
        Total overhead = 488 * 96 bytes = 46848 bytes / 1024 = 46KB total overhead by 488 pages
        which 3904 (actual size returned using SP_SPACEUSED) – 46KB (overhead of every page) = 3858KB
        less the actual size of the image was 3801 , that gives me 57KB.

        Now my question is
        1. Why the (size returned+overhead of the page) is not equal to size of the image
        2. Where the remaining 57KB has been used
        3. If overhead is already included in every page (in my case 488 * 96 bytes) then the difference is of 103KB


        Zafar Iqbal


  61. I have a query

    begin try

    insert into table1
    select * from table2

    End try

    Begin catch

    End catch

    The above insert query throwing an error of duplicate key voilation.
    can i Log the record details on which the error occured

    ie like on 50th record from table there is a voilation.?


  62. Hi Pinal,

    We have a database with tables, foreign keys and indexes.
    I need to develop 3 scripts which generates the create scripts for foreign keys, Non Clustered indexes and tables (with clustered index definition) separately when run on the existing database.

    The DML scripts can be generated with GUI by SSMS but we don’t want to go by that.

    Please let me know how this can be done, possibly an example. Thanks for the help.



  63. Hi Pinal dave, im a begginer in sql server, im migrating an access 97 data base to a sql server 2000, and some queries have the access functions FIRST(), PIVOT, TRANSFORM, OWNERACCESS, those access functions gives me a lot of problems, i dont know how to do the same functions in sql server 2000, thanks for your time, and your help will be very apreciated, regards.


  64. Hi Pinal,

    In my company, We are developing one interface engine which will store data from HL7 messaging in Oracle or SQL Server 2005 DB (Depending upon the configuration by user).

    I was assigned job for handling the SQL Server side. I feel that in SQL Server 2008, we have array handling mechanism directly but not in SQL Server 2005.

    I want to handle the multi dimensional array objects received from messaging using CLR (Creating assembly in C# and registering in SQL).

    Some sites says that usage of CLR should be avoided as it hurts performance and some site says that it don’t have performance issue.

    I request you to please give your opinion on the usage of CLR for handling Multi-dimensional array.



    • Hi Pinal,

      Request you to please tell me whether CLR usage handling multi-dimensional array is acceptable or it really have any performance drawback.



  65. Hi Pinal,
    I am a devloper in a software company we are using sql server 2005 and we devlopers have client of sql server is installed
    is there any method to restore database without using wizard can we restore database .
    Backup files and ldf and mdf file’s are also on server.


  66. Dear Pinal,
    First, I like to thank you for this wonderful website!!!!! The tips and code segments helped me very often. As i am new to sql server database, and developing database driven website. So to make things fast i am doing work at home and office, but as i try to take database to my office PC it first gave me a lot of trouble, some errors related to “PIPE” and the “debugger” related errors croup up… any how i managed to do it. Now i am in a fix.. how to manage on daily basis the synchronization of database at home PC and Office PC… right now i am managing by remembering the changes done and just duplicating those changes at other location PC…but this is time consuming and now a good practice… Please Help.


  67. hey pinal
    i am a software developer in a company, i have just joined the company as a fresher. I have to make a search engine page for a table so that i can use it to find out any word .
    so can you please explain me to make the processure for that?

    Thank you


  68. hi pinal,

    i have a problem with calling a user defined function which has been defined in ‘user’ database from ‘masters’ database. i am using SQL 2000 server.can u suggest any way to call????

    thanks in advance


  69. Pinal You are right but when I tried with Restore command it prompts error when i gives backup file path (network path) which is located on server.


  70. Hi Pinal,

    We have a database with tables, foreign keys and indexes.
    I need to develop 3 scripts which generates the create scripts for foreign keys, Non Clustered indexes and tables (with clustered index definition) separately when run on the existing database.

    The DML scripts can be generated with GUI by SSMS but we don’t want to go by that.

    Please let me know how this can be done, possibly an example. Thanks for the help.



  71. Hi all,

    am getting mail alerts only after I mutually run datamail.exe in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

    not automatimatic.

    OS – windows Server2003
    Environment – sql 2005
    SMTP status= enabled

    kinldy give the solution regaring this issue


  72. hi Pinal, a few days i posted a question about some access functions (FIRST(), PIVOT, TRANSFORM, OWNERACCESS) but i need to do the same things in SQL SERVER 2000, i dont know if you can help me with that or something, i appreciate your time, by the way, excelent blog. regards.


  73. What i want to do is that there is table which has age and zip code. I am writing a code that takes four records through loop then copy them to temporary table (calculate average over there of the four records ) then store that avg value in a variable. After that want to replicate the avg value in the original table. I have done all the above steps only have problem in replicating the avg value only against the four record only that were first selected and for whom average was calculated.

    DECLARE @count3 INT
    SET @count3 = 0
    WHILE (@count3 <@count2 )
    INSERT INTO #MyTempTable VALUES (1)
    SET @count3 = (@count3 + 1)
    CREATE TABLE #MyTempTable (cola INT)
    Select * from #MyTempTable
    Delete #MyTempTable
    Create table majeedwaheed(agee int, zipee int)
    DECLARE cursor_first CURSOR FOR
    SELECT Zip_code , age FROM Rt_md_rd_II
    OPEN cursor_first
    Declare @count5 INT
    Set @count5 = 0

    Declare @v_zip_code INT
    Declare @v_age INT
    Fetch NEXT from cursor_first into @v_zip_code,@v_age
    While (@count5 < 4)
    Select @v_zip_code as zip_code,@v_age as age insert into majeedwaheed values(@v_zip_code, @v_age)
    Set @count5 = (@count5 + 1)
    Fetch NEXT from cursor_first into @v_zip_code,@v_age
    Declare @avg_age int

    set @avg_age = (select avg(zipee) from majeedwaheed)

    select @avg_age
    Declare @count9 INT
    Set @count9 = 4
    While (@count9 < = 0)
    update Rt_md_rd_II set zip_code = @avg_age
    set @count5 = (@count9 – 1)


  74. the output of my code should be:

    original table
    Zip code Age
    1305 21
    1306 22
    1309 23
    1400 30

    Output table
    Zip code Age
    1330 24
    1330 24
    1330 24
    1300 24


  75. SELECT SERVERPROPERTY(‘BuildClrVersion’) BuildClrVersion
    ,SERVERPROPERTY(‘Collation’) Collation
    ,SERVERPROPERTY(‘CollationID’) CollationID
    ,SERVERPROPERTY(‘ComparisonStyle’) ComparisonStyle
    ,SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) ComputerNamePhysicalNetBIOS
    ,SERVERPROPERTY(‘Edition’) Edition
    ,SERVERPROPERTY(‘EditionID’) EditionID
    ,SERVERPROPERTY(‘EngineEdition’) EngineEdition
    ,SERVERPROPERTY(‘InstanceName’) InstanceName
    ,SERVERPROPERTY(‘IsClustered’) IsClustered
    ,SERVERPROPERTY(‘IsFullTextInstalled’) IsFullTextInstalled
    ,SERVERPROPERTY(‘IsIntegratedSecurityOnly’) IsIntegratedSecurityOnly
    ,SERVERPROPERTY(‘IsSingleUser’) IsSingleUser
    ,SERVERPROPERTY(‘LicenseType’) LicenseType
    ,SERVERPROPERTY(‘MachineName’) MachineName
    ,SERVERPROPERTY(‘NumLicenses’) NumLicenses
    ,SERVERPROPERTY(‘ProcessID’) ProcessID
    ,SERVERPROPERTY(‘ProductVersion’) ProductVersion
    ,SERVERPROPERTY(‘ProductLevel’) ProductLevel
    ,SERVERPROPERTY(‘ResourceLastUpdateDateTime’) ResourceLastUpdateDateTime
    ,SERVERPROPERTY(‘ResourceVersion’) ResourceVersion
    ,SERVERPROPERTY(‘ServerName’) ServerName
    ,SERVERPROPERTY(‘SqlCharSet’) SqlCharSet
    ,SERVERPROPERTY(‘SqlCharSetName’) SqlCharSetName
    ,SERVERPROPERTY(‘SqlSortOrder’) SqlSortOrder
    ,SERVERPROPERTY(‘SqlSortOrderName’) SqlSortOrderName;


  76. Hi Penal,

    I’m a beginner in SQL Server, could u please guide me to some good hands on lab exercises to practise SQL/T-SQL on Adventureworks db??

    Your help is appreciated :)

    Best Regards,


  77. Dear Pinal,

    I am a junior DBA and have been following your blogs for last 2 years.I find it really useful for any newbie.I was supposed to attend your BI session in Pune but I got transferred and missed this oppurtunity:-(

    I have a question which I am not sure how to ask.Will gve it a try:-

    In one of my projects we had to migrate from sql 2000 to sql 2008.Both were enterprise edition.My senior DBA did not run the upgrade wizard.He just installed SQL 2008,detached sql 2000 .mdf and .ldf files and just attached them to sql 2008.I have a gut feeling that this was one of the smart ways to do it but it might not be able to utilize the sql 2008 performance gains properly.Can you please throw some light on this.


  78. Hi,
    I am working on a Restaurant Web Site with Online Ordering. Now I am making an admin console to manage and print orders. and for that i need differential data. So I only need recent orders not all orders. so can you suggest me something on that?


    • You need to define the term recent


      select t1.* from table1 as t2 inner join
      select pkcol, max(date_col) as date_col from table group by pkcol
      ) as t2
      on t1.pkcol=t2.pkcol and t1.date_col=t2.date_col


  79. Hi All

    I want to update a table having the same name as day part of a particular column for example i want to store the value to 5\12\2009 in days5 and 15\12\2009 in days15 etc

    update #wrk set #wrk.days+cast(day(Col_name)))=colum_name
    from #wrk inner join #tem2 on #wrk.file_no= #tem2.file_no
    where @c=day(Col_name)and #wrk.file_no=#tem2.file_no

    i get error in above


    • Hello Firoz,

      write query to create a dynamic statement and then execute the dynamic statement. First create statement in a varchar variable as below:

      declare @strSQL varchar(2000)
      set @strSQL = ‘update wrk set wrk.days’+cast(day(Col_name)) + ‘=colum_name
      from wrk inner join tem2 on wrk.file_no= tem2.file_no
      where @c=day’ + (Col_name) + ‘and wrk.file_no=tem2.file_no’

      Make sure that you use permanent tables instead of temporary tables when using dynamic statement.

      Pinal Dave


  80. Hi Pinal,

    I have query regarding database performance.
    As i have sp and p file in oracle what are the parameter file in SQL where i can set the maximum num of session and cursors or some other parameter.
    What are the basic parameter i hav to set for a high end database.(200 gb)
    I hav a Database Server of 32 gb ram and quad core processor and my cpu utilization is 100%where could be the problem.As the system is utilizing only 17 gb and cpu utilization is 100%.
    4.What are the key parameters related to performance.


  81. I am using sql studio express and am trying to have the primary key of table 1 automatically be inserted into table 2 and table 3 where it is declared as foreign keys.

    any suggestions please?


  82. Hello Pranav,

    You will have to write extra code to add records automatically in foreign key table.
    Delete and update can be performed automatically by using the ON DELETE and ON UPDATE cascade option while writing the foreign key constraints.

    Pinal Dave


  83. Hi Pinal,

    Thank you for publishing this website, it has been very helpful to me in many occasions. My question is, what would be the best way to insert rows to Teradata via a linked server in SQL. I use the following syntax and it takes approx. 30minutes to do an insert of less than 15K records. Thank you.



  84. Hi

    I need some help, I need to create a Foreign key constraint that references to a table in a external database which resides in the same server, I’m using the next sintax:

    ALTER TABLE WorkOrderTable
    ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ElearnerPID)REFERENCES TECTA_BAK.dbo.Person(PersonID)
    SQL returns the next message:
    Msg 1763, Level 16, State 0, Line 1
    Cross-database foreign key references are not supported. Foreign key ‘TECTA_BAK.dbo.Person’.

    Could any one help me regarding this
    Thank you


  85. Pinal,
    my req is i need to connect to other database which is on other computer(Which is Created Locally on his Machine) but within same network.
    ENvi: Sql Server 2008.
    i Apreciate your Help.


    • Hello Sudarshan,

      You can access database from another server by creating a linkedserver and then using 4 part naming.
      You can also access using OPENQUERY and OPENDATASOURCE functions.

      Pinal Dave


    • @ Kishore~~ Can you please explain in detail as to what do you mean by configuring the path?

      If you wish to change the location of files,you can use alter database commands(see books online) or you can use detach and attach method after you change the location.But I prefer the first method


    • Hello Kishore,

      If you can stop the database for once than use the detach and attach method. You can also use backup restore method.
      Using ALTER DATABASE add a new file
      Using DBCC SHRINKFILE empty the previous file
      Using ALTER DATABASE remove the previous file.

      Pinal Dave


  86. Hi,

    Let me begin by saying that I’m a total newb.
    I’m trying to extract various columns from a fixed width text file, however since the file exceeds 65000+ rows I can not simply accomplish my task using MS Excel 2003.

    I’d like to use SQL to accomplish my task but I’m not certain how I can code a statement to only pull say column 2-5, 13-25, etc.. into discrete fields.

    I don’t have a CS or programming background, I’m just scouring the web to look for tutorials or articles to help accomplis my goal.

    Thank you.


  87. Dear Pinal Sir,

    I have a problem in following queries :-

    create table cust_tmp
    ( cust_no char(5),
    cust_name varchar(50),
    region char(5)
    insert into cust_tmp values(‘c1′,’abcd’,’abcd’)

    insert into cust_tmp values(‘c2′,’abcd’,’abcd’)
    select *
    from cust_tmp

    declare @region char(5)
    select @region=’abcd’

    EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,

    @query = ’select *
    from cust_tmp
    where region=”’+@region+”’/*problematic part */
    order by region,cust_name’

    –It Shows error message “Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ‘+’.

    /* If I used the following scripts it Works, I used variable @a and assign select statement into @a and after that
    I put it in front of @query. It work . but when I write select statement into @query. it doesn’t work */
    declare @region char(5),
    @a varchar(200)
    select @region=’abcd’
    select @a=’select *
    from cust_tmp
    where region=”’+@region+”’/*problematic part */
    order by region,cust_name’

    select @a
    EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,

    @query = @a

    –It works

    –Pls solve this issue.


  88. Hi Pinal,

    I have a issue while running a query in a view. This query is failing because the tempdb is growing and occupying the entire memory on the drive and resulting in query to fail due to insufficient space. But I can’t shrink the db when my query is running.Can we shirkin the tempdb on the fly.Please advise me on this issue.



    • @Venkat~ Tempdb is used as a temporary workspace.I would suggest you to keep the tempdb files on a separate drive and it should be given ample space to grow which depends upon your needs.when the tempdb is in use,shrinking it ‘on the fly’ does not help much.

      We also used to have such issues so once a while we needed to restart sql service to clear our tempdb.

      @Pinal- Over to you Sir


      • you may consider adding extra datafiles to tempdb.This way if a datafile in one drive is full,some other datafile will be utilized.

        But its all the same thing.You need sufficient space as per your needs.


  89. Hi Pinal,

    I have scenario to load values from CSV file into SQL table. I can do this using bulk insert and it works fine, but if I remove identity column from csv and if I try to load it is not working.

    Could you please suggest what is the best way to load specific columns value into SQL table.



  90. Hello

    I have a question

    Is there any possibility to enter multiple dates into a single column

    I would like to enter all work order contacting dates respective that order wants to enter into a single column.

    Is there any chance to enter like that or I need to enter different rows for each date

    please give me suggestion regarding this

    Thank you


    • Hello Usha,

      The best method is to use XML datatype column for this purpose.
      The another way is to use create a foreign key table containing order contacting dates.
      Otherwise convert the dates into a comma saperated string.

      Pinal Dave


  91. Hi Pinal,

    I have one question.

    I have three tables that are getting updated 24*7*365 and there are number of stored procedures that fires select on these tables.

    The table is having primary key but that is not used in where condition in any of the Select from these tables, they have one field called Product name and on based of that where condition evaluated.

    My question is what are the best options for creating the indexes on these three tables?

    P.S (I Dont have any Index right now and willing to improve performance using Indexes only.)


    • @Jigar- If an index gives you a high ‘SELECTIVITY’ and reduces your table scan then it is good for you.

      In your case you mention that your search condition is based on ‘Product name’.Hence you can create a non-clustered key on this field.It will improve your performance surely.

      PS: Indices get fragmented over the time.So ask your DBA to take care of that.(hint. alter index rebuild/reorganize commands)

      —>Over to Pinal sir


    • You could try Database Engine Tuning Advisor or just read the execution plan (insert your sql-statement into SQL Server Management Studio and press CTRL+L) and try to figure out where indexes are needed. Google for these and you find a lot of information on MSDN.

      But my bet would be just to add nonclustered index to the ‘Product name’ field:

      CREATE NONCLUSTERED INDEX [TableName_ProductName] ON [TableName]

      You say you have primary keys in tables. That usually means that you have also clustered indexes on those tables. So if you just JOIN tables using primary keys that should do fine.

      Now if you are still facing performance problems then possibly there’s something else that’s holding back the DB (possibly write locks). That is harder to fix without actually seeing the database.


  92. Hi All,

    I want to merge 2 columns between Newline/Carriage return.

    For Example..

    My table

    EmpID | First Name | Last Name
    1 | First | Second
    2 | chris | cintrella

    i want Output for the following format…

    EmpID | Name
    1 | First
    2 | chris

    Please give solution….

    warm regards,


  93. Hi Pinal,

    One of my DB got in to recovery mode, the log file is a monstrous 150gb… Is there way I can stop the recovery and delete the data and log files? I do not need the db as I have the bkp else where…

    This is a client server and I do not want to risk it as there are other production dbs there.

    Sriram Hariharan


  94. Hi,

    I have a requirement where using a single select statement I need to get the following output in sql
    Col 1 Col 2
    Row1 1 2
    Row2 3 4

    That is basically the output has to be just a display in above format.

    Please suggest as to how can we achieve it.


  95. Hi,

    i have a huge database with approx. 30.000.000 rows…
    i need to search 1 varchar-field per row with p.ex. \directory1\directory2\directory3\testfile_100121_sqlserver.bak

    lets say i want to search for the phrase “sql”

    What is the fastest way to get this done?

    With a simple select * from table where varcharthing LIKE ‘%sql%’ it nearly kills the Server…

    With an full-text-catalog the search is very fast but not exact and not showing all entries with “sql” in it….

    Help :-). Any ideas?



  96. hi, im migrating an access database and it have some access functions (FIRST(), PIVOT, TRANSFORM, OWNERACCESS) but i need to do the same things in SQL SERVER 2000, i already search in internet but nothing, i dont know if someone can help me or where can i find a solution, really thanks.



  97. Hi Ruben,

    SQL Server does not have functions for exact functionality of these MS Access functions. But FIRST() can be replaced by TOP 1, PIVOT is a function in SQL Server 2005 but in SQL Server 2000 you will have to write queries to perform it. Functionality of other functions also have to be written in T-sql.

    Pinal Dave


  98. Hi ,

    I am jaffer a B.E fresher ,want to build career in databases

    please give solution for this…

    -> consider any student table …

    select student_name, subject wise maximum marks ?

    please reply me soon.


  99. hi sir,

    I need Query for this situation,Please give

    Vehiclename Drivers
    Car | 2
    Car | 2,3
    Car | 2,5,8
    Car |
    Scooter | 4
    Scooter |
    Scooter 4,6

    I want Result like this

    Vehiclename Drivers

    Car 2,3,8
    Scooter 4,6

    Without using Cursor ,Please give query


  100. i have a problem wi th this sum is not working ,
    pls suggest alternate.

    select *,Fromdate,Todate,datediff(day,Fromdate,Todate) as leavedays,sum(leavedays)
    from leavepermission1
    where datepart(month,AppliedONdate) = ‘1’ and Employeeid = ‘125’


    • Harish,

      Aggregate Function is used with the help of ” group by” and following query may help you…

      declare @leavepermission1 table
      sno int,
      employeeid int,
      address char(10),
      Fromdate datetime,
      Todate datetime,
      AppliedONdate datetime
      insert into @leavepermission1 values (‘1′,’111′,’Agra’,’2008-08-01′,’2008-08-02′,’2008-08-05′)
      insert into @leavepermission1 values (‘1′,’112′,’CHD’,’2008-08-11′,’2008-08-22′,’2008-08-25′)
      insert into @leavepermission1 values (‘1′,’111′,’PKL’,’2008-09-01′,’2008-09-22′,’2008-09-05′)
      insert into @leavepermission1 values (‘1′,’113′,’Agra’,’2008-09-01′,’2008-09-02′,’2008-09-07′)
      insert into @leavepermission1 values (‘1′,’114′,’CHD’,’2008-11-01′,’2008-11-08′,’2008-11-25′)
      insert into @leavepermission1 values (‘1′,’114′,’CHD’,’2008-12-01′,’2008-12-12′,’2008-12-12′)
      insert into @leavepermission1 values (‘1′,’111′,’PKL’,’2009-01-01′,’2009-01-12′,’2009-01-05′)

      –select * from @leavepermission1

      select *,Fromdate,Todate,datediff(day,Fromdate,Todate) as leavedays–,sum(datediff(day,Fromdate,Todate) )
      from @leavepermission1
      where datepart(month,AppliedONdate) = ‘1’ and Employeeid = ‘111’

      — Used Group by
      select datepart(month,AppliedONdate),sum(datediff(day,Fromdate,Todate) )
      from @leavepermission1
      group by datepart(month,AppliedONdate)


  101. Thank you Mr.Pinal for your reply

    could you help me to write the query :

    how to convert the dates into a comma saperated string.

    Thank you


    • It may be helpful….

      select getdate()
      2010-01-25 12:51:37.997

      select replace((convert(varchar(20),getdate(),102)),’.’,’,’)



  102. Trying to replicate from SQL 2005 to 2 subscribers on 2008. one server is succeeded and another one is failing. Getting error “Agent message code 20084. Process could not connect to subscriber “. Using managment studio 2008 I could connect to all servers. Ping is working fine in all direction. Any ideas pls?


  103. Hello Christopher

    I did go through almost similar one

    Try to use thsi queru
    I Hope it will be useful for u

    IF OBJECT_ID(‘Table1’, ‘U’)
    Table1 CREATE TABLE Table1( ColumnA INT, ColumnB VARCHAR(1))

    INSERT INTO Table1 (ColumnA, ColumnB) VALUES (1, ‘a’)
    INSERT INTO Table1 (ColumnA, ColumnB) VALUES (1, ‘b’)
    INSERT INTO Table1 (ColumnA, ColumnB) VALUES (2, ‘c’)
    INSERT INTO Table1 (ColumnA, ColumnB) VALUES (2, ‘d’)

    ColumnA ColumnB
    1 a
    1 b
    2 c
    2 d

    IF OBJECT_ID(‘dbo.JoinString’, ‘FN’) IS NOT NULL DROP FUNCTION dbo.JoinString

    CREATE FUNCTION dbo.JoinString (@ColumnA INT)
    DECLARE @result VARCHAR(100)

    SET @result = ” SELECT @result = @result + ‘ ‘ + IsNull(ColumnB, ”)
    FROM Table1
    WHERE ColumnA = @ColumnA
    ORDER BY ColumnB

    return LTRIM(@result)



    DECLARE @Tab Table( ColumnA INT, ColumnB VARCHAR(100))

    INSERT INTO @Tab (ColumnA)


    FROM Table1 (NOLOCK)

    UPDATE @Tab SET ColumnB = dbo.JoinString(ColumnA)


    ColumnA ColumnB
    1 a b
    2 c d


  104. I am jaffer a B.E fresher ,want to build career in databases

    please give solution for this…

    -> consider any student table …

    select student_name, subject wise maximum marks ?

    please reply me soon.


  105. Hello Pinal.

    I’m looking for a SQL script that is output will be the SQL Server startup parameters.
    If I want to create this script witch system table should I address to find this information?


  106. i want to count total number of days for a particular month like below.
    for example:
    i want how many sundays,mondays,tuesdays,wednesdays,thursdays,fridays in this month
    like this:


  107. i want to count total number of days for a particular month like below.
    for example:
    i want how many sundays,mondays,tuesdays,wednesdays,thursdays,fridays in this month
    like this:

    thanks in advance.kindly rly plz very urgent..


    • declare @date datetime
      set @date='20100123'

      select week_day,count(*) as counting from
      select datename(weekday,dateadd(day,number,dateadd(month,datediff(month,0,@date),0))) as week_day from master..spt_values
      where type=’p’ and number between 0 and day(dateadd(month,datediff(month,0,@date)+1,-1))-1
      ) as t
      group by week_day


  108. Hi Pinal,
    I’m using My SQL, I’m facing on problem. In my table, there is a details for Products, sales,quarter and year. the details are as follows.

    ProdID Quarter Sales Year
    1 1 1 2010
    1 4 2 2009
    1 1 5 2009
    1 4 5 2008
    2 1 1 2009

    Now i want the sum of Sales as per the quarter.

    the output should be as follows

    ProdID Sales
    1 3
    1 10
    2 6

    I’ve written a following query to get the results, I’m getting the output for the prodID=1 but not for the prodID=2.
    the query is as follows

    SELECT (s.Sales+s1.Sales)as totalsales,s.year,s.quarter
    Sales s
    LEFT JOIN Sales s1 ON (s.ProdID = s1.ProdID)
    WHERE s1.year=s.year-1 and s.Quarter = 1 and (s1.quarter = 4 )

    Group by s.ProdID,s.year
    ORDER BY s.year;

    Please modify the query to get the output.


  109. hi, Pinal

    there r 2 tables master : username, GSID

    primary foreign key is username

    in SID, one default value goes in master tbls “GSID” n all other values for SID goes into details tbl under same username.

    for e.g. username : test user
    GSID: CometMotors,Goblin,AutoWay

    here CometMotors vl go into master n other 2 in detail under test user.

    nw, I want result as combined with comma seperated in query result llike : “CometMotors,Goblin,AutoWay”, can u provide such query?


  110. Hello
    I want to backup my database in the external harddrive

    I don’t know how to backup in external hard drive
    could any one help me regarding this

    Thank you in advance


    • While taking the backup,it asks for the location where you want to place your backup file.Just change the default path and put the path which you desire.I am assuming here that you are taking native backup.

      If I could not understand your qustion well,please elaborate a bit more:-)


  111. Hello chandan
    Thank you so much for your reply
    Actually I wnat to backup from remote desktop
    So it is not showing my external harddrive over there to backingup my database
    Could u explan me What I need to do in that situation

    thank you


  112. Our ‘sa’ password has been changed by some one and linked servers are failing. Is there a way to find who has made changes? If I get clue asap would reduce my search around.
    Advance thanks.


  113. @Usha.

    I dont know which version of SQLServer are you using.

    From SQL Server 2005 you can use certificates, Symmetric and ASymmetric keys to Encrypt and Decrypt data in database.

    Same key /certificate has to be used to decrypt data that was used to encrypt data. I don;t have hands on experience, to know more about Symmetric and Asymmetric Key please follow this link:

    Its a very easy tutorial, easy to understand and implement, written by none other than our SQL Maestro (Pinal Dave)

    ~ IM.


  114. Hi,

    I am surprised with the strange behavior of SSIS “Lookup transformaton” control. Actually, i was checking the duplicate entries for one field “Emp_ID”(which is unique). but due to some reasons it was not able to send all the “Emo_ID” to destination. in the same table i had one more field “EMP_no”(which is also unique ).

    Now something happened, which is hard to understand. What happend that the package was taking around 1~2 hours for completion with “Emp_ID”. But it is taking only 5~6 minutes afte adding the “Emp_no” to the “Columns” part of the “Lookup Transformaton” control. I used the same approach of “Composite key” in SQL. Afte this change, i got the solution and the package is working as expected, but couldnot able to understand why the performance got increase many folds.

    Any clue in this regard will help me out.
    Thanks in advance.



    • Hello Loknath,

      One thing about the “Lookup Transformation” task that we must know is that its value comparision is case sensitive.
      The reason of variant execution time could be index or data statistics. I think there would be an index on Emp_no column.

      Pinal Dave


      • Thanks Pinel,

        Actually, the indexes are as given:

        In Source DB:

        i) nonclustered located on PRIMARY index on “EMP_ID”
        ii) clustered, unique, primary key located on PRIMARY “EMP_NO”

        In Destination DB:

        i) nonclustered located on PRIMARY on “EMP_ID” and ii) clustered, unique, primary key located on PRIMARY for “EMP_NO and EMP_ID”

        Ok, i could understand that the index could play a role in performance. But i am not getting the case-sensivity of the “Lookup Transformation” control.

        The data types are as:
        EMP_ID -> int
        EMP_no -> bigint

        will it do any difference for pulling the records. My records are in the range of 25573200 for EMP_ID and 161664991 for EMP_No.

        Again thanks for you reply…



  115. CREATE TRIGGER trig_addEmployee

    ON staffcust



    DECLARE @newName VARCHAR(100)

    SELECT @newName = (SELECT staff_id + ‘ ‘ FROM Inserted)

    PRINT ‘New employee “‘ + @newName + ‘” added.’

    INSERT INTO staffcust values(s1)

    The name “s1” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.


  116. sorry i sent post above before i got chance to add comment…

    getting that error was just wondering if anyone could help me out


  117. How much space does an empty string use on SQL SERVER 2008? On an integer, varchar does it matter?

    I know that a SPARSE NULL column takes up no space but I cannot find the documentation that states how much a non sparse NULL value takes up.


  118. Hi pinal i have question
    SELECT username + ‘ من ‘ + location + ‘ يقول ‘+ comment + ‘ عن ‘ + CAST(Date as varchar) as ‘تعليقات القراء ‘ FROM tblComments

    this is my query i am concating arabic in the query
    my database supports arabic since i have got arabic data

    but this thing gives me question marks
    may be u can help me

    Nadir Firfire


  119. Hello pinal

    I am regular reader your blog last one year it so interesting and increase my knowledge of SQL server.

    Right now Please to know what is Master data Services in sql server 2008, Please give me more information about it.


    vishnu dalwadi


  120. Master Data Services (MDS) helps organizations standardize and streamline the business data customers use across their organization to make critical business decisions. MDS is a Master Data Management (MDM) application built from platform components which may be deployed as an application or extended by use of the platform components to consistently define and manage the critical data entities of an organization. MDS is an any-domain hub that supports but is not limited to domains such as product, customer, location, cost center, equipment, employee, and vendor.
    Using MDS, customers can manage critical data assets by enabling proactive stewardship, enforcing data quality rules, defining workflows around data changes, notifying impacted parties, managing hierarchies, and sharing the authoritative source with all impacted systems.

    refer :


  121. hi Pinal

    First many thanks to your great help..

    my problem is i want reset IDENT_CURRENT(‘tablename”)
    value to -1

    explanation: hi i’m using two table 1st i ll insert one table value after that i ll select identity value from that table to insert 2nd table value. my problem is i want to reset the 1st table ident_current value to -1. plz tel me how to do??

    eg: insert into master(name,email) 1st table with id as autoincrement value

    insert into child(IDENT_CURRENT(‘master’), marks, school)

    set IDENT_CURRENT(‘master’)=-1; // is it possible??

    plz help me i need it very urgently
    many thanks in advance :)


  122. sir,
    installed 2008 r2 in my PC
    in SSRS configuration tool when i click on report manager url i found an error like
    User ‘DEEPTHI-PC\DEEPTHI’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
    and when i click on web service URL
    The permissions granted to user ‘DEEPTHI-PC\DEEPTHI’ are insufficient for performing this operation. (rsAccessDenied) Get Online Help

    but i am logged on as administrator only
    is any installation problem…?
    or any configurations i need to do..?

    please help me..


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s