Help Please interfacing to SQL

  • Thread starter Thread starter Aceware
  • Start date Start date
A

Aceware

I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value from this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it returns
a single record with a single field that is the return value from the stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
 
Did you try set the Return Records to Yes? One of the last lines of the SP is
RETURN @RETVAL
This should return a value from the SP.
--
Duane Hookom
Microsoft Access MVP


Aceware said:
I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value from this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it returns
a single record with a single field that is the return value from the stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
 
Thanks Duane - is it meant to be as easy as that ?

I thought that my calling query in the front end would have to have some
syntax which was a
mixture of "Select ...."
and the existing "exec QUEUE_INSERT 1000795, 76850452, 1, 'fred'"

I did change the setting to returns records but now I just get the message:

"Pass through query with returnsrecords property set to true did not return
any records"

(the stored procedure is definitely doing it's bit and writing records in to
its queue table.
I guess if I am really stuck I could write my own version of this stored
procedure and use it - but not quite sure what I need to do)

Many thanks
Tony Epton


Duane Hookom said:
Did you try set the Return Records to Yes? One of the last lines of the SP is
RETURN @RETVAL
This should return a value from the SP.
--
Duane Hookom
Microsoft Access MVP


Aceware said:
I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value from this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it returns
a single record with a single field that is the return value from the stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
Click to expand...
 
I'm not that sharp with SPs but it looks like @retval might not always get a
value.

--
Duane Hookom
Microsoft Access MVP


Aceware said:
Thanks Duane - is it meant to be as easy as that ?

I thought that my calling query in the front end would have to have some
syntax which was a
mixture of "Select ...."
and the existing "exec QUEUE_INSERT 1000795, 76850452, 1, 'fred'"

I did change the setting to returns records but now I just get the message:

"Pass through query with returnsrecords property set to true did not return
any records"

(the stored procedure is definitely doing it's bit and writing records in to
its queue table.
I guess if I am really stuck I could write my own version of this stored
procedure and use it - but not quite sure what I need to do)

Many thanks
Tony Epton


Duane Hookom said:
Did you try set the Return Records to Yes? One of the last lines of the SP is
RETURN @RETVAL
This should return a value from the SP.
--
Duane Hookom
Microsoft Access MVP


Aceware said:
I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value from this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it returns
a single record with a single field that is the return value from the stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane

Even if I set a value at the very top of the sp it makes no difference.

Many thanks
Tony Epton

Duane Hookom said:
I'm not that sharp with SPs but it looks like @retval might not always get a
value.

--
Duane Hookom
Microsoft Access MVP


Aceware said:
Thanks Duane - is it meant to be as easy as that ?

I thought that my calling query in the front end would have to have some
syntax which was a
mixture of "Select ...."
and the existing "exec QUEUE_INSERT 1000795, 76850452, 1, 'fred'"

I did change the setting to returns records but now I just get the message:

"Pass through query with returnsrecords property set to true did not return
any records"

(the stored procedure is definitely doing it's bit and writing records in to
its queue table.
I guess if I am really stuck I could write my own version of this stored
procedure and use it - but not quite sure what I need to do)

Many thanks
Tony Epton


Duane Hookom said:
Did you try set the Return Records to Yes? One of the last lines of
Click to expand...
the SP
is
RETURN @RETVAL
This should return a value from the SP.
--
Duane Hookom
Microsoft Access MVP


:

I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value
Click to expand...
from
this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it returns
a single record with a single field that is the return value from
Click to expand...
the
stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
Click to expand...
Click to expand...
 
Have you tried running the SP in the SS Management Console?

--
Duane Hookom
Microsoft Access MVP


Aceware said:
Thanks Duane

Even if I set a value at the very top of the sp it makes no difference.

Many thanks
Tony Epton

Duane Hookom said:
I'm not that sharp with SPs but it looks like @retval might not always get a
value.

--
Duane Hookom
Microsoft Access MVP


Aceware said:
Thanks Duane - is it meant to be as easy as that ?

I thought that my calling query in the front end would have to have some
syntax which was a
mixture of "Select ...."
and the existing "exec QUEUE_INSERT 1000795, 76850452, 1, 'fred'"

I did change the setting to returns records but now I just get the message:

"Pass through query with returnsrecords property set to true did not return
any records"

(the stored procedure is definitely doing it's bit and writing records in to
its queue table.
I guess if I am really stuck I could write my own version of this stored
procedure and use it - but not quite sure what I need to do)

Many thanks
Tony Epton


Did you try set the Return Records to Yes? One of the last lines of the SP
is
RETURN @RETVAL
This should return a value from the SP.
--
Duane Hookom
Microsoft Access MVP


:

I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing
queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks
to(written
by someone else)

What I would like to do is get back in to Access the return value from
this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it
returns
a single record with a single field that is the return value from the
stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(),
NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(),
NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
Click to expand...
Click to expand...
 
There may be another way but what comes to mind is to change the last line
of the sp from
RETURN @RETVAL

SELECT @RETVAL AS RETVAL

As you mentioned, set the pass-through query to return records and just run
the query.
It will return one field (RETVAL) and one record.

--

AG
Email: npATadhdataDOTcom


Aceware said:
I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value from
this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it
returns
a single record with a single field that is the return value from the
stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
 
Thanks Duane

Yes - it gives a single record with a return value

Tony

Duane Hookom said:
Have you tried running the SP in the SS Management Console?

--
Duane Hookom
Microsoft Access MVP


Aceware said:
Thanks Duane

Even if I set a value at the very top of the sp it makes no difference.

Many thanks
Tony Epton

Duane Hookom said:
I'm not that sharp with SPs but it looks like @retval might not always
Click to expand...
get
a
value.

--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane - is it meant to be as easy as that ?

I thought that my calling query in the front end would have to have some
syntax which was a
mixture of "Select ...."
and the existing "exec QUEUE_INSERT 1000795, 76850452, 1, 'fred'"

I did change the setting to returns records but now I just get the message:

"Pass through query with returnsrecords property set to true did not return
any records"

(the stored procedure is definitely doing it's bit and writing
Click to expand...
records
in to
its queue table.
I guess if I am really stuck I could write my own version of this stored
procedure and use it - but not quite sure what I need to do)

Many thanks
Tony Epton


Did you try set the Return Records to Yes? One of the last lines
Click to expand...
of
the SP
is
RETURN @RETVAL
This should return a value from the SP.
--
Duane Hookom
Microsoft Access MVP


:

I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing
queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks
to(written
by someone else)

What I would like to do is get back in to Access the return
Click to expand...
value
from
this
stored procedure.

I know I need to change the query sp_Subscriptions to return
Click to expand...
records
but
could anyone please tell how I need to change its syntax so that it
returns
a single record with a single field that is the return value
Click to expand...
from
the
stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to
Click to expand...
spend a
day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(),
NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(),
NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
Click to expand...
Click to expand...
 
Yes - now something is coming through !
Many thanks

Tony

AG said:
There may be another way but what comes to mind is to change the last line
of the sp from
RETURN @RETVAL

SELECT @RETVAL AS RETVAL

As you mentioned, set the pass-through query to return records and just run
the query.
It will return one field (RETVAL) and one record.

--

AG
Email: npATadhdataDOTcom


Aceware said:
I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value from
this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it
returns
a single record with a single field that is the return value from the
stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END
Click to expand...
Click to expand...
 
Back
Top