Database

Using function in T-SQL

Categories: Database; Tagged with: Database • SQL • TSQL; @ November 2nd, 2012 16:49

Basic function:

ALTER FUNCTION fnGetAge(@birthDate datetime) RETURNS int AS BEGIN -- RETURN MONTH(@birthDate) RETURN DateDiff(day, @birthDate, getdate())/365.25 END


SELECT dbo.fnGetAge('09/10/1985');

Table-Valued Functions:


--INLINE Table-Valued Functions, returns a result set, like a view
CREATE FUNCTION fnGetStusByScore(@score int) RETURNS TABLE
AS    
    RETURN SELECT * FROM UserDavid WHERE score = @score
SELECT * FROM fnGetStusByScore(61)

Multi-Statement Table-Valued Functions:


-- Multi-Statement Table-Valued Functions, returns a table-type result set, the table is explicitly constructed in the script. 
ALTER FUNCTION fnGetUsers(@score int)
    RETURNS @userList TABLE
        (UserID int, UserName varchar(32))
    BEGIN
        IF @score IS NULL
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid
            END
        ELSE
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid WHERE score > @score
            END
    RETURN 
    END
SELECT * FROM fnGetUsers()

SSIS Difference between OLE DB Source & DataReader Source, OLE DB Destination & SQL Server Destination

Categories: Database; Tagged with: Database • ETL • SSIS; @ October 29th, 2012 11:41

OLE DB Source and DataReader Source (formerly known as ADO.NET source)

The OLE DB source and ADO .NET source are so similar in fact, it is forgivable to ask why they are even separate components. The simple(ish) reason for this is that the two components talk to their underlying data sources in very different ways – the OLE DB source will talk directly to relational OLE DB compliant sources, but the ADO .NET source goes through a layer of abstraction so it can talk to the source through a .NET provider. If all that sounds like gibberish, don’t worry. You only need to consider using the ADO .NET source in two cases. Firstly if no OLE DB provider is available – e.g. if you need to talk to a SAP BI instance. Secondly if you need to access the source through a Script Task.
from:http://www.bimonkey.com/2009/05/the-ado-net-source-and-sql-in-the-script-task/

And I think the difference between destinations should same as source.

OLE DB Destination v.s. SQL Server Destination

The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination. __http://msdn.microsoft.com/en-us/library/ms141095.aspx

SQL Server Destination

The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH.

Be aware that this destination requires shared memory connections to SQL Server. This means that it can only be used when Integration Services is running on the same physical computer as SQL Server.

OLE DB Destination

The OLE DB destination supports all of the bulk load options for SQL Server. However, to support ordered bulk load, some additional configuration is required. For more information, see “Sorted Input Data”. To use the bulk API, you have to configure this destination for “fast load”.

The OLE DB destinationcan use both TCP/IP and named pipes connections to SQL Server. This means that the OLE DB destination, unlike the SQL Server destination, can be run on a computer other than the bulk load target. Because Integration Services packages that use the OLE DB destination do not need to run on the SQL Server computer itself, you can scale out the ETL flow with workhorse servers.

— http://msdn.microsoft.com/en-us/library/dd425070(SQL.100).aspx

SSIS: Logging message in Script task

Categories: Database; Tagged with: Database • SQL Server • SSIS; @ October 24th, 2012 15:24

The Script task can use the Log method of the Dts object to log user-defined data. If logging is enabled, and the ScriptTaskLogEntry event is selected for logging on the Details tab of the Configure SSIS Logs dialog box, a single call to the Log method stores the event information in all the log providers configured for the task.

Pre-condition:
Loging is enabled and ScriptTaskLogEntry is selected:

Loging code:

Dim emptyBytes(0) As Byte Dts.Log("TotalRecords: " + Dts.Variables("User::NoForCheck").Value.ToString, 0, emptyBytes)
'Error: Dts.Events.FireError(0, "", "Can not find the source file!", "", 0) Dts.TaskResult = Dts.Results.Failure Return

Log:

User:ScriptTaskLogEntry,,,24/10/2012 3:16:22 PM,24/10/2012 3:16:22 PM,0,0×00,TotalRecords: 27

API:
Logging in the Script Task
http://msdn.microsoft.com/en-us/library/ms136131.aspx#Y272

SSIS: Lookup Error: Row yielded no match during lookup

Categories: Database; Tagged with: Database • SQL • SSIS; @ October 24th, 2012 14:14

Input: row set;
Output: validate the records using the other table’s data, and output the result.
Error Mesg: Error: 0xC020901E at Data Flow Task, Lookup 1 [2409]: Row yielded no match during lookup.

In SSIS 2005, if can not find the corresponding record, the component will be failed by default.
So the Error occurred.

What we need to do is, changing the ‘Error Output’ of the lookup component, from ‘Fail Component’ to ‘Redirect row’;

Output:

Generate database diagrams using MS SQL Server 2008

Categories: Database; Tagged with: Database • SQL • SQL Server; @ August 3rd, 2012 12:24

Generate database diagrams using MS SQL Server 2008

Open SQL Server Management Studio,
and explorer the databases, expand the database, and right click on the ‘Database Diagrams‘,
click ‘Create new diagram’, select the tables, and the diagrams will be generated.

see more: http://www.onlinehowto.net/create-database-diagrams-using-ms-sql-server-2008/1563