create procedure ProcessCube
@Database varchar(100),
@Cube varchar(100),
@Partition varchar(100) = null, -- If NULL, process the entire Cube
@Server varchar(100) = 'localhost'
as
/*
Author: Jeff Smith
Version: 10/27/2008
*/
/* variables used to store object handles */
declare @o_svr int, @o_db int, @o_cube int, @o_part int, @o_mds int
declare @hr int
/* different cube processing options. This SP uses "default" */
declare @PROCESS_DEFAULT int
declare @PROCESS_FULL int
declare @PROCESS_REFRESH_DATA int
set @PROCESS_DEFAULT = 0
set @PROCESS_FULL = 1
set @PROCESS_REFRESH_DATA = 2
-- create a DSO.Server object:
exec @hr = sp_OACreate 'DSO.Server', @o_svr out
if @hr <> 0
begin
print 'Error at create server:'
exec sp_OAGetErrorInfo @o_svr
goto cleanup
end
-- Connect to the server:
exec @hr = sp_OAMethod @o_svr, 'Connect', null, @Server
if @hr <> 0
begin
print 'Error at connect to server:'
exec sp_OAGetErrorInfo @o_svr
goto cleanup
end
-- Get the MDStores property from the Server:
exec @hr = sp_OAGetProperty @o_svr,'MDStores', @o_mds OUT
if @hr <> 0
begin
print 'Error at get getting Server MDStores:'
exec sp_OAGetErrorInfo @o_svr
goto cleanup
end
-- Get the database from the MDStores:
exec @hr = sp_OAGetProperty @o_mds,'Item',@o_db OUT,@Database
if @hr <> 0
begin
print 'Error at get database:'
exec sp_OAGetErrorInfo @o_mds
goto cleanup
end
-- get the MDStores property from the database:
exec sp_OADestroy @o_mds
exec @hr = sp_OAGetProperty @o_db,'MDStores', @o_mds OUT
if @hr <> 0
begin
print 'Error at get database MDStores:'
exec sp_OAGetErrorInfo @o_db
goto cleanup
end
-- get the Cube from the MDStores
exec @hr = sp_OAGetProperty @o_mds,'Item',@o_cube OUT, @Cube
if @hr <> 0
begin
print 'Error at get Cube:'
exec sp_OAGetErrorInfo @o_mds
goto cleanup
end
if @Partition is null -- Process the entire Cube, not just a single partition
begin
exec @hr = sp_OAMethod @o_cube, 'Process', null, @PROCESS_DEFAULT
if @hr <> 0
begin
print 'Error at process Cube:'
exec sp_OAGetErrorInfo @o_cube
goto cleanup
end
end
else -- just process the specified Partition
begin
-- Get the MDStores property of the Cube:
exec sp_OADestroy @o_mds
exec @hr = sp_OAGetProperty @o_cube,'MDStores', @o_mds OUT
if @hr <> 0
begin
print 'Error at get Cube MDStores:'
exec sp_OAGetErrorInfo @o_cube
goto cleanup
end
-- Get the partition to process:
exec @hr = sp_OAGetProperty @o_mds,'Item',@o_part OUT, @Partition
if @hr <> 0
begin
print 'Error at get Parition:'
exec sp_OAGetErrorInfo @o_mds
goto cleanup
end
-- Process the partition:
exec @hr = sp_OAMethod @o_part, 'Process', null, @PROCESS_DEFAULT
if @hr <> 0
begin
print 'Error at process Partition:'
exec sp_OAGetErrorInfo @o_part
goto cleanup
end
end
-- And unlock all objects on the server:
exec @hr = sp_OAMethod @o_svr, 'UnlockAllObjects'
if @hr <> 0
begin
print 'Error at unlock all server objects:'
exec sp_OAGetErrorInfo @o_svr
goto cleanup
end
cleanup:
if @o_mds is not null exec sp_OADestroy @o_mds
if @o_Part is not null exec sp_OADestroy @o_Part
if @o_cube is not null exec sp_OADestroy @o_cube
if @o_db is not null exec sp_OADestroy @o_db
if @o_svr is not null exec sp_OADestroy @o_svr
A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc). I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled.
In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written like this:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values ('" + txtDate.Text + "')";
c.ExecuteNonQuery();
Now, I think that hopefully even most beginners will agree that this is bad code. The primary issue, of course, is SQL Injection. Avoiding SQL Injection is very easy to do using Parameters. So, let's say that you rewrite this code using parameters like this:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.AddWithValue("@DateVal",txtDate.Text);
c.ExecuteNonQuery();
Looking at that, it seems we have done quite a bit better and should be happy with the code. It works well, and no injection is possible. But there is still an issue! Why? The txtDate.Text property returns a string, not a DateTime! And, since we are not setting the data type of the parameter explicitly, the parameter being passed is a string (i.e., VARCHAR or NVARCHAR) value, not a true DateTime value. This means that SQL Server must implicitly cast your string to a DateTime to store it in your table, and this may or may not work successfully, or as expected, depending on how the string is formatted.
I've said it over and over and I'll say it again: The concept of formatting dates should never be something that your database code should ever worry about. The database layer should be accepting DateTime data from clients, and returning DateTime data to your clients. Where and how the client got the data before passing it to the database, or what the client does with the data in terms of formatting after receiving it from the database is of no concern to the database itself.
So, we might decide that to fix this, we can simply declare the data type of the parameter explicitly:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = txtDate.Text;
c.ExecuteNonQuery();
It appears that now we are in good shape, right? Actually -- no! There is still an implicit conversion happening, because we are still passing a string value -- the txtDate.Text property -- to the parameter, not a true DateTime!
Let's try one more time. How can we avoid these implicit conversions? The answer that question is always the same: Convert explicitly! Your client application is fully capable of handling the parsing, validation, and conversion of that string to a true DateTime value, so go ahead and do it:
DateTime dateval = DateTime.Parse(txtDate.Text); // plus more code to validate, of course
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = dateval;
c.ExecuteNonQuery();
Now we are in business! Before we even create the SqlCommand object, we have a true DateTime value that we are ready to pass along to SQL Server. Our SQL code doesn't need to worry about formatting, parsing, converting, or anything -- it is being passed a completely valid piece of data with the correct type. In short, we can now be sure that whatever value we came up with for the date in our client code is exactly the value that will be stored in our database. That's the idea, right?
So, please, don't rely on your database code to validate your input. Don't just pass along generic string data and "hope" that at the end of the day the database can "handle it". Eliminate the chance of anything going wrong and write your code to explicitly cast and convert and validate any and all input before the database even comes into the picture.
