Advanced SQL generations options

Advanced SQL generations options:

generate INSERT, UPDATE, and DELETE statements is all greyed out?

in my sql data source control.?

I have made a brand new instance with sql server management express...have I missed something?

Does the table have a Primary key defined?|||

Sorry about being slow to get back

NO,

|||

Thankyou very much Mr Wellens.....

I just put in a primary key into table and all working fine.

Does that mean you cant insert into any table without it having a primary key.

many many thanks

|||

>>Does that mean you cant insert into any table without it having a primary key.

Well YOU can, but ASP.Net wants a primary key.

If there was no primary key, the ASP.Net code could get very confused about what row needs to be updated or deleted.

ADP/ADE Permissions Issue

I cannot add, update or delete records in a SQL Server database using ADP/ADE but I can within another database on the same server.

I have two databases. One is the test environment and the other is production. Both database reside on the same server but they have unique logins so as not to allow an admin in the test environment to automatically be an admin in production.

I can do everything and anything necessary through Enterprise Manager to both databases. I can do everything necessary to an Access ADP/ADE application in the test environment (add, update, delete) but I cannot do the same in the production database. I can connect and get a valid connection but the tables do not allow me to update or add rows to tables through the ADP/ADE application.

Given that the only difference to the application is which database it connects to I am left with the conclusion that there is something different to the login in the production environment that is not allowing the ADP application appropriate permissions. Can anyone please help or at least direct me to an answer to correct this problem?

Thanks!Well... I suppose this is what happens when you aren't in charge of the data and it is migrated by someone else.

After much digging I found that the tables in question did not have their primary key migrated with them to the production server. If the table did not have a primary key defined then ADP/ADE would not allow a user to add rows.

This was structural, not security. Hopefully someone else who runs across a similar problem in the future will find this information and more quickly correct their issue.

ADO query restricting to SELECT

Hi,
Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
query as input from user. Run it using ADO connection. Display the result in
grid. However I want to allow only SELECT queries. Is there a property on
ADO which allows this? Otherwise I will have to do the parsing myself.
Thanks in advance.
AjeyHi
You will need to restrict the access with permissions at table level. If you
allow them to write their own queries instead of using a controlled query
builder or stored procedures then you will need to do your own parsing.
You should also read up on SQL Injection such as
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
John
"Ajey" wrote:
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||Hi,
By the post I understand that, u have a layee between the database and the
user.
The user need to send a select query and the result is displayed in the Grid.
The users are presently having a flexibility to send the any kind of query.
If they send INSERT, UPDATE or DELETE, your data will be currupted, and you
wante to restrict that.
If my prediction was correct, what I suggest you is, to use a Stored
Procedure for this purpose or open the ADO with read only permissions.
I believe this answered your question. please revert back if u have any issues
thanks and regards
Chandra
"Ajey" wrote:
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||Thanks.
Yes, that's what i want to do.
And also I want to prevent sql-injection. Thanks John.
I want to restrict the query to SELECT. I even if the user has permissions
to modify the table I don't want him to alter it through the query.
How can I open ADO with read only permissons?
Thanks in advance.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> Hi,
> By the post I understand that, u have a layee between the database and the
> user.
> The user need to send a select query and the result is displayed in the
Grid.
> The users are presently having a flexibility to send the any kind of
query.
> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
you
> wante to restrict that.
> If my prediction was correct, what I suggest you is, to use a Stored
> Procedure for this purpose or open the ADO with read only permissions.
> I believe this answered your question. please revert back if u have any
issues
> thanks and regards
> Chandra
>
> "Ajey" wrote:
> > Hi,
> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
the
> > query as input from user. Run it using ADO connection. Display the
result in
> > grid. However I want to allow only SELECT queries. Is there a property
on
> > ADO which allows this? Otherwise I will have to do the parsing myself.
> >
> > Thanks in advance.
> > Ajey
> >
> >
> >
> >|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:OfmIdX8SFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
> How can I open ADO with read only permissons?
1. Set the Mode property of the underlying connection to adModeRead, and
2. Set the recordset.LockType property to adLockReadOnly
-Mark
> Thanks in advance.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
>> Hi,
>> By the post I understand that, u have a layee between the database and
>> the
>> user.
>> The user need to send a select query and the result is displayed in the
> Grid.
>> The users are presently having a flexibility to send the any kind of
> query.
>> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> you
>> wante to restrict that.
>> If my prediction was correct, what I suggest you is, to use a Stored
>> Procedure for this purpose or open the ADO with read only permissions.
>> I believe this answered your question. please revert back if u have any
> issues
>> thanks and regards
>> Chandra
>>
>> "Ajey" wrote:
>> > Hi,
>> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> the
>> > query as input from user. Run it using ADO connection. Display the
> result in
>> > grid. However I want to allow only SELECT queries. Is there a property
> on
>> > ADO which allows this? Otherwise I will have to do the parsing myself.
>> >
>> > Thanks in advance.
>> > Ajey
>> >
>> >
>> >
>> >
>|||You can do this while you are opening the connection itself.
For More info refer to:
http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
"Ajey" wrote:
> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
> How can I open ADO with read only permissons?
> Thanks in advance.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > Hi,
> > By the post I understand that, u have a layee between the database and the
> > user.
> > The user need to send a select query and the result is displayed in the
> Grid.
> >
> > The users are presently having a flexibility to send the any kind of
> query.
> > If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> you
> > wante to restrict that.
> >
> > If my prediction was correct, what I suggest you is, to use a Stored
> > Procedure for this purpose or open the ADO with read only permissions.
> >
> > I believe this answered your question. please revert back if u have any
> issues
> >
> > thanks and regards
> > Chandra
> >
> >
> >
> > "Ajey" wrote:
> >
> > > Hi,
> > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> the
> > > query as input from user. Run it using ADO connection. Display the
> result in
> > > grid. However I want to allow only SELECT queries. Is there a property
> on
> > > ADO which allows this? Otherwise I will have to do the parsing myself.
> > >
> > > Thanks in advance.
> > > Ajey
> > >
> > >
> > >
> > >
>
>|||It's not working:
Following is the sample code:
----
--
g_objConn.Mode = adModeRead
g_objConn.Open strConn, , , 0
Debug.Print g_objConn.Mode
strQuery = "select * from sysobjects select * from sysindexes insert
into testtable values ('xyz')"
Set objRS = New Recordset
'Set objRS = g_objConn.Execute(strQuery)
objRS.Open strQuery, g_objConn, adOpenForwardOnly, adLockReadOnly,
adCmdText
----
--
After the Open on recordset the insert is always successful.
Thanks.
Ajey
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:OWodsc8SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:OfmIdX8SFHA.2560@.TK2MSFTNGP09.phx.gbl...
> > Thanks.
> > Yes, that's what i want to do.
> > And also I want to prevent sql-injection. Thanks John.
> > I want to restrict the query to SELECT. I even if the user has
permissions
> > to modify the table I don't want him to alter it through the query.
> >
> > How can I open ADO with read only permissons?
> 1. Set the Mode property of the underlying connection to adModeRead, and
> 2. Set the recordset.LockType property to adLockReadOnly
>
> -Mark
>
>
> > Thanks in advance.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> >> Hi,
> >> By the post I understand that, u have a layee between the database and
> >> the
> >> user.
> >> The user need to send a select query and the result is displayed in the
> > Grid.
> >>
> >> The users are presently having a flexibility to send the any kind of
> > query.
> >> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> > you
> >> wante to restrict that.
> >>
> >> If my prediction was correct, what I suggest you is, to use a Stored
> >> Procedure for this purpose or open the ADO with read only permissions.
> >>
> >> I believe this answered your question. please revert back if u have any
> > issues
> >>
> >> thanks and regards
> >> Chandra
> >>
> >>
> >>
> >> "Ajey" wrote:
> >>
> >> > Hi,
> >> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> > the
> >> > query as input from user. Run it using ADO connection. Display the
> > result in
> >> > grid. However I want to allow only SELECT queries. Is there a
property
> > on
> >> > ADO which allows this? Otherwise I will have to do the parsing
myself.
> >> >
> >> > Thanks in advance.
> >> > Ajey
> >> >
> >> >
> >> >
> >> >
> >
> >
>|||I am not using ADO.NET but it's a simple ADO application.
Thanks.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> You can do this while you are opening the connection itself.
> For More info refer to:
> http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
>
>
> "Ajey" wrote:
> > Thanks.
> > Yes, that's what i want to do.
> > And also I want to prevent sql-injection. Thanks John.
> > I want to restrict the query to SELECT. I even if the user has
permissions
> > to modify the table I don't want him to alter it through the query.
> >
> > How can I open ADO with read only permissons?
> >
> > Thanks in advance.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > Hi,
> > > By the post I understand that, u have a layee between the database and
the
> > > user.
> > > The user need to send a select query and the result is displayed in
the
> > Grid.
> > >
> > > The users are presently having a flexibility to send the any kind of
> > query.
> > > If they send INSERT, UPDATE or DELETE, your data will be currupted,
and
> > you
> > > wante to restrict that.
> > >
> > > If my prediction was correct, what I suggest you is, to use a Stored
> > > Procedure for this purpose or open the ADO with read only permissions.
> > >
> > > I believe this answered your question. please revert back if u have
any
> > issues
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > Hi,
> > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> > the
> > > > query as input from user. Run it using ADO connection. Display the
> > result in
> > > > grid. However I want to allow only SELECT queries. Is there a
property
> > on
> > > > ADO which allows this? Otherwise I will have to do the parsing
myself.
> > > >
> > > > Thanks in advance.
> > > > Ajey
> > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Ajey
Will this be of any help:
===========
Set conn = New ADODB.Connection
conn.Open "dns=<>"
Dim rs As ADODB.Recordset
' Open the table.
Set rs = New ADODB.Recordset
rs.Open Query, conn, adOpenDynamic, adLockReadOnly
===========
thanks and regards
Chandar
"Ajey" wrote:
> I am not using ADO.NET but it's a simple ADO application.
> Thanks.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > You can do this while you are opening the connection itself.
> >
> > For More info refer to:
> > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> >
> >
> >
> >
> > "Ajey" wrote:
> >
> > > Thanks.
> > > Yes, that's what i want to do.
> > > And also I want to prevent sql-injection. Thanks John.
> > > I want to restrict the query to SELECT. I even if the user has
> permissions
> > > to modify the table I don't want him to alter it through the query.
> > >
> > > How can I open ADO with read only permissons?
> > >
> > > Thanks in advance.
> > > Ajey
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > Hi,
> > > > By the post I understand that, u have a layee between the database and
> the
> > > > user.
> > > > The user need to send a select query and the result is displayed in
> the
> > > Grid.
> > > >
> > > > The users are presently having a flexibility to send the any kind of
> > > query.
> > > > If they send INSERT, UPDATE or DELETE, your data will be currupted,
> and
> > > you
> > > > wante to restrict that.
> > > >
> > > > If my prediction was correct, what I suggest you is, to use a Stored
> > > > Procedure for this purpose or open the ADO with read only permissions.
> > > >
> > > > I believe this answered your question. please revert back if u have
> any
> > > issues
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > >
> > > > "Ajey" wrote:
> > > >
> > > > > Hi,
> > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
> get
> > > the
> > > > > query as input from user. Run it using ADO connection. Display the
> > > result in
> > > > > grid. However I want to allow only SELECT queries. Is there a
> property
> > > on
> > > > > ADO which allows this? Otherwise I will have to do the parsing
> myself.
> > > > >
> > > > > Thanks in advance.
> > > > > Ajey
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||This seems to be doing the trick. I get following error when I try to
execute multiple queries:
sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
select or a single stored procedure. : Microsoft OLE DB Provider for SQL
Server
But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
At least this now blocks the user from executing multiple statement and so i
can check only the first token to be SELECT.
But can I achive same using adOpenForwardOnly cursor type.
Thanks.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> Hi Ajey
> Will this be of any help:
> ===========> Set conn = New ADODB.Connection
> conn.Open "dns=<>"
>
> Dim rs As ADODB.Recordset
> ' Open the table.
> Set rs = New ADODB.Recordset
> rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> ===========> thanks and regards
> Chandar
>
> "Ajey" wrote:
> > I am not using ADO.NET but it's a simple ADO application.
> >
> > Thanks.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > You can do this while you are opening the connection itself.
> > >
> > > For More info refer to:
> > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > >
> > >
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > Thanks.
> > > > Yes, that's what i want to do.
> > > > And also I want to prevent sql-injection. Thanks John.
> > > > I want to restrict the query to SELECT. I even if the user has
> > permissions
> > > > to modify the table I don't want him to alter it through the query.
> > > >
> > > > How can I open ADO with read only permissons?
> > > >
> > > > Thanks in advance.
> > > > Ajey
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > Hi,
> > > > > By the post I understand that, u have a layee between the database
and
> > the
> > > > > user.
> > > > > The user need to send a select query and the result is displayed
in
> > the
> > > > Grid.
> > > > >
> > > > > The users are presently having a flexibility to send the any kind
of
> > > > query.
> > > > > If they send INSERT, UPDATE or DELETE, your data will be
currupted,
> > and
> > > > you
> > > > > wante to restrict that.
> > > > >
> > > > > If my prediction was correct, what I suggest you is, to use a
Stored
> > > > > Procedure for this purpose or open the ADO with read only
permissions.
> > > > >
> > > > > I believe this answered your question. please revert back if u
have
> > any
> > > > issues
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ajey" wrote:
> > > > >
> > > > > > Hi,
> > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO?
I
> > get
> > > > the
> > > > > > query as input from user. Run it using ADO connection. Display
the
> > > > result in
> > > > > > grid. However I want to allow only SELECT queries. Is there a
> > property
> > > > on
> > > > > > ADO which allows this? Otherwise I will have to do the parsing
> > myself.
> > > > > >
> > > > > > Thanks in advance.
> > > > > > Ajey
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Ajey
Good to know that ur problem is getting solved. adLockReadOnly will prevent
the users from using INSERT, DELETE and UPDATE. The cursor type is optional.
You can ignore it and procede further.
thanks and regards
Chandra
"Ajey" wrote:
> This seems to be doing the trick. I get following error when I try to
> execute multiple queries:
> sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
> select or a single stored procedure. : Microsoft OLE DB Provider for SQL
> Server
> But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
> At least this now blocks the user from executing multiple statement and so i
> can check only the first token to be SELECT.
> But can I achive same using adOpenForwardOnly cursor type.
> Thanks.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> > Hi Ajey
> >
> > Will this be of any help:
> >
> > ===========> >
> > Set conn = New ADODB.Connection
> > conn.Open "dns=<>"
> >
> >
> >
> > Dim rs As ADODB.Recordset
> >
> > ' Open the table.
> > Set rs = New ADODB.Recordset
> > rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> >
> > ===========> >
> > thanks and regards
> > Chandar
> >
> >
> > "Ajey" wrote:
> >
> > > I am not using ADO.NET but it's a simple ADO application.
> > >
> > > Thanks.
> > > Ajey
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > > You can do this while you are opening the connection itself.
> > > >
> > > > For More info refer to:
> > > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > > >
> > > >
> > > >
> > > >
> > > > "Ajey" wrote:
> > > >
> > > > > Thanks.
> > > > > Yes, that's what i want to do.
> > > > > And also I want to prevent sql-injection. Thanks John.
> > > > > I want to restrict the query to SELECT. I even if the user has
> > > permissions
> > > > > to modify the table I don't want him to alter it through the query.
> > > > >
> > > > > How can I open ADO with read only permissons?
> > > > >
> > > > > Thanks in advance.
> > > > > Ajey
> > > > >
> > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > > Hi,
> > > > > > By the post I understand that, u have a layee between the database
> and
> > > the
> > > > > > user.
> > > > > > The user need to send a select query and the result is displayed
> in
> > > the
> > > > > Grid.
> > > > > >
> > > > > > The users are presently having a flexibility to send the any kind
> of
> > > > > query.
> > > > > > If they send INSERT, UPDATE or DELETE, your data will be
> currupted,
> > > and
> > > > > you
> > > > > > wante to restrict that.
> > > > > >
> > > > > > If my prediction was correct, what I suggest you is, to use a
> Stored
> > > > > > Procedure for this purpose or open the ADO with read only
> permissions.
> > > > > >
> > > > > > I believe this answered your question. please revert back if u
> have
> > > any
> > > > > issues
> > > > > >
> > > > > > thanks and regards
> > > > > > Chandra
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Ajey" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO?
> I
> > > get
> > > > > the
> > > > > > > query as input from user. Run it using ADO connection. Display
> the
> > > > > result in
> > > > > > > grid. However I want to allow only SELECT queries. Is there a
> > > property
> > > > > on
> > > > > > > ADO which allows this? Otherwise I will have to do the parsing
> > > myself.
> > > > > > >
> > > > > > > Thanks in advance.
> > > > > > > Ajey
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||The error comes only when I specify the cusrosor type as adOpenDynamic and
not when adOpenForwardOnly
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:7F7945AF-AACD-4545-B5F2-1ACE9D3FD1D7@.microsoft.com...
> Hi Ajey
> Good to know that ur problem is getting solved. adLockReadOnly will
prevent
> the users from using INSERT, DELETE and UPDATE. The cursor type is
optional.
> You can ignore it and procede further.
> thanks and regards
> Chandra
>
>
> "Ajey" wrote:
> > This seems to be doing the trick. I get following error when I try to
> > execute multiple queries:
> > sp_cursoropen/sp_cursorprepare: The statement parameter can only be a
single
> > select or a single stored procedure. : Microsoft OLE DB Provider for SQL
> > Server
> >
> > But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
> >
> > At least this now blocks the user from executing multiple statement and
so i
> > can check only the first token to be SELECT.
> > But can I achive same using adOpenForwardOnly cursor type.
> >
> > Thanks.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> > > Hi Ajey
> > >
> > > Will this be of any help:
> > >
> > > ===========> > >
> > > Set conn = New ADODB.Connection
> > > conn.Open "dns=<>"
> > >
> > >
> > >
> > > Dim rs As ADODB.Recordset
> > >
> > > ' Open the table.
> > > Set rs = New ADODB.Recordset
> > > rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> > >
> > > ===========> > >
> > > thanks and regards
> > > Chandar
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > I am not using ADO.NET but it's a simple ADO application.
> > > >
> > > > Thanks.
> > > > Ajey
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > > > You can do this while you are opening the connection itself.
> > > > >
> > > > > For More info refer to:
> > > > >
http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Ajey" wrote:
> > > > >
> > > > > > Thanks.
> > > > > > Yes, that's what i want to do.
> > > > > > And also I want to prevent sql-injection. Thanks John.
> > > > > > I want to restrict the query to SELECT. I even if the user has
> > > > permissions
> > > > > > to modify the table I don't want him to alter it through the
query.
> > > > > >
> > > > > > How can I open ADO with read only permissons?
> > > > > >
> > > > > > Thanks in advance.
> > > > > > Ajey
> > > > > >
> > > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > > > Hi,
> > > > > > > By the post I understand that, u have a layee between the
database
> > and
> > > > the
> > > > > > > user.
> > > > > > > The user need to send a select query and the result is
displayed
> > in
> > > > the
> > > > > > Grid.
> > > > > > >
> > > > > > > The users are presently having a flexibility to send the any
kind
> > of
> > > > > > query.
> > > > > > > If they send INSERT, UPDATE or DELETE, your data will be
> > currupted,
> > > > and
> > > > > > you
> > > > > > > wante to restrict that.
> > > > > > >
> > > > > > > If my prediction was correct, what I suggest you is, to use a
> > Stored
> > > > > > > Procedure for this purpose or open the ADO with read only
> > permissions.
> > > > > > >
> > > > > > > I believe this answered your question. please revert back if u
> > have
> > > > any
> > > > > > issues
> > > > > > >
> > > > > > > thanks and regards
> > > > > > > Chandra
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Ajey" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in
ADO?
> > I
> > > > get
> > > > > > the
> > > > > > > > query as input from user. Run it using ADO connection.
Display
> > the
> > > > > > result in
> > > > > > > > grid. However I want to allow only SELECT queries. Is there
a
> > > > property
> > > > > > on
> > > > > > > > ADO which allows this? Otherwise I will have to do the
parsing
> > > > myself.
> > > > > > > >
> > > > > > > > Thanks in advance.
> > > > > > > > Ajey
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Also, I created a stored procedure sp_my_sp2 which raises an error on
execution.
When I try to run multiple queries as:
sp_my_sp2 select * from sysobjects
I can verify that the stored proc as well as the SELECT query gets executed.
But for the following:
select * from sysobjects sp_my_sp2
only the SELECT query gets executed.
What's the behavior for multiple queries in SQL Server?
Thanks in advance.
Ajey
"Ajey" <ajey5@.hotmail.com> wrote in message
news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result
in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Also, I created a stored procedure sp_my_sp2 which raises an error on
> execution.
> When I try to run multiple queries as:
> sp_my_sp2 select * from sysobjects
> I can verify that the stored proc as well as the SELECT query gets
> executed.
> But for the following:
> select * from sysobjects sp_my_sp2
select * from sysobjects; exec sp_my_sp2
-Mark
> only the SELECT query gets executed.
> What's the behavior for multiple queries in SQL Server?
> Thanks in advance.
> Ajey
>
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
>> the
>> query as input from user. Run it using ADO connection. Display the result
> in
>> grid. However I want to allow only SELECT queries. Is there a property on
>> ADO which allows this? Otherwise I will have to do the parsing myself.
>> Thanks in advance.
>> Ajey
>>
>|||That helps to execute the stored procedure as well
I wanted to make sure that user does not enter any other statement other
than SELECT. I can put restriction on having keywords INSERT, UPDATE,
DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
procedure given as mentioned below. With the execution i verified that in
this case the stored procedure does not get executed. But is this behavior
documented?
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:2j6ce.1$ZN.0@.fed1read07...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> > Also, I created a stored procedure sp_my_sp2 which raises an error on
> > execution.
> > When I try to run multiple queries as:
> > sp_my_sp2 select * from sysobjects
> > I can verify that the stored proc as well as the SELECT query gets
> > executed.
> >
> > But for the following:
> > select * from sysobjects sp_my_sp2
>
> select * from sysobjects; exec sp_my_sp2
>
> -Mark
>
>
> > only the SELECT query gets executed.
> >
> > What's the behavior for multiple queries in SQL Server?
> >
> > Thanks in advance.
> > Ajey
> >
> >
> > "Ajey" <ajey5@.hotmail.com> wrote in message
> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> >> Hi,
> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> >> the
> >> query as input from user. Run it using ADO connection. Display the
result
> > in
> >> grid. However I want to allow only SELECT queries. Is there a property
on
> >> ADO which allows this? Otherwise I will have to do the parsing myself.
> >>
> >> Thanks in advance.
> >> Ajey
> >>
> >>
> >>
> >
> >
>|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:uAiO1EATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> That helps to execute the stored procedure as well
> I wanted to make sure that user does not enter any other statement other
> than SELECT. I can put restriction on having keywords INSERT, UPDATE,
> DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
> procedure given as mentioned below. With the execution i verified that in
> this case the stored procedure does not get executed. But is this behavior
> documented?
Not sure if it's documented, but it has been that way forever (given that
SQL 6.5 marked the beginning of time.)
Just the stored procedure name (with args if any) alone will work for a
single statement, but you must use exec for each statement in a batch.
-Mark
> "Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
> news:2j6ce.1$ZN.0@.fed1read07...
>> "Ajey" <ajey5@.hotmail.com> wrote in message
>> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
>> > Also, I created a stored procedure sp_my_sp2 which raises an error on
>> > execution.
>> > When I try to run multiple queries as:
>> > sp_my_sp2 select * from sysobjects
>> > I can verify that the stored proc as well as the SELECT query gets
>> > executed.
>> >
>> > But for the following:
>> > select * from sysobjects sp_my_sp2
>>
>> select * from sysobjects; exec sp_my_sp2
>>
>> -Mark
>>
>>
>> > only the SELECT query gets executed.
>> >
>> > What's the behavior for multiple queries in SQL Server?
>> >
>> > Thanks in advance.
>> > Ajey
>> >
>> >
>> > "Ajey" <ajey5@.hotmail.com> wrote in message
>> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
>> >> Hi,
>> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
>> >> the
>> >> query as input from user. Run it using ADO connection. Display the
> result
>> > in
>> >> grid. However I want to allow only SELECT queries. Is there a property
> on
>> >> ADO which allows this? Otherwise I will have to do the parsing myself.
>> >>
>> >> Thanks in advance.
>> >> Ajey
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Thank Mark.
I even verified with the query execution plan to make sure that the stored
proc does not get executed. But then why no error is shown for the stored
proc name.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:2g8ce.17$ZN.14@.fed1read07...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:uAiO1EATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> > That helps to execute the stored procedure as well
> > I wanted to make sure that user does not enter any other statement other
> > than SELECT. I can put restriction on having keywords INSERT, UPDATE,
> > DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
> > procedure given as mentioned below. With the execution i verified that
in
> > this case the stored procedure does not get executed. But is this
behavior
> > documented?
> Not sure if it's documented, but it has been that way forever (given that
> SQL 6.5 marked the beginning of time.)
> Just the stored procedure name (with args if any) alone will work for a
> single statement, but you must use exec for each statement in a batch.
> -Mark
>
>
> > "Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
> > news:2j6ce.1$ZN.0@.fed1read07...
> >>
> >> "Ajey" <ajey5@.hotmail.com> wrote in message
> >> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> >> > Also, I created a stored procedure sp_my_sp2 which raises an error on
> >> > execution.
> >> > When I try to run multiple queries as:
> >> > sp_my_sp2 select * from sysobjects
> >> > I can verify that the stored proc as well as the SELECT query gets
> >> > executed.
> >> >
> >> > But for the following:
> >> > select * from sysobjects sp_my_sp2
> >>
> >>
> >> select * from sysobjects; exec sp_my_sp2
> >>
> >>
> >> -Mark
> >>
> >>
> >>
> >>
> >>
> >> > only the SELECT query gets executed.
> >> >
> >> > What's the behavior for multiple queries in SQL Server?
> >> >
> >> > Thanks in advance.
> >> > Ajey
> >> >
> >> >
> >> > "Ajey" <ajey5@.hotmail.com> wrote in message
> >> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> >> >> Hi,
> >> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> >> >> the
> >> >> query as input from user. Run it using ADO connection. Display the
> > result
> >> > in
> >> >> grid. However I want to allow only SELECT queries. Is there a
property
> > on
> >> >> ADO which allows this? Otherwise I will have to do the parsing
myself.
> >> >>
> >> >> Thanks in advance.
> >> >> Ajey
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

ADO connection

In VB6 I use more Forms where users can Add, Edit, Delete Records from more tables.

WHICH IS THE BEST METHOD

1. To open a connection when user start the application and I close the connection when user leave the application.

In Login form (Public cn As ADODB.Connection)

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

OR

2. To Open a connection in each Form and close the connection when Form is UnLoadetThe first case is not possible because

Set cn = New ADODB.Connection (this is in Form Login)
In other form it is necesary to set the connection again.|||The rule is that you only keep the connection open as long as needed - and no longer. What are your concerns ?|||How many clients will use this application ?|||5-8 users|||Will the security credentials be different for each user ? What is the purpose for the application and the multiple forms ? Are you thinking about opening 1 connection object that will be used for multiple recordset/command objects ?|||How I can open a connection when user start the application and kepp open until user leave the application ?

In module:
Public cn As ADODB.Connection

In first form when is load:
SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use "cn" in other forms is not possible (is not open)|||Are you destroying/closing the connection before the 2nd form is loaded ?|||I don`t close the connection, but I close (Unload) the first form.
The connection is closed ?|||Where are you declaring - Public cn As ADODB.Connection ?|||This declaration is made on a Module

Public cn As ADODB.Connection

In the Startup Form (where user input UserName and Password)

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use this connection on other forms I can't because is Closed.
I want to open the connection only one time(when application startup), and if it possible to use the connection in all form.|||Open the connection in a module or class when login in form successfull. Close it when application main form is closed.|||This Looks Like VB code If it is Do not forget to set the DBconnection ( and all all objects associated to it) = nothing

set cn = nothing

I personnally like to use 1 public DB connection instead of multiple connections per app.

Hope this helps.
LJ|||Hello Gurus out there!

I want to know what would be the best method in opening a connection to server:

1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used...What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

I hope you can light up our minds with these concerns.

Thanks,

Bernie|||Bernie - What driver are you using ?

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 databas
e. 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. I
n the trigger a stored procedure is beinng called and i execute a dynamic ex
ecute 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 i
s 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_Vehicl
e_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_Num
ber+convert(varchar,VVIT_Assigned_To_Cod
e)+ convert(varchar,VVIT_Condition_C
ode)+'
select @.strsql3 = 'rtrim(convert(varchar,VVIT_Disposal_Com
ments))+ convert(v
archar,VVIT_Disposal_Market_Value)+conve
rt(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_Cod
e)+rtrim(convert(varchar,VVIT_Replacemen
t_Comments))+ convert(varchar,VVIT_R
eplacement_Cost)+ '
select @.strsql5 = 'convert(varchar,VVIT_Replacement_Date)+
convert(varchar,V
VIT_Replacement_Date_Adjust)+ convert(varchar,VVIT_Replacement_Funding
_Year)
+ convert(varchar,VVIT_Use_Code)+convert(v
archar,VVIT_Useful_Life_in_Miles)+
convert(varchar,VVIT_Usefu
l_Life_in_Years)+'
select @.strsql6= 'rtrim(convert(varchar,VVIT_Vehicle_Comm
ents))+ rtrim(conve
rt(varchar,VVIT_Vehicle_Cost_Comments))+
VVIT_Vehicle_Description+ VVIT_Veh
icle_Id_Number+ VVIT_Vehicle_Licence_Plate+ convert(varchar,VVIT_Owner_Code)
,'
select @.strsql7 = '"' + @.TableName + '", VVIT_Vehicle_Number from ##VAM_Audi
t_Deleted'
select @.strsql
select @.strsql1
select @.strsql2
select @.strsql3
select @.strsql4
select @.strsql5
select @.strsql6
select @.strsql7
exec (@.strsql + @.strsql1 + @.strsql2 + @.strsql3 + @.strsql4 + @.strsql5 + @.strs
ql6 + @.strsql7)
go
The Trigger seems to be executing to the point where the the data is inserte
d 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')red">
> 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,V
VIT_Assigned_To_Code)+
convert(varchar,VVIT_Condition_Code)+'[c
olor=darkred]
> select @.strsql3 = 'rtrim(convert(varchar,VVIT_Disposal_Com
ments))+[/color]
convert(varchar,VVIT_Disposal_Market_Val
ue)+convert(varchar,VVIT_Disposal_Me
thod_Code)+ convert(varchar,VVIT_Disposal_Sale_Price
)+'
> select @.strsql4 = 'convert(varchar,VVIT_Fleet_Type_Code)+[
/color]
VVIT_Location_Code_GEAC+ convert(varchar,VVIT_Model_Year)+
convert(varchar,VVIT_Model_Type_Code)+rt
rim(convert(varchar,VVIT_Replacement
_Comments))+ convert(varchar,VVIT_Replacement_Cost)+ '
> select @.strsql5 = 'convert(varchar,VVIT_Replacement_Date)+

convert(varchar,VVIT_Replacement_Date_Ad
just)+
convert(varchar,VVIT_Replacement_Funding
_Year)+
convert(varchar,VVIT_Use_Code)+convert(v
archar,VVIT_Useful_Life_in_Miles)+
convert(varchar,VVIT_Useful_Life_in_Year
s)+'
> select @.strsql6= 'rtrim(convert(varchar,VVIT_Vehicle_Comm
ents))+
rtrim(convert(varchar,VVIT_Vehicle_Cost_
Comments))+
VVIT_Vehicle_Description+ VVIT_Vehicle_Id_Number+
VVIT_Vehicle_Licence_Plate+ convert(varchar,VVIT_Owner_Code),'[color
=darkred]
> 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

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

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