|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - DR Prep |
|||||||||||||||||
A few weeks ago we published a number of editorials and articles on disasters and the challenges this brings to data professionals. As we start the new year, I'd like to get you thinking about how you might change your DR planning or processes for the new year. With that in mind, a simple question this week: How will you approach disaster recovery or business continuity planning differently in 2013?I'm wondering if you will change some of your processes, or schedule additional testing. You should have at least one test planned each year, but will you make it more comprehensive? Will you schedule more frequent, but less intensive tests? Another thing to consider is the skill and experience of your staff. It might pay to. Incorporate some of the recovery skills into your weekly or monthly work to ensure that you know how to perform backups, restore in different scenarios, or even relocate your databases to another Windows host. Let us know this week how you would like to improve your preparations in 2013 and then spend the rest of the year adding th retails to your plan. » Join the debate, and respond to today's editorial on the forums The Voice of the DBA PodcastsNo podcasts today due to the busy holiday schedule, but they will return soon. » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: List all the differences between a Primary Key and a Unique Key? (Choose 4) 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. |
|||||||||||||||||
Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today. |
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
If you run the following code:
declare @NewYearsEve datetime;
declare @NewYearsDay datetime;
set @NewYearsEve = {ts '2012-12-31 23:59:59.997'};
set @NewYearsDay = {d '2013-01-01'};
select @NewYearsDay as NewYearsDay,
@NewYearsEve as NewYearsEve,
dateadd(year, datediff(year,@NewYearsEve, @NewYearsDay),
dateadd(month,datediff(month,@NewYearsEve, @NewYearsDay),
dateadd(day,datediff(day,@NewYearsEve, @NewYearsDay),
dateadd(hour,datediff(hour,@NewYearsEve, @NewYearsDay),
dateadd(minute,datediff(minute,@NewYearsEve, @NewYearsDay),
dateadd(second,datediff(second,@NewYearsEve, @NewYearsDay),
@NewYearsEve
)
)
)
)
)
)as ToTheNewYear;
What is returned as ToTheNewYear? Answer: 2014-02-02 01:01:00.997 Explanation: The key in getting this right lies in understanding that DATEDIFF and DATEADD are not opposites. DATEDIFF does not tell how much time has passed between two dates; it tells how many interval boundaries exist between the two dates. So although only 0 years, 0 months, 0 days, 0 hours, 0 seconds, and 3 milliseconds have passed between the two dates, DATEDIFF counts 1 year boundary (2012 to 2013), 1 month boundary (Dec to Jan), 1 day boundary, 1 hour boundary, 1 minute boundary, and 1 second boundary. DATEADD does not advance to the next boundary, but instead adds a full interval. So this code adds 1 year, 1 month, 1 day, 1 hour, 1 minute, and 1 second to 2012-12-31 23:59:59:997 to get 2014-02-02 01:01:00.9997 PS. In case you haven't seen them before, the literals in the set statements are ODBC escape clauses. References DATEADD: http://msdn.microsoft.com/en-us/library/ms186819.aspx DATEDIFF: http://msdn.microsoft.com/en-US/library/ms189794.aspx |
|||||||||||||||||
Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today. |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Search All Stored Procedures in a DatabaseSearch for a text in all the stored Procedures in a Database 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 : AdministeringSQL Server SP4 - I have updated my SQL 2005 server with SP4 .Is there any cumulative update or hotfixes need to be applied... SQL 2005 Cluster Breakout - Hi, can anyone please let me know the best procedure to breakout of a SQL 2005 Cluster Setup to an... Rename Database and Other Querys are Running on the same DB - Hi All, I have List of Jobs scheduled on my server, I have scenario like this [b]Job1:[/b] 1. Copy bkp file 2.... Stange memory and page file behaviour - Hi, I wonder if anyone can shed some light on a problem I experienced the other day. My company operates a... Linked server for Oracle - Hi, I am having this weird problem with Oracle linked server on SQL 2005 x64. I have installed Oracle client and... SQL Server 2005 : BackupsBackup on network fails - Hi, I am trying to configure SQL server 2005 backup to a another server drive \\server2\folder. The folder on second server... Transaction Log - I have a database in SQL server 2005 (64 bit) server. The recovery mode is set to simple recovery, and... SQL Server 2005 : Business IntelligenceForeach Loop Container, Traverse through subfolders - I am stuck here and in need of some help. I have a package which archives .mdb files(add to zip, using... HOW run ssis package on table update - Hi i have created ssis package to move data from one table to other in different instances when i update... SSRS - Calculate $ cost on monthly basis - I am new to SSRS and stuck at a point and can not move further and so need your help I... SSIS - E-mailing from causes error - All, A week ago some outside consultants upended my world. They switch the e-mail server from "tom" to "katherine". We... Extend Timeout on SSRS/BIDS Tooltip - Hi, Fairly new to this so bear with me please. I have a report built in SSRS / BIDS. I have the... SQL Server 2005 : DevelopmentExcel Connection Manager error in SSIS 2012 - Please assist with error below: Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to... SQL Server 2005 : SQL Server 2005 General DiscussionSQL 2005 3 node cluster lift and shift with IP change - Hi SQL Experts, We have a activity coming up in our project where sql server 2005 three node cluster will be... Dynamic Where Clause for Multiple paramaters with AND Operator - Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find... Job Activity Monitor view - but where are the In Progress jobs? - Hello all, I wrote a view that joins the sysjob, sysjobactivity, sysjobhistory and syscategories tables together to give me a resultset... SQL Server 2005 : SQL Server 2005 Performance TuningSQL Server stopped after changing startup parameters for mirroring. - Dear All, I was trying to implement mirroring in one of my TEST server. After the configuration I got an error... SQL Server 2005 : SQL Server 2005 Integration ServicesProblem with sql statement in vb script using variables. - Hi all, I have been trying to resolve this for hours but failed, hence have to post here. I have this... Returning Values from a Stored Procedure (SQL TASK, SSIS) - Hi all, Here the problem: I have the following stored procedure: [font="Courier New"][size="2"] ALTER procedure [admin].[up_SetLogicalDate] @QueryDate datetime ,@LogicalDate datetime OUTPUT ,@LogicalDate_Start datetime OUTPUT ,@LogicalDate_end datetime OUTPUT --with execute as caller as begin DECLARE ... The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. - Hi, I am trying to transfer the data from flat file to sql server.When I am running the package on local... SQL Server 2005 : SQL Server NewbiesStripping out all non-numerical characters - SELECT accountid,New_Column = REPLACE(telephone1,SUBSTRING(telephone1,PATINDEX('[^0-9]',telephone1),1),'') INTO #YourNewResults FROM #TelephoneTable I have the script above which I thought would remove all non-numerical characters from the field... SQL Server 7,2000 : Data CorruptionLogical consistency error - Hi, Often am getting logical consistency error in one my production server. Recently i did DBCC with allow dataloss option, to... SQL Server 7,2000 : GeneralCPU Usage goes down to 0 % suddenly - Hi I am running a web application having 500-600 users at a particular time on sql 2000 and ASP.NET 2005. generally... Import data into multiples tables or one? - I'm building an import package for a CSV file that has about 200 columns. If every column was maxed out... SQL Server 2008 : SQL Server 2008 - Generalcannot connect to sql server named instance - Hi Experts, I created a named instance, I am a DBA , i can connect to named instance using SERVERNAME\INSTANCE name, but... Suppress automatic Hyperlinks in outlook in HTML format - Happy New Year! Here is my problem that I am trying to resolve. I am sending emails using msdb.dbo.sp_send_dbmail. I have... Question in regarding to UpdateAdapter with BatchSize - My VB.net code first retrieves data from database, use the fill method to fill to a datatable, makes changes to... Adding a Sequence Number to the records from LKUP No match output - Hi Friends, I am migrating data from one table to another and I am doing a lookup to find if the... Cluster design comment wanted. - Hello Forummembers, For all of you a Happy Newyear. I have lost a bit my nightrest over the following: First we do not... SSIS Package fails in Production - I have created a SSIS data flow package in our development environment that works via debug or sql agent. but... SSIS count of files processed - I have a package that archives old access database files and then send a mail when it completes. I would... Getting a synchronized snapshot of several tables - Hi Guys I'm using SQL Server 2008 R2 (Standard Edition) and trying to get a nightly snapshot of several tables for... Concerns with Transaction replication parameters - Hi SQL Experts, We have an OLTP that was configured in P2P replication. Both the peer nodes were running with... Simple RDBMS scripts for Login Mockup - I am looking for a quick model to do a mock up from for a login process. I went to... I need help with my query. I found part of this, but doesn't work. Please help. - I need to get the emails from the n.Note field, which is a nVarchar(max) column. [b] I have the following, but I... How to copy and paste from a RDP session to your Local Desktop - Hi All, Is there any way to copy and paste from vpn session to our host session? Please advise. Regards, SueTons. Which free 3rd party tools do you guys use? - Hopefully RG doesn't mind this post, but I'm just curious what freely available add-ons to SSMS or BIDS do you... Memory management in sqlserver. - Team, I am looking for the TSQL to get the below details. 1)Total memory allocated in the windows machine where a... Query Help - Hello Everyone, I need one query help [code="sql"] create table #temp1 ( StudentLocalID varchar(9), EnrollSchoolCode Varchar(3) ) create table #temp2 ( StudentLocalID varchar(9), ScheduleSchoolCode varchar(3) ) insert into #temp1 values ('003682153','305') insert into #temp1... convert -ve int to +ve bigint - Hi, How do you convert the -ve int value to +ve bigint value. I have used cast and convert which didn't help. Example. declare... Export wizard failed? - Hi, pl. suggestion me, what could be on issues during export to another new database by export wizard? how to resolve... Record locking - Question1: How can a user, say User1, be able to lock a row, say Row1, and any dependent rows, say... Upgrading to 2012? - Is it worth upgrading from SQL Server 2008 R2 Developer to 2012 Developer? I believe most companies are still using SQL... Database restore issue - Hi, I am facing an error while restoring adventure works database to my sql server 2008. TITLE: Microsoft SQL Server Management... Keeping all three environment(Dev,Stag,Prod) in sync - In our office we run jobs to keep these environment in sync. We get data from outside source and run... Len did not display right size - I use len function to find out varchar column data size but did not display right size. For example, in some... Running a sql server 2008 job to back up to network folder - I'm trying to schedule a back up database task to backup some specific databases to a network folder but kept... SQL Server 2008 : T-SQL (SS2K8)Dynamic SQL - which would you use? - When I need to execute code depending upon the values of parameters I often implement one of 2 methods: In this... Null vs '' - Working through an ETL project at the moment and started to question some of my previous design thoughts. I have... Phone numbers in a Help Desk application - Hello all, I have an access database using sql server as the backend. It has been very simple in design... Quert regarding getting the results based on months. - use adventureworks go select YEAR(OrderDate) as orderyear, SUM(case MONTH(orderdate) when 1 then TotalDue end) as January, SUM(case MONTH(orderdate) when 2 then TotalDue end) as... Using xp_readmail to read mail from outer environment - HI Is there any way to read the (Inbox) mails from exchange servers(outside the sql server)? my first requirement is... Turning raw data into a grid layout - Hi all, not sure how best to explain what I'm trying to do so will try to break it down as... TSQL and percentile (not percentilerank :) - Hi all, I would like to ask for some help and maybe the best practices on calculating the 90th percentile using... SELECT * INTO Table without propagating IDENTITY attribute? - Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination... SQL Server 2008 : Working with OracleMSSQL Linked server to Netsuite cloud - Hi all, I'm having an issue to create a linked server connection to netsuite on the cloud. They supplied me with an... SQL Server 2008 : SQL Server Newbiesorphaned Users - hi can you please any one tell me what is exactly orphaned users and when will use this one and... Date Range or last 6 months of data - Is it possible to run a query that uses a date range, but if the records do not have any... Count Records in Table and.... - Hi All I am just after a bit of clarification. I am writing a procedure which inserts data from one table... Transaction Log file Deletion or movement? - Hello All, I have a Query Please help me out about this: Scenerio is, I have a database server which have a... Dev Licensing questions!? - hi all, trying to get my head around licensing. specifically in a dev environment. Could i get some feedback on... SQL Server 2012 Developer - I have a book that uses SQL Server 2008 and requires at least a developer edition to run SSIS. My... SQL Server 2008 : SQL Server 2008 High AvailabilityHow to Create Maintenance Plans on SQL Server Failover Cluster - We have configured a 2 node SQL Server Failover cluster on SQL Server 2008 R2 recently. I am curious to... SQL Server 2008 : SQL Server 2008 AdministrationDifference between Data Fragmentation & Index Fragmentation - Hi all, I am a newbie in SQL Server. I was exploring fragmentation topic. i came across two term which were... SQL Server Startup Parameter - What is the dif between -m and -f in sql server startup parameter ? SQL SERVER AGENT->JOBS ->JOB ACTIVITY MONITOR - Dear Expert, i can't find some jobs in SQL SERVER AGENT->JOBS ->JOB ACTIVITY MONITOR. but job exists in the system. and... Slow Transaction Log Deletes? - I have a sever that has around 230 databases ranging from 1 gig up to 300+ gigs. Running SQL 2008... Script to know who is logged o to the server - Hi All, Our servers are open to quite a few people. Unfortunately we cannot restrict the number of users on... Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch - Hi, Just run SQLIO on my new Cluster and got very interesting results that I would like to share and/or get... Faster way to release the Unused Space back to Disk - Hi, I have few databases that have 8 data files allocated with 300 GB each and used only 110 - 120... SQLServerCentral.com : Anything that is NOT about SQL!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... PolyServe replacement options - While it seems HP still has not made the official announcement, some of us have had conversations with HP representatives... 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 ServicesReport Builder record limitations if any? - Hi, I am new to reporting services but familiar with the basics. I have a stored procedure that queries tables and... Workaround - LookupFunction in 2008 - Hi geniuses! I have to edit some reports in SSRS 2008 and figure that I'm going to need to use data... Database Design : HardwareStorage Subsystems and RAMDisks - We're setting up a new database server for our OLTP and smaller website databases, and disk performance is a big... Data Warehousing : Integration ServicesLog type of the SSIS object that fire an eventhandler - Hello, i want to log some things with SSIS 208R2 Eventhandlers. I log: Begin, End,Username,PackageVersion,RowsOk,RowsError,... Now i think about using the OnInformation Eventhandler... SSIS Environment Reference Id is null - Hello I have created an SSIS package in visual studio 2010 and deployed it at SQL server 2012 by using Integration... Include Excel workbook password in OLEDB ACE 12.0 connection string - Is it possible to denote in an OLEDB ACE 12.0 connection string that an Excel workbook is password-protected and also... about cdc ? - Hi any one can explain me how cdc in sql server work ? thanks pradeep How to get the data in destination database immediately when source table updates? - We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination... How to get the data in destination database immediately when source table updates - We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination... Need to export excel sheet with SPECIAL formats - Hi, I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it... Run 2012 SSIS package on SQL Server 2005 - Hello all, We work alot with mysql and will only be doing more, although sql server will remain prevalent. We... Data Warehousing : Analysis ServicesDimTime table help - I have a time table that got created through SSAS but it has only data upto 2012. I need to... How to add Server Admin from different Location - Hi All, My PC is PEOECOM098. I wanted to add a fellow-developer (PEOECOM571\Owner) in the list of Server Administrators via the Analysis... Notification Services : AdministrationDatabase Mail Notification - Hello , Can you suggest me how to Receive a mail notification, not as soon as job completes but at a... Products and Books : Discussions about BooksAudio Books - Does anyone know of any good Audio books for SQL Server Admins? I love being able to listen to material during... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
The SSIS Data Pump - Level 2 of the Stairway to Integration Services
Row Sorting in SQL
T-SQL Tuesday #38 – Standing Firm
Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot?
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions