As in some of the databases stored procedures can be written in java. Java stored procedures run into inbuilt JVM of database.Does SQL server provides any support for writing stored procedures in java.Is there any inbuilt JVM in SQL server.
View 1 RepliesHello,
I want to call a stored procedure (SQL SErver 2000) from JAVA. The SP contains a temporary Table. At the end of the SP there is a Select like "Select * from #tmp".
Now I get no Resultset in my JAVA - Application. When i do a normal Select on an Table I get a Resultset.
I need the temp. Table in this SP. How can I get the Result in JAVA. It is urgent for me.
Thanks in advance.
Hi,
Are there any plans for Compact Edition to support Stored Procedures in the future? If so, is there an approximate timescale on release (ie. months, quarters, years)?
Thanks,
Aaron
I have successfully created CLR stored procures using c# and SQL Server 2005. Now I want to use the CLR stored procedures with some ASP.NET controls. I can add the CLR stored procedure to the dataset designer and have it add a new table adapter but it does not list the columns in the designer view. This forces me to code the column references manually into the controls (tried: DropDownList, GridView).
I can deal with adding the columns manually for controls using a CLR stored procedure, but I want to make sure I was not missing something, am I?
BTW: TSQL stored procedures work as expected (show columns in the dataset designer)
thanks!
-pieter
Hello,Does anyone know if SQL Server supports invoking Java methods from aStored Procedure or even a Trigger. For example, it looks like oracleprovides this capability based on the article athttp://www.cs.umbc.edu/help/oracle8...86/04_call2.htm. Itlooks like for a Trigger it uses a SP as an in-between. Any insightinto this would be greatly appreciated.Thanks,--Willard
View 1 Replies View RelatedI want to write a Java User-defined aggregate (UDA). Shall I use J#?
I converted the C# example given in books online to J#. I am getting this error (as well as whole bunch of warning when I create the assembly.)
Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
btw, I have the use unsafe assembly options when creating the assembly otherwise I get this error:
Msg 6265, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because type "com.ms.vjsharp.cor.COMUtils" in safe assembly "vjscor" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.
Warning: The Microsoft .Net frameworks assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
From the warning, I can tell the J# UDA is not tested.
Can someone confirm whether J# UDA is supported or not?
Thanks!
-------------------------
Here is my code:
ALTER DATABASE MEDIO set TRUSTWORTHY ON
CREATE ASSEMBLY MyAgg FROM 'C:codeconsolePriceUDAobjDebugPriceUDA.dll' WITH PERMISSION_SET = unsafe
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME MyAgg.Concatenate
GO
---------------------------------------
import System.*;
import System.Data.*;
import Microsoft.SqlServer.Server.*;
import System.Data.SqlTypes.*;
import System.IO.*;
import System.Text.*;
/** @attribute Serializable() */
/** @attribute SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
*/
public class Concatenate implements IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.get_IsNull())
{
return;
}
this.intermediateResult.Append(value.get_Value()).Append(',');
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
String output = String.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.get_Length() > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.get_Length() - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHow to encrypt the java application code using the 'with encryption' clause from sql server stored procedure or function.
View 3 Replies View RelatedHey all,
I've got a question and after doing some research I've found only a vague reference but no clear answer.
I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).
Can someone point me to a good source for executing java applications from a stored procedure?
Thanks in advance ...
tam
I'm trying to use a servlet to process a form, then send that data toan SQL server stored procedure. I'm using the WebLogic 8 App. server.I am able to retrieve database information, so I know my applicationserver can talk to the database.I've determined the failure occurs when the the following statement isexecuted: cstmt.execute(); (due to the failure of println statementsplaced afterwards). I get the following error after trying to executethe stored procedure call:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find storedprocedure 'insertTheForm'The username and password i'm using to connect is a Windows user withadmin rights. It is also associated with the Odbc connection--and ofcourse is a database user..with full rights. I have executablepermissions on the stored procedure set up as well. I did a microsoftrecommended registry fix as well (for a previouserror:http://support.microsoft.com/defaul...;en-us;Q238971).Am I missing something? I posted my servlet code below.Thanks for any help!DineshformHandlingServlet.class-------------------------package showme;/** formHandlingServlet.java** Created on July 6, 2003, 7:01 PM*/import javax.servlet.*;import javax.servlet.http.*;import java.io.*;import java.sql.*;import java.text.DateFormat;/**** @author Administrator*/public class formHandlingServlet extends HttpServlet {private static final String email1 = "email";private static final String password1 = "password1";private static final String password2 = "password2";private static final String displayname = "displayname";Connection dbConn = null;// create a persistent conneciton to the SQL serverpublic void init() throws ServletException{String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";String dbURL = "jdbc:odbc:Con2";String usernameDbConn = "dinesh";String passwordDbConn = "werty6969";try{Class.forName(jdbcDriver).newInstance();dbConn = DriverManager.getConnection(dbURL, usernameDbConn,passwordDbConn);}catch (ClassNotFoundException e){throw new UnavailableException("jdbc driver not found:" + dbURL);}catch (SQLException e){throw new UnavailableException("error: " + e);}catch (Exception e){throw new UnavailableException("error: " +e);}}public void doPost(HttpServletRequest request, HttpServletResponseresponse) throws ServletException, IOException{response.setContentType("text/plain");PrintWriter out = response.getWriter();//extract parameter information from register.jspString email1 = request.getParameter("email1");String password1 = request.getParameter("password1");String password2 = request.getParameter("password2");String displayname = request.getParameter("displayname");try{//make a callable statement for a stored procedure.//It has four parametersCallableStatement cstmt = dbConn.prepareCall("{call insertTheForm(?, ?, ?, ?)}");//set the values of the stored procedure's input parametersout.println("calling stored procedure . . .");cstmt.setString(1, email1);cstmt.setString(2, password1);cstmt.setString(3, password2);cstmt.setString(4, displayname);//now that the input parameters are set, we can proceed to execute theinsertTheForm stored procedurecstmt.execute();out.println("stored procedure executed");}catch (SQLException e){throw new UnavailableException("error: " + e);}}}
View 3 Replies View Relatedin my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View 2 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies View RelatedIS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example.....
View 3 Replies View RelatedHi,
Everytime after I restore any database on the sql server 6.5, I have to recompile all the stored procedures in that database.Is there any method to overcome this problem?
Thanks,
Manu.
Trying to run a SQL Server 6.5 stored procedure via ADO 2.0 in Visual Basic 6. The stored procedure moves roughly 10000 records from one table to another within the same database. The procedure works just fine when run from a SQL editor such as I/SQL, but only transfers a few hundred records when it is executed from a VB application using ADO 2.0.
The guilty code can be seen below. Any thoughts?
Public cnSQL As New ADODB.Connection
Public qry As New ADODB.Command
With cnSQL
.ConnectionString = ConnectStringSQL
.ConnectionTimeout = 20
.Open
End With
Set qry.ActiveConnection = cnSQL
qry.CommandType = adCmdStoredProc
qry.CommandTimeout = 120
qry.CommandText = "sp_VGInsertBOMStructure"
qry.Execute
Peter
I have a stored procedure on SQL/Server 2000 that inserts into a #temptable. When I execute it from Query Analyzer it works fine.
When I execute it from an ASP using ADO it gives me an Open Error message. When I comment out the
#temptable the ASP works fine. Can someone shed some light on this? Do I have to do an ADO opent on the
#temptable?
Is there any application which can tell you the procedure called within a procedure.
View 2 Replies View RelatedI come from a MySQL background, and have recently began migrating to SQL Server.
I can't, for the life of me, find an example of a stored procedure that does the following in SQL Server:
- Multi-lined
- Contains both input and output parameters
- Sets one of the output parameters within the stored procedure
Can anyone provide some insight? I.e. Why does the following not work?
IF NOT EXISTS (SELECT * FROM syscomments WHERE id = object_id('usp_test'))
BEGIN
CREATE PROCEDURE "usp_test"
(@num1 INT, @num2 INT, @result INT OUTPUT)
AS
BEGIN
DECLARE newValue INT;
SET @newValue = num1 + num2;
SET @result = @newValue;
END;
END;
I get these errors (I'm using the Management Studio Express)
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 155, Level 15, State 2, Line 7
'INT' is not a recognized CURSOR option.
Msg 137, Level 15, State 1, Line 8
Must declare the scalar variable "@newValue".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@newValue".
I just installed sql server 2005 on my machine. I've been using Access for some time and have created my stored procedures easily in it. However, this sql server 2005 is so complicated. In Access, all I had to do was "Create a New Query", write my sql statement, name and save it. However, I am having so much trouble with this sql server. For example, when I create a stored procedure in sql server, it has a .sql extension. I am assuming the stored procedure is a file. However, after saving it, I expand my database, expand programmability, and expand stored procedures, yet my new stored procedure is not there. It's placed in a file called projects. I need to access this stored procedure from code, but in visual studio, I get an error message, "Cannot find stored procedure sp_Roster". Here is the code I used to access my stored procedure from vb.net. I thought it would work but it didntDim comm As New SqlCommandDim strsql As StringDim strconn As String strsql = "sp_Roster" strconn = "server=Home; user=sa; pwd=juwar74; database=Book;" With comm .Connection = New SqlConnection(strconn) .CommandText = strsql .CommandType = CommandType.StoredProcedure With .Parameters.Add("TeacherID", SqlDbType.Char) .Value = "DawsMark@aol.com" End With With .Parameters.Add("ClassID", SqlDbType.Int) .Value = CInt(classid) End With With .Parameters.Add("sID", SqlDbType.Int) .Value = ssID End With With .Parameters.Add("sLastName", SqlDbType.Char) .Value = lastname End With With .Parameters.Add("sFirstName", SqlDbType.Char) .Value = firstname End With With .Parameters.Add("sMiddleName", SqlDbType.Char) .Value = middlename End With With .Parameters.Add("Student", SqlDbType.Char) .Value = fullname End With With .Parameters.Add("Password", SqlDbType.Char) .Value = password End With .Connection.Open() .ExecuteNonQuery() With comm.Connection If .State = ConnectionState.Open Then .Close() End If End With End With Here is my procedure that I created and that was saved as sp_Roster.sql in sql server CREATE PROCEDURE sp_Roster ASBEGIN SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@TeacherID, @ClassID, @sID, @sLastName, @sFirstName, @sMiddleName, @Student, @Password)ENDGO Why isn't ado.net finding it in sql server. Is the sqlstr correct?
View 2 Replies View Related In a previous post, someone helped me with creating stored procedures, and I am grateful because I am transitioning from the Access World.
Anyway, I get an error at .ExecuteNonQuery in visual studio 2005 when I run the following code:
Dim strsql As String Dim strconn As String strsql = "sp_Roster" strconn = "server=xxxx; user=xxxx; pwd=xxxx; database=xxxx;" With comm .Connection = New SqlConnection(strconn) .CommandText = strsql .CommandType = CommandType.StoredProcedure With .Parameters.Add("TeacherID", SqlDbType.Char) .Value = "DawsMark@aol.com" End With With .Parameters.Add("ClassID", SqlDbType.Int) .Value = classid End With With .Parameters.Add("sID", SqlDbType.Int) .Value = ssID End With With .Parameters.Add("sLastName", SqlDbType.Char) .Value = lastname End With With .Parameters.Add("sFirstName", SqlDbType.Char) .Value = firstname End With With .Parameters.Add("sMiddleName", SqlDbType.Char) .Value = middlename End With With .Parameters.Add("Student", SqlDbType.Char) .Value = fullname End With With .Parameters.Add("Password", SqlDbType.Char) .Value = password End With .Connection.Open() .ExecuteNonQuery() With comm.Connection If .State = ConnectionState.Open Then .Close() End If End With End With
The error was: Error converting data type char to int.
The stored procedure in sql server was as follows
CREATE PROCEDURE sp_Roster -- Add the parameters for the stored procedure here@TeacherID varchar(50),@ClassID int,@sID int,@sLastName varchar(50),@sFirstName varchar(50),@sMiddleName varchar(50),@Student varchar(50),@Password varchar(50)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@TeacherID, @ClassID, @sID, @sLastName, @sFirstName, @sMiddleName, @Student, @Password)ENDGO
The error also says "sqlexception was unhandled by user code.
This is strange because this code worked perfectly when connecting to Access and when I used oledb.
So how is it the code's problem? Is the stored procedure causing the error or the code. Can someone please help. thanks.
<Edited by Dinakar Nethi>
Please mask your useird/pwd info in the connection string when posting to a public forum like this
</Edit>
In Oracle database we can group our stored procedures in packets. It is very useful when we are creating big business solutions with hundreds stored procedures. Can we do something like this in SQL server?
View 2 Replies View RelatedI don't see the option to debug a stored procedure. I right click on
the procedure and should'nt it appear in the properties window. Do I
need to install some extra component to get the debugger.
How do you transfer stored procedures from SQL Express 2005 to MS SQL 200 ? I am using the Personal home page starter kit and also need to know if I have to attach and detach the database to my ISP's server?thanks,
View 2 Replies View Relatedhi, anyone can guide me on how to create stored procedures in MS SQL Server?
View 1 Replies View RelatedAbout a year ago we inherited a SQL server (7.0) from another division of our company. The time has come to migrate the SQL Server functionality onto a new machine. Before I do this though, I need to figure out what the initial installation configuration was so I can set up the new server with the same specs. I am looking for a system stored procedure(s) that can tell me the following:
Case sensitivity, accent sensitivity, SQL build (SP), character set, etc. Basically I need to know what checkboxes were ticked during the initial set up of the server.
Any help would be greatly appreciated. TIA.
Hi everyone,
Is versioning SQL Server 7.0 stored procedures with VSS possible? If so,
how? How are people versioning stored procedures out there?
Thanks in advance...
bth@prucka.com
Hi,
Does anyone know how to export all stored procedures attached to a database (MS SQL Server) into a sql script. Please help.
Thanks
I thought this problem would go away over the Christmas holiday, butof course it did not. I'm trying to write a stored procedureincorporating wildcards, so I can search for variations. Example, ifname 'Smith' is submitted, sproc should retrieve all recordscontaining 'John Smith', 'Zenia Smith', 'Smithfield & Co.' You get theidea.Using SQL Query Analyzer, the queryselect * from filewhere name like '%smith%'works like a charm.But if I write a stored procedure declaring the variable @name andusing a where clause 'where name like '%@name%'', I get zero results.The query doesn't bomb. It just doesn't produce anything - even thoughI know there are records that meet the criteria.Any ideas? Or are sprocs and wildcards incompatible?
View 1 Replies View Related
I have a simple stored procedure:
create procedure sp_testres
@mult1 int,
@mult2 int,
@result int output
as
select @result = (10*@mult1) + @mult2
go
When I call it
declare @result int
exec sp_testres 5, 6, @result output
print @result
(Result is correctly shown as 56).
I then in C# wrote the following:
m_cmd.CommandText = "sp_testres";
SqlParameter param2 = new SqlParameter("@mult2", SqlDbType.Int);
param2.Value = 6;
SqlParameter param1 = new SqlParameter("@mult1", SqlDbType.Int);
param1.Value = 5;
SqlParameter param3 = new SqlParameter("@result", SqlDbType.Int);
param3.Direction = ParameterDirection.Output;
m_cmd.CommandType = CommandType.StoredProcedure;
m_cmd.Parameters.Add(param1);
m_cmd.Parameters.Add(param2);
m_cmd.Parameters.Add(param3);
m_cmd.ExecuteNonQuery();
This works and param3.Value holds the result value.
I also notice that I can supply the parameters in any order, and things work fine.
What I want to know is: can I call the stored procedure with parameters, where I haven't supplied the parameter name, and just rely on the parameter order matching instead?
This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the natureof a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highlyspecialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by thesavings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of yourapplication to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this: CREATE PROCEDURE ProcName @Param int /* ... other parameters */ WITH RECOMPILE AS /* ... procedure code follows */
If we omit "WITH RECOMPILE", what will be the consequence? Thanks
Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn