Joice64
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;
QryParametrics.png
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.IDCollaborato re, Collaboratori.Nominativo, TMovAttivita.DataAttivita, Clienti.Nominativo, RMovAttivita.OraInizio, RMovAttivita.OraFine, Prestazioni.DescrizionePre stazione
FROM (Clienti INNER JOIN TMovAttivita ON Clienti.IDCliente = TMovAttivita.IDCliente) INNER JOIN ((RMovAttivita INNER JOIN Collaboratori ON RMovAttivita.IDCollaborato re = Collaboratori.IDCollaborat ore) INNER JOIN Prestazioni ON RMovAttivita.IDPrestazione = Prestazioni.IDPrestazione) ON TMovAttivita.IDAttivita = RMovAttivita.IDAttivita
WHERE (((RMovAttivita.IDCollabor atore)=@Co llaborator e) AND ((TMovAttivita.DataAttivit a) Between @Inizio And @Fine))
ORDER BY Collaboratori.Nominativo, TMovAttivita.DataAttivita, RMovAttivita.OraInizio;
END
That's it. Good luck.
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.IDCollaborato
FROM (Clienti INNER JOIN TMovAttivita ON Clienti.IDCliente = TMovAttivita.IDCliente) INNER JOIN ((RMovAttivita INNER JOIN Collaboratori ON RMovAttivita.IDCollaborato
WHERE (((RMovAttivita.IDCollabor
ORDER BY Collaboratori.Nominativo, TMovAttivita.DataAttivita,
END
That's it. Good luck.
Hi andy232,
When did Access start converting Access SQL to T-SQL?
Regards,
Jim
When did Access start converting Access SQL to T-SQL?
Regards,
Jim
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
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
>>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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
Regards,
Jim