Forum Widgets
Latest Discussions
Insert & Update both implemented wrong semantically
Insert means to introduce something, adding/entering something new Update means to change something, change and existing value. Semantically and logically speaking, Insert should only be used to enter data into an empty or partially empty record/table. Update should only be used to change existing values within a record/table. Using a 5 column table as an example where column 1 is an email address Example1 INSERT INTO table (Column1,Column2,Column3) VALUES (value1,value2,value3) Or Example2 INSERT INTO table VALUES (value1,value2,value3,value4,value5) With example 1, 2 columns are left empty Semantically, insert should be used to populate columns for the first time INSERT INTO table (column4, column5) VALUES (value4,value5) where column1 = "EmailAddress" Then for example, UPDATE table SET COLUMN2 = "newValue" WHERE COLUMN1 = "EmailAddress" Updating (setting) an empty column should cause an error, cannot update an empty column SQL, a great idea, pity its implementation is tainted by bad design. SQL works, but its design/implementation of Insert & Update is semantically wrong. IMO As the saying goes, if it isn't broken, don't fix it. Who cares about semantics or logic.
SERVERPROPERTY('IsClustered') does not return the correct value in a cluster configuration
Hi all! I'm testing some t-sql SQL properties for a data collection project and I got a strange case with the query below: SELECT SERVERPROPERTY(''MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('Edition') AS [Edition] I have two test instances of SQL Server 2019. They both belong to a cluster without shared storage, as the availability groups allow it: SQLAGCLU I don't understand why the query is returning "incorrect" properties. I expected them to have the value 1 for the "IsClustered" column. From the query that I use, I have no idea witch SQL instance are in a cluster... Some one can give a feedback about it? Thanks ALEN
How to communicate with Linked Server encryption
I'm using SQL SERVER's Linked Server to link between databases. But there is a security problem with this method. So is there a way to encrypt the communication section when accessing other databases through Linked Server and importing data? Please let me know if you have any good method or experience applying it Thank you.Changing dates on a sql select query to previous year
I want to be able look at the current years sales by month as in the current sql script. Then I want to look at the same information for the previous year as well. SELECT CASE EXTRACT(MONTH FROM t.transdate) WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END AS "Month", SUM(sm.quantity * sm.exsell) AS "Monthly Sales", SUM(sm.quantity * sm.cost) AS "Sales Cost", SUM(sm.quantity * sm.exsell) - SUM(sm.quantity * sm.cost) AS "Gross Profit" FROM stockmovement sm JOIN transactions t ON t.transref = sm.transref JOIN item i ON i.itemref = sm.itemref JOIN maker m ON m.makerref = i.makerref WHERE t.transource = 'D' AND sm.itemref <> '100037' AND sm.itemref <> '176176' AND t.transdate < CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || '01' AS DATE) AND t.transdate >= CAST(EXTRACT(YEAR FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || '01' AS DATE) GROUP BY EXTRACT(MONTH FROM t.transdate) ORDER BY EXTRACT(MONTH FROM t.transdate)
SSAS 2022 Connections fail following restart
I'm using an application which has SSAS 2022 OLAP cubes at the back end. We are having an issue that whenever we restart the server or the service, the connections to the SQL Server that is the data source break. I suspect this is a consequence of SSAS CU1 behaviour where the connection string is encrypted, but - because they get encrypted - there's no way to identify what the change is. SSAS is on the same instance as the SQL Server. Before a restart, i've tried adjusting a few connection properties, notably Impersonation set to Service Account Trust Server Certifcate to True Encryption for data to Optional The connection works fine with these settings. However, post reboot I get a connection error whenver I try toprocess any objects: Errors in the back-end database access module. No provider was specified for the data source. We are using MSOLEDB19 so should be fine, but it seems that post reboot the encrypted connection is somehow misconfiguring. Appreciate any guidance on what could be happening here? I can't avoid restarting the server as org policy demands servers are rebooted every fortnight.Compatibility change from 110 to 160 doubles the CPU
We are running SQL Server 2022, when we change our database from compatibility level 110 to 160, the CPU utilization eventually doubles. After switching to compatibility 160 I do run update statistics FULL on the whole database but I am unable to figure out which queries are the ones that jump to using more CPUs than on compatibility level of 110. Our frequently used queries are dynamic SQL so I am having difficulty identifying their execution stats before and after. I need some help. Thank you.
sql-machine-learning-services setup: RegisterRext.exe aborts with “MPI installation was not found”
In a SQL Server 2022 environment I have a problem in setting up the sql-machine-learning-services. When Configure Python runtime with SQL Server, executing the RegisterRext.exe command results in an error message “MPI installation was not found” I have been following the steps at this site https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16 In The Python section, an error occures when executing .\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python312" /instance:"MSSQLSERVER" Error: …. Granting NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 ... Granted NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 Error: Exception occurred in Settings Install Failed to complete the operation successfully. Usage …. The logfile RegisterRExt.log shows the detailed error: Granted NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 -- DateTime=2024-01-07T08:41:21.2670000Z --Error: 0 : System.Exception: Exception occurred in Settings Install ---> System.IO.DirectoryNotFoundException: MPI installation was not found. -- bei Microsoft.SqlServer.Server.Settings.GetFirstValidPath(IEnumerable`1 paths, String exceptionString) -- bei Microsoft.SqlServer.Server.Settings..ctor(ParsedArguments parsedArgs, String instancePath, Boolean isXcopyInstall, Boolean isAppContainerEnabled, String sqlExtDataPath) -- bei Microsoft.SqlServer.Server.Settings.Install(ParsedArguments parsedArgs, String binnPath, Boolean isXcopyInstall, FileSecurity fileSecurity) -- --- Ende der internen Ausnahmestapelüberwachung --- -- bei Microsoft.SqlServer.Server.Settings.Install(ParsedArguments parsedArgs, String binnPath, Boolean isXcopyInstall, FileSecurity fileSecurity) -- bei Microsoft.SqlServer.Server.Program.Main(String[] args) -- DateTime=2024-01-07T08:41:21.2826203Z --Information: 0 : Failed to complete the operation successfully. Environment is Microsoft SQL Server Express (64-bit) ( SQL Server 2022 (RTM-GDR) (KB5032968) - 16.0.1110.1 (X64)) ,Windows 10 , Version 22H2 Python 3.12.1 After installing and setup MPI the error remained the same. I need help regarding this problem.
Getting SQL support from Microsoft
Any got a trick to open a ticket with MS for SQL support (Paid)? We have CSP licenses, but CSP will not take a SQL ticket. I tried online, but it makes you use a personal account, then errors out. When I phone it says you can only open SQL tickets with web. We just have a urgent problem, and I think it's simple for a SQL expert. Should not be this hard to get support. Used to be easy. (BTW I am the MSP - they cut off our old way of opening tickets)
Password Expiration Behavior
If I set check_expiration = on for a SQL authenticated login, will the user get prompted to change their password either: after it expires just before it expires not at all I can't force a login's password to EXPIRED or else I'd test it myself and I've not found any documentation on what happens once IsExpired flips to 1.
