ADP and dynamic sql

[phil]

The following dynamic sql, compiled as a stored procedure, returns the
correct number of rows but only the columns from the first SET statement.
It's as if it executes the query which is the concatenation of the first and
second SET statement without the SELECT in the middle. Anyone know why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias, u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl ORDER BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN TaskDescriptionTbl AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey = u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE (c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)', @CompanyName


The middle SELECT statement is building a list of columns from which to pull
data.
The procedure executes properly from within SSMS.

 

[Sylvain Lafontaine]

My first thought would be the kind of subtle bug that can be introduced by
the use of the "sp_" prefix for the name of your SP. It might also be a
permission issue on the table/view LangTbl. If not, than store and show
somewhere the result of @dynsql after the Select statement and before the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP in your
ADP project.

Finally - and this is only a matter of personal taste - I would use added
rows to store language specific values instead of using alias columns. The
relationships will be a little more complicated (?) but at least, you won't
have a continuously moving target trying to match your language specific
columns with your frontend.

 

[phil]

Sylvain

Thanks for the suggestions. I've already tried capturing the value of
@dynsql and printing it to the screen (inserting PRINT @dynsql and commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be linked to
Multiple Active Results Sets - and that it was just exexcuting the first
statement - but that wouldn't explain why it is using the joins in the second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View, save it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a legacy
that I'm stuck with right now.

My first thought would be the kind of subtle bug that can be introduced by
the use of the "sp_" prefix for the name of your SP. It might also be a
permission issue on the table/view LangTbl. If not, than store and show
somewhere the result of @dynsql after the Select statement and before the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP in your
ADP project.

Finally - and this is only a matter of personal taste - I would use added
rows to store language specific values instead of using alias columns. The
relationships will be a little more complicated (?) but at least, you won't
have a continuously moving target trying to match your language specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

The following dynamic sql, compiled as a stored procedure, returns the
correct number of rows but only the columns from the first SET statement.
It's as if it executes the query which is the concatenation of the first
and
second SET statement without the SELECT in the middle. Anyone know why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias, u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl ORDER BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)', @CompanyName


The middle SELECT statement is building a list of columns from which to
pull
data.
The procedure executes properly from within SSMS.

 

[Sylvain Lafontaine]

First, you should try removing the sp_ prefix. Second, in order to be sure
that a valid SQL is constructed, you should keep the EXEC statement but
store the value of @dynsql in a table somewhere. This way, you will be sure
that it's constructing the valid SQL under the exact same conditions that
it's used in the ADP application.

Also, I don't see any MARS or even multiple resultsets here. However,
adding the SET NOCOUNT ON instruction at the beginning of the SP shouldn't
hurt anyone.

Finally, you don't tell us how you are using or calling this SP in the ADP
project; so maybe it's simply a problem with the refreshment of the
meta-information on the client side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sylvain

Thanks for the suggestions. I've already tried capturing the value of
@dynsql and printing it to the screen (inserting PRINT @dynsql and
commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be linked to
Multiple Active Results Sets - and that it was just exexcuting the first
statement - but that wouldn't explain why it is using the joins in the
second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View, save it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that
Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a legacy
that I'm stuck with right now.

My first thought would be the kind of subtle bug that can be introduced
by
the use of the "sp_" prefix for the name of your SP. It might also be a
permission issue on the table/view LangTbl. If not, than store and show
somewhere the result of @dynsql after the Select statement and before the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP in your
ADP project.

Finally - and this is only a matter of personal taste - I would use added
rows to store language specific values instead of using alias columns.
The
relationships will be a little more complicated (?) but at least, you
won't
have a continuously moving target trying to match your language specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

The following dynamic sql, compiled as a stored procedure, returns the
correct number of rows but only the columns from the first SET
statement.
It's as if it executes the query which is the concatenation of the
first
and
second SET statement without the SELECT in the middle. Anyone know why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias,
u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl ORDER BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN
TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)', @CompanyName


The middle SELECT statement is building a list of columns from which to
pull
data.
The procedure executes properly from within SSMS.

 

[phil]

Good ideas. Valid sql is generated in the table but same incomplete results
returned with procedure renamed.

I'm just right-clicking the procedure in Access' navigation pane, selecting
Open and supplying the argument.

If I make it the data source for a form, only the first 4 columns are listed
in the field list.

