A community of more than 620,000 database professionals and growing

Introduction to DTS in SQL Server 2005

Vinod Kumar from SQLServerCentral.com

DTS was one of the true innovations in SQL Server 7. SQL Server 2000 greatly enhanced this tool and SQL Server 2005 makes it truly a programming environment, well beyond the ETL functions of DTS in SQL Server 2000. New author Vinod Kumar gives a brief introduction to this new environment. More »

Designing an Entity Data Model

Additional Articles from MSDN Communities

The Entity Framework is an exciting new technology being developed for ADO.NET. It allows developers to view data using a logical model instead of a physical model, offering more flexibility.More »

Modifying Data in T-SQL

Additional Articles from JumpStartTV

In this video, Kathi continues her beginner series on T-SQL where she now shows you how to modify data in SQL Server using the DELETE, UPDATE or INSERT statement. If you're new to T-SQl, this video is perfect for you to quickly ramp up your skills. More »

From Last Year - Copyright at SQLServerCentral.com

Press Release from SQLServerCentral.com

Just to set the record straight, if you submit something you keep the copyright. More »

This Friday is an easy one, but a very important one. At least I think it is.

The other day I was querying people about patches and Service Packs. I asked about SQL Server 2005, SP3, since we've had quite a few cumulative updates and issues with SP2. I expected that there would be an SP3 coming soon (SP2 was in Feb 2007) that would be more heavily tested, correct the issues from SP2, and be a stable release. Hopefully without an SP3a!

Imagine my surprise when I was told there was no SP3 coming. (<disbelief>What? Huh?</disbelief>)

I asked and was told that customers aren't asking for SP3. That's the feedback to the product groups; that customers haven't been asking for patches to fix problems. So I'm asking the question:

Do You Want SQL Server 2005 Service Pack 3

My feeling is that most people out there, most Microsoft customers, wait for Service Packs, test them with their own applications, and roll them out if they prove stable. My guess is that most vendors also heavily test Service Packs, not cumulative updates. My feeling is that everyone out there is expecting SP3 and doesn't think they need to ask.

My answer is "YES!!" (expletives removed). I want SP3 for SQL Server 2005.

And I want SP5 for SQL Server 2000. If you're ending support this year, the least you can do it roll up all the QFEs/GDRs/Hotfixes that have been built for SQL Server 2000, test them, and roll out an SP5. Heck, SP4 had issues from the go with AWE.

If you don't release SP5, or an SP3, it looks to me that you've essentially ended support for SQL Server 2000 already.

I understand the need to move forward. I don't like the pace or structure in place, but I get you need to make money. However we've also paid you money and you've agreed that it comes with a support lifecycle. So give us that support and release the Service Packs we've grown to expect.

Steve Jones

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

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

• Windows Media Podcast - 24.1MB WMV
• iPod Video Podcast - 19.9MB MP4
• MP3 Audio Podcast - 4.2MB

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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

» To submit a tip, rant or editorial, log in to the Contribution Center

Today's Question:

Which versions of SQL Server 2008 will support Hot-Add CPUs?

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

This question is worth 1 point in this category: SQL Server 2008. 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.

Given the following table variable definition:

DECLARE @Vendors TABLE (VendorPK int, VendorName varchar(50), VendorStatus char(1))

which of the following are NOT valid statements?

By Chad Crawford

Answer:

• TRUNCATE TABLE @Vendors
• CREATE INDEX IX_VendorName ON @Vendors (VendorName)

Explanation: Although table variables can be used in most places where temporary or real tables can be used there are some exceptions. In this case, the TRUNCATE TABLE and CREATE INDEX statements will result in an error. Table variables do not honor TRANSACTION statements, but putting them in a transaction does not result in an error. See also http://msdn2.microsoft.com/en-us/library/ms175010.aspx

» Discuss this question and answer on the forums

Different Date Formats

Vinuraj R from SQLServerCentral.com

You Can Find Some Date Functions and extracting the different Date Formats using the Convert and CastMore »

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

