OLAP Client - your suggestions

Hello,

I am trying to Upgrade my AS 2000 to AS 2005 but end up hitting wall. Our clients is Excel 2002/2003 on Windows 2000 OS. I managed to convince group about advantage to moving AS 2005. At last I managed to convince advantage to moving Excel 2007 which added new project of upgrading office 2007 in selected workstations. Then I find out we can not install Excel 2007 in Windows 2000. Upgrading Clients workstations to XP is too much to ask for, so whole thing end up to ground zero. What you guys think what options I have. Is there any cheep out of box client/ or option of min. development which I can use in Windows 2000 workstations so that I can move on with AS 2005 migration.

I know I can still use excel 2002/2003 in client with AS 2005 but then all new features and measure groups are not coming ordered in Excel 2002/Excel2003.

Thanks for you advice,

-Ashok

This add-in can enhance ExcelXP and Excel2003 clients:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

It is not as good as Excel 2007 but still better than the basic functionality in these clients.

HTH

Thomas Ivarsson

OLAP Client

Upgrading to Analysis Services 2005 has put me in difficult position because of lack of good client from Microsoft and using Excel 2002/2003.

We are small shop and use Excel (Pivot Table) big time as client tool. We upgraded to Analysis Services 2005 and we all know on server side it helped a lot.

BUT, I was expecting Excel 2002/2003 to work "at least same" even it can not use new stuff in 2005. What's killing now is Excel 2002/2003 Pivot Table client using Analysis Services 2005 on server "Not able to display Measure Names in alphabetical order" - Big problem for users.

Upgrading to Excel 2007 is not easy at least let me put this way Upgrading to Analysis Services 2005 and Excel 2007 same time is not possible. It's a very big project with big risk if you are in financial company. It worse for us because we still use windows 2000 so upgrade OS for what? cube access.

Now what. Think about buying new client with one of these companies BO/Crystal, ProClarity etc. is also hard choice because it's not Excel and hard to sell to business group to pay for it when you ask for ......

one of the big business guy asked me yesterday "Can we go back to Analysis Services 2000 because I don't know what you got in Server but it's not giving me what I had before...."

what you will do?

- Ashok

Hello. This add in for older versions of Excel(2002 and 2003) might help:

http://www.microsoft.com/downloads/details.aspx?familyid=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

HTH

Thomas Ivarsson

|||

Hi Thomas,

As I was typing my question I knew this coming as first reply. This is not very helpful Add-in and just wandering any one using this in production let me know.

As you visit page first line you see this -

Microsoft does not provide any support for this solution accelerator and has no plans to release future versions. This add-in is not designed to work with with Office 2007.

That's ok

The number one reason to move to Analysis Services 2005 is to takeoff load from client. Remember when you run large query from client to Analysis Services 2000 your whole machine hang for some time. With excel "Cube Analysis" I don't know technologies behind but it's slow and uses client side memory. If you run large query and like to cancel (Esc key), you can not.

Interface is hard to use. Not looking for too much but mainly it's slow and using client memory.

One thing can help me if any one knows if this is possible. If it is possible to write VB code/macro which can run when excel file gets open and change the order in pivot table cube field list using Office APIs. Not looking for code just yes or no.

Thank you - Ashok

|||The Excel Add-in for Analysis Services is a good work around until you upgrade to Office 2007. There is no need for this tool in Office 2007 because PivotTable features in 2007 have been enhanced to include all the functionality that the Excel Add-In for Analysis Services includes. If you're thinking of using VB macros, I think it's a very bad road to take. You would be much better off using Reporting Services directly against the cube.|||

Thanks Martin. I understand using Add-in is good choice for now. Only issue I have with that it is taking client side memory and it will slow down not only cube query whole users box while they are using cube.

Reporting Services I can use what are the options we have in Reporting services to expose cube to end users? I know OLAP can be one data source to reporting services but then we need to design flat reports. Is there any control which allow users can drag drop fields and see data. I know about ReportBuilder.

Thanks again - Ashok

|||

Hi Ashok,

I wouldn't think that the Add-In would use more client-side memory than PivotTable services but I can't say I've done an exhaustive study either. ReportBuilder is another option but limited. If you have measure groups that are associated to dimensions at non-leaf levels, it likely won't be your solution. Additionally, users will only be able to construct reports using measures from a single measure group at a time so the produced reports are going to have to be simple. If your user's reporting requirements aren't complex, it's a good approach and very easy to implement and I'd recommend it.

|||

Thank's Martin for the comments.

Ashok, the add-in is a close you can get to the interface in Excel2007. It is not perfect but it works.

You can also choose to upgrade only the OLEDB-provider from 8 to 9 and use Excel2003 pivot tables. It will not look good but it will work.

If you choose to stay with older versions of clients you are left with that choice.

I have no advice of a cheap add-in for Excel that will solve your problem.

HTH

Thomas Ivarsson

|||

It may sound funny but it helped my cube users, when they really don't want any major change in the way they use current excel 2002/2003. so I had to find solution some thing they can accept.

I stored all my Analysis Services 2005 cubes measures from different measures groups in one xml file and created an excel form. Understanding all limitation in this way and giving some thing that can help cube users, trade off worked for this solution.

They have original Pivot table Field List to get Dimensions and for measures they open this excel form and get measures in much easy way. This is helping because we have over 200 measures.

Private Sub CommandButton1_Click()
'Try
For l = 1 To ListBox1.ListCount - 1
If ListBox1.Selected(l) = True Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[" & ListBox1.List(l) & "]"), ListBox1.List(l)
'MsgBox (ListBox1.List(l))
End If
Next l
'Catch ex As Exception
'MsgBox ("Error in getting records: " + ex.Message)

End Sub

Private Sub TextBox1_Change()
For l = 1 To ListBox1.ListCount - 1
If UCase(ListBox1.List(l)) = UCase(TextBox1.Text) Then
ListBox1.Selected(l) = True
Else
ListBox1.Selected(l) = False
End If
Next l
End Sub

Private Sub UserForm_Initialize()
Dim oXML As Object, oXSL As Object
Set oXML = CreateObject("MSXML.DOMDocument")
oXML.Load "T:\IT\Cubes\CubeMeasures.xml"
Dim t As String
For Counter = 1 To oXML.documentElement.childNodes.Length - 1
ListBox1.AddItem (oXML.documentElement.childNodes.Item(Counter).Text)
Next Counter
ListBox1.MultiSelect = fmMultiSelectExtended
End Sub

T:\IT\Cubes\CubeMeasures.xml File

<All>
<MeasureName>Measure 1</MeasureName>
<MeasureName>Measure 2</MeasureName>
<MeasureName>Measure 3</MeasureName>
<MeasureName>Measure 4</MeasureName>
</All>

OLAP Client

Upgrading to Analysis Services 2005 has put me in difficult position because of lack of good client from Microsoft and using Excel 2002/2003.

We are small shop and use Excel (Pivot Table) big time as client tool. We upgraded to Analysis Services 2005 and we all know on server side it helped a lot.

BUT, I was expecting Excel 2002/2003 to work "at least same" even it can not use new stuff in 2005. What's killing now is Excel 2002/2003 Pivot Table client using Analysis Services 2005 on server "Not able to display Measure Names in alphabetical order" - Big problem for users.

Upgrading to Excel 2007 is not easy at least let me put this way Upgrading to Analysis Services 2005 and Excel 2007 same time is not possible. It's a very big project with big risk if you are in financial company. It worse for us because we still use windows 2000 so upgrade OS for what? cube access.

Now what. Think about buying new client with one of these companies BO/Crystal, ProClarity etc. is also hard choice because it's not Excel and hard to sell to business group to pay for it when you ask for ......

one of the big business guy asked me yesterday "Can we go back to Analysis Services 2000 because I don't know what you got in Server but it's not giving me what I had before...."

what you will do?

- Ashok

Hello. This add in for older versions of Excel(2002 and 2003) might help:

http://www.microsoft.com/downloads/details.aspx?familyid=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

HTH

Thomas Ivarsson

|||

Hi Thomas,

As I was typing my question I knew this coming as first reply. This is not very helpful Add-in and just wandering any one using this in production let me know.

As you visit page first line you see this -

Microsoft does not provide any support for this solution accelerator and has no plans to release future versions. This add-in is not designed to work with with Office 2007.

That's ok

The number one reason to move to Analysis Services 2005 is to takeoff load from client. Remember when you run large query from client to Analysis Services 2000 your whole machine hang for some time. With excel "Cube Analysis" I don't know technologies behind but it's slow and uses client side memory. If you run large query and like to cancel (Esc key), you can not.

Interface is hard to use. Not looking for too much but mainly it's slow and using client memory.

One thing can help me if any one knows if this is possible. If it is possible to write VB code/macro which can run when excel file gets open and change the order in pivot table cube field list using Office APIs. Not looking for code just yes or no.

Thank you - Ashok

|||The Excel Add-in for Analysis Services is a good work around until you upgrade to Office 2007. There is no need for this tool in Office 2007 because PivotTable features in 2007 have been enhanced to include all the functionality that the Excel Add-In for Analysis Services includes. If you're thinking of using VB macros, I think it's a very bad road to take. You would be much better off using Reporting Services directly against the cube.|||

Thanks Martin. I understand using Add-in is good choice for now. Only issue I have with that it is taking client side memory and it will slow down not only cube query whole users box while they are using cube.

Reporting Services I can use what are the options we have in Reporting services to expose cube to end users? I know OLAP can be one data source to reporting services but then we need to design flat reports. Is there any control which allow users can drag drop fields and see data. I know about ReportBuilder.

Thanks again - Ashok

|||

Hi Ashok,

I wouldn't think that the Add-In would use more client-side memory than PivotTable services but I can't say I've done an exhaustive study either. ReportBuilder is another option but limited. If you have measure groups that are associated to dimensions at non-leaf levels, it likely won't be your solution. Additionally, users will only be able to construct reports using measures from a single measure group at a time so the produced reports are going to have to be simple. If your user's reporting requirements aren't complex, it's a good approach and very easy to implement and I'd recommend it.

|||

Thank's Martin for the comments.

Ashok, the add-in is a close you can get to the interface in Excel2007. It is not perfect but it works.

You can also choose to upgrade only the OLEDB-provider from 8 to 9 and use Excel2003 pivot tables. It will not look good but it will work.

If you choose to stay with older versions of clients you are left with that choice.

I have no advice of a cheap add-in for Excel that will solve your problem.

HTH

Thomas Ivarsson

|||

It may sound funny but it helped my cube users, when they really don't want any major change in the way they use current excel 2002/2003. so I had to find solution some thing they can accept.

I stored all my Analysis Services 2005 cubes measures from different measures groups in one xml file and created an excel form. Understanding all limitation in this way and giving some thing that can help cube users, trade off worked for this solution.

They have original Pivot table Field List to get Dimensions and for measures they open this excel form and get measures in much easy way. This is helping because we have over 200 measures.

Private Sub CommandButton1_Click()
'Try
For l = 1 To ListBox1.ListCount - 1
If ListBox1.Selected(l) = True Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[" & ListBox1.List(l) & "]"), ListBox1.List(l)
'MsgBox (ListBox1.List(l))
End If
Next l
'Catch ex As Exception
'MsgBox ("Error in getting records: " + ex.Message)

End Sub

Private Sub TextBox1_Change()
For l = 1 To ListBox1.ListCount - 1
If UCase(ListBox1.List(l)) = UCase(TextBox1.Text) Then
ListBox1.Selected(l) = True
Else
ListBox1.Selected(l) = False
End If
Next l
End Sub

Private Sub UserForm_Initialize()
Dim oXML As Object, oXSL As Object
Set oXML = CreateObject("MSXML.DOMDocument")
oXML.Load "T:\IT\Cubes\CubeMeasures.xml"
Dim t As String
For Counter = 1 To oXML.documentElement.childNodes.Length - 1
ListBox1.AddItem (oXML.documentElement.childNodes.Item(Counter).Text)
Next Counter
ListBox1.MultiSelect = fmMultiSelectExtended
End Sub

T:\IT\Cubes\CubeMeasures.xml File

<All>
<MeasureName>Measure 1</MeasureName>
<MeasureName>Measure 2</MeasureName>
<MeasureName>Measure 3</MeasureName>
<MeasureName>Measure 4</MeasureName>
</All>

OLAP based reports not rendering

I hope someone with a bit of knowledge about Analysis Services and Reporting
Services can give me some help here, please.
A client has the following setup:
One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services sp
2 and IIS 5.
One Windows 2003 server running Analysis Services sp 3.
--
1 role has been created in AS, with full access to everything. This is an AD
group, and we've tried adding all sorts of AD users to this group, to give
them access to the cube. We haven't tried adding "Everyone" and given it
full access.
--
When developing reports, we can access the OLAP cube with the usual NT login
of the developer. The report report renders perfectly on the dev box.
In Report Manager, reports based on SQL Server queries works perfectly.
Reports based on OLAP will work when run on the server running Reporing
Services, but NO OTHER computer. So when we access the Reporing Services
server with Remote Desktop, the OLAP report will render. From any other
computer, all in the same domain, it won't work.
The error message we get is basically "Cannot create a connection to data
source '<our datasource>'". But the "explanation" varies depending on what
user we try to use in our connection settings. The most common was "Database
'<our database>' could not be found." The other one was "Unknown error
E_(bunch of characters)".
So... any suggestions? I've found posts from other people who seem to have
had the same problem as ours, but no solution. We haven't figured out where
the problem is. Is it the credentials sent from Internet Explorer? Is it the
identity used in the connection string? Is it a new bug in SP 2? Reporting
Services has just been installed, and SP2 was installed immedeately. We
don't know if it would have worked without SP2, and I guess we won't find
out.
All help is really appreciated on this one!
Yours,
Kaisa M. Lindahloh yes please - we are having the same problems and noone seems to be able
to help !!!!
Please can someone help ?
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
>I hope someone with a bit of knowledge about Analysis Services and
>Reporting Services can give me some help here, please.
> A client has the following setup:
> One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> sp 2 and IIS 5.
> One Windows 2003 server running Analysis Services sp 3.
> --
> 1 role has been created in AS, with full access to everything. This is an
> AD group, and we've tried adding all sorts of AD users to this group, to
> give them access to the cube. We haven't tried adding "Everyone" and given
> it full access.
> --
> When developing reports, we can access the OLAP cube with the usual NT
> login of the developer. The report report renders perfectly on the dev
> box.
> In Report Manager, reports based on SQL Server queries works perfectly.
> Reports based on OLAP will work when run on the server running Reporing
> Services, but NO OTHER computer. So when we access the Reporing Services
> server with Remote Desktop, the OLAP report will render. From any other
> computer, all in the same domain, it won't work.
> The error message we get is basically "Cannot create a connection to data
> source '<our datasource>'". But the "explanation" varies depending on what
> user we try to use in our connection settings. The most common was
> "Database '<our database>' could not be found." The other one was "Unknown
> error E_(bunch of characters)".
> So... any suggestions? I've found posts from other people who seem to have
> had the same problem as ours, but no solution. We haven't figured out
> where the problem is. Is it the credentials sent from Internet Explorer?
> Is it the identity used in the connection string? Is it a new bug in SP 2?
> Reporting Services has just been installed, and SP2 was installed
> immedeately. We don't know if it would have worked without SP2, and I
> guess we won't find out.
> All help is really appreciated on this one!
> Yours,
> Kaisa M. Lindahl
>|||Provider=MSOLAP.2;Client Cache Size=25;Data
Source=myOLAPServer;SSPI=Kerberos;Initial Catalog=myOLAPDB;Auto Synch
Period=20000
Look into setting up Kerberos/trust betweent he servers and check the
connection string above "Kerberos"
--
Gary Foster
"Kathy" wrote:
> oh yes please - we are having the same problems and noone seems to be able
> to help !!!!
> Please can someone help ?
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
> >I hope someone with a bit of knowledge about Analysis Services and
> >Reporting Services can give me some help here, please.
> >
> > A client has the following setup:
> >
> > One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> > sp 2 and IIS 5.
> >
> > One Windows 2003 server running Analysis Services sp 3.
> > --
> > 1 role has been created in AS, with full access to everything. This is an
> > AD group, and we've tried adding all sorts of AD users to this group, to
> > give them access to the cube. We haven't tried adding "Everyone" and given
> > it full access.
> > --
> >
> > When developing reports, we can access the OLAP cube with the usual NT
> > login of the developer. The report report renders perfectly on the dev
> > box.
> > In Report Manager, reports based on SQL Server queries works perfectly.
> > Reports based on OLAP will work when run on the server running Reporing
> > Services, but NO OTHER computer. So when we access the Reporing Services
> > server with Remote Desktop, the OLAP report will render. From any other
> > computer, all in the same domain, it won't work.
> >
> > The error message we get is basically "Cannot create a connection to data
> > source '<our datasource>'". But the "explanation" varies depending on what
> > user we try to use in our connection settings. The most common was
> > "Database '<our database>' could not be found." The other one was "Unknown
> > error E_(bunch of characters)".
> >
> > So... any suggestions? I've found posts from other people who seem to have
> > had the same problem as ours, but no solution. We haven't figured out
> > where the problem is. Is it the credentials sent from Internet Explorer?
> > Is it the identity used in the connection string? Is it a new bug in SP 2?
> > Reporting Services has just been installed, and SP2 was installed
> > immedeately. We don't know if it would have worked without SP2, and I
> > guess we won't find out.
> >
> > All help is really appreciated on this one!
> >
> > Yours,
> >
> > Kaisa M. Lindahl
> >
>
>|||For our reports that use Analysis Services we had to do the following.
First, we ended up was using a single domain account hard-coded in the
data-link properties for the datasource. This account must have at least a
read-only role on the AS box in Analysis Manager. We were not allowed to use
Kerberos delegation as out SA team thought it was a security reisk on AD2k.
We may go this route once they have upgraded to AD2k3 as that release is
supposed to have the ability to provide scope limited Kerberos delegation.
Anyway, from here, it seemed like it would work. Our users are given access
or not based on their domain accounts being in a group ReportingServices on
the Report Server machine. What we found though was this still did not work.
Finally we found that after every deploy, we always have to go to the
web-based report manager, find the datasource that points to AS and check the
box "Use as Windows credentials when connecting to the data source" and then
retype the password and apply. After this our reports worked fine. Be aware
that this check box gets unchecked each time you deploy. I have not found
any other workaround. If you do, please let me know.
Hope this helps.
"garyf@.someone.com" wrote:
> Provider=MSOLAP.2;Client Cache Size=25;Data
> Source=myOLAPServer;SSPI=Kerberos;Initial Catalog=myOLAPDB;Auto Synch
> Period=20000
> Look into setting up Kerberos/trust betweent he servers and check the
> connection string above "Kerberos"
> --
> Gary Foster
>
> "Kathy" wrote:
> > oh yes please - we are having the same problems and noone seems to be able
> > to help !!!!
> > Please can someone help ?
> >
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
> > >I hope someone with a bit of knowledge about Analysis Services and
> > >Reporting Services can give me some help here, please.
> > >
> > > A client has the following setup:
> > >
> > > One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> > > sp 2 and IIS 5.
> > >
> > > One Windows 2003 server running Analysis Services sp 3.
> > > --
> > > 1 role has been created in AS, with full access to everything. This is an
> > > AD group, and we've tried adding all sorts of AD users to this group, to
> > > give them access to the cube. We haven't tried adding "Everyone" and given
> > > it full access.
> > > --
> > >
> > > When developing reports, we can access the OLAP cube with the usual NT
> > > login of the developer. The report report renders perfectly on the dev
> > > box.
> > > In Report Manager, reports based on SQL Server queries works perfectly.
> > > Reports based on OLAP will work when run on the server running Reporing
> > > Services, but NO OTHER computer. So when we access the Reporing Services
> > > server with Remote Desktop, the OLAP report will render. From any other
> > > computer, all in the same domain, it won't work.
> > >
> > > The error message we get is basically "Cannot create a connection to data
> > > source '<our datasource>'". But the "explanation" varies depending on what
> > > user we try to use in our connection settings. The most common was
> > > "Database '<our database>' could not be found." The other one was "Unknown
> > > error E_(bunch of characters)".
> > >
> > > So... any suggestions? I've found posts from other people who seem to have
> > > had the same problem as ours, but no solution. We haven't figured out
> > > where the problem is. Is it the credentials sent from Internet Explorer?
> > > Is it the identity used in the connection string? Is it a new bug in SP 2?
> > > Reporting Services has just been installed, and SP2 was installed
> > > immedeately. We don't know if it would have worked without SP2, and I
> > > guess we won't find out.
> > >
> > > All help is really appreciated on this one!
> > >
> > > Yours,
> > >
> > > Kaisa M. Lindahl
> > >
> >
> >
> >sql

OLAP / Analysis services version checking by client

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

Hello,

I'm going to move your question to one of the SQL Server forums were they should be able to help you.

Daniel Roth

|||

Erkki wrote:

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

I would like to know that too. Anyway, how is it possible to verify the version of OLAP ? How can it be done? Maybe to somehow connect to server where the olap is being used?

OK to install Outlook 2003 client on an SQL Server 2005?

Hello,
I have an SQL 2005 server which is lightly used and I'd like to
install Outlook 2003 on it for a conversion utility which has nothing
to do with SQL.
Will installing Outlook cause problems for the SQL Server please?
Thanks,
- Alan.I've been using both of them on the same machine for a long time and I
haven't met a problem related to Outlook yet.
--
Ekrem Önsoy
"Alan" <bruguy@.gmail.com> wrote in message
news:1194340629.291591.23270@.k79g2000hse.googlegroups.com...
> Hello,
> I have an SQL 2005 server which is lightly used and I'd like to
> install Outlook 2003 on it for a conversion utility which has nothing
> to do with SQL.
> Will installing Outlook cause problems for the SQL Server please?
> Thanks,
> - Alan.
>|||That's good enough for me, thanks.
On Nov 6, 12:15 pm, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> I've been using both of them on the same machine for a long time and I
> haven't met a problem related to Outlook yet.
> --
> Ekrem =D6nsoy
> "Alan" <bru...@.gmail.com> wrote in message
> news:1194340629.291591.23270@.k79g2000hse.googlegroups.com...
> > Hello,
> > I have an SQL 2005 server which is lightly used and I'd like to
> > install Outlook 2003 on it for a conversion utility which has nothing
> > to do with SQL.
> > Will installing Outlook cause problems for the SQL Server please?
> > Thanks,
> > - Alan.

OK HERE IS HOW IT IS DONE!!!

I'm a developer for a rich client application with a primary grid that should be refreshed when data is changed by other users. I loath to resort to some sort of polling. It would be really cool if there is a native way to raise an event on the client from a SQL Server trigger. RAISEERROR can send a message to the Connection's InfoMessage() event. But of course this will only send the message back to the user who made the change. Is there anyway in SQL Server to raise an error message on another process so that this ADO event can pick it up?

This is not possible without some elaborate engineering in SQL Server 2005 (even more so in SQL Server 2000). With SQL Server 2005, you can use service broker to do this. It is hard to tell without knowing more details but I suggest that you take a look at the new features in SQL Server 2005 and see if those address your needs.|||You could always use firebird/interbase. probably on of the best rdbms's around.

Other that that (hypotheticals follow)

in SQL 2000 ->
You could put a com+ object in DTS. Trigger on a data-change notifies the com+ object. COM+ object has list of registered clients that it notifies of the changes.

SQL 2005 it would be even easier with the integration of .NET

wouldn't that work?

Oh dont make me do this in VBSUX because VB, well vb simply sux - how did it become so successful?! I dont want to do it in VB.NET as I want to cater the lowest common denominator. And boy is VB the lowest denominator!

Simple to do in delphi! but give me an hour. . . I'll do it in VB
God delphi rox!|||Thanks for your help. I'm not suprised that there isn't anything. However I'm disapointed SQL Server 2005 doesn't have it. SQL Server has all the capabilities as a simply event conduit between it's clients. Kind of a waste then that some other messaging service is required for simple data related events to be passed around.
For an alternative I have an idea to referesh based on user activty. A few events will call a function and refresh if some criteria has been met. Eg the form is activated after five minutes.
|||

well trying to do it using vbsux further ingrained my disdain for the piece of sh|t that vb6 is. Its not hard to do in delphi. It is Actually rather simple.

About 20 lines of actual code you need to write. . .

Create an activeX exe (DBNotifier) that defines an object (NotifyClient) that implements the interface

INotifyClient
{
Notify()
}
with an event OnNotify

another singleton object (Manager) implements the interface -

IManager
{
void RegisterClient(INotifyClient client);
void UnRegisterClient(INotifyClient client);
void NotifyClients()
}
During the call to NotifyClients you need to synchronize access to a collection it contains that will hold references to the clients.

Finally an object that (NotifyAgent) implements
INotifyAgent
{
void Notify(long ID)
}
NotifyAgent calls the Managers.NotifyClients Method

In the GUI app, get a reference to the IManager Object, Instance An INotifyClient and register it. Define an OnNotifyEvent to do what ever you want.
Now in the database (I will use northwind for example) attach a trigger of this sort:
=============================

create trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'

EXEC @.hr = sp_OACreate 'DBNotifier.Agent', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=============================

Again. . . dont try in VBSUX, its not worth the effort use a real development environment. Why you might ask? The synchronization is impossible. VB is just a load of cr@.p (have they yet shot the guys who developed it? shoot their mothers too!!!)

Now. . . why is this not implemented natively? Well, because you aren't supposed to remain connected to the database. Its bad design!

Connect, get your data and get out!!!!
Connect, change your data and get out!!!!

|||You can do it using service broker and query notifications mechanisms in SQL Server 2005. It depends on your requirements. As I said, you should check out those features in Books Online.|||

Boy, I could complicate a wet dream!

Its not difficult at all, provided you use Delphi as you can't do it with VBSUX alone because VBSUX is a piece of cr@.p and cant create COM+ event objects. . . Have those guys been shot yet?

Total time of implementation 10 minutes depending on how many SQL objects you want sending notifications!!!

Three parts. . .

● Build, register and install a COM+ Event Object - SQLEvents (this is not hard but you cannot do it in VBSUX!!! Use Delphi!)
● Add triggers to your SQL objects that should initiate the SQLEvents
● Create an ActiveX Event Sink for handling the COM+ Events - SQLEventSink (This can be done in VBSUX!!!)
If you don't have delphi, go home - you suck!

[Part One - Looks like alot, but it takes all of 3 minutes!!!]
1. create a Delphi Active X library, Call it SQLEvents.
2. From the file menu - > Add >Other -> ActiveX -> Automation Object call it SQLEvent with Apartment Threading (no events)
3. From view Menu -> Type Library. . . In the the TypeLibrary editor, add a method to the ISQLEvent interface called Notify that takes a long parameter called SQLObject [see here]
4. Generate the code and call the generated pas file SQLEvents_impl.pas; you dont need to write ANY code!!!!!
5. From the Run Menu -> Register Active X Server (this also builds the DLL)

On the SQL Server machine:
6. Open Component Services and drill down to COM+ Applications. Right Click and select New -> Application - Next -> Empty Application Call it 'SQLEvents' and set Server Application as your activation type -> Applciation Identity Interactive User -> use the default application roles -> dont add any roles -> finish

7. Expand the SQLEvents Aplication folder to the Components folder and right click and select New -> Component -> Next -> Click Install New Event Class(es) and locate and open the SQLEvents.dll you built in step 5. -Next -> Finish

The event object is done. and the tree should look like this when expanded.

[Part 2]
8. Add triggers to the SQL Objects that need to send notifications -

NOTES: My build has a CLSID of "{8FD50E86-203D-4939-9CAE-0F2865C69465}" yours will be different! You can find it out by right clicking the Component installed in step 7 and selecting properties.
Also. this example uses northwind and will trigger events on Update Delete and Insert on the employees table:
=====================================================
CREATE trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'

EXEC @.hr = sp_OACreate '{8FD50E86-203D-4939-9CAE-0F2865C69465}', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=====================================================

Part 3 [you can use VB here, total time 2 minutes!!!]

9. Create a new VB ActiveX DLL Project, save it as SQLEventsSink, Rename Class1 to SQLEventsSink and save the file as SQLEventsSink.cls.

10. From the Projects menu Add a reference to the SQLEvents Lbrary you built in Step 5 above.

11. Add this code to SQLEventsSink:
=================================
Option Explicit
Implements SQLEvents.SQLEvent

Private mSQLObjectID As Long 'local copy
Public Event OnNotify()

Public Property Let SQLObjectID(ByVal vData As Long)
mSQLObjectID = vData
End Property

Public Property Get SQLObjectID() As Long
SQLObjectID = mSQLObjectID
End Property

Private Sub SQLEvent_Notify(ByVal SQLObjectID As Long)
If SQLObjectID = Me.SQLObjectID Then RaiseEvent OnNotify
End Sub
=================================

12. Build the Library and you are done. . . you have an event system!!!

Here's how to use ->

1. Create A New VB Application Project Call it TestApp
2. From the Project Menu, add references to MS Active Data Object 2.6 (minimum), COM+ Admin Library and the SQLEventSink library you built in step 12 above. In the Toolbox add the MS DataGrid 6.0
3. Add a Module, name it globals and add the following code
==============================================
Option Explicit

Public Const CONNECTIONSTRING = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=.\SQL2000"

' NOTE: CHANGE THE FOLLOWING TO REFLECT THE CLSID
' OF THE SQLEvents.SQLEvent OBJECT YOU PREVIOUSLY
' BUILT AND INSTALLED IN COM+

Public Const CLSID_SQLEVENT = "{8FD50E86-203D-4939-9CAE-0F2865C69465}"
==============================================

4. Add a Module, name it ComUtils and add the following code (funny, in delphi it only takes about 6 lines of code to accomplish the same!!! Have I mentioned that VBSUX, sucks?!?)
==============================================
' This method creates a Transient Subscription to a COM+ Component
' Refer to The Windows Platform SDK and Particularly ICOMAdminCatalog
' clsID is the COM+ Event Component to which you are subscribing
' objref is the subscriber
' hostname is the machine on which the COM+ Event Component is registered
' If empty, connection stays on local machine

Public Function CreateTransientSubscription( _
ByVal clsid As String, _
ByVal objref As Object, _
Optional ByVal hostName As String = "") As String
Dim oCOMAdminCatalog As COMAdmin.COMAdminCatalog
Dim oTSCol As COMAdminCatalogCollection
Dim oSubscription As ICatalogObject
Dim objvar As Variant
On Error GoTo CreateTransientSubscriptionError
Set oCOMAdminCatalog = CreateObject("COMAdmin.COMAdminCatalog")
'Connect to the
If hostName <> "" Then oCOMAdminCatalog.Connect hostName
'Gets the TransientSubscriptions collection
Set oTSCol = oCOMAdminCatalog.GetCollection( _
"TransientSubscriptions")
Set oSubscription = oTSCol.Add
Set objvar = objref
oSubscription.Value("SubscriberInterface") = objref
oSubscription.Value("EventCLSID") = clsid
oSubscription.Value("Name") = "TransientSubscription"
oTSCol.SaveChanges
CreateTransientSubscription = oSubscription.Value("ID")
Set oSubscription = Nothing
Set oTSCol = Nothing
Set oCOMAdminCatalog = Nothing
Set objvar = Nothing
Exit Function
CreateTransientSubscriptionError:
CreateTransientSubscription = ""
Err.Raise Err.Number, "[CreateTransientSubscription]" & _
Err.Source, Err.Description
End Function
==============================================

5. Drop a DataGrid control on your form leaving the properties as their defaults. Add a Timer to the form as you need it because VBSUX does not natively support multi-threading. . . Have I told you how much VBSUX sucks? It really does! I wouldn't lie to you!

6. In the Form1 code add the following:
=========================================

Option Explicit
Private WithEvents mSink As SQLEventSink

Private Sub Form_Load()
Set mSink = New SQLEventSink
CreateTransientSubscription _
"{8FD50E86-203D-4939-9CAE-0F2865C69465}", _
mSink
LoadGrid
End Sub

Private Sub mSink_OnNotify()
Timer1.Enabled = True
End Sub

Private Sub Timer1_Timer()
Timer1.Enabled = False
LoadGrid
End Sub

Private Sub LoadGrid()
Dim con As Connection
Set con = New ADODB.Connection
con.Open CONNECTIONSTRING
If DataGrid1.DataSource Is Nothing Then
With con.Execute("SELECT ID FROM SYSOBJECTS O " & _
" INNER JOIN SYSUSERS U ON O.UID = U.UID " & _
" WHERE U.NAME = 'DBO' and O.NAME = 'EMPLOYEES'")
mSink.SQLObjectID = .Fields(0)
.Close
End With
End If
Dim rst As Recordset
Set rst = New Recordset
rst.CursorLocation = adUseClient
Set rst.ActiveConnection = con
rst.Open "SELECT * FROM EMPLOYEES"
Set rst.ActiveConnection = Nothing
con.Close
Set DataGrid1.DataSource = rst
Set con = Nothing
Set rst = Nothing
End Sub
=========================================

7. Run the app -
if your Northwind has not been changed, the name for employeeID 7 should be: King, Robert.

In SQL Query Analyzer, execute:

update employees set FirstName = 'Stephen' where EmployeeID = 7

and "voila!!!" Stephen King automatically appears in your datagrid!!!

References:
ICOMAdminCatalog
Registering a Transient Subscription

Complete Source Code can be found here. Zip also contains a compiled SQLEvents.dll,, just in case you don't have delphi and are still hanging around!

|||1. Don't implement the sink in VBSUX as the VBSUX com object is unstable.
No problems with a sink implemented in delphi. Have I told you that VBSUX is a total piece of CR@.P?

2. You need to enter a critical immediately upon entering the handler. After entering the critical section, null the Sink reference. Reinitialize the sink right after make changes inside the thread that does the response to the notification. So Don't Implement a Sink Client in VBSUX because Critical sections in VBSUX are a total pain in the @.SS - and threads in VBSUX are even worse!!! Have I told you that VBSUX is a PIECE OF CR@.P?

3. Best perfromance is not kicking the COM+ event off in the sql server but in the applciation that makes the change to the database. Immediately after making a change you want to publish, instance a COM+ Event and call Notify.

On a 2.6 P4 H/T w 775mb mem, I had 22 publishers notifying 30 subscribers. . .slow but no blow-ups.

Offline Data Mining

Hi,

I am currently working for a client that is interested in performing some data mining on their customer data. The plan was to build a data warehouse, cubes, RS reports etc. and serve this up over the web.

However, we now have the problem of having to cater for countries with a very low bandwidth where the Reporting Services approach would not deliver the required performance due to the dependency of the solution on the network bandwith.

I am therefore looking into processing offline cubes and copying them over the network as part of the overnight process to a server in the countries.

I noticed that PivotTable Services in 2000 had the CREATE MINING MODEL ...... DDL statement for creating offline mining models.

Is this capability still there with SSAS 2005?

Can anyone post any links to usefull resources on this subject?

Thanks in advance.

There a couple of ways to deal with offline scenarios:

1. AS 2005 does support creation of local mining models if you are connected to a .cub file (in the "local" mode formerly known as PivotTable Services) through the MSOLAP.3 OLE DB provider. Only the Decision Trees and Clustering algorithms are supported in this case. You can then export these models individually, ship the files over to your server and import them back into your server. See http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/129.aspx for more details.

2. If the clients have AS 2005 Standard Edition, they can create models using all algorithms and export as described above. In addition, you can also export server models, ship them over to the client and import them back in, allowing local querying without going over the network.

|||

Yes, the capability is still there in SQL Server 2005.

This page contains some information about client scenarios with Analysis Services 2005, including local mining models: http://msdn2.microsoft.com/en-us/library/ms174518.aspx

More details specific for working with local mining models: http://msdn2.microsoft.com/en-us/library/ms345148.aspx#sqldmprgrm_topic7

The local mining models are limited in functionality. Major limitations:

- only 2 algorithms are supported (decision trees and clustering)

- BI Dev Studio cannot be used to explore local mining models

- server models cannot be exported and then imported in local cubes

|||Good stuff. Thanks for the help guys.sql

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
RegardsJohn wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>sql

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/We...CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
Regards
John wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/We...CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
Regards
John wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/W...&CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
RegardsJohn wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/W...&CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/W...&CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>

Office Web Components & Analysis Services

Hi,
We've been asked to enable an external client to access a cube via OWC.
The idea is that if they play about with the cube through the PivotTable
control, this will enable them to define their requirements for a reporting
system to sit on top of the cube.
I've read and implemented the contents of the following document:-
- http://www.microsoft.com/technet/pr...5/httpasws.mspx
I've installed OWC and the SQL data access components on a test machine, and
have been trying to access the cube through a web-page hosting the pivot
table control.
Data access across domains is enabled in IE
The error returned is "The query could not be processed: The HTTP server
returned the following error: Forbidden."
Obviosuly some security setting somewhere needs to be tweaked ... but which
one :/
Access to the web-page hosting the OWC control will be locked down by IP
address.
Bearing all the above, what is the most straightforward means of enabling
access via the OWC PivotTble control?
Thank you for help.does the XMLA virtual directory is hosted on the SSAS server himself or on
another HTTP server?
what is the security mode of the virtual directory? Anonymous, Basic, or
integrated security?
"craig_amtdatatechnologies@.discussions.mi"
< craigamtdatatechnologiesdiscussionsmi@.di
scussions.microsoft.com> wrote in
message news:075C02A4-F2FC-485C-BBFB-63B55EF261C8@.microsoft.com...
> Hi,
> We've been asked to enable an external client to access a cube via OWC.
> The idea is that if they play about with the cube through the PivotTable
> control, this will enable them to define their requirements for a
> reporting
> system to sit on top of the cube.
> I've read and implemented the contents of the following document:-
> - http://www.microsoft.com/technet/pr...5/httpasws.mspx
>
> I've installed OWC and the SQL data access components on a test machine,
> and
> have been trying to access the cube through a web-page hosting the pivot
> table control.
> Data access across domains is enabled in IE
> The error returned is "The query could not be processed: The HTTP server
> returned the following error: Forbidden."
> Obviosuly some security setting somewhere needs to be tweaked ... but
> which
> one :/
> Access to the web-page hosting the OWC control will be locked down by IP
> address.
> Bearing all the above, what is the most straightforward means of enabling
> access via the OWC PivotTble control?
>
> Thank you for help.
>
>

Office Web Components

Do the interactivity features of OWC client side exist with reporting
services?
Can we replace the work that we do now with OWC and lots of javascript with
RS?I've looked at trying to replicate the pivottable owc in reporting services
but rs just isn't for that, well not without a mega amount of programming.
What part of OWC are you using?
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||No, RS matrix and chart regions are not meant to replace OWC. As you would
understand it is difficult to implement the same functionality in HTML than
using ActiveX controls. However, you can integrate your OWC components to
run RS reports, e.g. when the lowest level in a dimension hierarchy is
reached.
One more thing. In RS OWC are used only when exporting the report to HTML
OWC format. When this happen, RS exports matrix regions as pivot tables,
charts as chart space objects. However, OWC is not linked to the data
source. Instead, the data is always restricted to the data that the report
query returns.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Mike B" <MikeB@.discussions.microsoft.com> wrote in message
news:D7C0A07C-527C-45AD-9ECE-614BBF123027@.microsoft.com...
> Do the interactivity features of OWC client side exist with reporting
> services?
> Can we replace the work that we do now with OWC and lots of javascript
with
> RS?

Off Topic question on Data Protection.

Hello,
I'm a SQL Server dba, recently my parent company took over
another company. This company has asked for a copy of our
client data for statical purposes.
Personally I feel it is against the Data Protection act to
do so. Could someone point my to an English web site so I
can provide my manager with the information ?
Apologies for the off topic.Jan
Information about what? That your boss has asked you to copy the data? Do
you think he/she has no rights to do that?
"Jan" <anonymous@.discussions.microsoft.com> wrote in message
news:8ec701c47882$9044dc10$a301280a@.phx.gbl...
> Hello,
> I'm a SQL Server dba, recently my parent company took over
> another company. This company has asked for a copy of our
> client data for statical purposes.
> Personally I feel it is against the Data Protection act to
> do so. Could someone point my to an English web site so I
> can provide my manager with the information ?
> Apologies for the off topic.
>|||The UK Data Protection Act (1998) concerns information about identifiable
individual people. If this describes the data you are referring to then see:
http://www.informationcommissioner.gov.uk
Of course your company may also have other obligations relating to other
types of data as well. In relation to client data for example, rules on the
use of that data may be defined in your terms of business with that client.
--
David Portas
SQL Server MVP
--|||Thanks for your Replay,
The request came from the other company, and I have been
asked to check on the feasibility of sending the other
company info on our clients.
In answer to your question, our Managing Director asked my
manager.
I will read what David has sent over and see what can be
done.
Thanks
>--Original Message--
>Jan
>Information about what? That your boss has asked you to
copy the data? Do
>you think he/she has no rights to do that?
>"Jan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8ec701c47882$9044dc10$a301280a@.phx.gbl...
>> Hello,
>> I'm a SQL Server dba, recently my parent company took
over
>> another company. This company has asked for a copy of
our
>> client data for statical purposes.
>> Personally I feel it is against the Data Protection act
to
>> do so. Could someone point my to an English web site so
I
>> can provide my manager with the information ?
>> Apologies for the off topic.
>
>.
>|||Thanks David
>--Original Message--
>The UK Data Protection Act (1998) concerns information
about identifiable
>individual people. If this describes the data you are
referring to then see:
>http://www.informationcommissioner.gov.uk
>Of course your company may also have other obligations
relating to other
>types of data as well. In relation to client data for
example, rules on the
>use of that data may be defined in your terms of business
with that client.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>sql

Oddball SQL calculation query - any Gurus up to this?

OK, so I have a situation where the Client gets money from activites based on mileage. The further from 'home', the greater the mileage rate is. The distance per stopover is measured from wither home, or the last stopover, making it a continuous interval.

Dependinig on the load size, the rates will increase or decrease as well.

Assume the following are skeleton table structures.

Table structures assume that appropriate keys are in place as 'Keyfields':

Table1Fields: <Table one holds the actual user-entered data per Size>

Key Keyfield(s), <keys to Table2's Keyfields>

Size Varchar,

DistanceFrom Number, (all distances are in miles)

DistanceTo Number,

Table2 Fields: <Table2 is the table holding the dollar rates per distance interval>

Key Keyfields(s),

Size Varchar,

DistanceFrom Number,

DistanceTo Number

Rate Number (dollar rate per mile )

Business rules:

1. The Table1 data may contain entries such as:

Size = 'A', DistanceFrom = 0, DistanceTo = 110

Size = 'A', DistanceFrom = 110, DistanceTo = 150

Size = 'B', DistanceFrom = 150, DistanceTo = 225, etc.

Note that the distance numbers are continuous, no breaks or gaps. Also note the Size B spans the interval in Table2

2. Table2 always contains unchanging intervals (ie: a lookup table) such as:

Size = 'A', DistanceFrom = 0, DistanceTo = 100, Rate = $10

Size = 'A', DistanceFrom = 100, DistanceTo = 200, Rate = $20, etc...

Size = 'B', DistanceFrom = 0, DistanceTo = 100, Rate = $15

Size = 'B', DistanceFrom = 100, DistanceTo = 200, Rate = $25

Size = 'B', DistanceFrom = 200, DistanceTo = 300, Rate = $30, etc...

Note that the distance numbers are continuous, no breaks or gaps. There may be from one to many rates per size (ie: could only have one interval defined, such as from 0 to 2000 miles, or several intervals, depending on the contract).

3. The max Table1.DistanceTo will never exceed the max Table2.DistanceTo.

4. The object is to calculate the total cost per Table1 intervals, per size given in Table1, using the lookup values in Table2. For example, the total cost for the data in Table1 would be:

Size A, 0 to 100 times $10 per mile = $1000 - plus -

Size A, 100 to 110 times $20 per mile = $200 - plus - (notice that the interval spanned that in Table2, so have to calc using next rate)

Size A, 110 to 155 times $20 per mile = $800 - plus -

Size B, 150 to 200 times $25 per mile = $1250 -plus -

Size B, 200 to 225 miles times $30 per mile = $750 (ditto, the interval spanned Table2 interval)

Total: Size A = $2000

Size B = $2000

===============================

Issue: How do I program this using SQL and not resorting to cursors (curses!)?

I've tried CASE statements, but keep getting bogged down in classic programming mush (ie: if-then thinking). My 'set' hat is crooked today!

Any ideas, given the business rules?

Thanks !

G.

Ok, that's a lot of information. Can you build a table, populate it with data and give us your desired results?|||

Thanks for reply.

My post has data for the simplest table, Table1 being the 'real-world' style of data I get. The calculations shown are for the data in Table1.

Table2 is the kind of lookup rates actually used.

G.

|||

Ok, but could you build something like:

CREATE TABLE table1
(
columns...
)

INSERT INTO table1
...

CREATE TABLE table2
(
columns...
)

INSERT INTO table2
...

I want to see:

table1.column table2.column
- --
x y
x z
z y

This would be really helpful to make us understand your requirements (and to make writing the query easier.)

|||

I believe that I have understood the problem you are trying to solve.

I worked my own example which is shown below. My approach was to first set up the join using the rule for overlapping -- t1.from < t2.to and t2.from < t1.to. Next, the calculation uses the min( t1.to, t2.to ) minus max( t1.from, t2.from ). This is the distance subject to the rate. You can see that I first did the calculation with extra fields then applied the sum.

drop table #table1
drop table #table2

create table #table1(
loadSize char(1) not null,
fromDistance int not null,
toDistance int not null
)

create table #table2(
loadSize char(1) not null,
fromDistance int not null,
toDistance int not null,
rate numeric(10,2) not null
)

insert #table1 values( 'A', 0, 110 )
insert #table1 values( 'A', 110, 200 )
insert #table1 values( 'A', 200, 2000 )
insert #table1 values( 'B', 0, 1100 )
insert #table1 values( 'B', 1100, 2000 )

insert #table2 values( 'A', 0, 100, 10.0 )
insert #table2 values( 'A', 100, 500, 20 )
insert #table2 values( 'A', 500, 2000, 15 )
insert #table2 values( 'B', 0, 2000, 30 )

select * from #table1
select * from #table2

Select t1.loadSize,
t1.fromDistance,
t1.toDistance,
t2.fromDistance,
t2.toDistance,
-- min( t1.toDistance, t2.toDistance )
(Case when t1.toDistance < t2.toDistance then t1.toDistance else t2.toDistance end
-
-- max( t1.fromDistance, t2.fromDistance )
Case when t1.fromDistance < t2.fromDistance then t2.fromDistance else t1.fromDistance end
)
*
rate
From #table1 t1
Join #table2 t2
On t1.loadSize = t2.loadSize
And t1.fromDistance < t2.toDistance
And t2.fromDistance < t1.toDistance

Select t1.loadSize,
t1.fromDistance,
t1.toDistance,
Sum(
-- min( t1.toDistance, t2.toDistance )
(Case when t1.toDistance < t2.toDistance then t1.toDistance else t2.toDistance end
-
-- max( t1.fromDistance, t2.fromDistance )
Case when t1.fromDistance < t2.fromDistance then t2.fromDistance else t1.fromDistance end
)
*
rate)
From #table1 t1
Join #table2 t2
On t1.loadSize = t2.loadSize
And t1.fromDistance < t2.toDistance
And t2.fromDistance < t1.toDistance
Group
By t1.loadSize,
t1.fromDistance,
t1.toDistance

|||

Thanks!

I'll get to try this out tomorrow and let U know what happens - looks like what I need essentially.

G.

sql

Odd Rights Issue

SQL Server 2000 Server at SP3a
WinXP SQL Server 2000 Client at SP4
Win2K SQL Server 2000 Client at SP3a
Identical local user account setup on both clients using peer-to-peer
windows authentication to server.
The account on Win2K client at SP3a can successfully open pre-existing
transformation tasks in DTS.
The account on WinXP client at SP4 cannot open pre-existing transforamtion
tasks in DTS (but can create new ones okay).
What gives? Thanks for any help anyone can offer!
ChuckI forgot to include the actual error message in WinXP client:
Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : [DNETLIB][ConnectionOpen (Connect()).]SQL Server
does
not exist or access denied.
WinXP client has no difficulty registering the server and doing other
functions. Since exact same account works on another client, leads me to
believe something about either XP or SP4 that's causing the problem. Can
SP4 cleint connect to SP3a server?
"CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
news:eZItbHLrFHA.2896@.TK2MSFTNGP10.phx.gbl...
> SQL Server 2000 Server at SP3a
> WinXP SQL Server 2000 Client at SP4
> Win2K SQL Server 2000 Client at SP3a
> Identical local user account setup on both clients using peer-to-peer
> windows authentication to server.
> The account on Win2K client at SP3a can successfully open pre-existing
> transformation tasks in DTS.
> The account on WinXP client at SP4 cannot open pre-existing transforamtion
> tasks in DTS (but can create new ones okay).
> What gives? Thanks for any help anyone can offer!
> Chuck
>|||I figured it out -- the problem client was missing a cleint network alias
for the server. Problem had nothing to do with XP or SP4.
"CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
news:OxYvzKLrFHA.716@.TK2MSFTNGP10.phx.gbl...
> I forgot to include the actual error message in WinXP client:
> Error Source : Microsoft OLE DB Provider for SQL Server
> Error Description : [DNETLIB][ConnectionOpen (Connect()).]SQL Serv
er does
> not exist or access denied.
> WinXP client has no difficulty registering the server and doing other
> functions. Since exact same account works on another client, leads me to
> believe something about either XP or SP4 that's causing the problem. Can
> SP4 cleint connect to SP3a server?
>
> "CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
> news:eZItbHLrFHA.2896@.TK2MSFTNGP10.phx.gbl...
transforamtion[vbcol=seagreen]
>

Odd connection problem

I (running SQL 7 client only) cannot connect to the
server via my machine, but CAN connect (after installing
client) to the server from ANY other machine on the LAN.
My SysAdmin and I have been workin on this for days but
have come up empty, any ideas?
Could it be some other software I'm running?
Specs:
Sql7 client attempting to connect to server fails via
client tools and isql command line...
client = XP Pro connected via LAN to server. Can ping
server... have re-installed client software multiple
times.
rcause,
What error message do you get when connecting from osql.exe? Do you have an
aliases set up in your client configuration utility?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
|||Mark,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionOpen (Connect()).
That's the osql error message.
Do not have alias set up?
Thanks
>--Original Message--
>rcause,
>What error message do you get when connecting from
osql.exe? Do you have an[vbcol=seagreen]
>aliases set up in your client configuration utility?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rcause" <rcausey@.med.unc.edu> wrote in message
>news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
installing[vbcol=seagreen]
LAN.
>
>.
>
|||Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004
|||Hi ricky
just goto the services in your client mechine.and find out the sql srevre services.
then if they are stop then start.
if they are start then go to the properits.
and go to the logon and check the authentication to local system
biswajit das
-- Ricky Artigas wrote: --
Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004

Odd connection problem

I (running SQL 7 client only) cannot connect to the
server via my machine, but CAN connect (after installing
client) to the server from ANY other machine on the LAN.
My SysAdmin and I have been workin on this for days but
have come up empty, any ideas?
Could it be some other software I'm running?
Specs:
Sql7 client attempting to connect to server fails via
client tools and isql command line...
client = XP Pro connected via LAN to server. Can ping
server... have re-installed client software multiple
times.rcause,
What error message do you get when connecting from osql.exe? Do you have an
aliases set up in your client configuration utility?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.|||Mark,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionOpen (Connect()).
That's the osql error message.
Do not have alias set up?
Thanks
>--Original Message--
>rcause,
>What error message do you get when connecting from
osql.exe? Do you have an
>aliases set up in your client configuration utility?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rcause" <rcausey@.med.unc.edu> wrote in message
>news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
installing[vbcol=seagreen]
LAN.[vbcol=seagreen]
>
>.
>|||Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004|||Hi ricky
just goto the services in your client mechine.and find out the sql srevre se
rvices.
then if they are stop then start.
if they are start then go to the properits.
and go to the logon and check the authentication to local system
biswajit das
-- Ricky Artigas wrote: --
Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004

Odbcping

Hye,
I have client tools only, install on my computers. and I wolud like to use
odbcping from my machin. does it possible to install the odbcping, because
right now i dont have it install on my computer.
Regards,
Dubka.
The odbcping utility is no longer installed by SQL Server 2000 Setup.
If you need to run odbcping utility you can run it from the x:\x86\Binn
directory on the SQL Server 2000 compact disc, or manually copy it to your
computer.
Cristian Lefter, SQL Server MVP
"??" <@.discussions.microsoft.com> wrote in message
news:B10B69C9-D831-405C-AFB0-BA58B37A282B@.microsoft.com...
> Hye,
> I have client tools only, install on my computers. and I wolud like to use
> odbcping from my machin. does it possible to install the odbcping, because
> right now i dont have it install on my computer.
> Regards,
> Dubka.