In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Optimize SQL Server performance
“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.
 
SQL DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now.

In This Issue

Change the Settings of a Database Object Using Powershell

This post tells you how to change the QUOTED_IDENTIFIER and ANSI_NULLS settings of a database objects using Powershell. More »


Red Gate Software Brings Free SQL Server Training Back to US Due to Popular Demand

Red Gate Software is bringing SQL in the City, the popular free training event, back to the US in three new cities. This October, the events will take place in Pasadena, Atlanta and Charlotte. More »


Uploading Data to Windows Azure SQL Database

One way of getting the advantages of the Cloud without having to migrate the entire database is to just maintain a copy of the data that needs to be accessible to internet-based users in Windows Azure SQL Database. There are various ways of keeping the two in sync, and Feodor Georgiev describes a solution based in using SSIS. More »


From the SQLServerCentral Blogs - Security Questions: Differences between “Grant” and “With Grant?”

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’sTraining on the T’s, I... More »


Editorial - Data Will Drive the World

There's a well known essay from Marc Andreeson that talks about how software is eating the world. There's a lot of truth to this, in my opinion, and it becomes very important for more and more people to realize that software is going to become more and more of a part of their lives, all parts of their lives. Whether in business, in your personal life, in government or anywhere else, software is going to increasingly be used to interact with the world. This will bring about many opportunities for people in technology to help shape the way those interactions affect all of our lives.

However it's not just software that's important. The data that drives this software is arguably more important than even the software. This data drives the software algorithms to produce some result or action. Hacking the data to change values can even change the results from the software. Different software algorithms might interpret or react to data differently. If data isn't more important, it's equally important as the software that processes it.

We are seeing the additional recording and gathering of data allowing all sorts of new software to be written. Some of the software is performing jobs that humans have do in a faster and perhaps more efficient way. Other software is helping people understand the world in a way they might never have seen it. We even have software replacing other  software jobs. That feels quite surreal to me, but it's made possible by more and more data being available to help guide the development and growth of systems.

We are in a good business for the future, with the growing needs to process, manage, and manipulate more and more data in the future. It's up to us to learn the skills we need to do this efficiently, and in ways that can't easily be automated.

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

Everyday Jones

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


Question of the Day

Today's Question:

What happens?

---Create temp table
CREATE TABLE #temp
(
ID int PRIMARY KEY,
Name varchar(max) 
CONSTRAINT UN_Name UNIQUE(Name)
);
---Insert few values to temp table
INSERT INTO #temp VALUES (1, 'NAME_1');
INSERT INTO #temp VALUES (2, 'NAME_2');
INSERT INTO #temp VALUES (3, 'NAME_2');
INSERT INTO #temp VALUES (4, 'NAME_4');
---Select all from temp table
SELECT * FROM #temp

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

This question is worth 1 point in this category: T-SQL. 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 Server Execution Plans

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

You come into work one morning and find an SSMS query window on your computer open, with the following text in the Messages pane:

Msg 2001, Level 1, State 1, Procedure OpenThePodBayDoors, Line 1

I'm sorry, Dave. I'm afraid I can't do that.

What is the likely cause of ths message? 

Answer: 4. Someone has typed that message in the Messages pane.

Explanation: 1. There is no known occurence of a SQL Server instance becoming self-aware. That's the stuff of science fiction.

2. Sadly, no, this is not an easter egg.

3. Messages added with sp_addmessage must have an ID of 50001 or higher. So someone could not have added this message with an ID of 2001. See http://msdn.microsoft.com/en-us/library/ms178649.aspx

4. You CAN type in the messages pane of SSMS, and if you run code that generates an actual error, and then carefully type your own error over the original error, you can even make your fake error appear in red text.

So 4 is the correct answer, as it is possible to make this happen by typing in the SSMS Messages pane.

» Discuss this question and answer on the forums

SQL Server Execution Plans

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!


Featured Script

Script to change auto growth settings for all databases in SQL Server

Script to change auto growth settings for all databases in SQL Server. 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 2005 : Administering

Correcting Login to User mappings - I have a server where there are logins created that are mapped to dbo in a database. I want to change...

Very large table - performance issues - We have a tall table that contains 2.6 billion rows Table structure: [img]http://i.imgur.com/Z02zDqk.jpg[/img] The application which uses this table has been running slow...

SQL Server 2005 : Business Intelligence

deleting rows from a table after processing them in SSIS - Hi Guys, I am a newbie and I need to do the following task: I have to process the rows in one...

