In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Red Gate logo Refreshing your development databases?
We are looking at ways to make it faster and simpler. See our ideas and leave your comments.
 
SQL Intersection SQL Intersection, April 8 – 11 Las Vegas
Join us for 125 different sessions at SQL Intersection. Attendees may cross over to the sessions of the co-located DevIntersection conference at no extra charge. Use discount code "CENTRAL" to receive $50 off. Book your place today.
 
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

Populating a Vertically Filtered Replicated Table

Vertical filtering of a large replicated table introduces the potential for unwanted transactions to be pushed to the subscriber. This article talks about how you might avoid this. More »


Expand Your Skills at the PASS Business Analytics Conference

Join top business intelligence and analytics experts in Chicago April 10-12 for 60+ sessions on the best practices, prescriptive guidance, and strategic vision you need to get the most from your data. Use code BACSSC to save $250 today! More »


The Power of the SQL Server Database Owner

I have a couple of databases supporting applications. The first application requires that it's user be a member of the db_owner role. The second application actually has to own the database. What are the effects of having such permissions? Check out this tip to learn more. More »


From the SQLServerCentral Blogs - Retrieving the next or previous element node value in an Xml blob using XQuery

It’s been a little while since I wrote a blog post on my favourite subject but this is from a... More »


Editorial - Stop, Smell, Smile

life list This editorial was originally published on Jun 6, 2008. It is being republished as Steve is on vacation.

Recently I saw a post from Chris Pirillo that his parents were in a car accident and it made me stop and think. I've followed Chris for a long time, think he's built a great company at Lockergnome, been inspired by them in my own endeavors here, and hope things are OK with his family.

If you've read these editorials for any length of time, you know that as much as I talk about your careers and IT, I also want you to remember there is a lot more in your life than your career and I think you should remember that.

We work long hours, endure stress, and from what I've seen in IT, most of us do our best at work. That's good, and many of us struggle when work isn't enjoyable. These forums are full of complaints and problems people have had with work that aren't in any way related to technical issues.

Life is a journey, in time and distance, and like any other road humans have built, it's not perfectly straight. It has ups and downs, it bends around obstacles that can't be moved, it blasts through some that it can, and it offers choices.

The important thing is to move in directions that you enjoy. Take the steps to make your life better, whatever those are, and however you can. Every day isn't your last, so move towards your future, but make it the future you want it to be. I can't tell you what those things are, but think about them, write them down, and make them your goals. Build a Life list and start checking things off. And find joy in the things you accomplish, however small they may seem.

Stop, Smell, Smile.

Stop for a minute or two in your life and smell the roses. Think about what's good in your life, wherever that is, and smile. Enjoy those things that you enjoy.

And oh yeah, repeat that often.

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


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.

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


Question of the Day

Today's Question:

/* Using SQL Server 2012 or 2008R2

I create the following two (2) tables 
*/

CREATE TABLE [dbo].[Order](
[OrderID] [int] NOT NULL,
CONSTRAINT [PK_Order_1] PRIMARY KEY CLUSTERED 
([OrderID] ASC)) 

GO

CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] NOT NULL,
[OrderID] [int] NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
([OrderDetailID] ASC))
GO

/* I then create a foreign key constraint */

ALTER TABLE [dbo].[OrderDetail] WITH CHECK 
 ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
   REFERENCES [dbo].[Order] ([OrderID]) ON DELETE CASCADE

/* I then insert data into both tables */

DECLARE @val INT,@val2 INT
SELECT @val= 4 

WHILE @val < 6 
 BEGIN 
  INSERT INTO dbo.[Order] VALUES(@val) 
  SELECT @val2=1
  WHILE @val2 < 10 
   BEGIN 
    INSERT INTO dbo.[OrderDetail] VALUES ((@val*1000)+@val2,@val) 
    SELECT @val2=@val2+1
   END
  SELECT @val=@val+1 
 END

/* I then execute the following T-SQL statement */

DELETE FROM [Order] WHERE ORDERID = 4

The questions are: (Select two answers)

Is the row containing the value of ORDERID = 4 deleted from the Order table?

How many of the rows containing a value of ORDERID = 4 are deleted from the OrderDetail table?

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

This question is worth 1 point in this category: cascade. 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 Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Yesterday's Question of the Day

You are working with an American English SQL Server instance. The full text search thesaurus file needs to be enhanced to include additional terms for your application. You edit the tseng.xml file, remove the comments, include your additions and save it. You reload the thesaurus in SQL Server, but your queries do not seem to be using the thesaurus file.

What could be the problem?

Answer: You edited the wrong language file

Explanation: The problem is that you edited the wrong file. The thesaurus files are encoded as tsxxx.xml, where xxx is the three letter language code. ENG is the code for UK English, while ENU is the code for American English.

Ref: Language Strings - http://msdn.microsoft.com/en-us/library/39cwe7zf%28v=vs.110%29.aspx

» Discuss this question and answer on the forums

 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!


Featured Script

Report Progress with Variables

Script task in my package's OnProgress event handler reports all variable values at each step. 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

Track changes to database objects - I need to supply my manager with a monthly report showing all the database objects that was changed. E.g. stored...

Linked Server Error - There is a Linked Server call to DB2 from inside an Stored proc that fails with the following error : Cannot...

SQL Server 2005 Database Mail - Mail configuration information could not be read from the database. - I'm trying to setup Database Mail and send a test message, but it's not working. I've done several step-by-steps and all...

maxdop doesn't take effect? - I have a SQL 2008R2 server with 16 cores. At the server level i have CTP set to 20 and...

ANSI to UTF-8 conversion - I am running a .bat file that runs a SQL script that extracts data from a SQL Server database and...

4 database instances in a single server (SQL Server 2005) - Hi Guys, I have 4 database instances (2005) running in a single server. How am i able to know how much memory...

an i use same instance on single server for Primary & Secondary database for Log shipping - Hi, I would like to know....Can i use same instance on single server for Primary & Secondary database for Log shipping. I have...

SQL Server 2005 : Backups

Restore Dbs on SQL Server 2012 via SQL Script taking more than 2hrs. - We just upgrade our QA environment to SQL Server 2012 from 2008R2. Our standard process is to backup four databases...

SQL Server 2005 : Business Intelligence

SSAS - I currently use Integration Services and Reporting Services quite extensively to query and populate data from SQL. I've always been...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Server 2005 Database Mail - Mail configuration information could not be read from the database. - I'm trying to setup Database Mail and send a test message, but it's not working. I've done several step-by-steps and all...

Conflicting Version number and Build Number on @@Version - Morning everyone. I installed 2005 (X64) SP3 & 4 on a server over the weekend. An odd thing came up when I...

Creating a format file to import XML data - Hello gurus and geniuses Please forgive me starting yet another thread on this subject. I have searched the forum and read...

Update one column of a table with data from another table - I have two tables which are different with the exception of a single field (column name = "LocationCode"). Both tables can also...

SQL Server 2005 : SQL Server 2005 Performance Tuning

sql pages out before hitting low memory - What can I do to prevent SQL from paging out its memory? Situation: I receive calls about SQL App being slow. event viewer...

sql server 2005 (std edition 32 bits) on windows server 2003 enterprise 32 bits - Hi Guys, I have 8GB RAM on windows server 2003 enterprise 32 bits. From my computer, right click properties I can...

SQL Server 2005 : SQL Server 2005 Integration Services

ftp task - PGP public key - Hi, I am in the process of using a FTP task in my ssis package. This is to retrieve files from a...

SSIS & Environment Variables - I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages. When...

SSIS, "Class Not Registered" error...deployment or permissions issue? - I'm getting an error with an SSIS package that reads from an Access DB on a network share and writes...

ssis and ssrs interview questions - can any one tell some interview questions in reporting service and integration service on sql server 2005 please this is urgent...

SQL Server 2005 : SQL Server Newbies

Bitwise Operator Fundamentals - For the first time in my SQL career, I have come across a bitwise operator and to say I'm completely...

SQL Server 7,2000 : Administration

CAST CONVERT doesn't work - Hello everybody I have th following CAST Convert statement: SELECT CAST(CONVERT(CHAR(8), EZACDT) AS DATE) AS Accounting_Date FROM SYM_SPT_SNI_01.dbo.DBA_MVX_CINACC00 WHERE EZCONO = 2 AND EZDIVI = '001' And get...

SQL Server 2008 : SQL Server 2008 - General

CPU usage - Hi, I ran some DMV queries to get a server's health and came across some questions.. * The signal waits query gave...

Deadlock on update command - I have the following dead lock happening quite frequently at 3-4 times a day. The deadlock seems to happen on...

Case statement - Hi all. I have written a script but in this I am not getting value in a column which is...

Strange issue with one of the four Sharepoint databases that we Log ship to our DR site. - On the server that hosts our Sharepoint databases we are log shipping 4 of them to a sql server instance...

SSMS User Interface Tip to Freeze Panes. - Accidentally discovered this, and thought I should share it, as I don't think I've seen it mentioned before. In Management Studio,...

Update statistics - Hello All, I am facing a load performance issue in one of the tables in our database.Whenever we face this,we abort...

select first alphabet - i have a column in my table which has values like 'value Based', 'value Discount', 'Percentage Discount'. I want to extract...

How to purge Transaction Log - Hi, I tried many thing to purge transaction log as suggested on google,But its size does not reduce. Plz Help.How to do...

reorganize index - Hi Guys, I'm going to reorganize indexes with > 10%. Is there a need to update statistics after the re-org? Understand that if...

ROWLOCK - CREATE TABLE [dbo].[TESTROWLOCK]( [ID] [int] NULL, [NAME] [varchar](100) NULL, [SURNAME] [varchar](200) NULL ) ON [PRIMARY] GO [b]TAB -1 [/b] I m trying BEGIN TRAN SELECT ID FROM TESTROWLOCK...

EXEC sp_MSforeachdb @command - Hi All, Apology i'm quite new to commands. I'm trying to loop the below SQL against all the DBs in the instance. But...

merge statement insert issue - iam using merge statement in my proc.target table have lineid primary key. if i get not matched rescords from source table.below...

existing column Level encryption in sql server2008 - Hi, how to set the column level encyption in sql server 2008 in production enviroment without drop existing column and...

BombProof Data Import from Excel ? - I have a very large spreadsheet of 125,000 rows, 200 columns, some of which contain a lot of text. I...

Differential backups being blocked, CHECKPOINT command - All my differential backups are being blocked by a SPID which has a command of CHECKPOINT. Is this something to be...

Trace - to Capture all or most queries - A. Are black box and defaula traces same? B. Is it possible to capture all the queires in the black/default trace,...

Print error message - Hi, here is the script below: In this script I am matching voucher type and according to that I am inserting...

Alter numeric(12,6) to numeric(19,4) - is it metadata only or is it recreate table? - Would be most grateful for confirmation/infirmation of theory below: Changing an existing table column from NUMERIC (12,6) to NUMERIC (19,4) should...

First and Last number - I have table in which i have data like - Columns are VoucherNo and TransactionID PD0000021 TRN0000003 PD0000022 TRN0000003 PD0000023 TRN0000003 PD0000024 TRN0000003 PD0000025 TRN0000003 PD0000026 TRN0000003 PD0000027 TRN0000003 PD0000028 TRN0000003 PD0000029 TRN0000003 PD0000030 TRN0000003 VB0000001 TRN0000001 VB0000002 TRN0000001 VB0000003 TRN0000001 VB0000004 TRN0000001 VB0000005

Insertion in table - HI, This is the table structure and script below: CREATE TABLE [dbo].[GV_Booklet]( [BookletId] [int] IDENTITY(1,1) NOT NULL, [LeafCount] [nchar](10) NULL, [Denomination] [int] NULL, [VoucherTypeId] [int] NOT...

Best Approach to Archieve DB - Example - We have a DB which have loads of data & we want to archiev it to some Server in such...

SQL architecture - Can any one explain the SQL architecture

Data not inserting - Hi i have written a query to check the insertion but its not working neither getting any error : Declare @valuebased...

how to combine and convert two integer columns to datetime - I have date stored in a column as integer type and time stored in a column as integer type. How...

can we install 2008 R2 integration services and 2012 integration services on the same server - Hi DBA'S I had 2012 integration services installed on a server. i am trying to connect to Oracle but it is...

How to add below complex conditions in SQL script.. - [size="2"][font="Courier New"]Hi All, Below is my written query DECLARE @FromDate DATETIME DECLARE @EndDate DATETIME SET @FromDate = '2013-01-01 00:00:00.000' SET @EndDate = '2013-02-13 00:00:00.000' SELECT DISTINCT year(sd.FKDAT) As YEARWISE_DATA, so.vkbur...

SSRS 2008R2 showing counties (not just states) - I have a hopefully simple question. SSRS 2008R2 can do maps by state just fine... is there an easy way...

sp_updatestats and the default sampling rate - Using SQL Server 2008R2 As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue...

Primary key or not to primary key, that is the question - Yeah, bad taste on the subject but nonetheless, you're reading my post :). I'd like to pose a scenario and see...

Stored Procedure execution with SQL Agent vs Management Studio - I'm running two stored procedures that basically copy data from one database into another. When I run them using Management...

Few tricky Questions about SQL Server 2008 that I came across... - Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of...

The log for database is not available; Database was shutdown due to error 9001 in routine 'XdesRMFull::Commit'. - Testing and Production db's have lost connection to the translog 2 days in a row now. Today, my prod db...

2008 Central Management Server - Schedule Multi Server Query? - Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL...

SQL Server 2008 : T-SQL (SS2K8)

Help with the query! - I came across a situation as follow [code="sql"] CREATE TABLE DAYOFWEEK(WeekID INT IDENTITY,DAY NVARCHAR(50)) CREATE TABLE SubjectToStudy(WeekID INT,SUBJECT NVARCHAR(50)) INSERT INTO DAYOFWEEK VALUES('MONDAY') INSERT INTO...

How can I create intervals fro start-dates only - Let's assume we have 1) A key value, like a product number 2) and a ValidFrom date, like this MyProductKey, 20100111 MyProductKey, 20110205 MyProductKey, 20120101 What...

Ranking functions, views, and predicates - So, I ran into a nifty little "bug" I'll call it. From what I can find this was an issue...

BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'. - DECLARE @Cmd VarChar(4000) DECLARE @SProcName VarChar(40) DECLARE @SP_Parm1Value VarChar(40) DECLARE @SP_Parm2Value VarChar(4000) set @SProcName = 'Rpt_JobTransactionsSp' set @SP_Parm2Value = '|SRMQDIC|,|ROD|,|B|,|HNS|,|0|,|B|,null,null,null,null,|000|,|9999|,|1/1/2013|,|3/17/2013|,null,null,null,null,null,null,null,null,|T|,null,null,|1|,|0|,|1033|' set @SP_Parm2Val

For Learning - I want to improve my DB Knowledge , i have experience in MS-SQL and ORACLE. My Problem is when i face any...

Sql Query - I have to return one result set by writing two different logic. Both of the logic are very complex.. one...

Stop large queries from running - Hi, I have a query window with a lot of code but I do not want to run it entirely in...

problem in select command - hi please look at this select command : [Code][b]select top 2 * from dbo.NFFeeds order by id desc [/b][/code] for my database the...

Custom sp_who - Hello All, Couldn't find anything that addresses this specifically, so here goes. I have a group that I want to allow to...

select rows into colums - Dear T-sqlérs, I have a test table (see script below) with the following result name length balk1 7 balk1 6 balk1 9 stof1 6 stof2 6 stof3 6 stof4 6 stof5 6 stof5 7 stof6 6 stof7 6 stof8 6 stof9 6 stof9 7 stof10 6 stof11 6 stof12 6 Now I would like the result to be...

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. - I have the following XML statement, where I want the union of several tables to be presented as XML. I get...

SQL Server 2008 : SQL Server Newbies

a query like factorial - Hi. I ask you for help because its more than 2 days im thinking about this question. Imagine we have a table...

I wish to compose Normalised tables for the below requirment Please Help. - There is requirement as below to create the Data Model for estimation in No of tables as of now Openings Resumes Available...

Obtaining Before & After Record & Missing Record. - I have a database that has two tables. On table is a date table that has all of the possible...

Backup databases depending on the size to multiple files - DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate...

Do Users based on logins in master have access to other databases? - When I create a user and select master as the default database does that mean or imply that user can...

New Database Request Form - Hi guys, Just wondering if anyone has created a new SQL Server Database Request Form? What I'm looking for is a...

SQL Server 2008 : Security (SS2K8)

users not able to access database - Hi Friends, We are facing an issue on one of our servers. We have some domain users in our database. These...

SQL Server 2008 : SQL Server 2008 High Availability

Cluster setup--system databases - We created a single node cluster and installed sql server failover cluster on it. Plan is to move LUNS and...

Sql Server 2008 r2 active/active configuration - Hello guys, Please can anybody point in right direction of implementing sql server failover clustering in active/active mode. There are alot of...

Failed Drive on Mirrored Server - Hi, We recently lost a drive the contained the transaction logs for a mirrored server. The principal databases responded to this...

SQL Server 2008 : SQL Server 2008 Administration

sql not finishing or stuck - I'm on SQL 2008 10.0.4371 with 16 cpus and 24 gigs of RAM. Under some condition, I start getting increasing...

SP3 upgrade on SQL 2008 issue - Hi, Recently we tried upgrading SQL 2008 cluster instance from SP2 to SP3. After patching done on passive node, we tried...

FileStream backup and restore - We're using MS SQLServer 2008 R2 and we are exploring the possibility of using FILESTREAM to store in a separate...

Log File Shrink - Now that we got the log file backups under control, now it's time to shrink the log file. I'm going...

SQL 08R2 SP2 - did not update!! - Hello SQL peeps, I installed SQL 08R2 SP2 last week on about 20 servers however one did not update. (All...

Problem with Database mail - We have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it...

SSIS ForEach Loop Container - loop through all DB's - I am trying to get the FELC to loop through all the databases for my sql server. I want to...

Programming : General

A tricky one - Hi I have a table of links to images. I have an identity key for a particular image but i want...

Programming : Service Broker

SQL Server 2005 Database Mail - Mail configuration information could not be read from the database. - I'm trying to setup Database Mail and send a test message, but it's not working. I've done several step-by-steps and all...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Changing Field Value With Parameter - Hi, I'd like to change a field value in my main data source query using a parameter. I cannot figure this...

date parameter issue - Hi I have created a parameter with a called MonthEnding that has a datatype Date/Time. In my report parameter properties i...

Reporting Services : Reporting Services 2005 Development

Special Characters - HI All, Can anyone help me in skipping the special characters in the Result set. column value For ex. If the actual...

Database Design : Design Ideas and Questions

Interactive webcast for seminars - When a seminar nevertheless takes place, it is relatively easy to make available the seminar for many more. In most...

Building my first data warehouse. Advice welcomed :) - Hi all, I've been brought on board to help a company start to build their first business intelligence setup. I'm a...

Data Warehousing : Integration Services

Share SSIS package with sister office - What is the best way to share an SSIS package? I have a sister office who receives all the same...

SSIS - Automatic Login to a website - Hi All, I havent been able to find a starting point or solution when searching the internet for my scenario which...

Couldn't apply special formats for the data.This is the erroe i receive while i try to do it.. - Hi, I am trying to apply special formats attributes available in excel to my target file which is excel too. The process...

Data Warehousing : Analysis Services

trying to make my first report and it is not going :( - Hi, I am new here, I have a question, unfortunately every time I solve it on my own I keep mixing...