In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Just released! SQL Backup Pro 7.2
Scheduling backup,verification and restore just got easier. Check out the latest features in SQL Backup Pro 7.2. for faster, smaller backups. Get a free trial.
 
Deployment Manager NEW! Deployment Manager Early Access Release
Deploy SQL Server changes and .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try the Early Access Release to get a 20% discount on Version 1. Download the Early Access Release.
 
sqlmonitor SQL Monitor v3 is even more powerful
Use custom metrics to monitor and alert on data that's most important for your environment, easily imported from our custom metrics site. Find out more.

In This Issue

A Versioning system for SSRS

Use SSRS to be create its own lightweight report version control system More »


Free eBook: SQL Server Backup and Restore

You can download a free eBook from SQLServerCentral and Red Gate software on the most important task a SQL Server DBA or developer needs to understand. More »


Tune Your Indexing Strategy with SQL Server DMVs

SQL Server Indexes need to be effective. It is wrong to have too few or too many. The ones you create must ensure that the workload reads the data quickly with a minimum of I/O. As well as a sound knowledge of the way that relational databases work, it helps to be familiar with the Dynamic Management Objects that are there to assist with your indexing strategy. More »


From the SQLServerCentral Blogs - MDS: Copying models and data to another server

Having recently completed a model in Master Data Services (MDS), I needed to move the model from a development environment... More »


Editorial - Walk on the Wild Side

In my career, I've worked in a variety of environments and positions. I've been a developer, and a production DBA, sometimes at the same time. Many of us aren't working in environments where we are strictly performing one job. We often flex our work to meet the needs of our organizations, doing whatever is needed to get the job done.

However many of us spend most of our time either producing software or supporting it, sometimes separated from the other side by internal rules or even regulatory requirements. Those of us that are successful usually learn to make friends with the people doing the other kind of job and these relationships allow us to bend rules and regulations to get things done quickly. That can be a good thing, but it often doesn't really give us any appreciation for the job that others do on a regular basis.

In a few large companies I've worked in, management felt it was important that the developers or engineers spent some time in the operations area. People would rotate out of their area for a month and spend time working in another area, gaining skills that might allow them to provide support in an emergency, but more as a way of helping them understand the impact of their work and how they might go back to their job with a new perspective in the future. The ultimate goal was for design and development to proceed more efficiently and at a higher quality level. It worked well at times, but often failed if the developer didn't embrace the temporary assignment with a positive attitude.

I've rarely seen administrators sent to work with development groups. Usually an administrator has to have an interest in development and is interested in making a career change. However I think that there is value from giving administrators some exposure to the development process. They might realize how complex and tedious it can be, how seemingly obvious problems slip through, and perhaps most important, they might learn how to better troubleshoot issues in production systems.

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

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

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

This question is worth 1 point in this category: Foreign Keys (FK). 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

CREATE TABLE IdentTest 
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
)

DBCC CHECKIDENT ('IdentTest',RESEED,100)

INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest

What is the value of Ident?

Answer: 100

Explanation: The RESEED parameter of DBCC CHECKIDENT let's you change the current column value for an identity column. In this particular case the output was: Checking identity information: current identity value 'NULL', current column value '100'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Ref: DBCC CHECKIDENT - http://msdn.microsoft.com/en-us/library/ms176057.aspx The next value is the value after the "current column value", based on the increment of course. Here is the 2012 definition of DBCC CHECKIDENT (it doesn't appear to have bee changed at least since 2000). http://msdn.microsoft.com/en-us/library/ms176057.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



Featured Script

Find size of all tables in a database

Script lists the size of all tables in a database. 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

Reading a string from windows command variable - can we use output of windows variable in SQL query

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

Loading Windows Application Logs to SQL Server 2012 - Hello, Could someone help me the best way to load Windows Application/System logs to SQL Server DB. Given we have application...

'DBCC' is not recognized as an internal or external command - Hi, Can anyone tell me what am i missing if i tried to test my script on the command prompt and...

