SQLServerCentral - www.sqlservercentral.com

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

The Voice of the DBA

Independence Day 2013

It's the Fourth of July, Independence Day, in the United States. This is a holiday commemorating the anniversary of the adoption of the Declaration of Independence signing, which was the beginning of the United States of America. It's a day when I think about the Declaration of Independence, and the ideals that were put forth in that document. I plan to re-read it with my morning cup of coffee.

It's a holiday for me, a day off where my family spends the time together, and partakes in some of the joys of summer, usually outdoor cooking, bike riding, or something else outdoors. We've watched Independence Day a few of the years, but this year we will likely spend some time in the mountains, enjoying some music and fresh air with the kids.

I hope it's a day off if you're in the US, or a slow, calm day elsewhere in the world. If you are looking for a little entertainment, I've got a short bloopers video for you.

Have a great day.

Steve Jones from SQLServerCentral.com

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

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

Get alerts within 15 seconds of SQL Server issues

SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.

SQL Prompt

Make working with SQL a breeze

SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

Featured Contents

 

Why SQL Server 2012 needs Windows Server 2012

Jim Johnston from SQLServerCentral.com

While AlwaysOn and Columnar lookup are good enough reasons to migrate to SQL Server 2012, you need to be aware of the problems associated with certain virtualize environments. More »


 

Don Knuth and the Art of Computer Programming: The Interview

Additional Articles from SimpleTalk

Fifty years after starting the 'Art of Computer Programming', Don Knuth is still working hard at the project. It is considered amongst the "hundred or so books that shaped a century of science”. Richard Morris asks him how things are going, and to find out more about his many achievements. More »

Question of the Day

Today's Question (by Steve Jones):

For the US Independence Day holiday, a fun one for you. Which three items in SSMS are colored red, white, and blue?

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 QOD, simply log in to the Contribution Center.

ADVERTISEMENT

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Kathiravan P):

What happens when this code is executed?

CREATE TABLE #MainTable
(
    ID INT,
    Val FLOAT
)

INSERT INTO #MainTable SELECT 1, 1.23
INSERT INTO #MainTable SELECT 2, 2.45
INSERT INTO #MainTable SELECT 3, 3
INSERT INTO #MainTable SELECT 4, 4.3

-- Statement 1
SELECT * FROM #MainTable WHERE Val LIKE '%'
GO

-- Statement 2
SELECT * FROM #MainTable WHERE Val = '%'
GO

Answer: Statement 1: Returns all rows. Statement 2: Error converting data type varchar to float.

Explanation: The SQL Server Database Engine converts the float values to character string for the LIKE since that operand requires strings. The first statement follows the rules of data type precedence and therefore the sting is converted to a float. Therefore the second statement gives an error.

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


» Discuss this question and answer on the forums

ADVERTISEMENT

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.

Featured Script

Huge varbinary or image to hex string

Florian Reischl from SQLServerCentral.com

udf_varbintohexstr_big

This function extends for undocumented Microsoft SQL Server system function fn_varbintohexsubstring to get a hex string for a specified VARBINARY. The Microsoft function is currently restricted for VARBINARY (8000). This function is a wrapper for this function but is able to handle VARBINARY (MAX).

Usage

SELECT dbo.udf_varbintohexstr_big(my_image_column)
   FROM my_table

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 SLOW - Hie, can any one help me what are the steps to b take when database is running slow?

TEMP DB ISSUE - hello All , we have issue in our environment where temp db grows very large in 6-8 hrs, the settings are...

2 way sync in SQL server 2005 - Dear All, I have a transactional replication from server A to B. But now for identity column of table at server...

xp_readerrorlog - Hi, I'm trying to search a logfile with xp_readerrorlog for certain text but i would like to exclude databases starting...

Log shipping has got disable automatically - hi , i have implemented log shipping on my live server successfully but it has disabled automatically after 10 to 15...

How to change the owner of the mirroring - my old friend (dba) create a mirroring and he is gone now, I want to change his Id to SA,...

Xp_cmdshell - Hi, i am trying to create a xp_cmdshell to execute the DBCC checkDB comand and same the output to a...

