In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.
 
SQL Compare Compare and sync databases with SQL Compare
“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.
 
SQL Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.

In This Issue

SSIS Deployments

Four ways to deploy SSIS packages are shown in this piece. More »


Tracking Business Metrics

There can be more to managing a SQL Server instance than just examining the performance metrics. There are times when understanding how your system is performing for the application is important. More »


Partitioning in SQL Server: Managing Sliding Window Scenario

The concept of a sliding window scenario is to manage and keep the same number of partitions on a partitioned table over time. Learn how in this step-by-step from Arshad Ali. More »


From the SQLServerCentral Blogs - Quick tip: Using sp_server_diagnostics

In SQL Server 2012 a new procedure sp_server_diagnostics returns a payload of information about server health and performance. The sproc... More »


Editorial - Don't Accept Defaults

Do you know the default users and passwords on Oracle databases? They're identified in the documentation for some versions, with the admonishment that these values should be changed. I've used those defaults on more Oracle databases than I care to count. SQL Server learned from this and there is no default username and password enabled on new SQL Server instances in recent versions. However, if you enable mixed mode authentication, please make sure you set a password.

There are numerous defaults that developers create for their software. In many cases, these are chosen to make setup easier on the user. However all too often the defaults are not set to the values that users would prefer, or what are the most secure settings. Sometimes vendors even changes the defaults during an upgrade to make them worse. If you are not careful, you might even end up exposing data you never expected to expose, as security researchers found at Amazon S3.

These days it seems to be more and more important that system administrators, especially database administrators, do not blindly accept defaults. They must be more concerned with security than ease of setup and use since the many data breaches make the news and I'm sure it won't be long before we see administrators getting terminated for not paying enough attention to configuration settings. With templates and tools being used for quickly creating new virtual machines, one poor security setting could be replicated to a large number of systems.

There are some good tools available for SQL Server, including PBM, which is built into all modern versions and editions. There are settings to force password changes and ensure compliance with your company policies. There's no excuse these days for not having servers that are configured correctly, using the defaults only when they are appropriate.

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


The Voice of the DBA Podcasts

No podcast today due to technical issues. We will return with our podcasts tomorrow.

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

What will be the outpt if i run following queries?

Declare @smldatetime SmallDateTime
      , @dt DateTime
      , @dt2 DateTime2
      , @d Date

Select
  @d=NULL
, @smldatetime='' 
, @dt='' 
, @dt2 =''

Select
  CAST(@d as varchar) 
, CAST(@smldatetime as VARCHAR)
, CAST(@dt as VARCHAR)
, CONVERT(VARCHAR,@dt2 )

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: Datetime Manipulation. 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.

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.


Yesterday's Question of the Day

What will happen after running this code?

CREATE TABLE dbo.Test
(
 Col_1 INT IDENTITY(1,1) PRIMARY KEY
,Col_2 INT REFERENCES Test(Col_1)
,Col_3 INT IDENTITY
)
;

Answer: The test table will not be created and an error returned.

Explanation: Only one identity column is allowed per table.

Ref: http://www.sqlteam.com/article/understanding-identity-columns

» Discuss this question and answer on the forums

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.


Featured Script

Retrieve SQL Instance Connect Info

This script quickly returns Server Name, Cluster Node, Default Drive Letter for MSDB database, IP address and port 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 : Administering

Query - Nested Loop Join - In below query , in the exec plan i see nested loop join on TBFE table. This is executed for 25,000...

Index Rebuild Taking too Long? - I have an instance running the following version: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c)...

Query Multiple Databases - 04/04/2013 I am a newbe database admin and I have been tasked to run queries against multiple databases. Each server has...

SQL Server 2005 : Backups

RedGate Backup - I installed Redgate Backup5 on Windows 2003 + SQL Server 2005 with SP4.But I am not able to see sql backup...

SQL Server 2005 : Business Intelligence

The 'WBBindings' required element is missing under /Partition at line 1, - Hi, first let me say i know practically nothing about SSAS. We have a dev team who have been building a...

Business Intelligence - Integration Services - FTP Task - Can't save password - How do you make an FTP connection save the password?

Loading Fact Tables - Step by Step Instructions Challenge - Dear All, This is my first Data warehouse project and I am having serious problems loading my fact table. I...

SQL Server 2005 : Data Corruption

dees is een probeersel - please delete my post

Database gone into Suspect mode after the execution of an SP - [b]SQL Server Version: 2005 Standard RTM version 9.00.1406.00 [/b] A Stored Procedure(This SP Runs everyday but has never caused suspect mode)...

SQL Server 2005 : Development

split single row into four rows. - Hi All, I am having a table which contains 4 field as below. stuid,studname,startdate,starttime,endtime. now if starttime is 7am and endtime is 8am...

SQL Server 2005 : SS2K5 Replication

Performance issues on Creating oracle Publication. - Hi, We have sql 2005 Enterprise edition with SP3 and oracle 10.2.0.4 on Unix solaris. The Problem we are facing is whenever...

Transaction Log Growing due to Pending Transaction (Replication) - SQL Server 2005 SP4 with Transactional replication. I have this Database Log, which keeps growing due to a Pending transaction in...

SQL Server 2005 : SQL Server 2005 Integration Services

writing data from Table to Excel files in Loop based On a condition - HI, I am trying to load the data from table to Excel files. I have 5 folders, each folder has an...

SSIS question - how do i modify a package which has been deployed to the server? This package is being used for dba...

SQL Server 2005 : T-SQL (SS2K5)

I need the code part for splitting the row into different columns? - 1 : 7 ; 9 : 21:Task Completed:2013-04-08, 1 : 8 ; 10 : 22:Good:2013-04-08, 1 : 9 ; 8 : 19:Not Bad:2013-04-08, 1 : 10 ; 7 : 18:Bad:2013-04-08, 1 : 11...

DBCC Shrinkfile issues - First of all, I know I shouldn't do a shrink file because it causes fragmentation. however, I am working with...

SQL Server 2005 : SQL Server Newbies

sequencial insert - I have list of Heads like A, B, C, D.... and like to insert in a table with three column COMCOD,...

SQL Server 7,2000 : Administration

Connecting to different instance of SQL Server - Hi all, I have installed 2 instance of SQL Server on a same computer . One is a defualt instance and...

The log for database "databasename" is not available. - "The log for database MYDB is not avaiable" .. when writing an UPDATE statement to a table. We first observed this problem...

SQL Server 7,2000 : Performance Tuning

Need help optimizing a query - Just fair warning, I am definitely a newbie, I fell into this work so to speak. I have a query that...

SQL Server 7,2000 : T-SQL

extracting waitresource from a blocked process report - Hi, I've got a "blocked process report" trace and want to get all the waitresoure values for a blocked process: select top...

Using WAITFOR and PRINT in a loop does not show any result - Hi, I'm trying to make a loop that waits for 5 seconds between every execution. When Using the following script, which is...

Error Message:Cannot open database requested in login ''databasename''. Login fails. - While i was trying to execute the stored procedure  have got  the below error and terminated the execution. Server: Msg 4060,...

How to join a table-valued Function - Dear Forum, I hope someone knows the trick. In a rather complicated computation, I use a table-valued function with two Parameters...

SQL Server 2008 : SQL Server 2008 - General

In linked server, which server will process data? - In ServerA, I created a linked server to ServerB. In ServerA, there is a query like below. select * from [ServerB].order.dbo.allorder...

Developers track changes - Hello, I am trying to find out how can I track changes in my database, how deleted table or updated table...

Restoring database - Hello, I am trying to automate my code by restoring once a week database from one server to another, this is...

access denied message while executing xp_cmdshell - Hi, I am trying to execute below simple copy statement using xp_cmdshell EXECUTE xp_cmdshell 'copy C:\test\*.txt C:\"Program Files\"' it gives an error as...

TempDB activity after inserts - I am running into issue doing a insert into fact table. Essentially i can see that insert is done and...

Error Message SqlDateTime - Please, When I run a Select on my table i receive this message: "Une erreur s'est produite lors de l'exécution...

Add user to sql server with the required permission - In my sql server express 2008 r2 i have about 15 login account. Now i want some about 5 users...

Finding DROPPED Trigger - Hi Team, I've 100 of triggers in my database, today i found that some triggers are dropped from my database. How to...

How to find common tables between two databases? - Hello All, Can someone tell me how can we find common tables between 2 databases? Any scripts or querry plzz. thanks in...

connect sql server using windows authentication in another pc - I am having sql server 2008 r2 express installed in a PC. I have some other PC which are connected...

connecting sql server in different domain using Windows authentication - hi, We have some client PCs connected to a server PC.Users in client PC access sql server 2008 r2 installed in...

Get data from 3 or more tables - I have tables with following definition' [code="sql"] CREATE TABLE [dbo].[items]( [srno] [int] IDENTITY(1,1) NOT NULL, [itemname] [nvarchar](255) NULL, [unitsymbol] [nvarchar](50) NULL, [itemtype] [nvarchar](50) NULL, [purchaseledger] [int] NULL, [salesledger]...

How to Get the Object Created by a Specific user in SQL Server - Hi Team, I am Looking to understand / create a query which will help to Identify the Objects which were created by...

tablediff Utility on a table with 1.3 billion records? - Hi, I have loaded data from a table with 1.3 Billion records into a new table which I have partitioned indexes...

Ignoring timeout in stored procedure - I know that this might cause a lot of suggestions about query optimization and such, but I have a somewhat...

Get week ending date given the week number - How can I get Saturday's date given the week number? This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I...

Linked Server Setup Always Creates a Loopback - I want to create a linked server to a remote server (not a loopback to the one I'm on). But...

Estimate storage for Images - Hi, I'm hoping that some of you have worked before with databases where Images are stored? I'm referring to the situation where...

Help with tuning stored proc - Hi all I have a proc here where the estimated amount of rows is around 2 trillion where the actual numbers...

Query Timeout help - Hi, Could anyone tell me why the following query is timing out please? [code="other"]SELECT PLAN_HANDLE FROM SYS.DM_EXEC_PROCEDURE_STATS PS WHERE PS.PLAN_HANDLE = 0x05000A00ED4FE245402231CC000000000000000000000000[/code] If there...

Sum 'LastPeriod' short version - Maybe my last post was too long ... Does anyone know if the results of a 'LastPeriod' MDX query can be...

SSIS script task - I need to set the unicode property of a connection manager (which is a variable) to true in a Script...

Use of Threads in Data Flow task of SSIS - How are the threads used by data flow engine? Is it like it assigns same no of threads per data source...

Publishing and accessing SQL Server 2008 Reports - I 'm new to SQL Server database and I need help. I 'm trying to publish sql reports for users...

Remote / Local node page looups/sec both zero - When I try to monitor the sys.dm_os_performance counters "SQLServer:Buffer Node\Local node page lookups/sec" and "SQLServer:Buffer Node\Remote node page lookups/sec" they...

SQL Server 2008 : T-SQL (SS2K8)

Need advice on this string manipulate scalar UDF - Dear all, [b]Can you please comment on this UDF, is there any better way to do it? For example CLF scalar UDF...

The query processor could not start the necessary thread resources for parallel query execution. - I'm getting below error while running this query. [code="sql"]The query processor could not start the necessary thread resources for parallel...

Initial size of the database file using T-SQL - Hi, Please let me know how to get the initial size of the database file using T-SQL. sys.master_files, sys.database_files, sysfiles, sysaltfiles...

CTE and Spool operators - I commonly find CTE using spool operators in the execution plan. According to Microsoft the spool operator uses the temp...

SQL /Procedure - for Transpose and Calculation - Hi Team, I have a Complex requirement. Source: -------- NAME PAYMENT_TYPE PAYMENT SUDHIR SAL 30.3 SUDHIR ADV 10.3 SUDHIR ALL_1 10 SUDHIR ALL_2 10 SUDHIR ALL_3 10 MADHAV SAL...

Rounding up - Hello Everyone! I have this issue where i use a store procedure to create a report: ALTER Procedure [dbo].[A_RepChequesxCheque] @FechaInicio T_DATETIME, @FechaTermino T_DATETIME AS Begin select ant.CheckId,...

T-SQL Variable vs String Equivalent - Got a question with some coding standards. There are developers declaring a variable to a constant value and using that...

pull out all values within an XML column - i'm wondering if something like this is possible in some way: [code="sql"] select OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'), --OriginalSchema.value('Form[1]/Codes[1]/code[sql:variable("y.myNum")]','varchar(max)'), * from ( select id, ROW_NUMBER() over(partition by id...

SQL Server 2008 : SQL Server Newbies

Please tell me how can I write one query to resolve this problem. - Let say I've two tables: Plates table with fields: -Start plate ( unsigned long type) -Stop plate ( unsigned long type) Plates row1: 1 12 Plates row2:...

Trace file error - Dear all I am getting following error "Encountered an error or an unexpected end of trace file 'c:\Program Files\Microsoft SQL...

Inserting into an auto increment PK field - i have a table as follows ColA PK autoincrement Colb nchar ColC ncahr ColD nchar and i have to insert values into this...

Automating Backup Monitoring Error - Hi All, I have created an stored procedure which uses thebelow select statement : select getdate(),'Old or No Backup At All For...

timeline in cross-tab?? - Hello experts, My problem is as follows. On one hand I have got the beginning and end date of a tournement,...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008 R2 cluster with default instance - Hi, I have a server with two instances: one default and one named using SQL Server 2008 R2 Enterprise on Windows...

services started on a passive node - In a active passive node if i start the sql service , and Full text search on a passive node what...

Seeking some guidance on the use of Log Shipping in SQL Server 2008 - I have recently taken on responsability for the database part of our Sharepoint solution. From looking at the documentation there...

multiple instance sql cluster with different windows configuration - We have a two node cluster. In order to utilize the max resources we are planning to add more instances...

SQL Server 2008 : SQL Server 2008 Administration

Have upgraded from 2005 to 2008R2 but still getting security updates for 2005 - Hi there, We've recently upgraded a 2005 SQL cluster (active / active) to 2008 R2. There still seems to be SQL 2005...

How to check the index fragmentation on a schema binding view - How to pull the index usage stats from a schema binding view ?

ON’Microsoft.ACE.OLEDB.12.0LEDB Error - Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". I have already tried USE [master] GO...

SQL 2K8 Edition Upgrade? - Hi, I want to upgrade SQL 2008 enterprise edition from standard edition. what are the points should take care before and after...

SQL Server Jobs, status, duration running, step - Hello, I need a help to pull information about SQL Server Agent jobs running on multiple instaces. (I am using Powershell) I...

Reindex task taking for ever - SQL Server 2008 R2 - 10.50.2500. We have a Database, about 35 GB in size, which has got several tables and...

Backup failed - no SQL agent job - We recently found there are quite a few error logs in sql log: Message BackupVirtualDeviceFile::PrepareToFreeze: failure on backup device '{4FEEA406-DD73-47D8-BC10-0ADAD6A44D6D}1'. Operating system...

Obscure Replication Error - I've had multi-terabyte transactional replication going for a couple years now, for the most part things flow quite smoothly with...

Can not read 'Sql Server Agent logs' on node 2 of a cluster - Clustered 2008 R2 SQL Server Enterprise Edition (64-bit) SP1 10.50.2500 Both nodes are running under the same account which is an...

how to enable back database mirroring - Hi Everyone, I Have disabled database mirroring on principal server using below command for restoring database over a mirrored database because...

SQLServerCentral.com : Anything that is NOT about SQL!

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 Services

Transparency on chart series object reference not set to an instance of an object. - Hey all, I have a chart that was converted from 2005 with dundas to 2008 r2. All looks fine except the...

value in middle of indicator - Is it possible to have a Value in middle of indicator color: I have set up an indicator that changes color...

SSRS 2008 R2 - SEGMENT table - Hello All, We are using SQL server 2008 R2 SSRS. The segment table in the SSRS database (Not SSRS Tempdb database)...

SSRS training provider recommendations, please? - This is a two-part question: (1) Do you have a technology training provider that you love? I need a 3-day or...

Dynamic Chart in SSRS - Is it possible to create the charts dynamically in SSRS? Based on the inputs the no of charts needs to be...

Reporting Services : Reporting Services 2005 Administration

Not able to access Reports (SSRS 2005) using servername in URL - Hi Experts, I have deployed Reports uisng localhost\reportserver on my machine in SSRS2005. Now, my problem is that: I am able...

Database Design : Disaster Recovery

Enable back DB Mirroring - Hi Everyone, I Have disabled database mirroring on principal server using below command for restoring database over a mirrored database because...

Data Warehousing : Integration Services

SQL server agent job fails - Hi, I have a SSIS package that picks up csv files from a shared folder on daily basis and load the...

SSIS FIlter conditions - Hi, I have a csv flat file with data from 2009 until date. I wanna restrict 2009 - 2011 data from flat...

Row count difference between flat file source and sql table - I have an SSIS package that imports flatFile data to a table. The package has error output for flat file...

Merge Statement One Server to another - Hello, I have a requirement where I need to insert/update data from a Table A in SqlServer A to a...

Extract data from CSV file - SSIS - Hi all, I have a csv file with data from 2005...and on daily basis the data is getting appended in the...

Data Warehousing : Analysis Services

Copy table to create an alias (Tabular model) - Hi guys, does anyone know how to copy and paste a table to create an alias with in a tabular model. e.g....

Unable attach analsysis service database without detach log file - Hi Post recycle of Analysis Service we found one of the database went missing. So we tried attaching it. But its...