Posts

Showing posts with the label SQL Server

set Not For Replication True via Stored Procedure

Some administrator may not prefer to use SQL Server Management Studio for the strictions such as timeout or following Foreign Key exists; To set or UnSet "Not for Replication" property of any column you want to use in Replication; DECLARE @ObjectID INT = OBJECT_ID('dbo.tFileProvider') //get Object Id exec sys.sp_identitycolumnforreplication @object_id = 246291937, @value = 1 //0 : No ; 1 : yes

Stored Procedure Pattern in SQL Server 2005

Here is a basic stored procedure concept i personally offer to advanced SQL Server users; SET NOCOUNT ON DECLARE cur_sor CURSOR DECLARE @CURSOR_FETCH_STATUS INT DECLARE @varible1 as [anytype] ,@variable2 as [anytype],.... SET cur_sor = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT .... FROM ..... /* for Arguments Detail check http://msdn.microsoft.com/en-us/library/ms180169.aspx */ OPEN cur_sor BEGIN TRY BEGIN TRANSACTION FETCH NEXT FROM cur_sor INTO @varible1,@variable2 ,... /* not more than selected columns above*/ SELECT @CURSOR_FETCH_STATUS = @@FETCH_STATUS WHILE @CURSOR_FETCH_STATUS = 0 BEGIN ....... /* INSERT,DELETE,UPDATE ( DML) or any other process */ ....... FETCH NEXT FROM cur_sor INTO @varible1,@variable2 ,... SELECT @CURSOR_FETCH_STATUS = @@FETCH_STATUS END COMMIT END TRY BEGIN CATCH PRINT(ERROR_NUMBER()) PRINT(ERROR_SEVERITY()) PRINT(ERROR_STATE()) PRINT(ERROR_PROCEDURE()) PRINT(ERROR_LINE()) PRINT(ERROR_MESSAGE()) ROLLBACK END CATCH CLOSE cur_sor DEALLOCATE cu

comparision executed user role with given

Syntax : is_srvrolemember(SQLServerRole in String) Returns : if user has right for given return will be 1 for example; if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that executing by SQ. begin raiserror(...) return(1) end

How to call any web page from SQL Enterprise manager as scheduled job

Here is code with VB Script; Dim WshShell Set WshShell =CreateObject("WScript.Shell") Set oExec = WshShell.Exec("C:\\PROGRA~1\\INTERN~1\\iexplore.exe http://localhost/xxxx/app/pages.aspx ") oExec.Terminate() Set WsShell = Nothing Pay attention to terminating iexplore.exe after Job finishes on codes.