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 Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.
 
Deployment Manager How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.

In This Issue

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO. More »


Raw Materials - Beyond Global

There's more than one way to be green. More »


Getting Started Formatting SSRS Reports

Once you've prepared an SSRS report, it needs formatting to get it ready for the people who need to read it. This tip covers the basics of formatting SSRS Reports. More »


From the SQLServerCentral Blogs - PowerSQL – Find Size, Name, LastAccess and Last Modified time of all bak files across ALL Servers

We came across a scenario where database bak files being created from long time which are no more in use... More »


Editorial - The Control Poll

I was reading about version control systems (VCS) recently, brushing up on some skills, and saw this quote in a thread:

"There is no excuse for not using version control, even for a small project developed by single developer. Setting up local version control is beyond trivial, benefits huge. Any developer not knowing that cannot be considered good nor experienced."

That's quite a pronouncement, and one that I believe is very true. No matter what type of development you engage in, I'd expect that you'd understand the benefits of using version control, and the dangers of not using it. It's just like never backing up your system. I'd think that any developer that cares about their craft and is a professional has used version control. The really good ones will insist upon it.

However I know that the decision to use a VCS is not always made by a developer. The company building the software might feel differently, and while I've always asked for a VCS, I have ended up with a series of folders on a share, named for dates, each containing a zip file of all our code at the end of that day. It was the bare minimum of version control I could live with, and fortunately we got by with just two people coordinating work. Any more than that and I'd insist on some type of VCS.

This week, I wanted to ask how many of you voluntarily or involuntarily might be forced to do something similar. 

How many of you skip source control for certain apps?

Even if you have source control for those large, multi-person teams, are there apps that you avoid putting into a VCS? What about your database code? I think it's important that you keep all your code, whether for the front end application or database objects, in some type of Version Control system. If your boss won't buy one, then check out Git or Subversion, both of which are open source and free.

Let us know this week how you feel about source control and whether or not you decide the effort isn't worthwhile for your projects.

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

CREATE TABLE [dbo].[test]
(
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [PK_col] [int] PRIMARY KEY,
      [Col1] [int] NULL,
      [Col2] [int] NULL,
      [Col3]  AS ([col1]+[col2])
)

SELECT *
 INTO TEST1
FROM TEST

---Statement1
INSERT INTO test
VALUES (1,2,3)

---Statement2
INSERT INTO TEST1
VALUES (1,2,3)

What will be output of these two statements?

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

What will happen on executing the CREATE index statement for this table's definition in SQL Server 2012?

Answer: An Error message will appear because column AmountAM has precision greater than 18

Explanation: The correct answer: Error message will appear because AmountAM has precision greater than 18. This error is returned:

Msg 35341, Level 16, State 1, Line 1
CREATE INDEX statement failed. A columnstore index cannot include a decimal or numeric data type with a precision greater than 18.  Reduce the precision of column 'AmountAM' to 18 or omit column 'AmountAM'.

Ref: http://msdn.microsoft.com/en-us/library/gg492088.aspx

» Discuss this question and answer on the forums

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.


Featured Script

SSIS Synchronize Remote FTP Directory

Get a distinct list of file names from a table into an ArrayList and compare against a remote FTP server. Retrieve only the files that have not already been processed into the database. 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

Stop synchronising few tables in replication. - Hi, One of the publication has few temp tables, which synchronises the data with subscriptions. I dont want this to happen....

Stuck with this update statement - I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120....

SQL Server has encountered 1 occurrence(s) of cachestore flush - I am getting below error when i try to restore database in sql server 2005 SQL Server has encountered 1 occurrence(s)...

SQL Server 2005 : Backups

Disaster recovery - Hi, Can anyone please help me...I am new in SQL Server. I have scheduled the database backup--- full backup-- everyday morning differntial backup--every 2...

SQL Server 2005 : Business Intelligence

ETL Load Approach - We have a table 'Sales' which is Fully loaded daily. Our target delivery is insert else update, So every time...

Best Practice for loading DW tables from live source with timestamp/Rowversion column to identify changes - I want to build a DW and I need to identify records that have changed, and luckily each table in...

SQL Server 2005 : Data Corruption

