ask for ASP connection string to ODBC

do anyone know the connection string for ASP connect to ODBC?
i have created an ODBC for MSSQL named "DMZ-SQL" already.
Thank you very much!!I have an example for DSN-less connection via ADO, so u don't have to always create your Data Source Name in the ODBC properties everytime u move your development.

Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open "Driver={SQL Server};SERVER=127.0.0.1;UID=sa;PWD1234=;DATABASE= Mydatabase"|||Thank you very much!

AS2005 Retreiving Data Problem

Hi All,

I'm having problem retreiving data from a AS2005.

I'm using a named instance, called Desktop126\SQL05

This code is being used:

using adomdnet = Microsoft.AnalysisServices.AdomdClient;

...

System.Text.StringBuilder result = new System.Text.StringBuilder();

using (adomdnet.AdomdConnection conn = new AdomdConnection("Data Source=Desktop126\\SQL05;Catalog=BuypassDW;Integrated Security=SSPI; ConnectTo=default")){

conn.Open();

//Create a command, using this connection

adomdnet.AdomdCommand cmd = conn.CreateCommand();

cmd.CommandText = @."

SELECT

{[Transactions Types].[ID].[All].Children} ON columns,

{[Merchants].[l Merchant Id].&[236553]} ON ROWS

FROM [Buypass DW1] WHERE( [Time].[Year].[2005])";

//Execute the query, returning a cellset

adomdnet.CellSet cs = cmd.ExecuteCellSet(); -- Fails with error:

// Either the user, DomainName\UserName, does not have access to the BuypassDW database, or the database does not exist.

conn.Close();

result.Append("\t");

adomdnet.TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;

foreach (adomdnet.Tuple column in tuplesOnColumns)

{

result.Append(column.Members[0].Caption + "\t");

}

result.AppendLine();

//Output the row captions from the second axis and cell data

//Note that this procedure assumes a two-dimensional cellset

adomdnet.TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;

for (int row = 0; row < tuplesOnRows.Count; row++)

{

result.Append(tuplesOnRows[row].Members[0].Caption + "\t");

for (int col = 0; col < tuplesOnColumns.Count; col++)

{

result.Append(cs.Cells[col, row].FormattedValue + "\t");

}

result.AppendLine();

}

conn.Close();

} // using connection

}

Any idea why this is failing, the user has permissionto to access the source database,

in AS the datasource has the follwoing value for impersonation info ( AS2005-->dataource-->property)

ImpersonateCurrentUser.

Also in the AS2005 service the DomainName\UserName is being added .

Any hlep will be appreciated.

Thanks.

Abdu

Looks like you havent granted your users access to Analysis Services database.

Create a new role in BypassDW database, add your domain\user to the new role and grant the role DB admin rights. See if user can issue a query.

If everything works, change your role permissions to allow new role read only access to the cubes in the database.

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

Articles in more than one Category - How to organize tables?

Greetings,
I have one table, named Article, and one table name Category.
The problem is, one Article could be in just one or in several categories.
What is the best way to connect data between Article and Category according to fast search performance?
I have several ideas:
1. To have third cross table Article_Category with fields Article_ID and Category_ID, and search Article_Category table
2. To have several INTEGER columns in Article table (like Category_ID1, Category_ID2,..) and search those columns
3. Add one VARCHAR field in Article table where I could write Category ID's delimited by some character (e.g. by comma), and do text search in only that column.
What is recommended for solving problems like this?

Option 1 makes the most sense. This is how many-to-many relationships are generally implemented.

|||Thanks for advice Adam|||Yes, that is how I implemented them, it works well. Then you can use an inner join to join the article info to the category info.|||

Will you have:
Option A: Many-to-Many (category 1 has article 1 and 2, category 2 has article 3, etc)

Option B: 1-to-Many (Category 1 has article 2, category 2 has article 2, category 3 has article 5, etc)
If B, you can put your article_id in your category table. This would give you the fastest search performance and allow your joins to be simpler.

Nick

|||Hi bmains,
how large are your tables?
Do you run it on Web with ASP.NET?
Do you satisfied with search speed?|||Hi Nick,
one Category will have many articles,
one Article could be in more than one category.

There is 500 000 articles and almost 2000 categories.
Regards|||

If you are talking a join between the join table and the two main tables, then no, I wouldn't worry about speed; it shouldn't be worse performance-wise. Though, if you are talking a lot of joins, then you need to worry more. outer joins are worse than inner joins; inner joins aren't bad; here we have a requirement that you have to try to rewrite a query to use an inner join if possible, when an outer join is used.