Populating data from an external database
 

I have a spreadsheet with a routine that runs a stored
procedure, sql query on a database over a network. The
routine works well for all except one condition where the
number of records are larger than any of the other
conditions. The condition used is the value located in
cell A1 on each sheet.
The routine to get the data is:

Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB;Server=XXXXX;Database=YYYY;uid= h
hh;password=0000 "

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "ABC SAMPLE"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = ActiveSheet.Cells(1,1)

Set rst = cmd.Execute()

The code fails during the execution of the final command
Set rst =cnd.Execute()

It seems that it is somehow timing out and failing.

Does anyone have any thoughts please.

Help will be much appreciated.

Regards

DavidC

Populating data from an external database
 

Do you need to enclose your command text in square brackets?

cmd.Command.Text="[ABC SAMPLE]"

"DavidC" wrote:

I have a spreadsheet with a routine that runs a stored
procedure, sql query on a database over a network. The
routine works well for all except one condition where the
number of records are larger than any of the other
conditions. The condition used is the value located in
cell A1 on each sheet.
The routine to get the data is:

Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB;Server=XXXXX;Database=YYYY;uid= h
hh;password=0000 "

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "ABC SAMPLE"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = ActiveSheet.Cells(1,1)

Set rst = cmd.Execute()

The code fails during the execution of the final command
Set rst =cnd.Execute()

It seems that it is somehow timing out and failing.

Does anyone have any thoughts please.

Help will be much appreciated.

Regards

DavidC