December 10, 2003 at 3:45 pm
I have developed a T-SQL procedure as follows:
ALTER PROCEDURE dbo.spAddNewAlert
(@PN varchar(32),
@Manufacturer nvarchar(100),
@MsgAsOf smalldatetime)
AS INSERT INTO dbo.tblAlerts
(AlertPn, Manufacturer, MsgAsOf)
VALUES (@PN, @Manufacturer, @MsgAsOf)
In my VBA Code I use a connection object to execute the above T-SQL. It creates a string as follows:
EXEC spAddNewAlert @PN ='1N485B', @Manufacturer ='Microsemi Corp', @MsgAsOf='11/02/2003'
When the code runs I get NO T-SQL errors but the row is not added. When I copy the above string to the query analyzer it adds the row. I log into both the database and the query analyzer using the same login. Why, in one case does it not work, and in another it works. I've already checked the permissions for the table and T-SQL stored procedure and found nothing amiss.
Any help would be appreciated.
December 10, 2003 at 4:08 pm
Possibly implicit transactions are set on under QA but your VBA app needs either a BEGIN TRAN... COMMIT, or implicit transactions set on.
The fact that you get no T-SQL errors suggests it is working ok, but then being rolled back.
Cheers,
- Mark
Cheers,
- Mark
December 10, 2003 at 4:38 pm
A couple things I failed to mention earlier. First, I use an ADO object for the connection and stop the processing to use the debugger. When I check for Cnn.Errors.Count I get a 0 even though the table isn't updated. Second, is that this T-SQL procedure was recently working fine but not now. Third, other T-SQL procedures work fine.
I've added a .BeginTrans and .CommitTrans in the VB but it also is unsuccessful.
I'm still confused about why the exact SQL works in query analyzer and not in the VB code.
December 10, 2003 at 11:48 pm
I have seen this before when using VB or VBA to SQL server. My experience of this problem has been in the way the variables have been declared in the VB code for instance if you are passing an integer parameter it must be declared as Long in VB and declared as Int in the stored procedure. If you are passing a date parameter in VB it must be declared as Date in VB and declared as Datetime in SQL server. Do not try and declare your variable in VB as a Variant, it will not work. Unfortunately when your parameters are passing from VBA to SQL Server, and the data type is not passed properly, the parameter is ignored and an error is not raised on the server.
December 11, 2003 at 4:38 am
The easiest way to pinpoint the problem is to run Profiler and capture the exact command being passed to SQL Server. Can you post the command being executed?
--
georged
--
George
December 11, 2003 at 8:25 am
Why not hit tblAlerts directly? There is no performance gain through optimization of your SP because you always append. But even if you needed to modify, SQL2000 would try to optimize these ad-hocs for you and likely as successfully as it would do a stored proc. Just do an explicit Insert from your code.
But if you still need to use a stored proc, try non-parametrized approach by using:
rs.open "spAddNewAlert '" & MyPN & "','" & MyManufacturer & "','" & MyDate & "'"
This usually takes care of impilcit datatype conversion problems Traveller mentioned earlier. Some article said it's also a bit faster because it doesn't use the 2PC protocol while assigning values to parameters
Vlad, MCSE+MCDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply