New answers tagged

0

Sounds like you don't care about the records unless they are of type "Balance Update." Also looks like you're just trying to return the highest of the "balance Update" records, and not a Sum of them. In that case, I think this will do the trick. This query shows what the second one is doing, which is using the ROW_NUMBER functionality to order the records ...


2

Yes, it failing is expected. It is documented here. AlwaysOn Availability Groups and SQL Failover Cluster Instance and Privileges When installing the Database Engine as a AlwaysOn Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY ...


0

1) Yes. It's a maintenance/documentation nightmare but technically there's no reason it wouldn't work. 2) In general each null will be one bit of storage. So 80 null fields might be 10 bytes per row. The full answer is that it varies depending on data type but with varchar for the most part it's a good rule of thumb. Some alternatives where you expect ...


1

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command? DECLARE @RunStoredProcSQL VARCHAR(1000); SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]'; --SELECT @RunStoredProcSQL --Debug EXEC (@RunStoredProcSQL) AT [LinkedServerName]; Print 'Procedure Executed'; That's what I have used successfully in the ...


0

Sounds like a job for SSIS. Set up an SSIS package, and use the Execute SQL Task to execute your stored procedures. You can then execute the SSIS packages through Agent jobs.


5

@Lennart's answer can be simplified to a single Group By: SELECT LogDate, StartOfDay, EndOfDay, DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds FROM ( SELECT -- instead of FORMAT better use a Standard SQL CAST: CAST(LogDateTime AS DATE) AS LogDate, MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay, ...


1