SQL VSS writer issue - Hi all, I'm having quite an unusual issue regarding the SQL vss writer. Whats basically happening is that it does not...

SQL Server 2005 : Backups

tail log backup - when do we take tail-log backup? and how can we take? wt cmds to be used

Automate restore script - Hi, I'm using Ola's backup script (http://ola.hallengren.com/) to backup the database. The problem I'm facing the Restore process. It took 4 hours to...

SQL Server 2005 : Business Intelligence

Teradata date issues with SSRS parameters - Hey Guys I am connecting teradata data source through SSRS. Query works fine but when I try to add date parameters...

Need help regarding SSRS , SSIS and SSAS concept - I am working on SSRS reports.We have a OLTP database in which we have created stored procedure for each report.These...

Tables Refresh Solution - I have 2 database tables. 1) From Production 2) From Dev. The task is to move the data refresh from Production to Dev...

Dts is not declared Error in Script Task Editor - Hi All, When I was trying to write some script in Script Task Editor it is showing "Dts is not...

SQL Server 2005 : SQL Server 2005 General Discussion

Can you please help me?? - I have a customer table select * from customer and i need some specific recid member details so i can write...

Server log error - Hi, My company user complain to me about the web application getting error every day on certain time. So I check...

SQL Server 2005 : SQL Server 2005 Security

How to Grant User access to View Linked Server Properties - hi, i need to give a User access to just VIEW the properties of a Linked Server. I've tried giving...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Poor performance from single database - I have a 2005 SQL server running about 20 databases identical in design. Each database is accessed by one or...

SQL Server 2005 : SQL Server 2005 Integration Services

Join several records from diferent tables of a staging area in one table on the DW - Hi, I´m new using SSIS so I need help - thanks! I´ve too tables A and B, and I need to create a...

SSIS: Custom Logging Using Event Handlers - hi im trying to create an event lof for when thinks fail in my ssis package im using the following code...

SQL Server 2005 : SQL Server Newbies

Linked tables in Acess from Sql server (call failed) error!!! - i have an access db which has appx 25 linked tables which read from a SQL server db, i have...

Grouping by day on a rollover count - Hi, I have a table containing a count that rollsover at [u]around[/u] 32000 to 0 possibly many times a day and...

SQL Server 7,2000 : Administration

SQL Agent Starting........... - I notice that my jobs aren't running now, when I look at Enterprise Manager -> Process Info under the column...

SQL Server 7,2000 : Backups

SQL 2000 - i have drope few tables from my SQL 2000 database by mistake. please help me to recover. these table have my...

SQL Server 2008 : SQL Server 2008 - General

Multiple Primary key in table? - Hi, I have doubts multiple primay key on single table, Table can be support multiple primary key in this case one Clustred...

Managing Registered Servers - Hi, I connect to SQL servers which are all hosted on a virtual environment. This virtual environment is not our on local...

Online Indexing Being Blocked - Hi All, Running SQL Server 2008 R2 Enterprise Edition. The environment runs 24/7 Had a normal indexing job...but this was obviously being...

Datafile percentages - Does anyone have any handy T-SQL for datafile percentages? What I am looking for is the percentage of the datafiles size...

Logshipping Alerts - Hi all, I am getting this message in error log frequently. Actually there is no database with name test in our...

Restoring failed - Hi all, i am trying to restore a database to point in time in sql server 2012. so i want to...

Using snapshots for reporting on historical data? - I've been using timestamps and status dates on tables to do historical reporting (e.g. today last year, quarter over quarter,...

