SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

It Starts with Version Control

As a software developer, I learned about the value of version control over time. My first forays into development were as a child, where I made a single backup of my code on a separate floppy disk in the event I had an issue. This was my one-man-wolf-pack version control system, and it served me well, although it wasn't efficient. In a few of my early development positions, we zipped up all our code each night to a separate folder, giving us the pentagram-version-control system. This worked as long as we didn't need to go back over a week, but it still wasn't very efficient. I think that because we worked on fairly small projects and could hold most of the code in our minds, we muddled through issues.

Later I was introduced to a real version control system (VCS), Visual SourceSafe. Some of you cringe, but that system worked well. We could see the differences in code, and over time I learned to cut loose with my development, just trying new things, knowing that I could easily revert code backwards without much effort. Since then I've worked a bit with Subversion and Git, both of which seem to be very capable, mature systems. I still plan on getting some work done with Mercurial and TFS at some point, just to compare the features and functions.

I started source controlling my databases after reading an article by Bill Wunder. This was over a decade ago, and it seemed to make sense to me. All of the things I had learned about VCS worked with databases as well, and I learned that having a database under VCS provided me with some stability in development, including the ability to roll back to previous versions of code when we found bugs. Trust me, checking out a previous version of a stored procedure is much, much less stressful than restoring an old backup just to look at stored procedure code.

These days I see more and more people putting their databases under version control, which is comforting. It makes me think that we, as data professionals, are maturing in our industry. However there are still lots of people out there that don't use a VCS for their database (DDL) code. Some don't even use a VCS for their application code.

There's no excuse these days. Subversion and Git are free and open source. Download them, spend a few days, and get control of your code. All of the ways in which you can improve the efficiency and quality of your software start with more knowledge, and a bit of version control on which to apply your craft.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 23.4MB) feed

MP4 iPod Video ( 27.2MB) feed

MP3 Audio ( 5.7MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL Backup Pro

"A real time saver" Andy Doyle, Head of IT Services

Andy and his team saved time by automating backup and restores with SQL Backup Pro. Find out how much time you could save. Download a free trial now.

Deployment Manager

Deployment Manager 2 is now free!

The new version includes tons of new features and we've launched a completely free Starter Edition! Get Deployment Manager here

SQL Monitor Hosted

New! SQL Monitor Hosted

Start monitoring your SQL Servers in under 5 minutes. Get clear insights into server performance, whilst we manage the monitoring software. Find out more.

Featured Contents

 

Stairway to SQL Server Replication: Level 3 - Transactional Replication – The Publisher

Sebastian Meine from SQLServerCentral.com

The Publisher is the database where all replicated data originates. Each Publisher can have multiple publications defined within it. Each publication contains a set of articles that all need to reside in a single database. Each article corresponds to all or part of a single database object. A single database object can map to an article in more than one publication. More »


 

Free eBook: SQL Server Transaction Log Management

Press Release from Red-Gate

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


 

Microsoft's Log Parser Utility: Swell ETL

Additional Articles from SimpleTalk

For loading text, CSV or XML files into SQL Server, the Log Parser utility, with its amazing SQL engine, is likely to be the obvious choice. Although initially developed purely for converting IIS logs, the Log Parser can turn its hand to a range of formats including even event logs or the Windows registry. More »


 

From the SQLServerCentral Blogs - Testing network throughput with iperf

kleegeek from SQLServerCentral Blogs

Have you ever wondered if that black box in your datacenter called the network is performing as well as it... More »

Question of the Day

Today's Question (by Steve Jacobs):

IN BIDS (SQL Server 2008 R2), which one of the following returns the system's container start time in either of the following formats?  Please keep in mind you are using this variable in email messages.

Formats:

  • MM/dd/yyyy h:mm:ss tt
  • M/d/yyyy h:mm:ss tt

(Choose 3)

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 2 points in this category: Integration Services (SSIS).

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

ADVERTISEMENT

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Ford Fairlane):

