Databasepropertyex problem

18 Feb 2006 3:32 PM

Amish Shah

Hi
when I run this query

SELECT *    FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=test;UID=sa;PWD=amish;','select name,
databasepropertyex(name,''status'') as status from
master..sysdatabases') AS a

Result is

Name      Status

master    0x4F004E004C0049004E004500
tempdb    0x4F004E004C0049004E004500
model    0x4F004E004C0049004E004500
msdb    0x4F004E004C0049004E004500
test1    0x5300550053005000450043005400
northwind    0x4F004E004C0049004E004500

If we run select databasepropertyex('dbname','status') it gives result
as character , why here it gives varbinary results in status column?

Regards
Amish Shah

Regards
Amish shah

*** Sent via Developersdex http://www.developersdex.com ***

18 Feb 2006 4:06 PM

Tibor Karaszi

Databasepropertyex returns an sql_variant, CAST it to the proper datatype (depending on what you ask
for, and I think you should be fine.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Amish Shah" <shahami***@gmail.com> wrote in message news:O65DZCKNGHA.3064@TK2MSFTNGP10.phx.gbl...
> Hi
> when I run this query
>
> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=test;UID=sa;PWD=amish;','select name,
> databasepropertyex(name,''status'') as status from
> master..sysdatabases') AS a
>
> Result is
>
> Name Status
>
> master 0x4F004E004C0049004E004500
> tempdb 0x4F004E004C0049004E004500
> model 0x4F004E004C0049004E004500
> msdb 0x4F004E004C0049004E004500
> test1 0x5300550053005000450043005400
> northwind 0x4F004E004C0049004E004500
>
> If we run select databasepropertyex('dbname','status') it gives result
> as character , why here it gives varbinary results in status column?
>
> Regards
> Amish Shah
>
> Regards
> Amish shah
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

19 Feb 2006 3:44 PM

Erland Sommarskog

Amish Shah (shahami***@gmail.com) writes:
Show quote

> when I run this query
>
> SELECT *    FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=test;UID=sa;PWD=amish;','select name,
> databasepropertyex(name,''status'') as status from
> master..sysdatabases') AS a
>
> Result is
>
> Name      Status
>
> master     0x4F004E004C0049004E004500
> tempdb     0x4F004E004C0049004E004500
> model     0x4F004E004C0049004E004500
> msdb     0x4F004E004C0049004E004500
> test1     0x5300550053005000450043005400
> northwind     0x4F004E004C0049004E004500
>
> If we run select databasepropertyex('dbname','status') it gives result
> as character , why here it gives varbinary results in status column?

Any particular reason you use MSDASQL? When I use SQLOLEDB, I get back
character data without cast.

SELECT * FROM OPENROWSET('SQLOLEDB', 'SERVER=test;UID=sa;PWD=xxxxx;',
'select name, databasepropertyex(name,''status'') as status from
master..sysdatabases') AS a

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx