Calling stored procedures from Access 2000

The information here has been great and accurate.
How do I execute a SQL-Server 2000 stored procedure from Access 2000.
The procedure has three parameters (P1, P2, P3).
Thanks in advance,From an mdb or an adp? you can execute from a pass-through query or
VBA/ADO using a Connection or Command object.
--Mary
On Fri, 15 Oct 2004 11:55:05 -0700, bnhcomputing
<bnhcomputing@.discussions.microsoft.com> wrote:
>The information here has been great and accurate.
>How do I execute a SQL-Server 2000 stored procedure from Access 2000.
>The procedure has three parameters (P1, P2, P3).
>Thanks in advance,
>

Calling stored procedures from Access 2000

The information here has been great and accurate.
How do I execute a SQL-Server 2000 stored procedure from Access 2000.
The procedure has three parameters (P1, P2, P3).
Thanks in advance,From an mdb or an adp? you can execute from a pass-through query or
VBA/ADO using a Connection or Command object.
--Mary
On Fri, 15 Oct 2004 11:55:05 -0700, bnhcomputing
<bnhcomputing@.discussions.microsoft.com> wrote:

>The information here has been great and accurate.
>How do I execute a SQL-Server 2000 stored procedure from Access 2000.
>The procedure has three parameters (P1, P2, P3).
>Thanks in advance,
>

Calling stored procedures from Access 2000

The information here has been great and accurate.
How do I execute a SQL-Server 2000 stored procedure from Access 2000.
The procedure has three parameters (P1, P2, P3).
Thanks in advance,
From an mdb or an adp? you can execute from a pass-through query or
VBA/ADO using a Connection or Command object.
--Mary
On Fri, 15 Oct 2004 11:55:05 -0700, bnhcomputing
<bnhcomputing@.discussions.microsoft.com> wrote:

>The information here has been great and accurate.
>How do I execute a SQL-Server 2000 stored procedure from Access 2000.
>The procedure has three parameters (P1, P2, P3).
>Thanks in advance,
>

Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.com...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=u%24dR%233nTEHA.3476%40tk2msftngp13.phx.gbl
David|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison

Calling of Store Procs on SQL Server from MS Access

Hi everyone,
This is my first cut-through in developing store procs on SQL server, i've
got them created and put in the master db on SQL server. on the Access front
end I have code that passes in an update statement which calls the store pro
c
on SQL server, i've checked the syntax and there are no errors. I've
checked the SQL statement being passed in to the store proc and there are no
errors too, using SQL query analyzer.
When I try to run the code, it does not update the records I want on SQL
server, I'm thinking there's something wrong with the connectionstring? Can
anybody give me some insight or alternative to get around this problem?
AlbertAlbert
I don't know that you want to put stored procedures in the master database.
You should build an application database.
If you are using ADPs in Access, does the interactive Test Connection button
work? If so it is not your connection string. Even if you are using
MDBs, you should be able to test your connection interactively to check the
string (say, if you are using an ODBC object).
Other than that we would need to see your code (including the DDL) to
understand what the problem might be.
Joe|||I still can't pin point the problem, but here is some more information to
provide you on my problem.
On the SQL server enterprise, i've created an store proc the following in
the master db:
CREATE PROCEDURE SP_SQLSTRING
@.sqlstring varchar(50)
AS
DECLARE @.InsertString NVARCHAR(500)
if exists(select * from sysobjects where name = @.sqlstring)
Set @.InsertString = @.sqlstring
execute sp_executesql @.InsertString
GO
Now in the front end access, the code i have supplies an query as an
parameter...
eg strSQL = "Update..."
then i make a call to the function that is suppose to call the store proc:
SP_SQLSTR (strSQL)
Within the function SP_SQLSTR it has the following partial code:
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=xxxxx;Initial Catalog=" xxxxxxxxx ";Data Source=xxxxxx"
cn.Open
Dim rstSQLStr As ADODB.Command
Set rstSQLStr = New ADODB.Command
rstSQLStr.ActiveConnection = cn
rstSQLStr.CommandText = "SP_SQLSTRING" 'name of the store proc on SQL
server
rstSQLStr.CommandType = adCmdStoredProc
SP = xSQLstr
DoCmd.SetWarnings False
'This is where I execute it, but i don't get any errors, and it does
nothing to the database.
rstSQLStr.Execute , Array(SP)
I don't know where i went wrong, and there are no errors from the system.
So i'm a bit to where to approach? If you can provide some guidanc
e
that would be very greatful..
Albert
"J. M. De Moor" wrote:

> Albert
> I don't know that you want to put stored procedures in the master database
.
> You should build an application database.
> If you are using ADPs in Access, does the interactive Test Connection butt
on
> work? If so it is not your connection string. Even if you are using
> MDBs, you should be able to test your connection interactively to check th
e
> string (say, if you are using an ODBC object).
> Other than that we would need to see your code (including the DDL) to
> understand what the problem might be.
> Joe
>
>|||I urge you to take a good look at the Books Online, but not before you get a
good book on the basics of SQL.
ML|||On Fri, 12 Aug 2005 08:19:13 -0700, "Albert Chan" <Albert
Chan@.discussions.microsoft.com> wrote:

>Hi everyone,
>This is my first cut-through in developing store procs on SQL server, i've
>got them created and put in the master db on SQL server. on the Access fron
t
>end I have code that passes in an update statement which calls the store pr
oc
>on SQL server, i've checked the syntax and there are no errors. I've
>checked the SQL statement being passed in to the store proc and there are n
o
>errors too, using SQL query analyzer.
>When I try to run the code, it does not update the records I want on SQL
>server, I'm thinking there's something wrong with the connectionstring? Ca
n
>anybody give me some insight or alternative to get around this problem?
>Albert
Hi Albert,
1. Create the procedure in the application database, not in the master
database.
2. Test the procedure first by calling it from Query Analyzer. That must
works without errors before you even attempt to call the procedure from
Access (or any other client).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 12 Aug 2005 13:25:04 -0700, Albert Chan wrote:

>CREATE PROCEDURE SP_SQLSTRING
>@.sqlstring varchar(50)
>AS
>DECLARE @.InsertString NVARCHAR(500)
>if exists(select * from sysobjects where name = @.sqlstring)
>Set @.InsertString = @.sqlstring
> execute sp_executesql @.InsertString
>GO
Hi Albert,
First of all, I fail to see the point of this procedure. If the
procedure does nothing but dynamically executing the string passed to
it, why not simply execute the SQL from your front end? Why use a stored
procedure at all?

>Now in the front end access, the code i have supplies an query as an
>parameter...
>eg strSQL = "Update..."
That means that the first statement will test for the existance of a
system object in the master database with the name "Update...". I guess
(and hope!) that you have no tables, triggers, or procedures with that
name, so the next statement is not executed.
The last statement will be executed (the conditional execution is only
for the first statement after an IF, unless you use a BEGIN / END block)
but since @.InsertString is still NULL, it won't do anything.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Calling MS Access function from DTS

Does anyone know if you can call an Access function from DTS?
I'm trying to delete data from an Access database, Compact the database, and load new data. My snag is calling a function in Access to compact the database.
Suggestions?I don't think you can actually call Access code from within DTS, but since DTS effectively is VBA (http://msdn.microsoft.com/vba/), you can write VB code that will do the same thing using ADO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_ax_06er.asp) from within DTS.

-PatP|||Sounds like using a nuclear bomb to kill a mosquito...

Maybe a bat file that launches a different access database, then uses autoexec does the work then closes...

I would probably do this is a sproc with bcp as well, and have the job scheduled...

What's the source of the data?

Where do you ski, or is it just a nickname?sql

Calling methods, functions threw Extended SP in a DLL

Hi,

I alredy tried to search this problem in last posts, but I couldn't
find the answer.
I try to access via Extended SP the method in a dll.

I registered the dll as a ExSP, with a name of method. But after
calling it in T-SQL, I became such a error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the
function SendGeneralNotify_FromA in the library [LibraryName.dll].
Reason: 127(error not found).

In this dll I have only one class an it has events, properities and
methods.
I will to call one of these methods.

aha ... very importand. Please don't say that this dll should be
written in c++ because it is made like that (no VB).

Maybe somebody of you have an example how I should call it, to became
an access on this dll?

Sorry for my not well english.

With best regards, looking forward for reply

----------
Matik
marzec@.sauron.xo.plMatik,

> In this dll I have only one class an it has events, properities and
> methods.
> I will to call one of these methods.
> aha ... very importand. Please don't say that this dll should be
> written in c++ because it is made like that (no VB).

If I understand you correctly, you've written a DLL in VB 6 (or 5) and you
want to call it as an extended stored procedure. Unfortunately, VB 6 can't
export a function, which is what an extended stored procedure must do. You
have three choices:

1. Use a different language (C, C++, Delphi, etc): I understand that you
don't want to do this. I don't know if VB.NET will let you export a
function, but if it will that might be an option.

2. Buy a tool from Desaware that enables you to export a function from VB 6.
I don't remember the name and I've never used it, but you should be able to
find it at Desaware.

3. Instead of using the extended stored procedure functionality, use the
sp_OA... stored procedures to instantiate your class and call the method
you want. Check books online or google for examples: sp_OACreate is a good
one to start with an google.

My standard extended sp warning: extended stored procedure are dangerous. I
refuse to allow them where I work because a single errant pointer can cause
untold damage. I know people use them to great effect, but it gives me the
shivers. I feel the same way (but not as strongly) about the sp_OA...
procedures. My real advice is to find another way to do it. And... if
writing an extended sp doesn't make you nervous, you probably shouldn't
write one :)

Craig|||Hej Craig,

> If I understand you correctly, you've written a DLL in VB 6 (or 5) and you
> want to call it as an extended stored procedure. Unfortunately, VB 6 can't
> export a function, which is what an extended stored procedure must do. You
> have three choices:

Maybe I explained it a little nfortunately. One more time:
- this is a object (in dll) which has properities and methodes.
- I must call one of these methods,
- it is written in C++,

If I try to do that via ExSP, it do not works (I have this error as
above alredy written).
If I do that with sp_OA, then It works ... but not like I expected:-)

First, I create the object:

exec @.iRetVal = sp_OACreate '{034188F2-8DBC-4613-829A-76D5279C35A3}',
@.iObject OUTPUT,1
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUTPUT
IF @.iRetVal <> 0
begin
set @.sLog = 'No object created. Source: ' + @.sSource + '
Description: ' + @.sDescription
print @.sLog
end

This is without problems.
Then I try to call this methode:

exec @.iRetVal = sp_OAMethod @.iObject,'SendNotification_FromA',
@.sProperty OUT, @.nMessageNr = 555, @.bstrDateTime = @.dDateEVT,
@.textFromA1 = @.sText1 OUTPUT, @.textFromA2 = @.sText2 OUT
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT

IF @.iRetVal <> 0
begin
set @.sLog = 'Source: ' + @.sSource + ' Description: ' +
@.sDescription
print @.sLog
end

IF @.iRetVal <> 0
begin
set @.sLog = 'Source: ' + @.sSource + ' Description: ' +
@.sDescription
print @.sLog
end

print @.iRetVal

PRINT @.sProperty
PRINT @.sText1
PRINT @.sText2

... and I became no errors, but the values are the same I set at the
beginning.
On my pc works also the small software, which should capture this
values.
This software recieves the data sended with this method.
I wrote the small VB programm, where I call the same method, with the
same values, and then the second programm reives the data ...

Hopeless?

Thank You for Your cindly reply.

Matik|||Matik,

Please see inline

> Maybe I explained it a little nfortunately. One more time:
> - this is a object (in dll) which has properities and methodes.
> - I must call one of these methods,
> - it is written in C++,

Ah... I see. I apologize for the misunderstanding.

> If I try to do that via ExSP, it do not works (I have this error as
> above alredy written).
> If I do that with sp_OA, then It works ... but not like I expected:-)

If you already have C++ and you really want an ExSP then you can look in SQL
Server Books Online for the topic "Extended Stored Procedure Programming"
under the main heading "Build SQL Server Applications". Basically you would
need to #include a special header or two and export a very specific function
type. But since you already have a COM object...

> First, I create the object:

Remember that the object identifier you're getting is basically a handle to
a COM interface pointer, so you need to treat it like you would any other
resource: if sp_OACreate succeeds, then you need to call sp_OADestroy.

Another gotcha that seems simple but people seem to forget: the COM object
needs to be registered on the SQL Server (if you're instantiating a local
COM Server, which you are here). I watched people nearly strangle a
laughing coworker when they realized that registering a COM component on the
computer running Query Analyzer isn't the same :) I know this isn't your
problem, but if it hasn't bitten you yet, it will...

<whitespace edited>
> exec @.iRetVal = sp_OAMethod
> @.iObject,
> 'SendNotification_FromA',
> @.sProperty OUT,
> @.nMessageNr = 555,
> @.bstrDateTime = @.dDateEVT,
> @.textFromA1 = @.sText1 OUTPUT,
> @.textFromA2 = @.sText2 OUTPUT

OK, I couldn't see what was wrong, so I created a quick COM object (I
cheated and used VB :) that matched what I *think* your COM object looks
like. Here's the IDL:

HRESULT SendNotification_FromA(
[in] long nMessageNr,
[in] BSTR bstrDateTime,
[in, out] BSTR* textFromA1,
[in, out] BSTR* textFromA2,
[out, retval] BSTR* );

This is the important part: you need to make sure that the parameter names
match up in the call to sp_OAMethod and your IDL. Since you're trying to
get output parameters, you also need to make sure that textFromA1 and
textFromA2 are [in,out] and are a datatype that allows that (such as BSTR*).

Also, you need to make sure that you've correctly implemented IDispatch and
that all your parameters are OLE compatible. A good test is to re-write
your VB program to use late-binding. For example (untested and coded in my
newsreader):

Dim obj As Object
Dim s, a1, a2
Set obj = CreateObject("SomeLib.SomeClass")

a1 = "in 1"
a2 = "in 2"
s = obj.SendNotification_FromA(555, "SomeDateTime", a1, a2)

MsgBox s & vbcrlf & a1 & vbcrlf & a2

Set obj = Nothing

Regardless, I got it to work, so I imagine there's something wrong with the
way your COM code is working. After checking your IDL and matching T-SQL
code, I would try stripping the COM method down to as little as possible
(maybe just log out input parms and set the output parms to constants) and
see if you can get that to work.

Good Luck,

Craig

Calling GetReportParameters within a report Code block?