dynamic import of multiple flat files - hi need your help regarding dynamically importing multiple flat files into different table of sql server database. the format of files...

Import Excel binary files into SQL Db using SSIS - Hello, I have a several .xlsb (MS Excel binary) files with data that I need to import into a SQL...

SQL Server 2005 : Development

Order by date not by alphabatical order but by usual jan - Feb etc required - In the query below: select right(convert(varchar, CCD.startDateTime , 106), 8) as startmonth, sum(case when CCD.contactDisposition = 1 then 1 else 0 end) as...

Result set to be modified - I have query as follows: select right(convert(varchar, CCD.startDateTime , 106), 8) as startmonth,CCD.contactDisposition, count(*) as TotalCalls from ContactCallDetail CCD, ContactQueueDetail CQD where CCD.sessionID=CQD.sessionID and CCD.sessionSeqNum=CQD.sessionSeqNum and...

SQL Server 2005 : SQL Server 2005 General Discussion

How do I calculate this? - I have a table called locations with fieldnames: locationId --determines the location of trainee locationName --name of the location training maxSeat --total number...

query xml - i have a large xml file one of my queries looks like: SELECT p.value('(PodactID)[1]', 'nvarchar(max)'), q.value('(CustomerID)[1]', 'nvarchar(max)'), r.value('(IsPaid)[1]', 'int'), r.value('(Payment)[1]', 'real'), r.value('(PaymentDate)[1]', 'nvarchar(max)') FROM XmlTable CROSS APPLY...

SQL Server 2005 : SQL Server 2005 Strategies

Convertion of integer to Datetime format - Hello All One of the column in my database contains createdate which gets stored in integer format just like that...

SQL Server 2005 : SQL Server 2005 Integration Services

Dump table to multi-line CSV file.... - I want to export a table to a CSV file using SSIS. Each table row has 2 "row type" columns...

please advise on transforms to use: Lookup and Merge? or something else? - I have an excel datasource which must be unpivoted, case statements run against it, joined with look-up tables to pull...

Derived Column to handled mulitple conditions in CASE statement? - HI, I've got a case statement that works, but because I have to do it in SSIS I am at...

SSIS by using SP with multiple result sets - Hello, We have an SP which is developed to give us two result sets based on selection. I want to...

SQL Server 2005 : T-SQL (SS2K5)

SQL Table Variable Issue - Good Afternoon All, I have defined a new Table Variable and initialized it. When I try to referance a column from...

SQL Server 7,2000 : Administration

string or binary data would be truncated error - Hi There is a " string or binary data would be truncated error " raised while executing the stored procedure. The stored...

SQL Server 7,2000 : Backups

Can't access data from database restored from backup if not sysadmin - Hi everybody! I need some help please: - I'm creating a .bak file from a remote sql server 2005 express database. -...

SQL Server 7,2000 : Performance Tuning

Shrink large database - I have been asked to look a a sql 2000 database that has been neglected for some time. It has an...

SQL server performance troubleshoot using performance counters - I am having sql server 2008 r2 server. I saw that yesterday the server so slow. When i see performance...

SQL Server 2008 : SQL Server 2008 - General

Can you delcare an operator? - Can you declare an operator? DECLARE @MYTEST AS VARCHAR(3) SET @MYTEST = '<>'

If Index Seeks + Scans + Lookups = 0, okay to drop the index with a lot of updates on it? - We're trying to optimize some of our tables and notice quite a few of our indexes are getting updated frequently,...

