Database

Use expression to get dynamic data in SSIS package (file name with date)

Categories: Database; Tagged with: SSIS; @ November 21st, 2012 18:40

Requirement:

Destination is flat file, and the file name should contains date: YYYYMMDD.

Solution:

Create one variable, stores the dir of the destination file.
Then use Expression to update the connection string of the file destination.


@[User::FileDestrination]  + 
"OVERSEAS_USER_INFO_"+
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".txt"

Installing SQL Server 2000 in Windows 7 professional Windows 7专业版下安装SQL Server 2000

Categories: Database; Tagged with: SQL Server • SQL Server 2000 • windows 7; @ November 8th, 2012 16:25

It seems to me I cannot install SQL Server 2000 in my win 7 pro:

I try to google it and follow some guides, but it seems can not work for me.

Then I want to use VM, at least I need one Windows licence, it’s not easy for me to get this….

Ohhhhh, that’s a feature called ‘Windows XP mode’ in Windows7!

After run the ‘Win XP mode’, actually, it’s a XP VM, install SQL SERVER 2000 in the XP, and then I can run the SQL SERVER 2000 from Win7!

—–

简单来说，就是使用Win7自带的XP模式（XP虚拟机）安装SQL Server 2000。

Change Script Task Script Language in SSIS 2005

Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ November 2nd, 2012 18:02

The answer in we can no choose language in 2005, “The scripting language is Microsoft Visual Basic .NET.” no other options. http://msdn.microsoft.com/en-us/library/ms187649(v=sql.90).aspx

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()

TSQL: IF ELSE, For Loop

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

IF:


CREATE PROCEDURE spTestDavid
@userID int
AS
    IF EXISTS(SELECT * FROM UserDavid WHERE ID = @userID)
        PRINT 'User EXISTS: ' + CAST(@userID AS VARCHAR(32))
    ELSE 
        PRINT 'User CANNOT found' + CAST(@userID AS VARCHAR(32))

WHILE:


CREATE PROCEDURE spTestDavid2
@COUNTER int
AS
    WHILE @COUNTER > 0 
    BEGIN 
        EXEC spTestDavid @userID = @COUNTER
        SET @COUNTER = @COUNTER - 1;
    END
EXEC spTestDavid2 @COUNTER = 30

Newer Posts <-> Older Posts