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

Stairway to MDX - Level 2: The Ordinal Function

Bill_Pearson from SQLServerCentral.com

Business Intelligence Architect Bill Pearson introduces the MDX Ordinal Function, as a means for generating lists and for conditionally presenting calculations. He also demonstrates the use of the function in creating datasets to support report parameter picklists. More »

SQL in Boston -- Red Gate Style

Additional Articles

Adam Machanic is speaking at SQL in the City - Boston on Oct 8, 2012. More »

Solving the SQL Server Multiple Cascade Path Issue with a Trigger

Additional Articles from MSSQLTips.com

This tip will look at how you can use triggers to replace the functionality you get from the ON DELETE CASCADE option of a foreign key constraint. More »

From the SQLServerCentral Blogs - Resources for Those Dealing with Security Patches

Brian Kelley from SQLServerCentral.com

It's been a while since I was in the day-to-day business of security patches. However, I still keep up with... More »

From the SQLServerCentral Blogs - Paying it Forward

Angela Henry from SQLServerCentral.com

My company has recently put forth an effort to hire fresh college graduates and interns.  I think this is a... More »

Whether you write code, or administer server instances, chances are that you have a decent amount of maintenance to do. This might be fixing a bug in code, whether yours or someone else's. It might be reindexing tables or even running periodic maintenance routines that can't easily be automated. There are any number of tasks that might require an administrator's attention, but are essentially the equivalent of the tedious activities that are involved with existing items as opposed to new creations.

I ran across this piece on Ars Technica, and I'll admit it sounded silly at first, but the more I thought about it, the more I thought that 90% of time spent on maintenance of some sort, and 10% spent on new items might be accurate. After all, it seems there's not shortage of work being done to tackle bugs, tune code, or enhance a feature that wasn't quite right the first time. The more I think about my career, the more I think that I've spent more time fixing things than actually working on new code.

If you think about your workload, do you fix things or create new ones most of the time?

Give us a rough idea of how your coding time is spent. Are you more of a craftsman that develops new widgets for your clients to use, or are you the handyman, repairing and improving things that weren't built well enough the first time.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 19.2MB WMV
• Watch the iPod Video Podcast - 15.4MB MP4
• Listen to the MP3 Audio Podcast - 3.2MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

What is true about catalog views? (select 2 options)

By Lokesh Vij

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

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

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

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.

What would be the result of the three given queries? 

-- Query # 1
INSERT INTO @Exceptions VALUES (0),(1),(2),...,(1000)
-- Query # 2
INSERT INTO #Exceptions VALUES (0),(1),(2),...,(8060)
-- Query # 3
INSERT INTO Exceptions VALUES (0),(1),(2),...,(65365)

S : Query ran successfully

E : Error in Query , Maximum Number of Row Value Expressions reached

(Note: Please consider "..." as continuation of the inserts in the same format, with sequential numbers)

By demonfox

Answer: E,E,E

Explanation: The number of row value expressions in the INSERT statement is maximum allowed till 1000 row values. All three queries tries to insert more than 1000 records.

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

» Discuss this question and answer on the forums

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports

This is the industry’s most comprehensive and useful guide to SQL Server 2008 and 2008 R2. It presents start-to-finish coverage of SQL Server’s core database server and management capabilities, plus complete introductions to Integration, Reporting, and Analysis Services, application development, and much more. Grab your copy today from Amazon!

Get dependant Objects

abmore

Get Dependant Objects by passing table name or stored procedure name. More »

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

SQL Server 2005 : Administering

Linked server to Sybase from SQL2008R2 - Hi, Does anybody know how can I create a linked server to Sybase from SQL008R2? I don't even see the provider, which...

Database Archival Procedure in SQL 2005 - Hi, What is the best archiving technics followed in SQL Server 2005. Please let me know any step by step...

secure ftp from sql server 2005 - Hi all, Is there a way that can do secure ftp using sql server 2005? I need to secure ftp to...

Is using Maintenance plans a good idea for this? - Good morning, We have 5 SQL Servers used by operations team. QBDB01 - 62 Databases QBDB02 - 41 Databases QBDB03 - 39 Databases QBDB04 - 31 Databases QBDB05 - 28 Databases The...

Database to Database migration tools? - I have a user wanting to transfer data from one database to another within MSSQL 2008 R2. They are wanting...

Data archive techniques - Please share your experiences on archiving older data from tables that have grown very large. We've looked at several approaches including...

How to stop other application accessing SQL port 1433 - We are planning to do some maintanance on SS 2008 and we DO NOT want any users / application accessing the...

