|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
The EXCEPT Query | |
I have a couple tables that are similar, but the data is different.
CREATE TABLE Original ( productID INT , Productname VARCHAR(20)); CREATE TABLE NewVersion ( productID INT , Productname VARCHAR(20)); GO INSERT dbo.Original (productID, Productname) VALUES (1, 'PC Case') , (2, 'Motherboard') , (3, 'CPU') , (4, 'RAM') , (5, 'SSD'); INSERT dbo.NewVersion (productID, Productname) VALUES (1, 'PC Case') , (2, 'Motherboard') , (3, 'CPU') , (4, 'DRAM') , (5, 'SD');How many rows are returned from this query? SELECT * FROM dbo.Original AS o EXCEPT SELECT * FROM dbo.NewVersion AS nv; | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
Multiple Encryptions I want to create a symmetric key with this command: CREATE SYMMETRIC KEY PIISymKey10 WITH ALGORITHM = AES_192 ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword' , ASYMMETRIC KEY HRProtection , CERTIFICATE MySalaryCert , ASYMMETRIC KEY HRProtection2 , PASSWORD = 'AnotherS$trinSD'; What happens when I run this? Assume that the asymmetric key and certificate exist in the database. Answer: This works fine Explanation: This works fine. You can encrypt the key with multiple methods of encryption. Ref: CREATE SYMMETRIC KEY - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-symmetric-key-transact-sql?view=sql-server-ver16 |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Troubleshooting a SQL Server Wait Resource Issue" - I'm encountering some wait resource entries in sysprocesses without specific details. Has anyone faced a similar situation? Any insights on how to decode or address this issue would be greatly appreciated. |
help decoding a wait resource from sysprocesses - Hi After some help . I've got a bunch of wait resources from sysprocesses that don't include specific resource type i.e its not prefixed with KEY, or TAB etc This type of format 66:1:6666666 So I presume the first numbers are the databaseid, maybe the second number is file id , but I cant decode […] |
What happens when an online index rebuild (not resumable) is cancelled? - TLDR: What is the expected behavior for an index rebuild in online mode (not resumable), which is cancelled on SQL Server 2017? Is it rolled back completely? left to be resumed? continues to cause overhead on writes? Details: Hello community! It has been a while since I posted. I have been away from SQL Server […] |
Administration - SQL Server 2014 |
How to include newly system generated tables to SQL replication? - Hi there, I have this situation that I have been trying to get around for some time but I haven't been able to despite some considerable investigation. I have transactional replication between 2 servers, the standard run of the mill set up. I noticed after some time, the replication goes out of sync (or breaks) […] |
SQL Server 2019 - Administration |
SSIS - "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". - Hi All, SSIS error: Recently the following error "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". i occurring. The SSIS package is executed through SQL Agent using a Domain service account and running as a job for years No changes to SQL Server no changes to the fileserver permissions etc. 1st thing checked is […] |
Replica stuck in resolving state during upgrade - Hi, Last week we tried to perform an upgrade on a 2 node sqlserver 2012 availability group. OS is windows server 2019. Cluster functional level is set to 9. We set secondary node to manual failover, assync commit, etc.. the secondary node was updated ok. On the primary, still sqlserver 2012, all was also good […] |
SQL Service is not starting after upgrade... - Seems to be Windows is somehow decided to upgrade something in SQL Server and after that it can't start. SQL Service is starting correctly only with trace flag 902. How to fix the issue? Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 15151, state 1, severity 16. This is a […] |
Can trigger execution be excluded only for a particular login? - Hi, I have an ERP application which connects to SQL 2019 database by an user - "vwuser". Whenever the users does transactions via ERP , data population happens. Very few people have backend access, but auditors are asking for tracking the data modifications that happen via Backend (say data corrections via Update / Delete). The […] |
CMS server sync - I have an old and a new CMS server. I am trying to keep the CMS groups and registrations in sync. I am trying this with dbatools. Here is what I have, error at bottom.... Remove-DbaRegServer -sqlinstance (my new cms server) -Confirm:$false Remove-DbaRegServerGroup -sqlinstance (my new cms server) -Confirm:$false Export-DbaRegServer -SqlInstance (old cms server) -FilePath […] |
can a DB user access that DB objects without a SQL server login? - assume this DB user (UserA) has no sql server login, not in any domain group that has a login. UserA is a windows user (user type) for Database A. UserA has only CONNECT permission to Database A. Can UserA without a login access any objects in Database A? if so, how? |
Query related issue - I have a small query that runs in 1 sec. See an example below. Use DB1 GO select EMPLD, FirstName, Lastname from DB1..Employee where dept = 'IT' But when I run the same query on the same server but in a different database, it takes 10x + more. Use DB2 GO select EMPLD, FirstName, […] |
Reporting Services |
Dynamic Subscription query with parameters???? - Hi, I'm trying to create a query within a dynamic subscription with a parameter for a Start and End Date. The query will not validate when I try to use @StartDate and @EndDate Something like: WILL VALIDATE --- Select ID from mytable where date between '10/1/23' and '10/31/23' Once I change the date to […] |
Third Party Products |
Is SQL Search 3 still supported? - I'm looking through my machine to see what I have installed and what I can remove. I found a product called SQL Search 3, which is by Red-Gate. Is it still supported? |
Article Discussions by Author |
Handling a Cancelled Online Index Rebuild in SQL Server - I was wondering what the expected behavior is when an online index rebuild (non-resumable) is cancelled in SQL Server? Are there any potential risks or considerations to keep in mind? Through Mcdvoice homepage, customer or participant can give his honest opinion (positive or negative feedback) to improve product quality, service, store quality & norms. https://mcdvoice.support/ […] |
SQL Server 2022 - Administration |
SQL Server - Kerberos Configuration Manager - Unable to connect - am currently working through replacing a development and production SQL server, moving from server 2012 to server 2022. These two servers have the SQL Service running as an AD account. Thus, I have gone through and configured the proper SPNs in Active Directory and configured Kerberos Constrained Delegation. Adding the SPN for the FQDN and […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |