Recently I uploaded a file in a document library on a SharePoint site. I had created a report and wanted to publish it. After reviewing client requested some changes in it, I tried to open that file from SharePoint directly in Microsoft Excel using “Open in Excel”.
Whatever I tried I always received an error “The file is corrupt and cannot be opened”. Did a little search on the web and found the solution.
- Open Microsoft Excel
- Go to File > Options
- Click on the Trust Center at the bottom of left panel
- Click on “Trust Center Settings” button on displayed pane
- Choose “Protected View” from left panel
- Uncheck all options on displayed pane, and click “OK” till back
Now when you open that file again, you will be able to do that, until any further issue, will keep that handy.
Recently we had a little tricky problem to solve. We were displaying a report in which we had to bucket numbers in a range, such that only consecutive numbers should be in that range, if any break is there, then a new range should start. Our first solution did not worked as required. Most difficult part was identifying numbers in a sequence, and placing them in a bucket. We could not create any simple T-SQL queries which could easily sort these things out. Then we thought of first capturing the bucket of each number so that we can easily work it out, and that was not possible without cursors. Lets have a look how we did that.
Recently I saw a puzzle on SQL Server Central, I was intrigued to solve it myself, so here is what I was able to accomplish, and in less than 10 minutes.
DECLARE @i int
SET @i = 0
CREATE TABLE #X (val INT)
WHILE @i < 100
SELECT @i = @i + 1
INSERT INTO #X VALUES (@i)
val % 3,
val % 5,
WHEN val % 3 = 0 AND val % 5 = 0 THEN 'BIZZBUZZ'
WHEN val % 3 = 0 THEN 'BIZZ'
WHEN val % 5 = 0 THEN 'BUZZ'
DROP TABLE #X
Today I had to add a new date column to one of my table, and populate it with some random entries. I found a script from SQL Team Blog about generating random numbers. I modified it a little and used it to populate these dates based on another date I had in my table.
Here is modified code:
-- Create the variables for the random number generation
DECLARE @Upper int;
DECLARE @Lower int-- This will create a random number between 1 and 365
SET @Lower = 1 -- The lowest random number
SET @Upper = 365 -- The highest random number
-- we'll use this temp table to assign a random number to each ID value
CREATE TABLE #temp (ID int NOT NULL, RandNum float NULL)
INSERT #temp (ID) SELECT ID FROM FOO
-- now, assign a new random value to each key value in #temp
DECLARE @id int
DECLARE Randomizer CURSOR
FOR SELECT RandNum FROM #temp
FETCH NEXT FROM Randomizer INTO @id
WHILE @@Fetch_Status != -1
UPDATE #temp SET RandNum = rand()
WHERE CURRENT OF Randomizer
FETCH NEXT FROM Randomizer
SET F.NewDate = dateadd(dd, round((T.RandNum * 100), 0), F.AnyDate)
FOO F INNER JOIN #temp T ON F.ID = T.ID
DROP TABLE #temp
Now say your table does not have an identify column, you don’t want to use a temporary table or alter your existing table, but you still would like to have a record number associated with each record. In this case you could use a self join to return a record number for each row. Here is an example that calculates a RecNum column, and displays the LastName for each record in the Northwind.dbo.Employees table. This example uses count(*) to count the number of records that are greater than or equal LastName in this self join.
SELECT COUNT(*) RecNum,
FROM Northwind.dbo.Employees a
JOIN Northwind.dbo.Employees b
ON a.LastName >= b.LastName
The results from this query looks like this:
Here how you can convert string date to a datetime value:
DECLARE @DateString char(14)
SET @DateString = '20060703074815'
SELECT CAST(SUBSTRING(@DateString, 1, 4) + '-' +
SUBSTRING(@DateString, 5, 2) + '-' +
SUBSTRING(@DateString, 7, 2) + 'T' +
SUBSTRING(@DateString, 9, 2) + ':' +
SUBSTRING(@DateString, 11, 2) + ':' +
SUBSTRING(@DateString, 13, 2) AS datetime)
If you want to find row count for each table in your database without naming any table then use following script:
exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''?'' as table_name from ?'
When you need to find all procedures and function referencing a particular table then use following:
SELECT o.name, t.TABLE_NAME, c.text
FROM syscomments c JOIN sysobjects o ON c.id = o.id JOIN INFORMATION_SCHEMA.Tables t ON c.text LIKE '%'+t.TABLE_NAME+'%'