Link to home
Start Free TrialLog in
Avatar of Joice64
Joice64Flag for Italy

asked on

Query Access (Parametric) to Stored Procedure.

Hi EE, I have a this problem,  needs to convert my parametric query (Access 2007) to Stored Procedure in SQL 2005, is there a nice utility or tools for make this conversion Job? I have attached the sample Qry with parameters and the code (SQL pass - trought used in Access) and also the table used for this sample.
SELECT RMovAttivita.IDCollaboratore, Collaboratori.Nominativo, TMovAttivita.DataAttivita, Clienti.Nominativo, RMovAttivita.OraInizio, RMovAttivita.OraFine, Prestazioni.DescrizionePrestazione
FROM (Clienti INNER JOIN TMovAttivita ON Clienti.IDCliente = TMovAttivita.IDCliente) INNER JOIN ((RMovAttivita INNER JOIN Collaboratori ON RMovAttivita.IDCollaboratore = Collaboratori.IDCollaboratore) INNER JOIN Prestazioni ON RMovAttivita.IDPrestazione = Prestazioni.IDPrestazione) ON TMovAttivita.IDAttivita = RMovAttivita.IDAttivita
WHERE (((RMovAttivita.IDCollaboratore)=[Collaboratore]) AND ((TMovAttivita.DataAttivita) Between [Inizio] And [Fine]))
ORDER BY Collaboratori.Nominativo, TMovAttivita.DataAttivita, RMovAttivita.OraInizio;

Open in new window

QryParametrics.png
Avatar of jmoss111
jmoss111
Flag of United States of America image

That doesn't look like a pass through.

Regards,

Jim
Not really.  

Access already converts the query to Transact SQL.  All you have to do is add a CREATE PROCEDURE <name> AS

BEGIN

<paste statement here>

END

for example


CREATE PROCEDURE test @Collaboratore varchar(255), @[Inizio smalldatetime,  @Fine smalldatetime

AS
BEGIN

SELECT RMovAttivita.IDCollaboratore, Collaboratori.Nominativo, TMovAttivita.DataAttivita, Clienti.Nominativo, RMovAttivita.OraInizio, RMovAttivita.OraFine, Prestazioni.DescrizionePrestazione
FROM (Clienti INNER JOIN TMovAttivita ON Clienti.IDCliente = TMovAttivita.IDCliente) INNER JOIN ((RMovAttivita INNER JOIN Collaboratori ON RMovAttivita.IDCollaboratore = Collaboratori.IDCollaboratore) INNER JOIN Prestazioni ON RMovAttivita.IDPrestazione = Prestazioni.IDPrestazione) ON TMovAttivita.IDAttivita = RMovAttivita.IDAttivita
WHERE (((RMovAttivita.IDCollaboratore)=@Collaboratore) AND ((TMovAttivita.DataAttivita) Between @Inizio And @Fine))
ORDER BY Collaboratori.Nominativo, TMovAttivita.DataAttivita, RMovAttivita.OraInizio;

END


That's it.  Good luck.



Hi andy232,

When did Access start converting Access SQL to T-SQL?

Regards,

Jim
Avatar of Joice64

ASKER

Hi Andy232

the script above doesn't work properly (seems to be something wrong), and I am not sure if  the syntax is correct, may you explain how is possible execute this SP in SQL Server Management Studio (SQL2005)?

Regards:

Massimo
Avatar of Anthony Perkins
>>the script above doesn't work properly<<
Ah, yes the infamous "doesn't work" syndrome.  So let's ask does it:
A. Produce an error message, if so what is it.
B. return the wrong results, if so what are they and what are you expecting.
C. Smoke comes out of your monitor.

If you want help, than you need to help us understand what "doesn't work" we may be technically capable, unfortunately very few of us can read a crystal ball.
In Access alias your tables, this means that when you are manipulating your SQL you only have to correct the table name in the FROM clause. To do this click on the table in the query, view the properties and set the Alias, please not you must tab or click ouit of the alias before going on to the next table (a very annoying habbit of Access)

In SQL Server you will probably need to expand the table name to include the owner of the table

You can eliminate the ( and ) in the WHERE that Access has put in.

To EXECUTE the SP then try
    EXEC Test 'ColVal', '20080101', '20081231'

Finally andy232 left a [ in the CREATE PROC line than shouldn't be there
Cheers, Andrew
CREATE PROCEDURE test @Collaboratore varchar(255), @Inizio smalldatetime,  @Fine smalldatetime
 
AS
BEGIN
 
SELECT R.IDCollaboratore
     , Co.Nominativo
     , T.DataAttivita
     , C.Nominativo
     , R.OraInizio
     , R.OraFine
     , P.DescrizionePrestazione
FROM (Clienti C INNER JOIN TMovAttivita T ON C.IDCliente = T.IDCliente) 
     INNER JOIN ((RMovAttivita R INNER JOIN Collaboratori Co ON R.IDCollaboratore = Co.IDCollaboratore) 
     INNER JOIN Prestazioni P ON R.IDPrestazione = P.IDPrestazione) 
     ON T.IDAttivita = R.IDAttivita
WHERE R.IDCollaboratore= @Collaboratore
AND   T.DataAttivita Between @Inizio And @Fine
ORDER BY Co.Nominativo
       , T.DataAttivita
       , RMovAttivita.OraInizio
 
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of andy232
andy232
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Joice64

ASKER

Many thanks to all for the answers, but is interesting to know if is available a tool or utility for get it automatically?


Regards:

Massimo
Well, there is a tool from Microsoft to convert an Access DB to MS SQL, but that is only for structures.  

http://support.microsoft.com/kb/237980

Here's another article I ran across in my searches:

http://www.databasejournal.com/features/msaccess/article.php/3705151/Converting-Access-Queries-to-SQL-Server.htm