In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Deployment Manager Deliver .NET and SQL Server updates on time every time
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.
 
SQL Toolbelt Want to work faster with SQL Server?
If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.

In This Issue

Low Profile Architecture

The goal of this article to show how to get unique transactions per client per month from the data set containing multiple transactions per client per month.  More »


SQL Saturday #203 - Boston

Join us at SQL Saturday Boston on April 6th. SQL Saturday is a free training event for SQL Server Professionals and those wanting to learn more about SQL Server. More »


Blocking SQL Server db_datareader, db_datawriter, and db_owner Permissions

n a database I currently support, the application makes use of db_datareader and db_datawriter to give permissions to the tables. In a different database, it's even worse as the application uses db_owner. We're adding new tables to both databases and we don't want the application to have access to these tables. How can I deal with the use of these three roles? More »


From the SQLServerCentral Blogs - Implicit Conversions and Avoiding Them With Computed Columns

I recently encountered an interesting performance issue (due to implicit conversions) that I was able to solve using a lesser known... More »


Editorial - Salaries in 2013

Salaries are looking good, at least in software development in the US. Windows IT Pro released a report (PDF) that came from over a half million surveys that were completed in late 2012. You can read the report, but the highlights were that more people were satisfied with their jobs (nearly 60%) with two thirds of the respondents saying their received a raise with about half getting bonuses. The downsides were that salaries and bonuses had trended downward from the last 4 hours, which shows that economic conditions in IT are not as great as we might like.

I found the survey on Trainsignal's blog, which isn't surprising given one other highlight. Eighter percent of respondents said that training in 2012 was a positive investment for themselves and their company. Ninety percent of those people said they learned new skills that could be used in their job. That's a great point, and one that I think is worth emphasizing to your management. Budgets are tight, and companies can be reluctant to invest in training, but surveys like this can help convince them the investment is worthwhile.

However I wouldn't just expect my company to train me. Many of you reading this don't, since you are a member of SQLServerCentral and are trying to learn more about your field. I'd point out those investments, and include any monetary or time investments you make on your own. If you attend events, buy books, practice at home, showcase those to your management and task that they make some investment in you as well. 

Everything is negotiable, and if they are worried you might take the training and leave, ask for an agreement that you will remain with the company for xx months for $yy spent on training. It's a good trade, and if you get a great offer to leave, pay back the company and call it an investment in your career.

I think technology is still one of the best fields to be in for working conditions and compensation, if you enjoy the work. However you to succeed and enjoy a good career, you need to continue to work on your skills on a regular basis.

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


The Voice of the DBA Podcasts

We normally publish three versions of the podcast each day for you to enjoy. We have no podcasts today due to technical difficulties.

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:

USE master;
GO

BEGIN TRY;
BEGIN TRAN;

SELECT 1 AS a INTO #temp1;
RAISERROR ('Catch Me If You Can', 10, 0);

END TRY;
BEGIN CATCH;

PRINT 'I Caught you!!';
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
THROW;

END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRAN; 

What happens when you run this code in SQL Server 2005 and higher?

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

This question is worth 1 point in this category: T-SQL. 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 Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Yesterday's Question of the Day

In which version of SQL Server did the SET ROLLBACK_TIMEOUT = X get added? (X is the number of milliseconds before an automatic rollback is initiated for a connection that is not actively running a command.)

Answer: None of the above.

Explanation: The correct answer is none of the above. It's Apr 1, so this is an invalid command and feature.

» Discuss this question and answer on the forums


Featured Script

Who has accessed my 2005 server?

Find out who has been on my server using the Default trace. 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

Exporting SQL Server configuration - We are in process of building a new Server for our SQL 2005. The current SQL Server 2005 , we made few...

Database IO is frozen for snapshot / IO is thawed - I notice in my error log there are messages which says the Databasename IO is frozen for snapshot then follow...

SQL Server 2005 : Business Intelligence

Data type conversion Issue - I am new to SSIS and is trying to load data from a flat file to Oracle DB using SSIS. I...

SSIS to IMPORT and then EXPORT to/from EXCEL - I have an SSIS package that creates the temp table, then uses a data flow to import a CSV file...

Issue Loading excel files into SQL SERVER 2008 database using SSIS package. - Hi, I use a ForEachLoop container in SSIS to import 427 xlsm files into a SQL Server 2008 R2 database. I use...

SSIS- Call to Stored Procedure - Hi I have scenario where I need to Call an Storeprocedure in SQL task Eg: Procedure Name Like Usp_ABCDEF 'Param1',Param2' Part of...

SQL Server 2005 : SQL Server 2005 General Discussion

Question about shrinking data files on a read only database - I've got a database that is read only that a couple times a month needs to be transferred from the...

The INSERT statement conflicted with the FOREIGN KEY constraint(Not reproducible) - We have one parent table: Document (GID int identity(1,1),ParentName) and two child tables, both having FOREIGN KEY(Document_GID) REFERENCES Document (GID) 1. DocumentChild1(Document_GID,Child1_name) 2. DocumentChild2(Document_GID,Child2_name) Steps: 1....

SQL Server 2005 : SS2K5 Replication

best replication method for large database - What is the best replication method for large database? :unsure: thanks

SQL Server 2005 : SQL Server 2005 Integration Services

Change SSIS package owner to SA - Hi,Previous dba has created some SSIS package. He is the owner of the package. Recently I joined and started using SSIS...

Conditional Split error - Hi All, I am facing issue with conditional split, i am very new to MSBI. while trying to fetch male and...

SSIS and dealing with embedded double quotes and coma - I posted my problem in other thread also but no go, I think this is the right forum to post...

Creating instances of excel file - I'm using SQL Server 2008 BI. I have an oledb source outputting data into an excel destination. I want the...

Nz(FieldName,'') Equivalent in SSIS for Microsoft Jet Ole db provider - Hi All, I'm trying to access the MS access tables in SSIS using Microsoft Jet Ole db provider. I'm trying...

SQL Server 2005 : T-SQL (SS2K5)

Identify node and tag in XML data using T-sql - Hi, I have XML data store in Sql table. XML structure will not be static.i want write the T-sql Code to...

Update Query - The table x is ambiguous - Hello, I am attempting to write a query to update the cost of an item, based on the cost of an...

SQL Server 2005 : SQL Server Newbies

create table to group th e people of same department - NAME AGE SALARY DEPARTMENT SHARATH 24 21000 MARKETING RATHAN 35 21000 MARKETING RATS 28 21000 IT DARSHAN 23 20000 IT LINI 25 25000 IT SHAN 23 20000 MARKETING SUDARSHAN 22 20000 IT to above given table i wan to write a qury that display IT MARKETING RATS RATHAN DARSHAN SHARATH LINI SHAN

SQL Server 7,2000 : Administration

SMTP Mail - Need xpsmtp80.dll - Hi, I'm trying to set up SMTP mail, and I can't find the required DLL, all the posts I found...

SQL Server 7,2000 : T-SQL

Sum then Divide - Is there any easier way to do this sum/divide without having to put results of sum in a temp table...

SQL Server 2008 : SQL Server 2008 - General

