khal
asked on
copy SQL query from ACCESS to SQL
I have some queries in Access and I am trying to use these as stored procedures in SQL, is there a way like copy/paste that I can use to copy these queries to the stored procedure windows in SQL Enterprise Manager ( then make some changes to them if needed) instead of retyping the whole thing again?
Yes you can do this, only copy the SQL querys encapsulate then in stored procedures (make changes if needed) and go ahead
ASKER
the problem is how to copy them, because it looks like there is no paste option in the Enterprise Manager stored procedure window? is there another way, please send it in details.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the problem is how to copy them, because it looks like there is no paste option in the Enterprise Manager stored procedure window? is there another way, please send it in details.
ASKER
I can't see your answer tomook?
Open the Access queries in SQL view, highlight the SQL, hit Control+C(copy). Open a new stored procedure, then use Control+V to paste the SQL. Name the stored procedure and clean up the SQL (SQL Server does not like semicolons, for example).
By the way, if you need help cleaning up the SQL (INNER JOIN, ...), just ask.
ASKER
by the way, is it possible to have a stored procedure reads from another stored procedure??. I have a stored procedure that should read from another stored procedure, but whenever I try to save the new stored procedure , an error comes up saying there is no object with the name of the old stored procedure that it is reading from??
ASKER
when I save the first stored procedure as a view, then the new stored procedure can read it with no problem???
First, you should use views whenever you can. Second, when you are editing a stored procedure in Enterprise Manager, the first 5 or 6 lines are to delete the stored procedure. If you use the route of editing an existing stored procedure to create a new one, watch out for this! If these are not the problems, post your Transact SQL and I will see what I can do. Might want to make it a new question so the other experts can get in on it without spending points.
ASKER
this is the old stored procedure(qtmpRainFall) that is called by the second one
two parameters are sent to it
if exists (select * from sysobjects where id = object_id('dbo.qtmpRainFal l') and sysstat & 0xf = 4)
drop procedure dbo.qtmpRainFall
GO
CREATE PROCEDURE qtmpRainFall @MyBeginDate datetime, @MyEndDate datetime AS
select @MyEndDate=dateadd(day,1,@ MyEndDate)
SELECT tblFieldMeasurement.Collec tionDate, Avg(tblFieldReading.Readin gValue) AS Rainfall,
FROM tblFieldMeasurement INNER JOIN (tblFieldReading INNER JOIN tlkpEMParameter ON
tblFieldReading.EMParamete rID = tlkpEMParameter.EMParamete rID) ON
tblFieldMeasurement.Measur ementID = tblFieldReading.Measuremen tID
GROUP BY tblFieldMeasurement.Collec tionDate, tlkpEMParameter.EMParamete rName
HAVING tblFieldMeasurement.Collec tionDate Between @myBeginDate And
@myEndDate AND tlkpEMParameter.EMParamete rName="Rai nfall"
GO
this is the new stored procedure that calls the previous one
CREATE PROCEDURE qsnpRainFall @MyBeginDate datetime, @MyEndDate datetime AS
SELECT convert(char,collectiondat e,1) AS JoinDate, qtmpRainfall.Rainfall,
Rainfall *0.4 AS MMGalRain
FROM qtmpRainfall
actually I added the parameters here (@MyEndDate, @MyBeginDate) because I thought these might solve the problem but it didn't (the new stored procedure reads some information from the old stored procedure results without sending any arguments, but since the old stored procedure needs argument anyway, I thought I might send them through the new one)
two parameters are sent to it
if exists (select * from sysobjects where id = object_id('dbo.qtmpRainFal
drop procedure dbo.qtmpRainFall
GO
CREATE PROCEDURE qtmpRainFall @MyBeginDate datetime, @MyEndDate datetime AS
select @MyEndDate=dateadd(day,1,@
SELECT tblFieldMeasurement.Collec
FROM tblFieldMeasurement INNER JOIN (tblFieldReading INNER JOIN tlkpEMParameter ON
tblFieldReading.EMParamete
tblFieldMeasurement.Measur
GROUP BY tblFieldMeasurement.Collec
HAVING tblFieldMeasurement.Collec
@myEndDate AND tlkpEMParameter.EMParamete
GO
this is the new stored procedure that calls the previous one
CREATE PROCEDURE qsnpRainFall @MyBeginDate datetime, @MyEndDate datetime AS
SELECT convert(char,collectiondat
Rainfall *0.4 AS MMGalRain
FROM qtmpRainfall
actually I added the parameters here (@MyEndDate, @MyBeginDate) because I thought these might solve the problem but it didn't (the new stored procedure reads some information from the old stored procedure results without sending any arguments, but since the old stored procedure needs argument anyway, I thought I might send them through the new one)