Contact Me – Archive 8

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

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

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

Copyright violation and Reproduction of blog:

SQLAuthority.com is trademark of Pinal Dave. Exact work “SQLAuthority”  or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave. If any article published on this blog violates copyright please contact me, I will remove it right away. Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, which ever is maximum in event of linking to blog (Link is must).

Search SQLAuthority.com

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

Community Rules

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

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

About these ads

271 thoughts on “Contact Me – Archive 8

  1. sir i want to know that is there any solution to get records from two different databases on server with different user name and passwords in one query…
    please help me out….I have a very bulky database and now i want to split it in two databases….

    Like

  2. Scenario:We have a bulky Database on the server, so we decided to divide the database acc. to branches on their local servers.And Our Main Database will be online.which will be synchronized with local DBs..on weekly or monthly basis..So for this problem m looking for Data Synchronization Solution which can be used in our asp.net MVC Application with SQL Server Express Edition.

    I would like to know the method of data synchronization in SQL Server2005 (Express Edition).I hav tried data synchronization using stored proc…bt it has certain limitations…so m luking for sum flawless solution.How should I proceed for Data synchronization in SQL Express(2005) edition.Will Microsoft Data synchronization service or SQL Azure do ny help to me.

    Like

  3. hi,

    I have a three tables where the information is
    countrycode,countryname
    1,India

    Another table information is
    StateCode,StateName,CountryCode
    1,Tamilnadu,1
    2,Delhi,1

    then another table information is
    CityCode,CityName,StateCode,CountryCode
    1,Chennai,1,1
    2,Madurai,1,1
    3,New Delhi,2,1

    I want the query result as
    CountryName,no of states,no of cities
    India,2,3

    Please let me know as early as possible the tell your suggestion or query for this.

    Thanks
    Samy

    Like

    • Hi,

      I have try to get result from this query,
      If any other better query is there, give me ur suggestions,

      Select A.CountryName,
      Count(Distinct B.StateName) AS [No. Of States],
      Count(C.CityName) AS [No. Of Cities]
      From CountryMaster AS A
      INNER JOIN StateMaster AS B
      INNER JOIN CityMaster AS C
      ON C.StateCode=B.StateCode
      ON A.CountryCode=B.CountryCode
      Group By A.CountryName

      Thanks
      Samy

      Like

      • Hi Samy,

        you can use below query also.

        select
        CountryMaster.countryname,
        (select count(StateName) from StateMaster where StateMaster.countrycode = CountryMaster.countrycode)as ‘no of states’,
        (select count(CityName) from CityMaster where CityMaster.countrycode = CountryMaster.countrycode)as ‘no of cities’
        from CountryMaster

        Like

        • From execution plan from MSSQL

          (91% of 100%)

          1.Select A.CountryName,
          Count(Distinct B.StateName) AS [No. Of States],
          Count(C.CityName) AS [No. Of Cities]
          From CountryMaster AS A
          INNER JOIN StateMaster AS B
          INNER JOIN CityMaster AS C
          ON C.StateCode=B.StateCode
          ON A.CountryCode=B.CountryCode
          Group By A.CountryName

          (9% of 100%)

          2.select
          CountryMaster.countryname,
          (select count(StateName) from StateMaster where StateMaster.countrycode = CountryMaster.countrycode)as ‘no of states’,
          (select count(CityName) from CityMaster where CityMaster.countrycode = CountryMaster.countrycode)as ‘no of cities’
          from CountryMaster

          Thanks & Regards,
          Amit Makwana

          Like

    • select TCO.CountryNAme,(SELECT COUNT(TS.StateID) from tblState TS WHERE TS.CountryID = TCO.CountryID) AS State ,(SELECT COUNT(TC.CityID) from tblCity TC INNER JOIN tblState TS1 ON TS1.StateID = TC.StateID ) AS CityCount FROM tblCountry TCO

      Like

  4. Dave i have a bit of a problem, hopefully you, or someone on your blog can help me.

    here is my delema:
    I have windows server 2008 r2 installed on VMWare fusion 3. And on windows server i have SQL server 2008 installed on it, but when i try to install a failover cluster on it I had no success. So i checked my server manager and noticed that i only have 41 features instead of 42. I downloaded a hardware assisted virtualization patches. Then, i tried the cluster installation again, yet another failure. I updated the R2, I tried to install the “Hyper-V” role, I received a message that reads; the processor on my machine is not compatible. what can i do to be able the install a failover cluster on my sql server 2008……

    Like

  5. I have searched the blogs and Internet but could not find the answer for below question. Can you please let me know whether Microsoft support this or not. If yes, can you please send me an article for achieving this? I hope I am not wasting your time.

    Question: – How to implement SQL SERVER 2008 R2 Anonymous access?

    References: – http://connect.microsoft.com/SQLServer/feedback/details/565010/anonymous-access-with-ssrs-2008-r2-not-working

    Like

  6. Hi All,

    Usually how much data does DMV’s Hold and how often the data from DMV’s gets cleared. I was under the impression when ever the SQL Server starts or machine (server) starts the DMV’s holding the information will get cleared, is this right if I’m not. Can any clear me with this issue.

    Thanks,

    Ravi.

    Like

  7. Hi Pinal,

    Can I schedule a job in SQL to run a Stored procedure which calls a batch file, when there is no users logged into the server PC?

    I know that we cannot schedule to run a batch file through Scheduled tasks when there is no users logged on to the Server PC. Is there any work around. Please help.

    Thank you in advance.

    Raj.

    Like

  8. Hi Pinal,

    My application consist of region,compaign(IBM,Microsoft,Dell),Line Of business(lob),weekly schedules of employees,Employee Data.User(Company Authority
    uploads weekly schedules of the employee by selecting region,Compaign and LOB in dropdownlist.

    Should i need to use master table for this,if yes can i use same master for the all the compaigns.
    One authority may upload dell copmaign data with 500 employees schedule,another authority may upload microsft compaign schedule.

    Give me table struture for this.

    EmpNo,EmpName,Monday(date and tiimings in and out)…sunday,Region,Compaign and LoB

    Like

  9. Hi,

    I am posting my questions where i wiil get my reply in mail or in this blog itslf.Last week i posted my question.

    should i need to create master table for scheduling agents for different compaigns(EarthLink,IBM etct) from different regions with more than 500 empoyees in each compaign.

    If i need to create master table then can i use same master table for all the compaigns.

    Like

  10. Hi,

    I’m pretty much new in the xml to sql implementation, I need to import an xml file into sql database. May you please advise as to which is the best way to implement this.

    My xml data adheres to the xsd file created from the database table.
    e.g

    PF
    414513
    Clermont Tea Room Cash Sale
    CLERMONT TOWNSHIP|DURBAN|||000
    test@test.com

    None
    11ad4b3d-d41d-4075-af8a-6de043e4572e
    11.990
    1.140
    COD
    Add

    Like

  11. How to use multiple update with WITH SQL statement. I’m getting error message invalid object. ?

    WITH a_BO(TEST_CUST_NUM, TEST_CUST_TERM_DATE, TEST_B0_NUM) as
    (
    SELECT a.CUST_NUM, a.CUST_TERM_DATE, b.B0_NUM
    FROM PS_TEST a
    INNER JOIN PS_VALUE v ON v.UDF_VALUE = a.CUST_NUM
    INNER JOIN PS_PERSON p on p.PERSONNUM = v.PERSONNUM
    INNER JOIN PS_SI b ON b.B0_NUM = p.B0_NUM
    WHERE CUST_TERM_DATE <= CONVERT(VARCHAR(10), GETDATE(), 101)
    AND CUST_TERM_DATE ’00/00/00′ a

    )

    UPDATE o
    SET BO_CM_END_DT = x.TEST_CUST_TERM_DATE
    FROM PS_CX o
    JOIN a_BO AS x
    on o.B0_NUM = x.TEST_B0_NUM

    UPDATE r
    SET ROLE_END_DT = x.TEST_CUST_TERM_DATE
    FROM PS_X_ROLE r
    JOIN a_BO as x
    on r.B0_NUM = x.TEST_B0_NUM;

    (4 row(s) affected)
    Msg 208, Level 16, State 1, Line 20
    Invalid object name ‘a_BO’.

    Like

  12. hi sir

    i have a table named CompanyMaster have data like

    ComId——————-ComName
    1————————–Infosys
    2————————–wipro
    3————————–C
    4————————–Dfgf
    5————————–Eghtghg
    6————————–tyhg
    7————————–hgfg
    8————————–fytyr
    9————————–gfdgtyuy

    if i deleted 2 records from middle then it shows like

    ComId——————-ComName
    1————————–Infosys
    2————————–wipro
    3————————–C
    7————————–hgfg
    8————————–fytyr
    9————————–gfdgtyuy

    here You see that the ComId is not serialize, means i want like this in each delete query

    ComId——————-ComName
    1————————–Infosys
    2————————–wipro
    3————————–C
    4————————–hgfg
    5————————–fytyr
    6————————–gfdgtyuy

    Plz suggest me, how can i achieve it
    thanks

    Like

  13. Pinal,

    I am working on project where I need help from you. I have created a online banking software which logs transactions. It works fine as online version. One of my customer wants offline version of that. So when there is no internet the will use software from local intranet website where code and database is hosted on their local network machine. They should able to perform all operations in offline database. Once internet is available they should able to synchronize their offline database with online database. While some user working on offline system other members in different region can use online system and add their transaction. So when there is synchronization only those data which are changed in offline database should affect in online database instead of over writing whole.

    Quick reply will be highly appreciated.

    Like

  14. table name is::CandidateTemp

    id FieldName Value

    1 email aaa
    1 skill c
    1 name AAAAA
    2 email aaa
    2 skill c,java
    2 name AAAAA
    5 email aaa
    5 skill c
    5 name GGGG
    9 email iii
    9 skill c,vb,java
    9 name AAAA

    and i want this format

    id email skill name

    1 aaa c AAAAA
    2 aaa c,java AAAAA
    5 aaa c GGGG
    9 iii c,vb,java AAAA

    Like

  15. Hi,
    I’m trying to update two columns types from part of a large procedure as follows:

    alter table dbo.fakturapos
    alter column rabattproz decimal(15,2) null

    alter table dbo.fakturapos
    alter column nachlassproz decimal(15,2) null

    I get the following meldung:

    Meldung 5074, Ebene 16, Status 1, Zeile 1
    Das Objekt-Objekt ‘DF__fakturapo__rabat__024846FC’ ist vom Spalte-Objekt ‘rabattproz’ abhängig.
    Meldung 4922, Ebene 16, Status 9, Zeile 1
    Fehler bei ALTER TABLE ALTER COLUMN rabattproz, da mindestens ein Objekt auf diese Spalte zugreift.

    The alter statements do work on their own but not in the procedure.

    Could you please explain why this is.

    Thank’s and kind regards

    Wayne

    Like

  16. Hi,

    I need help to solve the following problem:

    I have a sql server with multiple databases, all databases have the same tables and every table has the same structure.

    Example
    Database – 10CT01
    Table – CCON
    Field – CCONTA

    Database – 10CT02
    Table – CCON
    Field – CCONTA

    I need to make an update to the field CCONTA in all tables CCON in all databases begin by 10CT.

    Until now I had 10 databases and made the correction one at a time, but now I have 300 databases to correct.

    This is the statement that I use now:

    USE [10CT01]
    GO
    UPDATE CCON
    SET CCONTA = ‘S’
    WHERE CCOD IN (‘621′, ‘622’)

    I am a self didactic in SQL, everything I know I learned to read books and searching in the web.

    Can you please help me find the best way I can solve this problem?

    Thanks

    Like

  17. Sir,
    Please tell me the correct syntax for create a PROCEDURE because when i read the book, they all make this part more complex to understand .

    Regards,

    Like

  18. Hi,

    Am getting this error when am connecting to my trying to connect my two servers internally which are having Microsoft SQL Server Standard Edition (64-bit). Am doing this for mirroring.

    I have two more server with same settings and same SQL version where am able to connect the server vice versa using the computer name as the SERVER NAME. Also here the mirroring is working perfect.

    Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (.Net SqlClient Data Provider)

    please help me.

    Thanks in advance. as i know You have always have a solution to any SQL query…:-)

    Like

  19. Hello,

    I am very new to sql server management studio 2008/visual studio 2008.

    I develop at work but use perl, jsp jsp etc and have made connection strings but setting the whole framework and getting the connection has been a little rocky.

    I have the set up below

    Microsoft SQL Server Enterprise Edition
    L-06914 server name and computer name
    Windows Authentication

    getting error 26 cannot locate instance name and can’t seem to solve this. Any help would be great. Thanks,

    Like

  20. Hi,
    I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.

    Like

    • select col from
      (
      select max(col1) as col1, ‘col1′ as col from table
      union all
      select max(col1) as col1, ‘col1′ as col from table
      union all
      .
      .
      .
      select max(col20) as col20, ‘col20′ as col from table
      ) as t
      where col is not null

      Like

  21. Hi Pinal,

    I have table with data.

    EmpNo Date TimeIn TimeOut TotalHours Matchind Day
    1 8/1/2010 10:00 6:00 9 hours 10:00
    2 8/2/2010 11:00 7:00 9 hours 11:00
    3 8/3/2010 Off Off Off Off
    Similarly it will have 4 weeks data and for all the remaing employees.
    My question-

    Find the percentage of employees who are getting off in same day across 4 weeks,3 weeks and 2 weeks.

    There are 100 employees
    10 employees are getting off in sunday for 4 weeks then percentage is 10.
    SImilraly another 20 employees are getting off on saturday Now total percentage is 20+10=30.Off can be any day but we have to check whether that employee is getting off in same day for last 4,3,2 week.Similarly how many employees are getting off like that in percentage..

    Please send me sample query on how to acheive it.It is urgent.

    Like

  22. Thanq Raj,Madhivanan

    i hv gvn d same query before bt its nt wrkg… bt der is no error.

    insert into myaccount values(username='”+textbox1.text+”‘, address = ‘”+textbox2.text+”‘,mobile='”+textbox3.text+'”,email='”+textbox4.text+”‘,………….)select username, address, mobile,…..) from register where country=’india’

    this is my query.
    i did’nt fill the values fully……

    Like

  23. Hi Madhivanan,

    Can I schedule a job in SQL to run a Stored procedure which calls a batch file, when there is no users logged into the server PC?

    I know that we cannot schedule to run a batch file through Scheduled tasks when there is no users logged on to the Server PC. Is there any work around? Please help.

    Thank you in advance.

    Raj.

    Like

  24. Hi Pinal,

    I was going through your blog and I am highly impressed about the amount of effort you have put in and the dedication you have towards your blow. You can add up me in your fan list now.

    I am planning to go for SQL developer and SQL administrator certification. I have been learning all these through Video tutorials till now, but to be honest I am still very poor on majorly all the main skill set. So my request is to guide me and help me with your experience as in where to start and what to follow.

    I am in desperate need of help.

    Thanking you in advance.

    Regards,
    Premansh

    Like

  25. Hi ,

    I have experience on sql server 2000 and 2005 of 2 to 3 years

    But I have practicle experience.

    I mostly going to your site for many sql problem

    Sir Is it possible to have a training under you I am only becom graduate. I am ready to pay

    Currently I am making project for steel Utensils Manufacturing company which is producing from iron ore to steel untesils

    Please Guide me to make my future in It field

    I have complete many small projects.

    best regards

    ajay garg

    Like

  26. Hi all,
    I have a question and want a support
    We can create a Non-Clustered Index which contain a field which is key in Clustered Index
    Eg: I create a table has 3 column
    Create table A
    (
    ReportDate
    MerchantNumber
    MerchantName
    SaleCount
    )
    Then I create a Clustered Index P on ReportDate column
    So, Can I create a Non-Clustered Index A on ReportDate,MerchantNUmber?
    Thanks,

    Like

  27. Hi,

    I have to design a database for storing 3 different types of transactions.

    I have thought of two different approaches

    Style 1 : Make 3 master table for 3 different transaction type
    store data in 3 different transaction table related by their respective ids

    Ex. master 1 : (id, commodity)
    (1, pen) (2, pencil) (3,rubber)

    master 2 : (id, commodity)
    (1, monitor) (2, keybord) (3,ram)

    master 3 : (id,commodity)
    (1,nail) (2,ply) (3,adhesive)

    transaction 1(id, customerid, masterid, value)
    (1, 23, 2,1000) (2,23,3,500),(3,24,1,1000)

    transaction 2(id, customerid, masterid, value)
    (1, 23, 2,2000) (2,24,3,500),(3,24,1,1000)

    transaction 3(id, customerid, masterid, value)
    (1, 23, 1,2000) (2,24,3,500),(3,24,1,1000)

    no. of records expected in each master : 20
    no. of records expected in each transaction : 18 lakhs

    *******************

    Style 2 : Make 1 master table for store different transaction type with their ids for commodity type also and store all transactions in one table

    master 1 (id, commoidtytype,commodity)
    (1, S,pen) (2,S,pencil) (3,S,rubber)
    (4,C, monitor) (5,C, keybord) (6,C,ram)
    (7,H,nail) (8,H,ply) (9,H,adhesive)

    transaction 1(id, customerid, masterid, value)
    (1, 23, 2,2000) (2,24,3,500),(3,24,1,1000)

    no. of records expectein in master : 60
    no. of records expected in transation : 60 lakhs

    which is better approach in terms of :

    1) speed of operation at the time of page load….

    any help is highly appreciated

    Thanks in advance

    Like

  28. Hello everybody,

    I have a question and want you guys to help me find the solution.

    I want to find the last two records of a table. I know that using an ID column we can do it. But is it possible to display the last two records without using the ID column.

    For example, I have the following table TEST and it has the columns NAME and ADDRESS.

    The columns contains the following records :

    name address
    xyz abc
    bbb bcd
    yyy cde
    eee efg
    fff fgh
    yzz ghi
    hhh hij
    aaa ijk

    Is it possible to write a query which will display the last two records of the above table, i.e., I want to display :

    hhh hij
    aaa ijk

    I tried by using the following query :

    select top 2 * from em order by ename desc

    but it displays the following results :
    yzz ghi
    yyy cde

    Please help me with this.

    Regards,
    Rituraj

    Like

    • A change in last few lines :

      I tried by using the following query :

      select top 2 * from TEST order by NAME desc

      but it displays the following results :
      yzz ghi
      yyy cde

      Please help me with this.

      Regards,
      Rituraj

      Like

      • Hi Rituraj,
        This is impossible if you use NAME or ADDRESS to get the last 2 records.
        I suggest you should use a temp table. The table consists of a field ID is IDENTITY. Then you insert data into the table and use this query
        SELECT TOP 2 * FROm #tmp ORDER BY ID Desc

        Another way, you can use CURSOR to seek to the last 2 records. However, it’s not good if your table contains large data.
        Hope that can help y.
        Regards,

        Like

    • Without any unique it is not possible. Do you have any unique key? If you use version 2005, you can use row_number() function

      select top 2 * from
      (
      select *,row_number() over (order by (select 0)) as sno from table
      ) as t
      order by sno desc

      Like

  29. hi i am unable to retreive records form a table based on the date specified by the user… there are 3 dropdown menus from which i get the day.month and year from the user .. if i write the select query then it gives conversion failed while converting datetime from character string exception…

    i tried mm/dd/yyyy , dd/mm/yyyy , yyyy/mm/dd and date .parse function but still couldn’t retreive the records … but still i get the exception..
    hope u can help me ..
    regards..

    Like

  30. I have an application that requires a lot of calculation, there are a lot of clients that uses that application having separated DB’s. Now I am going to develop another web-application that will get the records of all those databases and on the basis of filter will display the results. I want to ask you a question that

    1. Should I change the structure of the application in which all of my clients share a same database and from where I could get all the records I needed and perform calculation on them?

    2. Would I keep the all databases separate (as it is condition) and develop a service that will fetch the records from the entire database into one from where I would perform my calculation and display the data.
    Do also tell me if there is some other solution to this application architecuture.

    Like

  31. hey Pinal Dave,
    I have some questions related to sql azure i wish if you can help me with these.

    the questions are:

    • • Is remote access to an SQL Azure™ database supported? If yes, is there an impact on performance?

    Can two separate SQL Azure™ databases access each others’ data using the “USE” statement?

    Like

  32. Hi Pinal,

    I’m working as Microsoft SQL server DBAdministrator for 3 years in SQL 2005 as well as in SQL 2008. Hence i would like to do certification in one of these (i prefer in 2008 ..since 2005 is little older). Can you please let me know what all i need to do and any material for preparation?

    I heard that 70-431 is enough for DBA .. is this true ? or let me know what all i need to do to complete this.

    Your quick response is appreciated.

    Thanks,

    Like

  33. I wanted to thank you for the useful articles in your site,
    Please help me solve deadlock in SQL server, I already posted it in different blogs but no answer yet

    the scenario:

    We have a database server running SQL server 2000 Win Server 2003 which manages couple of huge databases and hundreds of simultaneously connected clients to them. Number of transactions done by multiple large size applications on these databases are enormous and we did not have much problems for a long period with partially same data and transaction volume.
    Recently, server has encountered lots of blockings in its processes and finally deadlocks which made the server halt… the number of deadlocks is increasing day after day and server halts are becoming a serious headache: before we had once a month, now we have twice a day.
    We have set multiple trace flags on server to generate and capture logs, used profiler and installed advanced SQL analysis monitoring tools. Finally, we found out that deadlocks are mostly caused by similar simultaneous queries blocking each other’s in table access, but the queries and the tables are different for each deadlock and each query may come from a different application or client. Following is a typical blocking session that caused deadlock and server halt:

    – Blocking Process A ran at 9:36:11, Waiting time(ms): 469313, SQL Query: UPDATE TableX

    – Blocked Processes B ran at 1: 9:36:12, Waiting time (ms): 436094, SQL Query: INSERT INTO TableX

    The SELECT statements are locked and unfortunately we cannot change them by adding ‘nolock’ access modifier since they are all called from third party applications. The waiting times for most of blocked processes are above 400,000 mille seconds (more than 6 minutes!). Our servers are high performance, wealthy in CPU and Memory resources and the SQL has a huge memory allocated to itself that never reached to its limit! All we can do is to set changes directly on database, SQL Server settings, network and hardware.

    So far, we have come up with the following solutions (which are not the ultimate cure) and we still did not apply them since the database is huge in size and design detail and we better get sure before any action:
    1- reduce the LOCK_TIMEOUT in db that applications do not wait a long time
    2- Index all fields in blocking tables to shorten query times

    If you can suggest an avenue of search or an alternative to prevent or minimize this error it would be appreciated.

    Thanks, and thanks again for useful articles…

    A.Oveissian

    Like

  34. Hello Sir

    I have an urgent requriement to update my database to Sql Server 2005 and Sql Server 2008. So I am looking to have detailed notes for updating sql server 2000 to sql server 2005 and sql server 2005 to sql server 2008. Therefore I request you to mail me the steps to proceed.

    Thanks in Advance.

    V.Santosh Kumar.

    Like

  35. Parrallel demand for exclusive access to tables is causing this but we can not do anything for it. I’m looking for the reason why it started happening since 2 weeks ago only.

    Please let me know if I must check something else as well…

    Like

  36. Hi,

    i am facing a problem as following
    a table TB1 contain column

    AccountCode
    AS1
    AS10
    AS12
    AS13
    AS14
    AS15
    AS16
    AS17
    AS18
    AS19
    AS2
    AS20

    i want sort in following format

    AccountCode
    AS1
    AS2
    AS10
    AS11
    AS12
    AS13
    AS14
    AS15
    AS16
    AS17
    AS18
    AS19
    AS20

    can any one help me, given query in my mail ID
    [email address removed]

    Like

  37. Hello Sir

    I have an urgent requriement to update my database to Sql Server 2005 and Sql Server 2008. So I am looking to have detailed steps for updating sql server 2000 to sql server 2005 and sql server 2005 to sql server 2008. Therefore I request you to mail me the steps to proceed.

    Thanks in Advance.

    V.Santosh Kumar.

    Like

  38. Dear Sir,

    I have some problem while I am at last phase of my development. I have one module where I am backup and restore the database to network location. Here problem is that it is work successfully when the all PC’s are in the domain, but it is not work while I am doing same task at the PC which is not in the domain. Please give me some guidance to take backup and restore at without domain location….

    Thanks in advance,

    Ankit Shah

    Like

  39. Sir, Please help me with this, i have a table with the follwoing fields:

    Date
    Duration (in Months, eg 6, 12 or 18)
    StartDate
    EndDate

    from StartDate, I want to find out last date of the month as EndDate, which is determined by Duration.

    Regards.

    Like

  40. Hi Pinal,

    Here I am again.

    I just want to know stored procedures / functions or views to list users in a group, also how to add / drop user from a group.

    Thanks in adv. for your time and knowledge.

    Shoaib

    Like

  41. Hi Pinal
    I use query like this but i use 4 tables
    tblA have relation with tblB
    tblC have dont have relation with tblD

    i want to with INSERT INTO-SELECT statement
    copy data from tblC, tblD To tblA,tblB

    but i have error that say constarint error for parent and child rows…

    i want to know which row id has error or which data cant to transfer data from first 2 table to another 2 table

    INSERT INTO tbl1(a,b,c)

    SELECT a,b,c FROM tbl2

    and i have

    Like

  42. Hi Pinal,

    I have a problem regarding with SQL Server 2000 Standard sp4 on Windows server 2003. The application running above this is Eric (ERP System). There are a couple of instances wherein we got this error: db process is dead

    also when we try to use the query analyzer there are instances that we got disconnected. the connection will automatically be recovered after a few minutes.

    Can you help me solve this issue? we also had this problem with a SQL Server 2000 enterprise edition on windows server 2003….. Please help.. :D

    Like

  43. Hi Pinal,

    I am using MSSQL Server 2005, master db is corrupted, I am trying to rebuild the master db by using following command on command prompt :-

    D:\start /wait d:\sqlserver2005\setup.exe /qn instancename=mssql$Taint reinstall=sql_engine rebuilddatabase=1 sapwd=asd123

    when I press enter cursor start blinking and after few minutes it comes on next line without giving any notification.

    Then I try to start MSSQL server using following command on command prompt:-

    D:\net start mssql$Taint -m

    But it does not start, it throws an error :

    The sql server service could not be started.
    A service specific error occurred : 17113.

    I tried to search the error’s cause in events viewers of windows and searched in error log file of SQL but could not found appropriate solutions.

    Please suggest if I am doing something wrong.

    Thanks in advance. :)

    Like

  44. Hello sir,

    I am transfering my data from one db to another. While mapping process my query is slow.

    DECLARE @UpdateCursor as CURSOR
    declare @product_id as int
    DECLARE @product_name as NVARCHAR(MAX)

    SET @UpdateCursor = CURSOR FOR
    select product_id,product_name
    from kemchhorajkot.dbo.product_master

    OPEN @UpdateCursor
    FETCH NEXT
    FROM @UpdateCursor INTO @product_id, @product_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    –select @product_id,@product_name
    update CoreMaster
    set TempRCoreId=@product_id
    where CorePName=@product_name
    –UPDATE CompanyProduct
    –SET CProductID = (SELECT top 1 CProductID FROM CoreMaster WHERE CorePName=@CorePName ORDER BY CProductID)
    –WHERE CProductID IN (SELECT CProductID FROM
    –CoreMaster WHERE CorePName=@CorePName)
    FETCH NEXT
    FROM @UpdateCursor INTO @product_id,@product_name

    END

    CLOSE @UpdateCursor
    DEALLOCATE @UpdateCursor

    Plese help me in this query, its too much slow and time consuming.

    Thanks

    Like

  45. I am a very basic SQL user. I use SQL as a back-end database with a front end user interface using MS Access. I have screens where the user wishes to type in text with paragraphs – using carriage returns.

    The data fields I have for them to do this are set-up as varchar(max). If they type the text in Word and then paste it into the Access interface, the formatting of the paragraphs is preserved.

    However if they just type in text directly in the Access interface and try to use the carriage return to start a new paragraph, Access thinks the user is trying to advance to a new record.

    Is this a problem you can address and if so, what would be the cost? It would also be helpful to know what you think would be required to fix this problem.

    Like

  46. Sir,

    We have a unique proble with sqlserver 2005 over a cluster (Active/Active) on MS 2008 64bit server.

    It always happen without any exception that first time connection to SQL Server Manager fails and give following error: (2nd try is always successful)

    TITLE: Connect to Server
    ——————————

    Cannot connect to MSHSQLCLSTA\PR100.

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

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

    Kindly advise the resolution.
    SIH

    Like

  47. I have an application that requires a lot of calculation, there are a lot of clients that uses that application having separated DB’s. Now I am going to develop another web-application that will get the records of all those databases and on the basis of filter will display the results. I want to ask you a question that

    1. Should I change the structure of the application in which all of my clients share a same database and from where I could get all the records I needed and perform calculation on them?

    2. Would I keep the all databases separate (as it is condition) and develop a service that will fetch the records from the entire database into one from where I would perform my calculation and display the data.
    Do also tell me if there is some other solution to this application architecuture.

    Like

  48. I have transactional replication from one server to another and need to shut the servers down. How can I ensure that this goes smoothly?
    Do I have to stop the agents on the Publisher and Distributer first? Which order should I shut the servers down? Do you have a checklist of tasks?

    Like

  49. am trying to create a SQL temp table and populate it with data from a excel.csv file. The file is being uploaded from a ColdFuison page. I have set my SQL variable. I used your code with a couple of changes. Below is my code.
    CREATE TABLE #pmwtTable
    (circuit VARCHAR(50), ban VARCHAR(50), clli VARCHAR(50), uvClass int)
    BULK
    INSERT #pmwtTable
    FROM @file
    WITH
    (
    FIELDTERMINATOR = ‘,’,
    ROWTERMINATOR = ‘\n’
    )
    the SQL errors:
    Incorrect syntax near ‘@file’.
    Thank you for your help.

    Like

  50. Sir

    I have problem in connectivity to SQL Server. Please find the current structure.

    Domain Account: Windows 2003 server: IP 192.168.0.6
    SQL 2005 Server : IP 192.168.0.8
    Authentication is Windows authentication.

    When I set DNS 192.168.0.6 as primary and 192.168.0.8 as secondary, working fine.

    I have to set another IP for Internet Service Provider as primary DNS. When I change the order of windows domain account server to secondary, it triggers connection time out.

    I want to know whether IP address of SQL server installed machine must be primary DNS or not?

    Thanks & regards
    Abdurahman

    Like

  51. I’m using asp.net default membership, but now I realized that using uniqueidentifier as user id is useless and I want to convert uniqueidentifier column to int. But the column got many relations with other tables. I cant manually change the DB structure and update already exists row one by one. However, I thought to do that using queries, so any idea ?

    Like

  52. Hi Pinal
    I’m developing a ticketing system for 50 bus company. There are possibility of 1million ticket transaction in a month. My question is which are a preferred way to design database, either separate database for each company or manage all company in one database? FYI the company will use the system as a service not own the system

    Thanks

    Like

  53. Hi,

    I am getting an error

    Login failed for user(null)
    Reason:Not associated with a trusted SQL Server Connection.

    I am using SQL Server 2000

    I have 2 servers

    ServerA(DatabaseA)
    ServerB(DatabaseB)

    I try to do the following

    ServerA——>DatabaseA—–>Storedproc inserting a record in Database B of ServerB

    Please help.

    Like

  54. Hello Sir,
    i got this error when i tried to restore the database.
    help me to fix the problem.

    TITLE: Microsoft SQL Server Management Studio Express
    ——————————

    Restore failed for Server ‘CLIENT193\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

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

    System.Data.SqlClient.SqlError: Directory lookup for the file “C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\MedicalStore.mdf” failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

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

    OK
    ——————————

    Like

  55. CREATE TABLE [dbo].[SGA_INVOICED_SALES_Cumulative_YEAR](
    [0SHIP_TO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [0BILL_DATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [0SALES_ORG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Sales] [numeric](18, 2) NULL,
    [COST] [numeric](18, 2) NULL,
    [0DOC_TYPE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON cview_PartitionScheme(SELECT left([0bill_date],4));

    hi Pinal,
    is it possible to use left while creating a partioned table as above example

    Like

  56. Hi Pinal,

    We’re having connectivity issues with Active/Active SQL Server 2008 Enterprise Edition Cluster running on Windows Server 2008 R2 Enterprise Edition. Where we’re seeing disconnects prior to the connection timeout as well as issues similar to the issues with TCP Chimney Offload as described in http://blogs.msdn.com/b/psssql/archive/2010/02/21/tcp-offloading-again.aspx. Although their recommendations have not resolved our issues. Have you had any experience with this?

    Additionally, when we failed the instance having issues to the other node on the cluster the connectivity issues disappeared.

    Thanks in advance,

    Jeff

    Like

  57. Hi,
    I want to do insertion from another table but with some kind of datatype conversion like in my original table I took varchar and my old table has nvarchar or in my old table I took date and my old table has varchar.So I want to fire a query with datatype conversion?
    I appreciate if any onc help me out.Thanks

    Like

  58. Hi Pinal,

    My Question is I want to remove the old empty partitions and want to add new partitions with new range.

    My Main concern is how to findout empty partitions and merge them.

    Suggest me on this

    Like

  59. Here is my question. I’m writing a stored procedure in which I’m using a select statement which accepts two parameters: one which identifies the column of interest, strColumnName ,and the other which specifies the particular value, @NutriVal, of the rows I want returned to my data grid. The select statement I was attempting to use is as follows:
    set @SQL = ‘SELECT Food_Name, MType, ‘ + @strColumnName + ‘ FROM tblNutritionalData WHERE ‘ @strColumnName = @NutriVal
    Unfortunatly this statement isn’t working. What do I need to do to return the rows to my datagrid that contains the particular value, @NutriVal?

    Thanks in advanced,
    WBM, Jr.

    Like

  60. Hi,

    I saw your article regarding INSERT INTO statement in SQL. I have a similar problem with my project.
    I am trying to insert ID from table A to table B when record is inserted in table A.

    I am using the following stored procedure:

    PROCEDURE [dbo].[InfoInsert]
    — Add the parameters for the stored procedure here
    @UserId uniqueidentifier,
    @Title char(32),
    @FirstName char(32),
    @LastName char(32),
    @CompanyName char(32),
    @Email char(32),
    @DealerID int

    AS
    BEGIN
    INSERT INTO [tblDealers] ([UserId], [Title], [FirstName], [LastName], [CompanyName], [Email])
    VALUES (@UserId, @Title, @FirstName, @LastName, @CompanyName, @Email)

    INSERT INTO [Categories] ([DealerID]) SELECT tblDealers.DealerID FROM tblDealers WHERE tblDealers.DealerID = @DealerID
    END

    It is not inserting value in using WHERE tblDealers.DealerID = @DealerID statement. But if I change @DealerID to let’s say ‘547’ (which is a DealerID in tblDealers table) it executes above INSERT INTO statements separately and inserts two rows in Categories table. Whar I want to do is to create one row in Categories table with DealerID-CatCode in the same row.

    It is obviouse it can not retrive DealerID based on @DealerID but I don’t know how to resolve it.

    Thank you.

    Like

  61. i want to know that
    can i Create Live Test server which get Live Data from Live Database server
    i have one Live database server but when i want to use live database for testing i have to take backup it and restore on test server i do this process everyday to getting Live updated data
    is there any way that live Database automatically Transfer to my Test server and whatever data changed in Test server could not be replicate on the Live Database server

    Like

  62. Hi Pinal,

    I am using following pivot query getting result as 4 rows but i want it as 1 row.One more thing is if i pass dates as parameters to stored procedure i am getting error Incorrect syntax near ‘@date1′.This is my query and stored procdure
    Incorrect syntax near ‘@date1′

    Result should be like this

    HeadCount 8/1/2010 8/8/2010 8/15/2010 8/22/2010

    Forecasted 192 198 178 189

    But iam getting values daigonally as 4 rows

    My query

    create procedure Sample(@date1 datetime,@date2 datetime,@date3 datetime,@date4 datetime,@CampaignID int)
    as
    SELECT ‘Forecasted’ AS HeadCount,
    [@date1] as date1
    FROM
    (SELECT *
    FROM SAR_HeadCount) AS SourceTable
    PIVOT
    (
    SUM(HeadCount)
    FOR StartDate IN [@date1]
    ) AS PivotTable;

    create procedure Sample(@date1 datetime,@date2 datetime,@date3 datetime,@date4 datetime,@CampaignID int)
    as
    SELECT ‘Forecasted’ AS HeadCount,
    [@date1] as date1
    FROM
    (SELECT *
    FROM SAR_HeadCount) AS SourceTable
    PIVOT
    (
    SUM(HeadCount)
    FOR StartDate IN [@date1]
    ) AS PivotTable;

    Like

  63. Hi Dave,

    i need your or any one who can help me with sql 2005 T-log

    we have 3 internment in 3 different areas.
    master
    replication
    mirror
    our T-log backup start growing from 400 mk to 2,2 GB and it cause a lot of issue with Replication and mirror that can not catch up with production master DB its always behind for 3 to 4 hours.

    i need help to understand what is casing t-log to jump from 400 mg to 2 GB

    and our job for T-log backup run every 15 min and its 2 GB

    all help will be appreciated.

    Like

  64. Hi,

    Unable to pass parameters to stored procedure with pivot.This is my stored procedure

    alter procedure Sample(@date1 datetime,@date2 datetime,@date datetime,@date4 datetime)
    as
    SELECT ‘Forecasted’ AS HeadCount,[@date1],
    [@date2],[@date3] ,[@date4]
    FROM
    (SELECT
    StartDate,HeadCount FROM SAR_HeadCount)
    AS SourceTable
    PIVOT
    (
    SUM(HeadCount)
    FOR StartDate IN ([@date1],[@date2],[@date3],[@date4])
    ) AS PivotTable;

    StartDate is DateTime datatype only but still getting erro

    Error is

    Msg 8114, Level 16, State 1, Procedure Sample, Line 3
    Error converting data type nvarchar to datetime.
    Msg 473, Level 16, State 1, Procedure Sample, Line 3
    The incorrect value “@date1″ is supplied in the PIVOT operator.

    Like

  65. I would like to have a high level discussion with you about SQL DBA work, SQL Programming, SSIS/SSRS vs. BI tools like Micro Strategy for designing/maintaining information portals.

    I am the company owner for both Digitalrep (Retail Consulting) and Pixelstreams (IT SaaS services) and we’re a Microsoft SPLA shop with 20 employees.

    I’m specifically looking for advice that will help me determine strategy and best practices for future growth. This may lead into a consulting agreement, but I’m not sure how you engage with web based leads.

    fyi.. I’m not a SQL expert, so our conversation my just be limited to describing our current environment, data warehousing, reporting tools, etc.

    Thank you in advance for your time and consideration.

    Like

  66. I want to create a link server. In one machine I have sql server 2008 and in another machine I have sql server 2005. Now If I create a link server on 2008 to 2005 then It will me a network or instance related error.If I switch from 2005 t0 2008 then it gives me a version error. Even both the server are not connected each other like If I want to connect from sql server 2008 to 2005 then and then it gives me an error.I appreciate if anyone help me create link server with different version.Thanks.

    Like

  67. Hi,

    I have 4 weeks data.

    Can u tell me how to find reports for across last 4 weeks,3 weeks and 2 weeks and 1st week,2nd week,3rd week and 4th week.

    Across 4 weeks should include data among the 4 weeks.

    4th week means only 4th week.

    Just give me the structure on how to do that.

    Like

  68. Dear Sir,

    Due to some error in installation my SQL Express 2005 did not install completely. I tried uninstalling the components by using “Add or Remove Programmes”. All the components were removed successfully but SQL Server VSS Writer is not getting removed. The error message says sqlwriter.msi file is not available in the installation package.

    Since I downloaded it from the net I do not have an installation CD. Please help me in removing the VSS writer so that I can re-install SQL Express 2005 afresh.

    Regards,

    Shaibal.

    Like

  69. Hi,

    I am getting error because of this line in stored procedure.if i give integer value its working but when i enclose in quotation i am geting error.25:0 is string datatype
    Count(Case When Totalhours<='25:0' Then EmpNo end)

    ALTER PROCEDURE [dbo].[SAR_Sp_GetScheduledHours](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME)
    AS
    DECLARE @query VARCHAR(MAX)
    BEGIN
    SET @query = 'SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
    'FROM
    (SELECT WeekStartDate,
    Count(Case When Totalhours<='25:0' Then EmpNo end) as Less
    FROM TestTable1 group by WeekStartDate)
    AS SourceTable
    PIVOT
    (
    max(Less)
    FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
    ) AS PivotTable'
    EXEC(@query)
    END

    Like

  70. Hi,

    In Pivot can i have multiple selection.from table i am getting data based on the criteria for each criteria i want to select data for each week.Like this

    PIVOT
    (
    max(Less than 2 hours),max(greater than 2 hours)
    for WeekStartDate in ([],[])
    )

    SELECT WeekStartDate,
    Count(Case When Totalhours=’35:0′ Then EmpNo end) as greater than 2 hours
    FROM TestTable group by WeekStartDate)
    AS SourceTable
    PIVOT
    (
    max(Less than 2 hours),max(greater than 2 hours)
    FOR WeekStartDate IN (‘ + ‘[‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ + ‘)
    ) AS PivotTable

    Like

  71. Good Morning Pinal –

    I need your help regarding one of my requirement in SQL SELECt query..

    I want to pull Birthdate between given day and month range. I don’t want to consider year at all whatever the year is it doesn;t matter.

    Let’s say i want to pull birthdate between 11/10 to 20/12 (dd/mm) format. (i.e., 11/10/1990, 18/12/2001, 19/11/2010 and so on)

    I have tried by using Month and Day function with BETWEEN operator but it’s not giving me correct result.

    Let’s say, if someonce birthdate is 09/11/2008 or 05/12/1990 or 10/12/2010 (dd/mm) then this not pulls into result..

    It’s urgent..

    Like

  72. Hi
    Pinal

    I got help many time from your site. its very useful site and i realy like your your help for sql server queries.
    i have database server1 having sql server 2005 dev edition.
    I have another server2 having sql server 2005.
    Transaction take place in sql server1 , i want all transaction will also be place in sql server2.
    can you please help me how can i do this.
    i mean by replication or by clustering or any other way.
    if any of them then how?

    I’ll be very much thanks to you if you help me regarding this matter.

    Abdul Jabbar Patel

    Like

  73. Hi All,

    I am getting error in Stored procedure when passing TotalEmployee

    Please check words in Bold.Also i am getting result as percentage value without percentage symbol.Where to percentage symbol.(for example i am getting 75 i should get 75 %)

    Error is -Must declare the scalar variable “@TotalEmployee”.

    alter PROCEDURE GetSchedule @date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignID INT AS
    DECLARE @query VARCHAR(MAX),@TotalEmployee int
    BEGIN
    set @TotalEmployee=4
    SET @query = ‘SELECT ‘ + CHAR(39) + ‘b/w 24 and 40′ + CHAR(39) + ‘ AS TotalHours, [‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ +
    ‘FROM
    (SELECT WeekStartDate,
    convert(varchar,dbo.FDiv(Count(Case When Totalhours>=24.00 and TotalHours=40.00 Then EmpNo end),@TotalEmployee)*100) as empno
    FROM TestTable4 group by WeekStartDate) AS SourceTable
    PIVOT
    (
    max(empno)
    FOR WeekStartDate IN (‘ + ‘[‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ + ‘)
    ) AS PivotTable';
    EXEC(@query);
    END

    Like

  74. Hi All

    I would like to write a trigger on insert or update

    so it will write the recored from the table in the MS SQL database to the table in Oracle

    can some one help me in that

    the trigger must be written inthe MS SQl database and it should write the record to the Oracle table

    Regards

    Like

  75. Hello, im a Trainee Developer need ur help
    I have a table Student_INFO where S_ID,S_UNI,S_ROLL are Columns S_ID is a PK
    S_UNI have string values ‘UIT’ now in S_ROLL i want ‘UIT-0001′ automatically generated. I tried the formula [‘UIT’ +S_ID] but it doesn’t generate give INT value error.
    Pleas help me out.
    thanks :)

    Like

  76. Hi,

    I want to know about finding Discrepancy between tables records.

    I have 2 tables.1st table is static table and the other table I am forming from 15 different tables using joins.

    Now i want to compare results between these 2 and want to diaply records if there is any discrepancy in any of the field. Both are having same no of fileds.(say at least 150 fields)

    Thanks for your help.

    Thanks,
    Hitendra

    Like

  77. Hi,

    In my table I have a varchar type column, the values like..

    sale_date

    04/23/2010

    02/03/2010

    12/24/2010

    I need to retrieve the records in both ascending and descending order. Problem is now it is a varchar type if I use order by o/p like

    02/03/2010

    04/23/2010

    12/24/2010

    I tried with convert(DATETIME,sale_date) but no use.

    Like

  78. Hi
    I have a problem when i tyr tp backup the transaction log file i find this error Backup log cannot be performed because there is no current database backup

    Like

  79. Hi Madhivanan,

    I have one doubt for ‘SP_HELP’

    I have given “50” the column length, when create a table,

    Create Table Temp121010
    (ID INT,
    Description NVARCHAR(50))

    After created table then i’ve use ‘SP_HELP’ query, its displayed that column length is “100”

    {SP_HELP Temp121010}

    Can you tell me that reason, why appears?

    Thanks
    Samy

    Like

  80. Hi Pinal,

    iam trying to find the missing sequences numbers
    the values are 1, 5000000,99865754 like that
    Please help to find the missing sequences numbers.

    Regards
    kalyan

    Like

  81. Hi Sir,
    i have two table t1 and t2
    in t2 i created dual primary key on two columns
    i want to insert t1 table data to the t2 table.
    bt before inserting t1 table data to the t2 table
    i want to create report of data which will not going to insert in t2 table
    Means because of dual primary key of t2 table ,only unique records will insert ,nt duplicate.
    nw in this scenario before inserting i want to create report of data which is not going to insert?
    I used after insert,for insert but its giving data which is not going to insert in 2nd table after inserting records.
    Please help me.
    Thanks in advance

    Like

  82. Hi,

    I have 4 weeks data of employees.

    Can u tell me on how to find no of employees who are getting weekoff in sameDay.For example If the emp 101 is getting week off in same day say saturday or any day consistently for all 4 weeks then we can count is as 1.

    In the below table we have to get data based on MatchingOff.It will have TimeIn value if the TimeIn if Off then MatchingOff Column value will be Off.

    Tablestructure

    EmpNo Date TimeIn TimeOut MatchingOff

    Like

  83. SELECT iMap_TeamScan_Temp.ID, iMap_TeamScan_Temp.PlantID, iMap_TeamScan_Temp.TeamID, iMap_TeamScan_Temp.SRPID, iMap_TeamScan_Temp.ScanDateTime,
    iMap_TeamScan_Temp.Trn_User, iMap_TeamScan_Temp.Trn_Date, iMap_TeamScan_Temp.Status,
    iMap_TeamScan.SRPID AS SRPID2
    FROM iMap_TeamScan_Temp LEFT OUTER JOIN
    iMap_TeamScan ON iMap_TeamScan_Temp.SRPID = iMap_TeamScan.SRPID
    WHERE (iMap_TeamScan.SRPID IS NULL)

    I use join to insert avoid “Duplicate insert”. Also I can use a loop to insert row by row while checking if any duplicates are inserting.
    Which is the best inorder to avoid low performance.

    Like

  84. Hi ALl,

    I have 4 weeks employee data with table columns EmpNo,MatchingOff,atnDate.
    MatchingOff column will have TimeIn value if the TimeIn is 0 then MatchingOff column value will be Off.Based on this i need to check If the employee is getting weekOff on sameday for all weeks if yes then increment count.Similarly how many employees are getting weekoff on sameday then increment the count.

    Like

  85. Hi,

    I am currently setting up a SQL Server in my laptop using SQL Server 2008. However i dont know how to create a SQL server. I can create only SQL database.

    I have worked in my client test environment and they have a Server and inside that they installed SQL server. Here in my case, is my laptop is acting as server?

    Can you please clear this basic fundamental doubt?

    Regards,
    Divakar Ragupathy

    Like

  86. Hello Sir,
    i have table called Employee and i am trying to load data from one SP to this table, earlier it did not gave me error but its giving me error now like “violation of constraint of PK_Employee” it means duplicate data can not be added so i checked the PK_Employee which is clustered key on index for “Calldate “column and “Employee ID” column , Employee ID column is having unique values while Calldate Column have duplicate values but earlier it did not gave me error when i ran that SP to insert Records
    so i dont understand why its giving me error now as each record is different with unique Employee ID , n i tried to disable the clustered index key but then i can not access that table with even simple select query, so what should i do to Add the data in this table ?

    Please help me soon,,
    i would appreciate that

    Like

  87. Hi Pinal

    I have a table with Bit column. I want to write a sp which has a bit parameter. Now I want to create a query to do something like this

    select all records with 0 when the parameter is 0
    select all records with 1 when the parameter is 1
    select all records when the parameter is null

    I know i can do this by creating a temporary table variable and then using if clause, insert values in temporary table based on parameter value and then use this table with IN clause

    but I am looking for a solution which does not involve creating any temporary table.

    Like

  88. HI
    I CREATE XML FILES USING ADO AND XML DOM
    FROM ASP CLASSIC.
    COULD YOU TELL ME THE BEST WAY TO ADD THE ENCODING TAG AT THE BEGINNING OF MY DOC. I OPEN THE XML WITH THE FILESYSTEMOBJECT WRITE THE TAG AND APPEND THE REST OF THE XML. IS THAT GOOD ENOUGH?
    MY XML IS PRODUCED WITH ADO AND ‘FOR XML AUTO’ TO A STREAM AND THEN USING THE DOC I CREATE THE DOC
    PLEASE HELP
    THANK YOU

    Like

  89. Hi

    I’ve got a little question,

    When inserting a value too large for a column SQL server is truncating the string instead of throwing an exception.

    e.g.

    Table t(a varchar(10))

    insert into t(a) values (‘more than ten characters’)

    select a from t

    results in:

    ‘more than ‘

    why?

    Like

  90. I have a tabel Visitor with column names as in the following store procedure- but when I create this its gave an error that- the name “Vis_Name” is not permitted in this context……..Column names are not permitted.

    CREATE PROCEDURE sp_AddVisitor(@Vis_Name varchar(50),@Vis_Gender varchar(6),@Vis_Address varchar(100),@City_Id int,@State_Id int,@Vis_Pin int,@Vis_Contact_No)

    AS
    begin
    insert into VISITOR values(Vis_Name,Vis_Gender,Vis_Address,City_Id,State_Id,Vis_Pin,Vis_Contact_No)
    Return

    Like

  91. Hi Pinal,
    I have a ExcelSheet contain macros to format the data i.e adding borders to data. need to develop a report by copying the sheet to retain the macros and populate the excelsheet with the user filter conditions. is it possible to create a excel,placing data from sqlserver. i am in dilema whether to use .Net programming to create excel or directly generating the excel using sqlserver.

    Like

  92. Hello Nitesh,

    Make the following corrections in your stored procedure

    1. Specify the datatype for the last variable in your stored procedure (@Vis_Contact_No)
    2. You have created a stored procedure with the first three variables with varchar datatype and the next four variables with integer datatype and trying to insert the values without specifying the datatypes.
    3. end your stored procedure

    So, the corrected stored procedure is,

    CREATE PROCEDURE sp_AddVisitor(@Vis_Name varchar(50),@Vis_Gender varchar(6),@Vis_Address varchar(100),@City_Id int,@State_Id int,@Vis_Pin int,@Vis_Contact_No int)

    AS
    begin
    insert into VISITOR values (‘Vis_Name’,’Vis_Gender’,’Vis_Address’,City_Id,State_Id,Vis_Pin,Vis_Contact_No)
    —(‘Vis_Name’,’Vis_Gender’,’Vis_Address’,1, 2, 123, 1234)

    end
    return

    This should work fine.

    Like

  93. How to perform row by row operations without cursors in sql server 2005?
    Give Examples?
    or
    What is the Alternative of Cursor in sql server 2005?
    Give Examples?

    Like

  94. Sir

    I have a SQL database. There are 278 foreign key relationships in my db. I have an issue that when i delete record from my parent table whose relationship exists in child tables. It delete the record from parent table.

    I write this query: delete from parent table name where id = 2, but with id 2 record is exists in child table.

    In SSMS in Object Explorer in design view foreign key relation is exist but i am still able to delete the record from parent table.

    Also when i create foreign key relation by droping existing relation and create relation again then it works properally.

    Please help me on this issue or i have to create all relations again.

    Like

  95. hi, we are working on aspx version using sql server. but our application was build on asp. we are working on latest aspx. the problem is that when we are work on aspx it creating a big log file in sql server due this reason sql server become hang. we think apsx pages creating problem. can you describe how we can resolve this issue.
    we are using sql server 2008. asp pages creating small log file but when we work aspx pages it create big log file.
    for example asp pages create 1kb log file. but aspx pages creating 109211KB files.
    kindly help us. its urgent if you provide some guide or tips
    thanks

    Sultan

    Like

  96. HI Pinal,

    First I want to praise your site …it helps so greatly.
    I am totally new in SQL. Specially in SQL 2005. I am facing a big problem in one of my Visual basic program where there is frequent update and access of fields from several SQL 2005 tables. The program was running fine when the backend database was SQL2000. Recently we have shifted to SQL2005. Since then I am getting an error of DEAD LOCK error where it says Microsoft forcefully ended a process due to dealock of reqsouurces and I have to rerun the transaction. NOw I do understand the meaning of that. I have checked all my connection and records sets. I am getting that so frequently that I need to do something. Can You please please please help me . I can provide you with the code if you want to see. I am anxiously waiting for your reply. Thanks.

    Runa.(Winnipeg, Manitoba, Canada)

    Like

  97. I want to connect two remote SQL-Servers using Dynamic IP address over internet to replicate data.

    My Subscriber on dynamic IP.
    Receiver on Static IP.

    It is possible?

    Like

  98. Hi,

    There are two tables.

    Table Patient Table Followup
    Field1 PID Field1 FID
    Field2 Pname Field2 PID
    Field3 Fdate
    Field4 Remark
    Field5 Next_F_Date

    I want the all the process names and remarks which are there in the followup table with max(next_F_Date).

    I need the solution using the Group By and Having Clause without using the complex queries.

    Regards,
    Pritam.

    Like

  99. Dear Sir,

    FYI, I have few SQL servers with SQL 2008 database, I would like to know, how to bulk deploy some sql source codes (SP,alter/create table) into all this sql server database on the same time (in parallel) ? And how to maintain/manage all sql server like sql server database performance, And how to bulk install SQL server patches?
    Thank you very much !

    Like

  100. HI Pinal,

    I have a query and requirement also. query is if i will detach sql2000 database (.mdf and .ldf file) and then atach it to sql2005 or 2008 server, then i am not able to call stored procedures of it, altough i quite able to see the text of it via sp_helptext command. and my application is also not able to use any stored procedure from this database. Please suggest any server side settings or query for this issue.

    kind regards,
    Abhishek

    Like

  101. Hi Pinal,

    Your blog is too helpful,
    I need one more favour as i have to fetch records where if the current date is first of any month then data should be of entire last month else it should give current month data with 7 days gap.

    for e.g if today date is 01 Nov my query should return data for oct month .

    else if todays date is 03 Nov it should return data with date into consideration as 01 and 02,

    and if date is 08Nov or above it should consider dates with 7 days gap like 1,2,3,4,5,6,7

    Like

  102. Sir! My problem is whenever i try to create new login or change the password of sa in SQL Server 2008 what happens is when i set it up by providing the username and password & also uncheck that enforce password policy and press ok. Now when i reopen the properties of that login the password has always been changed to something unknown, also if soon after creating new login if i try to login using that username and password it doesen’t. Can you please help me
    Thankyou very Much.

    Like

  103. Hi

    How do you implement relational integrity without using foreign key?

    What is the alternative to achieve the same as foreign key without using one?

    Like

    • Staging table can be used for temporarily. For example you want to import some data from text file to the production table. but you want to do some processing before insertion. In this case you can import data to different table;process it;insert to production table

      Like

  104. Dear Sir,

    Can anyone help me.. I have successfully created 1000 linked servers to connect to 1000 branches ( original dbf files) , the application is liv and therefore migration is ruled out. Now can i create a single view out of these 1000 views..

    Like

  105. Hi,
    I am working with to databases. From one of de database I use a table and from de other one a view. There a no primary key in this tables that refer to one a another. So if a make a new view it doesn’t work good.

    In the table I have colum useractivity with string text rows like this “Modified Card : Card Number 17961 : Modified : Inactive Date/Time” , now I want to extract only de number from this string text and convert this number from string type to number type, so that I can put it in a alias colum, How can I do that in Sql 2005?????

    Because than I can set the number in the alias colum as primary key and the cardnumber in the view as primary key to get the info I want. So please can you help me.

    Like

  106. this is an interview question and even I did not understand why do he wants to do that? Is it possible that it might be beneficial in mobile applications ?

    Like

  107. Pinal,

    I have a value = ’00L103L1′ and I want to remove the ‘L1′ portion at the end. What is wrong with my query that the result ends up being ‘0003’ rather than my expectation of ’00L103’?

    update main1
    set main1.volser = replace(main1.volser, substring(main1.volser,7,2),”)
    where clientid = 1 and len(main1.volser) = 8 and substring(main1.volser,7,2) in (‘L1′)

    For your convience,

    create table main1
    (clientid numeric(18,0) not null, volser varchar(15) not null)

    insert into main1 (clientid, volser) values (1,’000003L1′)
    insert into main1 (clientid, volser) values (1,’00L103L1′)
    insert into main1 (clientid, volser) values (1,’00L103′)
    insert into main1 (clientid, volser) values (2,’000003L1′)
    insert into main1 (clientid, volser) values (2,’00L103L1′)
    insert into main1 (clientid, volser) values (2,’00L103′)

    select * from main1

    update main1
    set main1.volser = replace(main1.volser, substring(main1.volser,7,2),”)
    where clientid = 1 and len(main1.volser) = 8 and substring(main1.volser,7,2) in (‘L1′)

    select * from main1

    drop table main1

    Regards,

    Dave

    Like

  108. I need to loop thru a large number of rows 50k+
    compare a pay field in one table to a fund amtt in another table
    if table 2 amount is > = table 1 amount then
    create a new row in another table with the results and update amount in table 2
    I am new to loops – cursors – fetch while statements….any help will be greatly appreciated….

    Like

    • Lisa,
      Make us better understand your requirement with Table structure and lets find if logic can be derived from sql statments unless there is a need of cursors

      Like

  109. Hi,

    How can we view what are all objects using SQL Server memory and how much (mb) in a AWE Enabled 32 bit SQL Server 2005.

    Regards,
    S.Balavenkatesh

    Like

  110. Hi Dave,

    Is there any scripts or query how can we view what are all the objects using SQL Server Memory and how much in a AWE Enabled Sql Server 2005.

    Regards,
    S.Balavenkatesh

    Like

  111. hi mr.pinal dave.
    I enable cdc on mydb and 2tables and aduite my change.but see when I change data type in table’s design ,cdc table which store modified data,destroy and I can not see cdc.capture instance_ct and whole data destroy.what do this event happen?

    Like

  112. hi mr.pinal.
    I enable cdc for my db and 2 tables.but when modify data type on design’s table,I see that cdc.captureinstance_ct not exist and whole changed data destroy.why do this event happen?

    Like

  113. SSRS Report timepout.

    Error : “System.outofMemoryException”

    I have SSRS 2005 report.
    The report displays 10,000 of records successfully, but when the report is supposed to display lakhs of records, the report gets time-out.

    I have also tried the below changes but still the timeout occurs.
    1) In Report manager have selected the “Do not timeout report execution” option.
    2) In Machine.config changed the
    to
    3) In rsreportserver.config changed “MemoryLimit” and “MaximumMemoryLimit” to 120 and 160 respectively.

    Like

  114. SSRS Report timepout.

    Error : “System.outofMemoryException”

    FYI : Dotnet Framework 2.0, Sql Server 2005, SSRS 2005.

    I have SSRS 2005 report.
    The report displays 10,000 of records successfully, but when the report is supposed to display lakhs of records, the report gets time-out.

    I have also tried the below changes but still the timeout occurs.
    1) In Report manager have selected the “Do not timeout report execution” option.
    2) In Machine.config changed the
    to
    3) In rsreportserver.config changed “MemoryLimit” and “MaximumMemoryLimit” to 120 and 160 respectively.

    Please let me know, what are the possible solutions that I can do to avoid this error and display whatever data needs to be displayed.

    Like

  115. Hi,

    This is my table.If i dont have data for particular date i am getting null but it should return 0.

    CREATE TABLE SAR_HeadCount(
    HeadCount int,
    CampaignID int,
    atnDate datetime,
    )
    Insert into SAR_HeadCount(HeadCount,CampaignID,atnDate)
    select ’40’,’1′,’8/1/2010′ union all
    select ”,’1′,’8/8/2010′ union all
    select ’43’,’1′,’8/15/2010′ union all
    select ’43’,’1′,’8/22/2010′

    Please run this stored procedure it will return null for one column but it should return 0

    exec SAR_Sp_GetForecastedHC ‘8/8/2010′,’9/15/2010′,’10/22/2010′,’10/29/2010′,1

    ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
    AS
    DECLARE @query VARCHAR(MAX)
    BEGIN
    SET @query = ‘SELECT ‘+ CHAR(39) + ‘Agents Scheduled as per Schedule’ + CHAR(39) + ‘ AS HeadCount, [‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ +
    ‘FROM
    (SELECT HeadCount as Head,
    StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
    AS SourceTable
    PIVOT
    (
    max(Head)
    FOR StartDate IN (‘ + ‘[‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ + ‘)
    ) AS PivotTable
    union
    SELECT ‘+ CHAR(39) + ‘Forecasted HC as per Hiring Plan’ + CHAR(39) + ‘ AS HeadCount, [‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ +
    ‘FROM
    (SELECT HeadCount as Head,
    StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
    AS SourceTable
    PIVOT
    (
    max(Head)
    FOR StartDate IN (‘ + ‘[‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ + ‘)
    ) AS PivotTable’
    EXEC(@query)
    END

    Like

  116. Hi,

    Please tell me how to handle value for the below stored procedure.
    when i run the below stored procedure for date 8/22/2010 i am getting corresponding column value as null but is i should handle that null value with 0.

    CREATE TABLE SAR_HeadCount(
    HeadCount int,
    CampaignID int,
    WeekStartDate datetime,
    )

    Insert into SAR_HeadCount(HeadCount,CampaignID,WeekStartDate)
    select ’46’,’1′,’8/1/2010′ union all
    select ’46’,’1′,’8/8/2010′ union all
    select ’46’,’1′,’8/15/2010′

    Please execute this stored procedure

    exec SAR_Sp_GetForecastedHC ‘8/1/2010′,’8/8/2010′,’8/15/2010′,’8/22/2010′,1

    ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
    AS
    DECLARE @query VARCHAR(MAX)
    BEGIN
    SET @query = ‘SELECT ‘+ CHAR(39) + ‘Agents Scheduled as per Schedule’ + CHAR(39) + ‘ AS HeadCount, [‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ +
    ‘FROM
    (SELECT HeadCount as Head,
    StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
    AS SourceTable
    PIVOT
    (
    max(Head)
    FOR StartDate IN (‘ + ‘[‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ + ‘)
    ) AS PivotTable
    union
    SELECT ‘+ CHAR(39) + ‘Forecasted HC as per Hiring Plan’ + CHAR(39) + ‘ AS HeadCount, [‘ + CONVERT(VARCHAR,@date1,101) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + CONVERT(VARCHAR,@date4,101) + ‘]’ +
    ‘FROM
    (SELECT HeadCount as Head,
    StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
    AS SourceTable
    PIVOT
    (
    max(Head)
    FOR StartDate IN (‘ + ‘[‘ + isnull(CONVERT(VARCHAR,@date1,101),0) + ‘], [‘ + CONVERT(VARCHAR,@date2,101)+ ‘], [‘ + CONVERT(VARCHAR,@date3,101) + ‘], [‘ + isnull(CONVERT(VARCHAR,@date4,101),0) + ‘]’ + ‘)
    ) AS PivotTable’
    EXEC(@query)
    END

    Like

  117. Hi Dave:
    I just bought SQL Server 2008 Developer version. I Installed it on the old PC running Windows Vista and it was OK even though I could not see the DB diagram. But I try to install it on my newer PC running Windows 7 (using same defaults as other computer) I could not log-on.

    The system gave me the following error 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)”

    Would you please give me step-by-step instructions on how to correct it.

    Many thanks
    Hung

    Like

  118. Hello Sir,

    I need your help for querying the following problem.
    suppose i have a table with the following columns ord_num(PK) and ord_date.I need to pass 2 parameters for the start date and end date from SSRS report.
    lets take @StartDate=’2009-12-01′
    @EndDate=’2010-02-10
    Now i need to take count of the ord_num week wise.
    The output should be
    Week Count
    —————– ———————-
    Tue,Dec 1,2009 to Sun,Dec 12,2009 10
    Mon,Dec 13,2009 to Sun,Dec 19,2009 20
    ………………….
    Mon,Feb 08,2010 to Wed,Feb 10,2010 30

    Like

  119. I some how renamed a table [table1].
    Meaning the brackets are part of the name. ‘[ ]’
    using – MS SQL
    I would like to just remove the table altogether.
    I get errors because the brackets are part of the table name.

    Any suggestions?

    Like

  120. Hi,

    I have planned to write the MCTS 70-432 on SQL Server exam by next week. Please Provide me with any DISCOUNT VOUCHERS if anyone of you have it. This will be very helpful.

    Regards,
    VK

    Like

  121. Hello Sir,

    I want to do..

    If one database get any type of changes (insert, update etc) then another database will get those changes automatically at time.

    Those two database has same server.

    How i do in script (ASP)

    Please reply as soon as possible

    Like

  122. SQL 2000 data to 2008 after the transfer date field has changed

    sql2000 date field= 01.12.2010
    sql2008 date field = 2010-12-01

    SQL 2008 also show how the date field in the form of 01.12.2010

    please help me

    Like

  123. Hello Sir,
    Is there any limitation for the existence of SQL server 2000 and SQL server 2008 R2 in same server? If they can exist in same server, then is there any specific installation steps to be followed?

    Thanks

    Like

  124. Hi Pinal,
    I am following your tips almost every day in my practice and I want to say thanks to you that you really helped and helping so many pupils through your blog.
    Pinal I need your help regarding Linked server.
    We have two different locations for our two servers and I want to transfer my data from one server to another server every day. For that I used linked server.
    Both are SQL Server 2005 and operating server 2008.
    If I transfer data say about 10000 records it works fine.
    But when I am trying to send more then 10000 rows then I am getting some error after 30 to 40 min.
    Below is the error massage.

    – Copying to [XXXXXXXXXXXx].[dbo].[XXXXXXXXXXXXXXXXx] (Error)
    Messages
    • Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Communication link failure”.
    An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “TCP Provider: An existing connection was forcibly closed by the remote host.
    “.
    (SQL Server Import and Export Wizard)

    • Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “Destination Input” (107)” failed because error code 0xC020907B occurred, and the error row disposition on “input “Destination Input” (107)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
    (SQL Server Import and Export Wizard)

    • Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Destination – XXXXXXXXXXXXX” (94) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
    (SQL Server Import and Export Wizard)

    • Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “WorkThread0″ has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
    (SQL Server Import and Export Wizard)

    • Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
    (SQL Server Import and Export Wizard)

    • Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Source – XXXXXXXXXXXX” (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
    (SQL Server Import and Export Wizard)

    • Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “SourceThread0″ has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
    (SQL Server Import and Export Wizard)

    Please help me to find some solution.
    Thanks
    Prakash Paul

    Like

  125. I am novice to Sql Server 2005. I have a question to you:

    Why we have 249 non clustered index in sql server 2005? why not 240 or 300? and the same question for sql server 2008, why 999 ? Why not 800 or 1000?

    I have asked this question in several sites but all I see is “Ask Microsoft Developers”. I believe that there must be some reason for it.

    Hope to see your attention on it.

    Regards,
    Claus

    Like

  126. Hi,
    I am building a BI application where I want to copy input data from files located external from the SQL Server database environment.
    Until now the copy from external folders to the database has been made by a windows script (an admin autority) in a BAT-file controlled by the Scheduler.
    Now I want to solve it by using a File System Task.
    However, what’s left is the login process.
    Can this windows login/connect be made by programming?
    If so how to do that?
    And what kind of syntax should be used to refer to external sites/folders: //server/xxx or \\server\xxx or…

    Thanks in advance

    tomas

    Like

  127. I am trying to get the information of Logins history like LoginsName, Createdby, Created_Date, PermissionGiventothesatabase, Givenbywhom…etc. This is he imp report which I have to give every month. Please help me on the same.

    Like

  128. Hi

    I have a set of tables with a common field and I need to retrieve common records, as long as that record appears in more than one table. Also I need to know in which tables that common record is appearing.

    Regards
    Orateng

    Like

  129. Hi Pinal,
    Thanks for all the help you are providing through your blog.
    I’m new to SQL and need a help.
    I’ve two tables- Table A and Table A_Archived.
    Table A_Archived is an exact replica of table A.
    I want to move all the records from Table A to Table A_Archived after a period of 120 days. That means if today I enter some data in to the Table A after 120 days it should move to Table A_Archived and Table A data should be deleted simultaneously.
    I”m thinking of a trigger/Stored procedure for this.
    for 120 days condition can I use following :

    DECLARE @120DaysPrior datetime

    SET @120DaysPrior = DateAdd(d, -120, GetDate())
    — Calculate 120 days prior to today’s date

    Can you please help me writing the query.

    Thanks,
    Arnab.

    Like

  130. Hi All,

    Can we right query form the given table X for the data like

    GroupHead Team
    A A1
    A A2
    A A3
    A1 B1
    A1 B2
    A1 B3
    B1 C1
    C1 D1

    1. now I want to select group Head A and want result
    “A1,A2,A3,B1,B2,B3,C1,D1″
    2. now I want to select group Head B1 and want result
    “C1,D1″

    Please help if any one having idea regard this group select query.

    Thanks,
    Prakash

    Like

    • Prakash,
      Find below code snippet a workaround. Note that you got change GroupHead value in where clause to display comma seperated values.

      create table #table (
      GroupHead varchar(4),
      Team varchar(10))
      go
      insert into #table
      select ‘A’, ‘A1′
      union all
      select ‘A’, ‘A2′
      union all
      select ‘A’, ‘A3′
      union all
      select ‘A1′, ‘B1′
      union all
      select ‘A1′, ‘B2′
      union all
      select ‘A1′, ‘B3′
      union all
      select ‘B1′, ‘C1′
      union all
      select ‘B1′, ‘D1′
      go

      declare @sql varchar(1000)
      select @sql = coalesce(@sql+’, ‘, ”) + Team
      from #table
      where GroupHead = ‘A1′
      select @sql

      Like

  131. Hi Pinal

    I have a scenario in my project where the users are allowed to post Ads.The Ads have Start date and End Date which means the Ad will be visible to the end users only when the current date falls between start date and end date.My application is a world wide application and the data type for the start date and end date is smalldatetime.Suppose if a user posts an Ad in India and another user in USA is trying to view the Ad how should i manage datetime between the 2 countries.My question is how should manage the datetimes between different countries especially when dealing with the End Date.I should not show the ads if the current date exceeds the End Date.Since we have different time zones for different countries,should i take the localtime of the user into consideration who is browsing our application to show the ads irrespective of the datetime where the ad has been posted ?

    Like

  132. Hi Pinal

    I am in search of a stored procedure which can create html page from the table stored in sql server database.

    i am fetching the result in a sql query and i want that result to be displayed in html page.

    pls help me asap to find the solution.

    Thanks
    Vikram Mahapatra

    Like

  133. Hi Pinal Dave,

    I have a SQL problem I just can’t seem to resolve. I have a table:
    CREATE TABLE [dbo].[SensorData](
    [SensorID] [int] NOT NULL,
    [Temp] [float] NOT NULL,
    [UnitNo] [int] NOT NULL,
    [DateStamp] [datetime] NOT NULL
    ) ON [PRIMARY]

    My goal is to find the last entry (Datestamp) for each SensorID (especially when they are not equal to-88.88 or 85, so that if the latest date entry has one of those values then it will go to the next latest row/date, etc Note: those values are erroreous so we don’t want any rows returned with those “Temps”) I’ve tried:

    Select SensorData.SensorID, max(SensorData.DateStamp) as Datestamp FROM SensorData WHERE SensorData.UnitNo=2233 AND (sensordata.temp-88.88 AND sensordata.temp85)
    Group By SensorData.SensorID

    and it works almost perfect except I need to get the “Temp” column values returned for each row it returns and I just can’t seem to get that last piece of the puzzle.

    I hope I have supplied enough information. All help is appreciated.
    John

    UPDATE: just before posting this I decided to try one last thing and it seems to work :) Is the following SQL the “Best” way to accomplish my goal? I believe it would be so much more simpler if I could just get the “Temp” col to return with the original SQL.

    Select Unitno, SensorData.SensorID, SensorData.Temp, SensorData.DateStamp From SensorData, (Select SensorData.SensorID, max(SensorData.DateStamp) as Datestamp FROM SensorData WHERE SensorData.UnitNo=2200 AND (sensordata.temp-88.88 AND sensordata.temp85)
    Group By SensorID)b Where sensordata.SensorID=b.SensorID and sensordata.Datestamp=b.datestamp

    Like

  134. Hello,

    i have a problem with connecting sql server 2005 from sql server 2008 management studio. i did all you said about connecting to sql server on this link: http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    but it does not worked for me. there is no firewall restriction and all the configurations which have to made on sql server has been made. i could not figure it out.
    can you help?
    thanks

    Like

  135. hi
    please help me
    i install sql sever2005(developer) on my laptop with windows vista os.
    also visual stadio 2008
    but sql can not connect to server
    error:18456
    i cant create new user account for connect to data base
    please learn me more than text in your topic about this error
    thanks

    Like

  136. Hi,
    Can anyone help me in getting list of all SQL servers and instance/databases created under it in a particular domain.
    It will be really helpful.

    Thanks in advance.

    Regards
    Laldhar

    Like

  137. Hello Sir,
    1.i have a database of size upto 50GB.Can i split that into two databases.

    2.Can i retrieve a specific nth row from a table
    without using ‘where’condition in query.

    regards

    Like

  138. Hi, sir, i would like to know if there is a way to set the opening new window in the right side, like when you open a new tab in the IE or Firefox

    Regards
    Kenji

    Like

  139. Hi,

    Donno where to submit my query. Please help me on the following.

    Have two columns acctid and pages in accounts table
    acctid is uniqueid – 1,2,3,4,5,6,7,…
    and pages are pagenos like 2,24,3,2,8,2,45…..

    want to loop for each acctid it has to show in another columns like
    If there are 2 pages for acctid – 1
    then 1_1
    1_2
    If there are 24 pages for acctid – 2
    then 2_1
    2_2
    2_3
    2_4
    2_5
    2_6 and so on till 2_24
    Can you please help me in doing this. struggling with this from 4 days.
    Please help me asap. I need to complete this tommorow.
    Thanks in Advance.

    Like

  140. HI

    Below is my sqlstament:

    Select * from Emp where city=’Ahmedabad’ or Age>30.

    I got the correct result as query very simple. I want to show in result that for which criteria match in result.

    E.g If there are 10 records found. 5 for matching city=’Ahmedabad’, 3 for matching Age>35 and 2 for matching both.

    Like

  141. Hi,
    I am working on the migration project of sql 6.5 to sql 2008.In the
    SQL 6.5 version
    *=,=,=* are used for representing Joins
    SQL 2008
    LEFT OUTER JOIN,RIGHT OUTER JOIN,INNER JOIN
    are used for representing joins.Even *=,=* notations are allowed.

    Can anyone explain the difference between using the two kind of notation in SQL 2008?
    If any difference is there,is there any performance related issues between two?

    Like

  142. Hi,

    I am going to estimate the disk space for the new database. I have overall 1903 columns in a DB & monthly insertion of 1 million records.
    So how can I estimate the disk for the DB?

    Please Reply.

    Like

  143. hi sir,
    here i have a doubt in replication
    can i make my subscription database as a publisher database to another subscription ?

    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