Try this one – Query: SELECT database_name = DB_NAME(database_id) , log_size_mb = CAST(SUM(CASE WHEN type_desc=”LOG” THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , row_size_mb = CAST(SUM(CASE WHEN type_desc=”ROWS” THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = … Read more
Another way, similar to @Astander’s (but using OUTER APPLY instead of PIVOT / UNPIVOT): SELECT a = MIN(CASE WHEN y.rn = 1 THEN y.val END), b = MIN(CASE WHEN y.rn = 2 THEN y.val END), c = MIN(CASE WHEN y.rn = 3 THEN y.val END), d = MIN(CASE WHEN y.rn = 4 THEN y.val END), … Read more
Have a look at this article on Passing Multivalued parameters. Basically the above link contains a workaround to meet your requirement for an optional multivalued parameter. The steps to achieve this are as follows: Replace “ALL” with ” ” (BLANK) in the parameter’s dataset query. Assign -1 as default parameter value for your multivalued parameter.
EDIT: Casting to a float/int no longer works in recent versions of SQL Server. Use the following instead: select datediff(day, ‘1899-12-30T00:00:00′, my_date_field) from mytable Note the string date should be in an unambiguous date format so that it isn’t affected by your server’s regional settings. In older versions of SQL Server, you can convert from … Read more
You can use the STUFF() method to insert characters into your string to format it in to a value SQL Server will be able to understand: DECLARE @datestring NVARCHAR(20) = ‘20120225143620’ — desired format: ‘20120225 14:36:20′ SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,’:’),11,0,’:’),9,0,’ ‘) SELECT CONVERT(DATETIME, @datestring) AS FormattedDate Output: FormattedDate ======================= 2012-02-25 14:36:20.000 This approach will work … Read more
You need to reference the CTE in the delete statement… WITH a as ( SELECT Firstname,ROW_NUMBER() OVER(PARTITION by Firstname, empID ORDER BY Firstname) AS duplicateRecCount FROM dbo.tblEmployee ) –Now Delete Duplicate Records DELETE FROM a WHERE duplicateRecCount > 1
You can use a complicated order by clause. That requires one case for each sort direction and each data type. With this example dataset: create table t1 (id int, name varchar(50), created date); insert t1 values (1, ‘Chihiro Ogino’, ‘2012-01-01’), (2, ‘Spirit of the Kohaku River’, ‘2012-01-03’), (3, ‘Yubaba’, ‘2012-01-02’); You could use an order … Read more
ok, it works now! guess it was a compound problem … the steps I took to resolve it are as such: Changed the following property in the connection string (note the subtle difference): AttachDbFilename=|DataDirectory|CustomerDb.mdf; Deleted the contents of the following directory: c:\Users\<user name>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. I thought I had looked for this before, but … Read more
I found the solution in the following link http://www.cryer.co.uk/brian/sqlserver/replication_requires_actual_server_name.htm thankful to Brian Cryer for his useful site Quoting from the link to avoid link rot: Cause: This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function @@SERVERNAME still returned … Read more
Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS): sp_who sp_lock Also, in SSMS, you can view locks and processes in different ways: Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu … Read more