Wierd.

First, you should try removing the sp_ prefix. Second, in order to be sure
that a valid SQL is constructed, you should keep the EXEC statement but
store the value of @dynsql in a table somewhere. This way, you will be sure
that it's constructing the valid SQL under the exact same conditions that
it's used in the ADP application.

Also, I don't see any MARS or even multiple resultsets here. However,
adding the SET NOCOUNT ON instruction at the beginning of the SP shouldn't
hurt anyone.

Finally, you don't tell us how you are using or calling this SP in the ADP
project; so maybe it's simply a problem with the refreshment of the
meta-information on the client side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sylvain

Thanks for the suggestions. I've already tried capturing the value of
@dynsql and printing it to the screen (inserting PRINT @dynsql and
commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be linked to
Multiple Active Results Sets - and that it was just exexcuting the first
statement - but that wouldn't explain why it is using the joins in the
second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View, save it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that
Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a legacy
that I'm stuck with right now.

My first thought would be the kind of subtle bug that can be introduced
by
the use of the "sp_" prefix for the name of your SP. It might also be a
permission issue on the table/view LangTbl. If not, than store and show
somewhere the result of @dynsql after the Select statement and before the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP in your
ADP project.

Finally - and this is only a matter of personal taste - I would use added
rows to store language specific values instead of using alias columns.
The
relationships will be a little more complicated (?) but at least, you
won't
have a continuously moving target trying to match your language specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


The following dynamic sql, compiled as a stored procedure, returns the
correct number of rows but only the columns from the first SET
statement.
It's as if it executes the query which is the concatenation of the
first
and
second SET statement without the SELECT in the middle. Anyone know why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias,
u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl ORDER BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN
TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)', @CompanyName


The middle SELECT statement is building a list of columns from which to
pull
data.
The procedure executes properly from within SSMS.

 

[Sylvain Lafontaine]

If you are making it the data source of a form, I would say this is probably
a question of refreshing the metadata each time. Try building this string
property dynamically each time by using the EXEC t-sql command to call your
stored procedure (SP); so instead of having the RecordSource set to the name
of your SP and using the InputParameters property to pass the parameters,
build a sql string dynamically with all the parameters and assign it to the
RecordSource (no need to make a requery after that):

Me.RecordSource = "EXEC mySP " & Parameter1 ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Good ideas. Valid sql is generated in the table but same incomplete
results
returned with procedure renamed.

I'm just right-clicking the procedure in Access' navigation pane,
selecting
Open and supplying the argument.

If I make it the data source for a form, only the first 4 columns are
listed
in the field list.

Wierd.

First, you should try removing the sp_ prefix. Second, in order to be
sure
that a valid SQL is constructed, you should keep the EXEC statement but
store the value of @dynsql in a table somewhere. This way, you will be
sure
that it's constructing the valid SQL under the exact same conditions that
it's used in the ADP application.

Also, I don't see any MARS or even multiple resultsets here. However,
adding the SET NOCOUNT ON instruction at the beginning of the SP
shouldn't
hurt anyone.

Finally, you don't tell us how you are using or calling this SP in the
ADP
project; so maybe it's simply a problem with the refreshment of the
meta-information on the client side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sylvain

Thanks for the suggestions. I've already tried capturing the value of
@dynsql and printing it to the screen (inserting PRINT @dynsql and
commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be linked
to
Multiple Active Results Sets - and that it was just exexcuting the
first
statement - but that wouldn't explain why it is using the joins in the
second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View, save
it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that
Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a
legacy
that I'm stuck with right now.

:

My first thought would be the kind of subtle bug that can be
introduced
by
the use of the "sp_" prefix for the name of your SP. It might also be
a
permission issue on the table/view LangTbl. If not, than store and
show
somewhere the result of @dynsql after the Select statement and before
the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP in
your
ADP project.

Finally - and this is only a matter of personal taste - I would use
added
rows to store language specific values instead of using alias columns.
The
relationships will be a little more complicated (?) but at least, you
won't
have a continuously moving target trying to match your language
specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


The following dynamic sql, compiled as a stored procedure, returns
the
correct number of rows but only the columns from the first SET
statement.
It's as if it executes the query which is the concatenation of the
first
and
second SET statement without the SELECT in the middle. Anyone know
why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias,
u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl ORDER
BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN
TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)',
@CompanyName


