Database

Using function in SQL Server 在SQL Server中使用函数

Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 7th, 2012 15:06

Create SQL function:


USE DavidTest;
GO
IF OBJECT_ID ('dbo.fn_isUserActive') IS NOT NULL
    DROP FUNCTION dbo.fn_isUserActive;
GO
CREATE FUNCTION dbo.fn_isUserActive (@userID int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @userStatus int;
    DECLARE @isActive int;
    SELECT @userStatus = ID FROM DavidTest.dbo.DavidUser WHERE ID=@userID ;
    if(@userStatus is not null AND @userStatus = 3) 
    BEGIN
        SET @isActive = 1;
    END
    ELSE 
        SET @isActive = 0;
RETURN @isActive;
END;
GO

Using the function:


SELECT dbo.fn_isUserActive(3);
SELECT * FROM dbo.DavidUser u where dbo.fn_isUserActive(u.ID) > 0;

SSIS: Can not execute dataflow in Visual Studio

Categories: Database; Tagged with: SQL • SQL Server • SSIS; @ September 6th, 2012 19:24

This happened when we just edit the dtsx file.
If you want execute the flow in VS directly, create one Integration services project first, then you can copy your dtsx and pase it into the “SSIS Packages” in the Solution Explorer:

then you can execute the ssis dataflow directly in VS.

SQL Server: Execute SQL Agent job from T-SQL

Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 5th, 2012 19:02

Run SQL Agent job by this:

EXEC msdb.dbo.sp_start_job 'testJob';

Seems sp_start_job is asynchronous, here is a test:

-- Invoke SQL Agent job from T-SQL PRINT CONVERT(VARCHAR(24),GETDATE(),113) EXEC msdb.dbo.sp_start_job 'testJob'; PRINT CONVERT(VARCHAR(24),GETDATE(),113)

The results:
05 Sep 2012 18:55:11:747
Job ‘testJob’ started successfully.
05 Sep 2012 18:55:11:753

in testJob, we have one setp which is used for record the runtime:

PRINT CONVERT(VARCHAR(24),GETDATE(),113)
This job will output to one file, and in this file we can get the runtime date:

Job ‘testJob’ : Step 3, ‘recodTime’ : Began Executing 2012-09-05 18:55:12
05 Sep 2012 18:55:12:110 [SQLSTATE 01000]

MoreInfo:
1. EXECUTE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188332.aspx
2. Different ways to execute a SQL Agent job: http://www.mssqltips.com/sqlservertip/1730/different-ways-to-execute-a-sql-agent-job/
3. sp_start_job Runs Asynchronously: http://www.daveturpin.com/2009/11/sp_start_job-runs-asynchronously/

SQLServer: Export SQL result into CSV file 导出SQL结果到CSV文件

Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 5th, 2012 18:50

sqlcmd -E -iC:\test.sql -oC:\testSQLOutput.csv -s"," -h-1 -W

SQL in test.sql:

SELECT * FROM DavidTest.dbo.LiGuoliangComUsers;

All result should be export into the csv file.

SSMS(SQL Server Management Studio) can not edit SQL Agent Job steps

Categories: Database; Tagged with: SQL • SQL Server • SSMS; @ August 27th, 2012 21:39

After creating a new agent job in SSMS, I try to edit one of the steps, but got an error like this:

TITLE: Microsoft SQL Server Management Studio
——————————

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

——————————
ADDITIONAL INFORMATION:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

This is a SQL2008R2 bug >link<

you can download and install the hotfix, from the bug page.

——————————————————————

or you can try this simple way, but it’s not stable for me:

Quit all SQL based applications (SSMS, BIDS etc), you dont have to stop sql service.

Go to Start -> All Programs -> Accessories and then right click on Command Prompt and choose run as administrator.

Now copy and past the following two lines in to the command window to register the DLL’s

This will register 64 bit DTS.dll
c:\windows\system32\regsvr32 "c:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll"

This will register 32 bit which is required as SSMS is a 32-bit application.
c:\windows\syswow64\regsvr32 "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dts.dll"

Start SSMS and test that you can open and re-open jobs steps.

Job done!

from:

Cannot edit job steps in SQL Server 2008 R2 Management Studio on 64 bit windows.

Newer Posts <-> Older Posts