AMO and ProcessAdd for dimensions

Is there any way to process a dimension using ProcessAdd using AMO without using xmla? We currently have an app for SQL2000 that processes dimensions using DSO - ProcessUpdate that a colleague insists can just be changed to AMO - ProcessAdd but I am not getting the impression that it is as simple as that.

A quick yes or no would be great!

Thanks

Hi,

You can do it, but this only add the new records to your dimension.

If your dimension data has updated records they will not be updated in SSAS dimension.

You can check this link

http://msdn2.microsoft.com/en-us/library/ms345142.aspx

Regards,

Raul

|||

Thanks for the response. However, I have read that link previously and the examples all show that xmla must be used. I was wondering something similar to this post:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2147144&SiteID=17

rprs wrote:

Hi,

You can do it, but this only add the new records to your dimension.

If your dimension data has updated records they will not be updated in SSAS dimension.

You can check this link

http://msdn2.microsoft.com/en-us/library/ms345142.aspx

Regards,

Raul

Am I being role happy?

I'm working on the security portion of this vb app. In the application the
user is presented with a list of commands that can be executed on a piece of
data.

My client wants to be able to block certain users from seeing certain
commands on their screen.

The commands are loaded into a listview from a table.

I've created a role for each combination of command/province(A user may be
able to execute a command on an Ontario piece of data but not a Quebec
piece).

I just modified the query that loads the list box so that it includes an
IS_Member with the command/province combination.

The roles were easy to create(sql query to create the sp_addrole commands).
And the query modifications were easy as well. Is_Member barely affects
query times(very fast).

I've also created roles that contain these roles so a user only has to be
added to one role to get access to large groups of commands.

I may have one problem though. I've created hundreds of roles on the
server. Is that a problem? Can anyone tell me if there's a better way of
doing this?

Thanks in advance.
J
P.S. The application has just under 500 users and is going to hit 1000 in a
couple of years.Me (me@.here.com) writes:
> I've created a role for each combination of command/province(A user may be
> able to execute a command on an Ontario piece of data but not a Quebec
> piece).
> I just modified the query that loads the list box so that it includes an
> IS_Member with the command/province combination.
> The roles were easy to create(sql query to create the sp_addrole
> commands).
> And the query modifications were easy as well. Is_Member barely affects
> query times(very fast).
> I've also created roles that contain these roles so a user only has to be
> added to one role to get access to large groups of commands.
> I may have one problem though. I've created hundreds of roles on the
> server. Is that a problem? Can anyone tell me if there's a better way of
> doing this?

The one thing that I don't really like this design is that you use
SQL Server's own mechanisms to control data access within the
application. I would prefer to have my own authorization tables.
This design can confuse a DBA who thinks roles are for control access
to tables and T-SQL commands.

Then again, if it ain't broke...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.
One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.
|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement = N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:
> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement => N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> > We have been using the textcopy.exe tool since SQL7 (we were able to still
> > use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> > can
> > use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> > record
> > in our database. Basically, the VB6 code calls a stored proc and passes
> > it
> > the name and location of the file. The stored proc will then use the
> > textcopy.exe to upload this file into an image column in a database table:
> >
> > SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> > password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> >
> > EXEC Master..xp_cmdShell @.cmd
> >
> > where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> > test.pdf).
> >
> > When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> > this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> > to
> > work in 2005.
> >
> > What is the best way to insert a file into an image column in the database
> > in 2005? I'd like to not have to re-architect this piece of our
> > application,
> > but I may have to...is there an alternative similar to textcopy.exe for
> > SQL
> > 2005? Indexing the file is not important - we just need to save the file
> > in
> > the database so that it can be viewed from a web application.
> >
> > Thanks!
> > Jeff M.
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or recor
d
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe t
o
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application
,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from you
r
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>