Database

16-Nov-04 6:55

You are using a trusted connection, the ASP.NET application runs within a user account. You must grant permission to that account in SQL Server.

1. Find out the name of the user that ASP.NET using in IIS:
To find the name of the user open IIS. Open the properties dialog for the virtual directory your application is in. Select the Directory Security Tab. Press the edit button for the Anonymous access group. And take note of the user name.

2. Allow that user to login to SQL Server.
Open SQL Server Enterprise Manager. Open the Security/Logins folder for your SQL Server instance. Right click the Logins folder and select "New Logins...". Click the elipsis button (...) and select the user from the list. Press Add. Press Okay. Select the Database Access tab. Ensure the login has access to the database(s) that you want to use. Press Okay.

The user now has "public" access to the databases you have selected on your SQL Server.

Does this help?

ODBC drivers for Access in WinXP

Skynyrd16-Nov-04 5:13

Skynyrd

16-Nov-04 5:13

Hi all.

My problem is pretty simple. I was just wondering if a application based on a Access mdb file database can be deployed in a System that doesnt have MS Access installed.

The OS is Windows XP. The question is: Are the odbc drivers and the jet drivers for Access databases installed with MS Access or do they come with the OS? I know the app will run if the drivers are present, doesn't matter if Access is installed or not.

I cant test any similar environment because all computers here have Office installed and even uninstalling I cant be sure if the drivers are left behind or they really did come with Windows.

P.d. If drivers dont come with OS, is there a way (legal please) to install them without having to install MS Access?

Thanks for any help.

Re: ODBC drivers for Access in WinXP

fadee16-Nov-04 10:35

fadee

16-Nov-04 10:35

hmmm... yes, I there is no need to install Access driver on client machine. If no office is installed, it will work.

I remember that I have tested this and it was working fine.

MS Access installation would be required if you are creating objects of certain ActiveX components exposed by Access. But I am very much sure you are not doing so. So, I think it will work fine.

Also, another guess, I think to access Access Database, MDAC (or watevva it is) is required on client machine, and Windows XP by default contains this.

Another supporting argument: What MS Access installation has got to do with database connectivity? On servers, Microsoft discourages the installation of MS Office products. And all MS based servers still support Access connectivity.

So bottom line: No need to worry about.

Good Luck
fadee!

-------------------
Therez No Place like ... 127.0.0.1

Re: ODBC drivers for Access in WinXP

Skynyrd16-Nov-04 13:36

Skynyrd

16-Nov-04 13:36

Thanks for the reply Fadee!

glad to here there will be no problem. As a matter of fact, if I would have stopped to think about MDAC I wouldnt have wasted yout time :p... you are absolutely right, MDAC contains the drivers for all standard DB connectivity including Access.

Problems with CDaoDatabase::Execute

Ivan Cachicatari16-Nov-04 4:48

Ivan Cachicatari

16-Nov-04 4:48

Hello all.

I have other problem with CDaoDatabase class. When I attempt execute a long query (more 200 characters) occurs an fatal error.

m_pDB->Execute(...);

Why?

The query string (BSTR) less than 200 characters executes successfully.

I'm using Visual C++ 6 SP5 on Windows 98se and MSDE2000 (MySQL too).

Ivan Cachicatari
www.latindevelopers.com

ASCII Encoding & C#

StrayGrey16-Nov-04 4:41

StrayGrey

16-Nov-04 4:41

I have a situation where I am reading a MS SQL database table containing user
names and passwords in a C# program.
After applying Server Pack 3 to SQL to this database I can no longer get a user
to successfully logon.
The program is in C# and the particular line of code is:-
dr["Passwrd"] = AE.GetBytes(password);
How can I resolve this.

Regards,
Alf

A loop in T-SQL ?

Sebastien Lachance16-Nov-04 4:37

Sebastien Lachance

16-Nov-04 4:37

Is there a way to iterate through each line of a Select Statement ?
In the stored proc ? And do other update on another table while in the loop ?

Thank you very much in advance Smile | :)

Well.. later

Colin Angus Mackay16-Nov-04 5:47

16-Nov-04 5:47

You can use "cursors" to do that. However, Cursors have a big performance penalty - If you can do the same job without cursors then do that instead.

Sebastien Lachance16-Nov-04 7:05

Sebastien Lachance

16-Nov-04 7:05

Thats what i wanted not to happen Frown | :(

I will try every other mean i have before switching to that. Thank you !

Well.. later

Colin Angus Mackay16-Nov-04 9:45

16-Nov-04 9:45

What is it that you are actually trying to do? It might help to know that in order to come up with a non-cursor based solution.

Sebastien Lachance16-Nov-04 9:54

Sebastien Lachance

16-Nov-04 9:54

I was trying to update the first table and updating the second at the same time, depending on one of the 6 items put in the first. It's hard to explain.

Well.. later

Colin Angus Mackay16-Nov-04 10:04

16-Nov-04 10:04

Perhaps the use of a temporary table during the processing would be useful.

I'm assuming the the filter to determine which rows are updated is non-trivial (i.e. it takes some time or a complex set of joins)

Create a temporary table with the primary key to the FirstTable and foreign key to the second table. Then you populate the temp table with the keys to the rows that will need to be updated.

Make the update on the first table using the temporary table to quickly get at the relevant rows.

Now, using the temporary table again, join with the first table and make the update on the second table.

I don't know if that makes sense, but from what I understand of your scenario it would probably work.

DELETE X WHERE Y IN (SubQuery) Problem

Tristan Rhodes16-Nov-04 0:55

Tristan Rhodes

16-Nov-04 0:55

I've got a Delete command in an SQL statement. And i'm getting an INNER JOIN error, but the sub query works fine on its own. It's a bit baffeling.

'Build SQL to Delete subCategory descriptions, and execute
str_SQL = "DELETE FROM Description WHERE Description.BasicDesc IN " _
& "(SELECT Description.BasicDesc " _
& "FROM ((Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) " _
& "INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
& "WHERE ((Category.CategoryDescription)='" & GetCurrCategory & "')))"

Any thoughts?

Cheers

Cata

Re: DELETE X WHERE Y IN (SubQuery) Problem

Colin Angus Mackay16-Nov-04 1:38

16-Nov-04 1:38

You have to name the subquery and then use the name you've given the subquery in your INNER JOIN.

FROM (SELECT a, b, c FROM d WHERE e = f) AS subQuery<br />
INNER JOIN otherTable ON subQuery.a = otherTable.a

Re: DELETE X WHERE Y IN (SubQuery) Problem

Tristan Rhodes16-Nov-04 3:17

Tristan Rhodes

16-Nov-04 3:17

I don't fully understand the As SubQuery thing. I'm working in access and not exactly an SQL guru. Why does this query work fine:

str_SQL = "SELECT Description.BasicDesc " _
& "FROM ((Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) " _
& "INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
& "WHERE ((Category.CategoryDescription)='" & GetCurrCategory & "'))"

but when I try and set an IN into it, it doesn't want to know, even though I use full parenthesis?

Cheers

Cata

Re: DELETE X WHERE Y IN (SubQuery) Problem

Colin Angus Mackay16-Nov-04 5:46

16-Nov-04 5:46

The Catalyst wrote:
I don't fully understand the As SubQuery thing

Maybe it is a SQL Server thing. INNER JOINs onto subqueries don't work in SQL Server unless you name the subquery.

Okay. As it is quite difficult to read the SQL in this form let's extract it:

DELETE FROM Description WHERE Description.BasicDesc IN 
(SELECT Description.BasicDesc 
FROM ((Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) 
INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey 
WHERE ((Category.CategoryDescription)='xxxxx')))

Alot of these parethesis are confusing. And quite frankly I'd remove all except the ones shown:

DELETE FROM Description WHERE Description.BasicDesc IN 
(SELECT Description.BasicDesc 
FROM Category 
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey 
WHERE Category.CategoryDescription='xxxxx')

I don't know if that makes any difference. The only other way I'd do it is something like this:

DELETE Description
FROM Description
INNER JOIN SubCategory ON SubCategory.SubCategoryKey = Description.SubCategoryKey 
INNER JOIN Category ON Category.CategoryKey = SubCategory.CategoryKey
WHERE Category.CategoryDescription='xxxxx')

But that may just work in SQL Server.