Wednesday, September 2, 2015

How to schedule a SQL server job In SQL Server 2012

     SQL Server agent helps you to automate (or) schedule a job T-SQL or SSIS Packages Jobs contains one or more steps to execute step by step manner. You can manually run a job or  you can schedule the job as per you convenience. In express edition it is not possible to create a sql server job.

This post helps you to create a job in SQL server agent step by step:-

step:-1) open SSMS  connect to database engine with sql authentication.


step:-2) go to sql server agent right click on it and click on start
             select new job and name it here i named as test-job


step:-3)  go to steps click on new and select type as Transact-Sql Script if  you want execute stored procedure here i considered stored procedure mention the step name,select database where the stored procedure is present. write command "exec load_table" in your case write your stored procedure name 


step:-4) go to schedules click new and give schedule name  and select schedule type based on your requirement 

step:-5)Mentioned the frequency as per your requirement here i choosed daily  frequency for every 15 minutes,duration how many days you want to run the job mention the dates ,i selected no end date, click ok, again click ok 


step:-6)it will execute for every 15 minutes. 


step:-7)if you want to run the job manually you can run by right clicking on the job and click on start job  




How to Fix named Pipes Provider Error 40 cannot open connection to Sql server instance



Symptoms of error

A network related or instance specific error occurred while establishing a connection to sql server the server was not found or not accessible.verify   that the instance name is correct (Provider : Named pipes error Error no 40; etc .

To fix this error goto start menu--> go to  Microsoft Sql Server --> go to configurations folder and click on sql server configuration manager. check below image.



Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those



Expand Server Network Configuration In Protocols for Sql Server here  Enable Shared,Named,TCP/IP

Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those  and restart the Sql related services. Now the error fixed.





how to enable sa user name in sql server

Most of the users facing issue with "sa" login. this issue caused because of not selecting mixed  mode authentication while installing the sql server (or) some limitations created dba's as per security policy in  company's and mixed mode is less secure.
below are the symptoms you face while login

Login failed for sa
cannot connect to sa login etc,.,

here am going to show changing authentication mode Sql server.
Follow below steps to enable to sa user in sql server
Login Filed issue:-
if you have access to any other user login(database instance) with that. right click on server instance and go to properties


select security and highlight the bubble Sql Server and windows authentication. this option can enable the sql server sa login

click ok and restart the sql server services. go to start menu type  services.msc and restart the  services


Your sa login account enabled automatically after above steps.
if you want enable manually  type below query  in ssms query editor window

ALTER LOGIN [sa] WITH PASSWORD='AnyStronG@#Passw)e', CHECK_POLICY=OFF
GO
ALTER LOGIN [sa] ENABLE
GO

now you can login with sa user name and  defined password