Systems Engineering and RDBMS

Archive for July 10th, 2008

SP_HELPDB and the Insert Error

Posted by decipherinfosys on July 10, 2008

A reader asked yesterday how to resovle this error that he was getting when trying to run sp_helpdb on one of the instances:

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column ‘owner’, table ‘tempdb.dbo.#spdbdesc_
The statement has been terminated.

As you might already know, sp_helpdb returns information about all of your databases on a given instance and can be very useful for different reasons – one can be to monitor the size of the databases – we had blogged that before here. For the error above, that the end user received – the issue resolution is pretty straight forward. As the error message states, the database needs to have an owner else it will try to insert a NULL value into the “Owner” column of the temporary table and it will fail to provide the output when the system stored procedure sp_helpdb gets executed.

And the resolution is pretty straight forward as well – find the databases which do not have an owner assigned and assign an owner to them. We can use sysdatabases or sys.databases (in SQL Server 2005 and 2008):

For SQL Server 2000:

select name as database_name, SUSER_SNAME(sid) as owner_name, crdate as create_date_time
from master.dbo.sysdatabases

For SQL Server 2005:

select name as database_name, SUSER_SNAME(owner_sid) as owner_name, create_date as create_date_time
from master.sys.databases

And then see which databases do not have an owner assigned to them. The ones that do not have an owner assigned to them can be assigned an owner using this command:

Use <put the database name here>

go

exec sp_changedbowner ‘put the owner name here’

Once that is done, execution of sp_helpdb will work perfectly fine.

Posted in SQL Server | Leave a Comment »

SQL Server 2008 due in August?

Posted by decipherinfosys on July 10, 2008

As per this eWeek article, the next version is pretty close to the RTM. This information was released at the Microsoft Worldwide Partner Conference so am pretty sure that it is accurate. The nightly scrapper script also pointed out a post was made at the Data Platform Technologies blog as well in this regard. We have been working with SQL Server 2008 since it’s CTP1 stages and are very impressed with the new feature sets. You can search for SQL Server 2008 on this blog and get the articles and the whitepapers on it.

Posted in SQL Server | Leave a Comment »

Computed Column based on XML Data Type

Posted by decipherinfosys on July 10, 2008

We have discussed computed columns quite a bit in our posts at this blog. You can search for “computed column” and get to all those posts – the basic ones are here and here. A few days ago, a reader had asked whether we can create a computed column based on an XML Data Type column and if so, how can we go ahead and do it. In this post, we will present such a solution along with a deterministic function that we will use to extract out the value for making the computed column.

We will use the same example for the XML that we had used in our post here to demonstrate the nodes() method. The function is pretty simple and is based on the same XML as was demonstrated in the post above with some changes:

create function dbo.fn_xml_computed
(
@x xml,
@va_lkup varchar(10)
)
returns bigint
as
begin
declare @shid bigint
set @shid = (select
T.VA.value(‘Key[1]’, ‘bigint’) as Share_Holder_ID
from @x.nodes(‘//Details’) as T(VA)
where T.VA.exist(‘VoteAction/text()[.=sql:variable(“@va_lkup”)]’) = 1)
return @shid
end
go

Here, we will be taking in two parameters – one would be the actual vote XML and the second one will be the vote action performed and we will use the function to extract out the information on the Shareholder_ID value. So, our table definition would look something like this:

create table dbo.testcase
(
vote_xml xml,
vote_action varchar(10),
shareholder_id AS dbo.fn_xml_computed(vote_xml, vote_action)
)

And now let’s do the insert into the table now:

insert into dbo.testcase (vote_xml, vote_action) values
(
'<VoteAgent>
                        <InstructAck>
                                          <MtgNtfcnId>444</MtgNtfcnId>
                                          <Details>
                                                <Key>0987654321</Key>
                                                <Shares>100</Shares>
                                                <VoteAction>FOR</VoteAction>
                                          </Details>
                        </InstructAck>
         </VoteAgent>',
'FOR'
);

Let’s check the results:

select * from dbo.testcase

vote_xml    vote_action shareholder_id
----------- ----------- --------------------
<....>      FOR         987654321

As you can see from above, the shareholder_id value got extracted and populated in the column automatically. So, the computed column concept applies to the XML data type columns as well.

Posted in SQL Server | Leave a Comment »