Geeks With Blogs
DevJef's Mumbo-Jumbo «There's a bit of SQL in all of us» October 2011 Entries
Create XMLA file for OLAP Cubes
In order to refresh your OLAP Cubes (SQL Server Analysis Services), you need to create XMLA files. These XMLA files can be used in a SQL Server Agent Job, so that the Cubes are refreshed and re-processed. To accomplish this, you need to follow the next steps: 1) Deploy your Cube to your SQL Server 2) Right click on the database, and choose “Script database as” –> “Alter To” 3) Add the following node to the top of the XML: <Batch xmlns="http://schemas.micro... ......

Posted On Tuesday, October 25, 2011 2:13 PM

Count records in all database tables
Every ones in a while you want to know the record count of all objects in your database. If you have 10 objects or less in your database, you can choose to type out the statements yourself, but once it’s more then that it gets quite annoying. Because I needed this for a production issue (make sure that all objects contain records after import) I wrote a cursor to query all the database objects, and count the content of it: USE Sandbox --=========================... ......

Posted On Friday, October 21, 2011 1:28 PM

SQL Server source control
For (almost) all developers, there’s no greater feeling, then knowing your source code is (relatively) safe. The same counts for T-SQL script. Especially if you know that you need the same code next year (argh). So first thing I did after the initial release of scripts, was looking for a way to check-in T-SQL from SQL Server Management Studio (SSMS). So a colleague of mine, Dries (Blog | Twitter), dropped by with a solution: TFS MSSCCI Provider 2010 from the Visual Studio Gallery. It’s a free plugin ......

Posted On Thursday, October 20, 2011 10:30 AM

Extended properties of database objects
When you work with SQL Server, you regularly want to know when an object is last executed, last modified, etc.. Well, at least I do! In most cases you need to use the sys.objects to obtain this information, join it with other sys tables, search for your information, and so on. But there is an easier way. The script below will get you the latest information of any object in your database. USE SandBox SELECT DB_NAME(Txt.dbid) AS DatabaseName, OBJECT_NAME(Txt.objectid, Txt.dbid) AS ProcedureName, Objects.create_date ......

Posted On Wednesday, October 19, 2011 8:59 AM

Query order of execution
If you write a SQL query, it’s important to know when certain pieces of you query are executed. For example, it’s possible that some statement in your query interferes with another part of your query, because of the execution time of a specific piece. The order of execution is: 1. FROM Clause 2. JOIN / APPY / PIVOT / UNPIVOT Clause 3. WHERE Clause 4. GROUP BY Clause 5. CUBE / ROLLUP Clause 6. HAVING Clause 5. SELECT Clause 6. DISTINCT Clause 7. TOP Clause 8. ORDER BY Clause So in many cases this ......

Posted On Saturday, October 15, 2011 9:36 PM

SQL Server Temp Tables
In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables. In this blog I will try to explain the differences, and tell you how to use it. Local Temp Tables The local temp table is the most commonly used temp table. This temp tables is created with the following statement: CREATE TABLE #TempTable (ID INT IDENTITY(1,1) NOT NULL, Description VARCHAR(10) NULL) The table that the script above created is a temporary table that is stored on disk. To be ......

Posted On Saturday, October 8, 2011 4:16 PM

Copyright © DevJef | Powered by: