Databases – Page 5 – www.myw0.com

Create an ODBC Connection to Access Database

07/06/2015

| Microsoft Access, Microsoft Office, Reference

The Microsoft Access, also known as MSAccess, is a management system database from Microsoft, packaged with Microsoft Office Professional which combines the Microsoft Database Engine with a graphical user interface. In Windows 64bit to check whether the 32-bit Microsoft Access Database Engine drivers are installed, go to C:\Windows\SysWOW64\odbcad32.exe. If on a 32-bit machine, go to C:\Windows\System32\odbcad32.exe to access the utility. Go to the drivers tab. If the Microsoft Access Driver lists .mdb and .accdb, then the correct drivers are installed….

SQL Joins

06/23/2015

| No Comments

| SQL

SQL joins are used to combine rows from two or more tables. SQL JOIN An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met. Let’s look at a selection from the “Orders” table: OrderID CustomerID OrderDate 10308 2 1996-09-18 10309 37…

Querying the tables using JOIN Keyword

11/24/2014

| No Comments

| Reference, SQL

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The…

SELECT columns using the LIKE keyword

11/23/2014

| No Comments

| Microsoft SQL, SQL, T-SQL Code

Using the WHERE clause to return on the records in the Employee table where the employees are a manager of some type. This example uses the % character for any number of characters. Using the LIKE keyword to return records from the Person table where users’ first names end in “ary”. This example uses the _ characters to represent a single character. Using the LIKE keyword to return records from the Person table where users’ first names end in…

Selecting Domain from Email Address

11/21/2014

| No Comments

| Microsoft SQL, SQL, T-SQL Code

Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.

Get Current TimeZone Name in SQL Server

11/21/2014

| No Comments

| Microsoft SQL, T-SQL Code

A very common question we developer often encounters is what is the current Timezone of the server where SQL Server is installed. It is very easy to get current Timezone of the server where SQL Server is installed. Here is the simple script for the same.

Microsoft SQL Insert Trigger to Get Current Datetime Value

11/04/2014

| No Comments

| Microsoft SQL, SQL, T-SQL Code

This script gets the datetime value in this format MM/DD/YYYY:HR:MI:SSPM or AM. I could have used the CAST or CONVERT option but the did not reproduce the result that I wanted. There are some option in CONVERT option but they converts into a 24hr format. Here is my trigger for insert event. USE dwdata GO /* —————————————————————————– www.myw0.com, Version 1.0, Creation Date: 11/05/2014 Functionality: Generate Datetime values into a text field —————————————————————————– */ if exists (select * from sysobjects…

Oracle – Create Table – Using Select Statement With Data or No Data

10/17/2014

| No Comments

| Oracle

Here is a sample script: WITH data CREATE TABLE test3 AS SELECT table_name, tablespace_name FROM all_tables; ——————————— Without data CREATE TABLE ctas AS SELECT table_name, tablespace_name FROM all_tables WHERE 1=2; — For example, create a table named EMPLOYEE3 that includes all — of the column definitions from EMPLOYEE where the DEPTNO = D11. CREATE TABLE EMPLOYEE3 AS (SELECT PROJNO, PROJNAME, DEPTNO FROM EMPLOYEE WHERE DEPTNO = ‘D11’) WITH NO DATA —

Who is Active for MSSQL

06/26/2014

| No Comments

| Microsoft SQL

Who is Active is free SQL tool that you can download and use for personal, educational, and internal corporate purposes. If you work with MSSQL server and notice slowness this is a great tool for monitoring SQL transactions and performance in SQL server. I personally use this tool for monitoring SQL deadlocks in the database. You can find more information in this page: http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx You can download the SQL script from here: http://sqlblog.com/files/folders/beta/entry42453.aspx The installation process is very simple:…

SQL FORMAT() Function

03/09/2014

| No Comments

| SQL

The FORMAT() Function The FORMAT() function is used to format how a field is to be displayed. SQL FORMAT() Syntax SELECT FORMAT(column_name,format) FROM table_name; Parameter Description column_name Required. The field to be formatted. format Required. Specifies the format. Example SELECT ProductName, Price, FORMAT(Now(),’YYYY-MM-DD’) AS PerDate FROM Products;

Category: Databases

Create an ODBC Connection to Access Database

SQL Joins

Querying the tables using JOIN Keyword

SELECT columns using the LIKE keyword

Selecting Domain from Email Address

Get Current TimeZone Name in SQL Server

Microsoft SQL Insert Trigger to Get Current Datetime Value

Oracle – Create Table – Using Select Statement With Data or No Data

Who is Active for MSSQL

SQL FORMAT() Function

Menu

Contact Info

–