How to use NEWID() in SQL Server User Defined Functions (UDF)

Using NEWID() in an SQL Server user defined function returns an error message:
Msg 443, Level 16, State 1, ...
Invalid use of a side-effecting operator 'newid' within a function.

Here is a trick to get round this. Create a simple view that returns one row containing one column:
CREATE VIEW dbo.vu_newid
AS
    SELECT    NEWID() as mynewid


Test this out to prove it is working and returns a different NEWID each time.

Then in your function, use the view to return a NEWID or set of NEWIDs as follows:

To populate a variable:
DECLARE @mynewid uniqueidentifier
...
SELECT @mynewid = mynewid FROM dbo.vu_newid

To use in ORDER BY:
SELECT mt.*, vid.mynewid as mynewid
FROM   mytable mt
  CROSS JOIN dbo.vu_newid vid
ORDER BY vid.mynewid

To use in ROW_NUMBER():
SELECT ROW_NUMBER() OVER(ORDER BY vid.mynewid) as rn,
       mt.*
FROM   mytable mt
  CROSS JOIN dbo.vu_newid vid

Happy coding!

SSIS + MS-Sql-Server parameterized queries in "Execute SQL Task"

Struggled a bit with getting parameters to work with hand-crafted SQL in an "Execute SQL Task" that used an OLE DB Connector to the MS-SQL-Server database.

Eventually, after some playing around and some googling I realized that the parameters (which are marked by ? in the query) need to be mapped (in "Parameter Mapping") to numbers (0-based) in the "Parameter Name" column.

Here is the summary of what I found:

Connector	Parameter Names
OLE DB	0,1,2...
ODBC	0,1,2...
Excel	0,1,2...
ADO	1,2,3...
ADO.NET	@namedParameters...

Special thanks to: this answer on stackoverflow by Teju MB and christiandev

SSIS expression to generate date usable in file names

I finally cracked how to generate the date in an SSIS package in the ISO format of YYYYMMDDHHMM. By including this in file names, e.g. when archiving, it keeps them in logical order when sorted.

Here's the magic expression part:

SUBSTRING( REPLACE( REPLACE( REPLACE( (DT_WSTR, 30)GETDATE(), "-", ""), ":", ""), " ", ""), 1, 12 )

This will produce 201208131830 at 18:30 on 13/Aug/2012. The final 12 in the expression is used to chop it down to the minute. Use 14 if you need it down to the second.

SSIS SMTP Connection Manager setup

After fighting with setting up an SMTP Connection Manager  connection, testing various domain names in the SMTP Server property, it suddenly dawned on me:

localhost

is all you need! Plus ticking the Use Windows Authentication in my case.