how to get most queried db in the ms sql db server - Hello All.. My boss asked me to give the list of top 5 most queried database in our db server(Ms Sql...

SQL Server 2005 : Backups

Clearing SQL 2005/20008 TempDB - We have an alert that allows us to monitor the system TempDB on SQL 2005 and 2008 instances. The monitor...

SQL Server 2005 : Business Intelligence

Notify icon Script Task help...!!! help...!!! - My requirement is to show a balloon as notify icon tool tip or a message or content of a variable...

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

Convert SSRS 2008 to SSRS 2005 - I wondering if have any way possible to convert reports built in SSRS 2008 and open it on SSRS 2005...

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

Service Broker - In our environments, I have identified a problem with Service Broker and performance. We have a rows north of 50...

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

Delete Data from Publisher Without Replicating to Subscribers - Hi all, I have a requirement to archive a large volume of data from a table (T1) within my published database....

SQL Server 2005 : SQL Server 2005 Performance Tuning

Units of Process\Working Set counter? - I'm collecting the [b]Process(sqlserv)\Working Set [/b]counter on one of my servers and getting values like this: 27418284032 What units is this in?...

SQL Server 2005 : SQL Server 2005 Integration Services

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

SQL Server 2005 : T-SQL (SS2K5)

NOT operator and Index Use - Hi all, Very basic question. I was reading the MCTS 70-433 training kit book from Microsoft, and fairly early on, they...

Remove decimal places and force leading zeros - I have output for scores that have 2 decimal places (all zeros). I need to remove the decimal places and...

Find sub-tree nodes - Hi, I have a tree structure represented by a simple table that references itself: [code] CREATE TABLE [dbo].[AframeGroup] ( [GroupID] [int] IDENTITY (1, 1)...

SQL Server 2005 : SQL Server Newbies

Adding linked records - I need to add records to a DB in SQL Server 2005. Each composite record represents a project in the table...

SQL Server 7,2000 : Administration

DBCC shrinkfile error - Msg 8985 - Hi I am getting following error when I try to shrink the transaction log: DBCC SHRINKFILE (wslogdb50_Log, 2000) Server: Msg 8985, Level 16,...

SQL Server 7,2000 : General

Import data into multiples tables or one? - I'm building an import package for a CSV file that has about 200 columns. If every column was maxed out...

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

Good reference for Extended Events? - Does anyone have a good reference for setting up extended events and how to do it to get the info...

How to convert the integer value to datetime - hi i am new to sql and i have the following problem i am using the following query : select cast (1157068800...

here i just want update the indexnumber of a question table after deleting some question from that table ? - here i am having table @question which is having foreign key questionbank datatype uniqueidentifier this is the table [code="sql"] DECLARE @question table ( questionid...

Join - Hi, I have two tables employee(employeeId,LocationID) and employeelocation(employeeId,Locationcode). Both these tables exists in doff database. LocationCode in EmployeeLocation table has char values...

Extract text which is between 1st and 2nd comma - [font="Verdana"]I want to select text which is between 1st and 2nd comma on SQL Server 2005 for Ex: 1: SHOP NO 66/67[b],...

Changing setting for first day of the week - Hi there, I am having some trouble with some SQL Job schedules because I believe SQL is using Sunday as the...

How to archive records with cyclic relationships - In my application a single "Shipment" can include "OrderItems" from multiple "Orders" This creates a cyclic relationship in the database: OrderItem belongs...

Filegroups and files affecting database performance - survey - Dear All, I have asked on more question of survey-kind on this forum, and I was very pleasant of your answers. We...

SQL Server 2008 with 2000 - Hello everyone, I have recently joined Ms Products, we have a Database server in SQL Server 2000(located in X computer)....

Asking login Credentials in Reports server - Dear All, I installed the Sql Server 2008 in Mixed Mode and default configuration for Reporting services,Analysis services also. Then...

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

Estimate Backup Size before backup strat - Experts, I am new to SQL Server,i have requirement to run backup using stored procedure. I am using a stored procedure to...

SSRS linked server to MySQL parameter problems - In SSRS 2008, I have a linked server setup to SugarCRM using the 64 bit Mysql ODBC connector. Openquery is...

Writable Cached Table - Hi, I am trying to build multiple neural networks in sql. I'm doing this by looping through rows in a table...

Function - Spilt Columns into multiple rows - Hi I have a table which has columns containing sales values for each month for example.. Id, versionId, jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun The 'versionId' joins to...

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

Qusestion on delete job - Hello, I have a SQL job that deletes 10,000 records in batches of 1000 rows for every 4 minutes. There...

sp_columns performance change - I’m trying to fix a performance issue and can’t seem to get anywhere. During our daily busy period, our CPU...

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 job error - NT AUTHORITY\NETWORK SERVICE. Login failed - hello , I have transferred a job from one server to another and I get this error when trying to execute...

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

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)

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

how to get count of combination for multiple rows - Hi My application need at least 2 approval for an request. I need to generate a report to count which combinations are...

many to many relations - I have one table and the columns are bookingreference,complaintreference,category1,category2,desriptionofthecomplaint, one bookingref have one complaintid, one complaintid have many bookingreferences, one complaintid have many...

Identity column and the seed - I have seen a strang thing about the identity column of seed, and cannot find why, please help. My situation is...

Dynamic Footers, without dynamic SQL - Hello All, I have a bit of a 'is it possible without dynamic SQL' question. I have a table of Footers. I...

SQL Question - Some body ask this question during interview. I didn't the answer i would like to know if someone give me...

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

How do I use one column for node names and the others for elements in that node? - I'm trying to query some data with FOR XML to output the XML. Let's say I have data like this...

SQL Server 2008 : SQL Server Newbies

Adding a composite record to 2 tables - I need to add records to a DB on SQL Server 2005. Each composite record represents a project in the table...

Trying to get started with Full Text Searching - This is all new to me, we normally don't do much programming here and I've not had much luck getting...

Grant Permission to a table with restriction to data - Is it possible to grant access to specific columns in a table with restriction to the data with a where...

SSIS failure constraints - hello all... i have created an SSIS package which works beautifully until i come to the final task, which is sending...

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)

Cannot Generate SSPI Context with AD account - I have several SQL 2K8R2 servers in "the cloud"; most work correctly. One is giving me problems. On that one (which...

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 High Availability

Change of role between Primary and Secondary Database - We need to have a database in two SQL Servers one at Mumbai and another at Houston and both the...

Clustering on SQL Standard Edition vs Enterprise Edition - Is their any difference in clustering -- performance, setup admin etc between having a 2 Node Cluster using SQL 2008 Std...

Mirror Monitoring issue - I have setup mirroring on a pair of SQL 2008 R2 servers. This appears to be working ok when I...

dbcc shrinkfile - Does dbcc shrinkfile (filename, emptyfile) move the data to other file in the same filegroup thru online or the data...

SQL Server 2008 : SQL Server 2008 Administration

Upgrade replicated dbs to 2008 R2 - Hi, we have 3 servers that are SQL Server 2008. 2 servers are the publishers, and 1 server is the subscriber of...

Please suggestion program for copy database files. - Dear All We have problem about copy database we have backup database 11 files and data size is 90 GB every...

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

Email after DB restore completion - Hi, I have a DB that gets restored from from different server everyday. Once the database restore is completed, I...

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

Database Mail Error - This is a recent install of SQL Server 2008 R2 in a Windows Server 2008 R2 64 bit environment (which...

Idera Tools - Web Resources / Support Forums? - Hi, At my previous company we were using various Red Gate tools and I found the resources on the website and...

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

Career : Certification

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

SQLServerCentral.com : Anything that is NOT about SQL!

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

From 2 different sources, can we include columns from 2 datsets and get an ssrs report? - can we include columns from two data sets to 1 SSRS report? one data set is from table and one data...

chart x-axis. Separate label format plz help - Hello everyone, I want to do a simple thing which costed me a lot of time already :-) View the following chart: [img]http://i.imgur.com/kTZ9t.png[/img] For...

Reportin services - AVG of avg - Hello, I have question, how to sum rows. I create query =avg(iif(filds!urgency.value=1 and filds!_time<=14,1,iif(filds!urgency.value=2 and filds!_time<=130,1,0)), everything is ok, but I...

Open microsoft outlook by clicking field on SSRS report - I know this is possible in Business Objects, maybe it is here too, but I cannot seem to figure it...

Data Warehousing : Integration Services

Job fails after changing IP address - There is a package imports data from MySQL server to SQL Server. The Package connects to the linux server through...

Problem to create a CONNECTION MANAGER inside a SSIS - Hi to all. I currently use SSIS of SQL SERVER 2008 R2 to create data flow to import data from several...

SSIS vs SSMS ! - Here's a more theoretical question for you, to make change from all that hard code stuff. It all started when someone...

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

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

How to give the customer to access SSAS Cube's? - Am a beginning stage of SSAS , Based on the sample requirements, I have created the Cubes and dimensions, Now the...

Example SCD type 2 - Hi, Has anybody an example of how to use Slowly Changing Dimensions type 2 in SSAS? There are lots of examples of...

Article Discussions by Author : Discuss content posted by Perry Whittle

TDE and SQL network force encryption - Hello, I am a bit confuse about TDE and SQL network force encryption implementation with respect to which certificate to use....