Hitting an Oracle view

I have been asked to include a view from our Oracle system in one of my SQL queries. As a programmer I can write querries all day long, but I have no administration skills.

Can someone just give me the basics on creating this ability? On my development system I have installed the Oracle client and configured an ODBC driver that points to the Oracle views. This has allowed me to create a linked table in Access, but I have never attempted to port this to SQL Server 2k.

I assume I just load the drivers on the production system as before, but do I then use an ODBC driver again, or is there a better method with SQL Server? If its the ODBC method, then how do I add a linked table in SQL?

Sorry for the basic questions, and I really appreciate any help.

Thansks,

RobI'd suggest just using the Microsoft provided drivers unless you need specific Oracle functionality. It makes life a lot simpler in the long run.

Just use Enterprise Mangler or sp_addlinkedserver to make the Oracle server a linked server from your SQL Server, and life ought to be lovely. I don't remember needing to add any drivers to make it work, but I almost always use the Enterprise Edition of MS-SQL so I'm not always aware of what ships/installs with other editions.

-PatP|||Thanks for that tip - I have looked into sp_addlinkedserver and I can probably figure it out, but using the mangler just seems easier to me. If I start up the register server wizard, it seems to only be looking for SQL servers. I don't see any way to change the provider. Is it possible that the developer edition I am using doesn't have the functionality?|||Like so:

-PatPsql

hii

Write SQL Queries for the following:

Cust

Cust_ID

Name

1

AA

2

BB

3

CC

4

DD

5

EE

Ord

Ord_ID

Cust_ID

Amt

Tran_Type

From_Ord_ID

1

1

10

D

NULL

2

1

20

D

NULL

3

2

30

D

NULL

4

2

40

D

NULL

5

2

NULL

D

NULL

6

3

NULL

D

NULL

7

4

40

D

NULL

8

4

50

D

NULL

9

4

-10

C

7

10

4

-20

C

8

11

4

-30

C

8

<!--[endif]-->Write a query that shows the balance for customer DD by trans_type. Consider a Tran_type of “C” to be a transactional adjustment to the associated original “D” record. The result should be as follows.

Name

D

C

Balance

DD

40

-10

30

(It looks like that time of year again, when we start getting requests to do class assignments.)

Subhash,

Please post the efforts you have made up to now, and we can help guide you to a correct solution. I just don't think that you will find folks here willing to do your classwork for you. Also post the table DDL, and sample data in the form of INSERT statements. If you need help with that concept, check here or here.

|||

hahaha.

well its actually easy. you need to use join

and sum function and group by

sql

High disk I/O with long running queries surprising?

> Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
Unless all data can fit in the memory allocated to SQL Server, I would
expect significant disk activity during the process. Even then, modified
still data needs to be written to disk.
Hope this helps.
Dan Guzman
SQL Server MVP
<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
><sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
>
Assuming that SQL Server is using a buffer cache of significantly less than
50GB, and these queries need to read a large percentage of the 50GB of data,
then SQL Server will need to read the data from disk.
So no, I would not be surprised.
David|||Hello,
I am interested in opinions about a case that I recently ran into. We
have a server with SQL Server 2000 Standard where a 50 GB database is
run. A stored procedure is run every night that performs data updates
and perform several aggregations and calculations of the data for about
4 hours in this database. Based on this little information, would you
be surprised to find out that during the execution of this stored
procedure, SQL Server uses the disk to 100 % (or at least want to)?
/Stefan|||> Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
Unless all data can fit in the memory allocated to SQL Server, I would
expect significant disk activity during the process. Even then, modified
still data needs to be written to disk.
Hope this helps.
Dan Guzman
SQL Server MVP
<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
>
Assuming that SQL Server is using a buffer cache of significantly less than
50GB, and these queries need to read a large percentage of the 50GB of data,
then SQL Server will need to read the data from disk.
So no, I would not be surprised.
David|||In addition to what the others have said, if this is based on the readings
from the % Disk Time counter, you may want to check a few more disk counters
to be sure. This counter can be funky. It's more reliable to check I/O
latency counters such as Avg Disk Sec/Read and Avg Disk Sec/Write.
Linchi
"sl@.invativa.se" wrote:

> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||And, of course, WRITE the updates back to disk.
No, I wouldn't be at all surprised.
The next question 'should be', based upon the following DDL and stored
procedure code, is there a more efficient way to accomplish the task?
(include table DDL and stored procedure code.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eacg56dmGHA.660@.TK2MSFTNGP05.phx.gbl...
> <sl@.invativa.se> wrote in message
> news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Assuming that SQL Server is using a buffer cache of significantly less
> than 50GB, and these queries need to read a large percentage of the 50GB
> of data, then SQL Server will need to read the data from disk.
> So no, I would not be surprised.
> David|||In addition to what the others have said, if this is based on the readings
from the % Disk Time counter, you may want to check a few more disk counters
to be sure. This counter can be funky. It's more reliable to check I/O
latency counters such as Avg Disk Sec/Read and Avg Disk Sec/Write.
Linchi
"sl@.invativa.se" wrote:

> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||And, of course, WRITE the updates back to disk.
No, I wouldn't be at all surprised.
The next question 'should be', based upon the following DDL and stored
procedure code, is there a more efficient way to accomplish the task?
(include table DDL and stored procedure code.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eacg56dmGHA.660@.TK2MSFTNGP05.phx.gbl...
> <sl@.invativa.se> wrote in message
> news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Assuming that SQL Server is using a buffer cache of significantly less
> than 50GB, and these queries need to read a large percentage of the 50GB
> of data, then SQL Server will need to read the data from disk.
> So no, I would not be surprised.
> David|||Thanks for all answers.
Great that you all confirmed that lots of disk I/O is no surprise in
this scenario.
/Stefan

High disk I/O with long running queries surprising?

Hello,
I am interested in opinions about a case that I recently ran into. We
have a server with SQL Server 2000 Standard where a 50 GB database is
run. A stored procedure is run every night that performs data updates
and perform several aggregations and calculations of the data for about
4 hours in this database. Based on this little information, would you
be surprised to find out that during the execution of this stored
procedure, SQL Server uses the disk to 100 % (or at least want to)?
/Stefan> Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
Unless all data can fit in the memory allocated to SQL Server, I would
expect significant disk activity during the process. Even then, modified
still data needs to be written to disk.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
>
Assuming that SQL Server is using a buffer cache of significantly less than
50GB, and these queries need to read a large percentage of the 50GB of data,
then SQL Server will need to read the data from disk.
So no, I would not be surprised.
David|||In addition to what the others have said, if this is based on the readings
from the % Disk Time counter, you may want to check a few more disk counters
to be sure. This counter can be funky. It's more reliable to check I/O
latency counters such as Avg Disk Sec/Read and Avg Disk Sec/Write.
Linchi
"sl@.invativa.se" wrote:
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||And, of course, WRITE the updates back to disk.
No, I wouldn't be at all surprised.
The next question 'should be', based upon the following DDL and stored
procedure code, is there a more efficient way to accomplish the task?
(include table DDL and stored procedure code.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eacg56dmGHA.660@.TK2MSFTNGP05.phx.gbl...
> <sl@.invativa.se> wrote in message
> news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
>> Hello,
>> I am interested in opinions about a case that I recently ran into. We
>> have a server with SQL Server 2000 Standard where a 50 GB database is
>> run. A stored procedure is run every night that performs data updates
>> and perform several aggregations and calculations of the data for about
>> 4 hours in this database. Based on this little information, would you
>> be surprised to find out that during the execution of this stored
>> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> Assuming that SQL Server is using a buffer cache of significantly less
> than 50GB, and these queries need to read a large percentage of the 50GB
> of data, then SQL Server will need to read the data from disk.
> So no, I would not be surprised.
> David|||Thanks for all answers.
Great that you all confirmed that lots of disk I/O is no surprise in
this scenario.
/Stefan

High CPU -- identify which query ties up cpu

Hello,
I'm running SQL2K enterprise, recently faced constant high cpu usage by
sqlsrvr process.
How can I identify which query or queries tie up cpu? I mean something more
precise and comprehensible than cpu column in sp_who2.
thanks for any help,
Gary.Gary
I'd run SQL Server profiler to identify long running queries , there are
some events liek CPU,Duration and then try to speed them up
http://www.sql-server-performance.c...or_counters.asp
http://www.sql-server-performance.c...counters_io.asp
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:5079326B-B881-4728-B042-0448C1105D71@.microsoft.com...
> Hello,
> I'm running SQL2K enterprise, recently faced constant high cpu usage by
> sqlsrvr process.
> How can I identify which query or queries tie up cpu? I mean something
> more
> precise and comprehensible than cpu column in sp_who2.
> thanks for any help,
> Gary.

High CPU -- identify which query ties up cpu

Hello,
I'm running SQL2K enterprise, recently faced constant high cpu usage by
sqlsrvr process.
How can I identify which query or queries tie up cpu? I mean something more
precise and comprehensible than cpu column in sp_who2.
thanks for any help,
Gary.Gary
I'd run SQL Server profiler to identify long running queries , there are
some events liek CPU,Duration and then try to speed them up
http://www.sql-server-performance.com/performance_monitor_counters.asp
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:5079326B-B881-4728-B042-0448C1105D71@.microsoft.com...
> Hello,
> I'm running SQL2K enterprise, recently faced constant high cpu usage by
> sqlsrvr process.
> How can I identify which query or queries tie up cpu? I mean something
> more
> precise and comprehensible than cpu column in sp_who2.
> thanks for any help,
> Gary.

Hierarchical queries in SQL Server 2000

Hi,
Do we have Hierarchical queries in SQL Server 2000 (like
that by using start with...connect by prior... in
Oracle)?
If someone has worked on some work-around to do so
in SQL Server 2000, pl. let me know.
Thanks in advance.
Regards
M. Subbaiahwe don't have any such things in SQL Server 2000. Though we have it in SQL
Server 2005 which is the Common table Expression.
The work around is to use a table variable and poplute it in a while loop or
use a recursive stored procedure.
--
"Subbaiah" wrote:

> Hi,
> Do we have Hierarchical queries in SQL Server 2000 (like
> that by using start with...connect by prior... in
> Oracle)?
> If someone has worked on some work-around to do so
> in SQL Server 2000, pl. let me know.
>
> Thanks in advance.
> Regards
> M. Subbaiah
>
>|||Take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||>> Do we have Hierarchical queries in SQL Server 2000 (like that by using st
art with...connect by prior... in Oracle)? <<
The Oracle construct is a cursor hidden in the proprietary syntax.
Geta copy of TREES & HIERARCHIES IN SQL for several other ways to do
this kidn of thing by haivng proper DDL instead of doing it with
recursive or procedural code.

Hierarchial Queries - Order of the data

Hi

I am using SQL Server 2005 Developer edition(Sep 05). I had an oracle hierarchial query the equivalent of which I had written in SQL Server. The problem is the order of the data is different in SQL Server.

To put in proper context :

I need

1. The root node
2. The root’s children and the children of roolt's children and so on and so forth

I get

1. The root node
2. The root’s immediate children
3. The children of the root’s immediate children
4. And so forth.

Find below the DDL, Insert script, the Hierarchial Query, the data expected to return and the actual data returned.

--
-- - DDL Script -
--

CREATE TABLE [sfmfg].[SFPL_MFG_BOM_TEST](

[ITEM_ID] [varchar](40) NOT NULL,

[MFG_BOM_CHG] [varchar](4) NOT NULL,

[PARENT_ITEM_ID] [varchar](40) NOT NULL,

[PARENT_MFG_BOM_CHG] [varchar](4) NOT NULL,

CONSTRAINT [SFPL_MFG_BOM_TEST_PK] PRIMARY KEY CLUSTERED

(

[ITEM_ID] ASC,

[MFG_BOM_CHG] ASC,

[PARENT_ITEM_ID] ASC,

[PARENT_MFG_BOM_CHG] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--
-- - Insert Script --
--

INSERT INTO SFPL_MFG_BOM_TEST (ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'SE1','A', 'N/A', 'N/A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('MF1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'CYL1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('P1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('TB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BT1','A', 'MF1', 'A')

--
-- - Hierarchial Query -
--

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

--
-- - Expected Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3

P1 A SE1 A 2

--
-- - Returned Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

P1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3
Any help in this matter would be greatly appreciated.

Thanks & Regards
Imtiaz

Found solution...Here's how the query needs to look like....

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level,SortKey)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level,

CAST(item_id as varchar(8000))+ CAST(mfg_bom_chg as varchar(8000)) as SortKey

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1,

CAST(p.SortKey as varchar(8000)) + CAST(c.item_id as varchar(8000))+ CAST(c.mfg_bom_chg as varchar(8000) )

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

order by SortKey

Hiding a SP in Access under Query, but retain Execute on SP

Hello,

Access XP, SQL Server 2000

Is it possible to hide a SP under Queries in Access, yet still be able
to Execute it from Access?

We hooked up a custom form to accept the input parameters (MS Feature
Request!) for the Stored Procedure. We had two problems with MS's
'Input Parameter' dialog: 1) We could not customize, 2) We continually
received a message from Access stating, "The stored procedure executed
successfully but did not return records" ('SET NOCOUNT ON' gave no
joy).

Below is the sample code we are using.
TestInsert is the SP
txtName is a text box on the form
@.name is char(80)

Private Sub cmdAdd_Enter()

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute

Set cmd = Nothing

End Sub

Any help would be appreciated. We successfully hid our Tables, and
allowed access through Views. But we can't seem to find a good work
around here.

Thanks

Jeff
Jeffrey Walton
noloaderNoloader (noloader@.yahoo.com) writes:
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).

I have to admit that I understand very little of this. But it could
be because I don't know Access. Probably you should put this question
to comp.databases.ms-access instead.

As for the procedures not returning result sets - did you expect them
to return result sets? Did you run them from Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The most common advice is not to use the @. character in variable nemes with
the Jet engine. If this does not work then a specific cast of the input vale
to the parameter type might work.
Martin
"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0405061431.45bfa457@.posting.google.c om...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94E293F8A543Yazorman@.127.0.0.1>...
> Noloader (noloader@.yahoo.com) writes:
> > Is it possible to hide a SP under Queries in Access, yet still be able
> > to Execute it from Access?
> > We hooked up a custom form to accept the input parameters (MS Feature
> > Request!) for the Stored Procedure. We had two problems with MS's
> > 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> > received a message from Access stating, "The stored procedure executed
> > successfully but did not return records" ('SET NOCOUNT ON' gave no
> > joy).
> I have to admit that I understand very little of this. But it could
> be because I don't know Access. Probably you should put this question
> to comp.databases.ms-access instead.
Thanks for the suggestion. Done.

> As for the procedures not returning result sets - did you expect them
> to return result sets? Did you run them from Query Analyzer?
The SP does not return any records (its a basic insert). I don't
understand why Access is throwing the dialog. I take a cockpit
approach - silent is good. Don't flash the lights and blow the whistle
until something goes wrong.

Thanks for the help Erland and Martin.

Jeff|||noloader@.yahoo.com (Noloader) wrote in message news:<6b543aa7.0405061431.45bfa457@.posting.google.com>...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader

> 2) We continually received a message from Access
> stating, "The stored procedure executed successfully
> but did not return records" ('SET NOCOUNT ON' gave no joy).

We modified the Execute call:

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute , , adExecuteNoRecords

Set cmd = Nothing
Set par = Nothing

Jeff