Home » Technology
Category Archives: Technology
Restrict user to login from single Host
Restricting user to login from single host may be sometimes required from security point and other business requirements. We can achieve it through SERVER LEVEL LOGON TRIGGER.
ALTER TRIGGER TR_CHECK_LOGIN_TEST_HOST
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @HOSTNAME VARCHAR(48)
DECLARE @PROGNAME VARCHAR(100)
SELECT @HOSTNAME = HOST_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = @@SPID
IF ORIGINAL_LOGIN() = ‘TEST’ AND @HOSTNAME =’HostName’
ROLLBACK;
END
Once you create above trigger, Login “TEST” will be able to connect from specified host only. Connection from any other host will be failed and face below error: –
You can also find related errors in SQL Server Error log as well.
Error: 17892, Severity: 20, State: 1.
Logon failed for login ‘TEST’ due to trigger execution. [CLIENT: <local machine>]
Error: 3609, Severity: 16, State: 2.
The transaction ended in the trigger. The batch has been aborted.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Write-Host || Cannot invoke this function because the current host does not implement it
Issue: While running powershell in SQL job step, I am getting below error: –
A job step received an error at line 13 in a PowerShell script. The corresponding line is ‘Write-Host “ServerName: ” $comp’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot invoke this function because the current host does not implement it. ‘. Process Exit Code -1. The step failed.
Solution: SQL Server Agent allows users to directly run PowerShell scripts in SQL Server Agent. Internally this is implemented by reusing the SQLPS.EXE shell. When SQL Server Agent starts SQLPS.EXE, it does not do this through the console (there is no console attached).
When you run Write-Host, PowerShell asked to write on host console but due to no availability of HOST console, we got error.
I removed Write-Host to resolve issue.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Error While running PowerShell code from SQL Agent job – “Path Does Not Exist”
Issue: When I am running code from PowerShell console its running fine but when I execute it through SQL agent job step, I am getting below error: –
The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 14 in a PowerShell script. The corresponding line is ‘$files = Get-ChildItem “\\$comp\D$\*.xls”‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘An object at the specified path \\ServerName\D$ does not exist. ‘ A job step received an error at line 43 in a PowerShell script. The corresponding line is ‘ $str = Get-Content $i.FullName | Where-Object { ($_ -match ‘Alive’) }’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot bind argument to parameter ‘Path’ because it is null. ‘. Process Exit Code -1. The step failed.
Solution: SQL Server Agent allows users to directly run PowerShell scripts in SQL Server Agent. Internally this is implemented by reusing the SQLPS.EXE shell. When SQL Server Agent starts SQLPS.EXE, It cannot access file system.
To access file system, you need to change the scope from SQL to filesystem.
You can do it by adding “cd C:” before executing filesystem commands.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
What’s new in SQL Server 2016 installation?
1) As MS is kept increasing number of option while installation. To reduce no. of click, if setup pass Rules it will automatically moves to Next window.
2) On Server Configuration page, MS specifically ask to GRANT PERFORM VOLUME MAINTENANCE TASK permission to service account. SQL Services needs this access to perform instant file initialization for data files instead of zero file initialization. (For information read – https://msdn.microsoft.com/library/ms175935)
3) 2 New Tabs in Database Engine Configuration page. One is TempDB configuration & other is User Instance feature.
4) Separate Acceptance page for Microsoft R.
Step by Step Installation :-
1. Setup Page, Choose First option
2. Setup checks for Global Rules and moves to next page, if all rules passed
3. Option to choose, if you want setup to download latest update and install
4. Initial Setup files installed
5. Check for installation rules and moves to next page, if all rules passed
6. Accept terms and condition, Click to Next
7. Select features you want to install
8. Check feature rule and moves to next page, if all rules passed
9. Select Instance name
10. Specify service accounts, you can also select collation from collation tab.
Here, MS specifically ask to GRANT PERFORM VOLUME MAINTENANCE TASK permission to service account. (For information read – https://msdn.microsoft.com/library/ms175935)
https://msdn.microsoft.com/library/ms175935
11. Select Authentication type
12. Select Data Directories
13. New Tab introduced to provide TempDB configuration at time of installation only
14. New Tab introduced for “User Instance” feature. Although, MS marked this feature to be removed. After this special tab, It seems MS changed his mind.
Refer – https://technet.microsoft.com/en-us/library/ms143684
15. Option to enable FileStream
16. Option for SSRS installation and configuration
17. New Page added for Microsoft R acceptance
18. Installation on the way
19. Installation Completed Successfully
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Sequence – Long awaited feature
As per BOL (https://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx) : A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.
This feature is really a good to have and this feature is same as Oracle’s sequence objects. A sequence object generates sequence of unique numeric values as per specifications mentioned.
A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint. A sequence value is not effected by SQL server restart. Many people think sequence is similar to identity. But big difference is sequence object is independent of a table while identity columns are attached to a table.
Create Sequence
CREATE SEQUENCE TEMPSEQ AS INT
START WITH 1 — START WITH VALUE 1
INCREMENT BY 1– INCREMENT WITH VALUE 1
MINVALUE 0 — MINIMUM VALUE TO START IS 0
MAXVALUE 5 — MAXIMUM IT CAN GO TO 5
NO CYCLE — DO NOT GO ABOVE 5
CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO
Generate value from Sequence
SELECT NEXT VALUE FOR TEMPSEQ AS seq_no;
Error when Sequence reach to MAX value
Msg 11728, Level 16, State 1, Line 18
The sequence object ‘TEMPSEQ’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
Alter Sequence to RESTART it from 1 or desired value
ALTER SEQUENCE TEMPSEQ RESTART WITH 1
Create Sequence with CYCLE mode, where value will be reset to MIN value when it reaches to MAX value
CREATE SEQUENCE TEMPSEQ_CYCLE AS INT
START WITH 1 — START WITH VALUE 1
INCREMENT BY 1– INCREMENT WITH VALUE 1
MINVALUE 0 — MINIMUM VALUE TO START IS 0
MAXVALUE 5 — MAXIMUM IT CAN GO TO 5
CYCLE — RESET to 0 once reached to MAX VALUE 5
CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO
Output of Sequence with CYCLE
SELECT NEXT VALUE FOR TEMPSEQ_CYCLE AS ID, NAME FROM SYS.OBJECTS
Alter existing Sequence to set it to CYCLE mode
ALTER SEQUENCE TEMPSEQ CYCLE — RESET to 0 once reached to MAX VALUE 5
Use Sequence in Table as Default constraint.
Scenario: We need to generate token for each customer coming to meet him with HR & finance personal.
- Create Sequence
CREATE SEQUENCE TOKENNO AS INT
START WITH 1 — START WITH VALUE 1
INCREMENT BY 1– INCREMENT WITH VALUE 1
MINVALUE 0 — MINIMUM VALUE TO START IS 0
MAXVALUE 5000 — MAXIMUM IT CAN GO TO 5000
NO CYCLE — RESET to 0 once reached to MAX VALUE 5000
CACHE 200 — INCREMENT 200 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO
- Create Table with Sequence
CREATE TABLE HR_EXECUTIVE_MEETING
(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)
CREATE TABLE FINANCE_EXECUTIVE_MEETING
(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)
- Insert into table having Sequence
INSERT INTO HR_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,22)
INSERT INTO FINANCE_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,7)
- Check the table values
SELECT * FROM HR_EXECUTIVE_MEETING
SELECT * FROM FINANCE_EXECUTIVE_MEETING
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
http://beyondrelational.com/members/RohitGarg/default.aspx