Tuesday, October 22, 2013

“The program can’t start because MSVCR100.dll is missing from your computer.” error on Windows

I was installing a software on windows 2008R2 64 bit edition and ran into this error.

"The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem"

After doing some online research I figured the Microsoft Visual C++ Redistributable package requires installed prior installing my desired installation.

This Redistribution package is available on microsoft website. Here's the link for both x86 or x64 edition.

32 bit: http://www.microsoft.com/download/en/details.aspx?id=5555
64 bit: http://www.microsoft.com/download/en/details.aspx?id=14632

Friday, August 23, 2013

MySQL Import Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes


Today I was importing a MySQL database and ran into this Error “Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes”.

Basically , When you dump table data from MySQL, you may end up pulling a large chunk of data and it may exceed the MySQL client’s max_allowed_packet variable. If that happens, you might catch an error like this:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `tablename` at row: 1627

The default max_allowed_packet size is 25M, and you can adjust it for good within your my.cnf by setting the variable in a section for mysqldump:
OR
The fix is to increase the MySQL daemon’s max_allowed_packet. You can do this to a running daemon by logging in as Super and running the following commands. Keeping the session open create a 2nd session in which to run the import.

mysql> set global net_buffer_length=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global max_allowed_packet=1000000000;
Query OK, 0 rows affected (0.00 sec)

Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..


Wednesday, July 31, 2013

MySQL : Monitor live MySQL queries

You can run the mysql command "show processlist" to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for as this won’t show the whole SQL text.
 
Here are the steps to dump sql into a file for analysis.
 
mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
 
Turn on the SQL Logging and go to the general_log_file location.
 
mysql> SET GLOBAL general_log = 'ON';
Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log

Then don't forget to disable the general_log parameter to OFF.

mysql> SET GLOBAL general_log = 'OFF';


or the performance will plummet and your disk will fill!

Friday, July 26, 2013

MSSQL : Bring database back to normal from Restoring State

If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each restore except the last. If your database is still in the restoring state and you want to recover it without restoring additional backups you can issue a RESTORE DATABASE .. WITH RECOVERY to bring the database online for users to use.

Here is the syntax.

RESTORE DATABASE <DATABASE_NAME>
WITH RECOVERY

Friday, July 19, 2013

SQL Server: Linked Server is not configured for RPC error:

Workaround for calling table-valued function remotely in SQL Server over Linked Server needs some fancy in the coding. Something like this


EXEC SERVER_LINK.DATABASE_NAME..sp_executesql N'SELECT value FROM fn_testexecute(''value1'', value2, value3, value3, value4);'

Now what if the SQL fails due to below error:

Msg 7411, Level 16, State 1, Line 2
Server 'TEST_LINK' is not configured for RPC.

This is a configuration thing with the Linked Server option called "RPC OUT". This is necessary to execute a procedure calls to go out to the linked Server. Where as, "RPC" option allows stored procedure call from the linked Server.

For this case, I had to enable "RPC Out". I ran sp_helpserver and it reported RPC OUT isn’t enabled.


That's it. Problem solved.

MSSQL Server to MSSQL Server Instance connectivity with Linked Server

In order to create LINKED Server the user needs to have SYSADMIN privileges. Here in this demonstration I’m going to show the preferred LINKED SERVER option called “Be made using the login’s current security context”. Condition is, LOGIN needs to stay on both the servers. This is the preferred and recommended method in terms of security as other users can’t use this link except for the Login I’ll be using.


Here are the steps:

1. I’m going to use a user called TEST for this demonstration, and going to provide SYSADMIN privilege to this user. Once the link created and tested, I’ll revoke SYSADMIN privilege from the TEST user.

2. Login as TEST user

3. Open SSMS, Go to Server Objects-->Right Click Linked Servers--> New Linked Server. The below form pops up

4. Give a name for “Linked Server” , choose the provider as shown on form. Product name here I mentioned ‘*’ to specify any SQL Server product. On the Data source I’m mentioning the instance name from where I’d be pulling data. It’s “MSSQLTEST” for my case.


5. On the security tab select “Be made using the login’s current security context”. This would use my used Loging ie, “TEST” for this case.
6. Click OK, now you can browse through the objects using SSMS-->Linked Servers--> TEST_LINK-->Catalogs or you could use below syntax to fetch any table data

select * from [TEST_LINK].[TEST_DB].[dbo].[TEST_TABLE];
  7. Revoke “Sysadmin” privilege from “TEST” user

My “Linked Servers” has been created!

Thursday, July 18, 2013

