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

Dating Your Employer

When I first entered the job market and long after my approach to interviewing was one sided. I worked on my resume, read a bit about the company, dressed appropriately, and did my best to interview  well. At that point where they asked “do you have any questions” I didn’t have many, confirming that I understood the work schedule, benefits, and salary range. If they called with an offer, hooray! Getting paid trumped all else in most cases. I think the only time in those years I didn’t take an offer was when I was lucky enough to have two offers at the same time. It certainly wasn’t dating – closer to being  adopted.

Today I see an interview as a first date. Both sides should be thinking is this a good fit? That often drives me to ask more direct questions, because I want to find out now if it’s not a good fit – there is no point in glossing over potential differences only to face a painful break up later on. Some employers welcome this, some are taken aback. I can remember a recent client who called me about some work and was surprised to find me interviewing them far more than they interviewed me.

If it’s a good first date for me and they call, then it’s time to think about a second date (interview) and dig in a bit more. The key is to not get euphoric about getting a second interview. It’s good to be in the running, but that second interview is when you can get down to details. Ask for a tour of the office, check the break room, see what your potential desk/cube look like, see if the hardware is new or old, and definitely negotiate on the benefits (remember that you should care about your total benefit stack and value, not just the salary).

The comparison to dating is fun, but useful and I think enlightening. If nothing else it should make you smile as you sit in the reception area waiting on your next interview!

Andy Warren from SQLServerCentral.com

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

ADVERTISEMENT
SQL Bckup Hosted

New! Easy retention policies for offsite backups

Quickly and easily delete old offsite backups with new features in SQL Backup Pro. Get your first 5GB of storage free. Find out more.

SQL Toolbelt

Want to work faster with SQL Server?

If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.

SQL DBA Bundle

Top 5 Hard-earned Lessons of a DBA

‘10 Tips for Efficient Disaster Recovery’ by Steve Jones. Prepare for any future disaster by reading Steve’s tips today.

Featured Contents

 

Extended Properties Introduction

Adam Aspin from SQLServerCentral.com

Part one of a four part series intent on demystifying and making more accessible SQL Server extended properties More »


 

Generating Random Numbers in SQL Server Without Collisions

Additional Articles from MSSQLTips.com

From time to time, I see a requirement to generate random identifiers for things like users or orders. People want to use random numbers so that the "next" identifier is not guessable, or to prevent insight into how many new users or orders are being generated in a given time frame, but they don't want to run into any duplicates. We can trade a bit of disk space and relatively predictable (but not optimal) performance for the guarantee of no collisions, no matter how many random numbers we've already used. More »


 

An Interview with Allen Kinsel, PASS Board Candidate

Steve Jones from SQLServerCentral.com

Allen Kinsel is running for the board of directors of PASS. He took a little time to answer some questions from Steve Jones More »


 

From the SQLServerCentral Blogs - Monday Night Networking at the 2013 PASS Summit

Andy Warren from SQLServerCentral Blogs

For the fourth year Steve Jones and I are hosting an informal networking dinner on Monday night at the PASS... More »


 

From the SQLServerCentral Blogs - List all mapping users of SQL login account

jamesxu98918 from SQLServerCentral Blogs

Find a useful system store procedure to list all mapping user of SQL login account. sp_msloginmappings @Loginname , @Flags @loginname: the login account name, If... More »

Question of the Day

Today's Question (by Steve Jones):

What is the data type used for a column that stores FILESTREAM data?

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 1 point in this category: FILESTREAM.

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 Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Kshitij Satpute):

What will be the output of this query?
DECLARE @str VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE SP_EXECUTESQL @str

Answer: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Explanation:

The correct answer is:  Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Reason: The first and the second parameters of sp_executesql are Unicode - nvarchar/ntext (depend on the MSSQL version). Varchar is not valid!

How to Effectively use: http://sqlknowledgebank.blogspot.in/2012/06/dynamic-sql-with-spexecutesql.html

Documentation: http://technet.microsoft.com/en-us/library/ms188001.aspx

In order to execute this statement correctly first we need to make sure that parameter passed to sp_executesql is a unicode string, i.e. DECLARE @str VARCHAR(8000)

DECLARE @str NVARCHAR(8000)

This will still throw an error, "The size (8000) given to the parameter '@str' exceeds the maximum allowed (4000)". Because unicode datatypes have maximum size of 4000 as each character takes 2 bytes.

By further correcting the query statement as DECLARE @str NVARCHAR(4000) OR DECLARE @str NVARCHAR(MAX) we will be able to execute the statement successfully.

Discussion on same topic: http://www.sqlservercentral.com/Forums/Topic843272-338-1.aspx


» Discuss this question and answer on the forums

Featured Script

Find Top 5 expensive Queries from a Read IO perspective

Bodhisatya Mookherjee from SQLServerCentral.com

The below written query helps us find the most expensive queries from a read IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

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

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

Tempdb data file fills up very often - Tempdb fills up very often. tempdb configuration is name fileid filename filegroup size maxsize growth usage tempdev 1 N:\Data\tempdb.mdf PRIMARY 29428480 KB Unlimited 10% data only templog 2 N:\Data\templog.ldf NULL 512 KB Unlimited 10% log only I want to know why its happening very often...


SQL Server 2014 : Development - SQL Server 2014

Query to show Items in FIFO method. - Dear Experts, my client needs a report which shows inventory data. he executes this report before every sale and checks the Item...


SQL Server 2012 : SQL 2012 - General

AlwaysOn performance on Replica databases - I am looking into AlwaysOn Availabilty Groups as both an HA and load spreading solution. But there is something I...

Using cdc LSN for SSIS multiple table export without database snapshot - Hi, I have a range of tables enabled with CDC. At a set point I want to export all the net...

Performance issues SQL 2012 - Hi, Everyday I truncate a table of roughly 40 millions rows, populate it and re-create the indexes. If I restart the...

SSRS Number - Custom Format Issue - Hi Please find the attached snapshot for SSRS Custom Number format issue. I am using as Number format :- #,##0.00%;(#,##0.00%) Font Format :- IIF(ReportItems!txtBox4.Value...

Query Issue - DECLARE @County NVARCHAR(100) DECLARE @Longitude decimal(19,15) = -87.979529 DECLARE @Latitude decimal(19,15) = 42.219469 DECLARE @SQLQuery AS NVARCHAR(500) DECLARE @LongitudeLatitude geography SET @SQLQuery = N'SET @LongitudeLatitude = geography::Parse(''POINT(' + cast(@Longitude as...

multiple conditions - hi, idk if I'll give to explain very well. but I need to make a query with the following conditions example: I have...

I need an exclusion query again. My totals are wrong. - Hi Can someone put the following perhaps in a query that makes sense. My totals are wrong so I'm thinking...

Set up jobs on SQL Server 2012 Servers in AlwaysOn configuration - Hello everybody, I am a SQL DBA and need some help. We currently have two 2012 SQL Servers in High Availability. I scripted...

temp table vs permanent table performance - Hi There , Im handling cores of data which will refreshed in every run. for this which one I can go with...

sql server 2012 licensing question - Hello all We have a fairly simple SQL server environment today (licensing wise) All of our SQL Server instances are...

Adhoc queries and DB id - Dear All I want to get last executed queries on a DB. Using dm_exec_query_plan , dm_exec_query_stats ,dm_exec_sql_text gives this information. But it...

Build datawarehouse from scratch - Hi friends, I have couple of questions. I have experience working in datawarehouse projects but I am not sure how do...

Printed Books Vs E-Books - ;-)


SQL Server 2012 : SQL Server 2012 - T-SQL

Problem with writing a query. Kindly guide... - Hi All, I have RATE and SHIFT columns in a table as mentioned below. When I pass RATE=1 then I...

FULLTEXT problem - Hi, I've a table with a fulltext index and when I search for an exact phrase I get a result but...

Query now runs forever - Any idea how this might run forever? It is running through 174,000 rows, but that usually takes sub-second. Would this...

how to update column city value from 'A' to 'B' and 'B' to 'A' in single query - hi, I have a table like id city 1 A so i want to update city column from A to B and again...

Can't we rely on execution plan for performance !!! - Hi all, I have a heap table [b]USER_COUNT_MONITOR[/b] with a single column [b]ID[/b] and DISTINCT values from 1 to 100000...

SQL 2012 ERROR - The metadata could not be determined because statement contains dynamic SQL - Hi, This post is related to an error I have discovered through the execution of a SSIS package that has...


SQL Server 2008 : SQL Server 2008 - General

Need advice for best option - I have a 600GB database that I need to copy up to date information to. I must have it in...

Jobs that run during specific duration - Hi, Do you have any handy script where I can get the list of jobs that ran during specified time period...

Save float valur with a stored procedure adds some decimal numbers - Hi! I'm having a strange behaviour with a stored procedure that has some parameters in float format. I used: float x = (float)1.5; float y=...

TDE Restore Problem - Intermittent - anyone else experienced this? - Hi All, We have a very strange problem restoring backups which has only started since TDE was implemented on 19th September...

Fullscan statistic refresh produces badly performing histogram - Hello there, I have found an issue in our environment that I cannot explain. There is a statistic based off of two...

listing of months for a particular year - Dear All, Hope you are doing fine. What am I trying to do is get a stock count for each item...

Getting error message when trying to load XLSM file using SSIS package in SQL Server 2008 environment - Hi All, I use an SSIS package to dynamically load data from specific tabs of xlsm files into a database. The files...

Commenting - How much detail should be used in comments, do you assume a level of understanding? One comment I saw once...

Multi instance SQL 2008 R2 upgrade - I have 3 SQL 2008 R2 SP1 instances on a server. And i want to upgrade only 1 instance to...

merging different clients data onto same box - Hi. Our current infrastructure is set up whereby we have 20 different companies using the service we provide, each having their...

Importing from a improperly formatted excel file - Any guidance would be appreciated. Despite my protestations I have been tasked to import from an excel file. The file...

Bulk Insert - CSV with and without quotes - Hello I need to Bulk Insert a .csv file into an SQL table. The problem is within the .csv file some...

2008 SQL replication - I have a 2008 SQL server i need to replicate for a reporting server on a Nightly basis. I had...

SSIS Use a conditional statement on a file name? - I have a file that comes from a vendor who can't seem to spell. The file has a Prefix of...

Sql Agent - Hi Guys, Can someone please point me in the correct direction, been searching in vain for a number of days. I'm monitoring...

Replication-Distribution Database already exists problem - Hi All, When i complete the replication wizard it will try to run through, but the following error message appears: [i]Cannot create...

SQL With Browser Disabled - I am sure I have had this working before, but how can I connect to SQL Server remotely, using the...

32 BIt ODBC SQL driver - I need to insatll 32 bit SQL odbc driver in Linux environment , can any one pls help on this of...

Shift week to Wed - Tues - Hello. I need to be able to group data based on not only the date, but also the "week of"....

Works in Management Studio but not from C# - I have a simple stored procedure that takes one nvarchar parameter as input. The parm is compared to a column...

Error message when changing location of share drive in SSIS package - Hi all, At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS...

String Split - I want to write query which procures me result like below. General rule is if any word starts from number then...

Select record from group - I was assigned a difficult project. It is beyond by skill. I need expert to help me. From sample data, 1)...

Max Memory - I have set Max Memory to 6 GB. But SQL Server is using only 2.9 GB Query used: SELECT object_name, counter_name, cntr_value...

Shrink the database only upto its initial size which is set - I have some database which are already created which are already created with default initial size. But i want to...

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager - How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager I'm reading some articles and applying...


SQL Server 2008 : T-SQL (SS2K8)

How does Greater than operator works in Joins - Hello, I have writing SQL Queries for sometime now and writing inner joins, but always used the equal to operator(=) for...