SQL Server 2005 : Administering

SP1 and SP2 on same server but different instances? - We have a server with two instances of SQL Server 2005. Both the instances right now had SP1 applied on...

AWE Counters extremely high - I have SQL 2K server with 8GB memory and 11 databases (3 of which are over 15GB)...we'll call server A,...

MemToLeave (Non-Buffer Pool) - Does anybody know a good way to identify what is fragmenting my MemToLeave area? I am using LiteSpeed SQL 2005 on...

Audit listing of Select Statements - I'm looking for a method for monitoring/auditing browsing. I'm aware of Lumigent, but after reviewing the hooks that it needs into...

Copy and rename a file - Hi, I would like to copy a flat file from one location to another and rename it. Can i do it...

Running a production SQL environment on a virtual server - Hi to everyone, In order to reduce costs, we are looking at setting up some virtual servers. As the concept if...

Extended Stored procedures - SQL 2000 - Hi All, Are any of the following stored procedures used for legitimate production purposes on any of our SQL 2000 servers?...

Getting SQL Server 2005 Database out of Single-User Mode - Hello, I put a SQL Server 2005 database into single-user mode using Management Studio in an attempt to force a restore...

SQL Server 2005 : Backups

transactional log - If i need to truncate/shrink the transaction log file : can I use dbcc shrinkdatabase(' ',truncateonly) instead of using dbcc shrinkfile(2,truncateonly).earlier...

Intermitant Backup error - Run backups to a repository server with 3 strips, stats 30 BACKUP DATABASE [SERVER NAME]TO [DEVICE NAME], [DEVICE NAME], [DEVICE NAME]...

SQL Server 2005 Backup Third Party Tools - I am interested in obtaining a tool for database backup/recovery with compression and encryption that is cluster aware. I am...

Automated backup - Was unsure where to place this post. Windows Server 2003R2 w/ Sp2 MS SQL 2005 I currently use MS SQL 2005 to host...

need to backup database schema - I've got a large reporting database. I don't need to back this up as its data can be recreated. However,...

SQL Server 2005 : Business Intelligence

License for Business Intelligence Platform with SQL Server 2005 Standard Edition - If you buy license for one SQL Server 2005 standard edition along with 5 Client access license. Can Business Intelligence Platform...

Cannot Deploy SSAS database - I have been trying to use BIDS to build an SSAS database and cannot deploy the database if there is...

Dimensional DB Design Question - Dear all, I am familiar with Dimensional Modelling but here is a type of question I have not had to address...

Want to learn DW concepts : FACT tabels, Dim tables, CUbes - Hi, Can any one help in learning datawarehouse concepts . I work with SQL Serever DBA/Development stuff. I want to learn DW...

SQL Server 2005 : Data Corruption

Warning: Fatal error 602 while trying to delete from linked server - Hello! This is not directly data corruption related post, but I am hoping someone is able to assist. I was...

SQL Server 2005 : Development

Recursion??? - I need to write a stored procedure in SQL 2000. I have a tables fld and fld_term, relashionship is fld.id = fld_term.id fld...

Oracle DB stuff - Hello, guys how are you doing? I am familiar with SQL server 2000 database. and I am interested to learn...

SQL Server 2005 : SQL Server 2005 General Discussion

Copying SQL Server Agent - Jobs - Is there a script that enable the copy of specific sql server agent jobs from one database to another. Thanks...

required the way to update the System Catalog in Sql Server 2005 - In sql server 2005, I am trying to edit the type / replinfo values in sysobjects, I got the error message...

MS Sql Server 2000 to MS Sql Server 2005 - Hi, I've been using MS SQL Server 2000 for years - recently we upgraded a few servers to MS SQL Server 2005....

Data Modelers - Alternatives to Erwin - I have been asked to find data modeling tools to serve as an alternative to Computer Associate's Erwin. I downloaded...

Data Import/Export Wizard - Can anyone tell me why the data import/export wizard is so much faster than using T-SQL to shift data from...

