In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Toolbelt Want to work faster with SQL Server?
If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.
 
SQL Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.
 
SQL Skills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

SQLServerCentral Runs sp_Blitz - Query Plans

The fifth installment of the sp_Blitz script being run on the SQLServerCentral database server. More »


New SQL Monitor Custom Metric: Issues found by sp_Blitz

This custom metric adds Brent Ozar's popular SP_Blitz scripts to Red Gate SQL Monitor. It looks for configuration, security, health and performance problems, and reports back with a list of issues for you to look into. It’s great for quickly understanding the state of a server you've been asked to look after. More »


Database Deployment: The Bits - Agent Jobs and Other Server Objects

Databases often need more than just the database objects to run. There may be certain server objects and components, and SQL Agent objects, that are required as well. For an automated deployment, these need to be identified and their build script placed in source control. They then need to be deployed via the pre, or post deployment script. Phil spells out how and why. More »


From the SQLServerCentral Blogs - Dedicated Administrator Connection (DAC)

We all dread the scenario whereby SQL Server is under so much load and has a complete lack of resources... More »


Editorial - Helping MS Help Us

I think SQL Server is a fantastic product. I've been using the platform over 20 years, across 8+ versions and 2 operating systems. It's provided me with a great career, and one I've enjoyed. However the software isn't perfect, with numerous bugs and holes. The Connect system was introduced years ago as a way for people to submit bugs and suggestions, vote on them, and provide feedback for Microsoft. 

Does it work? I think Connect is broken, and while I sympathize with the volume of suggestions that Microsoft must deal with, I'd hope that they actually listen, consider, and act on something other than documentation bugs. While I sympathize with the volume of suggestions that Microsoft must deal with, I'd like to think that they actually listen, consider, and act on something other than documentation bugs. Those seem to be the only ones acted on lately.

Most of the items submitted are sent in by individuals, and they get one or two votes, but that's usually from the submitter's friends. Many of the items are, IMHO, not important enough to submit to Connect. My guess is they cause plenty of people in Microsoft to view Connect as a slop bucket where most users toss every complaint they have about the product. I sometimes wonder if items are submitted by users before they even spend a few minutes on a search engine trying to resolve their problem.

However there are some great suggestions, and this is one. It's asking for a new virtual table: Errors. This is in addition to the inserted and deleted tables. It's not just that I think so, but there are over 300 people that have voted it up. It's the number 6 highest voted item on Connect. Users see value in this, especially data warehousing users. If you read the comments, this thread, or this blog, you might agree. If so, then I'd encourage you to vote it up. If not, vote it down. Either way, leave a comment on why this would help (or not help) your work. I don't know if this will change Microsoft's mind on the issue, but it would be interesting to see if they respond with anything other than there are "resource constraints" preventing this from being implemented.

While you are at it, look over the list of top voted on items. There are some good ones:

If any of them would make your job easier, let Microsoft know. They always say that customer problems and situations give them reasons to enhance the product or fix issues. Let's give them a chance to actually prove that statement is true. Participate in our experiment and vote today.

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

I have a table, Sales, with the Notes field having the Filestream attribute. I run this query:

select
 salesmen, notes
 into #salestemp
 from Sales

What is the data type of the Notes field in the temporary table?

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 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today



Yesterday's Question of the Day

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

Answer: The temp table throws an error.

Explanation: The temp table failed to create because the name column type is invalid for use as a key column in an index. The total size of an index key is limited to 900 bytes, so a max varchar is too large.

Ref: http://msdn.microsoft.com/en-us/library/ms191241(v=sql.105).aspx

» 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

Passively detect attempts to guess passwords

Review the error log for possible brute force or dictionary attacks on your SQL Server instance. 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...

Crear Documentacion de Pacquete o proyecto ssis 2008 - Estimados. Muy Buenos Dias, mi Pregunta es la siguiente: existe alguna opcion en ssis 2008 que me permita exportar un paquete (dtsx)...

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

Stored Proc with temp table issue. - Hi everyone. I understand that this topic was covered several times on this forum, but I can't seem to find ones...

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

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

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

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

Searching stored procedure for table name but ignoring comments - Hi All, Thanks to others who have posted helpful tips I have a way of searching through stored procedures to list...

Invoke or BeginInvoke cannot be called on a control until the window handle has been created - Hi I am trying to install SQL Server 2008 Developer Edition x64 on a Windows Server 2008 VM. I run the...

SQL Server 2008 : T-SQL (SS2K8)

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

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

SQL Server 2008 Upgrade Advisor failure... - Hello, We're running SQL Server 2008 upgrade advisor against a SQL Server 2000 instance. When asked to identify which components to...

Counting duplicates - newbie question I am trying to count the number of diabetic patients who have had their blood pressure checked twice or...

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

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

cluster - Can someone help me understand cluster? we have two nodes for the cluster. each cluster we installed two sql instances, one...

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

Career : Certification

70-462: Can the Hyper-V environment be downloaded somewhere? - Hi all, When I just opened the 70-462 TK I saw a six server Hyper-V environment is neccesary to complete the...

Career : Employers and Employees

Go for job you can comfortably do or job you will grow into? - I'm in my current job over 2 years as a database consultant and dba its feeling like its time to...

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!

What is Functional DBA? - Hi All, I hear “Functional DBA”, I search in Google to know exact meaning for it and found that “A functional...

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

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

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

SSRS problem when adding PageName - I am using SSRS 2008r2 and importing the rdl into SharePoint 2010. The rdl worked fine and when imported into SharePoint...

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

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

call sproc in oracle - We would like to do this in SSIS package: 1. pull a list of employeeID, with their address from a SQL...

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