Finding StoreGeneratedPattern value in EdmProperty in T4 Template

If you need to find out if a primary key in your conceptual model which is using the Identity value from the property StoreGeneratedPattern, you can use the following code: (NOTE: This is used within a T4 template)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
string inputFile = @"..\EntitiesModel.edmx";
EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile);
string annotationNamespace = "http://schemas.microsoft.com/ado/2009/02/edm/annotation";

foreach (EntityType entity in ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name)) {
   foreach (EdmProperty edmProperty in entity.Properties.Where(p => p.TypeUsage.EdmType is PrimitiveType && p.DeclaringType == entity)) {
 
      MetadataProperty storeGeneratedPatternProperty = null;
      edmProperty.MetadataProperties.TryGetValue(annotationNamespace + ":StoreGeneratedPattern", false, out storeGeneratedPatternProperty);

      if (storeGeneratedPatternProperty != null && storeGeneratedPatternProperty.Value.ToString() == "Identity") {
         //We found an Identity property
      }
   }
}

Determing SQL Server Table Size

This is a bit of code I found that determines the size used by each table in your database.

Make sure you run DBCC UPDATEUSAGE first to correct any incorrect stats (pages etc) on your tables.

DBCC UPDATEUSAGE (YOUR DATABASE NAME)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName

WHILE (@@Fetch_Status &gt;= 0)
BEGIN
INSERT  #TempTable
EXEC sp_spaceused @TableName
FETCH NEXT FROM tableCursor INTO @TableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults, ordered by biggest
SELECT *
FROM #TempTable
ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC

DROP TABLE #TempTable

References:

http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx

You receive a “The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect” exception when using NVarchar parameters with Sqlclient

The problem

I got the following random error:

You receive a “The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect” exception when using NVarchar parameters with Sqlclient

The Solution

Its a known bug with the .Net SqlClient Data Provider.   If you have a field in the database of type nvarchar(max) or greater than 4000 characters and if the user enters data into the field greater than 4000 characters via the .Net SqlClient Data Provider you will receive an exception.

To get around this, reduce the field size to less that 4000 characters or change your type to ntext or set the Sqlparamter.size property to -1 to allow the entire data to be saved.

References




http://support.microsoft.com/kb/970519

Clean your SQL Server database!

Here are some lines of code that can help clean your database from test data so you can start over with a fresh canvas.

/* Disable constraints and triggers (if any) */

exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

/*Perform delete operation on all table for cleanup, or you can put specific delete <tablename> commands if you want to only do a subset of deletions

*/
exec sp_MSforeachtable ‘DELETE ?’

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable ‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1 BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’

Data in SSRS Report Header

Here is a tip for displaying data from data-sets into the Page Header/Footer of a SSRS report.   Traditional the hacky way of performing such tasks is to have a hidden text-box in the body of the report with the respective data-set field value.   You can then get the value of this text-box in your page header doing the following: =ReportItems!txtBox1.Value.    The major issue here is that the data is not available on multiple pages, so if your report has a large data set you will notice the value will not be shown on each page.

A better solution is to create a new internal report parameter that references a field value from the selected data set.   You can make the report parameter internal and reference it in your report header by doing the following: =Parameters!myParameter.Value.

Its cleaner and is available on every page.

References:

http://geekswithblogs.net/Bunch/archive/2008/03/05/data-into-a-ssrs-header-again.aspx

Split Function for T-SQL using XML

Here is some sample code to split a string and return the results via a table.

CREATE FUNCTION [dbo].[split]
(
@del char(1),
@str varchar(max)
)
RETURNS @tResult TABLE
(
val varchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>’ + replace(@str,@del,'</r><r>’) + ‘</r></root>’

insert into @tResult(val)
select
r.value(‘.’,’varchar(max)’) as item
from @xml.nodes(‘//root/r’) as records(r)

RETURN
END

Selecting a row position within a SELECT query

I want to get the third row in a result set from the following:

ID   Name

1   A

2  B

3  C

4 D

You can acheive this by using the ROW_NUMBER() rank function using SQL Server 2005/2008.   The following code will return the 3rd record.

create table #temp
(
ID int,
[Name] nvarchar(50)
)

insert into #temp values (1,’A’)
insert into #temp values (2,’B’)
insert into #temp values (3,’C’)
insert into #temp values (4,’D’)
insert into #temp values (5,’E’)

select [Name] from #temp where ID in (select ID from
(select ID, ROW_NUMBER() OVER (order by ID asc) as [Row] from #temp) as subquery
where [Row] = 3)

drop table #temp

Result:

Name = C

SQL Server Express 2008 Maintenance Script

The issue I have with SQL Server Express Edition 2008 is that the SQL Agent is disabled and according to Microsoft, this has been disabled on purpose.   So, how do we schedule jobs to do such tasks as Rebuilding Indexes, Full-text Catalogs etc?   I use the Task Scheduler on Windows 2008 Server that executes a SQL script using sqlcmd -i <database script>

Here is the sample code that I use for Rebuilding indexes and catalogs:

declare @databasename varchar(max)
declare @cat varchar(max)

DECLARE database_cursor CURSOR FOR
select name from master.sys.databases
where database_id > 4 –exclude system databases

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @databasename

WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRY
–Below line rebuilds indexes
exec (‘USE ‘ + @databasename + ‘ EXEC sp_MSforeachtable @command1 = “ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);”‘)

exec (‘USE ‘ + @databasename + ‘ declare @cat varchar(max) select @cat = (select top 1 name from ‘ + @databasename + ‘.sys.fulltext_catalogs) if @cat is not null begin exec (”ALTER FULLTEXT CATALOG ” + @cat + ” REBUILD WITH    ACCENT_SENSITIVITY=OFF;”) end’)

END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT

SET @ErrorMessage = ‘ databasename: ‘ + @databasename + ‘ ‘ + ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();

–print @ErrorMessage
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);

END CATCH

FETCH NEXT FROM database_cursor
INTO @databasename
END

CLOSE database_cursor
DEALLOCATE database_cursor Continue reading