New answers tagged

-1

LoginName and Hostname from Sys.sysprocesses you've already got a cross apply to dm_exec_Sql_Text. so you've already got everything else you need.


0

I found that the distribution and publication wasn't correctly disabled. When I tried to run the disable the publishing and Distribution (as seen in my image above) I was getting an error that the process couldn't complete because the distribution database was still in use. so I used SELECT spid FROM sys.sysprocesses WHERE dbid = db_id('distribution') to ...


0

you can split a large text to smaller chunks, in the rows and load them in foreach loop container, Declare @maxRowLength int = 50; with x as ( select 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s. Over the years, sometimes by accident, sometimes on ...


0

I was able to drop the filegroups after dropping partition schema and then dropping partition function. DROP PARTITION SCHEME <<PartitionSchemeName>>; DROP PARTITION FUNCTION <<PartitoinFunctionName>>; ALTER DATABASE DBNAME REMOVE FILEGROUP PartitionFG


1

I thought I would try a solution using XML. SEDE Demo Tables DECLARE @TableA AS table ( ID integer PRIMARY KEY, Section varchar(10) NOT NULL, AccountID char(2) NOT NULL ); DECLARE @TableB AS table ( AccountID char(2) PRIMARY KEY, Name varchar(20) NOT NULL ); Data INSERT @TableA (ID, Section, AccountID) VALUES (1, 'shoes', ...


3

Sorry, I missed a step in the relationship. Try this version (though Martin's will work as well): SELECT DISTINCT o.section, names= STUFF(( SELECT ', ' + b.Name FROM dbo.TableA AS a INNER JOIN dbo.TableB AS b ON a.AccountId = b.AccountId WHERE a.Section = o.Section FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '') FROM ...


1

Search for msolap*.dll to find your installed providers and register them using regsvr32. Examples follow (your file locations may vary): C:\>regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\130\msolap130.dll" C:\>regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll" And if you are using a ...


0

select categoryMinMax.category , tableMin.ID as [tableMin.ID] , tableMax.ID as [tableMax.ID] from ( select category , min(order) as orderMin , max(order) as orderMax from table group by category ) categoryMinMax join table as tableMin on tableMin.category = categoryMinMax.category ...


2

Assuming this sample data: USE tempdb; GO CREATE TABLE dbo.o([Order] INT, ID INT, CategoryID INT); INSERT dbo.o([Order],ID,CategoryID) VALUES (1,100,13), (2,103,13), (3,102,13), (4,105,13), (5,104,13), (6,101,13), (1,201,57), (2,200,57), (3,204,57), (4,203,57), (5,206,57), (6,205,57); GO We can use a CTE to determine the first and last row, for each ...


3

You could do this without the new (2012+) window function FIRST_VALUE(), using the ROW_NUMBER(). But you could also do it using OUTER APPLY: SELECT d.CategorieID, IDMinOrder = a.ID, IDMaxOrder = b.ID FROM ( SELECT DISTINCT CategorieID FROM tableX ) AS d OUTER APPLY ( SELECT TOP (1) a.ID FROM tableX AS a WHERE ...


1

if is is just two then select name , min(Contact No) as [cont 1] , max(Contact No) as [cont 2] from table group by name


1

Here's an example which does what you want and will get you started with using PIVOT. Sorry it is a bit rushed, but hopefully it will get you started and show you how it can be done fairly easily. There will be limitations and I would fully advise researching and playing with the pivot functions because they are really powerful. -- Some sample data to work ...


0

Another option is to create a SQL Server agent job that runs on a schedule, to execute your SQL for all databases. You can also wrap your code in a stored procedure, which you can create in the model database or a "Utilities" database. One possible implementation is below (this assumes the login name and user name will be the same, but can be modified to ...


1

Your comment confirms that the original long script was using variables and these have become parameters. Probably you are benefitting from parameter sniffing here and better execution plans. The values for variables are not sniffed unless you use the ’option recompile' query hint so you will get the same plan regardless of what the runtime values are. ...


2

will this script delete all tables with the prefix of Test_ on the database I execute it on? Yes, it will DROP tables. DELETE is normally used with deletion of data. Your script will physically drop the tables. Also, please dont use INFORMATION_SCHEMA and there is no need for a cursor. Your script can easily be written using dynamic sql. EDIT : In ...


1

What build of 2008 r2? Have you looked at this connect? https://connect.microsoft.com/SQLServer/feedback/details/672153/the-ole-db-provider-sqlncli10-for-linked-server-x-reported-a-change-in-schema-version-between-compile-time-and-run-time Have you tried the workaround listed? Workaround (From above connect): I solved my problem with DBCC ...


5

Summary Add the index: CREATE INDEX index_name ON TableName (companyid DESC, id DESC) INCLUDE (active); Try this rewrite: SELECT COUNT_BIG(*) FROM ( SELECT TOP (9223372036854775807) TN1.active FROM TableName AS TN1 WHERE TN1.id = ( SELECT MAX(TN2.id) FROM TableName AS TN2 ...


2

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages. This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to ...


1

If I understood your query correctly, the following query should produce the same result, but it may be more efficient, if you create an index on (companyId, id). Columns should be included in the index in this order. select count(*) from TableName tbl1 where tbl1.id = ( select TOP(1) tbl2.id from TableName tbl2 where ...


2

Aaron Bertrand's comments copied as an answer: SQL Server just uses the actual number of bytes for storage, but take a look at your data size estimates - they'll be (half the declared byte size x number of rows) rather than (the average size x number of rows), and this can have a big impact on memory grants. Right-sizing is a good thing. These values can be ...


0

SQL doesn't support database files on SMB prior to 2012. Otherwise it would be possible but not recommended. Your priority should be regular backups of system and user databases, which can be done to an SMB share (best way to do it actually!)


8

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level. In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and ...


0

It doesn't look like an SQL issue based on what you've posted. It's possible the web service has a bug in its status handling. Even if the web service was swallowing errors, SQL doesn't allow only one column of two to be updated in the same statement; and triggers have an implicit transaction plus your update there is a single statement. As you're on ...


3

Nothing particular to worry about, but you will incur downtime as the SP runs its stuff in "script mode" during which users cannot connect. It might go without saying, but I'd ensure you have a valid (i.e. tested) backup of the databases (including the system databases) on the affected system prior to doing any patching, whether on a single-node cluster, a ...


1

I was able to reduce the query time from 24.5 minutes to 2.5 minutes by making a minor change in the query joins. The modified query makes the initial select statement from my view (14 rows for a biweekly period), and that filters both of the tables down to only the relevant rows. The new query is: SELECT t1.calendar_dttm, t2.unit, t2.start_dttm, ...


0

According to your analysis, the only thing going on is the ROLLBACK from forever. Although the command in question works just fine on my test computer, or it just might be a machine state problem. Every once in a while, a SQL Server transaction will ROLLBACK for infinite time. It is good to let it roll back if at all possible, but sometimes you have no ...


1

Ypercube's answer in the comments is spot-on. You want a half-open interval, where end-date is exclusive. In mathematical terms, it's [start-date, end-date). Then your joins are rock-solid equijoins, with no tap dancing on computed indexes or whatnot. I see in your reply that you can't change the table, meaning of course you'll have to work around the ...


0

To be honest, I wouldn't want my database on a NAS as it is most likely accessed throughout the office and it is also very vulnerable as everyone connects to it. Ideally, your SQL database should be hidden in the deepest parts of your network. However, I seeing how this doesn't sound like a high end operation, I would see if they can give you the money to ...


1

I see joins to v1 on start_dttm and end_dttm but dont see it in the view or the table listed. Looking at your definition of your view, are you sure this is an issue with the join, or does the view itself have issues? There are a few problems, and I will list them in order my brain saw them: , DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from ...


0

I was able to get the databases back online, there must have been an issue with the transaction log. I rebuilt the logs and now the databases are online. I recreated the log file by: use master open master key decryption by password = 'strongPassword' ALTER DATABASE SuspectDb set emergency ALTER DATABASE SuspectDb set single_user ALTER DATABASE SuspectDb ...


0

1. Set your SystemDB File in the Registry Note that you can only specify ONE of these damn things. On 32-bit Windows If your Windows installation on which your SQL Server instance installed is 32-bit which is doubtful...use the following key to set the path to your MS-Access Workgroup file (*.mdw): ...


0

For the 3 suspect databases can you check the log files referred to in sys.master_files physically exist on disk and have the same access permissions as the other working database log files? Just that one of the error messages you posted said the log file is missing, and unless it's a red herring, that would make their problem separate to your TDE issues and ...


2

The database version is updated automatically, when the database is first brought online, during the recovery process. The database compatibility level is never automatically modified. If your database is at 100 now, that won't change. Refer to https://msdn.microsoft.com/en-us/library/bb510680.aspx for database compatibility. See ...


1

Somewhat surprisingly no one has mentioned that this is built into SQL Server Data Tools yet. Though the functionality is basic when compared with Redgate for example. Some details in Compare and Synchronize Data in One or More Tables with Data in a Reference Database


0

One of our free tools has a full interface for TableDiff: http://nobhillsoft.com/Diana.aspx Also, check out our DB Comparison tool. It’s the only one out there that compares unlimited amount of data (non of the others can do millions and millions of records) … so long as you compare between 2 servers that are linked http://nobhillsoft.com/NHDBCompare.aspx ...


3

Another option, also involving dynamic SQL, could be, basically, to replace every & with a comma, put SELECT  in front of the string and execute the resulting query. The idea is that SQL Server allows you to represent column aliases in the form of name = value and the input string already consists of such name/value pairs, only the delimiter needs to be ...


3

Triggers run, by default, under the security context of the principal who caused the trigger to fire. In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause. Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, ...


2

Upgrade to a modern version of SQL Server. As the error states, you cannot specify a default schema for a group. You can in SQL Server 2012 and newer.


4

Aaron Bertrand has an excellent set of articles on SQLPerformance.com about how to split strings, including a very thorough set of performance tests for the various mechanisms you can employ. Since you are using SQL Server 2008 R2, you cannot take advantage of the STRING_SPLIT() functionality in SQL Server 2016; however you can make use of the following ...


-3

I dont know if that is a good idea because the URI can get the values of the variables in uri and transmit it to the database processed, but if you are receiving something like that in the database and need to threat you will suffer, the solution that i found for you its running like this: insert into tabdata(col, col, col, col, col) select ...


0

We encountered the same issue with a domain user account, however the solution that worked for us was that the login's default database was set to a database the login didn't have access to. I modified that to a database the login did have permissions to, and then login succeeded.


0

If the table is replicated. (SELECT * FROM sys.tables where is_replicated = 1). Then only SA role is able to execute "DELETE"


4

Why would you expect performance to be different with partitioning? Partitioning improves manageability but performance depends greatly on the workload and queries. For example, if you have a useful index on the DateColumn, you will likely get the same performance with and without partitioning. With no index on the column, performance may be better with ...


15

This is explained by the rules for operator precedence: Operator Precedence (Transact-SQL) As noted there, AND has a higher evaluation precedence than OR. The 1 = 1 parts of your query are redundant (and removed by the optimizer). You could equally well have written: DECLARE @Test AS table ( Id integer PRIMARY KEY, [Description] varchar(10) ...


1

The account that you use to run the job WILL need permissions to delete those files. To test your process, login as that specific user and test your job--specifically the deletion of files-- with that user. That being said, I've had issues purging SQL Server backups the way I'd like so I created this CmdExec script do so. To delete files and get error ...


1

There is no need to change from extended props to plain comments. Since I figured out that the descriptions are displayed in the visual designer part of the table in the SSDT project's tables, this is not really required to change. This article by Jamie Thomson explains the details and that the descriptions of the columns are getting visible there as soon ...


3

Unless you've badly coded it, then no. It is probably less then 10 writes per second. Even very write heavy databases are over 90% reads and under 10% writes. Consider though that such things can be replaced by, say, an indexed view on this table that looks like the separate table. Or the DML can be done in a stored procedure so it's all in one place for ...


2

You will need to create an identical linked server on each of your 3 Availability Group nodes (server1, server2, server3). Linked servers (like logins, alerts, and jobs) are instance level objects so do not sync between the Availability Group nodes. You need one on each node in case of a failover, and you need to keep them in sync manually if they ever ...


1

The final reason for this effect was that the Backupexec process responsible to take regular log backups was still present and running (so IT monitoring did not alert anything), bunt it was kind of internally crashed, so it did not do it's job properly. In order to address this kind of event I decided to add the log file events and it's metadata details to ...


1

You'd likely need to write this something like: UPDATE Table SET oldvalue = CASE WHEN oldvalue = 'val1' OR oldvalue = 'val2' OR oldvalue = 'val3' THEN 'newVal1' WHEN oldvalue = 'val4' THEN oldvalue + 'newval2' ELSE oldvalue + 'othernewval' END WHERE condition; In order to get a really good answer, ...



Top 50 recent answers are included