Send SQLReport Snapshot through email (database mail) - I have setup database mail, in SQL2005 reporting service, Am able to send Test mail properly. I deployed a Report...

Query - Good practice of a query - Hi, Most of the SPs in our db written in a old format style and i dont thing that is the...

List of SQL 2005 features incompatible with level 80? - Does anyone know of a comprehensive list, or link, documenting most/all the features for SQL Server 2005 that cannot be...

SQL Server 2005 : SQL Server 2005 Security

DBMS Software monitoring - We have recently been awarded a government contract and now must meet new security standards. One of these new requirements...

SQL Server 2005 : SQL Server 2005 Strategies

Database design - Hi We are planning to implement a database which etl's about 2GB of data daily. Estimating the overall size on the...

SQL Server 2005 : SS2K5 Replication

Best Replication Strategy for this scenraio ? - Hi All, We have a Data Warehouse on a SQL 2005 Enterprise 64 bit Clustered SAN Environment. We have a need...

Adding Index to a replicated Table - Hi Guys, We have a merge Replication in place that replicates a database across 2 sites. I want to add an identity...

SQL 2005 merge replication across non trusted servers - Is it possible to set up merge replication between two servers that are not on the same network. We are...

SQL Server 2005 : SQL Server Express

Problem with Restore of Transaction Log - Hi, I'm writing a c# utility to syncronize 2 SSEE (only one direction is allowed) based on transanction log backup. My problem...

SQL Server 2005 : SQL Server 2005 Performance Tuning

using dm_exec_sessions to count reads? - I am trying to use sys.dm_exec_sessions to check how many reads I just did in TSQL... I can use STATISTICS IO...

OPTION (MERGE JOIN) "fixes" slow-running queries in SQL Server 2005? - The team I work on does nothing but data conversions. We convert data for new customers from their legacy system...

Different I/O performance each datafile same LUN - Hello guys We have a strange case. We have a SAP , on Windows 2003, using SQL Server 2005, and using a...

why is my fragmentation so high on non-leaf index levels - I run sys.dm_db_index_physical_stats daily on almost every server and db in our environment and have so for months. i've always...

Memory pressure issues - Hi everybody, We are running a Win2003 SP1 Server running SQL 2005 SP2 9.00.3042.00 Enterprise Edition. We have 16 processors and...

SQL Server 2005 : SQL Server 2005 Integration Services

DELETE Rows In Excel - How to delete rows in an excel worksheet? I have the following code in the exeutesqltask but i keep getting a...

Execute SSIS package from VB6 - I'm starting with SS2k5 and i'm modifying an old program in VB6. I need to execute a SSIS package to...

Select rows from table b not in table a? - I have made a small amount of progress with learning SSIS. But I am struggling with it more than I...

SSIS scheduled package works one day, fails another. Nothing changed. - I have 3 scheduled packages which are identical except for the connection manager names, as they connect to different databases....

SSIS packages\jobs connecting to Linux DB2 fail randomly with message:SQLAgent job failed. process exit code was -10737418 - Hello, I can not get thru this issue and I found other people complain about it, but there is no solution. Please...

SQL Server 2005 : T-SQL (SS2K5)

SubQuery returning more one record - I have the following code: -- Declare the Downline Recursive Query With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel, ConsultantXId,SponsorXID,Active, DeactivationDate,BumpupDate,NACDate,CurrentLevelAchieveDate, CurrentLevelXID, StatusID) AS ( -- Anchor member defintion SELECT A.ConsultantID ,A.EffectiveDate ,A.FirstName...

Simple OPENROWESET questions - I am running the following statement: SELECT cast(field1 as varchar(10)) as field1, cast(field2 as varchar(10)) as field2 INTO file1table FROM OPENROWSET('MSDASQL','Driver={Microsoft...

Simple LEFT JOIN gives surprising result - Why? - Programming in SQL is a humbling experience. I thought I knew just what this would return: [font="Courier New"]create table a (a1...

Disable print in a stored procedure - TSQL - Example: Print 'This is my result' - I have a stored procedure that has several statements like print 'This is the result' These statements are causing errors with...

Please help - Dear All, I have a two table Users && Roles Roles contains following columns Roleid, Rolename,InboxEnabled Users contains Userid,Region,Branchid,Roles The Data in the Roles Table is Admin -Admin...

Help ! XML problem (please see this updated version) - My problem : I have 2 tables Table "Company" Company_ID Member_IDs 101 201 Table "Member_info" Member_ID Member_name 1011 Member_1011 1012 Member_1012 I want to have : Company_ID Member_ID_name 101 201...

Last Field updated - Hello everyone, I have two tables, a product table and a product history table. I want the product history table to...

Counting in rows - I've never done anything quite like this and I'm having a major mental block, so any help appreciated. I have...

Delete files olde than 48 hours based on filename, not date modified. - Hi, Every hour i have files that get copied from one location to another and renamed with adding a date...

Nested Commit while rollback transaction - I know that I have done this before, but cannot find my code. I am debugging a complex set of...

IF Exists and a clause questions - Hi all, I am new here, and glad that we have a good community like this. I have 2 simple questions: First...

help-how to generate date backward from end to start - [b]how to generate date backward from end to start[/b] like this [code] begin -- loop to insert date backward while @end_date>=@start_Date begin INSERT INTO @tb_temp [/code] from middle of the...

The SQL * Wildcard - So I was on a interview at Microsoft and they threw me up in front of a whiteboard. They described...

SQL Server 7,2000 : Administration

SQL Profiler - I am trying to run a trace I have added everything i wanted and set duration to greater or equal...

Problems accessing SQL Server when NT account has been renamed - Hi Everybody, I wonder if anybody can offer any advice on the following. We use NT authentication to connect to SQL...

Adding higher capacity disk to the database server - Hi We have a database server configured in RAID 5 and 1. All the data files are on a disk array...

Question Regarding Index - Hi i was given a task to re-index indexes in some tables. To spot if a table has an index...

how to fiind data inside of a string - Any help is greatly appreciated. I'm using SQL server 2000 and a have a field that contains a file path...

SQLCLUSTER @@servername = NULL - People my master on the cluster of 2 nodes has being restored from the backup cluster with two nodes as...

Memory sql 2000 win 2003 problem - I have a new windows 2003 R2 box with 48 gig of memory. I am running the 32 bit version...

sql mail not notifying - sql mail is not notifying me when a job fails. Anyone know how to troubleshoot it? Looks like the mail icon...

SQL Server 7,2000 : Backups

huge .BAK FILE - I have a .bak file of 72gb. But my database size is only 32gb, I got this value from sp_spaceused? Anyone...

SQL Server 7,2000 : Data Corruption

CHECKDB Error - I am getting CHECKDB error in one of my database. The DBCC results are as follows: Server: Msg 2511, Level 16,...

SQL Server 7,2000 : General

08S01 [Microsoft][ODBC SQL Server Driver]Communication link failure - Hi have Citrix server (specific app) connecting over GB ethernet via ODBC to a windows 2003 sp1 server running SSQL...

Error Messages - Hi, I'm using DTS and need to catch the error message. how to retrieve the error messages. i have used workflow...

HELP - Weird ADO problem - Hi, I hope someone can help me with a weird problem I'm having. Towards the end of last week a...

DTS package to pass data between two databases - I want to monitor file growth across a number of databases on different servers from a central point. I'm using...

replacing multiple characters in a column - Hi, please help me.... I couldn't find answer for this anywhere... I have a table with a column having data type...

SQL Server 7,2000 : Security

Limit Access to Databases in Enterprise Manager - I have created a user account that has access to a specific database. When that user logs in via Enterprise...

SQL Server 7,2000 : SQL Server Newbies

help needed with stored proc.... - Hi guys, I am using sql server 2000 I am having an excel attachment which i need to import into my database...

DTS import textfile AND inputbox data for just 1 column to add - I'm quiet new with DTS and I managed to make an import with DTS from a textfile that append to...

odbc connection has two instance of the same server, trying to get rid of one - I am trying to create a odbc connection from my pc to a sql server eg. SVR16, but when i...

SQL Server 7,2000 : Performance Tuning

Microsoft: "Don´t use the prefix sp_" - A few days ago I visited one of Microsoft's "The Architect Blueprint" event in Sweden. Suddenly the "Architect Evangelist" said...

SQL Server 7,2000 : Replication

Replication__Distributor - hi all I have a replicatoin problem, I have single publisher and multiple subscribor. Now one of our server gives...

SQL Server 7,2000 : T-SQL

Setbased solution required. - Hi.i am trying to formulate a setbased solution which would speed things up dramatically: As part of a large report, I...

Need help with query - Hello, I have a column in a table that stores time in milliseconds and I want to be able to sum...

How to solve this? - I have this query select distinct p.first_name ,p.last_name ,'class level' = case when a.credits + t.earned_credits between 1 and 32 and a.program ='ft'then 'F1' when...

creating new users and granting permissions - I know that I can do this to add a new NT User to the Database and grant them access...

Set-Based Solution Possible? - I need to be able to summarize the data for a report ordered by StaffName allowing for multiple rows per...

Career : Certification

70-443... a grinder! - Hello all, you may have seen my post below regarding the tough time I had at the test center. Anyway,...

Career : Employers and Employees

The old IT conundrum (how do I get from here to there) - I work in a large shop and I'm currently doing client work. Building universal xp image for all hardware, group...

SQLServerCentral.com : Anything that is NOT about SQL!

Ken Henderson - A friend at work just pointed me to this post at the [url=http://blogs.msdn.com/sqlrem/archive/2008/01/28/ken-henderson-we-will-always-remember-you.aspx]SQL Server Manageability Team Blog[/url]. Ken Henderson has...

I am so depressed after I work on Question of the Day - I start my new job next week and the company is using SQL Server 2005. Since I have little experience...

SQLServerCentral.com : SQLServerCentral.com Website Upgrade

Proposed new section for the forums. - Steve, et. al. - I've recently noticed a lot of posts regarding consolidation, virtualization, scalability, etc. pretty much scattered throughout the...

SQLServerCentral.com : Suggestions

How to bookmark a post? - Is there a way to bookmark a post as my favourite post within SSC, some option like the briefcase? I...

Reporting Services : Reporting Services

"The underlying connection was closed: Could not establish secure channel for SSL/TLS "after changing IP address - Over the weekend we change the IP for our SRS webFarm server. from 999.999.0.17 to 999.999.10.17. The reports are still rendering fine,...

Table within a Table with different datasets - I have a report that has a table with another table within one of the textboxes. The table within contains...

Forms Authentication - Has anyone out there done this? - I've got some reports that are to be accessed through a web-based application. I've been told that the only way...

Data Regions rather than SubReports ??? - In many articles I've read it is suggested that a data region be used rather than a subreport fro performance...

Forward Dependencies are not valid. - I am working on reports from our HR system that allow a manager to only see their own direct reports...

Database Design : Design Ideas and Questions

Would you use sql_variant? - I'm working on a database design for an ODS / Data Warehouse. I have been asked to build a design with...

When to use cascade deletes/updates - One thing I'm not clear on is when to use cascade deletes/updates between related tables. I understand the maintenance benefit...

Data Warehousing : Data Transformation Services (DTS)

MAPI Logon Failed - I have a number of DTS packages sat on an SQL 2000 server. Almost all of them have send mail...

Error: Failing rows exceeds max specified - Source: SQL Server 2K (Win2k) Tool: DTS Destination: DB2 (Linux) Error Source: Data Pump Error Message: The failing rows exceeds the maximum specified Error Code:...

Microsoft Access : Microsoft Access

Upgrading from Access to Sql Server2005-vba functions - We are in the process of upgrading from MS Access to Sql as there have been instances when records have...

Article Discussions by Author : Discuss Content Posted by Peter Larsson

Pivot table for Microsoft SQL Server - Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp