SQL Alternative to cursor: update

Showing posts with label update. Show all posts

Sunday, March 25, 2012

Analysis Service: how can I process cubes?

how can I process and update cubes automaticly every night ?
ThanksThere is a task in DTS for processign cubes and other Analysis Services tasks. Create a package and just schedule it

analysis service

How to update a cube using SSIS?Use the Analysis Services Processing Task (see http://www.databasejournal.com/features/mssql/article.php/10894_3584306_2) for more details.sql

analysis service

How to update a cube using SSIS?Use the Analysis Services Processing Task (see http://www.databasejournal.com/features/mssql/article.php/10894_3584306_2) for more details.

Tuesday, March 20, 2012

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)
FROM some_table
GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )
FROM some_table
GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

Carlos

|||

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

Carlos

|||

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An update query

Hi,
I run a same update query in our two servers, which have the same
configuration and same database and indexes, one of them is returning the
result in 4 seconds, and the other one in 7 minutes. the only difference
between them is that the fast server has 512 MB and the other one has 256 MB
RAM. Why is it that much slow.
Thanks in advance,
Mathew
hi Mathew,
Mathew wrote:
> Hi,
> I run a same update query in our two servers, which have the same
> configuration and same database and indexes, one of them is returning
> the result in 4 seconds, and the other one in 7 minutes. the only
> difference between them is that the fast server has 512 MB and the
> other one has 256 MB RAM. Why is it that much slow.
> Thanks in advance,
actually the 2 servers are not the same... probably they do not have the
same data too, and/or the same disk subsystem... perhaps the second is more
fragmented too, both at physical OS file status and at internal logical page
status..
and having the half of RAM does not help for sure.., this mean MSDE has
fewer available resources, that the runnig applications have more
contentions for resources with the SQL Services...
more paging at OS level will be needed by all applications... the whole
system can be involved...
BTW, the 2 results are very different indeed... but try cleaning up your
server... defrag it, both at OS level and database level..
try comparing the statistics output as well as the used plans...
lot of variables are involved...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

an UPDATE inside a SELECT

I was wondering if it is possible to have an UPDATE satement inside a
SELECT statment. What I want to do is select certian rows from a table
then based on the result I want to update another table. Metaphorically
something like the following
SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
TABLE A
*** Sent via Developersdex http://www.examnotes.net ***Hi,
Looks like you are trying to Select and Update the contents of the table in
one go.
Try to use a Stored Procedure for this purpose.
Hope this answered your Question.
thanks,
Chandra
"Hussain Al-Dhaheri" wrote:

> I was wondering if it is possible to have an UPDATE satement inside a
> SELECT statment. What I want to do is select certian rows from a table
> then based on the result I want to update another table. Metaphorically
> something like the following
> SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
> TABLE A
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
No, you vannot do it in that way
[Untested]
UPDATE TableA SET col=(SELECT b.col FROM TableB b WHERE b.col=col)
WHERE EXISTS (SELECT * FROM TableB b WHERE b.col=col)
SELECT <column lists> FROM
"Hussain Al-Dhaheri" <hdhaheri@.hotmail.com> wrote in message
news:e4s4e1tSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> I was wondering if it is possible to have an UPDATE satement inside a
> SELECT statment. What I want to do is select certian rows from a table
> then based on the result I want to update another table. Metaphorically
> something like the following
> SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
> TABLE A
>
> *** Sent via Developersdex http://www.examnotes.net ***sql

an sql command that doest work in a page

Hello,

I have a sequense of sql commands in order to recursively update a table that has parents/childs

After I create a temporary table, I need to run an sql command that for some reason is not working. No errors, the command is actually excecuted, but I beieve the rowcount is 0 from the beggining

Here is the command:

Dim

