Home » SSMS
Category Archives: SSMS
Error while loading Excel data into database
Issue: I am trying to load data from Excel 97 – 2003 sheet into a database table. I have written a simple SSIS package to do that, but unfortunately, it fails with error: –
Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:02:37 AM Error: 2016-12-19 02:02:37.91 Code: 0xC0209303 Source: ExcelImpot Connection manager “SourceConnectionExcel” Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. End Error Error: 2016-12-19 02:02:37.91 Code: 0xC001002B Source: MAPTImpot Connection manager “SourceConnectionExcel” Description: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816 End Error Error: 2016-12-19 02:02:37.91 Code: 0xC020801C Source: Data Flow Task 1 Source – Sheet1$ [38] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “SourceConnectionExcel” failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2016-12-19 02:02:37.91 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Source – Sheet1$ failed validation and returned error code 0xC020801C. End Error Error: 2016-12-19 02:02:37.91 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-12-19 02:02:37.91 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:02:37 AM Finished: 2:02:37 AM Elapsed: 0.733 seconds. The package execution failed. The step failed.
Cause: This error occurs when you try to execute 32-bit environment package in 64-bit system.
Solution 1: If you have project file of SSIS package or If you are running package other than SQL Agent jobs like calling from CMD.
Step 1: Navigate to Project-> [PROJECT_NAME] Properties.
Step 2: Navigate to “Debugging” option from left panel and from Right panel change Run64BitRuntime value to false.
Solution 2: If you need to execute package from SQL agent job only then you have readymade option available. Go to Job step and check below option.
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
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