Friday, May 14, 2010

I had a requirement to import an excel 2007 spreadsheet into a database as a table in sql server 2005. In Excel 2007 the drivers required for exporting have changed when compared to the previous versions. With Excel 2007 you use the driver named 'Microsoft Access 12.0 database engine OL DB provider'

Following are the steps to import data into sql server 2005 from Excel.
  • Right click on the database from sql server management studio and choose tasks -- import data
  • Choose the data source as the driver name specified above ('Microsoft Access 12.0 database engine OL DB provider')
  • Click on properties button and click on All tab
  • Double click on the data source line and give the file name with the exact path in the property value field. Click ok
  • Double click on the Extended properties line and enter Excel12.0 in the property value field. Click ok twice.
  • Click next through the import wizard and preview the data and click finish.


Your data is imported into the sql server database.

Thursday, May 13, 2010

I have attended the idera sponsored webinar on 'Top 10 Most Useful SQL Server DMVs' by Greg Robidoux

Here are my learnings of that webcast

What are DMVs
Dynamic Management Views are views and functions introduced in sql server 2005 for monitoring and tuning sql server performance.

Dynamic Management Objects (DMOs)
Dynamic Management Views (DMVs) -- can select like a view
Dynamic Management Functions(DMFs) --Requires input parameters like a function

When and Why use them
Provides information that was not available in previous version of sql server
Provides a simpler way to query the data just like any other view versus using DBCC commands or system stored procedures

Types of DMVs
  • change data capture
  • common language runtime
  • database mirroring
  • database
  • execution
  • full-text search
  • I/O
  • Index
  • Object
  • Query notifications
  • Replication
  • Resource governor
  • SQL Operating System

Get a list of all DMOs

select name, type_descfrom sys.all_objects where name like 'dm%' order by name

Permissions

Server scoped-- view server state

Database scoped--view database state

Deny takes prescedencedeny state or deny select on an object
People should have sys admin privileges

Grant permissions

grant view server state to loginname

grant view database state to user
deny view server state to loginname

deny view database state
must create user in master first

Specific types of DMVs

  • database
  • execution
  • IO
  • Index
  • SQL operatng system

Database for page and row count

select object_name(object_id) as objname, * from sys.dm_db_partition_stats order by 1

Tips 1851 -- mssqltips.com

Execution--- (when sql server is restart everything is reset)
sys.dm_exec_sessions-- info about all active user connections and internal tasks

sys.dm_exec_connections-- info about connections established

sys.dm_exec_requests-- info about each request that is executing (including all system processes)
Tips 1811, 1817, 1829, 1861

Execution--- Query plans
sys.dm_exec_sql_text--returns text of sql batch

sys.dm_exec_query_plan--returns showplan in xml

select * from sys.dm_exec_query_stats-- returns stats for cached query plans sys.dm_exec_cached_plans--each query plan that is cached

Exection -- example

select * from dm_exec_connections cross apply
sys.dm_exec_sql_text(most_recent_sql_handle)
select * from dm_exec_requests cross apply
sys.dm_exec_sql_text(sql_handle)

Select T.[text],p.[query_plan], s.[program_name],s.host_name, s.client_interface_name, s.login_name, r.* from sys_dm_exec_requests r inner join sys.dm_exec_sessions S ONs.session_id = r.session_idcross apply sql_text cross apply sys.dm_execsql_query_plan
select usecounts, cacheobjype, objtype, text from sys.dm_exec_cached_planscross apply dm_exec_sql_text(plan_handle)where usecounts > 1 order by use counts desc


IO

select * sys.dm_io_pending_io_requests can be run when you think that io can be a bottleneck select * from sys.dm_io_virtual_file_stats (null,null)

select db_name(database_id), * from sys.dm_io_virtual_file_stats(null,null) --shows io stats for data and log files -- database id and
file id -- null returns all datadb_name is a funtion to return the name of the actual
database rather than database id


Index (when sql server is restart everything is reset)
sys.dm_dm_db_index_operational_stats (DMF) -- shows io, locking and access information such as inserts, deletes, updates
sys.dm_dm_db_index_physical_stats (DMF) -- shows index storage and fragmaentation info,
sys.dm_dm_db_index_usage_stats (DMV) -- shows how often indexes are used and for what type of SQL operation
Tips 1239, 1545, 1642, 1749, 1766, 1789

Index examples
select db_name(dtabase_id), object_name(), * from operation_stats(5,null,null,null)
parameters databaseid, objectid, indexid, partition number
select db_name(dtabase_id), object_name(), * from physical_stats(DB_ID(N'Northwind'),5,null,null,null, detailed)
parameters databaseid, objectid, indexid, partition number, mode

Missing indexes

  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_groups
  • sys.dm_db_missing_index_group_stats
  • sys.dm_db_missing_index_columns
    Tip 1634

SQL Operating system

sys.dm_os_schedulers-- information abt processors
sys.dm_os_sys_info-- info abt computer and abt resources available to and consumed by sql server
sys.dm_os_sys_memory-- how memory is used overall on the server, and how much memory is available.
sys.dm_os_wait_stats-- info abt all waitsDBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
Tips 1949


