How do I run an sql stored procedure in a microsoft access query? I am looking to do this because I have a macro that deletes a table and then appends it; however, since I moved the table to sql, the delete takes a very, very long time, and I think running a stored procedure on the server side would do it a lot quicker...unless anyone else has a better idea? This would be part of a macro btw.
SQL Stored Procedure in a Query
- Thread starter dzirkelb
- Start date
A delete is 'relatively' slow, have you tried a Truncate?
Additionaly your real problem is in "simply" re-adding the data, which is just going to take time depending on network and server capacity... Large tables can take a LOOOOOOONG time.
Additionaly your real problem is in "simply" re-adding the data, which is just going to take time depending on network and server capacity... Large tables can take a LOOOOOOONG time.
I'm not sure what a truncate is? I do know, however, that running the delete in query analyzer is extremely fast compared to the access query, so I am thinking the stored procedure would be fast also. I know it can be done as I have done it before, but I have absolutely no idea on what database or what query!
Truncate table YourTable
This is SQL way of a absolute delete, Delete from creates a log file, truncate dont.
Seriously though if you dont know Truncate, should you be messing with SQL Server? Pretty basic command to not know..
Still a Delete itself is probably not the problem as that doesnt do anything but send the command to the server and execute on the server.
The load is what is taking the time.
This is SQL way of a absolute delete, Delete from creates a log file, truncate dont.
Seriously though if you dont know Truncate, should you be messing with SQL Server? Pretty basic command to not know..
Still a Delete itself is probably not the problem as that doesnt do anything but send the command to the server and execute on the server.
The load is what is taking the time.
I will try the truncate in my stored procedure; however, I still haven't had my question answered on how to run as stored procedure from Access...be it in VB, macro itself, or a query.
Big deal if I didn't know what truncate was, i probably did, I just never use it. Creating queries in and stored procedures on sql server is something a monkey can be trained to do (the easy stuff that is).
Big deal if I didn't know what truncate was, i probably did, I just never use it. Creating queries in and stored procedures on sql server is something a monkey can be trained to do (the easy stuff that is).
Running a stored procedure from access you can do by simply calling it in a "pass trough" query that "does not return records" this is an option in the Pass through query option screen.
Well IMHO Truncate is a big deal, and doing the delete (only the delete) I dont expect to make much difference in a stored procedure.... I expect the loading to be a pain...
Well IMHO Truncate is a big deal, and doing the delete (only the delete) I dont expect to make much difference in a stored procedure.... I expect the loading to be a pain...
I definately think the truncate will help immensly...so, I have to make it a stored procedure in sql to run it in access then, correct? Access doesnt' recognzie that function, correct?
If so, then i create the stored procedure, and then call it in my query using the info you just recently gave me, correct?
And, for future reference, is the truncate able to be used with a where clause? I'm guessing not, but hoping.
If so, then i create the stored procedure, and then call it in my query using the info you just recently gave me, correct?
And, for future reference, is the truncate able to be used with a where clause? I'm guessing not, but hoping.
In a pass through query you are basicaly writing SQL for the target environment, sql server... Doing a trunc in a pass through (note Action query!) is perfectly valid.
Truncate is kill all period, no where no but no if, ALL. Sorry
Truncate is kill all period, no where no but no if, ALL. Sorry
Sorry cannot help you there, yes I too should not be tinkering with SQL Server
Similar threads
