In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro Free eBook: SQL Server Backup and Restore
Get your complete guide to backup and restore strategies for SQL Server - and try SQL Backup Pro to help you work through the examples. Download free resources.
 
Red Gate Cloud Services Schedule Azure backups
Red Gate’s Cloud Services makes it simple to create and schedule backups of your SQL Azure databases to Azure blob storage or Amazon S3. Try it for free today.
 
SQL Monitor What are your servers really trying to tell you?
Find out with Red Gate’s hosted monitoring service, an easy to use solution for no-nonsense database professionals. Find out more:

In This Issue

Creating a heat map in Reporting Services

Use custom assemblies to generate a heat map matrix in SQL Server Reporting Services More »


SQL in the City - Chicago 2012

A free day of training in Chicago on Oct 5, 2012. Join Grant Fritchey, Steve Jones and more to discuss, debate, ask questions, and learn about how to better run your organizations SQL Servers. More »


Using nmap to scan for SQL Servers on a network

I need to try and find all SQL Servers, not just the ones in my domain. We know there are a couple of appliances that are potentially running SQL Server and we want to see them, too. What can I use to do this? More »


From the SQLServerCentral Blogs - Enable Transparent Data Encryption

This is one of the things in my Encryption Primer presentation that I don’t demo. It’s really easy to do,... More »


Editorial - The Personal Checksum

Restaurant ChecksumThis editorial was originally published on Nov 2, 2007. It is being republished as Steve is on vacation.

Someone sent me this article on foiling thieves in restaurants with a personal checksum. It's an interesting idea and while it could help, I'm sure there are some smart people that will find a way around it.

I don't reconcile my restaurant bills that closely, but I have an idea of what I've spent and I think that changing of bills rarely happens. I spent about 8 years in the restaurant business cooking, waiting tables, bartending, and managing at various times. I worked in all types of restaurants from diners to nightclubs and I've rarely seen anyone change a tab. It does happen, usually when someone is upset about their gratuity. But the person usually will brag about it to other employees and often you can tell when a ticket is changed.

I don't condone that behavior. If someone doesn't want to tip you, then they don't have to. On the other hand, if you're going out to a restaurant in the US, remember that those people work essentially for tips. They aren't paid minimum wage; they're paid less as an allowance against tips.

I'm not looking to start a restaurant debate, but with this being a Friday, I needed a poll and here it is:

What other useful personal checksums do you use?

Or do you need? Or do you wish someone would develop? Do you want some type of checksum on your credit score, something to tell you if it's been accessed or even correctly calculated? Does it make sense to have some type of calculation to determine if your mortgage is being correctly credited?

I'm not really sure what checksum I'd be looking for. To date I've had fairly good luck with security in my personal data and have had no real cause for concern.

The one interesting thing I would like is a way to get a quick checksum for the last month off my automatic toll transponder and compare that to some calculation against my bill. I can never remember how often I've used it during the previous month.


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.

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 like it, tell the boss!

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

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


Question of the Day

Today's Question:

In SQL Server 2012 RTM, which of the following are valid index types?

Note that some index types may require additional attributes to be specified; this does not invalidate them for the context of this question. Only index types that include illegal attribute combinations or non existing attributes should be discarded. Also note that this question is not about the exact spelling of the various options.

This is a question with 4 correct answers. Please select all the options that are correct.

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

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


Yesterday's Question of the Day

Given the following table definition and statements ((For SQL Server 2008 or later):

CREATE TABLE dbo.Table1 (
    RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
    DateTimeStamp DATETIME DEFAULT GETDATE(),
    Col1 INTEGER SPARSE,
    Col2 INTEGER SPARSE,
    Col3 INTEGER SPARSE,
    TblColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

INSERT INTO dbo.Table1 (Col1) VALUES (1), (2);
INSERT INTO dbo.Table1 (Col2) VALUES (3), (4);
INSERT INTO dbo.Table1 (Col3) VALUES (5), (6);
INSERT INTO dbo.Table1 (TblColumnSet) VALUES ('<Col1>1</Col1><Col2>2</Col2><Col3>3</Col3>');

SELECT RowID, DateTimeStamp, Col1, Col2, Col3, TblColumnSet
INTO dbo.Table2
FROM dbo.Table1;

Which properties of the sparse columns (from dbo.Table1) will be transferred to dbo.Table2?

Answer:

  • Name
  • Data Type
  • Nullability (NULL / NOT NULL)

Explanation: The sparse property is not transferred to the new table dbo.Table2.

References: INTO Clause - http://msdn.microsoft.com/en-us/library/ms188029.aspx

» Discuss this question and answer on the forums


Featured Script

Insertion Query Creation of a Table in Milliseconds

This script helps you to constitute insertion procedures for tables in a millisecond. 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

Backup process - Your feedback required! - Good afternoon, I have the following setup: [b]1) 5 SQL Server instances (within the trusted network)[/b] DBSERVER01 – 62 DBs (58 SIMPLE, 4 FULL) DBSERVER02...

Log file is growing too much under simple recovery model - In our production database log file growing too much in simple recovery, what cause , in simple recovery every time check...

Temp db log file is growing . - tempdblog file is growing to 5 gb. I dont have more space on the drive. want a immediate solution . without restarting...

sql server CONFIG statement cannot be used inside a user transaction - I got 'CONFIG statement cannot be used inside a user transaction' when running proc 2 below. Any resolution? Thanks. (sql...

xp_delete_file not deleting files on share drive - Hello, xp_delete_file not deleting files on share drive. is there any permission to be given?

Changing FileGroup for File Name - Hi, How can I change a file gorup for a specific file name? The issue that I'm having is that I already...

SQL Server 2005 : Backups

Failing backup can't create backup - I have a new SQL2005 database with several databases on it. I have a pretty de-facto backup (from using the...

SQL Server 2005 : Business Intelligence

ssis flatfile data to database table prob - hi friend i have a small doubt in ssis plz tell me how to solve this prob all columns are separete...

ssas - hi all, now i have created cube structure. is ther any program or query is used to develop the cube....

SSRS Matrix Percentage calculation - I have a simple matrix as such: Rows: Return reasons Columns: Week Start Date Data cells: Two text boxes in each column: 1)...

SQL Server 2005 : Development

Store proc or trigger - I have a situation where, we have SQL Server 2005 Standard edition SP4 - Over 100 little table per customer - each table...

SQL Server 2005 : SQL Server 2005 General Discussion

Is UPDATE FROM only available in SQL 2005? - I rarely have to write an UPDATE query that uses more than 1 table. Today I did, and I came...

Background process and SINGLE_USER - Hi, we have a critical heavy loaded task which is running through all user databases of our SQL-Server 2005 (SP3). This...

SQL Server 2008 Management Data Warehouse problem - Hi All, We have set up sql server 2008 management dataware house (mdw) a month ago, it was working correctly for...

Error 7320 while executing a MDX through Linked Server on SQl server 2005 - while executing a MDX through the Linked Server on a 64 bit SQL server 2005 it's throwing Error 7320: Cannot...

SQL Server 2005 : SS2K5 Replication

add article to replication without snapshotting entire DB. - hi I simply want to add an article to an existing publication WITHOUT creating a new snapshot of the entire database,...

SQL Server 2005 : SQL Server 2005 Performance Tuning

selection from db table using function or sp. - Can we do the selection from the Database table by creating the Table Value Function. Or shall we create the...

SQL Server 2005 : SQL Server 2005 Integration Services

Avoiding Truncation? - Hi Friends, My project is under SSIS 2008, SQL 2008. there is a column from OLEDB source coming as DT_NTEXT....

IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage () - Job fails with following error. Any help is appreciated. Executed as user: xxx Microsoft (R) SQL Server Execute Package Utility Version...

error - failure inserting into id column,oledb destination . help??????

SSIS package errors and fails in SQL Agent... - Hello - I have an import/export wizard SSIS package that I have saved, and am trying to automate, but it keeps...

Delete files from different folders, approach plz - Hi I have a requirement that I need to implement delete logic before loading into it. I have around 10...

pass SSIS Variables to SQL Procedure in SSIS - hi, I have two varibles ..... I want to execute the sp with two variables as parameters... Execute testing.dbo.proc_TEmptest value1,value2 The value1 and value2...

SQL Server 7,2000 : Administration

I Have A Problem With Primavera 6 - Hello There .. i wish from you to help me in fixing this problem in primavera 6 i installed the program correctly...

SQL Server 7,2000 : General

Raima Database Data Import - Have many old raima database that i need to import into SQL databases for review. How can this be done?...

SQL Server 2008 : SQL Server 2008 - General

How to diagnose a query with a sub-query against another server. - I have a problem with performance of a query embedded in some 3rd party software. The query (see below) performs a...

Trace Flag Forcing All Dependancies to exist at Proc Creation? - OK I'm in a discussion with a coworker related to dependancies. you know you can create a procedure that used defered...

Uninstallation sql cluster error - Hi, I have uninstalled sql server 2005 from Add/Remove Programs using Win 2003 server. i have verified SQL Server 2005 is no...

all about BI,sql - Hello, I am new to this forum, and really appreciate the responsiveness from my first post. Here’s my question : I...

How to edit User Defined Type's column datatype - HI, I need to changes User Defined Type's column datatype, and this UDT is using many stored procedure and user defined...

String or binary data would be truncated on 2008, works on 2000?? - Hi, I'm in the process of migrating a system from SQL2000 to SQL2008 R2 (both Ent Edition 64 bit). I'm...

Automate Backup without using SQLServerAgent - Dear All, We have Express edition and need to schedule backup. How we can automate backup without using Maintenance plans and SQLServerAgent...

Slow query - update a set a.Flag = 0 from table1 a WHERE a.OLD_NUMBER in (select number from table2) and a.NEW_NUMBER not in (select number from table2) The...

How to update two tables data with JOIN in Single query - CREATE TABLE ABC (Id INT, Name VARCHAR(10)) INSERT INTO ABC VALUES (1, 'PK'), (2, 'SK') CREATE TABLE ABC1 (Id INT, Name VARCHAR(10)) INSERT...

Using PIVOT or any other concept to transform row values to column values - Hi Everyone, I am in need of display results but stuck in middle. I have two results different ways. [b]Result1 col1 col2 col3 col4 ------------------------- 1...

remove identity property - hello all. I want to insert into identity column and i want to first remove identity property and insert value and...

DBA Stress Management - I work as a DBA in a small shop with 20 developers. The demand for service is insatiable and my...

cannot connect to named instance thru SSMS on developers desktop - Hi Experts, I got a scenario where one of the developer cannot connect to named instance, but able to connect to...

RANK/TOP WITHOUT TIES - Hopefully someone can set me straight here - I am looking for a way to select the top n items, so...

How to copy the database from external server to my local system server through Stored Procedure - Hi, I tried using the stored procedure to connect to the external server bu am unable to connect by entering the...

Log Reader Agent Login Failure - SQL 2012, Replication: When I start the Replication Monitor I see that I have an error. Opening Agent History I see...

Can't Connect To Cluster - Hi guys, I had a fully functional SQL 2008 R2 Failover Cluster up and running. Without getting into the heavy details...

How to Generate Required xml format using t-sql - <code> <timevalue value="20120101"/> <value xsi:type="sa" value="10"/> </code>

How to avoid this T-SQL statement affecting my TempDB? - [code="sql"]WITH CTE AS (SELECT DISTINCT LTRIM(RTRIM(P.IncidentIDRef)) AS MODIDREF, P.JurisID, P.EntDttm AS CREATEDDTTM, P.DIDCU AS CREATEDPFIDREF, 'INC' AS MODCODE, 'C' RECTYPE,...

increment values on day basis on column - hi all, i want to generate id column values on the basis of every day. suppose table name like test and table have...

Weird Requirement... Multiple Left Joins? Am I missing something? - Ok, so I think I should know the answer to this but I'm getting nothing but what looks like a...

MDF File of MS SQL Server 2008 is Corrupted - Last Night I have a problem due to Power failure actually i have used Microsoft SQL Server 2008 everything is...

can any one tell me how to split the given @parameter data into three column of table? - this is way i tried to split data in parameter [code="other"] declare @parameter varchar (200) set @parameter ='1_2_3|4_5_6' ;WITH CTE1 AS( SELECT...

Deadlock Output - Hello experts, i enables trace flags 1222,1204,3226 to capture deadlock data, when i check in errorlog file i get an out...

Query help to update with next records - Hi expert , Here is the details , DECLARE @t TABLE(col1 INT, col2 INT) INSERT INTO @t(col1,col2) SELECT 1, NULL UNION ALL SELECT 2, 2...

Adventure Works DW 2008R2 - Hi all, I am working through Bill Pearson's tutorial Stairway to MDX - STEP 1: Getting Started with MDX. http://www.sqlservercentral.com/articles/Stairway+Series/71867/ I downloaded and installed...

Changing db owner script - Hello to all, Thanks for your time and hopefully this wont be too difficult to answer. I need to change 30-40...

SQL Server 2008 : T-SQL (SS2K8)

Stored procedure in Where clause - Hi, [b]below is the query to display the project_name where project_name matches the condition.[/b] select project_name from Groups where project_name like '%20%Catalog%' [b]below is...

will there be a table lock situation while deleting records from a partitioned table ? - HI Consider the below scenario: I Have a table with 5 partitions based on the org_id. In other words : Table A Partition org_id 1...

missing index script? - 1. First script As below code, wirtten too much overlaping index whenever executing also impact application performance. [code="sql"]SELECT dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)...

Suppressinig error values - I am using a trigger to check some business logic before doing an insert. If the business logic fails, it...

does not work with outer join or outer apply - I am new sql server. I am trying to get the following data. I have 3 different tables table1 table2 table3 table1 id location 1...

Validate rows in 2 tables and write log - Hi, I'm having 2 tables with old and new project numbers, If project number in Table A matches with Project Number...

find a grouping with at least one row within the group containing a certain value - Hi all, How do you find a group where number of rows in the group are gtr than 1 and at...

##table question? - hello. here is the scenario: sp 1 inserts data in #table, #table is in sp2 and sp3 sp 2 and sp3 are...

help with SQLCMD - Hi , I am trying to use SQLCMD to run a SQL Script. The script is of type. Begin tran Delete ..... Insert ..... Commit...

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

SQL Server 2008 : Working with Oracle

Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server - I have a view in Oracle 11G. I want to show the contents in an Excel 2010 Pivot Table without...

SQL Server 2008 : SQL Server Newbies

What do you use for creating admin utilities - I moving in to more SQL Server support coming from UNIX based Sybase. When coding administartive utility code ouside of...

Importing multiple .txt files into one table - no SSIS - I wish to import multiple text files into one table in SQL Server 2008 but I don't have SSIS capability....

Help needed in converting an int datatype to MM/DD/YYYY and converting another int to HH:MM PM - When running the script below I noticed that the two result fields are of datatype int. I would like to convert...

Create view with no outer joins - Need to create view with no outer joins so I can index the view. Here is the query I use...

DBCC SHRINKFILE failing with error Msg 3140, Level 16, State 5, Line 1, Could not adjust the space allocation for file - Hi, I am trying to run SHRINKFILE on one of our data files but it is failing with error [i][b]"Msg 3140,...

SQL Server 2008 : Security (SS2K8)

Orphaned Users - Hi All I'm planning on backing up and restoring +- 20 Databases from one SQL Server to another I've been reading about orphaned...

SQL Server 2008 : SQL Server 2008 High Availability

Replication Isuse - I am new to replication, just started learn it and implementing it. the questions may be silly...thanks in advance. 1) I...

Log shipping secondary not showing new results from primary - Hi all, Just set up log shipping. I get no errors from the agent jobs and can see the files...

Question on Multi-Instance Clustering - My shop is about to set up a multi-instance (two node) cluster. I have not yet had the opportunity to...

Log Shipping & DataBase Mirroring - I am New to the Log Shipping & DataBase Mirroring, unfortunately i am asked to perform this by Client :( an any...

SQL Server 2008 : SQL Server 2008 Administration

it's easy Not being able to enable AlwaysOn(how to enable the AlwaysOn??) - SQL 2012 Evaluation---WSFC HOTFIX KB 2494036 - Hi Friends, We're working on SQL server 2012 AlwaysOn Availability, but we're hitting an issue while trying to enable the AlwaysOn...

Permissions issue in accessing SQLExpress 2008 - Hello -- We have a Windows 7 64-bit distribution workstation running a SQLExpress 2008 database. There is one instance on the...

Backup failed - We are taking backup on to data domain but its failing for 2instace and in another instance its working fine...

Index Design - Hi All After reading a few articles on Clustered/NonClustered Index design, I want to comfirm my understanding on the topic and...

How to find last accessed date time of a SQL Server 2008 database? - How we can find last accessed date time of a SQL Server 2008 database apart from using the DMV sys.dm_db_index_usage_stats...

SQLServerCentral.com : Anything that is NOT about SQL!

Quality of Service Document - Good morning all - I've been asked by the project management team to review our config management processes, and part of...

Is it OK to ask salary range before applying? - Hi all, I came across a Jr. DBA position with no salary attached. A google search of the company didn't turn...

Helpdesk software which integrates with TFS - Hopefully someone will know a product which acts as a helpdesk call logging system and integrates with TFS to track...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

Reporting Services : Reporting Services

Stored procedure in a function - Hi, i have a report developed in Report Builder 3.0, having 3 parameters, 2 are drop down and 1 is...

Data Warehousing : Integration Services

Derived Column Error - I'm trying to "change" the data of a specific column during a SSIS process. I have a text file, that...

Ways to address incremental updates in SSIS - Hi fellows, I'm trying to deploy a datamart, I have the star schema designed and I am working on the ETLs,...

Data Warehousing : Strategies and Ideas

Where to put a Type 2 SCD? - This is actually a follow up to an earlier question about a Type 2 SCD. Anyway, we have decided on the...

Data Warehousing : Analysis Services

Defining calculated measures that depend on terms at different granularities - I work at a rental company with multiple shop fronts (branches) in different areas. Each individual thing that is rented...

MDX query help IF/IIF statement - I'm converting a report from a TSQL dataset to use a SSAS dataset. The report has a status parameter to choose...

MDX query max size - Hi folks, I am presently working on a scenario where in the query size keeps increasing based on the number of...