Sharepoint database 'docs' corruption ... what if? - Long story short :D We inherited a sharepoint database on SQL2k with corruption dating back 2 years (so no usable backups)....

SQL Server 2005 : SQL Server 2005 Security

How do I remove a table where TABLE_SCHEMA IS NULL - I have an application that creates and removes tables as part of its process. For some reason it doesn't always...

SQL Server 2005 : SS2K5 Replication

Dropping Article - I have a publication with 10 tables as articles and which is subscribed. Now I want to remove one of the...

SQL Server 7,2000 : Administration

msdb database not REALLY in single user mode? - I've run the following commands: [code="sql"]Sp_configure 'allow updates', 1 Reconfigure with override GO sp_resetstatus 'msdb' GO update master..sysdatabases set status = status | -32768 where name = 'msdb'...

BCP with query how input file - Hi: I want to generate a BCP commnad, but my query is very very large: ¿Is possible to put the...

SQL Server 2008 : SQL Server 2008 - General

Delete Statement on Fact table (Transaction data table) - Hi All, I have a Transaction Data holding table, which has data around 10 to 20 Million rows. Now the problem, is...

How do I generate Conditional TRUNCATE statements ? - I regularly copy our Production database to our Test system and then need to delete most of the data, while...

Need Help - Okay, I have created an application where you can add what we call (Export Scripts) and when you run the...

created statistics on columns because of an execution plan warning - yet, the execution plan is still complaining that the same columns i created the statistics on have no statistics on...

exporting hidden columns in SSRS R2 in Excel - Hi there, Have SSRS report with 2 hidden columns that must be a part of export into Excel. What do i do? thanks...

Connection Manager Properties - Hi, I need some assistance in adapting this connection string expression to retrieve the previous day rather than the current day-...

What is @Dummy ? - Hello all! I've been assigned to deconstruct and document a rather large Stored Procedure (SQL 2008). This procedure simply declared...

SQL2008R2 Connection reset by peer: socket write error - New application is getting this error: ContentManager.log-2013-05-15:ClientAbortException: java.net.SocketException: Connection reset by peer: socket write error ContentManager.log-2013-05-15:Caused by: java.net.SocketException: Connection reset by peer:...

Check each cell in one table and update another table - There are two tables as below: COLOR and ISCOLOR. I need a loop to check cell by cell in table COLOR. If...

Antivirus on a SQL Server VM - hi guys, my vm admin wants to run AV at the host and not at the guest level. what do...

same function from sql 2000 doesn't work on sql 2008 r2 - We have very strange situation I hope someone can help, we copied a database from sql 2000 sp4 to sql...

Missing Statistics - Good Day. We are running SQL Server 2008R2 ENT and kept auto update stats and auto create stats enabled. We identified...

