In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.
 
SQL DBA Bundle ‘Beating Backup Corruption’
The DBA Team are back, revealing more of their ‘Top 5 Hard-earned Lessons’. Lesson two is out now! Learn to beat backup corruption.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

In This Issue

PowerShell Basics

Powershell. I have seen the word but what is it? This article gives you a basic introduction to what PowerShell is and how you might use it. More »


NoSQL for the MSSQL Soul

The buzz around NoSQL solutions has reached stratospheric heights. This buzz has led many Microsoft SQL Server development shops to start looking at how and where a NoSQL solution can fit into their business. More »


SQL Server Central Webinar Series #22 – What counts for a DBA (Wednesday, March 13 2013 5:00pm - 6:00pm BST)

In this session, Louis Davidson, Microsoft MVP, will discuss how being observant of the environment you work in can help you make sure that you are aware of the health of your database systems, as well as your career. More »


From the SQLServerCentral Blogs - Day 28 of 31 Days of Disaster Recovery: Recovering SQL if the Tempdb Drive Dies

31 Days of Disaster Recovery Welcome to day 28 of my series 31 Days of Disaster Recovery. Today I want... More »


From the SQLServerCentral Blogs - Home Power – Generators

A slightly off topic post, but since I was asked recently, I thought I’d take my reply, make a post... More »


Editorial - Early Software

I  have no idea what the first software I saw working on a computer system was. I'm sure it was some  basic demo program on a Vic-20, which I convinced my mother to buy for us. I remember learning to program on this machine for a year or so before saving up enough money to get an Apple II system. I purchased a few games, but most of the software that ran on my computer was written by me. Programs to solve chemistry and physics labs, early attempts at making our own Dungeons and Dragons programs and even a baseball statistics program to "replay" games from the past.

The first real software I saw in a business setting was a construction estimation program that combined a spreadsheet with some basic CRUD application for managing projects. A neighbor had a business and hired me one summer to help him get it running in his office and train his staff to use it. I'm not sure how well it worked, but I remember being amazed at how much effort and paper it reduced for his company.

Since that time computers have evolved and software has grown tremendously to incorporate many more graphical elements than I imagined as a kid. However those early programs still stick in my mind and I'm sure have influenced the way I view the world today. Dave Winer wrote about early software and how it's influenced the applications we have today. I do think there are a lot of influential programs, and the wide variety of them, and the different ways people reacted to them, resulted in what we have today. This Friday, I wanted to ask you this:

What software first inspired you to work with computers.

What programs changed your career, or perhaps motivated you to work more closely with the data and less with the programs. Is there any software you really enjoy working with? Do you wish you could build something similar? Let us know today.

» 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. Today there are no podcasts due to various issues, but the podcasts will return on Monday.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

Which of the utilities has been deprecated in Microsoft SQL Server from SQL Server 2012? (Select Three)

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

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

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Yesterday's Question of the Day

Executing the code below, will the SELECT return a value of 1 or will an error message be generated?

CREATE TABLE #Test (Col1 int)

INSERT  INTO #TEST

VALUES (1)


SELECT * FROM #TEST WTH(NOLOCK)

Answer: The SELECT will return 1, no error is generated

Explanation: The misspelling of WITH is irrelevant in simple cases like these because a locking hint can be specified without including the WITH keyword i.e. (NOLOCK). When the “WITH” is spelled incorrectly, SQL Server assumes that this is a table alias and executes the code without error. The equivalent would be:

SELECT WTH.* FROM #TEST (NOLOCK)

Ref: http://msdn.microsoft.com/en-us/library/ms189857(v=sql.90).aspx

» Discuss this question and answer on the forums

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Featured Script

Transpose Query Output

script to return query result transposed. 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

Best Practice for Database Owner? - I have a SQL 2005 server with a dozen databases in production use. I'd like to know what is recommended...

SQL Server 2005 : Business Intelligence

avoid columns data "" in ssis package - hi friends i have small doubt in ssis package source data come text file that file contain data like id...

separate numericstring values in ssis - Hi friends i have small doubt in ssis package .how to solve issue flat file source data contains like id , name...

Problem with SSIS job running in SQL Agent - I'm new in BI and technology in general so bear with me. I completed a lot of work in our...

How to insert new record for a user having multiple records by performing lookup? - Hi, I have one table called Student. The source for loading the table is StudentHistory. My Scenario is i have a single...

How to Compare Data between to servers based in Id's - Hi All, I have a requirement like, Identify the out of sync records from two servers. I have Id's to pass...

RS SOLUTION - Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E) - I've been pulling my hair out with this problem.  I can create reports in BI Studio and see them return...

SQL Server 2005 : SQL Server 2005 General Discussion

query: from mssql to myssql - Like I posted before, I get the following error: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider...

How to read the values into sql server 2005 - hi all Please help me to solve this.. '<ROOT> <ROW> <ID>200147</ID> <VALUE>1,10,109,76</VALUE> </ROW> </ROOT>' Thanks,

Trouble Importing CSV. "Truncation" despite Varchar(max) - I'm trying to import a .csv file. One column has text from a website comment filed, so there are all...

Mirroring and performance - hi, anyone know how to maintain the performance of database while mirroring. Because we notice that the system become slower...

SQL Server 2005 : SQL Server 2005 Performance Tuning

How to Use SQL Profiler - Hi, I want to trace all sql,stored proc with sql server profiler. So that I may able to extract top resource...

SQL Server 2005 : SQL Server 2005 Integration Services

Running package in a loop - I have a pacakge that pulls the data from the table for a month. It runs on the first of...

Automating Excel via SSIS and SQL Agent - Hi I have encountered an issue on a project that I'm rather struggling with and I'm hoping someone has encountered something...

windows file system operation - I am working on windows server 2003. I have a file 'target.file' at local. There is a 'target' folder on network...

SQL Server 2005 : T-SQL (SS2K5)

Maintenance Plan Question - I have a extremely time consuming query that times out whenever it runs. To cure the issue, I created a...

Slow Update Processing - Badly Written Code ? - I'm at a new place, trying to find my way around. There's an import/update process that runs very slowly and...

SQL Server 2005 : SQL Server Newbies

Transpose/Pivot Help - Hi. I have a table & I need to transpose/pivot some of the columns into rows. The column that needs to...

shellwait - i run a Tsql query that move's a file from one computer to another set @sql='move '+@file1+' '+@file2 EXEC master..xp_cmdshell @sql , NO_OUTPUT...

SQL Server 7,2000 : Backups

SQL backup with Block-Level technology - Hi, I am running Block-Level Backup software on all my servers. The backup software provider recommends putting my SQL Server Databases...

SQL Server 7,2000 : Performance Tuning

Help - Can't query a single table - Hi All, I have a table in SQL Server 2000 that cannot be queried. Even a simple "select count(*) from dbo_tblAllocations"...

SQL Server 7,2000 : T-SQL

compare the value in a column against multiple databases - Using SQL 2000 I have a table in Database A, lets call it customer. In the customer table there are several columns...

Round to Even (aka Banker''s Rounding) - The final function - Since the other thread was trainwrecked, but since there is indeed a problem with the function as currently written, I...

SQL Server 2008 : SQL Server 2008 - General

SQL Server Express - automatic Backup - Hi, I have SQL Server 2008 R2 Express Edition on a server. I need to backup my database (small db) on a...

summary of servers - i have to prepare the summary of servers (current state) and recommendations for future state –Virtualization –Hosting –Maintenance can any one help me out. where...

Virtualization reviews - i have to Assess server landscape –Server reviews –Virtualization reviews –OS/patch level reviews can any one help me out regarding Virtualization reviews. what does it...

Best way to store data in table - hello, I have aprox 70,000 words and want to store this data in DB, for this there are two ways...

REPLICATION - differences b/w transactionall vs peer to peer ?

Partitioned tables - Create table Table1 (ID INT P.K, OrderDate Date)) 1. The table has clustered index on ID. Afterwards we have partitioned the table...

Indexing for Partitioned tables. - Hi All, I'm doing some R&D on Partitioning, If we create index on a partitioned table the index will be partitioned...

database security - I am developing an application which uses sql server express.so there are so many database which need to have security.So...

Bidirectional Foreign Key problem in Dynamics CRM - Hi all, I manually edited a Microsoft Dynamics CRM database (big mistake) and now I have to clean the things up. I...

What, if any, is the best type of replication for implementing on hundreds of databases on one server? - Hi all, We have a 2 node (active/passive) SQL 2008 SP1 Enterprise Cluster that hosts about 500 databases. Now while the Cluster...

List of some good MSBI, MS SQL Books to Buy Version 2008, 2012 for Development Purpose - [size="3"][font="Courier New"]Hi All, I am working in the field of MSBI and MS SQL development and its really good working in...

TSQL Optimization - CASTing and CURSOR - hi, Is CASTing cost an additional overhead to the query? I need to optimize our database initialization and i reviewing some...

Executing SQL Server Jobs from a remote server - Hi! I have a remote SQL 2012 server on which I am not allowed to create linked servers, run SSIS,...

Strip leading zeros of a field and add a zero to the back of a field. - Hi Guys I have a field called Account. It consits of the following sample data. 0090 >100 0091 0092 I need a script that will strip the...

Date formatiing - Hi, Here minibgtime and maxendtime are in below format 2013-02-19 16:15:48.000 select datediff (Mi,Cast((minibgtime) as datetime), Cast((maxendtime) as datetime))/60) as Total_Minutes from tablename Now...

Where to push-pin the object explorer? - It's a silly question but I couldn't find a answer, I don't know what quick key I touched but the...

How to script profiler trace to save results to a table ? - Hi all, I created a trace in Profiler GUI. I set it to save results to a table and it is...

Query Help - Hello Everyone I have one task I need to do pivoting [code="sql"] create table #Temp1 ( iSchoolYearCode int, dtCalendarDay datetime, iCalendarDatCategoryID int, vcCalendarCategoryCode varchar(25) ) insert into #Temp1 values (2012,'2012-08-30...

Error reading a csv (already open in Excel) using SSIS - hi, I'm loading data into a table from a csv file using a data flow task. If the file is already open...

i want to find the group members. - i am having the code for finding group members when i am executing in ssms 2008 r2 i am getting...

Date in BCP out file name - Hi, How can I insert current date in BCP out file name? I use this command, in SQL JOB step (type: Operating...

Display on Month and Year from date formula ?? - Hi, I want to display only MM/YY from the formula of @enddate -21?? Thanks

Merge Replication: Event ID 14151 - Hey All, I'm in the process of setting up Merge Replication on two test databases before I roll it out...

SQL Server 2008 : T-SQL (SS2K8)

T SQL statement issue - Guys, I have a requirement as follows: CREATE TABLE #temp (CallDate DAtetime, Talktime INT, ID INT, others Varchar(10)) INSERT INTO #temp SELECT '2013-01-01', 10,1,'ABC' UNION SELECT '2013-01-01', 10,1,'---' UNION SELECT '2013-01-01',...

TSQL joined Views - Hi all I am having some problems understanding this one. I have a stored proc (written by the developers), that's using up...

Delimited Values + Generic Query - All, I have to write a generic query for the below requirement. Table Structure: [code="sql"] create table swift_spec( bra_code varchar(5) not null, cur_code char(3)...

How To Make Unique Constraint Case Sensitive ? - I Have Table With Column With DataType Varchar(50) Which Is Having A Unique Constraint On That. For Example : I want To...

NEWID() - How does it work? - Okay, I have generalize SQL quesiton that I think belongs in this forum more than the General Quesitons. In my...

Find Row Number Based on Column Information - Hey Guys, I'm trying to find the ItemCode of the TreeType 'S' Above the Line that I'm running the query...

Grouping sets - Hi All, how to use the groupingsets to a query having more than 32 columns plz help out

Loading T-SQL Statements from a database - I want to call all of my heading from table A and call my select statements from Table B. TABLE A ID...

Every 3rd Friday of the Month - I'm trying to find every 3rd friday of the month The Following code retruns for the current month SELECT CONVERT(CHAR(10), DATEADD(wk,2,(CURRENT_TIMESTAMP - Day(CURRENT_TIMESTAMP)+1)+(6-DATEPART(dw,(CURRENT_TIMESTAMP...

SQL Server 2008 : SQL Server Newbies

Query - Pulling Data from another table - I am getting a tad frustrated and was wondering if you can help: I have a Pitch Values Table with the...

date problem in flat source - Hello everyone, So I really am a newbie and I have a flat source ( csv file ) with some dates. But all...

it takes too long to insert data to different tables - I was trying to dump data from a Temp table to 4 different tables (Computers, ComputerInfo, Vulnerabilities, and AuditInfo) in...

Clustered indexed primary key not in asc order when selected - Hi All, I have a table that has a primary key which is a clustered index. However when I select data based...

Creating a Ref Number usning letters and numbers. - I have a column on my database called Booking Number this is to be used on correspondence etc however I...

Delete taking time due to foreign keys - Dear All We have a base table and 10 other tables related with foreign keys to this. One of the foreign...

Update query will not run? - The following query gives me a random date between 1 and 28 days after the arrival date: SELECT ArrivalDate, DATEADD(day, 1...

Max Server Memory - We just took over a small network with 2k3 running Sql 2005. We virtualized it, per customer requirement, and seeing...

SqlServer 2012 and Windows XP - Most of the machines in my office are still on Windows XP. I built a little database in Access so...

SQL Server 2008 : Security (SS2K8)

Login Auditing - I have been asked to provide documentation on where SQL Server displays messages for login auditing. We set the auditing...

SQL Server 2008 : SQL Server 2008 High Availability

Log Shipping Stanby Restore - I am currently log shipping databases to a warm recovery server and my users would like to have read access...

SQL Server Cluster Installation Problem - Hello, I am installing SQL Server 2008 R2 as a new cluster (installing a single node first) on Windows Server 2008...

To setup an alert for balancing sql cluster nodes in the event of a failover - Here's what I am really looking for... We have a 2 node cluster setup in our environment, and in the recent...

SQL Server 2008 : SQL Server 2008 Administration

How to relocate the msmdsrv.ini file for OLAP - We recently purchased a high-performance SAN and I'm in the process of migrating stuff off from the old SAN drives...

i wish to move 'all' data for my teamserver databases to another Drive. - hi there. i have been doing some research on moving my Team Server Foundation Databases from one Drive, to another, on...

Track queries that caused index scan - Hi there I'm looking for a way to find the queries that are causing index scans on the SQL Server instance.I...

Transaction Logs Full - Hi Guys, New here and new to the SQL administration. I have a SQL server 2008 r2 with 3 data...

DBCC consistency error - Hi Team Last week we run dbcc command on few databases and found the below consistency errors in the database ,no...

SQL Cluster Gateway Info Lost on Node Move or Failure Simulation - Hello All, First time poster so if this has been asked, please forgive. I have searched the forums but didn't find...

Transaction Log Backup - Our company has never backed up the transaction log file, and is now about 143 gig, with about 35 gig...

Differential backups rendered useless by backup device - This is my situation. Hope someone can give some advice. I created a maintenance plan for a production database that takes...

Script to Add User to all Databases - Hello Everyone I have lost my script that I use to add a user to all databases. Does anyone have one handy...

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

but can they replace SSRS? - Do you know of alternatives to row and column quantitative reporting besides Qlikview and Tableau? I think both are smashing...

Problem with a report. - Hello, I have a Dataset with: Company, Depot, Account, Year Period, Ethical, Glaxo, Drug, Surgical, General, PI, Misc, Phone, PostCode, CustomerName,...

Hiding the Show/Hide details button on the Report Server - Although you can make a folder invisible in the view list, if you click on show details, the folder shows...

Data Warehousing : Integration Services

Convert Currency in SSIS - Hello, has anyone used SSIS to convert currency based on a date from a table? For example; If I have a table with...

Flat File connection Manager - File Name, The Folder in File Name changes every time the package is reopoened and needs resetting - Very strange issue. I have SQL Tables and Im using a For Loop to split the table up via a variable...

SSIS ERROR LOGGING - Hi can some body guide me best way to ERROR LOGGING in SSIS? Thanks in Advance

Import from .Excel .xls failis on 2012 - ( Origally posted in the 2012 forum, but I haven't had any replies. apologies!) Hi, I am seeking some assistance in troubleshooting a...

Can I use transactions for non database related control flow tasks - Guys, I have a package that has two tasks. The first is an SQL task which updates two tables (batch headers...

Export multiple files to Excel using SSIS - Hi all Can someone please help me with a link that has an example on how to export data to multiple...

Data Warehousing : Analysis Services

Related measures - I have two related tables in my DSV. The first table called Test has a primary key column called test_id...