Day to Day at the Office

SQL Server: Import Tab From Excel

--FOR XLS FILES (ALL COLUMNS EXAMPLE)

SELECT * INTO NEW_TABLE_NAME

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=FILESOURCE&NAME.xls', 'SELECT * FROM [TABNAME$]')

--FOR XLSX FILES (SPECIFIC COLUMNS EXAMPLE)

SELECT

[COLUMN_NAME],

[ANOTHER_COLUMN_NAME]

INTO NEW_TABLE_NAME

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 8.0;Database=FILESOURCE&NAME.xlsx', 'SELECT * FROM [TABNAME$]')

--LIVE EXAMPLE FOR FY11 ES, XLSX FILE

SELECT

[Legacy Sales Order Identifier],

LEFT([Legacy Sales Order Identifier],9) AS [ES Transaction Number],

'Int ES' AS [Sales Channel Name],

[Sales Motion]

INTO ESTS_Data

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 8.0;Database=C:\location\filename.xlsx', 'SELECT * FROM [FY11 ES Data 4-14-11$]')

Read More

Amazon.com Widgets

SQL Server: Enable 'Ad Hoc Distributed queries'

If you want to use OpenDataSource or OpenRowSet query, then you must have to enable “Ad Hoc Distributed queries”. It is disabled by default as a part of security configuration as per Microsoft.

You can set this option by two ways.

1.) sp_configure command

2.) Surface area configuration.

Since I am a script bee, I will explain steps for sp_configure.

You can run sp_configure to see current status of “run_value” field. If you don’t able to see “Ad Hoc Distributed queries” in the results set. You have to enable “Show advanced option” by following command.

sp_configure 'show advanced options', 1

Reconfigure
GO

You may get message

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you are done with that, you can see “Ad Hoc Distributed queries” in list of sp_configure. You have to set “Run_value” to “1”. If it is “0” than run following command.

sp_configure 'Ad Hoc Distributed Queries', 1

Reconfigure
GO

Which will show you following message.

Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you will go through the procedure described here, you will be able to run OpenRowSet and OpenDataSource query.

SQL, Excel, MS Access: First Word in String

Some examples, find out yourself, hope this helps

IN SQL

--Taking the first word of the Partner Name (FIELD) in this case

(CASE WHEN CHARINDEX(' ',[FIELD]) = 0 THEN UPPER([FIELD])

ELSE REPLACE(UPPER(SUBSTRING(LTRIM([FIELD]),1,CHARINDEX(' ',[FIELD]))),',','')

END)

IN EXCEL

=IF(ISERROR(LEFT(B2,FIND(" ",B2)-1)),B2,LEFT(B2,FIND(" ",B2)-1))

IN ACCESS

TRIM(Left([Yourfieldname], InStr([Yourfieldname], " ")))

What is the mathematical formula to calculate growth % ?

To increase Sale Growth

The amount of increase is the new value after growth less the original amount before the growth. The percent growth is the amount of increase times 100% divided by the original amount.

As an example, let's say the number of students in a school grows from 800 one year to 896 the next. First, subtract:

896 - 800 = 96. This is the increase. Next, divide by the original amount:

96(100%) / 800 = 12%. This is the percent increase.

Pasted from <http://answers.yahoo.com/question/index?qid=20070603082809AAPNXoq>

SQL Server 2008 R2: Configure Server Startup Options (SQL Server Configuration Manager)

SQL Server 2008 R2

Use SQL Server Configuration Manager to configure startup options to be used each time the Database Engine starts. For a list of startup options, see Using the SQL Server Service Startup Options.

SQL Server Configuration Manager writes startup parameters to the registry. They take effect upon the next startup of the Database Engine.

Note

On a cluster, changes must be made on the active server while SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.

To configure startup options

In SQL Server Configuration Manager, click SQL Server Services.
In the right pane, right-click SQL Server (<instance_name>), and then click Properties.
On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;).
For example, to start in single-user mode, insert -m; in front of the existing startup options, and then restart the database. (When you start SQL Server in single-user mode, first stop SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.)

Important

After you are finished using single-user mode, you must remove the -m; from the Startup Parameters box before you can restart the server instance in the normal multi-user mode.

Click OK.
Restart the Database Engine.

SQL Server 2008 R2: Restore the master Database (Transact-SQL)

SQL Server 2008 R2

This topic explains how to restore the master database from a full database backup.

To restore the master database

Start the server instance in single-user mode.
For information about how to specify the single-user startup parameter (-m), see How to: Configure Server Startup Options (SQL Server Configuration Manager).
To restore a full database backup of master, use the following RESTORE DATABASE Transact-SQL statement:
RESTORE DATABASE master FROM <backup_device> WITH REPLACE
The REPLACE option instructs SQL Server to restore the specified database even when a database of the same name already exists. The existing database, if any, is deleted. In single-user mode, we recommend that you enter the RESTORE DATABASE statement in the sqlcmd utility. For more information, see Using the sqlcmd Utility.

Important

After master is restored, the instance of SQL Server shuts down and terminates the sqlcmd process. Before you restart the server instance, remove the single-user startup parameter. For more information, see How to: Configure Server Startup Options (SQL Server Configuration Manager).

Restart the server instance and continue other recovery steps such as restoring other databases, attaching databases, and correcting user mismatches.

Example

The following example restores the master database on the default server instance. The example assumes that the server instance is already running in single-user mode. The example starts sqlcmd and executes a RESTORE DATABASE statement that restores a full database backup of master from a disk device:Z:\SQLServerBackups\master.bak.

Note

For a named instance, the sqlcmd command must specify the -S<ComputerName>\<InstanceName> option.

C:\> sqlcmd

1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH
REPLACE;

2> GO

SQL Server Management Studio: Set a Database to Single-user Mode

This topic describes how to set a user-defined database to single-user mode by using Object Explorer in SQL Server Management Studio. Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

Caution

If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

To set a database to single-user mode

In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Right-click the database to change, and then click Properties.
In the Database Properties dialog box, click the Options page.
From the Restrict Access option, select Single.
If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Monday, February 3, 2014