USE MyDatabase
GO

-- Windows 2000 / XP
-- Replace "{servername}" with your SQL Server name

EXEC sp_grantlogin [{servername}\ASPNET] 
EXEC sp_grantdbaccess [{servername}\ASPNET], [NETAPP]

-- GRANT EXECUTE ON [ProcedureName] TO [NETAPP]
grant exec on [dbo].[usp_My_Procedure] TO NETAPP 
GO

-- Windows Server 2003
-- Info.

EXEC sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_grantdbaccess [NT AUTHORITY\NETWORK SERVICE]

GRANT EXECUTE ON [ProcedureName] TO [NT AUTHORITY\NETWORK SERVICE]
GO

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.

1. http://en.wikipedia.org/wiki/RAID
2. http://www.raidarray.com/04_01_50.html

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