Procedure Time out: code

Showing posts with label code. Show all posts

Wednesday, March 28, 2012

Professionally Handling ADO Logins

Hello -- I'm curious to know how most folks write VB code to connect to
their database using ADO without hardcoding the values in the application.
I frequently see books that allow the user to enter a username/password
within a login form and pass this information to the Conneciton object
Open() method. However, they then turn around and hardcode the server
name!!!
It would appear even attempting to use ADO Bound Controls in a professinoal
application is totally out of the question; since you must set the
connection info at design-time within its properties.
I was wondering if it made sense to display a logon form requesting the
login name/password, and then having a combo box that enumerated all the SQL
Servers on the network using SQLDMO. The users selection can be saved to
the registry or to an INI file and pre-filled in the box the next time the
application is executed...
Any help, ideas, or feedback would be appreciated.
--
...david
I you wish to reply to me personnally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!I think there are multiple options you have for making the connection to the
database transparent to your application.
(1) You could use a DSN name in your code and possibly show the user the
list of configured DSN's in the system and then connect based on the user
selection.
(2) You could use a UDL file that contains information regarding the login
information.
(3) You could collect user-name, password and also show the list of servers
in the network for the the user to select and then form the connection
string yourself based on the input parameters and then connect.
In my experience, I've found (1) and (3) to be most popular.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"DavidM" <scandal_123@.cox.net> wrote in message
news:%234KT5atoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> Hello -- I'm curious to know how most folks write VB code to connect to
> their database using ADO without hardcoding the values in the application.
> I frequently see books that allow the user to enter a username/password
> within a login form and pass this information to the Conneciton object
> Open() method. However, they then turn around and hardcode the server
> name!!!
> It would appear even attempting to use ADO Bound Controls in a
professinoal
> application is totally out of the question; since you must set the
> connection info at design-time within its properties.
> I was wondering if it made sense to display a logon form requesting the
> login name/password, and then having a combo box that enumerated all the
SQL
> Servers on the network using SQLDMO. The users selection can be saved to
> the registry or to an INI file and pre-filled in the box the next time the
> application is executed...
> Any help, ideas, or feedback would be appreciated.
>
> --
> ...david
> I you wish to reply to me personnally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

Production server error

