UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ssms Use Of Queries (not Views), SQL Server 2008 R2    
 
   
firlandsfarm
post Jun 11 2019, 01:26 AM
Post#1



Posts: 366
Joined: 28-April 02
From: Heathfield, England


OK, it looks as if a View in SSMS is really a View in that it only likes data recalls that can be 'viewed' and doesn't have 'Append' views or 'Make Table' views as Access does (with queries). But it has Queries that can Append or Make a Table but ... how do you use them? I can write an SSMS Query and I can save an SSMS Query but I am directed to save it as an independant individual file in an independant individual folder, so how do I recall it for future use (other than go through the paraphanalia of finding the folder and opening the file!)? How do I refer to the query in any future SQL code I may write? How can I refer to the query (would Action be a better term?) from my Access front end?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
jleach
post Jun 11 2019, 03:54 AM
Post#2


UtterAccess Editor
Posts: 10,008
Joined: 7-December 09
From: St Augustine, FL


Generally an "action query" would be put into a stored procedure (or saved as a raw SQL string as part of the next-level application code).

--------------------
Go to the top of the page
 
cheekybuddha
post Jun 11 2019, 03:58 AM
Post#3


UtterAccess VIP
Posts: 11,293
Joined: 6-December 03
From: Telegraph Hill


Copy and paste the action query SQL from SSMS in to the SQL view of an Access query and save it as a Pass-thru query.

If you don't need to use access, then store it as a .SQL file and open it from SSMS, or create a Stored Procedure that contains the action SQL, which can be called directly from SSMS and can have parameters

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 11 2019, 03:59 AM
Post#4


UtterAccess VIP
Posts: 11,293
Joined: 6-December 03
From: Telegraph Hill


Hi Jack! wavehi.gif

I think we're suggesting along the same lines!

--------------------


Regards,

David Marten
Go to the top of the page
 
firlandsfarm
post Jun 11 2019, 06:25 AM
Post#5



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Thanks jleach but what is ...

a "stored procedure"?
a "raw SQL string" and saved how/where?
"next-level application code"?

I'm sorry but I have no idea what your line translates to in plain English! smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
firlandsfarm
post Jun 11 2019, 06:35 AM
Post#6



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Now I like that cheeky, I've actually been doing that as a tool in my self-learning technique and using the SQL checker in SSMS to confirm the code before putting it in a Pass-Through query from Access. I have no idea why I didn't realise that doh.gif Maybe I was thinking there must be an easy way in SSMS that I'm missing ... but maybe there isn't!

But I must admit your second line leaves me a bit like jleach's line did! I'm probably blinding myself but saving it and extracting it for use within SSMS is a whole lot more complicated that just clicking on an Access Pass-Through link!

Thanks for your input guys.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jun 11 2019, 07:40 AM
Post#7


UA Admin
Posts: 35,146
Joined: 20-June 02
From: Newcastle, WA


Pass-Thru queries are similar to local Access queries except for the fact that Access actually doesn't even try to execute the SQL statements in them. Instead Access "passes thru" that SQL to the SQL Server (or other RDBMS) where it is executed.

So, when you create and run a SQL Statement in SSMS, you are doing exactly the same thing as you would by creating and running that same SQL Statement as a pass-thru query from the Access side. Same SQL, different invocation of it.

In Access, you can create queries to SELECT, INSERT, DELETE and UPDATE data in tables.

In SQL Server (and other RDBMSs) you can create VIEWS, which are saved SELECT queries, or you can create Stored Procedures, which are saved INSERT, DELETE and UPDATE SQL statements--plus a lot more. In fact, Stored Procedures can take on many of the same features as Access procedures written as Functions or Subs.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
firlandsfarm
post Jun 11 2019, 07:47 AM
Post#8



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Thanks George, as I had said I knew that and had actually be using that to help me to code my SQL queries. I had a mental void! smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 10:17 PM