In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Search How do you search your database schema?
"I use SQL Search regularly and think it's great." Gregor Suttie, Senior Software Engineer, Pulsion Technology. Download Red Gate SQL Search while it's free.
 
SQL Monitor SQL Monitor v3 is even more powerful
Use custom metrics to monitor and alert on data that's most important for your environment, easily imported from our custom metrics site. Find out more.
 
SQL Source Control logo Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

In This Issue

Windows Server 2012 Deduplication – Should you use it with SQL Server Backups?

Windows Server 2012 introduces native deduplication functionality. While this is a promising new Windows feature for other file types and characteristics, there are some potential pitfalls that you need to be aware of when it comes to deduplication specifically for SQL Server backup files.  More »


Mastering the Art of Conversation and Making Business Contacts

In NYC on Feb 21 at 6:30, Don Gabor has a short seminar to help with your networking. Whether you are chatting in small groups or one-on-one at a networking event, IT conference or after-hours party, your ability to master the art of conversation will help you find and connect with business contacts. More »


Interviewing Tips for a Database Position

Interviewing for a database position is a careful game of give and take. Knowing what to expect and how to prepare for your interview is important, but it's only half the battle. You'll also need to ask questions to see if the job, and the company, is a good fit for you.  More »


From the SQLServerCentral Blogs - Granting Permission to Grant Permissions

I’ve never felt the need to allow this, but I saw someone ask the questions recently. Suppose you had a... More »


From the SQLServerCentral Blogs - Invitation for T-SQL Tuesday #39 – Can you shell what the PoSH is Cooking?

T-SQL Tuesday Party History In case you’ve been hiding out in the desert, oblivious to all that goes on, there is... More »


Editorial - Hunting Developers

This editorial was originally published on April 17, 2008. It is being re-published as Steve is on holiday.

Be ver-wy, ver-wy kwiet, I'm hunting de-welopers.

We are deep here in the heart of the evil empire...

Actually I'm up in the Microsoft offices in Redmond, trying to learn a bit more about SQL Server from the people that build the product. I'm rather surprised to see a fairly business-like atmostphere. Everyone has an office (a few people share), doors are often closed, and people are heads down working.

They're productive, but it's boring. Sounds are muted, there's no animation from the employees, it just seems like it could be an accounting firm as much as it could be a software developer. I'm not sure what I was expecting, maybe more people running around with Nerf dart guns, maybe more people excited and jazzed about what they're working on. A few loud "Yes"s or "Aha"s, following by someone outside their cube, reveling passerbys with the amazing code they're just written.

Most companies would probably just as soon have their employees with their heads down, doors closed, headphones on, writing code that they can actually use rather than dodging foam bullets. It's just a little disappointing for someone that was looking forward to a more fun, open atmosphere. I'm sure most people like having a private space, but I was imaging big bullpens where groups of developers would be pounding on keyboards, interacting and occassionally harassing each other while swigging down Mountain Dews and Rockstar energy drinks.

To be fair, the team is hard at work, getting SQL Server 2008 ready to ship this year. I'm sure we've got some news coming soon, perhaps this week at the MVP Summit. If there's anything I can release, I'll get you some notes. Being at the MVP Summit and listening to presentations from a variety of SQL Server teams, you can tell that most of these men and women enjoy what they do and they have a passion to build great software.

They're on an aggresive schedule and while I can't tell you when things are coming because of NDA, they are moving quickly.

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

No Podcasts today - I need a day off and got hung up at the Microsoft MVP Summit with no time to shoot anything.

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


Question of the Day

Today's Question:

I open up the default Thesaurus file for my language on my SQL Server 2008 instance and only add this code after the  first expansion set.

        <expansion>

            <sub>jumped</sub>

            <sub>leaped</sub>

        </expansion>
I save the file, reload it with sp_fulltext_load_thesaurus_file, and execute a query, but my thesaurus file does not appear to be returning any results. What could be wrong?

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

This question is worth 2 points in this category: Administration. 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.

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!


Yesterday's Question of the Day

I run this code:

CREATE VIEW MyView 
AS
 SELECT *
  FROM dbo.MyTable
GO

SELECT * FROM dbo.MyView
I get back the one column in MyTable in my result set. I then execute:
ALTER TABLE MyTable
  ADD x2 INT
GO

UPDATE MyTable
 SET x2 = 1
GO

SELECT * FROM dbo.MyView

However I only receive the one column with the same result set as in the first code block above. What do I need to run to ensure I get all the data back from the view?

Answer: EXEC sp_refreshview MyView

Explanation: Views do not necessarily update with schema changes. If they are not schema bound, then you need to specifically run sp_refreshview.

Ref: SP_REFRESHVIEW - http://msdn.microsoft.com/en-us/library/ms187821%28v=SQL.90%29.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 Master Data Services

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

Get your copy from Amazon today.


Featured Script

Number of Working Days

Number of workings days in a month minus weekends and holidays. 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

Database in Recovery mode from last 7 days. - Hello, Please any one can help me, how to recover databases or change status from in recovery to normal. i had...

Track all DB Growth for the Server - Hi, I would like to store the database growth (Data file size + Index Siie) into a table and send an email? Could...

Restoring DB with Full Text Catalog files - I received a 2005 .bak file from a third party. I create a db (a mdf and ldf file) on...

What is Index key Value in SQL SERVER 2005 - Hi All, I have a question because i confused........ [b]>> What is Index Key Value ?[/b] Can any one explain with proper example. Regards, Sachin.

Change MSSQLSERVER Service password on all SQL Instances - Hi SQL experts! Problem of the week to solve is to change the SQL Server Service account (MSSQLSERVER) password. I use...

Idera SQL DM - Hi, I'm currently using Idera SQL DM to pull back the following metric: - Disk Reads / Sec Is there any reason why this...

Comparing two rows - Dear, I want to compare string type values between two rows within a table. Pls help me to do this.

SPLIT Range in partition table - Whenever I use following function, I get one process blocked by other process. Any idea why is like that. ALTER PARTITION...

MODEL transaction log growing - Hello All, Our support team has recently received 3 calls from our customer stating the transaction log for the MODEL database...

SQL Server 2005 : Business Intelligence

loading data from multiple tables to multiple tables in other database? - hi ssis package loading data from multiple tables to multiple tables in other database idea s thanks pradeep

Unable to Connect Reporting Services (2008R2) in SSMS - Dear All, I am facing some problem in connecting the Reporting Services (2008R2). it is throwing the following error. [size="2"][color=#FF0000] TITLE: Connect to Server ------------------------------ Cannot...

Removing column headers in CSV export file - Hi when I export report to CSV file through sql reporting, header row is included, but there is no header row...

SQL Server 2005 : Data Corruption

Table data Corrupted - While Fetching record from a table we are getting below error for few records. Error:-Msg 823, Level 24, State 2,...

SQL Server 2005 : SQL Server 2005 General Discussion

Error in creating the new data source in Cognos Adaptive Warehouse 10.1.1 - Hi, I am not able to create the data source in IBM Cognos Adaptive Warehouse 10.1.1, The test for the connection...

List of parameters having default values - I need a query / way to get the list of parameters in stored procedures in my database having default values...

SQL Server 2005 : SS2K5 Replication

Merge Replication - Agent jobs maintaining data - I am just learning about replication, as I have been asked to set up a merge replication for one of...

SQL Server 2005 : SQL Server 2005 Integration Services

remote path is not the default path on ftp - i created a package following this http://stackoverflow.com/questions/140850/how-to-avoid-ssis-ftp-task-from-failing-when-there-are-no-files-to-download it's working except that the ftp task will not go to the specified remote...

Rename a file (stripping off a date) or importing a file with a different file name - I am downloading files once a week and put into a network folder. I need to distribute files to different...

SQL Server 2005 : T-SQL (SS2K5)

Select Distinct last records - i have a table with three fields id,bank_id,amount it has alot of data....how i can select last entry for all distinct banks...

daily weekly monthly calculate sum - hi guys, i need urgent sql query,kindly help me i need sql output like given parameter @startdate and @enddate->01/01/2011 to 01/01/2012 output like state...

Could not create an acceptable cursor - Hi, First time poster here, I'm having problems with an update statement at the moment, with an error I've not seen...

SQL Server 2005 : SQL Server Newbies

How to add two column and the to put the result on the next row? - [center]column1--- column2--- result 50 ------ 1500----- 1550 100----- - 1550----- 1650 40----- - 1650----- 1690 30----- - 1690----- 1720 Now I do have the value...

SQL Server 7,2000 : Data Corruption

Export DB - Hi, Can someone please clarify my below doubt I have a database on which 2 tables are corrupted, I dont have good...

SQL Server 2008 : SQL Server 2008 - General

DB Designig for City details with multiple webservices - I am working on travel application, so we have to deal with different web services like GTA, Gallileo, Kuoni etc...

Reindexing - I have a server having 20 DB's..... suddenly itis functioning slow.. i am sure that the Re-indexing needs to be...

error while casting a column name - [quote]Set FormattedValue = CASE When rg.ReferredFieldType='NUM' Then CAST(#PointedMslValues.Value AS NUMERIC(18,cAST(rg.ReferredDecimalPoints) ) more cases . END I am generating a dynamic query which is giving...

PIVOT -all columns will be null..how to solve? - In PIVOT ,i couldn't find out ,,what things went wrong..anyone can solve this? GO /****** Object: Table [dbo].[paymentHistory] Script Date: 02/06/2013 13:56:10...

How to update on condition based? - I want to update data on condition based, if condition met then only update else left it. I was using CASE...

I need a plan to cleans all the tables in a database for every 9 months. - I need a plan to cleans all the tables in a database for every 9 months. what are the best...

Error while querying Linked server - Hi all, I am a newbie to SQL Server. I have SQL Server 2008 R2 installed on a server machine...

SQL Server features and VMWare - At a meeting the other day a developer mentioned how some SQL Server features become either insignificant or obsolete in...

If or case staement in a where? - Hi I have this in a where : AND CASE WHEN @InfoKey = 5 THEN CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030) ELSE CLIENT_IDENTIFIER_TYPE.Code = @Code END I tried...

Log Shipping Simple question re: full backup involvement - I am after a clear picture of log shipping and all the references I can find do not explain 1...

Intermittent Stored Proc long running issue. - Hello I have an interesting (at least to me) case of a stored procedure that is intermittently running long. These are...

Catch Data between symbols in SQL - Hi geniuses, I need to catch some data from a column named AllLevels, that contains, in each line: Analysis»Letters»Numbers»Detail I want to be...

Preprocessing Files SSIS - Hello everyone, I'm trying to process files that have NUL characters w/a black background. Looks like a block. The client wants...

Save and menage SSMS client messages - Hi guys, you know, there are some commands that generate client messages (those messages that are generated and shown inside the...

Could not login after changing domain account. - Hi all, I installed a SQL instance on my computer. Here is info: Instance name: DTDUNG\MSSQLSERVER Login Mode: Window Authentication Domain user: T\dtdung After that,...

Is it possible to use a case statement inside a cursor? - I have to update a group of records but I need to make some comparison before actually making the update....

Staging table population - advice needed - I have a process like this: STEP 1: Data files get loaded into a staging database. STEP 2: Phonetic Keys table get...

What is proper way of indexing on a table which contain large amount of data - Hi experts, I have to optimize a database. I have almost done everything(modified stored procedures, implemented index also). But I...

Performance Issue - understanding query plans - Hi, We have a query with a bad performance issue. (28 hours to run). I have been looking at the estimated...

How to use table valued function as part of select statement in sql server - I want to use table valued function as part of select statement in sql server. Below is example INSERT INTO @Table...

SQL Function takes more than 2 hours to return a table.. - I have a function with cursors which returns a table in SQL Server 2008. On executing the query which calls...

how to find result for this table - PaymentHistoryID paymentID Amount receivedType Reason 1 11 5000.00 Dr ASD 2 12 4000.00 Cr BG 3 11 8000.00 Cr BG 4 11 3000.00 Dr SD 5 11 3000.00 Cr ASD 6 13 50000.00 Cr ASD 7 13 50000.00 Cr ASD 8 13 1000.00 Cr ASD 9 13 500.00 Cr ASD This is my table……….. pls help me at vanapandi@gmail.com Payment ID Amount(dr-cr) Reason 11 5000-3000=2000 ASD 11 0-8000=-8000 BG 12 0

Import Excel using OPENROWSET only works for SA - In our development environment web application we're uploading excel files to the SQL server and importing them using openrowset. This...

Record locking - Question1: How can a user, say User1, be able to lock a row, say Row1, and any dependent rows, say...

linked server using JDBC? - ca I create a linked server in MSSQL 2008 using a JDBC driver (located in a file)? How?

SQL Server 2008 : T-SQL (SS2K8)

PIVOT data, when table design is funny - Hi all! Is there an easy way to pivot data into another table, when the design of the first table is...

DELETE against same table that has INSERTS - How do we avoid blocks or performance down grade against a table that has deletes and inserts against it? Every 2...

t-sql update trigger - In a sql server 2008 r2 database, I am working with triggers for the first time. In an update trigger,...

Only NUMBERS - Hi, [code="sql"] DECLARE @NUMERO VARCHAR(15) SET @NUMERO='00-908/88AB***;' SET @NUMERO=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@NUMERO,'-',''),'/',''),'A',''),'B',''),'*',''),';','') SELECT @NUMERO RESULT - 0090888 [/code] Is there a way to do this in...

Error converting data type varchar to numeric - I get this error when using this function: [code="sql"]CREATE FUNCTION dbo.[PSBPR_FormatData] (@DataValue DECIMAL(15,4), @DataType NCHAR(1)) RETURNS VARCHAR(20) AS BEGIN DECLARE @FormattedData VARCHAR(20) SELECT @FormattedData = CASE WHEN @DataType...

UNION vs OR --> NP-Complete Problem - I have recently read about this in the below link. http://www.sql-server-performance.com/2013/tsql-incorrect-union-operator/ I just heard about "NP-Complete Problem" first time :) [quote] From Joe Celko... This...

Finding patterns in rows (date ordered) - Dear All, Here's the background - I have a table of staffids and the shifts/durations worked - with usually 1 shift worked per...

Issue with crypt_gen_random - This is my scalar function, which returns numbers between @min and @max (both included): [code] create function GetRandom(@min int, @max int) returns int as begin declare...

SQL Calculation - Hi all. I hope you can lend a hand to my sore head! I have to write a query to work...

SQL Server 2008 : Working with Oracle

What is the syntax for querying an oracle linked table in access 2007 - I have an oracle linked table in an access 2007 database that is linked via an odbc connection. I have not...

export/import error - I am trying to load tables from sql server to oracle using import/export wizard in SQL Server. Only 2 tables...

SQL Server 2008 : SQL Server Newbies

Adding new columns to a table using the datepart function SQL - Morning Everyone, I have just started training on SQL and was wondering if you could help with a problem I...

Nested SQL Query - % of Group Total - Hello. I am having trouble getting the correct query for this. What I want to do is: [b]QueryA[/b] Group Type Amount Dog...

While/Loop Help - Good Morning I am new to T-Sql and I have been taske with creating a simple Time table with four columns: Date...

