New answers tagged

2

Yes. Turn on partial database containment, and you’ll get a case-insensitive catalog collation along with the collation of your choice for the data. In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. This collation is the same for all contained databases on all instances of SQL Server and cannot be changed. The database ...


1

So its not that you get a bad plan, your issue is that you have somebody becoming a victim of the auto-stats, right? Since you probably know what table(s) we're talking about, I suggest you disable auto-update at the table (or even stats) level. You can do this using sp_autostats. Now you don't affect the rest of the database. And of course handle the stats ...


8

If you have a running query or an open transaction, SQL Server doesn't have a way to move that query or transaction over to another server. When your SQL Server instance (we'll call Instance A) restarts: Running queries will be aborted Open, uncommitted transactions will be rolled back New queries will get a connection timeout We can't fix #1 and #2, but ...


2

If the server is rebooting, the SQL Server instance is not available. The only way is another SQL Server instance, and setup using Availability Groups. There will be a short outage (a few seconds) while the AG moves the primary. There is no "100% uptime" option


2

If your data is XML compliant, you can get a little more flexible by making it look like XML, then getting the data from there: WITH V AS ( SELECT * FROM ( VALUES ('123_456_789') ,('123_ABC_789') ,('1_4_7') ) [Values](ToSplit) ) SELECT q.x.value('d[1]', 'varchar(5)') A , q.x.value('d[2]', '...


2

i usually say to people don't save data with any delimiter CREATE TABLE [test] (JOBNUMBR VARCHAR(15)) GO INSERT INTO test SELECT '57299_87961_003' UNION SELECT '66515_88567_OOO' UNION SELECT '58OO7_88695_OO1' GO SELECT JOBNUMBR ,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 1)) As [no1] ,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 2))...


8

The MCSA certifications are being retired very soon. This certification retires on January 31, 2021. You will no longer be able to earn this certification after this date. I would not recommend starting to pursue any of them right now. The "replacement" exam is Microsoft Certified: Azure Database Administrator Associate. Notice that the exam ...


0

Can you check what login was mapped to the dbo user in the original SQL Server instance (where the database came from)? My guess is that the dbo user points to a login that doesn't exists, and that is wnat is setting of this situation. If that dbo (again, check on the original server) points to a Windows login, then just create that login. If it points to a ...


2

I think I've seen this question elsewhere, and I probably asked for more info. And I'm doing it again. What is your purpose with this? Why are you using dynamic SQL? What part of your SQL is dynamic, and what part of the input can be modified by the evil criminal? Without such details, all we can do is propose something, and then somebody else can counter ...


3

This is not a safe way to do it. You can run this quick demo to see what could go wrong create database demo Go use demo Go Create table SomeVeryImportantData (id int, txt varchar(30)); insert into SomeVeryImportantData values (1,'some txt'); DECLARE @TSQL NVARCHAR(MAX) = N'truncate table SomeVeryImportantData;' CREATE TABLE #ThisIsATable (Id INT, VAL ...


1

Yes, you can use both the Package Deployment Model (msdb) and the Project Deployment Model (SSISDB) in tandem with no ill-effects. As a consultant, if I walked into a place that had both, it'd indicate that they've been using SSIS before the 2012 release and there's been no reason to upgrade existing work to a new model. In heavily regulated industries, ...


5

SQL Server estimates cardinality for joins, not physical operators. The inner join in question will have the same estimate regardless of the physical operator employed (hash, merge, nested loops, or apply). The physical operator may affect the display, but the selectivity of the logical join is the same. With that out of the way, logical join estimation is ...


2

Figured it out. The job step is also using a Proxy account, which is the same account. So you need to change the proxy account credentials as well.


0

In my case I had to add to the step: SET ANSI_NULLS, QUOTED_IDENTIFIER ON; And then the creation of the computed column. ALTER TABLE ... This way the Agent jobs run smoothly


2

Who is right - me, Learning material, or you (your option ) ? None of the options is correct. The "Disk Usage Standard Report" is a database-level report that displays disk space utilization, not IO. And what they mean by "types" of read/write operations ? Judging that the "correct" answer was SQLIOsim, it probably meant ...


5

One way to achieve this is to ensure the subquery is correlated i.e. it depends in some way on the outer query. Using an AdventureWorks example: SELECT E.BusinessEntityID, R.DepartmentID FROM HumanResources.Employee AS E CROSS APPLY ( SELECT TOP (1) D.DepartmentID FROM HumanResources.Department AS D ORDER BY NEWID(), ...


0

Try this: CREATE TABLE dbo.CustomerTransaction ( CustomerTransactionId int primary key identity(1,1), CustomerId int, Price decimal(10,2), Quantity int, TotalAmount as (cast(CustomerId AS decimal(10,2)) * cast(Price AS decimal(10,2))) PERSISTED )


0

SSRS can be upgraded seperately to the latest version, I believe without upgrading the db engine, but SSRS cant be upgraded without the db engine running, as the upgrade will need to make changes to the Reportserver database. Im not sure what you mean by "migrate off". SSRS requires a running db to work with. Although another contributor had stated ...


1

Because of licensing concerns I cannot have SQL Server 2016 running but I still need to upgrade the SSRS to 2016. They require the same license. So if you can't run SQL Server 2016, you can't run SSRS 2016.


11

This is due to a strange limitation1 in the accuracy of the datetime data type, as documented: Accuracy | Rounded to increments of .000, .003, or .007 seconds The solution would be to use datetime2, which provides better accuracy, as you can see in this dbfiddle demo: The return type of the DATEADD is dynamic based on what you send into it. So the ...


2

I fear the where clause with the in is causing problems. Try joining to the inserted table instead. Here are two examples - one with the in clause and one with a join to the inserted table. The results are different using my sample data. drop table if exists shipment; drop table if exists alphauniversal; go create table SHIPMENT (cm_controllerID int,au ...


1

I would recommend using this trigger template where possible. change it to suit your needs, of course. It uses an IF UPDATE(column) to short circuit if the DML doesn't reference the column the trigger cares about. It also uses the EXCEPT clause for what I've found the fastest way to compare the INSERTED and DELETED tables. Just add more columns to the ...


0

CHECKDB by design will use tempdb space for temporary storage and it will fail if you run out of space. Make sure you presize your tempdb. You can check how much space it needs by issuing WITH ESTIMATEONLY command: DBCC CHECKDB ([WideWorldImporters]) WITH ESTIMATEONLY; You will be able to get an estimate space requirement for tempdb: DBCC results for '...


16

My limited testing shows that the "IS NOT NULL" predicate can be eliminated if: the column is declared as NOT NULL in the table definition, or the column is protected from nulls by an active, trusted check constraint Here's a simple test table: CREATE TABLE dbo.Test ( Id int IDENTITY(1,1) NOT NULL, DeclareNotNull int NOT NULL, ...


1

Perhaps auto-update stats kicked in. AUTO_UPDATE_STATISTICS_ASYNC ON is your friend in that case. Check the index stats date to see if it coincides with the slowness: SELECT STATS_DATE( OBJECT_ID(N'YourSchema.YourTable') , INDEXPROPERTY(OBJECT_ID(N'YourSchema.YourTable'),N'YourIndexName','IndexID') ) AS LastStatsDate; -- dan-...


1

Uninstall with Revo uninstaller https://www.revouninstaller.com/ then install the newest version again. Revo Uninstaller removes all register keys for you.


1

These did not work for me: exit Exit-PSHostProcess One that worked is: [Environment]::Exit(0)


Top 50 recent answers are included