Hi,

You have to use TRY...CATCH construct in order to handle the errors. You have rightly set XACT_ABORT ON before begining the transaction. This way you can make sure that if...

@JayK,

Please replace all the instances of contact value from the log you have sent with 'xxxxxx' ASAP. I can see one instance where original contact value is visible.

For the community to help JayK more easily, below are the queries extracted from the log

Dynamic SQL

SELECT TOP 50

c.customercode

FROM ...

Hi JayK,

What I can see from it, table customer_contact is definitely used in your dynamic query

left join customer_contact cc on c.CustomerCode = cc.CustomerCode AND

and cc.Contactvalue like @xmobilenumber...

Can you please send the output (appended with any error if occurs) of this script

BEGIN TRY

IF OBJECT_ID('tempdb..#Output') IS NOT NULL

DROP TABLE #Output

CREATE TABLE #Output

(

Counter INT IDENTITY(1, 1)

,DirOutput...

Shiv,

You have not appended the output of xp_cmdshell commands. There would have been some output if ran successfully. So please send the output (or any error) for below scripted lines

/*==FOR...

Sorry to say, but you have not answered about the permissions. Can you please check that one as well. Can you please run the following script and send the output....

Shiv,

Once again I would ask have you checked the required permissions? Does the specified folder already exists there? Are you looking at the same server where your SQL Server resides?...

Please also make sure that you are looking at the correct server's directory. I had same kind of a problem once, when I was trying to look into the folders...

What windows error you are getting?

You can also use UNC to a share (Everyone must have write permissions on it) e.g. (N'\\Servername\Sharename\Directory\trace.trc'). This would make sure that the SQL service...

Have you checked the permissions are as it is desired?

@Gail Shaw

Please if you have time, do add your thoughts on "TRACE_PRODUCE_BLACKBOX" option in sp_trace_create procedure, in order to write in the default directory. At least this will make sure...

@lowell,

The way you are helping Shiv is more than appreciated. You must have a very cool mind. I believe if Shiv is working on the trace output(a sort...

@Mark-101232

Wow. That was something new for me to learn. Although it is obsolete ( not able to use such collation at server level), but still can be used at lower...

Hi,

Although I am a big fan of tally table and keen follower of Mr. Jeff Moden, I am not sure why we need a tally table here. It is just...