polygon-geography - Hi all, i'm facing some issue in geography datatype when converting into polygon This working fine DECLARE @polygon GEOGRAPHY SET @polygon=GEOGRAPHY::STPolyFromText('POLYGON ((-0.10200500506471144 51.517787451292115,...

single quote issue - Hi all, I have to update a column where I am giving query which is Update Table Set col = '[189] IS...

sql copying column attributes of one table to another table - please find the attached file... need a query for the table design i have mentioned here...

sql copying column attributes of one table to another table - i have 2 tables one call it as dbo.t1 and another dbo.t2 t1: P1 S1 B1 H1 S2 J1 ______________________________ b1 s1 b1...

The multi-part identifier "Members.MemberID_" could not be bound. - Hi When runing queries I keep geting the above error. Does anyone know how to resolve this pls? thanks

sql copying column attributes of one table to another table - i have 2 tables one call it as dbo.t1 and another dbo.t2 t1: P1 S1 B1 H1 S2 J1 ______________________________ b1 s1 b1...

SQL AGENT JOBS ARE FAILING - Hi Team, I have problems with sql agent. Getting below error. SQLServerAgent could not be started (reason: SQLServerAgent must be able to...

Trigger to fire when inserting rows in tbale - Hi ALl I am trying to fire a trigger when I am trying to excute the statement : SELECT * INTO Table_BACKUP from...

Query result - [code="other"]DECLARE @SQL NVARCHAR(MAX)='' DECLARE @LinkNumber INT DECLARE @LinkCount INT=0 DECLARE @LinkTable NVARCHAR(101)='' SELECT @LinkNumber= NoOfLinks FROM [ABC].[dbo].[MyTable] WHERE TableID=7 IF...

Tracing failed SP call - Does anyone know if its possible to trace failed SP calls through Profiler? For example, if you call an SP...

update statement - what's the locking/blocking risk? - Hello - I recently added a new column to a db table with 6.5 million rows. Now I need to populate...

Case with Wildcards - I am having trouble with a CASE statement like this. The All Cars doesn't work. SELECT CASE WHEN CARS.Model LIKE '%Ford%' THEN 'Ford' WHEN...

Execution Plan - when i'm looking at stored procedures i add the following to the top of the code dbcc freeproccache set statistics io on set...

Red Gate Sql Monitor Alert is downgraded but still shows as HIGH - Hi I have been reorganising/rebuilding some indexes due to high fragmentation due to an alert generated by Red Gates sqlmonitor. I have...

Adding text to Rank - Hi All, Please i need help to complete this query, what it does is that it ranks some records which works...

SQL Server 2008 Audit - Hi Guys, I wish to audit all the activities performed by users having sysadmin role. Is there any straight foward way to...

Inserting records with a TIMESTAMP field for Archiving Table Data - Because I am paranoid (well, I call it 'realistic'), I like to backup my data before I delete anything and...

SQL Server 2008 : T-SQL (SS2K8)

Database backup striping - Hello Pros, I manage 100 + SQL servers and wanted to find a way to backup all databases to multiple .bak files...

Query two different Servers - Is there a way to query two different servers at the same time if they aren't linked?

Selecting DISTINCT - I have three fields I am interested in: Employee_ID, Employee_Title, Employee_Entry I want to be able to select these three fields but only the...

Intelisense is not working !!! Even After trying everything mention on msdn website - Hi all Experts, I am trying this since a long time. My intellisense is not working with a specified [b]Database[/b]. Intellisense...

Help with Round to the nearest two decimals - Dear friends, I need to do a urgent requirement- I'm facing issues with rounding the duration to a whole number. The new...

Another grouping problem - I have found a bug in my duplicate macthing application and I believe it's with the way I group my...

OUTPUT CLAUSE - MERGE STATEMENT - Does anyone have a basic example of the use of the OUTPUT CLAUSE in a MERGE Statement? I found examples with...

SQL Server 2008 : Working with Oracle

Compare an Oracle Database with a Sql Database ! - Hi, first of all, i'm biginner in Bi Consulting with Microsoft tools. I want to compare two databases : Oracle Database and Sql...

Installing Oracle 11g Client in a sql server 2008R2 active/passive Clustered Environment - I'm trying to get a step-by-step approach to installing Oracle 11g Client in a sql server 2008r2 Active/Passive Clustered Environment....

SQL Server 2008 : SQL Server Newbies

Restore with Replace, but keep old views? - Hello, Just a quick question - I have two DBs - "DB 1" and "DB 2" DB 1 is, essentially, the production DB....

Incorrect Syntax near 'MAXSIZE' - I am getting a syntax error when trying to create a database. Here is my code: [code] Create database ToddTestDB On...

automate estore database into database with a different name - I currently have 2 databases on the same server: MAPA and MAPA2. MAPA is being backed up nightly as part of...

What would you do? - Virtualisation again, sorry! I have done a search but this is more about how to handle a particular issue at...

Configure 'Allow Updates' - hi there, hope in your help. I tried this code in sql server, but I've this error. Can you help me? I would...

Copy table+values from one server to another - This might be the dumbest question ever, but I really don't know/remember how this is done. Let's say you've got 2...

mdf file growing - Hi, I am new in SQL Server. I have a database whose size is almost 25 GB, there log size 18...

how to deny logins - Hi all, I've copied a database to a new server. How do I make database on original server inaccessible to users...

Backup and recovery for the accidental DBA - Hello - I am not a DBA, let's start with that...but I have acquired the task of backup up a db...

How to choose which instance of SQL Server I am using - I'm having a hard time figuring this out, and it's a very basic question, so maybe I'm just having a...

SQL Server 2008 : SQL Server 2008 High Availability

Restore is not happening - We have configured the log shipping. here backup job and copy jobs are working fine but restoring is not happening in...

SQL Server 2008 : SQL Server 2008 Administration

Want to configure a server-side trace to capture T-SQL being fired - What I'm looking to do is capture the queries that are being fired against a particular DB, to help work...

Need some advice regarding replication - I would like to replicate multiple databases on different servers into one master database. I have 5 different instances on separate...

Memory monitor - Hi , How to monitor memory usage per sql instance and identify any memory-starved instances so additional memory can be allocated...

ERD drawings for the tables not related - I am struggling to find the relationships between tables. There are no foreign key relationships on the tables and I...

send me som sample type of tickets - generally as a DBA what type of tickets we will get....? send some samples

Need advice of hardware configuration of SQL server in production environment - Dear All, We are having a SQL server which is being used through out day and night from local and outside...

Restart Server to shrink TempDB? - So I had a bad query blow out the tempdb (dev, not log) database to 36GB. It's normally a lot...

SQL Server has encountered 1 occurrence of I/O Requests taking longer than 15 seconds - I am running SQL Server 2008 R2 SP2 x64 and we just increased the RAM to 32GB (against my wishes)...

SQl Agent job when query fails to return result - Hello, I am trying to put in place a job which will email me when a query fails to return results:...

How can I kill ad-hoc or long time running queries, safely? - Ok, Need the final push on this ... I do have a requirement at work that I need to control or kill...

Career : Certification

Failed 70-462. Worth doing 70-461 before resit? - Took 462 yesterday and only managed to get 612 vs the pass mark of 700. Main problem is that I'm...

Programming : General

The Empty Set vs No Result - So, I've got somewhat of an academic question, but one that might have some practical implications... depending on the answer. So...

Reporting Services : Reporting Services

Way To Generate Report From ASP.NET to SSRS - Good Morning Guys, Would it be possible if i have An MVC ASP.NET Web Interface and Generate A Report(Either To Excel...

Permissions to connect to Reporting Services via Management Studio - Hi, Can anyone tell me what permissions I need to assign to a user to allow them to log into Reporting...

Syntax - Folks, Can anyone see what's wrong with the following? It's been bugging me for ages as I can't see any problem...

Help with setting LinkTarget=_blank to open URLs in new window - I want to change links on one report to open in a new window. I have read enough to know...

I would like to have an SSRS report export data to Excel every two hours - Hello, I would like to have an SSRS report export data to Excel every two hours and add the data to...

Permission by department manager from single report to view. - Hello, We are using SSRS 2005 and I have a report I want to give access to 5 different managers the...

Database Design : Design Ideas and Questions

Complex many to many relationships - Hi all, Here is a relatively complex design that I would like to have vetted by experts! :) Say that you need...

Data Warehousing : Integration Services

One row not importing in SSIS package - I am trying to import a text file into a SQL Server table. My package runs fine and imports the...

Dead Lock Issue -In update Scenario - HI , I have a Dataflowtask in which following following Transformations are used ---OLEDB Source[Pulls Data From X StagingTable] ---Balance data distributor is...

Dynamic Connections - Hello All, I have a package that downloads a lot of csv files and transfers their contents into a database. For...

Data Warehousing : Analysis Services

Hiding members from a dimension that aren't used in a measure group - SSAS 2012 Hi, I suspect this is a seriously rookie question but I've not managed to find an answer on it so...

Grouping transactions by Age of customers in SSAS or MDX. - Hi I came across this link [url]http://www.sqlbi.com/articles/grouping-transactions-by-age-of-customers-in-dax/[/url] It's exactly how I want but it's in DAX :angry:, I don't know DAX and the...

Can't get any DAX YTD functions to perform correctly - Hi, I am struggling to make any progress with time intelligence functions in SSAS Tabular (or indeed in PowerPivot). I have...

Microsoft Access : Microsoft Access

How to query sql in MS Access 2007 Find Min & Max with DateTime? - i query sql in MS Access 2007.i want find mix and max function with datetime. [b]This Code:[/b] [code="sql"]SELECT inf.SSN AS EmpNo,...