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

In this issue

Editorial

NCAs and NDAs

Featured Script

SQL Server Uptime

Need to share database changes?
Keep database dev teams in sync using your version control system and the SSMS plug-in SQL Source Control. Learn more.

SQL Monitor 3.2 is out, now with more flexibility!
Monitor metrics important to the whole business with custom metrics. There’s a whole site to help you get started. Find out more.

Keep your database and application development in sync
SQL Connect is a Visual Studio add-in that brings your databases into your solution. It then makes it easy to keep your database in sync, and commit to your existing source control system. Find out more.

In This Issue

A simple T-SQL statement to create a list of lookup values

Suresh G. Kumar from SQLServerCentral.com

In this article, we provide a simple way to get a comma delimited list from a table of entries without having to use a CURSOR or a WHILE loop to read through the table. More »

Data Access Tracing in SQL Server 2012

Additional Articles from Microsoft MSDN

Learn how to apply the tracing functionality in Microsoft data access technologies such as ADO.NET 2.0, MDAC 2.82, SQL Server Native Client, and the JDBC driver; and in the SQL Server network protocols and the Microsoft SQL Server database engine. More »

From the SQLServerCentral Blogs - SQL Server: Part 2 : Approaching Database Server Performance Issues

nelsonaloor from SQLServerCentral.com

In the Part 1, we have seen how quickly we can check the runnable task and I/O pending task on... More »

Editorial - NCAs and NDAs

It's getting close to March Madness, but this editorial has nothing to do with basketball. I saw a career newsletter recently that answered a question someone had about negotiating non-compete agreements (NCA) and non-disclosure agreements (NDA). The basic answer was that you might want to negotiate reasonable limits to these agreements to protect yourself.

I'm under a few NDAs, one as an MVP, one as part of Red Gate, and I still have a NCA as part of the sale of this site. I've also signed some in the past, but I've been careful to read them and negotiate the terms to be more fair, at least in my mind what was fair to me and the company. Often the original agreements I've seen are heavily in favor of the company and should be changed.

The MVP one with Microsoft actually seemed fair to me and mostly covered releasing information that we get as MVPs in advance of Microsoft's disclosure. So I always ask before I repeat something I've gotten through their channels.

I don't think that NDAs or even NCAs are that commonplace as a condition of regular employment, but you should be careful and read them carefully. The exceptions might be if you're a consultant (often an NCA will be given to you) or you work in a company that's going some type of research or invention, including software. In these cases, I think you might see a standard one.

However you feel about them, usually they're required as a condition of employment in those cases, however you don't necessarily have to accept them as is. I highly encourage you to read them and ask for changes that you think make sense. Usually this involves limiting the distance, time, or scope of the agreement. I definitely advise you to limit time frames to no more than one year for most time limits, a reasonable distance limitation (50-100 miles), and scope out specific companies for which you cannot work (for example, current customers or even only those you have contact with).

One thing I'd also mention is that don't look at these agreements as ironclad. I know consultants that have gone to work for current customers of their employers by negotiating the transition. Often the contractor doesn't want to anger its customer, so it may let an employee go as a gesture of goodwill. Of course you probably have to work for the company, not be your own contractor.

Just keep in mind one thing if you ever want to get around the NCA or NDA. You need to ask and negotiate again, which just might get you what you want. If I've learned one thing in negotiating anything, it's that you can't get it if you don't ask.

Steve Jones

This editorial was originally published on Mar 12, 2008. It is being re-run as Steve is on holiday.

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.

Steve Jones from SQLServerCentral.com

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

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

Question of the Day

Today's Question:

Within SQL Server 2008 R2, there are multiple channels in the extended events architecture. What are those channels? (Choose 2. Each correct answer is a part of the solution.)

By Jason Brimhall

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

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

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.

Yesterday's Question of the Day

Consider the table structure given below and the series of queries which are used to insert value in this table

CREATE TABLE IdentityTbl
  (
     id INT IDENTITY
  )
GO

-- Query#1
INSERT INTO IdentityTbl

-- Query#2
SET IDENTITY_INSERT ON

DECLARE @id INT

SELECT @id = ISNULL(MAX(id) + 1, 1)
FROM   IdentityTbl

INSERT INTO IdentityTbl (id)
VALUES      (@id)

SET IDENTITY_INSERT OFF

-- Query#3
INSERT INTO IdentityTbl
DEFAULT VALUES

-- Query#4
INSERT INTO IdentityTbl (id)
VALUES (DEFAULT)

Out of the 4 insert queries, select the query which inserts the value in IdentityTbl.

By Lokesh Vij

Answer: Query#3

Explanation: 1) IDENTITY_INSERT is used to set identity of a particular table. SET IDENTITY_INSERT

ON/OFF

Ref: http://msdn.microsoft.com/en-us/library/aa259221%28v=SQL.80%29.aspx

2) Identity does not explicitly take DEFAULT values. In a situation given in the question we need to use 'DEFALUT VALUES' keyword for identity value.

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

Featured Script
SQL Server Uptime Chandu Ade from SQLServerCentral.com This script is used to get uptime for the SQL Server. (Supports SQL2005 and onwards only) 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 SQL 2005 Cluster Problem - Hi Guys, I'm wondering if anyone can help me, i really don't wanto to reinstall one ofe the nodes in the... ssis package not working with sql server agent but run fine manually - I have setup job which run ssis package. In this job i pick data from oracle server with link server.... How can I find which certificate is used ? - Experts, Suppose a user gives a backup taken from one of the SQL server 2008 encrypted with TDE. But user doesn't... String or binary data would be truncated. - I am getting error in below code: SET NOCOUNT ON CREATE TABLE #EVENTS (LOGDATE DATETIME, PROCESSINFO VARCHAR(50), VCHMESSAGE VARCHAR(800), SOURCE VARCHAR(20),... Need help auditing - Hi everyone, Before I got to this new job, people were allowed to create many unused objects in our production... Monitoring scheduled backup jobs - Good afternoon, Apologies for this long post but I would really appreciate some feedback on my approach to solving the following... Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.' - Hi, While trying to restore a database (with replace option), I am getting the below error - Exclusive access could not be... Reclaiming deleted but unused LOB space in SQL 2005 - Hi, I've spent most of a day playing/wrestling with this, and my google searches have found lots of information but no... SQL Server 2005 : Business Intelligence SSIS Package with(Derived Column & Lookup) - Hi All, I have a SSIS package that has data in a table. I have a OLE DB Source to connect... SSIS Pakage Error - Executing SSIS package i am getting following error. [Execute SQL Task] Error: Executing the query "exec proc_BusinessRulesValidation @UserName = ?, @..." failed with the... IIF or SWITCH Condition to use and how - Hi, I have a report where countrycode is a paramater and i need to show the date format as dd/MM/yyyy... SQL Server 2005 : SQL Server 2005 General Discussion SQL Server 2005 64 bit vs 32 bit - Hello to all. I have installed SQL Server 2005 Developer Edition and Visual Studio 2005 Standard Edition on my Windows... when store procedure recompiled last time - Hi There, I am looking to find when is a specific store procedure recomplied last time. Reason to find this is... SQL Server 2005 : SS2K5 Replication Structure changes in tranasctional replication - I have Transactional replication setup between 2 SQL Server 2005 Servers. I have made structure changes (add 2 columns) to... SQL Server 2005 : SQL Server 2005 Performance Tuning How to increase indexes performance for fast queries results - Hi All, I have a table named "Tbl_Inv" in my database that contains 4.7 Lacs records with six non clustered indexes... SQL Server 2005 : SQL Server 2005 Integration Services SSIS - OLE DB Source task Error - I am using SSIS package to read the data from Access file and import them to SQL Server table. I... Error while running a package through job that downloads files for SFTP server - I created a package to download files from a SFTP server. This package, when run via Development Studio (BIDS), works... Transfer Data from one database table to Another database table - Hi, I have two databases – DBSample1,DBSample2 Both databases contains same table names(TabSample1 and TabSample11) and same design Need to copy the... SSIS - FTP TASK - DYNAMIC FILENAME - Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with... SQL Server 2005 : T-SQL (SS2K5) previous figure - i have a table with 3 columns. i want to get the salesamount for previous calcid for a perticular productid.below... trailing space in len() problem - hello all shortly, this [code="plain"] declare @sqlStr varchar(max) set @sqlStr = 'aa, ' print len(@sqlStr) [/code] returns 3 on one server and 4 on another... if i try... Remove Decimals Without Rounding - How do I take a number like maybe 10.73 and remove the decimal place and making it a number like... SQL Server 7,2000 : SQL Server Newbies The log for database 'DBNAME' is not available. - Hello, I have a database problem since yesterday. -------------------------------------------------------------------------------------------------------------------------------------------------- The error is: ----------------------------------------------------- Message The log for database 'DBNAME' is not available. Check the... SQL Server 7,2000 : Replication Unable to replicate in SQL Server 2000 using Transactional Replication - 1. My database consists of 397 tables. Platform is SQL Server 2000 Enterprise Edition 2. While creating a publication using transactional... SQL Server 7,2000 : T-SQL Time Out Error When trying to Edit a Table - I am running a procedure which includes some operations on X table.After couple of minutes I kill the execution of... SQL Server 2008 : SQL Server 2008 - General Query to find out latest data of each student - I have data as below Name--Month----Year--------Marks A--------8--------2012--------80 A--------7--------2012--------88 A--------3--------2011--------70 B--------8--------2012--------80 B--------7--------2012--------88 B--------3--------2011--------70 C--------3--------2012--------80 C--------5--------2012--------88 C--------7--------2012--------70 I want result like this Name-- SQL Compatabilty error - Hi Guys, Please help me on this, I have executed this SP EXEC sp_dbcmptlevel 'CDDB6', 100, before runing my Sql... how to calculate percentage and count without passing parameter from this tables? - here i am having four table [code="sql"] DECLARE @question table ( QuestionId int, Record uniqueidentifier, indexnumber int, questiondetail text, IsActive bit ) -- select... Creating a view from 2 joined tables - inserting multiple lines - Okay - my challenge today, is that I need to create some labels for a "Picking List" ( BOM ) - One lable per... problem in displaying result from stored procedure to Frontend application - Hi I written a procedure to search hotel based on particular hotel name or based on a cityID. The procedure... Deploy SQLS Database to a Produtiv-System - Hi, is there a guide or checklist for deploy SQL Server databases (2008/R2) after development and testing to a productiv/live platform?... How can I find the first column having NULL values in a table - Hi Friends, I have a file reader function that will read the file and load the data into a temp table.... MEMORY AND TEMP TABLES - I am using global temp tables in my store proc due to the requirement...After I run series of proc's my... Query causing disk space issues - OK, I'm out of ideas. Rather than use an existing document management model, my company has decided to build their own.... subreport as per value in the main report - I have two reports .One report shows data of top 10 handset in term of bar chart.another report shows detail... Getting login failed error when trying to run sql agent jobs after failback or failover - Hello All, I am using SQL Server 2008 R2 SE. For HA I am using double take. The source and target... Sample XML File to Import to SQL - Hi all Where can I get a sample XML file to test importing to SQL via SSIS. Thanks in advance. Aggregating query help - I have a table of possible trip routes like the following: [code="sql"] ID RouteID RouteName Origin Destination 1 1 ReturnTrip A B 2 1... SQL Transactional Replication 2008 problems - Hi, i need help urgently on this one. I have transactional replication in place between two servers. These db's are rather... Unable to connect to SQL Server 2008 Clinet system from Windows7 - hi all, I try login to my client system sql server using Sql Server Client Management Studio 2008 wit Windows 7.... Stored Procedure Insert Locking Until End of Processing - Hi, I have been trying to get the stored proc below to not lock dbo.some_table during the waitfor delay portion.... Selecting a substring up to a specific (special) character - OK, so I would like to write a query that pulls info after a certain number of underscores. For example: I... Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong - Hello, We have a problem in the query within the attached file. When I run in sql server 2000 it... rebuilding indexes - 1. Does index rebuild on a partitioned aligned index for an individual partition causes any locks on other partitions? 2.... sql service restarted unexpectedly - hello experts, in my production server sql service restarted unexpectedly, the concern teams are asking for root cause analysis doc for... new to DBA an dtechnology - Hi all, I am new to technology world, been in customer support, planing to move to technology, been thinking of doing... Copy results, update a few fields, reinsert - I'm trying to find an easier way of doing the following: We have a batch file that processes records into... Display Estimated Execution Time - Client Execution Time - I am currently learing about query optimization. Can someone tell me how to interpret within "Display Estimated Execution Time" the... Odd Error Message - Morning Just going throught my 2012 SSMS to a 2008r2 server, and I got the following error message, which I've never... SQL Server 2008 : T-SQL (SS2K8) Best way to run high number of transactions concurrently - Hi, I have been asked to see if our new database system is up to handling a high load and... TSQL Suggestion: Support GROUP_CONCAT - Please vote: https://connect.microsoft.com/SQLServer/feedback/details/764820/support-group-concat-aggregation-function row_number replacement - Hi friends, I have created temp table with row_number() since i need the latest loanumber with step Create Table #Max_step ( Loan_number varchar(10), FID... Consolidate Overlapping Date Periods - Getting late in the day and I could use some support from those who are feeling fresh! :-) Suppose I have... varchar to time or datetime or ARGGHH Please help me. - I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks... Is there any best way to REPLACE the query? - Hi there, I have a below query which needs to replace the label to actual value. I can do this by... T-SQL query help - Hi.. I have data the data in the below temp table(SQL query) [code="sql"]IF object_id('TempDB..#tTemp1') IS NOT NULL DROP TABLE #tTemp1 CREATE TABLE #tTemp1(ID int,... Need Help on User Tree Hierarchy with Addition/Deletion Option - Hello All, I have situation where if user pass the userid then their respective hierarchy display.Here is SP CREATE PROCEDURE TraverseUsersRecursive @UserId... SQL Server 2008 : Working with Oracle Oracle Driver Mania - We recently migrated our primary server to Win Server 2008 R2 and SQL Server 2008. It is an OLAP system... SQL Server 2008 : SQL Server Newbies Query Help - Hi All I have 2 tables. Table 1 MasterDefects Field1: Department Field2: Process Field3: Type Field4: Defect Table 2 Results Field1: Department Field2: Process Field3: Type Field4: Defect Field5: ShiftID Field6: SizeID Field7: Amount Field8:... bcp a pipe delimited file - hi all, i want to import a flat file of type Employee ID\|Employee Name; 134543543\|asdfasdfdsfdf; inside a sql proc As bcp requires the destination table... Problem with Grouping and max() - Basically the data is access data to a building. I want to establish if the person is in or out... How Do You Keep The "Pieces" Of A Complex Statement "Organized" - I just finished a complex SQL statement; and, yes, I know there are even more complex ones. When I am faced... Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access... SQL Server 2008 : SQL Server 2008 High Availability Compresses Backup - As the volume of data is less and Database is around 10 GB, here they prefer taking Full Backup on... Script to Backup SQL Server Log Files - Hi All, Need assistance in creating a script for the below requirement. Any assistance to satisfy this requirement will be greatly... LOGSHIPPING RE-ESTABLISHMENT - Hi, I want to understand the Logshipping reestablishment Scenerio After a Failover.Below Mentioned are the details of the Environment for the... Index !!!! - Now Daily at morning 6:00 am the server (Database) does not have any transactions.. i mean its on IDLE state... Replication - After setting up Replication (Transactional Publication with updatable Subscription) between two instances one instance being both Distributor and Publisher, with... SQL Server 2008 : SQL Server 2008 Administration Mgt Studio can see & login to other servers but not (local) - Built a new Windows 2008 R2 x64 server, and installed SQL Server 2008 on it, as a test version of... The distribution agent failed to create temporary files - Hi I'm getting this error with a transactional replication setup on SQL 2008R2 Standard (SP1): The distribution agent failed to create... Need a Memory Utilization alert SQL Server 2008 r2 - I Need a Memory Utilization alert SQL Server 2008 r2. I am not sure which alert in sql server will... SQL Disk to Memory - Hi all I have a quick question regarding pages moving from disk into memory If I select * from a table, are all... 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... Front End Dev - HI all, I'm very happy in my SQL world with all the tools MS give me. Just sometimes somebody wants a... 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... 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 can't access localhost/reports/pages/folder.aspx - Hi, I have a SQL-Server 2012 Developer Edition on a 32-bit-notebook. Ising IE9 I get a login window when connecting to... Data driven subscription to fileshare - Hi, I am new to ssrs and need to create a data driven subscription to a fileshare for a report. What... ExecutionLogStorage table and Subscriptions table - Is there a way to get only the ExecutionLogStorage rows that correspond to a particular subscription in the Subscriptions table? I'd... SSRS 2012 in Sharepoint Integrated Date problem - Hello all, Hope you can help me out. Scenario; SSRS 2012 in Sharepoint Foundation 2010. Basic select between two dates... Invalid Semantic Model - Hello, I got the following error while trying to create a report model: The semantic model is not valid. Details: The element... Data Warehousing : Integration Services Reshedule SSIS Package & get back data on Package Failure - [font="Courier New"]Hi All, I have scheduled SQL Job composed of several steps My first task is to get customer data from... SSIS - OLE DB Source task Error - I am using SSIS package to read the data from Access file and import them to SQL Server table. I... Memory allocation for SSIS variable - I'm having a problem with an SSIS variable. The variable is defined as data type object. I'm writing an ADO record... dynamic columns in flat file destination connection manager? - Hello, In a large database I wish to extract text files with the data from individual tables as well generate the... Run Excel Macros from SSIS - Hi Does any one know how to execute an Excel Macro from SSIS. I have been reading about it but... Data Warehousing : Strategies and Ideas Show Right Column to Right User - If I have three different user with different occupation (manager, salesman, accounting) The main question is to how display right column...

