|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - What Do You Want from SQL in the City? |
|||||||||||||||||
This week finished the SQL in the City 2012 tour, which covered six cities in the US this fall and London this past summer. It was a fun, hard, whirlwind event that had myself, Grant Fritchey, and a lot of other Red Gate employees working hard to bring you a fun, educational, and worthwhile set of events. We met a lot of great people, and hats off to Annabel Bradford at Red Gate for organizing the entire tour. I think we did a good job, and heard some great comments from people at the events. Right now we're starting to think about 2013, and I wanted to get some open-ended feedback here from people on what they'd like to see. Whether you attended a SQL in the City or not, give us your opinion. What do you want from a one day training event? I'm curious what you hope to get out of an event, what formats do you like, what types of content, and at what levels? Does the size, location, or any part of the venue matter? Do you need tables for a laptop or are you coming to listen and get ideas that you'll work on in the future? Give us your feedback on what you think makes a worthwhile day of training for you. There are any number of ways we can do things in the future, all with various tradeoffs. We can do smaller events in more cities, larger events with more staff, or something in between. I can't promise anything specific, but your opinion and feedback do help us to try and deliver the community service and education that will help you get better at using SQL Server. » 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. You can also follow Steve Jones on Twitter: 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. They have a great version of Message in a Bottle if you want to check it out. I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it. » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question:
Declare @a varchar(100)
select @a = null
select @a = isnull(coalesce(@a,coalesce(NULL,NULL)),'Pass')+'Fail'
select @a
What is returned? Think you know the answer? Click here, and find out if you are right. This question is worth 1 point in this category: Coalesce. 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. |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
The following code shows four methods of selecting the number (to the nearest 100) of nanoseconds since midnight. Which, if any, will work reliably (except on days when daylight saving time begins or ends)?
--Method 1:
-- use datediff with NS as the datepart parameter
select DATEDIFF(NS,
dateadd(D,datediff(D,0,sysdatetime()),0),SYSDATETIME())
--Method 2:
--use the internal structure of datetime2
select cast(cast(reverse(substring(cast(sysdatetime() as binary(9)),2,5))
as binary(5)) as bigint)*100
--Method 3:
-- go via a character string conversion
-- to ISO 8601 with nanoseconds format
select cast (substring(convert(
nchar(29),sysdatetime(),129),21,9) as bigint)
--Method 4:
-- do it piecemeal with several datediffs
with now(t) as (
select SYSDATETIME() as t
),dy (d) as (
select dateadd(D,datediff(D,0,now.t),0) as d from now
),
secs (ss) as (select datediff(S,dy.d,now.t) from now cross join dy),
secstart(start) as (select DATEADD(S,secs.ss,dy.d)from secs cross join dy)
select 1000000000 * CAST(secs.ss as bigint)+CAST(datediff(NS,secstart.start,t) as bigint)
from secs cross join dy cross join secstart cross join now
Answer: method 2 and method 4 only Explanation: Method one will work only for a small part of each day, because the number of nanoseconds in a day is much greater than can be represented as an int, so datediff (which returns an int) will fail during most of the day. Method 3 will not work because the named format doesn't exist and format style 129 is invalid. Method 4 works, and its components are fully documented in BoL (search BoL for dateadd, datediff, and sysdatetime). Method 2 works and is not documented in BoL, but it's pretty trivial to deduce from the Bol page for datetime2 plus a little experimentation what the structure is and why the binary conversion is a byte longer than the storage requirement, but MS are not going to change the internal represenation of any commonly used datatype (like datetime2(7)) because the impact on customers would be horrendous (rebuild all fields of that type in every table that has them in every database), so it's reliable probably at least as much as documented features (as used in method 3) and quite possibly more so! Method 2 has the advantage of being a lot less difficult to understand than method 4, provided you know the internal structure (which isn't actually documented, although it's a trivial excercise to discover it). But I trust that no-one will ever be stuck with making this rather silly calculation, so that the code for method 2 is just an example of something you can hope never to need. |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Top 14 KPIGet the 14 most used Key Performance Indicators 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 : AdministeringWindows Cluster Servers Migration - [font="Verdant"]Dear Friends, I need valuable suggestion to do the Servers Migration and SAN relocation in the below scenario: SQL Server... memory - To enable AWE and configure Lock Pages do we have to restart server or SQL service. any views. which one is right? - i have transaction isolation level set to read-uncommited but i am not sure which one really works though syntax wise... trigger - Hi guys, I have created this trigger: [quote]create trigger TRG_CONTRIBUINTES_VERIFCA_RF_E_NIF on contribuintes for update as if (contribuintes.NUMEROPOSTO) ='CENTRAL' BEGIN if contribuintes.cod_rep_fiscal not in... Clearing out msdb.dbo.sysmail_mailitems... - OK, so we (I) finally noticed that the MSDB backup was rather large. In looking into the problem I found... Index Rebuilds Still Leave Fragmentation - Hello All, I am running SQL Server 2005 SP2 Standard Edition on a few servers. I run SQL Agent Jobs every... SQL Server 2005 : Business IntelligenceError handling - Hi all. We are handling the errors at the package level and inside the data flow task we have many lookups... SQL Server 2005 : DevelopmentHow to write Update query for this scenario - RowId EName Project Time part Hours Worked [14 Jan] [15 Jan] [16 Jan] 1 Emp 1 Project 1 14/1/2010 7.0 2 Emp 2 Project 2 15/1/2010 8.0 3 Emp 1 Project 1 16/1/2010 8.0 From the above table: The above table is showing like Emp 1... SQL Server 2005 : SQL Server 2005 General Discussionmanagement studio is unable to see local instances - Hi All i have a problem on my server the management studio is unable to see local instances when trying to choose... SQL Server 2005 : SQL Server 2005 Performance Tuninghow to find the utilization is high while we are running performance monitor - HI 1.Can any one say how to find [or how we can know ]the utilization is high while we are... SQL Server 2005 : SQL Server 2005 Integration Servicesexcel connection error - hi, i am getting error when connection to excel connection manager TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Data Flow Task... load zipped file to the table - I have files which are saved in the web folder.and i get the file in the web folder every day... data exists - hi ia m getting data from 2 excel file ,that i need to merge and then load into destination table. but before... Dynamic:How to insert tablename (SSIS) - Hi Guys, I want to perform a task. I want to insert the table name of the destination tables to the another... Dynamic Excel File Source - What are the steps necessary to pass the Excel Source the excel file name as a variable? I've tried this... SQL Server 2005 : T-SQL (SS2K5)How to multiply rows in one table based on the value on another table - Data: [code] declare @t1 table (Id int) insert into @t1 (Id) select 1 union select 2 union select 3 declare @t2 table (Id int, X... SQL Server 7,2000 : AdministrationThe description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer. - SQL Server is up and running and did not find any errors on the SQL Error log, But receiving the... SQL Server 7,2000 : T-SQLGroup by with MAX then other columns - how to control what is returned if there are multiple results - Hi all, I know this is a 'normal' issue that I see littered about the forums alot, trying to get unique... SQL Server 2008 : SQL Server 2008 - Generalrebuild index in share point - is it possible to rebuild indexes in share point server? SSRS 2008 Parameter Passing Issue - Hi, We're using SSRS within an application where users login and are authenticated based on their Store. When they generate reports,... Error 15335 - is already in use as a object name... - I am getting this error: [b]Error 15335 - is already in use as a object name...[/b] when trying to rename a view in... s there any difference in using cursor and table? - is there any difference in using cursor and table ? any performance issues ? sample are shown below example 1: declare rs_cursor CURSOR for... How to schedule SSRS subscription via Sql server job agent - hi, i have schedule some job by sql server job agent ant to generate report after completion of specific step. can any... problem in attaching database (sql server 2008)? - I tried to attach database to sql server 2008 . i got error "Database 'DATABASE_NAME' cannot be upgraded because it is... Reg:Storedd Processing Moving One server to another server - In my company we have two different environments.one for development another one for testing environment.Testing Environment developers not having access... Query to import multiple image file to SQL server 2008 Database - I have a database with different tables. one of these tables has an image column more than 50, 000 records. I... To table or not to table spool (Just wondering which one is better) - Ok, this is not something I'm stuck on just wondering, currently I'm the last day of a dataconversion project. An hour... Problems Shrinking Log File - Hello, I know this question has probably been asked a thousand times, so I apologise in advance, but I appear to... Trace Flag 845 OS Dependent? - http://www.sqlservercentral.com/articles/trace+flags/70131/ This Trace flag is specifically for 64bit 2005/2008 non Enterprise and Developer edition ... Is this OS dependent? meaning 2000, 2003,... merge statement performance problem - Hello Hello platform: MSSQL 2008R2 I'm facing a performance problem using the merge statement, described below: begin try set transaction isolation level read uncommitted ... Get week no for the cuurent date between two dates - I have two dates , thats the week start date and end date . And i needs to know what is the... speed up deleting million of records from a table with one filter - hi all, I want to delete million of records from a table with only one filter of varchar(1) column for that... How to get the system date automatically in the columns - Hi experts, I have a table called branch with columns branch_code branch_name branch_desc modified_by modified_date Here i have data for the 1st three columns and the rest... Disabling Distributor & Publishing Error - Dear, I am trying to disable my distributor and publisher in replication but I am getting the following error message. [ [i]TITLE: Microsoft.SqlServer.ConnectionInfo ------------------------------ SQL... Null Values shows while importing from excel to Sql - Hi, I have Excel File with following coloumn and sample data. example, No TranRefNo 1 1111 2 2222 3 3333 4 4444 5 5555 6 6666 7 7777 8 8888 9... Call a URL and pass a parameter in the URL (http post) - Hello , Can somebody please assist me, i need to call this URL http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall=XXXX where XXXX is a number which we... Is It Necessary to Update Statistics? - My 2008 R2 database has Auto Create Statistics and Auto Update Statistics both set to TRUE. Is it necessary to also... package for zip files. - I have files which are saved in the web folder.and i get the file in the web folder every day... SSIS 2008 For Loop Container to loop until execute process task in container is Successful - Greetings All, I have an SSIS package that generates a text file, then uploads the text file to a SFTP site... 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,... Error: 18456, Severity: 14, State: 11 - I have one user that is receiving this error on 5 instances out of the 8 they have access to.... Out of memory exception - I have a stored procedure that is normally encrypted. I run this with a specific set of parameters(that I am... Include column VS Index Size - Hi, I an wondered why Include column does not change the size of non-clustered index. As I know non-clustered index is combination... Multiple Instances vs. Multiple DBs on one instance - If you have two beefy 64-bit servers in 2-node cluster, and several applications that need a database server, is it... How to locate sub-packages dynamically? - Hi there, I have a master package calling sub-packages. All my packages are deployed in one folder on the file system. How can... differential backup question - Hi Guys, For example only. I have a daily full db backup via SQL maintenance plan to file system at 10pm. I also... Merge with duplicate rows - Hello all, I am running merge stored procedures against various staging and final tables in my database. I have one... windows server 2008R2 SQL SERVER-2008 64 bit - Hi, I have windows 2008 R2 64 bit OS and SQL Server 2008 64 bit. I need to create the Linked server... Updating 566787 Records takes forever - Hi, I am writing a script to update bad data in a column of a table. The Table has 566787 rows... SSIS in cluster setup - Since SSIS is not cluster-aware, what is the best approach to configure SSIS in SQL 2008 Cluster. When the active goes... Best practices for sending a database to your client (schema & data) - We currently use SQL 2008's "Generate Scripts" feature to package our data into a series of scripts. We then have... Data compression - Guys.. I have read that 2008 will compress data in the databases and also backup when compared to 2005. Can... SQL Server 2008 : T-SQL (SS2K8)Reg: Date Requirements - Hi In my requirement For example, From date : 01-01-2012 To date : 30-12-2012 Intervel : 2 or 3 or 4 ....N I want follwing result set based... XQuery: Get the value only, and not the child node values - Hi Guys, If I have the following XML [code="sql"] <ROOT> <SPECIALNEEDS> INCLUDE <NEED> BLIND </NEED> <NEED> BRAILLE AUDIO </NEED> </SPECIALNEEDS> </ROOT> [/code] How do I get the value "INCLUDE" only. xmltext.value('(ROOT/SPECIALNEEDS)[1]', nvarchar(max)) is giving me all... Howto udpdate text using Replace html font in textfield - I have a textfield SectionText in column in my table Section. The sectionText field contains multiple instances of html with font... Group Values in TSQL - Hello, How can i merge same fields different values to one value? eg. tblEmployee table and output is, Date Employee Summary 07/11/2012 Susan Job... GROUP BY Error - Hi I have this code: SELECT pwh.IFA_Company_Name as IFA_Company_Name, t3.Client_Full_Name as Client_Full_Name, t3.SecondInvestor as SecondInvestor, IB.POLICY_ID AS Policy, CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value UNION SELECT pwh.IFA_Company_Name as... Sequence of Merge Inserts, Updates and Deletes - Does anyone know if there is any predictable order that the Merge statement will apply inserts and updates? I assumed... SQL Express - Create maintenance plan to run truncate table - Hi - I am new to SQL express, can someone please guide me to how I can schedule a task to... Query Help - I'm testing on some part of the application . My Table structure is like [code="other"]create table test (id char (8)) [/code] I want... Check and transfer data between two databases in same server using complex conditions - Source DB1.TB1 [ ID, EAN, ASIN, category, NAME, CONDITION] 1, null, 20001, ps2, COD2, new 2, 1002, 20002, xbox, HOLA, new ... Finding min and max date within a sub-group - My task is to find the minimum and maximum date for the Code contained in the latest row. A picture... Top 20 count - Hi all, I was wondering if this is possible, I use the query below which is working great if be... SQL Server 2008 : SQL Server Newbiessubstring/charindex - Hi all, I need to split the following string: 'A,B,C' In Oracle I'd use instr() function to get the position of commas... SQL Database moved under System Databases by mistake - I have a sw wich has a tool to configure replication between my 2 dbs I have. When the tool... Modifying a SQL 2000 maintenance plan -- and shrinking - I've got a SQL 2000 DB with a plan that was created long ago. On the Optimizations tab it's got... SQL Server 2008 : SQL Server 2008 High AvailabilitySQL Cluster shutdown issue due to network issue - Hi All, I have faced an issue yesterday on our SQL Server 2008 SP1 cluster. It got shut down due to... ReBuilt Index - I wanna confirm that some user are manually executing below query on each databases one by one This may be the... setting up Clustering - SQL Server 2008 R2 - I have a couple of questions about installing SQL Server 2008 R2 Standard in a 2 node active\passive cluster First, a... SQL Server 2008 : SQL Server 2008 AdministrationFull backup not emptying transaction log - I have a database that process a lot of transaction late at night. It is set to full recovery mode.... backup/restore - Hi, Where do I get the step by step sql scripts and explanations on how to get point in time restore? I... DB backup/restore without filestream - Dear All I have DB with Filestream. Recovery model for this is set to FULL. I need to backup only primary... High Tempdb Usage - Hi Experts, In one of our servers when some SSIS jobs are executed tempdb usage will become very high 1.99TB is... Kerberos authentication Issue - Recently we changed our Active-Passive cluster to Active - Active (Multi Instance). After this change,some of the users were unable to... - Hi, Recently we changed our Active-Passive cluster to Active - Active (Multi Instance). After this change,some of the users were unable to... check diskspace - Is there any existing and recommended script that checks sql server drives, and send alerts to DBA, like there is... Wait Types concern - Hi Experts, In one of our DW servers the performance is very slow that a simple select statment is taking hours.... User SP on Master DB - Hi Experts, Is there any problem if developers create user SP on master DB?? The row was not found at the Subscriber in replication - The row was not found at the Subscriber when applying the replicated command. Please suggest me Erroneous results on last_execution_time, execution_count obtained from DMV sys.dm_exec_procedure_stats - When I run the following query to get the last execution time and execution count of a stored procedure in... Career : CertificationMCM lab exam - how to register? - I'm going through the Prometric site (UK) and it does not appear in the list of available exams. 088-970 and 088-974. Any... SQLServerCentral.com : Anything that is NOT about SQL!How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at... 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 ServicesPass a parameter value to a Dataset Parameter in SSRS 2008 - I have a dataset with a parameter as which is passed to a query as shown below. [img]http://social.msdn.microsoft.com/Forums/getfile/191193[/img] The issue here is... Expose Parameters Value selected - Hi geniuses, I want to show which parameters the user as chosen to run the report. For a Non Multivalue parameter it... Data Warehousing : Integration ServicesBest Practice - Good morning. I just have a quick question for you folks. Do you know if when using a Script task... How to convert Epoch date into human readable format - I need to import a flat file into a simple table. There are three 'epoch' date columns in the data.... Data Warehousing : Strategies and IdeasHow to Relate a Snowflake Dimension to the primary Dimension - I have a dimension table with names of individuals and several demographics associated with the names. Each name can have... Unknown dimension values or - In your DW do you always create an "-1 Unknown" record for each dimension? For example in DimEmployee: [code="plain"]EmployeeKey -1 EmployeeName "Unknown" ect.[/code] Or... Data Warehousing : Analysis ServicesBest 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... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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 |
|||||||||||||||||
Reading SQL Server's Transaction Log
Day 2 of the OLAP Sprint
Script to create dynamic PIVOT queries in SQL Server
Server Level Security
