Got this when I ran my sp__updatestats procedure in a SQL Agent jobstep, but it worked in Query Analyzer. The reason? The default settings are probably different in QA rather than external connections similar to SQL Agent.

Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]

The solution was to recreate the procedure that ran UPDATE STATISTICS using a SET command within the procedure. 

CREATE PROCEDURE dbo.sp__UpdateStats
AS
SET ARITHABORT ON 
SET QUOTED_IDENTIFIER ON 
...

We started to get this problem after I added a computed column to a table. 

Finally getting around to replacing our DTS ActiveX scripts that make DTS Packages dynamic. Passing in variables on command line, allowed us to change our source, destination, remap transformations etc.

Getting started in SSIS.

1) Start with this list of Best Practices.
2) Follow this link to a great tutorial about using SSIS control flow script task.

Here's an example of how to create a delimited list, using a field of rows of data and COALESCE.

NOTE:
Don't be surprised when ORDER BY doesn't work.  

'SQL Server query processor builds an different execution plan when expressions are applied to columns in the query's ORDER BY clause, than when those same expressions are applied to columns in the query's SELECT list. The decision made by the query processor is based on the cost of possible execution plans.'

You bet!

1. On your SQL 2005/8 instance create a linked SQL 2000 server
2. Run your SQL 2005 query using something like PIVOT or UNPIVOT against the SQL 2000 linked server table.

SELECT *   
FROM  OPENROWSET (
  'MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=E:\DATA;SourceType=DBF;',
  'SELECT  * FROM MyFreeTable;'
)

Once the correct drivers were installed we could run the query, above, and create ODBC connections. Then, we were getting this error, but only when not running directly on the server itself.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'hprofile.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

So, our OPENROWSET queries and the linked server queries could be run locally to the server. There must still be some proxy permissions or security something we still need to set. 

Still, another error we're getting is this: 
Cannot get the schema rowset 'DBSCHEMA_CATALOGS' for OLE DB provider "SQL Server" for linked server "(null)" -- huh?

Don't forget that now that it is in the tempdb table you can script out a field descriptor or a CREATE table statement. Use that for a permanent import table elsewhere.

USE tempdb
GO
SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$
--
-- Now use Query Analyzer to script out the table in tempdb. Here's a sample script.
--
CREATE TABLE [dbo].[MyEXCELSheet] (
[CustomerID] [nvarchar] (255) NULL ,
[EmployeeID] [float] NULL ,
[Freight] [float] NULL ,
[OrderDate] [datetime] NULL ,
[OrderID] [float] NULL ,
[RequiredDate] [datetime] NULL ,
[ShipAddress] [nvarchar] (255) NULL ,
[ShipCity] [nvarchar] (255) NULL ,
[ShipCountry] [nvarchar] (255) NULL ,
[ShipName] [nvarchar] (255) NULL ,
[ShippedDate] [datetime] NULL ,
[ShipPostalCode] [float] NULL ,
[ShipRegion] [nvarchar] (255) NULL ,
[ShipVia] [float] NULL
) ON [PRIMARY]
END
GO

Easy linked server setup for a one hop query.

PC (A) to SERVER (B) will work.
PC (A) client tools to SERVER (B) and four-part queries from SERVER (B) connection to SERVER (C) will not work without some kind of delegation.

See "How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers" and this "Allow a computer to be trusted for delegation for specific services"

-- Add a linked server
EXEC sp_addlinkedserver 
@server = N'SQLDEV',
@srvproduct  = N'SQL Server'
GO
-- And some options
exec sp_serveroption 
@server = N'SQLDEV', 
@optname = N'data access', 
@optvalue = N'true' 
exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
GO
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'SQLDEV'
,@useself = 'TRUE'
--     ,@locallogin = 'sa'    -- this doesn't seem to be required.
GO
EXEC sp_helpserver 'SQLDEV'
EXEC sp_helplinkedsrvlogin 'SQLDEV'
GO
-- This query will work from one hop. e.g. If you are querying directly from one local server to another.
-- You can't do this by Query Analyser on PC A, logging into server B, and running the 4 part query agains
-- Server C.
select * from sqldev.master.dbo.sysservers
GO
-- Some options
-- exec sp_serveroption N'SQLDEV', N'query timeout', 0 
-- exec sp_serveroption N'SQLDEV', N'collation name', N'null' 
-- exec sp_serveroption N'SQLDEV', N'use remote collation', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc out', N'true' 
-- exec sp_serveroption N'SQLDEV', N'data access', N'true' 
-- exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
-- exec sp_helplinkedsrvlogin N'SQLDEV'
-- exec sp_addlinkedserver N'SQLDEV' 
-- Clean up our test
--  Exec Sp_dropserver 'SQLDEV', droplogins