#sqlhelp — Public Fediverse posts

TSQL Problem:
Let's say you've got one or more scalar values that are VARCHAR 'strings' and you want to determine if they can be typed more appropriately.

For example '16:16:34.6733333' is data type TIME. It's easy to examine the value manually to make that determination. But how would you do it programatically (via TSQL)?

I've tried casting the VARCHAR as different types. TRY_CAST is successful for some data types. How would I know which one is 'right'?

#SQLHelp
#SQLServer
#SQL
#TSQL

TSQL Problem:
Let's say you've got one or more scalar values that are VARCHAR 'strings' and you want to determine if they can be typed more appropriately.

For example '16:16:34.6733333' is data type TIME. It's easy to examine the value manually to make that determination. But how would you do it programatically (via TSQL)?

I've tried casting the VARCHAR as different types. TRY_CAST is successful for some data types. How would I know which one is 'right'?

#SQLHelp
#SQLServer
#SQL
#TSQL

Aha, root issue was not what I thought it was.

By virtue of them having mapped the login to SSRSAdmin, it was listed as the owner in the database properties. I changed that to sa and then was able to create the user proper in the database.

#sqlhelp

If anyone watches #SQLHelp, I have a database where someone created a SQL User but named them dbo

e.g.

CREATE USER [dbo] FOR LOGIN [SSRSAdmin];

What they should have done is specify the user is the same as the login but doubled it up in the SSMS UI because they were monkeys with lit dynamite.

My challenge is how do I unbugger this?

Following didn't work

ALTER USER [dbo] WITH NAME=[SSRSAdmin];

DROP USER [dbo];

Trying to build my first Data Vault model in #Snowflake. But I'm not quite understanding the concept of hash keys. My source system already has a sequence as a surrogate key and just creating a hash of that value seems to serve no purpose.

I can understand it if I didn't always have a surrogate key but why should I use them if I do?
#sqlhelp #datawarehouse #datavault #datadesign

Trying to build my first Data Vault model in #Snowflake. But I'm not quite understanding the concept of hash keys. My source system already has a sequence as a surrogate key and just creating a hash of that value seems to serve no purpose.

I can understand it if I didn't always have a surrogate key but why should I use them if I do?
#sqlhelp #datawarehouse #datavault #datadesign

#SQLServer Permission question: Can I grant a user the ability to alter existing objects but not create new?

It doesn't look like based on what my tests are indicating.

#sqlHelp

I'm trying to import the Azure-trusted root certificate authority keys to #sqlserver to set up a SQL Managed Instance Link as described here: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-configure-how-to-scripts?view=azuresql&tabs=sql-server#import-azure-trusted-root-certificate-authority-keys-to-sql-server But I'm getting the enclosed error message during the certificate creation. What am I doing wrong? #sqlhelp

I'm trying to import the Azure-trusted root certificate authority keys to #sqlserver to set up a SQL Managed Instance Link as described here: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-configure-how-to-scripts?view=azuresql&tabs=sql-server#import-azure-trusted-root-certificate-authority-keys-to-sql-server But I'm getting the enclosed error message during the certificate creation. What am I doing wrong? #sqlhelp

Tooting aloud but feel free to weigh in, my brain is fried, plus this is math(s), I think. Working with #TSQL #SQLHelp Fiddle at https://dbfiddle.uk/aesRm4jp

I have the values 0 to 360 degrees in a table, which corresponds to the cardinal directions. I want to round those to the nearest 15 so 8 to 22 are all "15"

I feel like there's a math function or two I could apply to make this work and modulus was initial reach but I'm not seeing it. Maybe I need to futz with case statements...

Anyone know of any upcoming demos of Microsoft Fabric out in the data community?

#SqlHelp

cc @SQLAllFather

Anyone know of any upcoming demos of Microsoft Fabric out in the data community?

#SqlHelp

cc @SQLAllFather

#sqlhelp Is there a way to get Azure Data Studio to recognize SETVAR and SQLCMD variables?

#sqlhelp Is there a way to get Azure Data Studio to recognize SETVAR and SQLCMD variables?

This is a _real_ dumb question but: anyone know how to kill SSMS via PowerShell? Neither of the following work because "Access is denied"

$Process.Kill()
$Process | Stop-Process -Force

#SQLHelp?

This is a _real_ dumb question but: anyone know how to kill SSMS via PowerShell? Neither of the following work because "Access is denied"

$Process.Kill()
$Process | Stop-Process -Force

#SQLHelp?

Shoutout to the #sqlserver #dba crowd. If my ring buffer keeps telling me RESOURCE_MEMPHYSICAL_LOW I'm quite sure that there's a memory problem *somewhere*. But what is the root cause if it's constantly attributed to IndicatorsPool=2 ?

#sqlhelp

#sqlhelp I'm trying to decipher an issue with a union all view between to databases that has instead of triggers on it. The insert trigger works but update and delete throw "UNION ALL view not updatable because a partitioning column was not found"

SQL server 2016 and both databases are 130

#sqlhelp I'm trying to decipher an issue with a union all view between to databases that has instead of triggers on it. The insert trigger works but update and delete throw "UNION ALL view not updatable because a partitioning column was not found"

SQL server 2016 and both databases are 130

@DaveMasonDotMe deligate that account the "Validated write to service principle name" on the domain in AD. See https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc731241(v=ws.10)?redirectedfrom=MSDN in the "Delegates Authority to Modify SPNs section #sqlhelp

@DaveMasonDotMe deligate that account the "Validated write to service principle name" on the domain in AD. See https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc731241(v=ws.10)?redirectedfrom=MSDN in the "Delegates Authority to Modify SPNs section #sqlhelp

@Lincolncyber Please don't advertise in the #sqlhelp hash tag. We try and keep this hash tag for questions, not advertisments.

Advertising to this hash tag is the quickest way for your account to be blocked by your target audience.

@lincolncyber Please don't advertise in the #sqlhelp hash tag. We try and keep this hash tag for questions, not advertisments.

Advertising to this hash tag is the quickest way for your account to be blocked by your target audience.

@leemarkum IS the database busy? You might just need a larger QS. I know that we've had to set some clients QS to be 10s of Gigs or higher. #sqlhelp

@leemarkum IS the database busy? You might just need a larger QS. I know that we've had to set some clients QS to be 10s of Gigs or higher. #sqlhelp

I have an Extended Event that records anything over severity 10 to the ring buffer. I've used it plenty of places so I don't think there's anything special about it on the Managed Instance.

It shows that it's an active session but there have been plenty of errors that ought to be there that aren't. Before I stop and restart the session, any idea on what's awry here? #sqlhelp

Session definition as image otherwise it's too long of a toot

Is it super weird or unreasonable for me to expect a BI team to manage their ETL tool's application server (i.e. they can see to ensuring the proper SQL connection drivers are installed etc.)? I am generally glad to help them but on the other hand 6 years into their run they also seem quite helpless? #SQLFamily #SQLHelp

Is it super weird or unreasonable for me to expect a BI team to manage their ETL tool's application server (i.e. they can see to ensuring the proper SQL connection drivers are installed etc.)? I am generally glad to help them but on the other hand 6 years into their run they also seem quite helpless? #SQLFamily #SQLHelp

Fun fact for the #sqlhelp crowd. Make sure the databases actually exist in the templates folder before running setup.exe with the REBUILDDATABASE action. Otherwise you end up with zero viable system databases.

-- in the end the template DBs can be copied from media as well.

When spinning up an AG in Azure, if you use an Azure virtual network for all VMs (across regions or availability zones), do you need either DNN or VNN/load balancer? And does this mean you could setup a DistAG from an on-prem deployment to the Azure AG? Or even span the on-prem AG into the cloud? #sqlhelp

When spinning up an AG in Azure, if you use an Azure virtual network for all VMs (across regions or availability zones), do you need either DNN or VNN/load balancer? And does this mean you could setup a DistAG from an on-prem deployment to the Azure AG? Or even span the on-prem AG into the cloud? #sqlhelp

Trying to set up the DTC on a new server and Start-DiagnosticResourceManager keeps throwing an error "Invalid Operation" and cannot open the host. Any #SQLHelp that can keep me from at least going down useless rabbit holes?

All the firewall rules have been enabled and Test-Dtc fails when it goes to actually run the test.

Trying to set up the DTC on a new server and Start-DiagnosticResourceManager keeps throwing an error "Invalid Operation" and cannot open the host. Any #SQLHelp that can keep me from at least going down useless rabbit holes?

All the firewall rules have been enabled and Test-Dtc fails when it goes to actually run the test.

@mbourgon want to make sure I've got this right.

1. upload the backups in continuous (full, diff, tlog) to blob
2. bring the test DB online ending the restore service
3. test
4. delete the test DB
5. Start a new LRS that uses the same files in the blob, plus any future, in continuous for now as the Prod DB

Yes that can be done. #sqlhelp #Azure

@mbourgon want to make sure I've got this right.

1. upload the backups in continuous (full, diff, tlog) to blob
2. bring the test DB online ending the restore service
3. test
4. delete the test DB
5. Start a new LRS that uses the same files in the blob, plus any future, in continuous for now as the Prod DB

Yes that can be done. #sqlhelp #Azure

@zippy1981 @DataChick last I tried backups to a private endpoint (and I'd assume restores) weren't working.

Last I talked to the PG about it, it was a "whoops that should be working, we'll get it fixed" situation. #sqlhelp