By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

Stuck with Truncate

P: 48
Hi,

I have a SQL Server 2005, used in combination with Access VBA.
The SQL Server uses Windows Authenticatiob

I want to do "Truncate Table" from within VBA.

At first I wrote the Truncate in VBA (ExecuteSPT), which works fine to me (owner of the SQLserver Database/Table). However running the code on another PC it fails. It's because of the rights, making that user Owner solves the problem, but this ain't an option.

Can this be solved or is it mandatory to go for PlanB ?

PlanB: Because I read somewhere, Stored procedures can be assigned to users, I wrote the Truncate stuff as a stored procedure, which I can execute (Parse) succesfully, but can't find a way to save it as a Stored procedure on the server. Despite I started with "New Stored procedure", the save option always results in a local save on my Desktop, not on the server

[HTML]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [TruncateTable]
@TableName VARCHAR(50)
AS
BEGIN
TRUNCATE TABLE [dbo].[@TableName]
END
GO
[/HTML]

Even If the save would be ok I'll still have to find out how to allow the Stored Procedure for an user, and how to launch it from VBA.

I've been Googling / Reading, but can't find one "complete" example as a good (noob-understandable) example

Someone any ideas ?
May 27 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
You don't save stored procedure as file like other development/data base tool that you can call.

Open a query analyzer and execute your script. The problem with this is you have to create one stored proc for every user, too messy.

You might need to give execute rights for those users and call the stored proc using the full naming convention db.dbo.sp

-- CK
May 27 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.