how to replace this cursor with set based solution? - I've got a cursor script that I want to replace with a set based solution because the cursor script takes...

Query Needed - Dear All Currently I am getting following result sets Registerno SubjectCode SubjectName Qno Marks 12402223 171906 Quality And Reliability Engineering 1 4 12402223 171906 Quality And Reliability...

inconsistently wrong query results - Hi All - I have a count/grouping problem and for the life of me can't make sense of it. Involved in...

Query - Dear All I have one scnario. One column i am having Following records 1a 2a 2b ... . . . . 10a 10b But i want following output through Query 1 2 2 . . . . 10 10 .


SQL Server 2008 : SQL Server Newbies

Red-Gate SQL DBA Bundle - Hi Guys, Firstly I would like to say thanks to all replies on my previous post. SQLServerCentral is my number 1...

sql server 2008 r2 - please from $ days I am struggling With it hi while installing sql server 2008 r2 at the end its giving error...

Is this Vulnerable for SQL injection?.. - Hi Everyone I hope everyone is having a nice day. This is my Code [code="sql"] set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <Author,,Name> -- Create date: <Create...

sleeping connection limit - One of my SQL Servers has 15,835 connected session with only 4 active. One application account has 15500+ sleeping connections...

How to search? - Hi Everyone ;-) I hope everyone is having a nice day ahead ;-) I have a question guys... I want to search for...

sql server 2008 r2 installation error - hi while installing sql server 2008 r2 at the end its giving error service 'sql browser' start error and installation failing. please...

Create an Alert for ROW Size increase - Hi being a newbie, i have been asked by one of our Apps admin guys to create an alert that...


SQL Server 2008 : Security (SS2K8)

Determine *which* database an account failed to login to? - Is there a way to determine, when an account attempts to open a DB and fails, which DB it was...

SCHEMA rights - Hello all, If a user has no SELECT rights on a schema (it's denied) he can't perform a SELECT statement on...


SQL Server 2008 : SQL Server 2008 High Availability

Basic difference between log shipping and mirroring - Hi I have some basic doubts between mirroring and log shipping .I know there is net but i didn`t proper result.In...

Adding a new instance on a existing cluster - Hi All I need to add a new sql instance on the existing cluster, how do i go about? The current setup...


SQL Server 2008 : SQL Server 2008 Administration

Log Shipping Revertion - Hi, This is Damodar. My question is on Log Shipping, I have to reverse the log shipping from DR to...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

SH Latch - Hi, After a disk failure we had a corruption on a live database, we did have backups and was able to...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Query on identical objects different systems, A performs speedy with index seek, B performs poorly with clustered index scan - Identical Query. A select with one column condition. Selecting from a view. The view is bad, joining eight tables and...


SQL Server 2005 : Administering

Query two DB on same virtual server, Linked server? - Hello all, I'm out of my depth here. Working for a small company that has two small databases on the...

How to change default database for multiple users (logins) at once? - Hello! I am trying write a sql script to change the default database to master for multiple users at once. The...

What roles do I add to a new custom role, which will allow CRUD and execute on SP? - I want to create a new, custom role to my databases, to which I'll add users. This role will allow...


SQL Server 2005 : Backups

litespeed backup consumes high cpu - is litespeed one of the causes of high cpu??


SQL Server 2005 : Business Intelligence

SSRS 2008 R2. Print Reports In sequential order - Hi All, I have 2 reports with following layout [b][u]Sales Report[/u][/b] Agent1 State..........................sales amount-----------------Number of items.......................Average Sales VA................................$25000.....................................10.........................................$250....... CA................................$35000....................

SSRS Number - Custom Format Issue - Hi Please find the attached snapshot for SSRS Custom Number format issue. I am using as Number format :- #,##0.00%;(#,##0.00%) Font Format :- IIF(ReportItems!txtBox4.Value...


SQL Server 2005 : Development

If else issue - Declare @Version int; select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2)) if (@version >=11) begin IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL DROP TABLE #checkresults create table #checkresults ( [Error] [int]...


SQL Server 2005 : SQL Server 2005 Integration Services

Creating tables on the fly with SSIS - I wanted to create a set of tables on the fly using a SQL script can I do this via...


SQL Server 2005 : SQL Server Newbies

Returning Windows Logon Credentials to a SQL Query - Hi All, I am using a trigger to update a table based on data changes - which is working well - but as...


Reporting Services : Reporting Services

FetchXML Parameter Cannot be compared to a Guid - Visual Studio 2008 BIDS In a CRM report, there is a drop-down to select a given account from the CRM...

Iif condition in total by descripion - I want o have my total by descripion based on the ISOrder value . If IsOrder = 1 then "Total " & Fields!ProjectName.Value & " by...

ssrs column group - HI All, i have a report which has a column group , that brings columns from a data set. right now suppose my...

SSRS 2008 Tool Tip Issue - i have a report. which has images embedded in it. (like red for value 1, yellow for 2 and 3...


Reporting Services : Reporting Services 2005 Development

Call SSRS package from Stored Procedure or From SSIS? - Hello, Can anyone help me in calling SSRS package from Stored Procedure or From SSIS? We need to execute the SSRS...


Reporting Services : Reporting Services 2008 Development

Help: Collapsing drilldown that crosses pages generates error. - The report renders fine, but when a drilldown that crosses pages is collapsed it errors. When running from Manager error: Unsupported...

history.back in drillthrough reports - Hi, we are trying to implement the browser back button functionality with a "history.back(-1)" action in our reports to return from...

ssrs 2008 display data as last item on report - In an ssrs 2008 report, I need to place the 'report parameters' and associated values at the end of the...

SSRS error when exporting as PDF: Index was outside the bounds of the array - Hello all... I have an SSRS report that, when you try to save it as a pdf, tiff, or print preview,...


Programming : General

How to improve a aquery - Good morning, I am writing a query thaht bring data from 7 tables. In the first statge the data is stored in...

VB 6.0 applications needs to replicate data - Hi, I have an application in VB 6.0. That application needs to replicate some data to another database (different database). The goal...


Data Warehousing : Integration Services

Question about dynamic OLE DB connections - Ok I have the following issue & scenario I want a ssis package that connects to a single configuration database server,on this...

Need help with small execute SQL task - I have a variable recordsInserted. A row count sets its value. I want to insert this value and the current...

SSIS shows warnings despite fixing them ? - It looks like there is a bug in SSIS (2008R2). I have a data flow that contains items which produced...

Complete manual for SSIS 2008 R2 ? - I know the basics of SSIS 2008 R2. Now, I want a manual which explains EVERY SINGLE option, menu, configuration...

How useful are fuzzy lookup and fuzzy grouping ? - Is it really useful in real world jobs ? Can we do things efficiently without it ?


Data Warehousing : Analysis Services

Missing SSAS project - Hello, I inherited a data warehouse with cube/dimensions and am being asked to make changes. I do not have the SSAS...

MDX Returning Member Properties - The following query returns a list of customers I am interested in: [b]select {[Measures].[Net Value]} on 0, NON EMPTY [Customer].[Customer].[Customer].Members on 1...


SQLServerCentral.com : Anything that is NOT about SQL!

My Favourite Sql Server Book ?? - Sql Server 2008 internals By kalen delaney

When Curiosity strikes - Hi Everyone.. What is the meaning of SSC Rockie, SSC Veteran, SSC Champion.... etc...?? I'm a New ACTIVE member to this group.. hehe Cheers...

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


SQLServerCentral.com : SQLServerCentral.com Website Issues

SQL versions on articles - This is more of a suggestion than issue with the website of which I'm pretty sure many others would benefit....


SQL Server 7,2000 : T-SQL

Use of N with unicode constant parameter - I am trying to get my arms around needed code changes - as per a database conversion from varchar to nvarchar. Using...

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