SQL Server hangs on drop foreign key

This actually happened on Azure during part of a SSIS SQL Server integration package, removal of key before delete and upload of data

Following post was very useful

http://weblogs.asp.net/jeffwids/lock-request-time-out-period-exceeded

To find sessions then

SELECT r.session_id, r.status, r.start_time, r.command, s.text,
r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

then Kill session id, but you cannot kill your own session, so open another session to do the kill

End.

Advertisements

T-SQL FileExist

File Exits


/*
SELECT dbo.myFileExist ('c:\Temp\Test.txt') AS FileExists
SELECT dbo.myFileExist ('c:\Temp\Test1.txt') AS FileExists
*/

ALTER FUNCTION dbo.myFileExist (@FileName varchar(100))
RETURNS int
AS
BEGIN
DECLARE @FileExists int
EXECUTE master.dbo.xp_fileexist @FileName, @FileExists OUT
RETURN @FileExists
END

SQL determine table row size

Personal note: See my SQL Monitor project


SELECT SCHEMA_NAME(so.schema_id) AS schemaname, OBJECT_NAME (sc.[id]) tablename
, COUNT (1) nr_columns
, SUM (sc.length) maxrowlength
FROM syscolumns sc
join sys.all_objects so on sc.[id] = so.[object_id]
--join sys.schemas s on so.schema_id = s.schema_id
WHERE so.is_ms_shipped = 0
GROUP BY so.schema_id, OBJECT_NAME (sc.[id])
ORDER BY SUM (sc.length) desc


SELECT SCHEMA_NAME(so.schema_id) AS schemaname, OBJECT_NAME (sc.[id]) tablename
, COUNT (1) nr_columns
, SUM (sc.length) maxrowlength
FROM syscolumns sc
join sys.all_objects so on sc.[id] = so.[object_id]
--join sys.schemas s on so.schema_id = s.schema_id
WHERE so.is_ms_shipped = 0
AND OBJECT_NAME(sc.id) = 'Orders'
GROUP BY so.schema_id, OBJECT_NAME (sc.[id])
ORDER BY SUM (sc.length) desc


dbcc showcontig ('Production.Orders') with tableresults

Table space used


EXEC sp_spaceused 'Product.Operations'

T-SQL unable to CAST text to a number – Byte order mark – Dodgy character at start

Scenario

Imported some data from Excel in form “156 x 54 x 16mm”. Parsed the text to get the 156. However this would not convert to a numeric.

Error converting data type nvarchar to numeric

After some exploration I discovered that the cursor required to steps through before the number. So a character was there that SQL Server could not display. To find this I used:

SELECT Code, UNICODE(LEFT(DimensionsTextImport, 1)) AS UnicodeTest FROM Product.<tablename>

And discovered text which would not convert had Unicode 65279

Solution

Create a new column and fill this with the data from the first column after a CASE statement.

UPDATE Product.Dimensions
SET DimensionsText = CASE
WHEN UNICODE(LEFT(DimensionsTextImport, 1)) = 65279 THEN SUBSTRING(DimensionsTextImport, 2, 50)
ELSE DimensionsTextImport END

With reference to:

http://stackoverflow.com/questions/6441208/unable-to-replace-char63-by-sql-query

http://www.fileformat.info/info/unicode/char/feff/index.htm

http://en.wikipedia.org/wiki/Byte_Order_Mark

 

 

Continue Statement (Visual Basic) (T-SQL) “Control-of-Flow Language”

My SQL 70-461 study reminded me about the T-SQL CONTINUE statement to cause execution to jump back to the beginning of the loop. Re. Querying Microsoft SQL Server 2012 – MS Press – p 479

http://msdn.microsoft.com/en-us/library/ms178642.aspx – SQL Server

I keep needing this in VB.Net and for some reason it is missing from my VB.Net vocabulary. Not any longer…

http://msdn.microsoft.com/en-us/library/801hyx6f(v=vs.100).aspx  – VB.Net

Continue transfers control immediately to the next iteration of a loop.

SQL: Windowed functions can only appear in the SELECT or ORDER BY clauses

Use a CTE. Problem could occur in

WITH myCTE AS
(
SELECT WO, QuoteNo, EntryNo = ROW_NUMBER() OVER (PARTITION BY QuoteNo ORDER BY WO)
FROM dbo.Orders2006
WHERE QuoteNo IS NOT NULL
)

UPDATE dbo.Orders2006
SET EntryNo = myCTE.EntryNo
FROM dbo.Orders2006
INNER JOIN myCTE ON myCTE.WO = dbo.Orders2006.WO

 

With thanks to:

http://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause

SQL Synonym

During study for Exam 70-461 looked at Synonym

Could be useful. For instance could use synonym to point at a data table in linked “Live” database. Then restore your project to “Test” version, change the synonym to point at the linked “Test” database and you have changed what all objects now point at.

http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/

 

Error: The multi-part identifier could not be bound. when using a function and alias

You cannot pass parameters to functions across a JOIN.

http://stackoverflow.com/questions/4123413/the-multi-part-identifier-columnname-could-not-be-bound

Use APPLY:

SELECT ROW_NUMBER() OVER(ORDER BY x.Year DESC, x.Month) AS ID,
x.*,
fr.*
FROM (
SELECT Client,
Month(TimeStamp) As Month,
Year(TimeStamp) As Year,
Day(TimeStamp) As Day,
(dateadd(yy,(Year(TimeStamp)-1900),0)
+ dateadd(mm,Month(TimeStamp)-1,0)
+ Day(TimeStamp)-1) AS XTimeStamp,
Sum(KwTop) As KwTop,
Sum(KwHeap) as KwHeap,
Sum(KwLow) As KwLow
FROM Ori.vEnergyUnion
GROUP BY
Year(TimeStamp), Month(TimeStamp),Day(TimeStamp), Client
) x
OUTER APPLY
(
SELECT *
FROM Finance.fGetRates(x.XTimeStamp) Fr
WHERE fr.ValidFrom = x.XTimeStamp
) fr

Get SQL table space – and store for KPI over time

See http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

For a single table

EXEC sp_spaceused ‘<schema>.<tablename>’

For database size

–See sql file for dbo.TableSpaceUsed
SET NOCOUNT ON DBCC UPDATEUSAGE(0) — DB size.
EXEC sp_spaceused– Table row counts and sizes.

For all tables

See http://codebetter.com/raymondlewallen/2005/03/25/using-sp_msforeachtable/
http://wp.me/p17IS4-ge

CREATE TABLE #t
(
[name] NVARCHAR(128)
,    [rows] CHAR(11)
,    reserved VARCHAR(18)
,     data VARCHAR(18)
,     index_size VARCHAR(18)
,    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

–Optional, but I am now using a table I have created for storing this table size over time
DECLARE @SpoolID int SELECT @SpoolID =ISNULL((SELECTMAX(SpoolID)FROM dbo.TableSpaceUsed), 0)+ 1
–SELECT @SpoolID

INSERT INTO dbo.TableSpaceUsed (SpoolID, [name], [rows], reserved, reservedText, data, index_size, unused)
SELECT @SpoolID, name,rows,CAST(REPLACE(reserved,‘KB’,)ASint)AS reserved, reserved AS reservedText, data, index_size, unused
FROM  #t

SELECT * FROM #t ORDER BY CAST(REPLACE(reserved,‘KB’,)AS int) DESC— # of rows.

SELECT SUM(CAST([rows] ASint))AS [rows] FROM #t

DROP TABLE #t

Using sp_MSforeachtable

http://codebetter.com/raymondlewallen/2005/03/25/using-sp_msforeachtable/

See usage in http://wp.me/p17IS4-gc