In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
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.
 
Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.

In This Issue

Dynamically Download FTP Files Using SSDT

Dynamically initialise FTP connection using expressions and retrieve files stored in multiple FTP directories using SQL Server Data Tools More »


SQL Saturday #204 - Detroit

Join of for a free day of SQL Server training and networking in Detroit on March 16th. More »


SQL Bits XI

The popular UK conference heads to Nottingham from May 2-4, 2013. Both Grant Fritchey and Steve Jones will be speaking, along with lots of talented SQL Server professionals. Register today. More »


Database Deployment: The Bits - Versioning

Although databases have no inherent way of recording their version numbers, SQL Server provides the means of doing so, and much more besides. This is a great advantage to anyone faced with the task of deploying databases without errors. More »


From the SQLServerCentral Blogs - A Month of PowerShell – Day 27 (Scripting SQL Objects)

Welcome to Day 27 of my “A Month of PowerShell” series. This series will use the series landing page on... More »


Editorial - Who Built This Thing?

This editorial was originally published on May 20, 2008. It is being re-run as Steve is on vacation.

I came across a humorous Tweet (since removed for some reason) on David Reed's blog. It said: Was Microsoft SQL Server designed by someone who doesn't need to use Microsoft SQL Server? Jesus.

There are definitely times that I think the people that build the system stored procedures and other code we can see don't really work with SQL Server. It seems that best practices aren't followed and things aren't even well coded.

But most of the people I've met working on SQL Server seem to be really, really smart guys. They care about what they do and I'm somewhat amazed at times how passionate they are. I can't name everyone, but I'll point out one that really struck me.

Recently I was in Redmond for a small conference and quite a few of the SQL Server program and product managers were there to speak about their work and get feedback. Many of the people speaking did a fine job and some were definitely more exuberant than others, but there was one that really stood out in my mind. This presenter works on the spatial team and gave a quick overview, talked about places that his team had debated about going with future work and then walked to the front of the stage to just talk to the audience. He asked us questions, really trying to engage people and drive the discussion forward. And you could feel the passion he had for his work and to build a world class product.

For those of you that don't work in the spatial area, his name is Isaac, and he writes the Isaac on Spatial blog. He really opened my eyes to a subject I had somewhat dismissed since I think plotting locations on a map isn't that interesting. However his passion for the subject, and his lateral thinking to other areas in spatial such as collisions and evolutions of areas in 4D (locations plus time), CAD/CAM work, planning and more made me think that the addition of spatial technologies is more than just a feature match with Oracle.

In many ways SQL Server mirrors what I see in other parts of Microsoft. It's a large group composed of teams, and each of those teams is rather passionate about what they do. They work hard, they make mistakes, they try to correct them and move forward, and mostly they care. They care about what they're doing and it shows when you can sit down with them.

And most importantly, I think they dislike some of the marketing moves as much as we, or at least I, do.

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.comto get better bandwidth and maybe a little more exposure :). 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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

Using SQL Server 2012 and 2008 R2 - DEVELOPER EDITION, U.S. version, installed without revisions i.e., default values not altered, I execute the following two (2) T-SQL statements

SET DATEFORMAT mdy;

SELECT CAST('1/27/49' AS date) AS 'Value 1',CAST('1/27/50' AS date) AS 'Value 2'

,CAST('1/27/1949' AS date) AS 'Value 3',CAST('1/27/1950' AS date) AS 'Value 4'

The question(s) is/are: what dates are returned by each statement ?

For example:  (This example may or may NOT be a valid answer, as per the famous T-SQL statement "IT DEPENDS")

Value 4 is 1950-01-27.

SELECT the four (4) correct answers.

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: DateTime. 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.

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

Which of these are true for subqueries? (select 3)

Answer:

  • The SELECT query of a subquery is always enclosed in parentheses
  • It cannot include a COMPUTE or FOR BROWSE clause
  • Must include an ORDER BY clause when a TOP clause is specified.

Explanation: The first three items are required for subqueries. There are only 32 nesting levels allowed and if a TOP clause is used, an ORDER BY must be included.

Ref: http://msdn.microsoft.com/en-us/library/aa213252(v=sql.80).aspx
http://www.simple-talk.com/sql/sql-training/subqueries-in-sql-server/

» Discuss this question and answer on the forums

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.


Featured Script

Date Format

Returns your date in the format mask you require. 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

Move Primary Key to File Group? - Was wondering if anyone was aware of any papers or best practices they could point me to regarding moving Clustered...

SQL Server 2005 : Business Intelligence

My FIrst Web Report Problem - Hi Specialists. I have created my first ever report in Microsoft Visual Studio and it works fine, however when i try...

SQL Server 2005 : SQL Server 2005 Security

permission issue with sql login - I have a stored proc that gets executed by SQL login which has execute access to the schema owned by...

SQL Server 2005 : T-SQL (SS2K5)

Looping without cursor not updating properly - I got asked to not use a cursor for a loop that I'm working on so I grabbed the code...

SQL Server 2005 : SQL Server Newbies

sequencial insert - I have list of Heads like A, B, C, D.... and like to insert in a table with three column COMCOD,...

Eliminate duplicate in the same column - I have a table which contains the field COMCOD data type nchar(4), PARTYCODE data type nchar(6) Primary Key, PARTYNAME nvarchar(250) COMCOD...

SQL Server 2008 : SQL Server 2008 - General

Diagnosing Page Latch Issue - I'm working with both a Vendor's DB and a purge script they've provided, and I need some outside opinions. This purge...

Replication ?? - My database ''ABC" is configured with snapshot to Sub 1 and transactional replication to Sub 2. last week i drop...

Upgrading/moving datbases frm 2005 to 2008 - After moving databases from 2005 to 2008, do we need to do run any queries (like update usage which we...

Login Error - TITLE: Connect to Server Cannot connect to “my machine name”. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to...

Assign values: conditional case when - My data are arranged like: IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1 --===== Create the test table with CREATE TABLE #Table1...

Performance difference between LIKE and CHARINDEX? - I have a T-SQL script that looks through the firstname column and modifies any firstname with and '&' anywhere in the...

sqlcode vs sp - hi friends i have small doubt in sql plese tell me how to Determine when to use stored procedures vs....

Change Article FILTER -- Where Clause -- in transactional replication - Looking for best way to accomplish this. I need to simply change the Horizontal FILTER / WHERE CLAUSE to include a...

Repeating code over time period - Hi Guys, I have a code snippet to run every month and have to take a union of current data and...

Query comes to a crawl until executed using the WITH RECOMPILE option - we have a stored procedure that will run just fine for a couple of hours but then somewhere during that...

SSRS Reporting assitance- consolidated report using 3 Stroed PROC - Hi, I have 3 Stored Procedures 1) SP_JobPostingID, 2)SP_WorkerOrder, and 3) SP_WorkOrderRevision Note- all the 3 have same number and signature of Columns, what...

Query in SSRS - hi All ,i have one requirement where the SSRS report is displaying only certain data of a column based on...

Publish SSRS 2008 in LAN - How to publish SSRS for internal alone. I have a project which needs to be circulated only to my team members...

SQL Server 2008 : T-SQL (SS2K8)

XML with NameSpaces in each Node. - HI Guys, I came to a situation where I have to load XML file in SQL server tables. I have worked...

Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL - I have a Menu table with a structure like this: MenuID, MenuDesc, ParentMenuID, Sequence The Menu is for a packaged bit of...

remove duplicates IF matches - So I have the following statement: SELECT ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor], [customer id], comm_ID FROM HMD hm join...

Is a doomed transaction inevitable if deadlock occurs in a trigger (SQL Server 2008 R2)? - Hi all, Below are two scenarios, however, what I am trying to accomplish is to catch deadlock errors and re-try...

SQL Server 2008 : SQL Server Newbies

UPDATE PART OF DATA - I HAVE A TABLE WITH THREE FIELD COMCOD SIRCODE SIRDESC 3305 180001 KARIM ENTERPRISE 3305 180002 RAHIM ENTERPRISE 3305 180003 JASON G STORE DATE...

Filtering by StatusId - I have a Stored Procedure which returns data based on the StatusId. DECLARE @StatusId INT=2 SELECT * FROM Employees WHERE StatusId=ISNULL(@StatusId,StatusId) If the StatusId is...

Login - This is the error message I recieve whenever I login. Please help! TITLE: Connect to Server Cannot connect to “my machine name”. ADDITIONAL...

Query Help - [code="sql"] I want a generic query to move the "IN" state records from tableB,TableC,TableD to TableA which has studid as...

import data with identity columns... - Really going crazy here! I have table1 and table2 [for e.g. purpse]. They are of exact design: id [autoincrement, primary key, int] name...

Stuck on new fault with my update - Hi Professionals I am running the following query as advised previously which updates the source table based on a column...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Installation on Windows 2008 - Hello, I have task of SQL 2008 installation (2 installation) on windows 2008 cluster server ( 2 Node). There are 2 approaches comes...

SQL Server 2008 : SQL Server 2008 Administration

Tracking changes to a database via triggers and the transaction logs - Hello -- We have SQL Server 2008R2 standard running on one of our servers. The server contains three databases. One of...

Script to calculate when a job ended. - I need to alter the script listed below to calculate the DateTime that the Job completed and sort on the...

What is the Difference between 32bit replication and 64bit replication? - Hi Friends, What is the Difference between 32bit replication and 64bit replication? Thanks in Advance. :-)

How can you issue a full backup and not interrupt the LSN's - My two cents worth...I was reading an article the other day on junior dba questions and saw the question: How can...

Question about DBA authority versus responsibility - Hi, I'm looking for some advice for how I can assert myself in this situation without alienating my colleagues or causing...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Reporting Services : Reporting Services

Similar Reports - Hi, We have many reports running that are very similar. Usually 1 column difference for example Sales by Product Sales by Month Sales by...

SSRS report to show the results of two datasets at different granularity on same report - I need to build a report to show the Top n products and sum of the rest products across column...

Launching a Report from the URL link - Hello All, I'm using SSRS 2008 R2. After rendering a report, I want to be able to copy the URL link...

Database Design : Design Ideas and Questions

Database describing databases and references to tables - Hello, I asked a question at dba.stackexchange.com [url=http://dba.stackexchange.com/questions/35855/database-describing-databases-and-references-to-tables]here[/url], that's not getting a lot of interest. Now that it has fallen...

Data Warehousing : Integration Services

ETL Standards - Hi Guys, I've been tasked with creating an ETL framework document that contains a set of standards that our company will...

Data Warehousing : Analysis Services

MDX simple concept question - As near as I can tell from documentation, the Descendants() function returns a set. This returns a set of 1,500+ product...

MDX First month of year - This shouldn't be as hard as it seems; I want to always get the first month of the year (January)...