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

An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

Dwain Camps from SQLServerCentral.com

Normalizing or UNPIVOTing data may be improved by using this lesser known approach in SQL Server 2008 or later. More »

The 2012 SQLServerCentral/Exceptional DBA Awards Party at the PASS Summit

Press Release from SQLServerCentral.com

The 2012 SQLServerCentral party at the PASS Summit is on and will once again include the awards ceremony for the Exceptional DBA of 2012. Get your tickets now. More »

Issues Determining an Individual SQL Server User's Permissions

Additional Articles from MSSQLTips.com

Recently I was supporting a third party application. It queries to determine what tables it has permissions to before it proceeds with the rest of its functionality. We had implemented permissions based on the best practice of creating roles, assigning the permissions to the roles, and then making the users members of the roles. The application was querying INFORMATION_SCHEMA.TABLE_PRIVILEGES and of course didn't find any permissions directly against the user in question. We ended up granting explicit permissions to the user so the application would work, but I'm more interested in the general case. How can I determine permissions for an individual user? More »

From the SQLServerCentral Blogs - Reading Excel into SSIS via OpenXML Scripting

Reading Excel data isn't easy.  It should be, but there's a lot of unfortunate barriers in the way that I've... More »

We've had Amazon Web Services (AWS) available for some time, and used for some interesting projects that wouldn't be possible if groups had to purchase their own equipment. From password cracking to cancer research, there are some amazing possibilities. Windows Azure works as well, and there are customers such as banks, using the cloud to perform complex analysis of data. Not to be left out, Google announced recently they were provide their Google Compute Engine to anyone, without any limits on scale. You have to pay for it, but if you want 770,000 cores to solve a problem, they'll provide them if you can pay for the resources. It's US$2m/day, but it's there.

I don't know what that means for us as data professionals, but I suspect there will be any number of companies that will consider using these types of resources to work on complex analysis of problems. Running a simulation might be something that costs a few hundred dollars a day, using resources for minutes at a time. Looking over some of the different projects people have used cloud computing engines for, it seems that many businesses might find this to be a cost effective way to perform data mining or BI type workloads.

If you can get the data to the engine. I still wonder how challenging this is, apart from all the security concerns. Just moving that much data around, refreshing it, removing the stale data, could be very complex. It concerns me that as more people attempt this type of work, we will have more and more poor decisions made because of data quality, age, or accuracy. After all, garbage in, garbage out has been a tenet of data professionals for decades.

If you're using cloud resources for computation, I'd love to know about it. I know many of the data professionals out there have reservations, and that's healthy. It shouldn't blind you to the possibilities that this is a most cost effective way of actually getting analysis and answers for your users.

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 - 20.4MB WMV
• Watch the iPod Video Podcast - 19.8MB MP4
• Listen to the MP3 Audio Podcast - 4.0MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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.

You can also follow Steve Jones on Twitter:

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

Today's Question:

I execute the following two (2) T-SQL statements

Statement # 1

SELECT OrderID, OrderDate AS 'D4 between results' 
 FROM D4
 WHERE OrderDate BETWEEN '20110701' AND '20110731'

Statement # 2

SELECT OrderID, OrderDate AS 'D7 between results'
 FROM D7
 WHERE OrderDate BETWEEN '20110701' AND '20110731'

The question: How many rows are returned by each T-SQL statement

By bitbucket

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

This question is worth 1 point in this category: DateTime. 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.

SQL Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.

How many rows are returned by the query below In SQL Server 2008?

By Unknown

Answer: 0

Explanation: XML is case sensitive. In the set statement we use 'Root' but in the last select query use the upper case 'ROOT'. This means no rows are returned.

Ref: Retrieving Data from XML Sources - http://technet.microsoft.com/en-us/library/cc546512.aspx

» Discuss this question and answer on the forums

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.

Padding function

Francis Jamet from SQLServerCentral.com

Function that helps you to normalize numbers or strings. More »

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

SQL Server 2005 : Administering

how to remove/delete standy by database on secondary instance? - I was doing Log shipping and something went wrote now I want to start all over. How to delete restored...

return parent child relation - PRNT CHLD POS 1050000 1000000 1 1050000 5000000 2 1050001 1000004 1 1050001 5000002 2 .............................................. .............................................. 1000000

Prevent users from impersonating sysadmin using runas /netonly - So I just learned that some of our users are using a VM to impersonate a sysadmin and logging into...

i dont have rights to access the location of a file - Hi!! i know nothing about p.c's and i'm freaked out.. I formated my laptop (windows 7 pro.), After back up,i transferred...

Decrypting program name from sysprocesses for agent job - Hey all, Are there any known ways to decrypt the hex shown in sysprocesses on program name when the process is...

SQL Server 2005 : Business Intelligence

split table data into multiple execel sheets in ssis - how to send table data into multiple sheets in ssis. supoose table data like id ,name,deptno 1 ,jai ,100 2,ravi,360 7,ran,100 ...

ssis execel - I'm working with a set of data from SQL Server that I'd like to get into a group of Excel...

SQL Server 2005 : Data Corruption

tempdb location change - sql not coming online - We have a newly built cluster environment and there is a dedicated disk for the tempdb. Unfortunately, after the setup, I...

SQL Server 2005 : Development

How to get current week as 0 for any year using week - Hi, I need to display current week as 0 for any year in the report......using week column in week table. I have...

SQL Server 2005 : SQL Server 2005 General Discussion

out of memory - hello, we are on: Microsoft SQL Server 2005 - Ent Ed 32-bit SP4 mutltiple dbs running used for web applications On Jun-9: - SP4...

SQL Server 2005 : SQL Server 2005 Security

Restrict access to individual table - Hi there, Is there a way to restrict access to individual table in sql server 2005.? Please let me know. Thanks, Ananda

SQL Server 2005 : SS2K5 Replication

There is already an object named 'FK_OSS_KBN_PC' in the database.(Error: 14151, Severity: 18, State: 1) - Hi All, I am getting this below error and i can't figure out why this is happening.if anyone has ever encountered...

Database Mirroring - Hi Gurus, Due to some network glitch mirroring connection is disable. Is it possible establish the connection automatically once network connected...

SQL Server 2005 : SQL Server 2005 Integration Services

Problem using expression in SSIS - Hi, Below is the expression i am trying to make work : [b] substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))[/b] Whe I use the exact string(Archive_Full_20120731200002)...

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

can SSIS be part of cluster - I have read in some MS article that its not advisable to cluster SSIS. My customer has a requirement to do...

SQL Server 2005 : T-SQL (SS2K5)

Derived field help - Hi My problem is I have a view named Forecast with data in that looks like this: RowID RegisteredSpendDate FinancialYr SpendPerFinYr($) FinQtr (DD/MM/YY) 1 ...

SQL Server 2005 : SQL Server Newbies

Can anyone help me with this business requirement? - We have situations in our application where the 1st business day of September of specified year is the due date for...

Please Provide Me with Best Solution - This is an issue we had to solve for a customer a few weeks back. Imagine you had list of 10,000...

SQL Server 7,2000 : Administration

SMTP Mail - Need xpsmtp80.dll - Hi, I'm trying to set up SMTP mail, and I can't find the required DLL, all the posts I found...

SQL Server 2008 : SQL Server 2008 - General

how to avoid null or ' ' value in a select query and replace that value as 0 in a select query not in the table? - here i am having table [code="sql"] declare @test1 table( id int, val varchar(20), ser int, sa int ) insert into @test1 select 1,'rer',1,Null union all select 2,'rer1',Null,Null union...

Manually Created statistics on a table on a column but it is seen nothing - Hi All, I created some statistics on a table on column. But when i examine statistics detail, it is not created...

Stack dump occured unable to understand and find the cause - Hi All, I overserved some messeges in sql error log stating Using 'dbghelp.dll' version '4.0.5' Exception Address = 00000000009B2204 Module(sqlservr+0000000000142204) * Exception Code = c0000005...

How to display current week as 0 for each year - Hi, I have a report requirement where I need to display current week as 0 for any season year user selects....like...

RESTORE - every Saturday full back : 10 PM daily differential backup : 10AM Tlog bkup happenes at every 1 hr like 9:30 , 10:30 ,11:30, 12:30......

SQL License and cores - Hi all Does anyone know if its possible to limit the number of cores available to SQL, I know you can...

Backup and restoration - i am confused in one scenario please help every Saturday full back : 10 PM daily differential backup : 10AM every hour transaction Log backup...

Count Per Hour By User By Day - I'm trying to create two reports that would look like this; passing a date range for start/end: Report #1 Sort By...

SSIS package fails in the scheduled job- How to add Decrypt password in command line - Any Example syntax please - Hi Experts, I am trying to schedule a job with ssis package.. The error message is: SQL Server Execute Package Utility Version...

Making a live copy of another server - What would be the best way to maintain a copy of a live db on another server to let the...

Mirroring with witness end point on mirror server - hi, i have only 2 servers and also want to configure witness with that i want to configure witness end point on...

keeping dev and live databases in sync - Does anyone know of any ways to keep a dev database up-to-date with changes on a live database without doubling...

