How to unzip in Powershell

Question: I have a zip file , which I normally win-zip or unzip . I'd like to unzip an archive file as part of a Powershell script Answer: You can use the Powershell cmdlet - Expand-Archive . I've also included some added bits around the ps cmdlet Expand-Archive , which will assist in managing access & file clean up . # Unblock if there's a block – common problem Unblock-File test.zip -ErrorAction SilentlyContinue #the actual expand process Expand-Archive -Path test.zip -DestinationPath e:\exsample\sql_server\modules -Force #to remove your zip file Remove-Item -Path test.zip Read more on Powershell scripts Powershell Script Library for... Read more →

How to unzip in Powershell

Question: I have a zip file , which I normally win-zip or unzip . I'd like to unzip an archive file as part of a Powershell script Answer: You can use the Powershell cmdlet - Expand-Archive . I've also included some added bits around the ps cmdlet Expand-Archive , which will assist in managing access & file clean up . # Unblock if there's a block – common problem Unblock-File test.zip -ErrorAction SilentlyContinue #the actual expand process Expand-Archive -Path test.zip -DestinationPath e:\exsample\sql_server\modules -Force #to remove your zip file Remove-Item -Path test.zip Read more on Powershell scripts Powershell Script Library for... Read more →

Check SQL Agent Job Exceution Status and Messages Generated with a sql script

Question: How can I check SQL Agent Job Execution Status using t-sql? I want to be able to report on the Job Steps execution status , run time duration and Message Generated. Do you have a script example , which returns the above values based on the last 24 hrs. Answer: SELECT @@servername AS 'Server_Name', j.NAME AS 'Job_Name', CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END AS ExecutionStatus ,s.step_id AS 'Step' ,s.step_name AS 'Step_Name' ,msdb.dbo.agent_datetime(run_date, run_time) AS 'Run_DateTime' ,((run_duration / 10000 * 3600 +... Read more →

Could not clear 'DIFFERENTIAL' bitmap in database - Error: 3041, Severity: 16, State: 1.

Question : An error message has started appearing in the SQL Server Error Logs during a nightly full backup. Could not clear 'DIFFERENTIAL' bitmap in database 'Database1' because of error 9002. As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup. This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary. Typically, the cause of this error is insufficient resources. Investigate the failure and resolve the cause. If the error occurred on a data backup, consider... Read more →

Scripting variable not defined error during batch job

Question: I'm trying to execute a batch script which includes a SQLCMD SQLCMD -E -d master -S %1 -i "SQL_AGENT_JOBS_INSTALL.sql" The sql script includes a reference to a SQL AGENT token SQLLOGDIR. When I run the same script from SSMS it all work OK, but executing it through the batch\ SQLCMD route throws this error. How can I fix this issue? Answer: To pass a variable into SQLCMD use the -v switch. the format is : -v var = "value" Extending the example used above : SQLCMD -E -d master -S %1 -v SQLLOGDIR="" JOBNAME="" STEPID="" STRTDT="" STRTTM="" -i "SQL_AGENT_JOBS_INSTALL.sql"... Read more →

Service Broker - When to use for synchronizing databases

Service Broker is used by SQL developers to manage applications running across multiple SQL Server instances . It is part of the SQL Server native feature set and is a messaging and queue service. Asynchronous processing in Service Broker is very effective in supporting decoupled processes for long running tasks. It's very versatile but be warned of attempting to over engineer solutions that could be better served by other features. As a very general feature summary for Service Broker think of - Guaranteed message delivery , Transactions,Backup,API and asynchronous communication. I receive questions regularly about whether Service Broker should be... Read more →

How to capture errors from SQLCMD

Question: I'm executing code using SQLCMD from a batch file . The code points to a sql file and there is also an output file. SQLCMD -E -S MYSERVER\INST1 -i "setup_job_entry.sql" -o "setup_job_entry.log" But I noticed that if the actual SQLCMD returns an error , for example , if I'm connecting to an server which doesn't exist this error message will appear in the output file - but there will not be an ERROR number , which would allow me to trap and return an appropriate message Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network... Read more →