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

One a Week

When Andy Warren and I first talked about SQL Saturday after the initial event in Orlando, we joked about whether we could have twelve events in a year, one a month. It seemed like a stretch goal, after all, how many events can you actually put on where the attendees don't have to pay?

The latest PASS Connector newsletter had the headline of "SQL Saturday: Setting Records" in which there were 86 events in the last year, 34 around the world and 52 in the US.

52.

That's one a week, though there wasn't actually an event every week. We had some busy weeks, mostly in April and September, including 6 events on Sept 14, 2013. That's an amazing number of events where SQL Server professionals around the world are getting a day of training for free. The cost of some events is a nominal fee to cover lunch expenses, but all the speakers give their time, often delivering the same sessions they'd give at a paid conference.

The success of SQL Saturday is due to all the efforts of the hundreds of organizers, volunteers, and speakers who work to give you a great event. If you attend an event, be sure to thank someone for their efforts. However much of the credit for the growth of SQL Saturday really belongs to Karla Landrum (b | t). The addition of Karla to the PASS staff, with her focus and support of event organizers, has made SQL Saturday grow far beyond what any of us might have thought possible at SQL Saturday #1, Orlando, 2007. If you get the chance, thank Karla when you see her.

If you get the chance, I'd encourage you to attend an event in the 2014-2015 fiscal year. If there's not one near you, organize one. You can put on a small event, with a single track, a few speakers, and support from your community. Lots of us would love to help support an event in a new city, even a small one. You might be surprised to see a nationally known speaker at a small event, but you shouldn't be. We love helping the community.

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

Come learn about Continuous Integration for databases at our Cambridge workshop on Friday, August 8, 2014 at the Red Gate office. Learn to link your source control system to a build environment and automatically generate and test your databases. We will use Subversion and Jenkins in the class, and explain how the techniques can be applied to any other VCS (Git, TFS, Mercurial, etc) as well as any build server (Bamboo, Team City, TFS Build, etc.)

You can register for the workshop here

ADVERTISEMENT
SQL Developer Bundle

10 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 10 tools for faster, simpler SQL Server development. Download a free trial.

SQL Backup

14 SQL Server Backup Questions You Were Too Shy to Ask

Read Grant Fritchey’s free PDF and get the answers to some of life’s big questions, like, “How do I retrieve a table from the log?” and, “Are SAN backups enough?” Download the free article PDF.

SQL Monitor

Getting started with SQL Server?

First things first, install SQL Monitor and begin monitoring your servers to plot baselines and initiate a health check. With baselines in hand, you can find irregularities in performance and use the in-tool advice to resolve or investigate problems. Try it free for 14 days.

Featured Contents

 

New SQL Server 2014 Permissions: CONNECT ANY DATABASE

Edward Pollack from SQLServerCentral.com

CONNECT ANY DATABASE is one of three new permissions in SQL Server 2014 that can be granted to server logins. What is this new permission good for and why would we ever want this? More »


 

Avoiding Database Deployment Disasters – 7 Tips

Additional Articles from SimpleTalk

Here are seven practical tips for any 'accidental DBA' or developer, faced with having to deploy an upgrade to a database live to production, in a development environment that is in the early stages of getting the database code “house in order". More »


 

From the SQLServerCentral Blogs - Find Users Logged Into Remote Computer

Bradley Schacht from SQLServerCentral Blogs

Have you ever wanted to find out if someone is logged into a computer or maybe who is logged into... More »


 

From the SQLServerCentral Blogs - SSRS report subscription: Fire Event from SSIS package

Rayis Imayev from SQLServerCentral Blogs

My previous two posts about SSRS reports being deployed to a SharePoint site along with their subscription didn't stop me... More »

Question of the Day

Today's Question (by free mascot):

How is a row is stored in memory-optimized table storage?

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 1 point in this category: Memory-Optimized Table Storage.

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

Professional Microsoft SQL Server 2014 Integration Services

The 2014 release of Microsoft's SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, improved disaster recovery, increased scalability, and much more. The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change. New tools come with new best practices, and Professional Microsoft SQL Server 2014 Integration Services will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Stuart Davies):

Assuming xp_cmdshell is enabled, how many rows will there be in the output file (C:\Exports\data.csv) from the following script?

CREATE TABLE #Employee
    (
      [UniqueId] [int] IDENTITY(1, 1)
                       NOT NULL ,
      [EmployeeName] [char](10) NULL,
    )

