The type initializer for threw an exception static class

Possibly save 1 hour of your time: I got this error when I am creating unit test to test a static class.

The type initializer for ‘YourClassName‘ threw an exception.

You can debug and look at the details of the Inner Exception property.
In my case, I see this error.
{“Value cannot be null.\r\nParameter name: String”}

If you are still not sure, debug into your static class by putting a break point in the first line of the class.

My issue turns out that there was ConfigurationManager.AppSettings value missing in the app.config file.

Advertisements

T-SQL – split a string value based on comma or another delimiter

Possibly save 1 hour of your time: This shows how you can take a column that has data such as a string value with comma separated value or semi-colon separated value and process each value for other purpose. In my case I want to check for foreign key not exist.

</pre>
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
SELECT [column_with_delimiter_separated_values]
FROM [dbo].[YourTable]

DECLARE @textString varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @textString

DECLARE @tempSplitText TABLE
(
SplitString nvarchar(500)
)

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @delimiter char = ';'
DECLARE @pos INT = 0
DECLARE @len INT = 0
DECLARE @value varchar(500)

WHILE CHARINDEX(@delimiter, @textString, @pos+1)>0
BEGIN
set @len = CHARINDEX(@delimiter, @textString, @pos+1) - @pos
set @value = SUBSTRING(@textString, @pos, @len)
INSERT INTO @tempSplitText(SplitString) VALUES(RTRIM(LTRIM(@Value)))
set @pos = CHARINDEX(@delimiter, @textString, @pos+@len) +1
END

IF (LEN(@region) > @len) --deals with last element
BEGIN
set @value = SUBSTRING(@textString, @pos, LEN(@textString)-@pos+1)
INSERT INTO @tempSplitText(TextString) VALUES(RTRIM(LTRIM(@Value)))
END

FETCH NEXT FROM @MyCursor
INTO @textString
END;

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

select r. from @tempSplitText r
left join [dbo].[YourTableWithTheForeignKey] c on r.SplitString = c.ForeignKey
where c.ForeignKey is null
<pre>

 

SSIS – no column information was returned by the sql command

Possibly save 1 hour of your time: In SSIS, I have a data flow task that selects the source with some logic using table variable. It causes this compilation error when you try to save.

SSIS doesn’t know like using table variable in the SQL command.

Solution:

I have some complex logic so I need to use variable table. Temp table would most likely have same issue. So I just use a stored procedure instead. In my SQL command I have exec MyStoredProcedureName.

Using T-SQL cursor to loop through table records

Possibly save 1 hour of your time: There may be times you need to loop through each record in a table and process the data in the specific row or column.

DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
  SELECT column1, column2
  FROM table
  WHERE clause

DECLARE @column1 int;
DECLARE @column2 varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @column1 , @column2

WHILE @@FETCH_STATUS = 0
BEGIN
 print Cast(@column1 as varchar(10)) + '-' + @column2
 FETCH NEXT FROM @MyCursor
INTO @column1 , @column2
END

CLOSE @MyCursor;
DEALLOCATE @MyCursor;