AGONY: Vista Business x64 and SQL Server Management Studio

I have been fighting this one for days: my computer is currently joined to our corporate domain, but I am logged in as a local user. I've used "Manage network passwords" to add all of the relevant credentials for domain access. However, I still get the following error when logging in via Windows Authentication in SSMS:

Login failed for user ''. The user is not associated with a trusted SQL Server connection.

The error logs show that the SSPI handshake is failing - meaning that Windows is not passing along my valid domain credentials to SQL Server.

Finally, I found a workaround that deals with what I'm experiencing by making use of the runas command. It's hacky, and this is definitely due to Vista's "wonderful feature", but it gets the job done. Thanks to Jason Follas for the following tip:

http://www.jasonfollas.com/wiki/Default.aspx?Page=SQL%20Management%20Studio%20on%20Vista&AspxAutoDetectCookieSupport=1

For the record, we should not have to do this.

T-SQL IP octet split

Found a neat piece of T-SQL that can be used to separate the octets of an IP address:

Declare @IP varchar(20)
Select @IP = '239.147.8.165'
select @IP AS IP,
cast(substring(@IP, 1, charindex('.', @IP) - 1) as int) AS Octet1,
cast(substring(@IP, charindex('.', @IP) + 1,
charindex('.', @IP, charindex('.', @IP) + 1) -
charindex('.', @IP) - 1) as int) as Octet2,
cast(reverse(substring(reverse(@IP), charindex('.', reverse(@IP)) + 1,
charindex('.', reverse(@IP), charindex('.', reverse(@IP)) + 1) -
charindex('.', reverse(@IP)) - 1)) as int) AS Octet3,
cast(reverse(substring(reverse(@IP), 1, charindex('.', reverse(@IP)) - 1)) as int) as Octet4

The source for this is http://www.umachandar.com/technical/SQL6x70Scripts/Main.htm.

How to determine owners of Oracle tables

I recently needed to find out the schema names of certain Oracle tables that belonged to schemas other than the connecting user. This is because the four-part queries in SQL Server require the schema/owner name of the tables referenced. This query (run in SQL*Plus) lists the owners and names of all tables that can be seen by the connecting user:

SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

A full reference for the ALL_TABLES view can be found here.

Casting to NUMERIC/DECIMAL with E notation

Found this neat tidbit on some other guy's blog. Basically, NUMERICs/DECIMALs are exact numeric types and cannot contain the E notation in their representations. FLOATs are approximate numeric types and can contain the E notation. FLOATs can also be CASTed to NUMERIC/DECIMAL with no problems (other than a loss of precision).

For example, take the VARCHAR value X = '10000000E-2'. To cast it to NUMERIC/DECIMAL, use the following:

SELECT CAST(CAST(X AS FLOAT) AS NUMERIC(15, 4))...

You would obviously lose some precision if you cast with a NUMERIC field of less precision than what is represented by the contents of the VARCHAR value.

Filling in gaps in incrementing INT column in SQL Server

Suppose you have a unique integer column i in a table T. i may contain gaps. i contains m rows.

Suppose you wish to insert n values into i starting at some value x0 and incrementing. Let x equal the first integer greater than or equal to x0 not found in i.

In general, this can be solved by performing the following:

SELECT TOP n * 2 IDENTITY(INT, x, 1) AS j
INTO #temp
FROM T T1
CROSS JOIN T T2
GO

INSERT INTO T (i)
SELECT TOP n j
FROM #temp
LEFT OUTER JOIN T
ON T.i = #temp.j
WHERE T.i IS NULL
GO

There should now be m + n rows in T.

sp_msforeachdb

To iterate a command over all databases on a server, the undocumented sp_msforeachdb stored procedure fits the bill nicely. For example, I was given the task to set all databases on a particular server to a bulk-logged recovery model; this was easily accomplished with a single command:

master..sp_msforeachdb 'exec sp_dboption ''?'', ''select into/bulkcopy'', ''on'''

Note that this procedure executes dynamic SQL, replacing the ? with the name of the database as it iterates.

xtype values in sysobjects

This is a list of all of the possible values for the xtype column in the sysobjects table of a SQL Server database:

• C - CHECK constraint
• D - Default or DEFAULT constraint
• F - FOREIGN KEY constraint
• L - Log
• P - Stored procedure
• PK - PRIMARY KEY constraint
• RF - Replication filter stored procedure
• S - System table
• TR - Trigger
• U - User table
• UQ - UNIQUE constraint
• V - View
• X - Extended stored procedure

SQL Server: DBCC CHECKIDENT

This neat little SQL Server statement allows you to view/edit properties of the identity value of a table.

Syntax: DBCC CHECKIDENT ('table_name' [ , { NORESEED { RESEED [ , new_reseed_value ] } } ])[ WITH NO_INFOMSGS ]

For example, if I want to reset the identity value of the table FOOBAR to 55, I run the following SQL statement:

DBCC CHECKIDENT('FOOBAR', RESEED, 55).

If FOOBAR already has rows, the next row's identity field will take on the value 56. If FOOBAR is an empty table, its first row's identity field will take on the value 55.

Neat-o.