Contact Me – Archive 4

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

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

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

Copyright violation and Reproduction of blog:

SQLAuthority.com is trademark of Pinal Dave. Exact work “SQLAuthority”  or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave.

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

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

Search SQLAuthority.com

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

pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com

362 thoughts on “Contact Me – Archive 4

  1. Dear Pinal,

    Greetings…

    Is there any working professional cum tutor/teacher to teach SQL 2008 in Chennai(Tamilnadu,India) apart from NIIT, STG and Digiterati. Thease institutes are giving only theoritical knowledge. But they unable to answer production based problems.
    Can you help in this regard?

    Like

  2. Dear sir,

    I m ram yadav from mumbai(india).
    i read u r blog always and i find it’s too much intresting.
    I have a query that :-

    I hava a table A and columns are SrNo,Qty,Wt
    suppose there are 3 srno like

    1 100 50
    2 200 50
    3 50 25

    now i have use srno 1 with 50 qty and now my balance qty is 50.i will use next time remaning 50 qty.
    so how i can use these process in my sql server please help…….

    i hope that u will reply…..
    and Please reply on my E-Mail Id Please…..Pls…..

    Thanks,

    Regards,
    Ram Niwas Yadav

    Like

  3. Dear Pinal,
    I solved many problems by reading your blog. It is excellent!

    But I still have 1 problem that cannot be fixed. I have a SP which can be completed in the SQL Managemet Studio in 1 seconds. But if I use the vb.net to execute it, it took very long time to complete (more than 1 min). I use the profiler and activity monitor to troubleshoot, the SP just keeps running. If I change the same vb.net code to another SP name, it works fine.

    May I ask what the problem is? Thanks again.

    Best regards,

    Ray

    Like

  4. Hi pinal
    I’m using sql server 2008.i get the following error when i try using sqlcmd
    HResult 0x2,Level 16,state 1
    Named pipes providers:could not open a connection to sql server[2]
    sqqlcmd:Error:microsoft sql server native client 10.0:An error has occured while establishing a connection to the server.
    sqlcmd:Error:microsoft sql server native client 10.0:login timeout expired.

    I have tired all solution suggested on different blogs but enable to solve the problem.plz answer as soon as possible

    Like

  5. I’ve a question. If you know any solution let me know

    Scenario:

    I need to write one batch file where it has to read text file data, first it has to check the data in the database whether record exist or not, if not then it has to insert that data into local database (Oracle).We’ve to use SQL Loader for this process.Do you know how do we create this one with SQL Loader?

    Thanks&Regards,

    Ravikiran.T

    Like

  6. @Ravi

    Which part are you asking about? Getting the data from SQL Server, or putting it into Oracle?

    SQL Server has DTS/SSIS packages that can handle the entire process.

    As for SQL*Loader, it has options not to overwrite. Check the Oracle utilities documentation.

    Like

  7. I have doubt..
    i have one table
    ———————————–
    1 kannadasan
    1 ram
    1 bala
    1 raman
    1 Babu

    this table convert to
    1
    kannadasan
    ram
    bala
    raman
    babu

    give solution in ASp.net

    Like

  8. @ Brian Tkatch

    Thanks for your reply.
    The parameter is the user ID. The SP will do this:

    select count(recid) from myView where userID = @userID

    Some people said we should turn Arithabort ON in the vb.net code. And some said the SP should include the with recompile option. Please advise. Thanks.

    Like

  9. @Ray,

    I Would suggest you use Exec Sp_ExecuteSQL to execute any dynamic SQL.

    Something like this.

    Exec Sp_ExecuteSQL N’select count(recid) from myView where userID = @userID’, N’userID int’

    ~IM

    Like

    • Mostly like you should use

      insert into target_table(col_list)
      select top 1000 col_list from source_table as s
      where not exists(select * from target_table where keycol=s.keycol)

      while @@rowcount>0
      Begin

      insert into target_table(col_list)
      select top 1000 col_list from source_table as s
      where not exists(select * from target_table where keycol=s.keycol)

      end

      Like

  10. How can i insert records from one table to another table in a batch of 1000 records without using a cursor in SQL Server 2005 ?

    Like

  11. @Qutub

    The simplest solution would be to: INSERT INTO tab2 SELECT TOP(1000) FROM tab1;

    But maybe there are more details?

    Like

  12. The above question was an interview question which was asked to me.
    Moreover, if there are around 2,00,000 records in the table which are to be copied, TOP(1000) would pick the first 1000 records. But what about the succeeding batches of 1000 records? Thanx.

    Like

  13. Hello Penal Dave,

    I am trying to install SQL Server Express Edition 2005. I cannot download the Adventureworks. I starts to download and then opens word processor. After that an error message show that this file is currently being used or is corrupt. Do you have any suggestions?

    Colleen

    Like

  14. sir i have one doubt in sql server 2005
    how can i insert a new column into table
    that column should insert between two columns

    for example
    i have Eid and Ename in my table i have add a column Esalay between Eid and Ename.

    how?
    tel me the query

    thank u

    Like

  15. I’m unable to install MS Sql Server 2000 on WinXP SP2. I need MS Sql Server because it comes with Query Analyzer, which is missing in the latest versions.
    When I start the setup, it does something for 2-3 seconds and nothing happens. When I start again it shows that setup already running. But it does nothing.
    Please respond.

    thanks.

    Like

  16. Hi,

    I am getting an error message :
    My dabase could not be open. When try to open it in SQL Server MAnagement studio, it gave me following error message:

    “DATABASE cannot be opened. It has been marked suspect by recovery. See the SQL Server Error Log for more information. ( Microsoft SQL Server Error 926)”

    I am using SQL Server 2005.

    Pls help how can i Solve this problem.

    Like

  17. Hi .

    I am getting problem while inserting data from a file using BULK INSERT utility server .
    The charecter to be inserted is of German charecer like ‘Jörg’ and its getting loaded as ‘J+Ârg .’

    Can you please tell me the solution on this .I am using Datafile type as ‘char’ in this case . If i use it as native or widenative then file is not at all getting inserted .

    Please help on this .

    Like

  18. In my organization, we are developing lot of SSIS package and deploy it. My team is group of 7, Each time, when SSIS package is deployed, we have Excel file in shared directory, whoever deploying package to production, needs to update Excel with source and target (meta information).

    Is there a way to develop SSIS package/ .Net application to run frequently to get meta data information of SSIS package on all production m/c. Our practice is , deploy into MSDB database, from there we are scheduling.

    If you have any logic to extract or any sample work , that will be great for us to avoid confusions other issues.

    Thanks
    Kalyan

    Like

  19. Hello,

    if i add one more ndf or ldf file in logshipping primary server. is it affect my logshipping configuration or not if no how come

    Like

  20. Hi Pinal,

    I am trying to update column using self- join as follows :
    UPDATE LoyaltyRept
    SET LoyaltyRept.CustomerChange = ((CONVERT(NUMERIC, LoyaltyRept.TotalOrderingCustomers)/CONVERT(NUMERIC,A.TotalOrderingCustomers)) -1)
    FROM LoyaltyRept A
    WHERE A.Month = @MIN_Month
    AND A.site =LoyaltyRept.site
    AND LoyaltyRept.Month = @Updt_Month

    But giving following error :

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

    Please help me.

    Thanks
    Manish

    Like

  21. @AlexB

    Neither. Use NOT EXISTS:

    SELECT Product.ProductID
    FROM Product
    WHERE NOT EXISTS(SELECT * FROM WorkOrder WHERE Product.ProductID = WorkOrder.ProductID)

    Like

  22. exec master.dbo.xp_startmail;
    EXEC master.dbo.xp_sendmail @recipients = ”,
    @subject = ‘RM testing’,
    @message = ‘RKKKKK tetsing .’
    exec master.dbo.xp_stopmail;

    couldn’t get subject line when I got mail.

    Please help me how can I get subject line.

    Thanks

    RK

    Like

  23. Hi Pinal,

    I have to replace *= ,=* by LEFT/RIGHT JOIN.Can you please give the solution for the below query

    FROM Famille_Tarifaire As FT,
    Prix_Base As PB,
    Member As Proprietaire,
    Member As Client
    WHERE
    PB.ID_Membre_Client =* Client.MemberId AND Proprietaire.MemberId = FT.Id_Membre_Fournisseur AND FT.Id_Famille_Tarifaire *= PB.Id_Famille_Tarifaire

    Thanks in advance!!

    Regards,
    Hari

    Like

  24. hi
    this is prashanth,

    I want to deploy a web application with a batch file containing scripts which create database and tables and sp associated with it.

    I can create and deploy a web application on iis but i cannot attach a batch file containing scripts which has db file

    i cannot even create a batchfile containing these scripts.

    plz help thanks in advance

    Like

  25. @Harinadh

    I’m not sure i got this right, but here’s a shot:

    FROM
    Famille_Tarifaire As FT
    LEFT JOIN
    Prix_Base As PB
    ON
    (FT.Id_Famille_Tarifaire = PB.Id_Famille_Tarifaire)
    RIGHT JOIN
    Member As Client
    ON
    (PB.ID_Membre_Client = Client.MemberId)
    INNER JOIN
    Member As Proprietaire
    ON
    (Proprietaire.MemberId = FT.Id_Membre_Fournisseur)

    Like

  26. Thank you Brian Tkatch

    But the solution is not giving the exact result as before

    Can someone provide the exact solution

    Regards,
    Hari

    Like

  27. but i now want to know how this batch file is attached with a web application and when we create a deployment package and install it in other computer this .bat file should execute and a data base should be created…

    if some one know plz..

    thnks in advance

    -pth

    Like

  28. @Harinadh

    Sorry about that. I am not very experienced in ANSI syntax.

    Does not “exact” mean not the same result set, or not the same order?

    Like

  29. Hi

    I am using sql2000 , and server got crashed ,
    my database ldf file showing issue and have renamed the log file and tried to attach using sp_attach_single_file_db
    while attaching the database got below error

    Server: Msg 823, Level 24, State 6, Line 1
    I/O error 38(Reached the end of the file.) detected during read at offset 0000000000000000 in file ‘F:\MSSqlSvr\Logs\CusActv_Log.LDF’.

    Connection Broken

    Please help me urgent , it is production database.

    thanks

    Like

  30. hi
    i am using sqlserver2000, when i am attaching the database got ldf file issue , and when i tried to attach single db file

    got below error :

    Server: Msg 823, Level 24, State 6, Line 1
    I/O error 38(Reached the end of the file.) detected during read at offset 0000000000000000 in file ‘F:\MSSqlSvr\Logs\CusActv_Log.LDF’.

    Connection Broken

    Please help me attach the databse , ASAP .. waiting for your reply sir thankyou

    Like

  31. Pinal,

    For any sql server issues, I usually refer to your website. Thanks for updating the site frequently and posting interesting articles.

    I could not find a quick solution for a problem? Is there a way to find out what databases are user created ones?

    I have one giant list of databases (executing sp_databases). But how do I find out which one is user created in sql server?

    Like

  32. pinal,

    i m not clear (by defination) about data types using by sql.
    Pl. explain

    –> char(n)-varchar(n)
    –> binary-varbinary-image
    –> nchar-nvarchar
    –> sql_variant-table

    pl. explain all descibed terms with example and tell where is it used and/or how far it will be usefull in terms of processing or any other concept.

    Like

  33. Pinal,

    I am new to SQL Server. I’m using SQL Express 2005 running SDE. My Database was created using ArcCatalog by ESRI.
    I’m trying to find out where in the SQL Server Manager Studio, do I write code that will calculate a record when the user creates a new record.

    Like

  34. Hello Brian Tkatch,

    after replacing with your suggested changes i got no result set values but before it is returning result with some records.

    Regards,
    Hari

    Like

  35. Dear Pinal ,

    Hi , I Want to concate two or More rows in one rows.My data is

    Code Name
    10541 abcd
    10541 xyz

    I want to display following data inresult.

    Code Name
    10541 abcd,xyz

    Please Give me a solution.

    Like

  36. @Harinadh

    I do not know then. Obviously, i still have a lot to learn. Hopefully, someone else will answer the question. Note, you can also ask at the MSDN forums.

    Like

  37. @Harinadh,

    Looks like you are trying to convert a PL-SQL script to SQL Server Script. I had done this assignment in the past. I did not spent too much time figuring out what is what.

    May I suggest you one tool that will convert PL-SQL to T-SQL.

    You can download trail version, and convert all your scripts converted into T-SQL. Review scripts carefully before you actually use them in your procedures.

    Here is the free download link (trail version) of SwisSQL (Oracle to SQL Server)

    http://www.swissql.com/products/oracle-to-sqlserver/oracle-to-sql-server.html

    Enjoy.
    ~ IM.

    Like

  38. Thank you Brian Tkatch

    Hello Imran,

    It is not conversion from PL-SQL to SQL server.

    I need to modify the SQL procedures with replacing the *=,=* by LEFT JOIN ,RIGHT JOIN .

    Can some one provide solution.

    Thanks in advance!!

    Regards,
    Harinadh

    Like

  39. Hi Pinal,
    I just face some problem on my database. It is self blocking.
    I am running sql server 2000(sp4). Can a select query be blocked and why self blocking occurrs on the server frequently.

    Thanks,
    Rupesh

    Like

  40. Hi Pinal,

    I’m trying to get the difference of first task’s StartDateTime and Second task’s StartDateTime and so as for 2nd and 3rd task to calculate Total Actual Time for an Order. I’ll only use the EndDateTime to get the final actual time because that is the end task of the Order. How can I get differnce of the first task start time and second task start time and so on. Can we do it without using cursor, may be using different temporary tables.

    TaskNumber Order StartDateTime EndDateTime
    5072161 3559013 2009-04-27 16:31:15.000 2009-04-27 16:32:11.000
    5072189 3559013 2009-04-27 16:32:12.000 2009-04-27 16:33:29.000
    5072305 3559013 2009-04-27 16:33:30.000
    2009-04-27 16:34:04.000

    Like

  41. @Sam

    How does this look?

    WITH
    Data(TaskNumber, [Order], StartDateTime, EndDateTime)
    AS
    (
    SELECT 5072161, 3559013, ‘2009-04-27 16:31:15.000′, ‘2009-04-27 16:32:11.000′ UNION ALL
    SELECT 5072189, 3559013, ‘2009-04-27 16:32:12.000′, ‘2009-04-27 16:33:29.000′ UNION ALL
    SELECT 5072305, 3559013, ‘2009-04-27 16:33:30.000′, ‘2009-04-27 16:34:04.000′
    ),
    Data_RN
    AS
    (
    SELECT
    ROW_NUMBER() OVER(PARTITION BY [Order] ORDER BY TaskNumber) RN,
    TaskNumber,
    [Order],
    StartDateTime,
    EndDateTime
    FROM
    Data
    )

    SELECT
    DR1.TaskNumber,
    DR1.[Order],
    DATEDIFF(s, DR1.StartDateTime, DR2.StartDateTime)
    FROM
    Data_RN DR1
    LEFT JOIN
    Data_RN DR2
    ON
    DR2.[Order] = DR1.[Order]
    AND DR2.RN = DR1.RN + 1;

    Like

  42. Hello Pinal,

    Please help me in modifying the below query by replacing *= with LEFT JOIN.I am unable to do because of the complex join condition.

    select * from
    Commercial_Response as cr,
    Lieu_Livraison_BOC as ll_boc,
    Member as m_Fournisseur,
    Member as m_selag,
    Product as p_offert,
    Product as p_commande,
    Commercial_Offer as co
    where
    cr.LocationId *= ll_boc.Id_Lieu_Livraison
    AND m_fournisseur.Code_BackOffice *= ll_boc.Code_BackOffice
    AND cr.Id_agence_Responsable *= m_selag.MemberID

    Thanks in advance!

    Regards,
    Hari

    Like

  43. Thank you Brian,

    Very good example. It works in Sql Server 2005, but not in 2000. I think, 2000 does not support WITH clause, right? And, apparently my development version is 2000. Any suggestion?

    Regards,

    Sam

    Like

  44. I am looking for smaple database AdventureWork DW for
    SQL 2005.
    Plz let me know the specific link to download this database.

    Or can mail me script to configure.

    With regards,

    Like

  45. @Sam

    The first CTE is just data, so that does not matter. The second CTE could be oput in the FROM clause.

    The real issue is, that ROW_NUMBER() is not available in 2000.

    If you could add a row number (or have a similar umber in there already), which would obviate the need for ROW_NUMBER(), it becomes possible.

    Like

  46. Hi,
    I have column InputDate which is Datetime datatype. If this InputDate column i need to display in the date format like if i use this query in US, US format should be displayed, if in UK that format and Singapore it should display in that date format.( may be by sql server installation date format it should read and display as output format.) Plz help me out.

    Rgds,
    Prasanth

    Like

  47. thanks Brian, but convert(), each and every date format we need to change for every country. is there any solution like sql server installtion date format we could get and according date could be displayed ? As my requirement is InputDate column should be displayed in Date format used by that respective country.

    Thanks in Adv.,
    Prasanth

    Like

  48. Hi,
    I have problem with query. i have following table.

    scheme nav_rate nav_date
    —————————————————–
    s0001 11.2354 27-apr-2009
    s0002 9.6547 28-apr-2009
    s0003 46.254 29-apr-2009
    s0001 13.2413 12-may-2009
    s0002 11.6547 24-may-2009
    s0003 49.3712 20-may-2009
    s0001 9.2354 27-mar-2009
    s0002 8.6547 28-mar-2009
    s0003 44.2413 29-mar-2009

    i want to summarise output ln this format

    scheme max(nav_rate) date min(nav_rate) date
    —————————————————————————
    s0001 13.2413 12-may-2009 9.2354 27-mar-2009
    ….

    Can some one provide solution.

    Thanks in advance!!

    Regards,
    Chirag

    Like

  49. @Chirag,

    You can get expected output as:

    DECLARE @test TABLE(Scheme VARCHAR(12), nav_rate NUMERIC(16,4), nav_Date DATETIME)

    INSERT INTO @test
    SELECT ‘s0001′, 11.2354 ,’27-apr-2009′
    UNION ALL
    SELECT ‘s0002′, 9.6547 ,’28-apr-2009′
    UNION ALL
    SELECT ‘s0003′, 46.254 ,’29-apr-2009′
    UNION ALL
    SELECT ‘s0001′, 13.2413 ,’12-may-2009′
    UNION ALL
    SELECT ‘s0002′, 11.6547 ,’24-may-2009′
    UNION ALL
    SELECT ‘s0003′, 49.3712 ,’20-may-2009′
    UNION ALL
    SELECT ‘s0001′, 9.2354 ,’27-mar-2009′
    UNION ALL
    SELECT ‘s0002′, 8.6547 ,’28-mar-2009′
    UNION ALL
    SELECT ‘s0003′, 44.2413 ,’29-mar-2009′

    SELECT t1.Scheme, t2.MAXnav_rate, t1.nav_date, t2.Minnav_Date
    FROM @test t1
    INNER JOIN (
    SELECT Scheme,
    MAX(nav_rate) AS MAXnav_rate,
    min(nav_Date) AS Minnav_Date
    FROm @test t1
    GROUP BY Scheme
    ) t2 ON t1.Scheme = t2.Scheme
    AND t1.nav_rate = MAXnav_rate

    Let me know if it helps you.

    Thanks,

    Tejas
    SQLYoga.com

    Like

  50. @chirag

    I’m not sure this is the best way to do it, but it should return the correct results.

    Note: The first CTE is just your data. The second CTE assigned a row number which is the basis of the query.

    WITH
    Data([scheme], nav_rate, nav_date)
    AS
    (
    SELECT ‘s0001′, 11.2354, ’27-apr-2009′ UNION ALL
    SELECT ‘s0002′, 9.6547, ’28-apr-2009′ UNION ALL
    SELECT ‘s0003′, 46.254, ’29-apr-2009′ UNION ALL
    SELECT ‘s0001′, 13.2413, ’12-may-2009′ UNION ALL
    SELECT ‘s0002′, 11.6547, ’24-may-2009′ UNION ALL
    SELECT ‘s0003′, 49.3712, ’20-may-2009′ UNION ALL
    SELECT ‘s0001′, 9.2354, ’27-mar-2009′ UNION ALL
    SELECT ‘s0002′, 8.6547, ’28-mar-2009′ UNION ALL
    SELECT ‘s0003′, 44.2413, ’29-mar-2009′
    ),
    Data_RN
    AS
    (
    SELECT
    [scheme],
    nav_rate,
    nav_date,
    ROW_NUMBER() OVER(PARTITION BY [scheme] ORDER BY nav_rate) RN_Min,
    ROW_NUMBER() OVER(PARTITION BY [scheme] ORDER BY nav_rate DESC) RN_Max
    FROM
    Data
    )
    SELECT
    Data_RN_Max.[scheme],
    Data_RN_Max.nav_rate,
    Data_RN_Max.nav_date,
    Data_RN_Min.nav_rate,
    Data_RN_Min.nav_date
    FROM
    Data_RN Data_RN_Max,
    Data_RN Data_RN_Min
    WHERE
    Data_RN_Max.RN_Max = 1
    AND Data_RN_Min.[scheme] = Data_RN_Max.[scheme]
    AND Data_RN_Min.RN_Min = 1;

    Like

  51. Hi Pinal I’ve been reading your blog and it’s very interesting. I have a serious problem with my SQL Server 2005 installed in my Windows Server 2008 machine.

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

    My dedicated admin connection port is 51780
    My default port is 49161

    The things I did:

    1. I installed SQL Service Pack 3, but it’s important to say that my SQL Server has never worked.
    2. I enabled TCP/IP connections in the Surface Area Configuration.
    3. In the Registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SuperSocketNetLib\Tcp\IPX I changed the TcpPort to my default port (49161)
    4. The same as step 3 a changed to my default port the port number in the Configuration Manager of my instance.
    5. I switched off my firewall (hehe).
    6. I created an alias and it doesn’t work.

    Other details are:
    Can not connect using localhost, I just can connect using ServerName\InstanceName and the alias I created.
    In the network databases I don’t see all of them and the ones I see I can’t connect to them.

    Don’t know if it’s a security issue and/or compability issue?

    I’m really desperate, please help guys.

    Like

  52. dear sir,

    i am a student and i am working on a VB.net application
    which is used to store accounts of a mobile store

    i need a database which could be deployed in the application as well as is capable to be adding the columns using alter table command

    i tested three databases
    1)sql server 2005 (it supports the alter table-working on buttont click event-“executenonquery method”)but it cant be deployed with app.
    2)sqlserver database file(similar to CE)

    3)sqlserver CE 3.5 (it could be deployed but the alter table command dosent works on it while i run the query at button click event)

    how can i overcome the problem to create such dynamic database.

    Like

  53. hi pinaldave,

    I hope u can help me in this matter. I have 2 servers. One run on sqlserver 2005 express edition and the other one run on sql server 7.0. My application is connected to sql server 7.0 . I need to transfer data from 3 tables when user click a button in my application to sql server 2005 express. table structure for both servers is the same.

    my questions are :
    1. how to do a script to transfer/update data 3 tables from sql server 7.0 to sql server 2005 edition?

    2. How to automate the process like DTS/job in sql server 7.0 . any ideas?

    Hope to get solution asap.
    Thank in advance.

    Like

  54. i sir
    i have a problam.
    i need a all date in given month nad year peramerter

    like

    march -2009 all date
    1/3/2009
    2/3/2009
    ———–
    ———–
    31/3/2009

    Like

  55. Hello Pinal Dave,

    Could you please tell me the exact difference between , Convert() and Cast() functions in Ms-SQL . Except the extra formatting paramter in convert().

    I searched lot but,didn’t find satisfactory response.

    Please help me to know this.

    Thanks in advance.
    Vikas Anand.

    Like

  56. @Vikas,

    Just to add to what Brian said, CAST is older version of CONVERT. CAST will not be available in new version starting from SQL SERVER 2008 and higher, so we better stay away from them.

    Both does the same job. Because Convert is a newly introduced T-SQL reserved Key word, it has some additional functionality, like changing dates to specific format, we call it as styles.

    Like Brian suggested, please go through Books Online Once, search for word, Convert. You will find Books Online information to be very helpful.

    ~ IM.

    Like

  57. THANKS BRIAN, TEJAS

    for supports.

    but i have laks of rows in a table.

    is thare any other way(/query) to dispaly/ get desired result.?

    Like

  58. Hi,

    I am new in SQL and have been asked to increment by 2 all year dates in a SQL DB.

    That is, the DB has dates ranging from 2005 to 2007. I need to leave everything identical except for the years that should be incremented in 2, to read 2007 to 2009.

    There are multiple year fields in the DB, so the idea would be to have something like the Excel Find-Replace that could be applied to the whole SQL DB

    Any suggestion is welcomed,

    Thanks, Manuel

    Like

  59. @Manuel.

    Your question is not clear.

    You have date fields in your database.
    Question 1 : Does all date fields have same column name in all tables in a database.

    You want to increment dates ranging from what to what ? And what dates should be left untouched.

    This can be done. All we need to do is write a dynamic SQL.

    Step 1:
    Get a list of all date data type fields in a temporary table. In that table you will store, table name and column name that has date field.

    Step2:
    Using Dynamic SQL you will prepare SQL that will replace a date in the field to a new date,

    step3 : You will execute the statement

    consider this example below…

    create table #example ( eid int, date datetime)

    insert into #example values (1, ’06/30/2008′)

    select * from #example

    update #example
    set date = replace ( date , ‘2008’ , ‘2009’)

    select * from #example

    Let us know, if you need more help with this.

    ~ IM.

    Like

  60. Is there a place for a “SQL Job” in the Visual Studio for Database Professionals product?

    I have a new project up and running GDR R2 and having great success with it but I can’t seem to find a place to position a scheduled job.

    Thank you for your assistance.

    Like

  61. @ Chirag

    Borrow populate script from Tejas and here i think a simple way to get ur results…

    DECLARE @test TABLE(Scheme VARCHAR(12), nav_rate NUMERIC(16,4), nav_Date DATETIME)

    INSERT INTO @test
    SELECT ‘s0001′, 11.2354 ,’27-apr-2009′
    UNION ALL
    SELECT ‘s0002′, 9.6547 ,’28-apr-2009′
    UNION ALL
    SELECT ‘s0003′, 46.254 ,’29-apr-2009′
    UNION ALL
    SELECT ‘s0001′, 13.2413 ,’12-may-2009′
    UNION ALL
    SELECT ‘s0002′, 11.6547 ,’24-may-2009′
    UNION ALL
    SELECT ‘s0003′, 49.3712 ,’20-may-2009′
    UNION ALL
    SELECT ‘s0001′, 9.2354 ,’27-mar-2009′
    UNION ALL
    SELECT ‘s0002′, 8.6547 ,’28-mar-2009′
    UNION ALL
    SELECT ‘s0003′, 44.2413 ,’29-mar-2009′

    select Scheme,max(nav_rate),max(nav_Date),min(nav_Date) from @test
    group by Scheme

    Like

  62. Hi Pinal,

    Do you have any article on extracting the schema for partitioned table in SQL Server 2008 Using SSMS 2008. When we extract the schema for partitioned table, it didn’t extract the on the file group (ie. partition scheme )? Do you have any idea on it? In SQL Server 2005, SSMS 2005 extract exactly the way it was lay out on the partition scheme.

    Thanks in advance

    -Krishna

    Like

  63. hi pinal
    would you help me about finding user in sqlserver2005 that connect dba and does some query.
    how can i find user and commend that it does.
    ofcourse use sp_who but i want find this user what did it do and find its ipaddress.
    thanks in advance

    Like

  64. I have some, I need to create 1 records Data, are as follows if required group by also to be added location/customer wise
    Data
    Location Customer Trn_dt Amount
    1 A001 01/01/2009 100
    1 A001 01/01/2009 100
    1 A001 01/02/2009 100
    1 A001 01/03/2009 100
    1 A001 01/04/2009 100
    1 A001 01/05/2009 100
    1 A001 01/06/2009 100
    1 A002 01/07/2009 100
    1 A002 01/08/2009 100
    1 A002 01/09/2009 100
    1 A002 01/10/2009 100
    1 A001 01/11/2009 100
    1 A001 01/12/2009 100

    Output Records
    Locn Cust jan feb mar apr may jun jul aug sep oct nov dec 1 A001 200 100 100 100 100 100 100 100
    1 A002 100 100 100

    Please help

    Like

  65. @Brian TKatch

    You are correct, I don’t know where I got that information from. But today, When I searched online, I could not found any information related to CAST being depreciate in future version.

    I apologize for any confusion.

    Thanks for correcting me.
    Imran.

    Like

  66. Hai Sir.

    I have a doubt on transaction.

    Is there any way to achive the nested transaction in Sql Server 2005.

    Regards,
    Gokula Krsihnan R

    Like

  67. Hi Pinal,
    I heard about you a lot.
    I am interested to learn sql service integration service 2005.
    Tell me some nice site names where i can learn it ……
    Or some video tutorials or their sites….

    Can you please help me?

    regards
    deepti

    Like

  68. Hi i have report (SSRS 2008 ) which look like this…………..

    Business Area MTD Month Estract X
    Y Z………………..
    Segment Sales Plan Sales
    Category %
    Brand
    ——————————————————————————————————————————————–
    +LL Builder 34567 678 111
    898

    +Decorative 43727 457 2453 6574

    + xyz 3587 8764 1134 36572

    +pqrs 6154 7676 8699 58585

    I need to sort the column that is if some one clicks on MTD Sales %
    all the values in the column need to be sorted even the drill down
    values in +LL Builder and others also need to be sorted

    Query:

    SELECT NON EMPTY { [Measures].[EST YTD NET SALES % of PLAN],
    [Measures].[EST YTD NET SALES % of PREV YR],
    [Measures].[MaxERAEOperTimeSales], [Measures].[YTD PLAN],
    [Measures].[TOTAL SNB GROSS], [Measures].[YTD DP@STD], [Measures].[YTD
    DP@STLF], [Measures].[CURR BACKLOG GROSS], [Measures].[TOTAL BACKLOG
    GROSS], [Measures].[EST MTD NET SALES], [Measures].[MTD NET SALES],
    [Measures].[EST YTD NET SALES], [Measures].[YTD NET SALES],
    [Measures].[EST MTD NET SALES % of PLAN], [Measures].[MONTH PLAN] } ON
    COLUMNS, NON EMPTY { ([Product].[By Business Area].[Business
    Area].ALLMEMBERS * [Product].[By Segment Category
    Brand].[Brand].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
    MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (
    STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED) ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(@ByYearMonth, CONSTRAINED) ) ON COLUMNS FROM
    [SALES])) WHERE ( IIF( STRTOSET(@ByYearMonth, CONSTRAINED).Count = 1,
    STRTOSET(@ByYearMonth, CONSTRAINED), [By Year Month].currentmember ),
    IIF( STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED).Count = 1,
    STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED), [Selling
    Company].[By Selling Company].currentmember ) ) CELL PROPERTIES VALUE,
    BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
    FONT_SIZE, FONT_FLAGS

    Like

  69. hi pinal, i am using sql server 2005

    i want to know more about SEC ROLES

    and how to find differene between 2 Databases (suppose the 2 database is same, i deleted i colum in one data base i want the deleted column like that)

    Like

  70. @Tejas, Brian

    I got the solution for said query …

    query should like …

    select t.schemecode, max(nav_rate) as MaxNav, min(nav_Rate) as MinNav,
    (select nav_date from test where nav_rate =(select max(nav_rate) from test t1 where t.schemecode=t1.schemecode)) as MaxDate ,
    (select nav_date from test where nav_rate =(select min(nav_rate) from test t2 where t.schemecode=t2.schemecode)) as MinDate
    from test t
    group by schemecode

    comments are welcome…

    Like

  71. Hello,
    CONGRATULATIONS!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    Our heartiest blessings to the SQLAuthority baby and all of you…Wishing all of you a very very happy and prosperous life ahead……..

    Regards,
    Thaker Family.

    Like

  72. Hi,

    I already subscribed to your blog, but I want to change my email id which I subscribed. How can I change the email subscription id?

    Please advice me.

    Thanks & Regards,
    J. Srivignesh

    Like

    • Subscribe with a post with old email id
      Confirm it with wordpress which will take you to all old subscription which you can delete them

      Subscribe with the new email id again

      Like

  73. hi pinal,

    Am using MSSQL Server 2000.I have a record in a table like this :

    NAME
    ——————–
    GANESH
    Raja Sekar
    VIJAY
    Vijay Shankar

    I want to retrive data from the table only first letter starts with capital letter.. i.e Raja Sekar and Vijay Shankar.. kindly help me..

    Like

  74. Dave,

    Wrote a query to pull row data. Want to combine multiple column fields into one field and stack the data like using a carriage return. Not sure how to do this.

    Here is the example I have tried with no sucess.

    SELECT
    ITEMS.QUANTITY AS Quantity,
    PACKAGES.PACKAGE1 AS Type,
    ITEMS.HM,
    ITEMS.ITEMDESC1 + char(13) + ITEMS.ITEMDESC2 + char(13) + ITEMS.ITEMDESC3 + char(13) + ITEMS.ITEMDESC4 + char(13) + ITEMS.ITEMDESC5 AS ITEM,
    ITEMS.ITEMGROSS AS Gross,
    ITEMS.ITEMTARE AS Tare,
    ITEMS.ITEMNET AS Net,
    ITEMS.WGT_LABEL,
    ITEMS.ACTIVE, ITEMS.SN_ID
    FROM ITEMS INNER JOIN
    PACKAGES ON ITEMS.PACKAGE_ID = PACKAGES.PACKAGE_ID
    WHERE (ITEMS.ACTIVE = ‘True’) AND (ITEMS.SN_ID = ‘32590’)

    Thanks

    Like

  75. hello <<
    I want to ask about how we can do pharmacy(warehouse) bills if the products can be sold with parts , some with
    units and some have life date ..
    and we should consider that product can be updated so in the future it can't be sold with units and bills can be update…
    Thanks

    Like

  76. Hi
    Mr Pinal Dave
    I Have problem in SQL Server 2005
    I Have 3 tables for Example tblA , tblB And tblUser
    tblA have Relation with tblB and he is perent for tblB
    and tblUser have relation with 2 table tblA and tblB
    and he is parent for tblA and tblB
    when I want to create Relation SQL Server 2005 trow
    error with this comment :

    Introducing FOREIGN KEY constraint ‘FK_tblB_tblUser’ on table ‘tblB’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Could not create constraint. See previous errors.

    if i Set ‘Cascade Rule’ to ‘NoAction’ and Set ‘Enforce Foriegn Constraint’ to ‘No’ then for this reason i have 2 solve
    1- use transaction to update
    2- use Trigger to Update

    please tel me wich one is better
    tblA and tblB grow 1 million record per year

    Thank U
    plz send your idea to my mail

    Like

  77. alter procedure ins @ename varchar(25),@key int output
    as
    insert into dept(empno,ename,ddate) select @key= max(empno),@ename,getdate()from emp

    i have following error

    Msg 141, Level 15, State 1, Procedure ins, Line 0
    A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

    Like

    • It should be

      alter procedure ins @ename varchar(25),@key int output
      as
      insert into dept(empno,ename,ddate) select empno,@ename,getdate()from emp

      SET @key= (select max(empno) from emp)

      Like

  78. Hi,
    Mr. Pinal Dave

    I Want to Migrate from Sql Server 2000 to Sql Server 2005 for beter Performace,
    But I dont know what steps are required to migrate on Sql Server level and Database level. and what should be take care while migrating .

    Please giude me , so I can Get maximum performace.

    Regards
    Janak

    Like

  79. Hello,

    I have Microsoft SQL 2005 server standard edition in that i’ve one TEST databse,whenever i tried to add huge amount of data in that databse i get a error like,

    “Could not allocate space for object “db.TEST” in Table1 because the primary file group is full. Create disc space by deleting unreed files, dropping files in filegroup, adding additional files to the filegroup, or setting autogrouth on for existing file in the filegroup.”

    even though i have 40 GB of enough space in my drive.
    i think when i exeed 4GB it is giving me an error.
    Is there any restriction on database size?

    Like

  80. Request for AdventureWorks Database – Samples
    As mentioned tried
    First, go to Microsoft Download Center and download SQL2000SampleDb.msi.
    Gives Following Error

    AccessDenied
    Access Denied
    2955D539CFB9C2D6

    ZnCjz7bcuibM41qzigspuLrcPmGh0YwhIBju8oVVvAewZibnSAMXikH79agKKcJY

    Please Help

    Like

  81. Hello ,

    i would like to have suggestion from you , actually in my current database i am having a rows more than 2 crore but now we want to split this table into multiple table using a field parameter

    for example table structure :

    Name,POS, CID, Value – data on this is 2 crore

    CID will have data 1,2,3,4,5,6,7,8

    so i want to split this table into multiple with

    tablename_1 = which will contain data for CID = 1

    similar way looping through all distinct CID value which is already stored in another table i will extract data , i am able to do this using a SSIS package , but i want to split them either using a technique from my vb.net application , so kindly suggest how i can achive maximum performance and what script should i use.

    with best regards

    Like

  82. Hi Daniel, How are you?…I hope good, Men, My question is some rarously, ok…
    I cant see the Storage folder on SQL 2005 Express…Or this option is enabled on pay versions?…I think no. or i need a SP or Hotfix or Script for view this folder…Thanks for answer my question and regards!…

    Like

  83. Hi Pinal,
    I am replication database in sql server 2005 from oracle using publisher and subscriptions. I had to delete publisher and subscription and recreate as requirements changed. when I try to create the publisher i get following error. I don’t know what to do .

    The Oracle server [XXX] is already defined as the Publisher [XXX_2] on the Distributor [XYXYX].[distribution]. Drop the Publisher or drop the public synonym [MSSQLSERVERDISTRIBUTOR].

    I appreciate your help in this regard.

    Like

  84. Hi i am ankit gandhi.
    Below is my proble.
    I have one table for calculation of employee leave.
    Table Structure
    Employee_id
    leave_Start_date
    leave_end_date

    Now I want to calculate total day difference of leave by month.
    Mins in January how many leave taken by employee.
    But my problem is when leave_start_date =”01/29/2009” and leave_end_date=”02/15/2009” so how to calculate 3 days in January and 16 days in February. And so on as per date changes.
    Please give me example to solve my problem.

    Like

  85. @Brian Tkatch

    regd. your answer to my test query, ‘If it gives the appropriate results within an acceptable amount of time, use it. :)’

    but regarding reffence book Sql Server 2005 Bible (by Paul Nielsen) wrote
    ” So far my testing, I’ve not seen any performance difference between simple subqueries and CTEs; both are compiled to the same query execution plan.”

    what is fact regd. CTEs? pls. Explain. if possible, set with example.

    thanks

    Chirag

    Like

  86. @Chirag

    Are you asking what a CTE is? A CTE is a Common Table Expression, which is the WITH at the top of a query.

    CTE example: WITH example_CTE AS (SELECT ‘chirag’ poster) SELECT poster FROM example_CTE;

    subquery example: SELECT poster FROM (SELECT ‘chirag’ poster) example_sub_query;

    Like

  87. I have one table
    inventory that contain number of column.

    i want to track column value updation of inventory table in log table that contain following column
    Updatefieldname,beforevalue,aftervalue,Updateusername,date,time.

    i create group edit for inventory in which updation criteria is selected through wizard.

    my problem is maintain log table for this group updation.

    i have one problem. i can get all values for log table in trigger.only one problem is that i can not pass updateusername as parameter to trigger.

    i also want to ask it is possible to create dynamic cursor or not.

    Like

  88. Hi Pinal,

    I have 2 tables and i am joining them baesd on CID. For CID 123 i have 4 types of status, these 4 status i have to load into 4 columns as shown below in output.
    Table 1:
    ———-
    CID STARTTIME
    123 8/19/09 01:11:32 AM
    124 8/19/09 01:13:13 AM
    125 8/19/09 01:14:32 AM
    126 8/19/09 01:15:36 AM

    Table 2:
    ———–

    CID status
    123 abc
    123 abcd
    123 abcde
    123 abcdef
    124 efg
    124 defgh
    125 ghij
    125 ghijk
    125 ghijkl
    126 jklm

    output
    ——-
    CID STARTTIME status1 status2 status3 status4
    123 8/19/09 01:11:32 AM abc abcd abcde abcdef
    124 8/19/09 01:13:13 AM defg defgh – –
    125 8/19/09 01:14:32 AM ghij ghijk ghijkl –
    126 8/19/09 01:15:36 AM jklm – – –

    Can ypu plz look into this and help me.
    Thanks in advance.

    Like

  89. @Brian Tkatch

    regd. your answer to my test query, ‘If it gives the appropriate results within an acceptable amount of time, use it. :)’

    but regarding reffence book Sql Server 2005 Bible (by Paul Nielsen) wrote
    ” So far my testing, I’ve not seen any performance difference between simple subqueries and CTEs; both are compiled to the same query execution plan.”

    means which will execute faster? with CTE or normal subquery? and which option preffer for query execution?

    my question is not regd. with CTE syntax. but regarding comments with your statements and statements given by author (paul nielsen).

    pls. clarify

    thanks

    Chirag

    Like

  90. Hi I have total 8 statuses just for an example i have mentioned 4 only, if i get more than 8 i have to ignore,
    no need to load if we get more than 8 statuses.

    please help me on this.

    Like

  91. @kasi

    This should start you off. The simplest way is with sub-queries. Though, if performance is bad an outer join can be used as well.

    This example shows 4, you can expand it to 8 as required. ROW_NUMBER() is ORDERing BY status itself. You might want to change that to a date COLUMN or the like so the ORDER makes sense.

    WITH
    [Table 1](CID, STARTTIME)
    AS
    (
    SELECT 123, ‘8/19/09 01:11:32 AM’ UNION ALL
    SELECT 124, ‘8/19/09 01:13:13 AM’ UNION ALL
    SELECT 125, ‘8/19/09 01:14:32 AM’ UNION ALL
    SELECT 126, ‘8/19/09 01:15:36 AM’
    ),
    [Table 2] (CID, [status])
    AS
    (
    SELECT 123, ‘abc’ UNION ALL
    SELECT 123, ‘abcd’ UNION ALL
    SELECT 123, ‘abcde’ UNION ALL
    SELECT 123, ‘abcdef’ UNION ALL
    SELECT 124, ‘efg’ UNION ALL
    SELECT 124, ‘defgh’ UNION ALL
    SELECT 125, ‘ghij’ UNION ALL
    SELECT 125, ‘ghijk’ UNION ALL
    SELECT 125, ‘ghijkl’ UNION ALL
    SELECT 126, ‘jklm’
    ),
    [Table 2 With RN](CID, [status], RN)
    AS
    (
    SELECT
    CID,
    [status],
    ROW_NUMBER() OVER(PARTITION BY CID ORDER BY [status])
    FROM
    [Table 2]
    )
    SELECT
    T1.CID,
    T1.STARTTIME,
    (SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 1) status1,
    (SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 2) status2,
    (SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 3) status3,
    (SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 4) status4
    FROM
    [Table 1] T1;

    Like

  92. Hello Pinal,

    First, congratulations on this website. It seems to be one of very few websites dedicated to real problem solving, beyond just the standard SQL language reference you find in so many places.

    Yesterday I encountered an SQL programming problem of my own.

    Imagine the following table columns:
    – timestamp (datetime)
    – variable_name (varchar)
    – variable_value (varchar)

    The table contains data records on multiple variables, and multiple records per variable. For example:

    01-01-2009 12:00, “a” , “1.0”
    01-01-2009 12:02, “b”, “2.0”
    01-01-2009 12:05, “a” , “1.2”
    01-01-2009 12:10, “b”, “2.5”

    The challenge: I would like to select the most recent value for each variable in the table. In this example, the 12:05 record on variable “a” and the 12:10 record on “b”.

    Obviously I could do an individual select on each variable

    ( SELECT TOP(1) timestamp, variable_name, variable_value
    FROM my_table
    WHERE variable_name = “a”
    ORDER BY timestamp DESC )
    + the same query for variable b.

    The problem with this approach is that the table contains data on more than 50 different variables. There has to be a way to combine this in a single “SELECT” statement.

    Any suggestions?

    Like

    • Freek,
      Check the below code snippet and I believe this is what you are looking for.
      create table #temp(
      time_stamp datetime,
      variable_name varchar(10),
      variable_value varchar(10))
      go
      insert into #temp
      select ’01-01-2009 12:00′, ‘a’, ‘1.0’
      union all select ’01-01-2009 12:02′, ‘b’, ‘2.0’
      union all select ’01-01-2009 12:05′, ‘a’, ‘1.2’
      union all select ’01-01-2009 12:10′, ‘b’, ‘2.5’
      go
      select t1.*
      from #temp t1
      inner join (select variable_name, max(time_stamp) as time_stamp
      from #temp
      group by variable_name)as t2
      on t1.variable_name = t2.variable_name
      and t1.time_stamp = t2.time_stamp

      Like

  93. Hii Pinal,

    I have gone through your blog many times and got my doubts cleared instantly, thanks for your support, I having an issues at my client site.

    table 1

    sno name comy
    1 aa xx
    2 bb yy
    3 cc zz

    table 1

    sno name comy
    11 aa xx
    12 dd yy
    33 cc zz

    no relation between two tables

    lookup table
    tab1sno tab2sno
    1 11
    2 12
    3 33

    Need to jon the 2 tables through lookup table , and need the details from the both, used the full outer join.

    Like

    • If you use version 2005 or above

      select t1.sno,t2.sno from
      (
      select row_number() over (order by sno) as rownum,sno from table1
      ) as t1
      inner join
      (
      select row_number() over (order by sno) as rownum,sno from table2
      ) as t2
      on t1.rownum=t2.rownum

      Like

  94. I did not get the following statment ….

    If you ask any one what is difference between udf and sp
    they are saying below statment..

    UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

    Stored procedures also return values and having out put parmeters so why we can not sp in select statment

    if is it possible how can we achive .. Please let me know

    Like

  95. How to fix this issue?

    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

    Like

  96. Hi
    I want to Count and display the Same Field Mutiple Times With Different Criteria from one table.

    For E.g
    I have a data base Anpdata file as Below

    FLD1 FLD2
    —— ——
    X 11X
    X 11X
    X 12Y
    X 13Z
    X 13Z
    S 11X
    S 12Y
    S 11X
    S 13Z
    V 12Y
    V 12Y
    V 11X
    V 11X
    V 13Z
    V 13Z

    Resultant must be as below

    FLD1 11X 12Y 13Z
    X 2 1 2
    S 2 1 1
    V 2 2 2

    Is it possible via sql?
    Here is my qry for 1 criteria

    SELECT FLD1, Count(*) as 11X from ANPDATA where X = ’11X’ and S = ‘ ‘ and V = ‘ ‘ Group by ANPDATA

    I need a query to display ALL 3 Criteria in one qry.
    Please help.

    Thanks
    Ajay

    Like

  97. @satish

    Is this what you want?

    WITH
    [table 1 a](sno, [name], comy)
    AS
    (
    SELECT 1, ‘aa’, ‘xx’ UNION ALL
    SELECT 2, ‘bb’, ‘yy’ UNION ALL
    SELECT 3, ‘cc’, ‘zz’
    ),
    [table 1 b](sno, [name], comy)
    AS
    (
    SELECT 11, ‘aa’, ‘xx’ UNION ALL
    SELECT 12, ‘dd’, ‘yy’ UNION ALL
    SELECT 33, ‘cc’, ‘zz’
    ),
    [lookup table](tab1sno, tab2sno)
    AS
    (
    SELECT 1, 11 UNION ALL
    SELECT 2, 12 UNION ALL
    SELECT 3, 33
    )
    SELECT
    [table 1 a].sno,
    [table 1 a].[name],
    [table 1 a].comy,
    [table 1 b].sno,
    [table 1 b].[name],
    [table 1 b].comy
    FROM
    [table 1 a]
    FULL OUTER JOIN
    [lookup table]
    ON
    [table 1 a].sno = [lookup table].tab1sno
    FULL OUTER JOIN
    [table 1 b]
    ON
    [table 1 b].sno = [lookup table].tab2sno;

    Like

  98. @Ajay

    How’s this?

    WITH
    Anpdata(FLD1, FLD2)
    AS
    (
    SELECT ‘X’, ’11X’ UNION ALL
    SELECT ‘X’, ’11X’ UNION ALL
    SELECT ‘X’, ’12Y’ UNION ALL
    SELECT ‘X’, ’13Z’ UNION ALL
    SELECT ‘X’, ’13Z’ UNION ALL
    SELECT ‘S’, ’11X’ UNION ALL
    SELECT ‘S’, ’12Y’ UNION ALL
    SELECT ‘S’, ’11X’ UNION ALL
    SELECT ‘S’, ’13Z’ UNION ALL
    SELECT ‘V’, ’12Y’ UNION ALL
    SELECT ‘V’, ’12Y’ UNION ALL
    SELECT ‘V’, ’11X’ UNION ALL
    SELECT ‘V’, ’11X’ UNION ALL
    SELECT ‘V’, ’13Z’ UNION ALL
    SELECT ‘V’, ’13Z’
    )
    SELECT
    FLD1,
    COUNT(CASE FLD2 WHEN ’11X’ THEN 1 END) [11X],
    COUNT(CASE FLD2 WHEN ’12Y’ THEN 1 END) [12Y],
    COUNT(CASE FLD2 WHEN ’13Z’ THEN 1 END) [13Z]
    FROM
    Anpdata
    GROUP BY
    FLD1;

    Like

  99. Hi Dave,
    Whenever i got stuck in any thing of SQL server. I tried it to find out in your blog.. This is first time i am writing to you. So, hopfully i ll get reply as well.
    I have date of birth and date of death in my talbes. i try to find exact age (like day, month and years) of my client. I used this sql but even it is giving wrong years.
    DATEDIFF(dd, dob, dod) / 365 as “Age”
    Can you please guide me. how can i get exact age of my client
    Regard
    Kaz

    Like

  100. Brian Tkatch
    I am confused

    Here is my Query for similar example

    SELECT ANENVI, count(*) FROM anpccdtl WHERE ANCCS = ‘D’ and ANCCZ = ’11x’ and ANCCS1 = ‘ ‘ and ANCCZ1 = ‘ ‘ and ANCCS2 = ‘ ‘ and ANCCZ2 = ‘ ‘ and ANCCS3 = ‘ ‘ and ANCCZ3 = ‘ ‘ and ANCCS4 = ‘ ‘ and ANCCZ4 = ‘ ‘ GROUP BY ANENVI
    UINON
    SELECT ANENVI, count(*) FROM anpccdtl WHERE ANCCS = ‘D’ and ANCCZ = ’17Y’ and ANCCS1 = ‘ ‘ and ANCCZ1 = ‘ ‘ and ANCCS2 = ‘ ‘ and ANCCZ2 = ‘ ‘ and ANCCS3 = ‘ ‘ and ANCCZ3 = ‘ ‘ and ANCCS4 = ‘ ‘ and ANCCZ4 = ‘ ‘

    The above Query dispays one below the other in same row.
    But the problem is, I need to display the field ANCCZ in colums
    as sown .i,e ANCZZ is one filed Have differnt records combinations,My Question is can Udisplay all three rcds in columns

    ENVI ANCCZ ANCCZ ANCCZ
    11x 17Y 14x
    ——————————————–
    X 2 1 0
    V 0 2 1
    S 2 2 0

    My data
    ———–
    ANENVI ANCCS ANCCZ ……………1 2 3 4
    ——————————–
    X D 11x
    X D 11x
    V D 17Y
    V D 17Y
    S D 11 x
    S D 11 x
    S D 17Y
    S D 17Y
    X D 17Y
    V D 14X

    Thanks You
    Ajay

    Like

  101. @Ajay

    What do you not understand about my solution?

    All i have done is use COUNT() with a CASE statement. If the code matches, we count it. Otherwise, the CASE statement turns it into a NULL, and COUNT() ignores it.

    Like

  102. Hello Pinal Dave,

    I am running a sql2008 server with IIS and a sharepoint site. The admin changed the IP address and some of my apps stopped working. Once I tracked down the IP address references I was good to go.

    The problem is with a sharepoint site I have now. I cant find anywhere that makes a reference to an IP. Should I delete the sp site and reinstall? ( I know if the server name is changed you have to do that)

    If you can think of somewhere that the IP is referenced that would be great!!

    thanks
    ben

    Like

  103. I have a table like as below:
    Table-A
    ———-
    Bill_Number Char(5)
    Bill_Seq Char(3)
    Line_Status Char(15)
    Bill_Status Char(15)

    The sample records would be like as below:
    Bill_Number Bill_Seq Line_Status Bill_Status
    11111 001 New
    11111 002 Ready

    22222 001 Rejected
    22222 002 Processing

    33333 001 Rejected
    33333 002 Close

    Now the above should be updated as below:
    Bill_Number Bill_Seq Line_Status Bill_Status
    11111 001 New Ready
    11111 002 Ready Ready

    22222 001 Rejected Processing
    22222 002 Processing Processing

    33333 001 Rejected Close
    33333 002 Close Close

    The condition is:
    1. If the line items for the bill has New and Ready change the Bill_Status to Ready.
    2. If the line items for the bill has Rejected and Processing change the Bill_Status to Processing.
    3. If the line items for the bill has Rejected and Close change the Bill_Status to Close.

    How can this be achieved using sql query. Please advice.

    Like

  104. @Nick,

    I Initially tried doing this with a select statement, but I could not figure it out, so I chose while loop to loop through all records and update our main table, I am sure, there must another (simple) way of doing this, but this is what I have came up with…

    Check if this could help

    — Script starts here.

    CREATE Table A
    (Bill_Number Char(5)
    ,Bill_Seq Char(3)
    ,Line_Status Char(15)
    ,Bill_Status Char(15)
    )

    Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 11111 ,001 ,’New’)
    Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 11111 ,002 ,’Ready’)

    Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 22222 ,001 ,’Rejected’)
    Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 22222 ,002 ,’Processing’)

    Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 33333 ,001, ‘Rejected’)
    Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 33333 ,002 ,’Close’)

    SET NOCOUNT ON
    Declare @table1 table ( Bill_Number varchar(5), Line_Status varchar(15))
    Declare @table2 table ( Ident int Identity , Bill_Number varchar(5))

    insert into @table1
    select Bill_Number
    , Line_Status
    from A
    order by Bill_Number ASC

    insert into @table2 (Bill_Number)
    select distinct Bill_Number
    from A

    declare @status1 varchar(15)
    declare @status2 varchar(15)

    declare @count int
    declare @string varchar (32)
    DECLARE @Bill_Number INT

    Set @count =1
    while @count < = ( select Count (*) from @table2)
    begin

    Set @status1 = NULL
    Set @status2 = NULL
    SELECT @Bill_Number = Bill_Number
    FROM @TABLE2
    WHERE Ident = @Count

    set @string = ''
    select @string = @string + case when @string = '' then '' else ',' end +LTRIM(RTRIM(Line_Status))
    FROM
    ( select
    Line_Status
    from @table2 T1
    join @table1 T2 on T1.Bill_Number = T2.Bill_Number
    where T2.Bill_Number = @Bill_Number
    ) X
    group by Line_Status

    SELECT @status1 = SUBSTRING ( @string , 1, CHARINDEX( ',', @string)-1)
    ,@status2 = SUBSTRING ( @string , CHARINDEX( ',', @string)+1 , LEN (@STRING) – (CHARINDEX( ',', @string)))

    IF (@status1 = 'New' AND @status2 = 'Ready') OR (@status2 = 'New' AND @status1 = 'Ready')
    UPDATE A
    Set Bill_Status = 'Ready'
    WHERE Bill_Number = @Bill_Number
    and Line_Status in ('New', 'Ready')

    IF (@status1 = 'Rejected' AND @status2 = 'Processing') OR (@status2 = 'Rejected' AND @status1 = 'Processing')
    UPDATE A
    Set Bill_Status = 'Processing'
    WHERE Bill_Number = @Bill_Number
    and Line_Status in ('Rejected', 'Processing')

    IF (@status1 = 'Rejected' AND @status2 = 'Close') OR (@status2 = 'Rejected' AND @status1 = 'Close')
    UPDATE A
    Set Bill_Status = 'Close'
    WHERE Bill_Number = @Bill_Number
    and Line_Status in ('Rejected', 'Close')

    Set @Count = @Count+1
    End

    SET NOCOUNT OFF

    GO

    select * from A

    If any one else has a better solution, please post it, it would be helpful for every one including me.

    ~ IM.

    Like

  105. Hello Sir,
    It’s first time that i am writing a message to you.
    i have some problem related with database queries actually
    i hav one excel sheet with me and i hav to import that sheet in sqlserever2000 The query i write is
    “select * INTO ” + tablename + ” from OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=” + dsn + “‘, ‘SELECT * FROM [Sheet1$]’) ” ok
    its working correctly but the problem is my requirement is to import only some fields not all fields of sheet1 and i dont knw where to write the specific fields name.so can you please solve my problem.

    It will be great help of urs.Thanks
    Regards Amit Shrivas

    Like

    • It should be

      “select * INTO ” + tablename + ” from
      (select col1,col2,…. from OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=” + dsn + “‘, ‘SELECT * FROM [Sheet1$]‘) ) as t”

      Like

  106. Dear Pinal,

    I am looking where I can dowload AdventureWorks Report Samples for SQL 2005 Express. I went to the site.
    But there is no way I can download report samples for sql server 2005. It’s always giving only download to SQL 2008 server reporting sample. Please could you indicate alternate location where I can dowload AdventureWorks Report Samples for SQL 2005 Express.

    Best regards, Jean Baptiste

    Like

  107. plz help me i seriously need your help
    i have to develop an ‘sql query cost based optimizer’ as my BE(IT) project and i am confused how to go about with it plz help me.it takes into consideration 1.sql query transformation 2.execution plan selection 3.cost estimation

    Like

  108. Respected Sir,

    I have completed PGDCA & MScIT and I am having a good expose for Microsoft technology i am also having experience about 2 years in this Developing field.

    but now i want to earn certain certificate like MCP or other Microsoft certification so i need your advice about

    1. which certificate is good for experienced developer and 2. Is there any special vacancy that need only person with these Microsoft certification … ?

    3. I enjoyed with ASP.Net Custom Server controls developing so is there any special requirement like
    “ASP.Net Server control developer ”

    so my main confusion is to how can i defined goal for specialization.

    My contact No is :

    Thanks With Regards,
    Hemal Bhatt
    Elitech Systems Pvt. Ltd. – Ahmedabad

    Like

  109. Please i am trying to populate a single colume that is within a table of 18 coulmns. I have been trying to foloow your steps but it appears im not getting it right.

    Here is my query

    INSERT INTO parceltable (gid, objectid, parcelid, year, allocation, parcelowne, sex__m_f_, parceltype, parcelarea, parcelar_1, parcelperi, x_cord, y_cord, id, clases, hectares, crop_type, geom)
    VALUES (”,1),
    (‘ ‘,2),
    (‘ ‘,3),
    (‘ ‘,4),
    (‘ ‘,5),
    (‘Irene’,6),
    (‘ ‘,7),
    (‘ ‘,8),
    (‘ ‘,9),
    (‘ ‘,10),
    (‘ ‘,11),
    (‘ ‘,12),
    (‘ ‘,13),
    (‘ ‘,14),
    (‘ ‘,15),
    (‘ ‘,16),
    (‘ ‘,17),
    (‘ ‘,18)

    This is the error i am getting.
    ERROR: INSERT has more target columns than expressions
    LINE 1: INSERT INTO parceltable (gid, objectid, parcelid, year, allo…

    Like

  110. Hi!
    I’ve downloaded installation files for:-

    1. SQL Management Studio_x64_ENU when i try to intall i got error msg says application is not support Win 32 and

    2. SQL server 2008 when am trying to install i got error says application is not support type of processer so what gonna do?

    Plz advice or give me another option, i need help

    Regards,

    Like

  111. hello sir ,
    I am facing one problem in Mysql . it’s a simple where condition but thought i am not able sort it out …so i need your help.

    I have one table suppose sample(id,Name);

    id Name
    1 abc’s home
    2 xyz’s home

    n i want to fire query like this

    select id from sample where name=’xyz’s home';

    but after running it giving error…

    any solution for this.

    thanks in advance !!

    Like

  112. Hi Pinal,

    I am using Soundex keyword in my sql query which is used for free text search. So I can get the related words also like if I search for butterfly then I will also get results of butterflies.

    But now I get one strange issue. When i search for ‘footwear’ word it also gives results of ‘fedora’ word. I have checked soundex code for both words and it is same.

    Can you please help me to sort it out? I just want to search related words like google do.

    Like

  113. hi pinal

    my query such like that :

    table name : amount
    field name : id and amount

    i run this query :

    select sum(amount) as amt1 from amount where amount>0
    union all
    select sum(amount) as amt2 from amount where amount<0

    Result :
    amt
    -21000
    31200

    but i want this type of result :
    amt1 amt2
    -21000 31200

    i hope this is enough to getting idea, what i want.
    please help me.

    thanks in advance

    Like

    • Ashutosh
      1. you can not have same column_name and table_name
      2.
      select sum(case when amount > 0 then amount end) as sum_positive,
      sum(case when amount < 0 then amount end) as sum_negative
      from

      Like

  114. hi 2 all,
    I have a problem with <>.
    <> this value is in the database name xyz, table customer, column telephone type.
    when I fetch this table into the .xls file It will automaticaly convert into combobox in xls file.I want to replace this value with ” “(space). Note: I have a 3 tables. I wrote join query with this field. …………………Please help me.?????????????

    Like

  115. how to get all database name from a sql server without run sql script, but use some command (run on DOS) or utility like :
    “osql -L > .\server.list”

    Like

  116. @sohil.

    Make you use of case function

    Select case when [telephone type] = ” then ‘ ‘ else [telephone type] End [telephone type]
    from xyz..customer

    Try this, Should work.

    ~ IM.

    Like

  117. hi 2 all,
    I have a new problem, i have a dateadded field which is getdate() type like 2009-09-09 00:00:00.000. but when I fetch that in my .xls file it will convert like 09-sep-2009 in my .xls file.

    Like

  118. Hi sir,

    Now days i m putting more interesting in the database professional. But mean while i m working in the both the platform as Asp.NET and sql server.

    How i can make the turn my carrier to change into the sql server platform completely ..

    Like

  119. Hi,

    in sql, i want to add constraint ( foreign key ) in existing table with existing column. but with alter table… add constraint.. syntax give error. Pls. help in this matter.
    thanx in advance.

    Like

  120. Hi, I need HELP from you..

    The One kind of Following Scenario is :

    Table in Sql server Database

    Fiscal year Sales
    ———— ——-
    2006-2007 100

    2007-2008 50

    2008-2009 200

    My Need is SSRS Charts from CUBES :

    I want column Chart for like to like sales..

    How to achieve this one

    Plz help me

    Thanks in advance …

    Like

  121. Hi All,

    I need a help in following scenario.
    I have to read data from a SQL Server database (for ex. XYZ_DB). XYZ_DB is getting restored from some other backup every night. There is no fix time of XYZ_DB restore.
    So my question is how do I know XYZ_DB restore got completed and I can start pulling data from it.

    I don’t have any control on the system which is restoring the backup file on XYZ_DB. So I cannot expect any kind of notification from that system, so that I can start my SQL job on XYZ_DB to read data. Whatever I have to do, I have to do it from my end.

    Pls let me know if any one have any idea on this.

    Like

  122. @Umakant,

    You can schedule a job, that will check for the database in online status.

    Schedule this job to run every minute, and check the status of that database, by querying sysdatabases view look for status online.

    if there exists a record in this view with this database name and status online, you can then start your main job which will read data out of your newly restored database.

    In between the two jobs, you could perform multiple steps by which you don’t repeat duplicate steps once database is detected to be online.

    If you need more help, please let us know.

    ~ IM.

    Like

  123. @Chirag

    Check Books online, there are plenty of examples for almost all cases of ALTER TABLE, follow one of the example.

    Also, next time you ask question, please post your script and error message, it will be helpful for us to identify issue.

    ~ IM.

    Like

  124. Does SQL Server 2005 has any facility like %ROWTYPE and %TYPE as in Oracle. I am fetching number of fields in a cursor and I need all those fields to be used in an INSERT query; how do I work with this without declaring variables as the number of fields in the Cursor query.

    Please help

    Like

  125. i want to select following records using this query

    select collect.File_No as FileNo,collect.Recept_No as ReceptNo, collect.Coll As Amount from collect where (col_date=’10/16/2009′)and (collect.file_no=’P0816′ or collect.file_no=’C7756′ or collect.file_no=’P0839′)union all select hirer.File_No as FileNo,0 as ReceptNo, 0 As Amount from hirer where(hirer.file_no=’P0816′ or hirer.file_no=’C7756′ or hirer.file_no=’P0839′)

    but it also generates duplicate record which must not b there

    Like

  126. @feroz

    Add DISTINCT to the query (or just change UNION ALL to UNION)

    BTW, (collect.file_no=’P0816′ or collect.file_no=’C7756′ or collect.file_no=’P0839′) can be a simple: collect.file_no IN (’P0816′, ’C7756′, ’P0839′)

    Like

  127. Hi,
    What is the difference between two string queries.

    1. ‘Create Table #Temp’+@RandomNumber+'(fields)’

    2.Declare @TableName Varchar(30)
    SET @TableName =’#Temp’+@RandomNumber
    ‘Create Table ‘+@TableName+'(fields)’

    Replacing all the references to the table name with this variable .

    And first one taking too much time to execute.

    Thanks.

    Like

    • What did you mean by duplicate?
      Note that you have used 0 for some of the columns in the second part of the union all that cuases this
      Post some sample data with expected result

      Like

  128. HI There, I have a question for you? I have an individual who is wanting to go into the sql 2005 cert. track but he also wants to learn scripting languages and VB. Where would those fall into the track? If he was starting with database and access, the going into Microsoft IT, would he take the scripting language before of after the SQL?/

    He is a programmer from years back and knows mainframe language but hasn’t had much hands on with the pc. so his skillset is outdated, but he knows programming. He has been going to university here, but is thinking of switching to my school which offer certification. Any advice you would have is greatly appreciated.

    Like

  129. Hi Pinal,

    I am facing some problem in LINQ.

    There are three tables, let’s say ‘Profile’, ‘Test1′ & ‘Test2′. (There are other tables, too).

    Profile – Id, Username, Email, Password

    Test1 – Id, Title, Description

    Test2 – ProfileId, Test1Id, Percentage

    Test2.ProfileId & Test2.Test1Id are definned as a composite primary key and foreign key reference to relevant tables.

    Test2 table has more than 9000000(9 million) records.

    Now I want to implement paging, so I am using .count() method of IQuerable object, but it is giving me following error.

    System.Data.SqlClient.SqlException

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    You can look at code in following post.

    http://forums.asp.net/t/1483028.aspx

    I’m not getting idea what to do further.

    Please help me out of this.

    Thanks in advance.

    Like

  130. Hi all
    Can someone help please ????????????

    how to get all database name from a sql server without run sql script, but use some command or utility like
    “osql -L > .\server.list” run on DOS will list all SQLserver on your network

    Like

  131. Hi,
    I am working on Dataconversion project. I want to validate the source data comes from Sqlserver 2005 and destination data from Oracle. I used to work on VB6 to .net on sql server conversion project. When I was validating the source and destination data I have written as one query for source and destination tables and gave the different server names on joins. My question is Can we write in query the Sql server data and oracle data? or Do we need to different queries for comparing the source and destination?

    Like

    • Either follow what you used now or make use of Linked Server so that you can query ORACLE data within a connection to SQL Server

      Read about sp_addLinkedServer in SQL Server help file

      Like

  132. Hi all,
    I have written one SP, which takes “ItemName” as input and returns “ItemGroup” of that Item.THese both fields are in “Item_master” table.

    when I execute this SP from SQL Server, I want to execute it for each n every “itemname” for which there is entry in table called “Sales_detail”.

    so for executing this SP, I hv written code like below, but it goes in infinite loop, why is it so?

    declare @itemgroup varchar(100)
    declare @itemnm varchar(100)
    while (exists(select itemname from sales_detail))
    begin
    select @itemnm=itemname from sales_detail
    if @itemnm is null
    break
    else
    continue
    exec dbo.getgroup @itemnm,@itemgroup out
    select @itemgroup
    end

    Like

  133. hi,

    what is the difference between sysobjects and sys.objects

    E.x
    1. select * from sysobjects
    2. select * from sys.objects

    it will return same affected rows. but some column id different.

    can u pls explain with technical reson.

    Thanks in advance.

    Like

  134. Hi Pinal,
    I am working as software engineer in Microsoft technologies working in asp.net.
    I want to become SQL Server DBA for this specify me a good institute where can i learn thoroughly in ahmedabad or in gandhinagar.

    thanks & Regards,
    Giridhar

    Like

  135. Hi Pinal can you please help me on below issue, I want to implement Full text search with join query but faced below problem

    i want to use full text search in sql server 2005.so i am facing the problems which is as below.

    1) i am using 6 tables.

    i)Product (ProductID primary key,Name,Description,Summary,ImageFileNameOverride)
    ii)Maufacturer(ManufacturerID,Name,Description)
    iii)Category(CategoryID,Name,Description)
    iv)ProductCategory(ProductID,CategoryID)
    v)ProductManufacturer(ProductID,ManufacturerID)
    vi)ProductVariant(ProductID,VariantID,SalePrice)

    i want this result
    ProductID,ProductName,ManufacturerName,CategoryName,ProductSummary,SalePrice,ImageFileNameOverride

    2) one product have multiple category.
    3) while i create a view the index in not possible because there was an error
    “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.ViewSearch’ and the index name ‘IX_ViewSearch’. The duplicate key value is (3).”

    4)i have tried also with temporary table but the it gave me an error that #temp is not available.
    5)when i change the datatype in view because index is not perform on ntext,image,text and xml type so i have converted
    to varchar(max) but still it gave me an error that there may be a usage of image,ntext,text or xml value.
    6)and when perform the below query
    SELECT product.ProductID,product.Name,product.Description,SalePrice,Product.ImageFilenameOverride FROM product,ProductVariant,Category WHERE FreeText((Category.Name,product.Name,product.description,product.summary,product.SETitle,product.SEDescription),'”Apple”‘) AND Product.ProductID=ProductVariant.ProductID
    it gave me an error that Fulltext predicate references columns from two different tables or indexed views ‘Category’ and ‘product’ which is not allowed.

    so please give me a solution as soon as possible

    thanking you

    Like

  136. Hi pinal,
    wondering if you could post something regarding credit card encryption and decryption like — ways to implement encryp/decrpttion,how they work and how to secure them.

    thanx in advance.

    Like

  137. hi pinal….

    I used the property called ‘WITH ENCRYPTION’ (while creating a procedure sp_testProc ) in order to make sure that my juniors who use that procedure will not able to edit it & modify it but unfortunately i am unable to get back the script of the same procedure sp_testProc…….. can I get the script or else I need to write it again

    If u have any other option of encryption also U can suggest me please

    Like

  138. Hi Pinal,
    I want to learn how to create simple SP and executing them directly from SQL Server as well as from VB.NET code, if u can show me some example or else if u hv written some E-books regarding this please tell me.

    Thank you.

    Like

  139. Table 1
    ——-
    LANG_VALUE
    LANG_DESC

    Table 2
    ——-
    EmpId
    LANG1
    LANG2
    LANG3
    LANG4
    LANG5
    LANG6
    It looks like as below:

    Table 1
    ——-

    LANGVALUE LANGDESC
    ——————-
    SPAN SPANISH
    GERM GERMAN

    Table 2
    ——-
    EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6
    —————————————–
    1 SPAN NULL NULL NULL NULL NULL
    2 GERM SPAN NULL NULL NULL NULL
    3 GERM NULL NULL NULL NULL NULL
    Expected Result:

    EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6
    —————————————–
    1 SPANISH NULL …
    2 GERMAN SPANISH NULL…
    3 GERMAN NULL…
    How to do this in sql query

    Like

  140. @Nick, this is a standard join:

    SELECT
    [Table 2].EmpId,
    T1.LANG_DESC,
    T2.LANG_DESC,
    T3.LANG_DESC,
    T4.LANG_DESC,
    T5.LANG_DESC,
    T6.LANG_DESC
    FROM
    [Table 2],
    [Table 1] T1,
    [Table 1] T2,
    [Table 1] T3,
    [Table 1] T4,
    [Table 1] T5,
    [Table 1] T6
    WHERE
    T1.LANG_VALUE = [Table 2].LANG1
    AND T2.LANG_VALUE = [Table 2].LANG2
    AND T3.LANG_VALUE = [Table 2].LANG3
    AND T4.LANG_VALUE = [Table 2].LANG4
    AND T5.LANG_VALUE = [Table 2].LANG5
    AND T6.LANG_VALUE = [Table 2].LANG6;

    Like

  141. hi pinal

    I got a scenario like this

    Say there is

    1)students table (tbl_students)

    with fields
    1)student_name
    2)student_id

    2)their marks table (tbl_marks)
    with fields
    1)student_id
    2)subject_id
    3)marks (out of 100)

    I need to get all students with marks 80 and 90. I mean
    not in (80,90) but those who got both 80 and 90 no matter what the subject is

    And once i got this result next i want is to get all students with marks 80 and 90 but without 70 for a subject X.

    i mean those who got both 80 and 90 but havnt scored 70 yet for the subject X .Can u please help this.

    thanks

    Like

  142. hi pinal in the above scenario what will be the case when its an institution where there are thousands of students and thier marks .Could you please help me and reply at my email id.

    thanks

    Like

  143. @Satheesh

    For both 80 and 90:

    SELECT
    student_id
    FROM
    tbl_marks
    WHERE
    marks IN (80, 90)
    GROUP BY
    student_id
    HAVING
    COUNT(DISTINCT marks) >= 2;

    For both 80 and 90 but not 70:

    SELECT
    student_id
    FROM
    tbl_marks
    WHERE
    marks IN (70, 80, 90)
    GROUP BY
    student_id
    HAVING
    COUNT(DISTINCT NULLIF(marks, 70)) >= 2
    AND COUNT(CASE WHEN marks = 70 THEN 1 END) = 0;

    Like

  144. Dear Pinal,

    I am developing FAS (Financial Accounting System) with .NET and Sql 2005. I have plan for a single database for multiple year (Is it worth ?). What should be the best method to calculate Balance of each account ? There should be heavy amount of data in table.

    The scenario like….I have a table with all the debit credit transactions which has any number of rows. So when i retrieve balance of a single party then i have to scan entire table. so what is the best method to do that ?

    Should i make another table which records partycode,financialyear and debit credit balance ? (Single row for single party). so that i can retrieve from that table.

    If i make different database for different financial year (after year ending), how can i affect balance in current financial year if i change any voucher or transaction in previous year ?

    Like

  145. Dear Pinal,

    i want to select 3 columns but on three different conditions

    how can i query that as my database structure is

    create table HIRER(
    DIST_CODE Char(9),
    BR_CODE Char(9),
    EMP_CODE Char(9),
    FILE_NO Char(9) primary key,
    HIRER_NAME Char(51),
    DISB_TYPE Char(15),
    STARTING Datetime,
    INSTALMENT Numeric,
    MODE Char(1),
    CLOSING Datetime,
    OVERDUE Numeric))
    go
    create table COLLECT
    ( FILE_NO Char(9),
    RECEPT_NO VARCHAR(60),
    COLL NUMERIC,
    col_date datetime,
    coll_type varchar(10),
    fin_year varchar(9)
    constraint uk_collect unique (recept_no,fin_year))

    and my query is

    select branch.br_name as Branch,hirer.br_code,sum(R.s1+A.s2) as Actual,s.coll
    from
    (select br_code,sum(hirer.instalment) as S1 from hirer inner join collect on hirer.file_no=collect.file_no where hirer.closing>’5/27/09’group by br_code) R,
    (select br_code,sum(hirer.overdue) as S2 from hirer inner join collect on hirer.file_no=collect.file_no where hirer.closing<'5/27/09' group by br_code) A,
    (select br_code,sum(collect.coll) as Coll from hirer inner join collect on hirer.file_no=collect.file_no where collect.col_date between '1/1/2009' and '10/31/2009' group by br_code ) S,
    branch inner join hirer on branch.br_code=hirer.br_code inner join collect on hirer.file_no=collect.file_no
    where collect.col_date between '1/1/2009' and '10/31/2009'group by br_name,hirer.br_code,s.coll

    actually i want to generate a report to find branch wise collection and actual amount we have to collect from customers

    please solve my problem

    Like

  146. Hello ALL,

    I have a question related to update statment.

    we have a table name EMP in this table there is a collumn name Status. The Status have 1 and 0 in this callumn we need to update all 0 in 1 and all 1 in 0.
    But in a single statment.

    If any one knows this answer please send it to me at rakesh.cdac@yahoo.com

    Thanks
    Rakesh Kumar

    Like

  147. Hi Rakesh,

    You can do this by update statement.

    SQL will only update values after statement execution, so you can write it in update statament,.

    Let see example:

    DECLARE @T AS TABLE(
    ID INT IDENTITY,
    A BIT,
    B BIT
    )

    INSERT INTO @T(A,B) VALUES(0,1)
    INSERT INTO @T(A,B) VALUES(0,1)
    INSERT INTO @T(A,B) VALUES(1,0)
    INSERT INTO @T(A,B) VALUES(1,0)

    SELECT * FROM @T

    UPDATE @t
    SET A = B,
    B = A

    SELECT * FROM @T

    Thanks,
    Tejas

    Like

  148. SQL SRVR 2000

    Using DTS – Import delimited file into temp table. I run a validation script against temp table to check for valid data in a column.

    For a column that should contain data – an IS NULL test.
    For a column that should NOT contain data – and IS NOT NULL test,

    However, for a column that is expected to be NULL, IF there is a space, it returns a false positive for an IS NOT NULL Test.

    I have looked everywhere to find a way to use SQL to tell me that what is being reported is a space – not data.

    Please help.

    Like

  149. Hi Rakesh,

    littel change, if req.

    update emp set status = ((status-1) * (-1))

    where status in (0,1)

    (if status have different value then 0,1)

    Like

  150. I have a production SQL Server 2000 instance that I back up (databases) daily and restore to a failover SQL server instance. Both SQL servers have inherited their default (host server)names. I can backup my production databases (SQL Server 2000) and restore them to the failover server (SQL Server 2005) and all databases work fine, and the Dot Net applications that connect to each of them run fine.

    When I attempt to back up a database from the failover instance, I receive the following error:

    Backup failed for Server ‘Sandbox’ (Microsoft.SqlServer.Smo)

    Additional information:

    System.Data.SqlClient.SqlError: THe backup of the filegroup “sysft_Name” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo).

    I managed to find some article a while back that indicated that this was catalog related (system files thinking that the server instance is still the production server) and that deleteing the catalog and recreating it would solve the problem, but I don’t know how to do that. Has anyone in this forum run into this before/ It is especially pressing, because I want to upgrade my production SQL server, and need to know that I can backup a restored database from a server that was named differently.

    The master database backs up ok, it is just the other database that throws the error.

    Like

  151. Hi Pinal,

    I wanted to fetch data from DB2 for reporting. I could not decide which tools is better. SSRS or Crystal Reports. Your help will be appreciated.

    Thanks,
    Preet Patel

    Like

  152. Hi Pinal,

    I like your website and your answers to questions of others.

    I was wondering, if you can help me on a small issue.

    I am trying to create a stored procedure to populate a table in my database with data from tables in another database. So I created synonyms of those tables in my database and added them in the stored procedure.

    Now I want every user, whom I give execute permission to be able to access the underlying tables in another database, without specifically giving them SELECT access to those tables.

    Do you think there is a way I can do this.

    Thanks,

    Nitin

    Like

  153. Hi Dave,
    Could you please tel me as how could i retrieve all the schema names from a database by passing the database name as a parameter using a stored procedure

    Like

  154. Hi All,

    Is this possible?

    I have this query which inserts records on table A (which has an increment identity column) from a temp table; when executed let’s say it has inserted 1000 records…

    Insert Into tableA (Email,Name)
    select email,name FROM tempTable
    WHERE email in (select email from tempTable where siteid=5);

    What I want to do is to get all those 1000 identities generated on the insert command and put them on a temp table. Is there a way of doing this?

    Thanks in advance!

    Like

  155. @Aleks

    A few ways:

    1) In the INSERT use OUTPUT INSERT.Id, INSERTED.email INTO @temp_tab

    Then use @temp_tab to UPDATE tempTale

    2) UPDATE tempTable SET id = (SELECT Id FROM tableA WHERE tableA.email = tempTable.email);

    3) same UPDATE, but use a join.

    Like

  156. SELECT D.Branch_Name,D.branch_id,D.Branch_Cd,(D.Add1+’ ‘+D.Add2+’ ‘+D.City)AS Address ,A.NAME,A.SMC_CD,A.Enroll_Dt,B.DATA, (select Name From Employee_master where Parent_id in (Contact_id))
    FROM EMPLOYEE_MASTER A
    INNER JOIN MASTER_DATA B ON B.DATA_ID=A.DSGN_ID
    INNER JOIN branch_master D On A.branch_id=D.branch_id

    I have table which column are mentioned in query.Contact_Id is PK and Parent_id reporting head ID.IF I am Manager My Contact ID is 1001 after some time I hired an assist manager they will report me so assist manager id will be 1002 and parent ID will be 1001.So I write above query and want to get reporting head ID with his name.So I write above query and it raise an error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

    So give me solution

    Warm Regards
    Najam Khan
    Software Engg.

    Like

  157. Hi Pinal Dave,

    I just wanted to express my gratitude for your site. I read tons of other sites but yours stands out for straight forward solutions. So don’t be too jealous of Steve Jones! He is great but so are you!

    Take care my friend and congratulations on your cute baby!

    Howie

    Like

    • Thank you howie,

      I appreciate your kind words. Steve Jones is my personal friend and you can see I have several of his photo graphs on my site. We are together as friend and appreciate each other. In fact, I do put on SQLServerCentral.com T-Shirt quite often.

      Thank you again for you visiting my site and observing the details. Very few people leave comments so those who leave comments, I thank them.

      Regards,
      Pinal

      Like

  158. Hello to all,

    This is a very good webside for information related to sql. I m persuing MCA ,please tell me how can i get quaries related to sql.

    Like

  159. Can you pls help asap?????

    when i migrate tables from oracle to sql server using import/export wizard , i choose option delete rows in destion table in wizard but at the end it showing u can’t truncate table it is referenced by other table.but i am 100% sure that i disabled all the foreign keys? Can you please help me its urgent??????????????????

    Like

  160. Hi Aleks,

    You can write your query as:

    DECLARE @Table TABLE(Seq INT)

    INSERT INTO Table(Column1, Column2)
    OUTPUT inserted. INTO @Table
    SELECT C1, C2

    This populate your table variable with inserted identity values.

    That you can use it to next queries for the session.

    Thanks,

    Tejas

    Like

  161. Thank you guys,

    I forgot to say that the Sql server version I’m using is 2000 which doesn’t have the OUTPUT clause; and the other approaches didn’t work at all.

    So far I have solved the issue using this query, but it’s not 100% secure…

    SELECT subscriberId INTO #TempTable2 FROM
    (
    SELECT TOP 1 WITH TIES id FROM tableA
    ORDER BY Date DESC
    ) as t;

    which takes all records that were inserted on the same date time as the top 1 and puts them into tempTable2, but if someone else is doing and insert on the same moment then I guess those records will be selected too.

    Like

  162. Hi Pinal and others,

    I am using one SQL Server (2005 Entp.) Database which is connected with a specific application. I want to create a Mirror Database from this SQL server Database which will work with the Mirror of this application. And of course, this mirror database will synchronize from the original database from time-to-time as usual.
    Now, as far as I know there are quite a few procedure to make a mirror from the database, such as Database Log shipping, Database mirroring, Database Snapshoot and so on. But the thing is, these all procedures create a ”Read-Only” DB, which synchronizes with the main Database. I am trying to create the mirror database with ”read and write” access to other application separately.

    My queries are:
    1. Is it possible to create a mirror database with ”read and write” operation?
    2. If yes, what is the procedure to do that?

    Like

  163. Hi Koushik,

    It is not possible to configure Mirroring with “Read and Write”.

    You can not access or query to mirror database.

    If you want to access same copy of DB then you have to implement replication. this will solve your purpose.

    Thanks,

    Tejas

    Like

  164. Hi Tejas,

    you are correct .During mirroring ,it’s not possible db with read and write.
    Actually i mean to say mirror means duplicate copy of Primary database with proper synchronization.

    Also replication of database its not possible because during replication db state is read only or recovery mode.

    Thanks

    Koushik

    Like

  165. I need to find the recent date should be displayed from the results

    The table looks like as follows

    ID Startdate endDate
    1 10/10/2009 10/30/2009
    2 10/15/2009 11/20 2009
    3 11/10/2009 12/31/2009
    4 09/01/2009 10/25/2009

    I should be able to get the 3rd record only

    Like

  166. Hello pinal,

    I have a very strange SQL 2005 installation problem which I have put in lot of forums including Microsoft’s one without any positive result.

    Whenever I try to install SQL 2005, I get this error

    “Failure setting security rights on DTS user account $ComputerName”.

    After this error, my installation is stopped abruptly.

    Please can you tell me how to solve this error.
    You are my last hope, Trust me i have put this in almost all sql forums with no positive result. please help.

    Like

  167. Hi,

    We are using master.dbo.xp_smtp_sendmail .

    EXEC master.dbo.xp_smtp_sendmail
    @from = N’spiProjectqmail@spi.com’,
    @TO = @Recipients_Id,
    @message = @strMessage,
    @type = N’text/html’,
    @subject = @strSubject,
    @server = N ‘IP address’

    TestSP contains above staements and also its working fine in SQL Server 2008 Query Editor but same TestSP we created Job and run that job its giving error.
    Message
    Executed as user: dbo. The EXECUTE permission was denied on the object ‘xp_smtp_sendmail’, database ‘master’, schema ‘dbo’. [SQLSTATE 42000] (Error 229). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.

    Any Configuration Required for xp_smtp_sendmail to run from SQL Agent.

    Can you please help me, this week end production migration.

    Like

  168. Hi,
    i want a query that add an existed user login to specific databases with a specified role (db_datareader) and not using management studio to add a user to a role for every database. Is there a way to do that?

    Thank you in advance.

    Like

  169. @Frida,

    Can you provide more details, why you do not want to use Management Studio.

    Do you want a Stored Procedure, which you can call from (Front End) application to add a user ……

    If you want a Stored Procedure, then yes, it could be done.

    ~ IM.

    Like

  170. @Krishna Mohan

    To answer your question quickly, You said, you put that script in a job and execute job and it fails,
    Solution: Give the owner of the job execute permission to stored procedure : xp_smtp_sendmail in master database. Below is a sample script.

    use master
    go
    Grant Execute on xp_smtp_sendmail to [Job_Owner]
    go

    Question:
    Dude, did you said, you are on SQL Server 2008, why are you still using xp_smtp_sendmail stored procedure, this is not a native SQL Server stored procedure, this is a third party stored procedure.

    Instead of this you can use, SQL Server native stored procedure with same functionality as xp_smtp_sendmail, this procedure is available from SQL Server 2005 and higher version.

    Stored Procedure Name: msdb.dbo.sp_send_dbmail

    Let us know, if you need more help with your migration.

    ~ IM.

    Like

  171. @Anil

    create table #example1 ( ID int, StartDate datetime, EndDate datetime)
    insert into #example1 values ( 1, ’10/10/2009′, ’10/30/2009′)
    insert into #example1 values ( 2, ’10/15/2009′, ’11/20/2009′)
    insert into #example1 values ( 3, ’11/10/2009′, ’12/31/2009′)
    insert into #example1 values ( 4, ’09/01/2009′, ’10/25/2009′)

    select ID, StartDate,EndDate
    From #example1
    Where EndDate = (select Max(EndDate)from #example1)
    go
    drop table #Example1

    ~ IM.

    Like

  172. Hi Imran Mohammed,

    Thanks for yours reply.

    exec PRJ_SP_Project_PMT_Notify

    Note : first give spiProject login account permission for mater database.

    Use Mater
    grant execute on xp_smtp_sendmail to spiProject

    use master
    go
    Grant Execute on xp_smtp_sendmail to [Job_Owner]
    go

    I done all the settings , no luck and also insted on xp_smtp_sendmail i used msdb.dbo.sp_send_dbmail.

    I done all the Configuration for sp_send_dbmail. Through SP mails going same thing i execute from job same error coming.

    Using Database Mail with SQL Server Agent

    http://msdn.microsoft.com/en-us/library/ms190606.aspx

    above settings also i done. Why emails are not triggering from jobs through storeprocedures its triggering mails.

    Like

  173. Hi Pinal,

    I have created 2 schemas in one database and trying to run the script in order to create tables under schemas. but problem is that script has another schema inside it and when i run that script its giving me error like:

    User does not have permission to perform this action.
    Msg 2759, Level 16, State 0, Line 2
    CREATE SCHEMA failed due to previous errors.”

    E.g

    I have 2 schemas say Tarun1,Tarun2 under abc database. now I want to add some table under Tarun1, Tarun2 schema. (something like nested schemas… )

    Here is script:
    – Create schema for common tables
    create schema OLS_TABLES
    ;

    – Create common tables
    create table OLS_TABLES.Table1(
    ID nvarchar(20) not null,
    TYPE nvarchar(50) not null,
    SCRIPT varbinary(max),
    primary key(ID, TYPE)
    )
    ;

    Can we add OLS_TABLES schema under Tarun1 schema? If yes then please suggest me how we can do that? what Kind of permissions we need?

    Waiting for your reply!
    Thanks in advance

    Tarun

    Like

  174. Hi,

    Give me few most promising reason when we are left with no option and have to use cursor only instead of while loop.

    Thanks

    Anil

    Like

  175. Hi Pinal,

    I am working on a process flow application and within a group of records, I need to create subgroups based upon certain actions.

    As the process moves from one person to the next, the current user can reject the process, causing it to move back to the beginning again. The first time the process goes through would be subgroup 1. If it gets rejected, the next group will be subgroup 2. Going forward, this would be pretty easy if I could change the table structure and add a new column. Unfortunately, that is not possible. This will have to be a dynamic process.

    I know I could do this with a cursor or other looping structure but would rather use CTE.

    Can you give me any thoughts on how to accomplish this?

    Thanks!

    Clay

    Like

  176. Hi Pinal, I have a question for ms sql 2000 that you maybe can help me.

    I’m not a dba, I was just needing to do a “what I thought was a sinple task” and suddenly I meet hell.

    What I need to do is as simple as to duplicate a database with a different name and I have no found a procedure to complete this task successfully.

    I have seen this http://blog.sqlauthority.com/2007/07/13/sql-server-2005-use-alter-database-modify-name-instead-of-sp_renamedb-to-rename/ and this http://www.mssqltips.com/tip.asp?tip=1122 with no results. always the data file keeps having same name and I cannot mount both databases at the same time in same server.

    What I need is… simply a database called PRODUCTION copy this same entire database to same ms sql server 2000 with other name like TESTDB.

    If you have a way of doing this please contact me!

    Thank you very much.

    Regards,
    Pablo Alonso

    Like

  177. We have one production DB. We are copying data from production ,say X….and archive DB , say Y, to one more DB , say Z….which is specially used for reporting.

    Our reports will always point to reporting DB Y….and every day there is reconsolidation of data for each process like orders, lens etc into respective tables in reporting DB.
    Reconsolidation scripts will use the ARCHIVE DB Y.

    Now archive DB Y. size is very large so customer decided to split the DB into year wise DB’s…

    Like
    • Y.__ARCHIVE_2009
    • Y.__ARCHIVE_2008
    • Y.__ARCHIVE_2007

    If we do this change,we have to run consolidation scripts on every above DB’s…now its 2009 but in future 2020, we have to run scripts in 10 to 20 Archive DB’s..

    My question: Is it right way to handle this kind of satuation? If not, what is the best approch?

    Like

  178. hi pinal,
    i was looking for Reporting Services at your blog but no luck.
    Please can you suggest some good reference to SQL Reporting services? or
    Can you post something related to reporting services?

    Thanks

    Like

  179. Hi Pinalkumar,

    I have a concern regarding Stored Procedure & Execution Plan Cache.
    I’ve heard that it’s not good when the execution logic depends too much on the input parameters, wrong execution plan maybe chosen and causes bad performance. However I have a scenarios like this: I have 3 task to do: Update, Insert, Select. And I have 2 way to create SPs

    1. Create 1 SP only, with an input parameter to tell which action need doing, something like:
    if ‘U’ then update data
    if ‘I’ then insert data
    …….
    2. Create 3 separate SPs for those 3 actions.

    For not too complicated SPs, I like option 2, to reduce the number of SPs. However, which is better regarding performance on large databases?

    Thank you very much,

    Like

  180. hello Pinal,

    Im a web site developer, I have a question for you. currently my team is busy doing database optimization task. I was asked to sort out the SPs and it full statics. I was able to do the thing except the condition, which is very challenging.
    We r using sql server 2005(Without sp2).
    My Query is that i have to sort out all the SPs which was never used, at this moment im able to get list with the help of sys.dm_exec_cached_plans but its giving the list which was executed at least once, furthermore there is no relation between the rows returned by sys.dm_exec_cached_plans and sys.object so that I can get SP name and its execution_count in a single query.

    Please suggest help…
    Thanks in advance.

    Like

  181. Hi,

    Need some help regarding SQL server 2005 .

    I have set up replication on SQL server 2005 with tables and Clustered and non clustered indexes being replicated.

    What i want to know here is what are the important parameters to be monitored and how to set up Notification alerts for the monitoring of these parameters or failures .

    Thanks

    Pravin

    Like

  182. Okay, I did this but I was able to inject (both delete and drop table statement worked.). Can you please a solution on sp_executesql. Yes, I did LOTS OF GOOGLE FOR PAST 8 HOURS WITH NO LUCK… THANKS

    CREATE PROCEDURE uspGetEmployee
    @LastName VARCHAR(50) = NULL
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(300)
    SELECT @sql = ‘ SELECT * ‘ + ‘ FROM EMPLOYEES
    WHERE 1=1 ‘
    IF @LastName IS NOT NULL
    SELECT @sql = @sql + ‘ AND [LASTNAME] = @LastName’
    EXEC sp_executesql @sql, N’@lastname VARCHAR(50)’, @LastName
    end
    GO

    Here is how I deleted and drop the table…

    DECLARE @RC int
    DECLARE @LastName varchar(50)
    EXECUTE @RC = [Northwind].[dbo].[uspGetEmployee]
    delete from employees where lastname=’king’

    Like

  183. Can you pls help asap?????
    I an new in sql Server, i need to become SQL server DBA, So plz tell me the best books for beginners. So that i can learn from A to Z of SQL server 2005

    Like

  184. Hi,

    I have just started learning TSQL and I have a basic question.
    ? If there are 100 million rows with 20 indexes, I want to delete 90 million rows from it.
    what I have to do for this?

    Like

  185. Hello Pinal sir,

    can u plz help me with this

    we r implementing some standards so we need to know

    can we monitor the sql error log configure settings which is greater than 0. I know sql server allows to store between 6 – 99 logs…I want to know where it store this information in system tables or registry some where etc..if we configure the log setting to 20 or 34 where does sql server store this information and is it possible to monitor these settings

    THANKS IN ADVANCE

    Like

  186. Hi,

    my client now want to migrate their Legacy Oracle database into SQL Server 2005 database uisng SSIS.

    can anybody suggest me some good URL or reading material for Oracle to SQL Server migration ?

    what to consideration and how to

    thanks.

    Like

  187. Hi

    I am executing query as follows

    select top(1) city, state from city_state_finder where
    freetext((city,state),’vancouver’) and freetext((city,state),’on’)

    While executing this query response will not come as it’s delaying the result without giving any errors.

    But if used same query without top(1) then result is coming up in 3 seconds.

    select city, state from city_state_finder where
    freetext((city,state),’vancouver’) and freetext((city,state),’on’)

    Please guide me why this is happening & how can i over come this situation.

    Thanks
    Shiv

    Like

  188. Hello Sir,

    Is that possible to recover truncate table in Sql Server 2000 without any available backup ?

    Please give me tips or idea to recover truncate table.

    Thanks
    Bhargav

    Like

  189. Hi all,

    I have a problem in Sql 2005. I have design a databse in Sql 2005 with applicable triggers, procedures , function etc. my database is handled properly from front end. Now, what i want that i have to create a new database which has same features as my current database. how can i generate ? pls. help me.
    thanks in advance.

    Nayan

    Like

  190. I will need your help,

    I have one table TAB1 with atributes name, surname
    I need to copy all data from tab1 into tab2 using cursor…
    tab2 has the same atributes…name, surname
    Can you help me with it?

    Like

  191. @Shiv

    This probably has to do with the optimizer plan. Usually, the optimizer is very good at picking the right plan, but sometimes it isn’t.

    To see if this is the issue, go to the menu “Query” then “Include Actual Execution Plan”

    Like

  192. Hey Dave,

    I am trying to figure out a way to find relations of tables in a database without constraints using “parsing the create statement of procedures”. Could you give any hint? Thanks…

    Like

      • Hey Brian. There are no keys whatsoever. It is an un-relational relational sql server db. I did parse the procedure definitions for cases like table1.column1 = table2.column2

        Like

          • I am defining logically “related tables” by discovering the logic embedded in stored procedures and examining data.at leas that is what I am hoping to do

            Like

          • @Ugur

            That sounds complicated, and probably best done by hand.

            But i am confused. If the relations are defined by their usage in SPs, how do you expect to find their relation without parsing those very SPs?

            I guess, technically, you could watch executed SQL statements, but even if that worked, is that really any different than parsing the SPs themselves?

            Perhaps the COLUMNs are similarly named?

            Like

      • Thats what I said actually. I am parsing SPs for the join cases on table column couples…Table1.column1 = table2.column2. Cases tools like PD can also help you related similar named columns but its nonsense so what I do is to find the cases and check if the tables are any of the dependent tables of the SP. u can find that using the query analyser or the INFORMATION_SCHEMA views…I will post the script when I am done

        Like

        • @Ugur

          Hmm.. If all the SQL statements use semicolons, parsing the FROM until the semicolon should find all TABLEs and the relations.

          Just exlcude any time where a literal is used, or only inlcude when a COLUMN is used on both sides, then ORDER BY the clauses (possibly duplicating every clause so it shows up backwards and forwards).

          It’s take a few minutes to write such a query, but shouldn’t actually be that bad.

          Like

  193. Hi all,

    I want to know that, if we apply trucate statement,
    will it effect on indexes, both cluster and non cluster?
    additionally, should ‘trucate’ affected any sql objects like
    trigger,sp,function etc…

    pls. reply

    Thanks

    Chirag

    Like

  194. Ha Panel – you seem to always help me an you dont even know – thanks to your blogs. Got a question for you. I am trying to create a full text indexing using the wizard in 2005 and I am getting this error message:

    “could not load type ‘microsoft.sqlserver.management.smo.agent.jobbasecollection’from assembly ‘microsoft.sql.server.smo, version=9.0.242.0,…”

    If you need more info please email me and i can send a screen capture.

    Thanks – Patrick

    Like

  195. Hi sir ,
    i have one doubt About SqlServer Convertion
    my code is below

    In SQl stored procedure
    Create Procedure OnlineExam_QidsFromQtype
    @QtypeID as int,
    @QIDS as nvarchar(100)
    as
    begin
    select * From Question where QtypeID=@QtypeID and QuestionID in(@QIDS)

    end

    In Table QuestionID Coloumn is int datatype

    what can i do sir,pls reply quick

    Like

  196. Hi sir ,
    i have one doubt About SqlServer Convertion
    my code is below

    In SQl stored procedure
    Create Procedure OnlineExam_QidsFromQtype
    @QtypeID as int,
    @QIDS as nvarchar(100)
    as
    begin
    select * From Question where QtypeID=@QtypeID and QuestionID in(@QIDS)

    end

    In Table QuestionID Coloumn is int datatype

    what can i do sir,pls reply quick

    Advance Thanks

    Like

  197. Hi Pinal,

    I have a desktop application. While installing my client machine, i am running my SQL Script through the installation. I need to add security to the database. I need to use SQL Server authentication, but i want to block all the other users to access my database and they should not able to change through any system stored procedure like sp_changeobjectowner, sp_change owner.

    Please reply me.

    Thanks – S.Devarajan.

    Like

  198. Hello Devarajan,

    Even Windows Authentication is more secure than Mixed mode Authentication. But If you want to use only the SQL Server authentication then delete all windows login including BUILTIN\Administrators.
    For more details on how to control windows logins please check the below article

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

    This way you will allow only to the users that have a valid SQL login and password. Now you will have to control authorization of each sql login. To a login allow only those permission that are needed.
    If you want to check what permission are granted on each object to all logins then use the below script:

    select USER_NAME(p.grantee_principal_id) AS principal_name,
    dp.type_desc AS principal_type_desc,
    p.class_desc,
    OBJECT_NAME(p.major_id) AS object_name,
    p.permission_name,
    p.state_desc AS permission_state_desc
    from sys.database_permissions p
    inner JOIN sys.database_principals dp
    on p.grantee_principal_id = dp.principal_id

    Please let me know if you have any more query/doubts.

    Kind Regards,
    Pinal Dave

    Like

  199. Hi Pinal,
    I am very happy to see your reply.

    I gone thru the technique what you forward me. But it mainly focused on creating service and agent. I can’t able do from script code those operation. Bcz i am running my SQL script from my setup file.

    another way and your suggesition,
    But now i have blocked my Sps, UDFs and Views. Using your blog (http://blog.sqlauthority.com/2007/07/01/sql-server-explanation-of-with-encryption-clause-for-stored-procedure-and-user-defined-functions/)
    still pending is table structure. I don’t want, my client to open my table structure. Is this possible i can encrypt the table structure(not data), i can write a script for that?

    Thanks and Helpful your pervious reply.

    S.Devarajan.

    Like

  200. Hi pinal,
    I have query where I need to display Enddate which is 18 yrs more than of Start date in my Reports.
    I have 3 columns say QEDate,QEStartDate & QEEndDate.
    first two column display from my form and I want to set the condition for Enddate which display 18 yrs more than of start date.
    please help me here

    Like

  201. Hi Pinal,

    I found the following paragraph in your interview Q&A and I thought you should correct the typo in the line “HAVING can be used only with the SELECT statement”. I am guessing you meant “WHERE can be used only with the SELECT statement”. Over all Good work. Thanks

    What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
    They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. (Read More Here)

    Like

    • Hello Dani,

      Thank you for your nice feedback on Interview Q&A. But there is no typo in the line “HAVING can be used only with the SELECT statement”.
      HAVING clause is used to apply criteria on aggregated values. We can not use aggregate fuctions or grouping in DELETE and UPDATE statements. Thats why HAVING clause can not be used in DELETE and UPDATE statements.

      Please let me know if you need more details.

      Kind Regards,
      Pinal Dave

      Like

  202. Hi Pinal.
    I am new in ur useful web site.
    I’m trying to download SQL Server Cheat Sheet,but it has an error.colud u pls email me.???
    thank you.

    Like

  203. Dear Pinal,

    Name of Table : poa_detail
    rows : 4405
    Reserved : 49640 KB
    Data : 7120 KB
    Index Size : 320 KB
    Unused : 42200 KB
    This is output of Sp_spaceused poa_detail
    I am working for performance tunning work on the table.After rebuilding,index defrag and update stat.The unused space for the table is not reducing.I want to reduce the unused space in the server.I am thinking that due to this reasonperformance slow? .its true.Please tell me how to reduce the unused space.Please tell me the tech.

    Please clear me.

    Like

    • If this table has clustered index then defrage the clustered index. If not then you can remove the high unused space by creating one temporarily.
      Unused space also include the free space specified by fillfactor and that can not be removed by defragmenting the table or index.

      Kind Regards,
      Pinal Dave

      Like

      • Hi Pinal,

        When we creating a cluster index and drop that index significantly reduced the unused space .After doing this similar to all tables performance will increase or decrease.(Mostly In our system use of only Non cluster index ).

        Please suggest me,

        Like

  204. Greetings Pinal.

    I have been reading this blog for a long time and it has really been very helpful to me.

    I am having an issue with the rsconfig utility.

    I am trying to execute the following command,

    rsconfig -c -s -i -d -a SQL -u sa -p sage

    But really I am not very sure about what needs to be there in . I put my machine name, since SQL Server is in my machine.

    It says “The Command Completed Successfully”., but throws an error saying,

    The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

    I have gone through the article regarding the error in your blog and found everything configured in my machine.

    I doubt there is something wrong with the .

    Please provide me some help on this.

    Regards,
    Kumarjit

    Like

    • My rsconfig command did not show up correctly. It is like

      rsconfig -c -s [server name] -i [instance name] -d [database name] -a SQL -u sa -p sage

      Like

  205. Hi,

    I have a conflict problem in my stored procedure Union operation.

    While i ran the stored procedure i got an error

    “Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the UNION operation”

    help me to sortout this problem

    Like

    • Anuthuvan,

      Collation conflict occures when we try to compare, join or union two column of different collation.
      For example

      SELECT col1, col2
      FROM Table1.col1 = Table2.col4
      UNION
      SELECT col5, col6
      FROM Table3

      Here make sure that collation fo following column should match
      col1 = col5
      col1 = col4
      col2 = col6

      Kinid Regards,
      Pinal Dave

      Like

  206. Hi Friends

    Here i have a doubt regarding the comparison of DATETIME fields in SQL Queries. Suppose i need to fetch certain list of data from a table by inputting a datetime parameter. Then i need to write a condition in the where clause.

    For ex

    SELECT [UserID] FROM [Users] WHERE [DateOfBirth] > @prmDate

    Here it seems simple but i felt some issues and im just trying to achieve a good query which can resolve all my queries or rather a better approach.

    If the inputting date is in “DD/MM/YYYY” format and the date is in Database is in “MM/DD/YYYY” or vice versa
    If the input date is having time part also then i will not get a complete result out. i.e. my input date is 01/01/2009 10.20.00 and the date in my Database is 01/01/2009 00.00.00 or something i will not get the result. But i should get the result out.
    Then i tried the code CONVERT(VARCHAR, ‘dd/MM/yyyy’,101) approach again i felt this is having some issue.
    For example

    Compare CONVERT(VARCHAR,’02/01/2009′,101) > CONVERT(VARCHAR,’01/01/2010′,101) , here i should get a false instead im getting true as output.

    Can you guys please advise me regarding this.

    Thank You

    Like

  207. Hi Pinal,

    1)I want remove the fragmentation in table.
    Not in index. Tell me the idea for removing or defragmenting the table.

    2) I want to know Table fragmentation will decrease the performance of the system.

    Dont suggest DBCC INDEXDEFRG,DBCC DBREINDEX DBCC UPDATEUSAGE .Beause all i tried it is vain

    please give me the brief description above

    Thank You

    Like

    • Hello Asin,

      If the table has clustered index then defragmentation of clustered index itself is defragmentation of table otherwise shrinkfile or shrinkdb degrag the table.

      Obviously defragmentation decrease the performance because it results in more numberof data page load in memory and more page switching while retreiving the required data.

      Kind Regards,
      Pinal Dave

      Like

  208. Pinal,

    We’re curious if you may be able to help us with an issue we’re seeing with one of our customer’s SQL/Access Db.

    We are an IT company specializing in Network Support, Help Desk etc. We have a customer who has built a SQL database with Access and they are having OBDC time out errors between the DB and the front end application. This application is a custom built solution similar to Master Builder. The customer elected to build their own database and application due to their belief that Master builder could not do what they needed to adequately support their business.

    They earlier believed this was related to network configuration which would have pointed back in our direction, however over the past few days have come to realize they have problems between the front and back end of their solution. They have agreed to have our team have an outside pair of eyes on the situation to see if there is anything glaringly obvious that is being overlooked. The db resides on one server and the application resides on another server. The two servers are within two feet of each other and we are experiencing no significant network delays that would lead to slowness or time outs.

    We would like to forward the database and have you analyze it for any glaring issues. We’re not programmers but I know in my past experience if you try to cram too many if statements and application deliverables into one solution, a lot of times the db and application react in the manner the customer is observing today.

    We would like to know your rates and your availability to work this over the next couple of weeks so that we can provide our customer with unbiased expertise on what we believe to be the main cause of their issue. Thank you for your time and we look forward to hearing from you in the near future.

    Like

    • Hello Bindi,

      To get the all unique key columns of a table, use the below query:

      select c.COLUMN_NAME
      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
      where pk.TABLE_NAME =
      and CONSTRAINT_TYPE = ‘UNIQUE’
      and c.TABLE_NAME = pk.TABLE_NAME
      and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
      Kind Regards,
      Pinal Dave

      Like

    • This should help.

      SELECT
      Con.Constraint_Name,
      Con.Table_Name,
      Col.Column_Name
      FROM
      INFORMATION_SCHEMA.TABLE_CONSTRAINTS Con,
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE Col
      WHERE
      Con.Constraint_Type = ‘UNIQUE’
      AND Col.Table_Name = Con.Table_Name
      AND Col.Constraint_Name = Col.COnstraint_Name;

      Like

  209. Hi Glinn,

    The ODBC timeout while connecting from MS Access to SQL Server causes to suspects various aspects. Some of them are as followings:

    ODBC driver you are using to connect to SQL Server.
    Connection timeout setting in Access db.
    U.ing Linked tables or pass-through queries
    Execution time and amount of data retrieved by query in SQL Server.
    Data type of columns in source table in SQL.
    Quering to SQL tables or views

    These all aspects need analysis and only after that we can findout any resolution.

    Kind Regards,
    Pinal Dave

    Like

  210. Hi Dave,

    I have some basic idea on Sql Server 2005.

    Can you suggest me some books or tutorials where I can learn more especially on stored procedures in sql server 2005

    Thanks
    Meghana

    Like

  211. Hi,

    I had to disable and enable an step of a DTS package in SQL Server 2005 several times. I did it 5 or 6 times in order to execute the DTS and verify the notification is working.

    The task was a “rebuild index task” that some tables were selected to be reindexed.

    The other night the job failed and I discovered there is no table selection in the Rebuild index task while it was already there.

    Do you think by disabling/enabling a task, its content could be lost?

    Thanks!

    Like

  212. Hi Pinal,

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

    Like

  213. Hello Sir,

    I have question regarding what strategy should be followed for auditing.We are working on windows Application. We have various screen mode like update,insert, delete etc.. We have two approaches:

    1) Our DBA prefers that auditing should be done based on screen mode. He prefers that all the information should be captured in audit tables present on screen once we update or delete any thing on screen.

    2) My fellow developers on other hand prefer that only that particular information should be send to audit table which is added or deleted.For example (If a row in a grid is deleted and another row is updated then separate audit_ids going for both rows a with respective delete and update operations)
    The first approach is simple but tracking is difficult, Second is complex but more accurate and easier to track changes
    Kindly please reply.

    Thanks & Regards
    Ranjoyt

    Like

  214. Thank you very much Mr Brian.

    In our case there is a audit table corresponding to every table. for example: If there for say ABC table we have audit_ABC table

    The goal of audit table is that every record that is modified or deleted should be inserted in the audit table to keep track or history of all the data.

    I guess now you should be able to suggest.

    Thanks & Regards,
    Ranjoyt Singh

    Like

    • Other than SQL Server built-in Auditing that Marko pointed out, to track ever change means to track every record. Like option 1, but better in the TABLE itself. Having a separate TABLE wastes space, unless the COLUMNs need to be left out of the main TABLE. Retrieving the data is easy. If it is hard, write a VIEW that does it for you.

      The second option seems to me like it might miss details. That is just a feeling though.

      In either case, tracking DELETEs is harder. If all the data need to be kept as a history, a status COLUMN (with a lookup TABLE) could be added to each TABLE and instead of DELETEing a record, the status can be changed to “Inactive” or the like. If only a record of the DELETEion is required, but not the actual data, an audit TABLE like the one the DBA suggested seems best.

      These comments are without knowing the details of you DB. The details can always change what is best. :)

      Like

  215. Dear dave, I hope you can help me with this error, please…

    I’m getting an error message on SQL Server 2008:
    Error al recuperar datos para esta solicitud. (Microsoft.SqlServer.Management.Sdk.Sfc)

    Excepción al ejecutar una instrucción o un proceso por lotes Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)

    No se puede abrir la base de datos ‘msdb’. Tiene la marca SUSPECT para recuperación. Consulte el registro de errores de SQL Server si desea más información. (Microsoft SQL Server, Error: 926)

    Many Thanks

    Like

  216. Hi Pinal

    I need to execute a procedure on multiple servers. Could you please suggest me how to execute a stored procedure on multiple servers simultanously. How to create batch file for that.

    Regards
    Vaibhav

    Like

  217. HI Pinale sir,
    One of my DBA sir asked a qsn but ican not find anywer…plz help me on this..

    Please refer below error while running the DTS package as a job . Please note that Other DTS jobs are running successfully.

    Please provide your soultion …. I have got couple of workaround but not yet applied on the server.

    Message
    Executed as user: CORP\ftrcom_sqladmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:00:00 PM Error: 2011-09-12 21:00:03.19 Code: 0xC0012024 Source: Delete Archive Files Description: The task “Delete Archive Files” cannot run on this edition of Integration Services. It requires a higher level edition. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:00:00 PM Finished: 9:00:03 PM Elapsed: 2.468 seconds. The package execution failed. The step failed.

    Like

  218. Hi,
    I have a table that has a field called ‘group_quartile’ which uses the sql ntile() function to calculate which quartile does each customer lie in on the basis of their activity scores. However using this ntile(0 function i find there are some customers which have same activity scores but are in different quartiles. I need to modify the ‘group-quartile’ column to make all customers with the same activity scores lie in the same group_quartile.

    A view of the table values :(apologies: the column headers are not aligned with the data rows)

    Customer_id Product Activity_Score Group_Quartile
    CH002 T 2328 1
    CR001 T 268 1
    CN001 T 178 1
    MS006 T 45 2
    ST001 T 21 2
    CH001 T 0 2
    CX001 T 0 3
    KH001 T 0 3
    MH002 T 0 4
    SJ003 T 0 4
    CN001 S 439 1
    AC002 S 177 1
    SC001 S 91 2
    PV001 S 69 3
    TS001 S 0 4

    I used CTE expression but it didnot work.
    My query only updates(from the above example) :
    CX001 T 0 3 to
    CX001 T 0 2

    So only the first repeating activity score is checked and that row’s group_quartile is updated to 2.
    I need to update all the below rows as well.
    CX001 T 0 3
    KH001 T 0 3
    MH002 T 0 4
    SJ003 T 0 4

    Can anyone help with what should be the t-sql statemnt?
    Cheers!

    Like

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s