UPGRADE SQL SERVER 2000 to 2008 R2 - Plan to upgrade (side by side) v. 2000 to 2008 R2 and set compatibility level to 90 (not 100) - all...

Record linkage advice required– Long read (YOU HAVE BEEN WARNED!) - Hi, I have the following table structure: [code="sql"]create table dbo.PreMerge ( master_id int, Title varchar(20), Forename varchar(50), Surname varchar(50), CompanyName varchar(200), CompanyName2 varchar(200), Address1 varchar(200), Address2 varchar(200), Address3 varchar(200), Address4 varchar(200), Town varchar(100), County varchar(100), Postcode

Subquery returned more than 1 value. This is not permitted - I am getting the above error when running the below code. This has started happening on this bit of sql after...

How to Retrieve the image data from database table and show in SSRS - Hi 1) My Table is Create Table Image_table (id int,images image ,crdate datetime default getdate()) Insert into Image_table (id ) Select 1 UPDATE dbo.Image_table SET images...

subscriber reinitialization (Replication) - Posted this under sql server 7,2000 replication category and was wondering why I don't get any replies. Anyway here is...

Database Redesign - I have to redesign the database based on the new business requirements Could someone please help me with the steps...

Is there an joiner much like the 8kSplitter? - I'm a fan of Jeff's 8k Splitter function, works a charm, but we have an application which feeds in a...

increasing duration for the same query over time - Please excuse the double post for very similar question- I think I was on the wrong track with my previous...

