Solved

copy SQL query from ACCESS to SQL

Posted on 1998-07-09
11
166 Views
Last Modified: 2010-03-19
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?
0
Comment
Question by:khal
  • 6
  • 4
11 Comments
 
LVL 2

Expert Comment

by:odessa
ID: 1091691
Yes you can do this, only copy the SQL querys encapsulate then in stored procedures (make changes if needed) and go ahead
0
 

Author Comment

by:khal
ID: 1091692
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.
0
 
LVL 4

Accepted Solution

by:
tomook earned 30 total points
ID: 1091693
Use Control+C to copy and Control+V to paste.
0
 

Author Comment

by:khal
ID: 1091694
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.
0
 

Author Comment

by:khal
ID: 1091695
I can't see your answer tomook?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:tomook
ID: 1091696
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).
0
 
LVL 4

Expert Comment

by:tomook
ID: 1091697
By the way, if you need help cleaning up the SQL (INNER JOIN, ...), just ask.
0
 

Author Comment

by:khal
ID: 1091698
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??
0
 

Author Comment

by:khal
ID: 1091699
when I save the first stored procedure as a view, then the new stored procedure can read it with no problem???
0
 
LVL 4

Expert Comment

by:tomook
ID: 1091700
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.
0
 

Author Comment

by:khal
ID: 1091701
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.qtmpRainFall') 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.CollectionDate, Avg(tblFieldReading.ReadingValue) AS Rainfall,

FROM tblFieldMeasurement INNER JOIN (tblFieldReading INNER JOIN tlkpEMParameter ON
tblFieldReading.EMParameterID = tlkpEMParameter.EMParameterID) ON
tblFieldMeasurement.MeasurementID = tblFieldReading.MeasurementID
GROUP BY tblFieldMeasurement.CollectionDate, tlkpEMParameter.EMParameterName
HAVING tblFieldMeasurement.CollectionDate Between @myBeginDate And
 @myEndDate AND tlkpEMParameter.EMParameterName="Rainfall"

GO

this is the new stored procedure that calls the previous one

CREATE PROCEDURE qsnpRainFall @MyBeginDate datetime, @MyEndDate datetime AS
SELECT convert(char,collectiondate,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)


0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now