Database

BSTR    g_bstrEmpty = ::SysAllocString(T2OLE(""));
CString DatabasePath("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=test;Data Source=SERVER_NAME");

ADOConnection *pIDBCon     = 0;
ADORecordset  *pIRecordset = 0;

// Create connection object
HRESULT hr = CoCreateInstance(CLSID_CADOConnection, 
                              NULL, 
                              CLSCTX_INPROC_SERVER, 
                              IID_IADOConnection, 
                              static_cast<LPVOID *>(&pIDBCon));
if(SUCCEEDED(hr))
{
  BSTR bstrConnection = DatabasePath.AllocSysString();

  pIDBCon->put_ConnectionString(bstrConnection);

  // Open database
  hr = pIDBCon->Open(g_bstrEmpty, g_bstrEmpty, g_bstrEmpty, -1);
  if(SUCCEEDED(hr))
  {
    hr = pIDBCon->put_CursorLocation(adUseClient);

    // Create recordset
    hr = CoCreateInstance(CLSID_CADORecordset, 
                          NULL, 
                          CLSCTX_INPROC_SERVER, 
                          IID_IADORecordset, 
                          static_cast<LPVOID *>(&pIRecordset));
    if(SUCCEEDED(hr))
    {
      hr = pIRecordset->putref_ActiveConnection(pIDBCon);

      // Get all records
      VARIANT g_varEmpty;

      g_varEmpty.vt    = VT_ERROR;
      g_varEmpty.scode = DISP_E_PARAMNOTFOUND;	

      COleVariant varSQL("SELECT * FROM Table");

      hr = pIRecordset->Open(varSQL, g_varEmpty, adOpenKeyset, adLockOptimistic, adCmdText);

      // Close recordset
      pIRecordSet->Close();
    }
  }

  // Close connection
  pIDBCon->Close();
}

Function GetSQLDate ( dtDate)
  ' check dtDate is a Variant->Date type
  If Not IsDate(dtDate) then
     Err.Raise vbObjectError + 1024, "Global::Function::GetSQLDate","Argument not a date"
  End If

  Dim dpYear, dpMonth, dpDay, dpHour, dpMinute, dpSecond
  dpYear=Year(CDate(dtDate))
  dpMonth=Month(CDate(dtDate))
  dpDay=Day(CDate(dtDate))
  dpHour=Hour(CDate(dtDate))
  dpMinute=Minute(CDate(dtDate))
  dpSecond=Second(CDate(dtDate))

  if Not Len(dpYear) = 4 then dpYear="20" & dpYear
  if Not Len(dpMonth) = 2 then dpMonth = "0" & dpMonth
  if Not Len(dpDay) = 2 then dpDay = "0" & dpDay
  if Not Len(dpHour) = 2 then dpHour = "0" & dpHour
  if Not Len(dpMinute) = 2 then dpMinute = "0" & dpMinute
  if Not Len(dpSecond) = 2 then dpSecond = "0" & dpSecond

  GetSQLDate=dpYear & "-" & dpMonth & "-" & dpDay & " " & dpHour & ":" & dpMinute & ":" & dpSecond

End Function

SELECT TOP 6 
    Title
FROM
    (

    SELECT
        P_Title As Title
    FROM
        tblProjects
    WHERE
        Status='True'

    UNION ALL

    SELECT
        Title
    FROM
        tblProjectDocs
    WHERE
        Status='True'

    ) As Titles

ORDER BY
    Title DESC

'**********************************************************************
'  Visual Basic ActiveX Script
'**********************************************************************

Function Main()
	Call Paul_Watson_Fun()
	Main = DTSTaskExecResult_Success
End Function


Function Paul_Watson_Fun()
Dim oServer 		'DMO Server object.
Dim oResult		'Result set.
Dim sParamValue

	'Create a SQLDMO server object.
	Set oServer =  CreateObject("SQLDMO.SQLServer")

	' Make a connection to the local server.
	oServer.Connect ".", "sa"

	'Select the desired row from the table.
	set oResult = oServer.Databases("pubs").ExecuteWithResults_
("select  paramvalue from  my_param_table")

	'Retrieve the first row, first column from the results.
	sParamValue = oResult.GetColumnString (1,1)

	'Set the global variable.
	DTSGlobalVariables("MyGlobalVar").Value = sParamValue

	Main = DTSTaskExecResult_Success
End Function