[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

80040e37 error through ADODB but query works in query analyzer

Posted on 2006-11-27
22
Medium Priority
?
444 Views
Last Modified: 2008-01-09
I've got a ADODB.Connection open which works for many queries

I've got a stored procedure that updates a view, the view is created in the stored procedure based on some of the stored procedure parameters.

Query works fine through query analyzer, but when run through asp/ADODB it gives this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]contacts_2006_09

contacts_2006_09 is the name of the view.

0
Comment
Question by:leos_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 4
22 Comments
 
LVL 3

Author Comment

by:leos_
ID: 18023623
correction, the name of the view is ContactTableName

but it is created by pulling data from a table called contacts_2006_09

ie: Create View ContactTableName as Select * from contacts_2006_09 where IsActive=1
0
 
LVL 3

Author Comment

by:leos_
ID: 18023630
Also, I get the same error if I hardcode the table the view is created from.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18023715
can you post the code of this stored procedure?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 2000 total points
ID: 18023844
Do you have permissions to access that table?

FtB
0
 
LVL 3

Author Comment

by:leos_
ID: 18027576
Here is the stored procedure:

CREATE PROCEDURE [dbo].[UpdateAgent]
      @ClientID int,
      @TableName varchar(20),
      @Salutation nvarchar(50),
      @FName nvarchar(50),
      @LName nvarchar(50),
      @Email nvarchar(100),
      @AddressShippingCompanyName nvarchar(100),
      @AddressShippingAddress1 nvarchar(250),
      @AddressShippingAddress2 nvarchar(250),
      @AddressShippingCity nvarchar(100),
      @AddressShippingProvinceCode nvarchar(2),
      @AddressShippingPostalCode nvarchar(16),
      @AddressShippingPhone_AC nvarchar(3),
      @AddressShippingPhone_1 nvarchar(4),
      @AddressShippingPhone_2 nvarchar(4),
      @AddressShippingPhone_X nvarchar(10),
      @AddressShippingCountry nvarchar(2),
      @AgentNumber int,
      @WorkingPercentage float(8),
      @PositionID int,
      @ManagerID int,
      @BranchID int,
      @IsEmail int,
      @IsGroupDepartment int,
      @BirthDay datetime,
      @StartedON datetime,
      @PermissionID int,
      @UserName varchar(250)
      
AS

print @TableName
declare @sqlString varchar(4000)
set @sqlString = 'Create View ContactTableName as Select * from ' + @TableName + ' where IsActive=1'
--set @sqlString = 'Create View ContactTableName as Select * from contacts_2006_09 where IsActive=1'
exec(@sqlString)

declare @AddressShippingId int
declare @AddressBusinessId int
declare @sqlContacts varchar(4000)

set select @AddressShippingId=AddressShippingId from Contacts where pkid=@ClientID

if (@AddressShippingId is null)
begin

      insert into AddressShipping (Address1) values ('1')
      set select @AddressShippingId = @@identity
end

update AddressShipping set
      CompanyName=@AddressShippingCompanyName,
      Address1=@AddressShippingAddress1,
      Address2=@AddressShippingAddress2,
      City=@AddressShippingCity,
      ProvinceCode=@AddressShippingProvinceCode,
      PostalCode=@AddressShippingPostalCode,
      Phone_AC=@AddressShippingPhone_AC,
      Phone_1=@AddressShippingPhone_1,
      Phone_2=@AddressShippingPhone_2,
      Phone_X=@AddressShippingPhone_X,
      Country=@AddressShippingCountry      
      where pkid=@AddressShippingId

print 'shipping address updated'


--update contacts set
update ContactTableName set
      Salutation=@Salutation,
      UserName=@UserName,
      FName=@FName,
      LName=@LName,
      Email=@Email,
      AddressShippingId=@AddressShippingId,
      PositionId=@PositionId,
      AgentNumber=@AgentNumber,
      WorkingPercentage=@WorkingPercentage,
      ManagerID=@ManagerID,
      BranchID=@BranchID,
      IsMonthlyPointsEmail=@IsEmail,
      IsGroupDepartment=@IsGroupDepartment,
      BirthDay=@BirthDay,
      StartedON=@StartedON,
      PermissionID=@PermissionID,
      ModifiedOn=getdate()
where pkid=@ClientID

print 'contact updated'

if (@BranchID is not null and @BranchID <> '')
begin

      set select @AddressBusinessId = BusinessAddressId from Branches where pkid = @BranchID
      
      update Contacts set AddressBusinessID = @AddressBusinessId where pkid = @ClientID      

end

Drop View ContactTableName
GO
0
 
LVL 3

Author Comment

by:leos_
ID: 18027585
Inet user definately has permissions to access the contacts table which the view is created from.  I'm not sure about permissions to access a view that that user created.
0
 
LVL 3

Author Comment

by:leos_
ID: 18027831
after running the stored procedure once, the view is created and the inet user has permission to access it, but the view is not removed, nor is the view (or table view points to) updated.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18030068
why are you using a view for this instead of a temp table?
0
 
LVL 3

Author Comment

by:leos_
ID: 18030617
This view points to a real table which is not temporary. If I update a temporary table the values get lost when I delete it.  However, when I delete the view, the table the view was based on remains updated.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 18031061
I am wondering about the temporary table creation. That may require more permissions than are currently granted.

FtB
0
 
LVL 3

Author Comment

by:leos_
ID: 18032509
There is no temporary table.

There is a temporary view which is created inside the stored procedure.  The view is created based on another table.

The inet user must have permissions to create the view, because the view is in fact created.  Further, under views in SQL Server it shows the inet user as having full permissions to that view.


0
 
LVL 22

Expert Comment

by:WMIF
ID: 18032530
im wondering if it isnt the iusr that should have the permissions.  what are you passing in your connection string?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 18033124
That is what I am getting at. To test, try using a connection string with your sa credentials to see if you still have the problem. If not, then you can work on sorting things out from there,

FtB
0
 
LVL 3

Author Comment

by:leos_
ID: 18038064
You are right. Using a login will admin permissions in the connection string works.

So, after I create the view, am I to assign permissions to the view?  Actually, I'm not sure where to go from here.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18040938
are you passing creds through the connection string, or are you using a trusted connection?
0
 
LVL 3

Author Comment

by:leos_
ID: 18041108
I'm not using a trusted connection, my connection string looks like this:

provider=msdasql;driver={SQL Server};UID=eosuser;password=eosuser;DATABASE=TC;WSID=vibe;SERVER=vibe;

or

provider=msdasql;driver={SQL Server};UID=sa;password=saPassword;DATABASE=TC;WSID=vibe;SERVER=vibe;

sa works, the other does not.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18041293
check out the permissions of that user then.  they might have write, but not delete.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 18046694
If you go into Enterprise Manager, you can give permissions to eouser to create tables, and that will fix the problem. That is what I was getting at in http:#18023844


FtB
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18047323
>>That is what I was getting at in http:#18023844

i agree. points to ftb if this fixes the problem.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question