Infopath and SQL stored procedures

[Nicolas Normand]

Hi:

I'm working about having a form in Infopath that requests information stored
in a database via some stored procedures in a MS SQL server.

I use the information available on the Microsoft website
(http://support.microsoft.com/default.aspx/kb/827007) explaining how to
create a infopath form requesting data via stored procedure. It works fine as
the stored procedure is really simple and is only a SELECT.

However, when I want to update the stored procedure and do an INSERT just
before the SELECT, I'm not able anymore to call it from the infopath form. I
don't get any error message, but the INSERT has not been executed and I don't
get any result from the SELECT. I have to say I can execute the stored
procedure from the SQL Query Analyzer on the server and it works correctly.

If anybody has the answer, that would be great.

Thanks

 

[Kalyan G Reddy MVP (GGK Tech)]

Hello

Can you send the stored procedure you have written?

 

[Nicolas Normand]

It works with:

CREATE PROCEDURE dbo.GetRecoveryKeyByUser
@LOGONID varchar(20)
AS
SET ROWCOUNT 1
SELECT *
FROM keys
WHERE logonID LIKE '%' + @LOGONID + '%'
ORDER BY creationDate DESC
GO

It does not work with:

CREATE PROCEDURE dbo.GetRecoveryKeyByUser
@LOGONID varchar(20)
AS
CREATE TABLE #Temp (spid smallint, ecid smallint, status varchar(30),
loginame varchar(128), hostname varchar(128), blk varchar(5), dbname
varchar(128), cmd varchar(16))
insert into #Temp
(spid, ecid, status, loginame, hostname, blk, dbname, cmd)
exec sp_who @@SPID
INSERT INTO accesslogs (spid, loginname, hostname, cmdname)
SELECT spid, loginame, hostname, cmd
FROM #Temp
SET ROWCOUNT 1
SELECT *
FROM keys
WHERE logonID LIKE '%' + @LOGONID + '%'
ORDER BY creationDate DESC
GO

The idea is to log every time a user execute the stored procedure.