Sql certificate chain was issued by an authority that is not trusted

How to solve the certificate chain was issued by an authority that is not trusted? Why the certificate chain was issued by an authority that is not trusted? Read more →

How to bcp export file with encoding ascii

Question: I have a bcp command exporting a sql query which outputs to a file . The problem is the file is being saved as Unicode type - causing a subsequent ETL process to fail. How can I fix? I want the file to export as ascii encoding bcp "select * FROM myTable" queryout E:\mytable.csv -w -t, -T -S SERVER\MYINST1 Answer: The quick answer is to ask you to add the -c and -C switches. From SQL Server Books Online "-c Performs the operation using a character data type. This option does not prompt for each field; it uses char... Read more →

Error Message:The UPDATE statement conflicted with the REFERENCE constraint

During an UPDATE statement on a particular row on a SQL Server table , this error message appeared: Error Message:The UPDATE statement conflicted with the REFERENCE constraint "FK_My_Constraint". The conflict occured in database "MyDB",table "dbo.MyTable". The statement has been terminated The initial input value was incorrect , so I needed to update the value - but there was a foreign key constraint in place to retain integrity between the tables. The way to fix this issue is to temporarily disable the FOREIGN KEY constraint - change the values and the set the FOREIGN KEY constraint again. SQL Server has a... Read more →

How to capture query errors using SQL Server Extended Events

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 →

Quick Fix: 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 →