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

What is SQL Server not good at?

I was reading a post that had this quote: " The MongoDB docs tell you what it’s good at, without emphasizing what it’s not good at. "

This isn't to pick on MongoDB, but the post did make me wonder what things SQL Server isn't good at? Should our docs, BOL specifically, have warnings about scenarios where there might be problems with code? In addition to the remarks section, should there be "Warnings" section about various features, functions and code? I image that the CREATE FUNCTION page might have some warning about scalar UDF performance in many situations, and I'm sure many of you would think of warnings that might be added for other features.

I understand Microsoft might not like to point out flaws, but in the interest of building better code and applications, shouldn't various versions of BOL, and perhaps all docs, warn the client about potential issues with using a feature in a certain way? Documenting the potential problems with using a feature in particular situations isn't a flaw; it's guidance about how misuse might introduce other issues.

It's not likely that we'll start seeing more warnings in the official documentation, but for those of you that would like to improve the situation for others, there are always the "Community Additions" sections on all BOL pages. I don't know if Microsoft would post your warnings there, but if they allow them, we might be able to help others understand the pitfalls of using a particular technique.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

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

Steve Jones

Windows Media Video ( 15.6MB) feed

MP4 iPod Video ( 18.3MB) feed

MP3 Audio ( 3.8MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL Developer Bundle

12 must-have SQL Server tools

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

SQL Monitor

Check SQL Server performance at a glance

We consulted 1000 SQL Server professionals to make SQL Monitor’s UI as clear as possible. Start monitoring with a free trial.

45 Database Performance Tips eBook

FREE eBook – "45 Database Performance Tips for Developers"

Improve your database performance with 45 tips from SQL Server MVPs and industry experts. Get the eBook here.

Featured Contents

 

Big Data for SQL folks: The Technologies (Part II)

Frank A. Banin from SQLServerCentral.com

SQL Server Big Data. Is the end Near for ETL as we know is? Will RDBMs be obsolete? What is NoSQL? What is Hadoop? Big Data-the whole story. More »


 

SQL Saturday #233 - Washington DC

Press Release from SQLServerCentral.com

SQL Saturday is a free day of SQL Server training and networking coming to Washington DC on December 7. Paid pre-con sessions for this SQL Saturday are available. More »


 

Migrating SQL Server Database to Windows Azure - Resolving Incompatibility Issues

Additional Articles from Database Journal

In this series on implementing data services in Azure, Marcin Policht turns his attention to the remediation of incompatibilities resulting from of limitations inherent to Platform as a Service (PaaS) based deployments, which will need to be addressed as part of the migration process. More »

Question of the Day

Today's Question (by Steve Jones):

Which of these wildcard characters is used to match a single character? (Select 2)

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: T-SQL.

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

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jacobs):

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

Formats:

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

(Choose 3)

Answer:

  • (DT_WSTR, 60) @[System::ContainerStartTime]
  • (DT_WSTR, 60)(DT_DATE) @[System::ContainerStartTime]
  • (DT_STR, 60, 1252) @[System::ContainerStartTime]

Explanation:

Answers:

(DT_WSTR, 60) @[System::ContainerStartTime]
(DT_STR, 60, 1252) @[System::ContainerStartTime]
(DT_WSTR, 60)(DT_DATE) @[System::ContainerStartTime] (the use of DT_DATE is redundant since the variable is already a DateTime)

To begin, I gave two optional DT formats.  I have used both by configuring SSIS (and\or my system's DT) to be able to display both formats.

ContainerStartTime is a system variable (the Container's Start Time) that is made up of Date and Time and it's default type is DateTime.  An example of using this is in an "OnError" event where you want to send an email message containing the Date and Time of the error.  Since the MessageSource (an expression in the Send Mail Task) is string based, you have to convert the datetime system variable to string (known as Type Casting).

Note:

If you were to use the SSIS function "GetDate()," the date time function will return in the format DT_DBTIMESTAMP with a higher precision for the fractional seconds.  This function returns the system's date and time (29 character return). 

As for the other options,

 (DT_DATE) @[System::ContainerStartTime] produces type cast error.

(DT_WSTR, 60)(DT_DBTIMESTAMP2, 4) produces a different format other than the one indicated in the question (plus adds precision to the time (fractional seconds))

(DT_TEXT)  @[System::ContainerStartTime] produces a type cast error.

(DT_STR,50,1252) (DT_DBTIME2, 7) @[System::ContainerStartTime]  only returns time (with precision (fractional seconds)) 

SSIS TYPE CASTING:  http://technet.microsoft.com/en-us/library/ms141704.aspx


» Discuss this question and answer on the forums

Featured Script

Split and concatenate function

Tomaz Kastrun from SQLServerCentral.com

Function has two input parameters; a string and delimiter.

Parameter string will be used in function for:

1) to split the values

2) add any additional logic as denoted in part with

