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 tabl
e 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
> 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,
Jothi
|||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_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_Assign ed_To_Code)+ convert(varchar,VVIT_Condition_Code)+'
select @.strsql3 = 'rtrim(convert(varchar,VVIT_Disposal_Comments))+ convert(varchar,VVIT_Disposal_Market_Value)+conver t(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(conver t(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,VVI T_Useful_Life_in_Miles)+ convert(varchar,VVIT_Usefu
l_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
|||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_Assign ed_To_Code)+
convert(varchar,VVIT_Condition_Code)+'
> select @.strsql3 = 'rtrim(convert(varchar,VVIT_Disposal_Comments))+
convert(varchar,VVIT_Disposal_Market_Value)+conver t(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(conver t(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,VVI T_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