SQL Server Journey with SQL Authority

42 Comments »

42 Responses

1. on November 4, 2011 at 12:25 am | Reply Sakaravarthi J

   Hello sir,

   Explain about HIERARCHYID in sqlserver 2008…
   What is the use?
   How to use it?

   -
   Sakaravarthi J

   
   
2. on November 8, 2011 at 5:10 pm | Reply bhavik

   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

   
   
3. on November 10, 2011 at 2:24 am | Reply Hozefa Unwala

   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.

   
   
4. on November 10, 2011 at 5:08 am | Reply SqlStudent

   Man, you really know a lot of stuff. MS should name you the official SQL Authority in the world.

   
   
5. on November 10, 2011 at 7:06 am | Reply Omarmoreno

   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?

   
   
   • on November 10, 2011 at 6:23 pm | Reply madhivanan

     You can alos make use of uniqueidentifier or timestamp columns for this

     
     
6. on November 10, 2011 at 10:35 pm | Reply Richard

   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.

   
   
7. on November 12, 2011 at 12:17 pm | Reply GRC

   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

   
   
8. on November 15, 2011 at 3:52 am | Reply Anand

   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.

   
   
9. on November 16, 2011 at 3:36 pm | Reply chitra

   sir, how to attach the database in the format of “microsoft sql server query file”.

   
   
10. on November 16, 2011 at 9:38 pm | Reply Hozefa Unwala

    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.

    
    
11. on November 16, 2011 at 11:49 pm | Reply Hozefa Unwala

    I’m looking into the Data Generator Tools and Performance Testing tools for SQL Server.
    Can you please suggest me 1 or 2 options?

    Thanks.

    
    
12. on November 17, 2011 at 1:04 pm | Reply NAGARAJ

    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.

    
    
13. on November 17, 2011 at 1:05 pm | Reply NAGARAJ

    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.

    
    
14. on November 21, 2011 at 3:41 pm | Reply Robert (Poland)

    Hello
    I have to do a MERGE replication that must operate only in one direction to MSSQL 2005 server, I have some indication, but can not cope. Can you help me?
    http://www.replicationanswers.com/MergeChangesArticle1.asp

    
    
15. on November 25, 2011 at 2:47 pm | Reply Rahul Dhoble

    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 ?

    
    
16. on November 28, 2011 at 3:58 pm | Reply SriHari

    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?

    
    
17. on November 28, 2011 at 8:30 pm | Reply Bala Krishna

    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

    
    
18. on November 29, 2011 at 10:32 pm | Reply William

    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

    
    
19. on December 2, 2011 at 2:49 am | Reply Wayne

    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

    
    
20. on December 2, 2011 at 4:23 am | Reply Kamran

    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

    
    
21. on December 4, 2011 at 10:32 am | Reply andrewpetre

    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?

    
    
22. on December 4, 2011 at 7:10 pm | Reply ravimadurishanker Maduri

    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

    
    
23. on December 5, 2011 at 7:32 pm | Reply sasi kiran

    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…………

    
    
24. on December 6, 2011 at 2:22 am | Reply srujana

    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?

    
    
25. on December 6, 2011 at 3:26 am | Reply Nikhil Nishant

    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.

    
    
26. on December 14, 2011 at 12:42 am | Reply omalie

    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

    
    
    • on December 14, 2011 at 1:26 am | Reply omalie

      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

      
      
27. on December 20, 2011 at 11:46 am | Reply sandeep

    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..

    
    
28. on December 20, 2011 at 11:47 am | Reply sandeep

    columnname and settingname both are same in above post

    
    
    • on December 30, 2011 at 2:10 am | Reply sina

      It’s possible that you have ‘ in your insert string. For solve this issue you can covert to base46….

      
      
29. on December 31, 2011 at 5:35 am | Reply Bharat

    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

    
    
30. on January 3, 2012 at 1:43 am | Reply Oluchi Ezem

    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.

    
    
31. on January 4, 2012 at 1:02 pm | Reply amit singh

    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.

    
    
32. on January 5, 2012 at 2:36 am | Reply SUBBAREDDY

    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

    
    
33. on January 9, 2012 at 6:00 pm | Reply Vinay Bommisetty

    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.

    
    
34. on January 11, 2012 at 11:51 am | Reply Manju

    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

    
    
35. on January 12, 2012 at 10:12 pm | Reply Bangaaram

    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

    
    
36. on January 13, 2012 at 10:15 pm | Reply Janel

    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.

    
    
37. on January 19, 2012 at 9:50 am | Reply andrew

    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?

    
    
38. on January 27, 2012 at 2:27 pm | Reply Mohamed Shukri

    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.

    
    
39. on January 29, 2012 at 2:20 am | Reply RM

    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.

    
    

Leave a Reply Cancel reply

Enter your comment here...

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

Email (required) (Not published)

Name (required)

Website

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

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

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

Cancel

Connecting to %s

Notify me of follow-up comments via email.

Notify me of new posts via email.