Can I move the older ERRORLOG files? - Hi all, Long story short, we had a process that was filling up our log file at an impressive rate (impressive...

sql query to be accessed online - Hi I just want to figure how to have a sql query that I generated in excel form and I want...

Discrepancy between sys.master_files and sys.database_files - Hi all, I have discrepancy beetween sys.master_files and sys.database_files in size column. Couple of days ago I changed the size of...

Issue with Float datatype while using EXCEPT - I have two tables with float data types and am using EXCEPT to check for the new rows in the...

Cardinality Issue - I have a 100,000,000 row table with a GUID primary key. (This is an IBM FileNet/ECM database, and utility query.) The...

Why duplicate indexes - It is query logically error, post deleted. Thanks for looking at.

Duplicating rows based on another table - Hi, I have some data in table a which has a unique identifier called column nd. I have another table, table...

Requirement of .net framework for sql server 2008 r2 in Windows 8 - What is the actual version requirement of .net framework for installing sql server 2008 r2 in a windows 8 PC? According...

Maintenance Plan issue - Hi all, I got a strange issue today. When i am trying to run a maintenance plan manually from GUI....

A severe error occurred on the current command. The results, if any, should be discarded. - Hi While running the query , I am getting the following error :- Msg 0, Level 11, State 0, Line 0 A severe...

sql server readonly permission - Which permission under sql server database properties->permission in SSMS should i select to make the database readonly for a user?

Publishing and accessing SQL Server 2008 Reports - I 'm new to SQL Server database and I need help. I 'm trying to publish sql reports for users...

Newbie (Know-nothing-bie:) Needs Urgent Assistance with SQLExpress - I have very little experience with SQL other than using it. I am a hardware tech. But I do have...

Eliminate FileGroup and move contents to Primary FG - Hi there, I'm looking for some guidance on how to resolve something I've been tasked with. I am working on a DB...

Unplanned outages causing bankruptcy - * I wasn't sure where to ask this throughout the forum, so I decided here was best. * For my thesis in...

Low Procedure Cache Hit Rate means no use of stored procedures? - Hi everyone. I'm not really understanding how the procedure cache works in SQL Server 2008 R2. I have 100GB ram allocated...

Queries and tables - Hi! I was wondering if I could find some queries and database tables for them. I would like to have some...

How to preserve global temporary table data - Hi guys, I would like to know if anyway to preserve global temporary table data till some one explicitly drop it....

Parse CC in String - Hi Guys, I have a table with a column which is VARCHAR. This column has text and potentially Credit Card # and...

Check Constraint Question - Situations Where a Child Record Should Be Limited to One Occurrance - One of my databases has a table for Project Dates. Some of the milestone dates can occur multiple times such...

club to columns into one - The type and task column are combined into one table task keeping in mind the order they have to come...

Several Service Broker queues keep auto-disabling, not sure how to debug it - I have five queues that are automatically disabling themselves after being re-enabled. I've been running broker for about two years...

Query - Please Help! - Someone please tell me what I'm doing wrong..... :( All I want to do, is get a list of databases which...

Transaction log in Subscriber database for Transactional Replication - I have created replication between two SQL Server 2008R2 databases and it has been functioning well since the initial setup...

SQL Server 2008 : T-SQL (SS2K8)

Transaction Isolation Level - Hello Everyone I have a medium sized SSIS Package, with multiple steps, that call a single sproc each. I am working...

Help with Query - Hi I have a query which returns the following Key segment Value A001 001 3 A001 002 4 A001 003 4 A002 001 2 A002 002...

TRIGGERS... - Hey guys, Our team ran into a problem over the weekend and Im confused over it... We are in the process...

Add Auto Incrementing column to my SELECT statement - Along with the data that I am pulling back from query, I also need to provide an ImportID column with...

Creating code objects within a single script - I need to deliver a large number of code objects (views and functions, not stored procedures) to our customers via...

Looking for a query to return rows in tableA that are inclusive of ID in tableB - I am looking for a query to get all records from tableA that exists in tableb inclusive. tableB contains the combination...

Need to unpivot a table - [b]Hello, I have a table like this[/b] CREATE TABLE [temp]( [Country] [varchar](200) NULL, [Country_Group] [varchar](200) NULL, [Year] [varchar](200) NULL, [R1_TV] [decimal](38, 2) NULL, [R2_TV] [decimal](38, 2) NULL, [R3_TV]...

MERGE TSQL in SQL SERVER 2008 - Hi ALL, Need one help/suggestion in tsql using MERGE in sql server 2008.I'm using the below code to do upsert in...

i need the max length value of every column in every table in a database - I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will...

How to select data in a particular format - Greetings all. I have data in a view that when selected from looks like this: CertYear CertCode Quantity -------- --------- -------- 2008 HI-Master 2 2008 HI-Instr...

please help with cursor syntax - I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor...

Return 1 record with data from multiple tables - All, I have data in numerous tables. The first 2 tables are simple: tblProducts ----------------- ProductID ProductName CategoryID tblCategories ---------------- CategoryID CategoryName Each product links to a category. That part is...

SQL Server 2008 : Working with Oracle

Need better solution to Import from Oracle - I maintain BI that needs to import the data from Oracle every day. I use the import wizard to create...

SQL Server 2008 : SQL Server Newbies

importing an excel file - hi professionals. I am trying to import and excel spreadhseet into the database by right clicking the database, tasks, import. I...

query to find timgaps between rows - Basically I want missing timegaps between the @startdate and @enddate with tbl_book and also in between tbl_book (do not care about...

on primary - why [b]on primary [/b] is used while creating a table is it necessary ex: create table temp (name varchar(50),age smallint,salary int)[b]on primary[/b]

Cursor in Stored Procedure - Hi, I am a SQL newbie and need to update code in a Stored Procedure that has a cursor. Is it...

Need help with a .bat for a stored procedure, please. - Hello all, I'm new to SQL and teaching myself as I go. I don't know what to do now though....

SQL Server 2008 : Security (SS2K8)

Strange network behavior causing problems - Hello everyone I'm trying to determine an odd problem and I have exhausted most of my capacity with no results... so...

Customize DB Role - Hi, I want create one database customize role, that role should able to do only fetching the data from SQL view...

SQL Server 2008 : SQL Server 2008 High Availability

Best practices on migrating a SQL 2K8\Windows 2K8 to SQL 2K8 R2\Win 2K8 R2 - Currently I have an Active\Passive cluster running windows 2008 64bit and SQL 2008 64bit. I now want to turn this...

multiple instance sql cluster with different windows configuration - We have a two node cluster. In order to utilize the max resources we are planning to add more instances...

Sql service and Sql agent not visible in the resources tab. - The sql server and sql agent and the virutal IP are all working but for some reason they are not...

Cluster failover - I am checking the failover scenarios. I just restarted the active node to check the failover process. All the resources...

SQL Server 2008 : SQL Server 2008 Administration

Howto. ..Load 2 columns from excel file into table with corresponding columns ? - Hello, Since I'm not a programmer and hopefully there's a simply way... I need to load one column from an excel file...

Not able to shrink transaction log file - Why am i not able to shrink the transaction log file of my database even though there's 91% free space...

Can I strictly limit access to database ? - Hi, I'm new in SQL Server administration however I need to make some major modifications in my SQL Server database. There...

Restore Master Database - I'm testing what to do in scenario where the Master database is corrupted beyond the ability for SQL Service to...

Call powershell in SQL agent job - I use SQL server agent job to call a powershell script. I am using Type operating system(Cmdexec) In the job command...

Script to calculate when a job ended. - I need to alter the script listed below to calculate the DateTime that the Job completed and sort on the...

Attempted to perform an unauthorized operation - Hi, I'm installing SQL Server 2008 x64 developer edition on Windows Server 2008 x64. I installed the SQL Server 2008 many...

Career : Certification

Upgrading from 2008 to 2012 - Hi, I have the MCTS in SQL2008 and want to upgrade to 2012. Currently I have been unable to find...

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Career : Resumes and Job Hunters

Looking for a SQL Server 2012 internship or on the job learning in South FLorida - http://www.sqlservercentral.com/Forums/Topic1435858-10-1.aspx is a topic I made a few days ago, just to keep is short. I finished learning mySQL and...

Have a job offer from US - I am looking forward to get a job in the USA. And I don't have residence permit. May be anyone have...

sample resume - :-):-D:-P;-):w00t::cool:

SQL exercises for job applicants - Hello, I'm not sure if this is the right forum for this question but here it goes. It looks like we may...

Programming : Powershell

INSERT statements containing regex as a string being read as regex - I am attempting to run a sql script with 4 simple INSERT statements. Each of the 4 INSERTS is inserting...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Help to get the Expression for Calling a column from different dataset - hi All, I'm preparing one graph for 22 different datasets. # of records in each dataset are different. Thats why i cannot...

5 parameters in a report - Hi All, I'm creating a basic report with 5 parameters. I want to populate the drop down menu of the...

Data Warehousing : Integration Services

How to reset metadata in SSIS packages - When working with SSIS and source or destination metadata changes SSIS does not reset the metadata it deletes the changed...