In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Red Gate Deployment Manager NEW! The easiest way to deploy .NET code
Deploy ASP.NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL Backup Pro Take control of your off site SQL Server backups
Seamlessly compress and encrypt your backups – and then take them off site into secure hosted storage. All from SQL Backup Pro’s GUI. Find out more.
 
SQL Source Control The best way to version control T-SQL
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.

In This Issue

SSRS In a Flash - Level 1 in the Stairway to Reporting Services

Learn the basics of Reporting Services, what it is, and what it can do from you. From MVP Jessica Moss, we have a new series that can help you get started with this part of SQL Server. More »


Report Builder 3.0: Adding Matrices to Your Reports

It is easy to create a basic matrix in Report Builder. However, it takes some practice in order to format and dispay the matrix exactly how you want it. There are a large number of options available to enhance the matrix and Robert Sheldon provides enough information to get you the point where you can experiment easily. More »


How do you develop software?

We’d like to learn more about software development at your company. Complete our 5 minute survey and get a chance at winning a $50 Amazon gift certificate. More »


From the SQLServerCentral Blogs - Where is my backup?

We recently added responsibility for ~80 more servers to our team. This means that my team-mates and I are working... More »


Editorial - Those Who Can, Do

There was a time I considered staying in college, getting a masters or PhD and teaching others. I still might follow that path at some point since I enjoy speaking and teaching others how to better work with SQL Server. At some point, however, I became frustrated with the theoretical approaches many teachers had. Like many 20-something-old students I tended to subscribe to the mantra "those that can, do. Those that can't, teach."

I was reminded of that by this piece: Those Who Can Do, Those Who Can't, Get Ceritified. It compares IT workers to the computer systems they manage, and it points out that if all that's required is to pass a test, that's something a computer can do very well, perhaps even replacing those that can just answer questions in their daily work.

There's some truth to that. I always wonder about a person that has 3, 4, or more certifications; do they have actual skills with the product?. Have they actually used the knowledge from those certifications in their work? Is the certification the goal, or is it a way to learn skills and knowledge that can be applied at work? If it's the former and not the latter, then I'd say your efforts to advance your career through certification are poorly aimed.

However if the certification gives you structure and focus, if it allows you to improve the skills you have, and bolster the weak areas in your knowledge, it can be beneficial to your career. If you are taking that knowledge and using it in your daily work, or even in your spare time, then the certification is merely a stepping stone to something greater.

I don't think that people who are certified are somehow incompetent at their jobs, but they have to showcase more than just the certification for me to believe they are valuable employees. 

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

Base off the code below what will the two selects of the id column return?

create table test_trun (id int identity(1,1) not null, somedata varchar(50) null)

insert into test_trun values('a')
insert into test_trun values('b')
insert into test_trun values('c')
insert into test_trun values('d')

select max(id) from test_trun

truncate table test_trun

insert into test_trun values('e')
insert into test_trun values('f')
insert into test_trun values('g')
insert into test_trun values('h')

select max(id) from test_trun

drop table test_trun

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

This question is worth 1 point in this category: IDENTITY. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Yesterday's Question of the Day

Can be table variables used in UDFs?

Answer: Yes

Explanation: They can. This code shows this

create function fn_ret_last_created_db() returns varchar(50)
as
begin
	declare @db_info varchar(50)
	declare @dbs table
	(	name varchar(30),
		created datetime)
	insert into @dbs
	select top 1 name,create_date 
	from sys.databases
	order by create_date desc
	select @db_info = db.name+' '+convert(varchar(20),db.created)
	from @dbs db
	return @db_info	
end

--test
create database new_test_db
select dbo.fn_ret_last_created_db()

Ref: http://www.sqlservercentral.com/articles/Table+Variables/63878/

» 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

Unused Input Parameters

Identify all objects containing unused input parameters. 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

How to load tables(with data) and dependent objects from DB to the other ? - hello All , Can someone suggest me how I Can load tables and its dependent objects from one DB to the...

How to get common Tables between 2 databases? - Hello All, I am sure some of the admin might have gone thorugh the same situation, I need a script that...

optimize for ad hoc workload - Could someone please help me? When trying to configure "optimize for ad hoc workload" in a 2005 Developer edition , getting...

sqlservr.exe process only growing to 125Mb on very busy system - why ? - Hi, We have a SQL 2005 instance that's the back end DB for our company helpdesk system. Performance of late has...

Maintenance plan getting failed for system db and user db - We have two different Maintenance plan for system database and user database doing reindexing for system db whereas reindexing and...

SQL Server 2005 : Business Intelligence

Database Diagrams tool in SQLSERVER - Is there any Database Diagrams tool in SQLSERVER ? Hello : We are planning to design new Datawarehousing product. I am taking...

PROCESS cube via script or scheduled job - i would like to create an automatic job or script or command to run PROCESS on my cube. doug

SQL Server 2005 : Data Corruption

DBCC - Could not repair this error. - Hi guys, one of my customer's hard drive failed. They were able to safe most of the data and create bit...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Performance wise (view or Temp Table )Which one is better - Hi , Table A is having 1 million Records, Table B is having 100 million Records. I need to show data (for...

SQL Server 2005 : SQL Server 2005 Integration Services

How to convert the pdf file to blob type ? - Hi Friends, I have a folder which contains thousands of pdf files where i have to convert into a blob...

How to store the Count(*) value fro datareader to int. SSIS 2005 - Hi all, I have been stucked with this for quite a while. I have a script: [quote]Dim sqlConnStr As String = "Data...

SQL Server 2005 : T-SQL (SS2K5)

Need help conveting this from Mysql to SQL server - Select * from openquery([connection],' -- Forum Messages by User use databasebname; select u.name, convert_tz(from_unixtime(m.modificationDate/1000),'+00:00','-08:00') as TimePST, m.subject, m.body, m.messageID, f.forumID, f.name from jiveMessage m inner...

Delete trigger - I'm not sure what is going on here - if my brain has gone on Christmas holiday early or what, but...

SQL Server 2005 : SQL Server Newbies

Stripping out all non-numerical characters - SELECT accountid,New_Column = REPLACE(telephone1,SUBSTRING(telephone1,PATINDEX('[^0-9]',telephone1),1),'') INTO #YourNewResults FROM #TelephoneTable I have the script above which I thought would remove all non-numerical characters from the field...

Split input string into multicolumn - multirows - Hello all - I am having hard time to split an input string into multicolumn - multirows. Task - Create a stored procedure that...

SQL Server 7,2000 : Administration

Negative number in SP_Spaceused - Hi all, DBName 159423.88 MB -1234567mb As the title says, the unallocated space was a negative number, and that is AFTER having run...

Detecting Detached Databases - I've many databases on the server I've inherited. Some are detached and rather than manually check each db to see...

SQL Server 7,2000 : SQL Server Newbies

Linked Servers Error - MSSQL Server A: MSSQL2000 - set to Windows Authentication mode MSSQL Server B: MSSQL2000 - set to SQL Server & Windows Authentication mode. My client...

SQL Server 7,2000 : T-SQL

Not getting result of MAX() - Hi i have a table having 2000000 rows. and i want a result of : select max(pksrnoN) from Table1 it is numeric...

SQL Server 2008 : SQL Server 2008 - General

Grouping as Equals in Report Builder - 1

how to find name of a table which has maximum number of transactions for a large database - is there a way to find a table which has maximum no. of records among a table list of 100...

HOW TO TABLE DATA VALUE CHANGES MANUALLY DONE BY A PERSON - we have a database of attandance system in which daily attendance recorded. there is a table in which one of...

Linked server problem - Hi guys, I'm trying to set up a linked server on a SQL 2005 server (on say domain A) to a...

SQL Server Memory - High - Hello I am running a physical server dedicated to SQL Server 2008 SP2 on windows 2008 Enterprise Edition 64bit SP2 with...

CXPACKET queries - Hi all Looking at my waits I can see lots of CXPACKETS, and I am wondering what the best way is...

Using Substring and Charindex to split a text in to columns - Hello everyone, I am struggeling with below: I have a field in ReportServer DB called “C.Path AS OrgInput (VARCHAR)”. In this...

Pulling count of records from Teradata - Hi, I am pulling count of records from a Teradata table. I have used Execute SQL Task in SSIS to get the...

Database Stuck in "Restoring .." Mode? - Hello, Anyone know how to set the database state such that it is NOT showing "Restoring .." in SSMS? I had started scripting...

weird memory usage on sql server - Hi Folks, I got this weird issue on sql server. I am running window 2008 r2 enterprise 64 bit with 8 gb...

eliminate one digit from the record - Hello All, I want to eliminate 1 digit from existing record from table for joing two tables.

how to get data where value is null - Hello, I have a table with data like below id value 1 2 2 4 3 6 4 NULL 5 8 6 NULL 7 NULL 8 12 9 15 10 NULL 11 20 12 NULL and i need output like below id value 1 2 2 4 3 6 4 6 5 8 6 8 7 8 8 12 9 15 10 15 11 20 12 20 please help..

Hi frn - I have table CREATE TABLE #varchar_field1 ( ID INT IDENTITY(1,1), mixed_field VARCHAR(100), ) INSERT INTO #varchar_field1 (mixed_field) SELECT '1' UNION ALL SELECT...

''=0 ?!?! - This is something I ran into today and am curious if anyone knows anything about this.... I was working on...

Select all data where one condition is met - Below is some sample data. I want to SELECT all Stat's where an ID exists with a StatDate in AppRec...

How to add features to a current SQL Server failover installation - How to add features to a current SQL Server failover installation? Which option do i have to select: New installation or add...

Change column from NVARCHAR to VARCHAR... - Hi, We have a SW that is made in VB6 so it doesn't support UNICODE, so I want to change all...

float question - sometimes float values are returned as "0E-7" instead of "0.0000000" or "4E-7" instead of "0.0000004" - is there some formatting statement...

How to avoid the SQL Server Agent to start on a SQL server Failover - Hi, I'm wondering how can I avoid the SQL server Agent to start o SQL Server failover cluster. I changed the...

db in restoring - one of my db is in restoring status, we are unable to find the connections on the database . how can...

Import from Access - I have an Access database with one single file that I want to import to my SQL Server DB... If...

Error :The Data Pump Task requires Transformation to be specified while executing DTs packages - I need updation through Data transformation Packages. I trying to update database with DTs packages and right know source and destination...

Alter Index : Rebuild - HI i have rebuilt one of the clustered index , table definition [code="sql"]CREATE TABLE [Person].[Person_BK]( [BusinessEntityID] [int] identity NOT NULL, [PersonType] [nchar](10) NOT NULL, [NameStyle]...

Missing Indexes on Temp tables - The DMV dm_db_missing_index_details has suggestions on missing indexes on permanent tables but I need some suggestions on ways to identify...

How to Shcedule set of SP,executing sequencly - Hi, I want Scedule the Set of Stored Procedures And if any one SP gets errors then next one should gets...

Profiler shows LoginName as 'sa', but 'sa' is disabled on the instance. - As the subject says, I have a profiler running and see some things executing as 'sa' in the LoginName field,...

Identifying Querys causing CPU spike - Hi Everyone, I have a situation where the CPU on my data warehouse server was pegged at 100% for approx. 10...

SQL Server 2008 : T-SQL (SS2K8)

Append query results to existing table - Hi, I currently run a query daily and have to results going into a table using insert into.. select... The problem is,...

Need an Example for Full Text search ? - Hi, Please provide an example for implementing Full text search using FreeTextTable Thanks

Need an example for Indirect Recursive Trigger? - hi, I need an example simple indirect recursive trigger. Thanks.

IF statement with subquery problem - I have a sub query within my IF statement that evaluates to one single number that I am using for...

Get continuous date with count - hi all, i am having a requirement like this input EmpID reportdate reportname noofdays 47 11/29/2012 Thursday 1 47 11/30/2012 Friday 1 47 12/4/2012 Tuesday 1 47 12/5/2012 Wednesday 1 47 12/7/2012 Friday 1 47 12/10/2012 Monday 1 48 11/29/2012 Thursday 1 48 11/30/2012 Friday 1 48 12/4/2012 Tuesday 1 48 12/5/2012 Wednesday 1 48 12/7/2012 Friday 1 48 12/10/2012 Monday 1 48 14/10/20

How to export SQL data to XML file - Hi, I have a sql data need to export to xml file; I can get data in xml mode using FOR...

concatenate of two values - Hi i have table like [code="sql"] CREATE TABLE [dbo].[Mas_Shift] ( [ShiftId] [int] IDENTITY(1,1) NOT NULL, [FacilityId] [int] NULL, [ShiftType] [nvarchar](5) NULL, [Shift] [nvarchar](3) NULL ) INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType],...

Finding combinations of values - My table structure CREATE TABLE trefClientShares{ intLMid int NULL, intMLid int NULL, intISid int NULL, intFRid int NULL } I need to figure out...

Query for a plus sign - Hello Everyone I am attempting to query a column that the data has a plus sign. I know there is data,...

Get list of months, monthname and Year - Hi, I want to create a temp table and insert all the list of monthorder , Monthnames , year 1 january 2011 2 february...

SQL Server 2008 : SQL Server Newbies

query-should i use case? - Hi, I am not sure what the best approach is to accomplish what I need.Should I use case? 1. If KEY in...

Retrieve second of three values separated by spaces - My brain isn't working right now. I have data such as 'HEYE-B Euro-IPA 69793' and 'HEYE-B RFE-IPA 70940'. I need...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

No data showing up in a Database replication - I have a server 2008 R2 server were Database A is replicating to Database B. A full snapshot was done...

need help with Xml file - Hi All, I just started this job liike half an year as a Data Analyst. I have a xml file...

SQL Server 2008 : SQL Server 2008 High Availability

Replication configure in Log Shipping Database - Hi, We have 5 databases in two different servers and its already configured in log shipping and its running successfully also....

Snapshot Backups - The company which i work for have just introduced Veeam backup and replication as the method of backing up the...

SQL Server 2008 : SQL Server 2008 Administration

SQL Monitor - Is anyone using SQL Monitor to monitor their SQL instances? Per this thread [url=http://www.sqlservercentral.com/Forums/Topic1394075-1550-1.aspx][/url], I'm looking to monitor about 40...

Index Performance. - Hi all, I search a lot for this issue but I can't realize what happen. The case is, randomly (no very...

Ways to move only table data in database? - Hi i have two databases in different instances i need to transfer data between two tables are by querry can...

How to check license information - Hi Friends, What is the best way to get the information regarding the SQL Server licenses in our environment. We need...

Faster way to release the Unused Space back to Disk - Hi, I have few databases that have 8 data files allocated with 300 GB each and used only 110 - 120...

How to solve buffer latches problem - We have database in SQL SERVER 2008.We have 393GB memory on that machine.Today we had lots of performance issues .SQL...

How to find allocated space and used space - Hello Team, How to find allocated space and used space for all databases in sqlserver instance ? Is there any query. Thanks in...

Shut down server for maintenance - We have some server maintenance needed at weekend. We plan to shut down web server first, then application server, then sql...

Table has large amount of Data querry geting delayed - Hi We have a table which contains allmost 1 lak to 2 lak of records querring on that table getting delayed...

Problem with Datetime Function - I have SP which contains function convert(varchar(12),[datetime],112) =convert(varchar(12),getdate(),112) this takes lots of time during the Market hours and it is very...

DB Shrink - I have a database that is 5.81836 GB in size with 2.3623 GB free space (due to old data being...

database log file size 900 GB - Hi I know this might be silly question here. but its intresting after in spite my trials the file does...

Job to query multiple instances at once - Like most environments, we have a bunch of SQL servers. I would like to set up some jobs to query...

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

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

SQLServerCentral.com : Suggestions

How do I add an image to my post? - There are opening and closing tags of '[img]' and '[/img]'. How do you use them (or is there another way)...

Reporting Services : Reporting Services 2005 Development

Converting .RDL files to .RDLC files - I got the below instructions from Microsoft, but, after #4 I'm lost. Does anyone have more detailed help???? Thank you 1. Rename the...

Data Warehousing : Integration Services

Import/Export Wizard Weird Behavior in SQL Server 2008R2 - Hi All, I saw some weird behavior of Import/Export wizard in SQL Server 2008R2. I made a video of it please...

Data Warehousing : Strategies and Ideas

Item/Foreign Currency Question - Each item has a currency (USD, AUD, CAD, etc.) and a currency amount. Each currency amount has a starting effective...

Data Warehousing : Analysis Services

Need Help with MDX Query .. - I need to write an MDX query where i can get the totals for all existing years plus the YTD...

Hierarchy from two different dimensions - Is it possible to have a hierarchy that contains attributes from two different dimensions?

Microsoft Access : Microsoft Access

QR Code-Integration of Microsoft Access and Microsoft Word - What is the easiest way to integrate Microsoft Access and Microsoft Word? We are converting permit numbers to QR code. 00000...