Random outpourings of a software developer
Identify the port that SQL Server is running on:
DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = ‘SOFTWAREMICROSOFTMSSQLSERVERMSSQLSERVERSUPERSOCKETNETLIBTCP’,
@value_name = ‘TcpPort’,
@value = @tcp_port OUTPUT
select @tcp_port
declare @text varchar(1000)
set @text = ‘<a firstname=”Joe” surname=”Blogs” >42</a>’
DECLARE @idoc int
select @text
declare @name varchar(10)
declare @name2 varchar(10)
— Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @text
— Execute a SELECT statement using OPENXML rowset provider.
SELECT @name=surname, @name2 = [firstname]
FROM OPENXML (@idoc, ‘/a’,1)
WITH ([firstname] varchar(10),
surname varchar(20))
select @name, @name2
SELECT age=[text]
FROM OPENXML (@idoc, ‘/a/text()’,2)
EXEC sp_xml_removedocument @idoc
The following is an extension of the previous post.
This is useful when dealing with sql server 2005 and custom schema’s.
(That is the prefix before database objects, not the structure of the database).
select tableName=SO.name, schemaName = SS.Name
from sysobjects SO
join sys.schemas SS ON SO.uid = SS.[schema_id]
where type = ‘U’
order by SO.name
This lists the tables:
select tableName=name from sysobjects where type = ‘U’ order by name
This lists the fields per table:
select tableName = so.name,
colName = sc.name,
colLength = sc.length,
colOrder = sc.colid,
dataType = st.name
from sysobjects so
inner join syscolumns sc on sc.id = so.id
inner join systypes st on st.usertype = sc.usertype
where so.type = ‘U’
order by so.name, sc.colid
Between the two you have the tools needed to compare two database schemas.
The only restriction is the database access.
This does not compare contents – that is a different problem.
— The following commands take database ‘foo’ offline and then on again:
use master
GO
ALTER DATABASE foo SET OFFLINE
GO
ALTER DATABASE foo SET ONLINE
GO
SQL Server 2000 has even more error handling problems than I blogged about before.
It would appear that error handling has not been thought through at all.
Take the RAISERROR command as an example. This appears to be a very elegant means of raising errors to the user. You only need to use sp_addmessage to register a new message. The problem is that the messages are server global.
This means that if you server is ever to host a third party application you cannot safely define your own messages for fear of a third party app stamping all over them. This should be scoped at a database level, providing at least some control.
The more I look at sql server error handling the more depressed I get.
I have been working with SQL Server 2000 for a while now. I’m no DBA…
Today I encountered a problem that the investigation of which led me to a greater understanding of SQL Server.
We have a reasonably large database that is recreated everyday as a snapshot of a larger system. The transaction log was getting rather large. Given that the database is set up to use the Simple recovery model and we were using no explicit transactions why is it getting so large?
Why can’t we switch off the transaction log?
It turns out that it is almost impossible to write directly to the database – bulk inserts only. A majority of data access goes directly into the transaction log which is then applied to the database. In order to clear down the transaction log you need to backup the transaction log or pretend that you have.
This can be performed with the command BACKUP LOG {database_name} NO_LOG.
Databases can be set up with the Auto-Shrink option (but it is not a good idea to do so). This will allow the transaction log to shrink itself. The problem is that you have no control over when this happens and it will kill performance when it occours – it will pick the worst possible time to do it. The best option is to pick a known quiet time and backup the log yourself.
For more details see “Shrinking the Transaction Log” in Books Online.
In addition we found that all data modification commands are wrapped in an automatic transaction. This meant that our transaction log included a 21 million row insert as a single transaction…
The whole buisness of error handling in sql server 2000 stored procedures is depressing.
For example if a query has an incorrect table structure then there is no way of handling that from within the sp.
It is rather inconsistent, the best that can be acheived is a compromise. You have to check @@ERROR after every step.
I hope that SQL Server 2005 will be better once I am allowed to use it.
Recently I found that @@SERVERNAME does not actually return the name of the server that the machine is running.
It returns the name that was last set using sp_addserver ‘NAME’, local
This makes a difference if you are creating an image of a machine as a master copy.
All of the copied machines will think that they are the master machine.
The following script should detect and repair incorrect server names:
SET NOCOUNT ON
DECLARE @OLDNAME VARCHAR(200)
DECLARE @NEWNAME VARCHAR(200)
SELECT @OLDNAME = @@SERVERNAME
SELECT @NEWNAME = CAST( SERVERPROPERTY(‘ServerName’) AS VARCHAR(200))
select @OLDNAME, @NEWNAME
IF @OLDNAME <> @NEWNAME
BEGIN
PRINT ‘Server name differs’
IF @NEWNAME IS NOT NULL
BEGIN
IF @OLDNAME IS NOT NULL
BEGIN
PRINT ‘Dropping server ‘ + @OLDNAME
EXEC sp_dropserver @OLDNAME
PRINT ‘Dropped server ‘ + @OLDNAME
END
PRINT ‘Adding server ‘ + @NEWNAME
EXEC sp_addserver @NEWNAME, ‘local’
PRINT ‘Added server ‘ + @NEWNAME
PRINT ‘Now stop and restart SQL Server’
END
END
Developer Rants 