Problem with attached view

I write you because I have a big problem with request access : when
I use a sql server wiev in access (attached view) and i join it with
a local access table, the sql server trace say sql server send all
the data of the view to a access . This view have 15 000 000 rows
When i do the same with an sql server table (attached table) , i
have a very fast answer. every data of the local table is send to
sqlserver (exec sp_execute 1, N'205513214066535435'), why it doesn't
happen this with the view.
there is no difference between the table and the view.
my table is :dbo.table
my view is : create view vtable as select * from dbo.table
Hi
Show us the code you use to call the view.
Where is the where clause?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"toni" <toni@.discussions.microsoft.com> wrote in message
news:2282D883-62D2-4810-9442-418C36148C9C@.microsoft.com...
> I write you because I have a big problem with request access : when
> I use a sql server wiev in access (attached view) and i join it
> with
> a local access table, the sql server trace say sql server send all
> the data of the view to a access . This view have 15 000 000 rows
> When i do the same with an sql server table (attached table) , i
> have a very fast answer. every data of the local table is send to
> sqlserver (exec sp_execute 1, N'205513214066535435'), why it
> doesn't
> happen this with the view.
> there is no difference between the table and the view.
> my table is :dbo.table
> my view is : create view vtable as select * from dbo.table
|||Hello,
The local acces table have one field id
The linked view sqlserver_view
The linked table sqlserver_table
in access the request is:
SELECT *
FROM sqlserver_view.view INNER JOIN local_acces.table ON
local_acces.table.id = sqlserver_view.view.id
All the datat of the view are send (odbc error after many minutes)
the trace give :
sqlbatchcompleted
SELECT *FROM sqlserver_view.view
when i do the same with the linked table sqlserver_table.table
SELECT *
FROM sqlserver_table.table INNER JOIN local_acces.table ON
local_acces.table.id = sqlserver_table.table.id
the data are send immediatly
the trace give
rpc.completed
declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT
*FROMsqlserver_table.table WHERE ("Carte_SAM" = @.P1)', N'453335736479610200'
select @.P1
Sorry , I don't speak a very good english because I'm spanish
regards
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Show us the code you use to call the view.
> Where is the where clause?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "toni" <toni@.discussions.microsoft.com> wrote in message
> news:2282D883-62D2-4810-9442-418C36148C9C@.microsoft.com...
>
>
|||The linked view sqlserver_view
The linked table sqlserver_table
and
the sqlserver_view is
create view dbo. sqlserver_view
as
select
*
from
dbo. sqlserver_table
regards
"toni" wrote:
[vbcol=seagreen]
> Hello,
> The local acces table have one field id
> The linked view sqlserver_view
> The linked table sqlserver_table
> in access the request is:
> SELECT *
> FROM sqlserver_view.view INNER JOIN local_acces.table ON
> local_acces.table.id = sqlserver_view.view.id
> All the datat of the view are send (odbc error after many minutes)
> the trace give :
> sqlbatchcompleted
> SELECT *FROM sqlserver_view.view
> when i do the same with the linked table sqlserver_table.table
> SELECT *
> FROM sqlserver_table.table INNER JOIN local_acces.table ON
> local_acces.table.id = sqlserver_table.table.id
> the data are send immediatly
> the trace give
> rpc.completed
> declare @.P1 int
> set @.P1=1
>
> exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT
> *FROMsqlserver_table.table WHERE ("Carte_SAM" = @.P1)', N'453335736479610200'
> select @.P1
>
> Sorry , I don't speak a very good english because I'm spanish
>
>
>
> regards
> "Mike Epprecht (SQL MVP)" wrote:
|||The view haven't clause where, it's a simply select * from the table
"toni" wrote:
[vbcol=seagreen]
> The linked view sqlserver_view
> The linked table sqlserver_table
> and
> the sqlserver_view is
> create view dbo. sqlserver_view
> as
> select
> *
> from
> dbo. sqlserver_table
>
> regards
>
>
> "toni" wrote:

problem with analysis server connection string

i have just installed a new version of analysis services 2000 on a test machine. when i try to connect to the local host i get the error "Datasource name is too long". I have set the datasource string to

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\serverName\MsOLAPRepository$\msmdrep.mdb

This is the same string as i have on other analysis servers, bar the server name, but it still does not work. any ideas?

The olap service is running correctly, just in case its a detail you need to know.

I assume you use Analysis Manager or DSO program to connect, and you change the "Repository Connection String" property from Analysis Manager. Can you check whether you can open msmdrep.mdb repository file from Access ? Or try to migrate repository to SQL Server - will you get the same error ?

Problem with Analysis Server

I just installed 2005 and have an Enterprise Editition instance on my local machine. My SSAS service appears to be working properly in Services. However, whenever I try to deploy a cube (simple one from tutorial) or try to connect from Management Console, I get rejected. The error message is:

No connection could be made because the target machine actively refused it (System).

I've tried about everything I can think of and can't get past this. Any guidance would be greatly appreciated.

Rod, can you enable shared memory and try again? This should solve the problem.

Thanks,
Sam Lester (MSFT)

|||How do you "enable" shared memory?sql

Problem with Analysis Server

I just installed 2005 and have an Enterprise Editition instance on my local machine. My SSAS service appears to be working properly in Services. However, whenever I try to deploy a cube (simple one from tutorial) or try to connect from Management Console, I get rejected. The error message is:

No connection could be made because the target machine actively refused it (System).

I've tried about everything I can think of and can't get past this. Any guidance would be greatly appreciated.

Rod, can you enable shared memory and try again? This should solve the problem.

Thanks,
Sam Lester (MSFT)

|||How do you "enable" shared memory?

Problem with Analysis Server

I just installed 2005 and have an Enterprise Editition instance on my local machine. My SSAS service appears to be working properly in Services. However, whenever I try to deploy a cube (simple one from tutorial) or try to connect from Management Console, I get rejected. The error message is:

No connection could be made because the target machine actively refused it (System).

I've tried about everything I can think of and can't get past this. Any guidance would be greatly appreciated.

Rod, can you enable shared memory and try again? This should solve the problem.

Thanks,
Sam Lester (MSFT)

|||How do you "enable" shared memory?

problem with ALTER TABLE syntax

I finally found a way to "deploy" my local SqlServerExpress (SSE) database to the remote Sql2K server... In VisualWebDeveloper (VWD) I can create the table definition and then save the creation sql script and use that in SSE Express Manager while connected to my remote DB. I am describing this because I'm so surprised no one has had problems with this as thousands of developers, some very unexperienced (as me maybe!), are trying the same situation now that some are offering 2.0 hosting... Well I thought this was a great idea until the Sql2K server is returning error messages on the script VWD created. So please could you help since I'm not that good at complex sql scripting. It seems the error comes from the ALTER TABLE syntax:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Table2
(
prID int NOT NULL IDENTITY (1, 1),
DateInserted datetime NOT NULL,
Title nvarchar(100) NOT NULL,
Description nvarchar(MAX) NULL,
CategoryID smallint NOT NULL,
DateLastUpdated datetime NULL,
Price int NOT NULL,
SpecialPrice int NULL,
ImgSuffix nvarchar(5) NULL,
ImgCustomWidth smallint NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
prID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Exactly what error message are you getting?
Tryst
|||

Msg 170... Incorrect syntax near '('.
I've found out if I remove "WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)"
it works...

Problem while using xp_sendmail

Hi,
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KP
XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP
|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:

> XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
>
>

Problem while using xp_sendmail

Hi,
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KPXP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:

> XP_Sendmail has a parameter for set_user, perhaps thewrong message is bein
g
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.co
m>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com..
.
>
>

Problem while using xp_sendmail

Hi,
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KPXP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> > Hi,
> >
> > When I use xp_sendmail on my local machine, using parameters like
> > Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> > some message', @.subject = ' some subject ', then it works fine and I
> > get
> > the mail in my mailbox ...
> >
> > But when I use the same stuff on any other SQL server (on same network), I
> > get the error --
> > xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> >
> > I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> >
> > What and where is the problem ?
> >
> > regards
> > KP
>
>

Problem w/ Data Flow Task of SSIS Package

I have a Data Flow Task in an SSIS package that transfers data from a local table to a table on a remote server. The remote database is SQL Server 2000 and the local one is SQL Server 2005. The package in the designer runs great. But, when I setup the job for the server agent it fails with the connection. I get the following error:

The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009.

This is on validation from what the error logs say. It is probably just a simple configuration problem or something I have overlooked.

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

I also ran a seperate import task on the remote server (the same one as above) database table (Tasks > Import Data) and saved it as an SSIS package as the last option it gives you in the wizard. I am copying from a local table to a remote table. The import was successful as expected. But, when I run that same SSIS package in a SQL Server Agent Job, that package fails. I have not made any changes to the package what so ever. I never even opened the package to look it in the editor. I just ran it as is.

I am thinking it is a security problem of some sort. Like the Agent does not have right privilidges. I am running the packages under my local system account.

Thanks in advance!

This does sound like a security problem. If you're executing from SQL SAerver Agent then the packages will run as the startup acocunt for SQL Server Agent. Does this account have permissions on the source database?

There is a way that you can see what connection string is being used at runtime as I have explained here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

This will cause the connection string to be output to your log provider (assuming you are using one and if you aren't - you should be).

-Jamie

|||

Thanks for your response.

I am running the local database from a Windows XP development machine. This is where the SSIS package resides because the hosting provider I am using for the remote database does not support SSIS. So I have to run the Server Agent locally and then copy the data over as the last step of the package.

So to answer your question, NO, my local account that is running Server Agent is not on the remote server. I am using the same username/password on the remote machine as I am for the ASP.NET pages on the application on that remote machine. The remote account works. I am using windows security locally on my development for the database server login.

As I mentioned earlier, I can run the SSIS package in the designer without a problem but it does not work in the Server Agent.

I do have a log provider setup to output to a table on the local database. Below is what the log output is (I replaced the actual server name for security reasons to "server.name.here"):

__

988	OnInformation	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	1074016266	<Binary data>	Validation phase is beginning.
989	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	0	<Binary data>	Validating
990	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	50	<Binary data>	Validating
991	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1071611876	<Binary data>	The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009.
992	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073450985	<Binary data>	component "Destination - Listings" (187) failed validation and returned error code 0xC020801C.
993	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	100	<Binary data>	Validating
994	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073450996	<Binary data>	One or more component failed validation.
995	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073594105	<Binary data>	There were errors during task validation.
996	OnPostValidate	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	0	<Binary data>

|||

After my last post I changed the timeout on the remote database connection to 60 seconds as you suggested in your blog. I would have never thought that it would have been that simple but it fixed the problem. I thought that the timeouts set at 0 seconds meant there WAS NO TIMEOUT. BAH!!

Thanks for your help! I was about ready to pull my hair out.

Problem viewing report based on analysis services

I am using using some code which Implements IReportServerCredentials (see
the object below) to pass credentials to a non local report server. This
works fine and my reportviewer can get it's report from a non local server.
When the report uses Analysis Services it displays the parameters part of
the report viewer so I know it is getting as far as the report server but
when I press view report I doesn't display anything, not even an error. I
have tried adding the user I am logging as to the roles within the analysis
Services project it is based on but with no luck. I have included the code
in case anyone finds it helpful. Can anyone help?
Dim cred As New ReportServerCredentials("username", "password", "domain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Imports Microsoft.VisualBasic
Imports Microsoft.Reporting.WebForms
Imports System.Net
Public Class reportingservices
End Class
<Serializable()> _
Public Class ReportServerCredentials
Implements IReportServerCredentials
Private _userName As String
Private _password As String
Private _domain As String
Public Sub New(ByVal userName As String, ByVal password As String, ByVal
domain As String)
_userName = userName
_password = password
_domain = domain
End Sub
Public ReadOnly Property ImpersonationUser() As
System.Security.Principal.WindowsIdentity Implements
Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
Get
Return Nothing
End Get
End Property
Public ReadOnly Property NetworkCredentials() As ICredentials Implements
Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
Get
Return New NetworkCredential(_userName, _password, _domain)
End Get
End Property
Public Function GetFormsCredentials(ByRef authCookie As System.Net.Cookie,
ByRef userName As String, ByRef password As String, ByRef authority As
String) As Boolean Implements
Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials
userName = _userName
password = _password
authority = _domain
Return Nothing
End Function
End ClassIn this instance are you trying to access the report in the following
situation:
client accessing report (machine1) -> Report Server (machine 2) -> Analysis
Services (machine 3)?
If so then it is most likely a kerberos authentication issue due to the
"double hop" you are experiencing.
For more information on enabling this check the following article:
http://support.microsoft.com/kb/917409/en-us
SQL Server Developer Support Engineer
"Fresno Bob" wrote:
> I am using using some code which Implements IReportServerCredentials (see
> the object below) to pass credentials to a non local report server. This
> works fine and my reportviewer can get it's report from a non local server.
> When the report uses Analysis Services it displays the parameters part of
> the report viewer so I know it is getting as far as the report server but
> when I press view report I doesn't display anything, not even an error. I
> have tried adding the user I am logging as to the roles within the analysis
> Services project it is based on but with no luck. I have included the code
> in case anyone finds it helpful. Can anyone help?
> Dim cred As New ReportServerCredentials("username", "password", "domain")
> ReportViewer1.ServerReport.ReportServerCredentials = cred
>
> Imports Microsoft.VisualBasic
> Imports Microsoft.Reporting.WebForms
> Imports System.Net
>
>
> Public Class reportingservices
> End Class
>
> <Serializable()> _
> Public Class ReportServerCredentials
> Implements IReportServerCredentials
> Private _userName As String
> Private _password As String
> Private _domain As String
> Public Sub New(ByVal userName As String, ByVal password As String, ByVal
> domain As String)
> _userName = userName
> _password = password
> _domain = domain
> End Sub
> Public ReadOnly Property ImpersonationUser() As
> System.Security.Principal.WindowsIdentity Implements
> Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
> Get
> Return Nothing
> End Get
> End Property
> Public ReadOnly Property NetworkCredentials() As ICredentials Implements
> Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
> Get
> Return New NetworkCredential(_userName, _password, _domain)
> End Get
> End Property
> Public Function GetFormsCredentials(ByRef authCookie As System.Net.Cookie,
> ByRef userName As String, ByRef password As String, ByRef authority As
> String) As Boolean Implements
> Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials
> userName = _userName
> password = _password
> authority = _domain
> Return Nothing
> End Function
> End Class
>
>

Problem Using Stored Procedures in Report Datasets

I have a local Reporting Services report that I am modifying to use a stored procedure.

Although I am executing a stored procedure in the dataset query window, I also have to run a SELECT statement to retrieve the fields from a table that will populate the report.

The code that I have in the dataset query window looks like the following:

EXECUTE @.retCode = RunClaimVerification @.parmID, @.parmDate, @.parmRecordID OUTPUT

SELECT *

FROM ClaimsDetail

WHERE ClaimRecordID = @.parmRecordID

When I execute this code, the only results that are returned SEEM TO BE the return code associated with running the stored procedure.

I thought about putting the SELECT code in the stored procedure and returning a table or a cursor from the stored procedure BUT it looks like tables are not supported as Report Parameter data types.

The stored procedure code generates Claim data that is stored in a SQL Table. The fields in this SQL table need to be retrieved by a unique record id to populate the fields in the report.

Does anybody have any suggestions as to how to go about doing this OR any suggestions that would help me resolve this problem?

Reporting Services only allows one result (table or the return value of a stored procedure) to be retrieved per query. This is the reason that only the return code seems to be included in the dataset. Also, out parameters for stored procedures are not supported in Reporting Services.

Try changing the stored procedure to also Select the data from the ClaimsDetail table, and return the resultant table instead of the return code. However, don't set the return value to a parameter--just execute the stored procedure. This should produce a dataset containing the results of the Select statement.

Ian

Problem using SQL 2005 Reporting Services & asp.net app

I'm using VS 2005, SQL 2005 reporting services.
SQL reporting services is working, and I have it both on my local
computer, as well as on a server.
I've created a report in the SQL Business Intelligence development
studio that works in that environment.
I've uploaded the same report to both the Reporting services on my local
computer as well as the server, and can log in to them and run the
report there.
In VS 2005, in a test application that otherwise functions, I brought in
a Reportviewer from the toolbar, and added the report to it.
The reportServerUrl is:
http://mylocalcomputer/reports$sql2005
(I have both sql 2000 and sql 2005 on this local box).
The report path is:
J4 Report/J4 Detail Report
When I try to run it, I get:
The attempt to connect to the report server failed. Check your
connection information and that the report server is a compatible version.
The request failed with HTTP status 404: Not Found.
When I change the server name to the other one, I get the same message.
However, when I go into SQL Server Reporting services, to this link:
http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+reports%2fJ4+Detail+Report
The report displays fine.
When I go to my local computer & sql reporting services, to this link:
http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+Reports%2fJ4+Detail+Report
I'm not using Localhost either in the VS 2005 app (in the properties for
the report viewer), but I am calling it (the web app itself where I'm
trying to call the report viewer from) thru localhost there.
http://localhost:2228/testjob/default.aspx
I'm calling other things (not report viewers) on this page that appear
to work.
I've seen some notes regarding this error, and that there is a lot of
registry hacks and config file updates you have to make for it, but not
sure if that's a true fix for the problem.
Anyone have any idea why SQL Reporting Services displays it fine, and
the report viewer - which is supposedly calling the same thing, doesn't?
BCOn Jul 27, 9:16 am, Blasting Cap <goo...@.christian.net> wrote:
> I'm using VS 2005, SQL 2005 reporting services.
> SQL reporting services is working, and I have it both on my local
> computer, as well as on a server.
> I've created a report in the SQL Business Intelligence development
> studio that works in that environment.
> I've uploaded the same report to both the Reporting services on my local
> computer as well as the server, and can log in to them and run the
> report there.
> In VS 2005, in a test application that otherwise functions, I brought in
> a Reportviewer from the toolbar, and added the report to it.
> The reportServerUrl is:
> http://mylocalcomputer/reports$sql2005
> (I have both sql 2000 and sql 2005 on this local box).
> The report path is:
> J4 Report/J4 Detail Report
> When I try to run it, I get:
> The attempt to connect to the report server failed. Check your
> connection information and that the report server is a compatible version.
> The request failed with HTTP status 404: Not Found.
> When I change the server name to the other one, I get the same message.
> However, when I go into SQL Server Reporting services, to this link:
> http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+repo...
> The report displays fine.
> When I go to my local computer & sql reporting services, to this link:
> http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2f...
> I'm not using Localhost either in the VS 2005 app (in the properties for
> the report viewer), but I am calling it (the web app itself where I'm
> trying to call the report viewer from) thru localhost there.
> http://localhost:2228/testjob/default.aspx
> I'm calling other things (not report viewers) on this page that appear
> to work.
> I've seen some notes regarding this error, and that there is a lot of
> registry hacks and config file updates you have to make for it, but not
> sure if that's a true fix for the problem.
> Anyone have any idea why SQL Reporting Services displays it fine, and
> the report viewer - which is supposedly calling the same thing, doesn't?
> BC
This might be a long shot, but you should check to see if the the
virtual directories (ReportServer and Reports) in IIS are set to the
correct .NET Framework (.NET 2.0) [via: right-click My Computer ->
select Manage -> Services and Applications -> Internet Information
Services -> Web Sites -> Default Web Site -> right-click the Reports/
ReportServer virtual directories -> select Properties -> select the
ASP.NET tab and verify that the ASP.NET version is 2.0.50727]. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Connect to ReportServer instead of Reports...i.e.
http://mylocalcomputer/ReportServer$sql2005
"Blasting Cap" wrote:
> I'm using VS 2005, SQL 2005 reporting services.
> SQL reporting services is working, and I have it both on my local
> computer, as well as on a server.
> I've created a report in the SQL Business Intelligence development
> studio that works in that environment.
> I've uploaded the same report to both the Reporting services on my local
> computer as well as the server, and can log in to them and run the
> report there.
> In VS 2005, in a test application that otherwise functions, I brought in
> a Reportviewer from the toolbar, and added the report to it.
> The reportServerUrl is:
> http://mylocalcomputer/reports$sql2005
> (I have both sql 2000 and sql 2005 on this local box).
> The report path is:
> J4 Report/J4 Detail Report
>
> When I try to run it, I get:
> The attempt to connect to the report server failed. Check your
> connection information and that the report server is a compatible version.
> The request failed with HTTP status 404: Not Found.
>
> When I change the server name to the other one, I get the same message.
>
> However, when I go into SQL Server Reporting services, to this link:
> http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+reports%2fJ4+Detail+Report
> The report displays fine.
> When I go to my local computer & sql reporting services, to this link:
> http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+Reports%2fJ4+Detail+Report
> I'm not using Localhost either in the VS 2005 app (in the properties for
> the report viewer), but I am calling it (the web app itself where I'm
> trying to call the report viewer from) thru localhost there.
> http://localhost:2228/testjob/default.aspx
> I'm calling other things (not report viewers) on this page that appear
> to work.
> I've seen some notes regarding this error, and that there is a lot of
> registry hacks and config file updates you have to make for it, but not
> sure if that's a true fix for the problem.
> Anyone have any idea why SQL Reporting Services displays it fine, and
> the report viewer - which is supposedly calling the same thing, doesn't?
> BC
>|||Thank you.
This appeared to work, although I had to put in a slash at the end of
the http://mylocalcomputer/ReportServer$sql2005 line.
However, now that I've gotten it to run, I have some unusual behavior -
The "e" on Internet explorer at the top of the tab now flickers, like
the page is reloading. It also runs the CPU up to 100% on the computer
and although I can go from tab to tab in it (I am using AJAX tab panels
in the page), it will take like up to a minute to go to the next tab.
I'm not doing anything really data-intensive on those tabs, and they had
been functioning fine prior to putting in the report viewer (i.e. they
weren't flickering & clocking the CPU).
I eventually have to kill the page to do anything, because it has the
system up to 100%.
Any idea why reportviewer might make this act this way?
Thanks for the help,
BC
David wrote:
> Connect to ReportServer instead of Reports...i.e.
> http://mylocalcomputer/ReportServer$sql2005
> "Blasting Cap" wrote:
>> I'm using VS 2005, SQL 2005 reporting services.
>> SQL reporting services is working, and I have it both on my local
>> computer, as well as on a server.
>> I've created a report in the SQL Business Intelligence development
>> studio that works in that environment.
>> I've uploaded the same report to both the Reporting services on my local
>> computer as well as the server, and can log in to them and run the
>> report there.
>> In VS 2005, in a test application that otherwise functions, I brought in
>> a Reportviewer from the toolbar, and added the report to it.
>> The reportServerUrl is:
>> http://mylocalcomputer/reports$sql2005
>> (I have both sql 2000 and sql 2005 on this local box).
>> The report path is:
>> J4 Report/J4 Detail Report
>>
>> When I try to run it, I get:
>> The attempt to connect to the report server failed. Check your
>> connection information and that the report server is a compatible version.
>> The request failed with HTTP status 404: Not Found.
>>
>> When I change the server name to the other one, I get the same message.
>>
>> However, when I go into SQL Server Reporting services, to this link:
>> http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+reports%2fJ4+Detail+Report
>> The report displays fine.
>> When I go to my local computer & sql reporting services, to this link:
>> http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+Reports%2fJ4+Detail+Report
>> I'm not using Localhost either in the VS 2005 app (in the properties for
>> the report viewer), but I am calling it (the web app itself where I'm
>> trying to call the report viewer from) thru localhost there.
>> http://localhost:2228/testjob/default.aspx
>> I'm calling other things (not report viewers) on this page that appear
>> to work.
>> I've seen some notes regarding this error, and that there is a lot of
>> registry hacks and config file updates you have to make for it, but not
>> sure if that's a true fix for the problem.
>> Anyone have any idea why SQL Reporting Services displays it fine, and
>> the report viewer - which is supposedly calling the same thing, doesn't?
>> BC
>>