|
Hi.
i'm working in an ADO App that uses Access and/or MSSQL Server 2000 and my problem is that when i Execute in the Connection the following:
DELETE x.* FROM [ODBC;DRIVER={SQL Server};UID=USER; PWD=PASSWORD;DATABASE=WORKING_DB;APP=Microsoft Data Access Components;SERVER=CONTROL02;Description=SQLServer].test_table01 x WHERE clave like '_017%'
i get an error "could not delete from specified tables".
notes:
needs to be with odbc.
I didn't made the database.
I tried using different user accounts.
Tried trusted connection
my permissions all to read write
with and without alias to x.*, and removed the *
my connection string:
Provider=Microsoft.Jet.OLEDB.4.0; Password=PPP;User ID=UUU;Data Source=XXX;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False
even in Access it won't work.
what i'm doing wrong?
|
|
|
|
|
Hey im new to ASP.NET web applications and sql server and am just starting to get a grip of web applications but I have been trying in vain for the last few days to get this walkthrough (that’s available with Visual Studio .NET) to connect to my sql server database but every time I try to run the application I get this error that says that the user “ASP” could not be logged on and so access is denied. Im just wondering if anyone has any ideas what the problem is or how I would go about solving it
Thanks in advance
Tadhg88
|
|
|
|
|
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?
Do you want to know more?
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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 
Well.. later
|
|
|
|
|
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.
Do you want to know more?
|
|
|
|
|
Thats what i wanted not to happen  I will try every other mean i have before switching to that. Thank you !
Well.. later
|
|
|
|
|
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.
Do you want to know more?
|
|
|
|
|
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
|
|
|
|
|
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.
Do you want to know more?
|
|
|
|
|
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
|
|
|
|
|
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
Do you want to know more?
|
|
|
|
|
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
|
|
|
|
|
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.
Do you want to know more?
|
|
|
|
|
When i import both libraries in Pocket PC project i get some redefinition errors:
<br />
error C2011: '_Collection' : 'struct' type redefinition<br />
error C2011: 'DataTypeEnum' : 'enum' type redefinition<br />
error C2011: 'Properties' : 'struct' type redefinition<br />
error C2011: 'Property' : 'struct' type redefinition<br />
how can be used both of them and avoid conflicts?
were rgrtgrtvrtrt rtrtb brt tyuhjghj hbhbnh hnjm 1234567?
|
|
|
|
|
Hi friends
I make a function in sql 2000. I pass One parameter to that function and i return a table from that function
i have 3 different tables. 1 is master table.
e.g
i have table of doctor i.e master
and another 2 are 1 is specialization and another is status in specialization there are specialities of doctror like GP, Physician etc and in status the status of doctor is Important and very Important etc
I want result like this
Important very Important
GP 4 5
Phy 3 2
first row indicate 4 Gp doctors are important and 5 are very important
i make a function to return this result
but i dont know how many Specialities in table
and how many status
so i want to alter temperory table in the function
create function abc(@docid)
returns table @tab (no varchar(10))
as
begin
declare @spec varchar(20)
declare @vis varchar(20)
declare @no int
declare @ctr int
select @ctr = count(visittypeid) from doc_visitytype where visitytypeid <> 0
while (@ctr >= 0)
begin
select @vis = visittype from doc_visittype where visittypeid = @ctr
ALTER TABLE @tab
ADD @vis varchar(10)
end
return
end
this code add the columns in the temporary table
but @vis is local veriable how can i assign a veriable that it should take value of @vis as a coloumn name
Thank You
|
|
|
|
|
Hi All
We have a table with a persons Employee number (Resource tag) , Date Worked and the Shift Worked on that date.
Like So
resource tag date worked shift type
------------ ------------------------------------------------------ --------------------------------------------------
125197584 2004-07-14 00:00:00.000 Night
125197584 2004-07-15 00:00:00.000 Night
125197584 2004-07-16 00:00:00.000 Night
125197584 2004-07-17 00:00:00.000 Night
125197584 2004-07-18 00:00:00.000 Morning
125197584 2004-07-19 00:00:00.000 Morning
125197584 2004-07-20 00:00:00.000 Morning
125197584 2004-07-21 00:00:00.000 Morning
125197584 2004-07-22 00:00:00.000 Morning
125197584 2004-07-23 00:00:00.000 Morning
125197584 2004-07-24 00:00:00.000 Morning
125197584 2004-07-25 00:00:00.000 Night
125197584 2004-07-26 00:00:00.000 Night
125197584 2004-07-27 00:00:00.000 Night
125197584 2004-07-28 00:00:00.000 Night
125197584 2004-07-29 00:00:00.000 Night
125197584 2004-07-30 00:00:00.000 Night
125197584 2004-07-31 00:00:00.000 Night
125197584 2004-08-01 00:00:00.000 Morning
125197584 2004-08-02 00:00:00.000 Morning
125197584 2004-08-03 00:00:00.000 Morning
125197584 2004-08-04 00:00:00.000 Morning
125197584 2004-08-05 00:00:00.000 Afternoon
125197584 2004-08-06 00:00:00.000 Morning
125197584 2004-08-07 00:00:00.000 Morning
125197584 2004-08-08 00:00:00.000 Morning
125197584 2004-08-09 00:00:00.000 Morning
125197584 2004-08-10 00:00:00.000 Morning
125197584 2004-08-11 00:00:00.000 Morning
125197584 2004-08-12 00:00:00.000 Morning
125197584 2004-08-13 00:00:00.000 Morning
125197584 2004-08-14 00:00:00.000 Morning
125197584 2004-08-15 00:00:00.000 Night
125197584 2004-08-16 00:00:00.000 Night
I need to find all the dates where the shift type has changed.
The only way that I have been able to figure out how to do this is to left join the table on itself , adding 1 day to the date worked in the right hand table and then selecting the differences.
The problem with this is that I may have up to 20 thousand people in the table and the left join is time consuming. The other problem is that the very first record has to be retrieved with a separate select.
Thanks
Regards
Peet Schultz
South Africa
If anybody wants I can mail a script to create the table and data.
|
|
|
|
|
You can try something like:
<code>SELECT
wt.DateWorked
FROM
WorkTable wt
WHERE
wt.ShiftType <>
(SELECT
ShiftType
FROM
WorkTable
WHERE
ResourceTag = wt.ResourceTag AND
DateWorked = (SELECT
MAX(DateWorked)
FROM
WorkTable
WHERE
ResourceTag = wt.ResourceTag AND
DateWorked < wt.DateWorked))
The table will need the following index to work quickly:
<code>CREATE INDEX ShiftTest ON WorkTable
(
ResourceTag,
DateWorked
)
This assumes that no worker will work more than 1 shift per day.
|
|
|
|
|
Thanks
This looks good, only problem is does not return the first record either.
I do have a way of getting past that and will run a couple of benchmarks to check the speed of this against the left join
Regards
Peet
YASP
|
|
|
|
|
Didn't think about the first record.
Just wrap the correlated Select in an ISNULL() that will return an invalid ShiftType:
wt.ShiftType <> ISNULL((Select...),'')
|
|
|
|
|
Hello! I am facing a problem while generating sql script from Enterprise Manager for some reason there are storeprocedure which are left while generating the sql script when I checked the difference between the other store procedures and the one which are left and are not being copied to the sql script , I found the only is that for some reason these store procedures are of "SYSTEM" TYPE , I mean in the store procedure area it shows you " Store procedure name , Owner and then Type so the Type for these are shown as system althout these arent system but created by us and their own is of the user we login to the database with and we have all the premission to change them . Please some one help me with this issue as I cant find a solution .
Thanks !
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
