Contents tagged with server

dbo.PartitionLeft, dbo.PartitionRight and dbo.Partition

Thursday, June 8, 2006
backoffice microsoft server sql sql server
No Comments

IF EXISTS(

Read more...

CREATE FUNCTION dbo.IsValidCPF

Tuesday, May 9, 2006

backoffice microsoft server sql sql server

1 Comment

IF EXISTS(

Read more...

How to mimic Microsoft Access's FIRST and LAST clauses on SQL Server 2k, 7.0 and 6.5

Wednesday, September 28, 2005

backoffice microsoft server sql sql server

6 Comments

Suppose I have the following query run against Pubs database

ISNULL + NULLIF instead of CASE WHEN

Wednesday, June 8, 2005

backoffice microsoft server sql sql server

5 Comments

Today I had to write a SQL statement quite similar to the sample below:

Roman's Weekly SQL Server Tip - Case of mistaken @@IDENTITY

Tuesday, December 21, 2004

backoffice microsoft server sql sql server

No Comments

Just read Roman's Weekly SQL Server Tip - Case of mistaken @@IDENTITY

Something I learnt about SQL table type this week

Friday, December 10, 2004

backoffice microsoft server sql sql server

No Comments

Q: When I try to compile the procedure below I get the error Must declare the variable '@TableType'. Can you guess why the following stored procedure does not compile?

CREATE PROCEDURE #SystemTablesAndColumns
AS
DECLARE @TableType TABLE (id int, name sysname)

INSERT INTO @TableType
(
 id,
 name
)
SELECT id,
       name
FROM sysobjects
WHERE name LIKE 'sys%'
ORDER BY name

SELECT @TableType.id AS TableID,
       @TableType.name AS TableName,
       syscolumns.colid AS ColumnID,
       syscolumns.name AS ColumnName
FROM @TableType
  INNER JOIN
     syscolumns ON @TableType.id = syscolumns.id
ORDER BY @TableType.name,
         syscolumns.name

RETURN

A: Because aliases are missing for the table type, as can be noted below:

CREATE PROCEDURE #SystemTablesAndColumns
AS
DECLARE @TableType TABLE (id int, name sysname)

INSERT INTO @TableType
(
 id,
 name
)
SELECT id,
       name
FROM sysobjects
WHERE name LIKE 'sys%'
ORDER BY name

SELECT TableType.id AS TableID,
       TableType.name AS TableName,
       syscolumns.colid AS ColumnID,
       syscolumns.name AS ColumnName
FROM @TableType AS TableType
  INNER JOIN
     syscolumns ON TableType.id = syscolumns.id
ORDER BY TableType.name,
         syscolumns.name

RETURN

P.S.: What the temporary stored procedure above does can be acomplished in other ways. I just used it that way to show up the problem I faced this week and how I workarounded it.

Boring SQL server message errors

Wednesday, July 7, 2004

backoffice microsoft server sql sql server

No Comments

Last week, I created some scripts to move data from an old database to a new one. Some tables consisted of many fields and the old ones were not typed, that is, all fields where char, varchar, etc.

INSERT INTO targetTable
(
 targetField1,
 targetField2,
 targetField3,
 ...
 targetFieldN
)
SELECT targetField1,
       targetField2,
       targetField3,
       ...
       targetFieldN
FROM sourceTable

When I run the script, which was quite similar to the sample below, I got errors like the ones below.

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '[varchar value here]' to a column of data type int.

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Wouldn't these messages be nicer if they state field(s) name(s) like I rewrote below?

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '[varchar value here]' to column '[column name here]' of data type int.

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated on column '[column name here]'.
The statement has been terminated.

Because of lack of column name on this message, I had spent a lot of time figuring out which field(s) were causing these errors. I would have spared a lot of time, if messages stated which field(s) I should take care of. If you know of other boring SQL server error message(s), drop a comment or trackback and let us know about.

DA, aka Data Administrators + ERWin = MESS

Tuesday, July 6, 2004

backoffice microsoft server sql sql server

No Comments

Last week, I found out DA team, using ERWin to synchronize models and databases, had messed up with some foreign keys on a database I architected for an application I am developing and the FKs were missing. Then the question "How to find out which FKs are missing on a database made of about 200 tables?" popped up.

As both parent colum name and child column name have the same name on my database and PK indexes are all named like 'XPK_%', I created the script below to help me out. Take a look at it and let your comments.

/*
DROP TABLE #constraints
*/
-- list all constraints on database
SELECT dbo.sysobjects.parent_obj AS tableid,
       'PK' AS constraintType,
        dbo.sysobjects.name AS constraintName,
        dbo.syscolumns.name AS keyColumn,
        dbo.sysindexkeys.keyno
INTO #constraints
FROM dbo.sysobjects
  INNER JOIN
     dbo.sysindexes ON dbo.sysobjects.name = dbo.sysindexes.name
  INNER JOIN
     dbo.sysindexkeys ON dbo.sysindexes.indid = dbo.sysindexkeys.indid AND
                         dbo.sysindexes.id = dbo.sysindexkeys.id
  INNER JOIN
     dbo.syscolumns ON dbo.sysindexkeys.colid = dbo.syscolumns.colid AND
                       dbo.sysobjects.parent_obj = dbo.syscolumns.id
WHERE dbo.sysobjects.xtype = 'PK'
UNION ALL
SELECT sysindexes.id AS tableid,
       'AK' AS constraintType,
       sysindexes.name AS constraintName,
       syscolumns.name AS keyColumn,
       sysindexkeys.keyno
FROM sysindexes
  INNER JOIN
     sysindexkeys ON sysindexes.id = sysindexkeys.id AND
                     sysindexes.indid = sysindexkeys.indid
  INNER JOIN
     syscolumns ON sysindexkeys.id = syscolumns.id AND
                   sysindexkeys.colid = syscolumns.colid
  INNER JOIN
     sysobjects ON syscolumns.id = sysobjects.id
WHERE (sysindexes.status & 2) <> 0 AND
      sysindexes.name NOT LIKE 'XPK_%'
UNION ALL
SELECT sysobjects.parent_obj AS tableid,
       'FK' AS constraintType,
       sysobjects.name AS constraintName,
       syscolumns.name AS keyColumn,
       sysforeignkeys.keyno
FROM sysobjects
  INNER JOIN
     sysforeignkeys ON sysobjects.id = sysforeignkeys.constid
  INNER JOIN
     syscolumns ON sysforeignkeys.fkeyid = syscolumns.id AND
                   sysforeignkeys.fkey = syscolumns.colid
  INNER JOIN
     syscolumns AS syscolumnsParent ON sysforeignkeys.rkey = syscolumnsParent.colid AND
                                       sysforeignkeys.rkeyid = syscolumnsParent.id
  INNER JOIN
     sysobjects AS sysobjectsParent ON syscolumnsParent.id = sysobjectsParent.id
WHERE sysobjects.xtype = 'F'
/*
DROP TABLE #duplicateKeyColumns
*/
-- Since both parent and child column names are the same, duplicates which are PK/AK
-- on some table might be the starting point of search
SELECT sc.name AS columnName,
       COUNT(*) AS score
INTO #duplicateKeyColumns
FROM syscolumns sc
  INNER JOIN
     sysobjects so ON sc.id = so.id
WHERE so.type = 'U' AND
      so.name NOT IN ('exclude list comes here') AND
      EXISTS(
             SELECT #constraints.keyColumn FROM #constraints
             WHERE #constraints.keyColumn = sc.name AND
                   #constraints.constraintType IN ('PK', 'AK')
            )
GROUP BY sc.name
HAVING COUNT(*) > 1
ORDER BY score DESC
/*
DROP TABLE #duplicateKeyColumnsRelatedTables
*/
SELECT sc.name AS columnName,
       so.name AS tableName,
       CASE WHEN EXISTS(
                        SELECT #constraints.keyColumn
                        FROM #constraints
                        WHERE #constraints.tableid = so.id AND
                              #constraints.keyColumn = sc.name AND
                              #constraints.constraintType IN ('PK')
                       )
            THEN '*' ELSE NULL END AS [PK],
       CASE WHEN EXISTS(
                        SELECT #constraints.keyColumn
                        FROM #constraints
                        WHERE #constraints.tableid = so.id AND
                              #constraints.keyColumn = sc.name AND
                              #constraints.constraintType IN ('AK')
                       )
            THEN '*' ELSE NULL END AS [AK],
       CASE WHEN EXISTS(
                        SELECT #constraints.keyColumn
                        FROM #constraints
                        WHERE #constraints.tableid = so.id AND
                              #constraints.keyColumn = sc.name AND
                              #constraints.constraintType IN ('FK')
                       )
            THEN '*' ELSE NULL END AS [FK]
INTO #duplicateKeyColumnsRelatedTables
FROM syscolumns sc
  INNER JOIN
     sysobjects so ON sc.id = so.id
  INNER JOIN
     #duplicateKeyColumns ON sc.name = #duplicateKeyColumns.columnName
WHERE so.type = 'U' AND
      so.name NOT IN ('exclude list comes here') AND
      (
       EXISTS(
              SELECT #constraints.keyColumn
              FROM #constraints
              WHERE #constraints.tableid = so.id AND
                    #constraints.keyColumn = sc.name AND
                    #constraints.constraintType IN ('PK', 'AK')
             ) OR
       NOT EXISTS(
                  SELECT #constraints.keyColumn
                  FROM #constraints
                  WHERE #constraints.tableid = so.id AND
                        #constraints.keyColumn = sc.name AND
                        #constraints.constraintType IN ('FK')
                 )
      )
ORDER BY #duplicateKeyColumns.score DESC,
         #duplicateKeyColumns.columnName,
         so.name
-- deletes all duplicate key columns which does not have table(s) missing any FK
DELETE #duplicateKeyColumnsRelatedTables
WHERE EXISTS(
             SELECT COUNT(*)
             FROM #duplicateKeyColumnsRelatedTables AS duplicateKeyColumnsRelatedTablesSQ
             WHERE duplicateKeyColumnsRelatedTablesSQ.columnName = #duplicateKeyColumnsRelatedTables.columnName AND
                   duplicateKeyColumnsRelatedTablesSQ.[FK] IS NULL
             HAVING COUNT(*) <= 1
            )
-- for each column listed, one of the related tables, with PK containing the column name
-- on its list, is the root of information. When you find the root (parentTable), all the
-- other tables without FKs containing the column are the the tables missing FKs on them
SELECT *
FROM #duplicateKeyColumnsRelatedTables
WHERE [FK] IS NULL

How to list nullable bit columns with no default bound with sp_bindefault

Friday, July 2, 2004

backoffice microsoft server sql sql server

No Comments

I generally create bit columns as NOT NULL DEFAULT dbo.BIT_NO, which in turn is a SQL server default which translates to 0. Today, I found out somebody on my team had created lots of bit fields on several tables as NULL with no default set. So I created the SQL statement below, to list these fields and make my life a bit easier. Comments are welcome. The fields I have to take care of is the ones with '*' on DNF column.

SELECT CASE WHEN dbo.syscomments.text IS NULL THEN '*' ELSE '' END AS [DNF], -- DNF = Default not found
       dbo.sysobjects.name AS tableName,
       dbo.syscolumns.name AS columnName,
       dbo.systypes.name AS typeName,
       dbo.syscolumns.isnullable, 
       REPLACE(dbo.syscomments.text, CHAR(13) + CHAR(10), '\n') AS [default bound with sp_bindefault]
FROM dbo.syscolumns
 INNER JOIN
     dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
 INNER JOIN
     dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype 
 LEFT OUTER JOIN
     dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id AND
                        OBJECTPROPERTY(dbo.syscolumns.cdefault, 'IsConstraint') = 0
WHERE (dbo.sysobjects.type = 'U') AND
      (dbo.systypes.name = 'bit') AND
      (dbo.syscolumns.isnullable = 0)
ORDER BY dbo.sysobjects.name,
         dbo.syscolumns.name

Problem working with named transactions inside stored procedures

Friday, June 18, 2004

backoffice microsoft server sql sql server

1 Comment

I created the following two stored procedures using named transactions inside them

/*
 **********************************************************
 */
CREATE PROCEDURE #WorkingWithNamedTransactionsOne
-- This procedure does not raise error
AS
DECLARE @ERROR bit
 
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
SET @ERROR = 1
PRINT 'NamedTransactionOne started'
 
-- SOME TSQL statement here
 
IF @@ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsOne_Finally
END
 
-- SOME TSQL statement here
 
IF @@ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsOne_Finally
END
 
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
SET @ERROR = 0
PRINT 'NamedTransactionOne committed'
 
 
WorkingWithNamedTransactionsOne_Finally:
IF @ERROR = 1
BEGIN
    PRINT 'Rolling back NamedTransactionOne'
    ROLLBACK TRANSACTION NamedTransactionOne
    SET @ERROR = 0
    PRINT 'NamedTransactionOne rolled back'
END
RETURN
GO
 
/*
 **********************************************************
 */
CREATE PROCEDURE #WorkingWithNamedTransactionsTwo
-- This procedure raises error
AS
DECLARE @ERROR bit
 
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
SET @ERROR = 1
PRINT 'NamedTransactionOne started'
 
-- SOME TSQL statement here
 
IF @@ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsTwo_Finally
END
 
SELECT 1/0 AS [This might raise an error]
 
IF @@ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsTwo_Finally
END
 
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
SET @ERROR = 0
PRINT 'NamedTransactionOne committed'
 
 
WorkingWithNamedTransactionsTwo_Finally:
IF @ERROR = 1
BEGIN
    PRINT 'Rolling back NamedTransactionOne'
    ROLLBACK TRANSACTION NamedTransactionOne
    SET @ERROR = 0
    PRINT 'NamedTransactionOne rolled back'
END
RETURN
GO

but when I execute them using the following script, an error occurred on the fourth EXECUTE statement

/*
 **********************************************************
 */
EXECUTE #WorkingWithNamedTransactionsOne
GO
 
EXECUTE #WorkingWithNamedTransactionsTwo
GO
 
BEGIN TRANSACTION
EXECUTE #WorkingWithNamedTransactionsOne
COMMIT TRANSACTION
GO
 
BEGIN TRANSACTION
EXECUTE #WorkingWithNamedTransactionsTwo --ATTENTION: Server: Msg 6401, Level 16, State 1
 - Cannot roll back NamedTransactionOne. No transaction or savepoint of that name was found.
COMMIT TRANSACTION
GO

How would you solve it? After dealing with this problem some time and doing some tests, I came out with the following new two stored procedures

/*
 **********************************************************
 */
CREATE PROCEDURE #WorkingWithNamedTransactionsAndSavepointsOne
-- This procedure does not raise error
AS
DECLARE @ERROR int
 
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne started'
 
PRINT 'Starting NamedSavepointOne'
SAVE TRANSACTION NamedSavepointOne
PRINT 'NamedSavepointOne started'
 
-- SOME TSQL statement here
 
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsAndSavepointsOne_Finally
END
 
-- SOME TSQL statement here
 
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsAndSavepointsOne_Finally
END
 
 
WorkingWithNamedTransactionsAndSavepointsOne_Finally:
IF @ERROR <> 0
BEGIN
    PRINT 'Rolling back NamedSavepointOne'
    ROLLBACK TRANSACTION NamedSavepointOne
    PRINT 'NamedSavepointOne rolled back'
END
 
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne committed'
 
RETURN
GO
 
/*
 **********************************************************
 */
CREATE PROCEDURE #WorkingWithNamedTransactionsAndSavepointsTwo
-- This procedure raises error
AS
DECLARE @ERROR int
 
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne started'
 
PRINT 'Starting NamedSavepointOne'
SAVE TRANSACTION NamedSavepointOne
PRINT 'NamedSavepointOne started'
 
-- SOME TSQL statement here
 
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsAndSavepointsTwo_Finally
END
 
SELECT 1/0 AS [This might raise an error]
 
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
    GOTO WorkingWithNamedTransactionsAndSavepointsTwo_Finally
END
 
WorkingWithNamedTransactionsAndSavepointsTwo_Finally:
IF @ERROR <> 0
BEGIN
    PRINT 'Rolling back NamedSavepointOne'
    ROLLBACK TRANSACTION NamedSavepointOne
    PRINT 'NamedSavepointOne rolled back'
END
 
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne committed'
 
RETURN
GO

Then I executed the new stored procedures again as follow, and no errors occurred

/*
 **********************************************************
 */
EXECUTE #WorkingWithNamedTransactionsAndSavepointsOne
GO
 
EXECUTE #WorkingWithNamedTransactionsAndSavepointsTwo
GO
 
BEGIN TRANSACTION
EXECUTE #WorkingWithNamedTransactionsAndSavepointsOne
COMMIT TRANSACTION
GO
 
BEGIN TRANSACTION
EXECUTE #WorkingWithNamedTransactionsAndSavepointsTwo
COMMIT TRANSACTION
GO

What do you think about it? If you came out with a different solution, let me know about.