In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro 7 Have you tried SQL Backup Pro 7 yet?
Get faster, smaller, fully verified backups today. Try SQL Backup Pro 7's automated DBCC CHECKDB and be prepared when you need to restore. Download free trial.
 
SQL Source Control Easy evaluation of database source control
The evaluation repository makes it easy to try SQL Source Control. Get started with the 28-day free trial.
 
SQL Monitor Short on time? Start monitoring today
Red Gate’s new hosted SQL monitoring service is so quick to set up, you’ll be seeing results within the first five minutes. Get results fast – find out more:

In This Issue

TDE and SQL Server Databases

The article discusses and demonstrates TDE for SQL Server databases More »


SQL Server 2012 Integration Services - Using dtexec.exe to Run File System-based Packages

Marcin Policht explores a method of running SSIS packages that relies on the dtexec.exe utility. More »


From the SQLServerCentral Blogs - Monitor Deadlock in SQL 2012

Do you still use trace flag 1204 and 1222 to monitor Deadlock? or using profile to capture deadlock? Now we... More »


Editorial - Marketing Data is Exploding

The last few years have seen the rise, and explosion, of social media. While fundamentally social media isn't introducing much that's different from actions in the real world, it is making the reach and speed at which we can communicate grow exponentially. Facebook will surpass a billion users soon and Twitter has exploded in the world. Even in the tiny SQL world, Twitter use has grown exponentially in the few years since I've used it. It you're unsure of what's happening there with regards to SQL Server, search the #sqlhelp hash tag and see what you find out.

All kinds of companies are starting to try and integrate this data into their systems. If you haven't yet, I suspect you might have to some time in the next few years. There's a short piece in how this data is in use at GM, and it's interesting to read. I don't know how seriously other companies take this, but I do see so many people "following the herd" and as more managers read about other managers implementing projects like this one, we'll see more adoption.

What's interesting here is that it gives us data professionals a number of new opportunities. From the sheer OLTP-type work of collecting and managing this data in real-time, or near real-time, to the need to add BI analysis to trends of data. I can see a lot of new projects, and new employment in this area. It wouldn't hurt to beef up your skills in this area, and maybe get a better understanding of how social media can work, as well as how you can manage the data.

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. 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.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

I create the following table: 

DECLARE @NewTable TABLE 
(MyID INT IDENTITY, 
col1 varchar(20))

and then insert some data.  What will be the result of executing the following code?

TRUNCATE TABLE @NewTable

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

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

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.


Yesterday's Question of the Day


DECLARE @EmployeeDetails TABLE (EmpNo Int ,
                     EmpName Varchar(10),
                     EmpPlace Varchar(100))
                    
Insert into @EmployeeDetails Values(1,'samith',NULL)
Insert into @EmployeeDetails Values(2,'sreepathi','Kannur')
Insert into @EmployeeDetails Values(3,'Jamsheer','Calicut')
Insert into @EmployeeDetails Values(4,'Naseer',NULL)
Insert into @EmployeeDetails Values(5,'Rejith','Palakkad')

SELECT *
FROM @EmployeeDetails
WHERE EmpPlace <> 'Kannur'

What are the number of rows returned in this query?

Answer: 2

Explanation: The Null value also matches the condition in case of Not Operators (<>,Not in ,Not like..) since it could possibly be a match.

Ref: <>- http://msdn.microsoft.com/en-us/library/ms176020.aspx

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.


Featured Script

Trim Left Char (for single words)

Remove the first replications of a specified char in a word. It does not work well with text that contains blank spaces in the middle. 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

Agent not showing in SSMS - I have remote server. Checked it this morning and I can't see SQL Agent in SSMS. I have stopped and restarted...

How should i free up the space from the BACKUP Drive whcih is increasing Rapidly - How should i free up the space from the BACKUP Drive whcih is increasing Rapidly Thanks Rohit

Filegroup vs performance - Hi, I currently have a +50GB database with only 1 filegroup (Primary). I am planing to archive some tables base on...

SQL Server 2005 : Backups

Master Backup Report? - My problem is that I have many different SQL servers and would like to have report emailed to me every...

what is Copy-Only Backup? - Hi, What is Copy-Only backup and how does it works? thanks Koteswarrao

SQL Server 2005 : Business Intelligence

kpi formula in dashboad designer (pps) - Hi I want to calculate a rate of evolution of a number over the years, which exists in a SharePoint list...

How to check Edition of Analysis Services, version and edition of Reporting Services - Hello all, How can I determine the Edition of SQL Server Analysis Services installed? Say for a SQL Server Database Engine, I...

SQL Server 2005 : Data Corruption

Regarding database backup corrupted - Hello Team, I was facing an issue with database backup which I took from sql server 2005. Actually in my office I...

SQL Server 2005 : Development

create procedure with execute as - I have stored procedure which includes bulk insert, activating and deactivating the access to xp_cmdshell and executing xp_cmdshell. I am...

Select Statements in Stored Procedure gets Error converting data type DBTYPE_DBTIMESTAMP to datetime. - I have a select statement that executes successfully by itself, so I used it in an stored procedure that our...

SQL Server 2005 : SQL Server 2005 General Discussion

check the physical memory in use - Hi

SQL Server 2005 : SQL Server 2005 Security

532 event from something trying to use the SQL service account - Hi All - Just taking a shot in the dark here... Here is my situation. It seems rather arbitrarily a 532 even...

SQL Server 2005 : SS2K5 Replication

Mirroring 4 Migration: Will this work? - Hello - I have a situation where I am being asked to implement mirroring for a SQL Server 2005 Standard Edition...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Any info on Deadlock detection algorithms? - Hi Gurus, I'm trying to find a starting point from which to troubleshoot an issue we are facing where the same...

SQL Server 2005 : SQL Server 2005 Integration Services

file example - hi, i need example of taking data from file and putting into table and doing some modification of data , getting data...

multiple datasources/servers? - Hi, What should be sql server integration package contents? For instance Query is ------------------ [code="sql"] select * from orders [/code] when connected to server...

Processing was cancelled due to an interrupt. SQLSTATE=57014 - My SSIS package throws below error while fetching data from DB2 to SQL Server. SSIS package fetch 50 tables from...

Assign a single column query value to SSIS variable - Hi I have created the following OLEDB Connection to the database variable --> user::GetName Exec SQL Task --> Select Top 1 contactname from contacts...

SQL Server 2005 : T-SQL (SS2K5)

How to escape the ^ symbol in the following function - Good afternoon. I have the following function: [code="sql"] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_QBRemoveNonAlphaChars]') AND type in (N'FN', N'IF', N'TF',...

How do I determine what process causes a lock with request_session_id = 0? - My Service Broker is blocked by a lock that I can see in sys.dm_tran_locks. But how do I determine what...

save varible in procedure - hi, i have problem with my storeprocedure : ALTER PROCEDURE [dbo].[sp_createdate2] @username AS VARCHAR(20) AS BEGIN DECLARE @FirstTable TABLE (id INT IDENTITY NOT NULL PRIMARY KEY,userid...

How to update/roll back two tables in separate Dbs from Stored Procedure - Take 2 - I posted this question [url=http://www.sqlservercentral.com/forums/Topic1316040-338-1.aspx][/url] awhile back and received some great responses. I have encountered an issue with the proposed...

Logic for comparing and deleting... - I know this probably is a lot more easy to construct than I can construct (in my non-developer mind), but...

How do you use a sub query in a join? - Looking to take the following query: SELECT sales_order_number , count(sales_order_number) as Notification_cnt from ztb_IMP_Notifications group by sales_order_number and use it to feed the ztb_IMP_Notifications section...

Inserting data in one table as selecting from other tables - Hi all! I have a simple question. I've made a simple database in Access and upsized it in my SQL Server...

hierachy with miltiple parent - Childid Childname Parentid pid 100 Bingo 200 1 101 Pingo 201 1 102 Zingo 201 1 100 Bingo 201 2 101 Pingo 200 2 102 Zingo 201 2 100 Bingo 201 3 101 Pingo 201 3 102 Zingo 200 3 100 ...

SQL Server 7,2000 : Administration

Installing SQL2000: Unable to update path variable - RegDBGetKeyValueEx - Hi I'm trying to install SQL 2000 Developer edition on my Laptop. Win 7. I do clean uninstalls after each failure....

SQL Server 7,2000 : Data Corruption

Suspect Mode - Good morning, It is a bit of a long story but i'm recently hired and have had a sql server that...

SQL Server 2008 : SQL Server 2008 - General

Can't KILL a process - I have a process that is blocking a lot of stuff on the server. The session id is "-2", which...

check the physical memory in use by sql - HI I use sys.dm_os_process_memory in sql server 2008 for checking the physical memory in use Is there any way I could...

While Loop into a Set Based Insert - I was wondering if anyone is able to provide me with any help turning this while loop inside a stored...

SQL Server replication and tablediff - Hi All, So my subscriber server had a crash the other day... The result of which meant that some row inserts...

Are there limits on the number of SQL Agent jobs? - Hi all - We have a real-time system for checking the status of lone workers, and I currently create a SQL...

Parallel database backup restore script - Please provide the parallel database backup restore script... Thanks -kamal

Transform XML record to master and detail table - Hi everyone, I have xml record as below <row id="VFAL.2312" xml:space="preserve"> <c1>DETAIL</c1> <c2>VFAL.2312</c2> <c2 m="2">272101-01</c2> <c2 m="3">AIR DLG-DOUBTFUL LOAN VB>1Y</c2> <c90>1</c90> <c91>43_VFC.2__OFS_BROWSERTC</c91> ...

Converting DTS to Stored Procedures - Hi, I have DTS packages and i need to convert those DTS packages to Stored Procedures in SQL Server 2008 R2. Some...

STORE PROCEDURE - Hi I have to tables and I have to create a store procedure which gives a problem here can anyone help.....

How was the record deleted!! - Hi All I am having a strange issue. From .NET application 5 records are inserted in a Table A. These records are...

error using linked server and EXECUTE AT - I am using sql 2008 R2 I ran the following on server A select name from sys.servers The "linkserverB" exists ( linking server B...

Insert Into Using OpenQuery Causes Massive Fragmentation - I use the following code to import a table from an Oracle DB to SQL Server every day: [code="sql"]TRUNCATE TABLE mytable...

Calculate days - I need help to code days. As the sample data below, days between [ADMDATE] and [DSCHDATE] need to be count...

Where are my SSIS packages stored on my server? - Hi, I saved a couple of import packages on my SQL Server. How do I go about finding these packages?...

opening ssrs straight into excel - Hi I would like to open an ssrs report directly in Excel via a link. I have tried &rs:Format=EXCEL and it doesnt...

Reporting on a production database - good or bad and why? - My development group is currently debating a new process we wish to implement and I'd like to get input to...

Column level collation to store multi linguage characters. - Morning all. We receive data from a 3rd party, the flat file is correct in that it contains the right characters...

Querying Data From One Table Against Another Table For All Results - Hello, I am new to the forums and overall new to SQL and SRSS. I have two 2008 SQL databases that...

Using REPLACE with a string where I need to replace %anystring% with some new string - Hi everyone, I am actually fairly new to using the REPLACE function and don't know if I can use regex with...

SSIS package load data from SQl to Mysql - Try to create a SSIS package to insert data from SQL to Mysql. The setup is as follows OLE DB connection to...

sql server max_lenght returns double the actual size - Hi all, I am retrieving column properties of a table with the following query [code="sql"] select c.name as ColumnName, type_name(c.system_type_id) as ColumnType, c.is_nullable...

Pivoting the table - I need some help in pivoting the table. The table is like this: [b]File Main Sub Text[/b] 1 1 A. hello 1...

Creation of dynamic sql query to create a select statement to pick a unique record - Hi All I am working on a search stored procedure for a website, I have following input parameters: [b]@Region[/b]: A region name [b]@Country[/b]:...

SQL Browser service needed for non-standard static port? - If I am using dynamic port settings on my sql server instance, I need the sql browser service to run...

How can I get SQL Browser Service running - I'm running sql server 2008 enterprise. SQL Server Configuration Manager shows all services running with the exception of SQL Browser...

How do I remove my stored password/username in SSMS 2008? - Hi everyone, I entered a wrong password for my ssms login and I am unable to delete it. SSMS remembers this...

SQL Server 2008 R2 Service Pack 2. - Hi All, I wanted to know whether the service pack 2 for sql server 2008 r2 is available for download. I...

SQL Server 2008 : T-SQL (SS2K8)

read the current row and previous row & calculate difference reporting values over - I am trying to write a script to read the current row and previous row per device and work out...

INSERT with ordered select - I am trying to optimise a script for performing DELETES in a DW staging table. DELETES are not logged on...

How to grab characters before and after a string - Hi, I need to take the characters from the left before the '/' string and insert into a column and the...

Insert records into one table form other table - Hi friends, I have a situation where i need tocreate table and insert records into that table from other table. New...

Yet another rows to column question (sorry) - Hi, I have read Jeff Moden's series on converting rows to columns but can't see how to do what I need...

Geography datatype problem in updation - Hi I have a location table [code="sql"] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[LocationCopy]( [LocationID] [int] IDENTITY(1,1) NOT NULL, [LocationName] [varchar](100) NOT NULL, [CityID]...

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

"UPDATE" Is Not Updating Correctly - I am executing the SQL Statements on SQL Server 2008 (SSMS). What am I missing? (I'm sure it's me, not the...

What is meant by set based programming? - Hi all, I have come across various section on this website where it is mention that SQL Server is a set...

SQL Server 2008 : Working with Oracle

Change of source databases(9i to 11g) 0xC02020F6 cannot convert between unicode and non-unicode string data types - Hi, Could someone tell me how to resolve this issue. I changed my oracle source database from 9i to 11g. Everything worked...

OraOLEDB.Oracle.1 is not registered on the local machine - I installed the 64 bit 11g 11.2 Oracle Client on a 64 bit Server. Then I installed the 32 bit Client...

SQL Server 2008 : SQL Server Newbies

Stored Procedure InputParameter and SQLString - Hello sqlservercentral :) I created a stored procedure to to grant/revoke permissions a bit more comfortable. (found most of it in the...

Format number thousands separator with point - Format number thousands separator with point Hi there, I need your help. I have this number in my database output result of...

Can't connect to sql server from an XP machine, but I can from win7 - Hi there, I've been given access to a SQL Server and I wrote a VBA script (in excel) to query the...

SQL Server 2008 : Security (SS2K8)

user mapped as dbo - hi, we are trying to drop a login that happens to be mapped/aliased as 'dbo'. it is not the owner of the...

system schemas owned by user - Hello, In one of our databases, the following schemas are showing as owned by a regular user: db_datareader db_denydatawriter db_owner the other...

SQL Server 2008 : SQL Server 2008 High Availability

Database Mirroring with clustering - Hi all, I've currently got a 2 node SQL Server 2008 R2 Enterprise cluster (Cluster_A). This works fine at the moment...

Log shipping throw errors - I have log shipping set up on my database running on SQL Server 2012. It is scheduled to run Monday...

Adding a second instance to a cluster - Hi there, I currently have a single instance cluster with 2 nodes and I have a requirement to add a second...

SQL Server 2008 : SQL Server 2008 Administration

SQL Default Schema for Users in the Local SQL Groups - Hello, I've just seen some strange behaviour and was hoping someone here could help explain why. I have a DB1 and SCHEMA1,...

SQL Plan Cache - Hi All I've been using the below script to check my Plan Cache for Ad-hoc plans [code="sql"]SELECT objtype , usecounts , COUNT(*) AS [no_of_plans] FROM...

Understanding Latches\Locks - Hi All I'm trying to understand Latch\Lock Waits in SQL Server If I am seeing waits with a wait type of %latch% Does...

PAGELATCH_UP suggestions - We're experiencing high waits for this type on a user database during high volume inserts on a partitioned table. What...

delete log file - I would like to use http://ola.hallengren.com/ script. There are log output files created in the folder of MSSQL\log. How can I setup...

Using a static port - I am wanting to use a static port for SQL but something that has always confused me with the following...

How change Analysis Server services account - Hi, I have installed a SQL Server 2008 R2 in a Windows Server 2008 R2 Server (this server is DC). All sql...

Upgrade from SQLServer Express to Full Version - How easy is it to move a production server from SQLServer Express to the full version? Is it just a...

Index Range_scans - Hi All I'm struggling to understand why my Index is reporting Range_scans DDL: Table: [code="sql"]CREATE TABLE [dbo].[Indexing2]( [Col1] [int] IDENTITY(1,1) NOT NULL, [Col2] [int] NULL, [Col3] [int]...

Programming : Connecting

Microsoft DB2 OLEDB Provider 4.0 - Has anyone managed to connect to DB2/AS400 using the new version (4.0) of Microsoft's DB2 OLEDB provider? I had the version...

SQLServerCentral.com : Anything that is NOT about SQL!

tablet ipad or nexus 7 or kindle fire ? - Hi, I think the ipad has that cool fad factor, but still overpriced. I am interested in buying one but...

Good relationship with manager - Hi all, Can you please suggest me how to make soft relationship with my boss since we are not working at...

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

Converting from Spreadsheet reporting to SSRS - I am in the process on trying to convert a bunch of our monthly reports from Excel Spreadsheets to SSRS...

Conversion of crystal report file to ssrs - I have a project in V1.1,Now we have to convert in VS2010 V4, so we have to convert many rpt files...

Reporting Services : Reporting Services 2005 Development

How to sort a matrix group with an expression? - I have a report that i cannot make it sort on a group based on an expression. I have another report...

Data Warehousing : Analysis Services

SSAS for the DBA - replication questions - Hey all, Sadly, I know close to nothing about SSAS. I recently joined a new company and we're working on some...

Data Warehousing : Data Transformation Services (DTS)

Cannot edit DTS Package on Windows 7 - When trying to open a DTS package using SQL Server Management Studio 2005 on Windows 7 (64-bit) get the error: ------------------------------ [b]This...

DTS connection to Access app w/ workgroup security - I need to export data from an Access 2000 (SP1) app which uses workgroup security. I open a new Access...

Article Discussions by Author : Discuss Content Posted by Brian Knight

Transparent Background Color in SSRS Textbox - I get a warning similar to this: [i][rsInvalidColor] The value of the BackgroundColor property for the textbox ‘textbox22’ is “Transparent”, which...