help with sp_MsForEachDb - Hi everyone. I have created a table using the following code in sql2008: [code="sql"]USE Dba_Admin GO CREATE TABLE dbo.databaseDataFileSize ( rowId INT IDENTITY(1,1), dbName...

Connection encryption between SQL Server and BackupExec - We are in the mists of securing all internal communications via encryption, a security requirement by law for us... We are...

Calculating date in time. - How I create a function to get all policies that expire a 120 days from now. Eg If the policy effectivedate...

Problem in executing bunch of Insert statements - Hello friends, I am executing bunch of Insert satements as follows: [b]use Testing go INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM...

CPU Flat lines and numerous Time Outs - Sorry for the long post... but here goes: We have recently migrated from a physical 2 node cluster environment to VM...

SQL 2008 express installation using command prompt - Hi I am trying to install sql server 2008 express using command prompt from my .net application This is the command [code="sql"] Arguments='/q /hideconsole...

summary - alter procedure [dbo].[pocc] ( @empid nvarchar(10), @department varchar(10) ) as begin create TABLE #TempEmployees ( date datetime, eid int, remarks varchar(50) ) -- Insert result from the SP to temp table INSERT INTO #TempEmployees EXEC...

SQL Server Replication - Good Day. We configured a pull Replication with the Distributor residing on the Publisher. I am able to see transactions...

Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help! - I have a SELECT statement in a cursor in a stored procedure that ran extremely fast in SQL Server 2000....

SQL SERVER 2008 64 bit installation on Windows 2008 - Error code 32 - Any advices or idea how to fix it. I am in the middle of installation of it and It is...

SSIS Solution File Issue: Generate a new .database file and ask to overwrite? - Hello All, I have a trouble with my SSIS project solution file. I would appreciate it if someone could help me...

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. - Windows 2003 R2 Enterprise SP2 x64 SQL Server 2008 SP2 At first, I thought it was an AD issue. Server guys checked...

Limit in date picker control in SSRS - I want to set the date limit in a parameter to the results of a SQL query/SSRS dataset. I mean...

SQL Server 2008 : T-SQL (SS2K8)

Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011 - 5.You are only given the transaction date in your transactions table and your client requires you to be able to...

Problem using FOR XML in SQL Query - Hi to all, I have one format problem that i cannot get it right. I hope you can help me. So...

Function to replace string 'NULL' with null value - Hi I am selecting values to one table and populating them in another table, and I'm using a case statement to...

sql quantity sold per day - I have tried this but my head of it department still telling me tht i have failed this is my...

sql sales - Write a SQL statement that will return all the Sales Orders for the Salespersons’ with the name starting with ‘John’....

financial year sql statement please help - You are only given the transaction date in your transactions table and your client requires you to be able to...

SQL statement that will perform a daily sum aggregation my last chance - a SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that...

Information displaying incorrectly..... - Hi, I have 2 tables: [code="sql"]CREATE TABLE [dbo].[GV_InwardAtStore]( [InwardAtStoreID] [int] IDENTITY(1,1) NOT NULL, [StoreCode] [int] NULL, [STNNo] [varchar](20) NULL, [GRNNo] [varchar](10) NULL, [VoucherBookletNo] [varchar](10) NULL, [ReceivedDate] [datetime] NULL, [StoreManagerID]...

CHARINDEX problem - Hello comunity I have a table name field FT.DESCAR Varchar(60) with the following content: 5695 - 0 | 7050-127 MONTEMOR O NOVO with this...

Urgent help with Date comparison in TSQL - Dear friends, I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013. I'm...

daily sum aggregation please help - Write an SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs...

substring comparison for last 2 characters - I have a field with data like below I need to join on patid 123453 124344ME 323390 3233MS I need to remove the last 2 charcters...

compare SQL syntax but need the total and %??? - Hello SQL GuRu's, A few weeks ago I asked the following (http://www.sqlservercentral.com/Forums/Topic1459631-150-1.aspx?Update=1). For this I had received a clear answer and...

Max of 2 dates - I search for Date functions for finding the max of the 2 dates. But such function does not exits. Is there...

SQL Server 2008 : Working with Oracle

is it possible to have Single instance(name or SID) on multiple database? - Hi Folks, I wanted to know that can we have a single instance on two different databases? I am till now aware...

SQL Server 2008 : SQL Server Newbies

Configuration Manager; nothing under SQL Server Services - Honestly, I give up. I can't connect to this database as sa ('a network-related or instance-specific error occurred error 40...

Performance Tuning on Very Small Databases - When is it worth it - When does rebuilding an index make sense? When does Shrink Database make sense on a Very Small Database? Some say...

Connect SQL Server 2012 with Database Engine - Hi all, Installed SSMS 2012 Express addition with localDB. I don't know how to connect with Database engine and start using...

Access form front end to database - Hi, I have created a database with a table called "cable" Fields are ID - identity cableID - nchar(8) not null other fields are present but irrelevant...

SQL to split row by date (split into multiple rows) - I am looking for help with splitting a row into multiple rows based on dates overlapping. As an example, I have...

a query like factorial - Hi. I ask you for help because its more than 2 days im thinking about this question. Imagine we have a table...

SQL Server 2008 : Security (SS2K8)

Linked server error - Hello, I'm trying to setup a Linked server between SS 2005 and SS2012 but keep getting "Login failed for user 'NT...

SQL Server 2008 : SQL Server 2008 High Availability

Log Shipping or Transactional Replication - Hi, We have an ecommerce site for which primary infrastructure (DB and web servers) is based in Chicago. We have a...

Windows active directory upgrade impact on Windows SQL cluster? - Hi, we are about to have an AD upgrade from our current Windows 2000 SP4 domain to Windows 2008 R2...

Table lock on MSmerge_contents - We are running merge replication over a WAN. Only changes to the Subscriber are replicated to the Publisher ( -ExchangeType 1 ). We...

Advanced Clustering Question: how can I monitor Clustered MSDTC Transaction Statistics - We have a robust 3-node cluster that includes Clustered MSDTC. You can see Transaction Statistics by using the Component Services...

DB Mirroring and Initialize from LSN doesn't work - Hi everyone first post here so please go easy, always used this site for it's great resources but now require...

SQL Server 2008 : SQL Server 2008 Administration

Creating a DBA Administration Database - Good Morning / Afternoon / Evening (depending on where you are) I'm going to be setting up a DBA Admin database to replace...

Urgent Issue Reg Checkdb issue please respond - Hi guys , I am trying to run checkdb in my prod its failing from last 2 months because of tempdb...

Overhead on a NC index if the key fields are not updated during production. - My next question while I am in the INDEX zone. As an example: I have a large table (4 - 5 million rows)...

Finding unused fields - We have a db with 388 tables/5,950 fields. I was just asked if I could find any fields (in the...

Linked server issue - We've had an issue for several days now that I'm hoping you can weigh in on. We're testing a linked...

Monitoring Your Database with SQL Profiler - Is there any way to identify if someone is running "Monitoring Your Database with SQL Profiler" sql trace against production...

Probably a very dumb file size question... - So I was adjusting the size of a DB for a customer (increasing the size of his QA DB) and...

SQL server restart - I see the SQL server restarted last night. I don't know who did that or it is an automatic restart. As...

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

litespeed restore - we have db backup file(litespeed backup file). Litespeed is not installed in the server. we need a command to restore the litespeed...

Programming : XML

LOADING COMPLEX XML FILE - The file below came from a web request I want to load it into SQL Server but it is too...

SQLServerCentral.com : Anything that is NOT about SQL!

PolyServe replacement options - While it seems HP still has not made the official announcement, some of us have had conversations with HP representatives...

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

Replicating a MS Access report in SSRS - I am trying to replicate a report a user uses in MS Access. In MS Access a user can enter a...

Session Execution Time On Cached/Snapshotted Reports - I am currently using the following in the footer of all reports. ="Execution Time: " + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", ( IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours >...

Reporting Services : Reporting Services 2005 Administration

New Group Permisssion - I have 50 users (in Active Directory) which are in a group and I would like to give them view...

Data Warehousing : Integration Services

For Each loop not enumerating recordset correctly - Guys, I am importing data from a flatfile source which has a header record, some data rows and a trailer record...

Data Warehousing : Strategies and Ideas

Create Fact table or Dimension table? - Hi all, We are trying to expand existing datamart for patient visit monitoring, and I would like to get some...

Data Warehousing : Analysis Services

1 Dimension 1 Fact table connected by 2 Keys - Hi, I hope all is well. I'm having a dilemma in my cube setup and i hope you guys can help. I...

Microsoft Access : Microsoft Access

Access Query ported to SQL - Here is the "inner" part of a query in Access. UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess' WHERE ((([Vehicle...

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