SOS_SCHEDULER_YIELD waits gradually increasing over time for same query - I have a job that does the following via C sharp code: -opens a connection to sql server (keeps it open...

Need advice - good place to start learning SSAS - Any suggestions would be very appreciated. Currently I have experience with TSQL, SSIS Thanks in advance, Nikolay

50000 error executing stored process - we are queirying an stored procedure multiple times same time,from our application. In this case, few processes executing successfully and...

Excel to Database - validate and import - New website has been activated this morning [url=http://leansoftware.net/en-us/products/exceltodatabase.aspx]http://leansoftware.net/en-us/products/exceltodatabase.aspx[/url] Many thanks for all the support from SQL Server Central members over the past...

Running Total - Hi all I have a table with columns in that I want to do a running total on. I want...

Full Text Search Estimated Update Time - Hi I want to learn that when we create full text indexes in auto change tracking mode, how much time...

DTS Package Maintance Plan... - One of my DTS Package Maintance Plan is getting fail. I investigated the job owner is having full permission and...

Convert SQL Server query to Oracle query - Hi Our Oracle Developer is out for the rest of the week and i need to convert a SQL Server Query...

Default Trace returns Error (Msg 568, Level 16, State 23, Line 1) - Morning All Looking through the default trace this morning to check what time some log files increased in size to see...

Uploading a file into a website using SSIS - Hi, Can any one guide me how to upload a xml file into a website using SSIS? Is it possible...

Access to SQL Agent Jobs - I have a 100 + Jobs running on our production SQL server and these jobs are running/owned under service account which...

MS SQL + MS Visual Studio VS Wherescape RED - Have you ever heard of or used Wherescape RED to do the data warehouse developing? The fact is it is...

SQL Server 2008 : T-SQL (SS2K8)

Code behind IsNumeric() function - Hi, Please let me know, how to see the coding part of IsNumeric() function in sql server.

without [union] in Query - i have two tables [code="sql"] CREATE TABLE [dbo].[A]( [id] [int] NULL, [value] [int] NULL ) ON [PRIMARY] insert into A(id, value) values(1,1) insert into A(id, value) values(2,2) insert...

trying to add alias for value generated by Where clause - Unless I comment out the alias 'AS RunningTotal' the query fails, but I need an alias for the column the...

Populate Quarters table - Hi All, I want to populate a table that will contain start and end dates for each quarter. My definition...

Inserting Month\Year based Records if they don't exist for the next 12 months - Hi All, I'm using SQL Server 2008. I have the following problem which I could do with some help with if anyone...

How to manage Transaction Log. - Hi All, I stheir a way we can manage Transaction log even before it fills up? I have some archive tables,...

Updating Multiiple value - Suppose i have 2 table as Emp and Emp2. select empid from Emp where deptid=2 The output of the above query is empid 1 2 3 5 7 9 10 16 17 Now,Insert...

Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE - I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed...

SQL Server 2008 : Working with Oracle

Queries running long - I am in the process of upgrading several old servers to SQL Server 2008 R2 and am having a problem...

SQL Server 2008 : SQL Server Newbies

when i run my below query it is getting error like.... - DECLARE @Sample Varchar(Max) DECLARE @EXISTS INT SELECT @EXISTS=0 --SET @Sample='mymail SET @Sample='mymail@mymail.com' ;WITH SAMPLEDATA (UserName,Email) AS (Select U.UserName,M.Email From Membership as M INNER JOIN Users...

Importance Of ObjectId - I have search this topic on internet. But the way of explanation has being too far of expert level. I...

Re-entering the DBA field - Looking for Advice/Suggestions - Hello Everyone, I'm a marketing professional looking to get back into the IT field that I left 10 years ago. I...

Cannot connect to SQL Server 2008 R2 remotely - I've attempting to create a Development box that host a SQL Server 2008 R2; however, I'm unable to connect to...

Enlisted or Drafted? Share your experience. - Some people ask to be a SQL Server DBA, some are drafted or rather are a "DBA by default". I...

How often SQL Update Statistics job recommend to run - Hi, Just a quick question. Probably would hve answered earlier in this forum, but I couldnt found an answer which...

SQL Server 2008 : Security (SS2K8)

TDE Encryption - Hi, I am wondering if anyone has had this problem. I recently encrypted my sql server 2008r2 databases and after that the...

Table access deny for partcular table - Hi, I have 5 tables in the [b]test[i][/i][/b] database namelist agelist gender locationlist email timezone i have given dbowner rights to [i]harry[/i] login , has a [i]sysadmin[/i]...

SQL Server 2008 : SQL Server 2008 High Availability

Mirror-Primary Server Fails - I have SQL Server 2008 with mirroring in High Safety Mode. One question I have here I made a transaction and it...

Witness server location - Hi all We have two datacentres and I am looking to add some HA to one of our applications through mirroring....

SQL Server 2008 : SQL Server 2008 Administration

Why there is a Limitations on Index size (900 Bytes)? - Hi, Is it correct that all the pages, index/data/iam..etc pages does have the 8KB in size? if yes, why we have...

SQL Server 2008 Table Partitioning - Hi, What is the best practice when deciding to partition a table? Should I look at the row count of the...

2008 R2 SP2 Install fails with no messages - I'm installing SP2 on my 2008 R2 servers and it has gone well for the most part. However, I have...

DB Backup Maintenance Plan Won't Execute - I've created a back up plan on one DB using the Maintenance Plans Wizard and the regular way (New Maintenance...

SQL Query Performance - Hi All I want to investigate Query Performance on my SQL Instance I've been looking at the sys.dm_exec_query_stats DMV How up to date...

Index Usage - Deleted - Hi All I'm using the below script to track index usage on a particular table. Why does the leaf_delete_count show zero? I...

Alternative to DBLaunch - automated remote SQL install - Hi! I get requests for new installs every day (some on physical boxes, some VM, and 6 different domains..ugh!). We...

How to check if there services running other than sql server and sql server agent ? - How to check if there services running other than sql server and sql server agent ? thanks in advance

model database stuck on Restore - Last night my backups failed on my maintenance plan and looks like my Model database was corrupted. It was restoring...

Career : Certification

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Passed 70-450 - Just passed the 70-450 exam; I used the Microsoft Administrator's Pocket Consultant for SQL Server 2008 and BOL to prepare...

Career : Resumes and Job Hunters

How can I attract Senior Database Administrator candidates? - I have a few full-time DBA positions open on my team, one of which is for a Sr. DBA. We...

SQLServerCentral.com : Anything that is NOT about SQL!

Discussion: is e-Commerce making a mockery of SQL Server? - Some of you may have heard of my latest tales of woe, in that I am in an environment that...

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

Reporting Services : Reporting Services

error coming when running Report manager url - HTTP Error 500.22 - Internal Server Error An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

Exporting to excel - How do I get my report to filter data by tabs when it is exported to excel?

Database Design : Design Ideas and Questions

Just a rant about how third parties design apps - And by third parties, I mean of all parties, Microsoft themselves. If a column needs to be added to a...

Data Warehousing : Integration Services

Variable mapping - For Each Loop - Metadata Table (SQL ServerDB Table) COLUMNS INPUT TABLE Column1 Column2 OUTPUT TABLE ---------- ------- ------- -------------- TableA ColName1 NULL TableD TableA ColName2 NULL TableE TableA ColName3 NULL TableF TableB ColName4 ColName1 TableG TableB ColName5 NULL TableH TableB ColName6 NULL TableI Variables created: INPUT TABLE -

SSIS 2008 - Download File Base on Last Modified Date Property - Hi all, I have a table in my developing database that has URLs. Each URL is a link to a source...

Article Discussions by Author : Discuss Content Posted by Peter Larsson

Pivot table for Microsoft SQL Server - Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp