SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Happy Thanksgiving 2013

Happy Thanksgiving to all the US members of the community and I hope those of you in the rest of the world have a quiet, easy day at work.

This is traditionally a day where the US remembers the origins of the country, often with big meals shared with family and quite a few sporting events on television. However it's not all fun and games. Typically this is the day and weekend when I've had to polish my electrical skills as I hang lights on the house and around the yard to get ready for Christmas. I don't go crazy, and there's no computer controls for my display, but I have found myself up on a ladder on quite a few Thanksgiving weekends.

I'm not sure if I'll be doing that this year. My kids are getting older, and we've spent the last 6 or 7 years skiing in the mountains. We tentatively have plans to return Thursday this year and have family over, so it's possible I'll be calculating amperage loads on circuits as we try to decorate for the holiday season.

I've included a blooper reel with my mistakes from the last month or so for you to enjoy. Hopefully I won't make any mistakes this weekend if I'm 10 feet in the air on a ladder.

Note: Most of these are the hands and face bloopers from the last couple months.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( MB) feed

MP4 iPod Video ( 17.0MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL DBA Bundle

Save 45% on our top SQL Server database administration tools.

Together they make up the SQL DBA Bundle, which supports your core tasks and helps your day run smoothly. Download a free trial now.

SQL Developer Bundle

12 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

SQL Backup Pro

Want faster, smaller backups you can rely on?

Use SQL Backup Pro for up to 95% compression, faster file transfer and integrated DBCC CHECKDB. Download a free trial now.

Featured Contents

 

Test-Driven Development of T-SQL Code

Louis Roy from SQLServerCentral.com

Learn how to incorporate Test-Driven Development practices into your database development methodology with TSQLUnit. More »


 

T-SQL Best Practices - Don't Use Scalar Value Functions in Column List or WHERE Clauses

Additional Articles from Database Journal

A classic article by Gerg Larsen explaining how and how not to use a function within your T-SQL statements. Where you place your function within your T-SQL statements determines how your query will be processed and can severely impact the query execution plan and the performance of your query. More »


 

SQL Saturday #264 - Ancona, Italy

Press Release from SQL Saturday

SQL Saturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Dec 13 2013 at Università Politecnica delle Marche - Ancona, Italy. This SQL Saturday will feature 3 tracks focused on BI, DBA, and Development. More »


 

From the SQLServerCentral Blogs - How to reset the SA password in SQL Server

Tim Radney from SQLServerCentral Blogs

Getting locked out of a SQL Server can happen in a number of ways.  You could inherit a server that... More »


 

From the SQLServerCentral Blogs - Power BI for Office 365 video

A demonstration of Power BI for Office 365, showing you how all the various tools and technologies work together: Power... More »

Question of the Day

Today's Question (by Steve Jones):

It's Thanksgiving in the US. Assuming we have a table of colors associated with holidays, what should be returned by this query?

SELECT sales = color + ' ' + datename( dw, DATEADD( d, 1, GETDATE()))
 FROM SalesDays
 WHERE holiday = 'Thanksgiving'

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 7 points in this category: humor.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Mike Hays):

Since SQL Server 2000, what the minimum number of VLFs that a transaction log file can have?

Answer: 2

Explanation:

The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files.

http://technet.microsoft.com/en-us/library/aa933049%28v=sql.80%29.aspx

Sorry about the dated referrence, but this information has not changed.  I have tested this in versions: 2000, 2005, 2008 R2, & 2012. When attempting to created a transaction log file smaller then 512 KB, the following error is displayed:

Msg 5174, Level 16, State 1, Line 8
Each file size must be greater than or equal to 512 KB.


» Discuss this question and answer on the forums

Featured Script

String Search

Sean Smith from SQLServerCentral.com

It's been rare in my career that I've needed to find a string of text within any and all of the many possible tables of a database. For the few occasions when I did, I would just go to Google, find something that worked, run it, and be done.

More recently my need for this type of script has increased, and I began to notice that most of the code out there was either slow (even when doing an extremely small search), didn't return all of the fields I wanted to see in the output, didn't search all of the character data types I was interested in, gave limited control / filter options (if any), and / or needed to be run from within the context of the database that was being searched (AKA: not feasible to make it a stored procedure unless you are only ever going to search within a specific database or deploy the procedure to all existing and future databases).

Hence my inspiration for creating my own stored procedure which allows one to search a multitude of data types in any specified database and includes some optional input parameter options / filters.

