In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Free Red Gate Books 300 SQL Server & .NET books to give away
To say thanks for our new SQL Mag awards, Red Gate are giving away 300 SQL Server & .NET books. There are 27 titles to choose from – enter the draw now.
 
SQL Backup Pro Gold Community Choice Award for SQL Backup Pro
Try award-winning SQL Backup Pro for faster, smaller, fully verified SQL Server backups. Download a free trial now.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

In This Issue

Stairway to SQL Dialects Level 3: MySQL

As part of the LAMP stack, MySQL is incredibly important for providing a reliable and platform-agnostic database platform for web development. This level looks at the syntax of MySQL and how to best port SQL code to a MySQL environment. More »


Steps to add Log Shipping monitor into an existing SQL Server

I have a requirement to add the Log Shipping Monitor for an existing installation. I have heard you can only complete this by rebuilding the Log Shipping infrastructure. Is that true? Are there any other options? In this tip I will explain how we can add the Log Shipping monitor to a SQL Server 2005, 2008, 2008 R2 or 2012 environment without rebuilding the Log Shipping installation. More »


From the SQLServerCentral Blogs - Plan cache bloat by SQLAgent (Prior to SQL2008) !

Recently I was checking the plan cache utilization with the help of the below query which I normally use for... More »


Editorial - Type A, B, or C

This editorial was originally published on Feb 26, 2008. It is being re-run as Steve is traveling.

There are all different types of people in the workplace; some are driven, some aren't, but there are more facets to this. I worked with someone awhile ago that was a good worker. Solid, produced quality results, and was dependably. This was someone that you might consider one of your better employees and a high achiever.

But not driven.

In other words, it was someone that liked to work, would do a good job, and enjoyed the work. But it wasn't someone that you should promote, despite this being the type of person you might think would be next in line. This person wanted to move their career forward and make more money, but in interesting ways as a technical worker, not as a manager. And this person wanted to confine work to work hours, being able to enjoy time with family outside of work.

In many ways I'd consider this a Type A person, but not so ambitious or driven to succeed they they'd compromise the rest of their life. But more driven to accomplish things than what I've typically seen as a Type B person.

I'd call this a "Type C" person, someone that has drive and a desire to succeed, but keeps that drive in check and balanced in their life. And I think that there are many, many of you that fit this category, not driven enough to be a workaholic, but not so distracted and free floating that you don't move forward. There's actually a Type C recognized by psychologists.

There are more types of personalities and even tests that measure the degree to which you fall into certain categories. The Myers-Briggs test is one of the most common, resulting in a four part rating. I've done it a few times and it's an interesting test. If you're interested, I'm usually an ISTJ.

I don't think the tests necessarily tell you who will do better in a situation or who might succeed, but they are interesting in that they can help you to determine how best to get along with other types. There are definitely techniques for dealing with different types of people, and more importantly, ways to ensure you don't cause conflicts.

Whatever your type, I think it's important that you learn who you are and what's important to you and keep that in mind as you make decisions about your life and career.

Steve Jones


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). 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.

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

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

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


Question of the Day

Today's Question:

True or false: In SQL Server 2005 and higher, if you have optional monitoring server in your log shipping setup, you cannot configure the monitor server to record the status and historic information about log shipping jobs. (like backup, copy, restore of the transaction log), but only to send alerts to the operator when it fails.

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

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

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Yesterday's Question of the Day

I have the following set options

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON;

I then execute the following T-SQL statement

CREATE TABLE "SELECT" ("TABLE" int)
GO;

Is the table (Select) created ?

Answer: The table is created

Explanation: From: http://msdn.microsoft.com/en-us/library/aa259228(v=sql.80).aspx. When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not usually allowed in Transact-SQL identifiers

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Get Rid of All Those Pesky 1 MB File Growth DB Files

Use sp_MSForeEachDB to set all 1 MB file growth databases to 10%.  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

Help needed with analyzing this deadlock - Hi all: Here is the output from the errorlog on a deadlock that occurred this morning: 12/04/2012 09:15:54,spid18s,Unknown,waiter id=process8d9e28 mode=S requestType=wait 12/04/2012 09:15:54,spid18s,Unknown,waiter-list 12/04/2012...

sql server user database file create date - how to find sql server database file create and alter date and time please help us

One sql sevrer 05 runs fine in UAt other runs slow ni Dev--Please suggest - hello All, i have 2 sql server 05 in 2 diff environment one in UAt and One in DEV theone server...

Error: 924 - Database "abc123" is open and can have only one user at a time. - I could not do anything on this server on any db, i get this same error on the server what...

Increasing the size of the container - Hello -- One of our systems is running SQL Server 2005, and has a database that was initially 'sized out' at...

SQL Server 2005 : Backups

Transaction Log Backup Job Failure - does it break chain? - Never had a "set-in-stone" answer on this one and I've, fortunately, never had to find out the hard way, but...

Create a policy to avoid backups withou copy_only option - Hi. We have several databases in full recovery model. Daily we make full backups, but sometimes we need take a...

Operating System error 5(Access is Denied) - I am an intern working with two SQL Server 2005 instances on two Windows Server 2003. The servers are linked...

SQL Server 2005 : SQL Server 2005 General Discussion

Different domains causing poor query performance? - I think my powers of google-fu must be letting me down - can anyone help with the following:- It has been suggested...

HELP! Resized disk SQL is installed on and now SQL won't start - HELP! I know, should have had a backup. I have db backups. I wanted the system drive to be larger so...

Regarding stored procedure in sql server 2005 - i have using two query in stored procedure, i want to use insert query in some time only alter proc...

Error while executing maintanance plan jobs - Hi everybody, I am getting the below error while executing the maintanance jobs like full backup, transactional log backup. Message Executed as user:...

SQL Server 2005 : SQL Server 2005 Performance Tuning

CXPACKET wait type - Hi guys, I'm seeing CXPACKET wait types in one of my database running SQL2005 which causing the CPU hit 100%. The...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Package step FTP - I have a step that ftp out to a location to grab a file. each month the file name changes,...

Pass SQL Execute task output to target tbl(Data flow). - Hi, we need to insert random values to target table. We are using the query in SQL Execute task with single row...

SSIS LOOKUP QUESTION - Wanted to use lookup transformation for the following: - Pull records from oledb source - Lookup in oledb destination first to see...

DATA CONVERSATIONS TASKS FAILS IN SSIS - Hi, The problem was long enough to take my whole day. To make story short-- i have flat file with...

SQL Server 7,2000 : Administration

Querry on Reintializaion of inactive subscription - In our scenario we are having two servers. There is transactional up datable replication in between those two servers One is...

Replication-Replication Distribution Subsystem: agent ********* scheduled for retry. Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. - Hi Every one. Due to some issue we restarted our server.After restarting we are observing the below error. Error1: Replication-Replication Distribution Subsystem: agent...

Seibel Support - Hello, I've got users using an application called contract works that is written on Seibel. I have had reports of it...

SQL Server 7,2000 : Data Corruption

How to Recover Deleted Rows from sql 2000 Database - How to Recover Deleted rows from sql 2000 Database using with some period parameter eg: from 01/01/2012 - 20/05/2012 Display all...

SQL Server 7,2000 : Performance Tuning

Why does where clause cause query to slow down so much? - Hi, I have the following query that I'm using to extract some demo report data. [b]SELECT HOURS.EMPID, HOURS.WORKDATE, HOURS.MINS, CODES.CORRCODEID, CODES.CORRCODE, CODES.NAME AS [CORRCODE NAME], CODES.CWTYPE, DEFAULT_STORE.[STORE NAME] AS...

SQL Server 7,2000 : T-SQL

Need Split delimiter string into columns Solution - Hi All, I need help with a string with delimiter that needs to be split into column names. I have been...

SQL Server 2008 : SQL Server 2008 - General

Querying Results From a Query - Good Evening Everyone, Seeing as in the next few weeks my boss is going to be increasing my workload dramatically :crying:...

Views based on tables on diff servers - Hi All, Could you please let me know the possibilities of creating a view based on a table that exists in...

MSSQL 2008 64 Bits Connection VB.Net project (VS 2005) - Hi. I'm having an issue with one new SQL 2008 64 Bits installation. an existing app using SQL 2008 32 Bits works...

Scheduled job that pulls from various linked servers keeps hanging process - suggestions? - We have a query running on SQL Server 2008 that pulls data from about 6 Linked SQL Servers hourly. It...

Issues with data format - Hello All, IF '12/04/2012' < '03/01/2013 select 1 The above query is not fetching any result but if i use '>' condition it...

Best way to store photos - Hello our app is used for plant inspection. Each inspection file will have about 30 photos. We resize photos so each...

Backups and restores - I have 2 SQL Server 2008 environments - Production and Test. Both are on different domains and cannot speak to each...

Database Systems - [size="5"]What standards are available to provide connectivity between database systems? [/size] This question i had get it , i had confused about...

