AMO with SQL2000

I am writing code in C# with VS2005. The code that I am writing uses SQL2000 and DSO. I want to upgrade to AMO but still use SQL2000. Does AMO work with SQL2000? I cannot upgrade the database to SQL2005.

You will need to upgrade to AS2005. Here's some relevent info.

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

|||

Yan is right,

AMO = AS2005 administrative API

DSO = AS2000 administrative API

But you should not have too much trouble using DSO from C# as VS.NET will generate a RCW (Runtime COM Wrapper) for you. If you are comfortable with VB.NET it might be a slightly better choice for coding against a COM library as it has better support for optional parameters and a few other things that were common in COM libraries.

But I have used C# against other COM dlls and most of the time it is not an issue and the optional parameter thing can be overcome by simply typing in all the default parameters, it's not incorrect, I just find VB.NET "cleaner" when working against COM DLLs, but that could be just me.

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 close or way off the mark?

I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP.

I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working?

// stored procedure to write externally generated random number value into database

PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE Links SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)

// Part One select links that need random number inserted.

public DataTable GetRandLinks()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd = new SqlCommand("RandomizerSelect001", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "Random001");
return ds.Tables["Random001"];
}
catch
{ throw new ApplicationException("Data error"); }
}

// Part Two I need two write a random number back into each row

protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
int LinkID; // this generates unassigned local variable "LinkID' error
int LRank; // this generates unassigned local variable "LRank' error

SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@.L_ID", LinkID);
cmd2.Parameters.AddWithValue("@.L_Rank", LRank);
SqlDataAdapter da2 = new SqlDataAdapter();

int RowIncrement;
RowIncrement = 0;
DataTable dt = GetRandLinks();
foreach (DataRow row in dt.Rows)
{
System.Random myRandom = new System.Random();
int LinkRank = myRandom.Next(25, 250);
LRank = LinkRank;
da2.UpdateCommand = cmd2;
RowIncrement++;
}

}

Question - instead of looping back to the database server for every row, can I pull the data into a DataTable, and then on the .Net web server update each row with it random number using system.Random and then finally read the updated DataTable back to update the SQL Database table? And if so is any of the code I have written useful to that end?

|||

Most of the T-SQL limitations can be gotten around by feeding the rand function the right 4 digits of the newid function.

|||

How would I incorporate that into this stored procedure? I am working on a SQL 2000 server, company hasn't got the money for SQL2007. And is there a way to limit the random number range to > 25 and < 250?

PROCEDURE spA_Random001
AS
DECLARE @.L_ID int
DECLARE @.L_Rank int
DECLARE cur CURSOR FOR
SELECT L_ID , L_Rank
FROM tblLinkInfo_OLD2
OPEN cur;
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.L_Rank > 10 AND @.L_Rank < 300)
UPDATE tblLinkInfo_OLD2 SET L_Rank = Convert(int, (L_ID)*RAND()) WHERE (L_ID = @.L_ID)
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
END
CLOSE cur;
DEALLOCATE cur;

|||

Thank you for the suggestion, it is an excellent solution for most applications however in my cash I need a strictly limit the random value to integers between 25 and 250. So far - pulling the table data into a DataSet, using the webserver resident System.Random to generate the random number and then writing the changes up to the SQL server seems to be the solution. I have been spoiled by the drag and drop GridViews and DataLists no I have to learn what is really going one. I am going to try to get th above solution working but I am beginning to think I am way of track.

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...
>
>