Contact Me

For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID.

is a Pluralsight Developer Evangelist. He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of three SQL Server books -SQL Server ProgrammingSQL Wait Stats and SQL Server Interview Questions and Answers. His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in the community.

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, whichever 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 hundred 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.
  • All comments are moderated because of heavy spam activities.

pinal “at” SQLAuthority.com

For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID.

About these ads

155 thoughts on “Contact Me

  1. Hello Pinal Sir,

    I want to undergo DBA certification with Microsoft. So could you plz guide me what initial steps i need to take as my knowledge with SQL is only with query’s related from past 3 years and not familiar with database administrator level.

  2. i want to import .sdf file database to sqlserver 2008, so can you guide how to do this.
    i have search on internet, suggest me use sql ce 3.5 for that but how to use that i don’t know.

  3. hi,
    I have 20 columns of data and 1 million rows related to technical support environment. I am given the task to search for specific keywords (around 100) in only 3 columns using SQL 2007. How can I accomplish this?

  4. I am a junior DBA and would like to know where(books/tutorials) I should use to learn about the following, I have been advised to learn the following to help with my development:

    •Storage area networks (because SANs really drive SQL Server performance — I am currently working in a SAN environment though I struggle to understand due to a lack of experience. — Where can I learn about SAN storage, “3 Pars” etc

    •Performance tuning T-SQL code — The best book for a beginner when performance tuning? NOTE:I have a good base knowledge of t-sql having worked on cursors, troubleshooting stored procs though none on performance tuning.

    •Business intelligence ??

  5. Hi Pinal,

    I am trying to figure out how to audit events in my database; ie, who disabled a job (etc) and when – do you know if I can do this and how?

    Thanks,
    Kris.

  6. Hello Pinal ,

    i want to import .csv file database to sq lserver 2005, plz can you guide how to do this.
    File Format : csv or text no column and separated by tild (~)
    and i have near about 100 files
    how to do this multiple file import in sql server 2005 and separated by tild (~)

  7. Hello Pinal ,

    i want to import .csv file database to sq lserver 2005, plz can you guide how to do this.
    File Format : csv or text no column and separated by tild (~)
    and i have near about 100 files
    how to do this multiple file import in sql server 2005 and separated by tild (~)

  8. I would like to be able to find a way, when one opens a new SQL window to have it auto save with a name so the tab is already set to a folder preset, or when I right click and say script as a file on an object it creates a file automatically based on the name if the object? Is this possible using macros?

    • Hello Pinal ,

      i want to import .csv file database to sq lserver 2005, plz can you guide how to do this. File Format : csv or text no column and separated by tild (~) and i have near about 100 files how to do this multiple file import in sql server 2005 and separated by tild (~)

  9. Hi Pinal,
    I want to know difference between function and procedure and which one is better for coding purpose. Also I want to know how to use cursor, trigger and pivot with very basic example so that I can implement it in my queries

  10. Hi Pinal,
    I want to update my table group wise, i have three cases AA,BB,CC , if group of records haveing AA means those records should be update by AA. second priority BB next CC. If group does not have AA,BB,CC no need to update .
    source Table.
    ID Name
    11 AA
    11 BB
    11 CC
    12 BB
    12 02
    12 CC
    13 CC
    13 03
    13 45
    13 65
    14 02
    14 05
    15 12

    Output table:
    ID NAME
    11 AA
    11 AA
    11 AA
    12 BB
    12 BB
    12 BB
    13 CC
    13 CC
    13 CC
    13 CC
    14 02
    14 05
    15 12

  11. Hi Pinal,
    I want to update my table group wise, i have three cases AA,BB,CC , if group of records haveing AA means those records should be update by AA. second priority BB next CC. If group does not have AA,BB,CC no need to update .
    source Table.
    ID Name
    11 AA
    11 BB
    11 CC
    12 BB
    12 02
    12 CC
    13 CC
    13 03
    13 45
    13 65
    14 02
    14 05
    15 12

    Output table:
    ID NAME
    11 AA
    11 AA
    11 AA
    12 BB
    12 BB
    12 BB
    13 CC
    13 CC
    13 CC
    13 CC
    14 02
    14 05
    15 12

  12. Hi Pinal,
    I want to update my table group wise, i have three cases AA,BB,CC , if group of records haveing AA means those records should be update by AA. second priority BB next CC. If group does not have AA,BB,CC no need to update .
    source Table.
    ID Name
    11 AA
    11 BB
    11 CC
    12 BB
    12 02
    12 CC
    13 CC
    13 03
    13 45
    13 65
    14 02
    14 05
    15 12

    Output table:
    ID NAME
    11 AA
    11 AA
    11 AA
    12 BB
    12 BB
    12 BB
    13 CC
    13 CC
    13 CC
    13 CC
    14 02
    14 05
    15 12

  13. Hi Pinal,
    Please can you help me with following query:
    I have License table where user can have multiple liceses in different state with same / different license expiry date.
    For Ex.
    LicenseID IDCode State DateLicenseExpires LicenseNo
    3465 345212 CA 11/1/2012 ca4556
    4566 345212 TN 10/10/2012 tn3456
    2345 345212 MI 10/10/2012 MI6433
    1563 345212 NJ 08/11/2012 NJ9090
    -How can select licenses with max(licenseexpirydate) with options like (DateLicenseExpires < getdate()) OR license expiring in next 30 days.
    -When there are multiple license with same expiry date then I want to select all licenses for that IDCode.
    -When there are mutiple license with different expiry date then select the license with max expiry date.
    Thank You,
    Usha

  14. Question: Can a user with ‘Sysadm’ privilege in a SQL Server Instance gain access (be it privileged or any other) to the databases created under that instance without having an active account in that instance?
    An example would be a user authenticated to the SQL server instance through Active Directory Domain group which as been granted a ‘sysadm’ privilege but the user account does not have an entry in the sysusers table in any of the databases under the same instance.
    Any help would be appreciated.
    Thanks,
    Fitsum.

  15. hi , i have a doubt i.e, i am passing column name from stored procedure as a parameter and i am getting error as ‘ Value cannot be null ‘ . i am using
    sql server-2005. please can u suggest whether this is the right format or Not.
    i ll be waiting for Your Reply..

  16. Hi,

    We’re interested in advertising on your blog / website. Let me know if you’re interested in discussing further about it.

    Thanks

    Amandeep singh

  17. Hello,

    How are you,I just want to know is it logical to store the email data of around 100 email addresses to store in Sql server table with columns like sender, subject,body etc.

    As previously i checked the size of that table which is 72 GB alone (from your script to check the size of table i get googling), when email downloading from 20 email addresses.

    Does this effect the sql server performance in future, as I have a Hard Disk Drive of 200GB.

    Please reply Urgently.

    regards,

    Farhan Shamim.

  18. hi sir,
    We have a table in which there are five columns.
    Structure of the table is

    fld_id, (Primary key)
    fld_city_en,( For storing english)
    fld_city_hb,(For storing hebrew)
    fld_city_sp,( For storing Spanish)
    fld_city_fr,( For storing Franch)
    fld_city_ru (For storing Russian)

    and there is one input box. if user enter in english text, then we have to retrieve all values of english column and if if user enter in hebrew text, then we have to retrieve all values of hebrew column and so on.

  19. Hi Pinal
    Stuck is the word what is the best way for too contact you about as I need to hopefully disscus a problem I’m having with an Update and Insert Trigger Script.
    Thanks Richard…

  20. Hi pinal
    can i have some suggestions on how to make bulk of xml files to be read by sql server 2008 r2 and we need to make use of them in sharepoint .I can take care of the sharepoint thing once the xml files are successfully moved to sql server.Can you suggest me ways in getting the xml files to be in sql server.Thanks in advance.

  21. hi iam having a table which consists of subjectcode and subjectname primary table.
    foreign table has studentid, studentname with different subjectcode, i need a query how to fetch studentid, studentname, physics, chemistry,maths,compsc

  22. tbl_Subject
    subjectcode subjectname
    33 physics
    34 chemistry
    35 maths
    41 computerscience
    45 accounts

    tbl_Student_registration
    studentid studentname subject1 subject2 subject3 subject4
    1 raj 33 34 35 41
    2 ram 33 45 34 41

    tbl_student_test
    studentid testid subjectcode(33) subjectcode(34) etc..
    1 1 20 20

    i need a query which gives me the following format

    studentid studentname physics chemistry maths computerscience
    1 raj 20 20 20 20

    20 is the marks of the student obtained from test

  23. Hi Sir,
    I’m working as a junior DBA in a company which are creating erp solutions for construction companies.How much experience is required for me inorder to have a chance to be absorbed in a database company and what are the necessary requirements that i have to obtain?.please help me.

  24. create procedure [dbo].[gmap_get_verification_data]
    (
    @return_table Table output
    )
    as
    begin
    DECLARE @temp_table TABLE
    (
    id int identity(1,1),
    state Varchar(50)
    )

    End
    return @temp_table
    end

    When i am declaring output table variable in stored procedure it is giving me error.
    Please help me how to return table in stored procedure.

  25. Hi Pinal,
    I have a table with columns Firstname and LastName.
    In Firstname i have values has ‘Joseph R.’
    In the lastname I have value has ‘Floyd’
    While searching for records i Concatenating the FirstName and LastName and returning the records.
    If users gives ‘Joseph’ or ‘Floyd’ or ‘Joseph R.Floyd’ it returns the record.
    If users searches for ‘Joseph Floyd’ or ”Joseph R Floyd’ it doesn’t returns any records. The record should be displayed for above search crietria also.
    please helpe me how to return the record.

  26. Hello Dave, I have a Question for performance….we process data daily 500k to 1mil rows these rows are broke down by a fileId (parent key), currently they go through set of procs and any number of programmers have wrote procs to work in this data based on the fileid (1 – 5000 rows) as u see its actually same record sets just different columns and some different where clauses…
    I am wonder if making a perm temptable so when this set of procs starts
    i insert all the identities into a perm temp table and then all procs work (join, select joins) on this temp table verses hiting the main data 100’s thousands of times in a 300-500 million table that needs joins , to me its a direct join on identies to the main data and the were clause will hit the rowset only

    Dave

  27. Hello Pinal,

    This blog is very much useful and Thanks for it…. I have a question here, As per the regular maintenance plan suggested by Microsoft is that, to do Rebuild index for the tables having Avg fragmentation percentage greater than 30%. If we perform Rebuild index for all tables whether it will perform defragmentation also ???
    Then what is the use of DBCC indexdefrag ???
    Please help me to get it clarified…

  28. please help calculate resolution time office working hours(9:30 to 5:30)
    my ligic is here

    alter proc ola_time
    as

    begin

    ;WITH A AS
    (
    SELECT

    call_no_id,call_all_dept,call_resolved_date,id
    FROM
    T_call_log_test –where call_no_id =’12112024′
    GROUP BY call_no_id,call_all_dept,call_resolved_date,id
    )

    SELECT d1.call_no_id,d1.call_all_dept,d1.call_resolved_date,d2.call_resolved_date as call_outtime, (Case
    when DATEDIFF(DAY, d1.call_resolved_date, d2.call_resolved_date) = 0 then 0
    else
    Case

    — If Call was Logged During Business Hours
    when DATEPART(hour, d1.call_resolved_date) >Cast(‘9:30′ as datetime) and DATEPART(hour, d1.call_resolved_date) < Cast('9:30' as datetime)
    — Then, Value is (total working minutes for day) – (minute of day that call was logged)
    then 28800 – (DATEDIFF(ss, Dateadd(day, datediff(day, 0, d1.call_resolved_date), 0), d1.call_resolved_date))

    — If Call was Logged before Start of working Hours, then Value is (total working second for day)
    when DATEPART(hh,d1.call_resolved_date) = Cast(‘5:30′ as datetime) then 0
    end
    end)

    +

    — If Call was Logged and Resolved on the Same Day,
    (case
    when DATEDIFF(DAY, d1.call_resolved_date, d2.call_resolved_date) = 1 then 0
    when DATEDIFF(DAY, d1.call_resolved_date, d2.call_resolved_date) = 0
    then

    Case
    — If Call was Logged Before Start of Business and Resolved During Business Hours
    when DATEPART(hour, d1.call_resolved_date)> Cast(‘9:30′ as datetime) and DATEPART(hour, d2.call_resolved_date) Cast(‘9:30′ as datetime) and DATEPART(hour, d2.call_resolved_date) < Cast('5:30' as datetime)
    then(DATEDIFF(DAY, d1.call_resolved_date, d2.call_resolved_date)) * 28880
    end
    end)

    +

    /*
    —————————————–
    Calculate End
    —————————————–
    */

    — Value is 0 if Call was Logged and Resolve on the Same Day
    (Case
    when DATEDIFF(DAY, d1.call_resolved_date, d2.call_resolved_date) = 0 then 0
    when (DATEDIFF(ss, Dateadd(day, datediff(day, 0, d2.call_resolved_date), 0), d2.call_resolved_date)) <= 28800
    then (DATEDIFF(ss, Dateadd(day, datediff(day, 0, d2.call_resolved_date), 0), d2.call_resolved_date))
    else

    — Otherwise, Value is (minute of day that call was logged) – (Minutes in Non-Working Hours of the Morning)
    (DATEDIFF(ss, Dateadd(day, datediff(day, 0, d2.call_resolved_date), 0), d2.call_resolved_date)) – 28800

    end) as total_working_second

    from a d1
    LEFT OUTER JOIN a d2
    ON d2.id=(d1.id+1)
    and d1.call_no_id=d2.call_no_id

  29. Hello Pinal Sir,
    I am new to SQL.
    I want to generate 2nd & 4th Saturday for entire 2013 year,
    Any way to do this in SQL SERVER?
    Thanks,
    Soham

  30. hello good morning, I have a question I hope you can help me, you can copy the SQL Server jobs in another server? If so, how should I do thanks

  31. Hi Pinal,

    I am trying to resolve one problem since a week but didnt get proper solution.

    I have created stored procedure which is working perfectly but when I put some header statements which are working okay independently but not with stored procedure and giving errors.

    Old stored procedure-
    —————————————-
    USE [JoeDB]
    GO
    /****** Object: StoredProcedure [dbo].[RPTSP_MonthlyEmployeeSched] Script Date: 01/07/2013 01:23:10 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[RPTSP_MonthlyEmployeeSched] (@from_timeframe datetime, @to_timeframe datetime,@wtksessionid int)
    AS
    BEGIN
    SELECT Distinct(R.PRIMORGJOBID) as acctid,
    R.CALENDARDATE as caldate
    into #RPTSP_CALENDAR
    FROM RDAILYSCHEDDTL R
    INNER JOIN MYWTKEMPLOYEE M
    ON R.EMPLOYEEID = M.EMPLOYEEID
    LEFT Join CALENDAR C on
    C.RPTCD = R.RETRIEVEBYSHIFTSW
    WHERE (R.CALENDARDATE BETWEEN @from_timeframe AND @to_timeframe)
    AND R.RETRIEVEBYSHIFTSW=1
    AND R.DELETEDSW=0
    AND M.SESSIONID=@wtksessionid
    AND R.PRIMORGJOBID -2
    SELECT
    1 As ISFORMATSW,
    RDAILYSCHEDDTL.EMPLOYEEID,RDAILYSCHEDDTL.PERSONFULLNM,RDAILYSCHEDDTL.CALENDARDATE,
    RDAILYSCHEDDTL.PRIMORGNMPATHTXT,RDAILYSCHEDDTL.WFCJOBORDERNUM,RDAILYSCHEDDTL.RETRIEVEBYDSTAFFSW,
    RDAILYSCHEDDTL.SHIFTSORTBYDATENUM,
    RDAILYSCHEDDTL.SHIFTDTLTYPEID,RDAILYSCHEDDTL.SHIFTDTLPCEAMT,
    RDAILYSCHEDDTL.ISSHIFTHASXFERSW,RDAILYSCHEDDTL.SHIFTSTARTDTM,RDAILYSCHEDDTL.SHIFTDTLPAYCODENM,
    RDAILYSCHEDDTL.SHIFTLABELNM,
    ROUND(DATEDIFF(d, @from_timeframe, RDAILYSCHEDDTL.CALENDARDATE) / 28, 0) monthgroup,
    CURRENCYASSIGNMNT.EMPCURLOCALE
    FROM
    CURRENCYASSIGNMNT INNER JOIN MYWTKEMPLOYEE MYWTKEMPLOYEE ON (CURRENCYASSIGNMNT.EMPLOYEEID=MYWTKEMPLOYEE.EMPLOYEEID)
    INNER JOIN RDAILYSCHEDDTL RDAILYSCHEDDTL ON (MYWTKEMPLOYEE.EMPLOYEEID=RDAILYSCHEDDTL.EMPLOYEEID)
    WHERE (RDAILYSCHEDDTL.CALENDARDATE BETWEEN @from_timeframe AND @to_timeframe)
    AND RDAILYSCHEDDTL.RETRIEVEBYSHIFTSW=1
    AND RDAILYSCHEDDTL.DELETEDSW=0
    AND MYWTKEMPLOYEE.SESSIONID=@wtksessionid
    AND RDAILYSCHEDDTL.PRIMORGJOBID -2
    UNION
    SELECT
    0 As ISFORMATSW,
    CAST(NULL as INTEGER),
    CAST(NULL as NVARCHAR(30)),
    T.CALDATE As CALENDARDATE,
    R.PRIMORGNMPATHTXT,
    0, 0, 0,
    CAST(NULL as INTEGER),
    CAST(NULL as NVARCHAR(20)),
    0,CAST(NULL as datetime),
    CAST(NULL as NVARCHAR(20)),
    CAST(NULL as NVARCHAR(20)),
    ROUND(DATEDIFF(d, @from_timeframe, T.CALDATE) / 28, 0) monthgroup,
    NULL
    FROM
    #RPTSP_CALENDAR T
    INNER JOIN RDAILYSCHEDDTL R
    ON acctid = R.PRIMORGJOBID
    WHERE T.caldate >= @from_timeframe
    AND T.caldate < @to_timeframe
    ORDER BY monthgroup, CALENDARDATE
    drop TABLE #RPTSP_CALENDAR
    END

    ——————————————————

    New Stored Procedure:
    —————————————————————
    USE [JoeDB]
    GO
    /****** Object: StoredProcedure [dbo].[RPTSP_MonthlyEmployeeSched] Script Date: 01/04/2013 11:13:57 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create PROCEDURE [dbo].[RPTSP_MonthlyEmployeeSchedCAL] (@from_timeframe datetime, @to_timeframe datetime,@wtksessionid int)
    AS
    BEGIN

    DECLARE @StartDate DATETIME, @EndDate DATETIME
    SET @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) –First day of current month
    SET @StartDate = DATEADD(DAY,-DATEPART(WEEKDAY,@StartDate)+1,@StartDate) –First day of calendar
    SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) –Last day of month
    SET @EndDate = DATEADD(DAY,6-DATEPART(WEEKDAY,@EndDate),@EndDate) –Last day of calendar

    ; WITH Dates([Date]) AS (
    –Select First day in range
    SELECT CONVERT(DATETIME,@StartDate) AS [Date]
    UNION ALL
    –Add a record for every day in the range
    SELECT DATEADD(DAY, 1, [Date]) FROM Dates WHERE Date < CONVERT(DATETIME,@EndDate)
    ), Events AS (
    –Create some events to place on the calendar

    SELECT Distinct(R.PRIMORGJOBID) as acctid,
    R.CALENDARDATE as caldate
    into #RPTSP_CALENDAR
    FROM RDAILYSCHEDDTL R
    INNER JOIN MYWTKEMPLOYEE M
    ON R.EMPLOYEEID = M.EMPLOYEEID
    LEFT Join CALENDAR C on
    C.RPTCD = R.RETRIEVEBYSHIFTSW
    WHERE (R.CALENDARDATE BETWEEN @from_timeframe AND @to_timeframe)
    AND R.RETRIEVEBYSHIFTSW=1
    AND R.DELETEDSW=0
    AND M.SESSIONID=@wtksessionid
    AND R.PRIMORGJOBID -2
    SELECT
    1 As ISFORMATSW,
    RDAILYSCHEDDTL.EMPLOYEEID,RDAILYSCHEDDTL.PERSONFULLNM,RDAILYSCHEDDTL.CALENDARDATE,
    RDAILYSCHEDDTL.PRIMORGNMPATHTXT,RDAILYSCHEDDTL.WFCJOBORDERNUM,RDAILYSCHEDDTL.RETRIEVEBYDSTAFFSW,
    RDAILYSCHEDDTL.SHIFTSORTBYDATENUM,
    RDAILYSCHEDDTL.SHIFTDTLTYPEID,RDAILYSCHEDDTL.SHIFTDTLPCEAMT,
    RDAILYSCHEDDTL.ISSHIFTHASXFERSW,RDAILYSCHEDDTL.SHIFTSTARTDTM,RDAILYSCHEDDTL.SHIFTDTLPAYCODENM,
    RDAILYSCHEDDTL.SHIFTLABELNM,
    ROUND(DATEDIFF(d, @from_timeframe, RDAILYSCHEDDTL.CALENDARDATE) / 28, 0) monthgroup,
    CURRENCYASSIGNMNT.EMPCURLOCALE
    FROM
    CURRENCYASSIGNMNT INNER JOIN MYWTKEMPLOYEE MYWTKEMPLOYEE ON (CURRENCYASSIGNMNT.EMPLOYEEID=MYWTKEMPLOYEE.EMPLOYEEID)
    INNER JOIN RDAILYSCHEDDTL RDAILYSCHEDDTL ON (MYWTKEMPLOYEE.EMPLOYEEID=RDAILYSCHEDDTL.EMPLOYEEID)
    WHERE (RDAILYSCHEDDTL.CALENDARDATE BETWEEN @from_timeframe AND @to_timeframe)
    AND RDAILYSCHEDDTL.RETRIEVEBYSHIFTSW=1
    AND RDAILYSCHEDDTL.DELETEDSW=0
    AND MYWTKEMPLOYEE.SESSIONID=@wtksessionid
    AND RDAILYSCHEDDTL.PRIMORGJOBID -2
    UNION
    SELECT
    0 As ISFORMATSW,
    CAST(NULL as INTEGER),
    CAST(NULL as NVARCHAR(30)),
    T.CALDATE As CALENDARDATE,
    R.PRIMORGNMPATHTXT,
    0, 0, 0,
    CAST(NULL as INTEGER),
    CAST(NULL as NVARCHAR(20)),
    0,CAST(NULL as datetime),
    CAST(NULL as NVARCHAR(20)),
    CAST(NULL as NVARCHAR(20)),
    ROUND(DATEDIFF(d, @from_timeframe, T.CALDATE) / 28, 0) monthgroup,
    NULL
    FROM
    #RPTSP_CALENDAR T
    INNER JOIN RDAILYSCHEDDTL R
    ON acctid = R.PRIMORGJOBID
    WHERE T.caldate >= @from_timeframe
    AND T.caldate < @to_timeframe
    ORDER BY monthgroup, CALENDARDATE
    drop TABLE #RPTSP_CALENDAR
    ) SELECT
    — Number the records based on the date, if multiple records have the same
    — date then they will be numbered the same. Used in calculation to determine
    — row record is to display on.
    [Order] = DENSE_RANK() OVER (ORDER BY d.[Date]),
    d.[Date], — date used in all caluclation for date
    [WeekDay] = DATEPART(WEEKDAY, d.[Date]), –generates matrix columns
    [Day] = DATEPART(DAY,d.[Date]), –used to display day of month on calendar
    [Month] = DATEPART(MONTH,d.[Date]), –used in some calculations for display
    e.SHIFTSTARTDTM, — used to get the time of the event
    e.SHIFTLABELNM, –event details to display
    e.PERSONFULLNM,
    e.SHIFTSTARTDTM
    –CTEs defined above are used as the queries for the results
    FROM Dates d
    LEFT JOIN Events e ON CAST(CONVERT(VARCHAR(10),e.SHIFTSTARTDTM,101) AS DATETIME) = d.[Date]

    –Set the maximum times the cte can recurse
    OPTION (MAXRECURSION 100)

    END

    ———————

    Error messages:
    Msg 156, Level 15, State 1, Procedure RPTSP_MonthlyEmployeeSchedCAL, Line 24
    Incorrect syntax near the keyword 'into'.
    Msg 102, Level 15, State 1, Procedure RPTSP_MonthlyEmployeeSchedCAL, Line 76
    Incorrect syntax near ')'.

    Thanks,
    Pravin

  32. Get Same Multiple Rows from One Row Data in Table Sql Server

    Hi Pinal,

    i want to get same multiple rows from 1 row data in table sql server.
    Table “schedule”
    Name | Hospital | Visit
    Ana | RSIA | 3
    Budi | RSIB | 2

    The query result is
    Ana | RSIA | 3
    Ana | RSIA | 3
    Ana | RSIA | 3
    Budi | RSIB | 2
    Budi | RSIB | 2

    Need someone help to create a query to get above result. Thanks.

  33. Hi Pinal,
    I was able to import data from a particular excel into DB via SQL Server Agent job scheduler, perfectly.

    But, now I came to know that.. the user will daily pull out an Excel Report ( perhaps with same Name+current-date as the file name) and drop that into the shared folder. Now the SQLServer Agent Scheduler ought to pick the latest excel and kindly append the data into the particular table of the database.
    My problem is while creating the SSIS package in wizard I will necessarily be giving one particular excel name in the DataSource. Any solution that you can suggest?

  34. I am using one column in database which uses format datetime…rightnow one stored procedure updates datetime column in database with some of the rows have values of some date..which is most of the time like in datetiome format and same value …something like 1900-01-01 00:00:00…something like that i am exactly not sure…but instead that value..i want each value replaced with just”-” instead that date now…but as that column has datetime format …i am unable to store “-” in that value …sql shows error..that “-” cant be stored in datetime format..is there any way i can able to do that?

  35. Hi U can use store select ‘ExcelImport’+convert(varchar(50),Getdate(),126)+’.xls’ in variable and then import with bulk import which will take file name from variable.

  36. I have SQL server 2005 as DB server. Is this possible to create replication between SQL 2008 R2 (subscriber) and SQL server 2005 (publisher). If possible then big next….HOW ?

  37. I am fresher in learning SQL, i have studied DBMS during my B.Tech. that’s the basic.. Please provide me the link in your blog to start freshly from the beginning of SQL.

  38. Hi,
    What is the equivalent to CONNECT BY in Oracle please?
    It would be nice if you add such article with examples to your great library.
    Thank you, Art.

  39. I am using SQL Server 2012. I have a called “ProjectDetails” where each client can have access to One OR More projects. The table has ClientId, ClientName, ProjectName. I need to write a query to return multiple ProjectNames next to the ClientName column as a single comma seperated list. The output I need should contain details such as: ClientId=3, ClientName=John, ProjectName= ABC, DEF, XYZ….Any help is appeciated.

  40. OBSCatalogItem
    ID(PK)
    Name

    Observation
    ID(PK)
    OBSItemID(FK)
    Value Text

    ObservationDocument
    ObservationID(FK)
    FlowSheetID(FK)
    ObservationDocumentID(PK)

    FlowSheet
    ID(PK)

    FlowSheetChart
    ID(PK)
    ClientID(FK)
    FlowsheetID(FK)

    Client
    ID(PK)

    Above is my different table structure. My table names are OBSCatalogItem,Observation,ObservationDocument,FlowSheet,FlowSheetChart,Client.
    After all table name specified my columns and their constraints.

    I need to retireve value text from observation table.

    ClientID=1 which is specifically declared.

    Name(OBSCatalogItem) = name is specific. it’s name is ‘abc’

    How do i write a query to retireve particulary for this client.

    Thanks a lot.

  41. Hi
    I want to use two databases simultaneously on a single page. Can we make a join between different tables of different database with a single select statement. i have make one of the query that is working on SQL Server enterprise fine. but when i am using it on my page then it is giving security error of other database.

  42. Sir, i need in sqlserver to apply constraint particular columns value’s count should not be more than some value in select clouse.

  43. Pinal we are an Technology firm in WA State and looking for qulified consultant for SQL2012 – also if possible looking for a small vendor firm who can help us service our client in that technology domain.. do let us know

  44. Hi sir ,
    i want to write sql query.In this i first create database before creating database i check for whether database exist or not and execute specific query depending on result.So for this i have used IF Else block
    but it’s not executing properly. please have look in my query and please suggest solution.If database exist i do nothing,but if doesn’t exist i create database,it’s table,stored procedure etc.

    IF EXISTS(SELECT name FROM sys.databases WHERE name = ‘SampleDB’)
    begin
    PRINT ‘Exist.’
    end
    Else
    begin
    CREATE DATABASE SampleDB
    use SampleDB
    exec sp_dboption N’SampleDB’, N’autoshrink’, N’false’
    exec sp_dboption N’SampleDB’, N’ANSI null default’, N’false’
    exec sp_dboption N’SampleDB’, N’recursive triggers’, N’false’
    exec sp_dboption N’SampleDB’, N’ANSI nulls’, N’false’
    exec sp_dboption N’SampleDB’, N’concat null yields null’, N’false’
    exec sp_dboption N’SampleDB’, N’cursor close on commit’, N’false’
    exec sp_dboption N’SampleDB’, N’default to local cursor’, N’false’
    exec sp_dboption N’SampleDB’, N’quoted identifier’, N’false’
    exec sp_dboption N’SampleDB’, N’ANSI warnings’, N’false’
    exec sp_dboption N’SampleDB’, N’auto create statistics’, N’true’
    exec sp_dboption N’SampleDB’, N’auto update statistics’, N’true’
    CREATE TABLE [dbo].[BrandMaster] (
    [BrandId] [int] IDENTITY (1, 1) CONSTRAINT [PK_BrandMaster] PRIMARY KEY NOT NULL ,
    [BrandName] [nvarchar] (50) NOT NULL ,
    [BrandStatus] [bit] NOT NULL ,
    )
    CREATE TABLE [dbo].[BrandProductMaster] (
    [BrandProductId] [int] IDENTITY (1, 1) CONSTRAINT [PK_BrandProductMaster] PRIMARY KEY NOT NULL ,
    [ProductId] [int] NOT NULL ,
    [BrandId] [int] NOT NULL ,
    [Units] [nvarchar] (15) NULL ,
    [Status] [bit] NOT NULL ,
    )
    CREATE TABLE [dbo].[BrokerMaster] (
    [BrokerId] [int] IDENTITY (1, 1) CONSTRAINT [PK_BrokerMaster] PRIMARY KEY NOT NULL ,
    [BrokerName] [nvarchar] (100) NOT NULL ,
    [BrokerPercentage] [float] NOT NULL ,
    [BrokerAddress] [nvarchar] (100) NULL ,
    [BrokerTelephoneNo] [bigint] NULL ,
    [BrokerMobileNo] [bigint] NULL ,
    [BrokerFaxNo] [bigint] NULL ,
    [BrokerEmailId] [nvarchar] (75) NULL ,
    [BrokerStatus] [bit] NOT NULL ,
    )
    INSERT INTO [dbo].[BrokerMaster] ([BrokerId],[BrokerName],[BrokerPercentage],[BrokerAddress],[BrokerEmailId],[BrokerStatus]) VALUES (1,’No Broker’,0.0,”,”,1)
    end

    here i only added limited table for e.g purpose but actually i have 20 to 25 table with record, number of procedures etc.When i execute code it show’s error like Database ‘SampleDB’ does not exist

    at this line use SampleDB.how i can execute multiple statements in else block?

  45. i am facing issues with my query

    select week_number
    ,year
    from accounting_calender
    where week_number3-6
    and year=2013

    here in this query im passing the week_number “3” and year “2013” through my reporting tool.

    im getting the following output

    | Week_number | year |
    ——————————
    | 3 | 2013 |
    | 2 | 2013 |
    | 1 | 2013 |

    but here in my accounting calender table i got even the entries for 2012 too.
    so here im subtracting “-6″, so it has to go to previous year weeks also..

    iam looking for something like below output

    | Week_number | year |
    ——————————
    | 51 | 2012 |
    | 52 | 2012 |
    | 53 | 2012 |
    | 3 | 2013 |
    | 2 | 2013 |
    | 1 | 2013 |

    kindly guide me.

    i have only read-only access.

  46. hello my name is antony and i am working as Sql DBA and here we have one sql server and we daily take the back up of this server. and also we have ARCGIS software this is a geographical mapping softw are four users daily working on it and we take the backup of ARCGIS to the local system. my goal is that i want to integrate sqlserver backup with ARCGIS back. that means if we once take the backup of sql server it must contain the backup of ARCGIS software. simply .mdf should contain the backup file arcgis if you have any experiance on issue like this and any idea for you please reply me

    antony

  47. Hi Pinal Dave

    I want to Create Stored Procedure Where I need to pass the string Variable with comma delimitation is there any way to do this For Eg :- Myvar= (‘ABC’,’CDF’,’XZ’,’MNH’)

    Regards
    Vanishree

  48. Hey dude….
    Scenario is I am inserting records from different database to one centralized database .First I inserted Trigger to all source database but it was not working fine it was deleting source records.Now,I implemented replication (transactional).Intially I used to hardcode the DBID in the source trigger but now I am using replication so I want to know the dbid from from source database to centralised databse.Which replication should i use and how can i put databseID in replication….

  49. Hi Rahul,

    If in your scenario, cetralized database needs database changes then you should use Transactional replication or other wise if you use it as a reporting purpose then you can use merge replication.

    Jitesh…

  50. Here i need to some correction in my previous post.
    you can use the Trasactional database in reporting purpose or if any changes done on centralized database you need to implement Merge replication.

  51. Pingback: SQL SERVER – Change Order of Column In Database Tables | Glow Online Jobs

  52. Dear Pinal,

    I am giving one of example of Sql query which I used in SSRS Report. In below example used few sub queries to achieve result. But few more queries are there where lot of sub queries used(15-20) in same manner. Basically what we want the count of record based on different condition. Because of lot of sub queries used in Sql queries its taking lot of time to execute (Performance issue). Is there are any alternate way to achieve same result within minimum time of execution.Please give valuable suggestion/solution.

    Example:
    SELECT FI.Name,
    (SELECT COUNT(*) FROM FilterIncidentTrans AS FIT
    WHERE FIT.IsPunUnits=1 AND FIT.RecId=FI.RecId) AS [PUN UNITS COUNT],
    (SELECT COUNT(*) FROM FilterIncidentTrans AS FIT
    WHERE FIT.IsREBELUnits=1 AND FIT.RecId=FI.RecId) AS [REBEL UNITS COUNT],
    (SELECT COUNT(*) FROM FilterIncidentTrans AS FIT
    WHERE FIT.IsOtherUnits=1 AND FIT.RecId=FI.RecId) AS [Other UNITS COUNT]
    FROM FilterIncident AS FI
    WHERE FI.StartDate=@StartDate and FI.EndDate=@EndDate

  53. Hi,
    I teach a job training class and I was hoping to get a pdf copy of your book to use for my class. I teach people new programming skills that have lost their jobs.

    Thanks,
    Chuck

  54. Hey Pinal Could you tell me is there any provision for getting to know how many times and when password for my sql server 2005 was changed. Does it maintain any log on its own?

  55. Hi Pinal,
    Could you please help me on this.
    I am looking for the best way to retrieve following information. I got a table with products and options assigned to them. Then I got a list of option combinations and wanting to know the existence of products for my combinations.

    product to option table

    p1 – option1
    p1 – option2
    p1 – option3
    p2 – option2
    p2 – option3
    p3 – option3
    p3 – option4
    My option combinations are [option1,option2], [option1,option4], [option2,option4] and [option3,option4]

    My expected result is [option1,option2] and [option3,option4]

    -The selection is based on, there should be at least one product associated with all the options in a combination. Here [option1, option2] is associated to p1 and [option3,option4] associated to p3. If the combination is distributed among multiple products then that combination will not be selected. Also there could be n number of options in a combination.(Please note this table contains several millions of records and there could be hundreds of combinations to check.)
    Thanks,
    Sujith

  56. Hello Pinal,
    How to allow for nulls in foreign keys:
    I have normalized table with lookups and the lookup ID as foreign keys. I set the relationship in a database diagram: It looks beautiful / readable. Now I am stuck as some of the foreign keys are not mandatory in the main normalized table!.
    Please advise,
    Regards,
    Raghu

  57. Hi Panal,
    This is Gavisiddappa form Gadag Dist,I am good in sql server 2008 ,ASP.NET ,C# and Other server side scripting languages,but still im not getting job,can u suggest me about this.

    Im waiting for response.

  58. Dear Sir,
    I here by Request you to help me out in the following scenario.

    I am maintaining an excel file,and a database has been created in the sql 2005 and imported the excel file to that database.Iam able to view all the excel sheet entries in the sql database which i have created.

    Is there any Possibility that while updating my excel sheet the same needs to be updated in Sql Database also.Is there any possibility,if so kindly help me out sir.

    Regards,
    sumesh.Menon

  59. Hello Sir
    Plz. help me
    I my database date saved in dd/mm/yyyy format and datatype is varchar(50) and not able to apply the between query on dates .
    like if i apply between query ‘1/3/2013′ and ‘4/5/2013′
    then show record all year weather exists in 2009,2010,2013 just match 1 and 4 .

    mail me if anyone can resolved this issue
    [email removed]

    Regards
    navi

  60. Is it possible to write a sql script which will run on remote computer ( having only .netframework installed and no SQL server installed).
    Also script should update table in database which is on the server.( Main server).

  61. Need advice. I have been out of IT work force since 2008, so I am extremely rusty on SQL (I used 2005 and little of 2008). I am ready to immediately start SQL classes and getting certification, starting today would be great. There seem to be so many books and classes on 2008 but I think I need to look at latest version 2012. I have 2012 express installed on home machines. Can you recommend any online courses free or fee based that would help me get prepared for certification exams? If so, can you recommend path for getting there. I am looking at just an associate certification at this time.

  62. Hi Pinal ,

    I have a requirement like the O/P data that i am receiving from the DB should be displayed in Message Source like one mail that has to contain all the O/P data that needs to be send as mail . I tried with recordset along with Foreach loop comprised with Send Mail Task but this is not send a single mail that contain all O/P of data from DB.

    Kindly provide me some light on this which could be really helpful for me.

    Thanks
    Athrey

  63. hi I am install SQL server 2008 for CUEAC. I am stuck at database engine configuration step. I select mix mode and put my password. my only question is whats the account I have to select.

  64. Dave, I am following you every day. Thank for your articles!

    I have a problem with microsoft access VBA code.

    I have two datasheet subform (A) and (B) linking to a master form. I want to copy a column-field values from subform (A) to subform (B). Do you know some VBA code or SQL statement to do this?

    Now I am using copy and paste manually.

  65. Hi Pinal,

    I have a problem while working with PERCENTILE function in SQL2008R2 as this is not a built in function. I tried to write the code for the same but having some problems as it took too much of time(more than an hour because the table contained about 9.5 lacs records.) I wrote the following code:

    First created the functions:

    –CREATE FUNCTION dbo.FDIV
    — (@numerator float,
    — @denominator float)
    –RETURNS float
    –AS
    –BEGIN
    — RETURN CASE WHEN @denominator = 0.0
    — THEN 0.0
    — ELSE @numerator / @denominator
    — END
    –END
    –GO

    –CREATE FUNCTION dbo.LERP
    — (@value float, — between low and high
    — @low float,
    — @high float,
    — @newlow float,
    — @newhigh float)
    –RETURNS float — between newlow and newhigh
    –AS
    –BEGIN
    — RETURN CASE
    — WHEN @value between @low and @high and @newlow <= @newhigh
    — THEN @newlow+ dbo.FDIV((@value-@low),@high-@low)*(@newhigh-@newlow)
    — WHEN @value = @low and @newlow is not NULL THEN @newlow
    — WHEN @value = @high and @newhigh is not NULL THEN @newhigh
    — ELSE NULL
    — END
    –END
    –GO

    select top 30000 * into #temp from readtrace.tblbatches
    –drop table #temp

    select * from #temp

    Declare @pp float
    Select @pp = 0.9

    Select
    @pp as factor,
    dbo.LERP(max(d), 0.0, 1.0, max(a.duration), max(b.duration))as Percentile
    From
    (
    Select floor(kf) as k , kf-floor(kf) as d
    from (
    select 1+@pp*(count(*)-1) as kf from #temp
    ) as x1
    ) as x2
    join #temp a
    on
    (select count(*) from #temp aa
    where aa.duration < a.duration) < k
    join #temp b
    on
    (select count(*) from #temp bb
    where bb.duration < b.duration) < k+1

    When I tried using temp table(fetched only 30,000 records) in place of actual table that contained about 9.5 lac records, it took about 28 mins. I believe that the time taken is mainly because of the nested inner join.

    Please suggest a better way to implement the same thus reducing the cost and time.

    Thanks,
    Shubham

  66. Hi Pinal,

    First of all congrats on your successful blog and career you have achieved. Hardwork repays.

    Secondly have you thought about ordering the comments in a Descending order. You know why.

    Thirdly, just a suggestion. Please try and upgrade your website design. I am sure if you post that you need to redesign your website, there will heaps of people around the world willing to it for free for you. If not, I will.

    Thanks and all the best..
    Maz

  67. Hi Sir,

    i have one table that have 5 laks rows

    when i make any update statement & Select statement it is taking long time to execute
    some times it is struct

    please help me

    [email removed]

  68. Use sqldataadapter for faster query execution. Use store procedure if possible. Bcs its precompiled query by sql query builder.
    let me know if still you are facing problem. Possible then mail your code to test.

  69. Hello Pinal Sir,
    In my production server having merge replication on One DB.
    And i need to move same distribution databse to another serve so
    Is it possible to create distribution db and publisher db on different server?

    Thanks

  70. I am using recursive CTE to get hierarchical resultset. Unfortunately the resultset in CTE is not coming in the order I want. Hence I need an identity column which will get incremented as and when rows are getting added in the recursive CTE so that I can order by on this identity column to sort my CTE resultset. I tried using ROW_Number, IDENTITY, but in vain. COUNT or a GROUP by is not allowed in a recursive CTE. Can you please provide me a solution.

  71. Hi Pinal,

    I need your suggestion for performing the below task.
    I have a script which would be run on every month end. That script refers to several tables. The script is designed in such a way that -it is Run_Id specific(Meaning Even if the user sets wrong value to the run-time parameter, he can run it again and all the tables will record the data for that particular run.) But there are some complex tables that are involved. Meaning it gets truncated and loaded with new set of data. But now my requirement is, I need to make this script to work for parallel runs too.
    So in this case, the table that are getting truncated in the mid of the run or before a run etc.. will be a hindrance. So I need to create different bucket of data. I can think of using temp tables. But the amount of data we are dealing with is million of records in every table. How to achieve this. Please help me with this. I truly appreciate you help.

    Thanks
    Nisha.V Krishnan

  72. Hi Pinal,

    Your blog helped me in so many different ways. I have a quick question can you please help on this. Am migrating data from SQL database to Microsoft CRM 2011. The process is OLDB – Script Component. Within the script component am calling CRM webservice and creating records in CRM. But its taking long time to migrate data, am trying to migrate 70million records. Can you please advise how can I optimize the performance. My system configuration is 2012 SQL Server, Ram : 8GB.

    Thanks
    Uday

  73. I have one issue with 2005 SQL ASPstate database. DB size in growing to 25 gb and log file to 100 gb. My db is in simple recovery mode. So please can you tell me the appropriate solution.

  74. Hello,

    I have a number of SQL Server Agent Jobs, all with different schedules. For example, a job can run every other week on Mondays. I need to determine given a specific date and job id, if that sql server agent job will be run. For example, If I 11/18/2013 to some stored procedure or query a 1 will be passed if the specific SSA job will run on that date.

    I know you can get the next_run_date value form the MSDB..SYSJOBSCHEDULES but was wondering if there is a way to take this a step future.

    thanks in advance.
    Chris

  75. Hi Pinal,

    You blog is great. It is such a valuable resource.

    I have a question that I’m hoping you can help me with:

    I have a set of SQL Server Agent Jobs all with a defined schedule. For example, a job runs on the 2nd tuesday of every month. What I need to determine is if any job will run given a specific date. Therefore, if I pass in 2013-12-09 as the date and a job id into a stored procedure a 1 will be returned if the job will run on that day and a –1 will be returned if the job will not be run on that day.

    I know I can get the next_run_date value from MSDB..SYSJOBSCHEDULES table but need this extended somehow to pass any date in the future.

    thanks in advance
    Chris

  76. Hi Pinal

    I am not the regular reader but whenever I get time or stuck in some problem I refer your blog. Thanks for providing or sharing real time practices, experience and knowledge with us.

    Small A very small question
    Having a table Called ppp by mistake it is renamed as [ppp]

    It is now not accessible .Sql Server does not treat it as an object

    “Invalid object name ‘ppp’.”

    Question
    Why Sql server not treating it as an object?
    Can we access the table contents without renaming it?
    Can we rename it by query?

    I can rename it by object explorer that resolve my problem, now my concern is what reason behind it.

    Thanks in advance
    Pankaj Wadhwa

  77. HI pinal sir,
    Sir i have doubt.
    How to update a specific(single) row based on DocumentID in DocumentHistory table?
    Actually i have to update one record to particular latest document record in DocumentHistory table.

    DocumentHistory table structure:
    ———————————————-

    DocumentHistoryID DocumentID StatusID IsMobile InsertedTimeStamp
    ———————– —————- ———– ———— ————————-
    10001 123 1 0 2013-12-01 13:03:11.933
    10002 123 3 0 2013-12-0212 13:03:11.933
    10003 123 4 0 2013-12-03 13:03:11.933
    10004 123 5 0 2013-12-04 13:03:11.933
    10005 123 6 0 2013-12-05 13:03:11.933

    Now i want to update latest Document record only(i.e for 10005 ).

    i know this below query to update but insted of this, is there any way to update top1 record with Order by Clause??

    Please tell me Pinal sir.

    Awaiting for your reply,
    Pratap,
    Daily Rss Feed Reader.

    • My query is

      UPDATE tblDocumentStatusHistory SET IsMobile=2 WHERE DocumentID = 10005 AND DocumentStatusHistoryID =
      (SELECT TOP 1 DocumentStatusHistoryID FROM tblDocumentStatusHistory WHERE DocumentID=10005 ORDER BY DocumentStatusHistoryID DESC)

  78. Hi,

    I am Interested in On-line training in T SQL and BI basics. Do you offer any online course?
    How much are the charges? And when can I start?

    Thanks

    Arshad

  79. Hi Pinal

    I am new to SQL and for quite some time, I have been trying to get your book “SQL Server Interview Questions and Answers for all Database Developers and Database Administrators” from Amazon but it always shows “Out of Stock”.

    Can you please tell me from where can I get this book or atleast the soft copy of it.

    Regards
    Gaurav Vashishtha

    • Good day !

      Respected Pinal,

      Are you planning / have any plan, to launch SQL Server Online Video Courses (including Query Tuning & other lots of stuff) ? Please let me/us know.

      _Thanks

  80. I need an overview how good are databases are documented. Can we create a t-sql script that will give following result for a database:
    Database DatabaseDesc TableDesc ColumnDesc StoredProcedureDesc ParameterDesc FunctionDesc FunctionParameter ViewDesc ColumnDesc
    DB Name 100% 90% 80% 75% 76% 40% 0% 5% 2%

    If extended property for database description exists column DatabaseDesc is 100%, if not 0%. If you have 20 tables within the database and 15 having a description then TableDesc is 75 % and so on.

    Please help.

  81. Hi Pinal,

    I want to create a stored procedure which return me two values,1st one is list of employee and 2nd one is a scaler value.

    Please give me solution.

  82. I am using PhpMyAdmin console for my MySQL DB. I have a Routine in DB and I want to call the routine on the last date of every month. I set up an event and for testing purpose I have set it for 1 min interval:

    CREATE EVENT `monthly_report` ON SCHEDULE EVERY 1 MINUTE STARTS ‘2014-02-05 07:50:00′ ON COMPLETION PRESERVE ENABLE DO CALL Test_Proc()
    But it is never been called. Can anyone help me?

    Regards,
    Surodip

  83. Hi,
    I need your help .

    Suppose my table looks like this now

    id lob
    1
    2
    3

    when i query

    declare @match varchar(10)
    set @match = ‘a’
    select * from #a
    SELECT id, lob.query(‘data(/xml/info/@wants)’) as Value
    FROM #a
    WHERE lob.exist(‘/xml/info/data[@lob=sql:variable(“@match”)]’) =1

    i get the o/p as

    id Value
    1 10
    1 30

    So my doubt is how can i pass “@lob in sql:variable instead of @lob=sql:variable” in where clause i

  84. Foreign Key Question

    I have a users table with a primary key of UserID

    I also created a ToDo table where users can enter to do item for another user. The ToDo table has a CreatedBy and ModifiedBy field that I’d like to create FK relationships back to the User Table to the UserID field. I’d like to set On Delete = Set Null, so if for whatever reason the User was deleted it would just change the CreatedBy / ModfiedBy value to Null

    If I setup the first FK using CreatedBy everything is fine, when I try to setup the ModifiedBy FK I get a “may cause cycles or multiple cascade paths” error.

    Can you help me?

    thanks a lot
    Seth

  85. am using geography data type in SQL Server 2012. I have stored location of a city in that column. Now I want to show that location in Google maps. How can I do it? Please give me advise..

  86. PLEASE HELP!
    Hi–I am trying to add a column that does a running total on my [Sales to Date{ column, partitioned by year and by MediaId. (SQL Server 2005)
    SELECT YEAR,

    CASE MediaID
    WHEN ‘D’ THEN ‘DMAG’
    WHEN ‘CEO’ THEN ‘DCEO’
    WHEN ‘DH’ THEN ‘DH’
    WHEN ‘DREAL’ THEN ‘DREA’
    WHEN ‘DW’ THEN ‘DW’
    WHEN ‘DON’ THEN ‘DON’
    WHEN ‘ONDR’ THEN ‘ONDR’
    WHEN ‘DB’ THEN ‘DB’
    WHEN ‘DHCD’ THEN ‘DHCDaily’
    WHEN ‘MD’ THEN ‘DHCDir’
    WHEN ‘DMOM’ THEN ‘DMOM’
    WHEN ‘COOK’ THEN ‘DCOOK’
    WHEN ‘IPHO’ THEN ‘IPHO’
    END AS Publication
    ,CASE MediaID
    WHEN ‘D’ THEN ‘PUBLICATIONS’
    WHEN ‘CEO’ THEN ‘PUBLICATIONS’
    WHEN ‘DH’ THEN ‘PUBLICATIONS’
    WHEN ‘DREAL’ THEN ‘PUBLICATIONS’
    WHEN ‘DW’ THEN ‘PUBLICATIONS’
    WHEN ‘DON’ THEN ‘ONLINE’
    WHEN ‘ONDR’ THEN ‘DIRECTORY ENH.’
    WHEN ‘DB’ THEN ‘PUBLICATIONS’
    WHEN ‘DHCD’ THEN ‘ONLINE’
    WHEN ‘IPHO’ THEN ‘ONLINE’
    WHEN ‘MD’ THEN ‘PUBLICATIONS’
    WHEN ‘DMOM’ THEN ‘PUBLICATIONS’
    WHEN ‘COOK’ THEN ‘PUBLICATIONS’
    END AS BusinessScope
    , CONVERT(datetime, IssueDate, 103) AS IssueDate
    , CONVERT(money,(SUM(A_Revenue)),0) AS [Sales to Date]
    , CONVERT(money,(SUM(B_Revenue)),0) AS [Budget]
    , CASE YEAR
    WHEN 2013 THEN ”
    ELSE SUBSTRING(CloseDate,1,10) END AS CloseDate
    , CASE DaysTilClose
    WHEN ” THEN NULL
    ELSE DaysTilClose END AS DaysTilClose

    FROM
    (
    SELECT isnull(Issues.YEAR1, YEAR(InOrdDet.IssueDate)) AS Year
    ,INORDDET.MediaId
    ,INORDDET.IssueDate
    ,isnull(INORCOAM.SLPRSNID,INORDDET.SLPRSNID) as SalesRep
    , isnull(ORDTUSDF.Picklist1,”) as Section
    ,SUM(INORDDET.RevenueAmount * (isnull(INORCOAM.PRCTOSAL,10000) * .0001)) AS A_Revenue
    ,0 AS B_Revenue
    , CASE
    WHEN convert(varchar,CloseDate,21) <= convert(varchar,GETDATE(),21) THEN 'CLOSED'
    ELSE convert(varchar,CloseDate,21) END AS CloseDate
    , CASE
    WHEN convert(varchar,CloseDate,21) <= convert(varchar,GETDATE(),21) THEN ''
    ELSE -1*(datediff(day, CloseDate, getdate())) END AS DaysTilClose

    FROM dbo.INORDDET

    LEFT JOIN IV00101
    ON INORDDET.ITEMNMBR = IV00101.ITEMNMBR
    LEFT JOIN dbo.INORCOAM
    ON dbo.INORDDET.OrderNumber = dbo.INORCOAM.OrderNumber
    LEFT JOIN INSHEAD
    ON INORDDET.OrderNumber = INSHEAD.OrderNumber
    LEFT JOIN ORDTUSDF
    ON INORDDET.OrderNumber = ORDTUSDF.OrderNumber
    AND INORDDET.SEQNUMBR = ORDTUSDF.SEQNUMBR
    LEFT JOIN Issues
    ON INORDDET.MediaId = Issues.MediaId
    AND INORDDET.IssueDate = Issues.ISSUDATE
    LEFT JOIN CloseSchedule
    ON InOrdDet.MediaId = CloseSchedule.MediaId
    AND InOrdDet.IssueDate = CloseSchedule.IssueDate

    WHERE IV00101.ITMCLSCD ‘FEES-CHGS’
    and dbo.INORDDET.IsBarterTrade = 0
    and INORDDET.Status 4
    and isnull(INORCOAM.SLPRSNID,INORDDET.SLPRSNID) ‘HOUSE’
    and isnull(INORCOAM.SLPRSNID,INORDDET.SLPRSNID) ‘TRADE’
    and isnull(INORCOAM.SLPRSNID,INORDDET.SLPRSNID) ‘TRADE – ADV’
    and isnull(INORCOAM.SLPRSNID,INORDDET.SLPRSNID) ‘ADVERTORIAL’
    and INSHEAD.HOLD 1
    and INORDDET.MediaId ‘DSOC’
    AND isnull(Issues.YEAR1, YEAR(InOrdDet.IssueDate)) BETWEEN 2013 AND 2014

    GROUP BY isnull(Issues.YEAR1, YEAR(InOrdDet.IssueDate))
    ,INORDDET.recognizedThruYear, INORDDET.MediaId, INORDDET.IssueDate,
    isnull(INORCOAM.SLPRSNID,INORDDET.SLPRSNID), isnull(ORDTUSDF.Picklist1,”) ,CloseSchedule.CloseDate

    UNION

    SELECT s.Budget_Year
    ,s.MediaID
    ,s.Issue_date
    ,s.RepID
    ,s.Section
    ,0 AS Expr1
    ,s.B_Revenue
    , CASE
    WHEN convert(varchar,c.CloseDate,21) <= convert(varchar,GETDATE(),21) THEN 'CLOSED'
    ELSE convert(varchar,c.CloseDate,21) END AS CloseDate
    , CASE
    WHEN convert(varchar,c.CloseDate,21) <= convert(varchar,GETDATE(),21) THEN ''
    ELSE -1*(datediff(day, c.CloseDate, getdate())) END AS DaysTilClose

    FROM dbo.SalesBudget s
    LEFT JOIN dbo.CloseSchedule c
    ON c.MediaId = s.MediaId
    AND c.IssueDate = s.Issue_Date

    WHERE s.RepID ‘HOUSE’
    AND s.RepID ‘TRADE’
    AND s.RepID ‘TRADE – ADV’
    AND s.MediaID ‘DSOC’
    AND Budget_Year BETWEEN 2013 AND 2014

    ) AS BudgetVsActual

    WHERE YEAR BETWEEN 2013 and 2014

    GROUP BY YEAR, MediaID, IssueDate , CloseDate, DaysTilClose
    ORDER BY YEAR, MediaID, IssueDate

  87. hello sir my data base have damaj and not a attach on sql2008 r2 taht some errors like that (TITLE: Microsoft SQL Server Management Studio
    ——————————

    An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

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

    OK
    ——————————
    ) plz solve this :))

  88. I’ve been searching your archives and I can’t find anything on the Some, Any or All statements. I’ve been doing SQL for years and I got blind sided by the “any” statement on a test. I’ve never seen these used by anyone in my career so it may make an interesting article to make us aware of these. If you do already have article please let me know where it is as it’s not coming up in the search due in part to the nature of the words.
    thanks.

  89. Hi sir,
    I have question related to primary key and foreign key. i have mysql database in that i have set composite primary key to cat_id and subcat_id and i want to use that both combination as a foreign key in third table called brand to identify specific brand with its parent ids called cat_id and subcat_id. so can i set composite foreign key in third table?
    ex.
    t1: category
    cat_id name
    1 food
    2 art

    t2: subcategory(both id is composite)
    cat_id subcat_id sub_name
    1 1 restro
    1 2 motels
    2 1 fashion

    t3:brandmaster
    cat_id subcat_id br_id br_name
    1 1 1 jupitor
    1 1 2 abc
    1 2 3 xyz
    2 1 4 pqr

    like this.

    in last table i have set foreign key to cat_id and subcat_id but i face problem when i fetch
    brand name with cat_id=2 and subcat_id=1.
    help me!

    thank you

  90. Hi Pinal,

    In a table, there 3 columns: GivenName, FamilyName, MiddleName. And I have to append all three columns values to output a single column like this

    Select Upper(GivenName) + FamilyName + Upper(MiddleName) as PersonName…..
    But if value for any one of the column is null then the whole output is Null.

    Any way if I can check if any of the column is null before appending? So that it is not appended and others which are not null gets appended.

    But I cannot use ‘where GivenName is not null, FamilyName is not null’ condition.

    I just dont want to append the string which is null. For Ex:

    If GivenName = ‘Mark’,
    FamilyName = ‘Joseph’,
    MiddleName is null
    Then output should be : MARK Joseph instead of NULL which has not appended MiddleName as it is Null.

    (But in SQL it the output is NULL. Try this..

    declare @FirstName nvarchar(20);
    declare @GivenName nvarchar(20);
    declare @MiddleName nvarchar(20);
    set @FirstName = ‘Steve';
    set @GivenName = ‘Hudson';
    set @MiddleName = null;
    select Upper(@FirstName) + @GivenName + UPPER(@MiddleName) => Outputs Null )

  91. I want to retrieve the recently modified or updated data from one table.There is no any column in that table to check the condition for retrieve the same..so Please help me to do that.Thank you

    • You can search all stored procedure text for references for to that Table, then change those stored procedures to log changes whenever an insert, update, or delete call is occurring.

      I prefer that to a trigger because triggers can cause a lot of problems in terms of tracking dependencies.

  92. Good day sir … I’ve a SQL Server database which contains a table named “Addresses”, this table contains about 5 million rows. I requested to extract the common words in a specific column in the mentioned table and the number of duplication for each word, I’ve no keyword list to search depending on it, but we want to get the frequency of the duplicated words. Kindly note that the columns contains data written in Arabic and English.

    So, how can I do that?

    • Insert a function StringSplit – you can get the code online, which will take a string and split it on a delimiter.

      Not familiar with arabic but in english you would use the white space characters – tab, line feed, carriage return, and space.

      This will split up the string into words, you put that in a temporary table then run a Count(*) and Group By to get the frequency.

      If you want to only count the occurrence once, then add the Row Primary Key into the temporary table,

  93. HI
    I am using ssis 2008,i have folder in which I have Four(4) “.txt” files each file will have 2 columns(ID, NAME). I loaded 4 files in one destination, but today I receive one more “.txt” file here we have 3 columns (ID, NAME, JOB) how can I get a message new column will receive in source. And how can I create in extra column in my destination table dynamically …please help me

  94. HI Pinal,

    Thanks for the great solution for SQL server.
    but now i would like to learn about SSIS so please suggest me something on sources for the same to learn SSIS like books,blog,tutorials.

    Please suggest me and do the needful.

  95. Hello Sir.
    i am working with sql server 2008 r2 i have one problem in select data from ms excel.
    in my machine win 7 64bit, sql server 2008 r2 64bit, MS office 2007 32bit, i have already installed AccessDatabaseEngine_2007,
    but i got the below error,

    —>
    OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Unspecified error”.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

    <—
    my procedure is

    ALTER PROCEDURE [dbo].[usp_SelectRateTableFromExcel]
    @FileName VARCHAR(2000),
    @ExcelSheet VARCHAR(255)
    AS
    BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..##Temp_GradingData') IS NOT NULL
    DROP TABLE ##Temp_GradingData
    DECLARE @sql VARCHAR(MAX)
    SET @sql =
    '
    SELECT * INTO ##Temp_GradingData' +
    + ' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;
    Database='+ @FileName + ''','' SELECT * FROM ['+@ExcelSheet+']'')'
    EXEC(@sql)
    select * from ##Temp_GradingData
    drop table ##Temp_GradingData

    END
    <—-
    and i also do this

    sp_configure 'Show Advanced Options', 1;
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    GO

    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO
    ,,,,

    before i format my machine same above installation are there but the data are selecting correctly. but now it's not work..

    please help me

  96. Hi Pinal,

    I have a question.

    I I called an EXE file from a Stored Procedure, and that EXE contains some exception, then are the SQL statements written after that EXE will run or not?

    Please Help.

    Thanks in anticipation.

    -Anand Neema

  97. Hi Sir,

    I am creating a cursor within a cursor. The outer cursor runs through some specific database names and the inner one pulls the text from some user defined procedures and functions I have created in the master database.
    The basic goal is to execute the text(create scripts for procedures and functions) and execute them in multiple databases. I tried using the undocumented ms_foreachdb procedure but since my procedures already have multiple quotes is has been giving me issues. I came down to using basic cursors.

    The code I am using is below:

    declare @dbname nvarchar(200);
    declare @script varchar(max);
    declare @cmd nvarchar(800);

    declare db_cur cursor for select NAME from master..sysdatabases where NAME IN (‘abc’,’xyz’,’hrishi’);

    Open db_cur
    Fetch NEXT from db_cur into @dbname;
    While @@FETCH_STATUS = 0
    BEGIN
    PRINT ‘Database Name: ‘ + @dbname;
    DECLARE script_cur cursor for select definition from sys.sql_modules where object_id IN (SELECT id FROM sysobjects WHERE category = 0);
    open script_cur;
    FETCH NEXT from script_cur into @script;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    set @cmd = N’USE ‘ + @dbname + N'; GO ‘ + @script;

    EXEC sp_executesql @cmd

    PRINT @cmd;

    FETCH NEXT FROM script_cur into @script;
    END;
    CLOSE script_cur;
    DEALLOCATE script_cur;
    FETCH NEXT FROM db_cur INTO @dbname;
    END;
    CLOSE db_cur;
    DEALLOCATE db_cur;

    Please guide me on the same. I removed the “GO” and added “;” but that does not seem to work as well. I need to execute the scripts as
    USE [db_name]
    Go
    create procedure ‘1234”

    Appreciate all the help!!!

    Thanks

  98. hi pinal,

    i need a stored procedure which accepts XML file as a string and insert or update multiple tables which are there in XML file, please suggest and please provide stored procedure and execution process and send me code to [email removed]

    Thanks in advance.

      • Dear Mr.Pinal,
        I Dont know whether am doing rit or wrng,Because i searched for ur mail id to contact but i didnt get so am writing here.

        Here is my Problem……

        Ex : On 24th July my Mdf size was 10Gb but when i checked on 27th July it was 31GB within 3 days its gets increased that much.So now we want to get the details of that increased size data and also the reason behind that.
        How can i reduce the MDF file size without affecting the Maintenance ….

  99. There are two tables :

    Table1 :

    id empid
    1 49
    2 50
    3 51
    4 52
    5 54
    Table 2 :

    empid date
    49 08/27/13
    50 08/27/13
    51 08/27/13
    52 08/27/13
    49 10/05/12
    49 11/05/12
    49 12/05/12
    49 01/05/13
    49 02/05/13
    Explanation :On a particular date in every month, we invite all emp for meeting.Lets assume on Jan15,2014 all emp. were present, same for feb2014. But In the month of march and april , some of the emp didn’t come. I want a result of list of emp alonwith date who were absent.

  100. hi sir…i have small doubt in covering index in covered query every column as (index column or non index column) both are in the select statement….let me explain

    ex:-select ename,job,deptno from emp—————–the query as ename,job columns non-index any columns,deptno is index column while it is write or not in covering index

  101. Hi Pinal,
    I created a stored procedure to bulk insert data from a .txt file into a table in my database. Now I need to execute that stored procedure in another procedure and use that data to do my calculations.
    what is the best way to do it.?
    I was trying to use bcp to execute the procedure to insert the data. Then run some queries.
    This sp is going to do all the calculations and ftp the results automatically in the script.

    Please help.
    Thanks,
    Amina

  102. Hi Pinal,
    I want to generate unique value for ever group even if it repeat.
    Like:
    User
    e
    e
    a
    b
    d
    d
    d
    e
    e
    h
    h
    h
    a
    a
    a

  103. Hi Sir, please I’m trying to select all the phone numbers of students in a table, and I have the ID numbers of those students in an excel sheet. I want to select telefoneNumber from the Student table where IDNumber exist in excel sheet. please help me out. Thanks in advance

    • Import the Excel sheet into a table then use:

      Select TelefoneNumber from StudentTable where ID in (Select ID from ExcelSheetTable)

  104. Hi Pinal,

    I have a task where i need to migrate table_1 data to table_2 data of different columns, i know the source & destination columns which need to be mapped but there is a possibility of truncation & data type mismatch issues, is there anyway to find in advance which which data may going to truncate or which rows data type may mismatch before going with migrating ?

    I have scrip to compare data types of destination & source columns but i am looking for some script which parse through data. Suppose my destination column in Integer and source column is varchar and source column have all integer rows only means then no problem but in case any rows have no integer value then i want my result set with that row details. Is this possible in SQL Server 2005 ?

    Thanks a lot in advance :)

  105. I am brand new to SQL and was wondering if there was a particular video or section of your website you could refer me to that would be the best starting point? You have a great website and I look forward to learning SQL!

    Thanks,
    Eric

  106. Hi Pinal,

    I have the following Stored Procedure (shown below).

    The SP keeps a transaction number in a Table. And then sequentially updates the table inside of a Begin and Commit Tran – transaction object.

    Recently, I was surprised to find that two transactions had grabbed the same transaction number, in spite of the TRAN statement.

    I tried to recreate it by running several concurrent statements in multiple query windows – but those worked fine.

    I think the issue is with the Select I_trno at the bottom which comes out as a dirty read, maybe it should be outside the Commit Tran.

    Do you have any comment ?

    ———————————————

    ALTER PROCEDURE [dbo].[GetNextTRNO] AS

    Declare @I_Trno int
    Declare @TempTrno int, @Acctrno int
    Begin Tran
    Begin

    SET NOCOUNT ON
    Select @TempTrno = isNull(Max(I_Trno),0)+1 from TrnTransactionHeader
    Select @AccTrno = isNull(Max(TrnNo),0)+1 from TrnTempAccounting

    if(@AccTrno > @TempTrno)
    begin
    set @TempTrno = @AccTrno
    End

    Select @I_Trno =Isnull(Max(I_Trno),0)+1 From KeyAutoTrno

    –check the header table to see if the transaction numbers are greater
    if(@TempTrno > @I_Trno)
    BEGIN
    Delete from KeyAutoTrno
    insert into KeyAutoTrno(I_Trno,Useless) values(@TempTrno,’1′)
    END

    else
    BEGIN
    –most of the time this check will run
    Delete from KeyAutoTrno

    insert into KeyAutoTrno(I_Trno,Useless) values(@I_Trno,’1′)
    END

    Select I_trno from KeyAutoTrno
    End
    Commit Tran

    return @I_trno

  107. Dave, your articles are great, and have helped me many times. Have you ever written a comparison between SQL Server and MySQL? I use both, and I am interested in your opinion on the strengths and weaknesses of both.

  108. Dave,
    I use SQL Server 2008 R2.
    As per the http://web.synametrics.com/sqlexpressremote.htm I enabled TCP/IP in SQL Server Configuration Manager.
    After doing this, I was not able to connect to SQL Server.
    The error message was
    “Login failed for user sa. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)”
    I was able to login with Windows Authentication. But my project databases were not found. Only System databases were found.
    When googled for a solution, I found to make Mixed mode authentication with Windows Authentication. But I was not able to make that also. I was getting Access is denied error.
    As I had urgent work, I re-installed SQL Server and restored the databases.

  109. Hey this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code
    with HTML. I’m starting a blog soon but have no coding expertise
    so I wanted to get guidance from someone with experience.
    Any help would be greatly appreciated!

  110. **Hello pinal dave**…

    I have a world population data per year which includes headings like ‘country name’ ‘country code’ ‘1960’ ‘1961’ ‘1962’ ‘1963’ and soo on…

    The problem is when i exported data to sql server 2012 the headings is changed to f3,f4,f5 and soo on while the ‘country name’ and ‘country code’ is remains the same..

    How can i get out of this ??

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