Exporting Data to Excel from SQL Server

Exporting data from SQL Server to Excel can be achieved in a variety of ways. Some of these options include Data Transformation Services (DTS), SQL Server Integration Services (SSIS) and Bulk Copy (BCP). Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job. It is just a matter of your comfort level with the tools and the best solution to meet the need.

Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005). This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL.

Below is the syntax (source is SQL Server Books online):

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]


Below is a simple example of writing out the Job name and date to Sheet1 of an Excel spreadsheet in either SQL Server 2005 or 2000:



Using the OPENROWSET command creates two caveats. The first caveat is the need to have an Excel spreadsheet serve as a template in the needed directory with the correct worksheets and columns. Without this the you would receive an error message. The second caveat is that it is necessary to enable the OPENROWSET command with the SQL Server 2005 Surface Area Configuration utility. Note - This step is not needed for SQL Server 2000. With the loop example you could copy and paste the Excel spreadsheet and load the data as needed.

Identifying version of SQL Server

(Figure-1)


(Figure-2)

Many organizations have multiple SQL Server systems installed, and now that Microsoft has released different service packs, chances of running multiple versions is high. This can be a problem because some features require a minimum service pack level.

For Example, Database mirroring requires a minimum of SQL Server SP1

To determine what version of SQL Server is running, execute below Query in Query Editor (Figure 1): -

SELECT @@version

To retrieve complete product information, execute below query (Figure 2): -

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'),
SERVERPROPERTY('edition')

Creating Custom Menu - Cognos Analyst

User defined menus are added before the Window and Help menus on the menu bar.
When using custom menus, you must create a .txt file.

This text file will consist of menu header and different menu level information.
File for Custom menu will always be a .txt file.

Syntax for creating menu is as follows:-

<Menu Level no><Menu Name>::M|<Library No> <Macro name>

Where;

• Menu Level No is Level 1, 2, 3, 4 .....n
  
• Level 1 is considered to be the top level, Level 2 will be a drop down under Level 1, etc ...
  

Few things to be remembered while creating custom menu text files are as follows: -

• In a text file semi-colon(;) indicates comments/ remark.
  
• Ampersand (&) is a highligted char in the menu, which can be used for selecting from keyboard ALT+(&)character.
  

Example of a custom menu file: -

;Custom menu for Cognos
1Cognos &Incorporated
2&Lists
3&Timescale::M|600102 DLO-periods
3&Versions::M|600102 DLO-Budget Versions
3&Profit and Loss Accounts::M|600102 DLO-Accounts
2&Update
3&Profit and Loss::M|600102 DCU-Accounts

In the above example, Menu is created as

• "Cognos &Incorporated" --> "Lists" --> Timescale
  
• "Cognos &Incorporated" --> "Lists" --> Versions
  
• "Cognos &Incorporated" --> "Lists" --> Profit and Loss Acconts
  
• "Cognos &Incorporated" --> "Update" --> Profit and Loss
  

Steps:

1. From the Tools menu, click Options.
   
2. Click the Custom tab.
   
3. In the Custom Menu File text box, type the path of the custom menu, or browse for the file.
   
4. Click OK.
   
5. You are prompted to restart Analyst.
   
6. For the changes to take effect, click Yes. Analyst closes and re-opens.