|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - The Future of DBAs |
|||||||||||||||||
At SQL Saturday #169 in Denver I sat in on a panel that talked about cloud computing. There was a consultant, a customer, and a Microsoft representative that give different perspectives on what their experiences were with Windows Azure. It was an interesting talk and the more I learn about the cloud, the more potential situations where I think it applies. It's not for everyone, but there are a lot of places where it can work, and I would encourage you to learn more about cloud services and cloud computing. One really interesting question was asked. Do you know the skills needed for modern Microsoft data centers? Truck driver. A truck driver can drop off a shipping container with servers pre-configured inside and just plug it in to power and network, often with no cooling needed. A fenced in parking lot, open to the atmosphere, makes a nice, inexpensive, low maintenance data center. That's simplified, but it has interesting implications for networking, hardware, and system administrators, but what does that mean for DBAs? How will our jobs change if these shipping containers run SQL Server services on Azure or AWS? It's a good question, and one that I know worries many people that manage SQL Servers. On one hand our jobs don't change. We still need to manage the data, enforce quality, design tables and manage indexes. We must ensure the data is available, intact, accessible by clients. We import, export, and even recover it at times. These are all the core skills of a DBA. On the other hand, we have new challenges to manage. Our recovery process will change. We will need to learn how to scale out our data and applications. We will need to better set expectations for performance, and perhaps more closely expand and contract the resources we use to match the demand we receive. In order to do that, we'll also have to better understand the financial costing models that vendors provide, along with gathering more knowledge on the actual value our organization receives from more, or fewer, resources. The idea of remote computing, whether as hosted VMs or a platform that provides database services, is going to be a part of many of our careers. We might as well embrace it, understand it, and find a way to fit in. » Join the debate, and respond to today's editorial on the forums The Voice of the DBA PodcastsWe publish three versions of the podcast each day for you to enjoy.
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: 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.
You can also follow Steve Jones on Twitter: » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: /* When I execute this batch on SQL 2008 server, what will be the answer? */ DECLARE @i int = 64; SET @i ^= 2; SELECT @i AS Answer; Think you know the answer? Click here, and find out if you are right. This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers. We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center. |
|||||||||||||||||
Performance Tuning with SQL Server Dynamic Management Views This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon! |
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
In the code below what will be the result set? The first result gives the values of @trancount1 and @trancount2 in a single row. The second result gives the data from #temp
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
select 1 as a,2 as b into #temp
BEGIN TRAN tran2
DECLARE @a int=3,@trancount1 int,@trancount2 int
WHILE(@a>0)
BEGIN
BEGIN TRY
BEGIN TRAN
declare @tempvar table (a int,b int)
insert into #temp
output inserted.a,inserted.b into @tempvar
select 7 as a,8 as b
union all
select 5 as a,6 as b
raiserror('test',16,0)
COMMIT TRAN
END TRY
BEGIN CATCH
set @trancount1=@@trancount
If(@@trancount>0)
ROLLBACK TRAN
END CATCH
SELECT @a=@a-1
END
INSERT INTO #temp
select 100 AS a,200 as b
set @trancount2=@@trancount
if(@@trancount>0)
ROLLBACK TRAN tran2
SELECT @trancount1 AS trancount1,@trancount2 AS trancount2
select COUNT(*) FROM #temp
Answer: result 1: 1,0 result 2: 2 Explanation: Within the while loop when rollback tran is run, it rolls back all the transaction, not just the inner transaction. Ref: ROLLBACK TRANSACTION - http://msdn.microsoft.com/en-us/library/ms181299.aspx |
|||||||||||||||||
SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon! |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Quantization of an RGB color code into a paletteQuantization of an RGB color code into a palette 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 : AdministeringAll Db's infromation of instance - Does any one know a simple query that would give following information about all databases on an insance? 1. Database Name 2.... ORMs and max dop option - Most of our queries are generated by an ORM (object relational mapper). Three weeks ago we put two new sql... linked server to get list of files in (dos) or windows directory structure - Good Morning Which oledb provider is the simplest to create a list of files from the files system(windows) from a linked... MSDB database not responding - Sql server 2000 I am getting below error, when i start backup Backup database cannot be used on database opened in... splitting tempdb to multiple files - max size? - i have noticed tempdb contention on our tempdb, so i want to split the data file to multiple files.We have... SQL Server 2005 : DevelopmentCopy DB Tables from PRod to DEV - Hello, I have situation that my developer wants some tables from Prodction DB server to their desktop m/c. We have already sql... How to copy production DB into New Developmant Database? - Hi, We have Sql server 2008 R2 and i would like to set up my new test server and copy production... SQL Server 2005 : SQL Server 2005 General DiscussionMaintenance Tasks on 2005 MS SQL Database - What are the recommended Maintenance tasks to perform on a 2005 SQL production Database. I am thinking of the following: 1.... SQL Server 2005 : SQL Server 2005 Performance TuningHuge cost issue with Temp DB using 2008r2 sp2 - Hi Guys, I have an issue that is completely baffling I need a guru's guidance / help. so a little bit of... SQL Server 2005 : SQL Server 2005 Integration ServicesIs derived column better for performance of a package ? - I have a scenario where an excel have thousands of records with say 20 columns. In which their is a... Trying to pull from many tables with similar names using one for each loop container - Hi. I have a situation where I have various tables named like, for example, mytable_t1, mytable_t2, ... and so on. I'd like... SQL Server 2005 : T-SQL (SS2K5)WHERE Filter - Hi to all. I haven't posted for a while and it is now time to wear my sql cap again... SQL Server 2008 : SQL Server 2008 - GeneralPivoting Help - Hello Please help me to do this [code="sql"] CREATE TABLE #X1 (vcEmployeeUniqueID VARCHAR(20), vcLastName VARCHAR(20), vcFirstName VARCHAR(20), vcSchoolName VARCHAR(50), iSchoolYearCode INT) INSERT INTO #X1 VALUES ('001210214','Miller','Helen','NORWALK HIGH SCHOOL',2009) INSERT... install SQL Server 2008 Client tools - Question: We are rolling out SQL Server 2008 R2 and I'm wondering if there is a way to install just... SQL Server @@VERSION on 2 node failover cluster. - Is there a server property I can use to find out the version of SQL Server installed on a 2... High percentage of Failed Auto-Params/sec -- 93% - I have procedure that has a high number of parameters (hundreds) that executes nested cursors and other procedures (ugh) on... Yet another question about log files! - So I've run sp_helpDB on a database that's in SIMPLE recovery model. It's telling me that the size of the ldf... .rpt file - I had to reboot my system, so I saved my .sql and .the output to a .rpt file. The .rpt has... Stored Procedure - I have a stored procedure and want to know after I run it is there away to get it to... SET STATISTICS TIME ON - How to save the results from "SET STATISTICS TIME ON" in table format? Ben Brugman Problem with Default value for Parameters - Hi all, Problem with setting default values to a paramater with true or false condition. I have 2 reports and I am... Moving Full Text Catalog - Hi all I am wanting to move a Full Text Catalog in SQL 2008. As this is a logical file in... How to Skip Invalid XML - Dear All, I have a Prod table with a million on rows having an ntext column to store XML. I need... How to flatten results from parent/children tables in view - I have a parent table and 2 children tables that store date ranges. I'm trying to create a view to... Need to transpose some data - and not sure of the best way to do so - pivot table perhaps? - So I have been reading up on pivot tables because I thought that was the direction I needed to go... Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?: - Can anyone show how to concatenate two fields into one in this line of the TRIGGER T-SQL?: INSERTED.(HeatTicketNumber,PO_Number) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>. GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER... Multiple Server Reboot with SQL Server 2008 R2 on it - Hi Experts, Our network team needs to upgrade firmware and other things on the server which has SQL Server 2008 on... Connection Manager Error - Hi all, I've created a very simple SSIS package where I'm trying to convert an excel spreadsheet into a flat file.... Job failure - Just says the step failed - I have a job that has been running at my job from before when I started working here 6 months... Generate schedule table - I'm trying to generate a backup schedule table where I do FULL backups starting tonight and then do another FULL... CURSOR strangeness - I have a cursor behaving strangely. Please consider the following SQL: DECLARE @condition bit SET @condition = 0 DECLARE X CURSOR FOR SELECT a + CASE... Ideal Shrink Size - Dear, I have a database of 400 tables. I set the default size of database file is 20 GB and log... Auto Backup and Restore in SQL Server 2008 R2 - Dear, I have two servers such as A and B. A is my main server and B is my backup server. My purpose... stored procedure problem_urgent!!! - Create stored procedure for users registration. Procedure accepts two parameters: username and password and introduces these two values into table... Trigger on Insert for a unique row - Hi All, i'm looking to try to create a trigger that will just create a txt file in a folder when... NOLOCK Hint Corrupts Results from SELECT - Good day fellow SQL Server Enthusiasts, I'm having an issue with the NOLOCK query hint causing erroneous joins. In a nutshell,... How To Get Timer Like Funcationality in SSMS to Auto execute a Query every N incremenets - Is there a way in SSMS 2008 to get a query to execute every N seconds with a Wait For... Release unused space in the mdf file - We recently upgrade to sql standard to sql Enterprise. We ran a maintenance job which defrags, reindex and cleans the... Continuing career with SQL - Hello, I hav just joint this forum. I have to ask few things. I have worked with SQL 2000 and basic 2005.database design. I've... Activity monitor loads slow - Hi, I have a very general question. Does anyone know why the activity monitor in SSMS 2008 and/or 2008R2 in a... Turn off Predictive Typing - Hi, There does anyone know how to turn of the Predictive Typing feature in SQL Server 2008 and 2008 R2 when... SQL Server 2008 : T-SQL (SS2K8)Pivoting Help - Hello Please help me to do this [code="sql"] CREATE TABLE #X1 (vcEmployeeUniqueID VARCHAR(20), vcLastName VARCHAR(20), vcFirstName VARCHAR(20), vcSchoolName VARCHAR(50), iSchoolYearCode INT) INSERT INTO #X1 VALUES ('001210214','Miller','Helen','NORWALK HIGH SCHOOL',2009) INSERT... Loop through multiple Linked servers in Query - I have a challenge. One of my jobs is to produce reports on data found in 7 different databases (and database... SQL Calculation - Hi all. I hope you can lend a hand to my sore head! I have to write a query to work... Exercises in t-SQL - I'm in the process of setting up a training environment here, it's a mechanism for me to bring high flyers... Run Stored Procedure on remote server - Would like to Run Stored Procedure on remote server and store the results in temp table. I am hoping Not to... rounding - Im performing some calculations and I need to round up the 2 decimal places to the next highest place eg calculation result... CREATE TABLE permission denied in database 'Reporting' - I am calling a stored procedure from an SSRS report. My stored procedure declares a table variable DECLARE @dataset TABLE ... I... Need help with this query - Hi all, I have to make the following, but I've had no luck getting it done. Can you please help? Thanks... Doing something like an Excel FillDown function in SQL looking for suggestions??? - I have a recordset that returns the following data: [code="plain"] ItemNumber DisplaySeqNum 10 1 2 3 4 20 5 6 7 8 30 9 10 11 [/code] It's like an invoice sheet where there's multiple parts to... No record count in PIVOT - Hi, I have the following query to count the number of employees per costcenter using the pivot command. The @Columns parameter declares... SQL Server 2008 : SQL Server Newbieshow to shrink log file? - Hello Master, There was an issue of disk space at one of my live MSSQL Server, I tried to shrink some... Improving intentionally denormalized table - I had a job interview about two weeks ago for a position for prviding support to teams of public health... Unable to Rebuild or Reorganize an Index - I was hoping to rebuild all indexes on one of our SQL Server 2008 databases over the weekend. The database... Can't restore transaction logs - Hi Everyone, I cannot restore the transaction logs after restoring the full backup. It shows the transaction log is too early to... Linq to Sql tutorial codes and SQL Server Express 2008 : - Linq to Sql tutorial codes and SQL Server Express 2008 : I am testing some tutorial sample codes for Linq to... data export - Hi all, What is the best (fastest) way to export data into a flat file if one of the columns is... SQL Server 2008 : SQL Server 2008 AdministrationSize of Log file too big - I have discovered yesterday that the size of the log file on my database has increased to 55 Gb while... SSAS Cube Migration to 2008 from 2000 - I need to restore Cube from Analysis Manager 2000 Cube to SSAS 2008. Is that any method we can perform this... Filestream database Backup Restore - I have set-up FileStream for 1 500 GB database on primary server. 99% Space is occupied by Filstream(blob) data. I am... Set the MAXDOP setting to 8? - We are running a [b]Dynamics AX production on SQL Server 2008 R2[/b], recently one of Microsoft consultants tested our production... uninstall Analysis service in cluster. - Can someone provide instruction to uninstall Analysis service in cluster . What would be the best RAID configuration for this hardware? - We have a brand new DELL MD1200 with 12-spindles - These are 15Krpm, 600GB SAS drives The server this is going to... Delete data in tables/performance issues - Hello, I need an advice, I need to get data daily from one server to another, pretty much I will truncate... SQLServerCentral.com : Anything that is NOT about SQL!Is there any good database magazine out there? - Hi there, can you recommend a database magazine that covers all database environments (MySQL, MSSQL, NoSQL, everything...? We had a good bi-monthly... Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You... 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... Reporting Services : Reporting ServicesWhat script language and version is is used to build expressions? - So I am looking for a good reference of book to help me write expression. I have heard the scripting... How can a report user browse a cube on a report server web site - So If I build a SSAS cube, what is the best way to present it to my end users? Is... Possible to update ReportServer.Subscriptions table with a script? Problems when tried - Hi folks Hope you can help. We have SQL Server 2008R2 but don't have SQL Enterprise so cannot create Data-Driven Subscriptions. We have... Retrieve BLOB contents onto the SSRS Reports - The requirement is to Read the BLOB/CLOB contents from the database and display it onto the reports. The BLOB/CLOB would contain... Reporting Services : Reporting Services 2005 DevelopmentDifference in Layout and display - Problem statement: In my Report, the text boxes are not lining up in Internet Explorer as I have them in... Database Design : Disaster RecoveryData Warehouse Disaster Revcovery Options - I'm in the process of implementing a Data Warehouse. I have a Staging Database and a DatawArehouse Database. I have data feeds... Data Warehousing : Integration ServicesErrorHandler problem - I'm hoping that someone can point me in the right direction with my error handling problem which is now seriojusly... Truncating HTML. - I've an odd problem truncating HTML. It's taken from as NTEXT which I'm then attempting to truncate by replacing the field... exec storedProc get return value - Hi guys, I am very experienced in T-SQL but am still fairly new to SSIS and am trying to run before... Data Warehousing : Analysis ServicesShow details cannot be executed when multiple items are selected in report filter - Hi All, I am building an XL based report. This report connects to an SSAS 2008 cube. When I pivot... Indexing Fact and Dimension Tables - Is there a way to create index on fact and dimension tables in cube? Semi-additive measures with SSAS Standard edition - [b]How do people handle semi-additive measures with SSAS Standard edition? [/b] (other than switching to Enterprise edition). I'm using SSAS... Best book to start off MSBI - I have some idea about Integration services and Reporting services. I couldnt get a chance to work on SSAS practically. I... Adding average makes all metrics blank - All, I have a SiteCharacteristic fact table surrounded by a SiteDimension, CharacteristicDimension and ZipCode (Geography) dimension. There is a single fact,... How to give the customer to access SSAS Cube's? - Am a beginning stage of SSAS , Based on the sample requirements, I have created the Cubes and dimensions, Now the... Microsoft Access : Microsoft Accessdisplace input row in Access 2003 and SQL Server 2000 - Hi, Output in Access 2003: ID | [b]Description[/b] | Quantity | Title | Obj --- ---------------- -------------------------------------------------- 22| [b]6 [/b]| 253000.00 | | 23| [b]7 [/b]| 330000.00 | | 17| [b]1 [/b]| 340000.00... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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 ©2012 Red Gate Software Ltd. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
Stairway to SQL Dialects Level 2: Postgres
Day 5 of The OLAP Sprint
Prevent Truncation of Dynamically Generated Results in SQL Server Management Studio
How to save deadlock graph events as .xdl file in SQL Server ?