You can modify your query to: SELECT LogDate, MAX(StartOfDay), MAX(EndOfDay), DATEDIFF(s, MAX(StartOfDay), MAX(EndOfDay)) FROM ( Select LogDate, StartOfDay = MIN(case when EventCode = 1 THEN LogDateTime END), EndOfDay = MAX(case when EventCode = 2 THEN LogDateTime END), EventCode FROM ( Select ...


2

I believe the problem is that you are executing all 5000 update statements as a single transaction. You need to break them up so each one is done in its own transaction. Then there are two options: If you are in simple recovery mode (or can temporarily switch the database to simple recovery mode), then breaking them up should be enough for the database to ...


0

Working out which Log Backup to restore is all based on LSN's. You have to apply all log backups since the full (or differential, but you didn't mention them) backup, including log backups taken while the full backup was in progress. There is no danger in trying to restore a log backup out of sequence, it just won't work. Kin answered above how to find the ...


0

how do I know which transaction log backups to select? You can find that out using msdb..backupset table. To automate that, you can use RestoreGene - TSQL or PowerShell version This procedure queries msdb database backup history and database file details in master. It builds and returns RESTORE DATABASE commands as it's result set, it does not ...


0

Adding MSSQL as a Generic Service Resource is not the Answer. That will just put the Cluster Manager in charge of the SQL Server Service, OK, yes it will fail-over automatically, but you will notice in SQL Server Configuration Manager that your services are now set to "Manual" indicating that the Cluster Manager is now in control of your SQL server Service. ...


3

Run this query: SELECT ASCII(' ') AS [SingleSpaceString] , ASCII('') AS [NoSpaceString] You will note the the Value for [SingleSpaceString] is '32', while [NoSpaceString] is NULL. I'd suggest adding ASCII(Box.Ice) to your select statement to confirm that a true space is the value, vs. an odd character.


3

The longer, more answer-y version of my comment Having 500k rows of backup information logged in msdb isn't really necessary for 30-ish databases. Even if you were taking log backups every minute, it would take a long time to get there. What's a reasonable amount of backup information to retain? Probably as much as you retain physically. If you keep ...


0

So I didn't got any luck with the deadlock graph. Doesn't seem to appear in my SQL Logs. But, I did find out that another job has been scheduled to run at the same time than my job, and it was taking backups and sending e-mails at the same time that my job was trying to cleanup the mail history. So changing the schedule of the other job got my problem solved!...


2

As you explained in the comments section, the objective is to prevent developers logging into production server. There are many ways to achieve this. The first and foremost is a company policy. Talk with your manager. Prepare for the meeting by making a list of reasons why access should be restricted. In addition, try to invent a number of counter-arguments ...


1

Quite often you can use row_number() to select only certain rows of the data. so maybe something like this would work: select * from ( SELECT instance_name , DU.database_name , DU.collection_time , DU.dbsize , DU.logsize , DU.reservedpages , DU.usedpages, row_number() over ( partition by instance_name, DU....


2

ALTER TABLE ... SWITCH can sometimes be useful for this type of thing. Specifically it can allow a large table with an IDENTITY column to be switched into a different table with the same structure but no IDENTITY column thus (after a drop and a rename) effectively allowing the IDENTITY property be toggled as a metadata change (example). With the tables ...


2

I believe what they are talking about is a partition SWITCH. Not really what you are looking for here. It's great for loading data into a table quickly (from the point of view of the user) but not really for an entire table. If I were you I'd do a re-name swap. Create a new table TableName_New where the computed column is just a regular column. Make ...


2

Man, I'm terribly sorry. I'm 'answering' stating that there's no simple solution as the one you devised. You presented us a very interesting challenge. I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself. If you, please, ...


3

the sub-select by itself still takes less than a second and returns about 20 rows ... Any suggestions? In that case you may as well materialize it into a #temp table and join onto that instead. SELECT BS.[database_name], DATEDIFF(HH, MAX(BS.backup_finish_date), GETDATE()) AS last_backup, DATEDIFF(MI, MAX(BS....


0

Just as Paparazzi mentioned, you are actually doing a product downgrade. But with everything already in place, I would suggest to download a copy of SQL Server Enterprise (Evaluation version) and try to upgrade the standard version, and if everything is fine, I will make the necessary changes (i.e. remove those enterprise features, such as partitions). ...


4

SQL Server Developer Edition is functionally equal to Enterprise Edition. The error is stating you are using features that only Developer or Enterprise support, such as Table Partitioning in this case. In order to use Standard, you cannot use any Enterprise features. Check Features Supported by the Editions of SQL Server 2012 for more details. One way to ...


1

If you are using SQL 2012 as your tag indicates, then you can do this with a recursive CTE, although it's a little different since you find the child first and then the parents. But Take a look at the sample below. The Setup DECLARE @Rentals TABLE ( PK INT NOT NULL , CustID INT NOT NULL , ProdID INT NOT NULL , OverrideByPK INT NOT NULL ...


0

Have you tried to give permission to Domain1\NewUser to MSDB(db_owner for tests)? Here you have a great article about the SSIS Proxy users: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/ Especially excerpt: EXEC msdb.dbo.sp_update_proxy @proxy_name = N'proxy_name', @enabled = 1


1

The file, when sent as an attachment, is converted into 7-bit ASCII, which makes the file a lot bigger than it would normally appear to be. As @M.Ali pointed out in his answer, you can adjust the maximum message size to accommodate your large query. However, what are you sending via email that needs to be so big? Are you sure that amount of info, in ...


2

You will have to change the allowed Attachment file size by going to the following location: Go to SQL Server Management Studio --> Management --> Database Mail This will fire up the Configuration Wizard. Click Next --> Select View or change system parameters --> click Next. Now change the Maximum File Size (Bytes) property and click Next, then ...


2

Unfortunately after searching SolarWinds' KB for about 10 minutes I cannot find what "Ineffective Statistics" measures. That information would be helpful. However, based on your question I'll infer that it means that the Statistics objects may be out of date or have taken quite a few modifications and Database Performance Analyzer has not detected an update ...


0

--STOP the Distribution Agent: sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db --START the Distribution Agent: sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre> Example: exec distribution.dbo.sp_MSstartdistribution_agent @publisher = '...


-1

For me it was the BitDefender firewall that made my SSM ultra slow Apparently, some sort of a firewall blocking for getting access to the database list


2

how to change the file directory of your log and data files and what are the effects Can I just change the settings of the instance to point it to the new directory, detach the existing databases move the data files and attach it back? Please share what would be the best approach in doing this. Below are two scripts I've used for this purpose ...


0

If you open the properties of the publication, then select the Subscription Options section, you will see a property named Replicate Schema Changes. Set that to true and any schema change will be replicated and display in the Distributor to Subscriber History in Replication Monitor as DDL change. No need to stop and restart any agents. I have found that you ...


1

No, the Developer Edition is for developers only. If it contains production data, you are not licensed and it doesn't matter that only the ERP system accesses the database, the users of your ERP system count as users of SQL server.


0

Some of those indexes seem to have a lot of included columns. A table definition will provide context into the width of these columns. What is the clustered index key? You need to look at the workload being run against this table. What queries are being executed, how often are they being executed, is this going to change in the future? I would probably ...


0

Just create a view CREATE VIEW test_view AS SELECT case when year(DOB)<=1900 then null else DOB end as DOB,sometext,id from test; and then use it instead original table: select * from test_view


4

While waiting to hear back on a few questions that I posted in a comment on the Question, I will at least reiterate one of my questions: "What about your current stats leads you to suspect that SQLCLR usage is in any way related to performance issues?" From what I see of your output, SQLCLR is taking up very little memory. It has 110 MB of physical ram for ...


0

So this is what I came up with as a workaround if the intention is that multiple people in your organization should be notified if a job fails and a different group of multiple people for successes. You will notice the Steps 1-3 are the normal tasks that the schedule job is uses for as you would do for your task. There can as many steps as needed before ...


1

Same version of Powershell on the servers/vms hosting SQL Server 2008R2, 2012, and 2016? A parsing error that removes the double quotes around the query would result in this error. Lonny Niederstadt


1

Seems like you're right on the tip of the answer. Just group by the customer, the year, and then the month. USE Masscomm_XT; WITH C0 AS ( SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate FROM dbo.reptview_LDUsageTrend ) SELECT C0.CustomerName ,C0.PostingMonth ,C0.PostingYear, SUM (...


0

Based on one of the suggestions that Dan Guzman gave me in your answer, and after read this question I found a workaround to my problem. Apparently, there is no way, in scenario as described, to grant to userX SELECT permission in the viewABC without grant also SELECT permission on tableA. The solution that I found was the following: in schemaD I ...


0

I would create a main sql agent job that executes the two jobs you mention in your post. You can execute sp_start_job to accomplish this, something like... Step 1: EXEC sp_start_job 'job A' Step 2: EXEC sp_start_job 'job B' Step 2 will not execute until Step 1 is finished.


0

A simple and easy way to do this would be to import the csv into a table, then manipulate/view. If you'd prefer a graphical method instead of using tsql; right click your database and select Tasks, and then select Import Data.


5

Very easy to test. CREATE TABLE T(X INT); GO INSERT INTO T SELECT TOP 10 1 FROM sys.all_objects; GO CREATE TRIGGER TR1 ON T INSTEAD OF DELETE AS DELETE TOP (8) FROM T; GO CREATE TRIGGER TR2 ON T AFTER DELETE AS SELECT COUNT(*) FROM DELETED; GO DELETE FROM T; DROP TABLE T; Answer 8 This is the correct behaviour. The after trigger should ...


0

You can refer to below link for how to read csv file in SQL Server: http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2015/02/09/t-sql-read-csv-files-using-openrowset/ Based on that you will be able to work with your csv data as a table and then you can create the update query as required.


2

I can't think of a way to avoid granting SELECT permissions due to the broken ownership chain in the view's underlying objects. One solution is to change the owner of TableA to ownerX so that the view ownership chain is unbroken: ALTER AUTHORIZATION ON OBJECT::dbo.tableA TO ownerX; Note that this will give ownerX CONTROL permissions on dbo.tableA. If ...


1

You can right click on the job in SSMS, go to properties, then to the 'schedule' option. From the schedule add your daily schedule to the 4h schedule. Default behavior of SQL Server jobs are that if a job is already running and the same job comes up again, it'll wait for the prior execution to finish before starting a new one. What you're describing is ...


2

How to migrate windows login in from one sql server to another sql server on another domain So whatever logins the database has in DMN1 cannot be used in DMN2 anymore. it would take a lot of time to sort out a lot of windows logins in DMN1, get their equivalent login in DMN2 and map them to the right databases. What would be an easier way of doing ...


1

I cross-posted this to Stackoverflow and found a working answer, even if it meant having to type out the column names (which explains why my WHERE clause didn't work) SELECT ins.[name], ins.[created_at] FROM INSERTED ins JOIN DELETED del ON ins.id = del.id WHERE del.[name] <> ins.[name] OR del.[age] <> ins.[age]; ...


0

I am not sure that this is the best way (I would welcome any comments to tell me how it is not), but it's very simple (and quick if you have a small database): First, backup the database to a .bak file. Then, restore the database from the same .bak file, choosing the new .mdf and .ldf file locations under file options for the restore task. I wouldn't do it ...



Top 50 recent answers are included