Showing posts with label related. Show all posts
Showing posts with label related. Show all posts

Monday, March 19, 2012

Another Stored procedure problem with VB 2005

Solving one problem leads to another. This post is related to my last one (Can't see stored proc results) so this will look familiar to some of you. This sproc is called by a VB front end. It retrieves a name based on an ID#:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]
-- Add the parameters for the stored procedure here
@.caseID nvarchar(12)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)
END

I am also looking for problems on the VB side if it's not here.

It executes fine on the SQL Server side. I took an option to preview the data through the VB dataset that this sproc fills. After entering the required parm, instead of the name I want, I see this:
Type Value
Int32 0
I previewed a view with the same select statement minus the WHERE clause. It worked fine. Any reason why the sproc shows up differently?
Thank you for all of the help.

If it executes fine in the SQL Server side (and you see results?) then there's nothing wrong with the proc. You have to look at the VB side. Does the VB code take care of the returned resultset as it should?

/Kenneth

|||I have been looking into that. Being new to both SQL Server and VB, it takes me a while to find where the real problem is at times. I have determined that the parm is not getting to the sproc correctly. I also found that the VB program is receiving a return value instead of the data set for both of the sprocs I have.|||

I found a way around this on the VB side. Some built in functions are now giving me the results I need.

Thanks for the reply, Kenneth.

Another SSL-Related problem?

I have seen all the other SSL related problems, and I don't think I am
having the same issue...but I am having something similar.
I have Reporting Services SP1 installed on the same machine as SQL
Server. IIS is also on this machine. The machine is not part of a
domain and its full name is SQL2. I have an internal CA within my
domain that I used to issue a certificate to SQL2 under the name SQL2.
I have set up my reports to deply to https://SQL2/ReportServer
Reports deploy fine and with no error. I can view the report by going
to https://SQL2/ReportManager or http://SQL2/ReportManager and it takes
parameters and returns data, exactly as it does in debug mode in
vs.net.
What doesn't work:
If I go to the report properties tab (SSL or not) within the report I
get the following error:
The underlying connection was closed: Could not establish trust
relationship with remote server.
If I try to go to the data source instead of the report (SSL or not),
I get the following error:
The underlying connection was closed: Could not establish trust
relationship with remote server.
If I try to export the report to another format for download (SSL or
not), I get a file not found error.
When I search for the "The underlying connection was closed: Could not
establish trust relationship with remote server." error message, it
seems like everyone who gets that cannot deploy the report or view it.
I can deploy and view with no problems, but it seems like I can't do
anything else.
Any ideas?
Thanks in advance.
ErikI'm experiencing the exact same problems. I'm also receiving the error
when saving a new report subscription. Any help would be greatly
appreciated.
Thanks
Chad
cybermud wrote:
> I have seen all the other SSL related problems, and I don't think I am
> having the same issue...but I am having something similar.
> I have Reporting Services SP1 installed on the same machine as SQL
> Server. IIS is also on this machine. The machine is not part of a
> domain and its full name is SQL2. I have an internal CA within my
> domain that I used to issue a certificate to SQL2 under the name SQL2.
> I have set up my reports to deply to https://SQL2/ReportServer
> Reports deploy fine and with no error. I can view the report by going
> to https://SQL2/ReportManager or http://SQL2/ReportManager and it takes
> parameters and returns data, exactly as it does in debug mode in
> vs.net.
> What doesn't work:
> If I go to the report properties tab (SSL or not) within the report I
> get the following error:
> The underlying connection was closed: Could not establish trust
> relationship with remote server.
> If I try to go to the data source instead of the report (SSL or not),
> I get the following error:
> The underlying connection was closed: Could not establish trust
> relationship with remote server.
> If I try to export the report to another format for download (SSL or
> not), I get a file not found error.
> When I search for the "The underlying connection was closed: Could not
> establish trust relationship with remote server." error message, it
> seems like everyone who gets that cannot deploy the report or view it.
> I can deploy and view with no problems, but it seems like I can't do
> anything else.
> Any ideas?
> Thanks in advance.
> Erik
>|||I tried giving the site a proper DNS name, sql2.domain.com, and
generated a new SSL certificate for that name, and I am still having
all the same issues. Reports work fine, but I cannot view a data
source or go to the report properties. I still get the same "The
underlying connection was closed: Could not establish trust
relationship with remote server." error message. Additionally, like
Chad, I cannot save a subscription. I get the same "The underlying
connection was closed: Could not establish trust relationship with
remote server." error message. Has anyone else had a similar
situation?
Thanks,
Erik|||Chad:
I edited the RSReportServer.config file to tell Reporting Services not
to use SSL at all, then restarted the web site, and now I can view the
properties, view the data source, and export files. If you want to try
this, open the file in notepad, look for the following string:
<Add Key="SecureConnectionLevel" Value="0"/>
make sure value is 0. that means no SSL. I found this in the sample
chaper for Hitchhikers Guide To Reporting Services. Its somewhere out
there on the internet.
Erik|||Hi Erik,
I receive the error both on by development machine and the production
server. The production website has a fully qualified domain name with a
valid SSL certificate. My development machine has a test certificate I
created with Makecert.exe. Both of the machines have the exact same
symptoms.
I have changed the Security Level within the RSReportServer.config on my
development machine. With the option set to 0, everything seems to
work. If the Security Level is set to 1, I start receiving the
underlying connection error. I also received the security warning when
rendering reports. If the level is set to 2, the just receive the
underlying connection error. If the level is set to 3, the site stops
working all together. I wish could just set the level to 0, but I can't
on the production site.
I did some research on the error itself. It seems to be a general
problem with the SOAP protocol and SSL. Since Web Services use SOAP and
SQL Reporting uses Web Services, I'm wonder if thats the primary cause
for the underlying connection error.
Thanks
Chad
cybermud wrote:
> Chad:
> I edited the RSReportServer.config file to tell Reporting Services not
> to use SSL at all, then restarted the web site, and now I can view the
> properties, view the data source, and export files. If you want to try
> this, open the file in notepad, look for the following string:
> <Add Key="SecureConnectionLevel" Value="0"/>
> make sure value is 0. that means no SSL. I found this in the sample
> chaper for Hitchhikers Guide To Reporting Services. Its somewhere out
> there on the internet.
> Erik
>|||Chad:
No SSL will be a no-go for me as well, but its nice to see some of the
other options. I wonder if the problem has to do with my SQL Server
not being a member of the domain that the certificate authority that
issued its certificate is a member of. Anyone...?
Erik|||This is not a valid solution to this problem.
Setting SecureConnectionLevel = 0 makes the report server NOT REQUIRE SSL.
As such, any links to the report server are made over an unprotected SSL
connection.
Your more likely solution is to edit the Report Manager configuration file
to change the REPORTSERVERURL property to be https://sql2/reportserver.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"cybermud" <cybermud@.gmail.com> wrote in message
news:1107797287.951689.55530@.g14g2000cwa.googlegroups.com...
> Chad:
> I edited the RSReportServer.config file to tell Reporting Services not
> to use SSL at all, then restarted the web site, and now I can view the
> properties, view the data source, and export files. If you want to try
> this, open the file in notepad, look for the following string:
> <Add Key="SecureConnectionLevel" Value="0"/>
> make sure value is 0. that means no SSL. I found this in the sample
> chaper for Hitchhikers Guide To Reporting Services. Its somewhere out
> there on the internet.
> Erik
>|||Lukasz:
Thank you for your reply. I have tried changing this value previosly
and it has had no effect on resolving my issue. I tried it again for
good measure, setting the REPORTSERVERURL to https://sql2/reportserver,
then changed the SECURECONNECTIONLEVEL to 3. Upon restarting IIS, I
cannot even view report manager, as I get an error of "The underlying
connection was closed: Could not establish trust relationship with
remote server."
If I change the SECURECONNECTIONLEVEL to 2, I can connect and view
reports, but I cannot view daa source properties, export reports, view
report properties, or save a new subscription.
Could this problem be because the CA that I used to issue the SSL
certificate is a domain member, and SQL2 is not a domain member? Would
joining SQL2 to my domain solve the issue?
Thanks in advance for any insight you may be able to provide.|||OK, I joined SQL2 to my domain.
Everything now works great with the SECURECONNECTIONLEVEL set to 3,
except I still can't export reports to any other format unless
SECURECONNECTIONLEVEL is set to 0. Any ideas?|||Hmm... so the CA was on the domain, but your SQL2 box was not on the domain?
What URL were you using to connect to the reportserver on this box?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"cybermud" <cybermud@.gmail.com> wrote in message
news:1107893981.484348.53620@.g14g2000cwa.googlegroups.com...
> OK, I joined SQL2 to my domain.
> Everything now works great with the SECURECONNECTIONLEVEL set to 3,
> except I still can't export reports to any other format unless
> SECURECONNECTIONLEVEL is set to 0. Any ideas?
>|||Lukasz:
Yes, CA was in domain, SQL2 was not. Now SQL2 is in the domain.
I was using https://sql2/ReportServer to access the report server. Now
I am using https://sql2.domain.com/ReportServer to access report
server, and https://sql2.domain.com/ReportManager to access the report
manager. And I can only export to a file if the security parameter is
set to 0. Any other setting and it looks as if the file is never
generated...when I click on the export link, it does not show a file
name like it does when the security parameter is set at 0.|||Gang:
I was looking through my log files for Reporting Services, and I
noticed the following entries:
w3wp!library!c70!02/14/2005-12:09:03:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!chunks!c70!02/14/2005-12:09:03:: i INFO: ###
GetReportChunk('RenderingInfo_EXCEL', 2), chunk was not found!
this=236c9c34-01c8-4806-9d14-4c06b744d28a
w3wp!cache!c70!02/14/2005-12:09:04:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!c70!02/14/2005-12:09:04:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
w3wp!library!574!02/14/2005-12:09:20:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!cache!574!02/14/2005-12:09:20:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!574!02/14/2005-12:09:20:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
w3wp!library!c70!02/14/2005-12:09:34:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!chunks!c70!02/14/2005-12:09:34:: i INFO: ###
GetReportChunk('RenderingInfo_MHTML', 2), chunk was not found!
this=236c9c34-01c8-4806-9d14-4c06b744d28a
w3wp!cache!c70!02/14/2005-12:09:34:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!c70!02/14/2005-12:09:34:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
w3wp!library!1254!02/14/2005-12:09:40:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!chunks!1254!02/14/2005-12:09:40:: i INFO: ###
GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
this=236c9c34-01c8-4806-9d14-4c06b744d28a
w3wp!cache!1254!02/14/2005-12:09:40:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!1254!02/14/2005-12:09:40:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
It seems as if I get a "Chunk Not Found" error when I try to export to
any format with SSL enabled.
Has anybody else encountered this? Has anyone else encountered a "file
nto found" error when trying to export into any format from Report
Manager?
When I export in vs.net it works fine, and if I turn SSL off on the web
site it will export fine...but not with SSL on.|||Hi Erik,
Sorry the delay in check this thread. I have not been able to solve the
SSL problem. I was able to create subscriptions through SSL using the
SQL Reporting SOAP APIs. It's more work, because you have to code your
own interface, but it does work through SSL.
Thanks
Chad
cybermud wrote:
> Gang:
> I was looking through my log files for Reporting Services, and I
> noticed the following entries:
> w3wp!library!c70!02/14/2005-12:09:03:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!chunks!c70!02/14/2005-12:09:03:: i INFO: ###
> GetReportChunk('RenderingInfo_EXCEL', 2), chunk was not found!
> this=236c9c34-01c8-4806-9d14-4c06b744d28a
> w3wp!cache!c70!02/14/2005-12:09:04:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!c70!02/14/2005-12:09:04:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> w3wp!library!574!02/14/2005-12:09:20:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!cache!574!02/14/2005-12:09:20:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!574!02/14/2005-12:09:20:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> w3wp!library!c70!02/14/2005-12:09:34:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!chunks!c70!02/14/2005-12:09:34:: i INFO: ###
> GetReportChunk('RenderingInfo_MHTML', 2), chunk was not found!
> this=236c9c34-01c8-4806-9d14-4c06b744d28a
> w3wp!cache!c70!02/14/2005-12:09:34:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!c70!02/14/2005-12:09:34:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> w3wp!library!1254!02/14/2005-12:09:40:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!chunks!1254!02/14/2005-12:09:40:: i INFO: ###
> GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
> this=236c9c34-01c8-4806-9d14-4c06b744d28a
> w3wp!cache!1254!02/14/2005-12:09:40:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!1254!02/14/2005-12:09:40:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> It seems as if I get a "Chunk Not Found" error when I try to export to
> any format with SSL enabled.
> Has anybody else encountered this? Has anyone else encountered a "file
> nto found" error when trying to export into any format from Report
> Manager?
> When I export in vs.net it works fine, and if I turn SSL off on the web
> site it will export fine...but not with SSL on.
>

Wednesday, March 7, 2012

Another - Cannot connect to Database Issue.

I've read several issues and resolutions related to my problem, but none of the resolutions have worked.

I have Visual Studio 2005 installed on my local machine.

My SQL Server 2005 is on a Member Server.

On a fresh Web application, I have no problem accessing the ASP.NET Configuration File to set up Users and Roles. The reason is obvious, the data is in SQLExpress and Local. You can see the database ASPNETDB.MDF residing under the App_Data Folder of the project.

How do I get this in the SQL Server 2005 System on the Member server?

Whenever I do the Copy Web Site procedure to post my application to the Web Server, I get an error stating that SQLExpress is not installed on the target server. That's correct because my target server has SQL 2005.

I've tried changing the Machine.Config. I've tried changing the Web.Config. Neither solution was successful. Both send me errors that no connection can be made.

I've also seen various recommendations concerning the ASPNET database. One camp says store your members and roles in the same database as your Data. The other camp says keep them seperate. Is there a rhyme or reason for either one? The data housed in this Database is what I'm after but how in the world to I get there?

I am on week #4 trying to get something going. I'm starting to feel like Thomas Edison when he said he discovered 900 different ways his experiment did not work.

Hi,

you will have to copy the database to the remote server, attach it on the (non SQL Server Express) system using the GUI or sp_attachdb. THen you will have to change the connectionstring not to use the UserInstance anymore (because you now will have the instance connected to a server instance rather than a user instance).

HTH, Jens K. Suessmeyer.

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

OK. For my case, I think I found the solution.

Here is the situation:

SQL Server 2005 is on a member Server.

VS 2005 is on Windows XP Work Station.

Development project is on Windows XP Work Station.

When running ASP.NET Configuration, all Users, roles, etc. end up in the App_Data folder in the ASPNETDB database. After testing application, it is copied to Server but the ASPNETDB errors out because it is looking for SQLExpress and not SQL Server 2005.

Here is what I did to get around the issue:

1. Made sure the SQL Server DB had the ASPNETDB.

if it is not there,

run aspnet_regsql.exe

click next

Select Configure SQL server for applications

Click next

Add the server name and LEAVE the database as <default>

Click next

Click next

That will create the ASPNETDB.

2. From inside my application, used Server Explorer to Connect to the SQL ASPNETDB database.

3. Right click on the ASPNETDB, select properties, locate connection string, and copy connection string.

4. Inside my Web.Config file, I created a new connection string as such.

<appSettings/>

<connectionStrings>

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=<myServer>;Initial Catalog=aspnetdb;Integrated Security=True;"/>

</connectionStrings>

5. I deleted the ASPNETDB from the app_data folder on my local drive.

Now when I run ASP.NET Configuration or my application, I'm posting data to the SQL Server and not the App_Data folder.

While looking for solutions, I ran across a couple of sites that recommended having users and roles inside the application database. Other recommended using the ASPNETDB database. I never did find a specific answer for one way or the other. All I know, what I have is now working and after 4 weeks of frustration, I'm going to stick with this for a while until someone can show me a better way.

Sunday, February 19, 2012

Analyzer 2000 using 2005 database gives error

I am using Query Analyzer 2000 and pointing at a 2005 database. I keep
getting an error that I think it related to some code that is selecting data
into a temp table. The procedure runs perfect on a 2000 database.
here is the message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas?
Thank you
RichardAnd the procedure? Can we see it?
ML
http://milambda.blogspot.com/|||CREATE PROCEDURE arLedgerListingGenAP
@.ProcMode CHOICE = NULL, -- 'R'- Report Mode, ' ' or
NULL - Customer Care Mode
@.CustomerKey SMARTKEY,
@.StartDate DATETIME = NULL,
@.EndDate DATETIME = NULL,
@.cStartDate VARCHAR(40),
@.cEndDate VARCHAR(40),
@.gsUserLanguage LANG
AS
SET NOCOUNT ON
SET ANSI_NULLS OFF
DECLARE @.BeginingBalance AMOUNT2DEC,
@.EndingBalance AMOUNT2DEC
IF @.StartDate IS NULL OR LTRIM(RTRIM(@.cStartDate)) = ''
SELECT @.StartDate = "1900-01-01 00:00:00"
IF @.EndDate IS NULL OR LTRIM(RTRIM(@.cEndDate)) = ''
SELECT @.EndDate = CAST(CAST(YEAR(@.EndDate) as varchar) + CASE
WHEN
LEN(MONTH(@.EndDate)) = 1
THEN
'0' + CAST(MONTH(@.EndDate) AS varchar)
ELSE
CAST(MONTH(@.EndDate) AS varchar)
END + CASE
WHEN LEN(DAY(@.EndDate)) = 1
THEN '0' + CAST(DAY(@.EndDate) AS varchar)
ELSE CAST(DAY(@.EndDate) AS varchar)
END
+ ' 23:59:59' AS datetime)
IF @.cStartDate IS NOT NULL AND LTRIM(RTRIM(@.cStartDate)) <> ''
SELECT @.StartDate = @.cStartDate
IF @.cEndDate IS NOT NULL AND LTRIM(RTRIM(@.cEndDate)) <> ''
SELECT @.EndDate = CAST(CAST(YEAR(@.cEndDate) as varchar) + CASE
WHEN
LEN(MONTH(@.cEndDate)) = 1
THEN
'0' + CAST(MONTH(@.cEndDate) AS varchar)
ELSE
CAST(MONTH(@.cEndDate) AS varchar)
END + CASE
WHEN LEN(DAY(@.cEndDate)) = 1
THEN '0' + CAST(DAY(@.cEndDate) AS varchar)
ELSE CAST(DAY(@.cEndDate) AS varchar)
END
+ ' 23:59:59' AS datetime)
-- The temp tables used in the Ledger Listind Stored procedures.
-- i.e. arLedgerListingGenAP.sql.
CREATE TABLE #Activity
(ActivityKey SMARTKEY IDENTITY(1,1),
ActivityDate DATETIME,
Amount AMOUNT2DEC NULL,
Discount AMOUNT2DEC NULL,
InvoiceNo INVOICENO NULL,
ContractKey SMARTKEY NULL,
CreditInvoiceNo INVOICENO NULL,
TranType VARCHAR(8) NULL,
ShortDesc CHAR(6) NULL,
ActDesc MAXCHAR NULL,
TransDescription MAXCHAR NULL,
InvoiceTotal AMOUNT2DEC NULL,
InvoiceBalance AMOUNT2DEC NULL,
AccountBalance AMOUNT2DEC NULL,
EmployeeCode CHAR(3) NULL,
PaymentReference DESCRIPTION NULL,
RecordType CHAR(5) NULL,
Target INVOICENO NULL,
TransactionGroupNo INTEGER NULL,
TransactionType VARCHAR(40) NULL,
AmtAffectingBal AMOUNT2DEC NULL,
PaymentApplicationKey SMARTKEY NULL,
InvoiceType CHOICE NULL,
ReversalReference CHOICE NULL,
DatePaid DATETIME NULL,
InvoiceTerms DESCRIPTION NULL,
TermsCode CHAR(3) NULL,
DueDate DATETIME NULL,
PastDueDays INTEGER NULL,
ForeColor INTEGER NULL,
CustomerKey SMARTKEY,
OwnerKey SMARTKEY NULL,
PaymentOrInvoice CHAR(1) NULL,
InvoiceDate DATEONLY NULL,
SortinvoiceNo INVOICENO NULL,
TranFrom CHOICE NULL)
CREATE TABLE #TempActivity (ActivityKey SMARTKEY,
InvoiceNo INVOICENO,
TransactionType VARCHAR(40),
TrxCnt INTEGER,
TransDescription MAXCHAR)
/* CreditInvoiceNo INVOICENO,
Amount AMOUNT2DEC,
Total AMOUNT2DEC,
AppliedCredits AMOUNT2DEC,
CreationDate DATETIME,*/
-- Find all Invoice Transaction for the customer. We have to go to the line
item to find out what was done, Refunds, Returns, etc.
-- Get also the pending credits and their Status.
/*
INSERT INTO #TempActivity EXEC arLedgerListingGen1AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
*/
-- Store all Transaction for the customer in a temp table.
INSERT INTO #Activity EXEC arLedgerListingGen2AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
--IF EXISTS (SELECT * FROM #Activity WHERE ShortDesc LIKE '%MT' OR
ShortDesc = 'DM')
BEGIN
EXEC arLedgerListingGen1AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
UPDATE X
SET TransactionType = Y.TransactionType,
TransDescription = Y.TransDescription,
ShortDesc = LTRIM(RTRIM(ShortDesc)) + 'MT'
FROM #Activity X
JOIN #TempActivity Y ON X.InvoiceNo = Y.InvoiceNo
END
EXEC arLedgerListGetBegBalGenAP @.CustomerKey, @.StartDate,
@.BeginingBalance OUTPUT
IF @.BeginingBalance IS NULL
SELECT @.BeginingBalance = 0
EXEC arLedgerListGetEndBalGenAP @.CustomerKey, @.EndDate, @.EndingBalance
OUTPUT
IF @.EndingBalance IS NULL
SELECT @.EndingBalance = 0
-- Get the Customerkey, RecordType and Contract key of the record that was
transferred IN/OUT, get running balance per Account and
-- per Invoice and Update Balances.
SELECT a.ActivityKey, a.ActivityDate, a.Amount, a.Discount, a.InvoiceNo,
a.ContractKey, a.CreditInvoiceNo,
a.TranType, ShortDesc = LEFT(a.ShortDesc,5), a.ActDesc,
a.TransDescription, a.InvoiceTotal,
InvoiceBalance = CASE
WHEN a.InvoiceNo NOT IN ('CREDIT',
'ACCCRD', 'ACCDEP', 'SECDEP','PREPAY')
THEN a.AmtAffectingBal + (SELECT
ISNULL(SUM(c.AmtAffectingBal), 0.00)
FROM
#Activity c
WHERE
c.ActivityKey < a.ActivityKey
AND
a.InvoiceNo = c.InvoiceNo)
ELSE 0.00
END,
AccountBalance = a.AmtAffectingBal + @.BeginingBalance + (SELECT
ISNULL(SUM(c.AmtAffectingBal),0.00)
FROM
#Activity c
WHERE
c.ActivityKey < a.ActivityKey),
a.EmployeeCode, a.PaymentReference, a.RecordType, a.Target,
a.TransactionGroupNo,
a.TransactionType, a.AmtAffectingBal, a.PaymentApplicationKey,
a.InvoiceType, a.ReversalReference,
a.DatePaid, a.InvoiceTerms, a.TermsCode, a.DueDate,
a.PastDueDays, a.ForeColor, a.CustomerKey, a.OwnerKey,
TransferCustomerKey = b.CustomerKey, TransferRecordType =
b.RecordType, TransferContractKey = b.ContractKey,
a.PaymentOrInvoice, a.InvoiceDate, a.SortinvoiceNo, a.TranFrom
INTO #Activity2
FROM #Activity a
LEFT JOIN arPaymentApplications b ON
a.TransactionGroupNo = b.TransactionGroupNo
AND
LEFT(a.RecordType, 1) = 'X'
--This record contains
the sum of all PREPAY adjustments for all customers
AND a.RecordType <>
'XIADP'
--XOADP is a Transfer
Out of Access Deposit for PREPAY
--In this case the
CustomerKey's must match.
AND a.CustomerKey =
CASE
WHEN a.RecordType = 'XOADP'
THEN a.CustomerKey
END
--Else any other X
RecordTypes are transfers of Security Deposits
--The CustomerKey's do
not match here because we are picking up who
--the moneies are
transfered to.
AND a.CustomerKey <>
CASE
WHEN a.RecordType <> 'XOADP'
THEN b.CustomerKey
END
-- get the Descriptions.
IF @.ProcMode IS NULL OR @.ProcMode = ' '
BEGIN
EXEC arLedgerListingGen3AP
@.BeginingBalance = @.BeginingBalance,
@.EndingBalance = @.EndingBalance,
@.gsUserLanguage = @.gsUserLanguage
END
ELSE
BEGIN
EXEC arLedgerListingGen4AP
@.BeginingBalance = @.BeginingBalance,
@.EndingBalance = @.EndingBalance,
@.gsUserLanguage = @.gsUserLanguage
END
GO
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8899258C-9E33-4876-B54F-408943B42727@.microsoft.com...
> And the procedure? Can we see it?
>
> ML
> --
> http://milambda.blogspot.com/|||After going through the code there's one issue that stands out - your
INSERT...EXECUTE statements lack column declarations. Try fixing that, e.g.
insert <table>
(
<column list>
)
exec <procedure> <parameter list>
Make certain that the columns in the INSERT statement match the columns in
the result set of the procedure.
ML
http://milambda.blogspot.com/

Monday, February 13, 2012

Analysis Services Lock-ups

I have a general comment and then a question related to Analysis Services 2005. It seems to me that AS2K (the previous version) scaled out better - because a significant amount of CPU-intensive calculation was done on the client machine. This was especially nice when user queries were:

Poorly formed / unreasonable / just-plain-stupid Very intensive but we-must-have-the-data

In the case of these types of user queries, the client machine's CPU would become swamped but the Analysis Services (AS2K) server could remain responsive in serving data to other users.

In Analysis Services 2005, however, a couple of bad user queries can quickly lock up the OLAP cubes tighter than Alcatraz. (Even when SSAS is running on a high performance 64 bit server.) It is especially bad when some partition update processing is trying to commit at the same time.

I was hoping the performance guide would discuss some ideas on how to scale out Analysis Services 2005 to a large number of users (ie. 10s or 100s). Lets face it, if you get a lot of users hitting these OLAP cubes then you can bet a good percentage of them won't know the difference between a reasonable and an unreasonable query.

Any thoughts or references to documention about this? Are there ways of limiting the negative impact that novice users have on the queries that other people are trying to run?


Thanks in advance, David

You are right, AS 2005 has centralized all of the calculations and as a result may not scale out as well as AS2K.

AS 2005 is pretty much version 1 of a new architecture and there is not too much that can be done about this.

There is a Timeout option which you can add to the connection string to force queries to timeout after a given amount of time, but this could be hard to administer.

The only other option I can think of is that you can synchronize a database across more that one server and set them up in an NLB cluster, but this would be an expensive option if you were only adding the second server in order to be able to cater for runaway queries. Even then I am not sure if the NLB software is smart enough to not still have some clients being "locked out" by a runaway query.

|||

Thanks for the reply. What is an NLB cluster?

We have a growing set of mission-critical, canned SSRS reports that read data from our new SSAS cubes. These reports are developed within the I.S. department and have well-defined (pre-defined) data requirements. In the very least I'd like these reports to remain available and responsive. Does anybody have ideas on preventing general OLAP users from being able to negatively imact our canned reports?

Could I back up the entire SSAS database after nightly processing and re-deploy it to a different SSAS server which only canned reports have access to? Does anybody have experience scaling SSAS in this way?

Thanks, David

|||

NLB - Network Load Balanced

You can set up SSAS in a configuration similar to a web farm where you can have mulitple SSAS servers all with an identical copy of the same database. I think the default is that requests are just handled in a round robin fashion, with each node taking turns at servicing an incoming session. There may be 3rd party solutions that are more sophisticated and allocate connections based on the load that each of the servers is under, but I'm not familiar with anything like this myself.

You could do a back up and re-deploy. SSAS actually has a built-in feature called synchronization which does this, see details here http://msdn2.microsoft.com/en-us/library/ms174928.aspx.

|||

Thanks for the help. Do you know how licensing would work in this kind of environment with sychronized SSAS databases?

I'm thinking that the extra SSAS services should be free. Users are telling me that these new SSAS cubes max out at 2 or 3 concurrent users. Depending on the types of data they are pulling, they are constantly locking each other up.

(No, don't blame my cube design. I was already following those best practices in Oct '05. )

|||

No, I'm by no means an expert on licensing, but in this case the extra instances of SSAS would require full SQL Server licenses. That said you should be getting way more than 2-3 concurrent users. I though we were talking about scaling out to 100's of users

(No, don't blame my cube design. I was already following those best practices in Oct '05. )

Are you saying that your design matches the best practices that were being promoted in Oct '05 or the ones that are being promoted now? Back in '05 Microsoft was saying to put everything into one big cube and let SSAS sort it out, now they are saying that we are better off breaking off logically related groups of measure groups into separate cubes.

Unfortunately sometimes the perfect logical models do not perform well and you may need to compromise on some aspects of the design in order to achieve your performance requirements.

Do you have any idea where the bottlenecks are? Is the server disk, cpu or memory bound? Do you know if it is specific calculations that are slow (some of the calcs added by the time intelligence wizard are not optimal - searching on this forum should turn up some helpful hints if this is a potential issue)? Have you tried applying usage based aggregtions? Do you have many parent child dimensions?|||

I was following the current best practices back then. (Aren't I modest. .) I never bought into the silly "supercube" approach which attempts to gather together the entire warehouse into a singularity (single .cube file & related version control). My concerns were primarily from a software development perspective. Designing, testing, and maintaining something this monolithic is impractical. Every design change would involve a tremendous amount of regression testing and tremendous risk. In general, most software development involves creating small things and re-using them to make bigger things. In addition to these abstact software development concerns, I had some practical concerns that are related directly to OLAP. For example, there are certain design issues involving cube-dimensions (visibility, agg design, etc.) which should definitely NOT apply to all measure groups in an entire data warehouse. I remember having this discussion on the Yukon Ascend forums with Mosha when he was still recommending it. It would be nice if those threads were available here...

We want to be able to scale out but SSAS is very bound up on both CPU and data processing concurrency problems. Normally 2 or 3 concurrent users is all we can get (ie. users running queries at exactly the same time) .

In general our CPU problems occur in queries when dicing calculated members on large dimensions and also when using time intelligence. SP2 helped a little bit with time intelligence - but query execution still reverts to cell-by-cell evaluation when the MDX query optimization engine gets a little confused. I expect these types of execution plans are a fall-back and are unavoidable at times. The main problem that I'd like to solve is this architecture which forces everyone else to suffer along with the user who is running the demanding query.

|||

Interesting thread.

We're experiencing the exact same issues over here - it's nice to know we're not alone in the behavior we're seeing.

We process data every hour (both fact and dimensional) as it's a mandatory business requirement (no if's, and's, or but's). Two of these dimensions are in excess of two million members so they take a couple minutes to process. To make matters worse, our aggregations are mainly flexible rather than rigid - data floats around a lot. Our total size is appx 500GB.

At any given time, within a minute we have about 10-15 users querying the cube. We've found that a single user can throw everything out of wack, especially when processing is going on.

We looked at synchronization but were not impressed by the performance. Since our aggs are mainly flexible a big chunk of files have to be moved.

I'm curious as to how people are scaling out to handle complex queries and rapidly changing data.

|||

Have you had a look at the ForceCommitLock setting. I did a post about this recently here http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx

With the default setting though you should not see any more than 30 seconds added to the front of the query where it is waiting for the pending commit lock to be released before it even starts. But maybe if you get enough queries queued up that it is thrashing a bit after the lock is released. It would be interesting to run some profiler traces and see if there is a definite correlation here.

|||

We are having some success with distributed cubes. It ultimately doesn't help with the final version of this problem - tons of people with bad queries will still bring the system down - but I think is better than round-robin nlb servers because 1) cube processing is cut down significantly, and 2) for few users they see a benefit to the scaling vs no beneift with nlb.

For #2, what I mean is that if you have a 60-second MDX query, and have 6 servers it will take maybe 10 seconds (assuming perfect spread, we are seeing 'almost perfect' spread in practice, so its close enough for now) for a single user - and if you had 6 servers with identical copies of the cubes when there aren't tens or hundres of users that single query still takes 60 seconds. Of course this is also mitigated by how you spread the data - if it is by week or month and most queries are last 5 days only, then the other method would be better as in a distributed environment these would all go to a single server.

Not a final answer, just one more piece of the puzzle.

Oh - and we had so many problems with data transmission across bad corporate networks and slow laptops with low memory that even in MSAS 2000 we had to basically disable client side processing anyhow - it wasn't practical at *any* of the sites we've developed for! We used provider string parameters to force processing to the server.

