Infopath and SQL stored procedures

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nicolas Normand - 28 Aug 2007 17:04 GMT

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

Reply to this Message

Kalyan G Reddy MVP (GGK Tech) - 29 Aug 2007 08:40 GMT

Hello

Can you send the stored procedure you have written?

Signature

Kalyan G Reddy - INFOPATH MVP 2007
http://www.ggktech.com

> Hi:
>
[quoted text clipped - 15 lines]
>
> Thanks

Reply to this Message

Nicolas Normand - 30 Aug 2007 11:06 GMT

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.

> Hello
>
[quoted text clipped - 19 lines]
> >
> > Thanks

Reply to this Message