Stairway to Server-side Tracing - Level 9: How to Automate SQL Traces using the Data Collector

Dan Guzman from SQLServerCentral.com

In this level you will see how to employ the Data Collector feature of SQL Server to automate management of SQL Traces among multiple servers. More »

SQL Server Semantic Search to Find Text in External Files

Additional Articles from MSSQLTips.com

Sometimes it is necessary to search for specific content inside documents stored in a SQL Server database. Is it possible to do this in SQL Server? Can I run T-SQL queries and find content inside Microsoft Word files? Yes, now with SQL Server 2012 you can do a semantic search. More »

From the SQLServerCentral Blogs - Another Stop-Gap Solution for the SQL Server 2012 Identity Crisis

SQLServerSteve from SQLServerCentral.com

                I haven’t seen this many fake ID’s since I was in college.                 Back on April 26, Grant Fritchey (of execution... More »

One of the regular problems that data professionals have to deal with is the whoops disaster, or some type of data entry error. It could be the DBA running a delete without a WHERE clause, or a user updating a bunch of data incorrectly. No matter what the cause, this is a problem that you will deal with multiple times in your career. There are a few tools available that will read the SQL Server transaction log and build reversing transactions, but there are few, mostly because there's just no money in the tools. People don't need the tools often, and when they do, they are likely to just download an evaluation, fix their issue and not purchase the tool or struggle to undo the damage. As an aside, if you are still working with SQL Server 2000, Red Gate has released our Log Rescue tool for free.

With every new version of SQL Server, there's a lot of effort spent on building new features and enhancing old ones. Marketing and sales drive a lot of resource decisions in product development, and that makes a lot of sense. But it doesn't mean that there isn't value in plugging holes inside of the platform and making SQL Server easier to work with.

A log reader tool would not be that hard to build and include in SQL Server. I'm sure Microsoft could build and release limited versions that might deconstruct certain types of statements, or might only read transaction log backups. I bet this would be a great intern project, allowing the very talented youth that spend a semester or two at Microsoft the chance to make an actual impact on the product. Back-ports to previous versions of SQL Server could be done with very little cost to Microsft, provide customers with a valuable tool, and allow college students to prove they are worthy of a future job working on the product.

The ecosystem around Microsoft products is important, both for the growth of the platform as well as filling customers' needs. Log readers, however, are not economical separate products. They ought to be incorporated into the platform, filling a hole that would greatly benefit lots of DBAs and developers who deal with incorrect data updates every day.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 22.3MB WMV
• Watch the iPod Video Podcast - 20.7MB MP4
• Listen to the MP3 Audio Podcast - 4.1MB MP3

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

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

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

Today's Question:

What is the result of this query? The answers reflect the three results in the last query.

declare @Quota1 int, @Quota2 int, @Quota3 int, @Value int;

Set @Value = 20;

select @Quota1 = @Value * 1.05;

select @Quota2 = cast(@Value as real) * 1.05;

Set @Value = 100;

select @Quota3 = cast(@Value as real) * 1.05;

Select @Quota1 * 5, @Quota2 * 5, @Quota3;

By Mike Tanner

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

This question is worth 2 points in this category: CAST. 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 Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

How many duplicate foreign keys will be created on a child table when the following script is executed:

-- Creating parentTbl 
CREATE TABLE parentTbl 
( id INT IDENTITY UNIQUE 
)
-- Creating childTbl 
CREATE TABLE childTbl
( child_id  INT IDENTITY UNIQUE
, parent_id INT   
)

INSERT INTO parentTbl DEFAULT VALUES

INSERT INTO childTbl (parent_id) VALUES (1)

DECLARE @fk VARCHAR(10),
        @i  INT = 1;

-- Infinite while loop to create duplicate foreign keys 
WHILE ( 1 = 1 )   
 BEGIN       
  PRINT @i
  SET @fk='FK' + Cast(@i AS CHAR(5));
  EXEC ('ALTER TABLE childTbl ADD CONSTRAINT '+@fk+'  FOREIGN KEY (parent_id)  REFERENCES parentTbl(id)');
  SET @i+=1;
 END

By Lokesh Vij

Answer: None of the above

Explanation: A table can contain unlimited number of duplicate foreign keys, though the recommended maximum is 253. But 253 is not the correct answer, as the question states how many duplicate keys can be created.

Reference: http://msdn.microsoft.com/en-us/library/ms143432.aspx

» Discuss this question and answer on the forums

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

Get Databases with no backup

Bruce James from SQLServerCentral.com

This script will return databases that have no backup. More »

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

SQL Server 2005 : Administering

Process not releasing tempdb space - Any suggestions on how to check why tempdb space is NOT being released?

SQL Server - Merging Two Tables with PK on both tables - Hello, I had a situation where we have two different DBs with Table-A (on DB1) & Table-B (on DB2). I have to...

Tempdb - monitoring queries - I know there are many different forums out there related to tempdb, but i couldn't find the one which suits...

SQL Parse?? - Hey everyone, Trying to find a bit of help with a project I am working on. Basically, we are trying to...

Clearing out msdb.dbo.sysmail_mailitems... - OK, so we (I) finally noticed that the MSDB backup was rather large. In looking into the problem I found...

SQL Server 2005 : Backups

Differential backups getting smaller. - I'm wondering if anyone can give some advice please - I'm restoring a live backup from Thursday night and will be...

.bak file quadrubles in size with restore - I'm a newbie and I'm using using SQL 2008 R2 I have a 10GB .bak file that when I restore requires...

Restoring from one instance to another... - Hello everyone! I'm playing around today with backup and restore for an SSIS package. My goal is to backup from my...

Backup Failure: Operating system error 64 - Hello, I am having a problem creating backups for a database.  I am using the Backup Task in SSIS and executing...

SQL Server 2005 : Business Intelligence

Data Conversion Error - Hi! I need to load a table of 35 million records on to a flat file .After loading 10 million...

ForEach Loop to delete all files in folder. - Hi... I went to the following URL: [url]http://consultingblogs.emc.com/jamiethomson/archive/2008/04/17/ssis-deleting-a-group-of-files.aspx[/url] for some help on how to configure a For each loop to delete all the...

patitioning monthly basis - Hi am trying to do partitions on monthly basis for every year in my solution. currently am trying it for material...

Dev environment to support 2012 , Poerview, Power Pivot, tabular SSAS - I have been a bit slow to get to grips with some of the new stuff such as power pivot,...

SQL Server 2005 : CLR Integration and Programming.

How does a C# CLR trigger know the table it was called for? - I'm researching to create a C# CLR trigger that implements the auditing for any table in a database. One generic...

SQL Server 2005 : SQL Server 2005 General Discussion

Scaler Variable Error !! :( - Hi Team, I an using the sp_exportdata_n to exporting the data into excel but when i am defining my @sql i...

Barcode 128 generator (using scalar function) - I'm looking for a barcode generating Function that uses the standard Code128. Apparently no function that fixes this has been...

SQL Server 2005 : SQL Server 2005 Strategies

Redesign help - Hi, Ive finally been allowed to redesign some tables which are "wide" (Think Field1, Field2, Field3 ... Fieldn). There are multiple fields...

SQL Server 2005 : SS2K5 Replication

Which setting required to use Pull Subscription option for Replication - Hi All, I want to implement Replication on my SQL server.And i have two different server like server1 and server2 with...

Is Merge Replication replicate uncommitted transaction. - I am using Merge Replication between SQL Server 2005 and SQL Server 2000. Isolation level is read_uncommitted. I ran some script on...

Merge replication always fails. - Asked by: migav Hi! I have a merge replication between 2 sql servers 2005 sp3 that goes up and down. I...

Transactional Replication for Reporting DB Server and Archival of Data - We are planning to have a reporting server where data is replicated using transactional replication from production server. If I...

SQL Server 2005 : SQL Server Express

SQL 2008 SSMS Server Management Studio crashes all the time since last week. - SQL 2008 Express Server Management Studio SSMS crashes all the time with the following message: SSMS - SQL Server Management Studio has...

SQL Server 2005 : SQL Server 2005 Integration Services

IS it possible to have dynamic sql in ole db src where the table is the part that is dynamic? - I am trying to create a for each loop and inside the loop add a dataflow where I pass in...

How to compare current row with previous row - I'm not sure how this can be done in SSIS, but I would like to compare the current row to...

Reg the peer to peer replication topology - Reg the peer to peer replication topology We allready configure to node now we add one more node into existing...

Progress To Sql Server - Hi, I am using SSIS to transfer a Database from progress to Sql server. I am using OpenEdge 10.1B ODBC driver to...

WMI EVENT WATCHER TASK STAYS YELLOW - Hi, I have a WMI Event Watcher task with the below WQL Query. Then task stays yellow even when a file...

SQL Server 2005 : T-SQL (SS2K5)

running sum cursor - Okay, I spent the last 24 hrs looking for a solution...I am totally new to server sql and after 24...

SQL Server 7,2000 : Administration

EXCEPTION_ACCESS_VIOLATION SQL 2000 SP4 BUILD 2282 - So, I'm having an intermittent issue with the following dump being plastered in my SQLLog on my SQL 2000 SP4...

Moving Table to Same Filegroup Different Drive - Hi, One of my filegroups is too large for the drive so I need to move a table. The question is...

Rename Instance of SQL Server - I know how to rename a SQL server, but how do I rename another instance of SQL server 2000. Thanks

SQL Server 7,2000 : General

Should I become a DBA?!? - Hi All, I've been offered a job as the sole MSQL DBA at an up and coming online retailer. I'm from...

SQL Server 2008 : SQL Server 2008 - General

Is it possible to execute an encrypted SQL script with SQLCMD? - What I am trying to achieve: I have a big SQL script, around 20.000 lines, that initializes a new database for...

Fetching data from two tables - Hi All, I'm having 2 tables named "Folders" and "Permissions" I want my result to be all the folders from "Folders" table...

i need how to design Authentication and Roles in my application for Sql Server. - hi i need how to design Authentication an:-)d Roles in my application for Sql Server.

Symantec NetBackups - Hi, I am new to netbackup. I need to check if netbackup is backing up the databases successfully. Where to find...

Retention Period for Queries Executed - Hi Guys, On Fri. 10/19 I had a process that was in a suspended status because it was blocked by another...

User error trying to "Alter to" in 2008 R2 SSMS - A developer with Management Studio gets the attached error when trying to generate the "script table as" --> "Create to"...

SQL connections timing out, except for a few - Came in this morning, SQL cluster down. Switch OS had been upgraded, but didn't come up. Got switch booted, and...

Installing SQL 2008 R2 onto a new PC - We've got new developer machines. They're Windows 7 machines, 64-bit. My current machine started life as a Windows XP machine,...

Parsing out email address in FROM field - Hello, Can someone please help me to trouble-shoot my query? Here is the scenario. I have FROM field in my table...

Ad Hoc Query using SSRS 2008 R2 - I'm looking for a ad hoc reporting tool for our users. Probably the report builder or crystal report would be...

IIS need for to Run SSRS Reports - Dear All, I'm new to SSRS 2008. I created one report using Report Wizard in SSRS. I build that report...

rssbus Quickbooks Connection Error - Hello, I have create a SQL Server Agent job that reads data from the QuickBooks database using the file name and...

JOINing two tables with 2 SELECT Statements - I am joining 2 tables of 30 columns with 5 mil and 50 columns with 10 mil rows. Now, I...

To sharpen the Query Logic - Hello SQL Gurus, I want to sharpen by logics in making efficient SQL queries. Is there any book or any website...

Split without delimiter based on position - I am having a query i need to split the query based on the position i specify Say string is 12345678910111213141516........ i...

Log file Management - I've recently gone through the article about log file management in SQL Server. I was told that in the article,...

SQL blocking report to table - I would like to be able to slice and dice the XML text data - ntext data field type in the...

SQL Developer edition in test environment - Can I use in a test environment the SQL Developer edition? To the test development will have access me (the...

Continuing career with SQL - Hello, I hav just joint this forum. I have to ask few things. I have worked with SQL 2000 and basic 2005.database design. I've...

Crystal report to SSRS conversion..!! - Hi Everyone .. I am working on conversion of Crystal report to SSRS . So anyone has done manually ? please if any...

SSRS 2008 R2 - print problem - What has to be done in Report Properties besides specifying page dimensions (e.g. 8.5 by 11 vs. 11 by 8.5)...

Its possible convert SSRS report developed from VS2008 to VS2005 - Hi all, I development several reports using the VS2008 and now was alerted that the client use the SQL server 2005...

SQL Server 2008 : T-SQL (SS2K8)

Need help for a Query - HI, We a have 2 tables.I need to insert values form DAT_STAGE table to DAT_DES with out duplicate records. I have used...

Permission Denied When Using "WITH EXECUTE AS" - I have a stored procedure that uses "WITH EXECUTE AS" to allow the querying of some system views, the stripped...

Regarding indexed views - Hi all, where sql server indexed view data is stored. for example in oracle materialized view, whenever we create materialized view...

Query Help - Hi guys, Need help in sql query, Let me let you what i need, during the S.P i want to...

how to show single column value (separated by ;) as multiple columns otherwise null - in my student table i have a column called as Subject, it has values as below Student ID Subject 101 EJB;C++;PERL 102 Dotnet 103 Java 104 Oracle;Java;Sql 105 Sql;SSIS i.e., subject column has multiple...

Dynamic SQL to change Recovery Model - Hi all, Looking at changing the recovery model of several DBs on a instance. Thought it would be a good...

why this condition fail (IF 1 <> 1) - Hi, I am confused about belwo statment. Although, Recovery model of TempDB can’t be changed but Why this simple IF...

Kerberos setup for SSAS 2008 R2 - Kerberos setup for SSAS 2008 R2?

Deleting the records without foreign key - i have datawarehouse database where there is only primary key and no foreign keys. I have a condition from one table...

Room occupancy per day given a date range - given data like this: PatientID, StartDate, EndDate 1, 2012-10-15 10:00:00, 2012-10-17 08:59:00 Any suggestions on how to turn that into daily data like...

SQL Server 2008 : SQL Server Newbies

How to select data (two columns) from table based on a composite unique value and insert in another table? - I have a table CustomerMaster and the two fields in it - CustomerID and Source - give me unique values. The table...

select top 1 in subquery returns multiple records - Hi I have two tables: table contact1: obviously contact information table contsupp: a catch-all for a various types of detail, one-to-many per contact we...

Create and populate a Zip file - I have a small program that runs every day from my client machine that creates a new zip folder on...

SQL Server 2008 : Security (SS2K8)

Move logins from one to anothe - I would like to move a database from one server. In this move, I need a script to copy all...

SQL Server 2008 : SQL Server 2008 Administration

High Waits for LCK - Hi All I'm using the following DMV to get an idea of the highest waits on my system: [code="sql"]SELECT TOP 15 wait_type , wait_time_ms...

Index Usage - Hi All When using the sys.dm_db_index_usage_stats DMV to track Index Usage, is the user_updates column just for updates to records in...

automate script needed - i need a automate script to add new sqlinstance name, the script will take the machinename dynamically and we need...

Single User mode for dbs. - I am trying to make a database can only be accessed by myself but not other users for some server...

Question re: service pack upgrade installation when encryption enabled for dbs - Hello. Apologies if this question has been asked before but i am a newbie to the site & forum. I started...

Shrining log file not releasing the unused space - I have a database with almost 116 GB of log file size. Earlier it was showing the available free space to...

Database inrecovery - Hi Experts, One of our VLDB(3TB) is inrecovery state after server restart and from sql log i got the message 2%...

SQL Server Maintenance Plans Failing - I am getting the error below when I setup a job using the Maintenance Plan (SQL 2008 SP2). I have...

Database mail saying remote certificate invalid - Hello Everyone, I have a crushing issue that i have been looking all over the web, a week ago we moved...

SQL Largest tables - Hi All I'm using the built in "Disk Usage by Top Tables Report" in SQL Management Studio to check the tables...

DBA basic - Hi, Where/how do you suggest to start learning about the basics of a DBA please? Thanks

sp_updatestats Versus Update Statistics - Hi, After I've read a couple of articles & suggestions on forums, we decided to change our maintenance plans slightly. Our initial plans...

SQL 2008 R2 Report services won't start - Hi, I have today installed a copy of SQL Server 2008 R2 developer edition on my 64 bit windows 7 PC...

Older Versions of SQL (v6.5, v6.0, v4.2) : Older Versions of SQL (v6.5, v6.0, v4.2)

Need to help to Restore/attach a database in SQL 6.5 - I have the data.Dat and log.dat files, but i find it hard to actually attach it or restore it into...

Programming : General

Is DBA a limited Profession ? - What after several years of DBA , Sr DBA.. Then what ? Just curious to know.... Isnt it DBA a profession which...

SQLServerCentral.com : Anything that is NOT about SQL!

Crazy Interviews - Reading the topic 'Stupid Interviewer Tricks' reminded me of an interview where I couldn't tell if I was crazy or...

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

Reporting Services : Reporting Services

Passing NULL values in a WHERE clause - I thought I had this worked out but when i started testing I was getting more records than expected. What I...

Postnet Barcode In Rdlc - how to generate Postnet Barcode in RDLC Webform based on Zipcode

Data Warehousing : Integration Services

SSIS-one source to 3 destination tables - I have one csv that needs to be distributed to 3 tables using SSIS. I am using the Multicast Transformation...

WMI task and WQL query - Hi guys, I am using a WMI task to watch for files. We get about 20 txt files. when they...

Data Warehousing : Analysis Services

multi level fact table - Hi, I am trying to model an insurance claims process that contains 2 levels. Each claim has a main occurrence number. This...

MDX to Calculate Moving Avarage - Hello everyone, I am a mdx newbie and I neep (please) your Help. I have created a Cube that has 3...

Calculating a percentage across a matrix group - Dear All, I am developing a report in Report Builder 3.0 Thus far I have a matrix which uses Service_Locality, Instance and...

SSAS- - hello I am getting error when i am processing qube on instance . But it works fine on one instance but not...