aiDeeva.com

Category: DB Articles

  • How to move Cluster Resource/Services to proper node using command prompt?

    cluster group “SQL Server Group” /move: Physical_Name_Of_Node1

    cluster group “SQL Server Group INST2” /move: Physical_Name_Of_Node2

  • Happy Birthday to Me (It’s 1-Sept)

    happybirthdaytome

  • Server: Msg 7391, Level 16, State 1, Line 2

    Problem Symtomps
    The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.

    Cause
    The problem occurs because Microsoft Distributed Transaction Coordinator (MS DTC) is not configured for network access. By default, the network access settings of MS DTC are disabled on new installations of SQL Server 2000 on computers that are running Windows Server 2003.
    Resolution

    Step 1: Open Component Services Wizard using below steps
    Go to -> Start -> All Programs -> Administrative Tools -> Component Services

    Step 2: Expand the Componenet Service and Double Click on Computers

    Step 3: Right Click on MyCompute and select properties

    Step 4: Click on MSDTC Tab and then click on Security Configuration

    Step 5: In the Security Configuration dialog box, check the Network DTC Access check box

    Step 6: Under  Network DTC Access, click on New transaction

     Please find the below images for the same.

    untitled1

    Figure 2

    Select Network Transaction

    Figure 3

    untitled3

  • Insert Extended Character using OSQL Utility

    Problem: OSQL utility uses ODBC to communicate with the server. User’s problem is that the ODBC driver he is using to connect to the database is performing translations on the character data in the T-SQL script. Extended characters, which are not in the standard ASCII character set, are translated by the driver based on drive settings. The character translation option is ON by default when SQL Server executes scripts through the OSQL utility. 

    Below query is inserting garbage data in the table. 

    CREATE TABLE #temp(col1 varchar(40) NOT NULL )

    INSERT INTO #temp VALUES( ‘Tëst’ )

    SELECT col1 FROM #temp

    DROP TABLE #temp 

    Solution: By using Unicode script files and converting the column to Unicode, user can avoid the character translation. For that user needs to add N against the column, which is already added. 

    Save As below script file as UNICODE file 

    CREATE TABLE #temp(col1 varchar(40) NOT NULL )

    INSERT INTO #temp VALUES( ‘Tëst’ )

    SELECT col1 FROM #temp

    DROP TABLE #temp 

    User needs to do the following with the ODBC DSN to execute the scripts successfully, without any translation:

    1.    Create an ODBC system data source called MyDSN on the machine where he is executing OSQL with the “Perform translation for character data” option cleared

    2.    Specify this data source name as a parameter to OSQL so that OSQL can read the DSN settings and use them upon connection to SQL Server. 

    osql -S. -itest.sql –DMyDSN

    OR

    User needs to develop a script which can pass the ASCII value 

    Select ASCII(‘ë’)

    INSERT INTO #temp (Col1) Select ‘T’ + chr(233) +  ‘st’

  • How to improve communication skills & Support Service

    As DBA comes under the support industry, it is extremely important for DBAs to improve communication skills. As we are working production servers and backend, it is extremely important for us to communicate properly. 

    • Please take care of the below points to improve your written communication skill. 
    •  When you reply all, make sure to move names from TO to CC and CC to TO list according to whom you are writing emails
    • Be concise and to the point. Use simple English.
    • Avoid the use of slang words/abbreviations
    • Use proper spelling, grammar & punctuation
    • Do not write in CAPITALS. CAPITALS indicate that we are shouting.
    • Numbers should be expressed as words when the number is less than 10 or is used to start a sentence (example: Ten years ago, my brother and I…). The number 10, or anything greater than 10, should be expressed as a figure (example: My brother has 13 Matchbox cars.)
    • You should just mention the name instead of Hi Jugal.As we are working as support team, use `We` instead of `I`. For example; Jugal instead of Hi Jugal
    • You should change the text in the Subject line during reply emails if it is not relevant.
    • If you are asking for something in the email, you should ask them first and then mention why you need that.
    • Don’t write open ended email to user
    • If you are not sure for any support request, please ask end user for more inputs on particular request.
    • Inform your team regarding any conversation with client through chat or voice call
    • Update your work log every day
    • If you are not sure for any resolution steps, discuss it with your team and then provide it to client
    • When sending large attachments, always “zip” or compress them before sending.
    • Always include subject in your mail body.
    • Don’t send email to any account using your client account
    • Discuss as much as possible with your client
    • Grab as much as possible knowledge of the client process/environment