Agent not showing in SSMS - I have remote server. Checked it this morning and I can't see SQL Agent in SSMS. I have stopped and restarted...

Killed Process still rolling back - So the night shift guy was running a process that had a problem, so he killed it through Activity Monitor....

Database Mirroring Transport is disabled when setting up Database Mirroring - Hi, I am trying to setup database mirroring on the RTM version of SQL Server 2005 but I hit an error...

SQL Server 2005 : Business Intelligence

Versioning in SSIS - I know DTS packages do we have versioning, just wondering whether versioning feature is still available in SSIS??

SSAS Error - All- I get the following error when trying to deploy my SSAS cubes- "Either the user does not have permission to alter...

SQL Server 2005 : SQL Server 2005 General Discussion

Insufficient permissions to access Reporting in Management Studio & web reports - Hi all, I've just added SQL Reporting Services to our SQL 2005 server, but cannot access the web interface (http://localhost/Reports) or...

Single sql column defined as 2 separate indexes-unique and non unique? - HI, I have a database that was set up by a vendor application. I have discovered at least 10 occurrences...

SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy...

Help! Problems connecting to SQL 2005 from Delphi - I have a system written in Delphi. We recently ported it from using BDE to ADO. I have an installation...

SQL Server 2005 : SQL Server 2005 Security

Object does not exist error is getting if VIEW DEFINITION denied - I have denied VIEW DEFINITION permission of all the objects of a database for a particular user. But he is...

SQL Server 2005 : SS2K5 Replication

Monitor Replication Using Scripts - Does anyone have any scripts that can give the data that replication monitor gives us. You help here would be...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Metrics for stored procedure execution - Hi All, We have a SQL server 2005 database with 2000 stored procedure and would to perform the performance tuning for...

high index fragmentation after deleting many rows - I recently had to delete around 22 million rows out of 30 million rows in a table. How I went...

SQL Server 2005 : SQL Server 2005 Integration Services

Dynamic OLE DB Source - Hello SSIS people... I have an issue I'd like to pose to the group, as I cannot think of a...

"not have right permissions on connection" error when transactionsoption is "required" - Dear all, I have a simple SSIS package, which contains only one "Execute SQL Tasks" task. Inside that task,there are...

Number/Letter column issue - I have a column in a Excel Spreadsheet with a code in it that is either a single digit number...

Want my SSISpackage/ SQL Job should fail if the SSIS Config file path is wrong - I have one SSIS package. In the package I configured the config file, and it is enabled. I am calling...

error 0x80040E21 "Multiple-step OLE DB operation generated errors...." - I've used the Import / Export wizard to dump a tables from a SQL 2005 db to a series of Excel...

SQL Server 2005 : T-SQL (SS2K5)

how to remove zero from my string - hi , I have two strings like ex:1.'000temp1' 2.'0000temp2'..like how can i remove the zeros from my string..

soundex function - What exactly and on what basis does the soundex function returns the integer values. I read through msdn but it only...

Finding client IP-Address - Need a query that returns the client ip-address of who is updating/accessing the database. I am using sql server 2005...

SQL Server 7,2000 : General

update using a wildcard - is it possible to update values based on a wildcard? For instance, I have many dates in the database that...

SQL Server 7,2000 : T-SQL

Batch file to execute SQL - I need to run a set of scripts on a database. How do i create a batch file to execute...

SQL Server 7,2000 : SQL Server Agent

How can i make the data transformation in DTS to do an update instead of insert? - Hi guys, this is the dilemma im in now. i have to create a DTS package that instead of doing the...

Agent Job failing with FTP task In SSIS package - Good afternoon everyone I have a SSIS job that uploads file to FTP that works fine via SSIS. I have the protection...

SQL Server 2008 : SQL Server 2008 - General

SQL Agent Job crashing DB2 Linked Server intermittently - Hi All Hoping you can shed some light on this. I have an agent job which runs every night copying some...

How to check more then one row for null by a value passed in - Hi all, I have a stored procedure that returns all records that match a certain criteria, i only want to show...

