Bugs in Sql Server

Today i got noticed a bug in sql server 2005(also in SSMS 08) while executing a simple select statement

select 3 --(*)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '--(*'.

If we put a space or any other characters before the "(" there is no error. Also
select 3 --)* not returning error.Any way this is a bug in sql server as it does not parse the comment statements



Also i got an error message while Parsing an sql script as

."Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."

But the script is executing successfully and giving the accurate results!!!!

Last modified Db User Objects & Tables without Trigger in Sql server

If you need to check the details of all DB User Objects by Last Modified Date try the following query


select name, modify_date, case when type_desc = 'USER_TABLE'
then 'Table'when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')
then 'Function'end as type_desc from sys.objects where type in ('U', 'P', 'FN', 'IF', 'TF')and is_ms_shipped = 0
order by 2 desc  
Find the below query that will list all tables without triggers in sql server.This is helpful when someone modify the table
make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make changes.
 
SELECT ob1.name FROM sysobjects ob1 LEFT JOIN sysobjects ob2 ON
ob1.id =ob2.parent_obj AND ob2.xtype = 'TR'
WHERE ob2.name IS NULL AND ob1.xtype = 'U'
ORDER BY ob1.name

ELMAH (Error Logging Modules and Handlers)

HTTP modules and handlers can be used in ASP.NET to provide a high degree of componentization for code that is orthogonal to a web application, enabling entire sets of functionalities to be developed,packaged and deployed as a single unit and independent of an application. ELMAH (Error Logging Modules and Handlers) illustrates this approach by demonstration of an application-wide error logging that is completely pluggable. It can be dynamically added to a running ASP.NET web application, or even all ASP.NET web applications on a machine,without any need for re-compilation or re-deployment.The end-user(if allows) can view the exception/inner exception logged in the server,Its description and even the screen-shot of the
error page


It can be downloaded from
http://code.google.com/p/elmah/
http://msdn2.microsoft.com/en-us/library/aa479332.aspx
http://download.microsoft.com/download/E/9/7/E97DB6A9-3418-46DF-99CA-840
357FE4D72/MSDNElmah.msi

Using spt_values to generate junk datas

master..spt_values can be used if you need to populate a table with 100 numbers from 1,or if you need to generate some Junk/Test data.Refer the query below that will generate data of various datatypes

select
abs(checksum(newid()))%10000 as n_id,
abs(checksum(newid()))*rand()/10000 as f_float,
substring(replace(cast(newid() as varchar(36)),'-',''),1,abs(checksum(newid()))%15) as s_string,
dateadd(day,0,abs(checksum(newid()))%100000) as d_date,
abs(checksum(newid()))%2 as b_byte
from master..spt_values
where type='p' and number between 1 and 200

Getting all reference of an Assembly

To get the assembly reference in a ASP.NET web application i found a recursive method that will read from the csproject file.
Import these namespace also
using System.Xml.Linq;
 
public void LoadAssembly(string path)
{

int result=0;
XNamespace msbuild = "http://schemas.microsoft.com/developer/msbuild/2003";
XDocument projDefinition = XDocument.Load(path);
IEnumerable<string> references = projDefinition.Element(msbuild + "Project")
.Elements(msbuild + "ItemGroup")
.Elements(msbuild + "Reference")
.Elements(msbuild + "Name")
.Select(refElem => refElem.Value);
foreach (string reference in references)
{

richTextBox1.AppendText(reference);

richTextBox1.AppendText(Environment.NewLine);

        }
}
 
If you need to loop through the IEnumerable iterator to find out other xml nodes u can use the following
 
using (IEnumerator<string> enumerator = references.GetEnumerator())
{

while (enumerator.MoveNext())
result++;

}
if (result == 0)
{

//Do ur xml operations

}

Trigger to monitor the entire database server

Please find this trigger script to monitor the entire database activities and to log its records into audit tables.
It will work on Sql Server 2008.
 
USE [master]
-------Create table -------------------------
-- DROP TABLE AuditTrigger
CREATE TABLE [dbo].[AuditTrigger](
[OID] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](max) NULL,
[Host] [varchar](20) NULL
)
GO
-----Create proc to insert into AuditTrigger table
IF EXISTS(SELECT 1 FROM sysobjects where name = 'PInsertAuditTrigger')
BEGIN
DROP PROCEDURE PInsertAuditTrigger
END
GO
CREATE PROC PInsertAuditTrigger(@Details varchar(max),@hostname VARCHAR(MAX))
AS
BEGIN
INSERT INTO master.dbo.AuditTrigger(DETAILS,HOST) SELECT @DETAILS,@HOSTNAME
END
-------- Trigger to monitor tables
GO
CREATE TRIGGER [Table_Trigger]
ON ALL SERVER
FOR create_table,alter_table,drop_table
AS
BEGIN
print 'Create/Alter table is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter table'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor procedures
GO
CREATE TRIGGER [Proc_Trigger]
ON ALL SERVER
FOR create_procedure,alter_procedure,drop_procedure
AS
BEGIN
print 'Create/Alter procedure is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter procedure'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor login,roles and database
GO
CREATE TRIGGER [Misc_Trigger]
ON ALL SERVER
FOR Create_role,drop_role,alter_role,create_database,drop_database,alter_database,create_view,alter_view,drop_view,create_login,drop_login
AS
BEGIN
print 'Illegal operation is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Illegal operation'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
GO

Removing all active connections from the db

Use Master

Go

Declare @dbname sysname

Set @dbname = 'DBNAME'

Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null Begin

Execute ('Kill ' + @spid)

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname) and spid > @spid

End

Sql Server 2008-Spacial Data

Microsoft SQL Server 2008 delivers comprehensive spatial support that enables organizations to seamlessly consume, use, and extend location-based data through spatial-enabled applications which ultimately helps end users make better decisions.storing Lat/Long data in a geography datatype and being able to calculate/query using the functions that go along with it.It supports both Planar and Geodetic data.


•Use the new geography data type to store geodetic spatial data and perform operations on it

•Use the new geometry data type to store planar spatial data and perform operations on it

•Take advantage of new spatial indexes for high performance queries

•Use the new spatial results tab to quickly and easily view spatial query results directly from within Management Studio

•Extend spatial data capabilities by building or integrating location-enabled applications through support for spatial standards and specifications

DECLARE @g GEOMETRY
SET @g = 'LINESTRING ( 69 26, 69 23, 69 21, 67 20, 65 20,
63 18, 58 17, 52 17, 51 17, 49 17, 45 18, 44 20,
44 21, 42 26, 42 29, 42 32, 42 35, 43 35, 47 38,
50 41, 55 42, 58 42, 65 44, 66 44, 67 44, 68 45,
69 47, 70 48, 70 50, 71 51, 70 56, 68 60, 68 63,
66 65, 65 66, 63 68, 60 71, 59 71, 57 71, 55 71,
51 69, 45 65, 44 63, 42 62, 41 59, 41 57, 41 56,
41 54, 42 53 ,67 77 ,44 88)'
insert into location values(2,'renju',null,@g);

declare @p geometry
set @p= 'POLYGON ((30 30, 40 30, 40 40, 30 40, 30 30))'
insert into location values(3,'renju2',null,@p);

http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

Sql Server Hidden Features - Part 2

A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.
It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily -

it allows access to the "virtual" tables called inserted and deleted (like in triggers):

 

DELETE FROM (table)OUTPUT deleted.ID, deleted.DescriptionWHERE (condition)

 

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2)OUTPUT inserted.IDVALUES (Value1, Value2)

 

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

 

UPDATE (table)SET field1 = value1, field2 = value2OUTPUT inserted.ID, deleted.field1, inserted.field1WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable (OUTPUT INTO @myInfoTable).

 

More tips

• Use ctrl-0 to insert a null value in a cell
• WITH (FORCESEEK) which forces the query optimizer to use only an index seek operation as the access path to the data in the table.
  http://msdn.microsoft.com/en-us/library/bb510478.aspx
• PIVOT and UNPIVOT
• Save transactions
• Select xact_state()  --Returns whether transaction pending/committed
• xp_fixeddrives       --Returns the free disk space in the server
• SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),UNICODE(N'?'),NCHAR(923),NCHAR(UNICODE(N'?')) --Some Ascii/unicode features
• select * from tablename tablesample(2 percent) --Returns the 2% of the records in the db.Alt select top 10 percent * from table
• select cast(GETDATE()as varchar) as datetime --Returns the date time as formatted.
• WITH TIES
  The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group
• select top 5 with ties * from scores order by name desc
• SELECT Column FROM Table ORDER BY CHECKSUM(NEWID()) -- Return rows in a random order

• master..xp_cmdshell to run commands from a command prompt on the server
  master..xp_cmdshell 'dir d:\'   --List the Directory
  master..xp_cmdshell 'sc query "ccm6.5_ess"' --Query a service
  master..xp_cmdshell 'sc start "ccm6.5_ess"' --Start a service

 

Two free security tools from Microsoft SDL team

The SDL team here at Microsoft released a couple of new tools recently to help development teams verify the security of their software before they ship. BinScope Binary Analyzer and MiniFuzz File Fuzzer are both being offered as free downloads. The team took the time to make sure that both tools work as stand-alone tools as well as integrated into Visual Studio and Team System.

BinScope is a verification tool that has been used inside Microsoft for several years to help developers and testers confirm they are building their code to use compiler/linker protections required by the SDL. BinScope allows you to scan your code to verify you are setting important security protections such as /GS, /SafeSEH, /NXCOMPAT, and /DYNAMICBASE. In addition it checks to see that you are using .NET strong-named assemblies, good ATL headers, an up-to-date compiler, and not using dangerous constructs such as global function pointers.



Both of these tools are equipped to easily integrate with Visual Studio 2008 Pro as well as Team Foundation Server 2008 and Team System 2008. By installing BinScope as integrated, it can be launched and output results within the Visual Studio IDE. MiniFuzz can be installed as an external tool add-in. Both tools have easy-to-set integration with Team Foundation Server 2008 and compliment the SDL Process Template for VSTS.

Writing secure code is becoming very important to most development teams. I am glad to see the SDL team making these types of tools available to the Visual Studio development community and making it easier to ship more secure code.