/* --- COPY/PAST YOUR CASE STATEMENT --- */

3) concatenating new values back to string

4) returning string.

Parameter delimiter is simply a char, used in function to destinguish how to split a string.

E.g.: '1,3,5,7,2,1' uses comma for delimitation between the values.

Alter function and taylor it to your needs in section marked with START and END. It uses CASE statement, but can easly be replaced with any other statement. Also joins can be added for bounding on to your data.

Usage for function is as following:
DECLARE @myString VARCHAR(10) = '1,1,2,2,42'
DECLARE @delimiter CHAR(1) = ','

SELECT
     @myString AS inputString
    ,[dbo].[Split_and_concatenate](@myString, @delimiter) AS outputString

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Development - SQL Server 2014

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


SQL Server 2012 : SQL 2012 - General

2012 Management Studio Server Name - I'm using SQL Server Management Studio 2012 and simply want to create a new database... when I launch SQL Server...

Database Mirroring Suspended - Could not redo log record. - Hi Database Mirroring gets suspended with the following messages on primary and mirror. Primary Error: 1453, Severity: 16, State: 1. 'TCP://MirrorServername.office.com:5022', the remote mirroring...

Is this a Kerberos issue? And if so any suggestions on how to fix it? - We've started testing DNS Aliasing to our SQL Servers, and on one new server we setup a DNS alias SQLSALES...

Converting Oracle data to SQL Server data - Issues - We are running SQL Server 2012 EE on Windows Server 2008 R2 EE. In SQL Server, we have set up...

Find in which mode Analysis services got installed - Hi, How to find in which mode Analysis services got installed? 1. Multidimensional and Data mining mode 2. Tabular mode

Collation error when adding distributer - I'm trying to set up Replication, and the first step is adding the distributer. I run the code: [code="sql"]use master exec sp_adddistributor...

SQL 2012 Cluster with SQL 2008 R2 Cluster - I am planning a clustered SQL 2012 instance on a Windows Server 2008 R2 cluster which already hosts a Production...

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

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

SSRS Report Dynamic columns Export issue - I have created a dynamic report where user has a option of selecting desire columns for the report & I have...

Truncate / Shrink Log for Mirrored Databse - Hello, I have a production database which is mirrored with witness. Unfortunately the log is growing huge. MDF file is nearly 15GB...

DB Offline - Hi In event log i can see 'Database option changed from Offline to On'. Whant to chekc why did the db...


SQL Server 2012 : SQL Server 2012 - T-SQL

A "special" stored procedure problem - Hi guys! I'm not saying that actually have a problem... :-P I have this table: ID(int) | START(datetime) | STOP(datetime) lets call it...

Comma separated column output. - Hi There, I need a query which results the output as comma separated for example , select name from tablename; say for example...

BCP for .xml Export Error - Hi All, I'm trying to export some tables from SQL Server 2012 into .xml format in a local drive and have...

How to reduce the joins in sql querry - Hi I have select query with 10 Joins in place with different i want to improve the performance of the...

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


SQL Server 2008 : SQL Server 2008 - General

Copy-only backups and differentials - Please consider the following scenario: Day1: FULL DB Backup Day2: Differential Backup Day3: Copy-Only Full Backup Day4: Differential Backup am I right in thinking that...

Before deleting/detaching database, Why is it necessary to close all other tasks/documents? - I faced a problem while detaching and deleting a new database.. I had to close all other processes and tasks(e.g-...

Difference between dates - Hi, I have the following data in a table : EmpNo BudgetYearStart BudgetYearEnd 2698 2013-02-01 2014-01-31 67682 2013-01-01 2013-12-31 43320 2013-02-01 2014-01-31 2849 2013-03-01 2014-02-28 67687 2013-01-01 2013-12-31 67675 2013-01-01 2013-12-31 67678 2013-01-01 2013-12-31 54870 2013

SQL Server 2008 Evaluation - Can I use now SQL Server 2008 Evaluation in production environment? Or should I use only 2012? Please advise.

SSIS 2008 - Update/insert into the database based on a condition - Hello everyone ! I have a query. I am updating/inserting a database table below(currently with no data in it) I have finance...

difference between sp_updatestats and update_statistics(when to use sp_updatestats and update_statistics) - Hi Experts, What is the difference between sp_updatestats and update_statistics. When we have to use sp_updatestats and when to use update_statistics?

Linked server/ very restricted access - We have a user who has access to just one view on one database, and he needs to create a...

select of excel file on file server doesn't work - Hi all, I seldom ask questions, finding answers searching the forums, but for this one I'm really puzzled and would greatly...

Help to combine 3 queries into one - Hi Guys I need help. Is there a way to make the following into one query. What its doing is...

What is Connection Pooling - Hi We had a stop on our sql server 2008 last night, Please find below the information I could find. Message : A TCPIP...

Error - SQL server management studio has stopped - HI Team, The App-V for SQL Server Management Studio works fine on a local workstation but on Citrix we have a...

find view details from linked server - Hi, I am having views on linked server andusing that views i want create tables in my current databaase. How do...

DTA Indexes and Statistics - I am reviewing a large database that used to be maintained by a previous DBA and I have run a...

database blocking - i notice today blocking in my DB. when i look at wait resource OBJECT: 15:638292499:0 what does this mean, i look...

Views to tables in another database - I have some views set up in database1 that pull data 2 tables in database2. The views have select for...

Pushing inserts into SQL server with C# ? - I want to insert about 5000 (typically, can be 10K+ at times) rows into SQL server at a time. My...

Changing replication publication name - Is it possible to change replication publication name without dropping the replication and rebuilding it?

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

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

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

Databases in vendor applications and indexes - What's best practice with regard to indexes in vendor applications? I assume it's not done to add your own indexes?...

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

ISO/IEEE Standards for Data Modeling? - Is anyone aware of any industry standards around data modeling? It seems like there are a lot of recommended approaches...

Need to replace HTML encoded special characters and language charcarters in tables - Historically entries have been stored in a DB with web styling encoding and are similar to these (i know bad...

[Microsoft][ODBC SQL Server Driver]Login timeout expired - hi everyone! i am a student working with SQL SERVER 2008R2 on windows 8 64-bit os.. from a month i...

Parsing EDI 834 flat file with SSIS, I need to be able to use multiple delimiters - Hello All, I am working at a small health plan and I am trying to parse a 834 flat file data...

Query Performance with Sophos AntiVirus on SQL2k8 - Here's a fun one for you. I a customer running SQL2k8 x64 on Windows Server 2008 R2 with Sophos 10.something...


SQL Server 2008 : T-SQL (SS2K8)

Pivot All Columns - Hello Everyone I am playing around with Pivot. But I am not sure if this particular instance is a Pivot or...

An INSERT EXEC statement cannot be nested. - Hi, I am using sql server 2008 R2 Can you help me to solve this error please? An INSERT EXEC statement cannot be...

T-SQL join to same table n number of times - Hi, I have a table where each row has an item and the item which superseeds it. There can be many...

Rearrange Result of Data with TSQL Script - Hi , I am replicating my problem in otherway . Say , we are tracking record of bus service i.e. how buses are...

Update column based on other column value - I am looking for a way to update a column for only one row for each ID based on a...

Bulk Inserts using a variable - I am trying to set a path ... that is loaded from a table. So I tried just to use a variable...

Copy records from tables with multiple levels of primary/foreign keys - Hello all, I have an enhancement request from business for them to be able to copy a cart. This is an insurance...

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

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

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


SQL Server 2008 : SQL Server Newbies

Script Concern - Hello everyone, We have a vendor that wants us to run a SQL script on our server. One example within the...

I want to be a DB Admin - I'm a graduating student and I want to be a DB admin in the future. Where should I start? I...


SQL Server 2008 : Security (SS2K8)

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


SQL Server 2008 : SQL Server 2008 High Availability

"Set Partner Failover" "User must be in the master database"? - I'm trying to switch roles in a mirroring session from mirror to principal using the following statement on the principal. alter...

clustering- quorum drive - We failed over the services to the passive node. In this process quorum has not moved to the passive node...

log shipping setup advise needed - Hi, I was wondering if experts here can help with an advise :-) Let's say I have a site A with...


SQL Server 2008 : SQL Server 2008 Administration

Dynamic Management Views: instance_name - If this has been covered before, I didn't catch it in my search criteria so hopefully the answer is simple. When...

Strange DBID - Hi Experts, We were running CHECKDB on one particular database and when ran sysprocesses we saw one SPID is running against...

Database file backup - Currently have a database with two files, PRIMARY and FILESTEAM. We only want to backup the PRIMARY file. What is...

blocking - i notice today blocking in my DB. when i look at wait resource OBJECT: 15:638292499:0 what does this mean, i look...

Regarding adding another data file on PRIMARY - Need to clarify something. I have this db about 90 gb data file sitting on a 100gb drive. And I am...

Service Packs for SQL 2008 R2 - Can you please tell me any one what are the Service Packs are available in SQL Server 2008 R2 ? There...

Email Notification for Transaction log full - How can we get an email notification when db transaction log is full rather than production getting affected. it would...

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

Call powershell in SQL agent job - I use SQL server agent job to call a powershell script. I am using Type operating system(Cmdexec) In the job command...


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

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


SQL Server 2008 : SQL Server 2008 Performance Tuning

how does With (Index (Index_Name)) help in fast query execution?? - how does With (Index (Index_Name)) help in fast query execution?? Does this emphasis on index mentioned during scanning?

CHECKDB Error - Hi Experts, We got below error please help Msg 8951, Level 16, State 1, Line 2 Table error: table 'TB1' (ID 1694329496). Data...

Insert Trigger and @@rowcount problem - Performance Issue - In an Insert Trigger this code is sometimes quite slow: "SELECT @numrows = @@rowcount" This code is used to determine if a...


Cloud Computing : General Cloud Computing Questions

can we working datawareshouse projects useing msbi(ssis,ssrs,ssas) tool on amazon web services - HI, We are looking to usage on amazon web services.before going this services. i found some few things above amazon web serverices.its...

msbi(ssis,ssrs,ssas) usages in amazon webservices cloud - Hi Friends I need some Information msbi (SSIS,SSRS,SSAS)tool to usage on amazon webservices . i gathered some some information above amazon...


SQL Server 2005 : Business Intelligence

Unzip .gz file from network map drive and load into table (SQL 2012) - Hi, The aim of this exercise is to - unzip the xml file (*.xml.gz file) from network map drive - load into table...

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


SQL Server 2005 : SQL Server 2005 General Discussion

Pushing inserts into SQL server with C# ? - I want to insert about 5000 (typically, can be 10K+ at times) rows into SQL server at a time. My...


SQL Server 2005 : SQL Server 2005 Integration Services

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


SQL Server 2005 : T-SQL (SS2K5)

Why doesn't this throw a syntax error? - Can someone help me understand why the following script works when I execute it in SSMS: [code] USE tempdb go USE master IF EXISTS(select * from...

Q: Excel Function that Calls SQl Query? - Hi everone, I am trying to write a function as excel add-ins that uses sql query to aggregate data (SUM for...

GREATEST and LEAST function - Hi, I'm looking for a function in T-SQL for getting the biggest or the smallest value between two or more columns...


SQL Server 2005 : SQL Server Newbies

Use SQL Server 2005 to select a column from Excel - Hello, I have a spread sheet that has 300+ columns. I would like to create a loop that will select...


Reporting Services : Reporting Services

Creating % based on total of rows - Hi I have a report that shows the time it took to do jobs as well as the time it was...

Adding 0's to time in SSRS Expression - Hello, I have a report that is displaying time in the following format I assume by default... (1:3:28), I would like...


Reporting Services : Reporting Services 2008 Development

Customize report by user - A request has come to me.. to take our reports and instead of creating a copy.. for each plant, to...

SSRS - 3rd party PDF rendering tool - IS anyone aware of a 3rd party PDF rendering tool that can be used with SSRS 2008 R2? We are trying...

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

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

Preview report without savings - Hi, I use SSRS 2008 R2 Developer. I want to change a report and preview changes but then close without saving them. The...


Programming : Powershell

SQL Logins output to a file via sp_help_revlogin - Hi all, I'm looking to get sql logins output to a file via powershell. After many different, but unsuccessful searches to find...


Data Warehousing : Integration Services

Unzip .gz file from network map drive and load into table (SQL 2012) - Hi, The aim of this exercise is to - unzip the xml file (*.xml.gz file) from network map drive - load into table...

Package need to run for file movement and delete files and replace files in a package with a scheduled jobs - Hi Everyone, My requirement is i need to run a package everyday ... then the output file generated in one location of...

SSIS: Finding Table Used in Other Package(s) - Hello - I'm trying to determine where a particular table(s) that SSIS is loading during a monthly job is being used...

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

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


SQLServerCentral.com : Anything that is NOT about SQL!

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

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

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

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com