http://www.songhaysystem.com/kb/subject/sql The Funky Knowledge Base at songhaysystem.com. The subject of this channel is SQL and Database Management Systems. Tue, 02 Sep 2008 03:28:56 GMT http://www.songhaysystem.com/kb/number/2076072023/subject/sql Article Excerpt: SQL Server Agent jobs can revert back to their previous owner. This can produce the error: “Unable to determine if the owner (DOMAIN\xxx) of job <JOB_Name> has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))” Ultimately the s… Tue, 02 Sep 2008 03:28:56 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071988/subject/sql Article Excerpt: MSDN: “Several questions have come up lately regarding whether SQL Server Express is an appropriate product to be used in a hosting environment.” “All versions of SQL Server including Express Edition are RECOMMENDED for dedicated (single-tenant) ho… Tue, 19 Aug 2008 22:51:01 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071957/subject/sql Article Excerpt: One glaring difference in the CREATE TABLE syntax between SQL 2000 and 2005 is this 2005 clause: WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] The only WITH clause support in SQL Server 2000 is WITH FILLFACTOR. Mon, 12 May 2008 20:19:56 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071938/subject/sql Article Excerpt: DECLARE @xml xml SET @xml = ' <Root> <Item>one</Item> <Item>two</Item> <Item>three</Item> <Item>four</Item> <Item>five</Item> </Root> ' SELECT Temp.Items.value('.','varchar(16)') AS [Items] FROM @x… Wed, 16 Jan 2008 01:38:06 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071935/subject/sql Article Excerpt: “spDTSChangeOwner, available on the downloads page, is a convenient wrapper around sp_reassign_dtspackageowner. It accepts two parameters @old_owner and @new_owner, which should be self explanatory.” As of this writing, download the script here: … Fri, 28 Dec 2007 19:28:48 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071925/subject/sql Article Excerpt: DECLARE @xml xml SET @xml = ' <Root> <Item1 /> <Item2 /> <Item3 /> <Item4 /> <Item5 /> </Root> ' SELECT Temp.Items.query('.') AS [Items] FROM @xml.nodes('//Root/*') AS Temp(Items) /* The SELEC… Sun, 23 Dec 2007 06:06:50 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071929/subject/sql Article Excerpt: DECLARE @xml xml SET @xml = ' <Root> <Item1 /> <Item2 /> <Item3 /> <Item4 /> <Item5 /> </Root> ' SET @xml.modify (' insert text {"Hello World"} as first into (/Root/Item3)[1] ') SET @xml.modify (' … Sun, 23 Dec 2007 06:58:02 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071928/subject/sql Article Excerpt: DECLARE @xml xml SET @xml = ' <Root> <Item1 /> <Item2 /> <Item3 /> <Item4 /> <Item5 /> </Root> ' SET @xml.modify (' insert text {"Hello World"} as first into (/Root/Item3)[1] ') SELECT @xml /* This projectio… Sun, 23 Dec 2007 07:03:00 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071926/subject/sql Article Excerpt: DECLARE @xml xml SET @xml = ' <Root> <Item1 /> <Item2 /> <Item3 /> <Item4 /> <Item5 /> </Root> ' SET @xml.modify (' insert element Item6 {} as last into (/Root)[1] ') SELECT @xml /* This projection produces … Sun, 23 Dec 2007 06:51:04 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071927/subject/sql Article Excerpt: DECLARE @xml xml SET @xml = ' <Root> <Item1 /> <Item2 /> <Item3 /> <Item4 /> <Item5 /> </Root> ' SET @xml.modify (' delete ((/Root/*[5])[1]) ') SELECT @xml /* <Item5 /> should be deleted. Note that the … Sun, 23 Dec 2007 06:44:38 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071920/subject/sql Article Excerpt: For some crazy reason you may want to avoid making a CREATE XML SCHEMA COLLECTION statement but still validate your XML. Here is an example to do this with XPath assertions in XQuery: IF (@xmlInput.exist('//HostName') = 0) OR (@xmlInput.value('s… Tue, 11 Dec 2007 20:57:09 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071919/subject/sql Article Excerpt: DECLARE @x xml SET @x = ' <root> <myElement myAttribute="attrValue">The element value.</myElement> </root> ' SELECT @x.query('//myElement/node()') as [element value] , @x.query('data(//myElement)') as [element value] , @x.query('data(//myEle… Mon, 10 Dec 2007 23:57:56 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071681/subject/sql Article Excerpt: IF OBJECT_ID('_newproc') IS NOT NULL BEGIN DROP PROCEDURE _newproc IF OBJECT_ID('_newproc') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE _newproc >>>' ELSE PRINT '<<< DROPPED PROCEDURE _newproc >>>' END GO CREATE PROCEDURE… Mon, 19 Nov 2007 23:18:09 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071382/subject/sql Article Excerpt: USE DatabaseName GO IF OBJECT_ID('StoredProcName') IS NOT NULL BEGIN DROP PROCEDURE StoredProcName IF OBJECT_ID('StoredProcName') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE StoredProcName >>>' ELSE PRINT '<<< DROPPED PRO… Mon, 19 Nov 2007 23:14:21 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-2037898918/subject/sql Article Excerpt: /* This form is based on what is extracted from SQL Enterprise Manager: */ /* Object: Stored Procedure MyProc Script Date: 09/13/1999 8:59:44 AM */ IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MyProc') AND sysstat & 0xf = 4) DR… Mon, 19 Nov 2007 23:17:20 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071470/subject/sql Article Excerpt: USE compliance GO IF OBJECT_ID('AutoDTS_complianceDumps') IS NOT NULL BEGIN DROP PROCEDURE AutoDTS_complianceDumps IF OBJECT_ID('AutoDTS_complianceDumps') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE AutoDTS_complianceDumps >>>' E… Mon, 19 Nov 2007 23:15:01 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071444/subject/sql Article Excerpt: USE MyLocalServer GO IF OBJECT_ID('ListIDXFiles') IS NOT NULL BEGIN DROP PROCEDURE ListIDXFiles IF OBJECT_ID('ListIDXFiles') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE ListIDXFiles >>>' ELSE PRINT '<<< DROPPED PROCEDU… Mon, 19 Nov 2007 23:16:26 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/910205312/subject/sql Article Excerpt: * Dynamic SQL and Stored Procedures Fundamentally, use Dynamic SQL (that is, SQL built "on the fly" in code) when it is impossible to use Stored Procedures. Many SQL servers cannot use late binding on database objects. In such servers, stored procedures … Mon, 19 Nov 2007 23:15:47 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071912/subject/sql Article Excerpt: The FOR XML PATH() syntax uses standard XPath queries to replace the proprietary and often extremely confusing syntax of the FOR XML EXPLICIT syntax. The following code block shows how this works: DECLARE @d1 XML , @d2 XML SET @d1 … Thu, 08 Nov 2007 01:39:25 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071882/subject/sql Article Excerpt: /* A user gets often gets orphaned (or broken) when attaching a database to a new server. Documentation: http://msdn2.microsoft.com/en-us/library/ms174378.aspx */ EXECUTE sp_change_users_login @Action='Auto_Fix', @… Mon, 17 Sep 2007 20:16:37 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071807/subject/sql Article Excerpt: USE [Database] GO IF OBJECT_ID('fItemExists') IS NOT NULL BEGIN DROP FUNCTION fItemExists IF OBJECT_ID('fItemExists') IS NOT NULL PRINT '<<< FAILED DROPPING FUNCTION fItemExists >>>' ELSE PRINT '<<< DROPPED FUNCTION fItemExi… Wed, 04 Apr 2007 19:44:27 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071727/subject/sql Article Excerpt: USE [Database] GO IF OBJECT_ID('fUserDefined') IS NOT NULL BEGIN DROP FUNCTION fUserDefined IF OBJECT_ID('fUserDefined') IS NOT NULL PRINT '<<< FAILED DROPPING FUNCTION fUserDefined >>>' ELSE PRINT '<<< DROPPED FUNCTION fUs… Tue, 20 Mar 2007 02:51:58 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071726/subject/sql Article Excerpt: USE [Database] GO IF OBJECT_ID('fUserDefined') IS NOT NULL BEGIN DROP FUNCTION fUserDefined IF OBJECT_ID('fUserDefined') IS NOT NULL PRINT '<<< FAILED DROPPING FUNCTION fUserDefined >>>' ELSE PRINT '<<< DROPPED FUNCTION fUs… Tue, 20 Mar 2007 02:51:30 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071783/subject/sql Article Excerpt: This statement used with ExecuteScalar() will truncate at 2033 characters: SELECT * FROM Segment , [Document] WHERE Segment.SegmentId = [Document].SegmentId FOR XML AUTO, ELEMENTS, ROOT('SegmentTreeSet') Sol… Tue, 16 Jan 2007 23:16:13 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071778/subject/sql Article Excerpt: DECLARE @cmd VARCHAR(512) DECLARE @lf CHAR(1) SET @lf = CHAR(10) --Unix line break SET @cmd=' BULK INSERT LogImport FROM ''{0}'' WITH ( ROWTERMINATOR = '''+@lf+''' ) Fri, 20 Oct 2006 11:00:35 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071756/subject/sql Article Excerpt: 0 or 100: mon dd yyyy hh:miAM (or PM) 101: mm/dd/yy 102: yy.mm.dd 103: dd/mm/yy 104: dd.mm.yy 105: dd-mm-yy 106: dd mon yy 107: Mon dd, yy 108: hh:mm:ss 9 or 109: mon dd yyyy hh:mi:ss:mmmAM (or PM) 110: mm-dd-yy 111: yy/mm/dd 112: yymmdd 13 or 113: dd mon… Wed, 12 Jul 2006 23:16:42 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071728/subject/sql Article Excerpt: IF OBJECT_ID('fDateFormat') IS NOT NULL BEGIN DROP FUNCTION fDateFormat IF OBJECT_ID('fDateFormat') IS NOT NULL PRINT '<<< FAILED DROPPING FUNCTION fDateFormat >>>' ELSE PRINT '<<< DROPPED FUNCTION fDateFormat >>>' END ; /* … Sat, 14 Jan 2006 17:47:32 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071729/subject/sql Article Excerpt: From Eron Wright's Blog: "You can convert local datetime values to UTC datetime values, and vice-versa, using the built-in GETUTCDATE() function:" DECLARE @LocalDate DATETIME SET @LocalDate = GETDATE() -- convert local date to utc date D… Sat, 14 Jan 2006 17:52:53 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071487/subject/sql Article Excerpt: The Security tab under the server-level Properties menu in the Enterprise Manager has options under Authentication:. I prefer to select the Windows only option as this effectively turns over the bulk of security issues over to Windows domain administrator… Fri, 16 Sep 2005 21:41:13 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071706/subject/sql Article Excerpt: 'Based on Global Variables and SQL statements in DTS by Darren Green 'http://www.sqldts.com/Default.aspx?205,2 Option Explicit Function Main() Dim objDtsPack,vSQL ' Get reference to the Exec SQL Task Set objDtsPack = DTSGlobalVariables.Paren… Thu, 15 Sep 2005 22:22:30 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071673/subject/sql Article Excerpt: SELECT p.* FROM OPENROWSET ( 'SQLOLEDB' , 'Trusted_Connection=yes; Integrated Security=SSPI; DataSource=MyServer;Initial_Catalog=pubs;' , ' SELECT * FROM pubs.dbo.authors ORDER … Fri, 06 May 2005 19:23:15 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071669/subject/sql Article Excerpt: Use the ALTER DATABASE t-SQL instead of the system stored procedure sp_renameDB. For SQL Server 2000, the ALTER DATABASE solution is an improvement over the system stored procedure (which can get into goofy 'exclusive locking errors'). One too many SQL Se… Thu, 28 Apr 2005 20:05:33 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071660/subject/sql Article Excerpt: /* This script is inspired by two MS KB articles: "PRB: 'Troubleshooting Orphaned Users' Topic in Books Online is Incomplete" http://support.microsoft.com/kb/274188 and "HOW TO: Resolve Permission Issues When You Move a Database Between Servers T… Sun, 27 Mar 2005 17:11:45 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071622/subject/sql Article Excerpt: I am familiar with the Transform Data Task. I use this frequently because this task is related to importing and exporting data. The Data Driven Query Task became relevant when I found frustration and a few hours of misery trying to update data with the Tr… Tue, 23 Nov 2004 23:13:45 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071609/subject/sql Article Excerpt: mysqldump Ver 9.11 Distrib 4.0.21, for Win95/Win98 (i32) By Igor Romanenko, Monty, Jani & Sinisa This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Dumping defi… Sun, 17 Oct 2004 03:10:30 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071605/subject/sql Article Excerpt: This is a proposed set of "minimum" fixed-role permissions required to run a DTS package to change data on the target database defined in the package. The login should be a member of the following fixed roles on the target database server: db_ddladmi… Tue, 31 Aug 2004 23:10:20 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071575/subject/sql Article Excerpt: To search for text like '[foo]' and '[bar]' where the brackets are seen as literals we can use the LIKE operator in the WHERE predicate: ... WHERE [ColumName] LIKE '[[]%]' This is covered in detail under "Using Wildcard Characters as Literals" i… Thu, 29 Apr 2004 19:05:03 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071549/subject/sql Article Excerpt: '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim objFileSys : Set objFileSys = CreateObject("Scripting.… Thu, 04 Dec 2003 19:51:27 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071528/subject/sql Article Excerpt: Microsoft readily reminds us that "correlated subqueries" can be included in the WHERE clause: SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_i… Mon, 11 Aug 2003 23:44:17 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071484/subject/sql Article Excerpt: Up until shortly before the writing if this article, I considered the use of table aliases just a matter of style. Since I enjoy writing (for humans) I preferred using table names instead of something like: USE pubs SELECT p.pub_id , … Fri, 31 May 2002 17:54:39 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071427/subject/sql Article Excerpt: Microsoft Access has the MDB file to represent its "classic" IISAM data stores in the OS file system; and we can't forget about its associated LDB file that appears when the MDB is opened and vanishes when closed. I see something similar in the SQL Server… Tue, 15 Jan 2002 08:26:20 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071359/subject/sql Article Excerpt: Views are not just useful for security reasons (where only subsets of data is shown). Views also improve performance when displaying normalized data. For more details please see the following: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5917 Sat, 05 Jan 2002 17:01:30 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071309/subject/sql Article Excerpt: The SQL 7 Tables sysxlogins and sysservers can be opened to remove users and/or servers that you do not want to appear in dialogs in the Enterprise Manager. By opening the SQL Server Properties > Server Settings dialog in the Enterprise Manager you can ma… Sat, 05 Jan 2002 17:02:30 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071399/subject/sql Article Excerpt: USE pubs ; SELECT Customers.* , Orders.* FROM Northwind.dbo.Customers AS Customers , OPENROWSET( -- OLE DB Provider Name: 'Microsoft.Jet.OLEDB.4.0' -- OLE DB Provider Source, ID and Password or Connection String: … Sun, 27 May 2001 01:13:57 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071400/subject/sql Article Excerpt: DECLARE @vSQL nvarchar(4000) SET @vSQL = 'SELECT * FROM authors' EXECUTE sp_executesql @vSQL Sun, 27 May 2001 01:14:43 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071385/subject/sql Article Excerpt: -- This code is inspired by MS KB article Q139444 -- ("INF: How to Remove Duplicate Rows From a Table"). SELECT [date],host,[file],kbID INTO #uVals FROM tblKLog GROUP BY [date],host,[file],kbID HAVING COUNT(*) > 1 DELETE tblKLog … Sun, 08 Apr 2001 01:40:52 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071353/subject/sql Article Excerpt: 'This is a "sketch" of handling a DTS package programmatically. 'For more info' check out the following MSKB articles: ' '"Q221193 - INF: How To Install DTS Event Handlers In Visual Basic" ' '"Q240221 - INF: How To Handle Errors in DTS Package and Step Ob… Sun, 08 Apr 2001 01:54:20 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071336/subject/sql Article Excerpt: /* Use this technique when enabling full-text search functionality is not possible. If full-text search functionality is possible consider a design featuring the CONTAINS Predicate. */ CREATE PROCEDURE ListSSKB ( @subjectID varchar(32) = NUL… Sat, 31 Mar 2001 17:08:21 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071380/subject/sql Article Excerpt: CREATE PROCEDURE NewHitLog AS -- Description: Inserts rows into tblLog from tblLogImport -- Developer: rasx SET NOCOUNT ON DECLARE @type varchar(10) , @date datetime , @address varchar(255) , @file varchar(255) , @logEntry varch… Sun, 04 Mar 2001 18:07:23 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071381/subject/sql Article Excerpt: CREATE PROCEDURE GetHitsImport ( @errorCount int OUTPUT ) AS -- Description: Transforms data in tblLogImport into data for tblLog. -- Developer: rasx DECLARE @logEntry varchar(1000) , @type varchar(8) , @date … Sun, 04 Mar 2001 18:06:53 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/917916325/subject/sql Article Excerpt: Sometimes is is better to DROP a table instead of using the ALTER TABLE or sp_rename commands. If the table has data in it, use the SELECT INTO syntax to create a temporary table. For example: SELECT * INTO tmpTable FROM MyTable when the tab… Sun, 24 Dec 2000 22:57:04 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071302/subject/sql Article Excerpt: CREATE Procedure ListGo2Events AS SET NOCOUNT ON -- Collect data into temp' table. SELECT tblVenueEvents.EventID , tblVenueEvents.ArtistSummary , tblVenueEvents.ArtistExtended , tblVenueEvents.VenueID , tblVenueEvents.DoorDate , tblV… Sun, 24 Dec 2000 22:50:05 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/892587086/subject/sql Article Excerpt: When HAVING appears in a SQL statement, this implies that there is a GROUP BY clause in the statement. If WHERE is also in the statement, the WHERE clause creates the first subset. The HAVING clause makes a "second pass" after the GROUP BY clause and make… Sun, 24 Dec 2000 22:50:17 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/923177003/subject/sql Article Excerpt: UNION ALL selects all records between two views including duplicates; whereas UNION takes the time to sort records in order to remove duplicates. To optimize the time a UNION query takes, using ALL may help. Sun, 24 Dec 2000 22:49:41 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/225/subject/sql Article Excerpt: Let's project Column1 in Table1 and look for duplicates. The SQL syntax for this is: SELECT Column1 FROM Table1 GROUP BY Column1 HAVING Count(*) > 1 NOTE: the argument of Count() relates to the projections of GROUP BY. Sun, 24 Dec 2000 22:52:36 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/895694248/subject/sql Article Excerpt: SQL Statements containing references to views based on domain aggregate functions are by definition not updateable. Sun, 24 Dec 2000 22:48:52 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/904268905/subject/sql Article Excerpt: These equations are derived from the so-called "Truth Tables": True AND True = True True AND False = False True AND Null = Null False AND True = False False AND False = False False AND Null = False Null AND True = Null N… Sun, 24 Dec 2000 22:47:33 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071320/subject/sql Article Excerpt: SYSDATE stores dates in days by default. It follows that all arithmetic operations with SYSDATE as an operand must be units of days. For example, to subtract 30 minutes from the current time, convert 30 minutes into units of days: 30 [minutes]/1440 [… Sun, 24 Dec 2000 22:51:51 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071333/subject/sql Article Excerpt: The need to follow the rules of Normalization break down in the context of DBMS performance and analytical processing. For details on the subject of Denormailization please see "BENEFITS OF DENORMALIZED RELATIONAL DATABASE TABLES" at: http://www.siue… Sun, 24 Dec 2000 22:48:23 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071315/subject/sql Article Excerpt: Fundamentals Microsoft SQL Server 7 is by default case insensitive as is Oracle 7, however Oracle 7.x explicitly stores all characters in upper-case. Procedural Languages Microsoft SQL Server 7 uses t-SQL or Transaction SQL to create stored procedures… Sun, 05 Nov 2000 01:02:00 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-463206374/subject/sql Article Excerpt: {call get_cust_dates(1,19993)} Wed, 02 Feb 2000 08:26:05 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/934617904/subject/sql Article Excerpt: Database management systems like those made by Sybase do not support what Microsoft calls the "Immediate If" or IIF() functions in their implementation of SQL. Sybase does have the System function isnull() which effectively the VBA equivalent of: IIF… Sun, 15 Aug 1999 16:56:33 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-556144079/subject/sql Article Excerpt: Borrowing heavily from the writings of Microsoft, please consider TableA and TableB with ColumnA and ColumnB respectively. Here are the two outer joins in a WHERE clause: TableA.ColumnA *= TableB.ColumnB This means, "Show all values in ColumnA but o… Thu, 12 Aug 1999 03:15:35 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-621649463/subject/sql Article Excerpt: When browsing a Microsoft or Sybase SQL Server database, looking for system objects, use a query of form: SELECT name FROM sysobjects WHERE type = '<type>' where <type> can be one of the following: C - CHECK constraint D - Default or DEFAULT c… Tue, 09 Feb 1999 19:37:47 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/897948023/subject/sql Article Excerpt: By wrapping the query (or queries) to be tested in GETDATE() commands, you can estimate how long it takes for the tSQL to run. This form is SELECT GETDATE() -- Enter t-SQL to be tested here. SELECT GETDATE() GO Tue, 09 Feb 1999 19:35:15 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/890074141/subject/sql Article Excerpt: The following t-SQL deletes rows from a table named "output_wal_ytm_detail" where its column calc_date contains values over 10 days old: DELETE output_wal_ytm_detail WHERE calc_date < DATEADD( dd , -10 , GETDATE() ) Tue, 09 Feb 1999 19:35:25 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/147/subject/sql Article Excerpt: Suppose Table1 has records that are not in Table2. Also suppose that Table1 and Table2 have a column named "IndexColumn" which can be used for an outer join. We want an outer join because we want to delete the records in Table1 that are not in Table2. In… Tue, 09 Feb 1999 19:36:41 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/917563494/subject/sql Article Excerpt: These are reflections on the SQL standards and guidelines derived through my direct experience in the field. Currently I am not aware of any popular "manuals of style" published by independent third parties. * After the DBA Gives You a Database Once a … Tue, 02 Feb 1999 18:30:21 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/915667772/subject/sql Article Excerpt: ISQL/w can read both Microsoft and Sybase SQL server databases. The SQL Client Configuration Utility can be used to add a Sybase SQL Server to the list of available servers. Under the Advanced > Client Configuration, type in a Sybase server name and selec… Thu, 07 Jan 1999 00:16:22 GMT Bryan Wilhite