To be removed from this list, please click here If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it here. This transmission is ©2012 Red Gate Software Ltd. All rights reserved. Contact: webmaster@sqlservercentral.com

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

In this issue

Featured Contents

Editorial

Featured Script

In This Issue

A simple T-SQL statement to create a list of lookup values

Data Access Tracing in SQL Server 2012

From the SQLServerCentral Blogs - SQL Server: Part 2 : Approaching Database Server Performance Issues

Editorial - NCAs and NDAs

The Voice of the DBA Podcasts

Question of the Day

Yesterday's Question of the Day

Featured Script

SQL Server Uptime

Database Pros Who Need Your Help!

SQL Server 2005 : Administering

SQL Server 2005 : Business Intelligence

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Server 2005 : SS2K5 Replication

SQL Server 2005 : SQL Server 2005 Performance Tuning

SQL Server 2005 : SQL Server 2005 Integration Services

SQL Server 2005 : T-SQL (SS2K5)

SQL Server 7,2000 : SQL Server Newbies

SQL Server 7,2000 : Replication

SQL Server 7,2000 : T-SQL

SQL Server 2008 : SQL Server 2008 - General

SQL Server 2008 : T-SQL (SS2K8)

SQL Server 2008 : Working with Oracle

SQL Server 2008 : SQL Server Newbies

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008 : SQL Server 2008 Administration

SQLServerCentral.com : Anything that is NOT about SQL!

Reporting Services : Reporting Services

Data Warehousing : Integration Services

Data Warehousing : Strategies and Ideas