|||

Hi,

I'm a BI consultant. In recent days I did a migration from SQL Server Analysis Services 2000 to 2005 and did completely redesign the cubes by following the guidelines promoted in Oct'05. I was not really amused reading the exact opposite in the olap best practices in March 2007. This sounds really like a kind of joke.

I am very angry about the situation and NOT satisfied by the product SQL Server 2005 at all. And none of the "cool guys" from MS really seems to have an opinion about the paradigm change and about all the still existing bugs, especially in SSAS. Maybe they discovered those bugs in their software and were to lazy or simply not able to solve them. Then they decided to recommend a completely other architecture. Maybe the bugs would not occur any more and all the problems are solved?

I'm not sure if they have only Micky Mouse cubes there in Redmond containing 2 rows of data to test their software, but maybe this is the case. But this is a very very bad style of software development and delivering.

This is just my opinion after about 1 year experience with SQL Server 2005. (having about 6 years BI experience with SQL Server 2000)

Stefoon

Analysis Services Lock-ups

I have a general comment and then a question related to Analysis Services 2005. It seems to me that AS2K (the previous version) scaled out better - because a significant amount of CPU-intensive calculation was done on the client machine. This was especially nice when user queries were:

Poorly formed / unreasonable / just-plain-stupid Very intensive but we-must-have-the-data

In the case of these types of user queries, the client machine's CPU would become swamped but the Analysis Services (AS2K) server could remain responsive in serving data to other users.

In Analysis Services 2005, however, a couple of bad user queries can quickly lock up the OLAP cubes tighter than Alcatraz. (Even when SSAS is running on a high performance 64 bit server.) It is especially bad when some partition update processing is trying to commit at the same time.

I was hoping the performance guide would discuss some ideas on how to scale out Analysis Services 2005 to a large number of users (ie. 10s or 100s). Lets face it, if you get a lot of users hitting these OLAP cubes then you can bet a good percentage of them won't know the difference between a reasonable and an unreasonable query.

Any thoughts or references to documention about this? Are there ways of limiting the negative impact that novice users have on the queries that other people are trying to run?


Thanks in advance, David

You are right, AS 2005 has centralized all of the calculations and as a result may not scale out as well as AS2K.

AS 2005 is pretty much version 1 of a new architecture and there is not too much that can be done about this.

There is a Timeout option which you can add to the connection string to force queries to timeout after a given amount of time, but this could be hard to administer.

The only other option I can think of is that you can synchronize a database across more that one server and set them up in an NLB cluster, but this would be an expensive option if you were only adding the second server in order to be able to cater for runaway queries. Even then I am not sure if the NLB software is smart enough to not still have some clients being "locked out" by a runaway query.

|||

Thanks for the reply. What is an NLB cluster?

We have a growing set of mission-critical, canned SSRS reports that read data from our new SSAS cubes. These reports are developed within the I.S. department and have well-defined (pre-defined) data requirements. In the very least I'd like these reports to remain available and responsive. Does anybody have ideas on preventing general OLAP users from being able to negatively imact our canned reports?

Could I back up the entire SSAS database after nightly processing and re-deploy it to a different SSAS server which only canned reports have access to? Does anybody have experience scaling SSAS in this way?

Thanks, David

|||

NLB - Network Load Balanced

You can set up SSAS in a configuration similar to a web farm where you can have mulitple SSAS servers all with an identical copy of the same database. I think the default is that requests are just handled in a round robin fashion, with each node taking turns at servicing an incoming session. There may be 3rd party solutions that are more sophisticated and allocate connections based on the load that each of the servers is under, but I'm not familiar with anything like this myself.

You could do a back up and re-deploy. SSAS actually has a built-in feature called synchronization which does this, see details here http://msdn2.microsoft.com/en-us/library/ms174928.aspx.

|||

Thanks for the help. Do you know how licensing would work in this kind of environment with sychronized SSAS databases?

I'm thinking that the extra SSAS services should be free. Users are telling me that these new SSAS cubes max out at 2 or 3 concurrent users. Depending on the types of data they are pulling, they are constantly locking each other up.

(No, don't blame my cube design. I was already following those best practices in Oct '05. )

|||

No, I'm by no means an expert on licensing, but in this case the extra instances of SSAS would require full SQL Server licenses. That said you should be getting way more than 2-3 concurrent users. I though we were talking about scaling out to 100's of users

(No, don't blame my cube design. I was already following those best practices in Oct '05. )

Are you saying that your design matches the best practices that were being promoted in Oct '05 or the ones that are being promoted now? Back in '05 Microsoft was saying to put everything into one big cube and let SSAS sort it out, now they are saying that we are better off breaking off logically related groups of measure groups into separate cubes.

Unfortunately sometimes the perfect logical models do not perform well and you may need to compromise on some aspects of the design in order to achieve your performance requirements.

Do you have any idea where the bottlenecks are? Is the server disk, cpu or memory bound? Do you know if it is specific calculations that are slow (some of the calcs added by the time intelligence wizard are not optimal - searching on this forum should turn up some helpful hints if this is a potential issue)? Have you tried applying usage based aggregtions? Do you have many parent child dimensions?|||

