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: Programming Language

Bob Lang from SQLServerCentral.com

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

ADVERTISEMENT
SQL Server Concurrency free eBook

Understanding SQL Server Concurrency - FREE eBook

Read Kalen Delaney's free eBook "SQL Server Concurrency: Locking, Blocking and Row Versioning" to learn how to use concurrent access, troubleshoot deadlocks, and more. Download the free eBook.

Azure Management Studio

Boost Productivity with Azure Management Studio from Cerebrata

Manage your Windows Azure storage, diagnostics and compute in one place with Azure Management Studio from Cerebrata. Begin a free trial.

SQL Prompt

5 ways to code effortlessly

Discover the different ways you can make writing, exploring, and refactoring SQL code refreshingly effortless with SQL Prompt 6. Download a free trial.

Featured Contents

 

Stairway to T-SQL: Beyond The Basics Level 5: Storing Data in Temporary Tables

Gregory Larsen from SQLServerCentral.com

There are times when retrieving data for complex business requirement requires you to temporarily store one or more results sets for a short period of time. Typically these temporary tables are stored in the scope of the current connection, but they may also need to be available across multiple connections. More »


 

The Girl with the Backup Tattoo

Additional Articles from Red-Gate

The DBA Team are back, ready to save another DBA. In this episode, a rogue DBA sabotages the backups, can Robyn Page, Steve Jones, Grant Fritchey, and Phil Factor find the solution? Save the backups...save the business. More »


 

Free eBook: SQL Server Execution Plans, Second Edition

Press Release from Red-Gate

Every day, out in the various online forums devoted to SQL Server, and on Twitter, the same types of questions come up repeatedly: Why is this query running slowly? Why is SQL Server ignoring my index? Why does this query run quickly sometimes and slowly at others? My response is the same in each case: have you looked at the execution plan? More »


 

DEVintersection: Developers, ITHeros, Industry-experts Intersect Here!

Press Release

Intersect with your favorite Microsoft experts in Orlando, Florida April 13-16, 2014 at this year’s most exciting tech conference: DEVintersection.com. Register with the discount code "RedSimple" for $50 off your registration. More »


 

From the SQLServerCentral Blogs - Grant View Server State

Richard Douglas from SQLServerCentral Blogs

There are lots of Dynamic Management Objects (DMO’s, more commonly referred to generically as DMV’s) that require extra permissions. Without... More »


 

From the SQLServerCentral Blogs - Collation: Expression

Kenneth Fisher from SQLServerCentral Blogs

I had a recent run in with collation problems and it got me started reading about them. As I read... More »

Question of the Day

Today's Question (by Andy Warren):

You arrive at work to find an urgent request in your queue: Create a table for Questions that will contain the question title, date it was last changed, and some kind of approval column. Need it before the 9 am stand up so we can discuss with the team.

Springing into action, you start by running the following:


CREATE TABLE Questions
    (
      QuestionID INT
    , QuestionTitle VARCHAR(100)
    , datechanged DATETIME
    , IsApproved BIT DEFAULT 0
    )
go
CREATE TRIGGER updateQuestions ON dbo.Questions
    FOR UPDATE
AS
    UPDATE Q
        SET Q.datechanged = GETUTCDATE()
        FROM inserted i
            INNER JOIN dbo.Questions Q
            ON I.QuestionID = q.QuestionID
go
INSERT INTO Questions
        ( QuestionID
        , QuestionTitle
        , datechanged
        )
    VALUES
        ( 1
        , 'Select me!'
        , GETUTCDATE()
        )
go
INSERT INTO Questions
        ( QuestionID
        , QuestionTitle
        , datechanged
        )
    VALUES
        ( 2
        , 'Tables and Columns, Oh My'
        , GETUTCDATE()
        )
go

You're about to test updates and work on performance (you haven't added any indexes yet) when the gong rings for the stand up.

You attend the meeting, everyone admires your table design, and you let them know you'll email when you've completed your testing a few minutes after the meeting ends. You return to your cube and pick up where you left off by writing and executing this statement:


UPDATE Questions
    SET IsApproved = 0
    WHERE QuestionID IN ( 1, 2 )

The query appears to affect a total of three rows and returns an error message. You are able to fix it two different ways. You immediately think "self, this would be a great question of the day!" and add it to your todo list to write it up.

Which of the following is true? (select 5)

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 2 points in this category: Triggers.

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

Tribal SQL

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

What version of SQL Server is being released today?

Answer: SQL Server 2014

Explanation:

Despite the day, SQL Server 2014 is being released for general availability today. 


» Discuss this question and answer on the forums

Featured Script

Get the all user and their server role

Mitesh Oswal from SQLServerCentral.com

The script give you the list of all users and their respective Server role.

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

Ideal table for Memory Optimization ? - I have a simple id, name table with six million records, that i converted to a "memory optimized' table. It now...

SQL Server 2014 Developer Edition - availability for download ? - Does anyone know when SQL Server 2014 Developer Edition will be availability for download ? I thought SQL Server 2014 was...


SQL Server 2014 : Development - SQL Server 2014

The new 3D Joins in SQL Server 2014 - Has anyone tried out the new 3D joins in 2014?

How to update the year an employee has been in a specific TITLE - --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable --===== Create the test table...


SQL Server 2012 : SQL 2012 - General

SQL Server Certification - Is there an alternative to Prometric? - From searching the forums, it seems my experience with Prometric lives up to that experience by many others, so there...

Question about SQL2012 availability groups & security - How should application/users security be set up? If server #1 fails, availability group #1 fails over to server #2 but...

Sql Sever Database Server Evaluation Report Template - I'm going to be evaluating a couple of Sql Server Database Servers and wanted a recommendation on more/less a Evaluation...

Error-CreateProcessAsUser on xp_cmdshell - Hi All, XP_cmdshell is working fine in our environment with a non-sysadmin account. But recently the server got restarted and the...

Data compression in SQL - Where can I get detailed notes on "Data compression in SQL server"?It would be helpful if the notes are accompanied...

Windows Server 2012 R2 vs Windows Server 2008 R2 - Hi Is there an advantage of running SQL 2012 Ent on Windows Server 2012 R2 vs Windows 2008 R2? Thanks Steven

Setting job schedule - Hi, I have a job which I would like to run once between the hours of 11pm and 3am. The schedule in...

SQL 2012 - SQL Connections not getting Closed by Client Application and Impact Feedback Request - Hi all, I have been investigating the number of connections active\inactive to a certain database server and I have stumbled across...

IDENTITY(1,1) doesn't work, from 17 jumped to 1001 - Hi, I have a table with PK column defined like IDENTITY(1,1), it has 16 entries, now after series of migration and...

Could having SSMS open for long periods of time, cause locking? - About an hour or so ago I had all of my users come to me, complaining that their application had...

SP - Hi, I have a sp. I alter stored procedure by adding some logic to that. How can I test that is...

COMPARE TWO TABLES COPY MATCHING VALUS TO TABLE_C - i have 3 table table_A table_B table_C TABLE_A SNO NAME ID 1 RAJU [b] 070491[/b] 2 VAMSHI 089767 3 ARUNA 068908 TABLE_B SNO NAME ID 2 RAJU [b] 070491[/b] 4...

installing Sql 2012 Evaluation version on Windows 7 Pro - I have spent the past 3 days trying to get a clean version of SQL 2012 installed on my laptop. I...

rewrite cursor to set based - Hi Is it possible to rewrite this function which is using cursor with set based operations ? any example how to do...


SQL Server 2012 : SQL Server 2012 - T-SQL

SQL Transpose / Pivot - Help - PS: The attachment has a screenshot of the tables described below which is easier to read Hi All, I am stuck on...

RAISERROR not showing custom message in job history - So I have two steps in my job that check conditions and call RAISERROR to gracefully complete the job if...

