There are cases when SQL developers want to filter the results of Procedures Output. This requires inserting the results of the procedure in the table. Here is how it can be done
Filter Procedure Output
In the above example the temp table is created with the same columns as that of the procedure output, the Procedure is executed and then stored in the table #temp. The table is later filtered on the users criteria.
Getting the list of tables and Columns that are embedded in a view ,function or a procedure is simple
sp_depends ‘viewName or ProcedureName‘
Documented in MSDN
The Problem occurs when an aggregate function(max,sum,avg..) exists on null values. Trying to get rid of the null values may be the solution but in certain cases..you may need the extra data relying on the null values.
So whats the solution?
Append set ANSI_WARNINGS OFF on the beginning of the sql statement.
When using the same in SSIS packages the Packages may fail.The Output Window may indicate that there is already a OLEDB destination exported which in this case is the error message.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x00040EDA Description: “Warning: Null value is eliminated by an aggregate or other SET operation.”
So use set ANSI_WARNINGS OFF
Sql Server 2008′s new TOP statement could help update the Table in batches
Consider the following Example
Update
The above code illustrates how the batchUpdate can be done using Top().
The top 100 records are updated depending on the criteria where fullname is null so that the same records dont get updated again
Here are the List of features that are deprecated in Sql server 2008
I constantly keep querying the Database sometimes for checking the metadata in the DatawareHouse. Lately I realised that have been executing the same long sql statements everyday. So is there a better way of Organising all my Favourite SQL statments or procedures ?
This is what I ended up with.
Create a Table
Table DataSource
The table contains the DatabaseName and the SQL that needs to be executed on that Database
Create the Procedure FavList
FavList Procedure
The above procedure is for listing the SQL statements and the Databases from the Table.
I have inserted my favourite SQL statements in the DataSource table as shown below
Table DataSource
Create the Procedure Fav
Fav Procedure
Run the Procedure with the parameter as the datasourceid to execute your favourite SQL statements.
like Fav 1,Fav 2 etc
The Procedure can be be modified to use the Search Tags
Happy SQL!!!
Ran a Job yesterday to check how the SQL agent Handles the DayLight Savings
Created the following Table
Table
Created the Following procedure to enter the current Date
Procedure
and Ran the Job every minute from March 28th 2009 22:50 to Mar 29th 2009 3:00 AM
Results:
daylightsaving-results
There was no 01:00 AM on March 29th !!!!. So any jobs scheduled at 01:00 AM will not run. Sql Agent cannot handle it!!!
The Following Script will output the List of Databases in the SQL server and gets the Last Date the Databases were backed up
select Convert(Varchar(25),server_name) as ‘Server Name’
,convert(varchar(25),database_name) as ‘Database’
,max(backup_finish_date) as ‘Last_backup_date’
from msdb..backupset
Where database_name
in
(select name from master..sysdatabases)
and server_name = @@servername
group by server_name,database_name order by Last_backup_date DESC
SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear,
modify_date, datepart(dy,modify_date) as ModificationDayOfYear
FROM sys.sql_modules
JOIN sys.objects
ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
order by datepart(yyyy,modify_date) desc,
datepart(dy,modify_date) desc, name;
Here is the Sql function that will Proper Case any column in a table
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);
select @Reset = 1, @i=1, @Ret = ”
while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like ‘[a-zA-Z]‘ or @c in (””) then 0 else 1 end,
@i = @i +1
return @Ret
end
–Example
declare @name varchar(100)
set @name =’marcus ford’
set @name = dbo.propercase(@name)
print @name
Result — Marcus Ford
My Twitter Feed
