using sp_helpdb and getting error

[ph3ng]

Hi

I was executing sp_help on a server (ran against master database) and
i get the following message:

[Microsoft][ODBC SQL Server Driver]Associated statement is not
prepared(HY007)
Anyone got any idea what is wrong?

Thank you in advance

[Erland Sommarskog]

ph3ng (jt****@gmail.com) writes:

I was executing sp_help on a server (ran against master database) and
i get the following message:

[Microsoft][ODBC SQL Server Driver]Associated statement is not
prepared(HY007)

Where did you run it? Which version of SQL Server? And what did you
run? sp_help or sp_helpdb? Any parameters?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ph3ng]

On Sep 12, 7:37*am, Erland Sommarskog <[e-mail]:

ph3ng ([e-mail]) writes:

I was executing sp_help on a server (ran against master database) and
i get the following message:

[Microsoft][ODBC SQL Server Driver]Associated statement is not
prepared(HY007)

Where did you run it? Which version of SQL Server? And what did you
run? sp_help or sp_helpdb? Any parameters?

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

My bad. Hadn't realised I've accidentally given some inaccurate info.

I've used sp_helpdb. I am using MSSQL 2000. No parameters - just
sp_helpdb.

I've ran it in via DBArtisan's ISQL window against the master database
of a MSSQL server.

Hope this provides a clearer picture. Thank you and my apologies for
that.

Justin

[ph3ng]

On Sep 12, 9:30*am, ph3ng <[e-mail]:

On Sep 12, 7:37*am, Erland Sommarskog <[e-mail]:

ph3ng ([e-mail]) writes:

I was executing sp_help on a server (ran against master database) and
i get the following message:

[Microsoft][ODBC SQL Server Driver]Associated statement is not
prepared(HY007)

Where did you run it? Which version of SQL Server? And what did you
run? sp_help or sp_helpdb? Any parameters?

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

My bad. Hadn't realised I've accidentally given some inaccurate info.

I've used sp_helpdb. I am using MSSQL 2000. No parameters - just
sp_helpdb.

I've ran it in via DBArtisan's ISQL window against the master database
of a MSSQL server.

Hope this provides a clearer picture. Thank you and my apologies for
that.

Justin

Interestingly, I've just tried to run sp_helpdb via SQL Query Analyzer
on the same MSSQL server (against the master db) and I get the
following error:

================================================== ==================
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
================================================== ==================

[Erland Sommarskog]

ph3ng (jt****@gmail.com) writes:

>I've ran it in via DBArtisan's ISQL window against the master database
of a MSSQL server.
...

Interestingly, I've just tried to run sp_helpdb via SQL Query Analyzer
on the same MSSQL server (against the master db) and I get the
following error:

================================================= ===================
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
================================================= ===================

Apparently DBArtisan is not very good at handling the error messages from
SQL Server. Not that it's unique. OdbcClient in .Net has similar problems.
Then again, I guess DBArtisan is able to connect to other database products
as well, so it has a more difficult task than Query Analyzer.

As for the error message, run this SELECT:

select name, sid, suser_sname(sid), convert(nvarchar(11), crdate),
dbid, cmptlevel from master.dbo.sysdatabases

Which of the columns are NULL? I would guess it's suser_sname(sid).
This would indicated that the database in question is owned by a
login that no longer exists.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53

Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.

I agree with Erland that the likly cause of this error is that the database
owner login no longer exists. You can correct with sp_changedbowner. For
example:

USE MyDatabase
EXEC sp_changedbowner 'sa'

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"ph3ng" <jt****@gmail.comwrote in message
news:8e**********************************@p31g2000 prf.googlegroups.com...
On Sep 12, 9:30 am, ph3ng <[e-mail]:

On Sep 12, 7:37 am, Erland Sommarskog <[e-mail]:

ph3ng ([e-mail]) writes:

I was executing sp_help on a server (ran against master database) and
i get the following message:

[Microsoft][ODBC SQL Server Driver]Associated statement is not
prepared(HY007)

Where did you run it? Which version of SQL Server? And what did you
run? sp_help or sp_helpdb? Any parameters?

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL
2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

My bad. Hadn't realised I've accidentally given some inaccurate info.

I've used sp_helpdb. I am using MSSQL 2000. No parameters - just
sp_helpdb.

I've ran it in via DBArtisan's ISQL window against the master database
of a MSSQL server.

Hope this provides a clearer picture. Thank you and my apologies for
that.

Justin

Interestingly, I've just tried to run sp_helpdb via SQL Query Analyzer
on the same MSSQL server (against the master db) and I get the
following error:

================================================== ==================
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
================================================== ==================