PATINDEX to split a column into multiple columns - Hi, I am trying to split a column into multiple columns in sql 2008. This is what I have: CREATE TABLE ints (interests...

Update Statement Query - Hi I would like to knw what is the differnce between executing both the update statement. when i execute the first...

Why there is no SQLServer:BufferManager performance counter in windows performance monitor after installing sqlserver? - Hi all, there is one strange problem. I have installed the sqlserver 2008 R2 on one windows 2008 server system, but I...

Facing problem in SQL Server 2008 installation on Windows 7 Ultimate - Hello, I am installing SQL server 2008 on my machine which have 500Gb HDD,4 GB Ram, I5 3rd gen processor and...

When to drop temp table? - Hi friends, Some time back we have discussed about dropping temp table, but here I have another question: the scenario is,...

Potential risks in migrating SQL 2005 to 2008 - Hi, We are in the process to upgrade SQL server from 2005 to 2008. What are the potential risks I can...

How can I pass table row as a in parameter to stored proc - Hi , I have incidents number of 7 character long and which are storing in table as below 1880992 2427572 1680575 2267755 2504343 2476359 2003648 1941562 2038118 1847247 304757 111610 495817 1482745 1822203 3396649 1475775 Is it possible...

Balancing database loads across multiple servers - Hi all. I'm wrestling with a planning issue that I'd like some advice about. We are going to move the contents...

Summing up while creating table? - Hi all, When I import data using the import and export wizard, I use a create table SQL Statement. I am...

Avoid characters that viloate UTF-8 Encoding - I'm storing information in an XML file that is UTF-8. I have a stored procedure I use to query some tables...

How to catch a query from an another database? - Hello! How to catch a query from an another database (example from profiler)? [code="sql"] -- Example: USE [master] -- or another database GO -- How to catch...

Send email with HTML format - Hi, I've created numerous emails which send the results, tabulated in the body of the email, it looks good. However...

Need Create View Script for all tables - I manage a SQL 2008 R2 Production environment for an application. We have a new requirement to create views( simple--select...

SSIS create text file and add text - Hello everyone... I have an SSIS package that copies a template file before it extracts data from the database. Somehow my...

high reads - i have a proc who on occasion it's execution plan goes to hell and it uses 11 million reads to...

BulkImport Problem - I have Unicode Data file which is Fixed Length Following Table Structure with Create Statement. sample Data file and format file i...

Return datediff as X Years Y Months internationally - Hi, What I'm looking to do is have an ininternationalized function that returns datediff between 2 dates formatted as x Years...

Grouping Measures from different measure groups in SSAS - Hi, this is my first post here - but you forum has helped my many times regarding various issues with SSAS. My problem...

Space used by Temp table - Hi All, I know that there are two types of temp table local and Global ( singe and double # ) , and I know...

SQL Server 2008 : T-SQL (SS2K8)

How to delete data from Product table by ProductID in AdventureWorks - I want to create a procedure to delete Production.Product table by ProductID in AdvetureWorks Database. How to do it? Because Table...

need sloution for current date in a job - My job starts at 8:00pm of currrent day to 3:00am of next day. This job has serveral steps for each step i...

coverting lower to upper case program in sql server 2000 - declare type v_arr is varray(10) of varchar empy.ename%type x_arr v_arr=v_arr() begin x_arr.extend x_arr(1)='hello' for i in 1..x_arr loop if(x_arr<=97 and x_arr>=122) then x_arr=x_arr-32 end if end loop end expecting output: HELLO but the...

subquery - hi can we user order by inside subquery?

bcp with xml insert - Hi all I am trying to insert a flat text fixed width in to SQL 2008r2. I have tried but...

depend objects - hi, how to find number of objects your stored procedure depend.

INSERT into Target table from Source table, redirecting duplicate key rows into ERROR table - 1) Source tables are heaps 2) Target tables are identical except with primary key columns. They often have data in them...

comparison in the merge statement about null values - I use the merge statement in a sproc to insert, update and delete records from a staging table to a...

calculate trend of a column - I have a temp table where Iam inserting data from original table temp_id, temp_test_id, temp_status 1 2 failed 2 2 failed 3 2 passed 4...

Extract string between delimiters - HI I want to extract string between the delimters where data is as follows: abc_def_cet_qwe output required in only def , how...

passing more than one value to a SP - I need to execute this stored procedure. Problem is that I need to pass in more than one value. At...

Will indexed view help this query? - Hi I have a fact table with around 100 million rows. Below is my query to pull top 10 records. ( i...

Cannot get Left JOIN to work correctly - I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner...

evaluating and combining values from two rows - I am running into a bit of trouble with the query below, would be great If you could offer some...

SQL Server 2008 : SQL Server Newbies

Insert data from unnormalized table into 3 new tables - I apologize in advance if I'm not posting correctly.... I need to migrate 1 unnormalized table (TempLegacy) into 3 tables: TempContacts,...

table size growth report - Hi all, I am new to sql server. I need to log table sizes to report on growth. Here is...

Query performance? - Gurus, Need help with Query performance question. There are two dataregions in the report body. one tablix and one chart Tablix is just a...

SQL Dimensions & Fact Tables - Hello, I am in the process of creating a datawarehouse database on our SQL Server, I have used SSIS to dump...

Please give query for below output - Hi All, My output looks like this 100 200 300 400 500 But I want to convert above output to '100,200,300,400,500,' For this how to write the query??? Regards, VenkiDesai.

SSIS 2008r2 - In the advanced editor for the OLE DB Destination I am adding columns to "External Columns". Once they are added...

SQL 2008 'Cannot Add Domain Users' - I currently am creating an infrastructure with 3 VPC's; DNS, SQL 2008 and an Application server. I am no way...

SQL Server 2008 : Security (SS2K8)

Preventing SQL server instance scanning osql -L /sqlcmd -L - Hi , I have create a SQL SERVER 2008 R2 - SQL EXPRESS instance. and Disabled the Browser services and Enabled the...

SQL Server 2008 : SQL Server 2008 High Availability

VMware or Microsoft Cluster Solution - Very simple question. I used to work for a big, big company. But changed job recently and on my new role,...

Replication problem - Hello, We are replicating few databases (which are identical in structures) in SQL Server 2008 R2 with transactional replication. The replication...

How do i connect cluster instance?? - Hi All, I am configured SQL Cluster 2008 in Win server 2008, below are the details.. NODE1 IP :- 192.168.10.51 NODE2 IP :- 192.168.10.52 SQL...

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding - Hi, We have log shipping configured in SQL Server 2008 R2 SP2. Log shipping copy job is failing and I'm getting the...

Replication OS error 5 - Dear all, I am doing some experiments with SQL server replication. Basically I have the following setup with two sql servers Server1:...

SQL Server 2008 : SQL Server 2008 Administration

sql server 2008r2 sp2_cu1 upgrade error - hi, when i upgrade our dev sql server to sql server 2008r2 sp2-cu1 i am getting installation failure error but version...

SQL 2008 Database Move To 2008 R2? - Hello - I have an application that is currently on a SQL Server 2008 instance I'll call instance A. That department...

Moving tempDB from RAID10 to RAID5 for OLTP? - My data files and tempDB file (single temp file) share the same RAID10 LUN and our SAN controller is saturated/overloaded....

Unable to enable FILESTREAM feature of SQL Server 2008 R2 on failover cluster - Setup: SQL Server 2008 R2 Enterprise Edition x64 (10.50.1600) Windows Server 2008 R2 Enterprise x64 When attempting to configure the filestream feature through...

Programming : General

Need a "Test" or Dummy XP Dll - I am working with a customer of mine to implement Audit alerts (from a C2 trace file) when/if someone adds...

Programming : Powershell

Get-WMIObject Win32_Volume fails on proxy account from Agent Job - It may just be a case of me missing something, but I get unexpected behaviour when running a job as...

SQLServerCentral.com : Anything that is NOT about SQL!

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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...

Reporting Services : Reporting Services

SSRS environments - This is really a DBA admin question or DBA team. We are migrating our reports from SSRS 2005 and SSRS...

Report Server (MSSQLSERVER) cannot load the SQLPDW extension - Please can anyone help with this error message: Report Server (MSSQLSERVER) cannot load the SQLPDW extension

dynamic reports at the time of prview reports in ssrs - hi friends i have small doubt in ssrs plz tell me answer i developed one report in ssrs. in that report...

How to Handle a column with Length more than 8000 characters - Hi, I have a report which consists of 40+ columns. one of the column's length is more than 8000 characters. Report is working...

Word Export Email Subscription - I have a subscription for a report for email delivery. The rpeort is calles Master. The first prob i have is that...

Database Design : Design Ideas and Questions

Avoiding the Deep model (Key, value) - I am currently working with a group of folks to design a data model. Each record the main table in...

Data Warehousing : Integration Services

Command Prompt Window Continuously Opening with Each Unzip - Hi all, I've created a System Task Editor to delete zipped files from a folder once they have been unpacked. Everything...

Data Warehousing : Strategies and Ideas

Where to put a Type 2 SCD? - This is actually a follow up to an earlier question about a Type 2 SCD. Anyway, we have decided on the...

Data Warehousing : Data Transformation Services (DTS)

Crystal Report run through DTS - Hi, I just run crystal report daily without any parameter and after run, save the file in c:\temp directory. I have...

Testing Center : Question of the Day (QOD)

Source / version control - Can anyone suggest me any (open source also)source/ version control software which can be used for storing all kinds of...