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

129 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

  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?

  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

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