Foreign Key Dependencies

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints
EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

-- Drop Foreign Keys
DECLARE @tname nvarchar(50), @cname nvarchar(50), @cmd nvarchar(max)

DECLARE​ constraint_cursor CURSOR FOR

SELECT

OBJECT_NAME(parent_boject_id) as [tablename]

,OBJECT_NAME(object_id) as [constraint]FROM sys.foreign_keys

OPEN constraint_cursor

FETCH NEXT FROM constraint_cursor INTO @tname, @cname

IF @@FETCH_STATUS <> 0

PRINT 'NOTE: sys.foreign_keys <>'

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @tname = '[dbo].[' + @tname + ']'
SELECT @cname = '[' + @cname + ']'

SELECT @cmd = 'ALTER TABLE ' + @tname + ' DROP CONSTRAINT ' + @cname

EXEC (@cmd)

PRINT 'SUCCESS: ' + @cmd

FETCH NEXT FROM constraint_cursor INTO @tname, @cname

END

CLOSE constraint_cursor

DEALLOCATE contraint_cursor

SSRS Reports not rendering fonts correctly

​By default text in RDL reports have a CSS style atribute of unicode-bidi:Embed. The problem occurs when a particular font is chosen to render without a license for embedding. Change the attribute value to 'Normal' to have the desired font print out on the client.

SSRS Report printng unwanted gray borders in table

​ I had to fix a report the other day that printed gray table borders out on hardcopy, but, the screen preview seemed fine.

After editing your report in Visual Studio the report preview seems fine on the screen, as designed. Publishing the report to SSRS and previewing the report from the server the report still seems ok on the screen. However, when it is printed gray lines appear on some of the table borders.

Open up the current version of the rdl file for the report and edit the xml. Remove all xml child elements within TextBox or TableCell elements that have a element other than and make sure that the element value is set to Black. Gray lines should disappear.

EXEC External Files

-- execute files

DECLARE @cmd varchar(1000), @dir varchar(1000), @ReturnCode int

SET @dir = N'Path'

SET @cmd = N'dir /b ' + @dir + N'\*.sql'

CREATE TABLE #DirResults (Diroutput varchar(500))

INSERT #DirResults

EXEC master..xp_cmdshell @command_string = @cmd

SELECT N'FOUND: ' + [Diroutput] FROM #DirResults

DECLARE constraint_cursor CURSOR FOR

SELECT [Diroutput]

FROM #DirResults

OPEN constraint_cursor

FETCH NEXT FROM constraint_cursor INTO @cmd

IF @@FETCH_STATUS <> 0

PRINT 'NOTE: Nothing to fetch'

WHILE @@FETCH_STATUS = 0

BEGIN

SET @dir = N'D:\SQLSer~2\Projects\Project1\Scripts\Table'

SET @cmd = 'osql -E -SServer -dDatabase -i ' + @dir + '\' + @cmd

EXEC @ReturnCode = master..xp_cmdshell @command_string = @cmd

PRINT 'ReturnCode: ' + CAST(@ReturnCode AS varchar) + ', ' + @cmd

FETCH NEXT FROM constraint_cursor INTO @cmd

END

CLOSE constraint_cursor

DEALLOCATE constraint_cursor

DROP TABLE #DirResults​

sp_spaceused (Transact-SQL)

​Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

database_name
nvarchar(128)
Name of the current database.

database_size
varchar(18)
Size of the current database in megabytes. database_size includes both data and log files.

unallocated space
varchar(18)
Space in the database that has not been reserved for database objects.

reserved
varchar(18)
Total amount of space allocated by objects in the database.

data
varchar(18)
Total amount of space used by data.

index_size
varchar(18)
Total amount of space used by indexes.

unused
varchar(18)
Total amount of space reserved for objects in the database, but not yet used.

Report Builder icon not displayed for users.

After editing an items' properties for group permissions the Report builder Icon still is not displayed for AD security groups allowed to create reports. After double checking the groups permissions it shows the Report Builder role as being assigned.

It is necessary to edit site settings for the Report site and add the AD security group as a 'System User". After a refresh of the report folder page the Report builder icon should appear on the toolbar.