The middle SELECT statement is building a list of columns from which
to
pull
data.
The procedure executes properly from within SSMS.

 

[phil]

Thanks for your continued support. Same result. :-{

If you are making it the data source of a form, I would say this is probably
a question of refreshing the metadata each time. Try building this string
property dynamically each time by using the EXEC t-sql command to call your
stored procedure (SP); so instead of having the RecordSource set to the name
of your SP and using the InputParameters property to pass the parameters,
build a sql string dynamically with all the parameters and assign it to the
RecordSource (no need to make a requery after that):

Me.RecordSource = "EXEC mySP " & Parameter1 ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Good ideas. Valid sql is generated in the table but same incomplete
results
returned with procedure renamed.

I'm just right-clicking the procedure in Access' navigation pane,
selecting
Open and supplying the argument.

If I make it the data source for a form, only the first 4 columns are
listed
in the field list.

Wierd.

First, you should try removing the sp_ prefix. Second, in order to be
sure
that a valid SQL is constructed, you should keep the EXEC statement but
store the value of @dynsql in a table somewhere. This way, you will be
sure
that it's constructing the valid SQL under the exact same conditions that
it's used in the ADP application.

Also, I don't see any MARS or even multiple resultsets here. However,
adding the SET NOCOUNT ON instruction at the beginning of the SP
shouldn't
hurt anyone.

Finally, you don't tell us how you are using or calling this SP in the
ADP
project; so maybe it's simply a problem with the refreshment of the
meta-information on the client side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Sylvain

Thanks for the suggestions. I've already tried capturing the value of
@dynsql and printing it to the screen (inserting PRINT @dynsql and
commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be linked
to
Multiple Active Results Sets - and that it was just exexcuting the
first
statement - but that wouldn't explain why it is using the joins in the
second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View, save
it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that
Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a
legacy
that I'm stuck with right now.

:

My first thought would be the kind of subtle bug that can be
introduced
by
the use of the "sp_" prefix for the name of your SP. It might also be
a
permission issue on the table/view LangTbl. If not, than store and
show
somewhere the result of @dynsql after the Select statement and before
the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP in
your
ADP project.

Finally - and this is only a matter of personal taste - I would use
added
rows to store language specific values instead of using alias columns.
The
relationships will be a little more complicated (?) but at least, you
won't
have a continuously moving target trying to match your language
specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


The following dynamic sql, compiled as a stored procedure, returns
the
correct number of rows but only the columns from the first SET
statement.
It's as if it executes the query which is the concatenation of the
first
and
second SET statement without the SELECT in the middle. Anyone know
why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias,
u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl ORDER
BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN
TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)',
@CompanyName


The middle SELECT statement is building a list of columns from which
to
pull
data.
The procedure executes properly from within SSMS.

 

[Sylvain Lafontaine]

Which version of Access are you using?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks for your continued support. Same result. :-{

If you are making it the data source of a form, I would say this is
probably
a question of refreshing the metadata each time. Try building this string
property dynamically each time by using the EXEC t-sql command to call
your
stored procedure (SP); so instead of having the RecordSource set to the
name
of your SP and using the InputParameters property to pass the parameters,
build a sql string dynamically with all the parameters and assign it to
the
RecordSource (no need to make a requery after that):

Me.RecordSource = "EXEC mySP " & Parameter1 ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Good ideas. Valid sql is generated in the table but same incomplete
results
returned with procedure renamed.

I'm just right-clicking the procedure in Access' navigation pane,
selecting
Open and supplying the argument.

If I make it the data source for a form, only the first 4 columns are
listed
in the field list.

Wierd.

:

First, you should try removing the sp_ prefix. Second, in order to be
sure
that a valid SQL is constructed, you should keep the EXEC statement
but
store the value of @dynsql in a table somewhere. This way, you will
be
sure
that it's constructing the valid SQL under the exact same conditions
that
it's used in the ADP application.

Also, I don't see any MARS or even multiple resultsets here. However,
adding the SET NOCOUNT ON instruction at the beginning of the SP
shouldn't
hurt anyone.

Finally, you don't tell us how you are using or calling this SP in the
ADP
project; so maybe it's simply a problem with the refreshment of the
meta-information on the client side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Sylvain

Thanks for the suggestions. I've already tried capturing the value
of
@dynsql and printing it to the screen (inserting PRINT @dynsql and
commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste
the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be
linked
to
Multiple Active Results Sets - and that it was just exexcuting the
first
statement - but that wouldn't explain why it is using the joins in
the
second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View,
save
it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that
Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a
legacy
that I'm stuck with right now.

:

My first thought would be the kind of subtle bug that can be
introduced
by
the use of the "sp_" prefix for the name of your SP. It might also
be
a
permission issue on the table/view LangTbl. If not, than store and
show
somewhere the result of @dynsql after the Select statement and
before
the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP
in
your
ADP project.

Finally - and this is only a matter of personal taste - I would use
added
rows to store language specific values instead of using alias
columns.
The
relationships will be a little more complicated (?) but at least,
you
won't
have a continuously moving target trying to match your language
specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


The following dynamic sql, compiled as a stored procedure,
returns
the
correct number of rows but only the columns from the first SET
statement.
It's as if it executes the query which is the concatenation of
the
first
and
second SET statement without the SELECT in the middle. Anyone
know
why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias,
u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl
ORDER
BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN
TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)',
@CompanyName


The middle SELECT statement is building a list of columns from
which
to
pull
data.
The procedure executes properly from within SSMS.

 

[Sylvain Lafontaine]

Also, as the result is bound to a form, it's probably better to always have
the same columns returned each time; so you should uses aliases to return
the columns with the same name each time, including returning empty columns
with null values if necessary.

Could you give us a exemple of the builded sql string?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks for your continued support. Same result. :-{

If you are making it the data source of a form, I would say this is
probably
a question of refreshing the metadata each time. Try building this string
property dynamically each time by using the EXEC t-sql command to call
your
stored procedure (SP); so instead of having the RecordSource set to the
name
of your SP and using the InputParameters property to pass the parameters,
build a sql string dynamically with all the parameters and assign it to
the
RecordSource (no need to make a requery after that):

Me.RecordSource = "EXEC mySP " & Parameter1 ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Good ideas. Valid sql is generated in the table but same incomplete
results
returned with procedure renamed.

I'm just right-clicking the procedure in Access' navigation pane,
selecting
Open and supplying the argument.

If I make it the data source for a form, only the first 4 columns are
listed
in the field list.

Wierd.

:

First, you should try removing the sp_ prefix. Second, in order to be
sure
that a valid SQL is constructed, you should keep the EXEC statement
but
store the value of @dynsql in a table somewhere. This way, you will
be
sure
that it's constructing the valid SQL under the exact same conditions
that
it's used in the ADP application.

Also, I don't see any MARS or even multiple resultsets here. However,
adding the SET NOCOUNT ON instruction at the beginning of the SP
shouldn't
hurt anyone.

Finally, you don't tell us how you are using or calling this SP in the
ADP
project; so maybe it's simply a problem with the refreshment of the
meta-information on the client side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Sylvain

Thanks for the suggestions. I've already tried capturing the value
of
@dynsql and printing it to the screen (inserting PRINT @dynsql and
commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste
the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be
linked
to
Multiple Active Results Sets - and that it was just exexcuting the
first
statement - but that wouldn't explain why it is using the joins in
the
second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View,
save
it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that
Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a
legacy
that I'm stuck with right now.

:

My first thought would be the kind of subtle bug that can be
introduced
by
the use of the "sp_" prefix for the name of your SP. It might also
be
a
permission issue on the table/view LangTbl. If not, than store and
show
somewhere the result of @dynsql after the Select statement and
before
the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP
in
your
ADP project.

Finally - and this is only a matter of personal taste - I would use
added
rows to store language specific values instead of using alias
columns.
The
relationships will be a little more complicated (?) but at least,
you
won't
have a continuously moving target trying to match your language
specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


The following dynamic sql, compiled as a stored procedure,
returns
the
correct number of rows but only the columns from the first SET
statement.
It's as if it executes the query which is the concatenation of
the
first
and
second SET statement without the SELECT in the middle. Anyone
know
why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias,
u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl
ORDER
BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN
TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)',
@CompanyName


The middle SELECT statement is building a list of columns from
which
to
pull
data.
The procedure executes properly from within SSMS.

 

[phil]

Sylvain

Using Access 2007.

This is the string that I captured in a table as suggested.

<sql>
SELECT t.TaskDescription, r.ClientTaskAlias, u.UnitType, r.Generic,
r.Afrikaans, r.Albanian, r.Arabic, r.Armenian, r.Azeri, r.Basque,
r.Belarusian, r.Bengali, r.Bosnian, r.Bulgarian, r.Catalan, r.ChineseHK,
r.ChineseS, r.ChineseT, r.Croatian, r.Czech, r.Danish, r.Dutch, r.EnglishAU,
r.EnglishCA, r.EnglishID, r.EnglishIE, r.EnglishMY, r.EnglishNZ, r.EnglishPH,
r.EnglishUK, r.EnglishUS, r.EnglishZA, r.Estonian, r.Farsi, r.Filipino,
r.Finnish, r.Flemish, r.French, r.FrenchBE, r.FrenchCA, r.FrenchCH,
r.Galician, r.German, r.GermanAT, r.GermanCH, r.Greek, r.Gujarati, r.Hebrew,
r.Hindi, r.Hungarian, r.Icelandic, r.Indonesian, r.Irish, r.Italian,
r.Japanese, r.Kannada, r.Kazakh, r.Konkani, r.Korean, r.Kyrgyz, r.Latvian,
r.Lithuanian, r.Macedonian, r.Malayalam, r.Malaysian, r.Maltese, r.Marathi,
r.Mongolian, r.Norwegian, r.NorwegianNynorsk, r.NSotho, r.Polish,
r.PortugueseB, r.PortugueseI, r.Punjabi, r.Romanian, r.Russian, r.Sanskrit,
r.Serbian, r.SerbianM, r.Slovak, r.Slovenian, r.Spanish, r.SpanishAR,
r.SpanishCL, r.SpanishInt, r.SpanishLA, r.SpanishMX, r.Swahili, r.Swedish,
r.Syriac, r.Tagalog, r.Tamil, r.Tatar, r.Telugu, r.Thai, r.Turkish,
r.Ukranian, r.Urdu, r.Uzbek, r.Vietnamese, r.Welsh, r.Xhosa, r.Zulu FROM
ReqsTbl AS r INNER JOIN TaskDescriptionTbl AS t ON r.TaskKey = t.TaskKey
INNER JOIN UnitTbl AS u ON r.UnitKey = u.UnitKey INNER JOIN
ClientTbl AS c ON
r.ClientKey = c.ClientKey WHERE (c.CompanyName = @CompanyName) ORDER BY
t.TaskDescription
</sql>

If I execute this statement in SSMS I get this output (extract as CSV)

<csv
TaskDescription,ClientTaskAlias,UnitType,Generic,Afrikaans,Albanian,Arabic,Armenian,Azeri,Basque,Belarusian,Bengali,Bosnian,Bulgarian,Catalan,ChineseHK,ChineseS,ChineseT,Croatian,Czech,Danish,Dutch,EnglishAU,EnglishCA,EnglishID,EnglishIE,EnglishMY,EnglishNZ,EnglishPH,EnglishUK,EnglishUS,EnglishZA,Estonian,Farsi,Filipino,Finnish,Flemish,French,FrenchBE,FrenchCA,FrenchCH,Galician,German,GermanAT,GermanCH,Greek,Gujarati,Hebrew,Hindi,Hungarian,Icelandic,Indonesian,Irish,Italian,Japanese,Kannada,Kazakh,Konkani,Korean,Kyrgyz,Latvian,Lithuanian,Macedonian,Malayalam,Malaysian,Maltese,Marathi,Mongolian,Norwegian,NorwegianNynorsk,NSotho,Polish,PortugueseB,PortugueseI,Punjabi,Romanian,Russian,Sanskrit,Serbian,SerbianM,Slovak,Slovenian,Spanish,SpanishAR,SpanishCL,SpanishInt,SpanishLA,SpanishMX,Swahili,Swedish,Syriac,Tagalog,Tamil,Tatar,Telugu,Thai,Turkish,Ukranian,Urdu,Uzbek,Vietnamese,Welsh,Xhosa,Zulu
Task 1,Task
1,day,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
</csv>

In Access I get the exquivalent of

<access>
TaskDescription,ClientTaskAlias,UnitType,Generic
Task 1,Task 1,day,0
Task 2,Task 2,day,0
Task 3,Task 3,day,0
</access>

Phil.

Also, as the result is bound to a form, it's probably better to always have
the same columns returned each time; so you should uses aliases to return
the columns with the same name each time, including returning empty columns
with null values if necessary.

Could you give us a exemple of the builded sql string?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks for your continued support. Same result. :-{

If you are making it the data source of a form, I would say this is
probably
a question of refreshing the metadata each time. Try building this string
property dynamically each time by using the EXEC t-sql command to call
your
stored procedure (SP); so instead of having the RecordSource set to the
name
of your SP and using the InputParameters property to pass the parameters,
build a sql string dynamically with all the parameters and assign it to
the
RecordSource (no need to make a requery after that):

Me.RecordSource = "EXEC mySP " & Parameter1 ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Good ideas. Valid sql is generated in the table but same incomplete
results
returned with procedure renamed.

I'm just right-clicking the procedure in Access' navigation pane,
selecting
Open and supplying the argument.

If I make it the data source for a form, only the first 4 columns are
listed
in the field list.

Wierd.

:

First, you should try removing the sp_ prefix. Second, in order to be
sure
that a valid SQL is constructed, you should keep the EXEC statement
but
store the value of @dynsql in a table somewhere. This way, you will
be
sure
that it's constructing the valid SQL under the exact same conditions
that
it's used in the ADP application.

Also, I don't see any MARS or even multiple resultsets here. However,
adding the SET NOCOUNT ON instruction at the beginning of the SP
shouldn't
hurt anyone.

Finally, you don't tell us how you are using or calling this SP in the
ADP
project; so maybe it's simply a problem with the refreshment of the
meta-information on the client side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Sylvain

Thanks for the suggestions. I've already tried capturing the value
of
@dynsql and printing it to the screen (inserting PRINT @dynsql and
commenting
out the EXEC) and it is constructing valid SQL. If I cut and paste
the
statement into SSMS and execute it, it works just fine.

It's purely Access that doesn't like it. I thought it might be
linked
to
Multiple Active Results Sets - and that it was just exexcuting the
first
statement - but that wouldn't explain why it is using the joins in
the
second
SET statement.

Oddly enough, if I cut and paste the constructed SQL into a View,
save
it
and run the view in Access, it works fine.

There's something about the SELECT @dynsql = @dynsql + ... line that
Access
doesn't like.

The whole arrangement of the ReqsTbl and having ColumnAlias' is a
legacy
that I'm stuck with right now.

:

My first thought would be the kind of subtle bug that can be
introduced
by
the use of the "sp_" prefix for the name of your SP. It might also
be
a
permission issue on the table/view LangTbl. If not, than store and
show
somewhere the result of @dynsql after the Select statement and
before
the
EXEC statement in order to put some light on this behavior.

It might be also the way that you are using the result of this SP
in
your
ADP project.

Finally - and this is only a matter of personal taste - I would use
added
rows to store language specific values instead of using alias
columns.
The
relationships will be a little more complicated (?) but at least,
you
won't
have a continuously moving target trying to match your language
specific
columns with your frontend.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


The following dynamic sql, compiled as a stored procedure,
returns
the
correct number of rows but only the columns from the first SET
statement.
It's as if it executes the query which is the concatenation of
the
first
and
second SET statement without the SELECT in the middle. Anyone
know
why?

ALTER PROCEDURE [dbo].[sp_ExportClientPricing]
@CompanyName nvarchar(100)
AS
DECLARE @dynsql nvarchar(4000)
SET @dynsql = N'SELECT t.TaskDescription, r.ClientTaskAlias,
u.UnitType,
r.Generic'

-- build a list of columns from which to pull data
SELECT @dynsql = @dynsql + ', r.' + ColumnAlias FROM LangTbl
ORDER
BY
ColumnAlias

SET @dynsql = @dynsql + ' FROM ReqsTbl AS r INNER JOIN
TaskDescriptionTbl
AS
t ON r.TaskKey = t.TaskKey INNER JOIN UnitTbl AS u ON r.UnitKey =
u.UnitKey
INNER JOIN ClientTbl AS c ON r.ClientKey = c.ClientKey WHERE
(c.CompanyName =
@CompanyName) ORDER BY t.TaskDescription'

EXEC sp_executesql @dynsql, N'@CompanyName nvarchar(100)',
@CompanyName


The middle SELECT statement is building a list of columns from
which
to
pull
data.
The procedure executes properly from within SSMS.