x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

April 2005 Blog Posts

Object Dependency Hierarchal Relationships

This always seems to pop as well.  To determine what objects are dependant on what you can use sp_depends.  Unfortunately that's for 1 level of relationships in either direction (to the parent or the child).  Also, and I don't know why they write them this way, but trying to automate some things using system stored procedures are a pain.  Especially when they return multiple result sets, or when the result have different number of columns.  At the bottom of this post is a rewrite of sp_depends that the script uses.  This script then builds a table of all the relationships...

posted @ Thursday, April 28, 2005 3:06 PM | Feedback (2) | Filed Under [ SQL Server ]

TRUNCATE TABLE in DB2

Well there isn't one.  If you read this post and your platform is SQL Server or Oracle, I hope you feel very fortunate that you have TRUNCATE TABLE <tablename>.  It appears that in version 8, there is a callable procedure that is supplied by IBM, but it cannot be executed as a standard SQL command.  It probably not ANSI anyway.  In any event, the way of the world to do this in the past, to not incur heavy logging of a DELETE, was to use the DB2 Load utility and do a LOAD REPLACE.  This has the affect of marking...

posted @ Thursday, April 28, 2005 11:54 AM | Feedback (6) | Filed Under [ DB2 ]

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

bcp fails to import data near reserved word. [Doooh]Alrighty then.  Thanks Tara (again) for pointing out the obvious.  The resolution to this problem is the -q option.  As BOL states, it sets quoted identifiers on in the context of the bcp thread.  So that solves that problem, and I'm sure it's one that I won't forget.  It's curious why there isn't a problem with the bcp out though.[/Doooh] BOL -q Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL Server. Use this option to specify a database, owner, table, or view name that contains a...

posted @ Monday, April 25, 2005 9:04 AM | Feedback (10) | Filed Under [ SQL Server ]

CREATE TYPE2 UNIQUE WHERE NULL INDEX

This pops up every so often, as it does Here.  The post title is the DB2 syntax for achieving a unique index that allows for nulls.  I always felt that since Nulls are not equal to anything, not even themselves, then how could you get a dup key violation.   To be fair, even in DB2 you can not have a primary key that allows more than 1 Null.  But you can create a unique index in lieu of the the PK that will allow nulls.  This allows the ability to build non-identifying relationships, something I believe is lacking in SQL Server. ...

posted @ Wednesday, April 20, 2005 1:23 PM | Feedback (3) | Filed Under [ SQL Server ]

DTS too hard to export data (How to export a table with a header using bcp)

I swear that's what was posted.  Mostly they want a header row in their data and they didn't want to use DTS... bcp out with column names post OK, here you go..probably need to deal with more datatype than I have accounted for... USE NorthwindGO SET NOCOUNT ON DECLARE @sql1 varchar(8000), @sql2 varchar(8000), @TABLE_NAME sysname, @TABLE_SCHEMA sysname DECLARE myCursor99 CURSOR   FOR  SELECT TABLE_SCHEMA, TABLE_NAME    FROM INFORMATION_SCHEMA.Tables   WHERE TABLE_TYPE = 'BASE TABLE'    AND TABLE_NAME LIKE 'O%' OPEN myCursor99FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA, @TABLE_NAME WHILE @@FETCH_STATUS = 0  BEGIN  SELECT @sql2 = ' UNION ALL SELECT ', @sql1 = null  SELECT @sql1 = COALESCE(@sql1 + ', '+''''+'"'+''''+'+'+'''','') +...

posted @ Wednesday, April 13, 2005 3:07 PM | Feedback (3) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET