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.

The requested list could not be retrieved

The error message 'The requested list could not be retrieved ...' appears
when creating reports with Report Builder is due to missing SSRS Roles at the
SSRS Home (root) folder.

It is necessary to add the Active Directory security groups with the
'Browser' Role to the Home folder in order for users to use the Report Model
properly to retrieve lists when creating report filters or
sub-groups.

Drop All Tables

-- caution: rollout only

EXEC sp_MSforeachtable @command1 = 'DROP TABLE ?'

SUSER_SNAME (Transact-SQL)

Returns the login name associated with a security identification number (SID).

SUSER_SNAME ( [ server_user_sid ] )

Arguments ------- server_user_sid

Is the login security identification number. server_user_sid, which is optional, is varbinary(85). server_user_sid can be the security identification number of any SQL Server login or Microsoft Windows user or group. If server_user_sid is not specified, information about the current user is returned. If contains the word NULL, will return NULL.

Winword Replace Wildcard Characters

Metacharacter	Description	Example
?	Any single character	s?t finds "sat" and "set".
*	Any string of characters	s*d finds "sad" and "started".
@	One or more occurrences of the previous character or expression.	lo@t finds "lot" and "loot".
<	The beginning of a word	<(inter) finds "interesting" and "intercept", but not "splintered".
>	The end of a word	(in)> finds "in" and "within", but not "interesting".
[a]	One of the specified characters	w[io]n finds "win" and "won".
[A-Za-z]	Any single character in this range	[r-t]ight finds "right" and "sight". Ranges must be in ascending order.
[!A-Z]	Any single character except the characters in	t[!a-m]ck finds "tock" and "tuck", but not the range inside the brackets "tack" or "tick".
{1}	Exactly n occurrences of the previous character or expression.	fe{2}d finds "feed" but not "fed".
{2,}	At least n occurrences of the previous character or expression.	fe{1,}d finds "fed" and "feed"
{1,3}	From n to m occurrences of the previous character or expression	10{1,3} finds "10", "100", and "1000".
^13	Carriage Return
()	Pattern group ([A-Z])
\1	First Pattern group	\1 - finds first pattern group and appends a hyphen

A Good Mom's Life