I want to write a Code block routine that returns the parameter Prompts
and ValidValue that was selected from within the report. Can I access
the ReportParameters collection (not the parameters collection, but the
actual parameter definitions collection) from within a Code block?
Thanks,
--jasonDo you know that you can get both of the things you want without doing
anything special? Parameters!parametername.Value and
Parameters!parametername.Label Lots of people don't realize you can use
the .Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<burt4684@.gmail.com> wrote in message
news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> I want to write a Code block routine that returns the parameter Prompts
> and ValidValue that was selected from within the report. Can I access
> the ReportParameters collection (not the parameters collection, but the
> actual parameter definitions collection) from within a Code block?
> Thanks,
> --jason
>|||Thanks, Bruce. I didn't know about the .Label property. That's good to
know. My only issue with .Value is some of my parameters are primary keys
and I need a good way to display the lookup values instead of the actual key
values. I was thinking I could iterate through the parameter.ValidValues
collection and display the ValidValue.Name that corresponds to the
parameter.Value that was selected. Do you know if this is possible?
Thanks,
--jason
"Bruce L-C [MVP]" wrote:
> Do you know that you can get both of the things you want without doing
> anything special? Parameters!parametername.Value and
> Parameters!parametername.Label Lots of people don't realize you can use
> the .Label
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <burt4684@.gmail.com> wrote in message
> news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > I want to write a Code block routine that returns the parameter Prompts
> > and ValidValue that was selected from within the report. Can I access
> > the ReportParameters collection (not the parameters collection, but the
> > actual parameter definitions collection) from within a Code block?
> > Thanks,
> >
> > --jason
> >
>
>|||Create another dataset that accepts the parameter as it's queryparameter.
You will end up with a single record (i.e. it has done your lookup), then
anywhere you need to use this value use this expression: =First(Fields!fieldname.value,"DatasetName")
Note that when you create a queryparameter RS adds a report parameter. If
you have the queryparameter with the same name as the reportparameter you
already have (must match case exactly since it is case sensitive) then your
query parameter and report parameter will already be mapped correctly.
This should do exactly what you need to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> Thanks, Bruce. I didn't know about the .Label property. That's good to
> know. My only issue with .Value is some of my parameters are primary keys
> and I need a good way to display the lookup values instead of the actual
key
> values. I was thinking I could iterate through the parameter.ValidValues
> collection and display the ValidValue.Name that corresponds to the
> parameter.Value that was selected. Do you know if this is possible?
> Thanks,
> --jason
> "Bruce L-C [MVP]" wrote:
> > Do you know that you can get both of the things you want without doing
> > anything special? Parameters!parametername.Value and
> > Parameters!parametername.Label Lots of people don't realize you can
use
> > the .Label
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > <burt4684@.gmail.com> wrote in message
> > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > I want to write a Code block routine that returns the parameter
Prompts
> > > and ValidValue that was selected from within the report. Can I access
> > > the ReportParameters collection (not the parameters collection, but
the
> > > actual parameter definitions collection) from within a Code block?
> > > Thanks,
> > >
> > > --jason
> > >
> >
> >
> >|||As always, there's more than one way to skin a cat. Great idea. Thanks for
the help!
--jason
"Bruce L-C [MVP]" wrote:
> Create another dataset that accepts the parameter as it's queryparameter.
> You will end up with a single record (i.e. it has done your lookup), then
> anywhere you need to use this value use this expression: => First(Fields!fieldname.value,"DatasetName")
> Note that when you create a queryparameter RS adds a report parameter. If
> you have the queryparameter with the same name as the reportparameter you
> already have (must match case exactly since it is case sensitive) then your
> query parameter and report parameter will already be mapped correctly.
> This should do exactly what you need to do.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
> news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> > Thanks, Bruce. I didn't know about the .Label property. That's good to
> > know. My only issue with .Value is some of my parameters are primary keys
> > and I need a good way to display the lookup values instead of the actual
> key
> > values. I was thinking I could iterate through the parameter.ValidValues
> > collection and display the ValidValue.Name that corresponds to the
> > parameter.Value that was selected. Do you know if this is possible?
> >
> > Thanks,
> >
> > --jason
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Do you know that you can get both of the things you want without doing
> > > anything special? Parameters!parametername.Value and
> > > Parameters!parametername.Label Lots of people don't realize you can
> use
> > > the .Label
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > <burt4684@.gmail.com> wrote in message
> > > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > > I want to write a Code block routine that returns the parameter
> Prompts
> > > > and ValidValue that was selected from within the report. Can I access
> > > > the ReportParameters collection (not the parameters collection, but
> the
> > > > actual parameter definitions collection) from within a Code block?
> > > > Thanks,
> > > >
> > > > --jason
> > > >
> > >
> > >
> > >
>
>

Calling Function From Stored Procdure ??

I'm a total novice with SQL, so please bare with me.
I'm trying to write a simple forum for my web site, i've done it before using Access but struggling to use the same idea with SQL. In acces i had one table for all the post and replies, i then had several querys that filtered off just the main topics and the number of replies for each thread. I did this by using multiple queries joined together, i've now found out that in SQL you cant call one strored procdure from another, so you have to use functions instead.

I have a stored procdure that filters off just the starter threads, but i want to also show how many replies ther has been for each topic and put then in date order of when they were posted.

this is my main stored procdure

CREATE PROCEDURE forum2 AS
SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID
FROM forum
WHERE forum.partID=0
ORDER BY dtDate DESC

this works ok and shows the main threads, but i also want to list the number of posts for each thread, i've got a function

CREATE FUNCTION dbo.CountofUsers()
RETURNS TABLE
AS RETURN
SELECT Count(*) AS CountOfID, forum.partID
FROM forum
WHERE forum.partID<>0
GROUP BY forum.partID

this should count the number of posts for each thread, but how do i combined the 2 together

i've messed about with adding "countofusers()" to the FROM clause in the procdure but i'm struggling!!! In acesses it something like "JOIN INNER..."
Please can anyone help!!In order to use table functions, assuming you are using SQL 2005 and not SQL 2000 in which case you are stuck and would have to either use cursors or else create an empty column which you update with the count afterwards, you need to join using either CROSS APPLY (like an inner join) or OUTER APPLY (like a left outer join).

E.g.

SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID,
CountofUsers.CountOfID
FROM forum
OUTER APPLY dbo.CountofUsers() CountofUsers
WHERE forum.partID=0
AND CountofUsers.partID = forum.partID
ORDER BY dtDate DESC

I would probably re-write the function to take a parameter for partID for optimal efficiency:

CREATE FUNCTION dbo.CountofUsers() (@.partID int)
RETURNS @.CountOfUsers TABLE (
CountOfID int,
partID int
)
AS
BEGIN
INSERT INTO @.CountOfUsers (
CountOfID
)
SELECT Count(*) AS CountOfID
FROM forum
WHERE forum.partID=(@.partID
RETURN
END

and then use the select like this:

SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID,
CountofUsers.CountOfID
FROM forum
OUTER APPLY dbo.CountofUsers(forum.partID) CountofUsers
WHERE forum.partID=0
ORDER BY dtDate DESC

Hope that helps.

Alternatively, I have found a way to be able to pass table results from one SQL stored procedure up to a caller via XML datatype output parameters. This simulates MARS behaviour with ADO.NET, allowing more than one resultset to be passed up as well. Check Books Online for more info.

Quote:

Originally Posted by Milkstr

I'm a total novice with SQL, so please bare with me.
I'm trying to write a simple forum for my web site, i've done it before using Access but struggling to use the same idea with SQL. In acces i had one table for all the post and replies, i then had several querys that filtered off just the main topics and the number of replies for each thread. I did this by using multiple queries joined together, i've now found out that in SQL you cant call one strored procdure from another, so you have to use functions instead.

I have a stored procdure that filters off just the starter threads, but i want to also show how many replies ther has been for each topic and put then in date order of when they were posted.

this is my main stored procdure

CREATE PROCEDURE forum2 AS
SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID
FROM forum
WHERE forum.partID=0
ORDER BY dtDate DESC

this works ok and shows the main threads, but i also want to list the number of posts for each thread, i've got a function

CREATE FUNCTION dbo.CountofUsers()
RETURNS TABLE
AS RETURN
SELECT Count(*) AS CountOfID, forum.partID
FROM forum
WHERE forum.partID<>0
GROUP BY forum.partID

this should count the number of posts for each thread, but how do i combined the 2 together

i've messed about with adding "countofusers()" to the FROM clause in the procdure but i'm struggling!!! In acesses it something like "JOIN INNER..."
Please can anyone help!!

|||Thanks for you explaination, very usefull, i am indeed using SQL 2000, i i guest i'm a little stuck. I thought what i was doing was quite straight forward but it apppears not to be!
I've tried using a sub query but didn't have much joy.
Can you suggest anything else that will get me going?? i'm only wrting a simple forum. (i dont have to use the table function, this was only a suggestion to me, if there is a better way for the fuction to work i can try it)|||Probably the easiest method is to create a temp table and insert the results of the SELECT statement from your function into it (either directly as a select or from the function or a stored procedure, if you want the piece of code to be reusable). Then perform a join to your previous result on partID to get the count value.

Calling external functions from SQL SELECT

Does anyone know whether (and if so how :) it is possible to call external
functions from SQL's SELECT statement in a similar way that MS Access allows
you call code in its VBA modules?
For example, I'd like to be able to do this:
SELECT Name, DOB, VeryComplexFunction([Table.Numeric]) AS NumericResult
FROM Table
where,
VeryComplexFunction is a function that I (or some other 3rd party)
provide, perhaps in a DLL, but which cannot be implemented in a UDF
Now, I currently do that in Access, but now need to migrate to SQL.
I be very grateful for for any help or pointers that anyone can offer.
Thanks in adavnce.A Shasore,
Take a look at sp_OA... stored procedures in BOL.
Ilya
"A Shasore" <AShasore@.discussions.microsoft.com> wrote in message
news:FF3F6C55-AB9A-4228-95C6-B0E564031EE9@.microsoft.com...
> Does anyone know whether (and if so how :) it is possible to call external
> functions from SQL's SELECT statement in a similar way that MS Access
allows
> you call code in its VBA modules?
> For example, I'd like to be able to do this:
> SELECT Name, DOB, VeryComplexFunction([Table.Numeric]) AS NumericResult
> FROM Table
> where,
> VeryComplexFunction is a function that I (or some other 3rd
party)
> provide, perhaps in a DLL, but which cannot be implemented in a UDF
> Now, I currently do that in Access, but now need to migrate to SQL.
> I be very grateful for for any help or pointers that anyone can offer.
> Thanks in adavnce.
>|||Hello Ilya,
Thanks for that, I'll take another look. Although from what (little) I know
of OLE Object support, I don't it can achieve the same thing.
In the meantime, thanks for your reply...
A.
"Ilya Margolin" wrote:

> A Shasore,
> Take a look at sp_OA... stored procedures in BOL.
> Ilya
> "A Shasore" <AShasore@.discussions.microsoft.com> wrote in message
> news:FF3F6C55-AB9A-4228-95C6-B0E564031EE9@.microsoft.com...
> allows
> party)
>
>

Calling child package out of process fails with Access Denied.

Has anyone ever used an Execute Package Task to call a child package, and the Execute Package Task's ExecuteOutOfProcess = True? Unless the account it runs under is an Administrator on the box, it fails for me with "Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_SalesTransaction_Tracking.dtsx". Access is denied."

This is eating up hours and hours of my time, time we can't afford. Is anyone able to successfully call a child package out of process?

Unfortunately I have faced the same problem.

This only happens when you're executing the package from SQL Server Agent, or your account is of lower priveledges than anything but Administrator.

The only current solution to it, until ofcourse someone from microsoft fixes this problem, is to have your package run under the privelleges of a user in the Local Admin Group. Anything, and I mean anything less than that will cause you the same problem. I tried enabling ever single option, one by one, in the Local Security Policy, and nothing but adding the user that the SQL Server Agent executes in the context of, adding that user to the Local Admin group was the only solution.

Sorry for the bad news. But that's probably your only current solution as to what I know of.

|||

In this case it appears that the SQL Server Agent user account does not have access to the location of the package to be executed. If that access can be granted, then that should be effective.

However, you may not wish to give access to the Program Files folder - so you may wish to save the packages elsewhere in the file system. Alternatively, if file system access is tricky to provide, you may wish to store the packages in SQL Server and execute them from there. There are specific SSIS roles which can be used to control access to packages. See this interesting article from Kirk Haselden: http://www.sqlmag.com/Article/ArticleID/46723/sql_server_46723.html or this entry in Books Online: http://msdn2.microsoft.com/en-US/library/ms141053.aspx

A solution which enables package access with least privileges is certainly preferable to adding your Agent user to Local Admin.

Donald

Calling child package out of process fails with Access Denied.

Has anyone ever used an Execute Package Task to call a child package, and the Execute Package Task's ExecuteOutOfProcess = True? Unless the account it runs under is an Administrator on the box, it fails for me with "Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_SalesTransaction_Tracking.dtsx". Access is denied."

This is eating up hours and hours of my time, time we can't afford. Is anyone able to successfully call a child package out of process?

Unfortunately I have faced the same problem.

This only happens when you're executing the package from SQL Server Agent, or your account is of lower priveledges than anything but Administrator.

The only current solution to it, until ofcourse someone from microsoft fixes this problem, is to have your package run under the privelleges of a user in the Local Admin Group. Anything, and I mean anything less than that will cause you the same problem. I tried enabling ever single option, one by one, in the Local Security Policy, and nothing but adding the user that the SQL Server Agent executes in the context of, adding that user to the Local Admin group was the only solution.

Sorry for the bad news. But that's probably your only current solution as to what I know of.

|||

In this case it appears that the SQL Server Agent user account does not have access to the location of the package to be executed. If that access can be granted, then that should be effective.

However, you may not wish to give access to the Program Files folder - so you may wish to save the packages elsewhere in the file system. Alternatively, if file system access is tricky to provide, you may wish to store the packages in SQL Server and execute them from there. There are specific SSIS roles which can be used to control access to packages. See this interesting article from Kirk Haselden: http://www.sqlmag.com/Article/ArticleID/46723/sql_server_46723.html or this entry in Books Online: http://msdn2.microsoft.com/en-US/library/ms141053.aspx

A solution which enables package access with least privileges is certainly preferable to adding your Agent user to Local Admin.

Donald

Callind Stored Proc from Stored Proc

I am trying to call a stored proc from within a stored proc as below, but it
throws a syntax error.
SELECT COUNT(*) FROM (EXEC spName)
I have no access to "spName" to make any amends, currnetly it returns a
large recordset of customers.
Anyone any ideas how to do this?Billy
INSERT INTO #tmp EXEC spName --Make sure that number of columns in the
#tmp should be match to the output from the SP
SELECT COUNT(*) FROM #tmp
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:26DC8B64-FF85-4BE0-8913-B84A021C2D40@.microsoft.com...
>I am trying to call a stored proc from within a stored proc as below, but
>it
> throws a syntax error.
> SELECT COUNT(*) FROM (EXEC spName)
> I have no access to "spName" to make any amends, currnetly it returns a
> large recordset of customers.
> Anyone any ideas how to do this?
>|||Try using "select @.@.rowcount" immediately after executing the proc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Callind Stored Proc from Stored Proc

I am trying to call a stored proc from within a stored proc as below, but it
throws a syntax error.
SELECT COUNT(*) FROM (EXEC spName)
I have no access to "spName" to make any amends, currnetly it returns a
large recordset of customers.
Anyone any ideas how to do this?
Billy
INSERT INTO #tmp EXEC spName --Make sure that number of columns in the
#tmp should be match to the output from the SP
SELECT COUNT(*) FROM #tmp
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:26DC8B64-FF85-4BE0-8913-B84A021C2D40@.microsoft.com...
>I am trying to call a stored proc from within a stored proc as below, but
>it
> throws a syntax error.
> SELECT COUNT(*) FROM (EXEC spName)
> I have no access to "spName" to make any amends, currnetly it returns a
> large recordset of customers.
> Anyone any ideas how to do this?
>
|||Try using "select @.@.rowcount" immediately after executing the proc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Caller identity

I am using SQL Server 2000 and SQL authentication in a web
appliacation. All data access is done via single SQL Server account.
In my front end I am using forms authentication. Is there a way to
retrieve the forms identity (or just a username) from SQL Server?

Thanks,
LacSQL can only know what's passed through the connection string, which
will be the SQL username.

Of course, there might be a solution if you describe more of the
problem; are you trying to log user activity? Do you just need to know
when a certain user is logged on or off?

Stu|||[posted and mailed, please reply in news]

(lac@.myrealbox.com) writes:
> I am using SQL Server 2000 and SQL authentication in a web
> appliacation. All data access is done via single SQL Server account.
> In my front end I am using forms authentication. Is there a way to
> retrieve the forms identity (or just a username) from SQL Server?

Yes, but the form has to cooperate, and do this:

DECLARE @.bin varbinary(128)
SELECT @.bin = convert(varbinary(128), 'Username')
SET CONTEXT_INFO @.bin

Then you can retrieve the username in SQL Server this way:

DECLARE @.username varchar(128)
SELECT @.username = convert(varchar(128), context_info)
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid

Note that on SQL 2005, you should replace the access to sysprocesses to
use the built-in function context_info(). (Which unfortunately is not
available in SQL 2000.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Called web page from DTS

How can I execute or access a web page from a DTS package?

Both SQL server AND website are hosted on the same server (a Dual 2.4Gz Xeon with 2Gb RAM, RAID 5 etc)

I have 2 tables in SQL server 2000 that hold orders. These need to be posted into another table at a predefined time (ie: 4:30pm) and at the same time, access a remote address (a web service) and post certain elements of the order back.

Basically, can anyone help me out on how to execute a web page from a DTS.

I do NOT want to access a DTS from a webpage, which is all I'm finding at the moment.Not a problem - solved my problem|||How did you solve your problem? Did you use the ServerHTTP object through VBScript?

David|||Create stored proc with :

exec xp_cmdshell 'c:\temp\test.vbs

Call stored proc from DTS

test.vbs should contain something like:

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run("http://localhost/yoursite/yourpage.aspx")

and then do whatever you like in 'yourpage.aspx'

Works fine for me, but then I have full control over the server. If the site is hosted with a hosting company you may have problems using xp_cmdshell due to the fact that it can pose a security problem because it can pretty much perform any command on the server.|||Why did you create the stored proc, and why did you create the vbs file? Why not just execute the vbscript within the DTS package itself?|||Note: I didn't post ALL of my code for the stored proc - I only posted the relevent part of it.

There's a lot more going on in the procedure which I haven't mentioned, which I didn't want to post.

Call a Stored Procedure in another server

is it possible to call a stored proc if for example im in server1 then the proc i need is in server2... can i access that proc?

any help is much appreciated! tnx!As you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR

call a Oracle stored proc in SQL SERVER

We have set up Oracle database as a linked server in SQL Server.
We are able to access Oracle tables fine.

I am trying to call a Oracle stored procedure in SQL Server as follows:
declare @.p1 varchar(1000)
set @.p1 = 'HHH'
exec GENRET..OPS$GENRET.BOB_TEST_PROC @.p1

This is the message:
Server 'GENRET' is not configured for RPC.

Please help.
Thanks in advance
vv (vanishri16@.yahoo.com) writes:
> We have set up Oracle database as a linked server in SQL Server.
> We are able to access Oracle tables fine.
> I am trying to call a Oracle stored procedure in SQL Server as follows:
> declare @.p1 varchar(1000)
> set @.p1 = 'HHH'
> exec GENRET..OPS$GENRET.BOB_TEST_PROC @.p1
> This is the message:
> Server 'GENRET' is not configured for RPC.

exec sp_serveroption GENRET, 'rpc', true
exec sp_serveroption GENRET, 'rpc out', true

may be a start.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Calculations in the SQL Server

Hi All,
I am converting database from Access 2000 to SQL Server 2000.
I have very long calculations written in Access.
eg.
A+B as C, C+D as E, E + F as G and so on...
In SQL I created views, and they refer to each other. From my query
with calculation I created 5 views, where view 2 use calculations from
view 1, view 3 from view 2 and 1... and view 5 use view 1 to view 4. I
use those views in Stored Procedures with sum, average and LAST which
doesn't exist in SQL. (So that is another view)
All of that works but very slow.
Is there any better way to handle this kind of calulations?
Thank you
Schapopa
Deeply nested views can be slow. It might be better to simply create a
single view with all of the calcs you need...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112614187.922629.116440@.z14g2000cwz.googlegr oups.com...
> Hi All,
> I am converting database from Access 2000 to SQL Server 2000.
> I have very long calculations written in Access.
> eg.
> A+B as C, C+D as E, E + F as G and so on...
> In SQL I created views, and they refer to each other. From my query
> with calculation I created 5 views, where view 2 use calculations from
> view 1, view 3 from view 2 and 1... and view 5 use view 1 to view 4. I
> use those views in Stored Procedures with sum, average and LAST which
> doesn't exist in SQL. (So that is another view)
> All of that works but very slow.
> Is there any better way to handle this kind of calulations?
> Thank you
> Schapopa
>
|||Is there any other way. Those calculation are really big, and often
contain divisions. (where I am checking if value = 0) I would say that
it is impossible to create one view from those 5 views.
Wayne Snyder wrote:
> Deeply nested views can be slow. It might be better to simply create
a[vbcol=seagreen]
> single view with all of the calcs you need...
from[vbcol=seagreen]
4. I[vbcol=seagreen]
which[vbcol=seagreen]
|||On 4 Apr 2005 07:32:33 -0700, schapopa wrote:

>Is there any other way. Those calculation are really big, and often
>contain divisions. (where I am checking if value = 0) I would say that
>it is impossible to create one view from those 5 views.
Hi schapopa,
I'm not sure if it helps in your case, but you could consider using
derived tables. Something like this:
SELECT Expr1, Expr2, Expr1 + Expr2 AS Expr3
FROM (SELECT Col1 - Col2 AS Expr1, Col3 * Col4 AS Expr2, Col7
FROM SomeTable
WHERE Col5 > Col6) AS x
WHERE Expr1 <> Expr2
AND Expr2 + Col7 > 500
You can even nest them. The example below ussees two levels of nesting,
but you can add more.
SELECT Expr1, Expr2, Expr3,
COALESCE(Expr3 / NULLIF(Expr1 - Expr2, 0), 0) AS Expr4
FROM (SELECT Expr1, Expr2, Expr1 + Expr2 AS Expr3
FROM (SELECT Col1 - Col2 AS Expr1, Col3 * Col4 AS Expr2, Col7
FROM SomeTable
WHERE Col5 > Col6) AS x
WHERE Expr1 <> Expr2
AND Expr2 + Col7 > 500) AS y
WHERE Expr3 = 18
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Look at using scalar user-defined functions.
Without seeing the calculations you are trying to perform, you should still
be able to build a base set of functions that you can nest. Then, create
one VIEW that puts them all together.
Sincerely,
Anthony Thomas

"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112625153.408751.17040@.g14g2000cwa.googlegro ups.com...
Is there any other way. Those calculation are really big, and often
contain divisions. (where I am checking if value = 0) I would say that
it is impossible to create one view from those 5 views.
Wayne Snyder wrote:
> Deeply nested views can be slow. It might be better to simply create
a[vbcol=seagreen]
> single view with all of the calcs you need...
from[vbcol=seagreen]
4. I[vbcol=seagreen]
which[vbcol=seagreen]

Calculations in the SQL Server

Hi All,
I am converting database from Access 2000 to SQL Server 2000.
I have very long calculations written in Access.
eg.
A+B as C, C+D as E, E + F as G and so on...
In SQL I created views, and they refer to each other. From my query
with calculation I created 5 views, where view 2 use calculations from
view 1, view 3 from view 2 and 1... and view 5 use view 1 to view 4. I
use those views in Stored Procedures with sum, average and LAST which
doesn't exist in SQL. (So that is another view)
All of that works but very slow.
Is there any better way to handle this kind of calulations?
Thank you
SchapopaDeeply nested views can be slow. It might be better to simply create a
single view with all of the calcs you need...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112614187.922629.116440@.z14g2000cwz.googlegroups.com...
> Hi All,
> I am converting database from Access 2000 to SQL Server 2000.
> I have very long calculations written in Access.
> eg.
> A+B as C, C+D as E, E + F as G and so on...
> In SQL I created views, and they refer to each other. From my query
> with calculation I created 5 views, where view 2 use calculations from
> view 1, view 3 from view 2 and 1... and view 5 use view 1 to view 4. I
> use those views in Stored Procedures with sum, average and LAST which
> doesn't exist in SQL. (So that is another view)
> All of that works but very slow.
> Is there any better way to handle this kind of calulations?
> Thank you
> Schapopa
>|||Is there any other way. Those calculation are really big, and often
contain divisions. (where I am checking if value = 0) I would say that
it is impossible to create one view from those 5 views.
Wayne Snyder wrote:
> Deeply nested views can be slow. It might be better to simply create
a
> single view with all of the calcs you need...
> > Hi All,
> >
> > I am converting database from Access 2000 to SQL Server 2000.
> > I have very long calculations written in Access.
> > eg.
> > A+B as C, C+D as E, E + F as G and so on...
> >
> > In SQL I created views, and they refer to each other. From my query
> > with calculation I created 5 views, where view 2 use calculations
from
> > view 1, view 3 from view 2 and 1... and view 5 use view 1 to view
4. I
> > use those views in Stored Procedures with sum, average and LAST
which
> > doesn't exist in SQL. (So that is another view)
> >
> > All of that works but very slow.
> >
> > Is there any better way to handle this kind of calulations?|||On 4 Apr 2005 07:32:33 -0700, schapopa wrote:
>Is there any other way. Those calculation are really big, and often
>contain divisions. (where I am checking if value = 0) I would say that
>it is impossible to create one view from those 5 views.
Hi schapopa,
I'm not sure if it helps in your case, but you could consider using
derived tables. Something like this:
SELECT Expr1, Expr2, Expr1 + Expr2 AS Expr3
FROM (SELECT Col1 - Col2 AS Expr1, Col3 * Col4 AS Expr2, Col7
FROM SomeTable
WHERE Col5 > Col6) AS x
WHERE Expr1 <> Expr2
AND Expr2 + Col7 > 500
You can even nest them. The example below ussees two levels of nesting,
but you can add more.
SELECT Expr1, Expr2, Expr3,
COALESCE(Expr3 / NULLIF(Expr1 - Expr2, 0), 0) AS Expr4
FROM (SELECT Expr1, Expr2, Expr1 + Expr2 AS Expr3
FROM (SELECT Col1 - Col2 AS Expr1, Col3 * Col4 AS Expr2, Col7
FROM SomeTable
WHERE Col5 > Col6) AS x
WHERE Expr1 <> Expr2
AND Expr2 + Col7 > 500) AS y
WHERE Expr3 = 18
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Look at using scalar user-defined functions.
Without seeing the calculations you are trying to perform, you should still
be able to build a base set of functions that you can nest. Then, create
one VIEW that puts them all together.
Sincerely,
Anthony Thomas
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112625153.408751.17040@.g14g2000cwa.googlegroups.com...
Is there any other way. Those calculation are really big, and often
contain divisions. (where I am checking if value = 0) I would say that
it is impossible to create one view from those 5 views.
Wayne Snyder wrote:
> Deeply nested views can be slow. It might be better to simply create
a
> single view with all of the calcs you need...
> > Hi All,
> >
> > I am converting database from Access 2000 to SQL Server 2000.
> > I have very long calculations written in Access.
> > eg.
> > A+B as C, C+D as E, E + F as G and so on...
> >
> > In SQL I created views, and they refer to each other. From my query
> > with calculation I created 5 views, where view 2 use calculations
from
> > view 1, view 3 from view 2 and 1... and view 5 use view 1 to view
4. I
> > use those views in Stored Procedures with sum, average and LAST
which
> > doesn't exist in SQL. (So that is another view)
> >
> > All of that works but very slow.
> >
> > Is there any better way to handle this kind of calulations?