I had this requirement today where I had to copy from a table in one database into a similar table in another database. the code I have used is insert into database1.dbo.table1 select * from database2.dbo.table1 where xxxxx where xxxx is the criteria that you need to add. Precautions you have to take before you do this. Backup the database into which you are copying the record(s) so that you can restore if something goes wrong. Copy contents when there are no other transactions being entered int...
Read More
I have listened to Craig Utley's SelectViews show on SSWUG.org . He gave the analysis tip that defining attribute relationships in SSAS is important for aggregations. What are attribute Relationships? In Analysis Services 2005, Attribute relationships define functional dependencies between attributes. For example an employee and manager relationship. Employee is related to Manager. Manager is relating to Employee Given this definition, consider the query: Select measures.sales on columns from Sa...
I thought I will put together all the date formats as a list here so that it can be a good referencing point when I need them. If anyone knows of anything that is not in here please let me know Query (current date: 21/05/2010) Example select convert(varchar, getdate(), 1) 05/21/10 select convert(varchar, getdate(), 2) 10.05.21 select convert(varchar, getdate(), 3) 21/05/10 select convert(varchar, getdate(), 4) 21.05.10 select convert(varchar, getdate(), 5) 21-05-10 select convert(varchar, getdat...
Defensive Database Programming By Alex Kuznetsov The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected. To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn't break under concurrent loads, and survives predictable changes to database schemas and settings. Inside this book, you will find dozens of practical, defensive programming techniques that will improve the quality...
BI Components of Office 2007 We have built a datawarehouse in our sql server 2005 database and built some cubes in Sql server analysis services using sqlser server integration services. Now I wanted to utilise the BI capabilities of office 2007 to provide useful information to the business users. The BI components of Office 2007 are: Excel 2007 Report Builder Microsoft Office SharePoint Server 2007 (MOSS) Excel Services PerformancePoint Services In this first part I will go through my learnings ...
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 dri...
I hve 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 ...
An exceptional lineup of SQL Server and BI experts is coming to your computer starting at 12:00 GMT (UTC) on May 19. 24 Hours of PASS provides an in-depth look at the hottest SQL Server and BI topics , including (but not limited to!) the new SQL Server 2008 R2. Take a look at some the sessions on offer: What Exactly is in SQL Server 2008 R2? Solving Common Business Problems with Microsoft PowerPivot Filtered Indexes, Sparse Columns: Together, Separately Top 10 Mistakes on SQL Server BLITZ! 60 Mi...
Here are the steps I followed to schedule a Sql server analysis services backup Connect to Analysis services using Management Studio Right click on the analysis services database for which you want to schedule the backup Click on backup Tick the Allow overwrite check box At the top click on the arrow beside the script and choose script to New Query Window The code is scripted. Below is an example. <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <...
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 (DEV...
Microsoft has released an ebook named -- " Introducing Microsoft SQL Server 2008 R2 , by Ross Mistry and Stacia Misner!" The book contains 10 chapters and 216 pages, like so: PART I Database Administration CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements 3 CHAPTER 2 Multi-Server Administration 21 CHAPTER 3 Data-Tier Applications 41 CHAPTER 4 High Availability and Virtualization Enhancements 63 CHAPTER 5 Consolidation and Monitoring 85 PART II Business Intelligence Development CHAPTER 6 Sca...
The following is a news item I thought would be interesting for all of you. 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...
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 the...
We had a really interesting session with Patrick of Pragmatic Works yesterday on the topic "Introduction to SSIS". He has covered the following basic aspects of SSIS Opening BIDS environment with a -NOSPLASH Creating a small package with some package requirements -- Control flow, Data flow, Event Handler, package explorer Deplying the created package Scheduling a package Here are my takeways from this session Tip 1 You can use -NOSPLASH in the shortcut of the Visual studio icon to avoid the init...
SQL Server 2008 R2 Training Kit New updated version of SQL Server 2008 R2 Training Kit is available for download from the microsoft site. This has some hands on labs, tutorials, presentations, and videos. Download from: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d Hope you will enjoy using this training kit.
Due to the proliferation of personal firewalls, inconsistent network library configurations, and multiple-instance support, SQL Server installations are becoming increasingly difficult to discover, assess, and maintain. Peter Ward from WardyIT Solutions while presenting in the SQL Saturday event at Auckland last weekend has asked all of us the following question: “Do you know how many instances are there in your network?” Only two people in the entire room have raised their hands. He mentioned a...
This past weekend I have attended the SQL Saturday event in Auckland The turnout for this event was really good with close to 100 people attending including the organizers and presenters. Thanks to everyone that helped out to put on a very successful event. Here is a list of the presenters and the topics presented. We had to cancel the main organizer’s (Dave Dustin) due to lack of time. Session Title My learning’s in brief Analysis Services - Introduction to Business Intelligence with SQL 2008 J...
Checksum vs. Hashbytes What is Checksum? Checksum is a function available in sql server 2005 and sql server 2008. It is intended to build a hash index based on an expression or column list. Determining if two rows or expressions are equal can be a difficult and resource intensive process. This can be the case where the update was conditional based on all of the columns being equal or not for a specific row. Without checksums it is a long process of using innerjoins to identify the exact row to u...
There are three ways to find who is connected to a database in sqlserver. First one: Use the SQL SERVER Management Studio -- Management -- Activity Monitor This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc. Second One : Use the built in stored procedure called sp_who2 Run the command exec sp_who2 This gives a list of users, database names, status, command, program with which they...
I have attended a cloud camp that was held in Auckland on Feb 26th 2010. Here are some points sent by Ben the Cloud camp organiser. Here's some post-CloudCamp reflections and some posts from different people about the event. I posted a roundup here giving my thoughts on the event. Thanks to all our sponsors who made the event possible - Gen-i, enStratus, Microsoft, salesforce, vmware, Intergen, the University of Auckland and Zendesk. Thanks also to Anne Bilek for doing a great job of helping to ...
Today I have attended the webinar by Brad McGehee titled 'How to become an Exceptional DBA' which was an inspirational talk about why and how you need to be exceptional. Characteristics of an Exceptional DBA : Enjoy Technology, challenges, problem solving, eye for detail and embrace change, trustworthy, accept responsibility, maintain professionalism, dependable, manage time well, be patient, good listener and most of all self confident. Honing your skills Having a 4 year degree differentiates y...
Today I have attended the free webinar on T-SQL for SLQ Server from Pragmatic Works. This was a fantastic session by Patrick LeBlanc. Even though I had some experience in T-SQL I have learnt a few new things mostly the new features of SQL SERVER 2008. The recorded webinar can be viewed from the resources page and click on View Recording at the relevant webinar. Interesting things I have learnt from this webinar are as follows: If you don't want to type the table names you can drag a table into t...