New answers tagged

1

There should be no negative impact on performance esp when you have encountered PFS page contention. Just make sure you dont blindly over create files. This script from Tigertoolbox (Microsoft Tiger Team) will help. Depending on the version of sql server you are using, you might need or might not need below trace flag : Trace Flag 1118 – Full Extents Only ...


0

The + operator only means concatenation when both operands are of character data type; in your case one of them is an INT, and, according to the data type conversion precedence rules: When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. INT ...


0

I had same issue once, You may need to recreate the linked server with this set to false (or just change it in the linked server properties->server options): EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'remote proc transaction promotion', @optvalue=N'false' Additionally, try using OPENQUERY to run this against the link syntax: ...


0

My assumption of stored procedure mechanism was role/user only requires EXECUTE to perform the procedure. We don't need to concern the permission setting of each DB object like CRUD to table and so on. This statement is true when ownership chaining applies. With an unbroken chain, users do not need permissions on indirectly referenced objects. However ...


0

on basic configuration sql server is need specific role for grant schema. like when you create new user the default role is public. But if you need access select some table on database you need grant that user to select or anything else you need. on your script the user only grant execute on procedure BlobCheckExisting.usp_DoesStoredProcedureExist but in ...


0

That's all in msdb. Note that the new server must be at exactly the same version to restore a system database. System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server ...


0

Give this a try: SELECT * /*12 columns*/ FROM [dbo].[EnterpriseGroup] WHERE EnterpriseGroup.ChildId = 123 AND StatusCd >= 2 UNION SELECT * /*12 columns*/ FROM [dbo].[EnterpriseGroup] WHERE EnterpriseGroup.FatherId = 234 AND StatusCd >= 2 And have these two composite indexes (with the columns in the order given): (ChildId, StatusCd) (FatherId, ...


1

Another option is Microsoft.SqlServer.XEvent.Linq instead of T-SQL to process XE data in Powershell or .NET application. The QueryableXEventData class can process XE data directly from target files or from the live event stream. The live steam source is generally appropriate for events that occur relatively infrequently (as in your monitoring use case) ...


1

Here what you need to do: 1) use #Temp table 2) break down query on parts, get data step by step 3) make sure columns which you use in the WHERE or join ON clause, are indexed Temp Table - column data types below are just an example, use data types that exactly match column data types on your tables create table #UserInfo ( tID ...


1

The biggest problem I see is in your joins. You're joining the "id" of Users, to the "id" of Posts and Comments. This basically says, take every record in Users and join it to every record in Posts, then join it to every record in Comments. Presuming that the UserID exists as a foreign key in Posts and Comments, the code below should work. If not, you'll ...


1

SMO is still the gold standard for scripting. It's what SSMS uses internally, and you can use it from .NET or PowerShell.


1

Ownership chaining is generally not something to worry about. It only implies permissions for DML (INSERT, UPDATE, and DELETE), SELECT, and EXECUTE operations. It does not allow for CREATE, ALTER, DROP, etc. The tricky part / nuance here is that, by default, objects have a NULL owner, meaning that their ownership is implied as being the owner of the schema ...


1

Is this the right approach? Does it have any unintended consequences? I am most worried about how ownership chaining may affect this approach negatively with dbo being the owner of the schema and the role (as well as other schemas/objects in the DB), and the alter permission being granted on the schema. The ALTER permission in the training schema ...


0

Database corruption is normally a symptom of an underlying hardware ( or occasionally OS) problem, bad disks being a classic example. So you should expect more trouble & work to either a) migrate to another server or to b) identify what the underlying cause is & resolve it. Personally I'd go with a) whenever possible.


1

I met a similar issue recently when inserting huge amount of data using ogr2ogr from shapefile to SQL Sever. The error message looks like: ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver][SQL Server]The app domain with specified version id (46) was unloaded due to memory pressure and could not be found. And the solution ...


1

There's a strong possibility that CU 4 for SP2 which was release in January of 2015 will resolve the issue you are experiencing: https://support.microsoft.com/en-us/help/3029825/fix-dbcc-checkdb-checktable-command-may-take-longer-in-sql-server-2012 That being said, you are running a SQL server that is out of support and SP2 CU 4 which should resolve the ...


0

You can create a view with something like : create view MyView as select * from YourTable where CanModify =1 Then you can grant insert, update, delete, select (the appropriate permission) on the view for the user. With this, he will only be able to update row where the "canModify" =1 If ever he needs to be able to read all rows, then you can grant select ...


0

In my opinion, if you are stuck with sql server 2012, the Row Level Security is not going to work for you. So you have two options: 1) application layer, which was already mentioned. 2) on database level, an trigger may work


0

You said that you have already installed the feature, but when you look at the sql server configuration manager do you see it there? according to the documentation: IsFulltextInstalled The full-text component is installed with the current instance of SQL Server. 0 = Full-text is not installed. 1 = Full-text is installed. NULL = Invalid input, or error. ...


0

Following is a script I use to send query results in an HTML-formatted email. Basically pipe the result of invoke-sqlcmd to ConvertTo-Html and then to Out-String, and then massage it a bit to get the table to display nicely. If you don't add the padding style, everything will run together. Send-MailMessage example below is for unathenticated, so if you ...


1

The other half of the version requirements is that your distributor be the highest SQL Server version in your Replication topography. In your case, you are replicating from SQL Server 2012 to SQL Server 2016, which works only if your distributor is also SQL Server 2016. If you are running your distribution database on your 2012 publisher, configuration ...


Top 50 recent answers are included