NOTE: Depending on your environment and needs (number of tables / columns / search criteria, etc.) these types of queries have the possibility to run long and have a performance impact on the system. Always use common sense when running queries such as these and limit your search via the input parameter options rather than casting as wide a net as possible to see what you will get back as results (especially in a production or shared environment).

The procedure accepts the following input parameters:

  • @v_Search_String: Text string you wish to search for.
  • @v_Database_Name: Database whose table(s) and / or view(s) you wish to search against.
  • @v_Object_Types: Object types to search against (U for tables, V for views, default is both).
  • @v_Data_Types: Data types to search against (default includes CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, and XML).
  • @v_Table_Max_Rows: Limits the search to objects of a maximum row count, avoiding scans against large datasets (default is no limit, though using this option will remove NTEXT, TEXT, XML and views from the search).
  • @v_Column_Max_Length: Limits the search to columns of a maximum defined data length; handy for when you only want to search against short description fields (default is no limit).

The procedure will output the following values:

  • object_type: Indicates if the object containing the search result is a table or view.
  • data_type: Indicates the data type of the column containing the search result.
  • data_length: Indicates the column length, as defined in the database, containing the search result.
  • database_name: Indicates the database name the search was performed against.
  • schema_name: Indicates the schema name the search result was found in.
  • object_name: Indicates the object name the search result was found in.
  • column_name: Indicates the column name the search result was found in.
  • column_data: Full contents of the column containing the search criteria match.
  • column_data_xml: When the contents of the column_data field are XML then this column will return the same data but converted to the XML data type.
  • occurrences: Count of the total number of occurrences of the full content of the column_data field.

To execute the stored procedure you must at least populate the @v_Search_String and @v_Database_Name input parameters:

EXECUTE [dbo].[usp_String_Search]

     @v_Search_String = N'random text'
    ,@v_Database_Name = N'my_database'

You can also use any or all of the optional input parameters to limit the scope of your search and produce results quicker while lessening the load on your server (recommended):

EXECUTE [dbo].[usp_String_Search]

     @v_Search_String = N'random text'
    ,@v_Database_Name = N'my_database'
    ,@v_Object_Types = N'U'
    ,@v_Data_Types = N'CHAR, NCHAR, NVARCHAR, VARCHAR'
    ,@v_Table_Max_Rows = 10000
    ,@v_Column_Max_Length = 50

Any friendly feedback is always welcome. Enjoy!

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 2014 : Administration - SQL Server 2014

TempDB Files gone missing... - I had an issue a few weeks back that we have gotten past, however I still do not know the...

bcp aborts on first FK violation - I want to use BCP to load data from a text file. By default, constraints are turned off in bcp, so...


SQL Server 2012 : SQL 2012 - General

reporting services 2012 Setup files-only mode, separate DB server - Hello, hope someone can help or point me in the right direction. I'm very new with SQL server. Trying to...

Using VMs in a cluster - Hello Could someone provide insight on using 2 VMs in a cluster fro SQL Server 2008 R2 (or higher)? Today, I have...

Switching between Database Instances - Hi there - I have installed an instance of SQL2012 and SQL2008 on my machine. I would like to access the...

SSRS 2012 report exports to PDF with incorrect data - I have a report written in SSRS 2012 that basically shows the wrong data when it's exported to PDF from...

Format string on dimension attributes SSAS - I have a SSAS multidim cube (2012) and I am trying to set format for some dimension attribute (for example...

How can I create Linkedserver - I'm going to create a liked server but I see this error, I think that I checked every thing but...

incorrect syntax in datetime - Hello I write code below and I use getdate() to get date [code="plain"]declare @ID int DECLARE BCursor CURSOR FOR SELECT ID from exhibitor.mainTable...

Changing from SIMPLE to FULL to Shrink a Transaction Log? - Hi, I just had a situation where the log file for a DB in Simple mode had blown out to 300Gb...

MINIMUM & MAXIMUM Memory in SQL 2012 - Dear Gurus, I have 384 GB physical and 24 CPU's. Please suggest what will be the Minimum and Maximum memory of SQL...

business intelligence solutions for insurance companies - Hi, We are planning to implement business intelligence solution for our [u]Insurance company[/u]. From my initial analysis, the below are the [u]TOP...

Converting Oracle data to SQL Server data - Issues - We are running SQL Server 2012 EE on Windows Server 2008 R2 EE. In SQL Server, we have set up...


SQL Server 2012 : SQL Server 2012 - T-SQL

SQL CLR Assembly - We use a SQL assembly to make web calls. We have 1 assembly with a few dozen functions, all use...

Concatenation & Dynamic-SQL (Unexpected Behavior)... - So, I have a query which uses dynamic-SQL. I noticed some unexpected behavior. I will try to simplify it. If,...

Consecutive Row Number - How to create a row number for a consecutive action. Example: I have a listing of people who have either...

Need a 2nd opinion on a query - I had a collegue write the following query. He got defensive when I asked what he was trying to accomplish...

Handling of SP failure within the calling SP - Hello, I'm trying to find the most efficient way of handling failures in a process that looks like this: CREATE PROCEDURE SP_CALC AS BEGIN ......

Download resul set in .csv - Hello. Could you help me? please. I have a task, download resul set in .csv declare @cmd varchar(8000) set @cmd= 'bcp "[Devel].TestDB.dbo.ia_ReportUser" out...


SQL Server 2008 : SQL Server 2008 - General

Its there a Way to find out - Hi everyone... Is there a way to search in the DATABASE to find out where the specific data is .. i...

SSRS 2008 embed an external website/URL into a report - Using SSRS 2008, is there a way to embed an external website on a report. I've tried the textBox method...

shrink file - I have a database, which is around 2gb but the transactional log file is around nearly 2gb. In order to...

Holiday Calendar double check - If you have a Calendar Table that also includes common holidays, can you either post a copy of the data,...

Transaction log growth creating partitioned index on table... db in simple mode. - I'm trying to partition a huge table and running into trouble running out of transaction log space. Can anyone help...

Is there any limitation of the way we perform concatenation in SQL server? - hi, It may sounds lame but can anybody help me in understanding this issue? please help..... This query does not work: SET @tSprocSQL1...

Impact of fragementation on execution plan - While had a conversation over fragmentation and execution plan, we ended up differenent opinion. My thought is fragmentation will not...

Nulls last in order by - I have the following select statement in a stored proc SELECT DISTINCT P.PlantNumber ,TSS.SpecimenTestDateTime ,TSH.SampleReferenceID ,TSS.SpecimenLetter ,TSH.TestSampleID ,TSS.TestSampleSpecimenID and an order by...

regarding backups - Hi All, I have 500 tables in a database. i want to take the backup and restore the 3 tables along...

space release after deleting rows from rable - How to release space to OS after deleting rows from table? We deleted about 25gb worth of data from 2 tables...

Difference Between BAU and Projects - I work in a shop which has a split between a BAU and a Projects DBA. Can anyone tell me...

deleting some data from a column - Hi All i had some virus attach on my database and my online database suddenly got corrupted. with every column data...

ora_rowscn equivalent - Hi, Is there any timestamps or values created by sql server for each rows (in incremental way). like the ORACLE have the...

is it possible to discern who created an object in a db like ferinstance - the windows login or network name of the creator of a view, something along the lines of View->Properties->Current User, but...

Difference between two dates ignoring the year - Hi I have a date field in the database called cutoffdate. The table name is Paydate. The cutoffdate is as shown...

Did you fiddled today? - As a web developer (with a data base developer background) I use a lot [url]http://jsfiddle.net/[/url]. Its a great tool to allow...

NO STATS:() even with AutoCreate Statistics is ON - Hi guys, I did a profile in my SQL Server and got several events named "Missing Column Statistics". However the database...

Linked server/ very restricted access - We have a user who has access to just one view on one database, and he needs to create a...

SA login question. - Good day, I found an issue where I am unable to log on the the local instance with my SA credentials...

Cannot open New SSIS Project in SQL Server 2008 R2 - I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer...


SQL Server 2008 : T-SQL (SS2K8)

Duplicate row data on condition... - Hi, I have a table with following structure: [code="sql"] SalesRow SaleId INT, SaleRowId, TaxCode INT, TaxValue INT, Price INT, ExtraTaxCode INT, ExtraTaxValue INT [/code] On a...

table output - Hello, Is the following possible? Two stored procedures, sp1 and sp2 1-sp1 to output the result of a select query into a table...

Concatenate column values as single value SQL Server 2008 - have a table with a column: |-------------| | ColumnName | |------------- | | Value One | | Value Two | | Value Three | | Value Four | | Value Five | |-------------| I will declare...

how to structure this FOR XML query? - Hi all, I am at a loss on how to structure a for xml query that would look like this. I've...

sp_makewebtask is not available in sql 2008 - Any Alternate code for below query, because sp_makewebtask is not available in sql 2008 Pl help USE pubs GO EXECUTE sp_makewebtask @outputfile = 'C:\WEB\MULTIPLE.HTM', @query...


SQL Server 2008 : SQL Server Newbies

part of database backup - Hi, Is it possible to backup part of database. i have 2 yrs data in my database can i backup last...

Extract string - Hi all, I have string like 'ABCD_kkk_DDD' I want only 'kkk' in output. How to achieve this?

Migrate a SQL 2000 database to a different server - I need to migrate an old SQL 2000 database, setting on a windows 2000 server, to a SQL 2008 setting...


SQL Server 2008 : SQL Server 2008 High Availability

Domain Migration - Log Shipping and Mirroring - Hi, Will it affect domain changes in log shipping and mirroring??? Because currently SQL server services running on xxxx\xxxxx domain account and...


SQL Server 2008 : SQL Server 2008 Administration

Restore Syntax - I'm upgrading a lot of Databases to SQL Server 2008 R2. I can't get the systax right. I'm confused about what to...

What the name of this Job? - I love systems administration and networking staff also SQL Server Administration , I plan to take some certificates with this sequence...

SQL Server Database Administrator next Position or Role - What is the best career path for SQL DBA to update himself and his career ladder, Please choose your answers...

Free Disk Space by Clearing Columns ? - I have a huge archive table in a lightly used archive database. We really don't need all the data in...

transaction log eats up all the space and growing until sql is stopped - Hi , I've got a peculiar problem with my VCENTER's SQL 2008 database. My Database is running on SIMPLE recovery model. The...

SQL Server 2008 R2 , change domain - SQL Server 2008 R2 Ho guys I need some help from you: I have a SQL Server 2008 R2 installation in a...


Cloud Computing : General Cloud Computing Questions

Need Information on Amazon Redshift - Hi I need some information on amazon redshift.msbi(ssis,ssrs,ssas) tolls working environment useing amazon redshit is it possible or not and...


SQL Server 2005 : Administering

TempDB Files gone missing... - I had an issue a few weeks back that we have gotten past, however I still do not know the...

why does server keep reporting db is offline? - We set a database offline because there was a process eating up available resources and, while this is not the...

Exception access violation reading address - My SQL 2005 SP2 on a windows 2003 R2 64 edition Sp2 keeps putting out a fatal exception error 18002...

Logshipping issue with restoring job - Hi All, Since couple of days i am experiencing on issue regarding log shipping restoring. backup and copy jobs are...


SQL Server 2005 : Backups

regarding backups - Hi All, I have 500 tables in a database. i want to take the backup and restore the 3 tables along...


SQL Server 2005 : Business Intelligence

report timeout - The 2008 ssrs report is using a stored procedure which takes 15 mins to run. The report shows "timeout" error and...

Convert Data type - My source file in Excel has a datatype : Double precision float. What datatype should I use for SQL destination to make...

SSIS SQL Serveer 2008 R2 - Good morning. I am trying to accomplish the following: I need to query a table in a database on Server1, and also...

Troubleshooting steps of Deploymnet - Hi Everyone, Could any one suggest me how to proceed if my deployment gets failed? What are the minimum troubleshooting steps? Thanks.

Difference between ODS and Datawarehouse - Hi All, Can any one tell the difference between ODS(Operational Data Store) and Datawarehouse? When do we go for ODS and when...


SQL Server 2005 : SQL Server 2005 General Discussion

Activity Monitor-ing - Can anyone explain why Microsoft consistently make the Activity Monitor in SQL Server (whichever version) so very, very bad? As...

Query Output in a text file - Hi, My requirement is to run a query and the o/p of the query has to go into a text file...


SQL Server 2005 : SQL Server 2005 Integration Services

Flat file connection having column names in Header - Hi, I'm using a Flat file destination. I 'Check' the column names in the first data row in the connection and...


SQL Server 2005 : T-SQL (SS2K5)

Concatenating a varchar(max) column - Hi, I'm having trouble concatenating multiple rows in a table into a single varchar(max) variable. I am trying to concat...

fn_virtualfilestats - Can anyone tell me why the following query will run against a database with a compatibility level of 90 but...


SQL Server 2005 : SQL Server Newbies

Join in two tables - I have two tables: T1 and T2. T1 has 1 row as name and age T2 has 2 rows as [b]name, money1 name, money2[/b] I use left...


Reporting Services : Reporting Services

MSBI projects - Hi i am MSBI developer. Due to some reasons am not working. I am a seasoned BI developer specializes in...

SSRS Study/Career Advice - I am a newbie to SQL and currently learning T-SQL (MS-SQL 2008R2 version). I have been working as a Data...

Problem with display of textbox controls on form - I have laid out a number of text box controls over top of a W2 image. I have noticed that...

Multiple Tablix with One to Many Relationships - Hello! I am hoping someone can help me with this - I am quite certain it can be done, I just...


Reporting Services : Reporting Services 2008 Administration

SSRS Subscription / Schedule Security - Hi All, We have set of SSRS Reports published over Report Manager. multiple users subscribe for these reports. the problem is...

Report Model Security - Hi All, We have a SSRS Report Model published to multiple users across our business (Around 25 users from 5 different...


Reporting Services : Reporting Services 2008 Development

ssrs 2008 r2 export to excel on report server problem - In an SSRS 2008 R2 report, I have the following expression on the 'Hidden' property on the Tablixes where I do...

ssrs 2008 r2 c hange server name location - In a new SSRS 2008 r2 report, I am using the 'action' option to point to a url. When the...

ssrs 2008 r2 Globals!RenderFormat - In an existing SSRS 2008 R2 report, I want to show or hide detail columns based upon all the export...

ssrs 2008 r2 export to pdf problem - In a SSRS 2008 r2 report I am having an export problem. When the report is exported to excel, everything...


Data Warehousing : Integration Services

File exists for multiple specific filenames in folder - Hi. In a SSIS package I need to first check that all files exist that I need before I start processing...

Configuration File question - Hello We store our configuration files on a san drive and we use an indirect configuration so the packages access them...

Executing Multiple "Execute Package Tasks" in a single Execute Package Package - I have a set (curently 10) individual packages that put the same data filtered for different sites. I have created...

How to copy data from multiple queries to same excel destination in ssis - Hi I have a requirement where i have data from multiple queries to be copied to a template in ssis. i...

SqlXMLBulkLoad error - I am trying to load a large xml file using sqlxmlbulkload in SSIS script task. It started loading for a...


Data Warehousing : Analysis Services

Switch between identical cubes - Is there an automated way of switching between two identical cubes. I want to create TwinCube1(production) and Twincube2(Copy of Twincube1)....

Account Dimension - Net Income - I have created a cube for Financial reporting using the AdventureWorks 2008R2 DW as a guide. I've defined my GL...

SSAS Training In London UK - Hi, Can anyone recommend a reputable training provider (ideally classroom / lab based), for Analysis Services 2008 or 2012? There are numerous...


SQLServerCentral.com : Anything that is NOT about SQL!

Jobs and Outsourcing - not to make a political statement, but more out of curiosity. Someone pinged me saying they didn't see a lot...

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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

How to retrieve permanently deleted mail in outlook - How to retrieve permanently deleted mail in outlook. Check out the attachment. Really useful one... From outlook 2007 this option is...


SQL Server 7,2000 : General

SQL developer - Hi, I am looking to deep dive myself in SQL development. Currently I am doing the DBA work in a project,...


SQL Server 7,2000 : T-SQL

comparing two string - Any suggestion on below: I need to compare two strings which have comma separated ISO Country codes. For example : String1 = '356,840'...


Career : Certification

70-461 - Done, but more difficult than expected - Just passed the 70-461 SQL Developer Exam Today. This exam is much harder than I expected. Its quite syntax heavy, and asks...


Career : Resumes and Job Hunters

Looking for a Sr. SQL DBA out of SF - Contact: Amber.Richard@staffmark.com Phone: (925) 969-4433 LinkedIn: [url=http://www.linkedin.com/in/amberrichard/]www.linkedin.com/in/amberrichard/[/url] Searching for a Senior level SQL DBA. This is a F/T or contract-to-hire position. SQL...

DBA Resume for US market - Guys, I created resume but I am not sure will it be competitive on US job market? May be you...

SQL DBA Career Path Advice Please - I'd like some advice on a training path to becoming a SQL DBA. I’m not sure what employers are looking...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com