|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - Acing an Audit |
|||||||||||||||||
I've been through relatively few audits in my database career. I've worked in a few industries that didn't require them, and avoided the stringent requirements of PCI and HIPAA. ISO 9000 was the first audit I encountered and I had been preparing for Sarbanes-Oxley (recently passed) when I left that company to come work for SQLServerCentral. The preparation for an audit required a lot of work, meetings, and organization. The first time I suffered through an ISO audit, I was amazed at how much of our daily work was interrupted and the time spent ensuring we would pass the audit. The second time wasn't much better, though I'd instituted some processes and controls for the DBA group that did reduce the amount of preparation needed for our portion of the audit. I wish that more companies I'd worked for had actually built the controls, security, and documentation into their processes. Maybe then they'd only need a 30 minute window to prepare for the audit. That's what an insurance company needed to do recently according to this piece. I found many of the rules and regulations required in the ISO and SOX documents to be ones I'd want to implement for my database systems. The hard part was getting management to agree and implement the rules as part of our daily work. I did find it interesting that the company had built their own software to match their processes and allow employees to work efficiently. Lots of companies have struggled with the idea of becoming their own software company, but if software is truly going to be an important part of most businesses, perhaps it's a good investment for most of them. » 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: How many rows get inserted into the table by this code? CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10)); ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0); INSERT INTO Test_Table VALUES (0, 'row 1'); INSERT INTO Test_Table VALUES (1, 'row 2'); INSERT INTO Test_Table VALUES (2, 'row 3'); INSERT INTO Test_Table VALUES (1, 'row 4'); INSERT INTO Test_Table VALUES (NULL, 'row 5'); SELECT * FROM Test_Table; DROP TABLE Test_Table; Think you know the answer? Click here, and find out if you are right. This question is worth 1 point in this category: Primary key. 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 |
|||||||||||||||||
|
Using SQL SERVER 2012 /* Version: Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) */ USE AdventureWorks2012 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO I create the following temporary table CREATE TABLE #Seasons(Hdate DATE,Season VARCHAR(10)) I then execute the following T-SQL: INSERT INTO #Seasons(Hdate,Season) SELECT HireDate, CHOOSE(MONTH(HireDate),'Winter','Winter', 'Winter','Spring', 'Spring','Summer','Summer','Summer','Fall','Fall','Fall','Winter') AS Season_Hired FROM [HumanResources].[Employee] SELECT COUNT(Season),Season FROM #Seasons GROUP BY Season. The questions are: Which season has the least number of hires? Which season has the largest number of hires? (select 2)
Answer:
Explanation: CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned. |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
SQL Job Timeline (Graphical)This script generates a graphical timeline of all SQL jobs with a variable zoom level 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 Connection memory usage is growing steadily - Issue: SQL connection memory usage is growing steadily (8G after only 2 days). It can keep increasing to use all... Restoring databases created on another server with certificates - We will soon be converting our existing production databases to a a system where we use certificates on the actual... Gettint minor error after 2005 installation - Upgraded 2000 to 2005. When I open management studio, i get below error, evn though it is opening. Access to... Delete with TABLOCK - [b]To delete rows in a heap and deallocate pages, use one of the following methods. Specify the TABLOCK hint in the... very high CX packet waits - i have noticed very high CXPacket waits on one of our dataware house server. All these queries are just select... about storing sql script in a table? - Hi Can i store the database creation script in a table call the script in stored procedure to create new... BCP native format data issue on import - I am using bcp in native format to export a products table out which has an integer primary key (NOTE it... I wish to send results of 'query' from trigger by email to specific recipients - ALTER TRIGGER [trig_NO_LABOR_DATA] ON [dbo].[WIP_FG] AFTER INSERT AS --insert results of query into the table below INSERT INTO No_Prod_Data_Inn (job_number, fg_date, qty_scanned)... SQL Server 2005 : BackupsMaintenance plan delete sometimes fails - I have a strange issue... in addition to our normal backups, there are a bunch of databases on our production... backup all databases in SQL 2008 - Hi, Need a script to compress and backup all the databases in sql 2008, except system databases. Please let me... SQL Server 2005 : Business IntelligenceSample Insurance Data warehouse - Hi guys, I am looking for a sample data warehouse for insurance company in order to have a solid starting point. Can... Stored Procedure explanation - Hi all, this is the first time am working on stored procedures, I need to make changes to the existing SP... where to install the new warehouse SQL Server? - I’m starting to build the DM design for BI data warehouse; I have two SQL server 2008 databases that will... SQL Server 2005 : SQL Server 2005 General DiscussionSQL Server taking 2 GB ram with out PAE - Dear Gurus, Can you please let me know why SQL server is taking more than 2 GB of RAM even PAE... Help with a query - Hi, I have a query and I am not sure why it doesnt return any lines. I am building some kind... SQL Server 2005 : SQL Server 2005 StrategiesRun stored procedure when temp table is created/dropped - Hi guys and girls, I need to update 1 or 2 records in my database each time a temp table with... SQL Server 2005 : SQL Server 2005 Performance TuningCan lots of User Connections cause high CPU use - If I use Activity Monitor I see 1400+ connections. Only 10 are "Runnable" the rest are sleeping. Meanwhile the CPU... SQL Server 2005 : SQL Server 2005 Integration ServicesSSIS Packages invoke from SQL AGent job from different SQL Server - Hello - Below is my setup : 1. 1 SQL Server 2012 [ destination server ] 2. 1 SQL Server 2008 [ source server ] 3.... passwords and package encryption - Due to unicode "upgrade", I'm looking at redoing about 20 packages. I use the wizard to generate the package. This... Creating Header AND Footer to a flat file destination file - Hi Guys, I am working on a financial project; the requirement is interface flat file should not contain any duplicated arrangement.... SQL Server 7,2000 : AdministrationSleeping Processes - I have a question about "sleeping" processes. We have an application that access our SQL Server. I just took a... SQL Server 7,2000 : Generaladdress management software - Does anyone know of any address management software that is compliant with sql server 2000, i.e. for record duplication and... SQL Server 7,2000 : SQL Server NewbiesSleeping Process... - Hi, Can anyone get back to me with tips regarding this. I am gettign connection pool error while using the .Net... SQL Server 2008 : SQL Server 2008 - Generalpermissions to create and run ssis - Hi all, I created database and login for one of our developer. What kind of permissions do i have to give him... Function Not Working - How to create a computed column based on the Title nvarchar(max) column of another field: I want to make Title... SQL Connection memory usage is growing steadily - Issue: SQL connection memory usage is growing steadily (8G after only 2 days). It can keep increasing to use all... Query processor ran out of internal resources and could not produce a query plan. - I'm getting this error message on a query that I've put together. It queries Infor's Smartstream product for general ledger... create synonym to schema - Hi I have a dev database and a live database both have linked servers to other databases in the dev environments and... Partitioning key not know. - During a selection, the partitioning key is not known. Is it still possible that an partioned index is used in a... Any chance of success? - I’m currently working on a migration project and need some feedback on whether or not this is the best way... database restore 2008 r2 - Hi all, approximately how much time the sql server 2008 r2 will take to restore 66GB of .BAK file. Environment: windows... SQL design discussion - help required - I'm developing a process for performing data cleaning / dedeuplication. To make the process as flexible as possible I am using... sql server datapage storage content - hi all, Is the Data Page contains the data from a single table or multiple tables data. Can concurrent INSERTs mess up a UNION? - Can SQL Server guarantee that if I issue a command like: [code="sql"] INSERT INTO T(...) SELECT t.a, t.b, 1 FROM sometable t UNION SELECT -t.a, t.b,... Deattach/Attach actions made CPU utilization 100 % - Production db had the 100 GB of log file. to remove log file i did following steps. - Deattached Database - Renamed... Stored procedure explanation - Hi all, this is the first time am working on stored procedures, I need to make changes to the existing SP... TIME/DATE Datatypes Accuracy. - Hi all, What is the meaning of "Accuracy of 100 nonseconds" in case TIME data type in sql server 2008. And... Inputs Required!! - Hello, I want to implement the audit trail on the daily transactions that are being performed. Please share the thoughts on how... sql writer issues - hi.......guys. sql writer while taking backup it backup for entire volumes or it take only sql backup ??? thnx...........reply partitioning - swapping in from smaller higher grain to lower grain - I have a table partitioned at the grain of Year. I have a second table partitioned at the grain of month. I... ERROR SETTING FK - I receive the following error when trying to set FK The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_TRIP_DETAILS_RIDER_NO".... UK Dates in Tab delimited file not importing correctly. - I'm sorry if this has been raised before. I have searched but have not found what I'm looking for. I have... Unexpected behaviour of query - Hi All, I am using below query in sql server to find out top 5 amount and date by customer. I... The CREATE TABLE SQL construct or statement is not supported. - Hi All, CREATE TABLE #Scope ( JobID BIGINT ,ListID INT ,BatchID INT ,SendStartTime DATETIME ,SendCompleteTime DATETIME ,PRIMARY KEY CLUSTERED (JobID, ListID, BatchID,... Deadlock Issues (Why Certain Locks?) - I am having some problems with frequent deadlocks in a vendor-supplied database and I have been analyzing the database and... Procedure is taking 15 seconds after service restart but from second time procedure takes 1 sec - Hi, I have one store procedure which is taking 15 seconds after restarting the SQL Server Service. But when I try... Group By - Help - Hi geniuses, I need some help with a query I need for a report, here's what my table contain: Project | ReceiverGroup... Distribution cleanup job failing - transactional replication - Since yesterday afternoon I've been seeing this in the Agent History: [quote]Executed as user: Domain\User. Time-out occurred while waiting for buffer... SQL Server 2008 : T-SQL (SS2K8)TSQL Query help - I have table from November with the flag 'N' and 'Y' ,when I get the laons for december , need to... Pulling errors from a linked server - Hi all, I've got a linked server to an attomix database. When i send an erroneous statements to the server an... Order of JOIN in multiple table joins - I want to know how order of joins executed in sql server. Suppose i have used 3 tables in join. table1... what to place in Stored Proc sysname parameter - I have a table that my company uses for logging. One of the columns is declared as: event (sysname(nvarchar(128)),not null) We... Inactive Extended Event Sessions - To check if an extended event session with a name exists I use the following statement: [code="sql"]SELECT 1 FROM sys.dm_xe_sessions WHERE... Dynamic Grouping - Hey all, I had a query that i didnt like - as it was a pain to maintain. Basically there are 6... Error On while executing to insert image on it??? - create table Wishing ( ID int identity, ImageName varchar(500), ImagePath varbinary(2000) ) insert into Wishing(Imagename,imagepath) values('sunrising','images/sun.jpeg') while i am inserting image to to table showing error on use... Bulk Insert from file to table - Hi, I have .dat file with following format data. 1,2,3, 4,5,6, 7,8,9, i want load this format file into sql table using bulk insert... IN or LEFT OUTER JOIN which is better ? - Hi, I am confused. Pls answer my question. should I use IN or LEFT OUTER JOIN in my sql query ? and why... WHERE vs. GROUP BY again - The following two T-SQL statements return exactly the same row set whatever data is: [font="Courier New"]SELECT [i]something [/i]FROM [i]somewhere[/i] WHERE [i]condition[/i]... t-sql 2008 r2 error message - This is the first time I am writing a trigger. I am getting the following error message on a sql... Update works on some systems but on few others - Hi All, I have some update and insert queries to update GEO location data, which when I execute on my system... Stuck with PIVOT - Hi, To simplify the problem, I have JOINed 3 tables together to create a new table with the following output: [img]https://sites.google.com/site/facesmapicons/PIVOT.jpg[/img] I... SQL Server 2008 : Working with OracleAfter Insert trigger - Hi , I have a requirement where i have a table (EMP) with fields id, name, status What i want is after... SQL Server 2008 : SQL Server Newbieschanging a sql 2005 developer license to production - I have a windows 2003 server running sql 2005 with a developer license installed. It's now in production. I have... Pivot results not as expected - I have a select statement as below: SELECT Institution_Number, Attached_Account_Number, [1], [2], [3] FROM ( SELECT CARD_Attached_Acct.Institution_Number, CARD_Attached_Acct.Card_Number, CARD_Attached_Acct.Portfolio, CARD_Attached_Acct.Attached_Account_Type, CARD_Attached_Acct.Attached_Account_Number, Inquiry_Name_To_DDA.Name_ID, Inquiry_Name_To_DDA.Name_Line FRO query to list linked server user - Hi How do i query the SQL server to list the "remote login" of a linked server? it sounds so simple but... Trigger on insert - Hi , I have a requirement where i have a table (EMP) with fields id, name, status What i want is... Property Collation is not available for Database - Backup job failed last night - viewing the history logs gave the following message "Could not find database ID 15. Database... how to attach database with variable path by storedprocedure?? - please help me, how to attach database with variable path?? Stored procedure very slow execution - Hello, Please help me, I'm desperate. I have an old SQL2000 (SP4) - and no, I can't upgrade it, is in my... SQL Server 2008 : SQL Server 2008 High AvailabilityPeer-to-peer replication and identity columns - All of the articles regarding identity columns for peer-to-peer replication talk about setting identity ranges when writing to multiple members... SQL Server Clustering Questions - Hi, Can someone provide few important interview questions on clustering with answers? SQL Server 2008 : SQL Server 2008 AdministrationRed Gate Restore Failed - Hi Experts, I got below error when tried verfying backup. Got the exact error when tried to restore DB earlier. Please... Restoring db's with certificates in another server - We will soon be converting our existing production databases to a a system where we use certificates on the actual... Slow Reports - Index Update - Good Morning Everyone, Firstly I hope everybody is having a nice morning :-) I have a question that I hope someone can... migrating dts 2000 packages to ssis 2008 packages - can please any one tell me that step by step process of how to migrate dts 2000 packages to ssis... Red Gate SQL Monitoring fails - I just started a new DBA job and was introduced to SQL Monitoring from Red Gate. The program is not... checkdb error - Hi experts, CHECKDB threw error Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is... Database sizing - I know how to estimate the size of a table (number of bytes stored per row X number of estimated... Programming : GeneralAdd SQL Objects to TFS/ Version Control - I work for a small company and we are trying to get all database objects in to TFS. Our dot... SQLServerCentral.com : Anything that is NOT about SQL!Web application design query - Hello, I am new to web application designing and using .net (MS Visual Studio 2010) to design an application. The issue I... Data center outside US? - We currently host our own (aging) hardware on site and are considering moving to the cloud. Our consultant suggested a... Reporting Services : Reporting ServicesAnother SSRS Conditional Formatting Question - Afternoon all... now ive been using SSRS for about 6 months and am becoming used to all the functions within it... SSRS 2008R2 GROUPING - I have add multiple goups in ssrs 2008 r2.My problem is when i try to add a group 1 extra... Refer out of scope expression - Here is my Expression that is trying to refer something out of scope: =iif(Fields!fee_name.Value<>"monthly" , StrConv(Replace(Fields!fee_name.Value,"_"," "), VbStrConv.ProperCase) , iif(ReportItems("Text58").value > Fields!fee.Value , StrConv(Replace(Fields!fee_name.Value,"_"," "), VbStrConv.ProperCase)... Reporting Services - Multiple Page Breaks Issue - Hi All, I have a unique kind of requirement explained as under. I have two table controls on report design.... Database Design : Design Ideas and QuestionsPolicy database Design - i have enclosed here sheet which contains column's list . can anybody help how can i seggregate them into different tables... Suggsted approach to masking - We have a reporting system that I present multiple databases to (i.e. for multiple clients). Our UAT & DEV source systems... database design help please - Hi I am having some trouble on how to normalize some tricky tables. Basically it is a recurrence event table that... Data Warehousing : Integration ServicesSSIS iSeries Source Duplicates Records - I am creating a package to read data from our production iSeries (AS/400) and copying it into our new data... Speed up simple SSIS load - Hi I just published a new post on SQL Server Central [url=http://www.sqlservercentral.com/blogs/katie-and-emil/2013/01/28/ssis-6-million-rows-in-60-seconds-using-standard-laptop/]SSIS 6 Million rows in 60 seconds using standard laptop[/url]... where to install the new warehouse SQL Server - I’m starting to build the DM design for BI data warehouse; I have two SQL server 2008 databases that will... SSIS, creating columns from rows based on id from result set - I have the following and is getting the attached error. I am trying to loop through a table and change... Generic SSIS Package Creation - Hello, I have an Idea and want to see if this can be possible or any real time Developed solution already... Data Warehousing : Strategies and IdeasBusiness datasets (overview, structure, naming conventions, business rules) - Hi Guys I was wondering if someone could point me in the right direction. I need to gather information about different datasets... Data Warehousing : Analysis ServicesComputing on a cube? - Hallo, I'm totally new on computing on SSAS, and quite new to SSAS in general. I'd like to get some index... SSAS CUBE PROCESSING ERROR - I am new bie trying to learn SSAS. I have these errors in trying to process my cube after it... Testing Center : Question of the Day (QOD)connections sleeping - i have a doubt, when i execute sp_who the display send me de current conections, but almost case there are... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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 |
|||||||||||||||||
My SQL Server Database is Corrupt - Now What?!
My view isn’t reflecting changes I’ve made to the underlying tables.
