Tag Info

Hot answers tagged

8

There is a simple solution to this: Drop all of the _dta_... statistics and stop blindly applying DTA recommendations. More information The particular problem was that there were multiple sets of statistics for the column in question. The extra dta statistics were created by sampling the data (the default behaviour for statistics not associated with an ...


6

My honest opinion is that - Don't reinvent the wheel as there is cost to reinvent it. Highly suggest you to use Ola's index and stats maintenance solution. It is very flexible and is highly awarded and tested. Also it does not use any of ms_foreach stuff that you have in your script.


5

This works: DECLARE @mycol VARCHAR(30) = 'Tue Apr 1 12:20:40 2014' SELECT CAST(SUBSTRING(@mycol, PATINDEX('% %', @mycol), 100) AS DATETIME) The day name at the beginning is what prevents it from being able to convert directly. Instead of declaring the variable, you can just substitute the column containing your dates for @mycol in the script above.


5

First let me say that it's generally recommended that you not use sp_msforeachdb. It has some well documented flaws and will occasionally miss databases. @AaronBertrand has a version he wrote that is much better. Second you appear to be cutting off your string early. The way you have it written it's going to execute like this. This part will execute ...


4

Native tooling approach Grab a newer copy of Management Studio (2012 SP2 and 2014 are both free, fully functional, and can co-exist with your 2008 R2 tools). Then you can do this. Put your cursor right before the first leading ABC. Hold Shift+Alt, then hit the down arrow three times. Type '. It's subtle, but you should see a faint blue vertical line ...


3

Using the newly added LAG() and LEAD() functions makes it look easy: WITH a AS ( SELECT PersonID, FloorNum, EnterTime, PrevFloorNum = LAG(FloorNum) OVER (PARTITION BY PersonID ORDER BY EnterTime) FROM RoomAssignments ) SELECT PersonID, FloorNum, FromTime = EnterTime, ToTime = ...


3

USE msdb; GO CREATE USER shims FROM LOGIN shims; GO ALTER ROLE SqlAgentUserRole ADD MEMBER shims; GO Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


3

This is neither technically nor even conceptually possible, due to the nature of what timezones are. It should be noted that timezones are not simply the offset from UTC / GMT (i.e. the "+" or "-" value). The "offset" is a property of the timezone, but it does not, and cannot, determine the timezone since: several timezones can share the same offset ...


3

I have no idea about efficiency* but a view like this: CREATE VIEW Orders_with_a_product_View AS SELECT p.ProductID, o.* FROM dbo.Product AS p JOIN dbo.[Order] AS o ON EXISTS ( SELECT 1 FROM dbo.OrderDetail AS od WHERE od.OrderID = o.OrderID AND od.ProductId = p.ProductId ) ; ...


3

You can set the database to SINGLE_USER so that no other session can come in while you are performing your changes. Also, sp_addrolemember has been deprecated; you should be using ALTER ROLE instead. Finally, please get in the habit of using statement terminators. USE master; GO ALTER DATABASE SQL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE SQL ...


3

How about something like below : change your output in SSMS to text and once you are happy run the generated sql statements in another query window : use master go select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go' from ...


3

Its not too difficult to build a query that does it. First create a query that joins on a table using LIKE with wildcards. Then exclude everything from this query. To see what I mean, see this simple example: http://sqlfiddle.com/#!6/619fb/2 Or alternately I have reproduced an example here: --Create tables for comparing data CREATE TABLE Invoice ( ...


2

When you have a unique constraint that you want to apply to only a subset of rows, you can enforce this using a unique, filtered index. The index that seemed to work for you in this case is: CREATE UNIQUE INDEX [UNQ_SampleTable_Code] ON dbo.[SampleTable]([Code]) WHERE ([Deleted] = 0); This ensures that only one distinct value of Code can exist for ...


2

Your plans show that the only option for looking up a scalar id in the table is a scan. This clearly indicates a missing index. Your code is incorrect under concurrency. Query plans have to work for any parameter value. To give an analogy, consider this example in a non-SQL language: int x; void f(int i) { if (i != 1) x++; } You are asking the ...


2

After setting the db to singleuser you have to switch back to master. I added a use master statement but I don't want to test this.... EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') BEGIN ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE **use master;** DROP DATABASE ''?'' END


2

SET SINGLE_USER means only one user can connect to the database at a time. If someone or some service (SSIS, SSRS, SQL Agent, etc) connects before you than you will have to wait for them to disconnect


2

Would you like to try this? DECLARE @cmd1 nvarchar(2000) SET @cmd1 = 'IF ''?'' NOT IN(''Name of your database'')' + 'BEGIN ' + 'drop DATABASE [?] ' + 'END' EXEC sp_MSForEachdb @command1 = @cmd1 GO it worked here. PAY ATTENTION TO NOT DROP MASTER AND ETC. Put then on that list.


1

If you want to change the current\default database to a different one try: alter login <loginname> with default_database = <dbname>; Now, create a user for above login created use <dbname>; create user <username> from login <loginname>; And now you can assign roles to the above create user for the login as below: use ...


1

try something like this.. DECLARE @MyTable AS TABLE ( [Month] INT, Salary INT ); INSERT INTO @MyTable VALUES (1,2000), (1,3100); SELECT [Month], STUFF(( SELECT ',' + CONVERT(NVARCHAR(30),Salary) from @MyTable WHERE Month = out.Month FOR XML Path('')) ,1,1,'') csv FROM @MyTable out GROUP BY Month;


1

Full text search is a richer way of indexing complex strings. I know your case does not contain prose but FTS may still work for you. As an alternative to wildcard search you could split your concatenated values into their separeate parts. There are many ways to achieve this; reading this link will lead you to one that suits. Then you can treat your data ...


1

Yes that is correct. It will backup your database to c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Backup\TestBackup.bak. INIT will overwrite the file everytime it backups up. Are you sure you just want one FULL backup ? What about transaction log backups? What if that backup gets corrupted (since you have only one backup and that is ...


1

It's converting the integer to a datetime- the column is a datetime. You're seeing the datetime conversion of the results of the DATEDIFF function. Implicit conversion from integer to datetime is in days beginning with 1/1/1900 as 0. SELECT CAST(1 AS DATETIME) Returns 1900-01-02 00:00:00.000 If you want to store the integer result in the column, ...


1

My question is ,is there a way to read logrecord output of fn_dblog(null,null) from sql Reading transaction log using fn_dblog is not supported by Microsoft. So you should avoid running this command on production server. You can run this on UAT/Test environment. What do you exactly want to read from output of fn_dblog, can you be precise. There are ...


1

I think what you'll need to do is create another temp table that has the same columns but with an identity column so you can use it for joining purposes. I also noticed that your date format had day.month.year which threw me off initially.... This will get you pretty close: create table newtable ( cid int, fromdate datetime, todate datetime) go insert ...


1

You could create a table-valued function like this: CREATE FUNCTION dbo.OrdersWithProduct (@ProductId int) RETURNS TABLE AS RETURN ( SELECT o.* FROM dbo.[Order] AS o WHERE EXISTS ( SELECT * FROM dbo.OrderDetail AS od WHERE od.OrderId = o.OrderId AND od.ProductId = @ProductId ) ); And ...



Only top voted, non community-wiki answers of a minimum length are eligible