Tag Info

New answers tagged

0

This issue happens because the SQL Server lost the logon id credentials or use an account without rights to start services, SQL for starting Automatically your services needs an account with rights to starts services in the computer start up. To solve this issue you need to go Administrative Tools > Services > SQL Server(MSSQLSERVER or the name of your ...


5

Stop using things like varchar without specifying the length. Watch what happens here: CREATE PROCEDURE [dbo].[Reconciliation_broken] @Param1 varchar AS BEGIN SET NOCOUNT ON; PRINT @Param1; END GO EXEC dbo.Reconciliation_broken @Param1 = 'what happened?'; Results: w When you don't specify the length, you get silent truncation - in some ...


2

If the path is being stored in a SQL_VARIANT field, then you might as well CAST / CONVERT to NVARCHAR(4000) on the way out as that is the most that can be stored by a SQL_VARIANT. While the typical upper limit for a path is 260 characters, it is possible to exceed that and go up to 32,767 characters (even if that is unlikely to happen in practice). Since ...


0

The answer to both of your questions is really the same. You are using a script generation tool. It does what it's told to do. Typically the second command is only needed if you are doing a WITH NOCHECK on the first one. However it's included because the settings for the script generation component of SSMS told it to include it. Here is a post I did on ...


0

ANSI_NULL_DEFAULT { ON | OFF } : When you dont specify ANSI_NULL_DEFAULT in CREATE TABLE or ALTER TABLE statements, this database option will determine the default value, NULL or NOT NULL for a column or CLR user-defined type. When SET to ON, the DEFAULT value is NULL. When SET to OFF, the DEFAULT value is NOT NULL. Connection-level settings that are ...


0

Instead of creating a trigger I would suggest you to create a new column in the purchase table which will be a computed column. Before creating it, create a scalar function which will accept productname or productid and returns int. Inside it, write a query to find the total from sales for that particular row (by passing productname or id) and subtract it ...


0

I don't think that a trigger is the right place for your application business logic. A stored procedure to update the corresponding rows in the stock table would be much more sensible in my opinion. If you want to prevent users from modifying the sales and purchase tables directly without updating the stock table, don't grant permissions on those objects ...


0

I have a hunch you changed system date to workaround with evaluation period expired message and then you forgot to change it back. Then you did edition upgarde, this some how changed registries to incorrect value. As per logs the system has been upgraded with NO FAILURE but problem lies with SSMS. SQl Server support team has written article to overcome ...


0

Well you can try to run a trace (if it's possible for you). But you can also try to just write it to your SQL Server Agent Job History instead. I tried to write a little bit of code which can be added as a first step in front of all other steps. It will simply write a line "Executed from [hostname]". I couldn't test it, due to the fact that I have just one ...


5

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable. SQL Server does however do it's best to ...


0

Every schema must have an owner, which is a database principal (user or role) within the database. The significance of the schema owner is that the owning principal has full control permissions. Also, objects within the schema inherit the schema owner by default so the owning principal typically has control permissions on all database objects within the ...


1

You have to remember that the leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row. In your where clause you state VeryRandomText = N'111' Since there is a Non clustered index on VeryRandomText (create index will create non clustered index unless you explicitly tell it to create a clustered) the ...


2

If you are sure that everything is replicated to all subscribers and you really need to shrink it right now, you can try this. Execute the following snippet inside your replication database. It will mark all replications as successful and your replication queue will be empty. Be sure that this is really the case! Otherwise you may loose some elements which ...


1

Here is your answer: MSDB on C: 86% of 32GB io_stall_write_ms : 192217 If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds! First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away Check if the master database ...


0

Check the latency between your PC and the domain controller after doing ipconfig /dnsflush. Are you connecting to server by IP or hostname ? If IP, it could be doing a reverse-DNS, so add a hosts file entry. Try switching Network Protocol between TCP or Named Pipes in SSMS Connect to Server dialog - Options - Connection Properties.


1

You can easily change the schema ownership to dbo, then you should be able to drop the user, and add them back using ALTER AUTHORIZATION: ALTER AUTHORIZATION ON SCHEMA::[schema_they_own] TO dbo; DROP USER [user_name]; ALTER AUTHORIZATION ON DATABASE::[database_name] TO login_name; Now, they will be the dbo user in the database (and will have automatic ...


3

It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion. There are 2 possible base reasons for your slow down. You upgraded your system and rebooted it You load a bunch ...


1

You can, but you don't want to. Growing the transaction log is an expensive operation because not only does it need to allocate the space on disk, but it needs to write zeros to all of the space allocated. Any operation that needs space in the log and doesn't have it will need to wait for that zero filling to complete. Which kills performance. Shrinking ...


0

I'm not sure how you what the output to display this. DECLARE @vtExp TABLE ( ACCCODE CHAR(5) NOT NULL, ACCName VARCHAR(50) NOT NULL, ACCParentCode CHAR(50) NULL, ACCExpense FLOAT NULL ) INSERT INTO @vtExp (ACCCODE,ACCName,ACCParentCode,ACCExpense) VALUES('00000','Account 00000',NULL,0) ,('00001','Account 00001','00000',0) ...


2

Check this article. According to that article the transaction log backup is the only way how to reduce the size of the transaction log in the FULL recovery mode. Shrinking will help only in a situation when you for example have forgotten to take transaction log backups and your transaction log file grows and then you take the backup and you need to shrink ...


1

An owner of a database object has full rights on it automatically and unrevokably. But of course you can give an user all rights to a schema without making him the owner.


2

SSIS code behind is actually a XML value and all components of package will be represented as XML node values within it.This XML can be parsed to identify if it has a reference for our searched table or column. Finding SSIS Packages having References to a Table or Column will help you.


0

Here's a potential syntax wrinkle. Even though the MS documentation, as @RemusRasanu pointed out, says to use -m"sqlcmd", like so: For example, -m"sqlcmd" limits connections to a single connection and that connection must identify itself as the sqlcmd client program. Use this option when you are starting SQL Server in single-user mode and an ...


2

This should give you a start. It assumes that you've already cloned all the relevant objects and that all of your permissions are at the object or schema level (if you have column-level permissions, the script gets a little more complex, but not too bad). DECLARE @oldschema SYSNAME = N'schema1', @newschema SYSNAME = N'schema1_dev'; DECLARE @sql ...


5

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following ALTER TABLE tablename REBUILD That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway ...


0

Window Functions, or the OVER() Clause It appears that what you are looking for is a window function or an OVER() clause. In your original example, you are trying to use two max() conditions, which doesn't work, because when you try to then apply GROUP BY, you can have a condition where the max of the first column ID and the second column startdate aren't ...


1

Well we have quite a mixed servers,using encryption, depending upon the need of business. For very critical we decided to upgrade to enterprise as it not only provides TDE but other benefits as well when it comes to performance or troubleshooting. Yes, TDE is quite effective and very good, but since it comes with a cost we decided for medium and low ...


1

Have you tried to give permissions to the temporary folder that may be in use for the user? (The root of the drive) It would be very common that your dev box would have permissions to C:\stuff and not on another box. A simple way to test would be to use xp_cmdshell (if enabled, included the enable and disable stuff since its pretty dangerous): exec ...


0

I was trying another way, and here what I got: SELECT ClientNo, ClientID, FullName, SUM(InvoiceTotalDue) as InvoiceTotalDue, sum(InvoiceTotalBalance) as InvoiceTotalBalance, sum(ReturnChequeTotalDue) as ReturnChequeTotalDue, sum(ReturnChequeTotalBalance) as ReturnChequeTotalBalance FROM ...


3

This may not be the best way to do this but it will work. SELECT c.ClientNo, c.ClientId, c.FullName, i.InvoiceTotalDue, i.InvoiceTotalBalance, r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance FROM Client_TBL c LEFT JOIN ( SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue, ...


2

You can refer to Paul Randal's blog (the guy who wrote DBCC CHECKDB) : CHECKDB From Every Angle: Complete description of all CHECKDB stages : For SQL Server 2005, CHECKDB uses an internal database snapshot to provide the required transactional consistency. So when CHECKDB starts in SQL Server 2005, the first thing it does is work out whether it can ...


1

The tool you want is Express Profiler Small, lightweight SQL profiler, open source, works great with SQL Express. (I am thrilled to have found it!) http://expressprofiler.codeplex.com/


1

HeavenCore's reply answers the question. However there is another way to get around this without security flaws. Return output to a table, query the table with DBmail.


1

Yes a computed column would achieve this for you. Or you could create an trigger that will fire on insert or updates. Here is link to a question like yours on stackoverflow.


2

If multiple threads are issuing the delete/update/select statements concurrently, then blocking is the most likely explanation. You could use SQL Profiler and filter the trace on TextData Like '%MYTABLE%' to find all the statements accessing the table. http://stackoverflow.com/questions/4076516/how-to-profile-for-one-table-in-sql-server However use ...


0

I would suggest you to verify the service accounts you are using: SQL Server service account requires CONNECT permissions to partners’ endpoints Best option is to use domain accounts for all partners If using Local Service or Local System, must use certificate authentication. Ensure that the start date of the certificate is the current date or a day ...


2

Well, you can check how often your plans are reused. After that, you may want to decide to switch to optimize for ad-hoc workloads. Those flag will only save a plan-stub instead of a full plan and will do a fast compile of the stub if there is a query which uses the same stub. Those option isn't enabled by default (due to the fact that it's a newer ...


7

The clustered index is partitioned on ReadTime so it couldn't use the PK as you describe. It would need to find the Max(Id) for each partition and then find the max of those. It is possible to rewrite the query to get such a plan however. Using an example based on the article here a possible rewrite might be SELECT MAX(ID) AS ID FROM sys.partitions AS P ...


0

Did you try to refresh your statistics? You can also try to use OPTION(RECOMPILE)on your query an recheck the actually used plan. Is it still the same? How fragmented is the clustered index?


0

Turns out that our GoDaddy dedicated server had a Cisco hardware firewall that had one IP permitted for TCP port 1433. My VM was on the network that had that IP, while my other machine was not. After spending several hours trying to get the Cisco ASDM software to launch properly, I was able to add the IP address of my other machine and it's now working. ...


0

There was a problem with the System Volume Information Folder which is a hidden folder at the root of this volume. On other volumes on the same machine, this protected volume was accessible after being made visible in Folder Options I could not however access the System Volume Information folder of the DATA directory that I was attempting to create ...


0

Could you try : Named Pipes instead of TCP in SSMS connection properties SQL authentication (create a SQL login if needed) instead of Windows


0

exec sp_helpdb gives you the size of each database. You can create a table dbsize and do insert into dbsize exec sp_helpdb to insert data into it once a week using a job. After a month, you can see how much each db grew


4

I often generate T-SQL like this: select 'ALTER DATABASE ' + quotename(name) + ' MODIFY FILE (...)' from sys.databases where databaseIsMisconfigured = 1 Select the entire column, past it into a new window and F5.


1

you could script out all settings. make the changes you want in the the GUI but instead of click OK to apply the change, click script at the top of the window and the SQL for your changes will be scripted out. You will now just have to change database name/file names/ sizes/ etc for each database. USE [master] GO ALTER DATABASE [test] MODIFY FILE ( NAME = ...


0

For the record, this will trim the trailing 0x00 and leave the 0x00 in place if they happen in the middle of the data: cast(substring(CONTEXT_INFO(), 1, len(REPLACE(REPLACE(CONTEXT_INFO(), 0x20, 0x21), 0x00, 0x20))) as varbinary(128)) This gives a result as a varbinary, if you want a string, remove the outer cast Most of the other solutions either end up ...


2

You can use a third party tool Idera Diagnostic Manager to identify the growth and cost. There is a report inbuilt which forecast database and table growth depending upon how much historical data it has. You can also set up this with the help of combination of TSQL + Powershell as described Database Space Capacity Planning by chad. It will help you in ...


0

Another option is to upgrade, if possible. Starting SQL Server 2012, SQL Server Audit is partially available to all editions (yes all, even Express). For a quick overview of which audit capabilities are available in which editions, check out this blog on SQL Server Audit Support in Different Editions and Versions.



Top 50 recent answers are included