Extended Events -capture query errors

If you're troubleshooting SQL Server query errors , you'll already know Extended Events are highly useful and very effective method to capture SQL Server errors. To use the script you'll need appropriate privileges to create the Extended Event. You will also need to have some space available on the disk to store output files. I usually place them in the C:\tmp - although be careful just in case you fill the C:\ drive! The alternative is to use another drive. CREATE EVENT SESSION [CaptureErrors] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username, sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username) WHERE ([severity]>=(10))) ADD TARGET package0.event_file(SET filename=N'C:\tmp\CaptureErrors.xel',metadatafile=N'C:\tmp\CaptureErrors.xem') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1... Read more →

SQL Server Docker and Container cheatsheet

I've started working with Docker platform and managing SQL Server Containers. This list are some of the commands I execute most often when using Docker and Containers. --Pull down a container from the Docker registry docker pull --(generic approach)Pull down a sql server container to a valid docker installation on RHEL and run (generic approach) docker pull microsoft/mssql-server-linux docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=LinuxSQLServer123' -p 1433:1433 -d microsoft/mssql-server-linux --(customised approach)Pull down a sql server container to a valid docker installation on RHEL and run Step 1 - Mapping volume names with customised locations **comment - data volume** docker volume... Read more →

The DELETE statement conflicted with the REFERENCE constraint

Question: In the application error logs there is a message - The DELETE statement conflicted with the REFERENCE constraint FK_xxxxxxxxx What is causing this error ? How can I troubleshoot? Answer: The error is occuring because there is an attempt to delete a parent table data before the referenced child table data is deleted When a database uses a FOREIGN KEY , its purpose is to create a link between two tables. The foreign key is one or more columns in the a table referencing another table's PRIMARY KEY. A child table is the table hosting the FOREIGN KEYS.A parent... Read more →

Email separator in Powershell

Question: I'm trying to send an email with multiple recipients in Powershell, and getting an exception error message: Exception calling "Add" with "1" argument(s): "An invalid character was found in the mail header: ';'." When I revert to send the email to one recipient - the email is successfull . What is the correct way to pass multiple recipients for powershell email. Some more information that may assist in troubleshooting. 1) When I hardcode the email addresses in the Powershell code e.g $emailTo = "user1@org.com,user2@org.com" it works ok . 2)When I attempt to pass the values in as input parameters... Read more →

Adding SSMS to the PATH variable with Powershell

Question: I've installed the latest SQL Server Management Studio onto a server. I can find the ssms.exe through the directory - but I cannot start the SQL Server Management Studio through the cmdline - i.e type "ssms" in the command line. How can I fix this problem? Answer: I had a similar problem recently when installing SSMS 18.4. I downloaded the binary - installed without any errors - but then couldn't execute "ssms" from the command line. To solve this issue I added the directory with ssms.exe into the PATH variable and applied this as a machine wide change ,... Read more →