I was following the current best practices back then. (Aren't I modest. .) I never bought into the silly "supercube" approach which attempts to gather together the entire warehouse into a singularity (single .cube file & related version control). My concerns were primarily from a software development perspective. Designing, testing, and maintaining something this monolithic is impractical. Every design change would involve a tremendous amount of regression testing and tremendous risk. In general, most software development involves creating small things and re-using them to make bigger things. In addition to these abstact software development concerns, I had some practical concerns that are related directly to OLAP. For example, there are certain design issues involving cube-dimensions (visibility, agg design, etc.) which should definitely NOT apply to all measure groups in an entire data warehouse. I remember having this discussion on the Yukon Ascend forums with Mosha when he was still recommending it. It would be nice if those threads were available here...

We want to be able to scale out but SSAS is very bound up on both CPU and data processing concurrency problems. Normally 2 or 3 concurrent users is all we can get (ie. users running queries at exactly the same time) .

In general our CPU problems occur in queries when dicing calculated members on large dimensions and also when using time intelligence. SP2 helped a little bit with time intelligence - but query execution still reverts to cell-by-cell evaluation when the MDX query optimization engine gets a little confused. I expect these types of execution plans are a fall-back and are unavoidable at times. The main problem that I'd like to solve is this architecture which forces everyone else to suffer along with the user who is running the demanding query.

|||

Interesting thread.

We're experiencing the exact same issues over here - it's nice to know we're not alone in the behavior we're seeing.

We process data every hour (both fact and dimensional) as it's a mandatory business requirement (no if's, and's, or but's). Two of these dimensions are in excess of two million members so they take a couple minutes to process. To make matters worse, our aggregations are mainly flexible rather than rigid - data floats around a lot. Our total size is appx 500GB.

At any given time, within a minute we have about 10-15 users querying the cube. We've found that a single user can throw everything out of wack, especially when processing is going on.

We looked at synchronization but were not impressed by the performance. Since our aggs are mainly flexible a big chunk of files have to be moved.

I'm curious as to how people are scaling out to handle complex queries and rapidly changing data.

|||

Have you had a look at the ForceCommitLock setting. I did a post about this recently here http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx

With the default setting though you should not see any more than 30 seconds added to the front of the query where it is waiting for the pending commit lock to be released before it even starts. But maybe if you get enough queries queued up that it is thrashing a bit after the lock is released. It would be interesting to run some profiler traces and see if there is a definite correlation here.

|||

We are having some success with distributed cubes. It ultimately doesn't help with the final version of this problem - tons of people with bad queries will still bring the system down - but I think is better than round-robin nlb servers because 1) cube processing is cut down significantly, and 2) for few users they see a benefit to the scaling vs no beneift with nlb.

For #2, what I mean is that if you have a 60-second MDX query, and have 6 servers it will take maybe 10 seconds (assuming perfect spread, we are seeing 'almost perfect' spread in practice, so its close enough for now) for a single user - and if you had 6 servers with identical copies of the cubes when there aren't tens or hundres of users that single query still takes 60 seconds. Of course this is also mitigated by how you spread the data - if it is by week or month and most queries are last 5 days only, then the other method would be better as in a distributed environment these would all go to a single server.

Not a final answer, just one more piece of the puzzle.

Oh - and we had so many problems with data transmission across bad corporate networks and slow laptops with low memory that even in MSAS 2000 we had to basically disable client side processing anyhow - it wasn't practical at *any* of the sites we've developed for! We used provider string parameters to force processing to the server.

|||

Hi,

I'm a BI consultant. In recent days I did a migration from SQL Server Analysis Services 2000 to 2005 and did completely redesign the cubes by following the guidelines promoted in Oct'05. I was not really amused reading the exact opposite in the olap best practices in March 2007. This sounds really like a kind of joke.

I am very angry about the situation and NOT satisfied by the product SQL Server 2005 at all. And none of the "cool guys" from MS really seems to have an opinion about the paradigm change and about all the still existing bugs, especially in SSAS. Maybe they discovered those bugs in their software and were to lazy or simply not able to solve them. Then they decided to recommend a completely other architecture. Maybe the bugs would not occur any more and all the problems are solved?

I'm not sure if they have only Micky Mouse cubes there in Redmond containing 2 rows of data to test their software, but maybe this is the case. But this is a very very bad style of software development and delivering.

This is just my opinion after about 1 year experience with SQL Server 2005. (having about 6 years BI experience with SQL Server 2000)

Stefoon

Thursday, February 9, 2012

Analysis Services 2005 using only one CPU

Hi,

I am facing issue with our client related to Analysis Services 2005 using only 13% of CPU.

Our client is running on server with 8 CPUs, 15GB of RAM. When request being made to execute MDX, only 13% of CPU is utilized. It is like using only one CPU. When additional concurrent user is added, then CPU usage increases.

The Analaysis Services being used is Evaluation Copy at this point.

I checked all possible settings in Analysis Services, like to increase concurrent threads, and other settings, but it brings no result. CPU is stuck at 13% for single user.

So, is it like limitation of SQL Server Evaluation copy that it does not utilize full CPU, or Analysis Services is not meant to use more that 1 CPU (which is unlikely)?

Can you help me to troubleshoot this issue.

Thank you

Analysis Services OLAP engine is roughly divided into two major components: 1) query processor and calculation engine 2) storage engine. Storage engine is multithreaded to support parallel access to multiple partitions. The calculation engine is mainly singlethreaded. If your query is calculation intensive instead of data intensive, you would only see a single CPU maxed out.|||

thanks for clarifying the internals of Analysis Services.

At the moment i have only one partition in the OLAP Database. The performance drop when several crossjoin statements used in the MDX. I will try to optimize my mdx statements, but is there anyway to optimize calculation engine?

Will it help calculation engine if i divide my cube into several partitions? Our client data is pretty big, 1GB for 8 days data. Eventually, our client wants to keep 60 days data in the cube, and historical data being purged or archived. My intention was also eventually create partitions either monthly, or weekly as it is implemented in Project REAL.

So, what are the way you can suggest to optimize calculating engine?

Thank you.