InsertConnectionAs Data.SqlClient.SqlConnection =New System.Data.SqlClient.SqlConnection("Server=myServer;User ID=myUser;pwd=myPSW;Database=myDatabase")Dim SqlInsertCommandAs Data.SqlClient.SqlCommand =New Data.SqlClient.SqlCommand("while @.@.rowcount > 0 " _

"begin INSERT INTO submenu" _

& uid &

" (pageid,parentid) SELECT y.pageid , y.parentid FROM submenu" & uid _

" i INNER JOIN page y ON y.ParentId = i.pageID LEFT OUTER JOIN subMenu" _

& uid &

" i1 ON i1.pageId = y.pageId WHERE(i1.pageID Is NULL) " _

"end", InsertConnection)

InsertConnection.Open()

SqlInsertCommand.ExecuteNonQuery()

InsertConnection.Close()

SqlInsertCommand =

Nothing

If I insert any other SQLcommand there it is excecuted normally.

The command I have is excecuted fine using sql server manager.

Is there any way that a command is excecuted in the SQL manager but not in a page...??

Any ideas would be great...

Thank you

Hello my friend,

I would not use @.@.rowcount outside of Enterprise Manager. Could you describe your database structure and what you are trying to insert. No need to send vb code, just the SQL or some comments on the steps and I can send you the correct SQL that will work from wherever it is used.

Kind regards

Scotty

|||

You use @.@.RowCount in first line of your query but this returns number of rows affected by last select statement in current SQL thread, but your thread is starting so it returns always 0 so your loop is never executed.

You should populate your temporary table in the same select statement to work correctly. The best solution is to create SQL stored procedure which will do all your work at one shot if you can do it.

Thanks

JPazgier

|||

Hi,

The software is a sitebuilder. The particular table holds the page stucrure of each site.

The table is this one:

----

pageid int identify

siteid int

pagename nvarchar(200)

parentid int

----

I need to update / delete all of the tree when the user wants to update or delete a top element. The number of levels is not limited.

I managed to do it, using a variable. The "problem" is that I set it to 1000 times. So if someone has more than 1000 pages under the parent, if will fail. And it's not right in the first place.

The thing is, that this worked fine when I was on an other server that used MS SQL 2000. I didn;t find any differences searching the web from 2000 to 2005

jpazgier, why does it work then when I excecute it using SQL manager..? Isn't this weird? I mean, if the rowcount is 0 from the beggining in the application, should't it be 0 in the SQL manager too?

Thank you

|||

Hello my friend,

I realize now what you are trying to do and I have the answer for you and this will work no matter how many levels you have (no 1000 limit). Run the following SQL, but change tblTree to the name of your table (I did not know what you have called it): -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

Now to get all child IDs of page 1 (either direct children of 1, and also children of ones that are children of 1, and so on) I run the following: -

select PageID from dbo.fnGetPages(1)

To delete the page and all of its children I run the following 2 commands: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(1))

DELETE FROM tblTree WHERE PageID = 1

Kind regards

Scotty

|||

Thanks Scotty,

The code to create the function is only run once right?

Then I just select update or do whatever I need to do using the function right?

Thanks

|||

Yes that is correct. You only run the function SQL once. You only need to run this again if you decide to use this functionality within a new database.

You just need to run the commands that use the function and you should be fine.

Kind regards

Scotty

An other DB copy Q:

Hello,
In an instance, I've two DBs: ABC y copyABC. I would like to update the
tables (and their contents added or removed) from ABC to copyABC twice a
day. For it will be using a Job but, what would be the syntax?
The idea is to get data for my reports from copyABC and creating my own
queries without disrupting the daily usage of ABC.
Would appreciate some directions.
Thank you
MarySSIS script or BulkCopy or SqlBulkCopy method. You might also consider
ADO.NET 3.5 Sync Services.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Mary" <noemail@.hotmail.com> wrote in message
news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
> Hello,
> In an instance, I've two DBs: ABC y copyABC. I would like to update the
> tables (and their contents added or removed) from ABC to copyABC twice a
> day. For it will be using a Job but, what would be the syntax?
> The idea is to get data for my reports from copyABC and creating my own
> queries without disrupting the daily usage of ABC.
> Would appreciate some directions.
> Thank you
> Mary
>|||Can a query be used? is so, how?
"William Vaughn [MVP]" <billvaNoSPAM@.betav.com> escribió en el mensaje de
noticias news:AAC71D98-1B95-4F63-90F4-0607B79F1222@.microsoft.com...
> SSIS script or BulkCopy or SqlBulkCopy method. You might also consider
> ADO.NET 3.5 Sync Services.
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
> "Mary" <noemail@.hotmail.com> wrote in message
> news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
>> Hello,
>> In an instance, I've two DBs: ABC y copyABC. I would like to update the
>> tables (and their contents added or removed) from ABC to copyABC twice a
>> day. For it will be using a Job but, what would be the syntax?
>> The idea is to get data for my reports from copyABC and creating my own
>> queries without disrupting the daily usage of ABC.
>> Would appreciate some directions.
>> Thank you
>> Mary
>|||SSIS (as launched via SQL Server Management Services) can support a query to
specify which rows to import/export
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Mary" <noemail@.hotmail.com> wrote in message
news:72185CFE-FB07-4E0C-8255-1FC84A034C8E@.microsoft.com...
> Can a query be used? is so, how?
> "William Vaughn [MVP]" <billvaNoSPAM@.betav.com> escribió en el mensaje de
> noticias news:AAC71D98-1B95-4F63-90F4-0607B79F1222@.microsoft.com...
>> SSIS script or BulkCopy or SqlBulkCopy method. You might also consider
>> ADO.NET 3.5 Sync Services.
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>> "Mary" <noemail@.hotmail.com> wrote in message
>> news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
>> Hello,
>> In an instance, I've two DBs: ABC y copyABC. I would like to update the
>> tables (and their contents added or removed) from ABC to copyABC twice a
>> day. For it will be using a Job but, what would be the syntax?
>> The idea is to get data for my reports from copyABC and creating my own
>> queries without disrupting the daily usage of ABC.
>> Would appreciate some directions.
>> Thank you
>> Mary
>>
>

Friday, February 24, 2012

AMO: How to ProcessFull a cube with no processing a Dimention?

Hi, friends, please have a look:

I just add a measure and do an Update to the cube object by AMO, and do a ProcessFull to the cube.

The problem is the ProcessFull will take a very long time. So I want to Process the cube with no processing the Dimentions, Could I do this?

Because I never change the Dimention, why the ProcessFull tot the cube will ALWAY process the dimentions by itself?

Thanks!

Hello! If you add a measure in a fact table you have actually change the whole structure of the cube and all aggregations will have to be rebuilt. That is why you will have to do a full process of the cube.

A full process of a dimension is only required if you change the structure like adding or changing user hierarchies and if you add or delete attributes. If this have not changed you can process(full) the cube and not the dimensions.

Processing dimensions normally takes less time than processing a cube(measure groups or partitions).

Adding calculated measures and other MDX -script objects do not require a full process. You can use process default in the user interface in BIDS to see that. I am not sure what that is called in AMO.

HTH

Thomas Ivarsson

|||

Thanks, Thomas.

Please just processfull the cube in the user interface in BIDS, NOT in AMO. You can see the process result report box, the ProcessFull to the cube will ALWAY make the dimentions be process ed automatically. Why and how to stop this?

|||

Hello! I think this can depend on a setting under "change setting" when you process the cube. Look for something like process affected objects.

HTH

Thomas Ivarsson

|||If you are doing this from BIDS, then adding a measure means you will need to re-deploy and if you have the default options set it will do a processDefault on the whole database. Which means that the cube that has been altered will be fully processed and the dimensions will all have processUpdate run on them to make sure that they have the latest information in them. If you want full control over the processing - do not use BIDS to process. Using XMLA commands from SSMS or ascmd is probably the best way to go. You could also write your own AMO utility in C# or VB.Net if you wanted.|||

Thanks Darren .

Then, you mean If I use AMO, and I use the cube(0).process(processFull), it WILL NOT make the dimention associated to be processed, right?

|||

A full process of a cube will always mean a full process of the dimensions.

HTH

Thomas Ivarsson

|||

ivanchain wrote:

Thanks Darren .

Then, you mean If I use AMO, and I use the cube(0).process(processFull), it WILL NOT make the dimention associated to be processed, right?

You can do cube(0).process(processDefault) to re-process the cube (and because you removed a measure, the Analysis Services server will decide to re-process the full measure group) without re-processing the dimensions. You can also call processDefault on the measure group, but it doesn't hurt to call it on the cube, the server will skip the other measure groups if they don't need to be re-processed.

The ProcessDefault option will only re-process what needs to be re-processed, if you did structural changes or not.

A description of the process types is available at: http://msdn2.microsoft.com/zh-cn/library/microsoft.analysisservices.processtype.aspx

|||

Adrian and Thomas are right, sorry I said processFull, but I was talking about processDefault.

Another good resource on processing is the Processing Architecture whitepaper: http://msdn2.microsoft.com/en-us/library/ms345142.aspx

|||

Thanks. From what you said, it means that I could always use ProcessDefault? Then why we need the ProcessFull?

Anyway, I will use ProcessDefault. OK?

|||

ProcessFull is to force a clean and full re-processing, if you want to do that.

|||

If I can say, I can always use ProcessDefault any time?

Thanks.

AMO: How to ProcessFull a cube with no processing a Dimention?

Hi, friends, please have a look:

I just add a measure and do an Update to the cube object by AMO, and do a ProcessFull to the cube.

The problem is the ProcessFull will take a very long time. So I want to Process the cube with no processing the Dimentions, Could I do this?

Because I never change the Dimention, why the ProcessFull tot the cube will ALWAY process the dimentions by itself?

Thanks!

Processing dimensions normally takes less time than processing a cube(measure groups or partitions).

HTH

Thomas Ivarsson

|||

Thanks, Thomas.

|||

Hello! I think this can depend on a setting under "change setting" when you process the cube. Look for something like process affected objects.

HTH

Thomas Ivarsson

Thanks Darren .

Then, you mean If I use AMO, and I use the cube(0).process(processFull), it WILL NOT make the dimention associated to be processed, right?

|||

A full process of a cube will always mean a full process of the dimensions.

HTH

Thomas Ivarsson

|||

ivanchain wrote:

Thanks Darren .

Then, you mean If I use AMO, and I use the cube(0).process(processFull), it WILL NOT make the dimention associated to be processed, right?

The ProcessDefault option will only re-process what needs to be re-processed, if you did structural changes or not.

A description of the process types is available at: http://msdn2.microsoft.com/zh-cn/library/microsoft.analysisservices.processtype.aspx

|||

Adrian and Thomas are right, sorry I said processFull, but I was talking about processDefault.

Another good resource on processing is the Processing Architecture whitepaper: http://msdn2.microsoft.com/en-us/library/ms345142.aspx

|||

Thanks. From what you said, it means that I could always use ProcessDefault? Then why we need the ProcessFull?

Anyway, I will use ProcessDefault. OK?

|||

ProcessFull is to force a clean and full re-processing, if you want to do that.

|||

If I can say, I can always use ProcessDefault any time?

Thanks.

AMO: Hanging on Partition.Update

I am using AMO to manage partitions.

I am trying to create a partition then use the Update method to create the new partition.. snippet of code below:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

71 newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

72 //TODO: Deal with warnings

Every time I run it, it just hangs on the Update method. It seems to be looping.. the query execution will only stop when I restart the SSAS Service.

No errors, no log entries, no Event Log entries.... nada, zilch, vacuum, nothing......

Any ideas anyone?

BTW: This is the SP2 CTP.. I think this might be a bug...|||

It sounds like a bug, it should not hang indefinitely. It would be a good idea to post this on the connect site if it is not already there.

In the mean time, have you tried calling Update() at the cube level, not on the partition object? This is the pattern that the AmoAdventureWorks sample uses (one of the AMO samples distributed with SQL Server), they don't actually call update on the partitions or measure groups, calling update at the cube level causes the changes to all the child objects to get persisted.

|||OK.. I'll give that a whirl... I do seem to be spending an awful lot of time at the connect site lately...

|||

Changed the code to the following:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

71 cube.Update(UpdateOptions.ExpandFull,UpdateMode.CreateOrReplace);

72 //newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

73 //TODO: Deal with XmlaWarningCollection warnings

But the answer is still no.. it hangs on the update cube method too.. I can see a CommandBegin in the trace with the following XMLA:

<Create AllowOverwrite="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
...

and the details for the new partition is indeed included in the XMLA...but nothing happens .. it is still hanging... no errors, no logs.. the CPU for the SSAS service is zero with the memory stationary.

When I try to look at anything to do with the SSAS database in the SQL Server Management studio, it just hangs.

This is getting serious!

|||

This is probably a silly question, but can I just confirm that you are in fact using the Enterprise edition of SQL Server? Partitions are an Enterprise only feature and if you try to deploy them to a Standard edition server you will get unpredictable results.

I don't know if you have tried this yet, but I have seen one other case where we had symptoms similar to this, simple "alter" statements were causing the server to "hang". We were lucky that this was a dev environment and deleting the database first and then fully re-deploying appeared to fix this, but I have not yet been able to identify what triggered this behaviour.

|||

This is currently the developer edition, SP2 CTP which I am using as a local dev environment. I don't yet have a proper server environment in which to run tests.

From my understanding developer edition has alll the functionality of the Enterprise edition (?)

|||

You are correct. The Developer edition has the same functionality as the Enterprise edition.

I don't know if this is possible, but could you create a partition manually and trace the xmla that is generated using profiler and then run your program and trace that and see if the xmla that is produced is different? That might help highlight something that you might need to add or change to get things working.

AMO: Can''t update dsv when delete a column

Hi, friends, please have a look at this:

I am using AMO, and I do this:

1 step: I drop a column from the source table by SQL:

ALTER TABLE TargetTable Drop COLUMN ColumnName

2 step: I try to update the dsv by AMO:

Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter( _
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0", connection)
Dim i As Integer

For i = 0 To dsv.Schema.Tables.Count - 1
If dsv.Schema.Tables(i).TableName = "dbo_" & tableName Then
MessageBox.Show("Before dsv.Schema.Tables.Count:" & dsv.Schema.Tables(i).Columns.Count)
Dim dataTable As DataTable = adapter.FillSchema(dsv.Schema.Tables(i), SchemaType.Mapped)
MessageBox.Show("After dsv.Schema.Tables.Count:" & dsv.Schema.Tables(i).Columns.Count)
End If

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column.

3 step: I save the dsv to the server.

If Mainform.tDatabase.DataSourceViews.Count > 0 Then
Mainform.tDatabase.DataSourceViews(0).Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull)
End If

Then I check the server, the dsv still include the columnname I have deleted.

Why and how to update the dsv after I delete a column?

Thanks!

ivanchain wrote:

Dim dataTable As DataTable = adapter.FillSchema(dsv.Schema.Tables(i), SchemaType.Mapped)

ivanchain wrote:

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column

Let's also check if the returned 'dataTable' still contains the column you deleted. If it does contain the column, then we need to double check the table name and its columns in SQL Server.If the returned 'dataTable' doesn't contain the column, it looks like you need to replace the table in the DSV with this returned 'dataTable', but according to documentation at http://msdn2.microsoft.com/en-us/library/152bda9x.aspx, this should not be the case.

The rest of the code looks good, the problem is not in AMO, but in the FillSchema area.

Adrian Dumitrascu

|||

I tried what you said:

Let's also check if the returned 'dataTable' still contains the column you deleted. If it does contain the column, then we need to double check the table name and its columns in SQL Server.

Yes, the returned 'dataTable' still contains the column I deleted. But I don't know what you exactly mean of DOUBLE CHECK the table name and its columns in SQL Server? I need to check what?

Thank you!

|||

The problem is still there.... help!

thanks.

|||

The only ideas that I have are:

- double check that the database name (that you use in the code) is the same with the database on which you removed the column from the table. There might be a concidence that you have 2 databases containing the same table name and column name, you deleted from one, but the code works on the other database by chance (since I don't see in the code where you explicitly chose the database on which to run the SELECT statement)

- double check that the name and the schema, 'dbo', of the table you use in the code are the same as the schema and the name of the table from which you deleted the column

|||

Thanks, Adrian. But, I don't think it's about the NAME of the table. Because my code could update dsv when I add a column into the SQL table in the SQL Server. If the name of the table is wrong, it will also not update when adding, right?

Thanks!

AMO: Can't update dsv when delete a column

Hi, friends, please have a look at this:

I am using AMO, and I do this:

1 step: I drop a column from the source table by SQL:

ALTER TABLE TargetTable Drop COLUMN ColumnName

2 step: I try to update the dsv by AMO:

Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter( _
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0", connection)
Dim i As Integer

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column.

3 step: I save the dsv to the server.

If Mainform.tDatabase.DataSourceViews.Count > 0 Then
Mainform.tDatabase.DataSourceViews(0).Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull)
End If

Then I check the server, the dsv still include the columnname I have deleted.

Why and how to update the dsv after I delete a column?

Thanks!

ivanchain wrote:

Dim dataTable As DataTable = adapter.FillSchema(dsv.Schema.Tables(i), SchemaType.Mapped)

ivanchain wrote:

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column

The rest of the code looks good, the problem is not in AMO, but in the FillSchema area.

Adrian Dumitrascu

|||

I tried what you said:

Let's also check if the returned 'dataTable' still contains the column you deleted. If it does contain the column, then we need to double check the table name and its columns in SQL Server.

Yes, the returned 'dataTable' still contains the column I deleted. But I don't know what you exactly mean of DOUBLE CHECK the table name and its columns in SQL Server? I need to check what?

Thank you!

|||

The problem is still there.... help!

thanks.

|||

The only ideas that I have are:

- double check that the name and the schema, 'dbo', of the table you use in the code are the same as the schema and the name of the table from which you deleted the column

|||

Thanks!

AMO: About Update

hi,friend, please have look:

I want to know, if I do:

dim tdatabase as Microsoft.AnalysisServices.database

' let tdatabase = someone existed on the server

tdatabase.update()

Will it cause the associated object be updated too ?

I mean, if I need NOT to do:

dsv.update()

dim.update()

cube.update()

measure.update()

and so on.

And, I can't find an update method on KPI. If that means I just need to update the cube object after I change a KPI object?

And, If I add/Remove a measure, need I do Process? Or just need to do Update?

Thanks!

ivanchain wrote:

I want to know, if I do:

dim tdatabase as Microsoft.AnalysisServices.database

' let tdatabase = someone existed on the server

tdatabase.update()

Will it cause the associated object be updated too ?

No it will not update all associated objects, see my next statment below.

ivanchain wrote:

I mean, if I need NOT to do:

dsv.update()

dim.update()

cube.update()

measure.update()

and so on.

The following is from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1842622&SiteID=1

Adrian Dumitrascu [MSFT]

The .Update() method only saves the minor properties and collections (thus not the major children) of an object. The major children are the AMO objects derived from the MajorObject class (Database, DataSource, Dimension, Cube, MeasureGroup, Partition, MiningStructure, MiningModel and others).

In our case, the database.Update() will save the database Name, ID, Description, Translations and other few minor properties, but won't save anything about the Role, which is a major object.

So you would need to call .Update on any major objects, so probably yes to dsv, dim, cube, but no to measure (I think a measure is a minor property of the measuregroup)

ivanchain wrote:

And, I can't find an update method on KPI. If that means I just need to update the cube object after I change a KPI object?

And, If I add/Remove a measure, need I do Process? Or just need to do Update?

If you add/remove a measure it will invalidate the cube structure, so once you do an update, your cube will be in an unprocessed state and you will need to process it.

|||

ivanchain wrote:

And, I can't find an update method on KPI. If that means I just need to update the cube object after I change a KPI object?

Yes, you need to call .Update() on the parent Cube. The Update method is only available on major objects (the ones that can be created, changed and deleted by themselves on the Analysis Services server - in AMO they are derived from the MajorObject class). To add/modify/delete a minor object (like Kpi, Measure, DimensionAttribute, Hierarchy, Level) you need to call .Update() on the parent major object.

|||

Very clear answer!

Thank you!

Sunday, February 19, 2012

AMD dual Core or Intel XEON

Hi
In our Office,We are building new server for SQL Server.
Presently we use that server for sql server 2000.
But in future (in 1 year) we will update to sql server 2005
The databases we use are for heavy transactional.
I have the following questions about which hardware to choose.
1.Is AMD dual Core processor GOOD
or
2.Intel xeon is good.
3.What factors we need to look into before deciding sql server hardware?
Any kind of help is greatly appreciated.
Thanks
Kumar
Kumar,
Might try this:
Dell's SQL Server 2000 Sizing Tool quickly and easily helps size your
database to find the right server and storage hardware for your
applications.
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:757C4523-0D67-4777-A667-9ECC335F9323@.microsoft.com...
> Hi
> In our Office,We are building new server for SQL Server.
> Presently we use that server for sql server 2000.
> But in future (in 1 year) we will update to sql server 2005
> The databases we use are for heavy transactional.
> I have the following questions about which hardware to choose.
>
> 1.Is AMD dual Core processor GOOD
> or
> 2.Intel xeon is good.
> 3.What factors we need to look into before deciding sql server hardware?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar

AMD dual Core or Intel XEON

Hi
In our Office,We are building new server for SQL Server.
Presently we use that server for sql server 2000.
But in future (in 1 year) we will update to sql server 2005
The databases we use are for heavy transactional.
I have the following questions about which hardware to choose.
1.Is AMD dual Core processor GOOD
or
2.Intel xeon is good.
3.What factors we need to look into before deciding sql server hardware'
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Might try this:
Dell's SQL Server 2000 Sizing Tool quickly and easily helps size your
database to find the right server and storage hardware for your
applications.
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:757C4523-0D67-4777-A667-9ECC335F9323@.microsoft.com...
> Hi
> In our Office,We are building new server for SQL Server.
> Presently we use that server for sql server 2000.
> But in future (in 1 year) we will update to sql server 2005
> The databases we use are for heavy transactional.
> I have the following questions about which hardware to choose.
>
> 1.Is AMD dual Core processor GOOD
> or
> 2.Intel xeon is good.
> 3.What factors we need to look into before deciding sql server hardware'
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar

Thursday, February 9, 2012

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

alternative for slowly changing dimension (SCD) object

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

cherriesh wrote:

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

Most people use the techniques described here:

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

-Jamie

|||

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh|||

See my blog...

there is a post to populate dimensions... different from others...

Regards!

|||

Hi Pedro,

Which one in your blog? can you post the url.

thanks a lot!

cherriesh

|||

cherriesh wrote:

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh

Cherriesh,

As part of the thread that Jamie posted earlier, there is a discussion on alternatives -- namely loading your updates to a staging table that you later use an Execute SQL task to perform the batch update. Please read through that entire thread.

|||

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Helped?

Regards

|||your problem is resolved?!

Sunday, March 25, 2012

Analysis Service: how can I process cubes?

analysis service

analysis service

Tuesday, March 20, 2012

An workaround (was The Answer (was Re: UDA and SQL Data Access))

An workaround (was The Answer (was Re: UDA and SQL Data Access))

An workaround (was The Answer (was Re: UDA and SQL Data Access))

An update query

an UPDATE inside a SELECT

an sql command that doest work in a page

An other DB copy Q:

Friday, February 24, 2012

AMO: How to ProcessFull a cube with no processing a Dimention?

AMO: How to ProcessFull a cube with no processing a Dimention?

AMO: Hanging on Partition.Update

AMO: Can''t update dsv when delete a column

AMO: Can't update dsv when delete a column

AMO: About Update

Sunday, February 19, 2012

AMD dual Core or Intel XEON

AMD dual Core or Intel XEON

Thursday, February 9, 2012

Alternative to cursor in trigger?

alternative for slowly changing dimension (SCD) object

SQL Alternative to cursor

Blog Archive

About Me