Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 2005
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints).
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-Luther
Hi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "=?Utf-8?B?THV0aGVyIE1pbGxlcg==?=" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 200
5
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints)
.
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-LutherHi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "examnotes" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

Getting "An existing connection was forcibly closed by the remote host (System)" error

I cannot connect to Analysis Services through SSMS. Receiving a "An existing connection was forcibly closed by the remote host (System) error." I changed my password this morning due to our company security policies and now I can't do squat. This is definitely a bug in the service as something is cached and not clearing out. I've rebooted both the server and client a couple of times. Running AS 2005 SP1 on Windows Server 2003. I can connect through another machine. Here's the message I'm getting. Don't know where to look to fix the problem.

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

Cannot connect to hctx-idwdev.

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

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

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

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()

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

An existing connection was forcibly closed by the remote host (System)

Program Location:

at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

I've uninstalled the client tools and reinstalled. After rebooting, I STILL CANNOT CONNECT TO ANALYSIS SERVICES FROM THIS MACHINE.

Major issue addressed in next service pack? Is there a local cache I need to clear? Any help would be greatly appreciated.

|||Fixed the problem even though the solution is less than ideal. To resolve, we had to delete my user profile on the machine. Apparently, something used by the drivers is being stored in the Document and Settings for the current user. What exactly we weren't able to identify.

Getting "An existing connection was forcibly closed by the remote host (System)" e

I cannot connect to Analysis Services through SSMS. Receiving a "An existing connection was forcibly closed by the remote host (System) error." I changed my password this morning due to our company security policies and now I can't do squat. This is definitely a bug in the service as something is cached and not clearing out. I've rebooted both the server and client a couple of times. Running AS 2005 SP1 on Windows Server 2003. I can connect through another machine. Here's the message I'm getting. Don't know where to look to fix the problem.

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

Cannot connect to hctx-idwdev.

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

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

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

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()

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

An existing connection was forcibly closed by the remote host (System)

Program Location:

at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

I've uninstalled the client tools and reinstalled. After rebooting, I STILL CANNOT CONNECT TO ANALYSIS SERVICES FROM THIS MACHINE.

Major issue addressed in next service pack? Is there a local cache I need to clear? Any help would be greatly appreciated.

|||Fixed the problem even though the solution is less than ideal. To resolve, we had to delete my user profile on the machine. Apparently, something used by the drivers is being stored in the Document and Settings for the current user. What exactly we weren't able to identify.sql

getresourcecontents

Could someone give me an example of displaying reporting services resources via a treeview using the getresourcecontents method?

Sorted this now

Dim rs As New ReportingService
Dim myByteArray() As Byte
myByteArray = rs.GetResourceContents(strResourcePath, "application/vnd.ms-excel")
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=Myxls.xls")
Response.BinaryWrite(myByteArray)

Geting event from ActiveX into database

Hello everyone,

I was wondering. Is it possible, to recive an event from ActiveX into
database?
I was looking alredy with notification services, but I think that's
the wrong way.

Lets say, that there is a ActiveX which realize some tasks.
The database trigers the ActiveX like below:

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

declare @.iRetValint
declare @.iObjectint
declare @.sPropertyvarchar(2560)
declare @.sSource varchar(1000)
declare @.sDescription varchar(1000)
declare @.sLog varchar(1000)
declare @.dDateEVT datetime
declare @.sText1varchar(10)
declare @.sText2varchar(10)
declare @.iProperty int

set @.iObject = 0
set @.dDateEVT = getdate()
set @.iRetVal = 0
set @.sText1 = '0000000000'
set @.sText2 = '0000000000'
set @.iProperty = 7

exec @.iRetVal = sp_OACreate 'MComponent.pidMess', @.iObject OUTPUT,1
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
IF @.iRetVal <> 0
begin
set @.sLog = 'LOG1: No object created. Source: ' + @.sSource + '
Description: ' + @.sDescription
print @.sLog
end

-- Method
exec @.iRetVal = sp_OAMethod @.iObject,'Send_FromA', @.iProperty
OUT,@.nMessageNr = 5, @.bstrDateTime = @.dDateEVT, @.textFromA1 = @.sText1,
@.textFromA2 = @.sText2
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT

IF @.iRetVal <> 0
begin
set @.sLog = 'LOG3: Source: ' + @.sSource + ' Description: ' +
@.sDescription
print @.sLog
end

PRINT 'Property from method:'
PRINT @.iProperty

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

This function works properly. I recive the data into ActiveX.
After the ActiveX process the data, it returns the event, wit a
response for this call of method.

Now ... how do I can get, this event into SQL server?
Is it possible to do that enyhow, without acctions like:
- do, that the ActiveX writes the data in to a interface table, where
from it will be readed.

Any help would be appreciated

Mateuszmarzec@.sauron.xo.pl (Matik) wrote in message news:<8b6e9bf5.0408190717.705a3dec@.posting.google.com>...
> Hello everyone,
> I was wondering. Is it possible, to recive an event from ActiveX into
> database?
> I was looking alredy with notification services, but I think that's
> the wrong way.
> Lets say, that there is a ActiveX which realize some tasks.
> The database trigers the ActiveX like below:
> ================================================== =============
> declare @.iRetValint
> declare @.iObjectint
> declare @.sPropertyvarchar(2560)
> declare @.sSource varchar(1000)
> declare @.sDescription varchar(1000)
> declare @.sLog varchar(1000)
> declare @.dDateEVT datetime
> declare @.sText1varchar(10)
> declare @.sText2varchar(10)
> declare @.iProperty int
> set @.iObject = 0
> set @.dDateEVT = getdate()
> set @.iRetVal = 0
> set @.sText1 = '0000000000'
> set @.sText2 = '0000000000'
> set @.iProperty = 7
> exec @.iRetVal = sp_OACreate 'MComponent.pidMess', @.iObject OUTPUT,1
> EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
> IF @.iRetVal <> 0
> begin
> set @.sLog = 'LOG1: No object created. Source: ' + @.sSource + '
> Description: ' + @.sDescription
> print @.sLog
> end
> -- Method
> exec @.iRetVal = sp_OAMethod @.iObject,'Send_FromA', @.iProperty
> OUT,@.nMessageNr = 5, @.bstrDateTime = @.dDateEVT, @.textFromA1 = @.sText1,
> @.textFromA2 = @.sText2
> EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
> IF @.iRetVal <> 0
> begin
> set @.sLog = 'LOG3: Source: ' + @.sSource + ' Description: ' +
> @.sDescription
> print @.sLog
> end
> PRINT 'Property from method:'
> PRINT @.iProperty
> ================================================== =============
> This function works properly. I recive the data into ActiveX.
> After the ActiveX process the data, it returns the event, wit a
> response for this call of method.
> Now ... how do I can get, this event into SQL server?
> Is it possible to do that enyhow, without acctions like:
> - do, that the ActiveX writes the data in to a interface table, where
> from it will be readed.
> Any help would be appreciated
> Mateusz

I suspect this is not possible, since Books Online has no information
on event handling with the sp_OA% procedures. You should probably
consider using an external program or script to do what you need,
perhaps scheduled to poll a table on the server at intervals. I know
you mentioned that you're trying to avoid this, so if you need an
alternative approach you might want to give some more details of
exactly what you're trying to do, and why an "interface table" isn't a
good solution in your situation.

Simon|||Thank you for reply.

Well... that's the problem. The ActiveX shouldn't be changed any more,
and that's why I have no possibility to implement a function in it,
which writes the data in to interface table. That's why I do not want
such a solution.

I do some algorithm in the database, and in one step, I must 'ask'
ActiveX, for data. This data are the condition for the next steps in the
algorithm from DB.
Now, via sp_OAx procedures, it is possible, to interact with this
ActiveX to give it the data. But the ActiveX do not respond immiedetly,
only via event. It is immposible, to change the ActiveX, that on event,
it writes the data back into DB. I have the solution, that I can have
another program, which catches the event from ActiveX and then writes it
back into DB (or a program which will be called from sp_OAx, and waits
internaly for the ActiveX so long, that as return value can give the
values recived from ActiveX). But these are solutions, which I use
reluctantly.

But anyway ... The solution with table, want be bad, but then I must
have a while loop, til I become answer. I would like not to implement
such a solution.

The best way, as I said, will be, to get the event directly (anyhow) in
to DB, with mechanics and solutions of SQL DB.

Mateusz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Mateusz Marzec (marzec@.sauron.xo.pl) writes:
> Well... that's the problem. The ActiveX shouldn't be changed any more,
> and that's why I have no possibility to implement a function in it,
> which writes the data in to interface table. That's why I do not want
> such a solution.
> I do some algorithm in the database, and in one step, I must 'ask'
> ActiveX, for data. This data are the condition for the next steps in the
> algorithm from DB.
> Now, via sp_OAx procedures, it is possible, to interact with this
> ActiveX to give it the data. But the ActiveX do not respond immiedetly,
> only via event. It is immposible, to change the ActiveX, that on event,
> it writes the data back into DB. I have the solution, that I can have
> another program, which catches the event from ActiveX and then writes it
> back into DB (or a program which will be called from sp_OAx, and waits
> internaly for the ActiveX so long, that as return value can give the
> values recived from ActiveX). But these are solutions, which I use
> reluctantly.

An event is essentially a callback, and T-SQL has no mechanism for this.
So if you can't change the COM module, you would have to write a wrapper
that reacts on the callback.

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

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

GETDATE()

I'm using SQL Server 2005 Repoting Services to find all funds availilble
before the current date. When I use GETDATE() I get an error ORA-00904. Any
other suggestions? I'm using Visual Studio to run SSRS.
ThanksJohn, I believe that you may be looking for NOW(). - RLF
"John" <John@.discussions.microsoft.com> wrote in message
news:64BFEA2C-8D4C-4215-B3B0-39B8930F0E6D@.microsoft.com...
> I'm using SQL Server 2005 Repoting Services to find all funds availilble
> before the current date. When I use GETDATE() I get an error ORA-00904.
> Any
> other suggestions? I'm using Visual Studio to run SSRS.
> Thanks|||GetDate() is specific to MSSQL. Try Sysdate instead.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"John" <John@.discussions.microsoft.com> wrote in message
news:64BFEA2C-8D4C-4215-B3B0-39B8930F0E6D@.microsoft.com...
> I'm using SQL Server 2005 Repoting Services to find all funds availilble
> before the current date. When I use GETDATE() I get an error ORA-00904.
> Any
> other suggestions? I'm using Visual Studio to run SSRS.
> Thanks

GET VS. POST METHODS and Querystring lengths

We've run into a rather sizable problem here.

When passing querystring values using GET, to our SQL Server Reporting Services Reports, we've discovered that we've exceeded the maximum URL length,.

We are thinking that we need to change how we're passing this data to our reports.

Can we use POST and transfer the data throught hte HTTP header and bypass this limitation?

Thanks.

Doug

What in the world could you be passing as parameters? Is it that there are so many parameters or that the parameter values are long strings?|||yes u can use hidden variables , param tags or Session variable to overcome this problem|||

good question.

We have data issues on our back end...which translates to list box items with multiple sometimes up to 10 values per selection. With multiple selections in a wizard that has 5 steps, they add up...

yeah it's ugly...

|||Maybe you should create a record in a temp database with all the keys/values. Then pass in the key to that record to your report. A datasource goes out gets the crazy values.

Get the max of the aggregates

Hello:

I've been researching a likely common problem with reporting services: the inability to get an aggregate of an aggregate. One particular discussion thread comes close to solving my specific problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1

Here's my problem. I have a table that groups data per month based on Count(). I'd like to get the max(count()) -- i.e., which month has the highest count?

For example:

JAN 30

FEB 20

MAR 25

I'd like to identify the month that has the max count. In this case, I'd like to capture the aggregate value "30" as being the max value of the three months displayed.

My goal is to embed a horizontal stacked-bar chart into the table (to the left of the count() values). Various types of medical services are being counted per month: Inpatient Stay, Outpatient Service, PCP visit. The key to displaying the horizontal stacked-bar charts is to make sure the maximum value of the chart is the same for all charts -- i.e., I need to know which month has the highest count and then set that value as the max limit on the chart.

Robert Bruckner's technical article on "Get More out of SQL Server Reporting Services Charts" briefly touches on the topic of embedded charts in a table, but doesn't go into the level of detail I'm considering.

I've also come across related information from SSW Rules to Better Reporting Services. Similar to Robert's article, SSW doesn't address scaling an in-line chart based on data that is aggregated, but rather scaling the in-line chart based on the values found in a specified data field.

Ultimately, I'd like to create an in-line bar chart that appropriate shows the month of JAN as having the longest horizontal bar, and FEB/MAR having appropriately scaled smaller bars.

Thanks in advance!

--Pete

Why not create a second dataset that calcualtes the MAX of the COUNT in SQL or MDX or whatever you're using. This dataset would return only 1 row and 1 column and you can refer to this value by using =First(Fields!your_max_count_field.value, "dataset name") to scale your charts.

I realise that this is doubling up on the query and increases maintenance and that it's a workaround for a missing bit of functionality etc. etc. etc.

The point is it would work.

|||

I've thought about setting up a second dataset to achieve this, as you suggested, but can a chart embedded in a table actually reference a different dataset? i.e. -- if the table uses dataset "A", and an inline chart is displaying a stacked-bar chart using "A", can you actually set the max limit of the chart to dataset "B"? Seems like anytime I've ever attempted to drive a data region with values that are NOT part of a the same data region dataset, I get an error....

|||

There is no restriction in using values from a different dataset. The key part is to qualify the Aggregate call with a dataset name i.e.

= First(Fields!col1.Value, "some_other_dataset")

|||

Ah, I see... I'll shall give it a go. Sounds promising.

Thanks Adam,

--Pete

Get Previous step result in an SSIS Script Task

Hello,

I am using SQL Server 2005 Integration Services to create new values for my tables. One step I must do is execute a query and the script task that receive its constraint (it is set to completion) must do different things depending on the query result.

My question is: how can I know the result of the precedence constraint?

Thank you,

Pablo Orte

If a task executes then it matched your precedence constraint. If you say "Complete" and "VarA == 20", and it executes then you can be assured that was the result. Are you wanting to do branching, or have multiple precedent constraints?|||

The key point to what I think Sean is suggesting, is that if you can get a variable to hold the result, you could use an expression to influence the constraint, whether it is satisfied or not. Expression support on a constraint is very useful for influencing workflow.

I am not sure that this is useful to you, since you have to populate that variable somehow. One way would be to use an on error event handler on the task. Default the variable to true, and change it to false in the event handler.

I don't know why or exactly what you are trying to do, but my first reaction would be this sounds like a bad idea. I think there should be a better, more SSIS way of trying to achieve this. Forgive me, but your method reminds me of the hacks we used in DTS. To conditionally do something why not use two tasks, or why not use event handlers to do an out of band type operation when something fails.

|||

This thread might have somthing for you...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=859001&SiteID=1

Rafael Salas

|||

Hi and thanks for your replies,

The problem I have is that I have to do this in a script task situated after an "Execute SQL". This cannot return any parameter and I need to have something in the code like this

IF objDTSPackage.Steps("DTSStep_DTSExecuteSQLTask_1").ExecutionResult = 0 THEN

This code worked in SQL Server 2000, but not in SQL Server 2005.

Thanks,

Pablo Orte

GET MAX Date of previous year

Dear Friends,

I need to create a new column in a VIEW with a reference date to call from SQL Analysis Services 2005.

FactTable ID Name DateID Date 1 blabla… 8225 16-03-2005 2 blabla… 12-08-2006 … 9999 … … TIME Dimension DateID Day Year MonthKey Month QuarterKey Quarter 8224 15-03-2005 0:00 2005 20053 March 20051 Q1 8225 16-03-2005 0:00 2005 20053 March 20051 Q1 8226 17-03-2005 0:00 2005 20053 March 20051 Q1 8227 18-03-2005 0:00 2005 20053 March 20051 Q1 .. … … … … … … 9321 16-03-2008 0:00 2008 20083 March 20081 Q1 9322 17-03-2008 0:00 2008 20083 March 20081 Q1 9323 18-03-2008 0:00 2008 20083 March 20081 Q1 9324 19-03-2008 0:00 2008 20083 March 20081 Q1 9325 20-03-2008 0:00 2008 20083 March 20081 Q1 9326 21-03-2008 0:00 2008 20083 March 20081 Q1 9327 22-03-2008 0:00 2008 20083 March 20081 Q1 9328 23-03-2008 0:00 2008 20083 March 20081 Q1 9329 24-03-2008 0:00 2008 20083 March 20081 Q1 9330 25-03-2008 0:00 2008 20083 March 20081 Q1 9331 26-03-2008 0:00 2008 20083 March 20081 Q1 9332 27-03-2008 0:00 2008 20083 March 20081 Q1 9333 28-03-2008 0:00 2008 20083 March 20081 Q1 Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2005 2 blabla… 12-08-2006 29-12-2006 … 9999 … … …

The refDate is the last VALID date from previous year for each date in each row of FactTable. My TimeTable only has valid dates (does not has holidays, saturday, sunday and forcedHolidays). so I need a query to get for each date in each row of FactTable the last date for previous year. Probably using the parameter "year" of the date in each row in facttable (using Datepart)

Someone help me?

Help me please!|||

Pedro,

To clarify: RefDate is the highest date from the Time Dimension table for a given year (dateid should be ignored)?

Also, you say "the last date for previous year" but your sample result shows the last date of the same year. Should DateID=8225 have 25-12-2004?

|||

Dalej you are write!!

I made a mistake, is

CORRECT Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2004 2 blabla… 12-08-2006 29-12-2005 … 9999 … … …

The RefDate is the highest date from Time Table for a given year (for each date in each row)

Could help me?!

THANKS

|||

Code Snippet

select ft.*, td.RefDate

from FactTabl ft

innerjoin

(

select [Year],max([Day])as RefDate

from [Time Dimension]

groupby [Year]

)as td

on(ft.datepart(yy, Date)-1)= td.[Year]

|||

Dear alej,

I customize your statment to my database and there is an error...

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(ft.datepart(yy, T_Dia)-1)= td.[Ano]

The error is:

Code Snippet

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "ft" or the user-defined function or aggregate "ft.datepart", or the name is ambiguous.

When I was trying to customize your code I saw that I dont have the DATE in my FactTable, only the integer foreign key for time dimension. So, I created the FactCashFlow as a view to do a inner join to time table to get the date value.

If I can do it only in one view would be perfect!!

Could help me?
Thanks!!!

|||

OK!

I changed and this statment finally works:

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

But I have 2 views, and would be better using only one...

I need to change the the first select to get the date value from time dimension...

|||

In order to have only one view to call from SSAS, I need the query something like this:

Code Snippet

select ft.*, td.RefDate, MyTable.Dia

from(SELECT Dia FROM dbo.Time INNERJOIN dbo.CashFlows ON CF_RKData_ID=time.ID) MyTable INNERJOIN

CashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

Code Snippet

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'Ano'.

And this view will be the FactCashFlows in SSAS!

But this is wrong, could someone help me?

Thanks!

|||

OK I Found the solution...

Code Snippet

select ft.*, td.RefDate

from(dbo.time INNERJOIN CashFlows ft ON CF_RKData_ID=time.ID)

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, dbo.time.Dia)-1)= td.[Ano]

THANKS ALL!!!