ODBC connection to SQL Server

I use Business Objects XI R2 (BOXI) to report against a SQL Server 2000 database using an ODBC connection.
When I query the database using Enterprise Manager on the server I can make use of all functions available within SQL Server, yet when I access it via the ODBC connection I only get a very small subset of SQL Server functions available.

I can use the inbuilt BOXI functions, which work on the data locally, but would like the functions processed on the server instead.

Is this constraint a property of the ODBC driver, and if so are there other drivers which would expose all of the available SQL Server functions.

Any suggestions appreciated.

Hi Farengi,

When you refer to "functions available within SQL Server", do you mean stored procedures and user-defined functions? Or you mean the GUI-related features like graphic query design?

If we are talking about the stored procs and user-defined functions, there's no limitation per client. In fact, Enterprise Manager uses ODBC under the hood. :-)

However, the stored procs and functions are just database objects - they have permissions granted to them for the appropriate user. Are you using the same login/user in Enterprise Manager as well as in BOXI? In addition, don't forget that the names of the procs and functions is also related to their owner, thus dbo.usp_MyProc is different from JivkoD.usp_MyProc.

If you are referring to the GUI-related features, unfortunately they are specific to the Enterprise Manager and are not exposed to different client applications.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Jivko,

Thank you for your extensive answer.

Sorry about the delay in responding to you, but I have been attending some advanced BO courses and my question, which I may not have stated correctly, was answered.

The functions I wanted to refer to (eg. max, min, stdev, stdevp, var, varp etc.) in my question, are displayed in the Business Objects sql builder interface only if they are contained in a .prm (parameter) file. This file is supplied with a frequently used subset of sql server functions, but it can be edited and added to. All that this file does is to present a list of functions to the interface.

The functions, which don't appear in the interface, can still be used in a sql statement as long as they are valid functions.

Thanks

ODBC connection to SQL Server

I use Business Objects XI R2 (BOXI) to report against a SQL Server 2000 database using an ODBC connection.
When I query the database using Enterprise Manager on the server I can make use of all functions available within SQL Server, yet when I access it via the ODBC connection I only get a very small subset of SQL Server functions available.

I can use the inbuilt BOXI functions, which work on the data locally, but would like the functions processed on the server instead.

Is this constraint a property of the ODBC driver, and if so are there other drivers which would expose all of the available SQL Server functions.

Any suggestions appreciated.

Hi Farengi,

When you refer to "functions available within SQL Server", do you mean stored procedures and user-defined functions? Or you mean the GUI-related features like graphic query design?

If we are talking about the stored procs and user-defined functions, there's no limitation per client. In fact, Enterprise Manager uses ODBC under the hood. :-)

However, the stored procs and functions are just database objects - they have permissions granted to them for the appropriate user. Are you using the same login/user in Enterprise Manager as well as in BOXI? In addition, don't forget that the names of the procs and functions is also related to their owner, thus dbo.usp_MyProc is different from JivkoD.usp_MyProc.

If you are referring to the GUI-related features, unfortunately they are specific to the Enterprise Manager and are not exposed to different client applications.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Jivko,

Thank you for your extensive answer.

Sorry about the delay in responding to you, but I have been attending some advanced BO courses and my question, which I may not have stated correctly, was answered.

The functions I wanted to refer to (eg. max, min, stdev, stdevp, var, varp etc.) in my question, are displayed in the Business Objects sql builder interface only if they are contained in a .prm (parameter) file. This file is supplied with a frequently used subset of sql server functions, but it can be edited and added to. All that this file does is to present a list of functions to the interface.

The functions, which don't appear in the interface, can still be used in a sql statement as long as they are valid functions.

Thanks

Obtaining business hours based on start and end date

I have a transaction log that tracks issues from a call center. Each
time an issue is assigned to someone else, closed, etc. I get a time
stamp. I have these time stamps for the beginning of an issue to the
end of an issue and I'd like to determine how many business hours these
issues were open.

Issue BeginDt Enddt Total hours
1 3/29/05 5:00 PM 4/1/05 2:00 PM 69

Basically, this is the type of data I'm looking at and my hours of work
are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
all nonbusiness hours, weekends, & holidays from the difference of the
two dates. Issues can span for 2-3 days or 20-30 days.

Please let me know if anyone has any ideas or has done something like
this before.

Thanks!On 1 Apr 2005 14:26:14 -0800, mitchchristensen@.gmail.com wrote:

>I have a transaction log that tracks issues from a call center. Each
>time an issue is assigned to someone else, closed, etc. I get a time
>stamp. I have these time stamps for the beginning of an issue to the
>end of an issue and I'd like to determine how many business hours these
>issues were open.
>Issue BeginDt Enddt Total hours
>1 3/29/05 5:00 PM 4/1/05 2:00 PM 69
>Basically, this is the type of data I'm looking at and my hours of work
>are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
>all nonbusiness hours, weekends, & holidays from the difference of the
>two dates. Issues can span for 2-3 days or 20-30 days.
>Please let me know if anyone has any ideas or has done something like
>this before.
>Thanks!

Hi mitchchristensen,

The easiest way to do it is to use a calendar table. What that is, how
you can make it and various good ways to use it are described at Aaron's
site: http://www.aspfaq.com/show.asp?id=2519.

For this specific situation, I'd suggest the following approach:

DECLARE @.Start smalldatetime,
@.End smalldatetime
SET @.Start = '2005-03-22T17:00:00'
SET @.End = '2005-04-01T14:00:00'

SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
- DATEDIFF (day, @.Start, @.End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @.Start
AND dt < @.End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

This might not be the quickes, but it has the advantage that it'spretty
straightforward: first, calculate the number of clock hours from start
to end; then subtract 14.5 hours (the time from 5:00 PM - 7:30 AM) for
each full day in the range; finally subtract another 9.5 hours (the time
from 7:30 AM to 5:00 PM) for each weekend or holiday in the range.

The assumption I made is that start and end dates will always be during
opening hours (i.e. not on weekends or on holidays and never outside the
7:30 AM - 5:00 PM range).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Hi mitchchristensen,
> The easiest way to do it is to use a calendar table. What that is, how
> you can make it and various good ways to use it are described at Aaron's
> site: http://www.aspfaq.com/show.asp?id=2519.
> For this specific situation, I'd suggest the following approach:
> DECLARE @.Start smalldatetime,
> @.End smalldatetime
> SET @.Start = '2005-03-22T17:00:00'
> SET @.End = '2005-04-01T14:00:00'
> SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
> - DATEDIFF (day, @.Start, @.End) * 14.5
> - (SELECT COUNT(*)
> FROM Calendar
> WHERE dt > @.Start
> AND dt < @.End
> AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

Since it seems unlikely that Mitch would like to disregard Christmas,
Thanksgiving and other holidays, Hugo solutions is very good. However,
I believe this is a solution that would work if we are for some reason
talking all Monday to Friday:

SELECT DATEDIFF (minute, @.start, @.stop) / 60.0 -
DATEDIFF (day, @.start, @.stop) * 14.5 -
DATEDIFF (week, @.start, @.stop) * 2 * 9.5

I owe Hugo's original query lot for this extension.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the suggestions!

Unfortunately I do need to disregard certain holidays based on the
region(I will have a holiday calendar for each region)

Also, end dates can occur on weekends or even holidays as some issues
are closed by the system based on a time parameter and the system has
no regards for whether or not it's an actual working day.

Mitch|||I have tried to do this in SQL and can tell you that you will be better
off doing it in application logic. The solution posted above performs
terribly.|||(mitchchristensen@.gmail.com) writes:
> Unfortunately I do need to disregard certain holidays based on the
> region(I will have a holiday calendar for each region)

Well, Hugo's solution should be your choice.

> Also, end dates can occur on weekends or even holidays as some issues
> are closed by the system based on a time parameter and the system has
> no regards for whether or not it's an actual working day.

Since I don't have any test data, I can't test Hugo's solution
for the case where the end date is a non a working day, but at a
glance it appers that his solution should handle this situation.

Since it was time, I repost Hugo's solution here:

DECLARE @.Start smalldatetime,
@.End smalldatetime
SET @.Start = '2005-03-22T17:00:00'
SET @.End = '2005-04-01T14:00:00'

SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
- DATEDIFF (day, @.Start, @.End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @.Start
AND dt < @.End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

The complete thead can be reviewed at
http://groups.google.com/groups?dq=...0127.0.0.1%253E

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp