leos_
asked on
80040e37 error through ADODB but query works in query analyzer
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.
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.
ASKER
Also, I get the same error if I hardcode the table the view is created from.
can you post the code of this stored procedure?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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),
@AddressShippingCompanyNam e nvarchar(100),
@AddressShippingAddress1 nvarchar(250),
@AddressShippingAddress2 nvarchar(250),
@AddressShippingCity nvarchar(100),
@AddressShippingProvinceCo de 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=Address ShippingId 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=@AddressShippi ngCompanyN ame,
Address1=@AddressShippingA ddress1,
Address2=@AddressShippingA ddress2,
City=@AddressShippingCity,
ProvinceCode=@AddressShipp ingProvinc eCode,
PostalCode=@AddressShippin gPostalCod e,
Phone_AC=@AddressShippingP hone_AC,
Phone_1=@AddressShippingPh one_1,
Phone_2=@AddressShippingPh one_2,
Phone_X=@AddressShippingPh one_X,
Country=@AddressShippingCo untry
where pkid=@AddressShippingId
print 'shipping address updated'
--update contacts set
update ContactTableName set
Salutation=@Salutation,
UserName=@UserName,
FName=@FName,
LName=@LName,
Email=@Email,
AddressShippingId=@Address ShippingId ,
PositionId=@PositionId,
AgentNumber=@AgentNumber,
WorkingPercentage=@Working Percentage ,
ManagerID=@ManagerID,
BranchID=@BranchID,
IsMonthlyPointsEmail=@IsEm ail,
IsGroupDepartment=@IsGroup Department ,
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
CREATE PROCEDURE [dbo].[UpdateAgent]
@ClientID int,
@TableName varchar(20),
@Salutation nvarchar(50),
@FName nvarchar(50),
@LName nvarchar(50),
@Email nvarchar(100),
@AddressShippingCompanyNam
@AddressShippingAddress1 nvarchar(250),
@AddressShippingAddress2 nvarchar(250),
@AddressShippingCity nvarchar(100),
@AddressShippingProvinceCo
@AddressShippingPostalCode
@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=Address
if (@AddressShippingId is null)
begin
insert into AddressShipping (Address1) values ('1')
set select @AddressShippingId = @@identity
end
update AddressShipping set
CompanyName=@AddressShippi
Address1=@AddressShippingA
Address2=@AddressShippingA
City=@AddressShippingCity,
ProvinceCode=@AddressShipp
PostalCode=@AddressShippin
Phone_AC=@AddressShippingP
Phone_1=@AddressShippingPh
Phone_2=@AddressShippingPh
Phone_X=@AddressShippingPh
Country=@AddressShippingCo
where pkid=@AddressShippingId
print 'shipping address updated'
--update contacts set
update ContactTableName set
Salutation=@Salutation,
UserName=@UserName,
FName=@FName,
LName=@LName,
Email=@Email,
AddressShippingId=@Address
PositionId=@PositionId,
AgentNumber=@AgentNumber,
WorkingPercentage=@Working
ManagerID=@ManagerID,
BranchID=@BranchID,
IsMonthlyPointsEmail=@IsEm
IsGroupDepartment=@IsGroup
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
ASKER
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.
ASKER
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.
why are you using a view for this instead of a temp table?
ASKER
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.
I am wondering about the temporary table creation. That may require more permissions than are currently granted.
FtB
FtB
ASKER
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.
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.
im wondering if it isnt the iusr that should have the permissions. what are you passing in your connection string?
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
FtB
ASKER
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.
So, after I create the view, am I to assign permissions to the view? Actually, I'm not sure where to go from here.
are you passing creds through the connection string, or are you using a trusted connection?
ASKER
I'm not using a trusted connection, my connection string looks like this:
provider=msdasql;driver={S QL Server};UID=eosuser;passwo rd=eosuser ;DATABASE= TC;WSID=vi be;SERVER= vibe;
or
provider=msdasql;driver={S QL Server};UID=sa;password=sa Password;D ATABASE=TC ;WSID=vibe ;SERVER=vi be;
sa works, the other does not.
provider=msdasql;driver={S
or
provider=msdasql;driver={S
sa works, the other does not.
check out the permissions of that user then. they might have write, but not delete.
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
FtB
ASKER
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