Contents tagged with sql server
-
adding custom SSIS transformation to visual studio toolbox fails
Just very recently I encountered an issue in deploying a custom SSIS component assembly which turns out to be a relative "no-brainer" error if only the clues were more straightforward. Basically after deploying the assembly I could not find my component listed in the "SSIS Data Flow Items" tab list.
-
caution in dropping a temp table before creating it
Recently I ran into a script instead a stored procedure
IF OBJECT_ID(tempdb..#temp1) DROP TABLE #temp1
Basically, the object of this script is to check if #temp1 (regular temporary table) exists. If so drop it.
However, I think it can have unintended consequences and maybe safer not to include. [more]
Say you have a script that includes the call to the stored procedure (eg. SampleStoredProc)
If the script (let’s call this “caller”) creates a table #temp1 and at the top of SampleStoredProc you have if object_id(tempdb..#temp1) drop table #temp1, what will happen is that the #temp1 table of the “caller” will be dropped.
And the caller might not want that (or won’t expect that the #temp1 table he/she created will be dropped). It is possible that after calling SampleStoredProc the caller would still want to use/access #temp1.
On the other hand if no drop table #temp1 is executed inside SampleStoredProc and a create table #temp1 is made even if the “caller” has a #temp1 already it will not be a problem. The #temp1 of the caller and #temp1 of BehavClusDOM1 will be separately identified.
Since BehavClusDOM1 is a stored procedure it is a scope for temp tables and safe to assume that at the start of the stored procedure no temp tables are present in that scope.
Basically the idea is that BehavClus should not touch whatever is beyond its scope.
So in my opinion the inclusion of this code can cause unexpected behavior to the caller while removing it poses no risk not to mention shortening the code and decreasing complexity and readability. The author of the stored procedure (eg. SampleStoredProc) involving creation of #temp1 should know when it is present and shouldn't worry about clashing with another #temp1 in another session. You can explicitly DROP TABLE #temp1 if you want but only after you have created your own #temp1 so your sure that you'd be dropping the one you created and not that in other scripts. -
temp table (#), global temp tables (##) and @table variables
-
Convert Delimited Values to a table with Ordinal Column
-
Performance, Measure and ANTS Profiler
Might need to create a separate page for notes on performance since I've been doing a lot of C# and database tuning lately but having them on this post so far. Here are some of my notes on performance [more]
-
Ancestor and Descendant IDs/info list using Common Table Expressions
Needed some TSQL code again to retrieve descendant and ancestor entries in a self-referencing table. I've done this a couple of times already and although I can write it from the top of my head, sometimes you just want to make life easier and have a script you can just modify a bit to fit the new requirement.
-
Recent SQL Injection Attacks on ASP sites
There seems to be a number of SQL injection attacks happening lately involving adding of <script src=http://www.banner82.org/b.js></script>, adword71.com/b.js (and the likes ) to entries under string/text/varchar columns in the database targetting ASP (classic/3.0) sites and SQL Server. Note, they need not know your table or column names to mess up with you.