ADO

Hi
I have a VB Application that Uses ADO to connect to a SQL Server Database. I have a delete trigger on a table and I have a series of SQL Statements from VB that update and delete and insert to various other tables in the database. But on a Particular table i have a delete trigger. WHen the delete statement is executed from VB the delete statement fails
If i execute the same statement form ISQL or query anlayzer it works fine. In the trigger a stored procedure is beinng called and i execute a dynamic execute statement. I use temporary tables in the trigger too
I would appreciate if anyone could help me with this problem
Thanks in advance fro your help
Bye
Jo> WHen the delete statement is executed from VB the delete statement fails.
What error are you getting? Is the security context the same?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jothivel Sadasivam" <JSADASIV@.libus.org> wrote in message
news:626AC6B9-4E03-4092-AB81-D2C7A669CA4A@.microsoft.com...
> Hi,
> I have a VB Application that Uses ADO to connect to a SQL Server Database.
I have a delete trigger on a table and I have a series of SQL Statements
from VB that update and delete and insert to various other tables in the
database. But on a Particular table i have a delete trigger. WHen the delete
statement is executed from VB the delete statement fails.
> If i execute the same statement form ISQL or query anlayzer it works fine.
In the trigger a stored procedure is beinng called and i execute a dynamic
execute statement. I use temporary tables in the trigger too.
> I would appreciate if anyone could help me with this problem.
> Thanks in advance fro your help.
> Bye,
> Joe
>|||Hi Dan
Thanks for your reply. The security context is the same. The strange thing is it doesnt return me an error message. But the No of ROws Affected returns me a value of -1
I would appreciate if you have any inputs on this
Thanks
Joth|||Not sure what might be going on. Can you post a repro script and code
snippet?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jothivel" <anonymous@.discussions.microsoft.com> wrote in message
news:4B995CB6-BEEC-4EA7-B992-DC21D2DC76A6@.microsoft.com...
> Hi Dan,
> Thanks for your reply. The security context is the same. The strange thing
is it doesnt return me an error message. But the No of ROws Affected returns
me a value of -1.
> I would appreciate if you have any inputs on this.
> Thanks,
> Jothi
>|||Hi Dan
Thanks for your interest in this case
Here is the code for the triiger
if exists(select id from sysobjects where type = 'TR' and name = 'VAM_Vehicle_Audit_Del_Trig'
DROP TRIGGER VAM_Vehicle_Audit_Del_Tri
g
CREATE TRIGGER VAM_Vehicle_Audit_Del_Trig ON VAM_Vehicle_Information_Table
FOR DELET
A
/* Write deleted records to Vehicle Information Audit Trail Table *
DECLARE @.intRowCount in
DECLARE @.strUserid varchar(30
declare @.Tablename varchar(40
declare @.@.CONCATENATE varchar(255
declare @.strsql varchar(255
declare @.strsql1 varchar(255
declare @.strsql2 varchar(255
declare @.strsql3 varchar(255
declare @.strsql4 varchar(255
declare @.strsql5 varchar(255
declare @.strsql6 varchar(255
declare @.strsql7 varchar(255
SELECT @.intRowCount = @.@.rowcoun
SELECT @.strUserid = HOST_NAME(
IF @.intRowCount = retur
/* Don't write audit trail if DBA is updating records *
IF SUBSTRING(SUSER_NAME(),1,6) = 'COHARA'
retur
select @.TableName = 'VAM_Vehicle_Information_Table
exec GET_PRIMARY_KEY_COLUMNS @.TableName, @.@.CONCATENATE OUTPU
select @.TableName = 'VAM_Vehicle_Information_Table
exec GET_PRIMARY_KEY_COLUMNS @.TableName, @.@.CONCATENATE OUTPU
select @.@.CONCATENAT
select * from delete
INTO ##VAM_Audit_Delete
select @.strsql = 'INSERT INTO VAM_Generic_Audit_Delete_Table (VGADT_Primary_Key_Values, VGADT_Record_Change_Text, VGADT_Table_Name, VGADT_Vehicle_Number)
select @.strsql1 = 'select ' + @.@.CONCATENATE + ','
select @.strsql2 = 'convert(varchar,VVIT_AMV_Status_Code) +VVIT_Asset_Tag_Number+convert(varchar,VVIT_Assigned_To_Code)+ convert(varchar,VVIT_Condition_Code)+
select @.strsql3 = 'rtrim(convert(varchar,VVIT_Disposal_Comments))+ convert(varchar,VVIT_Disposal_Market_Value)+convert(varchar,VVIT_Disposal_Method_Code)+ convert(varchar,VVIT_Disposal_Sale_Price)+
select @.strsql4 = 'convert(varchar,VVIT_Fleet_Type_Code)+ VVIT_Location_Code_GEAC+ convert(varchar,VVIT_Model_Year)+ convert(varchar,VVIT_Model_Type_Code)+rtrim(convert(varchar,VVIT_Replacement_Comments))+ convert(varchar,VVIT_Replacement_Cost)+
select @.strsql5 = 'convert(varchar,VVIT_Replacement_Date)+ convert(varchar,VVIT_Replacement_Date_Adjust)+ convert(varchar,VVIT_Replacement_Funding_Year)+ convert(varchar,VVIT_Use_Code)+convert(varchar,VVIT_Useful_Life_in_Miles)+ convert(varchar,VVIT_Useful_Life_in_Years)+
select @.strsql6= 'rtrim(convert(varchar,VVIT_Vehicle_Comments))+ rtrim(convert(varchar,VVIT_Vehicle_Cost_Comments))+ VVIT_Vehicle_Description+ VVIT_Vehicle_Id_Number+ VVIT_Vehicle_Licence_Plate+ convert(varchar,VVIT_Owner_Code),
select @.strsql7 = '"' + @.TableName + '", VVIT_Vehicle_Number from ##VAM_Audit_Deleted
select @.strsq
select @.strsql
select @.strsql
select @.strsql
select @.strsql
select @.strsql
select @.strsql
select @.strsql
exec (@.strsql + @.strsql1 + @.strsql2 + @.strsql3 + @.strsql4 + @.strsql5 + @.strsql6 + @.strsql7
g
The Trigger seems to be executing to the point where the the data is inserted into the temporary table from the deleted table. The Dynamic SQL seems not to execute from VB but executes from ISQL
Hope this helps you
Let me know if you need anything else
Thanks
Jothivel|||The trigger script you posted is syntactically incorrect. However I suspect
the problem may be caused by the trigger returning resultsets and this is
confusing ADO. Assuming the extraneous SELECT statements are used for only
for debugging, try removing these to see if that corrects the issue.
If you continue to have problems, please provide a repro script (including
table DDL) that we can run from Query Analyzer.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jothivel" <anonymous@.discussions.microsoft.com> wrote in message
news:509E9097-B0BE-492B-AFEF-1D4DECC35257@.microsoft.com...
> Hi Dan,
> Thanks for your interest in this case.
> Here is the code for the triiger:
> if exists(select id from sysobjects where type = 'TR' and name ='VAM_Vehicle_Audit_Del_Trig')
> DROP TRIGGER VAM_Vehicle_Audit_Del_Trig
> go
> CREATE TRIGGER VAM_Vehicle_Audit_Del_Trig ON VAM_Vehicle_Information_Table
> FOR DELETE
> AS
> /* Write deleted records to Vehicle Information Audit Trail Table */
> DECLARE @.intRowCount int
> DECLARE @.strUserid varchar(30)
> declare @.Tablename varchar(40)
> declare @.@.CONCATENATE varchar(255)
> declare @.strsql varchar(255)
> declare @.strsql1 varchar(255)
> declare @.strsql2 varchar(255)
> declare @.strsql3 varchar(255)
> declare @.strsql4 varchar(255)
> declare @.strsql5 varchar(255)
> declare @.strsql6 varchar(255)
> declare @.strsql7 varchar(255)
> SELECT @.intRowCount = @.@.rowcount
> SELECT @.strUserid = HOST_NAME()
> IF @.intRowCount = 0
> return
> /* Don't write audit trail if DBA is updating records */
> IF SUBSTRING(SUSER_NAME(),1,6) = 'COHARA'
> return
> select @.TableName = 'VAM_Vehicle_Information_Table'
> exec GET_PRIMARY_KEY_COLUMNS @.TableName, @.@.CONCATENATE OUTPUT
> select @.TableName = 'VAM_Vehicle_Information_Table'
> exec GET_PRIMARY_KEY_COLUMNS @.TableName, @.@.CONCATENATE OUTPUT
> select @.@.CONCATENATE
> select * from deleted
> INTO ##VAM_Audit_Deleted
> select @.strsql = 'INSERT INTO VAM_Generic_Audit_Delete_Table
(VGADT_Primary_Key_Values, VGADT_Record_Change_Text, VGADT_Table_Name,
VGADT_Vehicle_Number)'
> select @.strsql1 = 'select ' + @.@.CONCATENATE + ','
> select @.strsql2 = 'convert(varchar,VVIT_AMV_Status_Code)
+VVIT_Asset_Tag_Number+convert(varchar,VVIT_Assigned_To_Code)+
convert(varchar,VVIT_Condition_Code)+'
> select @.strsql3 = 'rtrim(convert(varchar,VVIT_Disposal_Comments))+
convert(varchar,VVIT_Disposal_Market_Value)+convert(varchar,VVIT_Disposal_Me
thod_Code)+ convert(varchar,VVIT_Disposal_Sale_Price)+'
> select @.strsql4 = 'convert(varchar,VVIT_Fleet_Type_Code)+
VVIT_Location_Code_GEAC+ convert(varchar,VVIT_Model_Year)+
convert(varchar,VVIT_Model_Type_Code)+rtrim(convert(varchar,VVIT_Replacement
_Comments))+ convert(varchar,VVIT_Replacement_Cost)+ '
> select @.strsql5 = 'convert(varchar,VVIT_Replacement_Date)+
convert(varchar,VVIT_Replacement_Date_Adjust)+
convert(varchar,VVIT_Replacement_Funding_Year)+
convert(varchar,VVIT_Use_Code)+convert(varchar,VVIT_Useful_Life_in_Miles)+
convert(varchar,VVIT_Useful_Life_in_Years)+'
> select @.strsql6= 'rtrim(convert(varchar,VVIT_Vehicle_Comments))+
rtrim(convert(varchar,VVIT_Vehicle_Cost_Comments))+
VVIT_Vehicle_Description+ VVIT_Vehicle_Id_Number+
VVIT_Vehicle_Licence_Plate+ convert(varchar,VVIT_Owner_Code),'
> select @.strsql7 = '"' + @.TableName + '", VVIT_Vehicle_Number from
##VAM_Audit_Deleted'
> select @.strsql
> select @.strsql1
> select @.strsql2
> select @.strsql3
> select @.strsql4
> select @.strsql5
> select @.strsql6
> select @.strsql7
> exec (@.strsql + @.strsql1 + @.strsql2 + @.strsql3 + @.strsql4 + @.strsql5 +
@.strsql6 + @.strsql7)
> go
> The Trigger seems to be executing to the point where the the data is
inserted into the temporary table from the deleted table. The Dynamic SQL
seems not to execute from VB but executes from ISQL.
> Hope this helps you.
> Let me know if you need anything else.
> Thanks,
> Jothivel