How to retrieve Table Variable data in a Trigger - Hi SSC Team, i have created a stored procedure with a "table variable" in it, inserted some records into table variable based...

SSIS and looping through result set - Hi all, Ive seen a few variations/pointers on this but nobody doing what I am trying. I *think* this is the...

Space issuefor Mdf files... - Hi, We have a sql server 2005 server running on win 2003, Our data files are present on F-drive . For one of...

Matching rather unstructured character data against lookup tables - Hello all, I am looking for advice on possible approaches to take to solve a specific problem. The problem is in...

Failover Problem - Dear, I have configured mirroring with high safety without automatic failover mode. For testing purpose I want to failover between Principal...

Searching for a string in two tables using joins - Hi Team, I have two tables, table 1 : Test1 ColA | Value ---------------------- A 100 Table 2 : Test2 ColA | Value ----------------------- B 200 if COLA =A is available in Table1 then COLA : A Value: 100 else check...

Searching in two tables and add final result to a string - Hi Team, I got exact results from Team for whatever i've posted, Very Very thanks. Here my requirement is : I have two...

sql upgrade - i need to upgrade SQL 2005 to sql 2008 r2 where the db's are configured transactional replication . can any one...

nested query with group by and inner join - select * from geodata.TB_City A inner join (select citydefaultname, CityCountryID, avg(CityLatitude) 'CityLatitude', avg(CityLongitude) 'CityLongitude' from geodata.TB_City group by citydefaultname, CityCountryID) B...

Create Or Replace Function Error - Hello, I'm doing text mining using Oracle SQL Developer: ODMiner.. I imported the data "WEBLOG" into a table.. This weblog data...

A quick and easy way to pull up table names, column names, and specific data structure - This is another one of my fun little tools that I decided to share. There are times that I need...

SQL Server Link to Oracle Inconsistent metadata problem - Hi I have created a linked server to Oracle using the Oracle OLE DB provider "ORAOLEDB.ORACLE" from SQL 2008. I can...

plant count really high-Activity Monitor - Hi I am new to the DBA side, but activity montior shows a high plan clount of 23438 The query that an...

database is not connecting . - Hi All, While connecting to database am getting below error. "A transport-level error has occurred when sending the request to the...

how to shrink logfile ? - Hi All, While running one sp am getting this below error 'The transaction log for database 'testdb' is full. To find...

Operating mode change in mirrroring - I configured mirroring with High safety with automatic failover (synchronous) option. Now I want to change my operating mode to High...

Identity Seeding - Just curious Anyone found a reason to declare an Identity column that wasn't (1,1)? Sure, legacy data can cause you to start...

Backup Overwrite - I have a requirement where i need to take daily backup with datetime stamp , the issue is we have limited...

Cannot change back to Multi User - I changed a database to Single User mode in SSMS and now I cannot change it back to Multi User...

passing multiple value from chart to another report - Hi all, I have SSRS 2008 and currently done a report that shows Length of Stay. X axis shows the months...

SQL Server 2008 : T-SQL (SS2K8)

Confused about count function - Hello, I can't wrap my head around what the below query does when it includes a having count(*) > 1 and...

Interview Questions - Hi..I had an interview and had few questions: Can anyone please help me finding out answers for the below questions? 1) Design...

Technical used of GO statement - Hi all, I am relatively new to SQL Server. I have seen that at the end of various statement we give...

Insert using select - I want to insert data into a temp table from a talbe... I used this query but getting an error Msg...

Convert varchar - is there any way to convert varchar value to TIME datatype?

Collation - How can I change the collation for all columns of a table?

Straight & Reverse Check - kcehc eserveR & Straight - All, I have two table as below. [code="sql"]create table currency ( currencypair varchar(30) not null, rate_value numeric(18,6) null ) insert into currency select 'BNGCZK',null union select 'CHFCLP',null union select 'CHFLKR',null union select 'CHFPEN',null union select 'CHFSIT',null union select...

XQuery get value from node root without child nodes - hi guys, Still trying to get my head around Xquery I have the following logic [code="sql"] SET @CRM = ( SELECT TOP 1 CRM FROM...

Column Name - Hi, I have table which have number of columns one of these columns contains value say 'ABC' I have to find...

Math operation debugging - Hi all, I have a formula that works fine in Excel but not in SQL. Hopefully someone knows of some...

Help with CTE query - Hi, I'm trying to get all the duplicate FKs on the database. I have the query that returns the data but I...

problem inner joininig a derived column - Hi, I'm trying to get a nice chart working, that gets data from SQL Server, but having trouble getting the...

How to use values keyword as parameter of a function - I would like to have a function which accepts any number of parameters with the same type and returns true...

SQL Server 2008 : Working with Oracle

Create Or Replace Function Error - Hello, I'm doing text mining using Oracle SQL Developer: ODMiner.. I imported the data "WEBLOG" into a table.. This weblog data...

SQL Server 2008 : SQL Server Newbies

xp_cmdshell works and then doesn't work - A stored procedure intermittently fails, and it appears to be because xp_cmdshell stops working. If I try it manually, here is...

Why does only one user have access? - If a coworker gets a new computer, they can't access our database from any other user account on the computer...

Precendence Constraint Logical AND/OR Confusion - Hi All, I am wet behind the ears with all things SQL and I'm currently just beginning to study for my...

2 queries on the same row? - i just know this must be a breeze if you have any type of experiance in t-sql, but i im...

combine column is same table - hi, i've 2 tables. table 1 is like [size="2"]col1 col2 col3[/size] [size="3"]x y z[/size] in the other table the items(x, y, z) are like...

unable to connect to sql server - Hi all! im posting this here since i am, in every sense of the word, a newbie. We have 3 MS failover...

SetUp Mail Notifications with Jobs failure - Hi All, To new to know what to do here. Using SQL Server 2008 R2. Setup DB mail and restarted Agent...

SQL Server 2008 : Security (SS2K8)

Login Auditing - I have been asked to provide documentation on where SQL Server displays messages for login auditing. We set the auditing...

Vulnerability in Windows Common Controls Could Allow Remote Code Execution - MS12-060, KB2687441. http://technet.microsoft.com/en-us/security/bulletin/ms12-060 http://support.microsoft.com/kb/2687441#AppliesToProducts I am researching applying this patch and wanted to see if others have installed this. When reading...

VPN access for vendor, multiple instance - Hello I have a request to allow a vendor to have VPN access to my SQL Server that currently has one...

SQL Server 2008 : SQL Server 2008 High Availability

Drop merge repliaction the right way - Hi i am about to drop a merge replication and rebuild it with new snapshot and DB. Replication Server A - B...

ssis package error.......critical - Hi guys, On our prod server cluster we are continuously getting the error, failed to acquire connection, connection may not be...

SQL Server 2008 : SQL Server 2008 Administration

Error, not sure where to start: Attept to fetch logical page... failed. incorrect checkum - i hope this is the right forum :D [code="plain"]i've been going through my sql logs and have noticed that every 10...

instance port number - I have a SQl SERVER that has a default instance and a named instance. Do I have to configure the named...

service pack for analysis service - We have an instance that has only analysis service installed. No database engine installed. How can I find out the version of...

Getting Error when trying to start the SQL Agent - I even tried manually through below command EXEC XP_SERVICECONTROL 'START','SQLSERVERAGENT' StartService() returned error 1053, 'The service did not respond to the start...

Whydoes 'N before the WHERE value causes an Index SCAN - Hello, I am trying to work out why an N' before the value in the WHERE clause would make a...

What Metrics to you monitor on your SQL Server? And now often do you check? - We use SQL Monitor to keep tabs on two of our bigger production servers, but given we have about 12...

Programming : Powershell

Query to variable - not wanting table style result - Hi I am trying to run a script that queries the last full backup file for a specific database. Invoke line...

SQLServerCentral.com : Anything that is NOT about SQL!

For anybody running MySQL - New set of MySQL vulnerabilities been discovered [url]http://www.theregister.co.uk/2012/12/04/mysql_new_vulns/[/url]

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Report Builder Multi-value parameter textbox expression - Hi, I have a distinct list of state-county parameter and a text box that displays this value if selected. How do I...

Error while export report into EXCEL - Hi, I have a SSRS Report. I am getting the following error when i am trying to export to Excel. I am...

Data Warehousing : Integration Services

Error trying to run an SSIS Package - Hi Forum I getting the following error: [b]SSIS Error Code DTA_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit vesion...

Limiting the scope of the SSIS Fuzzy Group Transformation - I'm working on a project for a client that uses the Fuzzy Grouping Transformation component to identify potential duplicate records...

SSIS datediff and dateadd - Hi All, I wanted to find the starting of the week and poulate it as an additional column. Im trying to...

Data Warehousing : Analysis Services

Need Help on MDX Query.. - How can we get running totals using MDX query ? I have grouped by state and now i want the totals...