Datasource to AnalysisServices

I have a question regarding creating a datasource that connects to an
Analysis Services Database. (AS and RS are both 2005 editions.)
If I create a (shared) datasource to an Analysis server, and Specify the IP
address (10.200.23.24) of that server, I have no problems.
However, if I want to connect via HTTP, I am having a few problems.
-In the connection properties, I specify the Server Name to the HTTP url
(http://10.200.23.24/OLAP/msmdpump.dll)
-I specify a username and password with permissions to see the cubes and
data.
-I am able to select the database name from the dropdown list. (So,
obviously it is hitting the AS server, and the AS server is sending back the
databases in which I have permission to see.)
-If I select test connection, it initially fails, but if I go to the
advanced options, and remove the SSPI, test connection succeeds.
-Press OK to save the datasource, and run (preview) the report.
When I go to run (preview) the report, I get the following error:
"Non-Windows user credentials were supplied for a non-http connection to
Analysis Services. The Microsoft SQL Server Analysis Services data extension
requires Windows Integrated Security unless connecting via http or https"
I don't understand why I am getting that error when I am connecting via
http. Is there another property somewhere I have to turn on, so that it
knows I am using http?
As a side note, I know the HTTP connection works, as I can connect via HTTP
with both proclarity, and management studio. The problem only seems to be in
reporting services.
Any ideas?
-RyanHello Ryan,
What credetial mode you chose in the shared data source?
And have you follow this article to configure the HTTP access to AS 2005?
Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft
Windows Server 2003
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||The credentials are set at "Use a specific username and password".
I have the username of a local machine account on the server, and it's
password.
I've also tried the username in the format of MACHINENAME\UserName. That
didn't work either.
I know the http is set up properly, as I can access the cubes on that server
via HTTP with proclarity, and management studio, so I do not believe that is
the issue.
Does it have anything to do with the fact the report was designed with the
datasource pointing to a direct tcp connection, and then wanting to change
it to HTTP after the fact?
-Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:rvJmMbUNHHA.2300@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> What credetial mode you chose in the shared data source?
> And have you follow this article to configure the HTTP access to AS 2005?
> Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft
> Windows Server 2003
> http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Here's a little bit more information:
The "Type" of the datasource is set as "Microsoft SQL Server Analysis
Services" . This is where I am having the issue where it works fine with tcp
connection, but HTTP connection gives the error.
If I change the type to OLEDB, and build a connection string to connect to
my cubes, when I preview the report, I get no errors.
However, nothing shows up for any of the queries. Warnings that say:
"The data set 'DatasetName' contains a definition for the Field 'FieldName'.
The field is missing from the returned resultset from the datasource."
Followed by:
"The data set 'DatasetName' contains a definition for the Field 'FieldName'.
The data extension returned an error during the reading of the field. THere
is no data for the field at position 5."
Further, I watched the profiler in analysis services, and it looks like the
right queries are being executed.
My questions are:
1. Do I have to use an OLEDB connection to use HTTP?
-If so, how do I get rid of the above errors? (Are they due to the fact
the datasets were designed with the analysis services connection type, then
switched to oledb?)
2. If I can use HTTP on the direct "Microsoft SQL Server Analysis Services"
connection type, is there anything else (advanced properties) that needs to
be set to make it work?
3. Has anyone successfully created a report using TCP on the direct
provider, then tried switching it to HTTP after the report was complete?
Thanks,
-Ryan|||Hello Ryan,
The reason why you could not connect to the Analysis Service Via http in
Reporting Services is because of the credential.
If you have enabled the Integrated Authentication in IIS, you need to use
the domain account. Or you need to specify a same account in the server as
the one you use in the local machine.
Please try this and let me know if this could not resolve your issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

DataSource has 2 instances that don't exist

Using VS05 SP1 Pro SQL Express…

In converting a program from VS03/MSDE, long ago I installed SQL 2005 Express with Advanced Services and everything was fine.Windows Update has recently been failing to install SQL 2005 Express with Advanced Services SP2.After no answers from the forums, and logs that were of no help, I decided to uninstall SQL 2005 Express with Advanced Services and reinstall SQL 2005 Express (basic version).As a test run, I did it on a desk top, no problems what so ever.

However, on my laptop, it has been a nightmare.

Question 1) in control panel, add/remove programs, all of SQL 2005 Express with Advanced Services uninstalled fine except one item - I am unable to uninstall MS SQL 2005 Backward Compatibility, getting the error message

A network error occurred while attempting to read from the file

c:\windows\installer\sqlserver2005_bc.msi

That file is not on the hard disk.

After installing SQL 2005 Express (the basic version), this problem persists and I cannot uninstall MS SQL 2005 Backward Compatibility for the same reason.

How can I get rid of MS SQL 2005 Backward Compatibility?

Question 2) Windows update still is trying to download and install SQL 2005 Express with Advanced Services SP2, even though I have uninstalled it.How to manually get rid of whatever settings are triggering this?

Thanks!

Bob

Using VS05 SP1 Pro SQL Express…

Server Explorer, Add Connection, Advance Settings Button, DataSource lists 3 options:

.\SQLEXPRESS

.\CSoltuion

.\MSSQLSERVER

I am only interested in the first one, SQLEXPRES, and would like to remove the other two (MSSQLSERVER is actually disabled because if you try to start it you get an error message The request failed or the server did not respond in a timely fashion).In Control Panel, Add/Remove Programs, MS SQL Server 2005, Remove Button, it gives me the option to remove only

CSoltuion

SQLEXPRESS

If I choose CSoltuion to remove, it immediately does nothing and goes back to the add/remove screen.If I choose Remove for MS SQL Server 2005, CSoltuion is still there.

How do I get rid of

.\CSoltuion

.\MSSQLSERVER

Thanks!

Bob

|||

Using VS05 SP1 Pro SQL Express…

Question 1) Is the only way to add a new named SQL instance to run the SQL Express (download file SQLEXPR32.EXE) and choose the name where it asks you to? This is incredibly time consuming and often you spend a lot of time to have it fail.

Question 2) In Add/Remove programs MS SQL Server 2005, I have two named instances

SQLEXPRESS

CSoltuion

If I choose either of them to be removed, nothing happens, and it instantly returns to the add/remove screen and they are still there. They are not listed as services under manager computer.

If I try add them by running SQLEXPR32.EXE, they are not listed as existing named instances (there is a button to check that before you choose a name), but when I get to the end (again, much time wasted), the install fails saying the named instance already exists.

How do I get rid of these two named instances?

Question 3) The only way I have been able to get a named instance to work is to choose a name that has never been used before, such as SQLForTheFirstTime, which is ridiculous.

I want a named instance only of SQLEXPRESS…How do I do that?

Thanks

Bob

|||

I'm looking into this Bob. In the future, please don't post the same question multiple times, it doesn't get you an answer any faster.

Mike

|||

Hi Bob,

Question 1) Is the only way to add a new named SQL instance to run the SQL Express (download file SQLEXPR32.EXE) and choose the name where it asks you to? This is incredibly time consuming and often you spend a lot of time to have it fail.

Yes, each named instance is a completely separate copy of SQL Express. There is no way to get a separate copy other than to install it.

Question 2) In Add/Remove programs MS SQL Server 2005, I have two named instances

SQLEXPRESS

CSoltuion

If I choose either of them to be removed, nothing happens, and it instantly returns to the add/remove screen and they are still there. They are not listed as services under manager computer.

If I try add them by running SQLEXPR32.EXE, they are not listed as existing named instances (there is a button to check that before you choose a name), but when I get to the end (again, much time wasted), the install fails saying the named instance already exists.

How do I get rid of these two named instances?

So you're seeing Microsoft SQL Server 2005 in Add/Remove Programs, and the report lists the two instances you've named, but they don't uninstall. When you say they are not listed under Manage Computer, you're talking about the list of Services, right? It seems like you've already tried to remove these instances from your computer but something went wrong during the uninstall, do you remember removing these previously before noticing the failure you've described?

I have some ideas for getting rid of these, but it will royally mess up SQL Server if you actually have it installed. We need to be sure these really aren't there. Are there any SQL Tools on the Start menu? For example, can you find Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager? If it's there, launch it and see if it lists any instances of SQL Server and check their state. (Started, Stopped, etc.) If they are stopped, try to start them.

If there is nothing on the Start menu and/or you can't start any of the instances on your computer, you can try using the Windows Installer Cleanup Utility. This doesn't actually remove programs from your computer, it just removes the entries for the program in the windows installer registry so that windows installer doesn't think it's installed. This will really break any SQL Components that you do have installed, so be carful. Run the utility and try to remove the entry for Microsoft SQL Server 2005. Once you've done that, try installing again.

It's possible that you could still have problems based on SQL registry entries, but lets cross that bridge when we get to it.

|||

As to your first paragraph, yes to everything.

Below is in chronological order after reading your message...

SQL Server Management Studio Express, Server Name, Browse For Servers shows the following local servers

(all prefaced by local machine name)

MICROSOFTSMLBIZ (for Outlook Business Contact Manager, MSDE Format I think)

MICROSOFTBCM (for Outlook Business Contact Manager, MSDE Format I think)

(MACHINE NAME ONLY)

SQLEXPRESS

CSOLUTION

SQLTEST

SQLEXPRESSDATA (this is the only one that works)

SQL Server Configuration Manager

SQL Server 2005 Services shows

SQLEXPRESS

SQLTEST

MSSQLSERVER

SQLEXPRESSDATA (this is the only one that works)

SQL Server 2005 Network Configuration shows

CSOLUTION (note this is not shown above)

SQLEXPRESS

SQLTEST

MSSQLSERVER

SQLEXPRESSDATA (this is the only one that works)

I then ran the Windows Install Clean up on

CSOLUTION

SQLEXPRESS

The only other one listed was

SQLEXPRESSDATA

Which I did not run it on because it is the only one working.

In add remove programs SQL 2005 remove button shows

CSOLUTION

SQLEXPRESS

SQLEXPRESSDATA

I clicked the radio button forSQLEXPRESS to remove it, and it tried to remove SQLEXPRESSDATA (the one that works), so I cancelled. I confirmed SQLEXPRESSDATA still works.

In computer manager services, it shows the following (note that the ones that did not start did not show before running Windows Install Cleanup)

SQLEXPRESS

SQLEXPRESSDATA (this is the only one that starts).

SQLTEST

MSSQLSERVER

SQL Server Management Studio Express and SQL Server Configuration Manager show the same thing after running Windows Install Cleanup.

The only way I have found to install a new instance is to use a name that has never been used before.

Also, when you create a new named instance, C:\Program Files\Microsoft SQL Server\MSSQL.4 starts at 4 (why does it not start at 1?) . SQLEXPRESSDATA (the only one that works) uses MSSQL.4

Where do we go from here? It looks like a real mess.

Thanks for your help.

Bob

|||

Quick question on these forums -

can you Alt_PrtScrn and Paste a dialog box into a forum, without having to type it all out like it did above, to show you what you are asking for?

Bob

|||

Try this:

Remove –Windows Installer Clean-Up Tool
http://download.microsoft.com/download/E/9/D/E9D80355-7AB4-45B8-80E8-983A48D5E1BD/msicuu2.exe

|||

Hi Bob,

Answer to quick question: No, you can't paste graphics into the forum posts, I wish you could.

Since all the standard ways to remove SQL Express seem to be failing we'll have to go a bit more old school. Follow the instructions in this KB to manually remove SQL from the machine. You may need to do some of the steps for each instance you're wanting to get rid of. This should get you back to a clean state that will allow you to install the default named instance of SQL Express.

Mike

|||

Based on the document, there were two options, here are the results:

Option 1 - Ran ARPWrapper.exe /Remove on CSolution, it did nothing. ARPWrapper.exe /Remove is the same as runing via the add/remove programs.

Option 2 - Looked at each GUID under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall, there was no Microsoft SQL entry.

No other approches in the document.

If I do a search on CSolution in the registry, there are entires, but I am hesitant to just start deleteing any I find.

Any other ideas?

Thanks

Bob

|||

Hi Bob,

Where are you finding the entries for CSolution, and for that matter, SQLEXPRESS? Don't start deleting just yet, I'm discussing this with our setup team to get the right solution.

Mike

|||

You can also find the product codes if you look in the registry under the InstId keys, located in a path like this:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.*\Setup

There will be one MSSQL.* key for each instance, numbered probably in order. Something like MSSQL.1, MSSQL.2, and so on.

|||

Under VS03, the name of the solution was CSolution as well as the name of the SQL MSDE Instance.

When we upgraded to VS05/SQL Express, the name of the solution was changed to CSolution_Telephony and the SQL instance was first left at Csolution but then changed to SQLExpress (without CSolution being uninstalled).Everything worked fine for a long time.

All the SQL problems started when Windows Update could not install the SP2 for SQL Express Advanced, so we uninstalled it and reinstalled the basic SQL Express.We are now using a named instance of SQLExpressData which works fine.

This is a development machine, so it has VS05 Pro and VB express on it.

Registry Search for CSOLUTION

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\CSOLUTION

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MSSQL$CSOLUTION

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\SQLAgent$CSOLUTION

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\SQLCTR$CSOLUTION

Registry Search for SQLEXPRESS

My Computer\HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\tools\ShellSEM\DataProject

My Computer\HKEY_CURRENT_USER\Software\Microsoft\VBExpress\8.0\DataProject

My Computer\HKEY_CURRENT_USER\Software\Microsoft\VBExpress\8.0\Find

My Computer\HKEY_CURRENT_USER\Software\Microsoft\VWDExprss\8.0\DataProject

Thansk!

Bob

|||

Here are two more that did not appear in the find, but where there when I looked at these specific places based on another post in this thread.

Registry for CSOLUTION

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2

Registry for SQLEXPRESS

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1

This is an odd one…

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.5 (no value set).

Only MSSQL.4, which is SQLExpressData, appear as folders in Program\MS SQL SERVER

Thanks,

Bob Day

|||

Hi Bob,

Per R. Greens instructions, take a look at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.*\Setup. The * in this case would be 2 for CSOLUTION and 1 for SQLEXPRESS. You shoud be able to find the Product Code listed there and use that product code with msiexec as described in the KB article.

Let us know how what happens.

Mike

sql

Datasource error

Hi, i've a problem with calling a report created with reporting service; i get this error :System.Web.Services.Protocols.SoapException : Impossible find datasource...... ( i translate from italian ) I'm sure ip i wrote is right , and , moreover, any ip datasource i put ,i get the same error. Thank you in advance for your help.

Hi,

From your description, it seems that you are using WebService as your datasource of your report, right?

And the error "System.Web.Services.Protocols.SoapException" indicates that the WebService failed to return the result which your report expected.

So I suggest that you should try to check your WebService to see if it can work correctly, we suppose that it returns a dataset typed datasource, then you can just write a simple project, remove the reporting service part, try to see if you can retrieve the return properly.

Thanks.

|||Hi Nai-Dong,
I use the webservice as result, distribuiting report I've developed with SQL Server Development intelligence studio.
I'm doing this for the first time , I'm studying how to use SQLServer reporting service.
The webservice "seems" to me that works properly because I see exposed all the methods, and moreover ,
calling the other methods that don't involve datasource , the report is correctly called.
I'm using XP .
Thank you.|||

Hi,

The webservice "seems" to me that works properly because I see exposed all the methods, and moreover , calling the other methods that don't involve datasource , the report is correctly called.

Well, if so. I think you should try to check if the database is available from the machine which host the web service. Another problem is the account. You should try to check if the account which runs the webservice exist in the web service identity of the report server. Also, try to check if the account which runs the webservice (ASPNET or IUSR_MachineName) has to permission to access the reporting service database. If not, add the account into the users of that database.

Besides, I suggest you to refer the following link, it may be helpful to you.

http://msdn2.microsoft.com/en-us/library/aa179578(SQL.80).aspx

Thanks.

DataSource and more than one recordset

I've a report that use a stored procedure as datasource.
This s.p. return 2 recordset. How to access the second recordset in
reporting services? (like Recordset.NextRecordset in VB)
--
OSVALDO COLITTIRS does not support more than one recordset being returned.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>|||Bruce is correct in that the native Data Processing Extensions do not
support stored procedures that return more than one recordset - what happens
is that the first recordset is all that your Reporting Services DataSet will
see.
However, you can certainly create a custom Data Processing Extension, and
could then gain access to the other recordsets returned by a stored
procedure in a report. Its not optimal and the best way forward is usually
to re-design the report logic if this is at all possible. Typically what you
need to do in your Data Processing Extension is archive off the recordsets
into a tables that you can then pull them down from seperately for other
Report DataSets in the Report.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>

Datasource and Catalog

Hi all,

I accidentally deleted the datasource including the root data source and catalog from reporting services running in SQL server 2005. As a result, my reports didn't work. What should I do? Thanks!

lk_spec

Create a new datasource and link the report to the new datasources. What do you mean by catalog ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for you reply!

The data source was created dynamiclly through web services and SOAP. Before I deleted the data source, my reports worked fine. After that, however, it didn't work. I wonder if it's related to the root data source or something. Any help is appreciated!

|||The datasource is saved in the RDL as:

<rd:DataSourceID>e80dd27e-83d3-475c-b4c7-81261ee98f08</rd:DataSourceID>

with an ID, I don′t know if you can recover that. I you have the chance to reploy everything, I would go this way.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jen,

Where did I find the datasource id. I checked the rdl file, but there wasn't such information. I'm really new at this stuff.

|||Did you have a view on the RDL in VS or the RDL in the Report Manager, you can download the RDL in Report Manager by Opening the report > Properties > Edit Report, you will get the RDL file downloaded which contained (in my case) this GUID.

HTH, Jens Suessmeyer.

http.//www.sqlserver2005.de|||

Jen,

Thanks for your reply!

I was able to create a new data source. The problem now is, however, an error message saying that the item "/" cannot be found. The application failed when it tried to FindCatalogItems:

rsws.FindItems(folderName, BooleanOperatorEnum.Or, conditions)

Any help would be appreciated!

lk_spec

|||

If you take regular backup of database than restore the database.

Dataset Window

Could someone please tell me how to bring up the dataset window in
Reporting Services so I can drag and drop the fields from the dataset?
I've searched high and low in the report designer and cannot find that
option anywhere. I am working with Reporting Services 2000.
Thank you.In Visual Studio, on the menu bar, select the View menu. When you have
a report RDL open, you should be able to see the "Datasets" option
under the View menu, usually towards the bottom of the menu.
Otherwise, try the "Other Windows >" option for more submenu options.
Regards,
Thiago Silva
Nergock@.gmail.com wrote:
> Could someone please tell me how to bring up the dataset window in
> Reporting Services so I can drag and drop the fields from the dataset?
> I've searched high and low in the report designer and cannot find that
> option anywhere. I am working with Reporting Services 2000.
> Thank you.

Dataset Refresh...

Hi Folks,
I have a dataset in Reporting Services which points to an stored procedure.
This SP returns all data. Then the data is filtered in the dataset based on
the report parameters is choosen.
1. Will the dataset be refreshed every time i hit the View Report button or
will it get refreshed only the first time the view report is choosen and
would have the data cached till the session ends.
2. Is it better to do the filtering of data in the SP by passing parameters
from the report or is it better to use filters on a dataset.
Thank you
RamdasThe dataset gets refreshed each time you click on View Report with one
important note. In development the designer caches the data and only
refreshes if the parameter changes. If deployed to the server it refreshes
every time.
You are much better off to filter with the query or the SP. I pretty much
never use report filters. The very best thing to do for performance is have
the database return only those records you want.
In RS 2005 all rendering operations are done in RAM. If you have a large
resultset you will be using up a lot of your server's resources.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> Hi Folks,
> I have a dataset in Reporting Services which points to an stored
> procedure.
> This SP returns all data. Then the data is filtered in the dataset based
> on
> the report parameters is choosen.
> 1. Will the dataset be refreshed every time i hit the View Report button
> or
> will it get refreshed only the first time the view report is choosen and
> would have the data cached till the session ends.
> 2. Is it better to do the filtering of data in the SP by passing
> parameters
> from the report or is it better to use filters on a dataset.
> Thank you
> Ramdas
>|||Hi Bruce,
Thank you for the insight.
One more question, is it possible to refresh the report without clicking the
View Report button.
Thank you
"Bruce L-C [MVP]" wrote:
> The dataset gets refreshed each time you click on View Report with one
> important note. In development the designer caches the data and only
> refreshes if the parameter changes. If deployed to the server it refreshes
> every time.
> You are much better off to filter with the query or the SP. I pretty much
> never use report filters. The very best thing to do for performance is have
> the database return only those records you want.
> In RS 2005 all rendering operations are done in RAM. If you have a large
> resultset you will be using up a lot of your server's resources.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> > Hi Folks,
> > I have a dataset in Reporting Services which points to an stored
> > procedure.
> > This SP returns all data. Then the data is filtered in the dataset based
> > on
> > the report parameters is choosen.
> >
> > 1. Will the dataset be refreshed every time i hit the View Report button
> > or
> > will it get refreshed only the first time the view report is choosen and
> > would have the data cached till the session ends.
> >
> > 2. Is it better to do the filtering of data in the SP by passing
> > parameters
> > from the report or is it better to use filters on a dataset.
> >
> > Thank you
> >
> > Ramdas
> >
>
>|||If you have a report that you want to refresh automatically then you can set
that in the dataset tab. Click on the ...
For instance, I have a report that refreshes every 5 minutes automatically.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:D205F7FF-A85D-4ACD-95EF-D68050FA3EFE@.microsoft.com...
> Hi Bruce,
> Thank you for the insight.
> One more question, is it possible to refresh the report without clicking
> the
> View Report button.
> Thank you
> "Bruce L-C [MVP]" wrote:
>> The dataset gets refreshed each time you click on View Report with one
>> important note. In development the designer caches the data and only
>> refreshes if the parameter changes. If deployed to the server it
>> refreshes
>> every time.
>> You are much better off to filter with the query or the SP. I pretty much
>> never use report filters. The very best thing to do for performance is
>> have
>> the database return only those records you want.
>> In RS 2005 all rendering operations are done in RAM. If you have a large
>> resultset you will be using up a lot of your server's resources.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ram" <Ram@.discussions.microsoft.com> wrote in message
>> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
>> > Hi Folks,
>> > I have a dataset in Reporting Services which points to an stored
>> > procedure.
>> > This SP returns all data. Then the data is filtered in the dataset
>> > based
>> > on
>> > the report parameters is choosen.
>> >
>> > 1. Will the dataset be refreshed every time i hit the View Report
>> > button
>> > or
>> > will it get refreshed only the first time the view report is choosen
>> > and
>> > would have the data cached till the session ends.
>> >
>> > 2. Is it better to do the filtering of data in the SP by passing
>> > parameters
>> > from the report or is it better to use filters on a dataset.
>> >
>> > Thank you
>> >
>> > Ramdas
>> >
>>

Dataset Query IN Clause issue

Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
want to use a variable in my IN clause that that holds the different
values (strings) but I can't seem to get it to work. I've posted the
SQL statement as it currently is and what I tried to do to get it to
work.
Thanks for any help in advance.
Mike
Current Statemenet:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotos
What I tried:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotosMike,
The string variable is treated as a single data element, not a list or array
of elements. So, if:
SET @.PhotoSet = '1,23,56,78'
Your query would only return a row with the value '1,23,56,78' in
vewStatsInnerSummary.Batch.
You can slice up the string into a temporary table and then use that table
in your query, or use dynamic SQL, etc.
Vyas has a write up on your issue.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
RLF
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>|||The type of (Reporting Services) parameter you are using is for a
multivalued parameter. It would show up as a drop down with several values,
each value having a checkbox next to it. Then, whatever values are checked
will be in your "IN" clause. The top checkbox is used for "Select All".
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>

Dataset Priority

I'm new to reporting services and have a problem that I need help with.
Is there a way to set Dataset priority?
My problem is this. I have two datasets on my report that produce data
to display.
Dataset1(DS1) is a Stored procedure that returns a recordset.
DS2 is also a procedure that returns data.
DS1 creates a detailed set of records for a specific entity. While
doing this it runs 10-20 views and Procs (based on User Settings). In
order for DS2 to be correct, DS1 needs to be completely done before
the call to DS2 is made.
When running the report with SQL Profiler, I see that DS1 before DS2,
but DS2 is completing sooner.
Creating a LINKED sub-report for DS2 is not a viable alternative.
Any help would be greatly appreciated.
RUMAGEtry this:
create a parameter for the second SP, then setup the value of this parameter
from the SP1 result.
RS will evaluate the first SP, then execute to the second SP because there
is a parameter dependency.
"jrumage" <jeff@.grinandgiggle.com> wrote in message
news:1137429946.779314.249040@.g14g2000cwa.googlegroups.com...
> I'm new to reporting services and have a problem that I need help with.
> Is there a way to set Dataset priority?
> My problem is this. I have two datasets on my report that produce data
> to display.
> Dataset1(DS1) is a Stored procedure that returns a recordset.
> DS2 is also a procedure that returns data.
> DS1 creates a detailed set of records for a specific entity. While
> doing this it runs 10-20 views and Procs (based on User Settings). In
> order for DS2 to be correct, DS1 needs to be completely done before
> the call to DS2 is made.
> When running the report with SQL Profiler, I see that DS1 before DS2,
> but DS2 is completing sooner.
> Creating a LINKED sub-report for DS2 is not a viable alternative.
> Any help would be greatly appreciated.
> RUMAGE
>|||Thats a good idea, however it doesn't work the way you might thinkI
Doing that causes the first proc(DS1) to run twice.
First DS1 runs to get the result for the Parameter,
Then DS2 Runs,
Then DS1 runs to get the results for the report
Any Other ideas?|||For RS 2000 (and I believe RS 2005) the datasets get filled in the order
they are in the rdl file. Make a copy of your report (because messing with
the rdl file directly is dangerous) and move the definitions around.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jrumage" <jeff@.grinandgiggle.com> wrote in message
news:1137517745.147303.157840@.g43g2000cwa.googlegroups.com...
> Thats a good idea, however it doesn't work the way you might thinkI
> Doing that causes the first proc(DS1) to run twice.
> First DS1 runs to get the result for the Parameter,
> Then DS2 Runs,
> Then DS1 runs to get the results for the report
> Any Other ideas?
>

Dataset Confusion in Reporting Services 2005

I'm creating a report that gets its data from a dataset returned from a
webservice call. I've supplied the URL for the webservice and in my Dataset
dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
Query String as follows:
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath />
</Query>
Where: 'Federation' is my namespace and 'GetAll' is the method which gets
consumed from the webservice.
When I click the Run button on the report Design screen, the webservice is
called correctly but what I get back is one row for each field in my dataset
instead of one row for each record in the dataset. IOW, my dataset has 20
fields in it and what I get returned is a table with 20 records in it, one
for each field, instead of the actual data.
Can someone tell me what I'm doing wrong?
Amos."Amos Soma" wrote:
> I'm creating a report that gets its data from a dataset returned from a
> webservice call. I've supplied the URL for the webservice and in my Dataset
> dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> Query String as follows:
> <Query>
> <SoapAction>Federation/GetAll</SoapAction>
> <ElementPath />
> </Query>
> Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> consumed from the webservice.
> When I click the Run button on the report Design screen, the webservice is
> called correctly but what I get back is one row for each field in my dataset
> instead of one row for each record in the dataset. IOW, my dataset has 20
> fields in it and what I get returned is a table with 20 records in it, one
> for each field, instead of the actual data.
> Can someone tell me what I'm doing wrong?
> Amos.
I am having the same problem. Any answers yet? I am messing around with the
Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
XML & Web Services the going is slow.|||Same problem!....please help!
Tito Perez wrote:
> "Amos Soma" wrote:
> > I'm creating a report that gets its data from a dataset returned from a
> > webservice call. I've supplied the URL for the webservice and in my Dataset
> > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > Query String as follows:
> >
> > <Query>
> > <SoapAction>Federation/GetAll</SoapAction>
> > <ElementPath />
> > </Query>
> >
> > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > consumed from the webservice.
> >
> > When I click the Run button on the report Design screen, the webservice is
> > called correctly but what I get back is one row for each field in my dataset
> > instead of one row for each record in the dataset. IOW, my dataset has 20
> > fields in it and what I get returned is a table with 20 records in it, one
> > for each field, instead of the actual data.
> >
> > Can someone tell me what I'm doing wrong?
> >
> > Amos.
> I am having the same problem. Any answers yet? I am messing around with the
> Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> XML & Web Services the going is slow.|||Can someone please help with this?|||Try this... I was having the same problem but found another example. Here is
what I did and it worked for me - Notice the changes to the "ElementPath".
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table</ElementPath>
</Query>
Note: If you only want certain columns returned, add your column name(s)
seperated by commas in brackets "{} " after "Table" (eg. <ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table{column1_name,column2_name}</ElementPath>)
Hope this helps!
"Kays" wrote:
> Same problem!....please help!
> Tito Perez wrote:
> > "Amos Soma" wrote:
> > > I'm creating a report that gets its data from a dataset returned from a
> > > webservice call. I've supplied the URL for the webservice and in my Dataset
> > > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > > Query String as follows:
> > >
> > > <Query>
> > > <SoapAction>Federation/GetAll</SoapAction>
> > > <ElementPath />
> > > </Query>
> > >
> > > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > > consumed from the webservice.
> > >
> > > When I click the Run button on the report Design screen, the webservice is
> > > called correctly but what I get back is one row for each field in my dataset
> > > instead of one row for each record in the dataset. IOW, my dataset has 20
> > > fields in it and what I get returned is a table with 20 records in it, one
> > > for each field, instead of the actual data.
> > >
> > > Can someone tell me what I'm doing wrong?
> > >
> > > Amos.
> >
> > I am having the same problem. Any answers yet? I am messing around with the
> > Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> > XML & Web Services the going is slow.
>

DataMining problems

Hello Experts,

I’ve got a logical problem. I’m just a newbie in this theme.

I build a cube with Analysis Services. It works and I can brows the cube.

For Example this could be a possible result:

Color

RedBlueBlackYellow

Auto brand

Opel12

VW214

Mercedes1122

The count shows us how much cars were stolen.

Now I try to build a Data Mining structure that shows me the behavior between the auto brand and the color. (To build the report I used Excel 2007 and the Data Mining AddIn)

After build the Mining Structure the Colum name are 1, 2 or 4 (just the counts).

To search for a better solution I tried to build the report manual. (Now it looked like that)

Auto BrandCollorstolen

OpelRedno

OpelBlueyes

OpelBlackyes

OpelYellowno

VWRedyes

VWBlueyes

VWBlackno

VWYellowyes

MercedesRedyes

MercedesBlueyes

MercedesBlackyes

MercedesYellowyes

Now the Column names are correct and the Data Mining structure is more understandable.

But the second report you can’t build with a cube, because the Measures were just numeric.

My Questions:

- Where is my error?

- How do you build a Data Mining?

- Is there somewhere a tutorial could help me (I just founded tutorials that explains the different works of the different Data Mining Models)

- How would you build this problem in a Mining Structure?

Hope I don’t forget some information and hear you soon

Yours sincerely,

Alex

Please visit our portal site ssdm.com. You can find tutorials, tips and tricks as well as samples there.

As to your problem, it depends on what information you wish to analyze. For example, you can build a data mining model using some classification algorithms (such as decision trees and neural network). If you mark stolen as predictable, you can use your model to predict the probability that some specific type of car got stolen. You can also train a clustering model to assist in exploring your data.

Thanks,

Data-Driven Subscriptions: Detecting Success

I am using Data-Driven Subcriptions to send a report to different
recipients using different parameters.
I would like to know if Reporting Services can:
-- update a database on successful distribution of report.
Simplest example: I have bit in table of recipients that determines
whether their report should be run and sent. After report is sent
(successfully), I want sql to set bit to 0.
Is this possible?
DanielThere is nothing built into Report Service that would allow you to do this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<dangordo@.gmail.com> wrote in message
news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
>I am using Data-Driven Subcriptions to send a report to different
> recipients using different parameters.
> I would like to know if Reporting Services can:
> -- update a database on successful distribution of report.
> Simplest example: I have bit in table of recipients that determines
> whether their report should be run and sent. After report is sent
> (successfully), I want sql to set bit to 0.
> Is this possible?
> Daniel
>|||Similar question from different angle: Sometimes distribution ecounters
errors. Is reportserverservice_*.log the only place success and errors
are recorded? Any advice/code on parsing that file?
I am prepared to create mini application to accomplish what i need.
Should I be investigating the rs utility? an application in C#?
Thanks.
Daniel Reib [MSFT] wrote:
> There is nothing built into Report Service that would allow you to do
this.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> <dangordo@.gmail.com> wrote in message
> news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
> >I am using Data-Driven Subcriptions to send a report to different
> > recipients using different parameters.
> >
> > I would like to know if Reporting Services can:
> >
> > -- update a database on successful distribution of report.
> >
> > Simplest example: I have bit in table of recipients that determines
> > whether their report should be run and sent. After report is sent
> > (successfully), I want sql to set bit to 0.
> >
> > Is this possible?
> >
> > Daniel
> >|||Yes, the log file is the only place that we record specific information
about a Data driven subscription failure. You could parse for the
information, but we do not guarantee that the format will be the same from
release to release (a QFE or SP could change it as well).
I'm not sure of how useful it would be anyway. Are you seeing some of the
subscriptions fail, while others succeed? Generally they will either all
succeed or all fail.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<dangordo@.gmail.com> wrote in message
news:1105456778.305024.60080@.f14g2000cwb.googlegroups.com...
> Similar question from different angle: Sometimes distribution ecounters
> errors. Is reportserverservice_*.log the only place success and errors
> are recorded? Any advice/code on parsing that file?
> I am prepared to create mini application to accomplish what i need.
> Should I be investigating the rs utility? an application in C#?
> Thanks.
> Daniel Reib [MSFT] wrote:
>> There is nothing built into Report Service that would allow you to do
> this.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> <dangordo@.gmail.com> wrote in message
>> news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
>> >I am using Data-Driven Subcriptions to send a report to different
>> > recipients using different parameters.
>> >
>> > I would like to know if Reporting Services can:
>> >
>> > -- update a database on successful distribution of report.
>> >
>> > Simplest example: I have bit in table of recipients that determines
>> > whether their report should be run and sent. After report is sent
>> > (successfully), I want sql to set bit to 0.
>> >
>> > Is this possible?
>> >
>> > Daniel
>> >
>

Data-driven Subscription e-mail problem

Trying to test a data-driving subscription for 5 records for a specific
report. Run report interactively successfully in report services. Can't
figure out why e-mail is not being sent (set my e-mail address for all 5).
Status showing "Done: 5 processed of 5; 5 errors". Verified SMTPServer info
set correctly in rsReportServer Config file. Viewed the
ReportServer(date).log file and no information listed, checked windows
application log and report server is being activated successfully. Any
suggestions' Thanks. JanineAre you running RS on a Windows Server 2003? If so, I think that there is a
known issue that can be solved with a hotfix. We had a similar problem and it
wasn't the RS configuration that was causing the problem rather it was an
issue with Windows Server 2003. Sorry that I do not have any additional info.
"JanineB1129" wrote:
> Trying to test a data-driving subscription for 5 records for a specific
> report. Run report interactively successfully in report services. Can't
> figure out why e-mail is not being sent (set my e-mail address for all 5).
> Status showing "Done: 5 processed of 5; 5 errors". Verified SMTPServer info
> set correctly in rsReportServer Config file. Viewed the
> ReportServer(date).log file and no information listed, checked windows
> application log and report server is being activated successfully. Any
> suggestions' Thanks. Janine|||Do regular subscriptions work?
You need to look in the reportserverservice<date>.log file for the errors.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:02470CE4-B6B0-48E5-B387-BF0691124515@.microsoft.com...
> Are you running RS on a Windows Server 2003? If so, I think that there is
a
> known issue that can be solved with a hotfix. We had a similar problem and
it
> wasn't the RS configuration that was causing the problem rather it was an
> issue with Windows Server 2003. Sorry that I do not have any additional
info.
> "JanineB1129" wrote:
> > Trying to test a data-driving subscription for 5 records for a specific
> > report. Run report interactively successfully in report services. Can't
> > figure out why e-mail is not being sent (set my e-mail address for all
5).
> > Status showing "Done: 5 processed of 5; 5 errors". Verified SMTPServer
info
> > set correctly in rsReportServer Config file. Viewed the
> > ReportServer(date).log file and no information listed, checked windows
> > application log and report server is being activated successfully. Any
> > suggestions' Thanks. Janine

data-driven subscription

Does Reporting Services 2000 have data-driven subscriptions or will I need
to upgrade to RS 2005. I would like different employees to pass different
parameters by default. I receive the following message in RS 2000 and I'm
storing a sql account password so I don't think security is the issue.
The error says the following "Subscriptions cannot be created because the
credentials used to run the report are not stored, the report is using
user-defined parameter values"data-driven subscriptions do work in RS 2000. I believe the problem with
your subscription is that one of your parameters is the user login. You are
probably defaulting the current user into the field. You may need to remove
the default value so that you can supply it from the data driven query for
your subscription.
"Colin" wrote:
> Does Reporting Services 2000 have data-driven subscriptions or will I need
> to upgrade to RS 2005. I would like different employees to pass different
> parameters by default. I receive the following message in RS 2000 and I'm
> storing a sql account password so I don't think security is the issue.
>
> The error says the following "Subscriptions cannot be created because the
> credentials used to run the report are not stored, the report is using
> user-defined parameter values"
>
>

DataConversion Problem

SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton

DataConversion Problem

SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim BuntonJim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move
data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in
the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton

Databases Syncronization

Hello Sir/Madam,
I need some help from you.I am working as an RA in a real-time
project,on itegration of Emergency medical services with wireless
technolgies, which involves work on databases.
1)I have a problem now, where I need to write a desktop application
which will syncronise 2 databases.

One database(which will be on a mobile ambulance server) should
constantly check for connection with a main database which is on
another server in someother place and should constantly update as well
as pull information from that. In other words both should be in
syncronisation.
2)First of all I do not know what is a desktop apllication and how
does it differ from other applications.

3)Is there any tutorials which would teach me how to do this, how to
write a desktop application for this? I know there are some tools
which will do this for us, but we want an application to be built by
us, so that we can modify it acording to our requirement.I am jus a
begginer in databses, so I dont know about databases to this extent,
so I need your help.

Thank you, It would be of great help if you could even tell me how go
about this.
Soumyamsowmya@.uab.edu (Sowmya Mulukutla) wrote in message news:<91fbf545.0406111019.43d98df8@.posting.google.com>...
> Hello Sir/Madam,
> I need some help from you.I am working as an RA in a real-time
> project,on itegration of Emergency medical services with wireless
> technolgies, which involves work on databases.
> 1)I have a problem now, where I need to write a desktop application
> which will syncronise 2 databases.
> One database(which will be on a mobile ambulance server) should
> constantly check for connection with a main database which is on
> another server in someother place and should constantly update as well
> as pull information from that. In other words both should be in
> syncronisation.
> 2)First of all I do not know what is a desktop apllication and how
> does it differ from other applications.
> 3)Is there any tutorials which would teach me how to do this, how to
> write a desktop application for this? I know there are some tools
> which will do this for us, but we want an application to be built by
> us, so that we can modify it acording to our requirement.I am jus a
> begginer in databses, so I dont know about databases to this extent,
> so I need your help.
> Thank you, It would be of great help if you could even tell me how go
> about this.
> Soumya

Assuming that this is an academic exercise (I notice you're posting
from a .edu address), then I would suggest asking your teachers for
guidance, if they didn't already give you some sources to consult. I
guess that they want you to investigate the issues related to merging
data when update conflicts can arise - in MSSQL2000, merge replication
is one example of this process. You might want to read up on that in
Books Online to see some of the issues to be considered.

Simon

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

Database versipn (C.0.8.40) does not match

When I click the "Upgrade" button while trying to configure reporting
services I get a couple of errors here are the two errors:
"Verify Database Version" First Yellow "!" Says :
The database version (C.0.8.40) does not match your reporting services
installation. You must upgrade your reporting services database.
"Create Upgrade Script for Database Version" Second Yellow "!" say:
Couldn't generate the upgrade script. There is no upgrade script available
for this version.
Has anyone seen these errors before. How to I get around these. Right now
my "Database Setup" in has a red "X" on the "Reporting Services Configuration
Manager" window.I have the same exact problem. Any fixes yet?
--
---
Yes, I searched first :)
"Greg Larsen" wrote:
> When I click the "Upgrade" button while trying to configure reporting
> services I get a couple of errors here are the two errors:
> "Verify Database Version" First Yellow "!" Says :
> The database version (C.0.8.40) does not match your reporting services
> installation. You must upgrade your reporting services database.
> "Create Upgrade Script for Database Version" Second Yellow "!" say:
> Couldn't generate the upgrade script. There is no upgrade script available
> for this version.
> Has anyone seen these errors before. How to I get around these. Right now
> my "Database Setup" in has a red "X" on the "Reporting Services Configuration
> Manager" window.
>|||Do you have a resolution to this issue? I ahve the exact same problem.
"Greg Larsen" wrote:
> When I click the "Upgrade" button while trying to configure reporting
> services I get a couple of errors here are the two errors:
> "Verify Database Version" First Yellow "!" Says :
> The database version (C.0.8.40) does not match your reporting services
> installation. You must upgrade your reporting services database.
> "Create Upgrade Script for Database Version" Second Yellow "!" say:
> Couldn't generate the upgrade script. There is no upgrade script available
> for this version.
> Has anyone seen these errors before. How to I get around these. Right now
> my "Database Setup" in has a red "X" on the "Reporting Services Configuration
> Manager" window.
>|||I had the same problem.
It was caused by that I had installed reporting services after I had run
SP1 on SQL Server 2005.
Solved it by runing SQL Server 2005 SP1 again.
Hope this helps!
"Rich" wrote:
> Do you have a resolution to this issue? I ahve the exact same problem.
> "Greg Larsen" wrote:
> > When I click the "Upgrade" button while trying to configure reporting
> > services I get a couple of errors here are the two errors:
> >
> > "Verify Database Version" First Yellow "!" Says :
> > The database version (C.0.8.40) does not match your reporting services
> > installation. You must upgrade your reporting services database.
> >
> > "Create Upgrade Script for Database Version" Second Yellow "!" say:
> > Couldn't generate the upgrade script. There is no upgrade script available
> > for this version.
> >
> > Has anyone seen these errors before. How to I get around these. Right now
> > my "Database Setup" in has a red "X" on the "Reporting Services Configuration
> > Manager" window.
> >

Database version - can't install SP1

I have a SQL Server 2005 install with the 64-bit Database Server, but
I've had to install the 32-bit version of Reporting Services, since IIS
is running in 32-bit mode. Everything's green in the Configuration
Tool, but when I go to http://server/reportserver, I get the following
error:
The version of the report server database is either in a format that is
not valid, or it cannot be read. The found version is 'C.0.8.43'. The
expected version is 'C.0.8.40'. To continue, update the version of the
report server database and verify access rights.
(rsInvalidReportServerDatabase)
I think the problem is that I have SP1 installed on the 64-bit install,
but it isn't installed on the 32-bit install. However, when I run the
x86 SP1, it doesn't find that I have Reporting Services installed, so
it doesn't update it. I've run the 32-bit and the 64-bit SP1 installer
several times (with reboots!), but to no avail.
Any suggestions? I think I need the SP1 installer to find my
installation of the 32-bit Reporting Services.
Thanks,
CliffeA little more info: If I try to run the 32-bit setup from the original
install CD, it doesn't recognize that anything's installed - it walks
through the wizard from scratch.
If I run ARPWrapper.exe from \Program Files (x86)\Microsoft SQL
Server\90\Setup Bootstrap, it comes up with an error "SQL Server Setup
cannot proceed because the component you have selected has already been
uninstalled. Refer to books online..."
Any suggestions?
Thanks,
Cliffe
cliffeh wrote:
> I have a SQL Server 2005 install with the 64-bit Database Server, but
> I've had to install the 32-bit version of Reporting Services, since IIS
> is running in 32-bit mode. Everything's green in the Configuration
> Tool, but when I go to http://server/reportserver, I get the following
> error:
> The version of the report server database is either in a format that is
> not valid, or it cannot be read. The found version is 'C.0.8.43'. The
> expected version is 'C.0.8.40'. To continue, update the version of the
> report server database and verify access rights.
> (rsInvalidReportServerDatabase)
> I think the problem is that I have SP1 installed on the 64-bit install,
> but it isn't installed on the 32-bit install. However, when I run the
> x86 SP1, it doesn't find that I have Reporting Services installed, so
> it doesn't update it. I've run the 32-bit and the 64-bit SP1 installer
> several times (with reboots!), but to no avail.
> Any suggestions? I think I need the SP1 installer to find my
> installation of the 32-bit Reporting Services.
> Thanks,
> Cliffe