Analysis Services

How can you process multiple Analysis Services databases against the same SQL Server database without receiving table locks and the processing fails.How about one by one database using some program? There is a command line tool available to execute XML/A requests.|||

Dear,

We can create a DTS Package that will process all the Analysis Databases one by one.

HTH

from

sufian

Am I locking it up...

Ok, this may be a brain dead question but I can't seem to figure out what it is I am doing wrong. I have a stored proc which has multiple inserts and updates and deletes. However, I do not want to commit until the end of the procedure. So near the end if no error has been return by a particular insert, update, delete I tell it to COMMIT TRAN. My problem is that it seems to run and run and run and run. I take out the Begin Tran and boom it runs fast and completes.

But if there is a problem near the end then those other statements will be committed. I wish to avoid that. I have an error routine at the end of the SP and I have if statement to GOTO sp_error: if @.@.error produces a non zero value. I am sure I am doing something goofy but can seem to see it. I know it has come down to the Begin Tran. Is it that I have too many uncommitted transactions? Or perhaps I am locking something up. I know its hard to tell without seeing what I am doing but is there something simple to remember about using explicit transactions that I am forgetting. Any help is appreciated.

Tomwhat do you see when you do sp_who2?

Any blocking?

Do you call any other sprocs?

Do you execute any extended procs?|||Nope I do not see any blocking. No, but will and no. Any ideas?

I did find this. By the way I am using your template.

Server: Msg 2732, Level 16, State 1, Procedure CreateParContracts, Line 879
Error number 0 is invalid. The number must be from 13000 through 2147483647
This happens when I reach the sp_error lable at the end of stored proc. Here is the code

SP_Error:
If @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
If (@.Error_Type = 50002)
BEGIN
Rollback TRAN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END
RAISERROR @.Error_Type @.Error_Message

the line 879 seems to be pointing to if statement "If @.Error_Type = 50001"|||Can you attach the sproc so we can look at it?|||Ok I believe I have attached the file. Actually I did not orginally produce this script. It was just a set of t-sql code being run each time they need to load these. I am trying to automate the process. Couple of things.

1. The attachment is not the one I could get to compile. I kept getting a Server: Msg 133, Level 15, State 1, Procedure CreateParContracts, Line 862
A GOTO statement references the label 'SP_Error' but the label has not been declared.

??

2. If I remove the sp_exit and just put it the end of the stored procedure then I have the snippet of code that I just posted. So I don't know. Looks ok to me but I have something wrong. :eek:

Thanks Brett

Tom|||I'm looking...

first very bad thing is the creation of a permanent table...what if the sproc is run at the same time...change that to use a temporary table...and even though the temp table goes away at the end of the batch, it's always a good idea to drop it on the way out...

I'm still looking|||Well, I agree but i didn't think it would hurt anything leaving it in since it would get run once a month and by one person. But, if this were to be used by more than one user than yes it has to use a temp table. As matter of fact I think it would better to just rewrite the whole thing. Since there are a lot of things that bother me about this script.|||Yeah..there are around 25 separate steps...

And where did you get that code? Error location btw is a debugging tool I use when I want to know which statement failed..I updated them..

Try this attachment|||It was just a script file that was being run with 4 other script files to process this data. The one you looked at was the first one that got ran. There are four others. This is all processed into files that get loaded to a AS400. As stated earlier I decided to try and create a sp for each script file. Then run all 5 from a containter. This may not work as they are. But I really don't have the time to rewrite them. I wonder if it will hang now. I will try it out. Thanks a lot for your help. I will let know if I get it working.

Thanks
Tom|||Good Luck...You might want to consider breaking it up.

BUT Where did you get that code?

That's definitely part of my template and definetly my error handling...

My Blog Entry (http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx)

Now I gotta make sure it runs|||I was afraid of this but now it just keeps running and running and running. It might have something to do with some code trying to delete or change data that as yet to be committed from being inserted before. Would that make sense? If I move the Begin Tran down near the end it runs fine.

Tom|||Yes, I got the error handleing from your template. The rest is a mixture of what was there and what I normally put in. Perhaps I am not understanding question. :confused:|||Doesn't make sense.....if you didn't have any at all, it is implicitly 1 transaction anyway for that spid...and would rollback with an error that was severe enough...

Run the sproc with the BEGIN TRAN at the top...

And while it's just sitting there...run sp_who2 to see what's up...

You might want to start profiler as well (On a client)

Alternative to Full Text Search ?

I'm aiming to provide a Ranked multiple keyword search for a varchar(3175) column on my database but Full Text Search is not an option and there could be as many as 15,000 rows in a result list.

I see that I have two choices.

1. Do everything on the Database server.
Get all results that match all submitted keywords and then count how many times each keyword is found for each record. Total each score for each record.

2. Do all of this nasty processor intenstive work on the web server
Get all results that match all submitted keywords from the database and place in a DataTable. For each record in the DataTable perform some C# based match and count loop. Place result of each loop in extra 'Result' column of DataTable and the sort DataTable according to score.

I can confidently code up option 2 however option 1 seems like a real headache in terms of the necessary SQL (my SQL is not that good). Also I think with option two I can use Caching to save the reordered datatable primary key with cache parameters based on the submitted keywords so the search should eventually get quite fast.

Does this all sound like nonsense ?
Has anyone tried to provide a Ranked multiple keyword search without using a Full Text Indexing ?

Thanks.Full Text Indexing would obviously be the right tool for this job.

What comes to mind for the next best solution would be to write a UDF which will take a keyword and a string and return the number of occurrences of the keyword in the string. Then you could sort by the number of occurrences.

The resulting query would look like this:

SELECT
 Description,
 OccurrenceCount
FROM
 (SELECT Description, dbo.fnOccurrenceCount(@.SearchFor, Description) AS OccurrenceCount FROM myTable ) A
WHERE
 OccurrenceCount > 0
ORDER BY
 OccurrenceCount DESC,
 Description

And the UDF would look like this:

CREATE FUNCTION dbo.fnOccurrenceCount (@.SearchNeedle varchar(8000), @.SearchHaystack varchar(8000))
RETURNS integer AS
BEGIN
DECLARE @.OccurrenceCount int
DECLARE @.FoundPosition int
DECLARE @.SearchHaystackPart varchar(1000)
DECLARE @.SearchNeedleLength int
SET @.OccurrenceCount = 0
SET @.FoundPosition = 0
SET @.SearchHaystackPart = @.SearchHaystack
SET @.SearchNeedleLength = DATALENGTH(@.SearchNeedle)
SET @.FoundPosition = CHARINDEX(@.SearchNeedle, @.SearchHayStackPart)
WHILE @.FoundPosition > 0
BEGIN
SET @.OccurrenceCount = @.OccurrenceCount + 1
SET @.SearchHaystackPart = SUBSTRING(@.SearchHaystackPart,@.FoundPosition+@.SearchNeedleLength,8000)
SET @.FoundPosition = CHARINDEX(@.SearchNeedle, @.SearchHayStackPart)
END
RETURN @.OccurrenceCount
END

Terri|||[sorry if this appears as a double post my last reply must have timed out]

Thanks Terri - that works really well ! - without trying to sound too dramtic - your're a life saver !

As I said in my first post my C# is OK but my T-SQL is not great. Can you recommend any books or article links I can read which could help me to work out solutions like this for myself ?

Thanks again,

TheDr|||Cool, I'm glad you were able to make use of that method!

Personally I have found that participating in forums such as this and those on aspadvice.com to be the best way to increase my knowledge. Reading other people's problems and then trying out different ways to solve them has helped me immensely -- especially when others recommend methods I hadn't even thought of. Following links that the experts post on the forums also helps.

For reading material, I would have to recommend Ken Henderson's The Guru's Guide to Transact-SQL as a must-read.

Terri|||The only question I have is from the following line of your Proc what does the 'A' represent ? If I try any run the Proc without it I'm given an error. Is it an alias for the parenthesised result ?

<snip>
(SELECT Description, dbo.fnOccurrenceCount(@.SearchFor, Description) AS OccurrenceCount FROM myTable ) A
</snip
Thanks for your book suggestion. I'll look out for that one.
Cheers.|||Yes, that's exactly what it is; an alias for the derived table. A lazy alias at that. When using derived tables a "correlation name alias" (as they call it) is required.

Terri

Alternative to cursor in trigger?

If the update trigger returns an "inserted" table with multiple records,
is there any way to address each record individually without using a
cursor? The code below is my solution using a cursor but the DBA says
no cursors. Thank you for your help.
/* Assume "inserted" table returned multiple records */
OPEN ins_cursor
FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
WHILE @.@.FETCH_STATUS = 0
BEGIN
/* **Pseudo code for sending email to address in each record returned**
xp_sendmail
emailTo = @.emailAddress
emailSubject = @.emailSubject
emailBody = @.emailBody
****************************************
*** */
/* Write log entry for each individual email sent*/
INSERT INTO NotifyLog
(
emailTo, emailSubject, emailBody
)
Values
(
@.emailTo, @.emailSubject, @.emailBody
)
FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
END
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I don't think there is a way to do this as requested without a cursor. The
"bad" part here isn't the word "cursor"... you have to call a stored
procedure for every single row, and whether you use a cursor or some other
fetch mechanism, you're still going to have to do the painful, iterative
approach of looping through each row, one at a time.
My suggestion: Use a scheduled job and perform this kind of row-by-row
activity there (you can mark rows as updated in the trigger, by joining the
real table against inserted on the primary key, and then un-mark each row as
the job sends each e-mail). Surely a five or two-minute interval will be
close enough to real time, without hogging all the performance it takes to
hold the transaction open while all that mail is sent (ugh).
http://www.aspfaq.com/
(Reverse address to reply.)
"Georgia" <xout@.deleted.gov> wrote in message
news:u27zY$cIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> If the update trigger returns an "inserted" table with multiple records,
> is there any way to address each record individually without using a
> cursor? The code below is my solution using a cursor but the DBA says
> no cursors. Thank you for your help.
> /* Assume "inserted" table returned multiple records */
> OPEN ins_cursor
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* **Pseudo code for sending email to address in each record returned**
> xp_sendmail
> emailTo = @.emailAddress
> emailSubject = @.emailSubject
> emailBody = @.emailBody
> ****************************************
*** */
> /* Write log entry for each individual email sent*/
> INSERT INTO NotifyLog
> (
> emailTo, emailSubject, emailBody
> )
> Values
> (
> @.emailTo, @.emailSubject, @.emailBody
> )
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> END
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Sure, use a locally defined Table variable...
Declare @.EMs Table (PKID Integer Primary Key Not Null)
Insert @.EMs (PKID) Select <PrimaryKey> From inserted
Declare @.PKid Integer
While Exists (Select * From @.EMs)
Begin
Select @.PKid = Max(PKID) From @.EMs
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody)
Select emailTo, emailSubject, emailBody
From inserted Where <PrimaryKey> = @.PKid
-- --
Delete @.EMs Where PKID = @.PKiid
End
But why not use a set based statement that "Inserts" the entire set of
records directly from the inserted table into NotifyLog table
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody)
Select emailTo, emailSubject, emailBody
From inserted
"Georgia" wrote:

> If the update trigger returns an "inserted" table with multiple records,
> is there any way to address each record individually without using a
> cursor? The code below is my solution using a cursor but the DBA says
> no cursors. Thank you for your help.
> /* Assume "inserted" table returned multiple records */
> OPEN ins_cursor
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* **Pseudo code for sending email to address in each record returned**
> xp_sendmail
> emailTo = @.emailAddress
> emailSubject = @.emailSubject
> emailBody = @.emailBody
> ****************************************
*** */
> /* Write log entry for each individual email sent*/
> INSERT INTO NotifyLog
> (
> emailTo, emailSubject, emailBody
> )
> Values
> (
> @.emailTo, @.emailSubject, @.emailBody
> )
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> END
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||Don't send email notifications from a trigger. There are a number of
reasons.
1. Email is an inherently asynchronous medium so it is unnecessary and
inefficient to hold a transaction open for the duration of sending
mail.
2. If the trigger is fired inside a transaction that later rolls back
you will have sent a notification for an update that never happened.
3. If the mail server is unavailable or causes a timeout or the
notification process fails for any other reason then either you have to
prevent the update or you have to go ahead without sending a
notification. Do you really want to make the mail server a critical
point of failure for your app?
4. Yes, you'll need a cursor. Not desirable in a trigger.
For these reasons I would second Aaron's suggestion: use some other
process outside a trigger to send notifications.
David Portas
SQL Server MVP
--|||
> Sure, use a locally defined Table variable...
> Declare @.EMs Table (PKID Integer Primary Key Not Null)
> Insert @.EMs (PKID) Select <PrimaryKey> From inserted
> Declare @.PKid Integer
> While Exists (Select * From @.EMs)
> Begin
> Select @.PKid = Max(PKID) From @.EMs
> INSERT INTO NotifyLog
> (emailTo, emailSubject, emailBody)
> Select emailTo, emailSubject, emailBody
> From inserted Where <PrimaryKey> = @.PKid
> -- --
> Delete @.EMs Where PKID = @.PKiid
> End
And for the OP's benefit, this is exactly what I meant by not using an
explicit cursor but still going through the process row-by-row, which is
kind of like a wolf in sheep's clothing. In other words, neither DECLARE
CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have in
a trigger.|||Why Not '
"Aaron [SQL Server MVP]" wrote:

>
> And for the OP's benefit, this is exactly what I meant by not using an
> explicit cursor but still going through the process row-by-row, which is
> kind of like a wolf in sheep's clothing. In other words, neither DECLARE
> CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have i
n
> a trigger.
>
>|||Because he's going to use a cursor, or some other looping mechanism like the
one you've provided, to send mail to each recipient in the inserted table.
If you're asking why that's a bad idea, I take it you don't have much
experience with sending mail from SQL Server, and/or having transactions
wait for and/or depend on it.
http://www.aspfaq.com/
(Reverse address to reply.)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:99713C31-6A95-445E-BB64-4D0EB353A8C6@.microsoft.com...
> Why Not '|||You'd be wrong, as well as impolite... I know about that... and I agree...
but you isaid
In other words, neither DECLARE
CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have in
a trigger.
<<<<<<<<<<<<<<<<<<<<<<
Other than the obvious, (re: looping through the records rather than dealing
with them as a set) Do you have anything to teach me about using such a loo
p
in a trigger?
"Aaron [SQL Server MVP]" wrote:
> Because he's going to use a cursor, or some other looping mechanism like t
he
> one you've provided, to send mail to each recipient in the inserted table.
> If you're asking why that's a bad idea, I take it you don't have much
> experience with sending mail from SQL Server, and/or having transactions
> wait for and/or depend on it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:99713C31-6A95-445E-BB64-4D0EB353A8C6@.microsoft.com...
>
>|||I agree. Use the trigger to post email send requests to another de-coupled
table storing the queue of emails to be sent. Use a second process, or job
to inspect the de-coupled table fro unsent emails and send them from the
second process. This will keep the email process from occurring within the
transaction.
fyi, the Service Broker in Yukon is a perfect queue, but y9ou can accomplish
the same objective in SQL Server 2K.
-Paul Nielsen, SQL Server MVP
www.sqlserverbible.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110063641.901975.135140@.z14g2000cwz.googlegroups.com...
> Don't send email notifications from a trigger. There are a number of
> reasons.
> 1. Email is an inherently asynchronous medium so it is unnecessary and
> inefficient to hold a transaction open for the duration of sending
> mail.
> 2. If the trigger is fired inside a transaction that later rolls back
> you will have sent a notification for an update that never happened.
> 3. If the mail server is unavailable or causes a timeout or the
> notification process fails for any other reason then either you have to
> prevent the update or you have to go ahead without sending a
> notification. Do you really want to make the mail server a critical
> point of failure for your app?
> 4. Yes, you'll need a cursor. Not desirable in a trigger.
> For these reasons I would second Aaron's suggestion: use some other
> process outside a trigger to send notifications.
> --
> David Portas
> SQL Server MVP
> --
>|||
The real problem here is not the cursor, it's sending mail in a trigger.
The mail is not transactional and it will slow down your transactions.
A better approach is to insert all the rows into your NotifyLog with a sent
flag. Then use a job to open a cursor on the NotifyLog for rows with
sent=0. Since its a background job using a cursor is no big deal. The
important thing is that the email will not be sent if the transaction is
rolled back.
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody, sent)
SELECT xxx emailTo, xxx emailSubject, xxx emailBody, 0
Then later
begin transaction
declare @.emailToSend table(id int, emailTo varchar(200) ...)
INSERT INTO @.emailToSend
(id, emailTo, emailSubject, emailBody)
select int, emailTo, emailSubject,emailBody
from NotifyLog (updlock,holdlock)
where sent = 0
update NotifyLog set sent = 1
where id in (select id from @.emailToSend)
commit transaction
//send all the emails
David