Query Governor 2012 - working when switched off ?!? - I am testing 11.0.3128 on a 12 core machine - this is a default SQL2012 installation on WindowsServer2008R2. I created 12 T-SQL...

Help With WHERE Statement - Okay friends, I'm having a hard time with this query. I have two tables, Inspections and InspectionsContacts. This is a...

RecursiveHierarchyCTE - Click on the attachment, save as text file (default option). Then open it and choose File, Save As, Save as...


SQL Server 2008 : SQL Server 2008 - General

sanpshot agent is faling - snap shot agent is failing as per the below error Server XXX, Level 14, State 1, Procedure , Line 65536 Login failed...

Set up SQL Server 2008 Express - So I am very new at the setup process for SQL Server. I have used one, but this is the...

Perfomance due to foreign key - I have a table with primari key. which is refrenced across 63 columns in 40 + tables Now when i tried to...

Display all fields of the Views as one Table - Good Morning I would like for each of the queries to have all of the selected fields shown horizontally in one...

Help with CTE please. - Hello, I am trying but failing miserably with this CTE. Thank you very much for your assistance. . DECLARE @t TABLE (rowId INT...

Get the hierarchy orders(top to bottom) of the table - Can we get the hierarchy orders of table Eg: 10 tables Table1 is master to table2 and so on till table10 Just...

change recovery model to bulk logged runtime - Can we change Simple recovery model to bulk logged recovery model in runtime, and again bring back to simple mode...

Problem setting up a Linked Server - Good Morning Everyone, I normally don't post any topics because I usually find a resolution by searching. I'm trying to link...

Shopping Cart Status Change - Using a self developed a highly specialized shopping cart in (VB.Net) with SQL Server 2008. “Cart” data ([CartID],[CartNumber], [Status], [StatusDate]) is...

Auto Growth Settings - I'm just looking for some guidelines on how everyone determines what to use as their auto growth settings for system...

Data Warehouse - How easy is it configure and install a Data Warehouse in SQL Server 2008 R2?

help with deletion of multiple records - Hi there, I have a table that has multiple duplicate records. I do not want to truncate the table. I...

Issue Access Named Instance - Hi - I have recently updated my memory allocation using SQLCMD. I increased it to 4036 which should be enough memory...

Flexible Recursive Level Splitting - One column per level - Hi All, see code below, using a small sample of data. I have a table that is formed like Id/ParentId/Name, but I...

Data Collector ( MDW ) - Hi guys, someone has experience with this ? Is it possible that default counters caused problem with performance ? Specially with writing...

Long-running query with no impact on the server - I have a view which is a union of three select statements. It ran ok before but now it ran...

Interesting SQL challenge - compare differences in cells and rows - I have a table... below is a limited version of what it looks like... ID, Received, TrailerID, Tyre1, Tyre2, Tyre3, Tyre4,...

Select statement and variables - Hi, I have a query which returns the correct data when I specify the variable @InYearMonths as any number between 1...

Copy DB from Prod to QA - I need to copy the Production Database to QA and it's running 24/7. Before I do this, I just need...

Cannot create default on column with a user defined data type - This my not be as simple as it seems in that the default can be created on one server but...

Unable to shrink large database (data file) - I know I know shrinking the data file is bad, but I have a case where I just have to....

Log file is filling up - I am having some issues with this log file being filled every other day at the same time. I tried...

Custom reports wont load in SSMS? - Hi, I have created a custom report which I want to load into SQL Server (by right clicking on the server...


SQL Server 2008 : T-SQL (SS2K8)

INSERT INTO with HierarchyID - Hi all, I feel like this should be an easy question to find the answer to, but google searching yielded no...

group columns to one row - I have a table with the following columns Num,ID,Pos,Value 74 ,1,2,beck 74 ,1,2,greg 74 ,1,9,mike 74 ,1,9,laggo 74 ,2,2,beck 74 ,2,2,greg 74 ,2,9,mike 74 ,2,9,laggo Iam trying to get the...

sp_send_dbmail has formatting off on datetime when directing output to csv file - I am running this sql which creates a csv file and sends the output via email. One of the columns...


SQL Server 2008 : Working with Oracle

Linked Server - OPENQUERY Performance Issue - Hi, I hope you can help. I have been tasked with developing a solution to populate a sql db with data from...


SQL Server 2008 : SQL Server Newbies

having trouble with a sql SP statement - I have 3 records grouped by ticket number(it is a field in my table), in 2 of them I have...


SQL Server 2008 : SQL Server 2008 High Availability

SQL Cluster - Should it be setup so that an instance be set to failover if the SQL Server Agent fails? Thoughts??

The Distributer Has not been Installed Correctly - Error 2036 SQL 2008 R2 - Hello, Hoping some one can advise, I am receiving the Error "The Distributer has not been installed correct(Microsoft SQL Server, Error:...


SQL Server 2008 : SQL Server 2008 Administration

SQL Server 2008 R2 Activity Monitor - In the Resource Waits of the Activity Monitor, There are 1124776 Cumulative Wait Time (sec) for the Network I/O, 1174612...

Weird problem in database backups - Got a bit of an odd one here. A client has SQL 2008 R2 Express installed to support our product--since...

Replication Not Alerting (2008r2) - Hi, This weekend one of our publications expired and needed to be reinitialized. We found this out when it started causing...

Execute Permission Problem on Stored Procedure - Hi, I suspect I've missed something obvious, but I can't see it myself. I'm getting the message "The EXECUTE permission...


SQL Server 2005 : Administering

Options for continuously moving data from 2005 to 2008R2 for a single table - We have a SQL Server 2005 instance that hosts an OLTP environment for our finance system (Navision). We have CRM...

Moving SQL LDF (Log) Files - I know this is going to sound like a daft question but want to be safe :-) Working with a customer...

The SQL Server service terminated - The SQL Server service terminated with the following error: Ran out of memory OS details: windows 2008 enterprise SQL Vesrion: sqlserver 2005...

Log shipping and shrinking data file - Hi Have a question on shrinking datafile when its log shipped. When the secondary database is setup in standby mode...


SQL Server 2005 : Backups

Will changing the recovery model on the fly prevent ability to restore properly - We have a very large import job on a critical database. At the start of the job we set the...

Backup failure - Need your help to resolve below error. Error:- Unable to start execution of step 1 (reason: The SSIS subsystem failed to...


SQL Server 2005 : Business Intelligence

Dynamic Dimension Security in Analysis Services based on User and Datasource Dimension - Hello I have an Question to Dynamic Dimension Security in AS. I have a following Problem. I want to Secure...


SQL Server 2005 : Data Corruption

No catalog entry found for partition ID (MSSQL 2005) - I've seen few posts regarding this error msg but none that have yet helped me. This error has happened a...


SQL Server 2005 : Development

Converting sequential time records into IN and OUT times - Hi, We have a Time & Attendance application that stores the time punched by each employee in a sequential format. There is...


SQL Server 2005 : SQL Server 2005 Integration Services

Derived Column Transformation not filtering as expected in SSIS 2008 Data Flow - Hi All, I have the following Expression working on a Derived Column in an SSIS Data Flow Transformation [quote][Date_Start] == " 0 0- 0-...

Can Package Be Run From Client Machine? Will Connect To Separate SQL Server Machine. - In a few months I will be installing a SQL Server package at a client's office. I've been doing SQL...

DTEXEC returning Exit Code 5 - I have a simple SSIS package that retrieves data from a formatted Excel spreadsheet. The Stored Procedure has the appropriate...

SSIS 2005: How to Dynamically Rename First Worksheet in Various Excel Files - Here's my situation... I'm working on a project for a client (I'm a newbie SSIS user, but have about 20 yrs....


SQL Server 2005 : T-SQL (SS2K5)

Nesting xml - Hello, I am working on xml from SS2k12 results. I can get it to work with for xml auto; elements but...


Reporting Services : Reporting Services

SSRS - Data Driven Subscription, Multi-valued parameter, SSAS Data Source - Hi, I am working on setting up data for a subscription table that will feed parameter values to an SSRS...

SSRS Javascript to redirect to another report in 2 mins - Hi, Can anyone help me with a JavaScript code that will redirect from the main report to another report url in...

How to convert decimal numbers to Hours and minutes - Hi, I am using SSRS 2005. I want to convert decimal numbers to Hours and minutes. Say suppose, I am adding daily...

SSRS Report slow - I have one ssrs report, when we run th report, its loading page opens very late, once it start showing...

Dual value axis in line chart - Is there a way to display a value (vertical) axis on both the left and right of a line chart?...


Reporting Services : Reporting Services 2008 Development

ssrs 2008 missing parameter value - In an SSRS 2008 R2 existing report, I am passing a new parameter called 'SchoolYear' to an existing report. I...

how to show column dynamically based on selection parameter and how to achieve below mentioned grouping - attachment will clear report format. So pfa. c1 c2 c3 c4 c5 c6 1 2 4 8 9 10 5 11 3 6 12 7 13

ssrs dynamic show column based on selection and grouping - I want to delete this Please remove this how to show column dynamically based on selection parameter and how to achieve below...

Report rendered in excel throwing error of "Excel found unreadable content" - Hello, I am exporting my report in excel 2010 and on opening the worksheet, its throwing me error of "Excel found...


Programming : General

Are cursors hard on servers? - I was reviewing a process with a colleague and mentioned cursors. He said, "Please don't use cursors as it is...


Data Warehousing : Integration Services

Merge data between 2 tables - How to write a query to add data from one table to every record in another table withou using cursor...

importing to SQL Server from .txt - Using SSIS, I'm importing a .csv flat file source having 7 columns into a SQL Server table destination. To avoid...


Data Warehousing : Strategies and Ideas

Business Intelligence Prototyping Tool - Hi All I'm developing a utility for rapidly prototyping full BI systems based on specification files. The idea is that an...


Data Warehousing : Analysis Services

Trying to create Hierarchy in BIDS from DimTable - Hi - I am trying to create a hiearchy from a Dim Table linked to the SQL DB and I'm having...

AdventureWorksDW-No Role Playing Dimensions? - I'm trying to read up on SSAS and going through some tutorials. They show the DimDate and FactResellerSales. What I don't understand...

Create single cube from multiple identical OLTP databases - I need to create a single cube from about 50 separate, but identical relational databases, each representing a different client,...

SSAS Cube on one server and SQL Server DB on another - Connection Issues - Hi Guys - I data modeled a star schema data mart in SQL Server 2008 and I've created a cube in...

SSAS Deployment Utility - Has anyone had any success using the SSAS Deployment Utility for SQL Server 2012 (Microsoft.AnalysisServices.Deployment.exe)? It should be possible to...


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

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 : Suggestions

DW Data Model - Finacial services - Hello, we are creating a new DW at our firm. I have several questions. The first one is In regards to...


SQLServerCentral.com : Articles Requested

XML Import - Looking for a basic article that imports a series of XML files in a folder into a table. There should...

Intro to Hadoop - basics, what is it, what's it used for.


SQL Server 7,2000 : Administration

when to use dbcc shrinkdatabase - Hi, When we need to use dbcc shrinkdatabase once i delete some bulk records or on timely bases? I am having a...

Server Registration problem - We finally made the intermediate jump to SQL 2000 Enterprise from 7. Everything is running smothly with the exception of...

SQL 2000 - Blocking Happening again and again - Hi guys.. on sql 2000 ..from today morning blocking keep happening again and again even though killed SPID .. Reastarted SQL and...

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


Career : Employers and Employees

DBAs happiest people at work - [Url] http://mobile.news.com.au/finance/work/careerbliss-survey-ranks-database-administrator-as-happiest-job-in-america/story-fnkgbb3b-1226868951892 [/url] is it really true??

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