By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,329 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.

How to type&pass variable from Excel SQLCMD to Stored Procedure

P: 93
Hi Everyone

I want to fire a stored procedures (SQL Server 2000) from Excel using SQLCMD but I want to be able to change the variable value by typing it in the 'parameter value' box.

Many thanks in advance
Mar 31 '08 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
This is more of an EXCEL question than a SQL-Server question.

In theory, try to create a dynamic sql in EXCEL. I would imagine something like
Expand|Select|Wrap|Line Numbers
  1. Dim sqlstring as string
  2. dim strvar as string
  3.  
  4. strvar = messagebox('please enter parameter")
  5.  
  6. sqlstring = "exec db..mystoredproc(" & strvar & ")"
  7.  
  8. '--Then execute the variable
  9.  
  10.  
That's not a working code, but I hope you get what am trying to say.

-- CK
Mar 31 '08 #2

P: 93
Thanks for the reply

I would post the issue on excel forum but I presume they would say is more SQL or VBA task, but I will try.
Apr 2 '08 #3

P: 93
Is it possible to set a variable in a stored procedures which refers to an external source? for instance a spreadsheet's cell (ideally) or text file?
Apr 2 '08 #4

ck9663
Expert 2.5K+
P: 2,878
Yes. But that might not be the right way of doing it. More details about your project might be necessary for us to help you.

-- CK
Apr 2 '08 #5

P: 93
I'll start from the beginning of my problems.

I want to use Excel 2003 to return some data from SQL Server 2000.

I've connected excel to SQL using Microsoft Query and everything would be fine but 'they' said I need to have the query with NO LOCKS (and I don't know how to do it) so I used stored procedure where I have this:

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
BEGIN TRANSACTION
(...)
COMMIT TRANSACTION

but I have an error when I try to use it directly in Microsoft Query; it runs but when I save it and come out and try to use it again (using Import Data... and selecting the saved query) it says that the file is damaged.

It would be great If I knew how to use Microsoft Query with no locks (or have some kind of proof that I can run it without explicitly specifying no locks - if it doesn't need it)

Many thanks for your help
Apr 3 '08 #6

ck9663
Expert 2.5K+
P: 2,878
Maybe you don't need a stored proc.

Read this

-- CK
Apr 3 '08 #7

Post your reply

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