DBCC OPENTRAN

You manage a database named LTDB. Today, you received a warning that the drive on which the LTDB log file is located is near capacity. Although the transaction log is backed up every five minutes, you observed that it is steadily growing. You think that an uncommitted transaction might be the cause and you want to investigate. You need to identify both the server process ID and the start time of the oldest active transaction in LTDB. What should you do?

A. Connect to the LTDB database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
B. Connect to the master database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
C. In SQL Server Management Studio, open the Activity Monitor. Select the Process Info page and apply the following filter settings.
Database = LTDB, Open Transactions = Yes, View the Process ID and Last Batch columns.
D. Open a query window. Connect to the master database. Execute the following statement
SELECT TOP 1 spid, last_batch
FROM sys.sysprocesses WHERE dbid = db_id('LTDB') AND open_tran > 0
ORDER BY last_batch

Answer:[A]
Highlight to find out the answer

My DB2 Connection string

Provider=DB2OLEDB;User ID=admin;Password=xxxxx;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=MyComputerName;Network Port=50000;Package Collection=Library;Default Schema=Library;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=Library;DBMS Platform=DB2/NT;Defer Prepare=False;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False;

This connection string is the same with production server.

Total security in a PostgreSQL database - StumbleUpon

Total security in a PostgreSQL database - StumbleUpon: "- Sent using Google Toolbar"

My OpenERP services setting

1. OpenERP Server 6.0 (Automatic)
2. OpenERP Web 6.0 (Automatic)
3. postgresql-9.0 - PostgreSQL Server 9.0 (Automatic)

My Oracle services setting

1. OracleDBConsoleMyERP (Automatic)
2. OracleJobSchedulerMYERP (Disabled)
3. OracleOraDb11g_home1TNSListener (Automatic)
4. OracleServiceMYERP (Automatic)

XOR operator

What will the output of the following scripts?
DECLARE @A INT, @B INT
SELECT @A = 12, @B = 15
SET @A = @A^@B
SET @B = @B^@A
SET @A = @A^@B
SELECT @A, @B

A. 12,15
B. Error occurs: no operator ^
C. Error occurs: incorrect syntax
D. 15,12
E. 13,14

Answer:[D]
Highlight to find out the answer.

Read a SQL Server table into a list using Python + pyodbc

import pyodbc as p
server = 'ServerName'
database = 'DatabaseName'
userid = 'UserName'
pwd = 'UserPassword'

connStr = ( r'DRIVER={SQL Server};SERVER=' +
            server + ';DATABASE=' + database + ';' +
            'UID=' + userid + ';PWD='+pwd+';')        
lst = []
conn = p.connect(connStr)
dbCursor = conn.cursor()
sql = ('SELECT ColumnName AS FieldValue FROM tblTableName') 
dbCursor = conn.cursor()
dbCursor.execute(sql)
for row in dbCursor:
    lst.append(row.FieldValue)        
conn.close()
print lst

Shrinking Truncate Log File

USE YourDatabaseName
GO
--Run this script to get your TransactionLogName
SELECT * FROM sys.sysfiles
GO
DBCC SHRINKFILE(TransactionLogName, 1)
BACKUP LOG YourDatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TransactionLogName, 1)
GO

How to kill session remotely

1. Download PsTools at this address http://technet.microsoft.com/en-us/sysinternals/bb896649.aspx and then run Windows command prompt at the folder containing PsTools
2. Run psexec command: D:\PsTools> psexec \\RemoteMachineName_Or_IPAddress -u UserName -p Password cmd
Wait a moment to allow to connect to remote server. A new command prompt will appear like that:
C:\WINDOWS\system32>
3. Run: qwinsta to get session ID need to log off: C:\WINDOWS\system32>qwinsta
A list of sessions will display, choose one you need to kill
4. Run: C:\WINDOWS\system32>logoff SessionID /v

Create linked server to Access 2007 accdb file

EXEC master.dbo.sp_addlinkedserver @server = N'AccessLinkedServerName', @srvproduct=N'Access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:\Projects\MyAccessDB.accdb'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AccessLinkedServerName', @locallogin = NULL , @useself = N'False'