A community of more than 1,500,000 database professionals and growing

Securing SQL Server: Vulnerabilities You Might Not Have Considered

Ashvin Kamaraju from SQLServerCentral.com

A short look at the vulnerabilities your data may be susceptible to outside of the database tables.  More »

Data Insecurity: A Perspective on Data Encryption

Ron Johnson from SQLServerCentral.com

The focus of this paper is on the protection of PII data stored within a database using encryption technologies. More »

A SQL-Based Universal Currency Formatter

Additional Articles from SimpleTalk

SQL Server isn't usually the best place to format dates or currency as strings. It can be a complex task to conform correctly with national and cultural conventions. Just occasionally, though, you need to do it. This is easy in SQL Server 2012, but if you aren't using that, what do you do? More »

From the SQLServerCentral Blogs - A Month of PowerShell – Day 25 (Integrity Checks)

Wayne Sheffield from SQLServerCentral.com

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

Recently Yahoo decided that remote work was no longer an option, requiring all workers to either begin coming into an office, or terminating their employment. As expected, it seems many tech workers were outraged, though more than a few noted while working at home is great, it isn't the best thing ever. There is no shortage of articles that support the decision, and ultimately I think, as with most things in the database world, what you think about this "depends" on your situation.

I've worked at home for over a decade; my wife for over two decades. In that time we've learned a few things. One of them is that telecommuting isn't for everyone. It's hard. We alternately struggle trying to stay motivated with understanding we need to stop working. We realize that the ability of telecommuting to work is job dependent. The more your job involves just you, the more likely it is that you can do it remotely. The more it involves collaboration, the more you'll struggle to get things done. We have also learned that periodic face-time is important in almost all situations.

There are numerous challenges that others have elicited with telecommuting, but the success or failure of remote work is measured no differently than it is in an office. If you are accountable for your productivity and complete all your work, you're a success. If you don't, you're not. It's that simple. It doesn't matter if you work in an office or never leave your house. It's about getting work done.

The people that do well as telecommuters typically do well in an office. They're just happier at home where they can juggle tasks. The people that slack off in offices, won't do well at home. We might blame slacking off on being away from supervision, but my experience is that people that don't want to work hard, don't.

I don't think the Yahoo decision was a good one for everyone, but it probably was for some. I think telecommuting is a great way to build a small team, and get work done in an efficient fashion when jobs are discrete and easily defined, but ultimately employee success comes down to each person being held accountable for their work.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 17.2MB WMV
• Watch the iPod Video Podcast - 20.4MB MP4
• Listen to the MP3 Audio Podcast - 4.1MB MP3

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

Today's Question:

We have following 6 sql statements. Run each, one at time on SQL Server 2008. Which queries will fail? (select 2)

-- Query #1
USE master;
GO
IF DB_ID('DBDB') IS NOT NULL DROP DATABASE DBDB
GO
-- Query #2
USE master;
GO
IF OBJECT_ID('DBDB') IS NULL CREATE DATABASE DBDB 
GO 
-- Query #3
USE DBDB; 
GO 
IF OBJECT_ID('dbo.TBTB') IS NULL CREATE Table dbo.TBTB (tb1 int)
GO
-- Query #4
USE DBDB; 
GO 
IF OBJECT_ID('dbo.uspUSP') IS NULL 
 CREATE PROCEDURE dbo.uspUSP AS SET NOCOUNT OFF
GO
-- Query #5
USE DBDB;
GO
IF OBJECT_ID('dbo.TYTP') IS NULL CREATE TYPE dbo.TPTP AS TABLE(TYTB int)
GO
-- Query #6
USE DBDB; 
GO 
IF OBJECT_ID('dbo.udfUDF') IS NULL 
 CREATE FUNCTION dbo.udfUDF(@f int) RETURNS INT AS BEGIN SET @f=1 RETURN @f END 
GO

By G C

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.

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.

What does this script return?

DECLARE @vchrIsNumeric VARCHAR

SET @vchrIsNumeric='3NAME'

IF ISNUMERIC(@vchrIsNumeric)=1
 SELECT 'Is Numeric'
ELSE
 SELECT 'Is not Numeric'

By Ramana Reddy

Answer: Is Numeric

Explanation: From Books Online: varchar [ ( n | max ) ] ; When n is not specified in a data definition or variable declaration statement, the default length is 1. In this case only "3" is stored in @vchrIsNumeric variable and this is evaluated to ISNUMERIC() = 1

Ref: http://msdn.microsoft.com/en-us/library/ms176089.aspx

» Discuss this question and answer on the forums

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Objects Referenced and Referencing

Charles Jacobus from SQLServerCentral.com

This script reports objects that are referenced by and that reference a given database object. When a column name is specified, it also includes results showing how that column is used. More »

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

SQL Server 2005 : Administering

sql server 2005 issues with nightly jobs - Hi, I am just starting to learn about these things, and i was hoping someone could give me some pointers to...

MSDN subscription confusion resulted in wrong edition of SQL installation! - So I logged into our MSDN subscription and there it was SQL Server 2012 Enterprise Edition with No Product Key...

SQL Agent troubles - The system cannot find the file specified. - Hi All I have a SQL 2005 system. The SQL Service is started with no issues - However, I can't seem to start...

SQL Server 2005 : Backups

Backup Failed but backup file is created - Hey guys, I have an issue with backing up a 80GB database over the network, Here is the error:A nonrecoverable I/O...

Differential backup question - I do FULL backups every two weeks and daily differentials on a number of SQL instances. The backup command I use...

Transferring Backups - we need to copy a backup file between two very slow network Data centers (transfer rate: 350 kbps), Our full...

SQL Server 2005 : Business Intelligence

SSIS - FTPS - Is there any way to implement FTPS in SSIS SQL 2005 without thirt party tools?

Adding identity values in a data flow - Hi All Having an issue with SSIS that is doing my nut in. In one of my data flows i wish to...

Access tasks in Data Flow Task dynamically - Hi friends, I have a package where the number of columns in DB tables could be changed. I want to change...

SQL Server 2005 : Development

When would be the order by faster ? - [code="sql"]use DOMAIN_event go CREATE table email_destination_bk ( msg_stub ut_stub not null , acct_id int not null , evt_stub ut_stub not null, evt_code nvarchar(20) ) create clustered index idx...

SQL Server 2005 : SQL Server 2005 General Discussion

Reducing Initial LDF/MDF size - Hello, I have a database (version 9.0.4060) that has an initial log size set to 322GB. I would like to change...

Can't Connect to SQL Server 2005 - I have a network with two Windows Server boxes. One is a 64bit machine and the other is a 32bit...

SQL Subtraction - Seperate rows - Hi I currently use the following statement: SELECT * FROM Month1_Month2_Diff_Detail ORDER BY Name, Order, Month and receive the following back: Name Order Month Detail ...

SQL Server 2005 : SQL Server 2005 Security

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. - [b]Problem Statement[/b]: I have some users that are not a part of any server role (except the default public). For each...

SQL Server 2005 : SQL Server 2005 Strategies

TempDB optimization - hello, in ma current configuration i have one data file and one log file of a tempdb database. as a process of...

SQL Server 2005 : SS2K5 Replication

Changing Distribution agent profiles during business hours - Does this require a restart of the distribution service, or are there any other pitfalls or traps? I have a...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Resolving a deadlock - I have a deadlock between a trigger and a stored procedure stored procedure is always the victim of the deadlock....

SQL Server 2005 : SQL Server 2005 Integration Services

Help Passing a variable using OLE DB source - I'm having trouble passing information from a variable to a query. I currently have 3 variable for this: _QueryVariable, _ExcludeInfo,...

Invalid character value for cast specification - I have a txt file with data as below: col1,col2,col3 NY,14319,9900 MA,14241,9900 CA,14358,Get WO NE To load this file, I am using a flat file...

SQL Server 2005 : T-SQL (SS2K5)

More help wit Sum, Pivot and so on... - Hi! First thanks for the help I got a couple of weeks ago! :-) Now to my new proble that is to...

SQL Server 2005 : SQL Server Newbies

scheduling job - Hi friends, We need to monitor a user session for locks to troubleshoot a problem in the program. When the...

SQL error Connection. - http://imageshack.us/photo/my-images/339/89149644.jpg http://imageshack.us/photo/my-images/4/36524814.jpg When i Click start at "Service manager" they be like this . Im using SQL 2000 =Sory my bad english

Moving Secondary Database in Transaction Log Configuration - I’ve been tasked with moving a production database server that is involved in log shipping. I am not a DBA...

SQL Server 7,2000 : SQL Server Newbies

MDX - SSAS, getting result count of last measurement within a period - Hi I am having trouble trying to write some MDX code. I am trying to get the last measurement result pr....

SQL Server 7,2000 : T-SQL

DTS - result to excel - Hi Guys I have created several DTS packages, and the data gets stored in csv flat file and get mailed automatically. Now...

SQL Server 2008 : SQL Server 2008 - General

SQL Server for Content Censorship - Hi Champs, We have little strange requirement in a case of SharePoint where backend is SQL. Users in social features of...

Auto Update Statistics Asynchronously option - Hi Does the option "Auto Update Statistics Asynchronously" work for update ,delete and insert statement or it is just used...

Attempt to fetch logical page (1:440) in database 2 failed. It belongs to allocation unit 422212869292032 not to 6269010747738816512. - hi all, One of my sp is giving the following error some times not every time: [b]Attempt to fetch logical page (1:440)...

Concatenate with a smallint variable - Hi All, I could not format a column in the below, [u] [i] [b]declare @table table (mMonth smallint); insert into @table values (1),(9),(11),(12); select mMonth, LEN(mMonth) Length, case...

Mirrored DB log file growth is High - Hi all, I have mirroring setup in SQL 2008r2 server, Everything working fine. But MirroredDB LDF file growth is very high. How...

Disable AUTO_CREATE_STATISTICS and AUTO_CREATE_STATISTICS for all databases - How to disable AUTO_CREATE_STATISTICS and AUTO_CREATE_STATISTICS for all databases in single step?

Transactions - Is it possible to update 2 Db with 2 different sqlConnections under same Transaction ? Thanks.

SSIS not producing information from my COMPUTE BY clause when exporting to Excel - When I run a script that uses COMPUTE BY in SSMS I get the desired results. When I try and...

Execution Plan - Key Lookup - I run the following query on the AdventureWorks database. SELECT ContactID, LastName, Phone FROM Person.Contact WHERE EmailAddress LIKE 'sab%' In the execution plan, why is the...

If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION - Can someone explain how data compression works at the index level? If I compress a primary key, am I compressing...

Displaying Data From 3 SQL Tables On A Form - I have a program of my own, for my own use, written in Excel VBA that I want to rewrite...

convert 2000 db to 2008 r2 db - Upgrade to 2008r2 from 2000. Backup the 2000 db and restore to 2008 r2. Create all application users and add...

Virtualization - yet again ... - I spent some time searching this forum and the interwebs and haven't found a lot of definitive insight/agreement on virtualization...

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

Formatting issue in SQL - Hello all, I have seen some strange things in my SQL Server 2008 enterprise edition. whenever I do sp_helptext for...

query performance - I have a table with 6 million rows. The table has around 200 columns. The data can be sliced and...

sp_updatestats and the default sampling rate - Using SQL Server 2008R2 As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue...

Copy a big table (250G) form one server to another - Hi, I need to copy a big table (about 250G, basically used to store images) that I have on a MSSQL...

SELECT list block with CASE? - is something like this possible? SELECT CASE WHEN (@X = 1) THEN COLUMN1, COLUMN2, COLUMN3 ELSE COLUMN4, COLUMN5, COLUMN6, END FROM TABLE1

Just accepted a Sr. DBA job. Time for a gut check? - Hi SQLSC! Buckle up, I apologize in advance for the long post. In many ways, typing it out is as much...

xml query datetime datatype problem while inserting to xml - I have a query like UPDATE a SET oldRemarks1.modify('insert <Value_Py>{sql:column("b.collection_date")}</Value_Py> after (/Root/Row[3]/Item)[1]') from b where a.[Date]='31-May-2004' and a.sl_no=b.regkey_slno Here collection_date is of datetime...

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)

To find perfect expense? - Hai friends, create table user ( user_id varchar(100), name varchar(100), designation_id varchar(100), grade_id varchar(100) ) insert into user values('0012','abc',13,8) insert into user values('0010','bc',5,3) insert into...

How to-Combine 3 Storedprocedures O/P to get one output report without UNION clause - Hi Friends, I have very complex functionality- I have developed 3 Stored Procedures- 1) Jobposting, 2) WorkOrder and 3) Worker now the final step...

substring issue - Not sure what I am missing here possible brain freeze declare @loginname varchar(35) set @loginname = '<rxno>001c</rxno>\test1' select SUBSTRING(@loginname,CHARINDEX('<rxno>',@loginname)+1,CHARINDEX('</rxno>',@loginname)-CHARINDEX('rxno>',@loginname)-1) I want to return...

Database Mail + sysmail_start_sp - Hi, My Database Mail works fine, but every time i open SSMS, in order to send mail. I have to execute...

How to report period wise data ? - Hi , I have a table which holds the employee details : /*******************************************************************/ /****** Object: Table [dbo].[empleaves] Script Date: 05-03-2013 17:42:59 ******/ SET ANSI_NULLS ON GO SET...

FOR XML and trying to avoid nested cursors - Alright, history first. I've got a bit of a mess I've inherited. Groups inheriting groups inheriting... you get the drift....

Guidance on table valued types. - We have 1 table valued parameter (tvp) coming to GetXXX stored procedures. It contains only 1 column of type uniqueidentifier....

Grouping records by time interval - Hi everyone, I have been searching for a few days now to try to solve a problem, but without success. What...

Hierarchy example - Ken Henderson's book - not working - I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL. He wants to show two things....

SQL Server 2008 : SQL Server Newbies

Performing calculations on subtotals - Hello: I'm a new user to SQL Server 2012 Standard. I have a table with information similar to what's found...

View results into string - I have a table where I need to take any number of financial distributions and stack the accounts into a...

Really confused newbie - Perhaps a Temp Table query? - Hi Guys, I'll basically do my best to describe what I'm trying to accomplish, and give you the query I have...

Move Database From One Server to Other - Hi All, What I am trying to do is that I am trying to copy database from Other Sever. I am...

Trying to make my lookup table unique - I am running the following query, what I am trying to do is only have in my reference table the...

Problem with a query - wrong update results - Hello experts, I have 2 tables. One of the tables has the data I need to know which user input it. On...

combine multiple rows into one dynamically query question - Hi, I have the below table, and I need to combine them into one records dynamically (I'm not sure I'm clear...

SQL Server 2008 : Security (SS2K8)

Disable & Rename 'SA' - Hi All, Our SQL risk assessment determined that 'sa' should be renamed and disabled. As a fallback readiness, i gathered all...

SQL Server 2008 : SQL Server 2008 High Availability

sql server mirroring synchronization not happening - Hi all, I have mirroring setup, Seems to everything fine.But synchronization not happening.i don't understand what was the problem. Please any one...

Help moving SQL instance folder structure from one SAN to another (cluster environment) - At the office, we have a 2-nodes cluster configuration running a SQL Server instance on each node (active/active). We need...

How to stop SQL services on both cluster node without failover - I need to clean stop SQL services and dependent services on Cluster both node without failover for adding new SAN...

Jobs not running in cluster - Hi , We have cluster environment with mirroroing . We have configured jobs to take the fullland log backups(all jobs in that...

SQL Server 2012 "Always On" "Listener" not working with named instance - Hello, I have setup "Always On" on named SQL Instances. I have setup the listener as well. I can not...

SQL Server 2008 : SQL Server 2008 Administration

Lazy Writer - Hi All I want to begin by stating that I have read the corresponding chapters in the SQL 2008 Internals (Kalen...

DBCC Table Check Long Running/Locking Issues - I have two 'identical' systems, both running SQL 2008 Enterprise with SP2 and Windows 2008 64bit. The DB's are around...

Mirror failover - Hi All, I have an issue with mirror failover 1. I configured mirroring on 3 databases. if in 3databses one got failover...

Database Owners - It recently came to my attention that the DB owner on many of our databases is the user who created...

Data Issue - Hi Experts, In one of our database developers found an issue with data. The data is 4F which is returned correctly...

rename SQL server - After we changed computer name, and I renamed SQL server name too following ms articles. by using sp_dropserver, sp_addserver. But I...

How notificate create database event - Hi, I need to detect when a new database is created in my SQL Server and send a mail notification to...

Career : Certification

Exam 70-461 - Hi, Ive just ordered the book: Querying SQL Server 2012 Training Kit. I am planning to take the exam 70-461 within...

Programming : General

column selection on dynamic basis - Hi, How can i select columns of a table dynamically through a select statement if the column name need to be...

Web dev suggestions - In some circles this could be as touchy a topic as asking which religion to follow. I'm a full time...

Generating JSON from SQL - Does anyone know of (or can recommend) a resource that tells me how to convert SQL query results into JSON...

Programming : Powershell

Risks/Best practices on execution policy - While it would be easy to set the execution policy to unrestricted, or possibly set it to unrestricted in an...

Determining if a server is clustered or stand-alone - I'm in the process of writing a powershell script to inventory my SQL Server environment. In collecting the server information,...

SQLServerCentral.com : Anything that is NOT about SQL!

The Beer Cooler Thread - On popular request - at least one! - a dedicated topic about beer. I shall start with my favorite beer: Duvel. It is...

Reporting Services : Reporting Services

XML Output - Fields in reverse order? - Guys, For some reason the XML output given by SSRS 2008 seems to put the fields in reverse order, has anyone...

MultiValue Parameter not working - Hello all, I have a table that displays different statistics about "Properties". The table only has 1 row, this single row...

Database Design : Disaster Recovery

In which Scenario we can use ldf file in sql server? - Hi all, ldf file will maintain all transactions based on recovery mode. but when we can use that ldf file and...

Data Warehousing : Integration Services

The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". - I have a csv file (FIXED Width) with one cloumn and I read that column as string data type and...

Datatype Issue ..SSIS - HI, I have a task in ssis which loads data into DB from Source Files on my Drive, Issue is...

IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found - I have a Macro which works in Excel. It basically just formats headers and creates a validation list so the...

Problem to create a CONNECTION MANAGER inside a SSIS - Hi to all. I currently use SSIS of SQL SERVER 2008 R2 to create data flow to import data from several...

Data Warehousing : Analysis Services

Different aggreation results with and without a dummy WITH clause - I'm trying to diagnose a bug in a calculated measure in a SSAS cube, and while debugging I get this...