Dataset Dynamic Query

I am trying to implement the setting of a parameter to the default value of
'-All-' using the IIF function in the WHERE clause.
I received a processing error then backed off and checked a basic 'SELECT
select list FROM tablename WHERE columnname = value' query. It ran in
Preview just fine.
Then I wrapped the query in ' = " query w/o changes" ' When this runs in
Preview I get the following message in a pop-up box .. "Processing Error ..
An error has occurred during report processing. Query execution failed for
data set 'TestDS'. Line 1: Incorrect syntax error'.' "
The only change is the ' = " ..." '
The Datatset Command type is 'Text'
Any idea what is happening here'
--
Thanks in advance
GHHi Vakar,
Based on my knowledge, the Generic Query Designer Execute button will grey
out when the query begins with an '='
This is expected behavior since dynamic query cannot be executed in Generic
Query Designer, which also means you are not able to Perview the data now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Correct the i cannot execute the query from the Query Designer in the Data tab.
I get the error when I go to the the Preview tab.
Thanks
"Michael Cheng [MSFT]" wrote:
> Hi Vakar,
> Based on my knowledge, the Generic Query Designer Execute button will grey
> out when the query begins with an '='
> This is expected behavior since dynamic query cannot be executed in Generic
> Query Designer, which also means you are not able to Perview the data now.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||If you get an error when you go to Preview it is because something is wrong
with your string. If you have to (and I avoid it) use an expression then do
these steps.
1. Create a report with report parameters and a text box (nothing else)
2. Set the textbox to the expression that will end up being the source for
the dataset. Preview and look at what is in the textbox and make sure that
it is valid SQL (copy and paste into query analyzer and validate you have it
correctly).
3. create a query that will have the same fields returned to get you field
list populated
4. change to the expression that you now know (from #2) is correct.
These are the steps I always take.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> Correct the i cannot execute the query from the Query Designer in the Data
tab.
> I get the error when I go to the the Preview tab.
> Thanks
> "Michael Cheng [MSFT]" wrote:
> > Hi Vakar,
> >
> > Based on my knowledge, the Generic Query Designer Execute button will
grey
> > out when the query begins with an '='
> >
> > This is expected behavior since dynamic query cannot be executed in
Generic
> > Query Designer, which also means you are not able to Perview the data
now.
> >
> > Thank you for your patience and corporation. If you have any questions
or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Get Secure! - http://www.microsoft.com/security
> >
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Great suggustion ... I was going to ask for debugging suggestions ... I
The following testing was just completed.
I created a dataset (query 2) that is a subset of my problem query (query 1)
which is populating another table. The IIF function is the SAME for both
queries (I cut and pasted from Query 2 to Query 1). Query 2 works but Query
1 gets the error. Query 1 runs when commenting out the IIF function and
removing the =" ".
Here are the queries:
Query 1
="SELECT DISTINCT
a.EID
,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
,a.PassageDistrictID
,Region = CASE
WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
WHEN e.PassageRegionFullName = '' THEN 'Unknown'
ELSE e.PassageRegionFullName
END
,a.EventName
,RegionalMgrName = CASE
WHEN b.LastName IS NULL THEN 'N/A'
WHEN b.LastName = '' THEN 'N/A'
ELSE b.LastName
END
,OperationsMgrName = CASE
WHEN d.LastName IS NULL THEN 'N/A'
WHEN d.LastName = '' THEN 'N/A'
ELSE d.LastName
END
,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
FROM dbo.Event a
LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=e.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
Query 2
="SELECT a.EID, a.EventName
FROM dbo.Event a
JOIN GeographicDim_PE b
ON a.PassageDistrictID = b.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
"Bruce L-C [MVP]" wrote:
> If you get an error when you go to Preview it is because something is wrong
> with your string. If you have to (and I avoid it) use an expression then do
> these steps.
> 1. Create a report with report parameters and a text box (nothing else)
> 2. Set the textbox to the expression that will end up being the source for
> the dataset. Preview and look at what is in the textbox and make sure that
> it is valid SQL (copy and paste into query analyzer and validate you have it
> correctly).
> 3. create a query that will have the same fields returned to get you field
> list populated
> 4. change to the expression that you now know (from #2) is correct.
> These are the steps I always take.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > Correct the i cannot execute the query from the Query Designer in the Data
> tab.
> >
> > I get the error when I go to the the Preview tab.
> >
> > Thanks
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> > > Hi Vakar,
> > >
> > > Based on my knowledge, the Generic Query Designer Execute button will
> grey
> > > out when the query begins with an '='
> > >
> > > This is expected behavior since dynamic query cannot be executed in
> Generic
> > > Query Designer, which also means you are not able to Perview the data
> now.
> > >
> > > Thank you for your patience and corporation. If you have any questions
> or
> > > concerns, don't hesitate to let me know. We are always here to be of
> > > assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Michael Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Get Secure! - http://www.microsoft.com/security
> > >
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
>|||I have heard that sometimes there is a problem with put in a hard carriage
return in the expression box. Make sure the iif part is on the same line.
I.e. let any line wrapping in the expression box occur on its own, don't do
a carriage return to put the iif on another line. Just a guess.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> Great suggustion ... I was going to ask for debugging suggestions ... I
> The following testing was just completed.
> I created a dataset (query 2) that is a subset of my problem query (query
1)
> which is populating another table. The IIF function is the SAME for both
> queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
Query
> 1 gets the error. Query 1 runs when commenting out the IIF function and
> removing the =" ".
> Here are the queries:
> Query 1
> ="SELECT DISTINCT
> a.EID
> ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> ,a.PassageDistrictID
> ,Region => CASE
> WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> ELSE e.PassageRegionFullName
> END
> ,a.EventName
> ,RegionalMgrName => CASE
> WHEN b.LastName IS NULL THEN 'N/A'
> WHEN b.LastName = '' THEN 'N/A'
> ELSE b.LastName
> END
> ,OperationsMgrName => CASE
> WHEN d.LastName IS NULL THEN 'N/A'
> WHEN d.LastName = '' THEN 'N/A'
> ELSE d.LastName
> END
> ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> FROM dbo.Event a
> LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> e.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> Query 2
> ="SELECT a.EID, a.EventName
> FROM dbo.Event a
> JOIN GeographicDim_PE b
> ON a.PassageDistrictID = b.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> "Bruce L-C [MVP]" wrote:
> > If you get an error when you go to Preview it is because something is
wrong
> > with your string. If you have to (and I avoid it) use an expression then
do
> > these steps.
> > 1. Create a report with report parameters and a text box (nothing else)
> > 2. Set the textbox to the expression that will end up being the source
for
> > the dataset. Preview and look at what is in the textbox and make sure
that
> > it is valid SQL (copy and paste into query analyzer and validate you
have it
> > correctly).
> > 3. create a query that will have the same fields returned to get you
field
> > list populated
> > 4. change to the expression that you now know (from #2) is correct.
> >
> > These are the steps I always take.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > Correct the i cannot execute the query from the Query Designer in the
Data
> > tab.
> > >
> > > I get the error when I go to the the Preview tab.
> > >
> > > Thanks
> > >
> > > "Michael Cheng [MSFT]" wrote:
> > >
> > > > Hi Vakar,
> > > >
> > > > Based on my knowledge, the Generic Query Designer Execute button
will
> > grey
> > > > out when the query begins with an '='
> > > >
> > > > This is expected behavior since dynamic query cannot be executed in
> > Generic
> > > > Query Designer, which also means you are not able to Perview the
data
> > now.
> > > >
> > > > Thank you for your patience and corporation. If you have any
questions
> > or
> > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > assistance!
> > > >
> > > >
> > > > Sincerely yours,
> > > >
> > > > Michael Cheng
> > > >
> > > > Online Partner Support Specialist
> > > > Partner Support Group
> > > > Microsoft Global Technical Support Center
> > > > ---
> > > > Get Secure! - http://www.microsoft.com/security
> > > >
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > Please reply to newsgroups only, many thanks!
> > > >
> > > >
> >
> >
> >|||I just tried that ...
I removed all cr/lf making on long line of code ... nastly ... but it
worked ...
My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
Thanks for you help!!
"Bruce L-C [MVP]" wrote:
> I have heard that sometimes there is a problem with put in a hard carriage
> return in the expression box. Make sure the iif part is on the same line.
> I.e. let any line wrapping in the expression box occur on its own, don't do
> a carriage return to put the iif on another line. Just a guess.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > Great suggustion ... I was going to ask for debugging suggestions ... I
> >
> > The following testing was just completed.
> >
> > I created a dataset (query 2) that is a subset of my problem query (query
> 1)
> > which is populating another table. The IIF function is the SAME for both
> > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> Query
> > 1 gets the error. Query 1 runs when commenting out the IIF function and
> > removing the =" ".
> >
> > Here are the queries:
> > Query 1
> > ="SELECT DISTINCT
> > a.EID
> > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > ,a.PassageDistrictID
> > ,Region => > CASE
> > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > ELSE e.PassageRegionFullName
> > END
> > ,a.EventName
> > ,RegionalMgrName => > CASE
> > WHEN b.LastName IS NULL THEN 'N/A'
> > WHEN b.LastName = '' THEN 'N/A'
> > ELSE b.LastName
> > END
> > ,OperationsMgrName => > CASE
> > WHEN d.LastName IS NULL THEN 'N/A'
> > WHEN d.LastName = '' THEN 'N/A'
> > ELSE d.LastName
> > END
> > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> > FROM dbo.Event a
> > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > e.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > Query 2
> > ="SELECT a.EID, a.EventName
> > FROM dbo.Event a
> > JOIN GeographicDim_PE b
> > ON a.PassageDistrictID = b.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > If you get an error when you go to Preview it is because something is
> wrong
> > > with your string. If you have to (and I avoid it) use an expression then
> do
> > > these steps.
> > > 1. Create a report with report parameters and a text box (nothing else)
> > > 2. Set the textbox to the expression that will end up being the source
> for
> > > the dataset. Preview and look at what is in the textbox and make sure
> that
> > > it is valid SQL (copy and paste into query analyzer and validate you
> have it
> > > correctly).
> > > 3. create a query that will have the same fields returned to get you
> field
> > > list populated
> > > 4. change to the expression that you now know (from #2) is correct.
> > >
> > > These are the steps I always take.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "GH" <vakar@.community.nospam> wrote in message
> > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > Correct the i cannot execute the query from the Query Designer in the
> Data
> > > tab.
> > > >
> > > > I get the error when I go to the the Preview tab.
> > > >
> > > > Thanks
> > > >
> > > > "Michael Cheng [MSFT]" wrote:
> > > >
> > > > > Hi Vakar,
> > > > >
> > > > > Based on my knowledge, the Generic Query Designer Execute button
> will
> > > grey
> > > > > out when the query begins with an '='
> > > > >
> > > > > This is expected behavior since dynamic query cannot be executed in
> > > Generic
> > > > > Query Designer, which also means you are not able to Perview the
> data
> > > now.
> > > > >
> > > > > Thank you for your patience and corporation. If you have any
> questions
> > > or
> > > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > > assistance!
> > > > >
> > > > >
> > > > > Sincerely yours,
> > > > >
> > > > > Michael Cheng
> > > > >
> > > > > Online Partner Support Specialist
> > > > > Partner Support Group
> > > > > Microsoft Global Technical Support Center
> > > > > ---
> > > > > Get Secure! - http://www.microsoft.com/security
> > > > >
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > > Please reply to newsgroups only, many thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Great. Wasn't sure if it would solve the problem but I couldn't see anything
wrong (having the second query with the iif work but not the first was the
clue for me).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:FF5CFDA6-8144-482A-BA91-DB35B53ECE25@.microsoft.com...
> I just tried that ...
> I removed all cr/lf making on long line of code ... nastly ... but it
> worked ...
> My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
> Thanks for you help!!
> "Bruce L-C [MVP]" wrote:
> > I have heard that sometimes there is a problem with put in a hard
carriage
> > return in the expression box. Make sure the iif part is on the same
line.
> > I.e. let any line wrapping in the expression box occur on its own, don't
do
> > a carriage return to put the iif on another line. Just a guess.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > > Great suggustion ... I was going to ask for debugging suggestions ...
I
> > >
> > > The following testing was just completed.
> > >
> > > I created a dataset (query 2) that is a subset of my problem query
(query
> > 1)
> > > which is populating another table. The IIF function is the SAME for
both
> > > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> > Query
> > > 1 gets the error. Query 1 runs when commenting out the IIF function
and
> > > removing the =" ".
> > >
> > > Here are the queries:
> > > Query 1
> > > ="SELECT DISTINCT
> > > a.EID
> > > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > > ,a.PassageDistrictID
> > > ,Region => > > CASE
> > > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > > ELSE e.PassageRegionFullName
> > > END
> > > ,a.EventName
> > > ,RegionalMgrName => > > CASE
> > > WHEN b.LastName IS NULL THEN 'N/A'
> > > WHEN b.LastName = '' THEN 'N/A'
> > > ELSE b.LastName
> > > END
> > > ,OperationsMgrName => > > CASE
> > > WHEN d.LastName IS NULL THEN 'N/A'
> > > WHEN d.LastName = '' THEN 'N/A'
> > > ELSE d.LastName
> > > END
> > > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS
AssignmentLtrSentDt
> > > FROM dbo.Event a
> > > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > > e.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > Query 2
> > > ="SELECT a.EID, a.EventName
> > > FROM dbo.Event a
> > > JOIN GeographicDim_PE b
> > > ON a.PassageDistrictID = b.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > If you get an error when you go to Preview it is because something
is
> > wrong
> > > > with your string. If you have to (and I avoid it) use an expression
then
> > do
> > > > these steps.
> > > > 1. Create a report with report parameters and a text box (nothing
else)
> > > > 2. Set the textbox to the expression that will end up being the
source
> > for
> > > > the dataset. Preview and look at what is in the textbox and make
sure
> > that
> > > > it is valid SQL (copy and paste into query analyzer and validate you
> > have it
> > > > correctly).
> > > > 3. create a query that will have the same fields returned to get you
> > field
> > > > list populated
> > > > 4. change to the expression that you now know (from #2) is correct.
> > > >
> > > > These are the steps I always take.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "GH" <vakar@.community.nospam> wrote in message
> > > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > > Correct the i cannot execute the query from the Query Designer in
the
> > Data
> > > > tab.
> > > > >
> > > > > I get the error when I go to the the Preview tab.
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Michael Cheng [MSFT]" wrote:
> > > > >
> > > > > > Hi Vakar,
> > > > > >
> > > > > > Based on my knowledge, the Generic Query Designer Execute button
> > will
> > > > grey
> > > > > > out when the query begins with an '='
> > > > > >
> > > > > > This is expected behavior since dynamic query cannot be executed
in
> > > > Generic
> > > > > > Query Designer, which also means you are not able to Perview the
> > data
> > > > now.
> > > > > >
> > > > > > Thank you for your patience and corporation. If you have any
> > questions
> > > > or
> > > > > > concerns, don't hesitate to let me know. We are always here to
be of
> > > > > > assistance!
> > > > > >
> > > > > >
> > > > > > Sincerely yours,
> > > > > >
> > > > > > Michael Cheng
> > > > > >
> > > > > > Online Partner Support Specialist
> > > > > > Partner Support Group
> > > > > > Microsoft Global Technical Support Center
> > > > > > ---
> > > > > > Get Secure! - http://www.microsoft.com/security
> > > > > >
> > > > > > This posting is provided "as is" with no warranties and confers
no
> > > > rights.
> > > > > > Please reply to newsgroups only, many thanks!
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

DataFile and Log File

I want to know the datafile and log file for the given input parameter. I am
basically creating function and the input parameter would be database name
and that will return me DataFile and the LogFile.
Can anybody tell me which query I have to used in the function. I have to
used to get the DataFile and LogFile
Have a look at the sys.database_files catalog view in BOL.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>I want to know the datafile and log file for the given input parameter. I
>am basically creating function and the input parameter would be database
>name and that will return me DataFile and the LogFile.
> Can anybody tell me which query I have to used in the function. I have to
> used to get the DataFile and LogFile
>
|||In SQL Server 2000?
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGIt1IRiHHA.392@.TK2MSFTNGP06.phx.gbl...
> Have a look at the sys.database_files catalog view in BOL.
> --
> Andrew J. Kelly SQL MVP
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>
|||On Sat, 28 Apr 2007 10:42:15 -0400, Roger wrote:

>In SQL Server 2000?
Hi Roger,
A more polite way to write this would have been; "Oh, I'm sorry I forgot
to include in my original post that I'm using SQL Server 2000. Is there
an alternative method for that version?"
The answer to that question would have been to check the sysfiles system
table in Books Online.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

DATABASEPROPERYEX is not a recognised function name

I am trying to write a report in reporting services 2000 which access a sql server 7 database. It all workes fine with the connection and creating a dataset but I cannot seem to get the results displayed in the preview section. I get the following error:

"An error occured during report processing query execution failed for dataset XXXX DATABASEPROPERYEX is not a recognised function name "

Any help greatly appreciated

Your are running RS 2000 RTM - you have two options:

* install RS 2000 SP1 or SP2 on both the report server and the report designer machines. The service packs contain the fix.

* Alternatively, in report designer go to the "Data Options" tab of the "Dataset"
dialog. 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

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/
>

'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

'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

'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

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
>>
>