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

Sensors and Data

The programmable world. It's an interesting concept, but not one in which we have extremely detailed specifications and strongly bound software systems that must be built to interact with each other. I think many of us have assumed that's how we would enable the further computerization of our physical world. As an example, we'd have cars that communicated with the road, with other cars, with semi-central authority(ies) that might be managing our interactions, all of the infrastructure pre-built. 

However that isn't necessarily what will happen. In this O'Reilly piece, the author notes cheap sensors can read the color of lights in the same way that humans or my Lego Mindstorm can detect color. Traffic sensors need not communicate with cars; these sensors could instead just identify cars and count them, measuring their speed and adjusting traffic lights based on actual conditions. Imagine the future when more and more sensors gather their own data and make decisions.

In a sense, this is how the Google self-driving car works. Rather than depend on infrastructure and external programming, the car gathers its own data and adjusts its behavior depending on the interpretation of the data. Whether or not you like the idea of self-driving cars (I do), the idea of an autonomous device acting based on programming and a large amount of data is a fascinating move forward in computing.

I think this shows that data gathering, processing, and analysis will become a more important part of our future computing worlds. Some of this data will be transient and discarded, but lots will be stored. We'll use data to ineract with the real world immediately, but we will also perform analysis later and reprogram our devices to operate better in the future. That means there will be new, and more, opportunities for those of us working with data.

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 ( 16.5MB) feed

MP4 iPod Video ( 20.0MB) feed

MP3 Audio (4.0MB) feed

Feeds are available at iTunes and Mevio

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


ADVERTISEMENT

What does normal looks like on your servers?

New benchmarks in SQL Monitor instantly show you if a performance spike is a problem, or within normal SQL Server behavior. Try it live.

ADVERTISEMENT
SQL DBA Bundle

Top 5 Hard-earned Lessons of a DBA

‘10 Tips for Efficient Disaster Recovery’ by Steve Jones. Prepare for any future disaster by reading Steve’s tips today.

25 Secrets for Faster ASP.NET Applications

Free eBook: 25 Secrets for Faster ASP.NET Applications

Want to speed up your web application? Our new eBook has 25 tips for getting maximum performance from ASP.NET - download it free.

Featured Contents

 

Setting Different Colors for Connections in SSMS

Peter Schott from SQLServerCentral.com

Learn how to set colors for different connections in SSMS. More »


 

New SQL Monitor Custom Metric: Percentage of Free Log Space

Press Release from Red-Gate

This metric measures the percentage of free space for transaction log files (LDF files). You’ll find this useful if your SQL Server has limited capacity, so you need to maximize existing disk space utilization by minimizing unused space. It will also alert you if the amount of free space drops below the specified thresholds. More »


 

SQL Server Analysis Services (SSAS) 2012 Top and Bottom Functions

Additional Articles from MSSQLTips.com

Scott Murray has used the Top and Bottom T-SQL functions in SQL Server and wonders, do the same functions exist in the SQL Server Analysis Services (SSAS) or MDX? If so, how do they work? Can you provide some examples? Check out this tip to learn more. More »


 

From the SQLServerCentral Blogs - Encryption Decryption Routine

Chris McGowan from SQLServerCentral.com

In this post we will look at a complete end to end routine for encrypting, storing, decrypting data in SQL... More »


 

From the SQLServerCentral Blogs - Why Government Required Backdoors Are a Bad Idea

I've heard the argument, "I've got nothing to hide. If it helps them catch the next guy, I'm all for... More »

Question of the Day

Today's Question (by Steve Jones):

What does the visibility attribute control for assemblies?

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 QOD, simply log in to the Contribution Center.

ADVERTISEMENT

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Samith C):

What will be the result of this batch?
declare @i int 

set @i = 2

if @i = 1 
 begin 
  print @i
  declare @j int
 end
 else
 begin 
  print @i
 end

set @j = 1

print @j

Answer: 2 1

Explanation: In this batch, the ELSE statement is executed, which prints "2". The variable is reset and the new value ("1") printed. The Declaration inside the "then" portion of the IF statement is ignored.

» Discuss this question and answer on the forums

Featured Script

Sales Report on a View

Sergiu Ungureanu from SQLServerCentral.com

A friend of mine asked me to help him on a project he had at work. Basically he was asked to create a simple report such as: GID, Week4,Week3,Week2,Week1,Turnover based on a table that stores the sales data something like this: GID, SaleDate, ItemQty, ItemPrice. Going back to the report:

- GID- should be unique- consider it a product id if you want;

- Week1- sum of the sales that have been made 3 weeks ago (meaning starting from Monday until Sunday)

- Week2- same as Week4 but for the week following Week4-> 2 weeks ago basically

- Week3-same but 1 week ago Monday to Sunday

- Week4- sum of all the sales made by this Monday until right now (or Sunday considering the possibility of having sales recorded in the future)

- Turnover- it's the avg of the week columns, but only for those whose value <>0. eg:

GID

1

2

3

4

Turnover

1

2000

0

800

900

1233

Turnover=(2000+800+900)/3 (not 4!) = 1233

The application my friend tries to configure can be used to design reports, but these reports can use as data sources ONLY views or tables. Thus the challenge! :D ...Challenge Accepted!

Challenges detected:

1. Each week should be filtered from Monday to Sunday as per the current date the report is ran at. Eg: If the report is ran today (20130906) this is how the sales amount should be filtered based on the sale date:

Week1    2013-08-12    2013-08-18
Week2    2013-08-19    2013-08-25
Week3    2013-08-26    2013-09-01
Week4    2013-09-02    2013-09-08

2. Once I'll have the data, I will have it as per the source table layout -> vertically. I will need it horizontally (also known as pivoted).

3. Doing all this in a single view that should run on top of millions of records.

After one hour of struggle here is the view.

(see the sql script)

Hope it can be of any use to anyone else. It can be easily modified for more or less weeks.

Thanks and hope this will not be my first and last script I share with you guys! 

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

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...

I can not connect to a data base in a linked server - Hi i have two server on the main server and on the main server i have 2 databases. I create a...


SQL Server 2014 : Development - SQL Server 2014

Tables inside In-Memory 2014 - How the data are stored in memory. How the memory is been shared between database and OS operations. What will happen after...

Query to show Items in FIFO method. - Dear Experts, my client needs a report which shows inventory data. he executes this report before every sale and checks the Item...


SQL Server 2012 : SQL 2012 - General

Microsoft's Data Virtualization Strategy - It seems to me that SSAS tabular models have some similar features when compared to data virtualization software, but the...

Splitting row into 2 rows - Hi, I have a table witht he following structure. EmpNo DutyId StartDate EndDate ------------------------------------------------- 101 01 2013-08-31 22:00:00 2013-09-01 06:00:00 The employee duty spanned...

SQL Alerts and WMI - I'm trying to create an alert disk % free space left using: SELECT * from __InstanceModificationEvent WITHIN 30 WHERE TargetInstance ISA 'Win32_LogicalDisk' AND...

How to add multiple stopwords to a stopwords table ? - I keep getting this error message, when i use insert into or update. insert into sys.fulltext_stopwords values('5', 'resume', 'English', '1033') I have 10k...

Rounding a number up to the nearest 5 - So I am attempting to mimic some legacy code on an old server at my work. It has a calculation...

CDC; AlwaysOn and CDC Jobs question. - Hi all, I have a Primary Replica A and Secondary B part of the AG Group. CDC is enabled at the...

Error 18059: Impersonation Contexts - We have an app (from Active Networks) which, since being upgraded and moving to SQL Server 2012, repeatedly throws this...

multi-valued report parameter weirdness - SSRS 2012, SQL 2012 I watched Brian Knight's video on filtering a report using a multi-valued parameter (here:[url]http://www.sqlservercentral.com/articles/Video/64369/[/url]), and after seemingly...


SQL Server 2012 : SQL Server 2012 - T-SQL

Trying to create line items records from a string - Hi all- I've got an invoice coming in as a single sting and am trying to figure out how to best...

Trigger Issue... - Hi, I've a table that has a trigger for insert that executes the flowing statement: [code="sql"] UPDATE ArtigoArmazem SET QtReservada = 0 FROM INSERTED...

Msg 4862, Level 16, State 1, Line 1 - with reference to OPENROWSET (TRANSACT-SQL) http://technet.microsoft.com/en-us/library/ms190312.aspx example E data [code] 1 Data Item 1 2 Data Item 2 3 Data Item 3 [/code] no-xml format file [code] 9.0 2 1 SQLCHAR 0...


SQL Server 2008 : SQL Server 2008 - General

Query with One Column Changing by Month - Hello all, I "inherited" the following code as part of a query for a report: [code="sql"]-- January if @MonthUnits = 1 begin insert into...

Problem with Cursor in trigger - Hi all, I have the following trigger after AvailPhysical field is updated. Although I have written that table field ITEMID must start...

Merge with more then one insert statement - Table1 : id, Name Table2: id, name1,name2,name3,name4,name5 MERGE Table1 t1 USING table2 t2 ON t1.id = t2.id WHEN NOT MATCHED THEN INSERT(name) values(t2.name1) values(t2.name2) values(t2.name3) values(t2.name4) values(t2.name5) as like this i have 16 names...

DELETE Duplicates - Hello, I did a check in my table and I found these duplicates: Key Month Usage 45 Sept 464 45 Sept 471 28 Sept 762 28...

Lookup tables - when too ude them - Hi All, I need to create an attribute table for say a product. Each of the attributes for a product can...

Run Dos Command in SSIS - Hi, I need to run DOS commands in SSIS 2008 to copy from one server to another server. Currently using batch files...

SQL releasing memory while there is free memory to use. - All, We had a situation on one of our production servers today. The max memory is set to 110 GB. At...

Any point in EVER shrinking trans log? - I have databases that generally do not accumulate much in the transaction log. There is an automated script that runs...

Need SQL query to check Lockings as per below columns - Query to check Lock occurance-- Need a SQl query which can show the details colums as below Columns Names - dd:hh:mm:ss:mss session_id sql_text login_name wait_info cpu etc Please let us...

SQL-Server 2008/R2 and 2012 parallel - Hello, I have installed SQL Server 2008/R2-64Bit (Developer Edition) on Win7. Can I install SQL Server 2012 64Bit (Developer Edition) parallel on...

Second Highest - Could anyone know how to write the query fro fetching the second highest salary without using top, rowcount and rank...

Copy database from 2012 to 2008r2 - Hi All, I have "Quiz database" in sql server 2012, Now i want copy same database in another 2008 server(SQL server...

trim column values - Hi Professionals I have a varchar column in my table with pricing information in it that has up to 4 decimal...

Cannot figure out my update correctly - Hi professionals I have a table like so [code] departmentid,Barcode,Invoiceno,expirydate 2036,xyz,12345,23/01/2013 2077,xyz,56245,05/05/2012 2036,abc,67654,02/11/2012 [/code] my employer wants me to update the table column invoiceno and concatonate the invoiceno...

I Can’t Connect to Analysis Services in SSMS 2008R2 - Installed SQL Server 2008 R2 and chose to install SSIS, SSRS and SSAS. now I am unable to connect SSRS...

Issues with msdb.dbo.sp_send_dbmail procedure - Hi All, For the last several days I'm trying to resolve issue with sending emails by following procedure (I've ommited some...

Concat column values - I have following table [code="sql"] DepartmentID Employee ------------- --------- 1 John 1 Michael 2 Jenson 2 Lily 2 Lara [/code] I want the output in following format [code="sql"] DepartmentID Employees ------------- ---------- 1

Optimization with multiple Select and Update statements - Hi, If I have multiple select and update statements in my stored procedure, will it be better to group up the...

Finding 2nd and 4th Saturdays of Current Year - Hi all, Please help me to find out 2nd and 4th saturdays of current year. Thanks and Regards Shirish Phadnis

Early Month end processing, How do you handle the dates? - We have always waited until the first of each month to start our month end processing. We would use code...

Why is my logon trigger blocking logons? - I've created the following trigger to track logons: [code="other"] Use master Go Create database AuditDB Go USE [AuditDb] GO CREATE TABLE [dbo].[ServerLogonHistory]( [SystemUser] [varchar](512) NULL, [DBUser] [varchar](512)...

Executing Scribe packages with stored procedure in SQL - Hi, Is there anybody that has any experience launching a scribe package (dts) with in a stored procedure on a SQL...

How connect Excel 2007 to Analysis Services 2008 - Hi All I am trying to use Excel 2007 to connect to my 2008 Analysis Server (64 bit SQL 2008 on...


SQL Server 2008 : T-SQL (SS2K8)

Pass semicolon deliminated parameter to stored procedure - Hell All, I am facing a bit of challenge: I have a table with the following structure: [code="sql"] [tabid] [int] IDENTITY(1,1) NOT...

ALTER TABLE - HI all, here is the scenario that I faced: 1. I had a table which some data. 2. I need to add a...

Searching a particular table in DB - Hi All, I have list of columns (say col1,col2,col3). I need to find the table in the database which contains these...

t-sql full table scan problem - I am having a performnace problem is a sql server 2008 sql. The stored procedure does a full table scan...

Move row value into column - I would like to pull a query with each student each day an attendance record. Our database setup an AM and...

Problem with update syntax - Hi all - I am preparing create tables with test data, but I thought this might be so simple and generic...

Extract string between to characters - I've a string in following format: <1|458|test|q><2|789|test2|s><5|567|test3|p> First I've to extract the strings between < and > like: 1|458|test|q 2|789|test2|s 5|567|test3|p after that I've...

Finding gaps within date ranges - Hi, I have a group of date ranges and wanted to identify all of the date gaps within the ranges,...

The working of PIVOT - Can you please explain how PIVOT is used in this query? i.e. logic behind the working of PIVOT in the...


SQL Server 2008 : SQL Server Newbies

Log file backup growth (how long's a piece of string...) - If your log backup file was as follows: 2,176 KB at 10 this morning 10,617 KB at 11 this morning 18,615 KB at...

Update Table - By Looping Through Field Name - HI, Is it possible to achieve the following ; I have a table with say ... 200 Fields and I want to check...

making records unique - Hi everyone, I've a table with 15 fields with 70,000 rows of data. I now realise I need to make...


SQL Server 2008 : SQL Server 2008 High Availability

Mirroring and big transaction log file - Hi All I have mirrored database with 2 Databases Motor and one witness. Size of one Database is 467 Mb, but...

Database sycronisation - Hi, looking for thoughts here please. I have a database 10GB in size that is used for scheduling. It runs on SQL...

Mirroring - Intermittent "network name is no longer available" - Is this a network issue? I have a mirroring setup - synchronous with automatic failover. The databases went into Disconnected mode...


SQL Server 2008 : SQL Server 2008 Administration

Validating large tables replicated - Hello, I am working with large tables (many millions of records) in SQL Server that are replicated from one server to...

Copy File Across Servers Failing - I have 2 servers that are in the same rack, but not on a domain. Ultimately, I want a SQL...

SQL Server Upgradation from 2005 to 2008 - Can you please any one clarify my below doubts, because we are going to Upgrade SQL 2005 to 2008 1) Can...

which table sql server will create database after log shipping and in which system database it will create? - which table sql server will create database after log shipping and in which system database it will create?

Looking for powershell script - Hi, i am looking to find powershell expert, so that can help on my query.below script does return sql installed...

Change NULLS to blank in Dynamic Pivot Results - Subject says it all. I would like to change the NULLS to blank in my Dynamic Pivot results. Here is...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Work Files / Min Increased almost 10 fold. - Hello. I was making some minor changes to indexes the other day. The next day I noticed our Work Files...


SQL Server 2005 : Administering

Create Views issue - The user account has dbo permissions on the database but couldnt create the views not sure what is the issue....

The sequence to install service packs or hotfixes on an instance of SQL Server that is part of Database Mirroring/Failover Clustering environment - Hi All, Can you please suggest me the steps to install service packs or hotfixes on an instance of SQL Server...

Database Not responding from 8:00 PM to 10:00 Pm - Dear All, I have an Sql server 2005 server which is hosting multiple Databases. Recently, users are complaining that the application...

Insert and indexes - Good morning INSERT query is suddenly taking too long to run which used to run under 30 minutes is taking 5...


SQL Server 2005 : Backups

Restore Help needed - Hello, I am not sure if this has been addressed already. I have been a long time reader but this is...


SQL Server 2005 : Business Intelligence

SSAS - How do I handle dimensions with valid duplicates? - I'm new to SSAS and I got a problem I'm not able to resolve. Let's start with two tables, this...

RDL to RDLC - hi how to convert RDL to RDLC for .Net integration i saw the below but it does not work for me. http://msdn.microsoft.com/en-us/library/ms252109(VS.80).aspx Thanks Parthi


SQL Server 2005 : SQL Server 2005 Compact Edition

Standard SQL scripting to CE - I am thinking tools that are free exist to script data and structures from Standard SQL to SQL CE in...


SQL Server 2005 : SQL Server 2005 General Discussion

msdb.dbo.sp_send_dbmail Not working with SQL JOB - Hi All, i have a SP, which will do some update operations and send mail to respective users to send mail i'm...


SQL Server 2005 : SS2K5 Replication

sql server merge replication issue mini dumpsql - hello all, I am getting an error while I am trying to replicate data. below is the output from the error...


SQL Server 2005 : SQL Server Express

SP4 on Express - Hello Friends, I want to update sp4 on SQLExpress 2005 edition. Do I need to reinstall the SQL server Express...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS - Input Excel file - updating Table - conditional split? - Hi - I am SUPER new to SSIS , kind of thrown into it with no training or help other that what...

Change column order in Flat File Destination - Hi there, I have several Flat File destinations and I need to change the output column order in each. I've opened...


Reporting Services : Reporting Services

Color rectangle - This rectangle is intended to show infromação invisible. So I would like when I clicked the rectangle change color. Something of...

HOW TO FIND THE STRING CONTAINING BEFORE '/' AND AFTER '/' IN 5THE GIVEN STRING - I HAVE ATABLE CONTAINING column loginid of varchar type .In EACH NAME IT CONTAINS MORE NO OF '/' CHARACTERS.HOW TO WRITE...

How to pass Null value to Multivalue Parameters in SSRS - Hi Folks, Could you please help me out, how can I pass Null Values to Multivalue Parameters dropdown and how can...


Reporting Services : Reporting Services 2008 Administration

Facing error while configuring SSRS 2008 for caching - Hi All, While configuring cache from Report Manager url, I open the Processing option tab. While trying to set the cache...


Reporting Services : Reporting Services 2008 Development

Pass Parameter for two different Datasets - I have two seperate datasets : Dataset1 and Dataset 2 and want to display results from both of them for the...

SUM values only for distinct ID's - Hello, I have the following report: ID Time 311793 3:12 312184 3:13 312184 3:13 312373 3:04 Is there a way to sum...

To Show Large Data in SSRS Report - Hi, i have to show a large amount of data in SSRS report. the report is just a plain Table,...

"Tax Detail" report - I need to structure the report by: SALES/PURCHASE MONTH TAX CODE I also need to show Totals after each TAX CODE set and after...

Dynamically back track records - Hi, Just made changes with my requirements. I need to dynamically back track 1 day at a time in extracting of records...


Data Warehousing : Integration Services

Get parent container name? - I've been searching the net for a few hours trying to answer how to get the name of the parent...

Unable to Schedule SSIS Package execution using Sql Agent in SSMS - Hi All, We have created a simple SSIS package on our servers and the Packge can be executed Manually. It gives...

Using OLEDB Command task Update records in Source table once they are loaded in Destination. - Hi All, I have a SSIS package which dumps Data from a SQL table to, say, a Oracle table. Once the...

SSIS Package WinZip all Files in a Folder with spaces in the folder path - Hello All, I have a SSIS package that uses an execute SQL task to locate and configure the package with parameters...

SSIS Dataflow source, inline query or SP - I realized I have an inconsistent philosophy. When working in Reporting Services I always use stored procedures for datasets. However,...

SSIS etl tables backup concept - Hello, I have a production database which is loaded regularly via SSIS packages. In the database there are etl (schema) tables which...

Null records being Inserted during Import of CSV file - I know very little about SSIS, so bear with me. I have a SSIS package that inserts records into a 'staging'...

Connecting to Excel via Agent job Issues - Hi all, Im guessing this is a simple one but im missing it somehow. We are trying to import data...


Data Warehousing : Strategies and Ideas

good book for temporal db design? - I was looking at.Temporal Data & the Relational Model (The Morgan Kaufmann Series in Data Management Systems) C. J. Date Has anyone read...


Data Warehousing : Analysis Services

How do I calculate the subtotal for a calculated column using the subtotals of the other columns used in the calculation? - I have in the underlying database a column computed from other values in the same row. In ssas all these...

a calculated member cannot be used as an operand of a range operator - hi All in mdx land, I have two queries which would do the same thing...doing a rolling 5 day average at...


Database Design : Design Ideas and Questions

Indexes on tables with a high churn rate - Are there any recommendations for indexing a table with a very high churn rate? I have a table (in an inherited...


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


SQLServerCentral.com : SQLServerCentral.com Website Issues

Is there a problem with the 'Add to Briefcase feature'? - Tried adding several articles to my Briefcase this morning but I just get a 'loading' msg appear?


SQLServerCentral.com : Articles Requested

Elapsed time - Show a series of start and stop times, listed for a group of people. calculate the elapsed times (or dates)...


SQL Server 7,2000 : Administration

Planning SQL on a SAN - Sorry, I posted this in the wrong forum, moved to hardware.

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