SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Runtime Code

I was talking with someone recently who was building a dynamic process that generated its own code at runtime to process ETL files based on a set of rules. It was an impressive system, and reminded me of how BIML can dynamically produce code and build packages.

However then I started to wonder. If you produce code at runtime that then runs, how well is that code tested? I suspect most people would test the code generation process. Is this producing the code I expect, but how many would then write additional tests for the generated code as it executes? I suppose people might also just test the generated code, but if that's the case, are they really testing the generation process extensively?

I'm not sure there's an easy answer, and not sure if there's actually a strong testing framework that exists to help here. As BIML becomes more popular and even ETL frameworks are used more and more in our software processes, I hope that we develop new testing methods to better ensure we are building quality code. I know some SSIS test frameworks exist, and there has been some writing on the topic, but I wonder how many SSIS developers bother to perform anything more than cursory tests? My suspicion is very, very few.

Ultimately I think we as an industry need to learn to build better software by improving our techniques, samples, skills, and testing.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 15.2MB) feed

MP4 iPod Video ( 17.9MB) feed

MP3 Audio ( 3.7MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
45 Database Performance Tips for Developers

FREE eBook
"45 Database Performance Tips for Developers"

Improve your database performance with 45 tips from SQL Server MVPs and industry experts. Get the eBook here.

SQL Monitor

Get alerts within 15 seconds of SQL Server issues

SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.

SQL Compare

Need to compare and sync database schemas?

Let SQL Compare do the hard work. ”With the productivity I'll get out of this tool, it's like buying time.” Robert Sondles. Download a free trial.

Featured Contents

 

Stairway to SQL Server Replication: Level 2 - The Role of the Distributor

Sebastian Meine from SQLServerCentral.com

In this Stairway, Sebastian will be covering the details of SQL Server transactional and merge replication, from understanding the basic terminology and methodology of setting up replication, to describing how it works and how the basic replication processes can be monitored. More »


 

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from Red-Gate

Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". More »


 

Overview of the Different In-Memory OLTP Index Types

Additional Articles from Database Journal

In-Memory table or what Microsoft refers to as In-Memory OLTP, or Hekaton is a new type of table is available with SQL Server 2014. In this article Greg Larsen discusses the different types of indexes you can place on your In-Memory tables, and how those indexes support different search criteria. More »


 

From the SQLServerCentral Blogs - Potential Backup Software Issues with Read-Only Databases in FULL Recovery

William Assaf from SQLServerCentral Blogs

If you decide to park a database in read-only mode for a long period of time, go ahead and alter... More »

Question of the Day

Today's Question (by P Sibi):

Which edition of SQL Server supports "Data compression and the vardecimal storage format"?

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: editions.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

Yesterday's Question of the Day

Yesterday's Question (by Mike Hays):

In SQL Server 2008 and beyond, automatic page repair is supported by database mirroring and by AlwaysOn Availability Groups.  Which three page types can the page repair process not automatically repair?

Answer:

  • Boot page
  • Global Allocation Map (GAM) page
  • File Header page

Explanation:

The Automatic page repair cannot repair the following control page types: File header page, the database boot page, & the Global Allocation Map (GAM) page.

Ref: http://technet.microsoft.com/en-us/library/bb677167.aspx


» Discuss this question and answer on the forums

Featured Script

Script to determine if two instances are on the same node

Gregory Ferdinandsen from SQLServerCentral.com

This script monitors that two instances of SQL are not running on the same node in an active/active cluster configuration.  I run this as a SQL Server Agent job every 30 minutes.

Notes:

*xp_cmdshell must be enabled

*best to run it from a server that is not a member of the cluster

*change @svrName to your server

*change #tmpTable names to match your server

This script can also be easily modified to use a cursor and read from a table if you have multiple active/active clusters

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 2014 : Development - SQL Server 2014

Subqueries - 1. I need to find the names of the customers who have purchased academic books. (Coding required as Subquery NOT...

SQl question - Hello Guys, Can you help me to solve these questions? Employee Customer id name age salary id name city industry type 1 ben 61...


SQL Server 2012 : SQL 2012 - General

SSIS expression need help - Not sure if this is good area to post this but could not find specific SSIS place for it. I...

Permission required to install sql cluster - Hi All, I need to install a two node sql 2008 r2 cluster on win 2008. What are the permisson or previlages...

Generate Insert Script - Dear All Want to create insert statement fron the view. Folloing steps followed SSMS -> Task - > generate script Selected the...

VMware SRM manage with SQL Server high transfer amounts. - In our organisation we have a number of SQL Servers running on VMWare vms which utalise VMwares SRM feature to...

Index was outside the bounds of array - Hi I am experiencing of problem relating to the error message on the production SQLServer2012; Index was outside the bounds of array....

SSRS 2012: incorporating custom template to Report Wizard - I’m not sure if this is an appropriate place for this question, I apologise if this is completely out of...

Combining variable values with result set - I have a bunch of variable values that i need to combine with the result set of dynamic sql and...

Index tuning - Hey all, I am doing an evaluation against all indices on my database, and I have a question regarding index maintenance....

SS 2012 Replication - How do you reinitialize in 2012 just one partition from a large SS 2012 table to another SS 2012 table? If...

Ideas for stopping customers meddling directly in the tables? - We have some issues with customers meddling directly in the tables for our application. Has anyone any ideas for monitoring or...

bulk export of xml column - with reference to http://technet.microsoft.com/en-us/library/ms191184.aspx [code] E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_featu res\db_features\bulk_import\examples_of_bulk>bcp practise..xTable out a-wn_x.out -x -T -S user-PC\sqlexpress Enter the file storage type of field xCol [nvarchar(max)]: varybin Invalid...

Scalar UDF different performance on different server - Hi all, I have a query that calls on a UDF. In staging, has the same number of records and...

SSIS 2012 - Pick and Choose which Packages to Deploy? - Hello all, I am using Deploy Tool in VS (Project Deployment Model) to deploy my project to SSIS Catalog. I...


SQL Server 2012 : SQL Server 2012 - T-SQL

Concat columns in Where clause - Hello everyone, I'm searching to see how performance works on columns concat in the where clause. What are the search...

More than one character - Hi there, In SQL2000 I used to use the following in my SELECT statement - [code="sql"]and len ([Initials]) > 1[/code] However it doesn't...

Calling SP with optional parameters - assignment not happening correctly... - Hi All... I have a question regarding calling SP with optional parameters... I have created a SP with definition like this [code="sql"] CREATE PROCEDURE...

stored procedure - Hii, I am trying to write a stored procedure to check whether all the columns of different tables are filled or...

sql query for ssrs report - I have the following report I need to generate . I am new to SSRS. [code="plain"] Number of Stores with June July...

identifying many to many relationship between columns - Hello All, I am struggling with something that is conceptually simple but I'm at a loss at how to implement it....

Proc with input paramaters does not compile - I have a SQL 2008 R2 proc which compiles & executes fine. It receives input variables to execute. This proc no...

Return the last version of set of records - Hello all, I have a query I am working on where an orders table has a version column for each...


SQL Server 2008 : SQL Server 2008 - General

Open Query : Unexpected null value returned - Hi, am trying to connect to PRONTO database from sqlserver via linked server and i get the following error An unexpected NULL...

Tablas con índice y opción IGNORE_DUP_KEY=ON problemas en los bloqueos al insertar en la tabla - Hola, Tengo una aplicación en SQL Server 2008 con varias tablas que tienen índices únicos con la opción IGNORE_DUP_KEY=ON que me...

RedGate SQL Search - Cannot Find "ON DELETE CASCADE" - Today I found myself having to remove records in a part of our database not yet visited my me. To...

SQl server restarted - I noticed there was a SQL server restart happened last night. It was shut down then start up in 2...

Transactional Replication issue - Hi I have restored one of my databases and as a result I have to re create replication publications and subscriptions....

Integration Services (SSIS) and Visual Studio Team Foundation Server - Hello, Anyone knows about the Team Foundation Server with SSIS. Also can we use same version tool for SQl 2008 and...

SSIS Oracle Instant Client - I need to create an SSIS package that connects to an Oracle 10g database on another server. I am running a...

sQL 2008 VS 2012 - We are moving from SQL 2008 to 2012. What are the big differences... As we have lot of packages around...

execution log info from a stored procedure - Currently we execute most of our SQL by executing a bat file that we can direct the execution log information...

audit report - I need to collect the audit report for around 100+ servers , how can i autoomate this task instead of going...

Row Version - We would like to see our tables contain a RowVersion column. Can you please suggest how we would add that...

CTE Recursive - I have this data and CTE recursion below. When I try to run it I get only one row back,...

Connection Problems - Good day i have come across a very interesting problem today regarding our Sql server 2008 server , we are currently...

SQLCMD ERROR WHILE EXECUTING INSERT STATEMENTS - Hi Team, I had a requirement of restoring Sql Server 2008 R2 database to Sql Server 2005 server for which I...

Insert Rows from excel file - I have an excel file with the items listed horizontally...I need to insert each item as a row in my...

query plan with "parallelism"... - hi everybody I have a query that runs in 2 seconds when I add another inner join, the same query lasts 120...

Connection problem with SQL 2008R2 and SQL 2005 on same server - Windows 2008R2 server had 2 instances of SQL 2005 installed, BACKUPEXEC and ACRONIS. I installed SQL 2008R2 Express (64 bit), created...

How can I create a user with securityadmin role that can assign read/write rights on tables, execute rights on procedures? - Good day all, How can I create a user with securityadmin role that can assign read/write rights on tables, execute rights...

use cte instesd of while and temp table - hello all. I have this quey: IF OBJECT_ID('tempdb..#Out') IS NOT NULL DROP TABLE #Out declare @count int,@i int,@RoleID int,@UserID int create table #Out...

Set Idenity insert problem (linked server) - using db server as parameter - Hi, Im trying to set idenity insert on on a table, however i cannot do this using the db server and...

Access rights - How to provide only rights to execute sp_who2 & killing the blocking SPID if any without giving sysadmin rights, is this...

Stored Procedure (or) SQL Script Output to Text File - Hi All, I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored...


SQL Server 2008 : T-SQL (SS2K8)

Summary, Rollup, Summation. - I have the following table: FormKey Flag1 Flag2 Flag3 ---------- ------ ------ ------ 1 130 132 129 1 130 130 130 1 130 NULL NULL 2 132 NULL...

OpenQuery linked server and local variables/syntex - I am trying to query from a linked server where a fields is in a list of values queried from...

XML Export File - Hi Friends, The below i have mentioned the sample data and Table structure [code="sql"]CREATE TABLE [dbo].[Xml_Export]( [City] [nvarchar](200) NULL, [Company] [nvarchar](300) NOT NULL, [Bedrooms] [int]...

ISOLATION LEVELS - Hii Guys what are Isolation Levels ? how to apply various isolation levels and please give me a detailed information in understanding...

Inserting error data into a table from CATCH statement within a TRY CATCH - I've been researching for past two days and hitting my head against wall, but I can't figure this out. I am...

Criteria Table - Hi all, I've had a trawl through the forum but can't find anything that will help although I'm sure this isn't...


SQL Server 2008 : Working with Oracle

Totally lost on creating a linked server from 2008R2 to Oracle - I've reviewed the two threads here, and many articles from doing a Goggle search. They all follow a theme, but...


SQL Server 2008 : SQL Server Newbies

csv file data into a table - Hi all, I am running SQL Server 2008R2 and trying to import .csv file data into a table. The problem is that...

Handling the exception - I have a calculated field in my SP. Please help to handle the exception rose if the start date and...

Where to start to learn XML with SQL Server - Hi Floks, I am trying to get my hands dirty with XML in SQL Server. I have tried MSDN and some...


SQL Server 2008 : SQL Server 2008 Administration

Message - Hi, I have Maintenance Plan with Full back up & Diff Back up & Transaction Log Back up. These 3 are created as...

Upgrade sql 2005 environment to sql 2008 - Hello, I have to upgrade a SQL 2005 box to SQL 2008 with jobs that run every 15 minutes. What approach...

SSIS 2005 Packages Upgarde to SSIS 2008 - Can you please tell me that how to upgrade SQL 2005 SSIS Packages to SQL 2008 SSIS packages. both servers...

Maintenance Plan Failure - Add detail to email - Is there a way I can add/attach some detail to my failure emails of my Maintenance Plan. e.g. the text...

Scheduled one-time job executes the next day? Any experience with that? - Dear colleagues. Recently I came across a very strange occurence on SQL2k8 R2, Enterprise edition. I scheduled a job to run once...

Updating ID field - Hi, I have a table: CREATE TABLE [dbo].[FIM_GLOBAL_ID]( [GlobalID] AS ('RH'+right(''+CONVERT([varchar],[ID]),(10))), [ID] [int] IDENTITY(100100,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [nvarchar](50) NULL, [PreferredName] [varchar](50) NULL, [DisplayName] [varchar](50) NULL, [DateofBirth]...

Database went into Restoring State for unknown reason - I have a Database that went into Restoring State for unknown reason. I had recovered it this morning. Then I performed...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

checkdb on Prod - Hi all, I just want a confirmation, i was told that running checkdb on a db that has not had...


SQL Server 2008 : SQL Server 2008 Performance Tuning

ASYNC_IO_COMPLETION while BACKUP DATABASE running - Is it normal for this lastwaittype to last for the entire duration of a backup database command? We're seeing the...

NC index seek vs NC index scan with implicit conversion - Hi all, I've been looking thru our production plan cache and have found a number of implicit conversions. Most are of...

Query Runs for Long Inspite of Indexes on table - Hi I have a table which has more than 6 million records . Below is the table CREATE TABLE [Off].[EngineResponseResult]( [EngineResponseResultId] [int]...


SQL Server 2005 : Administering

Best Practices for implementing Indexes - I'm working with a database that has some performance prolems. We have determined that some indexes need to be implemented...

Statistics Needed - When loading data? - I have a bunch of empty tables, i will be disabling all the nc indices and leave clustered index enabled....


SQL Server 2005 : Business Intelligence

Drop and Create table or Trunc table - Hi All, I have a scenario in which I have to delete rows in a MS SQL Server table and load...


SQL Server 2005 : Development

Bulk insert from file having varying number of columns - I have a csv file having different number of columns in each row. I will have to dump this data into...


SQL Server 2005 : SQL Server 2005 General Discussion

File missing - I have a BCP command that generates files from several views. It generates about 80 files and takes about 15...

converting UNIX timestamp problems. Help!!! - I am trying to run a query against a table with unix date time stamp information. The field is an...


SQL Server 2005 : SQL Server 2005 Strategies

IDENTIFY CHANGES TO TRANSFER IN ANOTHER DBSERVER - DATAWAREHOUSE - SQLServer2005 EE. Morning all, I need to identify records that are changed since 'last time' (e.g. 1 day ago). I thinking about 2...


SQL Server 2005 : SQL Server 2005 Integration Services

Error with packages in 64-bit environment - Version information on my computer : Visual Studio 2005 Professional, Version 8.0.50727.42 (RTM.050727-4200) .NET Framework 2.0.50727 SP2 Integration Services Version 9.00.2047.00 SQL Server version...

How to Excecute ssis package from excel - I have excel vba code in excel where i would like to add code to execute ssis package from excel....

SSIS (dtexec) and bulk load permission - Hi, I am an accidental DBA trying to install existing SQL Server 2005 in a new environment with existing contents. I...


SQL Server 2005 : T-SQL (SS2K5)

Concatenating columns returns NULL results - When I concatenate 2 columns, it returns NULL result. Column1 has some NULL value. How do I ensure it displays...


Reporting Services : Reporting Services

Repeat headertext and group header if Detail row continues on next page - Hi All, I have ssrs 2008 R2 and in my report I have grouped by owner , title, and the detail...

Multiple drill through options - I'm trying to replace existing reports from another BI tool with SSRS reports. The old tool offered an option to...

Default value for report parameter date field - Hi, My SSRS report parameter date field requires “next Monday” as default Start date and “Sunday of the next week” as...

SSRS - Sending Excel attachment & Body in same email - Hello , please advise , I have 2 data sets - 1st data set result email as in excel attachment -- Detail and 2nd...


Reporting Services : Reporting Services 2005 Administration

How to Send SSRS Reports(subscriptions) from SQL AGENT JOB STEP - Hi All, I have a sql agent job "LoadfromOracle" which loads data from oracle to sql server tables(Orders table, Marketing table...


Reporting Services : Reporting Services 2008 Administration

Report Grouping - Grouping Question - Best advice Here's my dataset DocNum ItemNumber ItemType QtyOnHand MO001 Item A LotTracked 5 MO001 Item B NotTracked 100 MO001 Item C LotTracked...


Reporting Services : Reporting Services 2008 Development

Limit the number of months to the last three months using ssrs - Hello ; I have a dataset which kind of gives me the below output : [code="plain"]Year Month TotalSales TotalProducts 2013 1 23233 45 2013...

Linked servers used in Report Builder 3.0 - I hope I'm in the right place. I am using Report Builder 3.0. I think it is basically the same...

Experssion - I am trying to Sum the percentage of increase or decrease in the Dataset. Here is the expression I have to...

ssrs 2008 r2 concateante values into one cell - In an sql server 2008 r2 report, I have one textbox that I need to concantenate values together. I then...


Programming : Connecting

Native Client driver choice by SQL Agent - How does the SQL Agent choose what version of the NC driver to use when connecting to linked servers? and/or...


Programming : XML

Stuck with an xml nodes query - Hello there, I'm currently working on extracting a load of xml elements out of a dynamic blob of xml. I have...


Data Warehousing : Integration Services

Executing Oracle stored procedure with XMLTYPE Output and capturing it in a SSIS variable. - I am in desperate need of help with executing an Oracle stored procedure that takes an XML of Oracle XMLTYPE...

Best way to grab first and last record from a file? - I have a inbound, pipe delimited, file. The first and last records contain audit information pertaining to the file. I...

Understanding the "Full Result Set" of SSIS - I have two questions about the result set generated by an execute SQL task - 1) What is the class of...

Handling strings methodology - It appears to me that I have basically 3 different methods in handling strings that are of wrong lengths from...

usage ssis,ssrs,ssas working environment on amazon webservices - Hi Friends I need some Information msbi tool to usage on amazon webservices . i gathered some some information above amazon...

Execute SQL Task: Executing the query ""CREATE TABLE " + @[User::FileName] - Hi Experts I am trying to create table dynamically in destination as same as Source file name with all columns.my...

How to configure the directory and file mask for 'Foreach File Enumerator' in 'Package configurations...'? - Hi, (Sql Server 2008 / VStudio 2008) it is easy to configure DB-connections - ServerName and Initial Catalog but how to accomplish the same with...

Import data from Excel to DB table - Hi, I'm learning SSIS and as a part of my project here is one scenario: I've a folder location where .xls...


Data Warehousing : Strategies and Ideas

Replicating data to a BI/Reporting Server - Hi, I'm looking for general advice/best practices/experiences on replicating production data to a reporting server. I ask as my company has...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...


SQL Server 7,2000 : T-SQL

TSQL returning only one row - Hi There, i have a query as below: by running the sql i.e. select NAMEEMPL from SAMINC.dbo.ARSAP INNER JOIN dbo.Users ON NAMEEMPL...

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, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com