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

[emsik1001]

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

[ck9663]

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

Dim sqlstring as string
dim strvar as string

strvar = messagebox('please enter parameter")

sqlstring = "exec db..mystoredproc(" & strvar & ")"

'--Then execute the variable

That's not a working code, but I hope you get what am trying to say.

-- CK

[emsik1001]

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.

[emsik1001]

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?

[ck9663]

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

[emsik1001]

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

[ck9663]

Maybe you don't need a stored proc.

Read this

-- CK