|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Roles Across Schemas | |
I ran this code in a new database:
CREATE TABLE dbo.mytable (myid INT) 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 CREATE LOGIN apiuser WITH PASSWORD = 'Demo1234' GO CREATE USER apiuser FOR LOGIN apiuser WITH DEFAULT_SCHEMA=webapi GO ALTER ROLE db_datareader ADD MEMBER apiuser GONext, I log in as the apiuser and change to this new database. I run this: SELECT TOP 10 s.stateid, s.statecode, s.statename FROM webapi.states AS s; GO SELECT myid FROM mytable; GOWhat happens? | |
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) |
Resuming Indexes on Old Databases I have a SQL Server 2019 developer edition instance with a database that is set to compatibility mode 130. I run this code: CREATE NONCLUSTERED INDEX [NC] ON [dbo].[RandomTestData] ( [ID] ASC, [DateOfBirth] ASC )WITH (ONLINE = ON,RESUMABLE = ON) GO What happens? Answer: The index creation starts and can be paused Explanation: In testing, this appears to work with SQL Server 2019, regardless of the compatibility level. Even with 100 set, there is no syntax error and I can pause and resume the index. The image below shows me checking the compat level and then resuming the index. Ref: CREATE INDEX - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-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 |
Log Shipping Jobs stop working automatically - Dear Friends, On my Log Shipping configuration , sometimes , more frequently actually, the Backup,Copy and Restore Jobs stop working automatically.. I have to check on them and run manually...and some times the restore has errors which force me to reconfigure it again..Any comments / advises on this will be highly helpful..Thank you. |
Trace Flag 3444 - I just discovered trace flag 3444 set on a SQL 2017 srver, and I can't find reference to it. Has anyone come across this trace flag and what is it used for? Thanks MC |
SQL Server 2016 - Administration |
Need to read ErrorLog to get the END TIME of last restore? - is there a stored proc somewhere for sql server 2016 that can return latest restore database duration in seconds and in minutes for each database on the server? I cannot find a ready code, and the GPT cannot help. it is hard to believe though that there is no working code somewhere. I looked and […] |
SQL Server 2016 - Development and T-SQL |
WITH RECURSIVE gives error - WITH RECURSIVE factorial(F,n) AS ( SELECT 1 F, 3 n UNION ALL SELECT F*n F, n-1 n from factorial where n>1 ) SELECT F from factorial where n=1 Error Msg 102, Level 15, State 1, Line 26 Incorrect syntax near 'factorial'. Completion time: 2023-07-07T14:16:59.5379702-04:00 If i remove RECURSIVE option it works fine. |
SQL Server 2019 - Administration |
[SSIS Server Maintenance Job] failed - Executed as user: ##MS_SSISServerCleanupJobLogin##. The SELECT permission was denied on the object 'availability_databases_cluster', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229). any help with this will be appreciated! |
SQL Server 2019 - Development |
List Month End Dates Between Two Dates - Hi everyone I am working on a query where I need to list all business month end dates between two dates. The two dates would define the start and end periods but these two dates are dynamically created: DECLARE @START_DATE DATE = DATEADD(YEAR,-1,GETDATE()) DECLARE @END_DATE DATE = GETDATE() I am not sure how to generate […] |
sql Convert datype int to string - hallo all, let me ask i have a query convert like this : INSERT INTO STG_RECOVERY (MODEL, VALID_ON, NOREK, KOL_AWAL, KOL_AKHIR, BD_AWAL, BD_AKHIR, BD_KL_AWAL, BD_KL_AKHIR, AMOUNT, RECOVERY, RECOVERY_PCT) SELECT 3, A.validon VALID_ON, A.Acct, A.BIKOLE KOL_AWAL, B.BIKOLE KOL_AKHIR, A.BAKI_DEBET BD_AWAL, B.BAKI_DEBET BD_AKHIR, NULL BD_KL_AWAL, NULL BD_KL_AKHIR, C.BD_KL AMOUNT, A.BAKI_DEBET - B.BAKI_DEBET RECOVERY, ((A.BAKI_DEBET - B.BAKI_DEBET) / […] |
getting next date - Is there a way to show the next date that is greater but not have them duplicate like the highlighted below? I am using sql server and doing a select from the columns below where the service date is greater than da discharge date. This is what I want it to show. It […] |
Remove duplicate data - Hi Below is the create table and insert script. I am attaching current output and expected output. USE master GO CREATE TABLE [dbo].[test_07052023]( [EMAILS SENT] [float] NULL, [mmClientID] [float] NULL, [Report Run Date] [nvarchar](4000) NULL, [Issue Name] [varchar](4) NOT NULL, [Issue Year] [int] NOT NULL, [Product] [varchar](17) NOT NULL, [Rate Card Name] [varchar](9) NOT NULL, […] |
Integration Services |
deploy ssis package to MSDB db in SSIS server using azure pipeline - Has anyone tried deploying ssis to MSDB db in SSIS server using Azure pipeline? I am used to do the file system deployment using Azure pipeline but not to MSDB db. Din't find any good article on this on the internet. |
Anything that is NOT about SQL! |
Need recommendations on replacing a NAS device for home use. - In my home office (SQL Server DBA, now a 13-year retiree) I have two NAS devices, a D-Link DNS-343 (4x2TB) and a WD EX4100 (4x10TB). I had to take the WD NAS off my surge protectors due to repeated 'power supply failure' notices, and it has worked since with no problem. My Win 10 machine […] |
Administration |
Transaction log BU fails in Maintenance Plan fails when there is no Full BU - I have 3 maintenance plans for full backups, differential and transaction log. The job for log backups fails in when there is no full backup. Which is logical of course. But is there a way to prevent this error from happening? I would like an option that it will not try to backup the log […] |
SQL Server 2022 - Administration |
RAISERROR Serverity 16 Showing in SQL Agent Log - We are evaluating SQL Server 2022 and have upgraded an SQL Server Fail Over Cluster and AG with with CU 4. In order not to run the jobs created by Reporting services on the passive node, we have added a step to check to all SQL Agent Jobs that does a RAISERROR to exit out […] |
SQL Server 2022 - Development |
SQL Server AOAG Add 3 IP's to LIstener with Powershell Script Not working - Team, Why does the below not work? Using parameters for listener name and ip addresses. When i hard code the IP addresses in there it works even with the listener as a parameter . I assume it has to do with the quotes. Any assistance is greatly appreciated. See full section of code not including […] |
Issue with sql server stored procedure--``i need to update multiple columns from - use dB DROP PROCEDURE [dbo].[pre_update] GO SET QUOTED_IDENTIFIER ON CREATE TYPE GetTrackingNo AS TABLE (TrackingNo Int ); go CREATE PROCEDURE pre_update @TVP GetTrackingNO READONLY AS Begin SET NOCOUNT ON; DECLARE @trackingTVP AS GetTrackingtNO ,@col1 VARCHAR (MAX) ,@col2 VARCHAR(100) ,@col3 VARCHAR(MAX) your text insert into @TrackingTVP (TrackingNO) (select distinct TrackingNO from sub where date between '2023-01-01' […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |