A community of more than 1,500,000 database professionals and growing

Stairway to XML: Level 6 - Inserting Data into an XML Instance

Rob Sheldon from SQLServerCentral.com

The modify method lets you manipulate XML data using XML DML. It can insert, alter or delete data. In this level, Robert shows how to use the method to insert a node into an XML instance.  More »

Report Builder 3.0: Adding Charts to Your Report

Additional Articles from SimpleTalk

Charts are one of the commonest ways of visualizing reports from data. Report Builder provides a way of generating charts and reports that will be intuitive to anyone who has done the same in Excel. Robert Sheldon provides a simple explanation of how to get the best from charts using Report Builder. More »

From the SQLServerCentral Blogs - Auditing changes in Merge Replication

Brandon J Williams from SQLServerCentral.com

One trick I have learned from the folks on the Replication Support Team is how to proactively audit data changes... More »

From the SQLServerCentral Blogs - Why Tune Queries?

Grant Fritchey from SQLServerCentral.com

It’s just a query against the database. If things are running slow, buy some more memory, a faster CPU or... More »

More and more SQL Server instances are being deployed all the time. In fact, with the ease with which we can build a new virtual machine (VM) through snapshotting and cloning, it seems that many administrators are finding that the number of servers for which they are responsible might be doubling or tripling.

Even moving to the cloud doesn't completely remove the need for some administration of your data and databases, though it does require you to rework the type of administration that you perform. I foresee more hybrid solutions over time, which will require DBAs to not only manage data, but help analyze the financial impacts of moving data (and analysis) to, or back from, the cloud.

In SQL Server 2008 we had the chance to begin managing our servers through a set of declared rules with  Policy Based Management (PBM). I haven't seen that feature take off, and it seems relatively few people are using PBM to manage their servers. I think it's a great platform for ensuring that your instances are conforming to certain rules, though I think there is a bit of creativity needed to ensure that this system works well for you.

Powershell is becoming integrated into all Microsoft products. Virtually everything in SQL Server, perhaps even every thing by now, can be managed through Powershell scripts that access the SMO objects. I hear various people say that Powershell is a critical skill for DBAs of the future. I'm not sure of that, but I do think it will be used more and more if you have the need to perform repeated actions on multiple servers. Whether you use it now or not, it doesn't hurt to learn how it works and what it can do for you. 

It just might be the tool to make your job easier as you get more and more instances to manage, something that seems to happen more and more.

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 - 19.0MB WMV
• Watch the iPod Video Podcast - 18.5MB MP4
• Listen to the MP3 Audio Podcast - 3.8MB 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 value is returned?

Declare @a varchar(100)

select @a = null

select @a = isnull(coalesce(@a,coalesce('NULL','NA')),'Pass')+'Fail'

select @a

By SKR V

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

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

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

What list determines the words that are ignored in full text searches in SQL Server 2008/SQL Server 2008 R2/SQL Server 2012?

By Steve Jones

Answer: StopList

Explanation: Prior to SQL Server 2008, the words ignored by full text search were called noise words. In SQL Server 2008 and later, this has been changed to the stopwords, which are stored in the stoplist.

Ref: Configure and Manage Stopwords and Stoplists for Full-Text Search - http://msdn.microsoft.com/en-us/library/ms142551

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

Port check for SQL Server connectivity

Michael Lascuola from SQLServerCentral.com

Generates a script to check connectivity between a list of SQL Server instances on a specified port. More »

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

SQL Server 2005 : Administering

monitor server health during load test - Hi, Our application is developed using Java. Now our QA wants to do a load test on application , as it will...

Types of DBA's ??? - Can any of you gentlemen tell me totally how many types of DBA's exists for an organization or overall Administrative...

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

Unable to deallocate kept page - DBCC CHECKDB - Hello all, Received a call this morning that the database maintenance plan on a server is failing on it's Check Database...

Small database with 1 million users - Hi all, We've a small database (1 GB) and SQL 2005 server is clustered one. Now Business is forecasting that database...

SQL Server Agent and sa Password Change - SQL 2000, 2005, 2008 - Many instances - I am changing the sa password. Some posts seem to say that there isn't any...

how to giver users access to viewing JOB Activity Monitor - I want to give access to some users to view the job activity monitor. Now I know there is the...

No full text languages - Hi, The following select select*fromsys.fulltext_languages retnurns me no rows. I have Windows XP x64 and SQL 2005 Developer version 9.00.2047.00 (X64). I...

SQL Server 2005 : Backups

Any Alternate to back up and Restore - I have Db almost 700GB which we daily restore on Production after making back up on build server. As the...

backup background process - when backup is taken can anyone tell me as to what exactly happens in the background

SQL Server 2005 : Business Intelligence

Converting Stored Procedures to SSIS packages - Hi All, We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps: 1....

SQL Server 2005 : Development

Reset the Number by Column Value using ROW_NUMBER() OVER (PARTITION BY Field) - Hi Guys Need your help [code="sql"] /* SELECT IDENTITY(BIGINT ,1 ,1) AS RowID ,CAST( '' AS DATETIME) AS TodaysDate ,CAST( '' AS BIGINT) AS RowNumber ,CAST( '' AS...

SQL Server 2005 : SQL Server 2005 General Discussion

confusion in setting up superkey - Dear Sir, I am in great trouble, my problem is I have a vendor Table which consist of some fields. I explaining...

SQL Server 2005 : SQL Server 2005 Security

How Can A File be Totaly Removed from an SQL Server Database - Hi Folks My administrators have asked how does sql server handle the "Total" removal of a file from a SharePoint SQL...

sql2005/sa - A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory...

my application user asking to give access to execute a storedprocedure in any database it it possible? if so how - Hi all one of my application user asking to give access to execute a storedprocedure where ever he wants to execute...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Proc Cache Question - Hi, Background... SQL Server 2005 sp2. Mirrored with witness Problem Every few days queries start getting slower The only way to speed them up again...

Client application losing connectivity with database/server - Hello, We are having an issue with our financial application client where users are being kicked out and seemingly random times...

Explicit Transaction to improve performance? - [font="Verdana"]Hi All, I have one Stored Procedure which frequently gets executed. Though it is a straight forward Stored Procedure with one...

SQL Server 2005 : SQL Server 2005 Integration Services

Moving files from one folder to another folder - Hi, I have one folder with two files C:\Pkg\28052012_sample.xlsx C:\Pkg\29052012_sample.xlsx Using SSIS 2005 i have loaded the data from the above xlsx file using...

Need to get particular date date from PROD to Dev tables - Hi friends, I Need to get particular date(eg: todays date) date from PROD to Dev tables.where the tables already exist...

An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "Failure creating file.". - Hi Everyone, I need urgent help on this one. I am running an SSIS package for 32 bit on a 64...

SQL Server 2005 : SQL Server Newbies

Test - Test Post

Barcodes Code128 generator function - I'm looking for a [b]barcode generating Function[/b] (in SQL 2005), that uses the standard [u]Code128[/u]. The result of calling the...

SQL Server 7,2000 : General

Bizarre issue while using datetime variable instead of harcoded value - Hi, I am facing a bizarre issue while testing a query, When i test with hardcoded date value, the query...

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

Index Scan vs Index Seek - What is the difference between index seek and index scan?

SQL Server 7,2000 : T-SQL

trigger to write to two tables - Thanks in advance for any help with this! I have a trigger that is successfully writing data to two different tables:...

SQL Server 2008 : SQL Server 2008 - General

time in query execution - I have a scenario in which I have a table citymaster(cityid,cityname,stateid) which needs to be getupdate from statemaster (stateid,statename,statecode). I...

TRANSACTION and @@IDENDITY - Consider something this: If, within a transaction we insert a row into a table (say Orders) containing an Identity column, and...

Output who invoked a SQL Job - Hi all, Is there a way I could output the username of whoever invoked a SQL job each time it runs?...

Commit, truncate of log file and checkpoint in SQL Server - .

Create select statement for the query - Hi, I have a requirement to create a CASE statement for the following condition: SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is...

how to add data to two table - i want to add three table to the datbase out of which two are from one table and one is...

Best practice - SSIS package question - Consider the following SQL Server setup: Trusted Domain: Server 1: Utility Server Server 2: Instance1 (5 Databases) server 3: Instance 2 (10 databases) When I...

Database Shrink - We've recently archived a massive database and we want to shrink it to recover disk space. I know shrinking is not...

Database Restore Problem - Hi Gurus, We've a DR server which is using VM machine, running Windows 2008 R2 (Standard). Had configured MS SQL Server...

If anyone can help i am the most happiest person in world - i have a table like [u]TID[/u] [u]PID[/u] [u]DSC[/u] 1 0 Parent1 2 1 Child1 3 1 Child2 4 1 Child3 5 0 ParentA

Removing non-alphanumeric characters from a column using tally tables - I have a table TXNS Column A is int (3) Column B is varchar 10 I wish to remove all non-alphanumeric characters from...

Timeout expired, Error: -2 - Hi, source server and destination server have same SQL Version - SQL2K8 R2 b3 bit, and SP2 - 10.50.4000.0 Two servers are located to...

Load CSV file into temptable - Hi Experts, can some one share script which loads data into temptable from csv file.?

Exclude all but date characters in SQL 2008 - I have a data clumn (A) stored as Text and contains all sorts of codes and also includes date stored...

Restoring SQL2008 Enterprise Edition db into SQL2008 Standard edition question - A vendor of ours is going to give us a database that is in SQL2008 Enterprise Edition for us to...

Active Directory Groups - Excuse my ignorance, but I'm fairly new to SQL and Visual Studio. I'm creating a VB app that allows users...

upgrading sql 2000 to sql 2008 will make any problem to my database ? - hi, i am running sql 2000 now my company has decided to go for sql server 2008. i have many tables,indexes,functions,procedures...

Run time of Cursor - hi all i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it...

using dateadd function - Hi All I'm new @ SQL server. i need to select record that are within 30 days of this given date...

How to select the inherited values in hierarchical data structure is efficient way - I have hierarchical data structure with [State] as top level, [Region] as intermediate level and [Branch] as leaf level. Region can...

Diagram Pane in Design View not showing columns - When creating a new view with the view designer from SSMS, if I type a table name in the SQL...

How do I join tables, but select top 1 from 1-many tbl? - Essentially I have a Contact table and a History table. I'd like to get the join the tables, but only show...

UDF Performance When Used As A "Macro" - My group is having a discussion as to performance hits when a UDF is used as a macro - IE it...

SQL 2008 R2 Standard Edition license requirements for a Virtual Server - Hi All, I've read several things on the web today and got myself totally confused. Can someone explain to me how I...

Columns not in key look up, why? - Hey all, We came across a problem today, no solutions yet, here is the scene, we have a query which uses...

Recursive function - sql2008 - Hi, I am new to sql server 2008 functions, I am facing an issue and request, if some can help. I have...

Physical and Logical design of the database - I am working in SQL from a long time however at times I find it little difficult to provide someone...

SQLWEP errors in Windows Event log - I'm seeing the following errors in my Windows Application event log: Cannot create the event notification 'SQLWEP_CE492A26_8D18_48CA_B4A0_0221AB3AD1DD', because you do not...

Migrate Sql 2000 Enterprice SP3 To SQL 2008 Std, Side By Side - Hi I am starting "the" upgrade and have found out that the server is only on SP3. I have only one...

SQL Server 2008 : T-SQL (SS2K8)

Read TRN Data - Hi Gurus, We've configured MS SQL Server 2008 R2 and restored the recent backup of PRD database (.bak file) into our...

Get the average of the data in the table. - Team, I have a set of data for perticular day.Plesae help me in writing a sql query to get the...

Combine different rows based on sequence - Hello All, I have some really ugly data coming in from several files. I am using SSIS to import into a...

replace - I have 2 columns in the database. The value in column 1 is 10000000. The value in column 2 is...

Flag field update and insert vs all columns update vs delete and insert - Hi All I have a data warehousing scenario related to Updates and SCD. In our ETL procedure, we currently update all...

TSQL to join two tables to calculate the total based on the date range - Hi I have 2 tables Rates and Records. RATES table country fromdate todate costpersec ======================================================================= USA 2012-10-10 00:00:00.000 2012-10-14 23:59:59.000 0.01 USA 2012-10-15 00:00:00.000...

error - incorrect syntax near '4' - CREATE PROCEDURE usp_update @pcid int ,@ppid int ,@ptid int ,@pqty int AS BEGIN SET NOCOUNT ON; DECLARE @Rate int SET @Rate = (select prate from M_PRDT where PID=@ppid) IF EXISTS...

Avg count of cases per day - Hello everyone. I am looking to produce a result set from a helpdesk application that tells me the average number...

Determine unique combinations of permissions assigned to users - I need to determine the unique combinations of group permissions assigned to users. Currently, permissions are assigned to a user....

SQL Server 2008 : SQL Server Newbies

QUERY taking too much time in execution - I have a scenario in which I have a table citymaster(cityid,cityname,stateid) which needs to be getupdate from statemaster (stateid,statename,statecode). I...

Can i install sql server 2008 r2 on windows server 2008 r2 which has active directory users and computers - Hello friends, Iam a newbie to sql server dba tasks, I want to install sql server 2008 r2 enterprise edition on...

split column pipe delimited - Hi All, I'm having trouble separating a single column into multiple columns where the pipe is the delimiter. I have enclosed an...

Select Query to return all rows from one table - Hi All, I have three tables tPeriod which has PeriodID int, PeriodDiscription char tTimeTable which has DayID int, StaffID int, SubjectID int,...

remote table update performance - Hi all, I am having problem updating remote table. It just taking too long. I am new to SQL Server and...

Trying to view properties of the Database - When I right click on the database to view properties on any of the databases on the server I get...

SQL Server 2008 : SQL Server 2008 High Availability

CLustering with Mirroring for Disaster Recovery - hi we are planning to do the following two physical servers, each physical server two vm.one is for db and other...

Quic answer plzzzz - Any method to perform Database Mirroring? without impacting perfromance??

Running SQL 2005 & 2008 in a 2 node cluster - Is it possible to have a setup running SQL 2005 & 2008 in a 2 node cluster? Thanks Pete

Would a smaller number of VLFs in the log speed up the redo phase of recovery of a mirror snapshot? - From time to time, when an intensive log operation takes place on the principal database, we observe very long snapshot...

SQL Server 2008 : SQL Server 2008 Administration

TEMPDB Usage - Hi All I'm trying to test the affect on tempdb of a query with a large sort operation. I'm using the below...

Paging Issues on SQL Server - Hi, I have a SQL Server installed on a VM with 2008 R2 version and the OS is also Windows 2008. The...

Only send DBMail when query results are present - I have a job in SQL Server Agent that sends the results of a query to my email twice daily,...

Shrink of data file - DBCC SHRINKFILE - taking a long time and not completing - stuck at 99% completion - We had a large data purge recently where a large part of the data in a file was deleted. File is...

Can't revert from snapshot - I wanted to remove user data from a database to make a new version of the database with no user...

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

Add days to date SSRS Expression - Hi geniuses, How do I add days to a date in an expression. Field: payment_date and I want to add 15 days to...

Expression Error - Hi geniuses, I'm having problems with a traffic light indicator expression: =SWITCH( ReportItems!Textbox21.Value="1.1.PC" and ReportItems!Textbox23.Value<>"",0, ReportItems!Textbox21.Value="4.2.PP" or "4.3.PE" or "4.4.S" and ReportItems!Textbox23.Value="",1, ReportItems!Textbox21.Value="1.1.PC"...

Issues with SSRS after migration - Hi, I was hoping someone can help me in here. I am fairly new to administration/maintenance of SSRS, but im learning...

Sum with condition not working - Hopefully someone here can help me solve this problem. What I have so far is a list with two tables in...

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

Database Design : Disaster Recovery

Graph to show downtime / data loss? - Not long ago I came across a graph plotting acceptable system downtime and acceptable data loss in an easy-to-understand format...

Data Warehousing : Analysis Services

dimension ordering - Is it a possibility to order the dimensions of an Analysis Services project? For Example I want to see the list...

SSAS any recommendation for installing this on a separate server from SQL Server Instance - Hi, I have to install SSAS but I don't know what is the best practice to follow whether installing this...

SSAS CUBE PROCESSING ERROR - I am new bie trying to learn SSAS. I have these errors in trying to process my cube after it...

Article Discussions by Author : Discuss Content Posted by Santveer Singh

SQL Query to get all the databases and permissions on them for a login - Hi, I have situation where i need to prepare a report to find all the users and what access have they...