|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
More Left Joins | |
I have a number of tables with this data:
CREATE TABLE CustomerLeft (CustomerID INT, CustomerName VARCHAR(20)) GO CREATE TABLE CustomerContact (CustomerID INT, CustomerEmail VARCHAR(100)) GO CREATE TABLE EmailDomain (CustomerEmail VARCHAR(100), domain VARCHAR(20)) GO INSERT dbo.CustomerLeft (CustomerID, CustomerName) VALUES (1, 'Steve'), (2, 'Andy'), (3, 'Brian') GO INSERT dbo.CustomerContact (CustomerID, CustomerEmail) VALUES (1, 'steve.jones@red-gate.com'), (2, 'awarren@sqlservercentral.com') GO INSERT dbo.EmailDomain (CustomerEmail, domain) VALUES ('steve.jones@red-gate.com', 'red-gate.com') GOIf I run this query, how many rows are returned? SELECT * FROM dbo.CustomerLeft AS cl LEFT JOIN dbo.CustomerContact AS cc ON cc.CustomerID = cl.CustomerID LEFT JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail | |
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) |
Changing the Default I have run this code in SQL Server 2019, which has no objects in the dbo schema: CREATE USER apiuser FOR LOGIN apiuser WITH DEFAULT_SCHEMA=webapi GO ALTER ROLE db_datareader ADD MEMBER apiuser GO CREATE TABLE webapi.states (stateid INT NOT NULL CONSTRAINT statespk PRIMARY KEY, statecode VARCHAR(2), statename VARCHAR(20)) GO INSERT webapi.states (stateid, statecode, statename) VALUES (1, 'AK', 'Alaska') GO I log in to a second query window as apiuser and run this code: SELECT TOP 10 s.statename FROM states AS s; GO This works and I get Alaska returned. I now run this code in my first, administrative window, knowing there are no objects in the Sales schema: ALTER USER APIUser WITH DEFAULT_SCHEMA=Sales GO I return to my second window, which is still logged in and connected to the SQL Server. What happens when I run this code? SELECT TOP 10 s.statename FROM states AS s; GO Answer: I get an invalid object name for states Explanation: The permission change takes place, and when I requery on the same connection, the schema has changed. Sales is checked, and there is no Sales table. The dbo schema is checked and the object is not found, so an invalid object is returned. The user still has permissions to query the States table if the schema is included. Ref: No good reference for this, but if you switch the default schema for a user, you will see the error. |
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 - Development |
tsql help - Hi All, Need some TSQL help. Need to extract a portion of the string of [tran_log_writes] column, convert that value to GB and display it as seperate column as "TLOG-gen-GB". Below is the sample data. CREATE TABLE [dbo].[test2]( [tran_log_writes] [varchar](8000) NULL ) GO INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)') GO INSERT [dbo].[test2] ([tran_log_writes]) […] |
SQL Server 2016 - Administration |
WSFC - DR question. - I'm trying to write a DR doc with no test system for someone with no experience of SQL / clustering. I know! Don't ask. It's a weird old place where I work but they pay, I do. So, 2 nodes at site 1. 2 at site 2. 2016. AG. WSFC underlying on 2016. File share […] |
SQL 2016 Patches and CUs marked "duplicate (do not use) - What's the story with a lot of SQL 2016 patches being marked "duplicate (do not use)"? It's not just one or two, it goes all the way back to CU1, and seems to be almost all of the patches, but there's no explanation or reference to what should be used or how to know if […] |
SQL Server 2019 - Administration |
Odd Connect Issue With FQN - I have a 2019 SQL server that I can connect to with the NetBIOS name, but get "Access Denied" when I try to connect to it using the FQN. I have tried from multiple clients, including the console of the problem server. I have verified the normal things, access rights, group membership, that the domain […] |
Need to enter the port to connect... - This is one of the mysterious things that clearly something changed, and n0body knows what. We have our monitoring software installed on a SQL 2019 server. Last week, it stopped connecting to two servers, SQL 2016 and SQL 2014 versions. Both are named instances. The connections were set up as ServerName\InstanceName, and this has been […] |
SQL Server 2019 - Development |
Audit data - Hi Team , We have a requirement from the client , in which we have get the data about all users(ip address , machine etc) who have performed DML operation on any table in the database . If there were few tables I would have created a DML trigger . Is there any tools in […] |
Azure Data Factory |
using ADF to extract Dynamics 365 data - Hello, we a migrating from on premises CRM to cloud D365 and need to ETL data into on-premises sql server/data warehouse. I have come across 2 options... KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365 / SSIS Productivity Pack Azure Data Factory. We are a company that uses Microsoft products and services and already have […] |
Reporting Services |
showing character "?'' as total of pages in the top toolbar navigation panel. - Hello fellow, I would like to post this issue which is driving me insane referred to the pagination control on the navigation top menu. On initial element listing paging shows up like image above. Whereas I'm moving forward thru next pages, it remains as such: Same issue continues till get the last page. The only time […] |
SSRS 2012 |
Why would Microsoft Publisher come up? - I don't do much with SSRS these days, but I do have a SSRS report I support. The users were having a hard time getting to the report, but that has been resolved. (It was a change on the server.) However, now they're getting something which I don't understand at all. They access the report […] |
How to config list spread the data on 2 column (Like side Z ) - Hello, I can`t figure out how to config my list to spread on 2 column (Like Z) and only then continue to the next page. pictures attached picture 2: example to my demand |
Powershell |
Append MMYYYY to File to Copy - How can I adjust this script to add MMYYYY to the file name before the extension on the Copy? thanks Before: dys_ihhist.txt After Copy to Dest dys_ihhist_072023.txt $source="c:\fileloading" #location of starting directory $destination="c:\filecopy"; #location where files will be copied to $files=@("*dys_ihhist*") New-Item -ItemType Directory -Force -Path ($destination); Get-ChildItem -recurse ($source) -include ($files) | Copy-Item […] |
Analysis Services |
Can workspace database be recreated in tabular cube? - Hi All, I need to modify an old tabular model for which workspace database has been deleted accidentally. Is there a way to recreate the workspace database? I deployed the cube but it only creates main DB not the workspace database. |
Strategies and Ideas |
What is the meaning actually for DWH - Hello.. Im working on a data facility almost since 8 years on a BI and DWH design … and there was always a question on my head about designing the DWH when we talk about storing data .. are we talking about every part , column , row , cell of data or are we […] |
Integration Services |
Script task to get oauth 2.0 token error - I am trying to figure out a way to get oauth2.0 bearer token from the rest API. So from Postman I can do a POST method for "https://helloworld.org:443/xyz/oauth2/token" And in the body, I choose x-www-form-urlencoded and put grant_type as "password" and put the values for my username and password. This gives me a token which […] |
SQL Server 2022 - Administration |
Find Version Mismatch - Hello, I'm looking for a way to generate a cross-sever report [using TSQL(without LinkedServers) or Powershell ] to show SQL Server AlwaysOn replicas that are not in the same version. Can someone please guide me? Thanks in advance |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |