Tag Info

New answers tagged

2

How can I be seeing shared locks? Is it because of foreign keys? Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of validating foreign key constraints. This is required for correctness, and cannot be disabled. The behaviour applies only to data-modification statements. ...


0

You can use stored procedures and functions for instance. Tracking who-saw-what can be done through specially developed stored procedures and functions. In order to use this auditing method, access to the database must be limited through use of stored procedures (allowing EXEC statements only, while prohibiting all queries and DML operations). The result ...


0

You can use custom policies or predefined ones (so called best practices) and adjust them according to your developer SQL Server instance. Then, as explained in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances online article, evaluate them against (even multiple) production instances


0

This issue never resolved. We dealt with this until migrating to SQL 2012 in October 2013.


0

If I do a Paste Special, I get two options, Text and Unicode, and selecting Text seems to work properly, where as Unicode does not. as per comment by @JonSeigel


0

If you have a date table the following could work. If the date table does not already have a column for the date format stored in table.bar then create the column or cast. select t.foo, t.bar from table t join date_table dt on t.bar like '%' + dt.DateID +'%' where dt.DateID > 20140401 Since you would be using a date table it would probably be better to ...


0

In which format are the date values stored? If you can determine the used format you can CONVERT with the specified format by resticting the rows using LEN and LIKE predicate. Example: If you have the date values stored like '10/23/2016' you can use U.S. standard (101) SELECT [foo], CONVERT(DATETIME, [bar], 101) FROM [table] WHERE LEN([bar]) = 10 AND ...


0

To stress a system you can either increase load or decrease resources. There are many commercial load-creating packages out there, and probably some freeware ones, too. It sounds like you've isolated it to just one SP, though, so a custom script to fire off 500 examples of it in parallel should do the trick. Running the script on the DB server will induce ...


0

Just to explain why do you need a full database backup and then the full chain of transaction log (LDF) files up to the point of the incident. Unlike INSERT and DELETE operations, which are fully logged in the LDF files, UPDATE operations are logged minimally - only the changes that are made are logged, but the old and new values are not. When logging UPDATE ...


0

Service Broker can provide this functionality. It uses a store-and-forward messaging model. Your local edition may not have this available, however.


0

If you really want to do it programmatically there's the MS Sync Framework.


2

The key concept you need to study is called "Replication".


0

Are you using a named instance or a default one? Did you enabled a remote connection on that instance? Right click on the server and in the Server Properties dialog under the Connections tab check the Allow remote connections to this server option In the TCP/IP Properties -> IP Addresses -> IPAII: For a named instance you should set set the TCP Dynamic ...


2

This is a known issue when using SQL Server 2008 to search for a number with more than nine digits. When using certain word-breakers, the number is stored in its scientific form e.g. nn5d68952e+009, leading to false-positive matches. One of the word-breakers affected is the neutral language (lcid 0): -- Result includes nn5d68952e+009 SELECT display_term ...


1

To reinforce what Jon says - a single DB to which all clients connect all the time, with the schema designed accordingly, will be a much better solution. You only need replication in a few circumstances. One is if a client machine must be able to work while disconnected. Another will be if there are huge latency issues between your various sites. ...


0

Have you compared the query plans for the working and non-working versions? With such a simple query it seems unlikely there'd be much choice for the optimiser, but its worth eliminating from enquiries.


2

As the error message suggests, OPENQUERY looks for a linked server. If you don't have that set up, and don't have the rights to get that set up, then perhaps using an OPENROWSET query might be the way to go. This will allow you to connect to a datasource on a more ad-hoc basis, by writing a query which contains a full connection string. In your case, you ...


0

If it was working recently and it's not working now, you've either got a whole bucket load more data or a worse query plan. I'd suggest you'd notice the former. The latter may be due to the statistics getting stale.


0

I was getting this error for some users. We tried many things. Finally, I just removed and re-added the user, and then they were able to see the databases.


0

I was having this same issue. Below is how I found the orphaned Log Reader Agents, Once they were identified I removed the rows from MSlogreader_agents and it corrected the issue of errors in the replication monitor SELECT TOP 500 [id] ,[name] ,[publisher_id] ,[publisher_db] ,[publication] ,[local_job] ,[job_id] ,[profile_id] ...


0

With a single stored procedure (SP) you will run into parameter sniffing problems. RECOMPILE becomes mandatory. Every invocation now has a compilation overhead. This can mount up since the SP is, almost by definition, very large. Lots of small SPs are less likely to need OPTION (RECOMILE) individually and the cost of compiling less when it does happen. ...


2

Your question may be a candidate for closing as "Primarily opinion-based" but yes, I'd follow your proposed pattern, to a point. What I tend toward is identifying the most frequent parameter combinations and coding discrete procedures for them. It becomes unfeasible with a large number of combinations of parameters to code and maintain for each, although ...


1

This will work for all three-part names, and all four-part names where the server is the local instance. SELECT name, base_object_name, DB_ID(PARSENAME(base_object_name,3)), DB_NAME(DB_ID(PARSENAME(base_object_name,3))) FROM sys.synonyms; If you get NULL it's either because the synonym is only 2-part (e.g. it references an object in the current ...


2

Yes, what you're talking about is a Filtered Index. It works just like you think, by creating an index off of a subset of data. Below is an excerpt from the above reference: A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a ...


6

Why do you think it should use the filtered index? You've used SELECT * in your query and your predicate involves more than just the filter condition. SQL Server is going to have to look up those columns in another index that covers it anyway (your SELECT * plan should include a key lookup to retrieve those other columns). SQL Server will decide based on ...


0

If fn_dblog is hanging you may try some third party tools from ApexSQL, RedGate or Idera. Or you can try to backup the transaction log file and use fn_dump_dblog just to exclude problems with an online transaction log. You can find more info on this DBA post


0

First of all use differential backups more often than you are using full backups. Use third-party backups tools. I would recommend you to use EMS SQL Backup which has very effective compression algorithm.


0

Use something like the comp command to find out the first place they're different. Then open the files up in a hex editor, like HxD for Windows, and look at where they're different. In particular, look for: CRLF (DOS, 0x0D0A) vs LF (*nix, 0x0A) line termination Unicode BOM (Byte Order Mark, UTF-8 being 0xEFBBBF) at the start of one but not the other ...


1

In your first screenshot, the BorderStyle value is None. In order for the BorderWidth setting to have an effect, the BorderStyle setting must be changed also (e.g. to Solid). The borders in your second screenshot are cell/row borders. The weight of these borders are controlled via the BorderWidth setting of the cell or row. The Tablix border is in addition ...


0

There can be various reasons why file hashes different: Line Endings differ; Spacing might differ (tabs vs spaces); Different layout for same data Encoding (of the file that data is saved in) Quotes (single vs double) Case sensitivity What I would do, is compare both files with diff tool. git diff can come in help here as it has ability to diff word by ...


2

Transaction log files are written to and used in a sequential manner. This can be observed by using DBCC LOGINFO to examine the use of Virtual Log Files(VLFs) within your log file. For example, if I have a database and a table, where I insert 1000 rows into it, DBCC LOGINFO will provide the following: RecoveryUnitId FileId FileSize ...


2

Transaction log writes are sequential. Only one of the log files will ever be written to at any one time, so having multiple files - in and of itself - can't possibly change your I/O patterns for that database. Unless you are getting lucky. For example, you've added a second log file to an SSD or otherwise faster or less busy disk, or split the log files ...


2

Your initial thoughts were correct: There is no benefit to having multiple transaction log files. SQL Server utilizes the transaction logs sequentially, not simultaneously. why would increasing the number of log files end up have a very noticeable improvement on the Write speed to the Disk? It wouldn't. There simply must be another factor involved ...


3

Your SQL Server is installed as named instance, so first of all try connecting to your server using the following server name: IP Address\SQLEXPRESS. When you install SQL Server as named instance it uses dynamic TCP/IP ports by default, so it is not possible to connect to it whitout specifying instance name (just IP address). If you need to connect to your ...


7

This doesn't really seem all that crazy, but note that some of the UI dialogs might not have completely up-to-date information (this is why we have things like DBCC UPDATEUSAGE), and rounding can also be involved in some of those calculations. Finally, the dialogs show you total space for the whole database, but unallocated space is only calculated for the ...


1

You need to use the condition that works. The reason is easy to demonstrate. Run this: DECLARE @x VARBINARY(8000) = ENCRYPTBYPASSPHRASE('12','YYY'); SELECT @x, CONVERT(VARCHAR(32), DECRYPTBYPASSPHRASE('12', @x)); GO 5 Do you see the same result every time? No. This is because the encryption adds some magic to prevent predictable results (that is grossly ...


1

This really isn't an SQL Server issue - it seems more of a firewall issue. If you are in a situation where your SQL Servers are directly accessible from dudes in china - I'd be sorting out why no one in your outfit is handling networking/firewalls etc It sounds quite possible that you need to be worried about more servers than just the one you've noticed ...


1

You should enable , following for me the issue solved by enabling following and restarting all service. Shared Memory TCP/IP Named Pipes


0

I dealt with this issue by creating following stored procedure to create a type with same schema existing table may have. Create PROCEDURE [dbo].[Sp_DefineTypeOutOfTableSchema] @TableNames NVARCHAR(500) AS BEGIN DECLARE @TableName NVARCHAR(100) DECLARE @strSQL NVARCHAR(max) DECLARE @strSQLCol NVARCHAR(1000) DECLARE @ColName NVARCHAR(100) DECLARE ...


4

I don't think there's a surefire method to find everything. After all, they could have access to things merely by virtue of being in a specific server or database role, or even a Windows AD group (you didn't specify if this is a SQL auth login or a Windows login). There also isn't a surefire way to identify what might break if this is changed - for example, ...


1

You're attempting to use "SQLExpress" as a server name. I'm guessing that's the instance name. Connect to ".\SQLExpress" instead.


0

Some steps to reduce these vulnerabilities can be found here: Disable the login that is being attacked. In most cases, the login would be a generic sa or admin account. This will disable access to the account. However, the server may still be vulnerable to outside attacks. Change name of the account that is being attacked. For instance, change the ...


0

1) Enable TCP/IP connections from Configuration Manager as Max has suggested. or 2) Please try re-installing it by disabling anti-virus and also firewall (if it is on). I guess there may be issues with Ports assigned for SQL Server.


2

As pointed above by Jeremy, permissions can almost always be found on the corresponding reference document within Books Online for SQL Server. On these reference documents, there is typically a section titled Permissions (typically towards the bottom of the document) which contains your desired data. CREATE LOGIN (copied directly from the reference) ...


6

You should fix the SQL agent first. as a temporary workaround, you can use SQLCMD and schedule it using windows scheduler for doing backups. no need to spawn a job from different server and do the backup on another server.


0

You can perhaps play with EXECUTE AT vs. OPENQUERY, but what I see as critical is: If the query optimizer picks a plan where the remote table is queried once, performance is acceptable, even if all 12,000 rows are returned. If this is both true now, and will be true after a few years of growth, and you've already restricted the amount of data returned ...


2

For the record, the SQL Server 2012 hash format is: 0x200<4 byte salt><hash result> where the hash result is essentially: HASHBYTES('SHA2_512', CONVERT(VARBINARY,N'[password]') + CAST(0x[salt] AS BINARY(4))) SQL Server 2005 to 2008 R2 are identical except they use SHA-1 instead of SHA-512. Some reasons NOT to use the previously hashed ...


0

You can use ApexSQL Restore – a SQL Server tool that attaches both native and natively compressed SQL database backups and transaction log backups as live databases, accessible via SQL Server Management Studio, Visual Studio or any other third-party tool. It allows attaching single or multiple full, differential and transaction log backups as well as ...


4

When you issue an ALTER TRIGGER statement, that process will attempt to acquire a SCH-M (schema modification) lock on the table object as well as the trigger object. This is an extremely low-concurrency lock and will cause the expected blocking that you're running into. See this reference for a chart on lock compatibility. For referencing sake, here is ...



Top 50 recent answers are included