INSERT  INTO #Employee
        ( EmployeeName
        )
        SELECT  'Brian'
        UNION ALL
        SELECT  'Mary'
        UNION ALL
        SELECT  'Neil'
        UNION ALL
        SELECT  'Fred'
        UNION ALL
        SELECT  'Steve'
        UNION ALL
        SELECT  'Aaron'
        UNION ALL
        SELECT  'Jason'
        UNION ALL
        SELECT  'Andy'
        UNION ALL
        SELECT  'Clare'
        UNION ALL
        SELECT  'Lynn' 
        

DECLARE @BcpCommand VARCHAR(250)

SELECT  @bcpcommand = 'bcp "SELECT EmployeeName   FROM #Employee WHERE UniqueId <= 7" queryout "C:\Exports\data.csv" -c -T '

EXEC master..xp_cmdshell @bcpcommand, NO_OUTPUT

DROP TABLE #employee

Answer: C:\Exports\data.csv won't be created

Explanation:

The answers 0 and 10 are incorrect - if you run the following, this is clearly shown

SELECT EmployeeName   FROM #Employee WHERE UniqueId <= 7

7 is also incorrect as the #Employee table is not accessible from the xp_cmdshell context (it would be however if the table was created as ##Employee).

The correct answer is that the file is not created, if you remove the no_output parameter from 

EXEC master..xp_cmdshell @bcpcommand, NO_OUTPUT

the following error will be displayed


SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#Employee'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL



» Discuss this question and answer on the forums

Featured Script

Drop Any Object if it exists

Rob Sumsion from SQLServerCentral.com

Simply install as procedure and use like this for any object:

usp_drop object_name

if temp table put single quotes around the table name:

usp_drop '#temptablename'

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

Disable Unicode output - I am running SQL Server 2012. I have been producing files as output for the Unix folks at work. We...

Can SSIS 2012 run against SQL 2014 database? - We are considering installing SQL 2014 to take advantage of columnstore updateable indexes for an upcoming ETL process but am...

Timeout expired. The timeout period elapsed - I am currently running SQL 2014 in my production environment. Both machines are configured for Windows Failover Cluster Services so...

New Member - Serious Problems - Just upsized from Access FE/BE to Access FE and SQL Server 2014 BE. Never done this before but all seemed...

Columnstore and In-memory will not play with Replication - A few months back I raised some Connect items requesting that Transactional Replication is enhanced in a future version of...

About Index - Hi Experts,

Upgrading SSRS from 2000 to 2008 - I am trying to upgrade SQL 2000 reporting services to SQL 2008. I backed up the ReportSever and ReportServer TempDB...

SQL Server 2014 is very slow - Hi, Just installed standard editon, put tempdb on SSD and configured buffer pool extension to SSD. Ran some production replay trace....


SQL Server 2014 : Development - SQL Server 2014

How to join INFORMATION_SCHEMA.COLUMNS, COLUMNS_UPDATED ( ), inserted and deleted tables - Hi :) On google, i found a script for Update Trigger without using a Cursor (). Line 32 to 74 http://beyondrelational.com/modules/2/blogs/71/posts/11988/how-to-find-the-right-columns-updated.aspx And here is...


SQL Server 2012 : SQL 2012 - General

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. - Hi, Getting the following error - Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from...

SSIS package config issue - Hello, I recently upgraded several SSIS packages from SQL 2005 - 2012 using the package upgrade wizard. All SSIS packages connect via...

SQL Server 2012 Transactional replication - Hi experts, I have two standalone sql instances on same domain with named instances and they are listening on 2 different...

MDF File size is growing fastly... - Hi Experts.... I have a Problem like the Following .. On 24th my Mdf size was 10GB,when i checked now the Mdf...

Fastest Way to consume large XML files - Hi everyone I want to pick your brain on something. I need to create script that will import large XML files (500...

DQS- Upgradedlls - Hi, I tried to move log to another drive but I moved it back. Now Data Cleansing Component is not working....

Working out what transaction isolation level a transaction is running under from extended events - Hi Ive got a standard extended event running capturing rpc_completed and sql_batch_completed I want to do analysis by transaction and wanted...

Attempt to Add New Job for SSIS Package type causing SQL Server to restart - I want a SQL Server Agent job to regularly run a SQL Server Integration Services Package but during configuration of...


SQL Server 2012 : SQL Server 2012 - T-SQL

Calculate EndDate From Subsequent StartDate - Here is sample data I am working with: Create table cattimelines (categoryID int, EffectiveDate datetime, CategoryValue varchar(11)) INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue)...

TSQL Script help for new user - Hi all, I'm hoping someone can help as I'm clueless :) I'm looking for some TSQL script that filters the method field...

Pre 2012 Stored Procedure: Need some DEV advice (COMPUTE BY TO WITH ROLL UP) - It's not my program, but it's a really nice Stored Procedure that was written by Richard Ding back in 2008...


SQL Server 2008 : SQL Server 2008 - General

How to optimize this query? - Hi All, I have a simple query as below Select A,B,C from Table1 How to create a good index on that...

Replication: Not for replication - Hi all, We are configuring a transactional replication and found out that we need to set all triggers, foreign keys, identity,...

Do recursive call without a ParentID column - I have the following tables [Order] ID [Schedule] OrderID Start End I have the following query that gets the schedule details for a specific OrderID. It also...

Merge Problem!! - I have the following 2 tables: [code] create table #Grantors ( EntryNumber int, Sequence int, NameRIN int ) Insert #Grantors (EntryNumber, Sequence, NameRIN) Values (473572,1,184991) ,(473572,2,184992) ,(473572,3,244602) ,(473573,1,57179) ,(473573,2,244603) ,(473574,1,184991) ,(473574,2,244604) create table...

CHECK CONSTRAINT statements added to my SP - SQL Server 2008 R2 I opened one of my Stored Procedures today to find a long list of statements like the...

Using Report Builder 3.0 with a MySQL back end - I am trying to check for if there is not a null in a column called ReviewDate. I am using...

Getting error message while using csv file - I am running package moving csv files to the database but now i am getting the mesage as attached. I...

Building a Large XML file to Include or Exclude Groups and Detail According to Parametrised Info - Hello all, I am trying to create an xml file for our customers, based on information stored in our database. The...

Calling two strored Procs inside a Stored Proc - hi, I have a Procedure which needs to 2 procs to be run before running it , so can i run the...

Multiple joins to the same table - Hi all I've got the following stored procedure (this is part of the from clause) and it looks horrendous. Here's the...

Update Statement - Hi I have table A, with (Id, Access and ReportPath) columns. I want to update ReportPath column. But now this column...

prerequisites for replication between SQL Server and Oracle Database - Hi, Wanted to know the prerequisites for replication between SQL Server and Oracle Database. In this SQL Server is the publisher...

problem whith subraport in report builder 3.0 - i have a problem when i want to add a report as a subreport because the program dont let me...

SQLServer file space management - Hey all, I need some help / advice. Newly started at a company (purchased by a crew trying to turn things...

Exporting to 32 Bit Excel in 64 Bit Enviroment - Hi All, I have recently upgraded from SQL Server 2005 to SQL Server 2008 R2 running on Windows Server 2008 R2...

Indexes - Clustered indexed column included in a nonclustered index - Hi there - I have recently noticed that there are a lot of indexes in my database which include multiple fields...

SSIS package turns yellow and stops. - Hello all, I have a SSIS package that consists of a Foreach file Enumerator and a Foreach ADO.NET Schema Rowset Enumerator....

alter memory in SSAS - I am new to ssas , i need to change the memory in one of the SSAS instance , how can i...

Backup Job in Stored Procedure, issues - Dear all I'm all new to SQL so I apologize if this question seems trivial to you. I have pasted the...

DDL Triggers and exec privilege - I have created a DDL Trigger in order to track who creates/modifies/deletes SP and Tables CREATE TRIGGER DDLTrigger_OBJECT --ALTER TRIGGER DDLTrigger_OBJECT ON...

Cannot open New SSIS Project in SQL Server 2008 R2 - I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer...


SQL Server 2008 : T-SQL (SS2K8)

T-SQL to get value on any given day - I have a table that records when a value changes. For example item = x, date = 2014-06-15, value = 0; item = y,...

Need suggestions on dividing two rows within a CASE statement - Hello, I was given the task to come up with a result set based on certain criteria: Please add one row for...

Filtered index "AND" and "OR" - I am trying to create an index for a specific query that is used a lot. Unfortunately I am keep getting...

SQL Query Help Please - Hello Everyone, I need to write a report where I query against my table that holds employee's Pay Data, I look...

T-SQL to add 7 days to ship date - Hello All, A quick question to all regarding my query. Below statement adds 7 days to the shipment date if we...

Cross Join Help - I have a massive table (company, plant, resource, part number) that I need to create a from part number, to...

CTE with Linked Server: Mulitpart Identifier could not be bound - Interesting issue. I have the following CTE that JOINs some tables from a Linked Server which is our SAP data....

T-SQL Pivot Question - Hello Everyone. This should be an easy one. It's been a long time since I have used PIVOT and think...


SQL Server 2008 : Working with Oracle

Error when selecting top N records from oracle - I am writing a large select statement in MS SQL to select data from linked Oracle 11g server. Started with this e.g. Select...


SQL Server 2008 : SQL Server Newbies

Parsing a String in SSRS - What up Guys, First time posting a question here. I'm not entirely sure how to solve. I have the following string...

CASE question - Hello all, If I'm using a temp table and I have 26 lines of case statements. Is each case statement considered...

Monster code modification - Hello all, I have recently gotten back into SQL coding after transferring to a different department in the company I work...


SQL Server 2008 : SQL Server 2008 High Availability

'Best' HA/DR option to protect against data corruption - Hi All, I know this may seem like an open-ended question but last week we encountered data corruption (eventid 824) and...

Process for Dropping & Readding articles in Trasnsreplication - Hi, I have a Publisher (Pub_A) with two subscribers (Sub_A/Sub_B), both are transactional replication with pull subscriptions. The publication has ten articles....

After sql cluster node started - 4hrs later sql DBI service terminated - During a ms patch maintenance window, 2 node sql cluster, 1 node shutdown and patched and rebooted, 2nd node shutdown...


SQL Server 2008 : SQL Server 2008 Administration

Cluster drive issue - Running out of space on active/pasive cluster (physical servers attached to SAN). netops added new drive, I see the new...

Non-Buffer Pool is huge size? - In SQL2008 R2 64 bit, i have setting cape memory 20 GB, [code="sql"] DECLARE @total_buffer INT ; SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE...

Mdf File Size is getting increased... - Hi Experts.... Am using 2008R2 in my location.. I have a Problem like the Following .. On 24th my Mdf size was 10GB,when...

I want to check the 7 Days backup status... - Hi All, If I want to check the 7 Days backup status means how can i check? Regards Jagadeesh...

MINIMUM events and fields need to be captured in trace. - Experts, Network team complaints that there's heavy I/O traffic between SQL Server and an application server. Though no issues reported anywhere they...


SQL Server 2008 : SQL Server 2008 Performance Tuning

High to troubleshoot high cpu and high memory usage happening on the same time? - Hi Experts. What would be the proper troubleshooting approach for the below performance issue. Server is 100% cpu and 90% memory...


Cloud Computing : Amazon AWS and other cloud vendors

AutoScaling SQL Server in EC2 - I am new to AWS, and I would like to understand how SQL Server autoscales on EC2. We are considering...


SQL Server 2005 : Administering

Mirror database monitoring - Hi all We've got a mirrored database on one of our servers and there's a mirrored database monitoring job set up. As...


SQL Server 2005 : Business Intelligence

SSRS- Need help with grouping by multiple columns - OK so I have a report that I need to hand over tomorrow, so any quick responses will greatly be...

SSIS-to-MSAcces: JET SQL or T-SQL ?? - Sorry, duplicate post. Can't figure out how to delete it. My mistake.

If count of records in query >0, proceed, else stop SSIS task and email - Hello, I'm new to SSIS and trying to figure out this seemingly simply task......what would be just a few lines...

SSIS - Proper sql syntax for running Access DB query - When connecting to a MS Access database and running SQL, would I use JET sql or T-sql ?

SSIS: DTS_E_OLEDBERROR and DTS_E_PRIMEOUTPUTFAILED - Hi Guys, I have a Simple Package that Loads Data from Oracle Table to Sql Server Table. For Source Connection i have...


SQL Server 2005 : SQL Server 2005 Integration Services

Need help writng a do while loop in 2008 Sql server - Hello I need some help writing a loop in 2008 Sql server. I am just begining to use this and...


SQL Server 2005 : T-SQL (SS2K5)

Begin End Block inserting too many records - I have a temporary table that can have anywhere from one to maybe 15 records each day. I am trying...

T-SQL Counting: difference of two methods? - Got this sample which works: [code]SELECT TOP (5) ROW_NUMBER() OVER(ORDER BY somefield DESC) FROM sometable[/code] Now the following can also the same...


Reporting Services : Reporting Services

Display negative seconds in HH:MM:SS format - I'm calculating the difference between two text boxes which record the time in seconds. When I get negative results EG...

Correct Expression or Method to subtract same field value in single group across column group members - I have a "simple" matrix report using SSRS 2012 that shows a bid category with various dollar fields associated with...

Keep row group while scrolling - I have an SSRS report. I don't have a header row but it's a row group that I need to...


Reporting Services : Reporting Services 2005 Development

Percentage Format Percent Sign % moves to a new line. - SQL 2005 Reporting services. In my report, when I format a column as a Percentage,the percent % pushes the % sign to a...


Reporting Services : Reporting Services 2008 Development

SSRS Matrix Sort query - Good Afternoon I have a matrix within SSRS I am trying to sort on. My matrix looks like as below: --Months-- ...


Reporting Services : SSRS 2012

One report Multiple Subscription Daily & Weekly - What is the best way to deliver this report, I have in the past just created another report for weekly,...

SSRS Deployment - Hi, I created a report in SSRS 2012 environment and deployed it into the local SSRS server ( installed in my own...


Programming : XML

Adding a Header Row to a SSIS package exporting out as XML - I've been asked to create an SSIS package to export data out as and XML file. I've got the xml file...


Programming : Powershell

email if no new files in last 2 days - I have this code that works great if there is a new file in the last 23 hours. how can...

SQLPS takes 30 minutes to import. How to fix it? - Hey SSC, SQLPS on my workstation used to take around 30 seconds to import. Not exactly speed-of-thought, but acceptable. Now it takes...


Data Warehousing : Integration Services

Common encountered errors in SSIS - Hi, I am currently writing my Bachelor Thesis about error handling in SSIS and generally increasing the robustness of ETL processes. One...

SSIS on SQL Server 2008R2 "The value is too large to fit in the column data area of the buffer." - Hi -- stuck on this problem. I have a package which takes in records like: [code="plain"] Type Data H ..... D ..... lots of data ... T...

expression yyyymm with mm-1 - In My derived column I want expression for getting yyyymm ( where mm is mm-1) Expression in Derived Column (DT_STR,4,1252)DATEPART("yyyy",GETDATE()) + RIGHT("0" + (DT_STR,2,1252)DATEPART("mm",GETDATE()),2) Result : 201407 Expected...

Migration Wizard - missing conversion of Datapump tasks - Hi, I'm working on the conversion of a group of DTS to SSIS and the function of a part of them...

SSIS package for INSERT/UPDATE from Oracle to SQL server Table - Hi , I need some info. about the Insert and updating the Records from Oracle Table to SQL server table using...

Bug in foreach loop with ado.net enumerator - Hi Greetings to everyone, Today I am going to report a bug in SSIS 2012. In one of the packages I...

Help me: How to capture the execution results (execution time) ?? - Hi Friends, I am just entering into the world of SSIS, I have to capture each of the execution time (please...


Data Warehousing : Analysis Services

Backing up / Restoreing cubes from Files - Not sure if this is the right place We have all incremental backups of all the files in the OLAP\data folder,...

Slicer on Date dimension in Excel - Hi everyone, I just wanted to confirm with everyone that I am not missing anything and that this is the intended...


Database Design : Design Ideas and Questions

Predefined Database Templates - There seems to be tons of predefined templates for programmng and web design and just about everything else you can...


SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...


SQLServerCentral.com : SQLServerCentral.com Website Issues

Did my post get deleted? - Today I put a post in the PowerShell forum with a presentation video I held on PowerShell for our SQL...


Career : Certification

I'm totally new. Need some guide on SQL 2012 - Hi folks! I'll put it briefly.. I want to work with DB, but I know nothing about it because I have...

How to break into data warehouse management - Hi All, I'm hoping I can get some valuable input from people in the field. My current and past jobs have...

How many tests do I need for 2012 after I get my MCITP - OK, I'm desperate to figure out how many exams I have to take after I finish the SQL 2008 exams...


Career : Employers and Employees

Do you usually go to agencies? - Hi all database professionals. Want to ask you some questions about agencies and dealing with them. When you are looking for...


Career : Job Postings

Warszawa, - Poszukujemy konsultanta dysponujacego zarówno wiedza z zakresu Linux/ Unix, baz danych, jak i dobrze rozwinietymi umiejetnosciami w zakresie komunikacji i...

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