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.examnotes.net ***Databasepropertyex returns an sql_variant, CAST it to the proper datatype (d
epending 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/
"Amish Shah" <shahamishm@.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.examnotes.net ***|||Amish Shah (shahamishm@.gmail.com) writes:
> 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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
