begin tran
exec("storeprocedure1('2')")
if @.@.error=0
begin
exec("storeprocedure2")
if @.@.error=0
begin
exec("storeprocedure3(122)")
if @.error=0
commit tran
else
rollback tran
end
else
rollback tran
end
else
rollback transure, exec statements will work,
but your syntax is not correct, it should be like this:
begin tran
statement1if (@.@.error <> 0)
begin
rollback tran
return
endstatement2
if (@.@.error <> 0)
begin
rollback tran
return
endcommit tran
about you're actually trying to do here. Are you talkingHow do I get RS to understand my schema? I'm using a sql server account as credentials for my data source.
Thanks,
-LoriUser error. I was tired. My datasource was not being deployed, and the tables did not exist in the old datasource. Once I updated the report/datasource and re-deployed, I could see the tables.
Dear Experts,
We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).
We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.
The Automation manager connection doesn't release all the connections.
What effects is this having to our SQL Server 2005 installation and how can we avoid it?
We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.
Thank you for your time.
Regards,
Spyros Christodoulou
You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,Thanks for your reply.
Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.
The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)
For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.
Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?
Regards,
Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,
Thanks for your reply,
I found a post you made at microsoft.public.sqlserver.programming on august of 2001.
"I have used this. It works perfectly fine. Are you making a EXE? Does it
have UI? Make sure it doesn't have any of those. Here is one sample:
NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE
NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE
-- Now run this on the SQL Server
declare @.o int, @.h int, @.p varchar( 255 )
exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.o
exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.p
exec sp_oadestroy @.o"
I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)
Do you know how to make it an out of process and called it based on the following scenario:
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
Any help will be greatly appreciated.
Thanks again,
Spyros Christodoulou
|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||
Hi Umachandar and Spyros:
I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.
The remaining problem is also similar as the one Spyros described:
"(...)
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
(...)"
In server 1, I have the COM+ application o package correctly installed and working since years
In Clients computers various front end consume the server classes via, for example in VB
CreateObject('AppMastervs.cOrga', mtsServer1)
or in VFP
CreateObjectEx('AppMastervs.cOrga', mtsServer1)
These clients has registered the corresponding .vbr & tlb via CliReg32
I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5
Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.
So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.
Thanks in advance;
Claudio Facundo Lacivita
Dear Experts,
We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).
We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.
The Automation manager connection doesn't release all the connections.
What effects is this having to our SQL Server 2005 installation and how can we avoid it?
We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.
Thank you for your time.
Regards,
Spyros Christodoulou
You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,Thanks for your reply.
Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.
The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)
For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.
Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?
Regards,
Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,
Thanks for your reply,
I found a post you made at microsoft.public.sqlserver.programming on august of 2001.
"I have used this. It works perfectly fine. Are you making a EXE? Does it
have UI? Make sure it doesn't have any of those. Here is one sample:
NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE
NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE
-- Now run this on the SQL Server
declare @.o int, @.h int, @.p varchar( 255 )
exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.o
exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.p
exec sp_oadestroy @.o"
I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)
Do you know how to make it an out of process and called it based on the following scenario:
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
Any help will be greatly appreciated.
Thanks again,
Spyros Christodoulou
|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||
Hi Umachandar and Spyros:
I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.
The remaining problem is also similar as the one Spyros described:
"(...)
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
(...)"
In server 1, I have the COM+ application o package correctly installed and working since years
In Clients computers various front end consume the server classes via, for example in VB
CreateObject('AppMastervs.cOrga', mtsServer1)
or in VFP
CreateObjectEx('AppMastervs.cOrga', mtsServer1)
These clients has registered the corresponding .vbr & tlb via CliReg32
I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5
Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.
So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.
Thanks in advance;
Claudio Facundo Lacivita
Hello All,
We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.
Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:
ADODB.Parameter param = new ADODB.Parameter();
param=cmd.CreateParameter("@.StoreID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput,sizeof(int) , 9);
cmd.Parameters.Append(param);
ADODB.Recordset rsInv = cmd.Execute(out objAffected, ref objAffected,-1 );
In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.
Sincerely,
Dwight Kulkarni
It was a simple thing of course, the cursor location was EOF.
Thanks.
I know it's possible to call a stored procedure from a stored procedure, both within the same dB and in different dB's, but is it possible to call a SP that's on a different server? My gut says "definitely not. at least not easily." But I wanted to see if anyone had any thoughts on this before I pursue a different course of action.Lookup "Linked Server" in books online and you will get lots of information on linking servers, which should then allow you to execute the SP on another server.|||Do you have any examples or know where I can find some of a stored procedure that uses a linked server? thanks for the help!
jonathan|||Actually, I got it to work. Thanks again!
jonathan
How can we call Stored Procedure inside any SQL Statement
For Example.
If we have procedure name sprocCurrentPriority
select * from tablename where colmunname = exec sprocCurrentPriority
Try with this:
|||SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
SqlCommand command = conn.CreateCommand())
conn.Open();
command.CommandText = "sprocCurrentPriority";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@.colmunname", SqlDbType.DateTime).Value = DateTime.Now;
command.ExecuteNonQuery();
conn.Close();
What can of output the stored procedure will return?
If it is an OUTPUT parameter, then the statment will be:
1EXEC MyStoredProcedure @.MyOuputParameterint23SELECT *FROM MyTableWhere MyColumn = @.MyOutputParameter
If the output is a result set, the you can load it into a table then do what you want to do with it :)
1Insert into MyTable2exec MyStoerdProcedure
Note: The returned result set must match the "MyTable" strcture.
Good luck.|||
EXEC MyStoredProcedure @.MyOuputParameterint
SELECT *FROM MyTableWhere MyColumn = @.MyOutputParameter
Ok.
But when this query will change to
EXEC MyStoredProcedure @.MyOuputParameterint
SELECT top 3 FROM MyTableWhere MyColumn = @.MyOutputParameter
Now for all 3 rows the value of @.MyOutputParameter will same.
But I want that for every row it will re execute the procedure and get the new value.
|||
zeeshanuddinkhan@.hotmail.com:
EXEC MyStoredProcedure @.MyOuputParameterint
SELECT *FROM MyTableWhere MyColumn = @.MyOutputParameter
Ok.
But when this query will change to
EXEC MyStoredProcedure @.MyOuputParameterint
SELECT top 3 FROM MyTableWhere MyColumn = @.MyOutputParameter
Now for all 3 rows the value of @.MyOutputParameter will same.
But I want that for every row it will re execute the procedure and get the new value.
So, lets your stored procedure return the output as a result set.
Then load (or insert) the result set in a table (must both the result set and the table have the same strcture).
Then your query will be:
1SELECT top 3FROM MyTableWhere MyColumnin2 (3Select MyColumnValue4From MyTableHavingTheResultSet5 )6GO
This will do the job; getting top 3 records that match the criteria (condition) you got from the stored procedures [more than a value]
Good luck..
|||CS4Ever, does a stored procedure be able to return a result set? i thoght it can only return integer value. Could you please show me an example? I would really appreciate your help :)
|||
CS4Ever, does a stored procedure be able to return a result set? i thoght it can only return integer value. Could you please show me an example? I would really appreciate your help :)
Hi Bo Chen,
Actually once I read your post I become surprised, then when I think again I though you become cofused when I wrote in my last post "stored procedure return result set".
What I meant by return (in my last post) is NOT the Return statment in stored procedure, but this (see the example):
1CREATE PROCEDURE MyStoredProcedure23AS45SET NOCOUNT ON67SELECT EmpID, EmpName, EmpDep8FROM EmployeeTable910SET NOCOUNT OFF
I hope things become clear now :)
Good luck.
|||
Got it . thanks :)
We have a large-scale app that uses many complex stored procedures,
and as of now, we're copying the SPs to every new database that is
created, and it will soon become a nightmare for propagating updates
and fixes. We'd like to keep a master set of the SPs in one DB and
"use" them from other DBs so that they only query data and manipulate
tables in the calling DB. I hope someone has some suggestions. Thanks.ZeBerg (adam@.alumni.northwestern.edu) writes:
> Is it possible to have a stored procedure in database A while calling
> it from database B and have it manipulate the tables in database B
> (whatever the calling database happens to be)?
> We have a large-scale app that uses many complex stored procedures,
> and as of now, we're copying the SPs to every new database that is
> created, and it will soon become a nightmare for propagating updates
> and fixes. We'd like to keep a master set of the SPs in one DB and
> "use" them from other DBs so that they only query data and manipulate
> tables in the calling DB. I hope someone has some suggestions. Thanks.
While this is possible, this will bring out of the frying pan, and into
the fire.
You would have to use dynamic SQL to manipulate the tables, and then
many of the advantages of stored procedures would go out the window.
OK, so there is the possibility of adding your own system procedures in
the master database, but this is unsupported, has security issues and
and I am not even sure whether it works with next version of SQL Server.
And neither of these schemes works the day you decide to scale out and
have databases one more than one server. Or the day you want to a test
version of the app running against a test database on the same server.
So what is left? Do as you do know, that is the way to go. What you
apparently need, is to invest time in how to deploy changes in a
controlled way. A foundation for this is to use a version-control
system. It is from the VCS you build update scripts to deploy the
changes. You might higher levels of sophistication, and there are
a number of third-party tools out there which aim specifically at
configuration management with SQL Server.
Since my shop in the same as yours - except that our multiple databases
are both in-house and remote customer sites - we have developed our
own routines, and the tool we use is actually available as freeware,
see http://www.abaris.se/abaperls/.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:
SqlCommand c =newSqlCommand("AddAuthor", myConnection);c.Parameters.Add(newSqlParameter("@.LastName",SqlDbType.VarChar, 100));
c.Parameters["@.LastName"].Value ="Last";
c.Parameters.Add(newSqlParameter("@.FirstName",SqlDbType.VarChar, 100));
c.Parameters["@.FirstName"].Value ="First";
In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction.
But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN@.@.IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code?
Thanks for the help in advance. Happy 4th to all! : ) -- Curt
If you do
SELECT @.@.IDENTITY in your stored procedure, you can get the value with
c.ExecuteScalar();
I do not know if this will work with the RETURN statement,though
To use an output parameter, you need to add an output parameter to your SqlCommand prior to execution, like:
SqlParameter output =new SqlParameter("@.Return_Value", SqlDbType.VarChar, 100);output.Direction = ParameterDirection.Output;c.Parameters.Add(output);When you execute your SP, you need to set the value. Then you can get the new value from the parameter.
This article is based around using a SqlDataSource, but it has details on the SP updateshttp://aspnet.4guysfromrolla.com/articles/050207-1.aspx.
I'd also take a look herehttp://msdn2.microsoft.com/en-gb/library/ms190315.aspx at the difference between @.@.IDENTITY and SCOPE_IDENTITY (my guess is SCOPE_IDENTITY is what you really want).
Hope that helps.
Aaron
|||dt...
Yeah, when I added the "SELECT" to my SPROC, it worked fine - thanks for the tip. And yeah, as you suspected it didn't work for the "RETURN" - I had to add the "SELECT" to my SPROC. No problem though...
Athens, eh? Well, that goes back a couple of thousand years, doesn't it? ; ) Thanks for the help!
Curt
|||
however, if you want to get multiple values from your database you should use output parameters, just like agolden described. Moreover, if you use SQL Server 2005, you should use SCOPE_IDENTITY, as agolden also stated.