Contact Me – Archive 11

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

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1900 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books – SQL Server Programming and SQL Wait Stats. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Copyright violation and Reproduction of blog:

SQLAuthority.com is trademark of Pinal Dave. Exact work “SQLAuthority” or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave. If any article published on this blog violates copyright please contact me, I will remove it right away. Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, 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.

Contact Alpesh Joshi for personalized accounting services.

+Pinal Dave

pinal “at” SQLAuthority.com


About these ads

125 thoughts on “Contact Me – Archive 11

  1. hi sir ,
    i m a dot net developer in ahmedabad..
    i want to sql store procedure which is used for text search in whole database with its position (means its location). e.g search functionality like “http://stackoverflow.com ” ..
    Thanks

  2. I have something this kind of data

    name constructionName TotalPayment RemainingPayment
    Mahesh LivingRoom 10000 3000
    Mahesh Bedroom 20000 5000
    Mahesh Diningroom 5000 1000
    Ashok LivingRoom 12000 2000
    Ashok Bedroom 25000 4000
    Nimesh Diningroom 20000 12000

    I have this kind of data. Now I want to conver this data from rows to column.
    like Each person name has only one entry

    Mahesh LivingRoom 10000 3000 Bedroom 20000 5000 diningroom 5000 1000

    Ashok Living room 12000 2000 Bedroom 25000 4000 diningroom 0 0
    Nimesh Livingroom 0 0 Bedroom 0 0 dining room 20000 12000

    How do i get this data?
    Please help me out.
    Thanks.

  3. To uniquely identify a record in a table, each table needs an identity column called an ID. This column is assumed to also be a primary key for the table. What other columns can be used if no ID column is created?

  4. Is there SQL code to resize all entries in a datatype: image field that is application/pdf. I have one table that is 84GB and need to resize over 75,000 images that are in PDF format.

  5. We have a Database due of some enhancements to application we need to change the width of a column for a table. when we have executed a statement alter table alter column we got an error incorrect syntax near column. Upon looking into various aspects we realised that database is of 6.5 compatibility mode.
    PL. explain about it.
    we understand that altering column width is not provided/not supported in SQL Server 6.5. Is this correct? If not then what causes the error
    If we change the compatibility level of the database what would be the implications? pl. share the info on this matter
    Thanks

  6. Mr. Dave,

    Thank you helping people like me.
    What is the difference between these two queries?

    Select * From A
    Inner Join B ON A.Column1 = B.Column1
    Left Join C On A.Colum2 = C.Column 2

    AND
    Select * from (
    (Select * From A
    Inner Join B ON A.Column1 = B.Column1 )X
    Left Join C On X.Colum2 = C.Column 2

    Is there any performance differences between the two queries.

  7. how do I convert rows into columns in sql server.

    I have something this kind of data

    name constructionName TotalPayment RemainingPayment
    Mahesh LivingRoom 10000 3000
    Mahesh Bedroom 20000 5000
    Mahesh Diningroom 5000 1000
    Ashok LivingRoom 12000 2000
    Ashok Bedroom 25000 4000
    Nimesh Diningroom 20000 12000

    I have this kind of data. Now I want to conver this data from rows to column.
    like Each person name has only one entry

    Mahesh LivingRoom 10000 3000 Bedroom 20000 5000 diningroom 5000 1000

    Ashok Living room 12000 2000 Bedroom 25000 4000 diningroom 0 0
    Nimesh Livingroom 0 0 Bedroom 0 0 dining room 20000 12000

    How do i get this data?
    Please help me out.
    Thanks.

      • i am using the following query in mysql data base—–bcz i am a php devloper
        select name,group_concat(constructionName) ,group_concat(TotalPayment) ,group_concat(RemainingPayment) from TABLE NAME group by name…………..waiting for your feedback gudluck….

  8. Hello Sir ,
    I had written 5 sp and one main sp to exec all 5 sp in main sp. i had written transaction & rollball it’s working for fine for all errors accept
    Violation of PRIMARY KEY constraint ‘PK__BsTmpTra__8425EEA3031ED1A9′. Cannot insert duplicate key in object. this error occured in the exec of 3rd sp. this time it’s roll backing.. it’s exec remaining things.

  9. Hello Sir ,
    I had written 5 sp and one main sp to exec all 5 sp in main sp. i had written transaction & rollball it’s working for fine for all errors accept
    Violation of PRIMARY KEY constraint ‘PK__BsTmpTra__8425EEA3031ED1A9′. Cannot insert duplicate key in object. this error occured in the exec of 3rd sp. this time it’sNOT roll backing.. it’s exec remaining things.

  10. Please tell me what is the best plan to do disaster recovery in below case :
    I have one sql server with one database. Backup plan include automatic full backup scheduled on 12:00 AM every day and Log backup after every 1 hour.
    What is the way to recover my database if it fails on 3:40 AM ?

  11. Hi Sir,

    I when am running a stored procedure through batch it is taking long time to complete but if run it manually it’s taking 2 or 3 min time and it is having maintenance tasks on source tables every day. Can you help me?

  12. I have a table with 10 lakh records and it has FK relationships with many tables, i want to add a column in between columns i tried using table designer but it is timedout. Please let me know if there is any other sql command or any method to do

  13. Dave – first off:
    Thank you for your great site and help!!

    Second, is a question:
    I have a table that stores my test values (ID,01,02,03,05,10,50,DTI)
    This table is the dump for ALL tested values.
    In another table I have to track 50 of these ID’s so this table is
    (ID,ID01,ID02~ID049,ID50,DTI) [DTI = DateTime inserted]
    Is there a better way to store these 50 id’s, should a have a constraint on them.
    Just unsure if this is the properway to store this “lookup” data.

    Keep up the good work!!
    Thanks again,
    Bill

  14. I am a newb so this might be too general of a request, however here goes…

    I am tasked with removing the ‘sa’ login from all connection strings within our apps. I need to create a login that has read/write/execute capabilities to all tables within a database (not necessarily the system tables).
    Is there a set of normal permissions to grant to a user that allows simple selects, inserts, updates, deletes and the ability to execute stored procedures.

    Thank you for your help, in advance

  15. Hi,

    I was trying to set up DB mirroring in SQL Server 2008 and was getting 1418 (connection failure), do you know if the servers have to be running on port 1433 to set up mirroring? My server is running at 1401.

    Any help would be appreciated.

    Thanks.
    Kamran

  16. I’m having an issue with a long-running UPDATE of a non-indexed column. Let’s say, for sake of example, that it’s three columns: Name, Date, Amount, and the PK is Name, Date non-clustered. What I’m experiencing is that if I run a basic UPDATE to SET Amount = 1, it takes too long. If I dump the PK, it finishes quickly. I don’t understand why and UPDATE into a non-indexed column would take any longer one way than another. I’ve tried searching the Web for suggestions, but I get a million pages of ‘tuning indexes’ results and nothing that speaks to this particular performance issue. If the PK index is unaffected by the UPDATE, what’s going on?

  17. Hi Pinal ,

    Your blogs are always help full . I have a query on the MDF files . We have mistakenly created a DB with mdf and ldf with the same file name as examp.mdf and examp.mdf , By this as mdf and ldf locations are pointed same file the mdf file is huge in space, How can we separate the mdf file to mdf and ldf files now , we are using sql server 2005 version.Please suggest me on this

  18. Hello Sir,

    I am learning SQL server through this blog. It is very helpful and interacting.
    I was hoping if u could guide me from where to start and how to be good at SQl server cause i am looking for a job in this field and i donot have any experience or training what so ever. I would be very greatful if u guide me though my learning process so that i would be able to get a new job. I am kind of in a crunch here. Please…………

  19. Hi,

    i created an ssis package which will be run monthly for fresh data to be imported in to the excel sheets. The first time i execute the package, it executes correctly but the next time i try to execute it, extra columns are being added into the files. How to resolve this?

  20. Hi,

    I have a situation where a stored procedure in a data base is used to populate data in several data bases. How could I list all the tables used in the stored procedure.

    example: SP “ABC” on db “XX”is populating tables on db “XX1″, “XX2″ and “XX”
    Now, I wish to list all the tables which are being used; those which are populated and those which are used to populate from.

    Kindly guide me to find the solution to this.

    Thanks in advance.

  21. help, i am trying to select a row of data and insert this data into a table while incrementing a specific field.every field stays the same except for the on being incremented….see code that i have below…it inserts first letter of each column and loop doesnt break..
    declare @sum as varchar
    set @sum = ’50′
    DECLARE @QUANTITY varchar
    DECLARE @SERIAL_NUMBER varchar
    DECLARE @TRAN_DATE varchar
    DECLARE @TRAN_TYPE varchar
    DECLARE @AVERAGE_COST varchar
    DECLARE @PRICE varchar
    DECLARE @ITEM_CODE varchar
    DECLARE @ITEM_DESC varchar
    DECLARE @ITEM_DIVISION varchar
    DECLARE @ITEM_CLASS varchar
    DECLARE @UPC_CODE varchar
    DECLARE @TAC varchar
    DECLARE @FAC varchar
    DECLARE @CUST_NUMBER varchar
    DECLARE @CUSTOMER_NAME varchar
    DECLARE @INVOICE_NUMBER varchar
    DECLARE @PO_NUMBER varchar
    DECLARE db_cursor CURSOR FOR
    SELECT TRAN_DATE, TRAN_TYPE, AVERAGE_COST, PRICE,ITEM_CODE,ITEM_DESC,
    ITEM_DIVISION, ITEM_CLASS,UPC_CODE,SERIAL_NUMBER, TAC, FAC,
    CUST_NUMBER,CUSTOMER_NAME,INVOICE_NUMBER,PO_NUMBER, QUANTITY
    FROM [S2K].[dbo].[Test_Sales]
    order by 1
    OPEN db_cursor
    FETCH NEXT FROM db_cursor
    INTO @TRAN_DATE,@TRAN_TYPE,@AVERAGE_COST,@PRICE,@ITEM_CODE,@ITEM_DESC,
    @ITEM_DIVISION,@ITEM_CLASS,@UPC_CODE,@SERIAL_NUMBER, @TAC,@FAC,
    @CUST_NUMBER,@CUSTOMER_NAME,@INVOICE_NUMBER,@PO_NUMBER,@QUANTITY
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SERIAL_NUMBER = @SERIAL_NUMBER
    WHILE (@SERIAL_NUMBER= @SERIAL_NUMBER + @sum)
    BREAK
    ELSE
    CONTINUE
    END
    FETCH NEXT FROM db_cursor
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor

    • this is a row of data from the table which contains multiple record query must move to each row and analyze
      SERIAL NUMBER CUST. NUMBER CUSTOMER NAME QUANTITY
      300521102351 SD-360 TREVOR PAUL B 350

      below is what i would like to be inserted into second table
      quantity- which is 350 divided by 50 = 7
      so i need seven rows
      serialnumber field (300521102351) is incremented by 50 (hence customer bought 7 packs)
      every other column stays the same only serialnumber is incremented by50
      SERIALNUMBER CUSTNUMBER CUSTOMER NAME QUANTITY
      300521102351 SD-360 TREVOR PAUL 350
      300521102401 SD-360 TREVOR PAUL 350
      300521102451 SD-360 TREVOR PAUL 350
      300521102501 SD-360 TREVOR PAUL 350
      300521102551 SD-360 TREVOR PAUL 350
      300521102601 SD-360 TREVOR PAUL 350
      300521102651 SD-360 TREVOR PAUL 350

  22. update tablename
    set columnname= ‘ var _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-27736300-1']);_gaq.push(['_trackPageview']); (function() { var ga = document.createElement(‘script’); ga.type = ‘text/javascript’; ga.async = true; ga.src = (‘https:’ == document.location.protocol ? ‘https://ssl’ : ‘http://www’) + ‘.google-analytics.com/ga.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(ga, s); })(); ‘
    WHERE settingname = ‘google_anylitics_code’

    Error which i face:
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘_setAccount’.
    Msg 132, Level 15, State 1, Line 2
    The label ‘https’ has already been declared. Label names must be unique within a query batch or stored procedure.

    Please reply..

  23. Hello,

    We are planning to upgrade the current SQLserver 2005 STD 32 bit to 2008 R2 Ent edition on windows server 2008 R2 server.

    can you please suggest me the procedures we need to follow as i understand this is not direct upgrade?

    Thanks,
    Bharat

  24. I have a degree in economics and an mba. Have 12 years experience working in financial services bt want to change to IT, what certification will complement my experience. I am considering business intelligence. I need your advise.

  25. Hello Sir,
    I am creating an application in which I am consuming Sql Server Database. Now I want to protect my database from unauthorized used of database. No one can take back up of my database or restore it or can’t attach mdf file in sql server on another system and no one can see schema definitions of the database.
    Please tell me how it is possible.

  26. Hi sir,
    I have a table,it contained EID,Ename,Deptno and i want to retrieve data like deptno,all Ename’s with comma separation(30,ramu,subbu,suman,raja) then deptno,all Ename’s with comma separation like this how can i get, it should be taking all ename’s all under particular deptno
    Thank

  27. Hi Sir,

    I have a question retrieve of data.

    I have a “address” table it store all the emails for the customer.
    A customer can have more than one email.
    And Each email can be share by multiple customers.

    I have a scenario Customer A is having multiple emails and Customer B is having multiple Emails in DB.

    but both these customer should share more than one email.

    Example Customer A – Email 1 Email 2 Email 3 and Email 4
    Customer B – Email 1 Email 2

    My result set should how, i need result set with customer names using, shared emails. (Count should be more than one email)

    Could you please help me with his.

    Thanks so much for your help.

  28. Hi Sir,

    I have a table “temp” with column name “Col1″ of data type varchar(8)
    Data in table:

    Col1
    1
    2
    321
    554
    HO

    table content is same in both Production and UAT instances.

    I have written a wrong query like this in a application deployed in production
    select * from temp where Col1 in (321)
    instead of
    select * from temp where Col1 in (’321′)

    but to my surprise the query
    select * from temp where Col1 in (321)
    is working in Production but getting the below error in the UAT instance
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value ‘HO’ to data type int.

    Now i dont want to correct the query in the application deployed in UAT/Production at this point,
    since its working is procuction i suspect some database settings which can ignore this alphanumeric thing and execute.

    Your help is valued

    Thanks

  29. How to Create a New Partition on a Cube using T-SQL in SQL Server 2000?

    Select Case iMonth
    Case 1,2,3
    sQuarter = “1″
    Case 4,5,6
    sQuarter = “2″
    Case 7,8,9
    sQuarter = “3″
    Case 10,11,12
    sQuarter = “4″
    End Select
    CreateNewPartition(“CustomerLog”)
    CreateNewPartition(“CustomerLogUpdates”)

    Main = DTSTaskExecResult_Success

    End Function

    ‘**************************************************************************************************************************************************
    ‘This function creates the new cube partition
    ‘**************************************************************************************************************************************************
    Function CreateNewPartition( sCubeName)

    Dim sSourceTableNew
    Dim sLQuote
    Dim sRQuote
    Dim sPartitionName
    Dim sDimensionName

    sPartitionName = sCubeName
    Set dsoServer = CreateObject(“DSO.Server”)
    dsoServer.Connect (sServerName)
    Set dsoDB = dsoServer.MDStores(sDatabaseName)
    Set dsoSelectedCube = dsoDB.MDStores.Item(sCubeName)

    ‘Clone the existing partition into a new partition
    Set dsoOldPartition = dsoSelectedCube.MDStores.Item(sPartitionName)
    Set dsoNewPartition = dsoSelectedCube.MDStores.AddNew(sPartitionName & “_” & sYear & “_” & sMonth)
    dsoNewPartition.AggregationPrefix = dsoOldPartition.AggregationPrefix &”_” & sYear & “_” & sMonth & “_”
    dsoOldPartition.Clone dsoNewPartition, cloneMinorChildren
    dsoNewPartition.EstimatedRows = iEstimatedRows

    ‘Update the source table in the new partition
    sLQuote = dsoOldPartition.DataSources(1).OpenQuoteChar
    sRQuote = dsoOldPartition.DataSources(1).CloseQuoteChar
    sSourceTableNew = sLQuote & “dbo” & sRQuote & “.” & sLQuote & sFactTablePrefix & sYear & “_” & sMonth & sRQuote
    dsoNewPartition.SourceTable = sSourceTableNew

    ‘ Update the FromClause and JoinClause properties of the new partition.
    dsoNewPartition.FromClause = Replace(dsoOldPartition.FromClause, dsoOldPartition.SourceTable, sSourceTableNew)
    dsoNewPartition.JoinClause = Replace(dsoOldPartition.JoinClause, dsoOldPartition.SourceTable, sSourceTableNew)

    ‘ Update the SliceValue properties of the affected levels and dimensions to the correct values.
    ‘sDimensionName = sCubeName & “_” & sYear & “_” & sMonth & “^Date”
    sDimensionName = “Date”
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels(“(All)”).SliceValue = “All Date”
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels(“Year”).SliceValue = sYear
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels(“Quarter”).SliceValue = sQuarter
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels(“Month”).SliceValue = sMonth

    ‘Apply all the above changes
    dsoNewPartition.Update
    dsoSelectedCube.Update

    ‘Process the new partition
    dsoNewPartition.Process

    End Function

    I haven’t seen a topic like this on Google yet. Can you write something on this please?

    Thanks

  30. I’m trying to see if someone changed a linked server at some point on a server. I restored the master database to a different server and called it something different. However when I run select * from sys.servers it still returns what is sitting in the actual master database on ther server i’m on, even when i preface it by putting the database name in like: select * from mydatabase.sys.servers.

    How can I query what is actually in my restored database?

    Any help would be greatly appreciated.

  31. Hi,

    We are developing CMS software which will be used by around 300-500 websites in future. There will be separate database for each website. All websites will be having same

    database structure but their content will be different.

    We will be having continuous changes to the database structure, so keeping all databases structure (tables, indexes,procedures) in synchronize will be huge task. What is better

    way to keep all these databases structures in synchronize with minimumeffort?

    I am thinking of saving all database changes in script and running it for all databases. Can all database changes be scripted or is it necessary to use sql server mgt studio

    wizard to do some changes? Because if there is need to use sql server wizard to make some changes, it will be big task to do it manually for all the databases. Please let me know the changes which can’t be easily scripted and tools we can use to generate scripts containing changes. Or will I have to use some 3rd party tools to generate
    script for changes?

    We are using asp.net with sql server 2008 for development. Please suggest technology/methodology that will be useful to achieve good performance with less maintenance. Can you provide me any article for reference?

  32. Dear Sir,
    we are running 7 different databases in on server [Server configuration Intel Xeon 2.53Ghz]. databases sizes are following

    Name Db_Size
    A 181.94 MB
    B 70.50 MB
    D 60138.00 MB
    E 156.44 MB
    F 4369.38 MB
    G 156281.13 MB
    H 434.19 MB

    My question is when doing a single process [update,select] in database G always getting struck database D. so did index in database G but no feel any performance different. Hard disk spaces also available. so dear sir please give me the better solution to that.

    Thanking you

    MMM Shukri.

  33. Pinal,
    I have a question about transaction isolation level.

    I have the following in one SSMS query window.
    while @@TRANCOUNT > 0 rollback
    set transaction isolation level serializable
    begin tran
    select * from LockTest where id = 3

    In another SSMS query window I am UNABLE to insert any record into the LockTest table until the first query window does a rollback or commit.
    insert into LockTest(name) values (‘Z1zzxx2zcc’);

    My understanding is that I SHOULD be able to do inserts, updates and deletes AS LONG AS the insert/update/delete does not affect the result set of the first query window. The result set of the first query window is just one record (whose id is 3).

    LockTest has 2 columns and indentity column, id and a name varchar(5)

    Any help will be appreciated.

    Thanks.

  34. Pinal,

    I’m working on a security audit with active directory and sql 2008 r2. I need to identify active directory groups nested within another group. Currently, nested groups are not showing in my audit.

    I am using xp_logininfo. I can see the users that group members, but not nested groups.

    Thanks for the help!

  35. Hi I have some job with script like this below and if exists somthing in #tablea I insert that in some other table and set value for column broj in tableb on max(rb) from #tablea.
    I have dilemma thus transactions between begin and end are executed as one or every command is separate transaction

    if exists(select 1 from #tablea)
    begin
    declare @RB as int
    select @RB = isnull(max(rb), 0)
    from #tablea
    insert into……select * from #tablea
    update tableb
    set broj = @RB
    where id = ‘rb’

    end

  36. hi
    i need help for following problem
    if u have time then pls help me for solution

    create table test
    (ID int, MemberId int, InvID int, Type varchar(100), Amount money, Tax money, Gross money, ID_Ref int)

    insert into test
    values (1189600, 101102 ,219 ,’Type1′,0.8333 ,20.00 ,1.00, 1189600)
    insert into test
    values (1189601, 101102 ,220 ,’Type2′,100,20.00 ,120.00, 1189601)
    insert into test
    values (1189602, 101102 ,221 ,’Paid : (Type1)’,-0.8333 ,20.00 ,-1.00, 1189600)
    insert into test
    values (1189663, 101102 ,222 ,’Paid : (Type2)’,-75,10.00 ,-90.00, 1189601)

    select * from test

    ID MemberId InvID Type Amount Tax Gross ID_Ref
    1189600 101102 219 Type1 0.8333 20 1 1189600
    1189601 101102 220 Type2 100 20 120 1189601
    1189602 101102 221 Paid : (Type1) -0.8333 20 -1 1189600
    1189663 101102 222 Paid : (Type2) -75 10 -90 1189601

    Required Output (1)
    ID MemberId InvID Type Amount Tax Gross ID_Ref
    1189601 101102 220 Type2 100 20 120 1189601
    1189663 101102 222 Paid : (Type2) -75 10 -90 1189601

    Logic
    if Sum(Gross) group by Id_Ref = 0 then do not get any row.
    if Sum(Gross) group by Id_Ref 0 then get all row.

    Query
    select * from test
    where id_ref in(select id_ref from test
    group by id_ref
    having ABS(Round(SUM(gross),0)) > 0)
    and MemberId = 101102

    Required Output (2)
    ID MemberId InvID Type Amount Tax Gross ID_Ref id_ref RemainingAmount RemainingGross
    1189601 101102 220 Type2 100 20 120 1189601 1189601 25 30

    Logic
    if Sum(Gross) group by Id_Ref = 0 then do not get any row.
    if Sum(Gross) group by ID_Ref 0 then get sum(Amount and Gross) group by Id_Ref, while other fields (i.e. Type, Tax…) when Id = ID_Ref,

    Query
    select * from test
    inner join
    (select id_ref, SUM(Amount) RemainingAmount, SUM(Gross) RemainingGross
    from test
    group by id_ref
    having ABS(Round(SUM(gross),0)) > 0) v1
    on test.id = v1.id_ref
    where MemberId = 101102

    But the problem with this query is “in my data base test is not table, but it is output of one complex query with 4 sub queries”. And ia have enteres that output in one test table.
    in both query written have use of test table 2 times. At that place i need to write my wuery. It becomes very large query. And very difficutl for further changes if required.
    i want same result with use of test table only ones, if possible.

    Thanks
    Pooja

  37. Hi Pinal Dave,

    I have a database with Tables and Stored Procedures( with lots of sql statements).
    Once a week the Windows Task scheduler pulls data from a different source and saves it in a Database table B. I need to compare Table B with existing Table A and if the quantity of Table B is less than Table A, i need to be notified through Email or any other such process.

    How can i do that? Please help me. I could nt get any information from the Net

  38. Hello Pinal Dave,

    Great site. I’ve found lots of valuable information. Keep up the good work!

    I’m curious if you’ve had any experience with renaming servers that are used as replication subscribers. I’m developing a migration plan to a new device. The original is a 24/7 reporting server with a replica copy of a couple other databases. I know I can replicate a publisher to multiple secondaries. Here’s the scenario: ServerA replicates to ServerB. I’m wondering if after adding an additional subscriber to ServerBNEW and letting it catch up if I can take ServerB down, rename ServerBNew to ServerB and have replication continue to function as ServerB. If so can I delete the subscriber entry to ServerBNEW without any impact since it no longer exists? I’m thinking there may be internal GUID pointers attached by server name to the replication processes that may cause problems with this migration scenario since the “new” ServerB is not the original even though it’s the same name on the network. This server also functions as a Log Shipping target so I’ll have a similar issue that those databases.

    Any insight/guidance you can provide would be appreciate. I’m looking for some method to expedite the migration in advance and do some preliminary work to minimize my downtime. Some of the databases are in excess of 275GB and the time necessary to copy the backups or replicate initial copies are exceeding the migration maintenance window.

  39. Hi Pinal Dave,

    I wanted to be more precise about the question i asked yesterday. So her is more information…

    I have a Database with stored procedures and corresponding DTS package and related job (The job has step1 to run the DTS package and step2 -> database email notification when the job ran successfully). This job is scheduled to run once a week.

    Everything works perfectly.

    But I was given a new request that… if the Table B quantity (new table created by importing Excel data ) is smaller than the quantity of existing Table A … then the above job shouldn’t run or the SQL Server Agent should stop the running job and needs to be notified to me or users.

    Where should I add the code/condition …inside a stored procedure or job alerts or …

    I have no clue how to do. Can you please give me a detailed information ?

    Can you please help me?

  40. Hi
    I have a situation where one department inserts data into a table in department database the data would like to inserted into a table in different department database. Whats the best way to do this? Creating a trigger in first database after insert or using webservices to call a function to insert into second database? (by the way I am using .Net and SQL Server 2008)

  41. I installed SqlServer 2008 R2 version but I forgot to select x86 at a time of installation. Now i want that option with X86 selection. So Is there anyway where i can configure and don’t need to setup again or I need to uninstall everything and install again?

    Thanks

  42. Hi Pinal Dave,

    Can you explain in detail the difference between sp_who and sp_who2? How we can use sp_who2 in tracking which query is taking more time for execution?

    Thanks

  43. sir ,
    i m making a mini project …….on student mgmt system
    .net techno as frontend and sqlserver2005 as backend..
    System.Data.SqlClient.SqlException: Cannot open database “student management system” requested by the login. The login failed.
    Login failed for user ‘INTEL\Administrator’.
    i m getting this error while trying to insert data….can u plz tell me the solution plz plz plz

  44. hi sir i need book for sql and pl/sql .can you suggest me which book i should buy.i wanna study for OCA and OCP.
    PLZ…………

  45. In My project Ive to distribute profit to the user on button click..where the user can
    have many investments.But profit has to be distributed the user individually..
    Pinal Sir.Can u help me in sorting out this issue.Please.

  46. Hi Dave ,
    thanks alot for the greate WebSite ,.
    i have litte Question please

    i have a Web application ASP.net 4.0 and connected with MS SQL Server 2008 .The DB is in App_data folder .

    when i run the release version or debug or deploy it and runs the system .i can’t open the Databse from Microsoft Management Tool .

    i habe to close VS2010 completly or restart the mashine ,

    i mean when i write code i must close the Management Tool completly .when i want to select some data from management tool or make DB Changes , i have to close VS 2010 completly .it makes me nervous .because when i forget that . i have to drop the DB and reattach it again .

    that means by asp.net application and 300 users worldwide ?? how will be that managed or monitored ?

    and also something else : i created the DB from Studio manager and then connected to it normally from the Web App .but the DB doesn’t appear in App_Data folder .in this case i can open the database from studio manager and open the web app at the same time and monitor the DB Transactions from SQL Management Tool

  47. i have a column , datatype is datetime it takes default value 1 \1\1901 i want to keep it null when there is nothing in the column .i allow to null to the column.please give the answer as soon as posible.its urgent

  48. Can any one tell me how to select records from .Dat File, For Example Data is coming in that file as follows :

    736 2012-03-01 12:27:35 1 1 0 0
    5016 2012-03-01 18:06:57 1 1 0 0
    5042 2012-03-01 18:42:30 1 1 0 0
    5046 2012-03-01 18:49:13 1 1 0 0
    706 2012-03-01 18:57:25 1 1 0 0
    128 2012-03-01 18:59:06 1 1 0 0
    736 2012-03-01 18:59:09 1 1 0 0
    445 2012-03-01 18:59:12 1 1 0 0
    445 2012-03-01 18:59:14 1 1 0 0

    I did this, But its not working (Select * into #Temp1 From OpenRowSet(Bulk’D:\Data\Abc.dat’)

    Please share it if someOne have any idea about how to query .dat file, Data i mentioned above in that format coming. My email address is (email removed), thnx in advance.awaiting for prompt response.

  49. @Santosh, try this, Isnull(EntryDate,Null) as EntryDate.. Or Save Default Date like this isnull(EntryDate,’01-01-2000′) as EntryDate, When Inserting new record with NULL Value, Replace it with Default Date with Isnull Reference.

  50. Sir,
    i have coded a web app program in vs 2010 and the database is in sql server 2008 r2.
    Could u suggest me how to deploy the web app with the database to the end machine and what would be the system requirement like (.net frmwork 4.0, sql server what version, and more) on the end machine on which the web app will be running.
    Please ignore my language if not correct, as i am a student and learning sql server and vs.
    Any help would be great.

    Thank You

  51. Hi,
    I’m looking for a ms SQ function or SP to convert an RTF field to plain text. so far, what I have found online does not work. Might you be able to assist?

    Thanks,
    Brian

  52. Hi,

    I need help in basic query on joining tables –

    select * from table#1

    101, LETTER1
    202, LETTER2
    302, LETTER3
    402, LETTER4

    select *
    from Table#2

    LETTER1 , Rate1
    LETTER2 , Rate2
    NULL, Rate3

    requirement is the query should fetches the results below -

    LETTER1, Rate1
    LETTER2, Rate2
    LETTER3, Rate3
    LETTER4, Rate3

    I can’t figure out how to do it using joining tables. Tried Left Join but rate column is empty. Any ideas

  53. Hi. I need to know how many maximum number of jobs which can be configured on SQL server 2005,64bit edition . The server has 4GB Ram and DB size is 30GB. Are 18,000 jobs ok for the server?

  54. Hello Sir,

    I am developing a desktop application in C# .net.
    I want to Insert Gujarati data using Stored Procedure.
    Please suggest me any way to do this.

    If possible then please post any example for that.

    Thanks.

  55. Hi Dear Pinal.I have a question about TIMESTAMP type in sql server.

    Is TimeStamp unique in all rows?
    Does it have a unique index on it?
    how much is it’s capacity?
    is it possible that we have OverFlow on TimeStamp Column?

  56. Hi Dave,

    I am a newbie in SQL server and i hve started working on OLAP creating cubes.
    I have created a basic project on ssas as it seems to work fine.

    But when i change the column name on the OLTP it doesnt get automatically updated on my SSAS project.
    As a result “the data source view does not contain a definition for the column”.

    I could fix this if i open the xml code and edit the column name manually.
    Please advise if there is any other way to fix it.

    Thanks in advance

  57. Hi, i’ve this problem.

    Any help me? Jogo 2, 254 and 255.

    CREATE TABLE [dbo].[Jogo](
    [Jogo] [float] NULL,
    [Casa] [float] NULL,
    [Coluna1] [nvarchar](3) NULL,
    [Coluna2] [nvarchar](3) NULL,
    [Coluna3] [nvarchar](3) NULL
    ) ON [PRIMARY]

    2,1,A1:,NULL,NULL
    2,2,A1:,B1:,C1:
    2,3,A1:,NULL,NULL
    2,4,A1:,C1:,NULL
    2,5,A1:,NULL,NULL
    2,6,B1:,C1:,NULL
    2,7,A1:,C1:,NULL
    2,8,B1:,NULL,NULL
    2,9,A1:,NULL,NULL
    2,10,A1:,B1:,C1:
    2,11,A1:,NULL,NULL
    2,12,B1:,C1,NULL
    2,13,A1:,NULL,NULL
    2,14,A1:,C1:,NULL
    2,15,A1:,NULL,NULL
    254,1,B1:,NULL,NULL
    254,2,A1:,B1:,C1:
    254,3,B1:,NULL,NULL
    254,4,A1:,C1:,NULL
    254,5,B1:,C1,NULL
    254,6,C1:,B1:,NULL
    254,7,A1:,C1:,NULL
    254,8,A1:,NULL,NULL
    254,9,A1:,NULL,NULL
    254,10,A1:,B1:,C1:
    254,11,A1:,B1,NULL
    254,12,C1:,NULL,NULL
    254,13,A1:,NULL,NULL
    254,14,A1:,B1:,NULL
    254,15,B1:,C1,NULL
    255,1,C1:,NULL,NULL
    255,2,A1:,B1:,C1:
    255,3,A1:,NULL,NULL
    255,4,A1:,C1:,NULL
    255,5,A1:,NULL,NULL
    255,6,C1:,B1:,NULL
    255,7,A1:,C1:,NULL
    255,8,A1:,NULL,NULL
    255,9,A1:,NULL,NULL
    255,10,A1:,B1:,C1:
    255,11,A1:,NULL,NULL
    255,12,C1:,NULL,NULL
    255,13,A1:,NULL,NULL
    255,14,A1:,C1:,NULL
    255,15,A1:,NULL,NULL

    sample result:

    B1:B1:A1:B1:A1:CA1::B1:A1:A1:B1:A1:B1:A1:B1:A1:

  58. Dear sir …

    i installed sqlserver2005 standard Edition , and we are facing Problem like ….while Restore back up we are getting Errors ” Cannot open backup device ‘D:\common.bak’. Operating system error 2 (The system cannot find the path specified.).
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally

  59. Hello I am trying to upgrade sql 200o dbs tio sql 2008. Is there anyway can you help me guiding on this one? I work in an industrial IT dept. It was really old aapplication db

  60. What is the point of BACKUP LOG option of NORECOVERY ?

    it says Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log back

    But we are going to restore first the database anyway ( from a full backup) with NORECOVERY , then all the transaction log backups in sequence with NORECOVERY and then the tail end of the log with RECOVERY . The NORECOVERY option in the BACKUP LOG seems redundant. Or is it?

  61. Hi, recently I was asked the following question: Given a database, a query returns correct output 99% of the time, however 1% of the time, it returns wrong output. What could be the possible reasons?

    How would you answer on such a question?

  62. I need some help on sql.
    I’m designing project on attendance system. It gives attendance in text file. I’ve stored that file in my database as:

    No. EnrNo Verify InOut DateTime
    1 1 1 0 2012/07/10 08:05:40
    2 2 1 0 2012/07/10 08:30:40
    3 1 1 1 2012/07/10 10:00:00
    4 2 1 1 2012/07/10 10:55:00
    5 1 1 0 2012/07/10 11:05:40
    6 2 1 0 2012/07/10 11:30:40
    7 1 1 1 2012/07/10 14:00:00
    8 2 1 1 2012/07/10 14:55:00
    9 1 1 0 2012/07/10 16:05:40
    10 2 1 0 2012/07/10 16:30:40
    11 1 1 1 2012/07/10 18:00:00
    12 2 1 1 2012/07/10 18:35:00
    13 1 1 0 2012/07/10 19:05:40
    14 2 1 0 2012/07/10 19:30:40
    15 1 1 1 2012/07/10 21:00:00
    16 2 1 1 2012/07/10 21:55:00
    Here In INOut column 0 means In and 1 means Out.
    Every employee should have 4 In’s and 4 Out’s.

    Now I need a database table like these columns: means these above 16 line data should be converted into a table of only 2 rows

    EnrNo In1 Out1 In2 Out2 In3 Out3 In4 Out4

  63. Hi-

    Just something that you might to add to yout knowledge base online (I could not find this when I looked.):

    The Microsoft library says:

    “CONVERT Deterministic unless used with datetime, smalldatetime, or sql_variant. The datetime and smalldatetime data types are deterministic if the style parameter is also specified.”

    The caveat at the end about using the style parameter is not always true. If the CONVERT is to a week, the result is still non-deterministic whether a style parameter is used or not (because it is a fact.)

  64. I have been reading your book SQL Wait Stats

    I have been thrown into the position of the accidental DBA. I have been asked to look into some performance issues with our application. I am getting high numbers in the signal_wait_time_ms column for XE_TIMER_EVENT and also REQUEST_FOR_DEADLOCK_SEARCH. Any help on what those are? Books Online was not much help.

  65. Hi Pinal,

    I have couple of scenarios with temp tables.

    1) I have 2 procedures p1 and p2. p2 calls the proc p1. Both procedures create a temporary table, lets say #t1 with different sets of columns. When p1 tries to insert data into #t1(table in p1), the procedure throws the invalid column name error. It seems to me that the insert statement in child proc(p1) expects the column list as per the the structure of the table in parent proc(p2), but will insert into the child procedure table(cross-checked this by inserting only to the matching column names). Is there any workaround? (Changing temp table name does not seems to easy workaround in either of the procedures as there is huge interdependency for these temp tables)

    2) I have a procedure. I the proc I am trying to create a temp table, then will drop it and will try to create a table with the same name. All these 3 tasks will be done inside the same sproc. But this throws the error “There is already an object named in the database”. This errors occurs when the sproc tried to recreate the table. Any suggestions/workarounds on this?

    Thanks..

  66. Hi,

    I need to convert rtf(RichTextFormat) to Plain text. The [dbo].[udf_StripHTML] converting html to plain text Like that i need to convert rtf to plain text.
    This is sample rtf which contains text as ‘Sample Note’.
    {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}} {\colortbl ;\red0\green128\blue255;} \viewkind4\uc1\pard\i\f0\fs18 Sample\i0\f1\fs17 \cf1\b\f0\fs32 Note\cf0\b0\f1\fs17\par }

    Is it Possible? any help?

  67. hi,
    im darshan.. i need a stored procedure for selecting all the employees whose come across a particular manager and that manager will be having another manager

  68. Hi Pinal,
    This is Raj, i need some help. i want to calculate how many working days are there in a month. but the twist is friday will be off and thursday will be half working day so now i need to find out how many working days are there in a month.

  69. Question : – How to compare two databases present in two different in instances (we can not install any tool in a secure environment so compare databases only by SQL scripts)

  70. Firstly, yours is a brilliant blog, both practically and philosophically. I knew, someday, I would have a question for you.

    Scenario: Investigating performance issue with third party app.
    The app uses multiple databases. It routinely runs queries across databases. In the below example MyTable contains only two records and has no indexes. Why the performance degradation when using three-part naming? The serveradmin has recently been defragging disks and I have theories but I have not been able to pinpoint the issue.

    USE DB1
    SELECT Count(Col1) FROM DB2.dbo.MyTable
    (Duration: 2140)

    USE DB2
    SELECT Count(Col1) FROM DB2.dbo.MyTable
    (Duration: 46)
    SELECT Count(Col1) FROM dbo.MyTable
    (Duration: 15)

  71. Hi sir Good Evening,

    your blog is very useful to me, i have questions
    1. am having the employee table and the columns are name,dob, how can i find who’s birthday will fall on current week.
    2. about nested store procedure with example

  72. Love the blog. Has saved me many hours of head bashing in the past, but not (yet) for the current issue.

    SQL 2005 – Reporting subscriptions are creating Agent jobs with schedules. Both are owned by an account with sysadmin rights. Subscriptions and schedules have no expiration/stop dates. However, none of them are firing and there is nothing in the history to show they even attempted. I do have many entries in the Agent Log that look like this set:

    [000] Job 0x0500B79DFB6A8642A8F60B3DC0C6D735 does not exist in the job cache
    [000] Failed to retrieve job 0xAA2AFCA1156EB64A9D8217344CD9D745 from the server
    [156] Job 0xAA2AFCA1156EB64A9D8217344CD9D745 does not exist in the job cache: attempting to re-acquire it from the server…
    [298] SQLServer Error: 2601, Cannot insert duplicate key row in object ‘dbo.sysjobactivity’ with unique index ‘clust’. [SQLSTATE 23000]
    [298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000]
    [000] Failed to retrieve job 0xAA2AFCA1156EB64A9D8217344CD9D745 from the server
    [000] Refresh of schedule 95736 failed because the parent job (0xAA2AFCA1156EB64A9D8217344CD9D745) was not found in the job cache

    I tracked back through the schedule_id to find the job to run sp_help_job and the content appears to be the dbo.AddEvent command. The dbo.Event is empty and the @EventData guid does not appear in sysjobactivity.

    Please help.

  73. Hi Pinal i wanna to attend your events but how can i get information about the your events (Classes,Presentation,) im working in Accenture. please give me a link where i can get all information about your events.

  74. Hi Pinal,

    In my sp i am storing the result sets into the XML i.e for XML path,root(‘name’).These XML’s are maaped into the Excel by configure the sp and refresh the data through .Net application. it’s Working fine for the past 8 Months.

    Now i got the issue in Production Server i.e. Exec Procedure Values (EXEC ProcedureName) & i execute only existing script in the procedure (After hiding the create procedure ,begin and end comments).

    Values are not matched for both conditions.

  75. Hi Pinal

    I was wondering if you could help me come up with a solution to a problem I’m having.

    In SSMS with SQL Server 2008 R2, in the Object Exporer if I look under any database > Programmability > Functions I can find a list of (all) functions that I assume can be used with SSMS.

    I have looked under INFORMATION_SCHEMA and SYS but I cant seem to find it.

    Do you know of any way to extract all of these functions including their parameters and what they return as this would enable me to create a separate save of this information that I could then use?

    Regards,

    Ivan

  76. I have one backup of SQL 2008 R2 database worth 4 GB. When i restore it, mdf file size will be 13gb and ldf file is 9gb.

    Then i delete 1 cr records from database bu database file was not shrink. So i shrink log file to truncateonly, but when i put the database to shrink it tooks 2 hours but nothing happen.so please can you provide me the solution for it.

  77. Hello Pinal,
    I have a sql database, need to archive the database into filesystem(excel files).

    Is the size of the data will increased or not when i migrate to filesystem?

    I am doing archive through c# and save into files.

    Some of the experts suggested the data of the filesystem is more as compared to the sql data.

    is it right or wrong

  78. Pingback: SQL SERVER – Change Order of Column In Database Tables | JULIANPERRY.CO.UK - IT Expert

  79. Pingback: SQL SERVER – 14 Best Practices for Better Database Performance | JULIANPERRY.CO.UK - IT Expert

  80. Hi Pinal, I have a question regarding the #temp Tables, I am working on Linked Server with read only rights , where I insert my query result into my temp table, the I create many temp tables, I wonder if I forget the names of these temp tables then from where I get these tables and wanted know that where these temp tables gonna resides either in tempdb of my sql server instance or in linked server. I checked the tempdb of both the servers but not able to find the #temp tables that I created.

    Regards,
    Bilal P

  81. Last few months i received error at sql maintenance plan excute time. Below i slow the error details. kindly guide me how to resolve the issue.
    the job failed. The job was invoked by user sa. The last step to run was step 1 (subplan)

  82. Hi Pinal,
    My Table Data as follows:
    Invoice_id Invoice_Number Service_Head Amount
    1 1 AF 1200
    2 1 Excise_Duty 10000
    3 1 Deumorages 1500
    4 2 AF 1500
    5 2 Excise_Duty 12000
    6 2 Deumorages 2000

    I want SQL query to get following Result Set:

    Invoice_Number AF Excise_Duty Deumorages Total
    1 1200 10000 1500 12701
    2 1500 12000 2000 15502

    please suggest me…
    Thanks
    Sekhar C

  83. hello friends i just want to search on first characters of the word in the string in my column like if i have ” great country ” in my db against any record…i just want if i search “gc” it should give me the record with great country…thnks in advance

  84. I would like to trace what parameters are passed into a (multi-line) table function whenever it gets called. It is called from about 50 stored procedures and 20 other table functions. This seems possible at the Stored Procedure level with SQL Profiler, but can’t seem to do this with Table Functions

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