DECLARE @tabs TABLE ( id int identity(1,1) not null
, tabname sysname not null );
DECLARE @i int
, @tabname sysname;
INSERT INTO @tabs ( tabname )
WHERE type = ‘U’;
SELECT @i = max(id) FROM @tabs;
WHILE @i > 0
SELECT @tabname = tabname FROM @tabs WHERE id = @i
PRINT ‘UPDATE STATISTICS ‘ + @tabname + ‘ WITH ALL’
SET @i = @i – 1
1. Data type determination of the resulting expression – ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence
2. The NULLability of result expression can be different for ISNULL and COALESCE. ISNULL return value is considered NOT NULLable if the return value is a non-nullable one (in the case when the argument that is returns is based on a non-null column or constant). Whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.
3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:
ISNULL(NULL, NULL) — is int
COALESCE(NULL, NULL) — Will throw an error
COALESCE(CAST(NULL as int), NULL) — it valid and returns int
4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters. You have to have nested ISNULL to get the same effect as COALESCE.
5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL built-in function.
6. You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and some queries with COALESCE be sub-optimal.
The way to implement a “many-to-many” relationship requires an additional table be created consisting of the primary keys from each of the entities. This additional table goes by many different names depending on who you talk to.
Cross-reference (xref) table
Join table (perhaps most common)
Link table, link entity
Many-to-many relationship tables
Probably the most proper (being a mathematical model) but least used is “Gerund” — so named by E.F. Codd, creator of the relational model. This term applies because an entity is functioning as a relationship.
Editorial – Don’t Criticize Code
By Phil Factor
After many years spent supervising development teams, I’ve come to recognize warning signs of problems ahead. There are many such signs, but the following is one that always makes me cringe: a developer looks at code that he or she is assigned to maintain, and says something like “Who one earth wrote such stupid and xxxx (unstructured/ mindless/ unintelligible/etc.) code?”
There is a developer showing his inexperience. Only adolescents writing their first application in some exciting new computer language in their bedroom ever believe that it is possible to write perfect code, or even that they are capable of doing it. Intensive commercial experience as a developer normally grinds off the idealism and naivety that inspires this attitude towards legacy code.
Never is it a good idea to criticize legacy code, let alone the poor developer responsible for it, particularly if you don’t know the history of the application and the circumstances of the code being written. If you don’t show the code due respect, you are in danger of refactoring it into something worse. You probably aren’t aware of the circumstances under which the code was written, or the real brief given to the programmer. The pressures of coding in a commercial development project are difficult to describe, but it is perfectly natural under such conditions to take decisions that, in retrospect look daft, but which actually saved the project.
Legacy code is something I pore through with eagerness, as if it were a rich historical archive; even if it merely illustrates the coder’s drift into lunacy. Many times, when doing so, I have been jolted out of a growing sense of contempt by coming across a technique or insight that is brilliant. This real sense of humility, when faced with the results of human endeavor under pressure, always served me well in my work as a consultant. When you’ve been called in to fix a project that is about to hit the wall, making any suggestion of criticism is probably the least helpful thing you could possibly do. Beyond the easy job of fixing things, comes the more difficult trick of tactfully attributing their original predicament to a cruel act of Fate.
For anyone eager for a long-term career in IT Development, humility and tact are probably more important habits to acquire than demon coding-skills.
CREATE FUNCTION [dbo].[fnTitleCase]( @text AS varchar(8000) )
DECLARE @Reset bit;
DECLARE @Ret varchar(8000);
DECLARE @i int;
DECLARE @c char(1);
SELECT @Reset = 1, @i=1, @Ret = '';
In Visual Studio I usually get errors when using 3rd party controls due to the assemblies not being deployed along with the application.
To resolve this, I put copies of the control assemblies in a sub-folder of the project called “References” and then add those files to the references in the project references in the solution explorer using browse.
You can then go to the properties page of the reference and set “Copy Local” to true. This will ensure that the module gets deployed to the “bin” folder of the app.
Had a problem with this after importing data from one database to another using “SELECT INTO” over a link. A co-worker developed this, so kudos go to him…
declare @table_name varchar(50)
declare @column varchar(50)
declare @datatype varchar(50)
declare @nullornot varchar(15)
declare @length varchar(3)
declare @getprops cursor
SET @getprops = CURSOR for
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CASE WHEN IS_NULLABLE = ‘YES’
ELSE ‘NOT NULL’
END AS NLL
WHERE TABLE_CATALOG = ‘Metrics’
AND COLLATION_NAME = ‘SQL_Latin1_General_CP1_CI_AS’
FROM @getprops into @table_name, @column, @datatype, @length, @nullornot
WHILE @@FETCH_STATUS = 0
PRINT N’ALTER TABLE ‘ + @table_name + N’ ALTER COLUMN ‘ + @column
PRINT @datatype + N'(‘ + @length + N’) COLLATE SQL_Latin1_General_CP850_CI_AS ‘ + @nullornot
FROM @getprops into @table_name, @column, @datatype, @length, @nullornot
I recently had to start doing stuff in MS Access 2007. It has been a great many moons since I’ve worked with Access and, of course, all of the menus have changed.
Now, to get the the settings page for the “Application”, instead of “Options”–>”Tools”, you now click the “Office Button” and down at the bottom of the menu page that pops up is “Access Options”.
This is one location you can specify which form you want to open up automatically when you open the access file.
A nice holdover from previous versions gives another, more flexible method. You can create an macro named “AutoExec” that can launch a macro, form, code module procedure, etc.
To override the default MS Access splash screen, create a 1×1 1bit bitmap file and name it the name as your ms access project (ie: “myproject.bmp”) and place it in the same folder as your ms access project “*.accdb” file.
If you use “CurrentUser()” you will as often as not get “Admin”, which is the default Access user.
To get the user’s windows authentication login name, place the following code at the top of a standard module:
Private Declare Function WNetGetUserA Lib "mpr.dll" _ (ByVal lpszLocalName As String, ByVal lpszUserName As String, lpcchBuffer As Long) As Long Public Function GetNetUser() As String Dim lpUserName As String, lpnLength As Long, lResult As Long 'Create a buffer lpUserName = String(256, Chr$(0)) 'Get the network user lResult = WNetGetUserA(vbNullString, lpUserName, 256) If lResult = 0 Then GetNetUser = Left$(lpUserName, InStr(1, lpUserName, Chr$(0)) - 1) Else GetNetUser = "-unknown-" End If End Function
It can be used in a query as GetNetUser() and in the control source of a text box on a form or report as =GetNetUser()
Will need to add the following to your Web.config:
If you want to turn off one of the protocols, just comment out that add line. The “Documentation” protocol is the .net generated WSDL.