In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL DBA Bundle Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here.
 
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.

In This Issue

Deploying CLR Assemblies with T-SQL

Learn how to simplify deployment and maintenance of SQL CLR assemblies by using T-SQL instead of Visual Studio. More »


Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS

The easiest ways to maintain and manage slowly changing dimensions is using Slowly Changing Dimension Transformation in the data flow task of SSIS packages. Arshad Ali provides you with the steps needed to manage Slowly Changing Dimension with Slowly Changing Dimension Transformation in the data flow task. More »


From the SQLServerCentral Blogs - Installing SSMS 2012 all by it’s lonesome

SQL Server Management Studio (SSMS) is not offered as a standalone download on MSDN. Installation requires the user to download the... More »


From the SQLServerCentral Blogs - SQL Server – Finding TCP Port Number SQL Instance is Listening on

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There... More »


Editorial - Small Data

Big data is a hot topic these days. Like the "cloud" terminology we've been hearing about for the last few years, there isn't a good definition of what "big data" really is. The best one I've seen so far is data that "doesn't fit in Excel," which I like. So many people perform their analysis on a spreadsheet of sorts, that if the data doesn't fit inside their edition of Excel, they'd probably consider it big.

The problem with big data, however, is that it while it contains more information, it can also contain more irrelevant information. That's noted in this piece on small data (from Brent Ozar, PLF), where the author states the signal to noise ration may be decreased when you examine very large data sets. You may find that there are correlations that appear to causations. With enough data, with enough things to examine, you can often start seeing patterns that aren't really there. These ghost patterns can lead you to draw incorrect, or at least less correct, conclusions if you do not investigate further and test your ideas on portions of your data set.

Some of you might have noticed fractal patterns like this:

This is a well known Mandelbrot Set pattern. However if we were to zoom in on this picture, we'd find that the patterns repeat over and over again. What holds true for the largest image we have holds true inside smaller sections. The pattern repeats. 

The same thing can happen with patterns in business. We may see a pattern in a large set of data, but we should verify that it also holds true for subsections of the same data set before we make a decision based on that pattern.

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

Below are 5 statements, I execute all statements. Which statement(s) execute successfully or give error. Choose your answer below. The answers are in order of the numbered statements.

---- DROP TABLE
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE OBject_ID =  OBJECT_ID('tempdb..#Table'))
    DROP TABLE #Table
GO
---- CREATE TABLE
 CREATE TABLE #Table (OrderNo INT, City VARCHAR(100), Country VARCHAR(100))
 GO
 INSERT INTO #Table
 SELECT 101, 'Reims', 'France' UNION ALL
 SELECT 102, 'Delhi', 'India' UNION ALL
 SELECT 103, 'Münster', 'Germany' UNION ALL
 SELECT 104, 'Lyon', 'France' UNION ALL
 SELECT 105, 'Reims', 'France' UNION ALL
 SELECT 106, 'Delhi', 'India' UNION ALL
 SELECT 107, 'Münster', 'Germany' UNION ALL
 SELECT 108, 'Lyon', 'France'
 GO
 ---- STATEMENT 1
 SELECT * FROM #Table
 GO
 ---- STATEMENT 2
 SELECT DISTINCT City, Country FROM #Table ORDER BY CITY, COUNTRY
 GO
 ---- STATEMENT 3
 SELECT City, Country FROM #Table ORDER BY CITY, COUNTRY
 GO
 ---- STATEMENT 4
 SELECT City, Country FROM #Table ORDER BY CITY, COUNTRY, OrderNo
 GO
 ---- STATEMENT 5
 SELECT DISTINCT City, Country FROM #Table ORDER BY CITY, COUNTRY, OrderNo
 GO
---- DROP TABLE
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE OBject_ID =  OBJECT_ID('tempdb..#Table'))
    DROP TABLE #Table
GO 

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 Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Yesterday's Question of the Day

In the batch 

-- statement #1
create table #QotD1 (Id int, timestamp timestamp)

-- statement #2
insert into #QotD1 (Id)

select top 10 id from sys.sysobjects

-- statement #3
select id, timestamp into #QotD2 from #QotD1 

-- statement #4
select * 
 from #QotD1 q1 
  join #QotD2 q2 on q1.timestamp = q2.timestamp

-- statement #5
delete #QotD2
 
-- statement #6
insert into #QotD2 (Id, timestamp)

select id, timestamp from #QotD1 

-- statement #7
select * 
 from #QotD1 q1 
  join #QotD2 q2 on q1.timestamp = q2.timestamp

-- statements #8
drop table #QotD1
drop table #QotD2
Which of the following is true (select 8):

Answer:

  • statement #1 runs succesfully
  • statement #2 runs succesfully
  • statement #3 runs succesfully
  • statement #5 runs succesfully
  • statement #4 runs succesfully and returns 10 rows
  • statement #6 fails or is not run due to previous error
  • statement #7 fails or is not run due to previous error
  • statements #8 fail or are not run due to previous error

Explanation: Timestamp columns are filled automaticaly with unique numbers. Duplicate timestamp values can be generated by using the SELECT INTO statement in which a timestamp column is in the SELECT list.

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

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Featured Script

Database Growth History

This script uses the backup tables to get info on the growth of your database files. 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

Replicate from Non-Partioned Table (Publisher) to a Partitioned Table (Subscriber) - Is it possible to Replicate from a Non-Partioned Table (Publisher- Standard edition) to a Partitioned Table (Subscriber- Enterprise edition) using...

Database stuck in single user mode - Hi, Suppose you had a database stuck in single user mode that is in a busy OLTP environment. Given that: 1. The connection...

"Sql server error 5120" while attaching database - HI, I am trying to attach few databases to sql server 2005.I have my .mdf files in "D:\MSSQL\Data" and my .ldf...

SQL Server 2005 : Backups

Redgate Differential Backup and Restore - I've recently taken over database duties and am trying to learn as I go. Our company uses Redgate 6.5 for...

backup issues - Hi, Im not sure this is the right area to post this in, but it is backup related. I have SQL...

SQL Server 2005 : Business Intelligence

connection to Oracle - We have a big vendor application and oracle is the database server. We are not allowed to write direct view,...

SSIS-Data in multiple .csv files to columns in same csv file - Hi Everyone I have the below problem,could anyone please suggest me a solution. file 1 has below data 1.csv A B C 2.csv E F G the data in the output...

SQL Server 2005 : CLR Integration and Programming.

Problem with SQLCLR in VS 2012 / Net 4.5 - Hello guys, I have problem that I can't resolve .. I want to make simple store procedure using .net 4.5 and [b]I...

SQL Server 2005 : Development

Difference between varchar(max) and varchar(8000) - Hi, whats the actual difference between varchar(max) and varchar(8000). Is the difference applies same for nvarchar(max) and nvarchar(8000). Thanks in advance.

SQL Server 2005 : SQL Server 2005 General Discussion

sql service broker - Hi Need help on getting code and info on service broker (for sql server 2005 only) for auditing DML commands. Respond if any. Thanks

SQL Server 2005 : SQL Server 2005 Security

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. - [b]Problem Statement[/b]: I have some users that are not a part of any server role (except the default public). For each...

SQL Server 2005 : SQL Server 2005 Integration Services

Merge records in a sorted order in SSIS. - I have a set of record types saying TH as Header and T1a, T1b, T1c, T1d, T1e, T2a, T2b, T2c,...

SQL Server 2005 : T-SQL (SS2K5)

Data transfer while dynamic increment of duplicate values. - Hi, I have to insert data from one table to another in SQL 2005 (destination having lesser but the same name...

Temp Table 'vs' Table Variable 'vs' CTE. - Hi All, I wants to clear my concept about Temp Table, Table Variable & Common Table Expression Concept. Can anybody please clear me...

SQL Server 7,2000 : Administration

User Authentication - I am trying to login with an account (for example user name: SQL1) using SQL Authentication and having problems, it...

xp_cmdshell - How can i disable this command and any other related commands??

SQL Server 7,2000 : T-SQL

Database Mapping Error - Hi I am actually trying to make relationships in an already designed database.. Database is historic one and no proper relationships,...

SQL Server 2008 : SQL Server 2008 - General

Help with SQL Server backups slow ( backup performance slower than before after data purge) - Hi everyone, (searched SQL Central... I didn't find anything specific) Wanted to get some ideas on solutions. I have a DB...

SQL Audit Help - Does SQL Audit Really Store Audit Info ON The Windows Logs - First off I admit I am no SQL Audit guru but from what I have read so far it sounds...

SQL Server 2008 R2 Enterprise Installation Recommendation - Hello Peeps, I would appreciate if you guys can recommend a good installation setup for SQL Server 2008. Below are...

? on running a sp in dbmail - Hi , I am trying to figure out how I can attach a file as an XML in dbmail and run...

Naming the column for xml result set - I am trying to get the proper column name for my xml string. Kindly help SELECT x + ' ' AS [text()] FROM ( SELECT 'a' UNION ALL SELECT 'b' ) AS...

SOX2 Compliance - SSAS Cube - We have enabled TDE on all databases that contain PII data for SOX2 compliance. However, how do we do this...

FOR XML EXPLICIT Help - Hello, I have the below SQL which is working great except for the <Type> element. All the types are appearing on...

syntax needed - The uncommented part is not working declare @str_folder_path varchar(1000)='\\Dataupload' ,@str_media_data_file_path_csv varchar(200)='Data File.csv' BULK INSERT vw_pkg_dataload -- FROM '\\Dataupload\Data File.csv' FROM '''' + @str_folder_path + '\' + @str_media_data_file_path_csv +'''' WITH ( FIRSTROW = 2, FIELDTERMINATOR...

SQL Server 2008 R2 Evaluation Edition Expired - Hi, I have installed SQL SERVER 2008 R2 Enterprise Edition French Version on my windows server 2008 R2 Standard Edition FRENCH...

Each client on its own VLAN - Hi guys, I'm going to be starting a new DBA job soon and thre's one aspect of it that I've...

Concept of Session killing by Sql server. - Hi, I had make changes in server connection properties and set timeout time 300 Secs. Now i had connect to a database...

Deadlock Issue - We are running SQL Server 2008R2 Ent and we are experiencing a high incident rate of Deadlocks . We started by...

best practices - development help - hi, im not sure this is the right forum to put this in, but i am working on redesigning our company...

Sorting Records Going Into New Table. - I am having problems ordering records to go into another table. The following is my code: [code] USE EURUSD GO SELECT * INTO NEWTABLE FROM OLDTABLE ORDER BY...

Post Transaction Log Full - I had a development database set to 'simple' recovery model which ran into a full transaction log error while executing...

Speed issue with SQL Server 2008 compared to SQL 2000 - We have built a new virtual server with Hyper-V and 64bit windows 2008R2 with 3 processors and 12 GB of...

Archiving - Hi I want to archive the data of a 400G table,what is the best way of archiving? My table has a clustered...

Operand type clash: int is incompatible with date - ALTER PROCEDURE [dbo].[bnd_ProjwiseCategory_rblockNODATE] @M_Acc_ID AS INT, @M_ProjVers_ID AS INT, @Category AS INT, @Action AS INT, @From_Date DATE, @To_DATE DATE, @Cate AS...

Index Text - Hello Everyone I was suggested that I use a different way of generating some IDs. This column is not any part...

Migration from SQL 2005 to SQL 2008 R2 using mirroring concept - I just have 1.5 yrs exp as a dba. The requirement is as below: Test environment - We have 15 user databases...

how to check lastest firmware in windows Server 2003? - how to check latest firmware in windows Server 2003?

How could i find the client? - I have a consolidated SQL Server Machine, and none of application are running on this. But I have an error...

2008 R2 upgrade advisor error - Hi I have just started getting issues with the SQL 2008 R2 upgrade advisor. This tool has previously worked ok for...

SQL Server 2008 : T-SQL (SS2K8)

How to Insert/Upadte data based on exist in mathcin table, in single query - Hello All, please help me with this single select statement, [u]StudentNew[/u] table is exist with below values, (sno and sname is composite...

Transforming an Interative Solution into a Set-Based Solution - The problem here is that the grpID field is sometimes loaded with multiple values for a single element of the...

Get windows user.. - Hi, Is there a way of getting the windows user name using an application accessing SQL Server with SQL Authentication? The sysprocesses...

Isolation Level - Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every...

Indexed view CI chosen of table CI. Why? - Hi, This is related to the table and query in question: [url]http://www.sqlservercentral.com/Forums/Topic1449684-392-1.aspx[/url] (Hope this is clear, otherwise I have to double post...

Understanding CASE processing - Hi, I found an SP that generates multiple index seeks from 1 case statement. By the looks of it each WHEN THEN...

conversion of the varchar value overflowed an int column - I have a stored procedure : In this prcedure I am returning @pSTN value and used in another sp as output....

Extract string between two special characters upto 4 sublevels - Hi Everyone, Can anyone of you please help me to get the T-SQL to extract a string between two special characters...

Multiple reference to a single column - I have created 3 table in which in T3 table I am referencing the ID columns of table T1 and...

T-SQL help - For the below SQL code, I need the output as: [b]person dob Age[/b] Frank 3/20/1990 23 Years Joey 12/31/2000 12 Years Sue 2/6/2012 1 Years Mary 4/1/2013 1 Months Bil 5/3/2012 1 Years Bob 1/1/2013 4 Months Will 10/1/2012 7 Months [code="sql"]declare @people...

Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries - Is it possible to use a stored procedure to kill all queries running longer than 2 hours AND are blocking?...

How to sum in SQL removing varchar - Hi All, I am trying to sum of amount (data type varchar) column but I am getting error error converting data...

convert a PLSQL to T_SQL for a function - I would like to convert a function from PL-SQL to T_SQL, not sure about the syntax, Any one can help?, code...

SQL Server 2008 : Working with Oracle

Oracle Linked server randomly failing from sql agent job - I have a number of jobs that fail because of a linked server message. [code="other"]Executed as user: Domain\User. Cannot initialize the...

SQL Server 2008 : SQL Server Newbies

Rounding Up with given precision - I would like to round up some figures with given precision. So far I can't find a way to do...

Use MAX in insert query - Hi, is it possible to use MAX function in an insert query? I try to write a query similar to this: [code="sql"] INSERT...

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

Updated muliple rows into one column? Why is it hard? HELP - Trying to update a table using integer number in 3 columns and a signing texts name to results in the...

SQL Server 2008 : Security (SS2K8)

How to prevent ANY use of xp_CmdShell? - Please forget whether or not you're pro or con on the subject of the use of xp_CmdShell for just a...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Service account change - Hi, Log shipping (backup job) failed after we change SQL service account in our production database and DR database server. Earlier...

Ownership of cluster disk 'Cluster Disk xxx has been unexpectedly lost by this node. - My Cluster went down again. I don't have to say... I am having a not so good morning already ... :-( Here's...

SQL Server 2008 : SQL Server 2008 Administration

Logshipping + Database snapshot combination - I set up log shipping on both servers and they are running fine. Due to a recent requirement, they want...

Database modifications reported in Error or other log? - I am a Jr. Level DBA, responsible for monitoring our production server. On Friday I noted that the drive our...

Rebuild Indexes - Hello Everyone I am rebuilding the indexes on a database every week. I am getting complaints that the database is being...

High CPU Issue - Hi, We have a high CPU issue, it's almost 80 - 90% and sometimes close to 100%. I will be trying to Rebuild...

HOW TO FIND the OFFENDING SESSION FROM sys.dm_tran_database transaction - Investigating on the use of tempdb with the following very simple query: select convert(char(15), db_name(database_id)) as dbname ,transaction_id ,database_transaction_begin_time ,database_transaction_log_bytes_used from...

Single User Mode - If you set a Database in Single User Mode and you loose you conection to the session that you set...

The database principal owns objects in the database and cannot be dropped - Hi guys, Recently we got this error: The database principal owns objects in the database and cannot be dropped. The database version...

Add features to an existing instance - Hi I need to install Full search and Analysis services to an existing instance of SQL Server 2012. When I...

how to reset SA password - I have couple SQL servers that nobody knows the SA password and nobody has sys_admin right to reset it. There...

Advice in restore and backup check schedule - Ok, We all know how important to check (and restore) your backup file is, right? ;-) ... so I am trying to follow...

How to know the SQL statements causing the Deadlock from this XML output? - I can figure the stored procedures names using object ID's. But i want to determine the actual SQL statements that...

Programming : Connecting

New tO SQL - Need help to Install SQL + BI - I need to install sql 2008 with BI tools.Apprently the one I have was not connecting to adventureworks and so...

Programming : General

Upload directory in Stored Procedure - Hi, I am using a stored procedure and would like it to find a file on a different server location. When...

SQLServerCentral.com : Anything that is NOT about SQL!

Database College degree... which one is better? - I am a growing DBA fanatic, and i am looking to get very involved with databases in the future. I...

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

Search Results Suggestion - It would be a great addition to the search results to include some more information about the items. Specifically when...

Reporting Services : Reporting Services

Output of SSRS in XML format - Hi, I got my sp TO WORK.. FINANLLY... Now I wanted to schedule the report to run and email in XML format.... I...

SSRS Report breaking with an error "System.UriFormatException: Invalid URI: The Uri string is too long." - Need quick help on this issue. Environment Details: Reporting server migrated from 2005 to 2012. But we haven't migrated rdls and these...

Database Design : Design Ideas and Questions

Is Having Primary key on a table essential? - Hi everyone We are using Sql Server 2008 and we have a log table which has one clustered index(on creationdate, that...

Data Warehousing : Integration Services

Whats the easiest way to read the first 10 characters of a flat file - Basically I am recieveing multiple flat files of two different file layouts, the file names do not give a clue...

What happens if new records are inserted in a source table during a package execution? - Hi Folks, I have a package that loads records in a CRM destination using a Web Service. This execution takes around...

How to avoid technically the null values while concatenating columns - HI, In SSIS by using dervied column i manipulate fields and end up in my desired results. But for an record if...

Data Warehousing : Strategies and Ideas

Designing a Dim table for SSAS and SSRS - New to SSAS. I'm working on my first cube. I'm creating a dim table for the accounts. The level of...

Data Warehousing : Analysis Services

Cube design for Snapshot scenario - Hi Team, We have a scenario where we need to provide the As on the given day how many products...

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