Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / SQL Server / ADP / May 2008

Tip: Looking for answers? Try searching our database.

ADP and dynamic sql

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
phil - 04 May 2008 18:54 GMT
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.
Reply to this Message
Sylvain Lafontaine - 04 May 2008 20:04 GMT
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.

Signature

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.
[quoted text clipped - 27 lines]
> data.
> The procedure executes properly from within SSMS.
Reply to this Message
phil - 04 May 2008 22:22 GMT
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
[quoted text clipped - 42 lines]
> > data.
> > The procedure executes properly from within SSMS.
Reply to this Message
Sylvain Lafontaine - 04 May 2008 23:25 GMT
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.

Signature

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

> Sylvain
>
[quoted text clipped - 73 lines]
>> > data.
>> > The procedure executes properly from within SSMS.
Reply to this Message
phil - 06 May 2008 12:04 GMT
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
[quoted text clipped - 87 lines]
> >> > data.
> >> > The procedure executes properly from within SSMS.
Reply to this Message
Sylvain Lafontaine - 06 May 2008 16:04 GMT
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 ...

Signature

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
[quoted text clipped - 121 lines]
>> >> > data.
>> >> > The procedure executes properly from within SSMS.
Reply to this Message
phil - 06 May 2008 17:34 GMT
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
[quoted text clipped - 131 lines]
> >> >> > data.
> >> >> > The procedure executes properly from within SSMS.
Reply to this Message
Sylvain Lafontaine - 06 May 2008 17:38 GMT
Which version of Access are you using?

Signature

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. :-{
>
[quoted text clipped - 155 lines]
>> >> >> > data.
>> >> >> > The procedure executes properly from within SSMS.
Reply to this Message
Sylvain Lafontaine - 06 May 2008 17:41 GMT
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?

Signature

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. :-{
>
[quoted text clipped - 155 lines]
>> >> >> > data.
>> >> >> > The procedure executes properly from within SSMS.
Reply to this Message
phil - 13 May 2008 08:19 GMT
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
[quoted text clipped - 162 lines]
> >> >> >> > data.
> >> >> >> > The procedure executes properly from within SSMS.
Reply to this Message

 Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.