sql-server
TSQL Email Validation (without regex)
Very basic would be: SELECT EmailAddress, CASE WHEN EmailAddress LIKE ‘%_@_%_.__%’ AND EmailAddress NOT LIKE ‘%[any obviously invalid characters]%’ THEN ‘Could be’ ELSE ‘Nope’ END Validates FROM Table This matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for … Read more
Variables scope which are defined within a while block in stored procedures – SQl Server
The variable scope is the whole batch in this case a stored procedure. It isn’t re-declared every loop So this is exactly as expected Edit: There is a recent blog article which is quite similar. The author was quickly corrected 🙂
Find Locked Table in SQL Server
You can use sp_lock (and sp_lock2), but in SQL Server 2005 onwards this is being deprecated in favour of querying sys.dm_tran_locks: select object_name(p.object_id) as TableName, resource_type, resource_description from sys.dm_tran_locks l join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
EF: The text data type cannot be selected as DISTINCT because it is not comparable
Simple answer is “don’t use text”. It was deprecated for varchar(max) years ago when SQL Server 2005 was released. The code you have is issuing SELECT DISTINCT. You need to fix the model/tables so it isn’t text datatype
How to count total number of stored procedure and tables in SQL Server 2008
This will give you the count of tables and stored procedures. SELECT CASE TYPE WHEN ‘U’ THEN ‘User Defined Tables’ WHEN ‘S’ THEN ‘System Tables’ WHEN ‘IT’ THEN ‘Internal Tables’ WHEN ‘P’ THEN ‘Stored Procedures’ WHEN ‘PC’ THEN ‘CLR Stored Procedures’ WHEN ‘X’ THEN ‘Extended Stored Procedures’ END, COUNT(*) FROM SYS.OBJECTS WHERE TYPE IN (‘U’, … Read more
How to get only numeric column values?
SELECT column1 FROM table WHERE ISNUMERIC(column1) = 1 Note, as Damien_The_Unbeliever has pointed out, this will include any valid numeric type. To filter out columns containing non-digit characters (and empty strings), you could use SELECT column1 FROM table WHERE column1 not like ‘%[^0-9]%’ and column1 != ”
Stored Procedures – End of days
maybe i’m too old-school, or too lazy, or both, but i have to disagree. Time and again stored procedures have ‘saved the day’ because when a minor back-end change or bug appears we only have to fix the stored procedure instead of updating the desktop application on several dozen desktops plus the web server. In … Read more
WSDL on SQL Server gives HTTP status 505 Version Not Supported
Today, years after I posted this question, we finally found the answer to this question. It was not a Squid proxy server as we had come to believe. We continued experiencing issues like this with various web services/sites. The last straw was when we finally needed to deploy an SVN server that was used by … Read more
What is the best way to test a stored procedure?
A colleague swears by the TSQLUnit testing framework. May be worth a look for your needs.