1

Topic: Start of stored procedure from Excel

People, help the code!
I need to launch stored procedure in SQL-server'e from  with parameter input. That is, I enter into a cell 0,56, I launch a macro -  it is fulfilled. And still somehow to receive as back coupling of the message of type, "it was not possible to write down value of density" or "the Parameter is written successfully down" is is provided by script .
I will make a reservation at once: from SQL procedure successfully works with output of diagnostic messages, the line of connection too is true. The resulted script advised, but not  it, swears "Type mismatch".
[spoiler]

Private Sub RecDens ()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Connection
Set cn = New ADODB.Connection
cn. ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=PowerPlant; Data Source ="PROTON"' Ïàðàìåòðû ñòðîêè ïîäêëþ÷åíèÿ
cn. Open
Set cmd = New ADODB.Command
cmd = "exec SPNetWriteGasDensity"AND"0.56"
cn. Close
End Sub

[/spoiler]
The moderator: we Learn to use tags of design of the code - FAQ

2

Re: Start of stored procedure from Excel

kovax;
You have on the server a procedure with name SPNetWriteGasDensity0.56?

3

Re: Start of stored procedure from Excel

To begin with in this code a syntactic error (there where inverted commas in a line of connection) - inverted commas in text literals it is necessary
And further it is necessary to esteem that-thread how to launch procedures with parameters
First http://www.askit.ru/custom/vba_office/m … ommand.htm

4

Re: Start of stored procedure from Excel

Shocker. Pro;
0,56 is a value which procedure SPNetWriteGasDensity due to write down in the instrument.
The specified procedure, certainly, is.

5

Re: Start of stored procedure from Excel

kovax wrote:

Shocker. Pro;
0,56 is a value which procedure SPNetWriteGasDensity due to write down in the instrument.
The specified procedure, certainly, is.

is not present, I asked about a name "SPNetWriteGasDensity0.56"
Because such you try to launch

cmd = "exec SPNetWriteGasDensity"AND"0.56"

6

Re: Start of stored procedure from Excel

Shocker Pro, I you do not understand couples: you that mean? In the above-stated line I put the following sense: exec (gap) a name procedures (gap) value. Perhaps in it an error?

7

Re: Start of stored procedure from Excel

kovax wrote:

a procedure name ( a gap ) value

also show me this gap

8

Re: Start of stored procedure from Excel

as it to show... Gap key press is simple.
Perhaps, it is necessary: cmd = "exec SPNetWriteGasDensity 0.56"?
On Monday on operation I will try.
Yes, and for a line of connection of attention do not turn - it at me in other macroes is used - checked up, works.

9

Re: Start of stored procedure from Excel

kovax wrote:

Hm as it to show.

Well as. I gave the reference to how to make out messages. There there are also selection means in the code.

kovax wrote:

gap key press is simple.

key press of a gap does not mean gap appearance in a test literal.

kovax wrote:

yes, and for a line of connection of attention do not turn - it at me in other macroes is used - checked up, works.

the line can also the worker, but it does not mean that  copied it will be correctly appropriated by a variable.

kovax wrote:

Perhaps, it is necessary: cmd = "exec SPNetWriteGasDensity 0.56"?

as a variant. And generally, the link how to launch procedure with parameters I gave above

kovax wrote:

the Resulted script advised, but not  it

I do not know, who there that advised to you, but this script about what, it does nothing even if to rectify in it errors

10

Re: Start of stored procedure from Excel

So to launch procedure with parameter I learned, at last!
It is necessary to solve the task with back coupling - that in the form of MsgBox' the message which is deduced if I fulfill this procedure from  was deduced.

Private Sub Rec_Density ()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cn = New ADODB.Connection
cn. ConnectionString = "a line "
cn. Open
Set cmd = New ADODB.Command
cmd. ActiveConnection = cn
cmd. CommandText = "SPNetWriteGasDensity"
cmd. CommandType = adCmdStoredProc
cmd. CommandTimeout = 20
cmd. Parameters. Append cmd. CreateParameter ("@Density", adVarChar, adParamInput, 5, TextBox1.Value)
cmd. Execute
cn. Close
Set cn = Nothing
Set cmd = Nothing
End Sub

11

Re: Start of stored procedure from Excel

Shocker Pro;
I ask you to help with this business! I know now that can!
I result script  (authorship not mine, if that):

CREATE PROCEDURE SPNetWriteGasDensity
@Density varChar (16)
AS
create table #SPNetMain (
Port varchar (16);
Baud int;
Timeout int;
ConnDevAddr int;
RequestDelay int
)
insert #SPNetMain select Port, Baud, Timeout, ConnDevAddr, RequestDelay from SPNetworks where id = 1
declare @Port as varchar (16)
declare @Baud as int
declare @Timeout as int
declare @ConnDevAddr as int
declare @RequestDelay as int
select @Port = Port from #SPNetMain
select @Baud = Baud from #SPNetMain
select @Timeout = Timeout from #SPNetMain
select @ConnDevAddr = ConnDevAddr from #SPNetMain
select @RequestDelay = RequestDelay from #SPNetMain
drop table #SPNetMain
declare @Handle int
declare @rc int
exec @rc = master. xp_SPNet_connect @Port, @Baud, @Timeout, @ConnDevAddr, @Handle OUTPUT
if @rc = 0 begin
declare @WriteResult int
set @WriteResult = 0
declare @ErrorMessage varchar (256)
set @ErrorMessage = ' I can not write down density of gas:'
-- The counter 7, the channel 1
exec @rc = master. xp_SPNet_set_array @Handle, 7, 1, 125, 9, @Density
set @WriteResult = @WriteResult + @rc
if @rc! = 0 begin
set @ErrorMessage = @ErrorMessage + ' (c 7, the channel 1)'
end
exec master. xp_SPNet_disconnect @Handle
if @WriteResult! = 0 begin
select @ErrorMessage as Diagnostic
end
else begin
select ' Ok ' as Diagnostic
end
end
else begin
select ' I can not be connected to  ' as Diagnostic
end
GO

12

Re: Start of stored procedure from Excel

Set rs = cmd. Execute ()
MsgBox rs ("Diagnostic")
rs. Close

That type

13

Re: Start of stored procedure from Excel

Private Sub Rec_Density ()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Set cn = New ADODB.Connection
cn. ConnectionString = ""
cn. Open
Set cmd = New ADODB.Command
cmd. ActiveConnection = cn
cmd. CommandText = "SPNetWriteGasDensity"
cmd. CommandType = adCmdStoredProc
cmd. CommandTimeout = 20
cmd. Parameters. Append cmd. CreateParameter ("@Density", adVarChar, adParamInput, 5, TextBox1.Value)
cmd. Execute
Set rst = cmd. Execute ()
MsgBox rst ("Diagnostic")
cn. Close
rst. Close
Set cn = Nothing
Set cmd = Nothing
End Sub

The error "Run-time error ' 3265 ' jumps out." It is not possible to find object in the family, corresponding to a demanded name or ordinal value ".
Tried to substitute instead of Diagnostic ErrorMessage (@ErrorMessage) - the same

14

Re: Start of stored procedure from Excel

The first operator  write

SET NOCOUNT ON

15

Re: Start of stored procedure from Excel

Interposed. After AS (i.e. 4th line SET NOCOUNT ON). Changed Nothing. Almost: if to write not "Diagnostic", and "Diagnostics" then jumps out MsgBox "Ok". However, it jumps out always even if in the counter value did not register. And before input SET NOCOUNT ON even such "diagnostics" did not work. In general not that it is necessary.

16

Re: Start of stored procedure from Excel

Just in case...

CREATE PROCEDURE SPNetWriteGasDensity
@Density varchar (16)
AS
SET NOCOUNT ON
create table #SPNetMain (
Port varchar (16);
Baud int;
Timeout int;
ConnDevAddr int;
RequestDelay int

17

Re: Start of stored procedure from Excel

You stir up something, because any Diagnostics in your code are not present.
Deliver break point on MsgBox, look that is returned in rst

18

Re: Start of stored procedure from Excel

Once again script  and a screenshot

CREATE PROCEDURE SPNetWriteGasDensity
@Density varchar (16)
AS
SET NOCOUNT ON
create table #SPNetMain (
Port varchar (16);
Baud int;
Timeout int;
ConnDevAddr int;
RequestDelay int
)
insert #SPNetMain select Port, Baud, Timeout, ConnDevAddr, RequestDelay from SPNetworks where id = 1
declare @Port as varchar (16)
declare @Baud as int
declare @Timeout as int
declare @ConnDevAddr as int
declare @RequestDelay as int
select @Port = Port from #SPNetMain
select @Baud = Baud from #SPNetMain
select @Timeout = Timeout from #SPNetMain
select @ConnDevAddr = ConnDevAddr from #SPNetMain
select @RequestDelay = RequestDelay from #SPNetMain
drop table #SPNetMain
declare @Handle int
declare @rc int
exec @rc = master. xp_SPNet_connect @Port, @Baud, @Timeout, @ConnDevAddr, @Handle OUTPUT
if @rc = 0 begin
declare @WriteResult int
set @WriteResult = 0
declare @ErrorMessage varchar (256)
set @ErrorMessage = ' I can not write down density of gas:'
-- The counter 7, the channel 1
exec @rc = master. xp_SPNet_set_array @Handle, 7, 1, 125, 9, @Density
set @WriteResult = @WriteResult + @rc
if @rc! = 0 begin
set @ErrorMessage = @ErrorMessage + ' (c 7, the channel 1)'
end
exec master. xp_SPNet_disconnect @Handle
if @WriteResult! = 0 begin
select @ErrorMessage as Diagnostic
end
else begin
select ' Ok ' as Diagnostic
end
end
else begin
select ' I can not be connected to  ' as Diagnostic
end
GO

19

Re: Start of stored procedure from Excel

It is guilty, the script hardly changed

CREATE PROCEDURE SPNetWriteGasDensity
@Density varchar (16)
AS
SET NOCOUNT ON
create table #SPNetMain (
Port varchar (16);
Baud int;
Timeout int;
ConnDevAddr int;
RequestDelay int
)
insert #SPNetMain select Port, Baud, Timeout, ConnDevAddr, RequestDelay from SPNetworks where id = 1
declare @Port as varchar (16)
declare @Baud as int
declare @Timeout as int
declare @ConnDevAddr as int
declare @RequestDelay as int
select @Port = Port from #SPNetMain
select @Baud = Baud from #SPNetMain
select @Timeout = Timeout from #SPNetMain
select @ConnDevAddr = ConnDevAddr from #SPNetMain
select @RequestDelay = RequestDelay from #SPNetMain
drop table #SPNetMain
declare @Handle int
declare @rc int
exec @rc = master. xp_SPNet_connect @Port, @Baud, @Timeout, @ConnDevAddr, @Handle OUTPUT
if @rc = 0 begin
declare @WriteResult int
set @WriteResult = 0
declare @ErrorMessage varchar (256)
set @ErrorMessage = ' I can not write down density of gas:'
-- The counter 7, the channel 1
exec @rc = master. xp_SPNet_set_array @Handle, 7, 1, 125, 9, @Density
set @WriteResult = @WriteResult + @rc
if @rc! = 0 begin
set @ErrorMessage = @ErrorMessage + ' (c 7, the channel 1)'
end
exec master. xp_SPNet_disconnect @Handle
if @WriteResult! = 0 begin
select @ErrorMessage as Diagnostic
end
else begin
select ' it is successful ' as Diagnostic
end
end
else begin
select ' I can not be connected to  ' as Diagnostic
end
GO

20

Re: Start of stored procedure from Excel

Screen with result of break point on MsgBox

21

Re: Start of stored procedure from Excel

And here for variant Diagnostics

22

Re: Start of stored procedure from Excel

Well so on a screen with QA nevertheless it is visible! In the first  at you Diagnostics = "Ok" that you fool a head.
To obtain the data from the second , it is necessary to take the data from rs. NextRecordset ()

23

Re: Start of stored procedure from Excel

But I in this case, time embedded procedures return any the , would receive the message through @ErrorMessage, declaring it as procedure output-parameter

24

Re: Start of stored procedure from Excel

Earned, as wanted. Added a line

Set rst=rst. NextRecodrset

Shocker Pro, thanks!

25

Re: Start of stored procedure from Excel

Shocker Pro, maybe, prompt, what is the matter?
Normally I enter number where as a separator the point serves - then  writes "Successfully". If to deliver a separator a comma and to try to launch  through QA  will swear, and the number does not register - is checked up. If as to make, using , it produces the message "Successfully" though  after all does not fulfill record! Even if to reboot a computer (to eliminate saving Recordset'), to open the book and at once to try to write down number with a "wrong" separator - all the same writes that is successful. Here it strains me!