sys.dm_os_buffer_descriptors-- info abt all data pages that are currently in the sql server buffer pool
Tips 1181, 1187

memory use by database

memory use by table

Monday, May 10, 2010

SSAS does not work if copied from another machine as a VM -- Why?

Did you know that even if you create an exact copy of the sql server virtual machine, the analysis services will not work?

I faced this problem last week.

The exisitng development machine (lets call it DEV1) stopped working and was giving lots of errrors.
So we thought that we will create a copy of the exisitng production system as a virtual machine and use that as the development machine and we created an exact copy of the production system as a virtual machine but gave it a different name (DEV2).
Then decomissioned the original development machine.

When I tried to access the sql server database on the new development machine (DEV2) the access was fine. But when I tried to access the analysis services through the management console, there was no response.

So I thought I had two options to make analysis services to work --

  • Option 1: Reload sql server from scratch and ssas and SSRS and then copy all databases and cubes from the production server.
  • Option 2: Try renaming the new development machine (Dev2) to the old development machine (Dev1)

Since option 2 was easier I thought I would try that first. And bingo the trick worked.

But I still don't know why analysis services does not work from a copy.

Can anyone of you help me understand why?

Thursday, May 06, 2010

PASS Hosts New Com.PASS Content Feeds

PASS is pleased to host Com.PASS, a new set of content feeds that provide Microsoft SQL Server and Business Intelligence professionals broad access to quality information across respected community Websites.

Conceived by Brian Knight of Pragmatic Works and developed in collaboration with PASS President Rushabh Mehta, SQLServerCentral.com Editor Steve Jones, and sswug.org Founder and Managing Editor Stephen Wynkoop, Com.PASS employs a SQL Server Integration Services (SSIS) package that uses keywords to scrub selected community Websites for relevant content.
“Com.PASS is about making it faster and easier for busy SQL Server pros to find the information they need to do their jobs better,” notes PASS’s Mehta. “You can quickly get lost in the sea of links and information available on the Web. Com.PASS feeds you content in your target topic areas from sites that you can trust.”

Initially focusing on BI content, Com.PASS currently includes five feeds:

§ Com.PASS.BI, for BI content across the Microsoft SQL Server and Office stacks
§ Com.PASS.SSAS, for SQL Server Analysis Services content
§ Com.PASS.SSIS, for SQL Server Integration Services content
§ Com.PASS.SSRS, for SQL Server Reporting Services content
§ Com.PASS, which combines all the feeds Just click a feed to add it to your RSS reader.

Learn more and subscribe to your favorite feeds.

Wednesday, May 05, 2010

Exceptional DBA Awards -- Get nominated or nominate people you know

Nominations for The Exceptional DBA of 2010 open today, giving database administrators around the world the chance to be recognized for service to their employers, the SQL Server community, and the IT industry at large.
The link to the site is:

http://www.exceptionaldba.com/?utm_source=ssc&utm_medium=survey&utm_content=dba_awards&utm_campaign=sqlbackupbundle

Secure and available data is crucial for a company's success, and so are the DBAs. All too often DBAs don't get the respect they deserve.
And if you agree with us that it's time to change this, then please help us find 2010's Exceptional DBA Awards winner!

"If you are an exceptional DBA, or know of an exceptional DBA, I encourage you to participate in the Exceptional DBA Awards. Not only will it give you or some exceptional DBA some much-deserved recognition, it will also help to increase the awareness of the importance of the DBA role among the IT community."
Brad McGehee, Exceptional DBA Awards Judge

Would you like to nominate yourself or a DBA you know? Nominations are now open, and we are waiting for your entry! Please make sure that all details have been submitted before June 4, 2010.

Free resources for exceptional DBAs
===========================
The awards sponsor, Red Gate Software, is offering you Brad McGehee's "Day-to-Day DBA Best Practices" poster and a free trial of the SQL Backup Bundle – all Red Gate's DBA tools in a single suite. Red Gate's SQL Backup Bundle includes products such as SQL Backup, to compress, encrypt and strengthen backups, and SQL Response, to monitor SQL Server health and activity. Download free resources now.

Saturday, May 01, 2010

XSLT, XML tools

Here are two free tools for working with XSLT, XQuery and XPath.

XRay is a free XML editing enviroment. Now in its second major release, XRay provides support for XML Schema (XSD) and an integrated online XML tutorial system.
XRay is free. Download it right away!.

Kernow is an open source tool designed to make it faster and easier to repeatedly run transforms using Saxon.
It uses compiled stylesheets, multiple threads and caching resolvers to make the transforms run efficiently, and comboboxes that remember between runs to save your fingers having to retype paths. Kernow is runnable from Ant allowing it to slot into your build process, and its a high level API for Saxon making it very easy to run transforms from your own Java applications.
Kernow is written by Andrew Welch using Java 1.6.

Rerunning a SSRS subscription report

Yesterday I had a situation where  a couple of SSRS reports failed due to a network upgrade. The SSRS report was not able to access the SQ...