Creating enough empty pages in the database. - I use the following script to create empty space in a database. (So that during an opperation the database does not...

Making a copy of a table. - What is a good method to make a copy of a table. Only the table not the data. Including all indexes. Excluding referential...

Procedure with Table-Valued Parameters in 2 databases ? - Hello! I created a type ( CREATE TYPE AS TABLE myType .. ) in my [b]2 databases[/b] to be passed as a parameter...

Cluster validation - Active/passive - I setup two nodes (node1 and node2 ) as active/passive clustered server. When I run the falilover cluster validation, I get...

Long-running process - Hi, We have a third-party application with a SQL Server backend that imports the records we insert into a staging table....

Estimating space needed for database - Hello - is there a way to determine the disk space needed based on this request... "please determine if we could host...

SQL Server 2008R2 Evaluation edition download link - Hi, I am trying to find the free trail version of SQL Server 2008 R2 Evaluation edition but no success.I...

trigger for email alert - i want the trigger for email alert any user taken backup or restore and also anybody login in development server

SQL Server 2008 Business Studio - Hello Folks, I am looking out to learn about the business studio features in details in 2008 version. I am...

how to get the text of isnull function in sql server - In Sql server to see the function text, we can use sp_helptext. can you please help me to view the...

Query executed by user - On my DB Instance some user truncated all my data table. So is there a way to find the user who...

Sum Time - Hey, I have a column named TOTALTIME (datetime data type). This is used to store the amount of time something takes...

alert configuration - i need to setup alert when the sql server is shutdown and server is up , how to achieve this

How to Allocate the recieved amount to different expenses - Create Table #Temp ( Number Int, Totalcoll Numeric(12,2), Maintainance_Due Numeric(12,2), Maintainance_Coll Numeric(12,2), Expense_Due Numeric(12,2), Expense_Coll Numeric(12,2) ) Insert Into #Temp Values (1,0,500,0,400,0), (1,900,0,0,0,0), (1,0,200,0,800,0), (1,1000,0,0,0,0) Select * From #Temp /* With reference

backup log - is it possible to take log backup in a restored database ?

Code review rant... - During an online code review: Me: Your trigger is not set based. It will only process a single row. SQL Dev (w/...

Using case statement in where clause to fetch rows whose column values are null - Hi, I need to fetch rows from a table in SP depending on input parameter value. @var is an input parameter which...

SSIS change destination to named instance - Hi We have several SSIS packages where destination is 'SQL Server Destination'. Now they are moved to another server with a...

Execution SSIS-package from SQL Server-Agent fails - Hi, there is a SSIS-package, that runs properly in Visual Studio. It has a for-each-container wich loops a file-directory within flat-files. There...

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. - Dear All, From morning onward in my sql server 2008 i am getting following error. Timeout expired. The timeout period elapsed prior...

Sql Programme for allocation of amount in accounting - Hi, I want to build the logic for "allocation of overhead amount in accounting" in sql server 2008.Please help me how...

How to read each row and update table - Arrears Credit Line Dates AvailableAmount Principal Interests Principal Interests 0015321-01 31/01/2003 220,000 40,000 30,000 - - 0015321-01 28/02/2003 - 40,000 30,000 - - 0015321-01 31/03/2003...

Decimal(18,0) or int? - Hi I have a column of data type decimal(18,0) can i change it to Int for the above case whether...

Agent Job Links to Another SQL Server and Fails due to Service Account Privs - To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite...

SQL Server can't connect to database - I downloaded file SQLEXPRADV_x86_ENU.exe (version 10.00.1600.22), and used it to install SQL Server 2008 Express with Advanced Services, which I...

SQL Server Corporate Standards - Automated Compliance Checking - Does anyone know of a flexible automated SQL Syntax checker? I want to be able to analyse scripts before they are...

Report not working on Report Server - I'm not sure if this is the right place to post this one, but maybe someone can help. I have a...

Settings of Windows Server for SQL - Hello, Do you have any tips for settings of WS? Roles, memory, etc.. ? it can be different betwen performance? any...

Help with next date in SQL query - What I need is to be able to find out when a customer next called in from the date I...

Backup failure History.... - Hi Team, I need to find out the backup failure history..... Till date how many backup are failed in sql server...

SQL Server 2008 : T-SQL (SS2K8)

Dropping select tables across a database. - Hi I have a situation whereby I have hundreds of tables across my instances that need dropping. The tables are prefixed...

Text Search for age-group related words - Hi Everyone, I have an app that creates groups and designates members based on their age. Now I would like to...

WHERE clause - I am trying to write a where clause that tests for a string which may or may not contain a...

Most Efficient Insert query - Good Morning Everyone I hope that everyone had a very nice weekend. I am inserting rows from one database to another. The...

I want to validate where each parent intermediary is also a parent to itself? - Hi All', I want to validate where each parent intermediary is also a parent to itself. So this query should...

need help with my SQL - greetings all, I'm trying to return a row of data for each licence and want that row to include the phone...

Funnies on Views??? - Hi all Just a quick one. As we all know a view is a set of columns from a table we...

Query Help - Hi,Can some one throw some light on how to approach this.... CREATE TABLE A (ID1 VARCHAR(10),ID2 VARCHAR(10)) INPUT TABLE A Id1 ID2 A B B...

I want to write validation Query which should return invalid parents if any.... - I have a table for which I want to validate where each parent intermediary is also a parent to itself....

Hierarchical Child Records with Multiple Parents - Hello, In my limited experience I have used a cte to write simple hierarchical result sets but this one is a...

Need some query help. - I need to figure out how to do a group by on this query by number of days between start...

OFAC Compliance queries - Hi, I am trying to implement OFAC Compliance check using sql queries to search for data in our database. If anybody...

SQL Server 2008 : SQL Server Newbies

Converting a old .sql to new - Hey all, I have a question from a complete noob to sql. I have a old set up .sql that...

Hel with a Stored Proc - Date Field - Hey guys - I'm running into an issue where SQL Server is barking at me over a syntax issue when testing...

Query Sum - Hi all, hope in your help. This is my table: [code] +----+--------+--------+ | id | field1 | field2 | +----+--------+--------+ | 1 | A1 | 7 | | 2 | B1 | 9 | | 3 | C1...

Random Number Generator - I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes...

SQL Server 2008 : Security (SS2K8)

SSL encryption - Hi Guys, I am not too familiar with SSL encryption in SQL server. Can anyone please give me the pros and...

Pre Login HAndShake Error in Sql Server - 2005 SSMS - Error: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared...

Best way to completely keep an entire Active Directory group of people out of a SQL Server - I'd like to ban an entire group from accessing specific SQL Servers, does anyone know of a solid way to...

SQL Server 2008 : SQL Server 2008 High Availability

Mirror on three instances on one PC - Trying to get developer edition 2008R2 to mirror AdventureWorks with a monitor as an experiment on my PC. Is it...

SQL Server 2008 : SQL Server 2008 Administration

Unusual Backup Strategy - Hi, One of our clients (whose SQL Server we don't support) has an unusual backup strategy within SQL Server. They appear to...

Add e-mai signature to SQL Database Mail - Hi All, Is it possible to configure SQL Server 2008 R2 database mail to add a signature to all e-mails sent...

Index maintenance, excluding index with page_level_lock disabled - Hi all, I'm running into an issue with not being able to rebuild / reorganize an index because allow_page_locks is not enabled...

Error: the select permission was denied on the object 'sysjobschedules' database 'msdb' schema 'dbo' - Error: the select permission was denied on the object 'sysjobschedules' database 'msdb' schema 'dbo' Hi All Please help me to fix...

failure:restore master database in sql server 2008r2... - Hi, I hope you are doing good. Iam facing one problem with Restoration master Database in Sql server 2008r2(named instance) enterprise...

Moving SQL Server 2008 R2 to new Server Hardware - Hi All, I'm tasked with moving an existing SQL Server 2008 R2 environment to a new server. The current server is...

Having backup problems, and unable to change service accounts... - Two problems, possibly related, both giving me heartburn as I'm going away for 2 weeks and don't want to leave...

Restoring Backup - Hello, I would like to know that what will be the best way to restore from Full backup, Differential and Transaction...

Backup Job Incomplete - Hi, I have problem My backup script runs on every sunday for full backup and should have to take 41 database...

Programming : Powershell

Retrieving Data From Hyphenated Server - What better place to solve a nagging Powershell issue than good ol' SQL Server Central :) I'm trying to retrieve disk...

SQLServerCentral.com : Anything that is NOT about SQL!

So How Do You do your mass Mailings? - Once a year I help out a local non profit promote their annual event. This is the third year for...

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

Running a report multiple times - Hi, So... I have a report that has a dataset that looks something like this: AccountManager, Product, Value The report has a variable...

Reporting Services : Reporting Services 2005 Development

SSRS With Progress Database connection - I am trying to assign two parameters to a query containing two tables against a Progress database. However, the parameters...

Database Design : Hardware

Hidden SAN fragmentation - Hey folks, a quickie question about the drive space assignment mechanics. We'll simplify things. Working on a single drive in a...

Data Warehousing : Integration Services

Explode dates in data flow task - I have been struggling with this, and I am starting to think this is impossible to do inside a data...

SSISDB Project Deployment Model - I have an SSIS project solution which I have deployed from our development server to a SSISB Catalog on our...

SSIS Package Won't Stop Running and Locking Files - We are running SQL Server 2008 R2 on a 64bit machine, I have several SSIS package that are run via...

Data Warehousing : Analysis Services

MDX ParallelPeriod Function - HELP - Hi to all. I have a calculated measure in one of my cubes. This is named DELTA SALES AMOUNT It calculates [b]Parallelperiod[/b] about...