SQL Server - Maintenace plan integrity checks fail with "Alter Failed for server xxx"

My maintenance plan integrity check task was failing with "Alter failed for server XXX". 

After tracing i found the maintenance plan executes sp_configure 'USER OPTIONS',xxxx followed by RECONFIGURE. The reconfigure statement then generates an error causing the plan to fail. This was because the server configuration option "Allow Updates" was set to 1. Changed the setting back to 0 and the reconfigure statement started to work again!

Following command changes "Allow Updates" parameter to "0"

SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE
GO 


Friday, June 28, 2013

SQL Server Performance Dashboard Reports in SSMS

The step by step procedure has been published on Microsoft MSDN blogs both for 2005 & 2008.
Couple of tweaks required for 2008, but everything is documented.
Here's the link.

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx

This is how it looks like:

 

Tuesday, June 11, 2013

When did a user changed his password in Oracle?

The table sys.user$ contains the field ptime, which keeps the time when the password was changed the last time over. Do not confound it with ctime, which is the "creation time", nor with ltime, which is the time the account has been locked (if any).

SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED"
FROM USER$
WHERE ptime IS NOT NULL
ORDER BY ptime DESC;



Friday, June 07, 2013

Recover MySQL Root Password

If you already have the root password, but just want to change it. Then it's simple and can be 
done when MySQL Database is up & running.
/usr/bin/mysqladmin -u root password 'new-password’
But If ROOT password is lost then following steps needs to be performed and requires reboot of 
MySQL Database:
STOP MySQL Service:
/etc/init.d/mysql stop
START MySQL Without grants:
mysqld_safe --skip-grant-tables &
Connect to mysql server using mysql client:
mysql -u root
Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
Stop MySQL Server:
# /etc/init.d/mysql stop
Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p

 

Tuesday, June 04, 2013

Create oracle as is user

The following script takes the input for SOURCE user and for the user I want to create. This script can go through many customization to suit your needs.
This can extract existing user hash password, tablespace quota, object/system/role privileges. Infact everything associated with the source user.

SET SERVEROUTPUT ON
SET LINESIZE 200
DECLARE
    TYPE T_CURSOR IS    REF CURSOR;
    V_CURSOR            T_CURSOR;
    V_USER                VARCHAR2 (30) := UPPER ('&1');
    V_NEW_USER            VARCHAR2 (30) := UPPER ('&2');
    V_PASSWORD1            VARCHAR2 (30);
    V_PASSWORD2            VARCHAR2 (30);
    V_D_TBS                VARCHAR2 (30);
    V_T_TBS                VARCHAR2 (30);
    V_PROFILE            VARCHAR2 (30);
    V_LOCK_DATE            DATE;
    V_COUNTER            NUMBER;
    V_MAX_BYTES            NUMBER;
    V_PRIVILEGE            VARCHAR2 (40);
    V_ADMIN_OPT            VARCHAR2 (3);
    V_ROLE                VARCHAR2 (30);
    V_DEF_ROLE            VARCHAR2 (3);
    V_DEFROLE            NUMBER;
    V_OUTPUT            VARCHAR2 (500);
    V_TABLE_NAME        VARCHAR2 (30);
    V_OWNER                VARCHAR2 (30);
    V_GRANT_OPT            VARCHAR2 (3);
    V_HIER_OPT            VARCHAR2 (3);
    V_COL_NAME            VARCHAR2 (30);
    V_AUDIT_OPT            VARCHAR2 (40);
    V_SUCCESS            VARCHAR2 (10);
    V_FAILURE            VARCHAR2 (10);

BEGIN
    SELECT COUNT (*)
      INTO V_COUNTER
      FROM DBA_USERS
     WHERE USERNAME = V_USER;

    IF V_COUNTER <> 0
    THEN
        IF V_NEW_USER IS NULL
        THEN
            V_NEW_USER := V_USER;
        END IF;

        SELECT B.PASSWORD, A.PASSWORD, A.DEFAULT_TABLESPACE, A.TEMPORARY_TABLESPACE, A.PROFILE, A.LOCK_DATE
          INTO V_PASSWORD1, V_PASSWORD2, V_D_TBS, V_T_TBS, V_PROFILE, V_LOCK_DATE
          FROM DBA_USERS A, SYS.USER$ B
         WHERE A.USERNAME = V_USER
         AND A.USERNAME = B.NAME;

        --Create User Command * Begin
        DBMS_OUTPUT.PUT_LINE ('--Create User command');
        DBMS_OUTPUT.PUT_LINE ('CREATE USER ' || V_NEW_USER);
        IF V_PASSWORD2 = 'EXTERNAL'
        THEN
            DBMS_OUTPUT.PUT_LINE ('IDENTIFIED EXTERNALLY');
        ELSE
            DBMS_OUTPUT.PUT_LINE ('IDENTIFIED BY VALUES ''' || V_PASSWORD1 || '''');
        END IF;
        DBMS_OUTPUT.PUT_LINE ('DEFAULT TABLESPACE ' || V_D_TBS);
        DBMS_OUTPUT.PUT_LINE ('TEMPORARY TABLESPACE ' || V_T_TBS);
        DBMS_OUTPUT.PUT_LINE ('PROFILE ' || V_PROFILE);
        IF V_LOCK_DATE IS NULL THEN
            DBMS_OUTPUT.PUT_LINE ('ACCOUNT UNLOCK;');
        ELSE
            DBMS_OUTPUT.PUT_LINE ('ACCOUNT LOCK;');
        END IF;
        --Create User Command * End

        --Tablespace Quotas * Begin
        SELECT COUNT (*)
          INTO V_COUNTER
          FROM DBA_TS_QUOTAS
         WHERE USERNAME = V_USER;

        IF V_COUNTER > 0
        THEN
            DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' tablespace quotas');

            OPEN V_CURSOR FOR
                SELECT TABLESPACE_NAME, MAX_BYTES
                  FROM DBA_TS_QUOTAS
                 WHERE USERNAME = V_USER;

            LOOP
                FETCH V_CURSOR
                INTO V_D_TBS, V_MAX_BYTES;

                EXIT WHEN V_CURSOR%NOTFOUND;

                IF V_MAX_BYTES = -1
                THEN
                    DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' QUOTA UNLIMITED ON ' || V_D_TBS || ';');
                ELSE
                    DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' QUOTA ' || V_MAX_BYTES || ' ON ' || V_D_TBS || ';');
                END IF;
            END LOOP;

            CLOSE V_CURSOR;
        END IF;
        --Tablespace Quotas * End

        --Roles * Begin
        SELECT COUNT (*)
          INTO V_COUNTER
          FROM DBA_ROLE_PRIVS
         WHERE GRANTEE = V_USER;

        IF V_COUNTER > 0
        THEN
            DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' Roles');

            SELECT DEFROLE
              INTO V_DEFROLE
              FROM SYS.USER$
             WHERE NAME = V_USER;

            CASE V_DEFROLE
                WHEN 0
                THEN
                    DBMS_OUTPUT.PUT_LINE (
                        'ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE NONE;');
                WHEN 1
                THEN
                    DBMS_OUTPUT.PUT_LINE (
                        'ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE ALL;');
                ELSE
                    NULL;
            END CASE;

            OPEN V_CURSOR FOR
                SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE
                  FROM DBA_ROLE_PRIVS
                 WHERE GRANTEE = V_USER;

            LOOP
                FETCH V_CURSOR
                INTO V_ROLE, V_ADMIN_OPT, V_DEF_ROLE;

                EXIT WHEN V_CURSOR%NOTFOUND;
                V_OUTPUT := 'GRANT ' || V_ROLE || ' TO ' || V_NEW_USER;

                IF V_ADMIN_OPT = 'NO'
                THEN
                    V_OUTPUT := V_OUTPUT || ';';
                ELSE
                    V_OUTPUT := V_OUTPUT || ' WITH ADMIN OPTION;';
                END IF;

                DBMS_OUTPUT.PUT_LINE (V_OUTPUT);

                IF V_DEFROLE = 2 AND V_DEF_ROLE='YES'
                THEN
                    DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE ' || V_ROLE || ';');
                END IF;
            END LOOP;

            CLOSE V_CURSOR;
        END IF;
        --Roles * End

        --System Privileges * Begin
        SELECT COUNT (*)
          INTO V_COUNTER
          FROM DBA_SYS_PRIVS
         WHERE GRANTEE = V_USER;

        IF V_COUNTER > 0
        THEN
            DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' system privileges');

            OPEN V_CURSOR FOR
                SELECT PRIVILEGE, ADMIN_OPTION
                  FROM DBA_SYS_PRIVS
                 WHERE GRANTEE = V_USER;

            LOOP
                FETCH V_CURSOR
                INTO V_PRIVILEGE, V_ADMIN_OPT;

                EXIT WHEN V_CURSOR%NOTFOUND;
                V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' TO ' || V_NEW_USER;

                IF V_ADMIN_OPT = 'NO'
                THEN
                    V_OUTPUT := V_OUTPUT || ';';
                ELSE
                    V_OUTPUT := V_OUTPUT || ' WITH ADMIN OPTION;';
                END IF;

                DBMS_OUTPUT.PUT_LINE (V_OUTPUT);
            END LOOP;

            CLOSE V_CURSOR;
        END IF;
        --System Privileges * End

        --Object Privileges * Begin
        SELECT COUNT (*)
          INTO V_COUNTER
          FROM DBA_TAB_PRIVS
         WHERE GRANTEE = V_USER;

        IF V_COUNTER > 0
        THEN
            DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' object privileges');

            OPEN V_CURSOR FOR
                SELECT PRIVILEGE, OWNER, TABLE_NAME, GRANTABLE, HIERARCHY
                  FROM DBA_TAB_PRIVS
                 WHERE GRANTEE = V_USER;

            LOOP
                FETCH V_CURSOR
                INTO V_PRIVILEGE, V_OWNER, V_TABLE_NAME, V_GRANT_OPT, V_HIER_OPT;

                EXIT WHEN V_CURSOR%NOTFOUND;
                V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' ON ' || V_OWNER || '.' || V_TABLE_NAME || ' TO ' || V_NEW_USER;

                IF V_HIER_OPT = 'YES'
                THEN
                    V_OUTPUT := V_OUTPUT || ' WITH HIERARCHY OPTION';
                END IF;

                IF V_GRANT_OPT = 'NO'
                THEN
                    V_OUTPUT := V_OUTPUT || ';';
                ELSE
                    V_OUTPUT := V_OUTPUT || ' WITH GRANT OPTION;';
                END IF;

                DBMS_OUTPUT.PUT_LINE (V_OUTPUT);
            END LOOP;

            CLOSE V_CURSOR;
        END IF;
        --Object Privileges * End

        --Column Privileges * Begin
        SELECT COUNT (*)
          INTO V_COUNTER
          FROM DBA_COL_PRIVS
         WHERE GRANTEE = V_USER;

        IF V_COUNTER > 0
        THEN
            DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' column privileges');

            OPEN V_CURSOR FOR
                SELECT PRIVILEGE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTABLE
                  FROM DBA_COL_PRIVS
                 WHERE GRANTEE = V_USER;

            LOOP
                FETCH V_CURSOR
                INTO V_PRIVILEGE, V_OWNER, V_TABLE_NAME, V_COL_NAME, V_GRANT_OPT;

                EXIT WHEN V_CURSOR%NOTFOUND;

                V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' (' || V_COL_NAME || ') ON ' || V_OWNER || '.' || V_TABLE_NAME || ' TO ' || V_NEW_USER;

                IF V_GRANT_OPT = 'NO'
                THEN
                    V_OUTPUT := V_OUTPUT || ';';
                ELSE
                    V_OUTPUT := V_OUTPUT || ' WITH GRANT OPTION;';
                END IF;

                DBMS_OUTPUT.PUT_LINE (V_OUTPUT);
            END LOOP;
           
            CLOSE V_CURSOR;
        END IF;
        --Column Privileges * End
   
        --Auditing options * Begin
        SELECT COUNT (*)
          INTO V_COUNTER
          FROM DBA_STMT_AUDIT_OPTS
         WHERE USER_NAME = V_USER;

        IF V_COUNTER > 0
        THEN
            DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' auditing options');

            OPEN V_CURSOR FOR
                SELECT AUDIT_OPTION, SUCCESS, FAILURE
                  FROM DBA_STMT_AUDIT_OPTS
                 WHERE USER_NAME = V_USER;

            LOOP
                FETCH V_CURSOR
                INTO V_AUDIT_OPT, V_SUCCESS, V_FAILURE;

                EXIT WHEN V_CURSOR%NOTFOUND;

                IF V_SUCCESS <> 'NOT SET'
                THEN
                    DBMS_OUTPUT.PUT_LINE('AUDIT '||V_AUDIT_OPT||' BY '||V_NEW_USER||' '||V_SUCCESS||' WHENEVER SUCCESSFUL;');
                END IF;
                IF V_FAILURE <> 'NOT SET'
                THEN
                    DBMS_OUTPUT.PUT_LINE('AUDIT '||V_AUDIT_OPT||' BY '||V_NEW_USER||' '||V_FAILURE||' WHENEVER NOT SUCCESSFUL;');
                END IF;

            END LOOP;
           
            CLOSE V_CURSOR;
        END IF;
    --Auditing options * End

    ELSE
        DBMS_OUTPUT.PUT_LINE ('User ' || V_USER || ' does not exist.');
    END IF;
END;