Connection Type

Microsoft Excel is a component of MS Office application, which is useful to create spreadsheet. This Office utility is fruitful to create and maintain huge lists easily and effortlessly. It is used for storing contacts, doing statistical analysis, performing financial calculations and much more. Excel is extensively used for storing hundreds and thousands of contacts.

MS Excel application is a boon for people in the marketing field, executives etc, as they prefer to use MS Excel for creating and preserving enormous contacts databases of various clients and prospective customers, along with their names, address, contact numbers, email identity etc, which can be stored in Excel XLS files. With the help of Excel spreadsheets, transactions can be easily conducted.

If you selected "Excel" for the Connection Type.

Optionally, click Create Database to create a new and empty spreadsheet file.

Otherwise:

Click to navigate to and select a .XLS file, then click Open.

If necessary, enter your login name in the User field.

If necessary, enter your password.

Enter any additional parameters required by your spreadsheet.

Click Test Connection.

If the connection was successful, click OK to conclude.

DAverage
In Excel, the DAverage function averages all numbers in a column in a list or database, based on a given criteria. The syntax for the DAverage function is: DAverage( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to average the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DCount
In Excel, the DCount function returns the number of cells in a column or database that contains numbers and meets a given criteria. The syntax for the DCount function is: DCount( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to count the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DCountA
In Excel, the DCountA function returns the number of cells in a column or database that contains nonblank values and meets a given criteria. The syntax for the DCountA function is: DCountA( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to count the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DGet
In Excel, the DGet function retrieves from a database a single record that matches a given criteria. The syntax for the DGet function is: DGet( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to retrieve. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria. Note: If no record matches the criteria, the DGet function returns #VALUE! error. If more than one record matches the criteria, the DGet function returns #NUM! error.

DMax
In Excel, the DMax function returns the largest number in a column in a list or database, based on a given criteria. The syntax for the DMax function is: DMax( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to find the largest number in. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DProduct
In Excel, the DProduct function returns the product of the numbers in a column in a list or database, based on a given criteria. The syntax for the DProduct function is: DProduct( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to multiply the numbers in. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DVarP
In Excel, the DVarP function returns the variance of a population based on the entire population of numbers in a column in a list or database, based on a given criteria. The syntax for the DVarP function is: DVarP( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DVarP function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DMin
In Excel, the DMin function returns the smallest number in a column in a list or database, based on a given criteria. The syntax for the DMin function is: DMin( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to find the smallest number in. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DStDevP
In Excel, the DStDevP function returns the standard deviation of a population based on the entire population of numbers in a column in a list or database, based on a given criteria. The syntax for the DStDevP function is: DStDevP( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DStDevP function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DStDev
In Excel, the DStDev function returns the standard deviation of a population based on a sample of numbers in a column in a list or database, based on a given criteria. The syntax for the DStDev function is: DStDev( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DStDev function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DSum
In Excel, the DSum function sums the numbers in a column or database that meets a given criteria. The syntax for the DSum function is: DSum( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to sum the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DVar
In Excel, the DVar function returns the variance of a population based on a sample of numbers in a column in a list or database, based on a given criteria. The syntax for the DVar function is: DVar( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DVar function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

READ MORE - Connection Type

SQL ODBC connection

SQL ODBC connection strings

Standard Security:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"

Trusted connection:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"

SQL OLE DB connection strings

Standard Security:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

SQL OleDbConnection .NET strings

Standard Security:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

READ MORE - SQL ODBC connection

SQL Functions

SQL Functions

Keyword Description

Create Function Ext_Orders

(parmWhere CHAR(5)) CREATE FUNCTION names the UDTF and registers it with SQL. This function is called Ext_Orders (external orders). This name will be referenced by SQL statements using the function.

You may define an optional comma-delimited parameter list. For table functions, parameter lists are usually passed to a) limit the number of rows returned or b) assist in the calculation of column values.

Returns Table This keyword defines the column list (column names and attributes) that the table function will return.

External Name 'ExtDB.orders' This option is applicable only to functions written in a language other than SQL. The external name is a program name that will be called when the function is invoked. For Java programs, the external name is specified as Class.method. For non-Java programs, the name is specified as library/program.

Language Java This keyword specifies that the function is written in Java. Other options include SQL, COBOL, CL, RPGLE, C, etc.

Parameter Style DB2General The parameter style keyword determines how the database manager will pass parameters to an external program. Research these options thoroughly before writing programs.

For Java, the DB2General style is a DB2-specific option that specifies that the table function will extend the com.ibm.db2.app.UDF class.

Another parameter style called "Java" is used when writing routines conforming to the SQLJ Part 1: SQL Routines standard. However, this standard only supports scalar (not table) functions, so DB2General must be used.

Fenced The fenced option specifies that multiple invocations of the function (i.e., the function is used more than once in a single statement) will run in separate threads so as to avoid potential conflict with each other.

No SQL This parameter, which is mainly for optimization, specifies that there are no DB2 SQL statements to be run by the function.

Disallow Parallel This option is required for UDTFs that cannot be multi-threaded.

Scratchpad This option specifies that a static storage area will be allocated to the function. This storage area will be preserved between calls to the function. If a number isn't specified, 100 bytes will be allocated.

This keyword is special for Java UDTFs because if it is not specified, Java will instantiate a new instance of the class each time the function is invoked.

Final Call This option specifies that the FIRST and FINAL call types will be invoked.

Returns Null on Null Input If one or more input parameters passed to the table function are NULL, the function will return zero rows.

READ MORE - SQL Functions

C, C++ and Java Assignment Operators

C, C++ and Java Assignment Operators
= -- simple assignment
+= -- add rhs, then assign to lhs
-= -- subtract rhs, then assign to lhs
*= -- multiply by rhs, then assign to lhs
/= -- divide lhs by rhs, then assign to lhs
&= -- bitwise and, then assign to lhs
|= -- bitwise or, then assign to lhs
^= -- bitwise exclusive or, then assign to lhs
%= -- bitwise remainder, then assign to lhs
<<= -- left shift lhs by rhs, then assign to lhs >>= -- signed right shift, then assign to lhs
>>>= -- unsigned right shift, then assign to lhs

Infix Operators
|| -- logical or
&& -- logical and
| -- integer bitwise or
^ -- integer bitwise exclusive or
& -- integer bitwise and
== -- numerical equality
!= -- numerical inequality
< -- less than > -- greater than
<= -- less than or equal to >= -- greater than or equal to
<< -- left shift >> -- signed right shift
>>> -- unsignd right shift
+ -- addition or string concatenation
- -- subtraction
* -- multiplication
/ -- division
% -- remainder

READ MORE - C, C++ and Java Assignment Operators

import java.sql

import java.sql.*;

public class DataTransfer {

private static String[] tableNames = new String[]{"SOME_TABLE_NAME"};
private static final int FETCH_SIZE = 100;
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection sourceConnection = ...;
Connection targetConnection = ...);

// records transfered counter
int c = 0;

for (String tableName : tableNames) {
final PreparedStatement ps = sourceConnection.prepareStatement("SELECT * FROM " + tableName);
ps.setFetchSize(FETCH_SIZE);
final ResultSet rs = ps.executeQuery();
final ResultSetMetaData metaData = rs.getMetaData();
final int columnCount = metaData.getColumnCount();
String insertSQL = "INSERT INTO " + tableName + " ";

while (rs.next()) {
StringBuffer columns = new StringBuffer("(");
StringBuffer params = new StringBuffer("(");

for (int i = 0; i < columnCount; i++) { columns.append(metaData.getColumnName(i+1)); params.append("?"); if ((i+1) != columnCount) { columns.append(","); params.append(","); } } columns.append(")"); params.append(")"); final String sql = insertSQL + columns.toString() + " VALUES " + params.toString(); c++; if (c > 0 && c % FETCH_SIZE == 0) {
System.out.println("Transfered " + c + " records.");
}

final PreparedStatement insertStatement = targetConnection.prepareStatement(sql);
for (int i = 0; i < columnCount; i++) {
insertStatement.setObject(i+1, rs.getObject(i+1));
}
insertStatement.execute();
targetConnection.commit();

insertStatement.close();
}
rs.close();
ps.close();
}
sourceConnection.close();
targetConnection.close();
System.out.println("Total transfered " + c + " records.");
}
}

READ MORE - import java.sql

Simple Database

A Simple Database Backup:

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
[username] - this is your database username
[password] - this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the file to which the backup should be written.


The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database 'Customers' with the username 'sadmin' and password 'pass21' to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p pass21 Customers > custback.sql

You can also ask mysqldump to add a drop table command before every create command by using the option --add-drop-table. This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump --add-drop-table -u sadmin -p pass21 Customers > custback.sql

Backing up only specified tables

If you'd like restrict the backup to only certain tables of your database, you can also specify the tables you want to backup. Let's say that you want to backup only customer_master & customer_details from the Customers database, you do that by issuing

mysqldump --add-drop-table -u sadmin -p pass21 Customers customer_master customer_details> custback.sql

So the syntax for the command to issue is:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]
[tables] - This is a list of tables to backup. Each table is separated by a space.

READ MORE - Simple Database

Central Management Service

The CMS store is used by the Central Management Service to maintain a current Lync Server 2010 Topology for the entire Lync deployment (Topology, Policies, Voice Routes, etc…). Here is what you need to know about the CMS:
The database used for this purpose is called “Xds”; it maintains the Lync configuration as published by the Topology Builder.
There is only one master copy of CMS database which is automatically installed on the first instance of a Standard or Enterprise Edition Lync pool.
Every subsequent Lync server in the topology gets a read-only copy of it. A topology change on the master is replicated to each read-only copy on each Lync server. This is a key element to Lync Server’s survivability feature set – if the network connection between a Lync Edge server goes down for example, the Edge server still knows about the topology and can keep functioning.

Changes are replicated to all Lync server roles except the Lync Edge using the Windows file copy SMB protocol. Changes are replicated to the Edge role via HTTPS. The Windows service “Lync server replica replicator agent” is responsible for receiving the snapshot and uploading the local copies of the databases. It then sends a status update to the Master Replicator (also a windows service) running on the CMS.

In CCM, on the Dependancy tab for CMS I have added :

OracleServiceBW
OracleMTSRecoveryService
OracleOraHome92TNSListener
OracleOraHome92Agent

READ MORE - Central Management Service

Visual Basic

Visual Basic is an event-driven language. So, when you talk about events, we're talking about things that happen in the program which cause little events to occur (similar idea to IRQ stuff you learned hopefully in 455). An example of this could be clicking a button causing a button_click event to be generated. You write code to react to these events and guide a user through your program.

You design your form completely visually, using a set of widget tools and drawing on the form directly just like you would in a paint program or something like this. This stuff is so easy, you'll love it ... or maybe not ... but it's quick and relatively pain free.

Okay, these examples were developed by James Tam, and I've just reproduced them here. His website has all his full details on what exactly these things entail, but I'll get into them a little bit myself. If it seems that there's something missing here, be sure to check out over there for more info.

Okay, the first thing you need to do with visual basic is basically just start it up. No problem. Go:

Start --- Programs --- Microsoft Visual Basic --- You get the picture .... :)

When you load it up for the first time, Microsoft Office might churn away for a couple of minutes. This is an issue with Office 2000 and you don't need to worry about it too too much. It will only happen the once. In any case, when VB is finally loaded up, and you pick Standard Exe from the new project dialog, here's what you'll be confronted with:

READ MORE - Visual Basic

SQL (Structured Query Language)

SQL (Structured Query Language) is a computer language aimed to store, manipulate, and query data stored in relational databases. The first incarnation of SQL appeared in 1974, when a group in IBM developed the first prototype of a relational database. The first commercial relational database was released by Relational Software (later becoming Oracle).

Standards for SQL exist. However, the SQL that can be used on each one of the major RDBMS today is in different flavors. This is due to two reasons: 1) the SQL command standard is fairly complex, and it is not practical to implement the entire standard, and 2) each database vendor needs a way to differentiate its product from others. In this tutorial, such differences are noted where appropriate.

READ MORE - SQL (Structured Query Language)