SSIS Oracle Connection Error

Added here to link to msdn article.

A query that can find the maximum value across many columns (A virtual Pivot)

Query To Alter ALL Tables in a specified Database

--Query To Alter all Tables in @db_NAME to include 5 new Columns, Status, Create_Date, modify_Date, Creator_ID, Modifier
declare @db_NAME varchar(100); select @db_NAME = 'test_indicator'
declare @tbl_TYPE varchar(100); select @tbl_TYPE = 'base table'
declare @tbl_NAME varchar(100);
declare @tbl int;
declare @tbl_MAX int;
declare @sql varchar(1000)
IF object_id('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
CREATE TABLE #Temp(
id int IDENTITY(1,1),
table_catalog varchar(255),
table_name varchar(255)
)

insert into #Temp
select table_catalog, table_name from INFORMATION_SCHEMA.TABLES where table_catalog=@db_NAME and table_type = @tbl_TYPE and table_name <> 'dtproperties'
select @tbl=min(id),@tbl_MAX = count(id) from #Temp


WHILE @tbl <= @tbl_MAX
BEGIN
select @tbl_NAME = table_name from #temp where id = @tbl
print @tbl_NAME
set @sql = 'ALTER TABLE ' + @tbl_NAME + ' ADD status bit, create_date datetime, modify_date datetime, creator_id int, modifier_id int'
--exec (@sql)
print @sql
set @tbl = @tbl +1
END

Get All Columns of a Table - MSSQL

As You can tell an iteration of INFORMATION_SCHEMA.COLUMNS is required, and I felt this should be created as an object of type function for best extensibility. Infact the post after this uses the same to achieve a more complex objective.

CREATE FUNCTION GetColumns (@tbl varchar(100))
RETURNS varchar(1000)
AS
BEGIN

declare @i int; select @i=min(ordinal_position) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl
declare @MAX int; select @MAX = count(ordinal_position) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl
declare @col varchar(100);declare @data varchar(100);
declare @TEMP varchar(1000); set @TEMP = ''
WHILE @i <= @MAX
BEGIN
select @col=Column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl and ordinal_position= @i
select @data=data_type from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl and ordinal_position= @i
if @i =1
set @TEMP = @col
else
set @TEMP = @TEMP + ',' + @col
set @i = @i +1

END
set @TEMP = @TEMP + ' '
return @TEMP
END

Edit and Continue (Cannot currently modify this text in the editor. It is read only)

In Visual Studio 20080 SP 1, Edit and Continue Does not work for code behind files in ASP.NET applications .NET framework 3.5. Code files get automatically locked on building the application and remain locked for the duration of the build, returning a "Cannot currently modify this text in the editor. It is read only" error message as indicated below.

I have tried all the following suggestions in the below methods to fix it.
http://msdn.microsoft.com/en-us/library/ba77s56w.aspx
http://social.msdn.microsoft.com/forums/en-US/vbide/thread/b65225e3-5b6a-4ac6-a902-6b1427c694aa/

I am running this on a Windows XP SP2 32 Bit computer, in DEBUG mode. I am also attaching snapshots of the current settings in my computer.

Thanks

Kanhar Munshi

Custom SPID Kill Procedure (kill2)

An overloaded kill procedure that increases the functionality of the standard SQL Kill operator, allowing the user to kill all processes within a range, or by comma separated values, or by database name and lastly by SQL login name.

[kill2] ''ln:username'' Deletes all Connects to database by UserName' '
[kill2] ''db=indicator'' Deletes all Connections made to Indicator Database' '
[kill2] ''15-33'' Deletes all SPIDS from 15 - 33' '
[kill2] ''15,16,17,32'' Deletes all listed Spid


ALTER procedure [dbo].[kill2] @param2 varchar(500)
as
--declare @param2 varchar(500)
declare @param varchar(500)
declare @startcount int
declare @killcmd varchar(100)
declare @endcount int
declare @spid int
declare @spid2 int
declare @tempvar varchar(100)
declare @tempvar2 varchar(100)
--set @param2 ='54'
set @param=REPLACE(@param2,' ','')
if CHARINDEX('-',@param) <> 0
begin
select @startcount= convert(int,SUBSTRING(@param,1,charindex('-',@param)-1))
select @endcount=convert(int,SUBSTRING(@param,charindex('-',@param)+1,(LEN(@param)-charindex('-',@param))))
print 'Killing all SPIDs from ' + convert(varchar(100),@startcount)+' to ' +convert(varchar(100),@endcount)
while @startcount <=@endcount
begin
set @spid=(select spid from master.dbo.sysprocesses where spid=@startcount and spid>50)
if @spid = @startcount
begin
print 'Killing '+convert(varchar(100),@startcount)
set @killcmd ='Kill '+convert(varchar(100),@startcount)
exec(@killcmd)
end
else
begin
Print 'Cannot kill the SPID ' +convert(varchar(100),@startcount) + ' because it does not Exist'
end
set @startcount=@startcount + 1
end
end
if CHARINDEX(',',@param) <> 0
begin
set @tempvar =@param
while charindex(',',@tempvar ) <> 0
begin
SET @tempvar2=left(@tempvar,charindex(',',@tempvar)-1)
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar2) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar2)
begin
print 'Killing '+CONVERT(varchar(100),@tempvar2)
set @killcmd='Kill '+CONVERT(varchar(100),@tempvar2)
exec (@killcmd)
end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@tempvar2) + ' because it does not Exist'
end
set @tempvar =REPLACE(@tempvar,left(@tempvar,charindex(',',@tempvar)),'')
end
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar)
begin
print 'Killing '+CONVERT(varchar(100),@tempvar)
set @killcmd='Kill '+CONVERT(varchar(100),@tempvar)
exec (@killcmd)
end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@tempvar) + ' because it does not Exist'
end
end
if CHARINDEX('=',@param2) <>0
begin
print 'Killing all the SPIDs that are connected to the database '+RIGHT(@param2,(len(@param2)-3))
declare dbcursor
cursor forward_only for select SPID from master.dbo.sysprocesses where DB_NAME(dbid) = RIGHT(@param2,(len(@param2)-3))
open dbcursor
fetch dbcursor into @spid
while @@FETCH_STATUS =0
begin
set @spid2=(select spid from master.dbo.sysprocesses where spid=@spid and spid>50)
if @spid = @spid2 begin
print 'Killing '+CONVERT(varchar(100),@spid2)
set @killcmd='Kill '+CONVERT(varchar(100),@spid2)
exec (@killcmd)
end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@spid2) + ' because it does not Exist'
end
fetch dbcursor into @spid
end
close dbcursor
deallocate dbcursor
end
if CHARINDEX(':',@param2) <>0
begin
print 'Killing all the SPIDs that are associated with username '+RIGHT(@param2,(len(@param2)-3))
declare dbcursor
cursor forward_only for select SPID from master.dbo.sysprocesses where loginame = RIGHT(@param2,(len(@param2)-3))
open dbcursor
fetch dbcursor into @spid
while @@FETCH_STATUS =0
begin
set @spid2=(select spid from master.dbo.sysprocesses where spid=@spid and spid>50)
if @spid = @spid2 begin
print 'Killing '+CONVERT(varchar(100),@spid2)
set @killcmd='Kill '+CONVERT(varchar(100),@spid2)
exec (@killcmd)
end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@spid2) + ' because it does not Exist'
end
fetch dbcursor into @spid
end
close dbcursor
deallocate dbcursor
end


if CHARINDEX('-',@param)=0 and CHARINDEX(',',@param) = 0 and CHARINDEX('=',@param)=0 and CHARINDEX(':',@param)=0
begin
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@param) and spid>50)
if @spid = CONVERT(varchar(100),@param)
begin
print 'Killing '+CONVERT(varchar(100),@param)
set @killcmd='Kill '+CONVERT(varchar(100),@param)
exec (@killcmd)
end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@param) + ' because it does not Exist'
end
end

Advanced Null Handling using the GROUP BY operator

As you may know,

select count(criteria) from junk
where criteria is null

and

select count(criteria) from junk
where criteria is not null

will always give you the same number. 10, in this case, irrespective of how many null values there exist in Column Criteria.

Therefore the problem would become clear if we needed to find the ratio of the number of null values for the following queries grouped by column 'FY' as below.

SELECT distinct count(case when decal is null then 0 else 1 end) as nulldecal,
Case
When [Date of Violation] Between '7/1/2005' AND '6/30/2006 23:59:59' then 'FY06'
When [Date of Violation]Between '7/1/2006' AND '6/30/2007 23:59:59' then 'FY07'
When [Date of Violation] Between '7/1/2007' AND '6/30/2008 23:59:59' then 'FY08'
else 'nulldate'
end as FY
from [ECB Violations]
where decal is null
group by
Case
When [Date of Violation] Between '7/1/2005' AND '6/30/2006 23:59:59' then 'FY06'
When [Date of Violation]Between '7/1/2006' AND '6/30/2007 23:59:59' then 'FY07'
When [Date of Violation] Between '7/1/2007' AND '6/30/2008 23:59:59' then 'FY08'
else 'nulldate'
end