SQL Server Journey with SQL Authority

249 Comments »

249 Responses

1. on April 8, 2011 at 10:14 am | Reply Jayan

   Hi Pinal!

   I’m a developer from Sri lanka who is a regular reader of your blog and attended your sessions here at Teched and in some of your training courses.
   Can you please let me know whether videos of the sessions you did at TechEd India 2011 are available some where for our reference.

   Thank you.

   Jayan Udayakantha.

   
   
2. on April 9, 2011 at 1:15 am | Reply deepraj singh

   hi pinal ,
   I`am having a transactional with updatable subscription setup on my test servers,where publisher,distributor are on same server and subscriber(pull subscription)is on another server.All changes except ddl changes are reflected on both the servers.
   if we use Gui or Alter statement to add or delete column
   change are not reflected to subscriber and vice-versa
   This command is allowing to make table structure modification
   sp_repladdcolumn @source_object = ‘dbo.ravi’, @column = ‘emp_ecr’
   , @typetext = ‘tinyint’
   , @publication_to_add = ‘tranwithupdate’,
   @force_reinit_subscription = 1
   I have started snapshot agent again after running above command.
   Replicate schema change is set to true
   i have checked replication monitor which shows an error”A DDL change has been replicated”.
   regards
   deepraj

   
   
3. on April 10, 2011 at 6:23 am | Reply Dip

   Hi Pinal,
   I have a strange problem as creating a report in ssrs 2008.
   I have 5 stored procedures and i called them in ssrs in 5 different datasets. I am using 5 different matrix regions and feeding data in each of them through procedures. Each matrix region shows product name in row group an year in column group in all matrix regions. Now the problem is in my third matrix region i have to feed values from first and second matrix regions by year. I know if i have only one matrix region i can use ReportItems!Textboxname to get the value. and if i use ReportItems!Textboxname then it will show me the grand total, i want total by year. but as the values are coming from two different matrix regions and going in third i don’t know what shall i do.

   any idea.

   Thanks in advance.

   
   
4. on April 12, 2011 at 5:49 am | Reply Amir

   Hi
   I am not able to leave a comment on your “Win Windows Phone” post

   
   
   • on April 12, 2011 at 1:41 pm | Reply madhivanan

     Whay? Did you get any error?

     
     
5. on April 12, 2011 at 11:33 am | Reply Pritesh

   How to continue execution of cursor after hitting an error. I tried google, with no success. Is there a way out?

   
   
   • on September 12, 2011 at 7:10 pm | Reply madhivanan

     You can use try catch error handling. Please explain what you are trying to do with cursor. There may be easier way than what you are doing

     
     
6. on April 13, 2011 at 7:43 am | Reply Ashish

   Hi
   I am having a tough time importing Excel 2007 files (xlsx) into SQL Server. Is this supported or am I doing some mistake? What is the correct way of doing this?

   
   
   • on July 14, 2011 at 9:25 pm | Reply Darshan

     Ashish,
     You can use SSIS or OpenRowset to import .xlsx file.

     Thanks
     Darshan Shah

     
     
7. on April 13, 2011 at 9:52 pm | Reply vani

   Hi Pinal,

   I have a strange problem..

   i need to check whether two dates fall in the same month or not? inorder to calculate the number of business days in a week.

   For example:If i give 4/1/2011 as an input my function should return me 1.

   As april 1 started on friday..the number of business days in only 1.

   as of now my function is returning 5 business days for every date I input.

   Any idea is og great help..!!

   My function:

   alter FUNCTION fnGetBusinessDays
   (
   @date datetime
   )
   RETURNS integer
   AS
   BEGIN

   DECLARE @days integer
   DECLARE @weekstart datetime
   declare @weekend datetime

   select @weekstart =DATEADD(dd, -(DATEPART(dw, @date)-1),@date)
   select @weekend=DATEADD(dd, 7-(DATEPART(dw, @date)), @date)

   if month(@weekstart)=month(@weekend)
   begin

   SELECT @days =
   DATEDIFF(dd,@weekstart,@weekend) – DATEDIFF(wk,@weekstart,@weekend) * 2
   - CASE
   WHEN DATENAME(dw, @weekstart) ‘Saturday’ AND DATENAME(dw, @weekend) = ‘Saturday’ THEN 1
   WHEN DATENAME(dw, @weekstart) = ‘Saturday’ AND DATENAME(dw, @weekend) ‘Saturday’ THEN -1
   ELSE 0
   END
   end

   if month(@weekstart) month(@weekend)
   begin

   —need logic here
   end

   RETURN (@days)
   END
   GO

   select [dbo].[fnGetBusinessDays] (’4/1/2011′)

   
   
8. on April 15, 2011 at 12:58 pm | Reply Ram

   Hi

   I need info that I write a stored procedure in which I updated 2 tables and somebody take out the network wire from SQL SERVER system and at this point Rollback cannot happen because the network wire is put out before the rollback statement execute

   So,my question is that how we can avoid this if it occurs accidentially. Is there any code available to check out the connection with SQL server exist or not….It is very important if we are in transactional query.

   Thanks
   Ram

   
   
9. on April 16, 2011 at 3:36 pm | Reply manideep

   Product : Database Services (MSSQLSERVER)

   Product Version (Previous): 4035

   Product Version (Final) :

   Status : Failure

   Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log

   Error Number : 29528

   Error Description : MSP Error: 29528 The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation

   
   
10. on April 18, 2011 at 4:10 am | Reply krishna

    Hi Pinal,

    In clustered server environment (consider having more than 30 cluster server’s)
    Is there any chance to write a Tsql script to find which server is working on which node,i mean if there is any switching of nodes happened
    And generate an alert or report on daily basis with the script

    Thanks,
    Krishna

    
    
11. on April 19, 2011 at 10:39 am | Reply Anton

    Hi Pinal.
    I’ve got the same problem but when I trying to create replication merge over internet on the other server abroad.
    When I want to add new subscriber it cannot connect to 22.215.85.252\HQ1

    It’s says SQL Server replication requires the actual server name to make a connection to the server. connection through a server alias, IP addreess or any other alternate name are not supported. Specify the actual server name

    Could You help me to establish the replication
    I’m really desperate.

    Thanks,

    Anton

    
    
12. on April 19, 2011 at 12:29 pm | Reply Mohan Kumar

    Hi Pinal

    Thanks for your informative articles.

    We need SMS alert through our Web application (DB : Sql Server 2008). When a user clicks any advertisement, a SMS has to be sent to the person who has posted the advertisement.

    I googled, but could not come to any conclusion. It looks like we have to integrate link to some other solution provider. But I’m not sure.

    Kindly let me know how this can be achieved.

    Rgds
    Mohan Kumar VS

    
    
13. on April 19, 2011 at 11:45 pm | Reply sprfrkr

    Hi Pinal,
    I just wanted to write and thank you for the resource you have created here. In managing our SQL server over the last few months, every error or issue resulted in a Google search which led me to this blog and ultimately, the solution. Save us a lot of time! Thank you.

    s

    
    
14. on April 20, 2011 at 10:34 am | Reply Neethu Suresh

    Hi,

    I was looking for a method to get the size of a particular record set- which is obtained as the result of a select query.
    I got a stored procedure ‘sp_spaceused’. But, It returns only the total size of the table.
    can you help me?
    Thanks in advance…
    Neethu

    
    
15. on April 20, 2011 at 2:42 pm | Reply Tim

    Hi Pinal,

    I have a question. I have a import process that runs, the currently creates a stage table, drops the production table, then transfers stage table schema to the production schema.

    To throw a wrench in, sometimes the table definition actually changes. Depending upon the data being imported, the table will change shape. It does a similar process to the above, instead of a drop and create.

    I was wondering, is this the most optimal way of doing this, as to minimize blocking, etc.? When some of these tables are being rebuilt it takes some time.

    Any advice would be great!

    Keep up the good work!

    
    
16. on April 20, 2011 at 6:04 pm | Reply Chintan Gandhi

    Hi Pinal,
    I have a history table with millions of rows. It really isnt used directly in any report but is more of a backup of daily loads on a day-to-day basis. Now due to space issues, I need to delete the data older and 1 month.

    If i run a delete statement, it would take ages (there isnt any index on the table). So I inserted the data i want to keep to another table, delete the original table and then rename the new table.

    However, inserting to another table takes quite long as well so i was wondering if there is a way of inserting without having it logged . Or if there is an altogether better way of doing this. (recovery mode is simple)

    
    
17. on April 21, 2011 at 1:22 pm | Reply suresh

    Hi Pinal,

    To enable to recursive triggers we use the following syntaxt..

    Eg: EXEC sp_dboption ‘database name’, ‘recursive triggers’, ‘true’

    This command allows recursive trigger for the entire database given..,

    my question here is I want enable recursive triggers only for a particular table.., Can we do this in Sql server?

    Waiting for your answer..

    
    
18. on April 21, 2011 at 6:30 pm | Reply DK Chauhan

    HI,
    I have a table having 10 million(++) record , I am updating a flag in this table like is_Uploaded = 1
    Ex:-
    Update tbl_Wage_Request Set is_Uploaded = 1 , Uploaded_By = @UPLOAD_BY, Uploaded_ByType = @UPLOADED_BYTYPE, Uploaded_Date = GetDate() Where Location_Id_Fk = @LOCATION_ID AND WR_Id_Pk IN (SELECT distinct WR_Id_Fk FROM @DUMP_TABLE where WR_Id_Fk 0 )

    This is taking long time to execute ,How can I speed up .Please help me

    
    
19. on April 22, 2011 at 8:12 am | Reply Darryl Mekeel

    Help!!
    Trying to return query from two tables one called (Labor) the other called (Parts) need to return in job format as below they have index of orderID as well as LaborID to join on

    Labor line 1 description etc
    part 1 description etc
    part 2 description etc

    Labor line 2 description etc
    part 1 description etc
    part 2 description etc

    Labor line 3 description etc
    part 1 description etc
    part 2 description etc
    part 3 description etc

    
    
20. on April 22, 2011 at 12:05 pm | Reply abdul

    Hi,

    I am facing the problem to get the record from the database. I am going to show my data in grid view up to 50 records but due to large amount of data it take time. So I want to apply a technique to extract the data which is record and when I use paging the next records comes from database. How it possible and what’s the best way to do that.

    Waiting.

    Abdul.

    
    
21. on April 23, 2011 at 3:10 pm | Reply nirav

    hi pinal ,

    Please Give me Some Example or Practicle About File And FileGroups…
    in Which Conditions File And FileGroup are Useful for Our database?

    
    
22. on April 23, 2011 at 7:52 pm | Reply deepraj singh

    hi nirav,
    when ur database size increases say above 100 gb
    then u shuld add ndf files because due to one mdf file and I\O cost and Cpu time increases whenever there are heavy operations on that mdf file.if it get corrupted or deleted accidently then we lose all that data.therefore for lrge databases data is stored logically in multiple file.a group of multiple files is known as a filegroup

    
    
    • on April 24, 2011 at 11:16 am | Reply Imran Mohammed

      Hello Nirav,

      I want to add few cents to Deepraj’s list,

      Deepraj actually discussed advantages of having multiple files in a database.

      But one point to remember is, you can have multiple files in 1 file group or multiple filegroup.

      Since filegroups provides only logical grouping and not physical, filegroup doesnot add any value in performance improvement or i/o improvement or data redundancy. I mean, You can improve performance of database by creating new files on different physical drives, but you can still put all those new files in 1 filegroup and you will get the same performance as it would be if you would put those new files under different file groups.

      So, below are the advantages for having multiple file groups,

      1. In large databases with ReadWrite and Read Only objects, you can separate out all your objects that need ReadWrite and put them in 1 file group and ReadOnly objects in other file group. There by you can mark 1 filegroup as Readonly and no one can change data in these Read Only objects. Or you can logically classify files of each client in one filegroup if you store data for more than 1 client.

      2. You can take backup of only those filegroups in which you think data is frequently changing. You can aviod taking huge backups of ReadOnly filegroups which will have no data change. Or, You will have a choice of taking backup of those Files Groups that you wanted to take instead of taking backup of whole database.

      One important point to remember is Log file cannot be placed in any of the filegroup. Above method of taking backup’s is only applicable for data files and not to log files.

      ~IM.

      
      
23. on April 25, 2011 at 11:48 am | Reply Anshul Jindal

    Hello Pinal,
    My question is if I am inserting and deleting 5lacs + record from multiple tables in single table so frequently (every 15 min or half hour)

    what it will affect on my server long term as well as short term?

    please help me because i am implementing this logic on my site to boost performance.

    or what are the other alternatives if it is not the right way to boost performance.

    Thanks

    
    
    • on April 26, 2011 at 7:39 pm | Reply madhivanan

      What do you want to do this? Log file will grow rapidly

      
      
24. on April 26, 2011 at 1:14 am | Reply Yashesh Rajyaguru

    Pinal,
    I am unable to use the intelliSense in the sql server 2008.
    Can you please help me with the steps.
    I followied the steps you have mentioned here in the blog: http://blog.sqlauthority.com/2009/03/31/sql-server-2008-intellisense-does-not-work-enable-intellisense/
    However it did not work.
    The setting is exactly how you have mentioned, but cant access.
    Please let me know at your earliest.
    Thanks Pinal
    Yash

    
    
25. on April 27, 2011 at 7:19 pm | Reply husnu

    hi Pinal.
    i need to know how many CPU and RAM needed for an application having daily 10 million hits. i have to configure a system like that. but i have not enough experiment about this subject. How many CPU, RAM is needed for this kind of application?
    i will be waiting for your answer, thanks..

    
    
26. on April 28, 2011 at 8:44 am | Reply Vishal Shinde

    Sir,

    My Name is Vishal Shinde and am working as a System Administrator in a IT company Which is into Datacentre services and Hosting Business.

    I am running through a issue with many of our servers where we use Plesk Control Panels and MSSQL database in the backend. I see that our servers performance goes down due to excessive utilization of server memory by sql whereas the CPU load is upto 0-3 %. so need some suggestion on this to tackle the scenario.

    I am a regular reader of your newsletters though i do not understand the major parts of the SQL Administration as am not at all into it however i try to grasp whatever i can

    Hope you reply at the earliest.

    
    
27. on April 28, 2011 at 9:48 pm | Reply Mark Hernández

    HOLA PINAL.

    MIRA YO COMPARTO TU MISMA PASIÓN POR LA TECNOLOGÍA. SIN EMBARGO HASTA HACE POCO MI MUNDO FUE

    ****ORACLE DB

    ESTOY INICIANDO EN SQLSERVER. NECESITO EXACTAMENTE ME EVANGELICES DESDE 0.0. NECESITO DOMINAR EL TEMA PRONTO.

    ESTOY SUBIDO EN EL TORO YA. NO PUEDO ECHARME ATRÁS.

    UN TRILLON DE GRACIAS.
    Saludos desde El Salvador en Centro América. en América Latina.

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

    Hi Pinal.

    I SHARE YOUR SAME PASSION BY THE TECHNOLOGY.
    NEVERTHELESS UNTIL RECENTLY WAS MY OTHER WORLD

    ****ORACLE DB

    I’M BEGINNING IN SQLSERVER. PLEASE, I NEED EXACTLY YOUR EVANGELIZE ME SINCE 0,0. I NEED TO DOMINATE THE SOON THEME. I AM RISEN IN THE BULL NOW. I CANNOT THROW ME BEHIND

    A TRILLON OF THANKS.

    Greetings from El Salvador in Central American. in Latin American.

    Basic this is urgent.

    Creating user groups and usergroups, revoke and assign permissions.

    Create backup scripts since 0. Incremental, differential and complete for each week

    Review, IDR inconsistencies in all data base.

    Create check list of the base

    *This data bases were never administrated. Is a important Institute from Ingenieria and too, where people of india comming to give trainning.

    Regards,

    Mark

    
    
28. on April 29, 2011 at 11:26 am | Reply VJ

    I love your blog – it’s been very useful every possible way I can imagine – just absolutely useful.

    Though, one thing bothers me a lot – and – it has been bothering me for a long time.

    Don’t you feel like you should update your picture? It’s been there for years. Just saying!!!

    Otherwise, awesome blog…

    
    
29. on May 2, 2011 at 1:01 pm | Reply K.P.Senthil Kumar

    We are having lot of confusions in creating indexes.

    There is a table which contains 731207 records.

    There are 360 columns in this table

    There are 4 Indexes on this table. one clustered index and three non clustered indexes.
    clustered index includes three columns compcode, code, processdate
    nonclustered index 1 includes compcode, processdate, code
    nonclustered index 2 includes compcode, processdate, code, ESI, Settlement Flag
    nonclustered index 3 on ccode

    The compcode contains only one value throughout the table(A0001)
    The code has 69000 different values.
    The processdate contains the date when the record get inserted.

    There is only insertion in this table and not any deletion

    we use to retrieve rows by using different where condition

    1. compcode and code and processdate
    2. compcode and processdate and code
    3. compcode and code and processdate and ………some other columns

    whenever the tables is called through a Stored Procedure it takes to much of time.

    My assumption is creating a clustered index on compcode column is utter waste. since it contains only one value.

    And my recommendation is to create individual nonclustered index on mostly used columns on where condition.

    which would be the best column to create a clustered index and non clustered indexes.

    kindly suggest me.

    
    
30. on May 3, 2011 at 10:56 am | Reply Jacky

    Hi, I want to know Which process ruining (like procedure,function,Cursor) in Sql Server,
    How to know, Some time Some process kill Server Speed

    
    
    • on May 4, 2011 at 8:11 pm | Reply madhivanan

      Run a profiler and monitor the processes

      
      
31. on May 3, 2011 at 11:37 am | Reply Saravanan

    hi Pinal,

    I am novice to SQL server 2008 R2,i would like to know about replicaiton in sql server,also difference between of those replication types,please tell me in which scenario i should i use snapshot,merge and transaction replication,

    also i want to know how should i perform replication using scripts,

    please let me know,

    Thank you in advance

    
    
32. on May 3, 2011 at 11:55 am | Reply Kumar

    Query is : How could we say that the below VIEW follows which type of JOIN by looking at result set or View ?

    CREATE VIEW [Sales].[vSalesPerson]
    AS
    SELECT
    s.[SalesPersonID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,[JobTitle] = e.[Title]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
    ,s.[SalesQuota]
    ,s.[SalesYTD]
    ,s.[SalesLastYear]
    FROM [Sales].[SalesPerson] s
    INNER JOIN [HumanResources].[Employee] e
    ON e.[EmployeeID] = s.[SalesPersonID]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st
    ON st.[TerritoryID] = s.[TerritoryID]
    INNER JOIN [Person].[Contact] c
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea
    ON e.[EmployeeID] = ea.[EmployeeID]
    INNER JOIN [Person].[Address] a
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

    
    
33. on May 3, 2011 at 1:44 pm | Reply Rashed

    hi Dave im regular reader of your blog and i have learnt a lot from ur articles, I have a simple question ” Is there a way to prevent users from viewing the structure of sqldatabase (all objects eg views, tables stored procedures and functions) they just can see with a password or some sort of enceryption please help

    
    
34. on May 4, 2011 at 2:22 am | Reply Jeff

    Pinal Dave,

    Thank you for your numerous posts which I always find so helpful.

    I’m curious if you have any experience with implementing the HierarchyID datatype in Sql Server 2008. I don’t see much around this topic on your site.

    In particular I’m interested if a single table with a HierarchyID column can support the following scenario:

    * Parents with 1 or more children AND…
    * Children with 1 or more (could be 1 or an arbitrary number of) parents

    Regards and thank you for sharing your treasure of DB information with the world.

    - Jeff

    
    
35. on May 4, 2011 at 10:09 am | Reply Sam

    Hi,

    I am newbie to sql server and want to best approach in my scenario.

    I have a table which user updates on daily basis. User need reports for specific dates, how data look at that time.

    What should i use for reporting snapshot, data warehousing ?

    Regards,
    Sam

    
    
36. on May 4, 2011 at 8:44 pm | Reply Steve

    Hello,

    First of all thanks for your all your blogs – it normally happens when search online for a sql problem I get directed to here for the solution

    I am currently trying to figure out the code so that I can see at a glance the counts of dupes across various sources/files. I have a staging table below which for each individual has a sourceid which represents the file that the individual existed in, a sourceurn which is the unique urn within the source and a personid which is the id of the individual that has been generated on the main universe.

    For example I could have:

    SourceId PersonId
    ———– ——————-
    18 58
    23 58
    10 58
    19 58
    19 58
    19 62
    18 62

    So a unique individual on the universe with personid 58 can be found in four different files and in one of those appears twice (I have left out sourceurn col). I would like to see at source level a total of the dupes across the various files. I can produce a pivot table which shows the source count across the top for each personid but can’t seem to work out the code to see a crosstab which ideally has source id down the left and across the top. Any help would be appreciated (there are many records in the table) – thanks.

    
    
37. on May 5, 2011 at 11:46 am | Reply Vishal

    Hi Pinal,
    will you please tell me the use of Sql script…
    I want to know the functionality of SqlScript.
    So kindly reply as erly as possible.

    Thank you.

    
    
38. on May 5, 2011 at 6:03 pm | Reply anwar saleem

    Hi Pinal,

    How can we export some sql server data to DBF file through sql query.

    or

    what is the best way to do this

    Saleem

    
    
39. on May 8, 2011 at 11:38 pm | Reply John

    Hey, Pinal!

    I just want to thank you for your articles! They are very helpful and insightful.

    Good job!

    John

    
    
40. on May 9, 2011 at 11:35 pm | Reply Viren

    hi pinal,
    I am in a situation where i loaded my reporting services sql 2008 r2 in the application server (separate from sql database box). My SQL database server is 2005. Sql Rept services 2008 connects with 2005 just fine but i can’t run reports once loaded in reporting server. It always comming up the exception “can not connect to the data source’ I tried several ways but nothing has changed. this what i did.
    1. I created seperate network account in which web services runs.
    2. same account is reporting services are running.
    3. same account has permission to the reporting database which is located in sql server..
    Here is the exception

    * An error has occurred during report processing. (rsProcessingAborted)
    o Cannot create a connection to data source ‘mydata’. (rsErrorOpeningConnection)
    + For more information about this error navigate to the report server on the local server machine, or enable remote errors

    
    
41. on May 10, 2011 at 12:41 pm | Reply goutham

    good evening,I want real time scenarios for sql server dba.
    like which tool u r using for migration

    
    
42. on May 13, 2011 at 8:32 am | Reply DBA100

    Did any one here run SQL server 2008 on VMware or Hyper-V? any issue experienced?

    
    
43. on May 17, 2011 at 12:23 am | Reply Shail

    I am running into a situation where I need to get gigantic long string using the following scenario.

    I would appreciate if somebody can help me or point me in right direction. I tried to solve this issue using XML/XQuery, but I could not visualize how XML/XQuery would fit into my situation.

    Table-One: It has hundreds of fields with data.
    For example:
    Fname | Lname | Age | Sex | … 250 more columns etc
    Steve | Martin | 32 | M | ….

    Table-Two: It has all fields name from table name as data with some other details.

    coumn_Name | Length | startAt | EndAt
    Fname | 10 | 15 | 24
    Lname | 10 | 25 | 34
    Age | 2 | 5 | 7
    Sex | 1 | 35 | 35

    Now I need to build a output string with value for each row in table-one by rearranging its fileds by reading insturction from table-two (table-two tells where the column should be in string). So in above example I need to build a string where each field falls under appropriate startAt, and endAt. So the output string should looks like below. I have indicated space with dots (…)

    ….32……..Steve…..Martin….M

    On above output string, since age start at 5th position, i have four space before are vaoue. In the same way since FirstName start at 15, I have additional space after age to make 15th place where you see name “Steve”…..

    I hope you see how the control table is manipulating the final output of string that I wanted.

    
    
44. on May 17, 2011 at 5:21 am | Reply Benjamin

    Hi Pinal, i have the following question. I looked over your blog but no answer was found.

    I have a SQL Server table which has the following:
    ————————————————————————-
    Quantity Customer Product
    10 12345 meat
    12 12345 rice
    24 12345 juice
    ————————————————————————-

    What I need to do is to display the information as follows
    ————————————————————————-
    Quantity Customer Product
    46 12345 meat, rice, juice
    ————————————————————————-

    I know how to group and how to sum the quantities but what I don`t know is how to set the products like this:
    ————————————————————————-
    meat, rice, juice.
    ————————————————————————-

    Hope you can help with this, thanks in advance

    
    
    • on May 17, 2011 at 8:28 am | Reply Imran Mohammed

      Benjamin,

      Look at this article,

      http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/

      ~IM.

      
      
    • on May 19, 2011 at 11:21 am | Reply Prashi

      Hi Pinal,

      I am regular reader of your blog.

      Could you please tell that is it possible to keep track on SQL server users that in which table they are performing which operation and on which date and time.

      If yes,how can it be accomplished.

      Thanks in advance….

      Prashi

      
      
45. on May 17, 2011 at 11:14 am | Reply Ravi

    Hi Pinal,

    I want to connect to some database in sql server 2005.
    I have declare the connection string. But in the connection string “User Id value contains the backslash(\) in it”. For this reason I am getting the following error.

    “User login failed for the user “Domain\\ravi”".

    Even I am using escaping sequence also. but still getting the same error.

    can we have backslash in user Id value?

    Could you please assist me on this.

    Thanks
    Ravi

    
    
46. on May 18, 2011 at 9:00 am | Reply Imran Mohammed

    @Ravi,

    Usually backslash comes for Windows Logins.

    When trying to connect to SQL Server through Windows Login, Choose Windows Authentication. You might be using SQL Authentication and then passing User ID, that is wrong.

    When you use Windows Authentication, User ID will be greyed out and will be the user’s ID logged on to OS.

    If this does not help, please provide more information.

    ~ IM.

    
    
47. on May 18, 2011 at 9:35 am | Reply tarak

    hi pinal,

    i have to insert super-script characters in SQL table. so later on, i can read from SQL table for displaying on the ASP.Net website.

    How to insert superscripted characters in SQL table ?

    thanks,
    tarak

    
    
48. on May 18, 2011 at 10:34 am | Reply Jigar Parmar

    I have data as CITY and AREA.
    (1) Each CITY has multiple AREAS
    (2) Each AREA has multiple AREAS (here there is no end, dynamically user can add AREAS under the child AREA like AREA->AREA->Area->AREA….->AREA)

    So , how to design such table structure which satisfy above requirement.

    Thanks in Advance..

    
    
49. on May 18, 2011 at 12:06 pm | Reply Rupesh

    Hi Pinal,

    I am .Net Developer and having fine potential on SQL Server 2005 and 2008.

    I am regular reader of your blog and impressd with your articles. I like to ask question about BI.

    Can u describe me What is the future of BI and how can I explore my skill in BI

    
    
50. on May 18, 2011 at 11:11 pm | Reply Snya

    Hi,
    I cant find an answer of this question, please help me..

    Under what circumstances you would use: record locking, page locking, table locking?

    Thank you

    
    
51. on May 18, 2011 at 11:12 pm | Reply Snya

    Hi,

    What circumstances you would use: table locking, record locking, page locking

    Thank you

    
    
52. on May 19, 2011 at 2:41 am | Reply Shy

    Hi,

    I have a issue with selecting from a linked server.

    I am getting this error:

    OLE DB provider ‘PatOleD2.PatOleDB’ for linked server ‘SV’ returned data that does not match expected data length for column ‘[PatOleD2.PatOleDB].text’. The (maximum) expected data length is 7999, while the returned data length is 26969.

    when I tried to query from a linked server.

    Select name,description,time from OPENQUERY(SV,’select * from dataview’)

    I am able to query if I leave out the desciption field.
    Select name,time from OPENQUERY(SV,’select * from dataview’)

    The funny thing is that this query used to work in MSSQL2000 but has not since the upgrade to MSSQL2008.

    Any help would be appreciated.

    Thanks,

    
    
53. on May 19, 2011 at 2:57 pm | Reply Naina

    Table : TP_SiteLanguageString

    TableName – LanguageString

    PK INDEX KEYS – Code1, Code2, LanguageCode, StringName
    Table COLUMNS :- Code1 Code2 LanguageCode StringName StringValue LastUpdate Updateby Context ContentType Description

    BEGIN
    PRINT ‘Create table TP_SiteLanguageString.’
    EXEC(‘
    CREATE TABLE dbo.TP_SiteLanguageString (
    Code1 char (4) NOT NULL ,
    Code2 char (4) NOT NULL ,
    LanguageCode char (2) NOT NULL ,
    StringName char (65) NOT NULL ,
    StringValue varchar (255) NULL ,
    LastUpdate datetime NULL ,
    Updateby varchar (40) NULL ,
    CONSTRAINT PK_SiteLanguageString PRIMARY KEY CLUSTERED
    (
    Code1
    Code2
    LanguageCode,
    StringName
    )
    )
    ‘)
    END
    ELSE
    PRINT ‘No action. Table TP_SiteLanguageString already exists.’
    GO

    Store PROCEDURE :-

    CREATE PROCEDURE dbo.SearchTool
    @xCode2 CHAR(8) ,
    @DefaultLanguageCode CHAR(2) = NULL ,
    @TranslationLanguageCode CHAR(2) = NULL ,
    @ReferenceLanguageCode CHAR(2) = NULL ,
    @StringName VARCHAR(65) = NULL , –varchar to allow comparison with %
    @ContentType VARCHAR(40) = NULL ,
    @ContextCode VARCHAR(50) = NULL ,
    @StringValue VARCHAR(1000) = NULL ,
    @Category VARCHAR(50) = NULL , — normally TINYINT but needs to be a varchar to allow parsing
    @Admin TINYINT = NULL ,
    @StringState SMALLINT = NULL
    AS
    BEGIN
    DECLARE
    @Code1 CHAR(4) ,
    @Code2 CHAR(4) ,
    @separator CHAR(1) ,
    @INDEX INT ,
    @SLICE VARCHAR(10)

    SET @separator = ‘#’

    EXEC dbo.SplitCode2
    @xCode2 ,
    @Code1 = @Code1 OUTPUT ,
    @Code2 = @Code2 OUTPUT

    – Begin parsing multiple value parameters
    ———————–
    DECLARE @T_Category TABLE ( Category VARCHAR(50) )

    SET @INDEX = 1

    IF @Category IS NOT NULL
    BEGIN
    WHILE @INDEX != 0
    BEGIN
    SELECT
    @INDEX = CHARINDEX(@separator, @Category)
    IF @INDEX != 0
    SELECT
    @SLICE = LEFT(@Category, @INDEX – 1)
    ELSE
    SELECT
    @SLICE = @Category
    INSERT INTO @T_Category
    ( Category )
    VALUES
    ( @SLICE )
    SELECT
    @Category = RIGHT(@Category,
    LEN(@Category) – @INDEX)
    IF LEN(@Category) = 0
    BREAK
    END
    END
    ————————
    DECLARE @T_Context TABLE
    (
    ContextCode VARCHAR(50)
    )

    SET @INDEX = 1

    IF @ContextCode IS NOT NULL
    BEGIN
    WHILE @INDEX != 0
    BEGIN
    SELECT
    @INDEX = CHARINDEX(@separator, @ContextCode)
    IF @INDEX != 0
    SELECT
    @SLICE = LEFT(@ContextCode, @INDEX – 1)
    ELSE
    SELECT
    @SLICE = @ContextCode
    INSERT INTO @T_Context
    ( ContextCode )
    VALUES
    ( @SLICE )
    SELECT
    @ContextCode = RIGHT(@ContextCode,
    LEN(@ContextCode) – @INDEX)
    IF LEN(@ContextCode) = 0
    BREAK
    END
    END
    ———————–
    – end of parsing
    SELECT
    DEFLT.LanguageCode ‘DefaultLanguage’ ,
    DEFLT.StringName ,
    TRANSLAT.StringValue ‘TranslationValue’ ,
    REFERENCE.StringValue ‘ReferenceValue’ ,
    DEFLT.ContentType ,
    DEFLT.Admin ,
    DEFLT.Category ,
    DEFLT.ContextCode ,
    DEFLT.ContentSubType ,
    DEFLT.Description
    FROM
    LanguageString DEFLT — get default language info
    LEFT JOIN LanguageString TRANSLAT
    ON DEFLT.Code1 = TRANSLAT.Code1
    AND DEFLT.Code2 = TRANSLAT.Code2
    AND DEFLT.StringName = TRANSLAT.StringName
    AND DEFLT.ContentType = TRANSLAT.ContentType
    AND DEFLT.ContextCode = TRANSLAT.ContextCode
    AND ( @TranslationLanguageCode IS NULL
    OR TRANSLAT.LanguageCode = @TranslationLanguageCode
    )
    – get reference language info
    LEFT JOIN LanguageString REFERENCE
    ON DEFLT.Code1 = REFERENCE.Code1
    AND DEFLT.Code2 = REFERENCE.Code2
    AND DEFLT.StringName = REFERENCE.StringName
    AND DEFLT.ContentType = REFERENCE.ContentType
    AND DEFLT.ContextCode = REFERENCE.ContextCode
    AND ( @ReferenceLanguageCode IS NULL
    OR REFERENCE.LanguageCode = @ReferenceLanguageCode
    )
    WHERE
    DEFLT.Code1 = @Code1
    AND DEFLT.Code2 = @Code2
    AND ( @DefaultLanguageCode IS NULL
    OR DEFLT.LanguageCode = @DefaultLanguageCode
    )
    AND ( @StringName IS NULL
    OR DEFLT.StringName LIKE @StringName
    ) — so that the call can include % in the parametername
    AND ( @StringValue IS NULL
    OR TRANSLAT.StringValue LIKE ‘%’ + @StringValue + ‘%’
    )
    AND ( @ContentType IS NULL
    OR DEFLT.ContentType = @ContentType
    )
    AND ( @ContextCode IS NULL
    OR DEFLT.ContextCode IN ( SELECT
    ContextCode
    FROM
    @T_Context )
    )
    AND ( @Category IS NULL
    OR DEFLT.Category IN ( SELECT
    Category
    FROM
    @T_Category )
    )
    AND ( @Admin IS NULL
    OR DEFLT.Admin = @Admin
    )
    AND ( @StringState IS NULL
    OR DEFLT.StringState = @StringState
    )

    END

    Hello Pinal,

    Please find above Table and Store Procedure.

    Please let me knw is there any issue with this Store Procedure or best way to write this Store Proc.

    Issue : When we are seraching by value in our application , application will be unresponsive.

    Kindly do the needful.

    Thanks
    Naina

    
    
54. on May 19, 2011 at 8:38 pm | Reply Annam

    Hi,
    I have to tables OrderDetail and OrderDetailReturned and they have 1:n relation respectively.
    I want to create a check constraint on OrderDetailReturned, such that the total (sum) quantity of OrderDetailReturned should not exceed the quantity of OrderDetail for a same OrderDetailID.

    I tried with creating a UDF and referencing the UDF with the checkconstraint but it is giving wrong results in SQL Server 2008.

    Please help me.

    
    
55. on May 19, 2011 at 8:41 pm | Reply Annam

    To Jigar Parmar,
    You have to create a table Area,
    in that you have to have 2 columns for Area, that is…
    Area, ParentArea.

    Then you can use self joins while querying/processing your data.

    
    
56. on May 20, 2011 at 5:56 am | Reply Robin Waner

    Hi Pinal Dave!

    I am currently training to use the more advanced features of SQL Server using my home laptop. I’m a bit of a stickler for saving memory when applications are not used so what i’ve done is when I want to run Management Studio, I run a batch file to load all of the relevant SQL related windows services before Management Studio is loaded. This way, the services are placed on a manual startup so not loaded automatically – thereby conserving memory. I have noticed something a little odd however….

    When I place the SQLWriter service into manual startup, it adheres to its new status – however, upon activating the services and loading SSMS, this service automatically changes itself to “Automatic” startup. It only seems to affect this particular service and not the others relevant to SQL Server. Any idea as to why this is?

    Thanks.

    
    
57. on May 20, 2011 at 11:04 am | Reply Imran Mohammed

    @Annam,

    After 2.5 Hours of analysis, I found out solution for your answer.

    You might have done the same mistake what I did in when creating Function to be used in Check Constraint.

    I Initially was checking if the sum of Quantity present in OrderReturnTable for that order + the Quantity to be added in Insert Statement is greater than Quantity in Order Table, If greater than fail the insert record, if not consider it as success, WHICH IS NOT RIGHT.

    When Validating Data through Check Constraint, This is what SQL Server does Internally
    1. First Inserts the data in the table,
    2. Then Checks for the condition using Check Constraint.

    You can see this using display Actual Execution Plan.

    So, by this time, there is a record already present in the table, when Check Constraint is validating and uses a UDF, that UDF is adding new Quantity once again which is already added by Insert Statement. That is why the Check Constraint was failing for valid quantities too.

    So, Solution is, In UDF, you should not consider the new quantity, that was added already to the table by Insert Statement.

    So, below is your sample script.

    USE tempdb
    GO
    If exists (Select 1 from tempdb.sys.objects where name = ‘Temp_Tbl_OrderDetail’)
    drop table Temp_Tbl_OrderDetail
    GO

    Create Table Temp_Tbl_OrderDetail (OrderID int, Quantity int)

    GO

    Insert into Temp_Tbl_OrderDetail values ( 1, 100)
    Insert into Temp_Tbl_OrderDetail values ( 2, 150)

    GO

    If exists (Select 1 from tempdb.sys.objects where name = ‘Temp_Tbl_OrderDetailReturned’)
    drop table Temp_Tbl_OrderDetailReturned
    GO
    Create table Temp_Tbl_OrderDetailReturned (OrderReturnedID int Identity (1,1), OrderID int, Quantity int )

    GO
    if exists (select 1 from tempdb.sys.all_objects where name = ‘Ufn_Validate_Quantity ‘ and schema_name(schema_id) = ‘dbo’ and type = ‘FN’ )
    drop function dbo.Ufn_Validate_Quantity

    GO
    /* This is the main function to Validate Quantity*/
    CREATE function dbo.Ufn_Validate_Quantity (@OrderID int,@Quantity int )
    Returns bit
    AS
    Begin
    –SET NOCOUNT ON
    Declare @Total_Returned_Qty_Until_Now int
    , @Original_Qty int
    ,@Return_Value bit

    Select @Total_Returned_Qty_Until_Now = ISNULL(SUM(Quantity),0)
    From Temp_Tbl_OrderDetailReturned
    Where OrderID = @OrderID

    select @Original_Qty = ISNUll(Quantity,0)
    From Temp_Tbl_OrderDetail
    Where OrderID = @OrderID

    If (@Original_Qty >= (@Total_Returned_Qty_Until_Now) )– True Condition
    /* MOST IMPORTANT: THIS INCLUDES VALUE YOU ARE TRYING TO VALIDATE*/
    /* Earlier I was using Condition If (@Original_Qty >= (@Total_Returned_Qty_Until_Now + @Quantity) ) as True Condition, This is wrong, because Quantity has already been added by Insert Statement when SQL Server execute this statement*/
    Set @Return_Value = 1

    Else
    Set @Return_Value = 0 — False Condition

    Return(@Return_Value)
    End

    GO

    ALTER TABLE Temp_Tbl_OrderDetailReturned
    ADD CONSTRAINT CK1_Temp_Tbl_OrderDetailReturned CHECK ((dbo.Ufn_Validate_Quantity (OrderID ,Quantity ))=1 )

    GO

    Select * from Temp_Tbl_OrderDetail
    GO
    SELECT * from Temp_Tbl_OrderDetailReturned
    GO

    – Testing Check Constraint

    Insert into Temp_Tbl_OrderDetailReturned (OrderID ,Quantity )Values (1, 45) — Record will be inserted :Sum = 45 Which is less than 100
    SELECT * from Temp_Tbl_OrderDetailReturned
    GO

    Insert into Temp_Tbl_OrderDetailReturned (OrderID ,Quantity )Values (1, 20) — Record will be inserted : Sum = 45+20 = 65 Which is less than 100
    SELECT * from Temp_Tbl_OrderDetailReturned
    GO

    Insert into Temp_Tbl_OrderDetailReturned (OrderID ,Quantity )Values (1, 20) — Record will be inserted : Sum = 65+20 = 85 Which is less than 100
    SELECT * from Temp_Tbl_OrderDetailReturned
    GO
    Insert into Temp_Tbl_OrderDetailReturned (OrderID ,Quantity )Values (1, 20) — Record will be inserted : Sum = 85+20 = 105 Which is greater than 100
    SELECT * from Temp_Tbl_OrderDetailReturned
    GO

    /*
    Error Message : Msg 547, Level 16, State 0, Line 2
    The INSERT statement conflicted with the CHECK constraint “CK1_Temp_Tbl_OrderDetailReturned”. The conflict occurred in database “tempdb”, table “dbo.Temp_Tbl_OrderDetailReturned”.
    The statement has been terminated.
    */

    /* CLEAN UP */
    Drop table Temp_Tbl_OrderDetail, Temp_Tbl_OrderDetailReturned

    One important note I would like to make here is, Check Constraint can validate data only when new data is inserted into the table. Check Constraint WILL NOT VALIDATE data when you update values in table. In that case you are left with 1 choice (Validating through Triggers).

    ~IM.

    
    
58. on May 20, 2011 at 1:28 pm | Reply DBA100

    Dear all DB experts,

    We are now doing DR drill on SQL server 2008 cluster, but after we failover we found out that some query need to use the linked server create problem as the remote server name is HARDED CODED. That remote server in the primary site supposed to be disappeared.

    The SQL CODE running at the problem is:
    CREATE view [dbo].[cap_baoa_status] as
    SELECT
    aob.betting_account_number
    ,baoa.bank_code
    ,baoa.bank_account_no
    ,CASE baoa.correspondence_language
    WHEN ‘en-us’ THEN ‘English’
    WHEN ‘zh-hk’ THEN ‘Traditional Chinese’
    END AS correspondence_language
    ,baoa.baoa_creation_business_date
    ,baoa.baoa_createion_datetime
    FROM [STCUSTDB\CUSTDB].[cust_db].[dbo].[arrangement_on_betting] aob
    INNER JOIN [STCUSTDB\CUSTDB].[cust_db].[dbo].[arrangement_on_betting_baoa] baoa
    ON baoa.arrangement_on_betting_id = aob.arrangement_identifier
    WHERE DATEDIFF(month,baoa.baoa_createion_datetime ,GETDATE()) < 3

    As far as we know, we try to user the @@servername to find out the current running server (DR SQL server) then try to use logic like IF and ELSE to give the a new name to a server in order to replace to character "[STCUSTDB\CUSTDB]', which is the linked server, to point to a local database with the same data.

    But this is a view and we can't include if and else into it, then how can we fix this kind of problem in order to transpanent to the application level.

    I can only think of changing DNS to make sure that if the code is asking for the server name, it just gives the changed IP address, then it will be done.

    Other question, when we are doing SQL server 2005 cluster, we can have the primary SQL server 2005 cluser server name resourece offline and then DR SQL server 2005 cluster name resource with the same name online, this can fix the shit as the server name will be the same and no coding change, right? someone tell me that SQL server 2008 can't do this as MS has confirm that this is not going to work in 2008 as this will give other BIG problem, rigjht? any link to verify this ?

    Please share how you guys handle this situation, if the same cluister DR method works find in SQL 2008, then this problem solved.

    DBA100.

    
    
59. on May 20, 2011 at 3:10 pm | Reply Simon

    Hi all DB experts,
    Like to get some advise on following issue on senerio;

    Application and SQL in one server process time is 1 hour.

    When Application and SQL separate in two servers, process time double to 2 hour.
    Hardware spec of two servers is the same, on same subnet, same network setting, full duplex, same speed.

    Management want explaination why time double and want me to reduce back to 1 hour.

    Like to find out the following,
    1) Will process time double once its separated (is this normal)?
    2) If this is normal, any links or url i can refer to, to show my management?
    3) What things or steps i do to improve process time?

    ~Simon

    
    
60. on May 20, 2011 at 6:23 pm | Reply vetri

    Dear Sir,

    I need query for select values between from date and to Date

    My Table
    ———–

    Create Table ClientInfo (
    clientname nvarchar(50),
    country nvarchar(50)
    arrivaldate datetime,
    departuredate datetime)

    I need Following Output
    ——————————-
    SELECT * FROM CLIENTINFO
    between ‘ARRIVALDATE’ and ‘DEPARTUREDATE’

    
    
61. on May 20, 2011 at 11:13 pm | Reply Neeraj

    Hi,

    I am stuck in a problem and need your advice on how to get out of it. I would really appreciate your help in this regard.

    I have a desktop application which reads the huge data stored in XML column. I would like to distribute this application to other users, but SQL SERVER Express edition 2005 does not allow FULLTEXT, and to enable fulltext search I dont want to install 250MB installer of SQL Express edition with Advanced service.

    I switched to XML and indexed it for PATH but it increased the size of my database to 5.8GB, which surpassed the limit of SQL Express edition 2005 of 4GB. Currently my problem is that I can not upgrade my development environment.

    Do you have any suggestion for me to solve my problem in SQL 2005? I would like to enable FREETEXT or XQUERY search on my database.

    Please help. Your help will be greatly appreciated.

    -Thanks-

    
    
62. on May 23, 2011 at 9:23 pm | Reply Mike

    Hi Pinal,

    How would I export the data from the stored procedure below as a fixed width text file to a specfic location? Thanks
    USE [OCCLICENSE]
    GO
    /****** Object: StoredProcedure [dbo].[SP_OCCLICMAM] Script Date: 05/23/2011 11:09:30 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    CREATE PROCEDURE [dbo].[SP_OCCLICMAM]
    AS
    SET NOCOUNT ON
    –Create a MAM file of all Account Number that are not status 3 mjm 5-23-2011
    –select distinct ac.account_number
    –SELECT TOP 100 RIGHT(’0000000000′ –Use for testing returns top 100 records
    SELECT RIGHT(’0000000000′
    + CAST(account_number AS VARCHAR(10)), 10) Paddedaccount_number
    –select distinct ac.account_number
    from account ac
    where ac.status not in (3)
    order by ac.account_number

    
    
    • on May 24, 2011 at 3:25 pm | Reply madhivanan

      Read about bcp in SQL Server help file

      
      
63. on May 24, 2011 at 12:22 pm | Reply Namit Jain

    Hi Pinal,

    I want to convert Query Output from Sqlserver Default Language to French.

    Pls. suggest.

    Thanks & Regards
    Namit Jain

    
    
    • on May 24, 2011 at 2:54 pm | Reply madhivanan

      Did you mean that the text returned from QA should be in french?

      
      
64. on May 24, 2011 at 8:55 pm | Reply Namit Jain

    Hi,

    Yes..

    Thanks & Regards
    Namit Jain

    
    
65. on May 25, 2011 at 6:57 am | Reply bc6really

    Hi Pinal,

    I have a rather unique situation that I am hoping you have stumbled accross. I have a user that has to have the sa account because the a third party application requires it, which really is a thorn in my side. Anyway, the user detached the database one night and made a copy of the database. Is there anyway to lock down the sa account or at least audit detaching of a database?

    Thanks!
    John

    
    
66. on May 25, 2011 at 3:59 pm | Reply Aarti

    Hi Pinal,

    I learned lot of things from you in SQL server

    Now i really wanna to learn SSIS but i don’t have any idea about SSIS and also don’t know how to start it

    Can u plz help me

    Thanks in advance

    
    
67. on May 26, 2011 at 4:02 pm | Reply ABHILASH

    Hi Pinal,
    I want to get result from joining of two table,
    But some times first table can be null..that it can have null values in all columns…
    Can you please let me know the solution how to get the result if table 1 is null table 2 is having values.
    which join to use?

    Regards,
    Abhilash

    
    
68. on May 27, 2011 at 1:37 am | Reply Eddie Dressler

    Dave,

    What books, articles, websites do you recommend for learning about SSAS 2008 advanced topics?

    Thanks,

    Eddie

    
    
69. on May 27, 2011 at 2:25 am | Reply Adolfo Avalos

    Hi Pinal,

    I have a question, and I would like to know if is possible to solve this problem.

    I want to create a horizontal table from a hierarchy that I have stored this way.

    ID | Article | Parent | Level
    1, Resources, 0, 0
    2, Soap, 1, 1
    3, XDS, 2, 2
    4, nivea, 3, 3
    5, pro, 4, 4
    6, Shoes, 1, 1
    7, Nke, 6, 2

    Starting from the last level of the hierarchy to build the table or view that displays the information in this way

    ID | Article | 1 | 2 | 3 | 4
    4, pro, nivea, XDS, Soap, Resources
    7, Nke, Shoes, Resources

    It’s possible to do this?

    
    
70. on May 27, 2011 at 8:39 pm | Reply Himanshu Jani

    Hi Pinal,
    I have created a very complex SP to insert data into many many tables now I have a situation here wherein the database name will be dynamic. I mean user will decide in which database they want to execute this SP so that tables (around 25) in that database will be populated.
    Now there are more than 200 databases I am talking about, all same structure storing data logically grouped, and new databases will be added on demand. If I create this SP in all the databases it would be extreme pain of maintaining this SP if I have to change something in SP.
    Can you please suggest best way out here?
    Creating a SP in one central database that takes dbname as parameter and having dynamic SQLs would be debugging nightmare.

    Thanks v much and best regards
    Himanshu Jani

    
    
71. on May 31, 2011 at 12:47 pm | Reply Zulfiqar Langah

    We are creating a database where we store large number of records. We estimate millions (billions after few years) of record in one table and we always INSERT and rarely UPDATE or DELETE any of the record. Its a kind of archive system where we insert historic record on daily basis. We will generate different sort of reports on this historic record on user request so we’ve some concerns and require technical input from you people:
    - What is the best way to manage this kind of table and database?
    - What impact we may see in future for very large table? Is there any limitation on number of records in one table or size of table?
    - How we suppose to INSERT bulk record from different sources (mostly from Excel sheet)?
    - What is the best way to index large data tables?

    
    
72. on May 31, 2011 at 2:51 pm | Reply Satish Menon

    I have taken a complete backup of database “NiAccDb11″ from SQL Management Studio.

    When I try to restore backup to the same database on another server, I get the following error message

    Restore failed for Server “SRV-NEWINDIA” (Microsoft.SqlServer.SmoExteded)

    Additional Information:

    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing “NiAccdb11″ database. (Micorsoft.SqlServer.Smo)

    How can I overcome this error message.

    
    
    • on May 31, 2011 at 11:32 pm | Reply deepraj

      Hi satish,
      you must restore backup on different path.Backup of old database carries path of old server while restoring change the path where ur default database files are located on new server.

      U might be doing restoration on same machine?

      check for default datafiles path in sql .
      connect to sql ->right click on name of sql->properties->general->root directory.copy that path and
      check where datafiles re present .use that path while restoring.

      
      
73. on May 31, 2011 at 11:43 pm | Reply Satish Menon

    Can I schedule to execute Copy Database function to transfer data from one server to another when the source database is in use. During this process will the system be slow and affect the users in any way.

    
    
74. on June 1, 2011 at 2:02 pm | Reply Ravi

    Hello Sir,

    I am facing one problem with asp web application.We have hosted database and asp web application on two different servers.
    Few days ago the application was working fine.But for last few days we are experiencing unusual error.

    ——————————————————–

    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)
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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)

    Source Error:

    Line 29: using (SqlConnection scon = hcon.SetConnection())
    Line 30: {
    Line 31: scon.Open();
    Line 32: SqlCommand cmdRole = new SqlCommand(“ShowDesignation”, scon);
    Line 33: cmdRole.CommandType = CommandType.StoredProcedure;

    Source File: d:\hosting\xxxxxx.aspx.cs Line: 31

    Stack Trace:

    [SqlException (0x80131904): 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)]
    System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1019
    System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +108
    System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +126
    System.Data.SqlClient.SqlConnection.Open() +125
    MPLogin.showRole() in d:\hosting\7939933\html\MPLogin.aspx.cs:31
    MPLogin.Page_Load(Object sender, EventArgs e) in d:\hosting\7939933\html\MPLogin.aspx.cs:19
    System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
    System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
    System.Web.UI.Control.OnLoad(EventArgs e) +91
    System.Web.UI.Control.LoadRecursive() +74
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

    
    
    • on June 2, 2011 at 6:44 am | Reply Imran Mohammed

      @Ravi,

      Try Checking this link,

      http://blog.sqlauthority.com/2008/08/24/sql-server-fix-error-40-could-not-open-a-connection-to-sql-server-fix-connection-problems-of-sql-server/

      This link has already been discussed many times earlier.

      If that does not solves your problem. Please let us know.

      ~ IM.

      
      
75. on June 1, 2011 at 8:01 pm | Reply Vijay Babu N

    Hi Pinal,

    I have a doubt on nvarchar(max) when it is used with the sp_executesql.
    A variable @SQL_Str is declared with nvarchar(max). The string is terminated once it crosses 8000. In the SQL Tutorials help it is found that 2^31-1 bytes can be used for the large value data types. How can i use this 2^31 -1 byte and add more code to the dynamic sql string? How could i add more than 8000 bytes of unicode characters?

    
    
    • on June 2, 2011 at 6:28 am | Reply Imran Mohammed

      Helo Vijay,

      Try Google before posting your question….

      Check this weblink,

      http://www.sqlservercentral.com/Forums/Topic334104-8-1.aspx#bm334403

      ~ IM.

      
      
76. on June 2, 2011 at 3:18 pm | Reply Deepak

    Hi,

    When i am tryining to open table in SQL server 2005 at design time it gives error it can not display data which table have more than 655 columns.Please help me to resolve this issue.

    Thanks and regards
    Deepak Gupta

    
    
77. on June 3, 2011 at 12:15 pm | Reply Ashish

    Hi Pinal

    I have table with XML data type column. I wated to encrypt the XML data using Symmetric key encryption method. Please guide how can I perform this task.

    Ashish

    
    
78. on June 3, 2011 at 1:37 pm | Reply AlexC

    Hi Pinal,

    I would just like to thank you for your excellent ‘documentation’. I’ve recently become a lot more involved in SQL, and whenever I Googled for a solution to my problems, and you have done something simpler, your answer to the problem has always been more simple and elegant than any other I could find on the net.

    Keep up the great work and keep on inspiring others !

    Regards,

    AlexC

    
    
79. on June 3, 2011 at 6:53 pm | Reply Pilar Sastre

    Hi Pinal

    I’m trying to change a folder’s attribute through Scripting.FilesystemObject. I want the folder to be hidden. The spcript I wrote to accomplish this is not doing it, can you please help me with this.
    I want the “Customer” folder to be hidden.
    Here is the script:

    DECLARE @sPath sysname, @fso int
    DECLARE @OLEResult int, @folder AS int

    SET @sPath= ‘A:\inetpub\wwwroot\HERC\AFCEEDocuments\TO-0013\Submittals\SUB_1389\Customer\’
    – Create Scripting.FilesystemObject
    EXEC @OLEResult = sp_OACreate ‘Scripting.FilesystemObject’, @fso OUTPUT
    EXECUTE @OLEResult = sp_OAMethod @fso, ‘GetFolder’, @folder OUT, @sPath
    EXECUTE @OLEResult = sp_OASetProperty @folder, ‘Attributes’, 2
    EXEC @OLEResult = master.dbo.sp_OADestroy @fso

    
    
80. on June 4, 2011 at 12:25 am | Reply Bruce

    I am unable to connect to a Database after opening SMSS. The locally supplied credentials for the server instance using Windows Authentication are failing with error code 18456. The password field is grayed out and it appears that a blank password is being sent to sqlweb.exe. While it is possible to open the SMSS console from a command line, I still can’t attach to the Database one SMSS opens. Any thoughts on this would be helpful.

    
    
81. on June 4, 2011 at 11:35 am | Reply SQL SERVER – 14 Best Practices for Better Database Performance

    [...] SQLAuthority [!] Report this post Processing your request, Please wait…. [...]

    
    
82. on June 5, 2011 at 7:08 pm | Reply Hubert J. Valcke

    HI,

    I am new in SQL since only some months. My application is generic and use one different database for each user loged in the apllication.

    A1, B1, C1, D1, … are existing Databases.
    @DBID is the scalar variable contening the name of the database of the loged user.

    – Sample 1 is not working –
    use @DBID

    – Sample 2 is not working –
    declare @DBID nvarchar ( 100 )
    set @DBID = ‘A1′ — This value come from elsewhere
    select * from @DBID.DBO.TABLES — is not working

    – Sample 3 is not working –
    declare @NOM_DB nvarchar ( 100 )
    declare @CMD nvarchar ( 100 )
    declare @VALEUR nvarchar ( 100 )
    set @NOM_DB = ‘A1.DBO.DIRECTORIES’ — La valeur provient d’ailleurs
    set @VALEUR = ”’LOGO”’ — ou set @VALEUR = “‘LOGO’”
    set @cmd = ‘select * from ‘ +@NOM_DB + ‘ where ITEMS=’ + @VALEUR
    exec (@cmd )

    What is the good way to do ?

    Thanks, regards

    Hubert from Montreal, Quebec

    
    
83. on June 5, 2011 at 8:07 pm | Reply Shibu P

    Friends,
    I have this query,
    DECLARE @EmpCode VARCHAR(10)
    DECLARE @WeekEnd VARCHAR(10)
    SET @EmpCode = ’201300′
    SET @WeekEnd = ’20110603′–GETDATE()

    SELECT cMenuOrder, MenuLevel, EMPCODE, EMPNAME
    FROM [dbo].[ReleaseTrans05_ufn] (@EmpCode, @WeekEnd)
    ORDER BY cMenuOrder
    ————–
    The output I get is
    ————–
    cMenuOrder| MenuLevel| EMPCODE| EMPNAME
    0023| 1| 0023| ABC
    0023.0021| 2| 0021| DEF
    0023.0021.0020| 3| 0020| GHI
    0023.0021.0019| 3| 0019| JKL
    0023.0018| 2| 0018| MNO
    0023.0018.0022| 3| 0022| PQR
    0023.0018.0017| 3| 0017| STU
    ———————————–
    I like to report this as an organization chart. Or a hierarchical manner connected by lines.

    I have placed this information in a matrix, but finding it difficult to implement a drill down with the Menu Level.
    Please help me with this report,
    Regards
    Shibu P

    
    
84. on June 7, 2011 at 8:54 pm | Reply Cory

    Hi Pinal,

    I just wanted to post a great big thank you! I’ve not yet had an issue with SQL Server, that I couldn’t find the answer to on your site/blog. You’ve been a great service to myself and my other 2 developers and I just wanted to extend my appreciation for your site. Keep it up!

    
    
85. on June 8, 2011 at 4:16 am | Reply Jim Clawson

    Mr. Pinal,
    Thank you for your blog. It has been of great worth and value as I have learned how to use SQL Server. I appreciate your exceptional talent, not only as a developer, but as a communicator and teacher as well.

    Jim

    
    
86. on June 8, 2011 at 8:49 pm | Reply Joe

    Do you have any advice for SQL Server refactoring?
    I need to rename various tables and columns in my DB and want to know that the change has been propagated throughout all of the dependent SPROCS.

    Thank you,

    Joe

    
    
87. on June 9, 2011 at 9:05 pm | Reply Cory

    Pinal,

    I am looking for a good resource that has a wealth of information in regards to creating a SQL Server farm. My need is to have multiple servers that share in the load balance of client requests in order to balance performance. It seems easy to find solutions for high availability and active/passive solutions for failover, but not for active/active/active, etc. type solutions. Any help in pointing me to information would be greatly appreciated.

    Thanks,
    Cory

    
    
88. on June 10, 2011 at 12:37 am | Reply DarlingJen

    Hello,

    I am trying to use the AdventureWorks2008 sample OLTP database and am getting an error that I am missing the .ldf (log) files.

    Any suggestions?

    Jennifer

    
    
89. on June 11, 2011 at 1:43 am | Reply Luc

    Hallo Pinal,
    I like to know some ideas to make a query that can give me the available rooms.
    Each room has a unique number. In the database there are a reservationStartDate and a reservationEndDate, RoomID, ReservationID. With a StartReservationDate and an EndReservationDate , I like to know which rooms are available.
    Many thanks in advance for the advice and suggestion

    Luc

    
    
90. on June 11, 2011 at 2:47 am | Reply mike

    Hello,
    I am trying to flag records in SQL server 2005, depending upon whether the previous record is decremented by 1. Since SQL does not have a lag operator, how would I go about this?

    I have tried using the ROW_NUM() OVER() functions, but am not too familiar with them, so am a bit stumped.

    TIA
    Mike

    
    
91. on June 11, 2011 at 3:01 am | Reply mike

    Hello,
    I am trying to flag records in SQL server 2005, depending upon whether a value in the previous record is decremented by 1. For example, “Month” can be consecutive in 10 related records with “Month” values: 1,2,3,4,7,8,9,11,12. I want to flag these related records with “Month”=7 & 11. Since SQL does not have a lag operator, how would I go about this without flagging the “Month”=1?

    I have tried using the ROW_NUM() OVER() functions, but am not too familiar with them, so am a bit stumped.

    TIA
    Mike

    
    
92. on June 11, 2011 at 4:26 pm | Reply Hiran

    Hi,

    I have a requirement of installing of SQL server from an custom installer (means my own installer). I have created a setup project (.vdproj using Visual Studio), added SQL2008sp1.exe as resource. When my installer is being installed, in my custom actions am extracting the SQL exe to some temp location and through a batch file am trying to install SQL.
    But its not sucessful as SQL.exe inturn will execute some MSI’s and parallel execution of MSI’s is not allowed by default. There is a specific requirement for me to get it execured through MSI.

    Is there any posibility of doing this? or can i find an MSI installer for SQL2008sp1( i.e something like SQL2008SP1.msi????)

    
    
93. on June 12, 2011 at 12:13 pm | Reply Jabbar

    Hi I’m developing a .Net utility that works with log shipping to maintain a stand by server. The primary server may be a simple database server, a publisher or a subscriber. When i wanna to do log shipping for publisher i need to backup my publisher, msdb & master DB, i have done all that but when going to activate my secondary server as publisher i have to restore master DB, which involve following steps,
    1) Run SQL Server in Single User Mode
    2) Run SQL command prompt to restore master DB.
    I want to do above two steps through my .Net utility. Is there any way using SQL Queries to switch SQL between single & multiple user Mode and also SQL commands for restoring master DB via programming or SQL Query analyzer instead of sql command prompt.
    Thank you

    
    
94. on June 12, 2011 at 7:58 pm | Reply Anshu Shikha Mehtani

    Hi,

    I want to know how can i generate a list which displays a ‘male row’
    followed by a ‘female row’ followed by a ‘male row’ etc…if i have one table employee that contains Column as name, salary, gender etc. I want to do it using CTE or row_number() function. Can you please help me??

    
    
    • on June 14, 2011 at 7:09 pm | Reply madhivanan

      select * from
      (
      select row_number() over (order by gender) as sno, * from table
      ) as t
      order by sno%2

      
      
95. on June 14, 2011 at 3:22 pm | Reply Sonam

    Hi,
    I have a query.
    Having a table with the following values:

    Id Name City
    1 name1 city1,st1
    2 name2 city2,st2
    3 name3687 city1,st3
    4 name4568 city4,st4
    5 name5 city5,st5
    6 name6 city4,st7
    7 name7 city7,st7
    8 name8 city8,st8

    I want to select distinct city records

    
    
    • on June 14, 2011 at 6:41 pm | Reply madhivanan

      You need to post the expected result

      
      
96. on June 14, 2011 at 6:46 pm | Reply birju shah

    how to select top two salaries from every department in emp and dept tables using mysql?

    
    
    • on June 19, 2011 at 2:12 pm | Reply Birju Shah

      i have a query here which worked but i don’t understand how….please help me

      mysql> select * from emp a
      where 2>(select count( distinct(sal))from emp where sal>a.sal and a.deptno=deptno )
      order by deptno;

      it is the query to find two highest salaries in every department

      
      
97. on June 16, 2011 at 10:16 pm | Reply shamim

    Hello,
    by using the following command prompt
    NET STOP MSSQLSERVER
    NET START MSSQLSERVER
    I can stop and start my sql server only whenever I log on with the user of Administrator. But my Pc has another local user which is accountant.I face a problem when log on with the accountant user which has no administrator privilege. During this time in SQL server service manager, the server name displays blank and the server could not be started automatically. In this situation I have to write the server name manually in the server option of SQL server service manager and then have to click the refresh services. Is there any way to make a batch file which can be used to start or stop the server outomatically even whenever I log on with the accountant user and do not need to write the server name manully in the SQL server service manager. My server name is same as the computer name and the accountant user is a power user.

    
    
98. on June 17, 2011 at 2:30 pm | Reply Naveen

    Hi,

    Can you Plz tell me the difference between a LEFT OUTER JOIN ON condition and a WHERE condition…. With example ?

    
    
99. on June 20, 2011 at 6:56 am | Reply kath

    Hi Dave!
    I know you are an expert in sql and everything about db and software development, so I have a question for you.
    I have done an application in C sharp using database (sql) but I want to create a installer for my application. I want to know if there is a way to join or mix (I’m not sure about if it is the correct word) the database with the application in order to when I install my application in other computer I don’t have to create again the database for my application can work. Please if you have idea how to do this, I would thank you so much if you help me. Because till today, I have not found a correct answer.

    
    
100. on June 20, 2011 at 3:21 pm | Reply Ravi

     Hi Dave,
     I want a query for calculating number of working hours per week or per month..

     
     
101. on June 20, 2011 at 9:14 pm | Reply Sidd

     I am trying to figure out the best way to calculate difference between two dates. The difference should also consider the timestap.
     Right now we are using the below formula for date difference,
     CAST( date1 – date2 AS FLOAT)

     I think using “cast” to calculate the date difference is not the most efficient way to calculate the date difference. I think by using convert we can get a better query performance. Please let me know if I am right.

     Thanks!

     
     
102. on June 21, 2011 at 12:23 pm | Reply Aditya Dhekane

     Indexing: How will you index following Query ???

     create table TB (StartDate datetime, StartTime datetime, EndDate datetime, EndTime datetime)

     declare @dt datetime , @time datetime

     select * from TB
     where StartDate < @dt
     AND StartTime=@dt OR EndTime >=@time)

     In the above query how will you apply indexes???

     –Whether it will be clustered on StartDate only? or Nounclustered on all four columns? or Nonclustered on EndDate and EndTime ? or anything else???
     –In these type of queries how do we decide indexing???
     –Can we rewrite this query in some more efficient way???

     I came across this question recently and not finding proper answer to this, even I tried with dummy data and execution plan. But I am not able to come to any final conclusion

     Plzzzzzzzzzzzzzz help.

     
     
103. on June 21, 2011 at 11:39 pm | Reply Murugan Chokkalingam

     Good day ,

     I am trying to find a solution for following sample / sample query in my development

     Select * from <> where Convert( int, @FieldName)=1

     I am getting conversion error .

     Any Suggestion

     
     
104. on June 23, 2011 at 3:37 am | Reply Nilesh

     Pinal,
     I am planning to install SQL server Express edition to our Development environment for POC. I have following question.
     1) What is Difference between “Microsoft® SQL Server® 2008 R2 Express “, “Microsoft® SQL Server® 2008 R2 Express with Tools” and “Microsoft® SQL Server® 2008 R2 Express with Advanced Services” from feature standpoint?
     2) How Can I do unattended installation of SQL Server Express and create basic user with DBO access?
     3) If I choose windows Authentication, how can I find, configure Windows local user and use with SQL server instance.

     Thanks
     Nilesh

     
     
105. on June 23, 2011 at 10:07 am | Reply Abhilash

     Hai,
     Pinal Can you please arrange a Tech Event in kerala.

     
     
106. on June 23, 2011 at 1:35 pm | Reply Asim

     I have created DB XYZ in SQL Server 2005 and then created user ABC with sysadmin priviledge using SQL Authentication .But I am able to access the DB XYZ from both Windows and SQL authentication . What is exactly meant by Windows and SQL authentication .Is it correct for SQL authentication ? If not how it will be rectified .

     Please revert asap.

     Thanks in advance .

     
     
107. on June 23, 2011 at 5:58 pm | Reply Shyam

     @Nilesh
     Try
     SELECT * FROM tableName WHERE coalesce(@FieldName,’0′) ’0′
     OR
     SELECT * FROM tableName WHERE coalesce(@FieldName,0) 0

     
     
108. on June 23, 2011 at 6:00 pm | Reply Shyam

     @Murugan Chokkalingam

     Try
     SELECT * FROM tableName WHERE coalesce(@FieldName,’0′) ’0′
     OR
     SELECT * FROM tableName WHERE coalesce(@FieldName,0) 0

     
     
109. on June 24, 2011 at 11:41 am | Reply Swanand Pol

     Hi Pinal,
     Actually currently we are looking for Arabic searches using like keyword on database and its fields … We were not able to search it because of improper knowledge about the collations and the language conventions … Could you please post an article regarding to it?
     Thanks in Advance
     :D
     Swaa

     
     
110. on June 24, 2011 at 6:39 pm | Reply Alex

     Hi Pinal,

     Ive been tasked with creating a database replication application for sql 2005 express (possibly 2008 but definitely express). Since express has none of the replication stuff we ended up going down the route of creating a master backup sending this file to our remote server and then continuously sending the differential backups (WITH STANDBY), ..we check to see if data has changed by referencing the log file ..

     SELECT MAX ([Begin Time]) as ‘BeginTime’ FROM fn_dblog(null,null) WHERE [Transaction Name] IN (‘INSERT’,'user_transaction’,'UPDATE’)

     in case we try to restore an unchanged differential backup which has its own problems (not committing the non changes and so putting the differential base out of sync)

     The problem we have is that as it stands the differential backup will continually grow unless we recreate and send another master backup which while doable is somewhat unsustainable..

     is there a way to create the master backup on the client machine to reset the differential backup and maintaining the correct differential base on our servers without sending this newly created master backup?

     
     
111. on June 26, 2011 at 1:04 pm | Reply George Kirkwood

     We have developed new backup software for SQL Server over the last 5 years. You can manage backups from anywhere using the Windows utility, the web or our Android software. You can manage any server, anywhere in the world and start / check jobs. Backups are encrypted, compressed and can be sent to FTP/TFTP/NAS devices We don’t want to publish links to respect your no spam and advertising policy.We would love you to try it and let your readers know your thoughts. There is a 30 day free trial for users, and the cost is pretty low too. Thank you.

     
     
112. on June 26, 2011 at 11:30 pm | Reply sangram2681

     Hi Pinal ,I come across strange problem I created a user defined function then used that function is a store procedure in where part of a query for filtering recordset code runs fine on my local machin which is xp & sql server 2005
     Now when i put code on live server i get msg 4121 error

     My Function is

     CREATE FUNCTION Occurance ( @pInput VARCHAR(8000), @pSearchString VARCHAR(100) )
     RETURNS INT
     BEGIN

     RETURN (LEN(@pInput) –
     LEN(REPLACE(@pInput, @pSearchString, ”))) /
     LEN(@pSearchString)
     END

     and sp is

     ALTER PROCEDURE [dbo].[SavedShippingAddress_GetByUserLoginId]
     (
     @UserLoginId int
     )
     AS
     set nocount on
     SELECT
     a.ShippingAddressId,
     a.ShippingAddressTag,
     a.UserLoginId,
     a.Title,
     a.FirstName,
     a.LastName,
     a.BusinessName,
     a.EmailId,
     a.Phone1,
     a.Phone2,
     a.Phone3,
     a.Fax,
     a.Address1,
     a.Address2,
     a.SpecialInstruction,
     a.GiftMessage,
     a.CountryId,
     a.StateId,
     a.CityId,
     a.OtherState,
     a.OtherCity,
     a.ZipCode,
     a.TypeOfAddress,
     a.CompanyId,
     a.OtherCompany,
     a.CreatedDatetime,
     a.LastAccessDatetime,
     a.Deleted,
     a.IsGiftOrder,
     b.SavedShippingAddressId
     FROM
     ShippingAddress a with (nolock),
     SavedShippingAddress b with (nolock)
     WHERE
     a.ShippingAddressId = b.ShippingAddressId AND
     dbo.Occurance(a.ShippingAddressTag,’#') AND
     a.UserLoginId=@UserLoginId
     SELECT @@ROWCOUNT

     
     
113. on June 28, 2011 at 5:51 pm | Reply Abdussalam

     Hi Pinal,

     Good Day…!!!

     Hope you are doing well.

     Me a regular reader of your blog…Hope you can help me on this query. I have a table (purchase detail table) which is having three columns ITEM_CODE, EXPIRY_DATE and QUANTITY. (It has other fields also like price an all. Since I need the quantity, I mentioned only required fields here) For example

     ITEM_CODE EXPIRY_DATE QUANTITY

     ————- ————— —————

     101.0027 2011/06/30 50

     101.0027 2011/07/12 40

     102.0028 2011/12/01 30

     102.0028 2011/06/01 90

     From my STOCK info table, I can get exactly the balance quantity of each item. Example

     ITEM_CODE BALANCE_QTY

     ————- —————

     101.0027 70

     102.0028 60

     My requirement is, I need to generate an Expiry List Report which means list of items going to expire.In the above sample, I should get a result as like

     for item 101.0027, total qty = 90 and balance = 70. Hence

     ITEM_CODE EXPIRY_DATE QTY

     ————- ————— ——-

     101.0027 2011/06/30 30

     101.0027 2011/07/12 40

     For item 102.0028, total qty = 120 and balance = 60. Hence report should be

     ITEM_CODE EXPIRY_DATE QTY

     ————- ————— ——-

     102.0028 2011/06/01 60

     * HINT: Items are sold on basis of FIFO

     Thanks n Regards

     Abdussalam

     
     
114. on June 28, 2011 at 10:01 pm | Reply Samir Shah

     Hello Pinal,
     I have a quick question. We are converting PDF and other attachments into bytes stream and store them in a database column. Do you know how big attachments can be stored this way? Would there be any size limit on this one?
     Thanks

     
     
115. on June 28, 2011 at 11:02 pm | Reply Dhans Shah

     Hi Pinal can you post Q & A for just DBA interview. I have gone thru all the questions and they are very good but its more for developers so can you make separete Q & A for SQL Server DBA. I tried to find online and i got that too but your questions and answers are really good for real work experience.

     
     
116. on June 29, 2011 at 7:24 pm | Reply kalpen

     How to get Store procedure which is in encrypted form

     
     
117. on June 30, 2011 at 4:46 pm | Reply tahire

     Hi Pinal,

     I am trying to contact you by email but you only offer facebook and twitter, do you have an email address i can reach you by?

     
     
     • on June 30, 2011 at 5:01 pm | Reply pinaldave

       pinal at sqlauthority.com

       
       
       • on July 2, 2011 at 4:04 am | Reply tynicool

         Sorry to be improper. But I have consulted many professionals trying to create a hierarchical tree in one dimension. for example, a hierarchy for the departments of a company. My table has three fields: id, parentid, Area. could you help me with this, PLEASE. I want to show the grandchildren / great grandchildren of an area in dimension.

         
         
118. on July 1, 2011 at 11:45 am | Reply haile

     how do you update programmatically two tables in the same dataset with some similar columns (not all).

     
     
119. on July 2, 2011 at 6:35 pm | Reply PR

     Hi Pinal,

     Can you share some interview questions on Microsoft Business Intelligence?

     Thanks for such a nice informative blog.

     
     
120. on July 3, 2011 at 1:53 am | Reply John

     Hi Pinal,

     Article suggestion: I use SQL Server database mail successfully to send email alerts out for an application via SMTP. However, my SP’s that send the alerts need to use nested loops – one per recipient, then an inner one with detail lines for the recipient (can be done either with while loops or cursors, both of which are recognised as bad practise).
     Is there a set based way that I can generate customised emails with different content for each recipient without using said functionality? If so, how?

     Typically this is 150-160 emails per run, 3 x per day.

     Be grateful for any comments/thoughts?

     
     
121. on July 3, 2011 at 2:53 pm | Reply chauvan

     HI Pinal Dave,
     I copy 2 files (*.mdf, *.ldf) database name from server, then attach to laptop, but error show like this ”
     An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

     Attach database failed for Server ‘CHAUVAN\SQLEXPRESS’. (Microsoft.SqlServer.Smo)

     For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

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

     An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

     ——————————

     One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error: 5173)

     For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5173&LinkId=20476

     Questions:
     Could you explain to me that what happy ? and instruct me how to solve this problem to attach database successful.
     Thank you very much
     van

     
     
122. on July 6, 2011 at 2:09 pm | Reply arka

     Hi,
     I have 64-bit windows 7, & I have installed sql server 2005 but management toll is unable to detect the server name.
     Please suggest me.

     Regards,
     Arka

     
     
123. on July 6, 2011 at 7:20 pm | Reply harish

     Hi Pinal this is harish here . I am developing a window application in c# .Net and trying to create a stored procedure with multiple ceate and go. but i am getting an error near go or batch file etc. any idea???

     
     
     • on July 7, 2011 at 1:59 pm | Reply madhivanan

       You cannot use the batch seperator GO inside a procedure. When you use the GO, it signals the end of batch and the body of the procedure ends to that point

       
       
124. on July 7, 2011 at 4:00 pm | Reply Shelly

     Hi Pinal,
     This is Shelly.I am just statred working on sql sever and i have been given an assigenment to display the calender year of 2011 in sql.
     Can you help me with this?
     I am able to display current month’s calender but not for prvious month and subsequent months!!!

     Thanks in advance!

     
     
     • on July 7, 2011 at 6:53 pm | Reply madhivanan

       Search for generating calender in this site. You will find the related article

       
       
125. on July 8, 2011 at 12:07 pm | Reply Mahesh

     Hi Pinal,

     I have a doubt. Is there any problem for using temporary table in stored procedures? Is there any performance problem with that?

     Thanks and Regards,
     Mahesh

     
     
     • on July 8, 2011 at 8:50 pm | Reply madhivanan

       No problem. You can only access it inside that procedure

       
       
126. on July 12, 2011 at 6:48 pm | Reply HARI JAGANATHAN

     Hi Pinal,
     This is Hari from Chennai. Currently working as a SQL Developer in Chennai. Im very much interested in BI tool(DataStage). I came to know SQL Server is the path for BI tool. Is it fine? Is it possible to switch over? Whr to get training on BI tools in Chennai region? Plz let me know any crash course and guide me.
     Thanks
     Hari Jaganathan

     
     
127. on July 14, 2011 at 12:24 pm | Reply Satheesh Kumar . P

     Hi Pinal
     This is satheesh from chennai….
     My database is too big,for performance wise i did partitions for the big tables….
     after that if i want to delete the partition means what can i do ?

     For ex:

     My table name is Employee_Master,and one of the column name is Joined_Date.

     it contains more than 4 to 5 lahks records.

     I used the Joined_Date column to partition the table year wise.

     The joined_Date column has values in the range of 2008,2009,2010,2011.

     First i created the 3 extra file groups to my database, and then did the partition using the partition function and schema.
     For say 2008_FG1 — for storing 2008 year data’s
     2009_FG1 — for storing 2008 year data’s
     2010_FG1 — for storing 2008 year data’s
     My primary MDF will contains the current year data i.e 2011.

     All going successfully but now what i need is i want to merge the MDF as well as NDF files and then delete the ndf files. i dont want to loose the records. so how can i achieve this ???

     Please help me out……………

     
     
128. on July 14, 2011 at 12:30 pm | Reply Satheesh Kumar . P

     Hi Pinal,

     I did the partition on more than 1 table and to share the same file groups location accordingly.
     Now i want delete the partition for the second table? How can i do this?
     For single table partition,i deleted the partition easily but the same more than one table its showing error ? how can i achieve this????

     
     
129. on July 14, 2011 at 6:02 pm | Reply Rashmi

     Hi Pinal,

     I have table called category

     categoryid parentid NAME
     ———– ———– ——————————
     111 0 rashmi
     113 111 rashmi2
     112 0 rashmi3
     114 113 rashmi4
     115 114 rashmi5
     116 115 rashmi6
     118 112 rashmi7
     119 118 rashmi8
     120 119 rashmi9

     I want to write stored procedure and i will pass categoryid to that. Now i have following query.from this we get rows where category reflects in parentid and from this row corresponding categoryid we fetch next rows from same query.this operation has to repeat untill parentid!=categoryid.after getting those rows i want to delete from that table.please help me in writing stored procedure for this.

     select categoryid,parentid from category where parentid=111 or categoryid=111

     
     
     • on July 14, 2011 at 9:37 pm | Reply Darshan

       Rashmi,
       Use below Script. CTE is possible from SQL server 2008.

       Create table CatMast(Parent int,Child int,Name varchar(20))

       Insert into CatMast values (111, 0, ‘rashmi’)
       Insert into CatMast values (113, 111, ‘rashmi2′)
       Insert into CatMast values (112, 0, ‘rashmi3′)
       Insert into CatMast values (114, 113, ‘rashmi4′)
       Insert into CatMast values (115, 114, ‘rashmi5′)
       Insert into CatMast values (116, 115, ‘rashmi6′)
       Insert into CatMast values (118, 112, ‘rashmi7′)
       Insert into CatMast values (119, 118, ‘rashmi8′)
       Insert into CatMast values (120, 119, ‘rashmi9′)

       WITH Objects (Child, Parent,name) AS
       ( — This is the ‘Anchor’ or starting point of the recursive query
       SELECT rel.Child,
       rel.Parent,rel.name
       FROM CatMast rel
       WHERE rel.Child = 111
       UNION ALL — This is the recursive portion of the query
       SELECT rel.Child,
       rel.Parent,rel.name
       FROM CatMast rel
       INNER JOIN Objects — Note the reference to CTE table name (Recursive Join)
       ON rel.Child = Objects.Parent
       )
       SELECT o.* FROM Objects o

       
       
130. on July 16, 2011 at 2:55 am | Reply Ravindar

     How to convert query result\ resultset into XML and pass it into stored procedure

     
     
131. on July 16, 2011 at 4:12 am | Reply Ravindar

     Hi,

     I want to convert query result into XML and pass that XML into another stored procedure as parameter.

     Bellow is the stored procedure

     Create PROC dbo.Select_Master_Table_Data
     @Temp_Table XML
     AS
     SET NOCOUNT ON;

     SELECT
     Current$.Port#,
     Current$.Protocol
     ,Current$.Process
     ,Current$.Services
     FROM Current$
     JOIN (
     SELECT
     Temp_Table.Row.query(‘./Protocol’).value(‘.’, ‘varchar(100)’) AS Protocol
     ,Temp_Table.Row.query(‘./Services’).value(‘.’, ‘varchar(100)’) AS Services
     ,Temp_Table.Row.query(‘./Process’).value(‘.’, ‘varchar(100)’) AS Process
     FROM @Temp_Table.nodes(‘/Temp_Table/Row’) AS Temp_Table(Row)
     ) AS Temp_Table ON
     Temp_Table.Protocol = Current$.Protocol
     AND Temp_Table.Services = Current$.Services
     AND Temp_Table.Process = Current$.Process and Current$.IP=’10.16.2.90′;

     RETURN @@ERROR;
     GO

     If i manually declared the XML as below its works correctly

     DECLARE @Temp_Table XML;
     SET @Temp_Table = ‘

     TCP
     seclogon
     svchost.exe

     UDP
     ””
     discfcsn.exe

     UDP
     Netlogon
     lsass.exe

     ‘;
     EXEC dbo.Select_Master_Table_Data @Temp_Table;

     If i directly assign a query to the XML datatype i didn’t find the result

     DECLARE @Temp_Table XML;
     SET @Temp_Table = ‘select * from temp_port for XML RAW’
     EXEC dbo.Select_Master_Table_Data @Temp_Table;

     If a just query
     select * from temp_port for XML RAW
     i am getting below output in the XML file.

     Can anyone please help me how to build a XML file from a query and pass that XML query into the Stored Procedure.

     Thanks in advance.

     
     
132. on July 16, 2011 at 4:39 pm | Reply Raghav

     Hi pinal,
     Could you please advice a approach for a problem i am facing.Need to create the Data in such a way that is at Table A level wise, i.e. The File should show Table A Details with its Table B Details together
     (The Data should be in form of Sets, where in each set contains Table A Level Details at top, and at the trailer/footer of set with Table2 Details in between which are corresponding to that particular Table A Record,
     this set is followed by another(Multiple) set of Table A)

     
     
133. on July 18, 2011 at 3:46 pm | Reply Loknath

     Hi Pinal,

     I am using this query to get information about views in my database

     SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

     which was very helpful for me in providing complete information about a particular view
     (VIEW_CATALOG,VIEW_SCHEMA, VIEW_NAME,TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME)

     But, I am getting the information just for the current DB where i am executing this TSQL. In my database, there are views which are reffering other DB tables

     For Eg:

     CREATE VIEW [dbo].[vwTEST]
     AS

     SELECT A1.Col1 as Col1
     FROM DB1.dbo.Table1 A1

     UNION
     SELECT A2.Col1 as Col1
     FROM DB2.dbo.Table2 A2
     JOIN DB3.dbo.Table3 A3
     ON A2.Col1= A3.Col1

     UNION
     SELECT A4.Col1 as Col1
     FROM DB4.dbo.Tabbl4 A4

     Could you please let me know if there is a way with which i can get all the information about the base tables which were refered in my view!
     The query should return me the DB name, table name and column names for that particular view.

     Advance many thanks for your help!

     Thanks,
     Loknath

     
     
134. on July 18, 2011 at 6:45 pm | Reply dLCreations

     Select * From Table_Name

     AND

     Select * From Table_Name Where 1=1

     This Both the Quries retrives the Same Result..
     Any Reason .?

     
     
     • on July 18, 2011 at 7:02 pm | Reply madhivanan

       The condition where 1=1 is always true and as there is no other condition, it is equivalent to not using a where clause

       
       
135. on July 19, 2011 at 1:35 am | Reply Mahesh

     We are using SQL 2005 and have replicated database (different schema with tables/indexes). What is the best way to delete 65 million records from a table which has 160 million rows?

     Thanks in advance.

     
     
     • on July 19, 2011 at 4:01 pm | Reply madhivanan

       Delete as a batch

       set rowcount 10000
       delete from table where ….
       while @@rowcount>0
       begin
       delete from table where ….
       end
       set rowcount 0

       
       
136. on July 20, 2011 at 10:37 am | Reply Ara

     Good day Journey to SQLAuthority,

     I’m looking for any contact details (email, phone number or mail address) of the website owner or marketing person that I can communicate with for possible business venture on website income opportunities. We are very much interested to do business with you. Please send me an email together with your website name and I’ll be glad to call you to discuss this matter.

     Thank you

     Save your Precious Files now to TheDataLocker Online Backup! Checkout http://www.thedatalocker.com

     
     
137. on July 20, 2011 at 7:32 pm | Reply Tarak

     Hi Dave,

     We have stored procedures (approx. 20) and tables (approx 30). And We have a column called ‘Active’. Could you tell me how to find the column ‘Active’ dependencies (from both tables and Stored procedures). I mean if We used ‘Active’ in a stored procedure, we need to get that stored procedure name. and if we used ‘Active’ in 15 stored procedures, we need to get all the stored procedures names and the table names (in which Column ‘Active’ exists).

     Thanks,
     Tarak

     
     
138. on July 21, 2011 at 1:44 pm | Reply Jeriesh

     Hi,
     I am using AX and I have restore the sql 2008 data base backup from different domain to my domain.But,due to user authentication issue it is not working when i search for solution in one blog i found i need to do the following:
     “Reset UserInfo.sid for the admin user to the sid of the user under which you want to work with the database”
     How and Where i need to this…i don’t much about SQL DB pls guide me to resolve this.

     Thanks in advance
     Jeriesh

     
     
139. on July 21, 2011 at 6:23 pm | Reply Shelly

     Hi Pinal,

     Can you please help me with a logic here?

     I have a table mytable,

     Name | Qualification
     —————————-
     Ankur | Bcom
     Ankur | MBA
     Sheetal | BA
     Sheetal | MA
     Sheetal |PHD
     Hari | Bcom

     Now i want to display the result as

     Ankur Bcom MBA, Sheetal BA MA PHD

     I tries using self join but i think i will have to use “for XML path”
     Can you suggest some other way to do this?

     Thanks in advance!!!

     
     
     • on July 22, 2011 at 7:15 pm | Reply madhivanan

       You need to use the for xml path twice to get this done. Search for for xml in this site

       
       
140. on July 21, 2011 at 9:50 pm | Reply Jim Holliday

     Pinal, When I do a search looking for an sql server solution and see one of your pages my instance thought is YES, I will succeed. Thank you so much for your postings.
     Jim
     MCDBA 2000/2008

     
     
141. on July 22, 2011 at 2:37 pm | Reply damusmiling

     Hello SqlAuthority.. I need one favour from you. I am designing table for one big databse application. Please let me know the main points we have to consider.
     Thanks in advance
     Damodar

     
     
142. on July 25, 2011 at 3:19 pm | Reply PC Rao

     Hi Pinal

     I have stored proceduere(see below). This procedure supposed to process 20 million records. But this proceduere taking day(6days) together to execute, never fimished as i stopped it aftre 6 days as its not correct solution.

     Can you please go thru this SP and advise me some wrokaround please.
     Here by I’m provding you SP and table script i’m using. I tested without using indexs and with indexes i have’t seen any diffrenece. It very urgent. Can you pleaase help me ASAP.

     SP script
     ============================================================

     CREATE PROCEDURE [dwh].[Usp_MapCampaignCode] AS
     BEGIN
     SET NOCOUNT ON
     DECLARE CustomerCode_cursor CURSOR FOR
     SELECT DISTINCT CustomerCode FROM dwh.tblActivity WHERE
     JourneyStart=’Journey Start’
     AND ActivityDate IS NOT NULL
     AND OBJECT_NAME (TableKey)=’tblAppointments’
     AND ActivityDate > ’09-30-2009′

     DECLARE @OrderKey AS INT
     DECLARE @ActivityDate AS DATETIME
     DECLARE @CampaignCode AS VARCHAR(100)
     DECLARE @CustomerCode AS VARCHAR(100)

     OPEN CustomerCode_cursor
     FETCH NEXT FROM CustomerCode_cursor INTO @CustomerCode
     WHILE @@FETCH_STATUS = 0
     BEGIN
     DECLARE Appointments_Cursor CURSOR FOR
     SELECT OrderKey, ISNULL(CampaignCodeName,0),
     CAST(ActivityDate AS DATE) AS ActivityDate
     FROM dwh.tblActivity WHERE
     JourneyStart=’Journey Start’
     AND ActivityDate IS NOT NULL
     AND ActivityDate > ’09-30-2009′
     AND OBJECT_NAME (TableKey)=’tblAppointments’
     AND CustomerCode=@CustomerCode
     ORDER BY ORDERKey

     OPEN Appointments_Cursor
     FETCH NEXT FROM Appointments_Cursor INTO
     @OrderKey ,@CampaignCode,@ActivityDate
     WHILE @@FETCH_STATUS = 0
     BEGIN
     DECLARE @NewCampaignCode AS VARCHAR(100)
     DECLARE @dtStartElapsedDate AS DATETIME
     DECLARE @dtEndElapsedDate AS DATETIME

     SET @dtStartElapsedDate=DATEADD (DAY ,-60,@ActivityDate)
     SET @dtEndElapsedDate=DATEADD (DAY ,60,@ActivityDate)
     SET @NewCampaignCode=’0′

     SELECT @NewCampaignCode=ISNULL(CampaignCodeName,0) FROM dwh.tblActivity WHERE
     CustomerCode=@CustomerCode
     AND OBJECT_NAME (TableKey)=’tblResponseStaging’
     AND ActivityDate >@dtStartElapsedDate
     AND OrderKey < @ORDERKey
     AND CampaignCodeName IS NOT NULL

     – If Campaign Code not existed look for Campaign Code for Response Staging Activity

     IF @NewCampaignCode’0′ SET @CampaignCode= @NewCampaignCode
     IF @CampaignCode IS NULL or @CampaignCode =’0′
     BEGIN

     IF EXISTS (SELECT COUNT(1) FROM dwh.tblActivity WHERE
     ActivityDate >@dtStartElapsedDate
     AND OrderKey @dtStartElapsedDate
     AND OrderKey @dtStartElapsedDate
     AND OrderKey @dtStartElapsedDate
     AND OrderKey 1
     SET @NewCampaignCode=@CampaignCode

     IF @NewCampaignCode’0′

     BEGIN
     UPDATE dwh.tblActivity
     SET MappedCampaignCode=@NewCampaignCode
     WHERE ActivityDate >= @ActivityDate
     AND ActivityDate @ORDERKey
     AND CustomerCode=@CustomerCode

     SET @NewCampaignCode=’0′

     END

     FETCH NEXT FROM Appointments_Cursor INTO
     @OrderKey ,@CampaignCode,@ActivityDate

     END
     CLOSE Appointments_Cursor
     DEALLOCATE Appointments_Cursor

     FETCH NEXT FROM CustomerCode_cursor INTO @CustomerCode
     END

     UPDATE dwh.tblActivity SET CalculatedCampaignCode= ISNULL(MappedCampaignCode,CampaignCodeName)

     CLOSE CustomerCode_cursor
     DEALLOCATE CustomerCode_cursor
     END

     ============================================================

     Table Script
     ============================================================

     CREATE TABLE [dwh].[tblActivity](
     [ActivityKey] [int] IDENTITY(1,1) NOT NULL,
     [TableKey] [int] NOT NULL,
     [PKey] [int] NOT NULL,
     [CustomerCode] [varchar](50) NULL,
     [CampaignCodeName] [varchar](50) NULL,
     [ActivityDate] [datetime] NULL,
     [ActivityTime] [time](7) NULL,
     [ActivityStatus] [varchar](50) NULL,
     [ActivityOutcome] [varchar](50) NULL,
     [ActivityType] [varchar](50) NULL,
     [OrderKey] [int] NULL,
     [JourneyStart] [varchar](100) NULL,
     [JourneyEnd] [varchar](100) NULL,
     [MappedCampaignCode] [varchar](50) NULL,
     [CalculatedCampaignCode] [varchar](50) NULL
     ) ON [PRIMARY]
     GO
     SET ANSI_PADDING OFF
     GO

     /****** Object: Index [idx_ActivityDate] Script Date: 07/25/2011 10:46:21 ******/
     CREATE NONCLUSTERED INDEX [idx_ActivityDate] ON [dwh].[tblActivity]
     (
     [ActivityDate] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     GO

     /****** Object: Index [idx_CampaignCodeName] Script Date: 07/25/2011 10:46:44 ******/
     CREATE NONCLUSTERED INDEX [idx_CampaignCodeName] ON [dwh].[tblActivity]
     (
     [CampaignCodeName] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     GO

     /****** Object: Index [idx_CustomerCode] Script Date: 07/25/2011 10:46:58 ******/
     CREATE NONCLUSTERED INDEX [idx_CustomerCode] ON [dwh].[tblActivity]
     (
     [CustomerCode] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     GO

     /****** Object: Index [idx_OrderKey] Script Date: 07/25/2011 10:47:13 ******/
     CREATE NONCLUSTERED INDEX [idx_OrderKey] ON [dwh].[tblActivity]
     (
     [OrderKey] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     GO

     /****** Object: Index [idx_TableKey] Script Date: 07/25/2011 10:47:26 ******/
     CREATE NONCLUSTERED INDEX [idx_TableKey] ON [dwh].[tblActivity]
     (
     [TableKey] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     GO

     
     
     • on July 27, 2011 at 7:55 pm | Reply PC Rao

       Hi Pianl

       I have solved this problem my self by removing all indexes except [idx_CustomerCode] and by tewaking a bit on SP.

       Thanks
       PC Rao

       
       
143. on July 26, 2011 at 3:45 pm | Reply nitinkabir

     Hi !
     i have a table with column name description in which there are some img tag. i want to replace the source of these images. Images can be 1 or more.

     images source are as like websitename/folder/subfolder/…/image/abc.jpg
     i want to change these source to a particular folder websitename/image/abc.jpg

     
     
     • on August 19, 2011 at 7:44 pm | Reply madhivanan

       update table
       set col=replace(‘websitename/folder/subfolder/…/image/’,'websitename/image/’)

       
       
144. on July 27, 2011 at 3:12 pm | Reply vivek

     i have a “transaction” with in that transaction i have two or three process(it may be insert,delete,update stored procedure etc..), and executed all these process with in that transaction, now if i want to rollback any particular process alone (ie only insert) is that possible.. if so can you please explain sir!

     
     
145. on July 27, 2011 at 3:14 pm | Reply vivek

     In a transaction if i rolled back that transaction, is that possible to undo that rollback again

     
     
     • on August 19, 2011 at 7:42 pm | Reply madhivanan

       It is not possible to undo the rollback again

       
       
146. on July 28, 2011 at 3:45 pm | Reply sri

     hi,

     We are facing a problem in SQL server, Our clients using normally 12 GB to 16 GB RAM on server , but Every 2 days or 1 day , some time 2 times in a Day, my application hang, when they log-in.

     for this, we have to re-start SQL , or restart the Server, after that again they can log-in fine without any changes.

     Actually what could be the cause, its really creating bad impression on our application.

     If anybody can help, really appreciate.

     
     
147. on July 29, 2011 at 12:55 am | Reply Sachin Panchal

     Dear All,

     I have one trigger on one server’s SQL Database. I want to insert some of data from this Server’s database to another server’s database by trigger only. Can any person help me for this transaction.

     
     
     • on July 29, 2011 at 3:25 pm | Reply madhivanan

       I dont think cross server option for trigger is possible. Try using a linked server and update it

       
       
       • on July 29, 2011 at 11:08 pm | Reply Sachin Panchal

         Dear Madhivanan,

         Thanks for reply. If trigger is not possible then please could u suggest me whether is is possible by procedure or any another method…..

         Thanks in advance….

         
         
         • on August 16, 2011 at 1:42 pm | Reply Madhivanan

           As I said, try using a linked server

           
           
148. on July 29, 2011 at 5:59 pm | Reply Sergei Topolov

     I’d like to know how to query my SQL Server database for listing the Navigation Properties of all the entities in my ADO.Net Entity Data Model .edmx

     
     
149. on July 30, 2011 at 6:13 pm | Reply Leonard Shackelford

     Thank you for maintaining a great blog; I’ve learned more from you blog than I ever hoped in college (I know, doesn’t speak much for my college ;) ) – Regards Leonard

     
     
150. on August 1, 2011 at 9:02 pm | Reply Bankade

     HI Pinal,

     Thanks for the great Blog. I have few question on Trigger. I am using Server Explorer to write Trigger. Ther are two table in two Database. They are exactly same tables but they are in different database like Master Table and Local Table. I want to synchronise them everynight. e.g If Any value is added or removed or updated in master Table. I want to reflect the changes in Local table.
     Its little bit confusing. I know but my problem is, I dont have access to master Table.Is ther any way i can acchive this.
     Thanks in advance.

     Regards,
     p.bankade

     
     
     • on August 16, 2011 at 1:45 pm | Reply Madhivanan

       Why do you want to maintain two copies of the tables in two databases? You can schedule it as a job or do it via a trigger

       
       
151. on August 4, 2011 at 12:38 am | Reply Moustafa Safwat

     Dear Eng. Pinal Dave,

     Thank you in the beginning for your efforts on publishing Database tutorials and problems solutions for helping people.

     Actually I’m planing with my team for developing social network system as a graduation project; somehow like facebook.

     So Could you please help us to get ERD for how the facebook database is working.

     Best Wishes
     Moustafa ElSayed

     
     
152. on August 4, 2011 at 3:14 am | Reply Darsh

     Hi Pinal I am giving SQL Server DBA position interview and I have 1 interview with VP of Technology Department. What question VP can ask to me?

     
     
     • on August 16, 2011 at 1:47 pm | Reply Madhivanan

       It depneds on your experience level. Mostly questions will be on data security, high availability, managing users, etc

       
       
153. on August 4, 2011 at 10:19 am | Reply ss

     Hi Pinal,

     I have a table in which one column updates everyday and I have to also keep track of the all the values that are being updated according to the date.Is it possible to use temporary tables or common table expressions in this scenario. Can you please suggest a way to do this efficiently?

     SS

     
     
     • on August 19, 2011 at 7:01 pm | Reply madhivanan

       Using the trigger is the correct way.

       
       
154. on August 11, 2011 at 7:26 am | Reply Derek Waltho

     Hi Pinal, I have a windows server 2003 x64 running SQL 2005 x64 with full SSIS services running as well. I have developed a job in SSIS to perform various data imports, updates…into my SQL db. This database application has it’s own oledb driver that is 32 bit to connect to the db. When I run the SSIS job in debug mode I am able to connect through this 32bit ole driver and perfom the updates and imports, however when I build the application in SSIS designer to run in 32bit mode and install it as job in SQL server. The job fails when trying to establish a connection with the 32bit oldedb driver stating the “oledb driver is not registered.

     With a little research I found that when setting up the job in sql server in the New job setup dialog box under the “Execution Option” we can select “Use 32bit runtime” for this job

     Here is the link to this web page from microsoft “http://msdn.microsoft.com/en-us/library/ms141766.aspx”

     So my question is can I install the 32bit sql server and 32bit sql server agent onto this same box as stated in the above web link and will there be any issues that I should be aware of?

     Any help is greatly appreciated!

     Thanks,

     Derek

     
     
155. on August 19, 2011 at 1:22 am | Reply kishor

     Pls Help me on this..

     I have to find a record is existing in any of the table in the database using a loop or somthing like that.. pls help me on this.

     eg:

     I have
     1 raj [email removed]
     2 kumr [email removed]

     I have to find in which table this data existing.. how to solve my issue..

     adv thanks..

     
     
     • on August 19, 2011 at 6:56 pm | Reply madhivanan

       You can find this post useful
       http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

       
       
156. on August 19, 2011 at 5:37 pm | Reply Di

     Hi Pinal, I find your blog really helpful. :) I was reading your blg get lasr run date (2009).
     My issue is that I cannot use it because my table got different Types with the same date. (Email = 8/1/2011 10:48:08 PM, Industry = 8/1/2011 10:47:13 PM)

     I know I have to convert the date to get the last run date somehow or? Can you give me a hint please? Many Thanks Di

     SELECT TOP 100 PERCENT BU_NAME AS [Office Name], CODED AS [#Email Captured], TOTAL AS [# Contacts Total], CONVERT(VARCHAR, CONVERT(DECIMAL(5,
     2), 100.0 * CODED / TOTAL)) + ‘%’ AS [Email Captured %], DATE_RUN, TYPE
     FROM dbo.rpt_CODING_SUMMARY WITH (Nolock)
     WHERE (NOT (BU_NAME IN (N’Default Organization’, N’IQPC Summits Australia’, N’IQPC Summits Canada’, N’Shared Services Network’, N’IQ Memberships’,
     N’IQPC Training’, N’IQPC Summits UK’)))
     ORDER BY DATE_RUN DESC, BU_NAME

     
     
     • on August 19, 2011 at 6:57 pm | Reply madhivanan

       What are the datatypes of the columns and what do you want to return?

       
       
157. on August 20, 2011 at 12:15 pm | Reply krishna

     Hi Pinal,
     I have a table with more than 10 million records like this

     —————————————————————————
     ID Keywords | varchar(900) |
     —————————————————————————
     1 ford, acura, honda
     2 Honda, suzuki, ford
     3 honda, volksvagen, dodge
     4 dodge, honda, ford
     5 Mini, ford, kia,suzuki
     6 honda, dodge,kia
     7 Dodge, volksvagen

     I tried this sql :
     select id from table
     where keywords like ‘%suzuki%’

     Now, because the table has more than 10 million records this like operator is working too much slow.
     Can you please guide me a way to fasten the search.

     Thanks for your help.

     Regards
     Krishna

     
     
158. on August 24, 2011 at 3:46 pm | Reply pradeep

     Hi Pinal,

     Please provide the script to get alert if DB goes offline

     Thanks
     Pradeep

     
     
     • on September 12, 2011 at 7:09 pm | Reply madhivanan

       I think you can check this in the sql server error log

       
       
159. on August 25, 2011 at 4:18 pm | Reply Ramakrishna

     Hi Dave,

     Please let me know how to rebuild all indexes in a particular database in SQL Server 2000 version?

     And i would like to automate it aswell.

     Thanks in Advance

     Regards,
     Ramakrishna

     
     
160. on August 29, 2011 at 1:45 pm | Reply oshaileshhailesh

     Hello Sir,

     Please help me…. Actually I have a query please answer??? Sir There is an option in mysql to findout the total execution time for query in my.cnf file. Is there any option in sql server too for this….so that If we will put the entry that if there wil be any query taking more than 2 second to execute that query have written to be a text file????? Is it possible ???

     Regards
     Shailesh jha

     
     
161. on September 1, 2011 at 1:02 am | Reply Imran Mohammed

     Hello Pinal,

     Congracts. Blog has crossed 30 Million Hits.

     ~IM

     
     
162. on September 2, 2011 at 9:05 am | Reply AN Rawal

     Hello Dear, We faced one serious problem about linked server in SQL server 2000. Our Linked server is connected within other our group company. Every Thursday at early morning our linked server is down with error no 17: Access denied. At this time other site has linked server of our server connectivity is running without any problem. We checked all things at OS & SQL level, but didn’t find any problem. please help us.

     
     
163. on September 2, 2011 at 5:11 pm | Reply Rupesh

     Hi Pinal,

     I need your help in writing a query in which I need to apply case statement in the where clause

     e.g.

     Declare @var varchar(50)

     select col1, col2,col3,col4
     from Table
     where case when @var = ‘ABC’ then “Condition1*” else No condition should be applied end

     *Conditon1 is of type (col2+col3)<0

     I hope my problem is clear to you.
     Thanks a lot for the help.

     
     
164. on September 3, 2011 at 4:07 pm | Reply kaushal

     I want to create a proc in which we pass a @jobid and we have use join two tables jobs and job_location .Sir can we check this manner–>” j.JobID=j1.JobID where IsActive =’true’ and j.JobID=@jobid ”

     Please chek my procedure

     create PROCEDURE [dbo].[USP_M_DisplayJob1]
     @jobid int
     as
     — Get this job info for displaying the job`
     begin

     select j.companyid,j.jobtitle,j.dateinserted,j.companyinfo,j.jobsummary,j1.joblocationid from Jobs
     as j inner join Job_Location as j1 on j.JobID=j1.JobID where IsActive =’true’ and j.JobID=@jobid
     end
     — Increment hitcount for this job by 1
     UPDATE Jobs SET Numhits = (NumHits + 1) WHERE JobID = @JobID
     go

     
     
165. on September 5, 2011 at 1:52 pm | Reply Gopi

     Dear Pinal ,
     I have a problem with the view ,
     if i select * from view time taken to execute the query is fast
     but if i select column from view it takes a long time to execute the results, why it will happen and any suggestion regarding this.

     
     
166. on September 5, 2011 at 2:12 pm | Reply Parul

     Hi Pinal,

     I have one query…
     I have multiple Check Boxes in Silverlight, contains week days like (Monday to Sunday)

     I want to store the checked values as 1, non checked values as 0, in Binary format.

     Say for example: 0000011 (if Monday ,Tuesday selected)
     How can I achieve this in SQL Server 2005.
     Please suggest.

     Thanks,
     Parul

     
     
167. on September 6, 2011 at 6:47 pm | Reply Carlos Jimenez

     Hi Pinal Dave

     I have one question, please help me.

     Script:
     INSERT INTO prueba_PI (tag,Valor) SELECT * FROM OPENQUERY (PILINK,’
     SELECT tag, avg(value)
     FROM piarchive..piavg
     WHERE tag=”U2950-FI_29”
     AND time BETWEEN ”Date-1second” AND ”Date”
     GROUP BY tag’)

     In previous script, i need get a date of present system, in part of time, and i need subtract one second on this time.

     Thank you very much.
     Carlos Jimenez
     Electronic Control Engineer
     Colombia

     
     
     • on September 12, 2011 at 7:06 pm | Reply madhivanan

       use dateadd(second,-1,getdate()) in place of ”Date-1second” and use getdate() in place of “date”

       
       
168. on September 8, 2011 at 8:00 am | Reply Indy

     Pinal,

     How can I automate as a job to create Script of dtabase Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other. Your post says that you had sent the script to a few users. Can you please share it on your blog? I have to run this every night to backup the database object definations ONLY mainly tables, SPs, Views and FNs.

     Thanks,

     
     
169. on September 8, 2011 at 12:02 pm | Reply Saurabh

     Hi Pinal,

     I’m into Data WareHouse Testing (Some ETL + Reporting mainly).
     Could you please let me know about some QA related Certification if any in DWH.

     Regards,
     Saurabh

     
     
170. on September 9, 2011 at 12:36 am | Reply Wesley

     Hi everyone,
     can anyone help me please?

     I have one query to run colunm A get record from column B. When i upload new record to Colunm A i have to run ths query again to get record to Column B.

     Does it have anyway to run my query automatically when my Column A have a new record?

     Thanks,
     Wesley

     
     
     • on September 9, 2011 at 3:47 pm | Reply madhivanan

       Make use of computed column. Read the same in SQL Server help file

       
       
171. on September 10, 2011 at 11:53 am | Reply babu

     how Inserting data into specific row and how to insert multiple values in to a cell
     ex :stud _name | stu_id
     n1,n2,n3,n4 | 1,2,3,4

     
     
     • on September 12, 2011 at 7:04 pm | Reply madhivanan

       You can have columns with varchar datatypes. However it is good to store them in each row for each value. Read about normalization

       
       
172. on September 14, 2011 at 4:07 pm | Reply Venkat

     HI Pinal,

     We are encountering an issue with the Log file growth.

     We are loading the data into a table from a different source. After the data load the triggers will fire and does the following activities:

     1. load the data in a different database(in a different server using linked server).
     2. Load the data into the a different table in the same database(Only Key columns will be loaded)

     But during this process log file is growing enormously. Log file is growing to 100 GB where as the data file growth is very minimal.

     We have tried to implement the following solutions:

     1. Setting the recovery model to bulk-logged
     2. Scheduled the t-log backup to run for every 30 mins.

     But both the solutions didnt worked. The log file is growing as usual.

     Observations:

     When ever I check the ststus of the field log_reuse_wait_desc under the sys.databases table I am seeing the status as “Active_Transaction”.

     Request for your suggestions on this

     
     
173. on September 15, 2011 at 10:52 am | Reply haritha

     can i create any tables in tempdb ,if yes what is the use if the model database if no where is the security for outer transactions

     
     
174. on September 16, 2011 at 1:47 pm | Reply davidarmstrong982

     Hi Dave
     I have been trying to do somthing in SQL for ages, but end up doing the work on the web page instead, I just wondered if it could be done in SQL.

     – Data –
     Company, Ad, Internal_Viewed, External_Viewd
     ABC, Ad1, 0, 1
     ABC, Ad1, 1, 0
     ABC, Ad1, 1, 0
     ABC, Ad1, 0, 1
     ABC, Ad1, 0, 1

     I would like to group the results and see:-
     Company, Ad, Internal_Viewed, External_Viewd
     ABC, Ad1, 2, 3

     But when I use a GROUP BY, I see
     Company, Ad, Internal_Viewed, External_Viewd
     ABC, Ad1, 0, 3
     ABC, Ad1, 2, 0

     Tried different things, but I just can’t get it to work.

     Thanks
     David.

     
     
175. on September 16, 2011 at 4:30 pm | Reply Teshendra

     Hi,
     Pinal
     I need a help………..
     I have a stored procedure, which calculate interest at the end of each month with two parameters.this interest is added quarterly in customer account with two different parameters.
     I want to schedule the above sp at the end of month as well as first date of quarter(4 th month).
     So kindly provide me solution……………….

     regards,
     Teshendra

     
     
176. on September 19, 2011 at 9:13 am | Reply Jeswin

     Hi Pinal ,

     I am one of the regular readers of your blog.It gives me much insight on the SQL SERVER , as i am interested to work with database and program with it.I have read an aritcle in your blog regarding the SQL SERVER issue on Remote connections (Provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified).I have tried your solution but it seems to be not working in my case , even the server tech team have given the following reply when we approached them regarding the issue, SQL version installed is express and it won’t support for the remote connectivity and programmable APIs.Is this true? Is express a demo database?

     Regards,
     Jeswin

     
     
177. on September 23, 2011 at 12:06 pm | Reply veena

     Can someone pls help out. I have a problem that i would need some assistance. i have a table as mentioned below

     dealer_code dealer_name region month
     A1001 Asha delhi 4/1/2011
     A1002 Boby mumbai 5/1/2011
     A1003 Vijay kolkata 6/1/2011
     A1004 Rathi jodhpur 7/1/2011
     A1005 Gita gujrat 8/1/2011

     what i want accomplish is to have the above mentioned data in the below metioned format. Also the column dealer1 dealer2 dealer3….. changes dynamically

     particulars dealer1 dealer2 dealer3 dealer4 dealer5
     dealer_code A1001 A1002 A1003 A1004 A1005
     dealer_name Asha Boby Vijay Rathi Gita
     region delhi mumbai kolkata jodhpur gujrat
     month 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011
     Pls provide me the solution for the above problem

     
     
178. on September 23, 2011 at 12:09 pm | Reply Veena

     Can someone pls help out. I have a problem that i would need some assistance. i have a table as mentioned below

     dealer_code dealer_name region month
     A1001 Asha delhi 4/1/2011
     A1002 Boby mumbai 5/1/2011
     A1003 Vijay kolkata 6/1/2011
     A1004 Rathi jodhpur 7/1/2011
     A1005 Gita gujrat 8/1/2011

     what i want accomplish is to have the above mentioned data in the below metioned format. Also the column dealer1 dealer2 dealer3….. changes dynamically

     particulars dealer1 dealer2 dealer3 dealer4 dealer5
     dealer_code A1001 A1002 A1003 A1004 A1005
     dealer_name Asha Boby Vijay Rathi Gita
     region delhi mumbai kolkata jodhpur gujrat
     month 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011
     Pls provide me the solution for the above problem
     It’s very urgent

     
     
179. on September 25, 2011 at 12:57 pm | Reply Nawaf

     What built in mechanism does SQL Server have to do Flashback Queries?

     
     
180. on September 26, 2011 at 1:55 pm | Reply sukumar

     USE msdb
     GO
     EXEC sp_send_dbmail @profile_name=’PinalProfile’,
     @recipients=’test@Example.com’,
     @subject=’Test message’,
     @body=’This is the body of the test message.
     Congrates Database Mail Received By you Successfully.’

     Can any tell me how to send a link(Exe file) in the body text . when i click the EXE file link to open the particular exe application screen is it possible or not .

     
     
181. on September 29, 2011 at 6:13 pm | Reply Balu

     Hi,
     Please help me to find the issue!!
     i am working on a large database. (above 400Gb.)
     when i tried backing up the database, it is not completing after 90%. it resumes for almost 12 hours. I usually take back up. but this time it is not completing. I tries using the SQL Management studio and then using the following query. both the times the result was the same. i checked the CPU usage and memory usage. All is showing minimum.

     i used the following query.

     BACKUP DATABASE [TestDB] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\TestDB.bak’ WITH NOFORMAT, INIT,
     NAME = N’TestDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10.
     Thanks in advance.

     
     
182. on October 3, 2011 at 9:03 pm | Reply shelly

     Hi Pinal,

     another help.

     I have 500 records in a table

     email month count
     abcd@123.COM 5 6
     abcd@123.COM 4 1
     rfe@222.com 4 2
     pqr@xyz.com 5 3
     pqr@xyz.com 4 1

     there are duplicate email ids.I want to display the unique ids and the maximum counts and months.for example for abcd@123.com 6 is the maximum count.so the result should display ‘abcd@123.COm 5 6′ and not the
     ‘abcd@123.COM 4 1′

     Please help

     
     
183. on October 4, 2011 at 1:00 am | Reply Scott

     Why doesn’t this work?

     if @repemail = ‘sampleemail1@email.com’
     Begin
     EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘profile1′,
     @recipients = @email,
     –@from_address = @repemail,
     @body = @message,
     @body_format = ‘HTML’,
     @importance = ‘High’,
     @sensitivity = ‘Confidential’,
     @subject = @account
     select @RowCnt = @RowCnt + 1
     end
     else if @repemail = ‘sampleemail2@email.com’
     Begin
     EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘profile2′,
     @recipients = @email,
     –@from_address = @repemail,
     @body = @message,
     @body_format = ‘HTML’,
     @importance = ‘High’,
     @sensitivity = ‘Confidential’,
     @subject = @account
     select @RowCnt = @RowCnt + 1
     end

     
     
     • on October 4, 2011 at 1:31 pm | Reply shelly

       you have not declared the variables and you have not assigned value rowcount

       
       
184. on October 4, 2011 at 1:37 pm | Reply shelly

     Try this..

     declare @repemail varchar(max)
     declare @rowcnt int,@email varchar(max),@message varchar(max),@account varchar(max)
     set @rowcnt=0
     if @repemail =’sampleemail1@email.com’
     Begin
     EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘profile1′,
     @recipients = @email,
     @from_address = @repemail,
     @body = @message,
     @body_format = ‘HTML’,
     @importance = ‘High’,
     @sensitivity = ‘Confidential’,
     @subject = @account
     select @RowCnt = @RowCnt + 1
     end
     else if @repemail =’sampleemail2@email.com’
     Begin
     EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘profile2′,
     @recipients = @email,
     @from_address = @repemail,
     @body = @message,
     @body_format = ‘HTML’,
     @importance = ‘High’,
     @sensitivity = ‘Confidential’,
     @subject = @account
     select @RowCnt = @RowCnt + 1
     end

     
     
     • on October 5, 2011 at 7:15 pm | Reply Scott

       I’ve declared that already. I am on MS SQL 05. Here is the entire script…

       CREATE PROCEDURE dbo.Email_Loan_Officers

       AS

       BEGIN

       declare @RowCnt int
       declare @MaxRows int
       DECLARE @NL AS CHAR(2)
       declare @cif nvarchar(30)
       declare @account nvarchar(30)
       declare @name nvarchar(30)
       declare @note nvarchar(4000)
       declare @email nvarchar(30)
       declare @username nvarchar(30)
       declare @repemail nvarchar(30)
       declare @subject nvarchar(30)
       declare @message nvarchar(4000)

       DECLARE @Import TABLE

       (
       rownum int IDENTITY (1, 1) Primary key NOT NULL ,
       cif nvarchar(30),
       account nvarchar(30),
       name nvarchar(30),
       note nvarchar(4000),
       email nvarchar(30),
       username nvarchar(30),
       repemail nvarchar(30)
       )

       insert into @Import (cif, account, name, note, email, username, repemail)
       select master.id1, master.account, master.name, notes.comment, miscextra.thedata, users.username, users.email
       from master
       inner join notes on notes.number = master.number
       inner join users on users.loginname = notes.user0
       inner join miscextra on miscextra.number = master.number
       where master.customer in (’0001140′, ’0001196′)
       and notes.created >= { fn CURDATE() } AND notes.created < DATEADD(DAY, 1, { fn CURDATE() })
       and notes.action = 'EM' and notes.result = 'EM'
       and miscextra."title" = 'OFFICER EMAIL'

       select @MaxRows=count(*) from @Import

       select @RowCnt = 1

       set @NL = CHAR(13) + CHAR(10)

       while @RowCnt <= @MaxRows

       begin
       set @email = 'email@email.com'

       set @cif = (select cif from @Import where rownum = @RowCnt)
       set @account = (select account from @Import where rownum = @RowCnt)
       set @name = (select name from @Import where rownum = @RowCnt)
       set @note = (select note from @Import where rownum = @RowCnt)
       –set @email = (select email from @Import where rownum = @RowCnt)
       set @username = (select username from @Import where rownum = @RowCnt)
       set @repemail = (select repemail from @Import where rownum = @RowCnt)
       set @message = '’ +
       ‘body {font-size: 12px; font-family:Arial;}’ +
       ‘CIF#: ‘ + @cif +
       ‘Account: ‘ + @account +
       ‘Name: ‘ + @name +
       ” + @note +
       ” + @username + ” +
       ‘PO Box 5555 Anywhere, USA 999999′ +
       ‘P: (555) 555-5555 – (444) 444-1234′ +
       ‘F: (777) 333-3333′ +
       ‘E: ‘ + @repemail + ” +
       ”

       if @repemail = ‘email1@email.com’
       Begin
       EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘profile1′,
       @recipients = @email,
       –@from_address = @repemail,
       @body = @message,
       @body_format = ‘HTML’,
       @importance = ‘High’,
       @sensitivity = ‘Confidential’,
       @subject = @account
       select @RowCnt = @RowCnt + 1
       end
       else
       EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘profile2′,
       @recipients = @email,
       –@from_address = @repemail,
       @body = @message,
       @body_format = ‘HTML’,
       @importance = ‘High’,
       @sensitivity = ‘Confidential’,
       @subject = @account
       select @RowCnt = @RowCnt + 1

       end
       END
       GO

       
       
185. on October 7, 2011 at 8:21 pm | Reply Greg Ko

     Dear Pinal,

     I enjoy your blog. Thank you for teaching us.

     Please forgive newbie’s ignorance!

     How do I grab the value of the output parameter in execute sp_executesql?

     I can see the output but cannot get to it:

     DECLARE @LastActivity nvarchar(100)
     DECLARE @LastActivityDate datetime
     DECLARE @sql nvarchar(MAX)
     DECLARE @RowsToProcess int
     DECLARE @CurrentRow int
     DECLARE @SelectCol1 nvarchar(100)
     DECLARE @SelectCol2 nvarchar(100)
     DECLARE @SelectCol3 nvarchar(100)
     DECLARE @LastDate TABLE (RowID int not null primary key identity(1,1), col4 nvarchar(MAX), col5 sql_variant)
     DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 nvarchar(100),col2 nvarchar(100),col3 nvarchar(100))
     INSERT into @table1 (col1,col2,col3)(SELECT t.name AS col1, c.name AS col2, m.Field1 as col3
     FROM sys.columns c INNER JOIN
     sys.tables t ON c.object_id = t.object_id INNER JOIN
     sys.schemas s ON t.schema_id = s.schema_id INNER JOIN
     dbo.MERGE_TABLES m ON m.Table_Name=t.name
     WHERE c.name LIKE ‘%[_]DATE%’ and m.[Enabled]=’Y')
     SET @RowsToProcess=@@ROWCOUNT
     SET @CurrentRow=0
     WHILE @CurrentRow SET @sql=’set @date =(‘SELECT ‘ + ‘[dbo].[ConvertToDatetime](MAX(‘ +
     > @SelectCol2 + ‘))’ + ‘ FROM ‘ + @SelectCol1 + ‘ Where ‘ + @SelectCol3
     > + ‘ = ‘ + ”’0722607QZ”’ ) ‘

     the above sql gives me error: Incorrect syntax near ‘.’

     >SET @sql=’set @date =(SELECT [dbo].[ConvertToDatetime](MAX( + @SelectCol2 + ))
     >FROM @SelectCol1 Where @SelectCol3 ”=0722607QZ” ) ‘

     The above sql gives the error: Must declare the scalar variable “@SelectCol2″

     SET @sql=’SELECT ‘ + @date + ‘=convert(nvarchar(100), [dbo].[ConvertToDatetime](MAX(‘ + @SelectCol2 + ‘)))’ + ‘ FROM ‘ + @SelectCol1 + ‘ Where ‘ + @SelectCol3 + ‘ = ‘ + ”’0722607QZ”’

     > the above produces the error : Implicit conversion from data type sql_variant to nvarchar is not
     > allowed. Use the CONVERT function to run this query.

     SET @sql=’SELECT ‘ + @date + ‘=convert(nvarchar(MAX),(MAX(‘ + @SelectCol2 + ‘))’ + ‘ FROM ‘ + @SelectCol1 + ‘ Where ‘ + @SelectCol3 + ‘ = ‘ + ”’0722607QZ”’

     > the above produces no error but all output is NULL, no values.

     Best regards
     Greg

     
     
186. on October 10, 2011 at 6:34 pm | Reply srikanth

     hi pinal sir, i am looking for a solution to query xml passed as a parameter to procedure where in i need to query xml and store the name=value pair to actual physical columns in a table. so can any body give solution.

     
     
187. on October 15, 2011 at 1:28 am | Reply Srinivas

     Hi Pinal,

     I’m planning to run unattended installation from remotely, can you please advice can i do it or not. Details are below:

     1. My setup.exe files are share location like \\Server 1,
     2. My sql configuration.ini file is in share location \\Server 2.
     3. Using batch I have to install SQL Server in other servers at a time (I’m planning to install SQL Server in 15 different server) this batch file is in share \\server 3.

     I have written one batch file with the following Query for one server but it is not working. Please help me .

     psexec \\ -u -p “\\\\\\setup.exe” /ConfigurationFile=\\\\

     if you have any other ideas or scripts please share with me please

     reply to me : [email removed]

     Thanks and Regards
     Srinivas

     
     
188. on October 17, 2011 at 8:18 am | Reply Prabhu

     Hi Pinal
     Iam running SQL server 2008 on Stand alone windows 7 64bit machine

     was using Adventure works2008 for practice

     When I try to access to access to MSDB folder

     it throws following errors

     “=================================

     Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

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

     ——————————
     Program Location:

     at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
     at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
     at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
     at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
     at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.GetChildren(IGetChildrenRequest request)
     at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

     ===================================

     The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in Server 2008 Books Online.

     A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
     Client unable to establish connection
     Registry information is corrupt or missing. Make sure the provider installed and registered correctly. (MsDtsSrvr)

     ——————————
     Program Location:

     at Microsoft.SqlServer.Dts.Server.StorageSqlServer.Connect()
     at Microsoft.SqlServer.Dts.Server.StorageSqlServer.GetFolderContent()
     at Microsoft.SqlServer.Dts.Server.Storage.GetFolderContent(String folder)
     at Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String sPackageFolder, String sServerName)
     at Microsoft.SqlServer.Dts.SmoEnum.PackageEnum.GetData(EnumResult erParent)
     at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
     at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
     at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
     at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

     ===================================

     A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
     Client unable to establish connection
     Registry information is corrupt or missing. Make sure the provider installed and registered correctly. (Microsoft SQL Server Native Client 10.0)

     ——————————
     Program Location:

     at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
     at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
     at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
     at System.Data.OleDb.OleDbConnection.Open()
     at Microsoft.SqlServer.Dts.Server.StorageSqlServer.Connect()

     ”

     Could you please help me with this

     Been stuck on this for a while

     
     
189. on October 17, 2011 at 8:19 am | Reply Prabhu

     Sorry,,,wasnt precise in my query,,,when trying to access MSDB folder in SSIS

     
     
190. on October 17, 2011 at 4:13 pm | Reply Hasmukh

     Hi Pinal,

     I want to know which query execute fast from the below query.

     I have on table name : Student Master
     I want filter record using std_id coloum in strucent master which is between 1 to 100.
     I write query like

     Query 1 :
     declare @std_idFrom as int
     declare @std_idTo as int
     declare @v_where as varchar(100)
     declare @v_qyery as varchar(500)

     set @std_idFrom =1
     set @std_idTo =100

     set @v_where=’ where 1=1 ‘
     set @v_where=@v_where + ‘ std_id between ‘ + cast(@std_idFrom as varchar(10)) + ‘ and ‘ + cast(@std_idTo as varchar(10))

     set @v_qyery = ‘select * from Student Master ‘ + @v_where

     exec(@v_qyery)

     Query 2 :
     declare @std_idFrom as int
     declare @std_idTo as int

     select * from Student Master where std_id between + @std_idFrom + and + @std_idTo

     From above query which one execute fast and what the difference in execuation

     
     
     • on November 4, 2011 at 6:56 pm | Reply Hasmukh Kholakiya

       hi pinal sir…

       Please give me solution i am waiting for right solution.

       Thanks & Regards
       Hasmukh Kholakiya

       
       
191. on October 19, 2011 at 4:33 pm | Reply Dharmendra Rai

     Hi Pinal,

     I am great fan of yours ,and would like to thank you for this great Blog.
     i am also having a problem that my database server has crashed and i had to rebuild it but after re-installation sms service is not functioning well when ever sms jobs is enabled it slows down the application server and exception occurs as below

     Memory
     MemoryLoad = 39%
     Total Physical = 8182 MB
     Available Physical = 4991 MB
     Total Page File = 14123 MB
     Available Page File = 11089 MB
     Total Virtual = 2047 MB
     Available Virtual = 288 MB
     **Dump thread – spid = 0, PSS = 0×00000000, EC = 0×00000000

     Please help due to this i am facing lot of problem

     Thanks in advance.

     
     
192. on October 19, 2011 at 7:59 pm | Reply Ramesh Muchhadiya

     HI Pinal,
     Two Table Here In Table One Fetch Data And Compare To other Table And Create / insert Third Table.
     I Want SQl Script Of That.

     
     
193. on October 21, 2011 at 3:14 am | Reply Hozefa Unwala

     I have one table called person

     I have three columns called firstname, lastname and status.

     I have 100 employees working in my team.

     I want to create one stored procedure with one parameter is on first name

     I can write a query like Select firstname,lastname from person where firstname=’AnyName’

     But how do i write a name with multiple values. but my parameter name should be one and i will delimit my parameter value with ‘;’

     Thanks.

     
     
     • on October 24, 2011 at 10:03 pm | Reply madhivanan

       Search for “split csv strings” in this site. You will find related links

       Another method would be

       where ‘,’+@fname+’,’ like ‘%’+fname+’,%’

       
       
194. on October 24, 2011 at 2:01 pm | Reply Avinash

     Hi Pinal,
     Your web site SQLAUTHORITY is not opening up when searched from google. Pls check.

     
     
     • on October 24, 2011 at 7:34 pm | Reply madhivanan

       Can you post the link? What is the error you are getting?

       
       
195. on October 26, 2011 at 1:14 pm | Reply RAVINDRAN.P

     Hi Pinal

     I need a advise regarding sql server 2008 R2 audit.

     We have Sql server instance and all the transactions are occuring through our Inhouse application only. Some times we database team forced to do insert / update / delete records through SQLSERVER MANAGEMENT STUDIO. We need to create audit only for back door operation. Is it possible?

     I tried to create a audit through SQLSERVER MANAGEMENT STUDIO but it
     creates each and every logs from all applications. so it effects database performance as well as disk cost. We do not need this because
     application is strictly controlled user interaction.

     Awaiting for your reply

     Rgds

     Ravindran.P
     System Analyst

     
     
196. on October 26, 2011 at 7:18 pm | Reply rommell143@yahoo.com

     Hi Pinal,

     I would like to know how I can audit Windows Login account login and logout times for an entire SQL Server Instance.

     Could you please help.

     Best,

     Ronaldo.

     
     
197. on October 27, 2011 at 12:41 pm | Reply Kajal Kaur

     Hello Pinal,

     My server has 4CPUs 32bit sql server 2008 OS:windows 2008.
     I hav been facing a problem maximum CPU utilization by sql server(90 to 100%)there is only one database that is extensively used and only one application.But still the cpu utilization suddenly increases to 100% and the application gets hanged and we have to sometimes restart the server.Have enabled the Awe settin which was not there before and also monitored the server it shows high wait times for CXPACKET,ASYNC_NW_IO,LATCH_EX,SOS_SCHEDULAR_YEILD,
     Hence i changed the MDOP which was 0 to 3 and it worked fine for a week but now again it is showing huge wait time for SOS_Schedular_yeild.
     Also have to mention that there is only one drive i.e D: drive on the server. I am new to the performance issues hence unable to decide on whether we can use performance monitor to detect this as not sure of the counters to be used.
     Its has 8GB memory.Also checked for blocking and updated the ststistics and also reindexed the tables .
     Please assist in the same……..

     
     
198. on October 27, 2011 at 10:00 pm | Reply Praveen

     Hi Pinal,
     1.I want to get the list of tables in a database which is having morethan 3years of data..i have lot of tables in the database,if i check each and every table it will take moretime right?
     2.sqlserver2000,How to know the tables which are having morethan 50% fragmentation.DBCC SHOWCONTIG is giving fragmentation information about all the tables..

     Please help me..

     Thanks,
     Praveen

     
     
199. on November 4, 2011 at 6:10 pm | Reply Kajal Kaur

     Hello Pinal,

     My server has 4CPUs 32bit sql server 2008 OS:windows 2008.
     I hav been facing a problem maximum CPU utilization by sql server(90 to 100%)there is only one database that is extensively used and only one application.But still the cpu utilization suddenly increases to 100% and the application gets hanged and we have to sometimes restart the server.Have enabled the Awe settin which was not there before and also monitored the server it shows high wait times for CXPACKET,ASYNC_NW_IO,LATCH_EX,SOS_SCHEDULAR_YEILD,
     Hence i changed the MDOP which was 0 to 3 and it worked fine for a week but now again it is showing huge wait time for SOS_Schedular_yeild.
     Also have to mention that there is only one drive i.e D: drive on the server. I am new to the performance issues hence unable to decide on whether we can use performance monitor to detect this as not sure of the counters to be used.
     Its has 8GB memory.Also checked for blocking and updated the ststistics and also reindexed the tables .
     Please assist in the same……..
     Please help!!!!!!!

     
     
200. on March 20, 2012 at 6:23 pm | Reply Nila

     Hi ,

     I am facing pecuilar behaviou rin CASE statement with Convert function

     DECLARE @client_charge_amount numeric (38,20),@client_charge_ccy varchar(3),
     @client_charge_amount1 numeric (38,20)
     SELECT @client_charge_amount = 15.00000000000000000000 ,@client_charge_ccy =’USD’,
     @client_charge_amount1 = 14.00000000000000000000
     SELECT CASE @client_charge_ccy
     WHEN ‘JPY’ THEN CONVERT(numeric(38, 0), @client_charge_amount)
     WHEN NULL THEN NULL
     ELSE
     CONVERT(numeric(38, 2), @client_charge_amount1)

     END AS client_charge

     Though goes inside ELSE part ,it does not give result with 2 decimal
     Expected Output : 14.00
     Actual Output : 14

     Whenever we are using contradiction in convert of numeric in decimal point, it took least decimal point one alone.

     Can anyone help me how to fix and make decimal point according to input
     value

     
     
201. on May 30, 2012 at 1:08 pm | Reply Harshal

     Hi,

     currently i am working on Core banking project and i was stuck at one point i.e sending SMS from SQL Server. When customer do any kind of Transaction like Credit, Debit immediately Customer has to receive Transaction SMS.

     pl help me pinal, it is very difficult task for me pl help me

     
     
202. on October 20, 2012 at 2:57 am | Reply sunny

     hi Pinal
     My Name Is Sunny

     We have a dedicated server that will host our client’s database for a web portal. To connect to this database, you need an ip address.

     When I attempt to create a subscription from a client, it will not let me put the ip address in, I am getting:

     SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name.

     Do I have my publication and subscription confused? How do I connect to a Publication that is not local?

     Kindly Solve Me This Issue ,if you have Any Video, Link or Information About Merge-Replication Configuration Dedicated Server IP

     please send me My Email Id Is (sysman.sadiq@gmail.com)

     Please Help Me out About thie Issue….I m Very Thank Full To You

     
     
203. on January 21, 2013 at 2:19 pm | Reply Made Damodara Rao

     mms and images send receive authurity

     
     
204. on February 28, 2013 at 9:40 pm | Reply S Kumar

     Hi Pinal,

     We are using sql server 2008 on test server. In table column datatype is defined as nvarchar. If I pass value like ‘Domain\User’ ( i.e. Test\1234) in profiler it is removing backslash and executing like Test1234 it has removed backslash. But same sql statement works well on development system.
     If I pass Test\3001234 then in sql profiler it is displaying like Test?1234 i.e. \300 converted to ?.

     Can you help on this? Why backslash has been removed.

     Thanks,
     S Kumar

     
     

Leave a Reply Cancel reply

Enter your comment here...

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

Email (required) (Address never made public)

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.