Tag: 2019

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.

Key Pair permission on Windows 2019

I generated a key pair on Windows Server 2019 using the native ssh-keygen located in System32\OpenSSH. Now I think the permissions of the private key are somewhat worrying…

  1. Is it expected to have read-write-execute (700) for Administrators, and for the SFTPClient user (part of Users, not Administrators) on the Windows implementation?

  2. Is there any reason why the key isn’t following the same logic than on Unix/Linux? (POSIX permissions set to 600 = read-write for owner only)

enter image description here

enter image description here

Original French Recipe Website with 70K visitors in 2019

Why are you selling this site?

Not much time to develop it further.

How is it monetized?

I have added AdSense to a few pages, but nothing to speak of as I haven't given a great deal of thought on placement.

Does this site come with any social media accounts?

No.

How much time does this site take to run?

Articles haven't been added since 2016, though I recently have done website re-design which has made the website look a lot better and reduced bounces….

Original French Recipe Website with 70K visitors in 2019