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

execsys.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 ONDECLARE 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 CATCHCLOSE cur_sor DEALLOCATE cur_sor


good luck

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.