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
Showing posts with label databasepropertyex. Show all posts
Showing posts with label databasepropertyex. Show all posts
Friday, February 24, 2012
Databasepropertyex problem
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 wrote:
> 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
>
Good question, but you can cast is back:
SELECT * FROM OPENROWSET(
'MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=my_pas
s;','select name,
CAST(databasepropertyex(name,''status'')
as NVARCHAR(100)) as status
from master..sysdatabases') AS a
David Gugick - SQL Server MVP
Quest Software
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 wrote:
> 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
>
Good question, but you can cast is back:
SELECT * FROM OPENROWSET(
'MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=my_pas
s;','select name,
CAST(databasepropertyex(name,''status'')
as NVARCHAR(100)) as status
from master..sysdatabases') AS a
David Gugick - SQL Server MVP
Quest Software
DATABASEPROPERTYEX linked server
It is possible to grap a database property from a database on a linked server? Like this?
Select DATABASEPROPERTYEX('servername.databasename','Reco very')
Thanks!
TommyI Tried a variety of ways, and it doesn't look like it...
Select DATABASEPROPERTYEX('QA.dbo.Northwind','Recovery')|||Same results, here, I just get null. I'm going to try to look at the sysdatabases.status field. I just need to get the recovery model, and see if the database is online or not.
Thanks
Tommy|||Why not create a sproc on each (master) db and do a remotr sproc call? That should work...|||..know what?
If you could do that then you would easily have the ability to know...they shouldn't change strategies at all...
If you're the dba you should know...
You should have an inventory of everything...are these out of your control, and what are you trying to accomplish? (inventory automation?)|||Brett:
Thanks for your help. I'm an ISP, and my users have the ability to set their db's the simple. I run my own log shipping scripts. When I backup the logs on the source, it's easy to check the status, and NOT back up logs if it's set to simple. The restorelog runs on the failover server, and I have to tell my script to not try to restore if the original db is set to simple. Other wise, my job shows as failed, even it it fails on one database that is set to simple.
Tommy|||Are these physical servers or instances of sql 2k?
In either case you have to build both, just store a sproc in master, and open a cursor to find the dbs with
select * from sysdatabases
Select DATABASEPROPERTYEX('servername.databasename','Reco very')
Thanks!
TommyI Tried a variety of ways, and it doesn't look like it...
Select DATABASEPROPERTYEX('QA.dbo.Northwind','Recovery')|||Same results, here, I just get null. I'm going to try to look at the sysdatabases.status field. I just need to get the recovery model, and see if the database is online or not.
Thanks
Tommy|||Why not create a sproc on each (master) db and do a remotr sproc call? That should work...|||..know what?
If you could do that then you would easily have the ability to know...they shouldn't change strategies at all...
If you're the dba you should know...
You should have an inventory of everything...are these out of your control, and what are you trying to accomplish? (inventory automation?)|||Brett:
Thanks for your help. I'm an ISP, and my users have the ability to set their db's the simple. I run my own log shipping scripts. When I backup the logs on the source, it's easy to check the status, and NOT back up logs if it's set to simple. The restorelog runs on the failover server, and I have to tell my script to not try to restore if the original db is set to simple. Other wise, my job shows as failed, even it it fails on one database that is set to simple.
Tommy|||Are these physical servers or instances of sql 2k?
In either case you have to build both, just store a sproc in master, and open a cursor to find the dbs with
select * from sysdatabases
DATABASEPROPERTYEX is not a recognized function name?
Hi, I just made a report with the wizard when I swith to preview I got that
message and I cant see my report.
Thanks in advance
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/You are accessing a SQL Server 7.0 or older, correct? Just install SP1 of RS
2000 on report server and report designer machines and it will work.
The workaround for RS 2000 _without_ SP1 is as follows: Go to the "Data
Options" tab of the "Dataset" dialog in report designer. On the Data Options
tab you will see that all settings contain "Auto" (and report server would
therefore try to auto-detect the collation settings from the database
server). Replace the Auto-settings with the following settings (e.g. if your
SQL 7.0 database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uCrElYQGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Hi, I just made a report with the wizard when I swith to preview I got
> that
> message and I cant see my report.
> Thanks in advance
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>
message and I cant see my report.
Thanks in advance
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/You are accessing a SQL Server 7.0 or older, correct? Just install SP1 of RS
2000 on report server and report designer machines and it will work.
The workaround for RS 2000 _without_ SP1 is as follows: Go to the "Data
Options" tab of the "Dataset" dialog in report designer. On the Data Options
tab you will see that all settings contain "Auto" (and report server would
therefore try to auto-detect the collation settings from the database
server). Replace the Auto-settings with the following settings (e.g. if your
SQL 7.0 database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uCrElYQGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Hi, I just made a report with the wizard when I swith to preview I got
> that
> message and I cant see my report.
> Thanks in advance
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>
'DATABASEPROPERTYEX' is not a recognized function name
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
You could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
You could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
'DATABASEPROPERTYEX' is not a recognized function name
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
'DATABASEPROPERTYEX' is not a recognized function name
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
DATABASEPROPERTYEX Error but not using this function...
Hi Everyone,
I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0 database
as the datasource. My SQL statement isn't anything crazy, just a Left Outer
Join between two tables (see below), but when I go into Preview mode on the
report I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'MyDatasource'.
'DATABASEPROPERTYEX' is not a recognized function name.
I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
suggestions?
Datasource: MS SQL 7.0
Report created on VS.Net 2003
To be published on MS SQL 2005 Reporting Server
And here is my SQL statement:
SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
PAYHIST.trans
FROM MASTER LEFT OUTER JOIN
PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
ORDER BY MASTER.asset_no, PAYHIST.post_date
I used a wizard to create this report, so does VS.Net 2003 do something
wierd in the background that MS SQL 7.0 might not like? Just curious ...
Thanks --
AlexHi. Mistake in my last post, I initially said I was using Visual Studio.Net
2005, but it's actually 2003. I corrected this at the bottom of the
message, but forgot to at the top. So to correct, this is being done under
VS.Net 2003 as opposed to 2005.
Sorry 'bout that .. Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
> database as the datasource. My SQL statement isn't anything crazy, just a
> Left Outer Join between two tables (see below), but when I go into Preview
> mode on the report I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'MyDatasource'.
> 'DATABASEPROPERTYEX' is not a recognized function name.
> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
> suggestions?
> Datasource: MS SQL 7.0
> Report created on VS.Net 2003
> To be published on MS SQL 2005 Reporting Server
> And here is my SQL statement:
> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
> PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
> PAYHIST.trans
> FROM MASTER LEFT OUTER JOIN
> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
> ORDER BY MASTER.asset_no, PAYHIST.post_date
> I used a wizard to create this report, so does VS.Net 2003 do something
> wierd in the background that MS SQL 7.0 might not like? Just curious ...
> Thanks --
> Alex
>
>|||Solution Found...
After searching more in Google Groups I found an older post with the fix.
Here it is for anyone who might run across this issue in the future:
You are accessing a SQL Server 7.0 or older, correct? Just install SP1 of RS
2000 on report server and report designer machines and it will work.
The workaround for RS 2000 _without_ SP1 is as follows: Go to the "Data
Options" tab of the "Dataset" dialog in report designer. On the Data Options
tab you will see that all settings contain "Auto" (and report server would
therefore try to auto-detect the collation settings from the database
server). Replace the Auto-settings with the following settings (e.g. if your
SQL 7.0 database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true
Take care -- Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23$GDP9R2HHA.140@.TK2MSFTNGP02.phx.gbl...
> Hi. Mistake in my last post, I initially said I was using Visual
> Studio.Net 2005, but it's actually 2003. I corrected this at the bottom
> of the message, but forgot to at the top. So to correct, this is being
> done under VS.Net 2003 as opposed to 2005.
> Sorry 'bout that .. Alex
> "Alex" <samalex@.gmail.com> wrote in message
> news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
>> Hi Everyone,
>> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
>> database as the datasource. My SQL statement isn't anything crazy, just
>> a Left Outer Join between two tables (see below), but when I go into
>> Preview mode on the report I get the following error:
>> An error has occurred during report processing.
>> Query execution failed for data set 'MyDatasource'.
>> 'DATABASEPROPERTYEX' is not a recognized function name.
>> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
>> suggestions?
>> Datasource: MS SQL 7.0
>> Report created on VS.Net 2003
>> To be published on MS SQL 2005 Reporting Server
>> And here is my SQL statement:
>> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
>> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
>> PAYHIST.interest, PAYHIST.late_fee,
>> PAYHIST.suspense, PAYHIST.trans
>> FROM MASTER LEFT OUTER JOIN
>> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
>> ORDER BY MASTER.asset_no, PAYHIST.post_date
>> I used a wizard to create this report, so does VS.Net 2003 do something
>> wierd in the background that MS SQL 7.0 might not like? Just curious ...
>> Thanks --
>> Alex
>>
>
I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0 database
as the datasource. My SQL statement isn't anything crazy, just a Left Outer
Join between two tables (see below), but when I go into Preview mode on the
report I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'MyDatasource'.
'DATABASEPROPERTYEX' is not a recognized function name.
I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
suggestions?
Datasource: MS SQL 7.0
Report created on VS.Net 2003
To be published on MS SQL 2005 Reporting Server
And here is my SQL statement:
SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
PAYHIST.trans
FROM MASTER LEFT OUTER JOIN
PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
ORDER BY MASTER.asset_no, PAYHIST.post_date
I used a wizard to create this report, so does VS.Net 2003 do something
wierd in the background that MS SQL 7.0 might not like? Just curious ...
Thanks --
AlexHi. Mistake in my last post, I initially said I was using Visual Studio.Net
2005, but it's actually 2003. I corrected this at the bottom of the
message, but forgot to at the top. So to correct, this is being done under
VS.Net 2003 as opposed to 2005.
Sorry 'bout that .. Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
> database as the datasource. My SQL statement isn't anything crazy, just a
> Left Outer Join between two tables (see below), but when I go into Preview
> mode on the report I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'MyDatasource'.
> 'DATABASEPROPERTYEX' is not a recognized function name.
> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
> suggestions?
> Datasource: MS SQL 7.0
> Report created on VS.Net 2003
> To be published on MS SQL 2005 Reporting Server
> And here is my SQL statement:
> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
> PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
> PAYHIST.trans
> FROM MASTER LEFT OUTER JOIN
> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
> ORDER BY MASTER.asset_no, PAYHIST.post_date
> I used a wizard to create this report, so does VS.Net 2003 do something
> wierd in the background that MS SQL 7.0 might not like? Just curious ...
> Thanks --
> Alex
>
>|||Solution Found...
After searching more in Google Groups I found an older post with the fix.
Here it is for anyone who might run across this issue in the future:
You are accessing a SQL Server 7.0 or older, correct? Just install SP1 of RS
2000 on report server and report designer machines and it will work.
The workaround for RS 2000 _without_ SP1 is as follows: Go to the "Data
Options" tab of the "Dataset" dialog in report designer. On the Data Options
tab you will see that all settings contain "Auto" (and report server would
therefore try to auto-detect the collation settings from the database
server). Replace the Auto-settings with the following settings (e.g. if your
SQL 7.0 database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true
Take care -- Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23$GDP9R2HHA.140@.TK2MSFTNGP02.phx.gbl...
> Hi. Mistake in my last post, I initially said I was using Visual
> Studio.Net 2005, but it's actually 2003. I corrected this at the bottom
> of the message, but forgot to at the top. So to correct, this is being
> done under VS.Net 2003 as opposed to 2005.
> Sorry 'bout that .. Alex
> "Alex" <samalex@.gmail.com> wrote in message
> news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
>> Hi Everyone,
>> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
>> database as the datasource. My SQL statement isn't anything crazy, just
>> a Left Outer Join between two tables (see below), but when I go into
>> Preview mode on the report I get the following error:
>> An error has occurred during report processing.
>> Query execution failed for data set 'MyDatasource'.
>> 'DATABASEPROPERTYEX' is not a recognized function name.
>> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
>> suggestions?
>> Datasource: MS SQL 7.0
>> Report created on VS.Net 2003
>> To be published on MS SQL 2005 Reporting Server
>> And here is my SQL statement:
>> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
>> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
>> PAYHIST.interest, PAYHIST.late_fee,
>> PAYHIST.suspense, PAYHIST.trans
>> FROM MASTER LEFT OUTER JOIN
>> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
>> ORDER BY MASTER.asset_no, PAYHIST.post_date
>> I used a wizard to create this report, so does VS.Net 2003 do something
>> wierd in the background that MS SQL 7.0 might not like? Just curious ...
>> Thanks --
>> Alex
>>
>
DATABASEPROPERTYEX code?
where does DATABASEPROPERTYEX() get its information from? I cant seem to use
sp_helptext to see the code. error "DATABASEPROPERTYEX does not exist in thi
s
database". I checked every db on the server, where does it exist at?
I am tryign to wrte code that checks for recovery level without using the
DATABASEPROPERTYEX function.
thanks in advance!DATABASEPROPERTYEX() is a system function. It's coded as part of the SQL
Server engine, and the code of it not publicly available. What are you
trying to do that prevents you from using DATABASEPROPERTYEX()?
Jacco Schalkwijk
SQL Server MVP
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:3D552067-10AD-4023-BAD7-9145BC7A0F02@.microsoft.com...
> where does DATABASEPROPERTYEX() get its information from? I cant seem to
> use
> sp_helptext to see the code. error "DATABASEPROPERTYEX does not exist in
> this
> database". I checked every db on the server, where does it exist at?
> I am tryign to wrte code that checks for recovery level without using the
> DATABASEPROPERTYEX function.
> thanks in advance!|||Many of the properties returned by the DATABASEPROPERTYEX function can
be found in the master.dbo.sysdatabases table, in the status and
status2 columns. The recovery model is dependent on the 'select
into/bulkcopy' and 'trunc. log on chkpt.' database options. However,
it's recommended that you use the DATABASEPROPERTYEX function (instead
of reading from system tables).
Razvan
sp_helptext to see the code. error "DATABASEPROPERTYEX does not exist in thi
s
database". I checked every db on the server, where does it exist at?
I am tryign to wrte code that checks for recovery level without using the
DATABASEPROPERTYEX function.
thanks in advance!DATABASEPROPERTYEX() is a system function. It's coded as part of the SQL
Server engine, and the code of it not publicly available. What are you
trying to do that prevents you from using DATABASEPROPERTYEX()?
Jacco Schalkwijk
SQL Server MVP
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:3D552067-10AD-4023-BAD7-9145BC7A0F02@.microsoft.com...
> where does DATABASEPROPERTYEX() get its information from? I cant seem to
> use
> sp_helptext to see the code. error "DATABASEPROPERTYEX does not exist in
> this
> database". I checked every db on the server, where does it exist at?
> I am tryign to wrte code that checks for recovery level without using the
> DATABASEPROPERTYEX function.
> thanks in advance!|||Many of the properties returned by the DATABASEPROPERTYEX function can
be found in the master.dbo.sysdatabases table, in the status and
status2 columns. The recovery model is dependent on the 'select
into/bulkcopy' and 'trunc. log on chkpt.' database options. However,
it's recommended that you use the DATABASEPROPERTYEX function (instead
of reading from system tables).
Razvan
Subscribe to:
Posts (Atom)