True or False: In SQL Server 2014 you have the ability to set the following off.

  • SET ANSI_NULLS OFF and ANSI_NULLS OFF database option
  • SET ANSI_PADDING OFF and ANSI_PADDING OFF database option
  • SET CONCAT_NULL_YIELDS_NULL OFF and CONCAT_NULL_YIELDS_NULL OFF database option
  • SET OFFSETS.

Answer: False

Explanation:

The answer is False.

With SQL Server 2014 the ANSI_NULLS, ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL will always be set to ON. SET OFFSETS will be unavailable.

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


» Discuss this question and answer on the forums

Featured Script

Capture User Auths For Database

Donald Patterson from SQLServerCentral.com

This script is for capturing user authorizations on a SQL instance.  This script is by database versus user.  Thank you to everyone for their feedback on the first script I submitted.

This procedure takes in a valid database name and will return all   authorities for the logins with the database as their default database.  The script captures any server roles, database roles, and any database permissions and authorites granted on the default database along with any other database the login has access to on the SQL instance.
 
If the database name is not provided, it will generate the access and authorities for all logins which could take some time.
 
This procedure uses the stroed procedure sp_hexidecimal and Aaron Bertrand's sp_foreachdb stored procedure

This has been tested on SQL Server 2005 and SQL Server 2008 R2.  Please note that some facets on each of these SQL versions have not been tested due to lack of availability.
  
EX - sp_help_revlogin_db 'DWProd' 
 
EX - sp_help_revlogin_db

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 2014 : Development - SQL Server 2014

Microsoft SQL Temp Tables (without declaring columns – like Informix)? - I recently changed positions, and came from an Informix database environment, where I could use SQL statements to select one...


SQL Server 2012 : SQL 2012 - General

SSIS 2012 Failure Saving Package - I've copied SSIS packages from 2005 MSDB to a folder. THen I opend SDT in 2012 and copied package from...

SSIS WMI Event Watcher Result - Is there a way to access the result of the WMI Event Watcher Task? If I look at the logs, the...

SSIS adding data sources from excel file - Hi I have a number of datasources i need in a SSIS package and the specifications are listed in an excel...

RESTORING SQL 2008 R2 DATABASE ON SQL 2012 - hi, How can i restore a 2008 R2 Database on a SQL2012 server?

Import / Export Wizard XML file - Hi , I want to import a XML file into a table to see some data. The import and export wizard does...

Sql connection with port number - Hi all, In My machine i have two instances i.e. one is default and named instance , How i can connect named...

Using SSMS 2008 with SQL 2012 - I'm struggling to adjust my practices to the changes in SSMS 2012. There are a lot of things that I...

SSIS Export different tables to different files in one SSIS Package - Hi All, thanks for looking at my question . at the moment, i need to do something at work which specified...

Change initial sixe of transaction log file - Dear All We have a db with Full recovery Model. Daily full backup is taken and every hour transaction back up...


SQL Server 2012 : SQL Server 2012 - T-SQL

How to insert data into table from linked server - Hi all, I wonder if it possible to move data from tables on a linked server to a "normal database"? I am...

Improving DELETE performance - Hi, in a my stored procedure I need to use a DELETE statement and I cannot use the TRUNCATE TABLE. The table...

How to find whether an object is used by any sp , view ? - How to find whether an object is used by any sp , view ? I need a query which will result the...

stored procedure - Hii, I am trying to write a stored procedure to check whether all the columns of different tables are filled or...

SQL Report Builder - Hi, Not sure where to put this... Im working in SQL Report Builder (sorry dont know which version) and am having trouble...


SQL Server 2008 : SQL Server 2008 - General

what will be the best delimiter for bcp file? Dont want to use , |,^. Any suggestion will be appreciated - We are migrating dbs from sql server to oracle. I have 50+ table with million + rows. I was going to...

What happens if you use replication services to replicate views - Ok, this is a question I've so far not seen a clear & cut answer for. I got the following situation on...

INSERT that automatically increments sub sequences and generates "hierarchical" string, keeping in mind concurrent invocations - Hi, In order to write a post system that accepts multi-level answers, I am looking for an efficient "INSERT" statement that...

Create Table Default Data Type Length - Hello Everyone I have a rather odd kind of question that I cannot seem to find an answer in the BOL. When,...

Reporting changes to a database table - My latest mission at work - well, one of them, is... I have two tables - Suppliers and Supplier Addresses. I need to record...

264] An attempt was made to send an email when no email session has been established - Hi 264] An attempt was made to send an email when no email session has been established I see following error...

Robocopy - Hi I'm using Robocopy within a Agent job to copy a backup file to a shared location. It works fine, but,...

CPu high - Hi, User was getting error saying that database locked out.and when i logged into server i could see Sql is taking high...

Check change in database - Hello, I have idea about sending all changed which somebody do in sql to email, because here is little problem that...

Exporting Maintenance plans - We are moving our databases to another server (both old and new server SQL Server 2008 v2). Is it possible...

Modifying each row based on attribute in the row - I have created a stored procedure that will return a list of test values for a SPC (Statistical Process Control)...

Schedule a SQL Job only for the first ten working days of each month - I want to make a schedule in SQL only for the first ten working days of each month. What's the best...

SA login question. - Good day, I found an issue where I am unable to log on the the local instance with my SA credentials...

Sqlserver Management Studio problem - Hi Professionals Everytime I go to log into Sqlserver management studio on the Connect to Server prompt it always shows a...

Last one hour database status - How to check that how many raw are inserted, updated, deleted in last one hours for all the databases of...

Changing the SQL Server license key - SQL Server 2008 R2 with SP2 on WIndows 2008 R2 Recently, i have installed a SQL Instance (2008R2 Standard edition x64)...

Use placeholder for complete list - hi, all I doing some stat reports, and every day it has different products (not all), but for my ssrs data...

Columns to Rows - Looked all over, found rows to columns can't find this. Desired Result (notice A distinct) two fields, one with id, other...

Is point-in-time possible after restore - Scenario: SQL Server 2008, Database Full recovery model, full backup every night, taking approx. 2.5 hours to complete. log backups every...

Update query with & without null values - Hi All, I have a grid with checkbox, where users can select multiple rows and edit at the same time and...

Cursor fetch loops endlessly - I am having a cursor which I use to update a 2nd table containing unique IDs from an identity column. The...

Transactional Replication Performance Issues After Migration From 2000 to 2008 - We currently have 140 SQL 2000 publications replicating to a single 2008 server with about 25 articles. 5 of the...

SSIS Oracle Instant Client - I need to create an SSIS package that connects to an Oracle 10g database on another server. I am running a...

Insert Rows from excel file - I have an excel file with the items listed horizontally...I need to insert each item as a row in my...

Update Stats Job causing Dropped Connections and Cursor not Declared errors - Has anyone ran across or have seen dropped connections from TIBCO and getting a 'cursor not declared' error message when...

Rename SQL Server 2008 R2 Cluster Instance Name - I have a Sql 2008 cluster with SQL network name as bbmnet but the cluster name is bbmnetsql , now the...


SQL Server 2008 : T-SQL (SS2K8)

problem with new query ... - Hi, i have to do a view but i have problem with query its all about asset. i have table with data...

How To Pass Multiple Date Values In a Single Parameter(comma separated) - Hi Guys, I need to pass comma separated date values in a single parameter and do a Year on Year...

SQL Query help - 1. First Query Gives me Outstanding Amount for Billing. Here Transaction type is ('SLINV','SLCRD') SELECT C.COMPANY_SK, CUST.CUSTOMER_DESC, CUST.CUSTOMER_SHORT_DESC, SUM((SL.BASEVALUE)*CASE WHEN CURR.RATE IS NULL THEN...

ISNULL not working - I have a select statement in my existing SP, which is not working: [code="sql"] select IsNull(St.alternateStudyCode, St.studyCode) AS StudyCode,* from Studies...

how to display totals grand total in t-sql - hi, I have a table class classname section Marks first a 800 first a 200 first b 100 first b 200 second a 400 second b 400 first a...

T-SQL puzzler - given a cell in an N-dimensional space, return the cells that are inline with the given cell along each axis - The cells in an N-dimensional space are modelled with the 2 tables below. A script is needed that takes a...

Age - I have a table called member which contains memberid and DOB. How can I calculate age to date based on...


SQL Server 2008 : SQL Server Newbies

SQL Server 2008 - How can I find out if my SQL Server is under stress? Here is the version information, Microsoft SQL Server...


SQL Server 2008 : Security (SS2K8)

Need to use Kerberos, but it still is using NTLM - I am having trouble with my SQL server not using Kerberos. I read Brian Kelly's article on sqlservercentral.com, but I...


SQL Server 2008 : SQL Server 2008 High Availability

how to protect the data loss with database snapshot - I have database where continuous changes are happening I do take backups on regular interval bases and also take snapshot...


SQL Server 2008 : SQL Server 2008 Administration

Create Login and create User. User not visible - I Create Loin and then create a User. However the User not visible. Something seems corrupt. The 3rd party company told the...

Migrate from SQL 2005 to SQL 2008 -- Pre/Post Migration Steps? - Hello, I'm preparing to move a production database from SQL 2005 to SQL 2008, and wanted to know what pre/post migration...

SQL: creating assembly:: - Hello team, What's the best practice for SQL 2008 r2 creating an assembly for an existing functional non-DotNet DLL? In another...

Server is ignoring Max Server Memory - Hi, I have a production server that has been having weird slowdowns. I suspect memory. I have set the 'max server memory'...

Server side trace question - I've started monitoring on my SQL 2000 servers by running server side trace through SQL job. The steps of the job...

Corrupt Backup File ? - [size="1"]I got this message when trying to restore a DIIF backup Msg 3183, Sev 16, State 2, Line 91 : RESTORE detected...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

String or binary data would be truncated. Error when inserting empty table results into TEMP table - Has someone ran into something strange like this where no data is being inserted but this error happens. Msg 8152, Level...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Recommended websites/book about PT - Hello! Do you have any good websites or books to read about best practice or advice for performance tuning?

ASYNC_IO_COMPLETION while BACKUP DATABASE running - Is it normal for this lastwaittype to last for the entire duration of a backup database command? We're seeing the...

Index creation help - I was analysing an execution plan before and after creation of an index. Disk IO decreased considerably after creation of...


SQL Server 2005 : Administering

query to get updates/inserts done everyday - Hi, Is there any query to know what type of changes done by users on sql server tables?

Database Mail does not work from SQL Agent Job - Hi all, We are running Sql server 2005 Sp1 on windows 2008 Sp1.The Sql server agent is up and running but...

Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "CHIMSSQLDEV" does not contain the table ""ArrowProdDB"."dbo"."sysfiles"". The table either does not exist or the current user does not have permissions on that table. - I can select from a master table however get this error for all user databases. any idea what is going...


SQL Server 2005 : Backups

Using Robocopy within SQL Agent job - headache with error codes! - Hi all Some of you will know the headaches of using Robocopy within a SQL Agent job via a windows batch...


SQL Server 2005 : Business Intelligence

ssrs 2008 delay in report - first run - There are two server for SSRS. Prod. and Dev. Every morning the very first user who runs any report on the Dev....

ssrs 2008 page break on export to pdf - Hi, In my ssrs report, I am using tablix at the bottom of the page to do page break after. The report...

ssrs 2008 report timeout - Hello, The ssrs report is calling a stored procedure to populate data... The stored proc. takes about 15 minutes to complete. Therefore...


SQL Server 2005 : CLR Integration and Programming.

Debugging SQL Stored Procedures in .Net IDE with ODBC driver - Hi guys. I'm very much able to debug SQL stored procedures in visual studio when i choose the data source...


SQL Server 2005 : Development

Help in writing a query - Attached is sample data in excel sheet. Basically i am trying to find out the keeps and drops for sql...

Case Vs IF Else - Hi, In one of my project I am using both case as well as if else statement the condition and required...


SQL Server 2005 : SQL Server 2005 General Discussion

Compatibility_44_40a_30003 Collation in database - Hello, In a SQL Server 2005 we have a database with SQL 2000 compatibility mode. The value recorded in the 'collation_name' column...

Convert a field from numeric type to date format - Hi, DB version: MS SQL server 2008 R2 I have a date field which stores date as numeric. How can I...

T-SQL syntax - Getting a syntax error on the "WHERE" clause (second last line)... That clause should modify the main "UPDATE tblBoMDetail" command... Ideas? [code="sql"] DECLARE ...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS parameterized package problem - Hi, I'm hoping to get help with a problem I've been struggling with for several months. I have inherited a...

Failure of SSIS Package when Executed from SQL Server Agent - Good day, We’ve created a basic SSIS package that connects to an Oracle Database, retrieves information, and dumps the info into...


SQL Server 2005 : T-SQL (SS2K5)

replacement of format(0,"percent") in SQL - do we have a replacment for replacement of format(0,"percent") in SQL?


Reporting Services : Reporting Services

2008 vs 2012 - Same Proc, different outcomes - Hey guys, I've been tearing my hair out over an issue with SSRS for weeks now (yes, weeks). I've got a...

Multiple Tablix with One to Many Relationships - Hello! I am hoping someone can help me with this - I am quite certain it can be done, I just...

Send Message with SSRS to user's Email Acount Automatic - Goal: Make the user to retrieve an incoming message to their e-mail account containing a report document once in a month. Problem: How...

Reporting Services Errors SQLServerAgent exceeded the allowed timeout - Hey There Coming across the below error in our reporting services log file can anyone tell me what is causing this...


Reporting Services : Reporting Services 2005 Administration

Getting an invalid report server database error - I've just tried to run SSRS off of the new server we're moving it to, and I've gotten the following...


Reporting Services : Reporting Services 2005 Development

#Error - I have a report that works perfectly in Visual Studio but when I deploy it to Report Manager running on...


Reporting Services : Reporting Services 2008 Development

Organising Tables (NOT ROWS) based on Dates - Good Afternoon, I have been tasked with creating a travel Itinerary, simple stuff.... however upon further investigation each part of a journey...

SSRS matrix group by error - I have an ssrs matrix with total in rows and months as columns .. something like this . [code="other"] Jan Feb March Total1 Total2...

ssrs 2008 export to excel leaves blank row - In an SSRS 2008 R2 existing report, I want to change the report so the users have the option to...


Programming : XML

Retrieve XML Code by API with Automatic Approach - Goal: Retrieve a XML list from Internet, using API connection, and transfer the data into a sql server's table automatic using...


Programming : Powershell

Powershell Script help for Scripting - Hello , Please assists me if any one has idead about this . I have script in powershell which script out SP...


Data Warehousing : Integration Services

Proper SSIS deployment method - What is the proper way to deploy an ssis package? More specifically, I find some level of complexity around what I...

Import of CSV file only inserting half the rows - I am running into an issue where only half of the rows from a csv file are getting inserted into...

SQL SSIS to Oracle CLOB Performance Issue.. - Hi I'm attempting to push data from SQL via SSIS DT_TEXT to an oracle CLOB field, but performance is utterly horrendous....

Retrieve XML Code by API with Automatic Approach - Goal: Retrieve a XML list from Internet, using API connection, and transfer the data into a sql server's table automatic using...

SSIS PDF - Can I generate PDF extracts while extracting data from SQL databases? I have been able to create .doc,.txt and .html files...

ssis script task throws error when run from sql agent job - When I run this package from Visual Studio, it works fine, but when I run it from the sql agent...


Data Warehousing : Analysis Services

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


Database Design : Relational Theory

Database without ANY primary keys - Hi all, I work with a database that has no primary keys defined on any tables. As a customer's consultant I...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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


SQL Server 7,2000 : T-SQL

Conversion failed when converting the varchar value '98.97%' to data type int. - Hi guys, I need your help. I am receiving the following error from the set @percent line "Conversion failed when converting the...

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 ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com