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

If or When?

I saw this post recently about security and preparing for a data breach. The title caught my eye because it implies that we're all doomed. Do the rest of you think that? Is it a question of when we'll have a security breach not if?

Given the headlines, the news we find out about companies not disclosing security issues, the back doors and poor code in much software, is it any wonder that people think it's a "when" and not an "if"? Given the lack of realization from many companies that suffer incidents that they were even attacked, perhaps that's an assumption worth making.

We've been hacked at SQLServerCentral in the past. I don't think we've been hacked in many years, but I also have no way of knowing. That's the difficult part of dealing with bits. If they get copied, there's not necessarily a trace of anything amiss. It's quite possible that many of us have no idea that our bits are being copied. Every read is a copy of data and how long did the NSA read data without most of us being aware? How sure are we that they, or some other organization, hasn't been reading much more than was disclosed?

I'd hate to think that our systems are so porous that we're all likely to get hacked at some point. It's probably technically possible, but hopefully not likely for most of us. However we should consider that it will happen and ensure we have some handle on our data security. It's hard, and complex for most of us, and I'd like to think that Microsoft will recognize this and build better controls and features into future versions of Windows and SQL Server that enable easier auditing, granular controls and separation of duties.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA 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.

Everyday Jones

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

ADVERTISEMENT
SQL Developer Bundle

12 must-have SQL Server tools

Start a new chapter of your career with everything you need. The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

Performance Tuning with DMVs eBook

Free eBook - Performance Tuning with DMVs

This free eBook provides you with the core techniques and scripts to monitor your query execution, index usage, session and transaction activity, disk IO, and more. Download the free eBook.

Hosted Monitor

Try new SQL Monitor Hosted

Get real-time server performance updates and instant access to the data you need to fix the problem, whilst we take care of the monitoring software. Get started now.

Featured Contents

 

How to create an SSIS conditional data flow task

Stan Kulp from SQLServerCentral.com

This article demonstrates how to selectively channel records from a flat-file data source to separate destination tables in an SSIS package using a combination of multicast, data-conversion and conditional-split data flow transformation elements within a data flow task. More »


 

Write for SQLServerCentral

Site Owners from SQLServerCentral.com

We are looking for new content here for the site, based on your experiences in the real world. Submit something and get yourself published. More »


 

Managing SQL Server Services with PowerShell

Additional Articles from Database Journal

PowerShell provides a command-line shell and scripting language (built in the .NET Framework) especially designed for administrative task automation and configuration management. Learn how to manage Windows services related to SQL Server, either on a local machine or remote machine, using PowerShell cmdlets. More »


 

From the SQLServerCentral Blogs - Help! I'm stuck in Single User Mode and can't get out!

Robert Pearl from SQLServerCentral.com

So, here’s a silly little take on a serious end-user issue where no-one could access the production database. Got a call... More »

Question of the Day

Today's Question (by Auke Teeninga):

Given the query below, what result will be returned when you execute the query? 
declare @message varchar(15)

begin try
   print 1/0
   set @message = 'Query completed'
end try

begin catch

end catch

print IsNull(@message, 'Query completed with errors')

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

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

ADVERTISEMENT

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Joe Barth):

Okay, you need Adventureworks, on a instance you don't care about or you will want to wrap the deletes in a transaction and rollback.

Run statement one:

SELECT TOP 10 'DELETE FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] ' + CHAR(13) + 'WHERE [BusinessEntityID]='+CAST([BusinessEntityID] AS VARCHAR(50))+ CHAR(13) + 'GO'
 FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] 

Run statement two:

SELECT TOP 10 'DELETE FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] ' + CHAR(13) + 'WHERE [BusinessEntityID]='+CAST([BusinessEntityID] AS VARCHAR(50))+ CHAR(10) + 'GO'
 FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] 

When you copy the results and paste in a new SSMS query window,  which Deletes will successfully execute?

Answer: Statement Two Successful

Explanation:

This comes down to Carriage Returns and Line Feeds. A good explanation is in this forum post. Difference between char(13) and char(10)

Basically, I was thrown for a loop when it was displayed in SSMS , I scratching my head why this not working for Statement one.

The GO statement cannot be on the same line as another Transact SQL Statement. That is why we need a line feed char(10) rather than a cariage return char(13).

If you paste results from statement two into Notepad you will notice that GO is still on the same line.

A better version and probably more correct version. You need statement 3.

SELECT TOP 10 'DELETE FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] ' + CHAR(13) + 'WHERE [BusinessEntityID]='+CAST([BusinessEntityID] AS VARCHAR(50))+ CHAR(13) + CHAR(10) + 'GO'

FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] 
Now past into Notepad.


» Discuss this question and answer on the forums

Featured Script

Get the Column level difference for 2 database

Mitesh Oswal from SQLServerCentral.com

The query will help to get the column level difference for 2 database(Like Prod and Dev database). This help us to verify what are the column changes has been done in dev environment after Prod deployment. 

Please change the database name as per your requirement.

In below example  I used the MiteshProd as Prouction db and Miteshdev as dev enviroment

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

AMR tool for collecting stats - i have setup the MDW database for collecting stats on my production server. I have enabled the data collector to...

Linked Servers to MySQL Login Timeout - Hi, I want to set up a Linked Server between my mssql 2014 and a MySQL. I set up the system dsn...

What is the use of creating manifest file in SSIS... - please help me and tell me What is the use of creating manifest file in SSIS... because i was schedule a...

License SQL 2014 - Cores - Hi - Hypothetical situation: I have a 32 core server. SQL 2014 enterprise installed. The server licensed for 32 cores. Question raised - can...


SQL Server 2014 : Development - SQL Server 2014

The new 3D Joins in SQL Server 2014 - Has anyone tried out the new 3D joins in 2014?


SQL Server 2012 : SQL 2012 - General

Sql Server 2012 installation - Hi, I have installed SQL Server 2012 evaluation, I need that domain accounts can login to the SQL Server Management studio, but...

How can I update system catalogs or sys.indexes on SQL2012 - 1st, please do not tell me this is not recommended, I know this already. I am testing a backup/restore of...

Multi-Victim Deadlocks - Hello, I have been seeing a lot of deadlocks occurring on a system with deadlock graphs similar to this one:- http://tinyurl.com/o94gzg5 I...

restoration issue - restored full backup with norecovery , getting below error while restore the diff backup with recovery . Error: Unexpected termination on thread: 0,...

two prod database servers:-one is for insert/update/delete another one is for select - Hi Experts, currently we used to perform insert/update/delete/select operations on transnational tables in same database. In order to make efficient enterprise case...

Extended Event - Hi, I didn't get chance to work with the extended events. I am working in the analysis of deadlock. I have extended...

Duplicate records on a join. - Hi all, I'm having problems with the query below, this is a link to my previous post on the forum...

Backup Compression Restrictions - I came across the below Backup Compression restrictions. can someone please add on any more restrictions to backup compression in...

Problem with DatabaseMail - Hi all We've just moved our database to a new server, and we're having problems getting databaseMail to work. Essentially, when we...

Simple SSAS (for dummies) Tutorial? - Hi, I'm okay at SQL Server and Reporting Services, but know close to nothing about SSAS. I get the theory behind...


SQL Server 2012 : SQL Server 2012 - T-SQL

Deadlock prediction - Hi, Do we have any specific mechanism or tools that can predict the dead lock that's will be going to...

Get ACTIVE_TRANSACTION error in simple recovery mode - I have a table "FaktTable" with 2.500.000 columns. The Database is in simple recovery mode. I have to update the table...

Find the Numbers from a string - Hi experts, i hope my problem will be solved. I have a string and i want to get only the numbers...

Geometry - Parallel lines - Hi, Assuming I have a line, is there a function I can call to create a parallel line at a given...


SQL Server 2008 : SQL Server 2008 - General

[High Important]Could not continue scan with NOLOCK due to data movement - Hi, I am getting the below error in prodution ODBC SQL Server Driver][SQL Server]Could not continue scan with NOLOCK due to data...

SQL Server 2008 R2 SP2 CU10 patch download - I would need help in confirming the right CU10 patch for SQL Server 2008 R2. [i]Download SQL Server 2008 R2 CU10...

update stock table - ordered quantity [b]7[/b] I have a table that im trying to write an update query that will work down the rows...

How can I stop the trace from continue to run? - SQL Server 2008R2 Hi, I traced my SQL Server last night using an agent job. The trace started fine. I said it...

how to rollback restoration - Hi, I restored my database with the old backup without backing up the latest database. Can i rollback this restoration. Actually I have...

Data Loading from Staging to Development - - Hello, I want to load data from Staging to Development environment. In Dev. environment , I have index that is use...

CHAR column concenation - Hey gang, I'm a little stumped here. I want to concatenate character data from a table. [code="sql"]declare @ttest table (nItem integer not null, ctext varchar(32) not null) insert...

Use of create statistics - What is the use of create statistics ? I am having a DB With AUTO_CREATE and AUTO_UPDATE for statistics ON, then...

Need Query for summarizing data - Hi there, I need query for the attached output [img]http://www.sqlservercentral.com/Forums/Attachment15316.aspx[/img] [code="sql"] create table #Wholeseller ( wsid varchar(100),[Productid From] int, [Productid To] int, units int) create table #Retailer ( retid...

rebuild index for 1 table at 1 time? - Hi, I have a huge DB with 30 tables out of which 10 are extremely huge tables. From the index physical...

SQL Server on domain or workgroup - Hi Friends, I have very limited knowledge on this topic. I have worked and working on SQL servers on the machines...

Select statement and variables - Hi, I have a query which returns the correct data when I specify the variable @InYearMonths as any number between 1...

Table Partitioning - I am trying to partition a large table. The table is wide as well as long. There is a clustered...

SQL.log file growing rapidly C:\Users\sqlaccount\AppData\Local\Temp - Hi Guys, On the development server I noticed a file called SQL in [b]C:\Users\sqlaccount\AppData\Local\Temp [/b] that has grown up to 10GB....

SQL 2008 R2 Replication Error (No replicated transactions are available). - Hello, I am a relative newbie at replication although I've worked in SQL Server for several years. I am setting up...


SQL Server 2008 : T-SQL (SS2K8)

Aliases in OPENQUERY - Hi All I have the following query [code="sql"]SELECT [KPI].* FROM OPENQUERY(LINKED_OLAP,'SELECT HEAD(TAIL(DESCENDANTS (TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS, ([Game].[Game Code].&[1] , { [Measures].[ActiveUsers], [Measures].[NewUsers] })ON ROWS FROM [CQGaming]') AS [KPI][/code]

Need assistance with XML - Hello all, I have a table which contains an XML column. This column is set up as "<properties><propertyName1>Value1</propertyName1><propertyName2>Value2</propertyName2>" etc... I also have...

Changes not holding in Stored Procedure - I am connecting to a new SQL Server 2008 R2 database using SSMS from my ADMIN VM workstation. I bring...

Arrival and Depart location query help. - Hi all, I have a question regarding the selection of arrival and departure city. [code="sql"] CREATE TABLE #XY123 ( tktamt MONEY, departcty...

Table Size Query - Hi Experts, I'm using Ms SQL Server 2008 R2 (Enterprise edition), I need some help in compiling the correct SQL query...

generate random value before @ in email address - Hello, Here is my table, [sup]CREATE TABLE test_43(id int, email varchar(50)) INSERT INTO test_43 VALUES (1, 'testing1@sql.com') INSERT INTO test_43 VALUES (2, 'testing2@sql.com') SELECT * FROM...

Is it POWER function and FLOAT limitation ? - Hi, I am just wondering why SELECT POWER(2.718, -34.08) returns me 0. Tested it in Microsoft Excel =2.718^-34.08 and it is returning me 1.58774E-15...

Fill in the gap between 2 number range - In my SQL Server database, I have table with the following records [quote] counter, value1 12345, 10.1 12370, 10.5 12390, 9.7 [/quote] Let's assume that I input...

