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 http://blog.sqlauthority.com. 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” sqlauthority.com. 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:

SQLAuthority.com 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).

Search SQLAuthority.com

If you have any questions for faster response, Search SQLAuthority.com. 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” SQLAuthority.com
pinaldave “at” yahoo.com

About these ads

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,

      Thanks

      Like

    • 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
      Ian

      Like

    • 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.

      Like

    • 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

      Like

  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

    Like

  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
    GROUP BY GROUPING SETS((EmpId), (Yr))

    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….

    Like

  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
    Time_stamp—Called_number
    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
    Regards
    Fangu

    Like

    • 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

      Like

  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

    vivekanand

    Like

    • 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

      Like

  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

    Like

  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

    tHANKS IN ADVANCE

    Like

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

      USE master;
      GO
      ALTER DATABASE AdventureWorks
      MODIFY FILE
      (
      NAME = Test1dat2,
      FILENAME = N’D:\t1dat2.ndf’
      );

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

      Like

    • 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! :)

      Like

  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:

    \\Shared\softusers\Gautham\att.txt
    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.

    Regards,
    Gautham

    Like

  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,
    Gustavo

    Like

    • 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,
      substring(r.text,exr.statement_start_offset/2,
      (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,
      substring(p.text,blk_exr.statement_start_offset/2,
      (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
      from
      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
      where
      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

      Regards,
      Pinal Dave

      Like

      • Hi Pinal,

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

        Regards,

        Like

  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.

    Thanks,
    Rupesh

    Like

  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.

    Regards,
    Pinal Dave

    Like

  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
    Yours
    Kishan

    Like

  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

    eg:
    Input 4 Output Even
    Input 5 Output Odd

    Like

    • @Ajay

      DROP FUNCTION Is_Even;
      GO
      CREATE FUNCTION Is_Even(@A INT)
      RETURNS CHAR(4)
      BEGIN
      RETURN CASE @A % 2 WHEN 0 THEN ‘Even’ ELSE ‘Odd’ END
      END
      GO

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

      Like

  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.

    Like

  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.

    ——————————
    ADDITIONAL INFORMATION:

    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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    i AM USING WINDOWS 7

    Like

  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

    Like

  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.

    Like

  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.

    Like

  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?

    Like

  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?
    Thanks.
    Cleto.

    Like

  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

    Like

    • 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')

      Like

  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.

    Like

    • 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.

      Regards,
      Pinal Dave

      Like

      • 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.

        Like

  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?????

    Like

    • 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.

      Like

    • 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.

      Regards,
      Pinal Dave

      Like

  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!

    Like

  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

    Regards

    Jay

    Queries
    ————–
    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

    Like

  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)

    Regards

    Jay

    Like

  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

    Like

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

      fileName.bat

      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!

      Like

    • 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.

      Like

    • 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

      Like

  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.

    Thanks
    AbdulVahab

    Like

    • 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’

      Thanks
      Manish

      Like

  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)?

    Like

  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.

    Regards,
    Pinal Dave

    Like

  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 asp.net 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

    Like

  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.

    Thanks.

    Like

  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]
    ,[ename]
    FROM [Replication].[dbo].[emp]’)

    —shish\sql2005 is a logical name linked server .

    Like

  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 ?

    Like

  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

    Arun

    Like

  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

    Arun

    Like

  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.

    Like

  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.

    Regards
    Jim

    Like

  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
    where(mode=’m’and
    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
    dbo.demand_range(’12/12/2007′,’12/20/2009′)))
    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

    Like

    • @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.

      Like

  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’
    end

    Like

      • 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.

        Like

  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.

    Like

  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

    Like

  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.

    Regards,
    Pinal Dave

    Like

  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";

    Like

  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.

    Saludos!

    Like

      • 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.

        Regards

        Jim

        Like

        • @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.

          Like

  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.

    Thanks

    Like

    • 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:

      http://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx

      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.

      Regards,
      Pinal Dave

      Like

  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.

    Regards,
    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

    Like

  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 ?

    Thanks,
    Parikshit

    Like

  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.

    Regards,
    Pinal Dave

    Like

  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…

    Thanks,
    Ashwani

    Like

  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…

    Thanks,
    Ashwani

    Like

  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,
    Pramod

    Like

    • 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.

      Regards,
      Pinal Dave

      Like

      • 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.

        Thanks,
        Pramod

        Like

  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
    sach

    Like

    • 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.

      Like

  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

    eg:

    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.k

    Like

    • @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,%’

      Like

  54. Hi Pinal,

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

    http://blog.sqlauthority.com/2008/02/12/sql-server-get-current-database-name/#comment-59495

    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
    go

    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.

    Shoaib,
    DBA from Canada.

    Like

    • @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.

      Like

      • 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.

        Thanks,

        Tejas
        SQLYoga.com

        Like

        • 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)

          ~IM.

          Like

          • 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.

            Thanks,

            Tejas

            Like

      • 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.

        Shoaib

        Like

  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.

    Like

  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…

    Like

  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.

    Regards
    Ahmed

    Like

  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
    Declare
    @databaseName varchar(50),
    @sqlqry nvarchar(max) ,
    @sqlqry1 nvarchar(max)
    Begin
    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
    Begin
    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
    End
    close c
    Deallocate c
    End
    select * from #temp
    Drop table #temp

    Like

    • 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.

      Regards.

      Shoaib
      DBA from Canada

      Like

  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.

    Like

  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?

    Thanks

    Like

    • 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.

      Regards,
      Pinal Dave

      Like

      • 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

        Regards

        Zafar Iqbal

        Like

  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.?

    Like

  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.

    Rahul

    Like

  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.

    Like

  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.

    Thanks,
    Pramod

    Like

    • Hi Pinal,

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

      Thanks,
      Pramod

      Like

  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.

    Like

  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.

    Like

  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

    Like

  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

    Like

  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.
    Thanks
    Rahul

    Like

  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.

    Rahul

    Like

  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

    Like

  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.

    Like

  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 )
    BEGIN
    INSERT INTO #MyTempTable VALUES (1)
    SET @count3 = (@count3 + 1)
    END
    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)
    BEGIN
    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
    END
    Declare @avg_age int

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

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

    Like

  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

    Like

  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(‘LCID’) LCID
    ,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;

    Like

  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,
    Amit

    Like

  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.

    Like

  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?

    Like

    • You need to define the term recent

      Generally

      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

      Like

  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

    Like

    • 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.

      Regards,
      Pinal Dave

      Like

  80. Hi Pinal,

    I have query regarding database performance.
    1.
    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.
    2.
    What are the basic parameter i hav to set for a high end database.(200 gb)
    3.
    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.

    Like

  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?

    Like

  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.

    Regards,
    Pinal Dave

    Like

  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.

    INSERT INTO OPENQUERY(, ‘SELECT * FROM LinkedServerTableName’) SELECT * FROM

    Like

  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)
    GO
    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

    Like

  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.

    Like

    • 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.

      Regards,
      Pinal Dave

      Like

    • @ 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

      Like

    • Hello Kishore,

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

      Regards,
      Pinal Dave

      Like

  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.

    Like

  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.

    Like

  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.

    Regards,
    Venkat

    Like

    • @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.

      http://support.microsoft.com/kb/307487

      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

      Like

      • 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.

        Like

  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.

    Cheers,
    Ramu

    Like

  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

    Like

    • 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.

      Regards,
      Pinal Dave

      Like

  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.)

    Like

    • @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

      Like

    • 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]
      (
      [ProductName]
      )

      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.

      Like

  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
    Second
    2 | chris
    cintrella

    Please give solution….

    warm regards,
    Ms.Hema

    Like

  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.

    Regards,
    Sriram Hariharan

    Like

  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.

    Like

  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?

    Regards,
    Patrick

    Like

  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.

    regards

    Like

  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.

    Regards,
    Pinal Dave

    Like

  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.

    Like

  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

    Like

  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’

    Like

    • 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)

      Like

  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

    Like

    • It may be helpful….

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

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

      output
      ————————-
      2010,01,25

      Like

  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?

    Like

  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’)
    IS NOT NULL
    DROP TABLE
    Table1 CREATE TABLE Table1( ColumnA INT, ColumnB VARCHAR(1))
    GO

    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’)
    GO

    ColumnA ColumnB
    1 a
    1 b
    2 c
    2 d

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

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

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

    return LTRIM(@result)

    END

    GO

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

    INSERT INTO @Tab (ColumnA)

    SELECT DISTINCT(ColumnA)

    FROM Table1 (NOLOCK)

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

    SELECT *FROM @Tab

    ColumnA ColumnB
    1 a b
    2 c d

    Like

  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.

    Like

  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?
    Thanks
    Oren

    Like

  106. i want to count total number of days for a particular month like below.
    for example:
    Date:23/01/2010
    i want how many sundays,mondays,tuesdays,wednesdays,thursdays,fridays in this month
    like this:
    sundays(5)
    mondays(4)
    tuesdays(4)

    Like

  107. i want to count total number of days for a particular month like below.
    for example:
    Date:23/01/2010
    i want how many sundays,mondays,tuesdays,wednesdays,thursdays,fridays in this month
    like this:
    sundays(5)
    mondays(4)
    tuesdays(4)

    thanks in advance.kindly rly plz very urgent..

    Like

    • 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

      Like

  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
    FROM
    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.

    Like

  109. hi, Pinal

    there r 2 tables master : username, GSID
    details:username,SID.

    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?

    Like

  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

    Like

    • 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:-)

      Like

  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

    Like

  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.

    Like

  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:

    http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

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

    ~ IM.

    Like

  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.

    Thanks,
    Loknath

    Like

    • 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.

      Regards,
      Pinal Dave

      Like

      • 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…

        Thanks,
        Loknath

        Like

  115. CREATE TRIGGER trig_addEmployee

    ON staffcust

    FOR INSERT

    AS

    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.

    Like

  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

    Like

  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.

    Like

  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

    rgds
    Nadir Firfire

    Like

  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.

    Rgds

    vishnu dalwadi

    Like

  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 : http://www.microsoft.com/sqlserver/2008/en/us/mds.aspx

    Like

  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 :)

    Like

  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..

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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