Primary - Hi gurus I would like to know how can I tell wich of the two sql in my cluster is the...

CASE WHEN date condition - Good morning! :-) I'm having a little issue with a CASE statement with a date condition. So basically all I need...

Filegroup Restore - Hi Everyone, I have been trying learn SQL Server by myself from quite some time. While today when I was trying...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers - Hello all, I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional...

SQL Server 2008 : SQL Server 2008 Administration

SPN Settings - I have windows 2003 Server with SQL Server 2005 and Windows 2008 with SQL 2008 R2 Is there any difference in...

Cannot view Report Server Jobs in management studio - Good Day Please assist, trying to view the Report Server jobs in management studio, but its blank, can view the shared...

To Re-index all indexes for a table. - Hi All, I have to do re-index of all the indexes on a particular table, however, when I go to database...

SQL Server analysis report - Hi, I need help building a SQL Server installation, database, IO, ... report. I ran DMV queries and some parts of sp_Blitz from...

Cannot obtain LOCK resource at this time :sick: - Hi All, I got following Problem on my SQL Server : The instance of the SQL Server Database Engine cannot obtain a...

Server configuration - Hi, I've a customer that has a weird infrastructure implemented to store his customers databases. He has 4 SQL Servers, with 1...

Granting sysadmin rights to local admin to a db - I need to give sysadmin rights to a local admin to only one database on the server. There are 100...

Sanity Check - Ola Hallengren Job sheduling - Hi, I am setting up Ola's SQL Server Maintenance solution and I am not entirely sure about the setting up of...

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

Hardware requirements for Central Management Server on SQL Server 2008 R2 - For the life of me I cannot seem to find hardware requirements for CMS on SQL Server 2008 R2. Are...

one issue about transactional replication in SQL Server 2008. - Hi all, There is one problem about transactional replication in SQLServer 2008. After configuring the replication, there will be three jobs 'Distribution...

Creating a new Management Data Warehouse and removing the old one - When I took on the Role of DBA, i inherited some leftovers from the previous DBA. One such leftover was...

SQL Maintenance job - Manual run/Scheduled run - Hi All, We have a weekly full backup job, daily differential backup job and hourly transaction log backup on our servers....

cdosysmail works for a few days then stops - I am having an issue with cdosysmail. I am using this to send me an alert to tell if a...

SQLServerCentral.com : Anything that is NOT about SQL!

Employer asking for Social Media login? - I've never experienced this but found it bizarre. If I was ever asked to provide access to my social media...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

SQLServerCentral.com : Suggestions

Discourse.org - Looking for comments on the Discourse.org framework. It's potentially a direction we could move the forums from SSC in. Not...

Reporting Services : Reporting Services

The permissions granted to user 'server\username' are insufficient for performing this operation. (rsAccessDenied) - Trying to copy our Production Report Services over to a new machine. Really know almost nothing about it. Copied the...

Creating Variables in Reporting Services - Is there a way to create variables in the 2005 version? There are two tabs on the Layout screen called...

How to implement this formula? - Hi All, This the formula they have written in crystal reports.How to implement this one in SSRS2008R2? Formula: Shared StringVar sNewBranch; StringVar sPrintMe := "yy"; if...

SQL report returning slowly - I have a report running on our production Reporting Web server that is just spinning. When I run the procedure...

Extremely slow report rendering in SSRS 2008 R2 in Internet Explorer 8 - With some reports that I have written for SSRS 2008 R2, the performance is problematically slow when the number of...

Data Warehousing : Integration Services

How to update SSIS package on sql server - Hi, I have a ssis package in production. It is been deployed longtime back. Now we have got an requirement to...

Data Warehousing : Analysis Services

Calculated measure based on dimension attribute repeats - I'm new to SSAS so bear with me. I have a calculated measure based on an attribute in one of my...

Report Builder 3.0 - Merging cells vertically in a table - I am trying to create a table with some cells merged vertically. The table is static, and is therefore not...