Tag: 2019

CmdExec on MS-SQL 2019 Linux

I’m trying to add a CmdExec as task in a job, on MS-SQL 2019 on Centos8. However, the task always fails with "running the requested program is not allowed". I have tried specifying both /bin/true and true as the program, but these also fail.

Is there a list of authorised programs for CmdExec, and is this is editable? I found that MS-SQL Linux 2017 doesn’t support CmdExec, but I think this has been fixed in 2019

Difference in light between Unity 5.5.2f1 and Unity 2019

The lighting is different between Unity 5.5.2f1 and Unity 2019. Please see the attached image.enter image description here

I dislike the way the lighting is done in the 2019 version. Both verions are installed out of the box, without any additions.

What do I need to change / set up inside Unity 2019, in order to have the exact lighting as in 5.5.2f1 version?

Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module

DBAs,
Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over?

When I hit debug (Alt-F5), gives me this error, after making some progress. Looks like once it hits the section of the code that calls another procedure, it gives this error.

Frame not in module.   The current stack frame was not found in a loaded module. Source cannot be shown for this location.

Example:

1  ..    2  ..   3  SELECT 1 FROM sys.objects   4  SELECT dbo.SomeFunction(1)   5  UPDATE TABLE SET X = 1 ....   6

It runs well, until line 3, once it hits line 4, where it is calling a different function, it throws the aboe error. In SSMS, that line just opens a new window with code for the function and show debugging progress for each F11 step-into — but not in Visual Studio 2019 (Version 16.7.1)

Thanks

SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server’s databases. I have a script to do this, and it has been working quite well when the local server is 2008 R2 or 2017. But I just installed 2019 and the same script puts the same value in every row of the table, rather than a different value for each row. It is as though the function is executed only once, then that value is used in the UPDATE statement. Perhaps it is some kind of optimization 2019 is doing? Here is a snippet of the script.

UPDATE Guest SET GuestFirstName=Utility.dbo.RANDWORD() WHERE GuestFirstName IS NOT NULL;

RANDWORD grabs a random word from a table containing approximately 100,000 English words.

After upgrade to SQL Server 2019 some stored procedures fail

We just upgraded from SQL Server 2008 R2 to SQL Server 2019(Compability lvl 150).

We have two different stored procedures that started failing after the upgrade, with error messages like this:

Msg 8632, Level 17, State 2, Procedure BuildSelfSaleStats, Line 14 [Batch Start Line 4] Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Whats really strange is that this particular stored procedure doesnt take any arguments, and when we simply execute the body of the SQL code in SSMS, it works fine(!?).

What might cause some SQL code that works fine when executed in SSMS, to suddenly start failing when its wrapped in a stored procedure?

SQL Server 2019 Always On using Distributed Network Name

We’re running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we’re running SQL Server 2019. When we set up the cluster, it was set up to use a Distributed Network Name instead of a static cluster IP address. Thanks to this we shouldn’t need an internal load balancer according to these notes: https://github.com/MicrosoftDocs/azure-docs/issues/34648.

I’m struggling to understand exactly how this works though. Based on what I read, it seems like our connection strings will point to the DNS name of the cluster (let’s call it AgCluster). If I look in DNS, there is an A record for AgCluster pointing to sql1 and another pointing to sql2. When I use AgCluster in my connection string it seems to always connect me to the primary server, even if I have ApplicationIntent=ReadOnly set. When I query @@SERVERNAME I always get the same server.

So with the Distributed Network Name setup, what should I use in my connection strings to make sure read/write queries go to the primary and read only go to a secondary? Any guides on setting this up in general would be helpful. Thanks!

Cannot find DReplay.exe for SQL Server 2019

I have been working on SQL Server 2019 version and trying to setup a load test environment using SQL Server Distributed Reply Controller – Client, however I cannot find the DReplay.exe. I thought the it will be installed as part of the installation of Distributed Reply Controller, but it didnt. Some articles suggest that it will be installed as part Management Tools from the installation media for SQL Server but the Management Tools option has been removed from the installation option.

There is also no link online to suggest an alternative way to get the DReplay.exe installed. Running out of options now.

As a desperate measure, I did try to copy the the “C:\Program Files (x86)\Microsoft SQL Server0\Tools\Binn” folder content from an older server where it was installed to the new SQL Server 2019 which did not work, throwing error “Failed to load assembly….”

Any ideas any suggestions please?? Thank you.