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

Scripting

One of the directions in which Red Gate Software is to build more tools and utilities that all of you can use for working in cloud environments. We aren't ignoring the desktop tools like SQL Compare and Prompt (v6, yeah!) that you've come to appreciate and depend on, but we are investing in expanding these tools to work with a remote, services style architecture since there are plenty of companies looking at AWSAzure, or other providers for a portion of their applications.

I've been learning a bit more about the way cloud services work and one of the things that becomes important in an IaaS environment is scripting. The environment you get is a base install and if you need more software or processes to run when a new VM starts up, you need to script the various installations or configurations.

There are some great scripting features for SQL Server, like Finebuild for installs and Powershell for most everything else. Scripting is a great way to accomplish tasks, but like most skills, it needs to be used in order to be efficient and practical for most tasks. I've struggled with this, and I'm sure many others feel the same way.

However I am starting to think that Powershell might be the best investment in my future skills. The quicker I can write it and build scripts to accomplish tasks, the more I'll be able to do over time as I build a library of commandlets and scripts. That's if I remember to document them well so I can easily search for what I need.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
SQL Prompt

New! SQL Prompt 6 – now with tab history

Writing, exploring, and editing SQL just became even more effortless with SQL Prompt 6. Download a free trial.

SQL Monitor

Understand Locking, Blocking & Row Versioning

Read Kalen Delaney's eBook to understand SQL Server concurrency, and use SQL Monitor to pinpoint excessive blocking and deadlocking. Download free resources.

SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

Featured Contents

 

Calculating Elapsed Times (SQL Spackle)

Dwain Camps from SQLServerCentral.com

Calculating elapsed time between time values can be an interesting problem that is easily solved even when data anomalies are present. More »


 

A Practical Guide to Microsoft Hyper-V Replica: Part II

Additional Articles from SimpleTalk

A Hyper-V replica installation provides rapid disaster-recovery by asynchronously replicating a VM running at a primary site to a replica site. Know how to prepare for something to go wrong at the Primary Server/Site? In this article Nirmal Sharma presents the Replication options for a Replica Virtual Machine with a focus on Hyper-V Failover. More »


 

Red Gate will be exhibiting at SQL Server Live! Orlando

Press Release from Red-Gate

SQL Server Live! is coming to Orlando November 18-22. SQL Server Live! provides comprehensive education and knowledge share on SQL Server database management, data warehouse/BI model design, performance tuning, troubleshooting and more. More »


 

From the SQLServerCentral Blogs - Can You Compress a Temp Table?

SQLBalls from SQLServerCentral Blogs

 Hello Dear Reader!  We are finishing up the final day of the Performance Tuning Workshop here in Atlanta and I... More »

Question of the Day

Today'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

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: Dynamic SQL.

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 Samith C):

create table #temp_test
 (id int);

insert into #temp_test
 values(1)
insert into #temp_test
 values(2)
insert into #temp_test
 values(3)
insert into #temp_test
 values(5)
go

with ABC_CTE as
 (select * from #temp_test)
delete from ABC_CTE where id = 1;

How many rows in the #temp_test table ?

Answer: 3

Explanation:

The CTE will directly perform the DML operation on the souce table. This removes the one row from the table with the id=1.

Ref: Using CTEs - http://technet.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx


» Discuss this question and answer on the forums

Featured Script

Recursive CTE Example

Neil Bryan from SQLServerCentral.com

The idea of this script is to demonstrate how a recurive common table expression can be used to derive hirearchial data. The example given is an employee table where each record pertains to an employee, which has a link to the manager record in the same table. 
Using a technique such as this is far more efficient than using cursors.
The script sets up a table variable of hirearchial employee data.
To test, set the @BossID to a valid employee ID.

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

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...

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 2012 : SQL 2012 - General

Force T-SQL Job Step to Run Under Specific SQL Account - I have created a SQL user to run certain dbcc commands for us (DBCC CHECKTABLE in this case). This user...

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

How to set user permissions for service - I have two services that I am creating. The first one is continuously adding rows to a single table and...

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

ssis vs jobs - I just started as dba at new co. I'm looking over what the previous guy did. He has some ETL...

Weird slowdown issue, possible table lock - Can anyone help me understand what is happening here? I have a tall table containing a list of IDs and...

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

Doubts with Log Shipping - When logshipping is configured, we know that along with LS Backup/copy and restore jobs, LS ALert jobs are also created,...

Failure Restoring Differential Backup - I have an issue with my SQL 2012 database server and restoring a full backup (norecovery) then applying a differential...

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

Transaction Control in SSIS - Hi Team Require your help on TRANSACTION CONTROL IN SSIS , We have created package with 12 dataflow task in one...

Setting log shipping in AlwaysOn environment - Hello everyone, We have 4 Servers which have SQL SERVER 2012 and "AlwaysOn" have been enabled on all 4 servers: ...


SQL Server 2012 : SQL Server 2012 - T-SQL

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

sales by product category - Return the number of sales by product category where the average recommended price is R10 or more greater than the average...

Simple BCP will not work on clusters - I have a fairly simple need to place a text file on in a directory from a query. BCP seemed...

Deletes takes a long time, but CPU, Disk and RAM untroubled...why ? - Hi all, Running SQL 2012 BI on a spanky new server with 2690 x 2 CPU, fast SSD, ramdisk for tempdb...

Trying to build a report menu from data stored in two tables in SQL Database - I have two separate tables in my database ReportCategories and Reports. I am trying to determine how I can pull...


SQL Server 2008 : SQL Server 2008 - General

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

Point in time restore Not from latest .BAK file - Lets say I need to restore a db to from BACKUP A to TRANSLOG BACKUP Z. I have a script to...

CTE - Is it possible to call procedure in CTE?

Indexes missing after restoring a database to a different server - Hello, I am a bit befuddled by a problem I have encountered. The scenario is, On Server A, database is backed up...

An INSERT EXEC statement cannot be nested - Hi All, i am trying to post sp result to temp table then am getting this error, How can i resolve...

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

Combine CASE for Boolean (is NULL) and Expression - Hi, I just thinking how to make my code more readable, I need to check C1 for is NULL and for...

SQL Server downtimes - Hello team, Sorry if duplicated, is it possible to get ride of how many times an instance has been restarted? I...

TempDB - same files - Friends, I want to know if my datafiles (mdf and ldf) should be the same disk or disks distinct. ex: Example: 1 DISK...

how to move ssis packages from one server to another - server name: A Server name :b ssis project to be moved .. I am try ing getting lot of errors in the package.

SQL cluster installation failed with below error - Hi, SQl cluster 2008 r2 failed with below error. Overall summary: Final result: Failed: see details below Exit code (Decimal): -2068119551 Exit...

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

Urgent Help Guys for this Scenario!!! - Hi, I have a table say emp which has two columns empid and empskills.I need a query to retrieve the...

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


SQL Server 2008 : T-SQL (SS2K8)

Top 50 based on Sum - Hi all based on my query below, how do I select just 50 based on a Sum of duration matching...

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 .

Rapid Growth Of Data and Purging Issues - Hi, I tried a few different things which though work well but needs atleast few minutes of downtime so I am...

Return text from a PDF stored in the database (Adobe iFilter) - We are storing PDF files inside a SQL Server 2008R2 DB. We have installed the Adobe iFilter to create a...

Help required to extra image from varbinary column and create image on file system - SQLServer 2008. Problem: I have a table populated by an external system which stores image data in a varbinary column. I...


SQL Server 2008 : Working with Oracle

Can't connect to a remote Oracle server - Win 2008 server 64 bit Bids 2008 SQL Server 2008 R2 Driver: Oracle 11.02.00.01 So I've done the following installed the 32 bit client...


SQL Server 2008 : SQL Server Newbies

Creating a CASE login within a CASE logic - I have the following code which seems to accumulate the complete file - rather than the upper record set - When OperationPhase...

how to script an conditional update - Hi all clever scripters out there! I am going to make a one time update after having made a new function. I...

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

Breaking up a string of text - Hello again, I have some very dirty data here. I'm working on a Customers table which I plan to clean up...

Display the results of search in a DataGridView - Hi to all especially to those who always answer my questions wholeheartedly :-) I Have another question..and this is my Code..... ============================================================ set...

domain\server$ - Hi, SQL Server log shows several login failures from the login "domain\server$". What does that mean? I read somewhere that...

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)

Read-only access to data AND database design? Can it be done? - I want to give a fellow developer read-only access to a set of databases that collectively form an application. He...


SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008R2 in a Cluster - errors in event log on passive node - I have 2 Instances installed in a 2 Node Cluster. Windows 2008R2 and SQL Server 2008 R2. currently, all Cluster...


SQL Server 2008 : SQL Server 2008 Administration

query to get permissions for a user on all databases - I want to monitor rights change on certain users with windows authentication and sql server authentication. I am planning to...

SQL Server event alert - restore from UNC path. - Hi, I have a SQL Server event alert that fires after a database restore using error ID 18267. This works fine...

Report Services taking up a lot of memory - Hey guys, I've got an issue on my production GP server. ReportingServicesService is taking up 12GB by itself, and growing...

Install - SQL Server 2008 R2 SP1 - Hello guys, I when i try install SQL Server 2008 R2 P1 Build 7602 on Windows Server 2012 R2 Standard, I...

Application stopped during Rebuild Index? - Hi, Version - SQL Server 2008 R2 64 bit. I created Maintenance plan for Re-organize and Rebuild index in single jobs.. Jobs successfully...

Replication Databses are not Showing - Hi, I have some replication databases on some servers and those replicated databases are not showing in my table, i don't...

MOVE DB Online - Hi Is there any way to move DB to different location with out downtime. It means We have to move DB online...

Kerberos Authentication stopped working - Hi guys, i´ve only been a DBA for 2 years and still i feel like a rookie at this, need...

Tool for SQL Inventory - I am doing a SQL inventory. basicly I need to scan a network range,find all SQL instance and record server...

Piecemeal restore takes forever - Hi Everyone I have a 1.1TB database with 700GB of data on a SQL Server 2008 R2 SP1 instance. Most data...

SQL SERVER 2008 R2 Standard Edition Encryption? - any option in sql server 2008 r2 standard edition can encrypt a database, other than changing the application?


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

Timeout in select * from [master].sys.all_objects and SQL Prompt timeout - I have a situation where SQL Prompt times out reading the DB objects when I open SQL Manager. Red-Gate suggested...


SQL Server 2008 : SQL Server 2008 Performance Tuning

cxpacket wait differences for same query on different cluster nodes - Hey guys, We are running a 5 node windows server cluster with multiple SQL 2008 instances. 1 instance in particular, our...


Cloud Computing : SQL Azure - Development

SQL Azure install on linux 32 bit. - Can i install SQL Azure on Linux 32 bit. Thanks!


SQL Server 2005 : Development

A severe error occurred on the current command - When iam Running the Stored procedure there is an error occuring "A severe error occurred on the current command" this is my...


SQL Server 2005 : SQL Server 2005 General Discussion

Need to shut down sql instances with out disturbing other instances... - As we have four instances in one physical server which is all are 2005 instances. In this one instance not...

Hide resluts pane for queries with no resluts - I have a query that I will eventually make into a stored procedure. It is sort of a QA helper...

Unable to send mail using database mail - Hi at one of client side i am not able to send mail i am getting following error The mail could...

Can you please anybody one explain? - How to do database migration in Sql server? i need basic and step by step instruction..... Please help me Thanks in advance

List Files In Directory Using SQL - Hi All, I hope this makes sense, lets say i have folder a and folder b. Within Folder A I have...


SQL Server 2005 : SS2K5 Replication

replication from 2005 to 2012 - Hi everyone, Is it possible to replicate the data from 2005 to 2012? Thanks Daniel


SQL Server 2005 : SQL Server 2005 Integration Services

Printing from SSIS - Can I automatically print text extracts created from SSIS to a network folder ? If yes please elaborate


SQL Server 2005 : SQL Server Newbies

SQl 2005 Could not locate entry in sysdatabases for database 'dbo' - I am trying to create a stored procedure that has 5 steps in it. the procedure is failing at step...

How to query same table with 2 WHERE clause onto 1 row - I have the following two queries but how do I combine them to return the results into one row as...

Need urgent help with query - Hello friends, One table (table1) cardno, datetime1, channel_no another table (table 2) cardno, datetime1, channel_no values in channel_no can be either 1 or 2. I...

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

MDX: SSRS Expression: Count Dimension members based on other Dimension - Hi there, I am pretty new to mdx expressions, and am currently trying to write a calculated member in SSRS...

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

REPORTING SERVICES. - can anybody please giude me how to start reporting services(SSRS) from command prompt,as i googled it but in vain.please give...

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

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


Reporting Services : Reporting Services 2005 Development

I have a matrix with totals and hyperlinks - the hyperlinks are wrong - I have a matrix report that i added customer hyperlinks to jump to a url with specific parameters. It seems...


Reporting Services : Reporting Services 2008 Development

Subreports - Hi, I have a report that has a subreport. The subreport appears to the right of the persons name Person Name ->...


Programming : General

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

SSIS 2012 XML Source: System.ArgumentException: Value does not fall within the expected range. - Dear All, I'm newbie for SSIS. I use XML source to get data to MSSQL DB. I have test to...

Cannot find the file MsDtsSrvr.ini.xml - My SSIS 2008 book says that - The MSDTSServer100 service is configured through an XML file that is located by default...

SSIS Package installation wizard - I am trying to instal a test package by double clicking it. I want to create a folder such that...

Setting All Connections at run time. - In my SSIS Packages, I have my Package Configurations set up to first, use an XML Configuration File to set...

dateadd expression is ssis is not validating - Hi, I am using following expression in SSIS DATEADD( "hour" , TT_DETL_TO_TM_ZN_OFST , TT_DETL_POD_DTT) and here the number field and the date...


Data Warehousing : Strategies and Ideas

No dry book like Inmon/Kimball - Good beginner books for DW ? - Please don't recommend books like Inmon and Kimball. They are dry and mostly abstract. I need a suggestion for a...


SQLServerCentral.com : Anything that is NOT about SQL!

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

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 : Articles Requested

Fixing duplicates - An article that looks at the problem of getting duplicate data, for example, invoices. Imagine I have a software bug...


SQL Server 7,2000 : Working with Oracle

create Linked Server to Oracle DB - Is this TSQL Script correct? - Novice - Installed the Oracle 11g client on the SQL Server 2010 server. Using the Select Directory Server - the dengs.eo.com with the...


SQL Server 7,2000 : Performance Tuning

Weird timing with Audit Logout Event - We are experiencing an issue involving a stored procedure run multiple times on 2005 on two different servers. Some Background:...


SQL Server 7,2000 : T-SQL

Limiting rows in CTE with pagination? SQL 2008R2 - I have been asked to look at tuning a improving an SP query that uses an CTE and rownumber on...

Case Statement to return multiple columns - Hi all am trying to write a case statement an that currently returns one column. Is there any way that it...

TempDB best practices - Doubt - Hello guys, I have two doubt: -- 1 script: SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count],...

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