In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.
 
Deployment Manager One-click deployment for .NET and SQL Server applications
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.
 
SQL DBA Bundle ‘Six Scary SQL Surprises’
Brent Ozar joins the DBA Team, for Lesson 3 of their ‘Top 5 Hard-earned Lessons’. Gain valuable tips from the pros - Read now.

In This Issue

Tune SQL Server 2012 Databases Using Database Engine Tuning Advisor

Microsoft Database Engine Tuning Advisor (DTA) is a database performance tuning utility designed to analyze your SQL Server database and suggest actions to be taken in order for taking your query performance to the next level.  More »


Automating SQL Server Database Deployments: A Worked Example

Alex talks through a simple practical example of a database deployment, First creating a empty database and then upgrading it through three steps by writing T-SQL scripts, adjusting configuration files and the change log, before generating a full build script containing all schema objects. More »


From the SQLServerCentral Blogs - SQL Server Reporiting Services: Migrating SSRS reports, data sources and subscriptions from one server to another

As part of our SQL Server infrastructure consolidation project, it was decided to consolidate multiple companies SQL Server 2005 / SQL... More »


From the SQLServerCentral Blogs - SQL Server – Saving Changes Not Permitted in Management Studio

SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as... More »


Editorial - Driving Value

This editorial was originally published on July 2, 2008. It is being re-published as Steve is at SQL Intersection.

Information Technology Does Drive Value, or does it?

I think this is highly dependent on the IT leadership in your company. Your CIO (or equivalent) can either view his domain as functional or strategic. If it's functional then you're no different than the groups that clean the office, ensure the water is on, the doors open and close, etc. IT then acts as a utility service, which is something it must do. If the department can't provide the reliability and stability that approaches that of a utility, it's a mess.

But if you view IT as a strategic group, an asset to the company, than it must make the business better by more than just reducing costs through automation. At that point IT must really find ways that not only provide more benefits than it costs, but must really drive the business forward.

IT must bring the company a competitive advantage against others.

I do think that Business Intelligence systems, with data warehouses, well designed, focused cubes, and fancy interactive reports, can bring about a strategic advantage. However I think well designed systems are complex, require time, and many companies and staff are unwilling to make the investment.

There are other ways to build systems that create synergies, that are worth more than the sum of their parts (or investments). Database administrators have a great advantage here, seeing many sides of the company, it's technical side, the business side, and the data. Knowing the data that's available (or even missing) is key here.

Those working with data have the chance to find opportunities and build small scale prototypes or systems that can show value. Take advantage of this, talk to business people, find out more about how your company works, and look to make a difference. Who knows, you might find a great new career in designing systems.

At the very least, it would be one great thing to bring up at bonus time.

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

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

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


Question of the Day

Today's Question:

If we run the code below:



--statement 1
CREATE TABLE temp 
(
ID INT NOT NULL,
MyText TEXT NOT NULL
);
GO
--statement 2
CREATE TRIGGER dbo.CheckTextSize
ON dbo.temp
AFTER INSERT
AS
IF (SELECT COUNT(*) FROM Inserted WHERE DATALENGTH (MyText) > 1000000) = 1
 BEGIN
  RAISERROR ('Too large Text Size being inserted', 16, 1);
  ROLLBACK TRAN;
 END
GO

--statement 3 (text size being inserted into text column is < 1000000 characters in length)
INSERT INTO temp
 VALUES (1, 'valid text size')
GO

--statement 4 (Assume text size being inserted into text column is > 1000000 characters in length)
INSERT INTO temp
 VALUES (2, 'invalid text size .......(text size > 1000000 characters)')
GO

--statement 5 (Assume text size being inserted into text column is > 1000000 characters in length)
INSERT INTO temp
 SELECT 3, 'invalid text size .......(text size > 1000000 characters)'
 UNION ALL
 SELECT 4, 'invalid text size .......(text size > 1000000 characters)'
GO

--statement 6
SELECT * FROM dbo.temp
GO

DROP TRIGGER dbo.CheckTextSize;
GO
DROP TABLE temp;
GO

How many rows are returned by the select statement (statement 6) ?

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

This question is worth 1 point in this category: Triggers. 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 Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Yesterday's Question of the Day

What does this script return on SQL Server 2008?

SET NOCOUNT ON;

SET ANSI_WARNINGS OFF;

CREATE TABLE #VARS(ID INT, VchrIsNumeric VARCHAR(2));

INSERT #VARS VALUES(1, '+$NAME');

INSERT #VARS VALUES(2, '$+NAME');

INSERT #VARS VALUES(3, '-$NAME');

INSERT #VARS VALUES(4, '$-NAME');

INSERT #VARS VALUES(5, '+.NAME');

INSERT #VARS VALUES(6, '.+NAME');

INSERT #VARS VALUES(7, '.,NAME');

INSERT #VARS VALUES(8, ',.NAME');

INSERT #VARS VALUES(9, '\,NAME');

INSERT #VARS VALUES(10,',\NAME');

SELECT ISNUMERIC(VchrIsNumeric) AS IsNumeric FROM #VARS ORDER BY ID;

DROP TABLE #VARS;

SET NOCOUNT OFF;

SET ANSI_WARNINGS ON;

Answer: 1,1,1,1,1,0,1,1,1,0

Explanation: From the article suggested by Ron in QotD at 03/06/2013, I recognized that ISNUMERIC may is NOT the best way to determine if a value or a column of values IS ALL DIGITS.

Ref: http://msdn.microsoft.com/en-us/library/ms186272(v=sql.105).aspx
http://www.sqlservercentral.com/articles/IsNumeric/71512/

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Featured Script

Scripting SQL Agents Jobs using Powershell

The script helps to Scripting SQL Agents Jobs using Powershell automatically by scheduling as a job. 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

Query with high lob logical reads / read-ahead reads - Hi I have a table with a column of image data type. Our application fires queries at this table, retrieving the...

Alerting on session count - Hi Does anyone know how I could create an alert on a database when a particular number of connected sessions is...

Reg:Sqlserver Named Instance - Hi 1) How to set a static port in sql server named instance? PLease help me

Linked server provider is not accessable - Hi, I am using SQL SERVER 2005 Standard Edition 64 BIT with Service pack 3. From past few days I...

Process currently not released - We have a peculiar issue in SQL Server 2005. When we try to access the Providers under linked servers in...

Query Multiple Databases - 04/04/2013 I am a newbe database admin and I have been tasked to run queries against multiple databases. Each server has...

quick question about all indexes for my DB - hello guys, i am trying to get a list of all my indexes in my databases, i followed this article...

SQL Server 2005 : Backups

RedGate Backup - I installed Redgate Backup5 on Windows 2003 + SQL Server 2005 with SP4.But I am not able to see sql backup...

SQL Server 2005 : Business Intelligence

Loading Fact Tables - Step by Step Instructions Challenge - Dear All, This is my first Data warehouse project and I am having serious problems loading my fact table. I...

Access to the path in [File System Task] - Hi All, I am facing this given below issue "[i][File System Task] Error: An error occurred with the following error message: "Access...

Microsoft Neural Network Algorithm (SSAS) - I've just been asked about the [i]Microsoft Neural Network Algorithm [/i]in SSAS. I read BOL ([url]http://msdn2.microsoft.com/en-us/library/ms174941.aspx[/url]), but was in over...

SQL Server 2005 : SQL Server 2005 General Discussion

Barcode 128 generator (using scalar function) - I'm looking for a barcode generating Function that uses the standard Code128. Apparently no function that fixes this has been...

SQL Server 2005 : SQL Server 2005 Strategies

Moving prod DBs from local disk to SAN with absolute minimal downtime - [b][u]Background[/u][/b] I have a number of production databases that are currently on the local disk on the server. We have recently...

SQL Server 2005 : SS2K5 Replication

Transaction Log Growing due to Pending Transaction (Replication) - SQL Server 2005 SP4 with Transactional replication. I have this Database Log, which keeps growing due to a Pending transaction in...

SQL Server 2005 : SQL Server 2005 Integration Services

moving data from SSIS to As400 using IBM DB2 UDB OLEDB provider, failing due to special symbol – endash ANSI value 0150 - Hi, I am getting error while moving data from SSIS dataflow task using provider "IBM DB2 UDB for iseries IBMDASQL OLEDB...

SSIS 0xC020801e Connection Manager Issue - I have a job that runs nightly. Intermittently it gets Code: 0xC020801E ---The connection manager "Microsoft OLE DB Provider for...

Ideas on resolving error -- "VS_NEEDSNEWMETADATA". - Hi I am running a large dtsx package and in a data flow task I am getting the following error...

Pushing data to an AS400 - In SQL Server 2000 I can easily push data to our AS400 using an ODBC connection - which then uses the...

SQL Server 2005 : T-SQL (SS2K5)

Need help - Hello, I need syntax to get the all the values of one employee from the employee table, in a single...

Update Query - The table x is ambiguous - Hello, I am attempting to write a query to update the cost of an item, based on the cost of an...

SQL Server 2005 : SQL Server Newbies

Undeleatable record (on linked table) - Hi, I've got a system made up of a SQL 2005 backend and an Access front-end. The front-end only holds the...

SQL Server 7,2000 : Backups

Backup Log cannot be performed because there is no current database backup - I have a job that runs every two hours starting at 7 AM and ending at 5 to back up...

SQL Server 7,2000 : T-SQL

Scalar function result into temp table column - Recently I found that I could not have the return of scalar function as part of an update statement on...

SQL Server 2008 : SQL Server 2008 - General

tempdb - Hi, Our monitoring tool has reported that blocking on prodution i checked the details one user is running adhoc query on standalone database(not...

How to NOT match on an exception list - If I have a table of Products that have a ProductName and I have a table of ProductNames that I...

Prepared vs AdHoc - Plan Cache ObjType - I would think that this would be an easy find with google... However, I have not found anything useful. So,...

Tracking a change to the notifications on an Agent job - Hi everyone, So I've had someone change the notifications, specifically the Page: nofitication on all agent jobs on our production server....

Joining with large table - Good morning. I have been working on a faster solution for some time, and thought I had it. I have...

Linked server - I created a linked server and use it to select data from one server to another. Is there another way except...

WHERE > CASE > IN - Hey, I'm using MONTH and YEAR functions to match on current month and current year. If the Month is 12 (December)...

Error while moving model database - I have moved the model database by using following method 1. For each file to be moved, run the following statement. ALTER...

User interface - I am new to SQL server and currently using Access 2007. I am just wondering could someone attach a screen...

Link Server Connection - Sir, I am working in Medical college campus.They have 5 server in our campus. I have done a Port for...

check condition - Hi all, I have to check a condition that if I entered correct first and last voucherno then only it...

SQL to transpose data using the Formula from a Different table. - Hi Team, I have a Complex requirement. Source: -------- NAME PAYMENT_TYPE PAYMENT SUDHIR SAL 30.3 SUDHIR ADV 10.3 SUDHIR ALL_1 10 SUDHIR ALL_2 10 SUDHIR ALL_3 10 MADHAV SAL 34 MADHAV ALL_1 24 MADHAV BONUS 10 Formula: -------- TGT_PAYMENT_TYPE PAYMENT_FORMULA SAL SAL-ADV COMM ALL_1+ALL_2-ALL_3 BONUS

SQL Server 2008R2 Memory - Good Day. We are running an SQL Server 2008R2 ent 64 Bit on a server with 200 gig of memory . At...

Index fragmentation - HI All, The table is not using the specified fillfactor after rebuild the index. Please find the attached doc, where i have...

Is it really unused index? - Hi, Can you confirm, Is it really unused index? UserSeek - 16951 UserScans - 11803 UserLookup- 0 UserUpdates - 62992 rgds ananda

Pushing Data from T-SQL to Visual Studio While Debugging - I am debugging my T-SQL code in Visual Studio ... is it possible for me to write something in my Transact-SQL...

Help on building a pivot query - Hi, Am having difficulty writing a pivot query as am pretty new to this. In the attached word document I have...

Moving MSDB database - When i am trying to shift msdb database from it's default path(C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA) to another drive(E:\). After shifting...

Convert file from ANSI to UNICODE - Currently, we generate file in ANSI. Is there a setting we need to change to generate files in UNICODE? or...

Char(9) to Time - Hey, I have a column that's char(9) and effectively stores time. This is a sample of the data; 0 02:19 -03:06 00:22 ...

How to Seperate number from a text - Hi, Here is my scenario, i have set a data in my column SBA 60 OFF 19.99 NOW 7.99 SBA 50 OFF 99.99...

*MAY* need to switch DBs from Full Recovery to Simple, due to backup space issues... - First and foremost, I'm working with our backup admin to find a better solution for the problem, that won't require...

Migration detail for SQL SERVER 2000 TO SQL SERVER 2012 - HI i need help for migration the coding in sql server 2000 to sql server 2012 . regarding this what are the...

Corruption - Renaming of system data databases to ldf :( - Hi, This morning a made a proper boo boo while building a new SQL Server 2008 R2 install. After the installation...

Publishing and accessing SQL Server 2008 Reports - I 'm new to SQL Server database and I need help. I 'm trying to publish sql reports for users...

Omnibackup.exe backup got failed while trying through this setup. - In SQL Server,to talk a backup for various databases from various server we are using omnibackup setup to execute SQL...

SQL migration - Whats the best way to copy logins, users, logins with password along with exact status of enable/disable from source 2008...

While executing query getting error - Hi All, When ever i am executing the query getting below error, if i use "Result in Grid" error :- [b]An error...

HOW TO FIND A VALUE IN ALL COLUMN OF ALL TABLE IN A db. - HI ALL, As i Have a task to find 9 digit value in all column of all table in a DB...

Searching stored procedure for table name but ignoring comments - Hi All, Thanks to others who have posted helpful tips I have a way of searching through stored procedures to list...

SQL Server 2008 : T-SQL (SS2K8)

T-SQL Variable vs String Equivalent - Got a question with some coding standards. There are developers declaring a variable to a constant value and using that...

Inserting missing records using multiple fields as the key - Hi, I have a complex problem that I'm pretty sure should have a very simple solution. If I could just find...

Confused selecting Info From Two DB - i have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in...

Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL) - Create procedure temp ( @MID smallint ) as Begin select TranID, [MonthValue]=(CASE WHEN @MID=1 THEN Jan WHEN @MID=2 THEN Feb WHEN @MID=3 THEN Mar WHEN @MID=4 THEN...

Function for similar phrases - Hi All, Is there any built in function available for checking the similarity of the phrases eg: US Eastern District Court...

Query help - [code="other"]I need a query to get the expected output Table:Student stguid stuName studwor stid ------------------------ ----------------- ----------------- ----------- 642-4d5d-9af0-4c7a18dd ChrisName Chris 255 4171-8655-2de255b88e08 ChrisCity SAN City 179 2a0d-4100-bd1c-343882 ChrisCou

pull out all values within an XML column - i'm wondering if something like this is possible in some way: [code="sql"] select OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'), --OriginalSchema.value('Form[1]/Codes[1]/code[sql:variable("y.myNum")]','varchar(max)'), * from ( select id, ROW_NUMBER() over(partition by id...

Checking to see if a time is between two times - Hi all, I'm trying to break down events by hour of day as 00-23. So if an event has a duration of...

SQL Server 2008 : SQL Server Newbies

timeline in cross-tab?? - Hello experts, My problem is as follows. On one hand I have got the beginning and end date of a tournement,...

synonyms for table column - Dear All The way we hide the tbale name from user by using the synonyms. Is there a way to hide...

Two basic MySQL questions - Due to my lack of expertise with MySQL I have two rather basic questions. I use this query to give me...

Temp Table Column name or number of supplied values does not match table definition - Hi all, Starting to play around with PBM and found an example of using DBCC LOGINFO. When i copy the...

How to get difference between two dates in days and hours? - Hi, I'm looking code to find difference between two dates in days and hours. Example: date1: 2013-04-07 14:45:41.013 - date2: 2013-04-05 10:45:41.013 I...

auto foreign key generation - I created a table using the statement below in one of my databases (database1). The table is to be updated...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

Importing data via ODBC (from mozaic) to SQL - Good Day Ladies/Gents I was wondering how I would go about importing data from a data source (mozaic) in the same...

SQL Server 2008 : SQL Server 2008 High Availability

Moving Mirror mdf and ldf files to another drive - I'm currently running SQL Server 2008 R2 Enterprise with 3 servers in an active / passive / witness high availability mirror. As a...

Seeking some guidance on the use of Log Shipping in SQL Server 2008 - I have recently taken on responsability for the database part of our Sharepoint solution. From looking at the documentation there...

Log shipping Monitor server - Hi, What is the threshold limit to configure log shipping in monitor server? We have configured successfully around 1107 databases in one...

Do I have to pay for Microsoft's support? - Yesterday was a long day for me. My SQL2012 Cluster went down and the root cause was the iSCSI initiator...

SQL Server 2008 : SQL Server 2008 Administration

Moving data file - For a db involved in db mirroring, what steps do I take to move its data and log file to...

SQL Server 2008 Link Server Connection Error - Sir, I am working in Medical college campus.They have 5 server in our campus. I have done a Port for SQL 1433...

sql server how to split a mirrored db on to multiple devices - Say I have a large production mirrored 1TB DB that resides on a single MDF device and I would like...

SQL Server silent installation - problem with cleartext passwords - Hi, We are planning to install SQL 2008 on over a large number of servers using the silent installation technique....

Insert into a table when new record - Hello, I would need help from a programming front... I guess this is probably done via trigger but not sure how to...

Growing Log Files - I am intermediate in database admin. Please give me your solution for this issue. Size of my db is growing...

SSIS - Database Transfer Wizard - @flags parameters is not valid - I'm trying to use copy database wizard to make a copy of a SQL Server 2005 database (9.0.3042) on a...

Version Store isolation levels confusion - Hi All I used the below query to assess which databases have produced records in the version store [code="sql"]select distinct db_name(database_id)...

Programming : General

Ocassional rare "Self-Commit" with ADO .NET and SQL Server 2008 - Hi We've got a production environment with SQL Server 2008 R2 using VB .NET code and .NET Framework 3.5., our database...

SQLServerCentral.com : Anything that is NOT about SQL!

Encrypting files with PGP - While this is not related to sql server I imagine that many database administrators have had to deal with this...

Snowboarding Thread - This thread has been long overdue. Post anything related to snowboarding: your number of days, your gear, new purchases, upcoming...

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

SSRS 2012 - deprecated Report Model Designer - What is replacement? - Hello, I hope someone can tell me what is the replacement for Report Model Designer now that it's been deprecated by...

value in middle of indicator - Is it possible to have a Value in middle of indicator color: I have set up an indicator that changes color...

Jasper Smith's SSRS Scripter - Anyone happen to have this tool handy? I used in the past to successfully migrate a 2005 SSRS instance to...

Unmerge Parent-Grouped Data - Before I get a whole lot of hate for this, please know that I do not like this data-set either....

Database Design : Virtualization

Drill through VS slice and dice - Hi all, Is Dill through and slice and dice are same? Are any one please explain what is drill through...