Converting AS2000 DSO from VB6 to VS2005

I have a VB6 program that uses Analysis Services 2000 DSO to Automatically Process an AS2000 Database. I ran the Upgrade Wizard in VS2005 for Visual Basic on this VB6 program but the result has an error I can't find how to correct.

Here is the original code -

Dim mdDatabase As DSO.MDStore
Dim dsoServer As DSO.Server

Set dsoServer = New DSO.Server
dsoServer.Connect ("CubeServer")
Set mdDatabase = dsoServer.MDStores("TestCubes")
mdDatabase.Process

Here is the code as a result of the 2005 upgrade wizard -

Dim mdDatabase As DSO.MDStore
Dim dsoServer As DSO.Server

Set dsoServer = New DSO.Server
dsoServer.Connect ("CubeServer")
mdDatabase = dsoServer.MDStores("TestCubes")

The above line displays the following error: 'DSO.OlapCollection Cannot be indexed'

mdDatabase.Process

Can anyone tell me what's wrong? I can't use AMO because it doesn't work against AS2000. How do I select a specific AS2000 Databse with VB in VS2005 and process it?

Thanks,

JohnWC

Try posting this question on the on the Visual Basic General discussion forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I did find the answer in the OLAP Managed Newsgroup. For anyone who may be interested, the following code line works.

mdDatabase = dsoServer.MDStores.Item("TestCubes")

JohnWC

Converting Access Reports to SRS 2005

Hello Everyone,
Is there a wizard of Visual Studio add-in that I can use to convert MS Access
Reports to SRS 2005?
Regards,
Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MVP - MOM
Founder, SystemCenterForum.org
URL:http://www.systemcenterforum.org
BLOG: http://www.it-jedi.net/
mailto:pete.zerger AT gmail.coOn Apr 21, 5:51 pm, Pete Zerger <pete.zer...@.gmail.com> wrote:
> Hello Everyone,
> Is there a wizard of Visual Studio add-in that I can use to convert MS Access
> Reports to SRS 2005?
> Regards,
> Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MVP - MOM
> Founder, SystemCenterForum.org
> URL:http://www.systemcenterforum.org
> BLOG:http://www.it-jedi.net/
> mailto:pete.zerger AT gmail.com
I use Visual Studio 2005 Professional, and the SSRS Report Designer
has a import utility for Access Report definitions built right into
it. If you start a Report Project, and then right-click on the
Reports folder within the Solution Explorer window, you should see an
option to "Import Reports, Microsoft Access..." appear in a context
menu.
I don't know if BIDS or VB.NET has the same options - I would guess
they do, however.

Converting Access 2000 to SQL

Ok, I finally figured out SQL somewhat. I cannot upsize all of my databases using the upsize wizard provided with Access 2000. I can upsize some database, but most importantly, not the one I need to upsize. Does anybody have any idea why some would upsize and some wouldn't? Could it be a problem with linked tables? OR is there any other way of converting the access databases into SQL databases for SQL Server 2000? Remember, I am a very first time SQL user.
Thanks a lot,
Aaron Shoverwith SQL Server a very handy tool is delivered: Data Transformation Services (DTS). You can use DTS to transform data from one source to another for all major databases, office applications, text files and more. If you are willing to convert to SQL Server this is probably the way to go. You can search in the Books Online for more info.
You can also set up your Access databases as linked servers to access them from SQL server, but conversion is probably better.|||I found the DTS and I used it. Now, this database has a front end and a backend. When I transferred the front end all it allowed me to transfer were the queries. So then I had to go to the back end and transfer the backend in order to transfer the tables. I assume this is because of the database being split, and if it wasn't it would allow me to do both at the same time?|||well the tables aren't located in the front end - how can they be transferred??

They are only *linked* to the tables in the back end.|||That's what I thought; I just wanted conformation so I understand everything that is going on.

Convert SQL table data to XML format

Hello,

I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?

Thanks all in advance,
SauravHello,

I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?

Thanks all in advance,
Saurav

Check BOL for FOR XML Clause|||if you need some structure to your xml (and because you are on 2005), you can use FOR XML PATH, which is infinitely better than the (horrible) 2000 alternative, FOR XML EXPLICIT.

don't use FOR XML EXPLICIT. it's impossible to maintain any but the simplest queries written with it.

Convert Date Format of 00-XXX-00 to NULL

Let me start by saying that I'm brand new to SQL Server 2005 and SSIS.
I'm using the import wizard in SQL2005 to import from a flat file into a table and everything works fine except for dates. A typical date in my flat file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The flat file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"

The value of 00-XXX-00 means that there is no purchase date.

I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.

The table Data Type is datetime.

If I use the import wizard using the example above I get this error message:

- Copying to [cpstest].[dbo].[date] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "DateOfLastOrder" (32) on input "Destination Input" (26). The column status returned was: "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (26)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (26)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - date" (13) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

If I remove the 00-XXX-00 values and import something like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"03-DEC-05"

The import is successfull and the dates look correct in a querry.

SELECT *
FROM date

date
--
2006-01-01 00:00:00.000
2006-01-02 00:00:00.000
2005-12-03 00:00:00.000

(3 row(s) affected)

Does anyone know how I should go about getting these date columns into a datetime table and convert the 00-XXX-00 values into NULLs?

Thank you,

Ryan

Ryan,

The import wizard gives you the option to save the package. You should do this and then open it up in Business Intelligence Development Studio (BIDS) so that you can edit it.

In there you will find a data-flow task which is the thing that does the work. It is made up of things called components which can change the data before it gets inserted to the destination.

You need to introduce a component called a Derived Column component. That can take some input data and change it in-memory. It uses an expression language to do this. In your case the expression wants to be something like:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

Hope that helps!

-Jamie

|||Could you please break down what that expression does or point me to a reference for the expression language?
|||

Don't mean to butt in here, but what Jamie has listed here:

Jamie Thomson wrote:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

-Jamie

uses the SUBSTRING function (like similar functions in VB or C#) and it returns a subtring of the string <input-col> starting at the 4th character of the string and returns 3 characters. In your situation where your specific date that was throwing an error, you would want to look for the ones with "XXX" at this position.

So if the substring of your date column equals "XXX" then return a null value (null of type string) else return your original column value.

The "?" and ":" are somewhat equivalent to "then" and "else." and in this expression language, the "if" is implied.

Hope this helps.

Mark

http://spaces.msn.com/mgarnerbi

|||

There is a very good expression reference in BOL. In fact, that is the only reference seeing as it is rather good - another one aint really needed.

-Jamie