Contact Me – Archive 9

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1600 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and 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.

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

About these ads

204 thoughts on “Contact Me – Archive 9

    • If you are referring about the tables inserted and deleted, they are explicitely used in the triggers whose data structure rely on the underlying tables in which the trigger is created

  1. Hi:
    Is it possible to create a computed column that will serve as primary key using a VarChar column and an Idenity int column.

    For example:
    VarChar column value = X
    Identity value = 1
    Primary Key column = X1

    Thanks!

  2. Sir,
    I faced a problem while putting validation rules in sql server 2005. I need to put some validation rules on text type of field. I tried to add check constraint with the field having data type as text but it gave me error message. I again tried to add a trigger to validate the input but it gave the error for text type of field.

    So please tell me how to validate input for text type of field to check whether it has values like ‘%some text%’.

    Please give me answer ASAP.

    Thanks.

    I once placed a query but didn’t get any answer, hope for this time.

  3. Hi madhivanan,

    yes you are correct i was referring to the inserted and deleted tables which are created in trigger when a new row is added or deleted .
    now my query is how can we query r see results of deleted table
    can any one explain with an example

    Thanks,
    Ahmed Tabrez

  4. Hi Dave,
    I post it with too much hope.I am an intern-ship student doing migration database. I’ m newbie for most of things.I have administrator right. Batch file is saved with ANSI.
    I want to run batch file (include sqlcmd ) with sql server agent.
    I change ISQL command to SQLCMD and make it sql server agent jobs.I can’t parse syntax and got following error:

    // An exceptional occurred while executing a Transact-SQl statement or batch.
    (Microsoft.SqlServer.ConnectionInfo)

    Incorrect syntax near ‘LOADS’.
    An expression of non-boolean type specified in a context where a condition is expected, near ‘C:’.
    The label ‘C’ has been already been declared. Label names must be unique within a query batch or stored procedure. (Microsoft SQl Server, Error:102)//

    —————My syntax is (batch file)
    (converted from below original isql)

    REM JOB LOADS DAILY LEW FILE
    REM 21/7/2000 Change to MSSQL
    REM
    ebrun.exe C:\Shared\NPAC_Bat_2008_Test\lewd.ebx
    REM check lec exist
    IF exist C:\Shared\NPAC_Bat_2008_Test\lewd goto process
    exit
    :process
    sqlcmd -U user -P Password -S Myserver C:\Shared\NPAC_Bat_2008_Test\trlewd.log
    bcp npacdb..lewd_tmp in c:\Shared\NPAC_Bat_2008_Test\lewd -fC:\Shared\NPAC_Bat_2008_Test\lewd.fmt -Sfmsdev01>C:\Shared\NPAC_Bat_2008_Test\bcplewd.log
    sqlcmd -U user -P Password -S Myserver C:\Shared\lewd.log
    D:
    del C:\Shared\NPAC_Bat_2008_Test\lewd

    ——————————————————————
    original isql (2000 version)

    REM JOB LOADS DAILY LEW FILE FROM POWERSUPPLY
    REM 21/7/2000 Change to MSSQL
    REM
    ebrun.exe C:\Shared\NPAC_Bat_2008_Test\lewd.ebx
    REM check lec exist
    IF exist C:\Shared\NPAC_Bat_2008_Test\lewd goto process
    exit
    :process
    isql -Uuser -Ppassword -Sserver C:\Shared\NPAC_Bat_2008_Test\trlewd.log
    bcp npacdb..lewd_tmp in c:\Shared\NPAC_Bat_2008_Test\lewd -fC:\Shared\NPAC_Bat_2008_Test\lewd.fmt –Uuser -Ppassword -Sserver>C:\Shared\NPAC_Bat_2008_Test\bcplewd.log
    isql -Uuser -Ppassword -Sserver c:\npac\schedule\logs\lewd.log
    D:
    del C:\Shared\NPAC_Bat_2008_Test\lewd

    then I change to this way………………..
    REM ‘LOAD’
    :setvar firstdir “C:\Shared\lewd.bat”
    -setvar secdir “C:\Shared\trlewd.txt”
    :setvar thirddir “C:\Shared\trlewd.log”
    :setvar forthdir “C:\Shared\lewd.fmt”
    :setvar fifthdir “C:\Shared\bcplewd.log”
    :setvar sixthdir “C:\Shared\lewd.txt”
    :setvar seventhdir “C:\Shared\lewd.log”
    if exist $(firstdir) goto process
    :process
    sqlcmd -U user -P Password -S myserver -i $(secdir)
    go
    bcp npacdb..lewd_tmp in $(forthdir) -U user -P Password -S myserve
    go
    sqlcmd U user -P Password -S myserve -i $(sixthdir)
    go
    :D
    del C:\Shared\lewd

    ———————————
    I still got this error

    Incorrect syntax near ‘:’ . (Microsoft SQL Server, Error 102)

    Pls Pls Pls Pls help me! I m torturing one week already with this.I don’t know what to do. Tomorrow is dead line. Hoo!
    If anything wrong, pls forgive me.
    Thank you very much in advance.
    Thanks & Regards,
    Kaysi

  5. Hi Pinal,
    I am software engineer at pune with 4+ years of experience in SQL server.
    The problem I am facing is I am into technicle support kind of things where I am into correcting the client database records to make sure that our software works fine for the clients. In this job I am not into writting any procedures,triggers or any complex codes. Before this I was into development there I was in touch with writting procedures, triggres, cursors and I was quite good at it.
    Now I have no issues with writting all these things, but wherever I go for the interviwes they are asking for the Query tuning and optimization. Unfortunately I do not have any experience in this fields.
    I have already gone through most of your blogs in query optimization and tuning tried at my end also read lots of books/articles on this, but I am not very much confident about it.
    Can you please suggest me the best way to face these interviwes and if you know anybody from pune which provides training on advance SQL Query tuning and optimization?

    Thanks!
    Sachin :)

  6. Hi,
    We are using the following SP in SQL Server 2008 R2 to retrieve tables having changes (Insert, Update, delete) from a specific version (Using Change Tracking). But it takes about 3 minutes for a 1000 tables database. Is there any way to increase the performance?

    ALTER PROCEDURE [dbo].[spGreenChangedTables] ( @Last_version BIGINT, @SYS_CHANGE_CONTEXT VARCHAR(250) = ‘GreenReplicationChanged’, @TableName sysname = ” ) AS
    BEGIN SET NOCOUNT ON; –============================== DECLARE @SQL NVARCHAR(MAX) DECLARE @TBL SYSNAME DECLARE @RET INT SET @SQL = ‘ CREATE TABLE #TMP(ID int IDENTITY(1,1) PRIMARY KEY,TBL SYSNAME) ‘ + CHAR(13) SELECT @SQL = @SQL + ‘ INSERT INTO #TMP(TBL) ‘ + CHAR(13) + ‘ SELECT TOP 1 ”’ + TGRT.Name + ”’ ‘ + CHAR(13) + ‘ FROM CHANGETABLE(CHANGES ‘ + TGRT.Name + ‘ , ‘ + CAST(@last_version AS VARCHAR(50)) + ‘) AS CHNG ‘ + CHAR(13) + ‘ WHERE 1 = 1 ‘ + CHAR(13) + ‘ AND ISNULL(CHNG.SYS_CHANGE_CONTEXT,0) CAST(”’ + CAST(@SYS_CHANGE_CONTEXT AS VARCHAR(MAX)) + ”’ as varbinary(128)) ‘ + CHAR(13) + ‘ ;’ + CHAR(13) FROM sys.tables AS TGRT INNER JOIN sys.change_tracking_tables AS CTT ON TGRT.object_id = CTT.object_id WHERE 1 = 1 AND TGRT.name LIKE ‘tbl%’ AND TGRT.Name = CASE @TableName WHEN ” THEN TGRT.Name ELSE @TableName END –================================================================== SET @SQL = @SQL + CHAR(13) + ‘SELECT * FROM #TMP ‘ –PRINT @SQL EXEC(@SQL) END
    Thanks in Advance,
    Maral

  7. Hi,

    I am not an SQL pro. But my simple requirement is as follows. I have 2 SQL Tables one having Tagnames and the other having the corrsponding tag values for the Tagnames. I have to create a new table where Tagnames will be the column names and the corresponding tag values for that will be under each column. Could anybody can help me with some sample code for this.

    Regards,

    Manash

  8. Sir,

    I am having three tables

    1 Trans_transnar (view) ‘ AROUND 1 LAKE RECORDS

    2 Arbills ‘ 30000 RECORDS

    3 Argls ‘ 200 RECORDS

    I Want to Update ARBILLS TABLE ,
    By Select data from TRANS_TRANSNAR If that data Is
    NOT EXIST IN ARBILLS, THEN ONLY I Have To INSERT DATA INTO THE ARBILLS Table. And Also I have To Check
    With ARGLS TABLE. If The GLCODE IN THE ARGLS Only
    Select From The TRANS_TRANSNAR VIEW.
    I write the Query for that. Its working.
    But I dont know perfortmancewise it is correct or not.
    If its NOT a Correct Method. Please Tell
    us how to write this Query.

    Thank you,

    Insert into USERDATA.DBO.ARBILLS (trtype,COCODE,BRCODE,loccode,glcode,slcode,trdt,trno,BILLNO,billdt,billamt,BALAMT,regno)
    Select f.trtype,f.COCODE,f.BRCODE,f.loccode,f.glcode,f.slcode,f.trdt,f.trno,f.REFNO,f.refdt,ROUND(f.tramt,2,0) AS BILLAMT,ROUND(f.TRAMT,2,0) AS BALAMT,isnull(F.regno,’-‘) as regno FROM
    (SELECT A.COCODE,A.BRCODE,A.LOCCODE,A.TRTYPE,A.TRNO,A.TRDT,A.GLCODE,A.SLCODE,A.TRAMT,A.REFDT,A.REFNO,A.DRCRFLAG,SUBSTRING(A.NARRATION,21,50) AS REGNO FROM ARGLS B ,trans_transnarr A
    WHERE ( A.COCODE = ‘C0001′ AND B.LOCCODE = ‘L0001′) AND B.LOCCODE = A.loccode AND B.GLCODE = A.glcode AND CANCDT IS NULL and a.refdt 0 and f.drcrflag = ‘D’ and len(f.slcode) >0)

  9. /* To Create Table */

    If Object_ID (‘Details’) Is Null
    Begin
    Create Table Details
    (
    FirstName Varchar(100),
    MiddleName Varchar(100),
    LastName Varchar(100)
    )
    End
    Else
    Begin
    Print ‘Change the table Name’
    End
    GO
    /* To Insert Records */

    Insert Into Details (FirstName,MiddleName,LastName)
    Values (‘ABC’,’DEF’,’GHI’)

    GO
    /* To Retrieve Records */

    Select FirstName,MiddleName,LastName From Details
    GO

    I want out put like this……
    Please help

    ColumnName Value
    ———- —–
    FirstName ABC
    MiddleName DEF
    LastName GHI

  10. Create Table Details
    (
    FirstName Varchar(100),
    MiddleName Varchar(100),
    LastName Varchar(100)
    )
    GO
    Insert Into Details (FirstName,MiddleName,LastName)
    Values (‘ABC’,’DEF’,’GHI’)
    GO
    Select FirstName,MiddleName,LastName From Details
    GO

    I want out-put Like This
    Please help……

    Select ‘FirstName’ ‘ColumnName’,’ABC’ ‘Value’
    Union
    Select ‘MiddleName’,’DEF’
    Union
    Select ‘LastName’,’GHI’
    Order By Value

  11. Hi all,
    pls…………………..help me
    I really don’t know what’s wrong with my sqlcmd command
    Pls help me point out where did I wrong.

    Thanks million

    Thanks & Regards,
    kaysi

    • Hi,
      How r u comparing the efficiency of func and SP ?
      Each of these two are meant for diff usage.
      Func : should return scalar/table to the calling statement
      SP : execute a series of sql statements but not embedded in any sql statement. SPs are compiled (if there is not Dynamic sql) unlike Funcs are recompiled and executed

    • Static queries are always faster as the objects are resolved during compilation. If you dont know to which object you need to refer, then you would go for Dynamic sql – here, objects names are resolved during runtime which makes the sql to be recompiled

  12. Hi Pinal,
    I have created a linked server from SQL 2000 to 2008 using SQL OLE DB Provider, but querying the remote SQL 2008 server only works intermittently. I get: Server: Msg 8179, Level 16, State 1, Line 2 Could not find prepared statement with handle -1. I installed the SQL 2008 native client on the SQL 2000 server but still the same. The Windows firewall is not enabled on the server either. Any ideas?
    The query I am using is:

    SELECT mmatter, clname1 + RTrim(‘ ‘ + IsNull(clname2, ”)), mdesc1 + RTrim(‘ ‘ + IsNull(mdesc2, ”)), mopendt, mbillaty, msupaty
    FROM matter
    INNER JOIN client ON matter.mclient = client.clnum
    INNER JOIN udf ON matter.mmatter = udf.udjoin AND udtype = ‘MT’ AND udfindex = 87
    WHERE mstatus ‘CL’
    AND udvalue = ‘LSLONDOFF’
    AND NOT EXISTS
    (SELECT *
    FROM RemoteServer.Extranet.dbo.Matter Matter
    WHERE Reference COLLATE Latin1_General_CI_AI = matter.mmatter COLLATE Latin1_General_CI_AI
    AND IsNull(Matter.PendingDelete, 0) = 0
    AND Matter.SiteID 2)
    ORDER BY 1
    GO

    Thanks
    Stuart

  13. Hello Sir,

    I have a problem.Please help me out.Here is the situation.

    There is a database test1 on server1.
    under this database test1 ,there is a table t1 having 5 columns.

    name
    roll
    phone
    address
    address2

    Now there is another database test2 on server2.
    under this database test2. I want to have a replica of t1 as t1 but with some more columns say 5 columns i.e this table will have 5+5 = 25 columns.

    name
    roll
    phone
    address
    address2
    marks1
    marks2
    marks3
    marks4
    marks5

    what i want to achieve is that, if there is any changes in any column values in t1 under database test1 on server1,that should automatically reflect in t1 under database test2 on server2.

    i.e, these 2 tables should always be in sync but marks1,marks2,…values should remain intact.I donot want to loose these values.

    How can i do this?Please help me out ASAP.

    Thanks,
    Dhiraj

      • Thanks Vinod,
        Here is what i have done on test1.t1

        ALTER TRIGGER [Driver_update_Rtsbos2]
        ON [dbo].[DriverInfoTest]
        AFTER INSERT
        AS
        BEGIN
        insert into webserver.RtsBosDataBase.dbo.driverinfonew (ID,Name,DriverPin,Sex,FatherName,NRICNo,DateOfBirth,
        PhoneNo,Address,OwnerId,LicenseNo,LicenseIssueDate,issuePlace,
        LicenseValidDate,Licensetype,TaxiNo,SmartCardID,
        DriverType,Reference1,RefAddress1,Reference2,RefAddress2,status,
        FrID,deletestatus,driverid,CompanyId,SubsStatus,
        SubsStartFrom,SubsActivate,language,CUGCardNo,CabNo)

        (select ID,Name,DriverPin,Sex,FatherName,NRICNo,DateOfBirth,
        PhoneNo,Address,OwnerId,LicenseNo,LicenseIssueDate,issuePlace,
        LicenseValidDate,Licensetype,TaxiNo,SmartCardID,
        DriverType,Reference1,RefAddress1,Reference2,RefAddress2,status,
        FrID,deletestatus,driverid,CompanyId,SubsStatus,
        SubsStartFrom,SubsActivate,language,CUGCardNo,CabNo from orix.dbo.driverinfotest where ID=@@identity)
        END

        After creating above trigger I was getting an SQL exception ‘The partner transaction manager has disabled its support for remote/network transactions’
        Then after doing some settings on both servers,this exception has gone but a new problem has come.Now it is giving ‘No transaction is active’.Although there is a transaction on test1.t1.

        Can u suggest me any solution?
        Thanks,
        Dhiraj

  14. 1) Is there a quick way to find out what port number SQL is listening to? I have 10 instances running on one server and I could find out by going to SQL log but it’s bit time consuming.

    please tell me in any query to retrive this listner port list?

    Regards
    harish

  15. You wrote a blog entry about setting up Database Mail – http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/ – which was great and I was able to send out email tests in SQL Server 2008 R2 with no issues.

    Now, however, I want to use that account to send out Notifications for various Job failures and the account does not appear as an option in a given Job’s Notifications tab. Can you please help?

  16. I have created your query from your article series:SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2 per the following: I am geting this error, How do I assign the required permission

    Msg 262, Level 14, State 1, Procedure vw_ViewLimit1, Line 4
    CREATE VIEW permission denied in database ‘AdventureWorks’.

    USE AdventureWorks
    GO
    IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]’))
    DROP VIEW [dbo].[vw_ViewLimit1]
    GO
    — Create View on sample tables
    CREATE VIEW vw_ViewLimit1
    AS
    SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
    ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
    ,[LineTotal],[ReferenceOrderID]
    FROM Sales.SalesOrderDetail sod
    INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
    GO

  17. Hi Dave,

    I have a project where I need to create a #temp table that has uniquely named rows for a given condition. so..

    If x=1
    Create table #temp(
    name varchar(10),
    address varchar(20)
    )

    If x=2
    Create table #temp(
    name varchar(10),
    Phone varchar (12)
    )

    Seems like even if the condition is NOT true, SQL is trying to create the temp table anyway as my results always tell me that there is already an object called #temp. =(

    any suggestions greatly appreciated!!

    • SQL Server does not allow same table name in IF-ELSE constructs. While parsing, SQL Server creates a plan and reserves the object name as #temp. for x=2, same name #temp is used. Hence the error.
      Try the below sql :
      declare @x int
      set @x=2
      If @x=1
      begin
      Create table #temp(
      name varchar(10),
      address varchar(20)
      )
      end
      else If @x=2
      begin
      Create table #temp2(
      name varchar(10),
      Phone varchar (12)
      )
      end

  18. i need to synchronize two or more sql servers..

    In vb.net windows application,i used sql server 2008 for database.
    For deployemnt and implementation process, we used currently the sql server is in the web.client have different locations and want to access application from any where.
    The problem is at some rural area the internet down time is high and user unable to do data entry job in the application.Then the requiremnt have changed to use local server for each branches . After completing data entry work and they want to update to sql server in the web.during that process latest transactions changes from local server have to update in web server, mean time another branch local server will also update to server in web.and local servers have to get exchange data through web server.When user click update button, only todays transactions from local server should update to web server and vice versa and also another branch latest data that had already updated in web server have to transfer to local server.
    What are the possible ways to do this ?
    could you guide me for this situation?
    is any tool available for this?

  19. Friends

    I am working on some reports in SSRS. In our region, we have our week ending on friday. So when I work on weekly report and I want to use the “datepart” function, the result is not as expected.

    I tried using the “set datefirst 5″ and the “set language arabic”, but this did not help.

    What is required is that,

    declare @myDate datetime
    set @myDate = ‘2010-10-30′

    select datepart(wk,@myDate)

    should return 45 not 44
    For Nov,

    The Weeks are

    45: Oct 30 – Nov 05
    46: Nov 06 – Nov 12
    47: Nov 13 – Nov 19
    48: Nov 20 – Nov 26

    Note: Nov 27 – Dec 03, that is Week 49, is considered to be in Dec for weekly reports.

    For Dec,

    The Weeks are

    49: Nov 27 – Dec 03
    50: Dec 04 – Dec 10
    51: Dec 11 – Dec 17
    52: Dec 18 – Dec 24
    53: Dec 25 – Dec 31

    Please let me know if we can work out a logic to handle this case.

    Thanks.
    Shibu P

    • Friends,
      I have received help from another form. I am posting an update on my earlier post.

      It was my mistake, I should have used “SET DATEFIRST 6″ as my start date is saturday.

      SET DATEFIRST 6
      GO
      declare @myDate datetime
      set @myDate = ‘2011-01-01′

      select datepart(wk,@myDate)

      Regards
      Shibu P

  20. Hi Dear Pinal
    Can you Explain what is difference between this two statement:
    1)select @@trancount
    2)DBCC opentran

    when I Use TransactionScope in C# Statement No.1 return 0 but No.2 return Result.when I Use begin Tran in sql server No.1 return 1 but No.2 return nothing.
    what is difference between them?
    thanks A lot

  21. I am creating an SSIS package and my test data has a lot of dups, so I am trying to strip them out of the data before inserting it into my production table. The following code from a stored procedure runs fine with SQL Mgmt Studio and removes the records from my data but won’t execute and remove dups from an SSIS Execute SQL Server Task:

    DECLARE @quie int
    DECLARE @quievalue varchar(100)
    DECLARE Dups CURSOR FOR SELECT Max(RxClaimStageId), ClientId + PatientID + DateofService + CAST(ListedDrugID As varchar(11))
    FROM Stage.RxClaim
    GROUP BY ClientId, PatientID, DateofService, CAST(ListedDrugID As varchar(11))
    HAVING COUNT(ClientId + PatientID + DateofService + CAST(ListedDrugID As varchar(11))) > 1
    OPEN dups
    FETCH NEXT FROM dups INTO @quie, @quievalue
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DELETE FROM Stage.RxClaim Where ClientId + PatientID + DateofService + CAST(ListedDrugID As varchar(11)) =
    @quievalue AND RxClaimStageId @quie
    FETCH NEXT FROM dups INTO @quie, @quievalue
    END
    CLOSE dups
    DEALLOCATE dups

    Any ideas why or know of any settings on the connection string I can alter to make this work? I have even copied this to another Execute SQL Task so it runs before my call to the stored procedure (so it is running twice) and it still does not execute and doesn’t throw any errors.

  22. Disregard my earlier question, the problem is me. I modified dates after the attempt to remove dups, which actually caused the dups. I have it working now. So it would work on the second attempt.

    Thanks,
    Jim Denny

  23. Hi Pinal,

    We are using SQL 2005. Now my Database MDF file size is approximatly 50GB and running out disk space.

    We have an network drive where we have approximately 5TB of disk space.

    We want to move Database MDF and LDF to network Drive.

    Is it possible to move MDF and LDF files on network drive and use the same SQL Server?

    If yes how we can achieve this.

    Please help

    Anshul Kumar

  24. Hello Sir,

    I have one stored procedure which gives me 2 tables (result sets)

    Create Proc GetJobs
    As
    Begin
    –Here JobID is primary key

    Select JobID, JobName from JobQueue where Status = ‘In Progress’

    Select JobID, JobName from JobQueue where Status = ‘Processed’
    End

    There is one problem. Sometimes I got same JobID in both result sets. Because when system execute first query then Job Status is “In Progress” and before start executing on second query if Job goes in “Processed” state (by executing below UpdateJobStatus stored procedure) then system show that Job ID is in second result set also.

    Create Proc UpdateJobStatus
    @JobID int,
    @Status varchar(20)
    As
    Begin

    Update JobQueue Set Status=@Status where JobID = @JobID

    End

    Is there any way by which I can restrict others users/query/stored procedudre to update the JobQueue table during execution of GetJobs stored procedure? I know that lock might work for this but how I don’t know. Please help me.

    Thanks a lot in advance!

    Regards
    Paresh

  25. 1. Set the Storage Settings to “Real-time ROLAP�?(standard setting) and in “Options�?selected Storage Mode as “ROLAP�? Enable Proactive Caching.

    2. In the General tabs, checked “Drop outdated cache�?Latency 0 seconds (default)

    3. Under Notifications, “SQL Server�?option selected and no Tracking tables specified

    ………………….thus i set my ROLAP cube but it doesnot work.i want to process the cube for new data.\
    please any one answer for this solution ?

  26. Hello Pinal ,

    I have a question for you regarding SQL 2008 R2 Installation . Basically I am a starter asp.net developer and I am trying to install SQL 2008 R2 with Analysis Service on my machine . My Problem is regarding ” COLLATION ” thing which needs to be specified while server configuration . While configuring server I want to specify ” Collation ” instead of service accounts. I was asked by my boss to install SQL 2008 R2 with Analysis Services . I can find Collation attribute ” SQL_Latin1_General_CP1_CI_AI” for Database engine. But I cannt find the same attribute for Analysis Service . Instead of desired Collation attribute I found ” Latin1_General_CI_AI ” . Now Set Up is giving me a warning saying that both of these collations must be same . I tried to find some solution for this in internet but couldnt find any . Some says that I need to change my default language settings to English US , currently I have Windows 7 OS with English India as language settings .

    Please Help me ……..

  27. Hi,
    Can any one demonstrate with screen shots how to build a add-in for sql server 2005.

    Something like executing a query or something like that.

  28. Dear Mr. Pinal,

    i am facing a problem with SQL 2000 server. We are using a software intachange of intasoft for changemanagement and its backend is sql 2000 database and 2003 server (OS).

    It used to send SQL mail through our exchange server account.

    But now we are getting email but all are in wired charecter set like below:
    䴼呅⁁瑨灴攭畱癩∽潃瑮湥⵴祔数

    in Body, though subject is in English as usal. WHile we tried test email, it sent in English text.

    Please reply me soon.

    Thanks and best regards,
    EKHAN

  29. Was wondering if you could help. I have a .mdf file, no .ndf or .ldf file, anyway to attached or restore ? I don’t need the actual data , but the database tables.

    thanks

  30. Hi Pinal,
    I am fairly new here. Let me know if iam posting at wrong place.

    our database is partitioned on monthly basis and i am seeing large amount (nearly 50-70%) of space available in the data files. These files are not expected to grow(in large amount) in future. So i want to remove the unused space. What is the reason for that large amount of unused space and how to eliminate it? I tried shrinking the file which decresed the unused space to decent size but that resulted in fragmentation. To avoid fragmentation I rebuilded the indexes which again brought the unused space to the original size.
    Is there any way to find which table (which field ) is causing this.
    thanks in advance,
    sneha

  31. Hi Pinal,

    Look, is there a way to migrate all my reports files from one reportserver to other?

    It’s kinda boring moving the reports one by one between servers everytime we need to migrate.

    Thanks for advance!

  32. Hello Sir,

    I want to move only stored procedure from one database to another database using sq l-query. or any other way…Please suggest the solution.

    Thanks in Advance,
    Avdesh Kataria
    [remove phone number]

    • There is another way. Right click on your database name then go to task then Generate Scripts then select your database name then next then select stored procedure then next then select the store procedure which you want to create or copy to another database. Then next and finish. So it will create a new query window. Copy them and paste into your another database and execute it. Thank. Sorry if something wrong.

  33. Hi Dave
    I have a question regarding database design.
    We have a table (TBLACTIVI) containing activities and their properties (attributes).

    An activity can meet various properties. However, as more properties are added, the table grows horizontally (adding more features) and this is filled with values T or F as appropriate.

    One option we have is to leave these features in another table -TBLACTIVI_PROPERTIES this table would have the catalog of properties- and a third table that links the two tables.

    Thus, in this way, when an activity has a property, we fill the record in the intermediate table.

    Which of the designs is best?

    With an option, we will end with a table containing multiple attributes and the access would be via index.

    With the other option, we remove such access but to find the properties will require an join operation between 3 tables

    Thanks in Advance,
    R.A.VilledaRuz

  34. Hello sir,

    When I am running A DTA for index recommendations,the DTA stopped with error “Tuning process exited unexpectedly” in consuming workload step.Can you pls suggest the possible solutions?

  35. Hello sir,

    When I am running A DTA for index recommendations,the DTA stopped with error “Tuning process exited unexpectedly” in consuming workload step.Can you pls suggest the possible solutions?

  36. Hi.

    Firstly many thnaks for your blog and code samples, there very useful.

    Very quick question which has been causing much scratching of heads. I am in the progess of re-engineering an ETL package which has to run on a local PC. The ETL is eating up a lot of RAM so I’ve broken it down and put a couple of loop containers in it. The first is to loop through years and the second one contained within the first loops through the months of the year. Now the only way I have found so far is to add an execute process task which fires off a batch file which in turn kills the local SQL Server Service and restarts it. This releases the cached memory and subsequently helps the process run a bit quicker. Is there any other way in which the memory can be released without stopping and restarting the service?

    Many thanks
    Nick

  37. Hi Pinal,

    Basically, I’ve list of sps, which giving timeout while called from .net code. However, when i tried to call same sp from sql server IDE, it executes in fraction of seconds.

    If i drop those sps & create it again, it works fine.

    I just want to know that dropping & creating any database object will make impact on performance issue?

    Looking forward for your suggestion on this, as we’re facing this issue since very long time.

    Regards,
    Kaushal

  38. Hi Dave

    I have been following your blog for a long time now.
    This is first time I have a question for you.

    How to edit a DTS package saved inside SQLServer ?

    Let says I did a import/export of data from 1 server to another using export/import wizard.
    When it gives me a option of running it immediately/save as DTS. I save it as DTS inside SQL Server.
    Now if want to edit due to a change in column mapping or sql itself. How do I access the package and edit it?
    Currently there seems to be no way of editing it.
    It doesn’t show under MSDB in the integration services (object explorer).

    thanks
    Ganesh

  39. Hi, I have an issue in Sql server stored Procdure. I want to retrieve three rows. My searching criteria is like on EmpId which has int datatype. Now if I am writing a storedProcedure then how can I retrieve all three data. Becuase if I am using in operator then it will give me conversion error. because I am passing parameter. The id’s are not fixed. So is there any other way to write a stored procedure. or How can i convert data from int to varchar.Thanks.

  40. Hi!,
    I want difference between SQL 2005 and SQL 2008
    Just tell me some basic differences so that i can tell it in the interview which will be useful and simple.

    Thanks
    Ashish Fugat
    [email removed]

  41. Hi Pinal,

    I have been constantly visiting ur site and it is really helpfull.

    Currently i am working on huge database wherein i am creating joins of multiple tables based on user selection.
    IF user does all by all by all selections the query execute successfully, but if few options are selected it gives the below error.

    Creating or altering table ‘FakeWorkTable’ failed because the minimum row size would be 10950, including 230 bytes of internal overhead. This exceeds the maximum allowable table row size of 8094 bytes.
    Internal Query Processor Error: The query processor encountered an unexpected error during execution.

    Could not understand how come memory does not full when sleections are huge and it fills when selections are small.
    Please share ur feedback at the earliest humble request.

  42. Hi Pinal Sir,

    I have an issue,

    When i send mail (front end C#,back end SQL 2005) , it
    shows to mail entries in mail box:

    Code is OK,button get disabled after First click till i get this issue.
    Can you Help me?

  43. Hi Pinal :-),

    Can you please explain what CLR waits are all about. I use Quest PA to monitor my key instances and I have noticed tha CLR wait have increased sharply one of them and I think it might be contributing the CPU pressure.

    Thank you

    Mark Johnson

  44. Pinal,

    I am getting this error every minute the sharepoint crawl starts

    Logon Error: 18456, Severity: 14, State: 38.

    LogDate ProcessInfo Text
    2011-01-13 11:00:01.650 Logon Login failed for user ‘domain\SPDevelFarm’. Reason: Failed to open the explicitly specified database. [CLIENT: ]
    2011-01-13 11:00:01.660 Logon Error: 18456, Severity: 14, State: 38.

    38 – Initial database could not be determined for session (SQL 2008)

    This is showing up every minute as it is running crawl for sharepoint

    domain\develfarm account is sysadmin

    any suggestions

    Thank you
    Neel

  45. I want to know whether table or stored procedure are being used or not in server and DB.

    Beucase most of the tables and SP which are not using the my application since long.so i want to find the those objects.

    can you please let me know how to find this

    • Hi Balu

      isnull(sum(marks),0) this is correct ,

      In this if the marks have values it will return the total sum of marks , if no values it will return 0

      isnull —> it will return any values or 0 , null will not come in fields

      and isnull expects two parameters

      • Sathish,
        ThankYou for replying. But actually I want to know about isnull(sum(marks),0). Why is it not correct?

        if all the rows have null for that column, isnull(sum(marks),0) is returning 0. And even it is checking whether it is null for only the sum. If we are using sum(isnull(marks,0)), it may be checking each row whether it is null. Please correct me if I am wrong.

  46. empid name city

    001 Raja Sydney
    002 ram Melbourne
    003 lokesh cbe
    001 Raja Sydney
    002 ram Melbourne
    003 lokesh cbe

    My question is how to delete single row when two rows having same values, only the three columns.

  47. Hi Balu

    isnull(sum(marks),0) this is correct ,

    In this if the marks have values it will return the total sum of marks , if no values it will return 0

    isnull —> it will return any values or 0 , null will not come in fields

    and isnull expects two parameters

    • Sathish,

      Again thanks buddy.

      Actually somebody told me that isnull(sum(marks),0) can cause error and he suggested me to use only sum(isnull(marks,0)). But I didnt find a scenario where it can cause error.

      • Balu,
        Sathish is right in explanation.
        Its nothing to do with error out.
        As, ISNULL() returns expr2 if expr1 is null. or expr1 if expr1 is not null. In case, if expr1 is null then it checks for expr2.
        actually both statements would return same value. Question here is what u want to achieve here.

  48. Hi,

    We are upgrading from SQL Server 2005 (SP3) to SQL Server 2008 R2.

    I did the following:

    1. Ran the SQL Server Upgrade Advisor and took care of the issues.

    2. Moved the Logins SQL Server 2005 to SQL Server 2008.

    3. Backed up the SQL Server 2005 Databases and restored them to SQL Server 2008 R2.

    So far so good…

    My questions:

    1. We have SQL Server Reporting Services (SSRS) reports on SQL Server 2005.

    Should I open these reports in BIDS 2005 or BIDS 2008 and then apply them ontu the SQL Server 2008 R2 server?

    2. We have SQL Server Integration Services (SSIS) packages on SQL Server 2005.

    Should I open these SSIS packages in BIDS 2005 or BIDS 2008 and then apply them ontu the SQL Server 2008 R2 server?

    3. I understand that SQL Server 2008 SSRS does not use IIS. So how does one configure SSRS on SQL Server 2008 R2?

    Any help, tips or pointers appreciated.

    Thanks

  49. Friends,
    I am getting an error “Invalid use of a side-effecting operator ‘SET COMMAND’ within a function.”

    when I am creating a function

    CREATE FUNCTION [dbo].[CustAgingTrans_ufn]()
    RETURNS @retCustAgingTrans TABLE
    (
    JOURNALNUM VARCHAR(25),
    VOUCHER VARCHAR(25), ACCOUNTNUM VARCHAR(25),
    TransDate DATETIME,
    Narration VARCHAR(150),TrnWKDat VARCHAR(25),
    firstWKDay DATETIME, lastWKDay DATETIME,
    TransDayDiff DATETIME
    )
    AS
    BEGIN
    SET DATEFIRST 6
    INSERT INTO @retCustAgingTrans (
    JOURNALNUM, VOUCHER, ACCOUNTNUM,
    TransDate,
    Narration, TrnWKDat,
    firstWKDay, lastWKDay,
    TransDayDiff)

    SELECT
    m.JOURNALNUM,
    c.VOUCHER,
    c.ACCOUNTNUM,

    c.TransDate,
    c.AccountTxt Narration,
    CAST(DATEPART(YY, c.TRANSDATE) AS VARCHAR) + ‘ – WEEK (‘ + REPLICATE(‘0′,2 – DATALENGTH(RTRIM(DATEPART(WK, c.TRANSDATE)))) + CAST(DATEPART(WK, c.TRANSDATE) AS VARCHAR) + ‘)’ AS TrnWKDat,

    DATEADD(dd,(DATEPART(dw, c.TRANSDATE) – 1) * – 1, c.TRANSDATE) AS firstWKDay,
    DATEADD(dd, 7 – DATEPART(dw,c.TRANSDATE), c.TRANSDATE) AS lastWKDay,

    DATEDIFF(D,c.TRANSDATE,GETDATE()) TransDayDiff
    FROM LedgerTrans c
    INNER JOIN LedgerTable m
    ON c.Ledg_ID = m.Ledg_Id AND c.AccountType = ‘CUS’
    RETURN
    END

    The error is from the SET, I need to use this as my week starts on saturday. Please help me sort out this problem.
    Regards
    Shibu P

      • Thanks, in fact that is what I am using now.
        I was hoping that since functions would allow SET to be used in functions as is the case with procedures. And both of them are in the “programmability” section, why this difference.

        I should be a difference that is caused by the method in which a procedure is executed and a function is executed in stored procedure.

        When I posted this question, I was hoping to get more information in this regard.

        Regards
        Shibu P

      • Thanks, in fact what I am using now is .

        SET ….
        SELECT * from dbo….

        I was hoping that since procedures would allow SET to be used in functions would also allow them as both of them are in the “programmability” section. why this difference?

        It should be a difference that is caused by the method in which a procedure is executed and a function is executed. When I posted this question, I was hoping to get more information in this regard.

        Regards
        Shibu P

  50. Hi Pinal,
    I have table like below,

    first_nm last_nm Gender
    Raj s F
    Rajp e M
    Kar Sh M
    De re M
    Sw ko F
    Kal la F
    Sa Sa M

    now i need to update my table each row last_nm with another record last_nm within gender(means female-female,male-male)

    example:
    first_nm last_nm Gender
    Rajp re M
    Kar e M
    De Sh M
    Sw s F
    Kal ko F
    Raj la F

    Thanks,
    Srr

    Am using below procedure,
    declare c1 scroll cursor for select distinct gender from table
    open c1
    declare @gender_var varchar(2)
    fetch first from c1 into @gender_var
    while(@@FETCH_STATUS = 0)
    begin
    DECLARE c2 scroll CURSOR
    FOR SELECT last_nm FROM table where table.gender=@gender_var FOR UPDATE OF last_nm
    declare @rindex bigint=0 declare @prev_ln varchar(20) declare @lastnm_var varchar(20) declare @CLN varchar(20)
    OPEN c2
    while(@@FETCH_STATUS = 0)
    begin
    set @rindex=@rindex+1
    if (@rindex=1)
    begin
    FETCH first FROM c2 INTO @lastnm_var
    set @prev_ln=@lastnm_var
    print @prev_ln
    end
    if(@rindex>1)
    begin
    set @CLN=@lastnm_var
    print @CLN
    update table set last_nm=@prev_ln –where CURRENT of c2
    set @prev_ln=@CLN
    print @prev_ln
    end
    FETCH next FROM c2 INTO @lastnm_var
    end
    CLOSE c2
    DEALLOCATE c2
    fetch next from c1 into @gender_var
    end
    –update table
    –set last_nm=@prev_ln where rnk=1
    CLOSE c1
    DEALLOCATE c1

  51. Hi Pinal,

    I would like to ask in SQL Server how I can have SQL statements especially alter statements. I’m aware of that Generate Script option. In my case it will not work always since it gives whole CREATE statement.

    The situation is like this I have DB and my remote developer also have same DB. Whenever I do a change in any of the objects I have to pass the alter SQL statement to my developer so that he can execute the statement in his local DB.

    For some reason I can’t go for products like embarcadero and redgate.

    Could you pls give me solution?

    Kind Regards,
    Pappachan

  52. Hello,

    I need your urgent help.

    I have configured the “Merge Replication” in SQL Server 2008 and SQL server Compact 3.5 and the synchronization process is running excellent.

    But in synchronization i want to handle delete condition. Condition 1:- when i delete the records from Sql server compact (mobile database) after synchronization the records deleted from mobile database SHOULD NOT BE deleted from server database .

    Condition 2:- But if i delete the records from server database, after synchronization the records deleted from server database MUST ALSO BE deleted from Mobile Database.

    I tried my making delete_tracking false for replication but by this only my first condition is satisfied.

    sp_changemergearticle
    @publication = ‘MobilePublication’,
    @article = ‘AttachmentTable’,
    @property =’delete_tracking’,
    @value = ‘false’

    I found on MSDN that both the condition can be satisfied by configuring the merge option change

    http://msdn.microsoft.com/en-us/library/ms173005.aspx

    I am still not able to find Merge Agent and on which identity does it runs?

    from two weeks i am struggling to find the solution.

    Please help!!

    Thanks in advance.

    SachinC

  53. Hi,

    I am kushal Basappa from India.

    Currently residing in Australia.

    I have just completer my masters in Information Technology from QUT Queensland Australia.

    I am very much interested in Database field and hence- at the moment preparing for 70-433 Certification.

    As you can see I am still a student, Dreaming to become a DBA.

    Can you please suggest me – how to pass 70-433.
    Exams.

    Thanks
    Kushal Basappa

  54. Hi Pinal,

    How we can reduce time while creating index’s or adding column in table which contains 27 billion records….
    In our Production environment it was taking 20 hr approx.

    Thanks
    Rahul

  55. Can you plz explain why the below query executes within seconds in sql but takes hours to generate the result in SSRS

    select
    CONVERT(CHAR(10),INVOICEDATE ,103)”Date”, invoiceid, customercode “Customer Code”,
    ym.itemid “Product Code”, IB.BATCHID “Batch Number” ,
    CONVERT(CHAR(10),EXPDATE,103) “Expiry Date” ,
    code , SUM(qty) tot
    from salesdim_mel YM ,INVENTDIM ID , INVENTtABLE it , BATCH IB
    WHERE YM.INVENTDIMID=ID.INVENTDIMID
    and it.itemid=ym.itemid and it.dataareaid=’YIA’ AND ID.DATAAREAID=’YIA’
    and it.itembuyergroupid = (@itembuyergroupid)
    and it.itemgroupid =@itemgroupid
    AND IB.BATCHID=ID.INVENTBATCHID and IB.BATCHID like (@batchNumb+’%’)
    AND ym.itemid=IB.ITEMID
    AND IB.DATAAREAID=’YIA’
    and salesoriginid in (SELECT FieldName FROM SplitList(@salesoriginid ,’,’))
    AND INVOICEDATE between @stdate and @edate
    and code in (SELECT FieldName FROM SplitList(@salestype ,’,’))
    and it.itemid between @StartItem and @EndItem
    GROUP BY ym.itemid,customercode , IB.BATCHID,CONVERT(CHAR(10),INVOICEDATE ,103) ,
    CONVERT(CHAR(10),EXPDATE,103) , code, invoiceid
    ORDER BY CONVERT(CHAR(10),INVOICEDATE ,103)
    END

  56. Hi Pinal,

    I have been assigned a task of enhancing Performance issue of one of the custom applications at work. The problem is at the database level not the front end.
    The database consists of lots of views. Each view references other views using unions and inner joins whicn intern references other views.

    Views are not schema bound, therefore cannot create indexes on them. On other hand, views that can be schema bound have unions. Also the Business logic has been done in DAL layer (Lot of mathametical caluations). Each view is over 100 lines of code without any comments.

    Other problem is that tables are not indexed properly. running each view takes over half hour. Some of the views have update statements which causes locks till view has finished executing which intern locks users at front end.

    Please let me know your toughts about how can i proceed further.

    Thank you

  57. Hello Sir,

    If we use generalized stored procedures for Insert/update/delete , will it make any issues in performance?.

    Is it a good practice to use generalized procedures? what are the pros and cons of these.

    Can you please answer these questions?

  58. Hello Sir,

    I am new to SQL, I am using SQL Server 2005. I want your help to solve my issue. I’ve a table with the following Data.
    Table Name: MasterDetails
    FieldName ID NAME ParentID
    1 A 0
    2 B 0
    3 C 1
    4 D 2
    5 E 1

    Now my query is: Need to display data as below
    If ParentID is 0 >> display just NAME and ID
    if ParentID is not 0 >> display NAME where ID is ParentID along with NAME (i.e row 3 has parentid 1 so it should display A-C, B-D, A-E). I hope you will get the idea how I want the data.

  59. i have a table with an id, doctors, and vist date along with other cols. I want count visit so for the same id, doctor and date, visit count will be one. how do I do this?

  60. doctor id patient visit date
    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/1/2010 10:24
    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/1/2010 10:24

    Here is the table I have I want to count number of visits,
    for the same id, doctor and visit date visit count =1,
    how do I count and put it in the table? Thanks so much

    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 11/23/2009 11:26
    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 4/20/2010 17:17
    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 11/23/2009 11:26
    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/14/2010 15:40
    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 11/23/2009 11:26
    dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/14/2010 15:40

  61. Hi Pinal,

    I am trying to create a linked server that receives data from Windows Search 4 (or Windows Desktop Search) so I can associate filename and path information from my files with my metadata in SQL Server 2005. I can link to Windows Search using VBScript using a simple script such as this:

    ‘To run this snippet, save it to a file and run it using cscript.exe from a command line.

    ‘Running the .vbs file with Windows Script Host may cause dialog boxes to open for each item returned from the index.

    On Error Resume Next

    Set objConnection = CreateObject(“ADODB.Connection”)

    Set objRecordSet = CreateObject(“ADODB.Recordset”)

    objConnection.Open “Provider=Search.CollatorDSO;Extended Properties=’Application=Windows';”

    objRecordSet.Open “select system.itempathdisplay from systemindex”, objConnection

    objRecordSet.MoveFirst

    Do Until objRecordset.EOF

    Wscript.Echo objRecordset.Fields.Item(“System.ItemPathDisplay”)

    objRecordset.MoveNext

    Loop

    This provides path information (in DOS) to all of my files in the index as follows:

    C:\temp\myDoc.doc

    C:\temp\page.html

    C:\temp\sheet.xls

    What I would like to do is get the same info from within SQL Server 2005 (or similar) using a linked server. In the past I have done this using Indexing Service, but that is an antiquated solution, and it seems that I should be able to link to Windows Search in a similar way.

    I can run the following query in SQL Server (and the connection test is passed):

    SELECT * FROM OPENQUERY(“Windows Search3″, ‘SELECT prop:System.itempathdisplay FROM SYSTEMINDEX’)

    Where Windows Search3 is the name of my linked server. This will return the following error:

    OLE DB provider “Search.CollatorDSO” for linked server “Windows Search3″ returned message “One or more errors occurred during processing of command.”.

    Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing the query “SELECT prop:System.itempathdisplay FROM system.search.store:file” for execution against OLE DB provider “Search.CollatorDSO” for linked server “Windows Search3″.

    This error seems a little further ahead (or at least different) than the error that other web posters have stated in the past:

    OLE DB provider “Search.CollatorDSO” for linked server “WinSearch4″ returned message “Command was not prepared.”.

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider “Search.CollatorDSO” for linked server “WinSearch4″ reported an error. Command was not prepared.

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider “Search.CollatorDSO” for linked server “WinSearch4″.

    Which is the result of:

    SELECT * FROM OPENQUERY(“Windows Search3″, ‘SELECT System.itempathdisplay FROM SYSTEMINDEX’)

    You will notice the only difference is prop: before system.itempathdisplay. I think my sql is quite close to what it should be to work, but it seems that something is missing. I think this connects somehow to one of the options in this API http://msdn.microsoft.com/en-us/library/ff684394%28v=vs.85%29.aspx , but it must be lacking something. I don’t know enough of how SQL Server repackages this info and sends it off to Windows Search, so I am now clueless. Either a SQL guru or a Windows Search master might be able to jump the last hurdle to make this thing work. It seems like there is only a small piece of the puzzle missing.

    I will dump my linked server info for reference:

    /****** Object: LinkedServer [Windows Search3] Script Date: 01/31/2011 17:23:36 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N’Windows Search3′, @srvproduct=N’Microsoft OLE DB Provider for Search’, @provider=N’Search.CollatorDSO’, @datasrc=N’SYSTEMINDEX’, @provstr=N’Application=Windows’

    /* For security reasons the linked server remote logins password is changed with ######## */

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’Windows Search3′,@useself=N’True’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’collation compatible’, @optvalue=N’true’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’data access’, @optvalue=N’true’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’dist’, @optvalue=N’false’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’pub’, @optvalue=N’false’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’rpc’, @optvalue=N’true’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’rpc out’, @optvalue=N’true’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’sub’, @optvalue=N’false’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’connect timeout’, @optvalue=N’0′

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’collation name’, @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’lazy schema validation’, @optvalue=N’false’

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’query timeout’, @optvalue=N’0′

    GO

    EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’use remote collation’, @optvalue=N’true’

    My only other thought is that I am having an authentication or impersonation issue, but I am using the security setting (as quoted on the Linked Server Properties page for Security):

    “Be made using the login’s current security context”

    Conclusion:

    I know many people have been looking for this solution, and it seems that it should have been made available sooner. Any help or solution or even agreement appreciated.

  62. hello mr pinal.

    i want to some help from you.

    i want to bind all sql server within my LAN are in to DropDownList.

    and when i select this connection then all tabels of this connection bind to another dropdownlist.

    can you pls hel me??

  63. I want to list down all instances of SQL Servers in my window application. How can I get the list of SQL Server instances? Is there any SQL Query for this?

  64. Hello Dave,

    In one of our projects we have three different log in system users, each user has their own contact persons, for the first two users they have only one contact person and for the third user we have multiple contact persons.

    For that scenario we have created three table with their own business fields, and the contacts of the first two users we are storing in the same respective tables, and for the third user we have created contacts table separately, now we want to have a single table for storing all the contacts, is it a good practice to have a single table for all the contacts for the entire system or is it ok to keep the table like as it ease.

    Please give us your comments and suggestions.

    Thanks,
    Shalem

  65. Hi Dave,
    I have an interesting issue I have a MSSQL 2005 Express database that I want to be able to replicate a few tables to another SQL server. The reason for this is the SQL Express server handles live data and I want to setup a reporting database that users can hammer without affecting production. One of your posts went over a method to resolve this but I wanted to see if you could send me the details on how to go about this. I’m not a sql guru so I’m not sure of all the options available to me.

    Thank You ahead of time Dave and e-mail when your suggesion.

  66. Hi Pinal,

    I have a query regarding OPENXML. I have seen many examples where in WITH clause of OPENXML, people use ‘@’.
    For example in the following example, we can see @OrderID, @CustomerID, @OrderDate, @ProductID, @Quantity.

    I am very confused about what this does???

    Please let me know as i am really getting mad about finding the use of ‘@’.

    DECLARE @idoc int
    DECLARE @doc varchar(1000)
    SET @doc =’


    –Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    — SELECT stmt using OPENXML rowset provider
    SELECT *
    FROM OPENXML (@idoc, ‘/ROOT/Customer/Order/OrderDetail’,2)
    WITH (OrderID int ‘../@OrderID’,
    CustomerID varchar(10) ‘../@CustomerID’,
    OrderDate datetime ‘../@OrderDate’,
    ProdID int ‘@ProductID’,
    Qty int ‘@Quantity’)

    • Hi Pinal,

      i forgot to include the XML string in the previous post.

      I have a query regarding OPENXML. I have seen many examples where in WITH clause of OPENXML, people use ‘@’.
      For example in the following example, we can see @OrderID, @CustomerID, @OrderDate, @ProductID, @Quantity.

      I am very confused about what this does???

      Please let me know as i am really getting mad about finding the use of ‘@’.

      DECLARE @idoc int
      DECLARE @doc varchar(1000)
      SET @doc =’


      –Create an internal representation of the XML document.
      EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
      — SELECT stmt using OPENXML rowset provider
      SELECT *
      FROM OPENXML (@idoc, ‘/ROOT/Customer/Order/OrderDetail’,2)
      WITH (OrderID int ‘../@OrderID’,
      CustomerID varchar(10) ‘../@CustomerID’,
      OrderDate datetime ‘../@OrderDate’,
      ProdID int ‘@ProductID’,
      Qty int ‘@Quantity’)

  67. Hi Pinal
    I have MS ACCESS database in One CLINET
    and i want to link ms access database to SQL SERVER 2008 ok
    but can i create second database in sql server that if the row modify or delete or insert into linked database in sql server in second database i see modify- but second database is copy of first database but one column additional in second database like below:
    ——-
    MS ACCESS database >> Table_1 with A,B COLUMN
    ——-
    MS SQL server LINKED database >> Table_1 with A,B COLUMN
    ——
    MS SQL server Copy LINKED database>> Table_1 with A,B,C
    COLUMN
    ———-
    every change in ms access database i see in linked database
    ——–
    i want that i see every change in linked database i see in second database but value of C column in entry data
    ——
    how to do this thank

  68. hi friends
    i venkat i have critical problem
    i have one table consisting of 4 columns like as follows
    Emp_no Date Punch_Time In_Out
    1 110101 0930 p10
    1 110101 0932 p20
    1 110101 0935 p10
    2 110101 0930 p10
    2 110101 0936 p20
    3 110101 0939 p10
    4 110101 0930 p10
    4 110101 0937 p20
    same empno first time P10 and same empno second time p20
    in my hand above table with lot of records with different combination. my resulting table is empno no is 2 time with p10 and p20 first punch and last punch.
    like below result table
    please tell the logic i am using .net (windows applications)
    1 110101 0930 p10
    1 110101 0935 p20
    2 110101 0930 p10
    2 110101 0936 p20
    3 110101 0939 p10
    4 110101 0930 p10
    4 110101 0937 p20

  69. select
    *
    from
    northwind..orders as o
    where
    (select count(*) from northwind..orders where customerid=o.customerid
    and orderdate>=o.orderdate)<=2
    order by customerid,orderdate desc

    thankyou Madhivanan

    i clear my problem but some thing i am getting this =2 if i place =2 is working 1 columns correct all employees . but my problem is both first columns and second columns correct. i hope you have the solutions.

  70. Hi Pinal,

    I just want to know reg microsoft certification. I just want to write MCTS 70-433. Whether 70-433 is expired or what? can you please tell me if not when it will expired.

    For this 70-433 is that pre requiste is required or not? Once i clear this exam i can able to get certified as MCTS or any more exams need to write. Pls put a copy to mail id eve. bcos i dotn know hw to again come n see the reply for my post.

    Thanks & Regards
    Shanthi

  71. Hi Pinal,

    I’m using SQL Server 2008 R2 build. When i use TIME/DATE datatype in create query it gives error:

    “Msg 2715, Level 16, State 7, Line 1
    Column, parameter, or variable #2: Cannot find data type time.”

    Query:
    create table ShowDetails(show_id int,show_timing time(3));

    When i try to set the compatibility level to 100

    EXEC sp_dbcmptlevel movietickets, 100;

    it gives error :
    Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 70
    Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

    Valid values of the database compatibility level are 60, 65, 70, 80, or 90.

    Please help !!

  72. I am facing a problem in Mirroring the Database, Can you send me the procedure how to fix my problem, I am follow the bellow procedure but finaly it showing error.

    First I take a Full backup of my Primary server Database after that I restore this backup in my other server with Nonrecovery Restore

    Then I go to Primary Database and start the mirroring there I set the Configure Security—> Include Witness Server —> No then set the Primary server with port no and Set the Mirror Server Instance with conection credential and set the port no also

    Then I am unable to understand the Service Accounts there I didn’t mention Principal and Mirror and next I finish this its process and show successfuly completed.

    Then Next widow is asking me Start Mirroring and Do Not Start Mirroring——–> I press Start Mirroing and finaly I got error “An error Occured while starting mirroring. Alter Failed for Database ‘Quantum’.(Microsoft.SqlServer.Smo)”

  73. hi…

    can you please teach me if how can I import the data from excel to datagrid by using the connection of SQL…. please teach me how… I would really be needing it for my thesis…

    : 4th Yr. Comsci Student

  74. Hi Pinal,

    Jus came across one strange thing when working with SQL Server 2005 and .Net.

    Well in my database table I have a column which is of type Varchar and stores Alphanumeric chars. Over here in my get stored proc when reading this column I use a case statement to return numbers according to the string. When I read this column in dotnet using SQLDatareader I can see their type as Int32 but I want it to be read as smallint. Other than doing a cast in the query do we have any other approach to read them as int16 in the datareader?

    Thanks
    Kris

  75. Hi ,
    I need to connect to azure Database to sql server 2005 with 32 bit. I have tried the Microsoft.Synchronization class to achive that . but i am not able to do that . pls help

  76. Hello Pinal,
    I am working with calculated field from virtual column. This seems very easy one but some reason I could not find answer yet.

    Can you help me to resolve the following problem?

    SELECT
    10 AS Column1,
    20 AS Column2,
    Column1+Column1 AS Total,
    ((Total*30)/100) AS PerOfTotal

    When I tried above SQL, i get the following error.
    Msg 207, Level 16, State 1, Line 4
    Invalid column name ‘Column1′.
    Msg 207, Level 16, State 1, Line 4
    Invalid column name ‘Column1′.
    Msg 207, Level 16, State 1, Line 5
    Invalid column name ‘Total’.

    How do I solve this issue?

    Thank you
    Shailen

    • Hi, sorry for my english, but the quick answer is that you should not use the alias “Column1″ (or Column2) in the expression, so the SQL is:

      SELECT
      10 ,
      20 ,
      10 + 20 AS Total,
      (((10 + 20)*30)/100) AS PerOfTotal

  77. Shailen,
    You can not directly refer alias as column names very next to its static values unless we consider columns from either resultset / inline view at present.
    Well, I could provide 1 solution but i’m sure there may exist better than mine.

    with cte(Column1, Column2)
    as (SELECT 10 AS Column1, 20 AS Column2)
    select *, ((Total*30)/100) AS PerOfTotal
    from (select Column1, Column2, Column1+Column2 AS Total from cte) as new_cte
    — output : 10 20 30 9

    Is this what output you are looking for ?

  78. Or else below could be the solution. Correct me if i’m wrong
    SELECT
    10 AS Column1,
    20 AS Column2,
    10 + 20 AS Total,
    (((10 + 20 )*30)/100) AS PerOfTotal

  79. another one :

    select Column1, Column2, Total, ((Total*30)/100) AS PerOfTotal
    from (select Column1, Column2, Column1+Column2 AS Total
    from (select 10 as Column1, 20 as Column2) as v1) as v2

  80. Or… if you really want those columns name, try this dirt SQL:

    SELECT Column1, Column2, Total, ((Total*30)/100) AS PerOfTotal
    FROM ( SELECT Column1, Column2, Column1+Column1 AS Total
    FROM ( SELECT 10 AS Column1, 20 AS Column2) as a ) as b

  81. Hello Pinal,

    We have an requirement where we need to pick up sql files one by one and then review the file for microsoft best practices and log errors if any of the file is not matching the criteria.
    Is there any tool availavle for the same.
    Please let me know.

  82. Hi Pinal,

    Need some help understanding this concept,

    I’ve a table which has around 1 lack records. It has 3 columns, ID1 , ID1, Description, with ID1 (Left most column) and ID2 forming the primary key on the table (Composite Clustered index).

    When I do a simple join operation like following,

    SELECT * FROM dbo.CompositeTable C1
    INNER JOIN dbo.CompositeTable C2 ON C1.ID2 = C2.ID2

    It takes a lot of time to execute. Around 1 min in query analyzer.

    I went ahead and checked the statistics build on this table and found that, there are 2 statistics created on the table,
    1. Containing ID1 on top and ID2. This one has lot of imformation stored.
    2. Containing only ID2, when I click on the details of this stat, I see a message in SQL server pane — “No statistics information available.”.

    What does this mean? Also, why does SQL server does not use the clustered index properly on such a join (I can see some table spool created when I check the actual execution plan)

    Regards, Vinay

  83. Hi,

    It’s posible to create a table from a select union

    Example:
    CREATE TABLE C as
    (SELECT * FROM a
    WHERE …
    UNION
    SELECT * FROM B
    WHERE …
    )

    SELECT * FROM A
    UNION
    SELECT * FROM C

  84. Hello Pinal,

    I inherited a database that was very poorly designed. Lately the traffic to the db’s web site has gone up dramatically and all of a sudden I am plagued with lots of deadlocks. I am a programmer with some knowledge of databases but this problem is definitely over my head. The deadlocks don’t seem to be localized to any one place or type of sql. I get them with combinations of updates, inserts, and select statements on various tables.

    After cleaning up a lot of statements I found out how to do a trace. Below is an example of one where there are several updates on the same table.

    This database has a very high number of inserts and updates being executed. I am almost convinced that my problem is being caused by the fact that I have clustered compound primary keys all over the place. The only problem is I don’t really understand the mechanism as to how the deadlocks are occuring. I could just change the pks and see what happens but I would rather understand the why first.

    So my questions are:
    1. Is my assumption right that lots of inserts and updates on tables with compound pks can deadlock?

    2. If my assumption is correct could you explain the mechanism at work?

    Thanks,
    Nat

    btw- I would like to thank you for all the information you provide on your site. It’s extremely helpful. It’s the first place I go when I have sql server questions.

    DEADLOCK_GRAPH
    2011-02-25T11:21:17.710
    15

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12
    unknown

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12
    unknown

    (@P0 bit,@P1 bit,@P2 nvarchar(4000),@P3 nvarchar(4000),@P4
    nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 int,@P8
    nvarchar(4000),@P9 int,@P10 int,@P11 int,@P12 nvarchar(4000))UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    (@P0 bit,@P1 bit,@P2 nvarchar(4000),@P3 nvarchar(4000),@P4
    nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 int,@P8
    nvarchar(4000),@P9 int,@P10 int,@P11 int,@P12 nvarchar(4000))UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    UPDATE
    MemberInfo SET
    ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
    WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
    AND Source = @P12

    xxx
    2011-02-25T11:21:17.710
    Server_name
    AQ==
    23345777
    1

  85. Dear Pinalkumar

    I’ve got a column in a SQL Server table that is type XML. I want to change multiple values in the XML in a stored proc. I’ve already found the XML.Modify(…) command and have it working for a single value. Can I change multiple values in the XML in one update command or do I need to do it as multuple update commands?

    Is it possible to do this or should i have to use invidule update command for each column.

    Regards Faisal

  86. I am experiencing difficulties using a Stored Procedure with w/multiple parameters and accessing the results using the ReportViewer (local Mode) control in an ASP based website. I am not sure if you deal with this at all but possibly you could point me in the direction of someone who does know about using the reportviewer? I have been searching the web for samples and help on the forums but have had no luck. I am willing to pay for services but not sure where to turn?

    The problem is not in the SP as I get the correct results when running in SQL it is in the setup between SQL and reports. I dont want to clog up your comments section with details if you are unable to assist. I just thought I woiuld ask because I have learned much from your blog post.

    Thanks

      • I have found that since I am working with a local report that I am better off creating the dataset from the stored Procedure having had passed the SP the required parameters and then setting the report data set to the dataset I created fro the SP.

        Which then works great and displays the report as expected.

        What I do not understand now is how to create the report without dragging the TableAdapter control onto the form and populating it via the wizard which will add the actual fields returned from the SP into my available data objects and assigning them to the report fields so they bind correctly. Once that is completed I wam wondering if I can then delete the Physical table adapter from the form and still have it work from the dataset created in code?

  87. Pinal,

    I am getting same error as Neel,

    Error: 18456, Severity: 14, State: 38.
    Login failed for user ‘MOSS\PRD_MOSSAdmin’. Reason: Failed to open the explicitly specified database. [CLIENT: xx.x.x.xxx]

    Tried every single solution available (such as, providing access to this user to master db, checked all SQL Jobs, checked all web.config, can open Management studio and see all db using this user, etc…). Nothing works.

    Can you please porovide your opnion?

    Thanks,

    Gopal

  88. Just wanted to say thank you. I have been doing a lot of searches for advanced SQL abilities and around half the time this is where I find the answer to my questions with great examples that cleanly and simply show how to use them.

  89. I need to upgrade the following components of sqlserver 2005 express edition using commmand : UPGRADE=Client_Components,Connectivity,SQL_Tools90,SQLXML
    When i upgrade the above using SQL Server 2005 standard edition CD, will it effect the other services also?

    Please suggest

  90. Greetings!

    I wonder on stange behaviour of SQL Server Transaction LOG.
    The following script makes transaction log growing and finally ends with 9002. I understand it. There is one VERY LONG tansaction:
    USE [master]
    GO

    CREATE DATABASE [testw] ON PRIMARY
    ( NAME = N’testw’, FILENAME = N’C:\temp\testw.mdf’ , SIZE = 4096KB , MAXSIZE = unlimited, FILEGROWTH = 4096KB )
    LOG ON
    ( NAME = N’testw_log’, FILENAME = N’C:\temp2\testw_log.ldf’ , SIZE = 512KB , MAXSIZE = 512KB , FILEGROWTH = 10%)
    GO
    USE testw
    go

    create table test
    (txt nchar(4000));
    go
    DECLARE @i int;
    SELECT @i=1;

    BEGIN TRANSACTION
    while(1=1)
    begin
    INSERT INTO test VALUES(CAST(@i as NCHAR(4000)));
    select @i=@i+1;
    end
    END TRANSACTION

    But when I change the location of BEGIN/END TRANSACTION (inside while), so I have many small transactions, the log does not grow! The recovery model is full. Why one big transaction takes much place in transaction log and many small transactions not?

    DECLARE @i int;
    SELECT @i=1;

    while(1=1)
    begin
    begin transaction
    INSERT INTO test VALUES(CAST(@i as NCHAR(4000)));
    select @i=@i+1;
    end transaction
    end

    Please help,

  91. Hello,

    I need some help regarding tuning.. I am unable to solve one mystry….
    Using profiler i discovered that same query performing almost similar number of reads and writes is taking longer and longer to execute..i.e. the duration is increased every time i execute the query given the total number of records in table are same

    I am sure you know why this is happening… i will be very glad if you share this knowledge with me

    Thanks

      • DELETE FROM [CO SRC Job Table]
        WHERE [Company RESOURCE] = @Resource
        AND [Interval] = @Period

        It was taking 10 ms and now it is taking around 15 ms

        I changed the index from clustered to non clustered which increased the duration further therefore i changed the type back to clustered

        Here i must mention that the time taken to execute had increased more but this morning when i checked, it got executed taking 15 ms

        Another query is

        UPDATE [CO SRC Job Table]
        SET [Days per Month]=B.[Days per Month],
        [Hours per Month]=B.[Hours per Month],
        [Hours per Day]=B.[Hours per Day],
        [Days per Week]=B.[Days per Week],
        [Weeks per Month]=B.[Weeks per Month],
        [Rate of Pay]=B.[Rate of Pay],
        [Rate frequency]=B.[Rate Frequency]

        FROM [PER REM Payrate] B

        WHERE [CO SRC Job Table].[Interval] = @Period
        AND [CO SRC Job Table].[Employee] = B.[Employee]
        AND B.[Start date] = [CO SRC Job Table].[Period End]
        AND [CO SRC Job Table].[Company Resource] = @Resource

        48 ms with 87 writes …. but the strange thing is that later it took 49 ms and the number of writes were only 24…(Reads were same)

        Other queries are longer and more complex, if this does not help, then i will try to provide those as well..

        Thanks alot for response and your time

        • Zeeshan,
          I dont believe, changing index from clustered to non-clustered or vice versa (as you said above) would solve the issue.
          What I suggest is to check the Index page is Fragmented!!
          Query :
          select avg_fragmentation_in_percent
          from sys.dm_db_index_physical_stats(db_id(), — your database id
          object_id(”) — your tablename
          , null, null, ‘detailed’)

          Check what is value of it. If its between 10-30%, you got to re-organize. Else if more than 30%, Rebuild indexes…

  92. I have installed SQL Server 2000 Enterprise edition with service pack 4 instance on Windows server 20008 R@ Data Center OS.After installing SQL 2000 i have renamed machine.

    But After renaming machine name i couldnot able to connect that instance remotely.

    but when i try machine name with port number i m able to connect.

    I did not understand what is the problem.

    Please, provide me some solution .

    Thanks in Advance

  93. Hi Pinal,

    I am just starting to learn MS SQL Server, previously I was working with Teradata. I guess there is no options to see the SQL execution history in SQL Server..rite?? And I can’t retain my result window unclosed for all the queries am executing..rite?? I am confused because all these options were there in Teradata..Please correct me if am wrong..

    Thanks,
    Nithya

  94. Hi Pinal

    I facing a problem with large transaction-log , we are using temp-db , is there is any way we can avoid transaction log , because it is filling temp-db , and I can not make recovery mode to simple .

    any advice

    thanks
    Hemanshu

  95. I facing a problem with large transaction-log , we are using temp-table , is there is any way we can avoid transaction log , because it is filling temp-db , and I can not make recovery mode to simple

    Hemanshu

  96. Hi,
    I have 2 different environment (INT and UAT).

    Today I write a small function to stress test the connections to UAT database.

    The C# function will loop a class method to get data from branch and the staffs for all the branches.
    There is about 550 branches and each branch have about 3-10 staffs.

    Result is, both UAT and INT instance totally mess up and down. It prompt me this error.
    Logon failed for login ‘CRM’ due to trigger execution.
    Changed database context to ‘CRM_Database’.
    Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

    I have asked the server team to help on this since I don’t have enough permission to investigate.
    But they have come back and said there is no way to recover the instance and they have to reinstall the whole instance.
    According to the server team, the faulty is cause by my stress test (too stress of making connection to database).

    I got 2 questions.
    1. I only connect to UAT instance, how come INT instance also crash?
    2. Are there really no way to recover the instance?

    I done some research, I know is need to drop the trigger Tr_ServerLogon. But I tried that in my local machine, It said cannot find the object.
    I’m not sure whether the server team did that but I believe they can find this suggestion from internet.

    I really wish to know is there any solution to recover this issue.

  97. hi sir,
    can you help me to install sql server jdbc connector i downloaded but don’t know how can i use it,
    thanks

    soufya,

  98. Anyone can sort this? (using: SQL/My SQL/Oracle)

    CategoryID ParentCategoryID Category
    1 0 Flower
    2 1 Jasmine
    3 1 Rose
    4 1 Orchid
    5 0 Animal
    6 5 Cat
    7 5 Dog
    8 6 Tiger
    9 3 Red Rose
    10 3 White Rose
    11 9 Small Red Rose

    My final answer should be like this:
    CategoryID ParentCategoryID Category
    1 0 Flower
    2 1 Jasmine
    4 1 Orchid
    3 1 Rose
    10 3 White Rose
    9 3 Red Rose
    11 9 Small Red Rose
    5 0 Animal
    7 5 Dog
    6 5 Cat
    8 6 Tiger

  99. Hello sir,

    I just wanted to tell you that I love your website.
    It contains many useful advices on SQL SERVER.

    Keep up the good work.

    Have a wonderful day,
    Roni Vered.

  100. Hi Pinal

    I m using sql server 2005 & I faced a problem while Bulk Insert is occured.

    error as below

    Fatal error 3624 occurred at Mar 16 2011 3:58PM. Note the error and time, and contact your system administrator. A severe error occurred on the current command. The results, if any, should be discarded. Location: lckmgr.cpp:10846 Expression: GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition () SPID: 75 Process ID: 1824

    plss help..

    regards

    Ankur

  101. Dear Pinal ;

    I am planning to do Logshipping on sql server 2000 and 2008 servers , SAP is the application running for this DB.
    I have already restored the standby Database , didnt choose the NON Recovery mode.
    Is there any option to change the mode to Non recovery?

    Thanks in advance.

    Regards

    SAM

  102. HI

    I have an interesting scenario which has happened with one of my clients.

    The issue is that a table “dissapeared” – i say this because there is no log in the log file which shows a DROP TABLE on this table. And from what i have found, in trying to recreate this table from a create statement which i generated from the backup, it complained that the Primary key was still there. I tried to drop the PK using alter table Drop constraint it said couldn’t find table.

    My biggest question is that how is it possible for a table to be removed without the PK and FK’s being removed with the table?

    My understanding of relational databases is that PK’s are “related” to tables – and should be removed when a table is removed.

    I have checked google and i dont see any issue like this before.

    Your Quick response would be most appreciated – as i am completely dumbfounded by this.

  103. Hola Dave:
    Tengo el siguiente escenario:

    Servidor Windows 2003 con MS-SQL 2008, actualmente migrado de MS-Sql 7.
    En la actualidad estamos teniendo problemas graves en nuestras aplicaciones, ya que depende de la configuracion regional de cada PC. En aquellos PC cuya Configuracion Regional es English(United States) los datos se nos convierten mal ya que los separadores de coma decimal (coma) y separador de miles(punto) son diferentes a la mayoria de los PC que tienen (punto) y (coma) en los cuales nuestras aplicaciones funcionan bien, es decir los datos numericos se presentan y se graban en las tablas correctamente, no sucediendo el caso de English donde da overflow de estos campos cunado los numero son muy grandes!!!!

    Alguna idea de lo que esta sucediendo?
    Tiene importancia la intercalacion del SQL que tiene el servidor y la intercalacion SQL del PC cliente con configuracion regional English?

    Aprecio mucho toda la ayuda que me pueda dar en este sentido.
    Saludos

    Ing Miguel Rivero

  104. Hi Pinal,
    I am a regular at your blog and they are very informative and interesting. I had a question, is there a direct and simple way to implement Fuzzy grouping and fuzzy lookup in SQL Server without using SSIS? If yes could you please guide me.

  105. sir i am downloading data from BioMetric Device
    date format is mm/dd/yyyy and time format is h:mm:ss tt

    first i taken data table it is coming exactly after that i am inserting bulk data to Database sql server one table. but it is not showing exactformat wat ever is there in DataTable.
    it is not showing AM or PM. it showing DataTable but not showing AM or PM in SQl server table.

    before it showing correct after one week is not showing for this reason i am unable to transfer sql server table to one more table it showing System.Outof Memory Exceptions. if i give manually it is not showing this error .wat can i do to solve this error.

  106. Hi Pinal,
    Congratulations on doing spectacularly well!
    Your blog has helped me several times during the course of my work & I find the contents to the point, easy to understand & interestingly simple to follow……

    I parallely work on 3 flavors of DBMSs – SQL Server 2005 / 2008, Oracle 10g & DB2 9.5 & my work area consists of ETL, SQL Tuning, Data Modelling & Database Design….

    I would really like to know your recommendations on the best resources / books for the aforesaid topics……

    It would be great if you could mention your favorites too…

  107. Dear Dave:

    Thank you for sharing your knowledge.

    I develop hardware/firmware architectures/devices.

    I have a client who needs that a device that I made, makes a SQL query on TCP to his database.

    I program my device in C ¿from where I can lear the sintaxis to make (hand made) a packet with a query to a SQL server, that he understand it, and answer it?

    Thank you very very much

    Sergio

  108. Hi Pinal,

    I have read many of your articles and has helped a lot.

    Can you please brief me as to if it’s possible to import the following data into SQl Server and the best way to go ahead with it. A sample of the data is given below:

    –Columns

    Column1
    Column2
    Column3
    Column4
    Column5

    –Data

    XYZ
    999999
    ABC
    9999999
    2011-03-25 06:01:00.0

    DEF
    888888
    GHI
    8888888
    2011-03-24 06:01:41.0

    Thanks for your help.

    PRMP

  109. I used the code below to move the Model database and Model log file to new locations.

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_path’ )

    After running the commands I stopped the SQL server and moved the physical files to the correct location.

    Now I am unable to start SQL server, I get error

    I think the problem maybe that for FILENAME=new_path I put “J:\SQL Server\MSSQL.1\MSSQL\Data”
    but did not include model.mdf and modellog.ldf in the file path.

    Now I couldnt start my database engine.. How to overcome from this prob??

  110. Hi Pinal,
    I would like to know wether we can update all the column in a table using Update statement….
    Can you suggest me what type of query we can use to update all the column of a table….
    Note: the table is created dynamically….

    Hope you will respond to my query….

    Thanks in advance….

    Thanks & Regards
    Prithviraj MK

  111. Hi Pinal,

    Thanks for your session at teched 2011 .

    I am having question .We have hosted 500 databases in a single instance.

    Now ,I have problem to identify the databases that are being not used.

    Its difficult to find these databases which are not used.Can you let me

    know whether any good soultion for this.

    Thanks
    Srinivas

    • If you want to find it make offline eash database and run the application. There wont be a problem if it not used by any application. Other method is to run a trace and see if any database is not used

  112. Hi,

    Am completely new to Database Technology. I have a problem, I need to convert a database script that i have been using to create my database in SQL 2000. The script includes all the Views, Stored Procedure and User roles and permissions.

    I need to convert this script so I can run it on SQL 2008 and create a table for use on it.

    Sample:

    CREATE TABLE dbo.tbl_users (
    id bigint IDENTITY (1, 1) NOT NULL ,
    column… nchar (50) NOT NULL ,
    column… char (50) NOT NULL ,
    column… NOT NULL ,
    column… NOT NULL ,
    ) ON PRIMARY
    GO

    ALTER TABLE dbo.tbl_users WITH NOCHECK ADD
    CONSTRAINT IX_tbl_users UNIQUE CLUSTERED
    (
    email
    ) WITH FILLFACTOR = 90 ON PRIMARY
    GO

    Please help!

  113. hi,
    i have read article on self joins already.
    i am engineering student. i am working on project for navigation purpose for academics. i have table for routes available and cities and their respective locations. now i have one problem regarding selfjoins in one table. i have a table which has three fields

    ROUTE_ID CITY_ID STOP_NO
    1 3 0
    1 4 1
    1 7 2
    2 7 0
    2 4 1
    2 3 2
    like above table there is listing of route stops within a route. the above table have one route in both directions i.e. target city for routeid 1 is source city for routeid 2 and vice versa . this is how i have saved the data.
    I am giving two city ids as input parameters for source (from) to target(to) and want to know the routeid which contains these cities. i am able to find the route using self join, but the problem is the the source city should lower stopno from the target city stopno.
    so currently if i search for routeid from cityid 4 to 7 i get routeid 1 and 2 as result. my requirement is it should give me routeid 1 because cityid 4 (stopno is 1)is earlier to cityid 7(stopno is 2) .
    [email removed]
    Thanks in advance.

  114. Hi,

    I am a regular reader of ur blogs and they are awesome.I am having an issue if you can help me plz.

    I have a DB in SQL 2000 which is being replicated,the issue is that one of the Table have a ‘TEXT’ field.during Transactional Replication,the Agent stops many times,i have increased the size ‘max text repl size’ ,its gone better but still its an issue,is there any other way i can replicate the ‘Text’ field.

    Thanks

  115. Hello,

    I want to configure reporting service 2008 can you tell the steps to configure it. we met you when you come to NIC,Gandhinagar i attend seminer on silver light. we are facing the Credential problem using it.

    Thanking you,

    Chintan

  116. Hi Pinal,

    Could you please help with the below issue.

    If suppose I have 2 flatfiles:

    \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER

    \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 INVOICE_ORDER

    Archive folder is \\ODCDEV01\LOAD\Archive

    I get these flatfiles daily..monday through friday. On Monday I get previous friday’s file. On Tuesday I get Monday’s file to process.The date on filename i.e (2011-03-30-22-00-01) gets this way.( Night batch runs and the max load date is loaded in a sql table and that date is included in the filename by the application team..)

    I am creating two packages.

    One for loading the below flatfile

    \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER

    second package for loading below flat file

    \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 INVOICE_ORDER

    Package logic I use is (same logic for two packages)

    1.implementing business logic to select the row inserted by the application team to get the load date.

    2. using sequence container

    a. In that I am using For Each loop . I am trying to create 2 variables. One to hold the archive folder that I could do (\\ODCDEV01\LOAD\Archive). Secong variable is to hold the “\\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER”. As the date in this path changes dynamically I have to capture this and assign to a variable. I am unable to write the expression and use this variable in the for each loop container. I am not sure what datatype I have to give while declaring this variable as it has date and string data in the filename.

    Inside the foreach loop, I am giving dataflow task to load data from flatfile to OLEDB destination. Here again in the flatfile connection string , I dont know how to write the expression.

    After dataflow task I am using File System Task to move flatfile to archive folder. Here again I am unable to give the source connection. Destination connection I could give in the expression.

    So my question is how to write the expression to declare the source variable? Please let me know if you have any questions? Thanks a lot for your time and help.

    Thanks,

    Naveen.

  117. Hi,
    I have a group in AD that logins to windows and what i want to do is, the same AD group to access also SQL Server using the AD credentials.

    my sql server is setup to use mixed mode (windows and sql server login). so lets say my AD group name is :

    fxb-dss-fsx-sqladmin,

    this group have(2 user) access to win 2008(OS) only. Same group have to access SSMS also.

    so i want to give access to the above group to use also SQL Server.

    OS: Win 2008
    SQL: sql 2008

    I have around 50 Sql Server and more then 100 Instance, NO 3rd party tools we used here.

    can anybody guide me how to do this?

    Thanks.
    New DB

  118. I’m trying to create a Database in Microsoft SQL Server 2008 R2. I can sign in under Windows Authentication a set up a database with Login & Password. Everything goes fine until I goto login in to my newly created database. It say ‘login in fail error message 18456′ when I go back to look at my login & password that I created, the password is not the same. It’s twice as long and I don’t know what the password is because it’s all dots. I trying to change it but it never works. Can you help me figure this out? Thanks.

  119. Hello Sir,
    I am Student. I have Two table
    tblEmp
    Emp.ID|Name|Salary|Dep.ID
    1 abc 100 1
    2 xyz 200 2
    3 pqr 300 1

    tblDep
    Dep.ID|Name
    1 D1
    2 D2

    Now Update Salary 10% where Dep Name is ‘D1′ and 20% where Dep Name is ‘D2′ in Single Query use join.

  120. Hi Mr Pinal,

    I was wondering if you could shed some light on a problem that I’m having. I have a SQL Server 2008 installed on a remote desktop. One of my team mates is having trouble accessing it by the server name when he tries to remotely access the Server via SQL Management Studio. I however can connect to it using the IP and the server name. I’ve tried all the steps you indicated on one of your guides. I tried to detect all available servers on the local network but nothing shows up. I’m stuck right now and I was hoping you could shed some light on this. Thank you very much.

    Regards,
    Adrian

  121. hello sir

    when u are free and feeling cool then please write an article about how to debug a query or store procedure in sql server 2008 R2

    i am trying many times but failed to enable the debug in sql server R2

    so i make a kind request to you, to write a step by step article that how we can enable debugging in sql server 2008 R2 and then how to debug a query or stored procedure

    and how to check the query performance and some another hows that is fruit full for me and another sql guys

  122. i have a requirement like if i give the table name i need to generate Script for that existing table with constraints also please help me how to get create table script with constraints of that existing table ….

  123. Hi Pinal,
    Can you give me tsql script for dynamically comparing tables in two different database with same name and i want to return the varying columns in database.
    Regards
    Rahul Trehan

  124. Hi Pinal,
    i am a Junier SQL developer. Now my job is finding the missing indexes in our live database. When i try to find the missing indexes from the database by using DMV’s it does’t allow me to run this queries. Then i explain the situation to my manager then he said,generate a script for this live database and load it into local database server and text it. i did the same thing what he said, But the problem is when i execute the query for missing indexes it doesn’t show any indexes in this database.
    Is it possible to find the missing indexes on local database server after script it from the remote desktop?
    please help me
    thank you,

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