SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Byte Me: Data Mining

Data Mining

Bob Lang from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

ADVERTISEMENT
SQL Developer Bundle

12 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

SQL Backup questions too shy to ask

Got backup questions? Don’t be shy!

Read Grant Fritchey’s SQL Server Backup Questions We Were Too Shy To Ask and get the answers to some of life’s big questions like, "How do I retrieve a table from the log?" and, "Are SAN backups enough?" Download the full article PDF.

sqldatagenerator

Generate realistic test data, fast

“In less than the time it took me to get my coffee, I had a database with 2 million rows of data for each of 10 tables.” Stephanie Beach, QA Manager. Try SQL Data Generator now.

Featured Contents

 

A genuine use for a SQL CLR Aggregate

David Poole from SQLServerCentral.com

When should you use a SQL CLR Aggregate? Lots of people have struggled with this one, but David Poole found a use, and has some interesting performance data analysis as well. More »


 

Free eBook: Understanding SQL Server Concurrency

Press Release from Red-Gate

When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do. More »


 

Fix SQL Server Log Shipping After a New Database File has been Added

Additional Articles from MSSQLTips.com

The code in this tip will demonstrate how to continue SQL Server Log Shipping after a file has been added to the primary SQL Server database. More »


 

GenesisOne T-SQL Unscrambler

Press Release

This industry-first T-SQL source code unscrambler automatically reads the details hidden in T-SQL stored procedures without altering the source code. With this granular information, it generates organized and precise data flow diagrams along with plain-language descriptions of each programming step within any database object. Resulting documentation can be generated in PDF, Word or HTML. More »


 

From the SQLServerCentral Blogs - Just use Version Control

Steve Jones from SQLServerCentral Blogs

There are free systems out there. If you have no budget, and want to get started, download one of these: Subversion:... More »


 

From the SQLServerCentral Blogs - Master Data Services for the Beginner Part 03

Stefaans from SQLServerCentral.com

  Introduction In yesterday’s discussion we looked at creating entities and their related attributes. We learnt that a ‘model’ could be very... More »

Question of the Day

Today's Question (by Steven Neumersky):

A week of Analysis Services

True or False: DAX is the only query language supported by the Analysis Services Tabular Model.

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Querying.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Create database objects
  • Work with data
  • Modify data
  • Troubleshoot and optimize queries

You also get an exam discount voucher—making this book an exceptional value and a great career investment.

Yesterday's Question of the Day

Yesterday's Question (by Steven Neumersky):

A week of Analysis Services

You have deployed a cube successfully, but your users tell you "it does not make sense to roll up a certain attribute hierarchy in the currency dimension to "ALL". What property must you change in order to disable that attribute's "ALL" level while keeping the rest of its attribute hierarchy intact?

Answer: IsAggregatable set to False on attribute at the top of the hierarchy

Explanation:

The presence of an (All) level in an attribute hierarchy depends on the IsAggregatable property setting for the attribute and the presence of an (All) level in a user-defined hierarchy depends on the IsAggregatable property of the attribute at the top-most level of user-defined hierarchy.

Setting the AttributeHierarchyEnabled to "False" would indeed remove the ALL level, but it would remove the whole entire attribute hierarchy. Had this attribute been used in a natural hierarchy in the same dimension and become redundant and confusing as its own attribute hierarchy, then setting the AttributeHierarchyEnabled to "False" would have been the most appropriate move.

Ref: http://msdn.microsoft.com/en-us/library/ms174497.aspx


» Discuss this question and answer on the forums

Featured Script

NameSQLinConfioIgnite

Thomas Keller from SQLServerCentral.com

This will name Ignite SQL hashes that have not been named yet, based on the object (or batch) they are within.
Runs on MS-SQL2005+ (uses VARCHAR(MAX) to concatenate SQL sections).
Run this script in ignite_repository database (but if you do not, it will just error and exit without doing anything).
It works best if you have a shorthand code for the object (or batch), with no spaces in it.
Place a comment like this near the beginning of the batch (at the end of the CREATE line of the object):
CREATE PROCEDURE [schema].[procname] -- Ignite=ShortObjCode VersionCode
Place a comment like this within or directly after each statement: -- Ignite=ShortObjCode ShortStmtCode
The hash for the statement will then be named "ShortObjCode ShortStmtCode VersionCode" as long as
 Ignite stored the entire object script (or batch), which it might not if it was very long.
But it will work without such comments, by using the first 18 chars of the Object name.
YYMMDD and the last 4 digits of the hash are used, if necessary, to enforce uniqueness.
The last result set will be the SQL Text that still could not be named (not in an object, and no such comment).
If you are unable to inject such comments into the SQL, you may be able to see a pattern in the SQL
 which would enable you to synthesize an 18-char "object name" yourself. Search for "synthesize" in the script to see where.
The last column of both result sets is a link to the Historical Chart for that SQL, so you can check its usage.
 (That link is built assuming Ignite is running on localhost, but you can just replace localhost in the script.)

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Administration - SQL Server 2014

AlwaysOn availability groups & Distributed Transactions - I know that Microsoft has been pretty clear about distributed transactions and cross database transactions not supported in AlwaysOn availability...

Database Restore - If backup schedule scnario as below Monday- 10 PM full backup Tuesday-6AM Differential backup Tuesday-1 Pm differential backup And every one our transactional backup...


SQL Server 2014 : Development - SQL Server 2014

NTLM Authentication using SQL Server ODBC driver on Linux - Hello, I'm trying to connect to SQL server 2012/2008 using SQL Server ODBC linux driver(version 11.0.2270.0). When i connect using Windows...


SQL Server 2012 : SQL 2012 - General

Primary key to be the last row in the table. - I was just assigned to review some conversion from as/400 dds to SQL tables, but I noticed that the tables...

Msg 0, Level 11, State 0, Line 0 in SQL Server 2012 - I have received this message on two different activities. The first time was when trying to move one table to...

Can't upgrade from SQL 2008 R2 to 2012 Eval (no key yet) - While I am waiting for the company to complete the purchasing, I tried to upgrade my current SQL 2008 R2...

SSRS UAC error for non-admins - Hi, I have SSRS installed in native mode and have setup the admin access and viewer roles in the site settings....

Best Practice - Large Updates - Hi everyone We have a database with about 25 million bibliographic records, the rows have very detailed information and loads of...

Old Subscriptions in Replication Monitor - Good Day . We are running Replication between an SQL Server 2012 and an SQL Server 2008R2 server. We tried to...

Transactional Replication between SQL Server 2012 and SQL Server 2008R2 - Good Day. We are runing an SQL Server 2012 Production Server as the Publisher and a Reporting Server with SQL Server...

SSIS Destination/Source and "Testing Connection" and "Validating" - Hi All, I have a package with 1000s of tables int, many data flow tasks with Sources inserting to Destinations....

Cant figure how to pull out unique values from 3 tables - Hi All I have this code [code] SELECT f.product_name,f.image_url,f.non_working_price,f.description,f.price,f.deeplink, m.product_name,m.image_url,m.non_working_price,m.description,m.price,m.deeplink,c.companylogo from fonehub f, MobilePhoneCompany m , company_logos c where f.product_name = 'Nokia C7' and m.product_name = 'Nokia C7' and c.program_id...

Troubleshoot sp that hangs - I have a fairly simple procedure with 4 if statments and code for each. When I run the procedure in a...

Database Mail limitations in SQL Server 2012 - Hello, I have a problem with my client, who want to passe from Lotus Notes App to SQL Server/.NET App and...

Failover cluster and global groups - I'm working through my first failover cluster install for SQL 2012, previously you had to put the service accounts into...

Linked server - I define a connected server and got the following error: The thing name '[OXFORD\BA].master.dbo.Mirroring.Config' includes more than the maximum number of...

Searching all tables for a value, return table name - Hi guys, I am wanting to search all tables in my database for a value & then return the name of the...

Never Worked with SQL, but am interested in earning an MCSA in SQL Server 2012 - Hi, I have never worked with SQL before. I have been exposed to Access databases and can set up basic databases...

Always On: Backup on Secondary question - Hello all- I was hoping someone can shed some knowledge on Always On for me when it comes to back up...

AlwaysOn sometimes becomes out of sync - Hi all, We have a AlwaysOn availability group that is serving a SCOM 2012 installation. We have just moved the VMs...

Value of @@DBTS / timestamp column - Hi, I have a question regarding the timestamp type in SQL Server. As far as I know the timestamp column holds...

SSIS 2012 Project Deployment version control for multiple environments - Question: How do we manage the different versions of the SSIS project codebase for the different environments in the IS Server...


SQL Server 2012 : SQL Server 2012 - T-SQL

Bad performance when using parameterized query with like in where clause. - Hi all, from MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this: where...

Exclude NULL values from select statement - Hi, Can anyone help me with this problem? I wrote a select statement, I only want to see orders with max...

sql case when statement - Hi below is the scenario which I have currently in my query. I need to write this query without any hardoce...

INSERT..SELECT - CPU single core bottleneck - Dear All, I'm using INSERT..SELECT to gather data from a complex query into a holding table, this happens right at the...

Get list of all workers having same SET of Rates - Hi All, I have attached some test data for you that has two temp tables "#worker" and "#worker_rate". The issue...

using a user defined function in a view - need help - Hi, I have a function that accespts a string and a delimeter returns the results in a temp table. I am...

Querying a Supersession Two Column Table with Multiple Supersessions in both Columns - Hi Guys, I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server....


SQL Server 2008 : SQL Server 2008 - General

Need case expression for sql select statement - Hello, how can I write a sql select statement using case I have a column called type id which contains id's of...

Suitable SQL Server Management tool - Hi First time poster on this forum so I apologise if there is already a topic about this elsewhere or even...

How to decompress internal tables? - Hi All, I use tfs, and there was necessity to downgrade tfs' sql server Enterprise edition to standard edition. Before to downgrade,...

Does LiteSpeed works good for SQL 2012 - Can you advice whether to go for Litespeed. I've >1TB databases setup on SQL 2012.

Regarding Audits in sql server - Hi All, I'm using Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation...

linked server from SQL 2008 to SSAS 2012 - hi, I need to creat a linked server from SQL 2008 to SSAS 2012 SSAS SERVICE IS running under [b]"NT Service\MSSQLServerOLAPService"[/b] CREDENTIAL SQL...

Using the informations in the database ReportServer - Hello, I just begin to use Reporting Services. I have created a report on the view ExecutionLog2 and ExecutionLog3 for having...

high cpu utilization - Hi We just had higher than normal cpu utilization for approx 1 hour. Cpu normally runs between 20 - 30% capacity...

DBCC CheckTable repair_allow_data_loss - Hi everyone. After some advice. I have a database reporting 35 consistency errors in a table. I want to test the repair process...

SQL case when statement( without hardcode values) - Hi below is the scenario which I have currently in my query. I need to write this query without any hardoce...

Improper data while importing from excel to sql - Hi, Attached Herewith is the test file,which has a column test, I want to import this file in sql 2008 r2. But...

Investigating MDF file growth. - I have an alert that checks sys.master_files.size. A database grew 20gigs in less than 24 hours. I verified the mdf...

Recompile error too long to get useful error information - Hi, y'all I'm using the following recompile script. I wish I could give credit to the originator of the code but...

Copy DB from QA to Prod - I just implemented TDE on one of the DB in our Prod environment, will this cause any issue if I...

SQL Job reports strange results - I've created the following script to report on the status on SQL logins: [code] EXEC msdb.dbo.sp_send_dbmail @recipients= N'XXXX', @subject = 'ED - SQL_Logins_Report', @profile_name = 'XXXX', @query = 'SELECT @@SERVERNAME SELECT...

Processor Affinity - Why does this happen? - I've been battling away with a very badly designed application which is causing some issues on one of my SQL...

Write a conditional query - Dear, I require to write a single query that if I pass a customer id, it will retrieve that customer's information....

Change Tracking - Hi, I have Change tracking set on two databases, required for synchronization. The retention period is set to two days with auto-cleanup...

Failed to connect to server 2008. timeout expired - I am trying to login in sql server 2008 through sql server browser,for this i am using ip address of...

Sorting large volume of data - This question is regarding how to sort a very large volume of data using SQL Server 2008 R2. I have...

getDate(), each database is for different timezones - As part of database cosolidation, I have 4 databases that were hosted on 4 different servers. Each database is for a...

last 6 months data - Hi, Below is my table structure, Table Name : MemberVisit Columns: Id,Visiteddate,PutchaseAmount sample data: [code="sql"] select * from( select 1 as Id,'2014-01-13' as Visiteddate,20 as PutchaseAmount union all select 1 as...

"Parent tag ID is not among the open tags" error message - Hi all, I try to write a query generating XML code for the first time and face this error "Parent tag...

Change column from NVARCHAR to VARCHAR... - Hi, We have a SW that is made in VB6 so it doesn't support UNICODE, so I want to change all...

SSMS 2008 Export/Import of Aliases and Registered Servers can fail - Every Microsoft DBA either has to build their support workstations from time-to-time, or perhaps help a new member of a...

Load excel data using SSIS- unicode error - Trying to load excel data to empty destination table using ssis. data extracted from table has exactly similar data types as...


SQL Server 2008 : T-SQL (SS2K8)

Testing multiple columns in a table - Afternoon all, I need to be able to test columns in a table to see if they contain the correct data,...

Separate two Values - Hello Everyone I am working with some odd data in the fact that someone placed two values in a single column,...

SQL case when statement - Hi below is the scenario which I have currently in my query. I need to write this query without any hardoce...

Need help on SQL script - Hello, I am learning SQL and wondering if someone can help me with script below – not sure if I am...

Is there a better query criteria? - Hello, I would like to know if there is a better way to do the following: [code="sql"]DECLARE @TempTab TABLE (PhoneNum VARCHAR(10)); INSERT INTO...

COMPARE COLUMNS OF TWO TABLES AND POPULATE THE FLAG COLUMNS - i have TABLEA and TABLEB Table A: [code="sql"] Create table TableA (ID INT , A1 DECIMAL(6,5) , A2 DECIMAL(6,5) , A3 DECIMAL(6,5) , A4 DECIMAL(10,9) ...


SQL Server 2008 : Working with Oracle

SSIS Package SQL to Oracle - Hello, I am importing data from SQL server into Oracle Database and it's taking too much time. I tried both Native...


SQL Server 2008 : SQL Server Newbies

Remove special characters from String - Hi, When I run a select statement the output I get is: ;#Bank;# ;#Finance;# ;#Cash;# How to remove ;# from the data? Thanks for the help

Linq To Sql, how to show picture from image field by drag - dropping - Hi, all: I used Ling To Sql, OR Designer to drag-drop Employees table from Northwind.mdf and showed all the fields on...

need to update 2nd table if data exists in table 1 - Hi everyone, I've 2 tables and both have a common field. What I'm after is to search table 1 and...

FINDING THE DATE WHICH IS NEAREST TO CURRENT DATE - i need to find the date from a column , which is less than and is nearest to the current date...


SQL Server 2008 : SQL Server 2008 High Availability

SQL SERVER 2008 R2 transaction log shipping - I have setup transaction log shipping in dev and QA successfully. But, on Prod log shipping everything working fine, the backup...

SP upgrade for SQL 2005 active/passive cluster - Can you share your experience for SP upgrade for SQL 2005 active/passive cluster. I have read that for sql 2005 you...

Convert Drive Letters to Mount Points in SQL Cluster - Hi Techies, I need some really urgent help help here. We use a single drive letter and use 4 mount points...


SQL Server 2008 : SQL Server 2008 Administration

Query to find blocking is getting blocked. - I have this query to find blocking: [code="sql"] SELECT DISTINCT sp.spid SPID, sp.blocked BlockingSPID, DB_NAME(tl.resource_database_id) DatabaseName, es.Status , (SELECT Text FROM sys.dm_exec_sql_text(sp.sql_handle)) SQLText ,...

Failed install - 2008 Standard - Hey, As title. Failed during an install of only Client Tools Connectivity and Management Studio Complete. Can't uninstall as it says it...

reports from database - HI, Is there any feasibility to take hourly data capture in the form of reports from database wherein we can get...

Sleeping processes - I have a sql cluster and it has about 500 sleeping spid, what should I do? Thank in advance.

CheckPoint - I have a SPID 18 doing checkpoing for a user database since last server reboot. There is no related error...

CLR assembly DLL file location? Corrupted - CLR file location. Is it C:\windows\Framework\version? Frequently facing problems SSIS - backup package dll files are corrupted and maintenance plan not taken automatically...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Query running slow in Production and fast in Test environment - Hi, We have SQL 2008 R2 servers in our AWS cloud instances. Both environments are in cloud and have same configuration...


SQL Server 2005 : Business Intelligence

Dynamic column mapping in ssis - Good Day, I am new to ssis, and working on archiving data from Teradata to sql, and I am having problems...

First/Last difference using MDX - Hi, I have a report that shows three lines for a group. The first line contains the first or initial values,...

SSAS/MDX Calculated member - Excel Mode function - Hello, I need some help. I am quite new to MDX; I've created a cube (SQL Server 2012)and the measure needs...

Using Statistical Functions to build Calculated Members - Ok I have a measure defined in my cube. It's a sum measure on a value from the table called loadtime....

Creating a Solution File from an Existing SSAS Cube on the Server - We have an internal cube we've developed where, over time, the solution file has gone out of date with the...


SQL Server 2005 : SQL Server 2005 General Discussion

NT AUTHORITY\IUSR user issue - I am trying to finding the user IUSER after looking at the following article. Login failed for user 'NT AUTHORITY\IUSR' If...


SQL Server 2005 : SQL Server 2005 Performance Tuning

tsql NOT using INDEX when parameter value in use - This is my first post so HELLO to everyone! Hi All, I am working on a sql 2005 Enterprise Edition DB using...


SQL Server 2005 : SQL Server 2005 Integration Services

Pivot transform not executing - Hi Experts, Am facing problem when trying to execute Pivot transform in BIDS 2008. The error is : [Pivot [25]] Error: The pivot...

How to Extract data from Excel 2003(password protected) - Hi Guys, I want to read specific cells from excel(.xls) file and then export it to SQL SERVER. But the problem...


SQL Server 2005 : T-SQL (SS2K5)

Difficulties to use sp_send_dbmail with @@SERVERNAME - Hi everyone, I have some trouble with the sp_send_dbmail procedure. I use the following code : [CODE="other"] declare @Body varchar(500) set @body = 'my text for...


Reporting Services : Reporting Services

need expression for the field if there is no data - hello, how can I write an expression in the field which is a dollar amount value if there is a data...

SSRS Expressions - Hi All, I have a field that contains some names and would want to lift the name part that I want...


Reporting Services : Reporting Services 2008 Development

Count - I am trying to write an expression in report builder to count all device name that are duplicates or >1 Can...


Data Warehousing : Integration Services

Excel connection manager woes - what are my other options for creating and using saved connection managers - I have some code which will loop through a directory, and operate on all the Excel files in there for...

30 Columns From FLAT file + 1 Column from Table??? - New to SSIS have done mostly Import Export wizard jobs with small modifications. I have a Flat File Source that has...

Deploy package to multiple servers - Hi, we currently have several servers running SSIS packages building tables for a data warehouse. These packages were created once and...

Implementation of regular expressions - I have a project where the schema is the report. IOW, its got Addr1, Addr2, Addr3, Phone1, Phone2...you get the idea. This...


Data Warehousing : Strategies and Ideas

Fact table for each KPI - Still new to BI design methologie. When im building my facts tables is it generally accepted to to have one fact...


Data Warehousing : Analysis Services

DAX Puzzle - I think I know the answer but would welcome any thoughts. I'm using the Tabular BISM with Excel Pivot as...


SQLServerCentral.com : Anything that is NOT about SQL!

Vulnerable to SQL Injection third-party API - Not sure where this post should go. . . I have a third party API that I pass data from my database...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...


SQLServerCentral.com : Articles Requested

SQL 2012 Paging - A piece that shows how you can page sets of results, say 5 at a time from a table for...


SQL Server 7,2000 : General

Case select sql 2000 - Good day, Is it possible to do this on a case select on sql query. Thanks ALTER PROC DoubleSidedSheets @StartDate datetime , @EndDate...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com