Hello,
I have reports that have embedded code from a custom assembly.
In design, they work fine. When I deploy them and run them via the
Report Manager on production I get the following error in the cells
where I have this code running:
"Error in method xxxxxx - Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed."
I have followed all previous articles, made changes to .config files,
etc., etc. Given the assembly full trust, etc. Still no luck.
Has ANYONE actually got this to work? Crystal reports was a pain, but
at least it worked!
Thanks for any help with this.
FabioIt sounds like you did not explicitly assert permissions to open a database
connection. Unless you assert (!) the permission explicitly, it will fail
with a security exception. Example for opening a connection to a SQL Server
in custom code / custom assemblies:
...
SqlClientPermission permission = new
SqlClientPermission(PermissionState.Unrestricted);
try
{
permission.Assert(); // Assert security permission !!!
SqlConnection conn = new SqlConnection("...");
conn.Open();
...
}
BTW: you don't need FullTrust for using the SqlClient in a custom assembly,
because the SqlClient is enabled for partial trust scenarios. Just check the
MSDN documentation on the SqlClientPermission class.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
<fassmann@.gmail.com> wrote in message
news:1124303487.138374.35130@.g43g2000cwa.googlegroups.com...
> Hello,
> I have reports that have embedded code from a custom assembly.
> In design, they work fine. When I deploy them and run them via the
> Report Manager on production I get the following error in the cells
> where I have this code running:
> "Error in method xxxxxx - Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> failed."
> I have followed all previous articles, made changes to .config files,
> etc., etc. Given the assembly full trust, etc. Still no luck.
> Has ANYONE actually got this to work? Crystal reports was a pain, but
> at least it worked!
> Thanks for any help with this.
> Fabio
>|||Thanks Robert.
I am explicity asserting permissions. Here's the code I'm using. Am I
missing something?
Try
Dim permission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
permission.Assert()
'Use the MS data access app block to get the data reader
dr = _myDBAccess.ExecuteReader(_myConn,
CommandType.StoredProcedure, "Trader_GetTransByAssetID", New
SqlParameter("@.AssetID", nAssetID))
'Loop thru all transactions to build arraylist of
transaction objects
While (dr.Read())
objTrans = New Transaction(dr.GetInt32(COL_TRANSID),
dr.GetDateTime(COL_TRADEDATE), dr.GetSqlMoney(COL_DOLLARS).ToDouble,
sShareClass, 0.0, dr.GetSqlDecimal(COL_SHARES).ToDouble,
dr.GetSqlDecimal(COL_PRICE).ToDouble,
CType(dr.GetSqlInt16(COL_CALCSHAREBALANCE).ToString, Integer),
CType(dr.GetSqlInt16(COL_CALCCOSTBASIS).ToString, Integer))
arlTrans.Add(objTrans)
End While
Catch ex As Exception
_myErrorMessage += " Error in method
CDSC.BuildTransArray - " & ex.Message
Finally
'Clean Up
If Not (IsNothing(dr)) Then
dr.Close()
End If
End Try
Thanks for your help.|||OK, got it fixed.
You have to explicitly open the connection everywhere you use this. So,
we've got it added to every function/procedure within our assembly.
So not oly are we using the ExecuteReader, but we're opening the
connection each time as well.|||I have a similar problem, could you please give some example on this. when
you say
> So not oly are we using the ExecuteReader, but we're opening the
> connection each time as well.
do you mean you declare explicitly
Dim cn as sqlconnection = new sqlconnection(connectionstring)
after sqlclientpermission
From your example,
Try
Dim permission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
permission.Assert()
-----
' Dim _myConn as sqlconnection = new sqlconnection(connectionstring)
(adding this solved your issue?)
----
'Use the MS data access app block to get the data reader
dr = _myDBAccess.ExecuteReader(_myConn,
CommandType.StoredProcedure, "Trader_GetTransByAssetID", New
SqlParameter("@.AssetID", nAssetID
Appreciate your help on this.
--
kvs
"fassmann@.gmail.com" wrote:
> OK, got it fixed.
> You have to explicitly open the connection everywhere you use this. So,
> we've got it added to every function/procedure within our assembly.
> So not oly are we using the ExecuteReader, but we're opening the
> connection each time as well.
>|||Ok, here is what I tried but still getting error. Could you please tell me
what am I doing wrong or missing?
Dim permission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
permission.Assert()
'open connection explicitly
Dim cn As SqlConnection = New SqlConnection(cns)
cn.Open()
Try
Return SqlDataAccess.ExecuteDataSet(cn, sql, arp)
Finally
cn.Dispose()
End Try
--
Thanks.
kvs
"kvs" wrote:
> I have a similar problem, could you please give some example on this. when
> you say
> > So not oly are we using the ExecuteReader, but we're opening the
> > connection each time as well.
> do you mean you declare explicitly
> Dim cn as sqlconnection = new sqlconnection(connectionstring)
> after sqlclientpermission
> From your example,
> Try
> Dim permission As New
> SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
> permission.Assert()
> -----
> ' Dim _myConn as sqlconnection = new sqlconnection(connectionstring)
> (adding this solved your issue?)
> ----
> 'Use the MS data access app block to get the data reader
> dr = _myDBAccess.ExecuteReader(_myConn,
> CommandType.StoredProcedure, "Trader_GetTransByAssetID", New
> SqlParameter("@.AssetID", nAssetID
>
> Appreciate your help on this.
> --
> kvs
>
> "fassmann@.gmail.com" wrote:
> > OK, got it fixed.
> >
> > You have to explicitly open the connection everywhere you use this. So,
> > we've got it added to every function/procedure within our assembly.
> >
> > So not oly are we using the ExecuteReader, but we're opening the
> > connection each time as well.
> >
> >sql

Monday, March 26, 2012

Production debugging of SQL Server 2005 without VS 2005?

Hi,
Apparently Visual Studio 2005 is normally needed to debug
SQL Server 2005 problems. For example stored procedures
written in managed code. What is the recommended way to
do debugging on a production server that doesn't have VS
on it? Remote debugging from another machine on the
same LAN? What if that isn't possible? ADPlus, dump
files and the SOS debugger extension?
Thanks,
Alan Cobb
Hi
Usually you would not want to do this on a live system, but a copy, as
potentially you could cause havoc if you do this. Any code change should go
through a proper review/test/release process.
John
"Alan Cobb" wrote:

> Hi,
> Apparently Visual Studio 2005 is normally needed to debug
> SQL Server 2005 problems. For example stored procedures
> written in managed code. What is the recommended way to
> do debugging on a production server that doesn't have VS
> on it? Remote debugging from another machine on the
> same LAN? What if that isn't possible? ADPlus, dump
> files and the SOS debugger extension?
> Thanks,
> Alan Cobb
>
|||Hi John,
Let's say we've exhausted all the "non-live" approaches
and there is some bug (not just the stored procedure example)
that only happens on a particular live system.
Maybe "production debugging" just doesn't come up that much
in the SQL Server world, but in the ASP.NET world people do
have to occasionally debug problems on live ASP.NET servers.
Partly for security reasons people often don't want to install
Visual Studio's debugger on a production ASP.NET server, so
they capture dump files with ADPlus. Then they analyze the
dump files in a debugger somewhere else. But maybe that's
not as common in the SQL Server world?
Thanks,
Alan Cobb
On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>Usually you would not want to do this on a live system, but a copy, as
>potentially you could cause havoc if you do this. Any code change should go
>through a proper review/test/release process.
>John
>"Alan Cobb" wrote:
|||Hi Alan
With backend systems control and auditability can be a mandatory requirement
depending on your circumstances. Also if a business' wellbeing it dependent
on such systems it would be outside of the remit for a developer to decide
what should/should not be done on these servers. This is obviously dependent
on the company and may well be different for yours.
If you have the web logs maybe you can recreate the problem by replaying
them using ACT. This may give you the opportunity to debug the application in
a controlled environment, alternatively if you can validate that you will not
get the issue one a set of hardware you may want to consider
changing/rebuilding the system.
John
"Alan Cobb" wrote:

> Hi John,
> Let's say we've exhausted all the "non-live" approaches
> and there is some bug (not just the stored procedure example)
> that only happens on a particular live system.
> Maybe "production debugging" just doesn't come up that much
> in the SQL Server world, but in the ASP.NET world people do
> have to occasionally debug problems on live ASP.NET servers.
> Partly for security reasons people often don't want to install
> Visual Studio's debugger on a production ASP.NET server, so
> they capture dump files with ADPlus. Then they analyze the
> dump files in a debugger somewhere else. But maybe that's
> not as common in the SQL Server world?
> Thanks,
> Alan Cobb
> On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>
>

Production debugging of SQL Server 2005 without VS 2005?

Hi,
Apparently Visual Studio 2005 is normally needed to debug
SQL Server 2005 problems. For example stored procedures
written in managed code. What is the recommended way to
do debugging on a production server that doesn't have VS
on it? Remote debugging from another machine on the
same LAN? What if that isn't possible? ADPlus, dump
files and the SOS debugger extension?
Thanks,
Alan CobbHi
Usually you would not want to do this on a live system, but a copy, as
potentially you could cause havoc if you do this. Any code change should go
through a proper review/test/release process.
John
"Alan Cobb" wrote:
> Hi,
> Apparently Visual Studio 2005 is normally needed to debug
> SQL Server 2005 problems. For example stored procedures
> written in managed code. What is the recommended way to
> do debugging on a production server that doesn't have VS
> on it? Remote debugging from another machine on the
> same LAN? What if that isn't possible? ADPlus, dump
> files and the SOS debugger extension?
> Thanks,
> Alan Cobb
>|||Hi John,
Let's say we've exhausted all the "non-live" approaches
and there is some bug (not just the stored procedure example)
that only happens on a particular live system.
Maybe "production debugging" just doesn't come up that much
in the SQL Server world, but in the ASP.NET world people do
have to occasionally debug problems on live ASP.NET servers.
Partly for security reasons people often don't want to install
Visual Studio's debugger on a production ASP.NET server, so
they capture dump files with ADPlus. Then they analyze the
dump files in a debugger somewhere else. But maybe that's
not as common in the SQL Server world?
Thanks,
Alan Cobb
On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Usually you would not want to do this on a live system, but a copy, as
>potentially you could cause havoc if you do this. Any code change should go
>through a proper review/test/release process.
>John
>"Alan Cobb" wrote:
>> Hi,
>> Apparently Visual Studio 2005 is normally needed to debug
>> SQL Server 2005 problems. For example stored procedures
>> written in managed code. What is the recommended way to
>> do debugging on a production server that doesn't have VS
>> on it? Remote debugging from another machine on the
>> same LAN? What if that isn't possible? ADPlus, dump
>> files and the SOS debugger extension?
>> Thanks,
>> Alan Cobb|||Hi Alan
With backend systems control and auditability can be a mandatory requirement
depending on your circumstances. Also if a business' wellbeing it dependent
on such systems it would be outside of the remit for a developer to decide
what should/should not be done on these servers. This is obviously dependent
on the company and may well be different for yours.
If you have the web logs maybe you can recreate the problem by replaying
them using ACT. This may give you the opportunity to debug the application in
a controlled environment, alternatively if you can validate that you will not
get the issue one a set of hardware you may want to consider
changing/rebuilding the system.
John
"Alan Cobb" wrote:
> Hi John,
> Let's say we've exhausted all the "non-live" approaches
> and there is some bug (not just the stored procedure example)
> that only happens on a particular live system.
> Maybe "production debugging" just doesn't come up that much
> in the SQL Server world, but in the ASP.NET world people do
> have to occasionally debug problems on live ASP.NET servers.
> Partly for security reasons people often don't want to install
> Visual Studio's debugger on a production ASP.NET server, so
> they capture dump files with ADPlus. Then they analyze the
> dump files in a debugger somewhere else. But maybe that's
> not as common in the SQL Server world?
> Thanks,
> Alan Cobb
> On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Usually you would not want to do this on a live system, but a copy, as
> >potentially you could cause havoc if you do this. Any code change should go
> >through a proper review/test/release process.
> >
> >John
> >
> >"Alan Cobb" wrote:
> >
> >> Hi,
> >>
> >> Apparently Visual Studio 2005 is normally needed to debug
> >> SQL Server 2005 problems. For example stored procedures
> >> written in managed code. What is the recommended way to
> >> do debugging on a production server that doesn't have VS
> >> on it? Remote debugging from another machine on the
> >> same LAN? What if that isn't possible? ADPlus, dump
> >> files and the SOS debugger extension?
> >>
> >> Thanks,
> >> Alan Cobb
> >>
>sql

Production debugging of SQL Server 2005 without VS 2005?

Hi,
Apparently Visual Studio 2005 is normally needed to debug
SQL Server 2005 problems. For example stored procedures
written in managed code. What is the recommended way to
do debugging on a production server that doesn't have VS
on it? Remote debugging from another machine on the
same LAN? What if that isn't possible? ADPlus, dump
files and the SOS debugger extension?
Thanks,
Alan CobbHi
Usually you would not want to do this on a live system, but a copy, as
potentially you could cause havoc if you do this. Any code change should go
through a proper review/test/release process.
John
"Alan Cobb" wrote:

> Hi,
> Apparently Visual Studio 2005 is normally needed to debug
> SQL Server 2005 problems. For example stored procedures
> written in managed code. What is the recommended way to
> do debugging on a production server that doesn't have VS
> on it? Remote debugging from another machine on the
> same LAN? What if that isn't possible? ADPlus, dump
> files and the SOS debugger extension?
> Thanks,
> Alan Cobb
>|||Hi John,
Let's say we've exhausted all the "non-live" approaches
and there is some bug (not just the stored procedure example)
that only happens on a particular live system.
Maybe "production debugging" just doesn't come up that much
in the SQL Server world, but in the ASP.NET world people do
have to occasionally debug problems on live ASP.NET servers.
Partly for security reasons people often don't want to install
Visual Studio's debugger on a production ASP.NET server, so
they capture dump files with ADPlus. Then they analyze the
dump files in a debugger somewhere else. But maybe that's
not as common in the SQL Server world?
Thanks,
Alan Cobb
On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>Usually you would not want to do this on a live system, but a copy, as
>potentially you could cause havoc if you do this. Any code change should go
>through a proper review/test/release process.
>John
>"Alan Cobb" wrote:
>|||Hi Alan
With backend systems control and auditability can be a mandatory requirement
depending on your circumstances. Also if a business' wellbeing it dependent
on such systems it would be outside of the remit for a developer to decide
what should/should not be done on these servers. This is obviously dependent
on the company and may well be different for yours.
If you have the web logs maybe you can recreate the problem by replaying
them using ACT. This may give you the opportunity to debug the application i
n
a controlled environment, alternatively if you can validate that you will no
t
get the issue one a set of hardware you may want to consider
changing/rebuilding the system.
John
"Alan Cobb" wrote:

> Hi John,
> Let's say we've exhausted all the "non-live" approaches
> and there is some bug (not just the stored procedure example)
> that only happens on a particular live system.
> Maybe "production debugging" just doesn't come up that much
> in the SQL Server world, but in the ASP.NET world people do
> have to occasionally debug problems on live ASP.NET servers.
> Partly for security reasons people often don't want to install
> Visual Studio's debugger on a production ASP.NET server, so
> they capture dump files with ADPlus. Then they analyze the
> dump files in a debugger somewhere else. But maybe that's
> not as common in the SQL Server world?
> Thanks,
> Alan Cobb
> On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>
>

Friday, March 9, 2012

Processes in Sysprocesses

Hi
When using sybase EA server I can utilise the following code to inform me of
what a particular Spid is executing
select s.stmtnum, s.linenum, o.name
from sysobjects o, master..sysprocesses s
where s.id = o.id
and s.spid = 77
is there anything I can use in sqlserver to give me the same kind of
details? ie, what line of a SP is running?
however, in sql server the columns linenum1) DBCC INPUTBUFFER(spid)
2) DECLARE @.Handle BINARY(30)
SELECT @.Handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.spid
SELECT * FROM ::fn_get_sql(@.Handle)
For more details please refer to BOL
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
> Hi
> When using sybase EA server I can utilise the following code to inform me
> of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:

> Hi
> When using sybase EA server I can utilise the following code to inform me
of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||Thanks for that Uri, every day is a lesson eh!
However, is there any way to drill down to a more granular level so that I
can identify what line of the proc it is running?
The problem we are encountering is that on occasions a couple of large
procedures grind to a standstill and we are trying to identify where they ar
e
slow.
Any more assistance would be gratefully received.
Gavin
"Uri Dimant" wrote:

> 1) DBCC INPUTBUFFER(spid)
> 2) DECLARE @.Handle BINARY(30)
> SELECT @.Handle = sql_handle
> FROM master..sysprocesses
> WHERE spid = @.@.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> For more details please refer to BOL
>
> "almightygav" <almightygav@.discussions.microsoft.com> wrote in message
> news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
>
>|||I've got to hand it to you Vadivel, that was good!
I'd like to think I could have written it if the help files were updated
inline with the Service Packs but hey ho, top job!
When do the help files get updated to show changes made by the Service
Packs? The two columns added to sysprocesses don't seem to be documented
anywhere?
Thank you very much, perfect!
"Vadivel" wrote:
> You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> http://thinkingms.com/vadivel
> "almightygav" wrote:
>|||> When do the help files get updated to show changes made by the Service
> Packs?
You need to download the update for Books Online. BOL update is not in the s
ervice pack files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:FF370F16-5824-4E66-909F-2FFF2A1FB160@.microsoft.com...
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||Thanks almighty! I am glad i was of some help to you.
Btw as Tibor said, BOL updates are not part of Service pack files.
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||On Thu, 10 Nov 2005 05:22:04 -0800, "almightygav"
<almightygav@.discussions.microsoft.com> wrote:
>Thanks for that Uri, every day is a lesson eh!
>However, is there any way to drill down to a more granular level so that I
>can identify what line of the proc it is running?
>The problem we are encountering is that on occasions a couple of large
>procedures grind to a standstill and we are trying to identify where they a
re
>slow.
>Any more assistance would be gratefully received.
You can get that level of detail by running the profiler.
There's probably some voodoo way to get it via queries, but I don't
know it.
Well, maybe I do, "set statistics profile on" and then run your fat SP
and see if that helps, if that's not too much information!
J.

ProcessAdd for dimensions

I'm poking at some code that sends uses an XML/A ProcessAdd command to incrementally process a dimension. The command contains the out-of-line binding which specifies the query for the new rows. However, when the command gets submitted, I don't see anything that looks like the specified query in the profiler log. As I figure it, either:

a) The command isn't constructed correctly. SSAS is claiming success, and the dimension is processed at the end, so if the command is wrong, then I don't know what SSAS would be doing instead (a full process? a process update?).

b) My expectation of seeing that query, or something like it, in the profiler log, is wrong. We also have ProcessAdd commands for partitions, and those queries DO end up being displayed in Profiler. But maybe dimensions are different in some way.

The ProcessAdd stuff is very poorly documented, so it's entirely possible we did something wrong in implementing it. On the whole of the internet, I found exactly one complete sample of a ProcessAdd for a dimension, posted by EdwardM on the OLAP newsgroup. I tried plucking that out and using it as a sanity check, and I see very similar behavior to what I see with our own dimension. However, that sample references a dimension (NQ Customer) that doesn't exist in my AdventureWorks cube, so I have no idea if I hacked it correctly to match the Customer dimension in my AW.

I beleive the behavior is that if you dont get it right, Analysis Server will start using the original binding for dimension you process using ProcessAdd command.

Important is to move <DataSource> and <DataSourceView> sections from each individual <Process> section to <Batch> section before the empty <Bindings> element.

So it would look as

There isnt much value of using ProcessAdd for partitions. It is simple wrapper around Create new partitition->ProcessFull->Merge into a original partition.
The main idea behind ProcessAdd that it gives you ability to only add members to dimesion, so partitions wouldnt drop aggregations as it happens during ProcesUpdate.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Edward, that's definitely something we didn't have right on our own ProcessAdd command.

However, I'm still having no luck getting the query criteria to be used (or, at least, the criteria never show up in any of the SQL statements in the profiler log).

I've gone back to the example you posted here:
http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_frm/thread/59ccd215cd8afbf2/12b7a464419f5304?lnk=st&q=ProcessAdd&rnum=1#12b7a464419f5304
But I'm having no luck with that either. Is there something else I could be missing? Is that samples still good?

Thanks,
Kevin

|||

Yet there is another way you can try.
If you have a dimension based on a single table, you should be able to use what is called QueryBinding.
Check out T.K's whitepaper on the processing architechture http://msdn2.microsoft.com/en-us/library/ms345142.aspx#sqk2k5_asproc_topic7

It has example of QueryBinding used with ProcessAdd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Edward. I played around a bit with the QueryBinding, but couldn't make it work. Are you sure it's supposed to work with dimensions? According the XML/A docs, the elements of that binding are specific to PartitionBinding.

In any event, I was able to finally get ProcessAdd to work correctly with dimensions using the out-of-line DSV specification. I think the things that were tripping us up were that a) our DSV view specification wasn't quite right, b) the DSV was inside the ProcessAdd (not sure why that isn't supposed to work), c) we didn't have the DataSource, only the DSV, and d) we were missing the trailing empty bindings element (that's the one that really took a while to track down. Boy, the XML/A for ProcessAdd on a heavily snowflaked dimension is BIG and hair.

Thanks for your help.
|||

I hear you Kevin.
This functionality is really useful and it is hard to use.

Can't comment on the plans of upcoming release, but can assure we will try to do our best to make it easier to use it. And post here if you have any quesitons.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||>I beleive the behavior is that if you dont get it right, Analysis Server will start using the original binding for
>dimension you process using ProcessAdd command.

FYI, this doesn't appear to be the case anymore in SP2 - it now generates an internal error during incremental processing. Ultimately this is due to an incorrectly constructed XML/A command, so I don't expect MS to do anything about it, but I wanted to post this info in case anyone else runs into it.
|||

Hi Kevin,

I saw your entry that you have got it working with out of line DSV specification. I'm exactly trying to do the same thing and can't get it working (tried both query binding and the DSV binding). Pl can you share the source code if it is possible (just the part where you define your DSV spec).

Cheers,

Arun

The XML-A command I use :

<Batch Transaction="1" ProcessAffectedObjects="0"

xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<DimensionID>Tbl Currency</DimensionID>

</Object>

<Type>ProcessAdd</Type>

</Process>

</Parallel>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">

Provider=SQLNCLI.1;Data Source=<<servername>>;Integrated Security=SSPI;Initial Catalog=<<dbname>>

</ConnectionString>

</DataSource>

<xs:element name="dbo_tblCurrency" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" >

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="dbo_tblCurrency"

msprop:QueryDefinition="Select * from tblCurrency where CurrencyCode = 'ZZZ'"

mspropbTableName="tblCurrency"

msprop:IsLogical="True"

msprop:TableType="View">

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xschema>

</Schema>

</DataSourceView>

</Bindings>

</Batch>

|||

I have had the same frustration with ProcessAdd trying to figure out all the XMLA syntax. I finally got it to work and have blogged about it in addition to some performance tests. Hope this helps.

Some full working examples of ProcessAdd:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

And some performance tests showing the performance of ProcessAdd:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx

|||

can you explain me how to fetch the member name column (msprop:ComputedColumnExpression=""PHABRK_CODE" + '#&@.' + "SHORT_DESC&quot) and to use it in VB.net application

<ID>ORION D 60500 RPM STL ORIORSAR_15232__20070619</ID>

<Name>ORION D 60500 RPM STL ORIORSAR_15232__20070619</Name>

<DataSourceID>ORION D 60500 RPM STL ORIORSAR_15232__20070619</DataSourceID>

<xs:element name="ORION_x0020_D_x0020_60500_x0020_RPM_x0020_STL_x0020_ORIORSAR_15232__20070619" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="D_GEOGRAPHY_GEO_15104" msprop:FriendlyName="D_GEOGRAPHY_GEO_15104" mspropbTableName="D_GEOGRAPHY_GEO_15104" msprop:TableType="Table">

<xs:complexType>

<xs:element name="PHABRK_CODE" type="xstring" />

<xs:element name="Column1" msdata:ReadOnly="true" msprop:ComputedColumnExpression=""PHABRK_CODE" + '#&@.' + "SHORT_DESC"" msprop:IsLogical="True" minOccurs="0">

<xs:restriction base="xstring">

<xs:maxLength value="255" />

</xs:restriction>

</xsimpleType>

</xs:element>

<xs:element name="PARENT_CODE" type="xstring" minOccurs="0" />

<xs:element name="customrollup" mspropbColumnName="customrollup" msprop:ComputedColumnExpression="(select top 1 customrollup from D_GEOGRAPHY_GEO_15104)" mspropescription="customrollup" msprop:IsLogical="True" minOccurs="0">

<xs:restriction base="xstring">

<xs:maxLength value="500" />

</xs:restriction>

</xsimpleType>

</xs:element>

</xsequence>

</xs:complexType>

</xs:element>

|||

Jothi-

I don't think your question has anything to do with this thread. Why don't you start a new thread and we'll try to respond?

ProcessAdd for dimensions

I beleive the behavior is that if you dont get it right, Analysis Server will start using the original binding for dimension you process using ProcessAdd command.

Important is to move <DataSource> and <DataSourceView> sections from each individual <Process> section to <Batch> section before the empty <Bindings> element.

So it would look as

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Edward, that's definitely something we didn't have right on our own ProcessAdd command.

However, I'm still having no luck getting the query criteria to be used (or, at least, the criteria never show up in any of the SQL statements in the profiler log).

Thanks,
Kevin

|||

It has example of QueryBinding used with ProcessAdd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks for your help.
|||

I hear you Kevin.
This functionality is really useful and it is hard to use.

Can't comment on the plans of upcoming release, but can assure we will try to do our best to make it easier to use it. And post here if you have any quesitons.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||>I beleive the behavior is that if you dont get it right, Analysis Server will start using the original binding for
>dimension you process using ProcessAdd command.

Hi Kevin,

Cheers,

Arun

The XML-A command I use :

<Batch Transaction="1" ProcessAffectedObjects="0"

xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<DimensionID>Tbl Currency</DimensionID>

</Object>

<Type>ProcessAdd</Type>

</Process>

</Parallel>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">

Provider=SQLNCLI.1;Data Source=<<servername>>;Integrated Security=SSPI;Initial Catalog=<<dbname>>

</ConnectionString>

</DataSource>

<xs:element name="dbo_tblCurrency" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" >

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="dbo_tblCurrency"

msprop:QueryDefinition="Select * from tblCurrency where CurrencyCode = 'ZZZ'"

mspropbTableName="tblCurrency"

msprop:IsLogical="True"

msprop:TableType="View">

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xschema>

</Schema>

</DataSourceView>

</Bindings>

</Batch>

|||

I have had the same frustration with ProcessAdd trying to figure out all the XMLA syntax. I finally got it to work and have blogged about it in addition to some performance tests. Hope this helps.

Some full working examples of ProcessAdd:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

And some performance tests showing the performance of ProcessAdd:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx

|||

can you explain me how to fetch the member name column (msprop:ComputedColumnExpression=""PHABRK_CODE" + '#&@.' + "SHORT_DESC&quot) and to use it in VB.net application

<ID>ORION D 60500 RPM STL ORIORSAR_15232__20070619</ID>

<Name>ORION D 60500 RPM STL ORIORSAR_15232__20070619</Name>

<DataSourceID>ORION D 60500 RPM STL ORIORSAR_15232__20070619</DataSourceID>

<xs:element name="ORION_x0020_D_x0020_60500_x0020_RPM_x0020_STL_x0020_ORIORSAR_15232__20070619" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="D_GEOGRAPHY_GEO_15104" msprop:FriendlyName="D_GEOGRAPHY_GEO_15104" mspropbTableName="D_GEOGRAPHY_GEO_15104" msprop:TableType="Table">

<xs:complexType>

<xs:element name="PHABRK_CODE" type="xstring" />

<xs:element name="Column1" msdata:ReadOnly="true" msprop:ComputedColumnExpression=""PHABRK_CODE" + '#&@.' + "SHORT_DESC"" msprop:IsLogical="True" minOccurs="0">

<xs:restriction base="xstring">

<xs:maxLength value="255" />

</xs:restriction>

</xsimpleType>

</xs:element>

<xs:element name="PARENT_CODE" type="xstring" minOccurs="0" />

<xs:restriction base="xstring">

<xs:maxLength value="500" />

</xs:restriction>

</xsimpleType>

</xs:element>

</xsequence>

</xs:complexType>

</xs:element>

|||

Jothi-

I don't think your question has anything to do with this thread. Why don't you start a new thread and we'll try to respond?

Wednesday, March 28, 2012

Professionally Handling ADO Logins

Production server error

Monday, March 26, 2012

Production debugging of SQL Server 2005 without VS 2005?

Production debugging of SQL Server 2005 without VS 2005?

Production debugging of SQL Server 2005 without VS 2005?

Friday, March 9, 2012

Processes in Sysprocesses

ProcessAdd for dimensions

ProcessAdd for dimensions

Procedure Time out

Blog Archive

About Me