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

ETL Magic with SSIS – Part II

Ron Johnson from SQLServerCentral.com

Part II of a discussion and demonstration of ETL of a CSV data file using SSIS. More »

Raw Materials - Looking Up

Larry Gonick from SQLServerCentral.com

Leila and Arthur see shapes in the vapor. More »

Grant Fritchey's SQL Server Howlers

Additional Articles from SimpleTalk

We decided to ask various well-known SQL Server people to write about their favorite SQL Server Howlers. These are those common misunderstandings about how SQL Server works that end in tears, and plaintive forum questions. Grant Fritchey opens the series with some of those howlers that stick in the mind. More »

From the SQLServerCentral Blogs - SSRS - Embedded Custom Code

When developing reports in Reporting Services you will often use the built-in expression language to make report data and formatting... More »

When I wrote computer code in school, programming as we kids called it, I could literally watch my programs compile on the screen. Working on a Radio Shack TRS-80 in PASCAL, we would see the lines of code scroll by, 3 or 4 a second, as they compiled into executable code. When that is the speed of the computer building your work, you learn very quickly to proofread your code from syntax or logic errors. A simple semicolon on the wrong place can result in quite a bit of lost time.

At some point we transitioned to Turbo Pascal, which compiled hundreds of lines in a split second, on par with the speed of today's compilers, which are often working through thousands of lines of code. Is that a good thing, however? Has this speed improvement ended up building poor habits in developers?

I ran across this post on the times when the author had one compile a day. That's slower than I had, but we did have time limits on the computer, as well as printer limits, when I was in college. The compile/fix a syntax error/recompile/test cycle  that we have now wasn't an option. Making too many simple mistakes would result in missed assignments.

Limiting developers to one compile a day wouldn't work these days, but I wonder if limiting the number of compiles might result in developers spending a little more time thinking about their code, their logic, and writing applications with a little more care? 

Or would they follow the same process they follow now, only slower?

Steve Jones

The Voice of the DBA Podcasts

• Watch the Windows Media Podcast - 18.4MB WMV
• Watch the iPod Video Podcast - 16.2MB MP4
• Watch the MP3 Audio Podcast - 3.3MB MP3

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: or now on iTunes!

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:

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

In SQL Server 2008 R2, you are setting up a subscription with sp_addsubscription. What is the default type of subscription if you do not specify a type in the parameter list?

By Steve Jones

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

This question is worth 1 point in this category: Replication. 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 Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

If you use the HAS_DBACCESS() function in SQL Server 2008 R2, which of the following will return a 0 for a particular login? (choose 2)

By Steve Jones

Answer:

• The user does not have access to the database
• The database is suspect

Explanation: If the login does not have access to the database, a zero is returned. This also happens if the database is suspect, offline, or in single user mode and another user is in the database. If the database is in single user mode, but no other users are in the database, a 1 is returned if the user has access.

If the database name parameter in invalid, a NULL is returned.

Ref: HAS_DBACCESS() - http://technet.microsoft.com/en-us/library/ms187718.aspx

» Discuss this question and answer on the forums

This book shows how to use of mixture of home-grown scripts, native SQL Server tools, and tools from the Red Gate SQL Toolbelt, to successfully develop database applications in a team environment, and make database development as similar as possible to "normal" development.

Get your copy from Amazon today.

Display row value as a TABLE column using SQL select statement

Gulappa Vijaya Kumar from SQLServerCentral.com

Display row value as a TABLE column using SQL select statement More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Merge a 3 tables data in one view - I have 3 tables as below, USE [Dummy_New] GO /****** Object: Table [dbo].[College_CutoffMaster] Script Date: 01/11/2012 13:15:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE...

databases file space utilization 100%? - Hi All, Today i monitor my databases i found some of my databases are utilized 100% file space. What is the...

CPU Utilization on 64 bit SQL Server 2005 - Hi, I have SQL Server 2005 64 bit running on Windows 2008 64 bit. I am continuously getting average CPU more...

Database mail - I have configured database mail,but send test email is not working...in another sever i configured and it is working fine.In...

SQL Server 2005 : Backups

How take backup without overwriting or Appending the old files - Hi, I want to schedule back up [i]DB[/i] in ms sql server 2005 using maintainence plan wizard. I want to...

SQL Server 2005 : Business Intelligence

New to SSIS - Hi, Designed SSIS package. Now i need to move this package from Devlopment to Staging environment. Can anyone share the detail process...

Import a text file into a column - Hello All, I am trying to import a text file which is unstructured. 5 20120103 recon een7890987 yuioiuy 0987657890988888790 uiioopppjjnnn 87909877666 89098u8uiopo...

SSAS VS SSRS - Hi Can some one differentiate between SSAS and SSRS? Using SSAS also,we can generate reports right ? Thanks in advance

Saving Multiple RDL's to local computer - Good morning, I have a folder on a reporting services website and I would like to pull the RDL's all down...

SQL Server 2005 : CLR Integration and Programming.

Creating a round robin table with SQL & VB - I am trying to create a dynamic round robin scoring sheet with SQL stored procedure & VB and suggestion on how...

SQL Server 2005 : Development

unable to select the column - when i am using bellow query i am getting problem. select * from table --> no issue table result is showing. where as...

SQL Server 2005 : SQL Server 2005 General Discussion

Error Need Help ASAP!!!! Error Code 901 - I am getting error message and can't seem to resolve it. On a Windows XP SP 3 computer. My sql...

SQL 2K5 Cluster on Server 2008 x86 - Hi, I have stood up a Server 2008 cluster with 3 ISCSI LUN's, One for the cluster resource, one for the...

Removing commas and quotes from numeric fields in csv file using SSIS - I am creating SSIS package which reads data from a csv file and stores in SQL Server database. There are...

Auditing Stored Procedure calls - I'd like to know how often a stored procedure is called, also useful would be the time, which user made...

How do I get the table definition ? - Team: Is there a way for me to get the definition of a table via a sql statement. Now I was able...

OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active." - I have linked a local SQL 2005 Standard Server 32-bit Unclustered to a remote SQL Server 2005 Standard 64-bit Clustered...

SQL Server 2005 : SS2K5 Replication

Replicating child table but not the parent table - Pretty basic(stupid) question, is it possible somehow to make a child table (the one with foreign key constraints) a publication...

SQL Server 2005 : SQL Server 2005 Performance Tuning

SQL 2005 Standard Edition 64bit not using all available memory - We have a rather busy production server which is running SQL2005 SE 64bit on Windows 2003 64bit. The server has...

I'm using EXECUTE sp_executesql in my ASP.NET - My current code as following, [code="sql"] Dim sqlNm As String = "" Dim sqlNm2 As String = "" Dim strType As String = "" Dim newSQL As String...

How to read LDF file? sql 2000 - How can I read LDF file? My table is missing from database, and I want to know how it has...

SQL Server 2005 : SQL Server 2005 Integration Services

Relative Paths in Task 'execute task' - Hello, what I mean with relative paths: I want to setup the WorkingDirectory or rather the variable "Executeable" so that the...

Excel Connection string - Dynamic string - Hi All, when i am trying to give the connection string as dynamic for excel as below: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::FilePath] +";Extended Properties=\""...

"Access to the path is denied" error in SSIS package - Hi All, I am facing really strange issue. I am working with a customer for who, we have created a SSIS...

SQL Server 2005 : T-SQL (SS2K5)

execute string - Hi All, I have a very interesting situation. I am pulling following string (see Coulmn1) using substring,replace..etc. from the varchar(5000). I...

Temp Table Name confusion with Nested Procedures? - Normally I ask this type of question on Ask SSC, but I thought I'd try one here for a change. I...

SQL Server 2008 : SQL Server 2008 - General

Replication Lactency with out Tracer Tokens - Hi Im looking for ways to view replication latency using script and without the use of tracer tokens does anyone have...

Server side trace to a table - The link below helps me to create a server side trace to a file. I'd like to send the results...

Restore script not working - Hi all, I have been working on this script all day trying to get it to work. Just can't figure it out....

Merge Replication Error - I have a SQL server 2005 server with a merge publication of 6 articles all of which are tables which...

SQL Server SSIS issue. - hi guys... SQL Server 2008 R2 Standard Edition 64 Bit TITLE: Microsoft SQL Server Management Studio ------------------------------ Failed to retrieve data for this request....

.ldf deleted and now cant attach database - Hi All, I had deleted my .ldf as it was getting far too large! and then when i went back to...

DR setup for a customer server - One of our customers has requested to setup a Disaster recovery site.. Their motive is just to cover up for some...

DB MIRROR Error - The server network address "TCP://192.168.147.24:5022" can not be reached or does not exist. Check the network address name and that...

Execution Plan - I executed this query and checked the execution plan and it is showing segment and sequence project. What does SQL...

need help with CASE stmt and table joins -

Update takes too long and freezes the server - I selected the data, it took micro seconds. But, when running an update statement (two columns) it takes too long....

Authentication mode in SQL Server - Hi everybody, Most of the Servers in our Company are using SQL 2008 editions with the mixed 'SQL Server and Windows...

How to create database from .mdf file only - Hi, I have installed SQL Server 2012 and downloaded sanple database from the below link, which has AdventureWorks2008R2_Data.mdf for download. http://msftdbprodsamples.codeplex.com/releases/view/55330 Now, how...

What is the best way to access a web service - What is the best way to access a web service from SQL Server 2008 R2? I have read about CLR...

How we dealocate the memory of a row - How we dealocate the memory of a row in a table in sql 2008 R2. please show the query if...

Is there a quick and dirty way to format varchar(max) SQL Server output into HTML without using Reporting Services? - Is there a quick and dirty way to format varchar(max) SQL Server output into HTML without using Reporting Services (which...

Selecting TOP PERCENT based on GROUP ? - Hi, I wanted to know if we can select percent of records for each group? something like :- SELECT TOP 30 PERCENT...

HOW TO FIND A VALUE IN ALL COLUMN OF ALL TABLE IN A db. - HI ALL, As i Have a task to find 9 digit value in all column of all table in a DB...

Relational design question - I am working on a project, and I've hit a small-ish bump in the road. I have to link a...

SQL Agent Job succeeds even though step should fail - Does anyone know why a Transact-SQL job step that executes a stored procedure and returns a "Query timeout" response continues...

merge statement and cte(common table expression) - Hello all, i am new to these topics can any body breifly explain about merge statement and cte. i am confusing while learning...

Running a sql server 2008 job to back up to network folder - I'm trying to schedule a back up database task to backup some specific databases to a network folder but kept...

SQL Server 2008 : T-SQL (SS2K8)

SQL Server equivalent to DB2 HEX - Greetings all. I'm using SQL Server 2008 R2 Express, and I've got a linked server to a DB2 mainframe. There...

Quick Opinion on @@ROWCOUNT vs EXISTS - Hi, I'm editing a large stored procedure (very few comments) which has 3 methods (so far) of getting or creating...

Returning results for the previous year - I have the following script: WITH A AS (SELECT c.owneridname as BSC,z.Tpl_TotalGWP as Total2012,a.tpl_traderref as TraderRef,a.name as CompanyName,LEFT(z.Tpl_Month, 4) AS Year,...

Grouping - I have the following data: User Salesperson A 23171 320230.00 Salesperson B 11782 199584.00 Salesperson C 16769 14437175.00 Salesperson D 17191 -635580.00 Salesperson A 16844 122000.00 Salesperson B 18708 159775.00 Salesperson D 10969 225295.00 Salesperson C 18681 73040.00 Salesperson D 26480 768201.00 Salesperson A 10310 -111325.00 Salesperson B 17291 -460550.00 Salesperson B 11116 3

if 2nd year and same Programme for same Student, then return no results - /* Objective: if report is for Year 2009, and 2009 is the first registration year, then return Programme for student. if report is...

Need to audit update without cursor - Hi All, I have a client that has separate HR and Payroll systems each with their own database. There is a...

Combine different periods into one - Hi, I want to combine different periods into one, I don't know how to explain it properly, so I try...

Returning a column per date in a range - Hi I need to take a set of records for people with hotel check-in/check-out dates, and display a table that has...

How to insert multiple rows into a table with identity column - Hi, I am having trouble inserting the multiple rows into a table with identity column. The error i get is... An explicit...

Recusion on Group and their subGroups- how can I solve this problem? - I have to pass a list of groups to a function which return those groups and their sub-groups till nth...

Read XMl file to SQL table - I am trying to import xml file to sql table. Any help please! m new with XML, obviously! Sample XML file [code="xml"]<CustomerDetails> ...

SQL Server 2008 : Working with Oracle

Convert date format in SQL sever report(BIDS) with Oracle date format - Creating a report with Oracle 9i backend database. Setup two parameters @startdate @enddate Select * from mytable Where mytable.date Between @startdate And @enddate I received error message...

SQL Server 2008 : SQL Server Newbies

comparsion of users between two servers - Can we compare users between two servers, for ex: users in production and test environments to see users in the...

Retrieving rows with unique data in certain fields - I have yet to come up with a simple but fast way of retrieving rows from a table that have...

Add Convert for UTC time - Okay, I have to add a convert for the UTC column and I have no idea where to put it,...

Help with Counts - Hello All I have a large database which I need to run a lot of counts on. Using the following code: [code="other"]create...

Count fields that do not meet specified condition - [CODE] VID SERVERIP BASESCORE EXPLOITABILITY SCANCOMPLETE 51 192.168.0.1 5 H some date 31 192.168.0.1 9 H some date 42 192.168.0.2 3 POC some date 64 192.168.0.2 3 H some date 85 192.168.0.3 5 POC some date 26 192.168.0.4 9 H some date 37 192.168.0.4 9 POC some date [/CODE] Requir

Timed Out when calling from VBA function - I have a vba function below. I call a stored procedure but it times out. Even if I set the...

SQL Server 2008 : Security (SS2K8)

Logon Triggers. A good idea? Or a nerve-racking exprience? - Because of security issues, I was requested to keep track of active login accounts so that unused logins can be...

Failure audits - Hello, I have enabled SQL Server Audit to write to the Application event log. Seems to be working fine but...

SQL Server 2008 : SQL Server 2008 High Availability

SQL server 2008 R2 log shipping wizard not showing backup compression option - log ship wizard in 2K8R2 Std not showing compression option,but compression column is present in log_shipping_primary_databases. The option should still...

SQL Server 2008 : SQL Server 2008 Administration

Sql agent job step - tsql command - I need to figure out how to cause a job to fail and do an alert based on whether a...

RAID recomendations - I'm starting to study for my Admin MCITP for 2008 and the first thing I have been hitting is the...

SQL 2008 R2 Report services won't start - Hi, I have today installed a copy of SQL Server 2008 R2 developer edition on my 64 bit windows 7 PC...

syspolicy_purge_history failing - Hello, I have a clustered (active-passive) SQL Server cluster running under version 10.0.4000.0 (SQL 2008 SP2 64-bit Enterprise Edition) where the...

Monitoring Windows Event Logs - I am curious about which tools people are using to monitor Windows Event Logs on servers hosting SQL Server.

How many databases on one single instance - Specs: VM-ware Dual core 8 gig memory 64 bit hard and software (Windows 2008 R2 + SQL server 2008 R2) How many databases can i run...

Powershell: "a positional parameter cannot be found that accepts argument 'system.object '" - Hi, I am trying to insert some data from a PS command into a table, but getting this error: "a positional parameter...

Back-up strategy - What would you recommend as the ideal back-up strategy for SQL Server?Currently i do daily full back-ups and burn them...

Failed "ALTER DATABASE...REMOVE FILE" leaves sys.database_files missing a file - From SSMS I removed a secondary file (named tempdev2) in the PRIMARY filegroup of tempdb and lowered the maximum size...

Job Ownership: No object was found with the name "[sa]".... - When I create a job it typically creates the job with my account as the owner, e.g. domain\login. The jobs...

Career : Certification

failed 70-668 exam - I failed my 70-668 exam today,only got a score of 532,I studied the online materials but many of questions are...

Programming : General

need help with CASE statement and table joins - Hi, can someone help me figure out the best syntax for this query? Much appreciated - SELECT sa.accountno AS accountno, s.saledate...

Correct Age calculation - We are using the following to calculate age: It doesn't seem correct ? Any ideas would be great? Thanks Joe INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA) SELECT TAG_NAME = 'CUST_AGE',...

SQLServerCentral.com : Anything that is NOT about SQL!

A new year. None of us are getting younger. How do you view things differently than you did in your younger days? - On a personal level, I'll be turning 60 this year and I've already lost many more of my peers to...

Censoring the Internet - The US Congress is considering America's first system for censoring the Internet. Despite public outcry, the Internet Censorship bill could...

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 2005 Development

Trailing Spaces on Grouped Column - I was Grouping by City in a Report but the Column had Trailing spaces so I modified the Sp to...

How to Ignore Report Parameter - I modified a Stored Procedure to allow the parameter to have a Null Value. I checked the Allow Nulls in the...

Create Paramater Filter by State - I'm trying to create a parameter to filter a Report by State. Any help would be greatly appreciated.

Database Design : Design Ideas and Questions

Filtered Foreign Key - A conceptual thought: We have now (from SQL Server 2008) got filtered indexes. What do you think of the idea...

Database Design : Hardware

Possible to install sql server 2008 to Windows 2000 server opeation sys - Hi, Helo help Can i install sql server 2008 on Windows 2000 server system,. Please anyone advise me thanks Jerry

Data Warehousing : Analysis Services

New partition takes a long time to process (AS 2000) - anyone still using analysis services 2000? each year i add a new partition to our cubes for the new year....

Client-side cube browsing application - Does anyone have a recommendation for a thin client-side cube browsing application with similar abilities as BIDS but none of...

Sybase varchar(10000) to SQL varchar(Max) getting problem in SSIS package only for 64 bit Machine. - Hi All, i am transfer the data Sybase to SQL that time i am getting problem where ever varchar(max) is there...

Data Warehousing : Data Transformation Services (DTS)

Adding a column to a table at import (run) time. - I have an SSIS package that I used to import excel data, I need to add a column to the...

Article Discussions by Author : Discuss Content Posted by Brian Knight

Transparent Background Color in SSRS Textbox - I get a warning similar to this: [i][rsInvalidColor] The value of the BackgroundColor property for the textbox ‘textbox22’ is “Transparent”, which...