Contact Me – Archive 10

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

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1800 articles on the subject on his blog at http://blog.sqlauthority.com. He has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Copyright violation and Reproduction of blog:

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

Search SQLAuthority.com

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

Community Rules

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

My Profile

pinal “at” SQLAuthority.com

About these ads

249 thoughts on “Contact Me – Archive 10

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

  5. 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′)

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

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

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

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

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

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

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

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

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

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

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

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

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

  19. hi pinal ,

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

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

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

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

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

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

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

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

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

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

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

  29. 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];

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  56. Hi Pinal,

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

    Pls. suggest.

    Thanks & Regards
    Namit Jain

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

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

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

  60. Dave,

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

    Thanks,

    Eddie

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

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

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

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

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

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

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

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

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

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

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

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

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

  73. Pingback: SQL SERVER – 14 Best Practices for Better Database Performance

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

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

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

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

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

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

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

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

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

  83. 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????)

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

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

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

    • 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

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

  88. Hi,

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

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

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

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

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

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

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

  95. @Murugan Chokkalingam

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

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

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

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

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

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

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

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

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

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

  104. Hi Pinal,

    Can you share some interview questions on Microsoft Business Intelligence?

    Thanks for such a nice informative blog.

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

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

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

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

    • 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

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

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

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

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

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

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

    • 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

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

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

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

  118. Select * From Table_Name

    AND

    Select * From Table_Name Where 1=1

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

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

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

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

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

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

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

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

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

    • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s