Help need in Avoiding Loop - Hi, Here is my table structure: [code="sql"] ;with Users as ( select 1 as UserId, 2 as PendingAmount,10 as AvailableAmount union all select 2 as...


SQL Server 2008 : SQL Server Newbies

Count - I am trying to count the serversState based on server. Here is the query and Output Select top 20 COUNT(State) StateCount...

1st Trigger Attempt - Hello again! This is my first trigger attempt and not sure how to start. Basically, I have a table (dbo.RemLactation) that...

Parameters - I am actually working on a report where I need to pass parameters. I need to pass the parameters HoursStated which...

variable in a view - I would like to create a view based on the following sql statement. I understand that I cannot declare a...

Concatenating rows from aliased tables on MSSQL 2005 server - Hi Everyone, I'm newbie here and in TSQL too, so please be lenient and patient for me ;) I have some...


SQL Server 2008 : SQL Server 2008 Administration

PageFile.Sys Size and Virtual Memory Size - On one of my server the output of sys.dm_os_memory is as below total_physical_memory_kb: 75486092 available_physical_memory_kb: 878376 total_page_file_kb: 82229572 availabel_page_file_kb: 7284988 At windows (Computer-> properties->advanced->VM-> it is set to be...

While Creating an User to a Database am getting an error - Dear All, Am Using 2008R2 Server,Windows 7 OS. while creating an user am getting an user pleas suggest me some resolutions PFA Regards Jagadeesh...

Transfer of Logins from 2k to 2K8 - Hi all, Is this feasible via the SSIS Transfer Logins task?? I've read a lot of articles talking about doing this...

Rollback on Rebuild index does not end (KILL) - Hello, On a production server and a heavily queried database I had to kill the process doing a rebuild on a...

restore operation was interrupted - restored full backup with norecovery , getting below error while restore the diff backup with recovery . Error: Unexpected termination on thread: 0,...

SQL Server not able to connect - Hi, In one of my critical production server not able to connect i check in event viewer the given below errors...


SQL Server 2005 : Administering

User database moved to "System Databases" container - I need help. One of my user database is somehow moved to "System Databases" container in SQL Server Management Studio(I don't...


SQL Server 2005 : Backups

system DB backups in maintenance plans/jobs stopped working when "show advanced features" was on - Hello. Our backup job of all the system databases in a sql server 2005 maintenance plan stopped working without giving any...


SQL Server 2005 : SQL Server 2005 Performance Tuning

SQLOS: Runnable, Running, Wait List - I was reading a Microsoft White Paper (haven't completed the reading yet) Using the illustration below, why would SPID 56 go...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS package to create backup of Procs and Functions - Hi all, i want to create SSIS package to create backup of Procs and Functions . Please guide me. Thanks.

SSIS 2008: insufficient disk - Hi All, Executed as user: --. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010....


SQL Server 2005 : T-SQL (SS2K5)

Convert Row Data in 1 Field to Many Columns - Hello this is my first post and my TSQL is average at best so any help will be much appreciated I...


Reporting Services : Reporting Services

How to collect data from multiple servers to a single table to generate report? - I have query which populates a table. I want to run that query across 100+ servers daily and save the...

SSRS Faster than Management Studio!? - Hi all, When I run a stored procedure as a direct query in Management studio it's taking ~18 seconds to complete,...

Beginner - need to create a simple report from a asp.net template - My book says that I can display a SSRS report as a web page. Steps given are > File >...

How do I embed an image in a SSRS report? - I tried the instructions as per my book. The screenshot shows what I have done so far. [img]http://i.stack.imgur.com/6qDV5.jpg[/img] Now the book says...

Access SSRS From Multiple Untrusted Domains? - Hi. I'm working with a client who has an SSRS portal that is accessed via web browser. They would like...

Report Builder 3.0 Expression count - I have got a dataset which is pulling monitoring counter values for logical drives, ram and cpu for multiple machines....


Reporting Services : Reporting Services 2005 Administration

Reports Modified Data - Hi All, we have 200 reports and daily one of the reports will be modified as per business requirements so i...


Reporting Services : Reporting Services 2008 Development

how to concatenate two columns seperated by hifen in ssrs (Need Expression) - Hello, I have an SSRS report where I am grouping the table with system Name when I click on ALL in...

Custom Naming for excel workbook rendered from SSRS - Is it possible to set custom naming for the reports which are excel rendered in SSRS? By default, the excel...

How to write ssrs expression to sum the amounts from different tables - Hello, I am preparing an ssrs report where I have two tables and each table is using same dataset and...

SSRS Reports Timeout decrease for Standard Subscriptions - Hi, I am trying to reduce the SSRS Time out limit as there are number of reports which runs for very...


Programming : Service Broker

Permissions on sys.transmission_queue - Hi All, We have several DBs on several servers hosting Service Broker and our developers generally monitor them (which is just...


Data Warehousing : Integration Services

Offline-Jobs schedule - I have scheduled my jobs successfully and they run fine in SQL Server Agent 2008. But When I shutdown my...

importing to SQL Server from .txt - Using SSIS, I'm importing a .csv flat file source having 7 columns into a SQL Server table destination. To avoid...


Data Warehousing : Analysis Services

Unable attach analsysis service database without detach log file - Hi Post recycle of Analysis Service we found one of the database went missing. So we tried attaching it. But its...


Database Design : Hardware

Ram Memory Uasage is full - Hi All, I am using SQL SERVER 2008R2 in Window 7 with 16 GB RAM, and three production database are running...


SQLServerCentral.com : Articles Requested

Deploy to Test - How do you deploy your code changes to test from development? Give us the process and an example.

Write tsqlt tests for stored procedures. - Looking for a number of articles here. Basically for each I'd like to see some type of code that you...

Link a database to version control - Mostly for people using Red Gate's SQL Source Control, but a short piece on how you link your database to...

Adding and Reviewing Maintenance Plan logs - Since so many people will only check the Agent Job for the log, I'd like a piece that looks at...

Building Natively compiled stored procedures in SQL 2014 - Why, how, what purposes and restrictions.

Azure SQL Database Firewall security - An article on how to configure and use the firewalls for SQL Azure database

Sysprep installation of 2014 - How this can work and why you'd do this.

Power View Usage - Overview and an example on how this is used

SELECT ALL USER SECURABLES Permission - How auditors might use this to check what data a person can view.

IMPERSONATE ANY LOGIN permission - New in 2014. How this works, and the potential controls of DENYing this to admins

CONNECT ANY DATABASE Permission - This is new in 2014. What this means, how it works, and potential downfalls.

Resource Governor 2014 - How this works in 2014 with the new IO controls.

Incremental Statistics - Changes in 2014 and how this helps.

Writeable columnstore indexes - An article that shows how these work. This needs a good example scenario that shows how performance is improved.

Rebuilding a single partition's indexes in SQL Server 2014 - How this works with an example.

Host a database in Azure - give us a better writeup of this process: [url]http://msdn.microsoft.com/en-us/library/dn195938%28v=sql.120%29.aspx[/url]

Create data files in Azure - Show how to set up an Azure account for hosting data files and then creating them with an on-premises SQL...

New DMVs in 2012 - Late, but a good list of the changes in 2012 from the DMV standpoint and how each is used. Something...

New DMVs in 2014 - A list of the new DMVs in 2014 and how they are useful.

Creating Memory-Optimized Tables - A piece that shows how to create memory-optimized tables in 2014 and includes some of the restrictions and limitations. Perhaps...

Overview of Memory-Optimized Tables - A generic look at what the Hekaton stuff is in 2014 and how it's implemented. Some basic examples, but not...

BPE in 2014 - A short piece on what the BPE is in SQL Server 2014 and how to implement it.

Create a Dimention - BI - Short piece for reference to show how to create a new dimension w/ screenshots and code.


SQL Server 7,2000 : T-SQL

Need help to build a query. - Dear All, Need help to build query ,Data in the below format Name Dept Punch Date Status Robin Purchase 4/3/14 18:00 Out Robin Purchase...

This email has been sent to {user_email}. 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 ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com