|
acording to the SQLServer BOL, that can't be done, variables used within the query being executed by sp_executesql are local to the query therefore they can't be accessed by the query that executed it in the first place
i hope i made myself clear with that 
|
|
|
|
|
Actually you can do it. 
What you are missing is the that it needs to be an nvarchar.
this is from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q262499
\CREATE PROCEDURE Myproc
@parm varchar(10),
@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT
AS
SELECT @parm1OUT='parm 1' + @parm
SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
@parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT
SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc
Barbara, MCP
|
|
|
|
|
WOW! Works great! Thanks alot....I thought I was a T-SQL GURU but I acquiesce the title to you.
~LizardWiz()
|
|
|
|
|
thanks for pointing that out, i made some test myself and by lookign at your code i see that i was missing the OUTPUT statement in the parameters that were returning a value
|
|
|
|
|
kinda nasty... but check this out
http://www.sqlmag.com/Forums/messageview.cfm?catid=22&threadid=722
|
|
|
|
|
plz i need the way how to creat table by sql statmen . my project is to read data from xml put it in databas i want to use sql server , how i can creat table dynamicly
hi
|
|
|
|
|
samo7a wrote:
creat table by sql statmen
Use CREATE TABLE SQL command and run it with SQLCommand if you use .net application.
samo7a wrote:
my project is to read data from xml put it in databas i want to use sql server
Write a stored procedure and pass a text of your xml to it, in that procedure use EXEC sp_xml_preparedocumnet and OPENXML to update your table . If you asearch for these keywords in SQLServer online book you can find a VB sample for it.
Mazy
No sig. available now.
|
|
|
|
|
I have a question about using ExecutiveNonQuery() in C# (using Access).
The following code would return 0, when I specify "@myMax".Value = 1 (see below).
However, if I changed @myMax to a constant (ie. not using a variable), everything worked fine and it returned 1 as the result.
The problem is that since it doesn't throw an exception, and only returns 0, I have no idea what's wrong with the "Update" SQL statement. Thank you and appreciate any feedbacks!!
<br />
OleDbCommand myUpdate = new OleDbCommand("UPDATE Product SET Prod_max = @myMax, Prod_desc = @myNote WHERE Prod_id = @myID", oleDbConnection1);<br />
<br />
myUpdate.Parameters.Add("@myNotes", OleDbType.LongVarWChar);<br />
myUpdate.Parameters.Add("@myID", OleDbType.VarChar);<br />
myUpdate.Parameters.Add("@myMax", OleDbType.SmallInt);<br />
<br />
myUpdate.Parameters["@myNotes"].Value = Notestxtbox.Text.ToString();<br />
myUpdate.Parameters["@myID"].Value = ProdIDtxtbox.Text.ToString();<br />
myUpdate.Parameters["@myMax"].Value = 1;<br />
<br />
try<br />
{<br />
textBox1.Text = myUpdate.ExecuteNonQuery().ToString();<br />
}<br />
catch (Exception ex)<br />
{<br />
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);<br />
}
|
|
|
|
|
HI,
Can someone help me with following SQL?
I have an array of about 200 string IDs in my program (C++).
I need to delete these IDs from a database table.
I would like to use the most efficent method of doing this.
Any suggestions on the best SQL query?
Thanks
Jeremy Pullicino
C++ Developer
Homepage
|
|
|
|
|
If your id columns is numeric, try this...
DELETE FROM [Table] WHERE [ID_Column] IN (1,2,3,...,n)
If it's string...
DELETE FROM [Table] WHERE [ID_Column] IN ('1','2','3',...,'n)
Free your mind...
|
|
|
|
|
Use the In Clause to execute the query.
DELETE FROM [TableName]
WHERE ID IN (1,2,3,4,5,6,etc...200)
I know Sysbase 11 had a limit of 255 values in the In Clause. However, I do not think SQL Server or Access has that limitation.
Michael
|
|
|
|
|
Otherday I installed SQL Server on my personal computer, but now I forgot the password for 'sa' userid. How to retrive it? or How can I reset it?
Promise only what you can do. And then deliver more than what you promised.
This signature was created by "Code Project Quoter".
|
|
|
|
|
Do you have Enterprise Manger? As much as I think it logged in automaticlly and in Security-->Login node you can change password(I don't remembr if it asked old pass),if it asks, you can backup from your databases there and uninstall-install SQLServer again.
Mazy
No sig. available now.
|
|
|
|
|
Thanks for your tip.  It didn't ask for the old password.
Promise only what you can do. And then deliver more than what you promised.
This signature was created by "Code Project Quoter".
|
|
|
|
|
Hi
I have a question of database.
If you have some solutions, please tell me.
Now I'm developing tool getting and filing trigger_body from database using ODP.NET. As a ODP.NET case, I can set fetch size value, so I set value. But Trigger_body I'd like to get is heavy, so I couldn't get it all( I could get 80%).
I investigated ODP.NET, I noticed there is maximum fetch size is exist(32KB).
So I am looking for how to get it using .NET Framework and etc.
I usually use Toad, this have a function display trigger_body. So I tried to display trigger_body over 32KB. I got it. I noticed that how to get trigger_body over 32KB was exist using program.
thank you in advance for your kindness.
best regards,
yu-yu
|
|
|
|
|
The trigger body is saved in a LONG field, I guess you are using the LONG Type included in the ODP.NET.
Can you send the code you are using to extract the trigger_body from DB ?
Free your mind...
|
|
|
|
|
hi,
thank you for your mail.
The code getting trigger_body from DB using ODP.NET is the following.
------------------------------------------------------------
string sSQL = "select trigger_body from user_triggers where trigger_name='" + sName + "'";
OracleCommand com = conn.CreateCommand();
com.CommandText = sSQL;
com.InitialLONGFetchSize = 32767; // Maxvalue
OracleDataReader reader = com.ExecuteReader();
string sResultString = string.Empty;
reader.Read();
if (!reader.IsDBNull(0))
{
sResultString = reader.GetOracleString(0);
}
reader.Close();
-------------------------------------------------------------
best regards,
yu-yu
|
|
|
|
|
Try using this approach...
First, on the DB run this to create a Stored Procedure
CREATE OR REPLACE
PROCEDURE get_trigger(iName IN VARCHAR2, iBody OUT LONG) IS
BEGIN
SELECT TRIGGER_BODY INTO iBody FROM USER_TRIGGERS WHERE TRIGGER_NAME=iName;
END;
/
Then try this...
string GetTriggerBody()
{
OracleCommand vCommand;
OracleParameter vParameter;
OracleString vString;
string vTriggerBody;
vCommand = new OracleCommand();
vCommand.Connection = this.mConn;
vCommand.CommandText = "get_trigger";
vCommand.CommandType = CommandType.StoredProcedure;
vCommand.Parameters.Add(new OracleParameter("iName", OracleDbType.Varchar2, ParameterDirection.Input));
vCommand.Parameters["iName"].Value = "TR_AC_DOCUMENTOS_FECHAFIN";
vParameter = new OracleParameter("iBody", OracleDbType.Long);
vParameter.Direction = ParameterDirection.Output;
vParameter.Size = 80000;
command1.Parameters.Add(vParameter);
command1.ExecuteNonQuery();
vString = (Oracle.DataAccess.Types.OracleString)vParameter.Value;
vTriggerBody = vString.Value;
return vTriggerBody;
}
Hope this helps...
Free your mind...
|
|
|
|
|
hi,
I tried to do it, but I couldn't.
I have a question.
command1.Parameters.Add(vParameter);
command1.ExecuteNonQuery();
what is "command1" ?
I thought it's vCommand, so I changed it.
but Error occuered at pl/sql Ora-06502
ORA-06512: 1 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(
Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String procedure, String[] args)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String[] args)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at ConsoleApplication2.Class1.test() in d:\testing\consoleapplication2\class1
.cs:line 162
at ConsoleApplication2.Class1.Main(String[] args) in d:\testing\consoleapplication2\class1.cs:line 37
best regards,
yu-yu
|
|
|
|
|
Sorry, it's vCommand, not command1.
The Oracle Error happens because the trigger you are trying to get doesn't exist.
Free your mind...
|
|
|
|
|
Really?
Thanks, I will try again.
After I will announce the result for you.
Best Regards,
yu-yu
|
|
|
|
|
Hi
I tried it, but same situation occuered.
The test code is the following.
----------------------------------------------------------
OracleConnection conn = new OracleConnection("Data Source=hora9;user id=aaa;password=aaa");
conn.Open();
OracleTransaction trn = conn.BeginTransaction();
OracleCommand vCommand = new OracleCommand();
OracleParameter vParameter;
OracleString vString;
string vTriggerBody;
vCommand.Connection = conn;
vCommand.CommandText = "get_trigger";
vCommand.CommandType = CommandType.StoredProcedure;
vCommand.Parameters.Add(new OracleParameter("iName", OracleDbType.Varchar2, ParameterDirection.Input));
vCommand.Parameters["iName"].Value = "T_T1URIA";
vParameter = new OracleParameter("iBody", OracleDbType.Long);
vParameter.Direction = ParameterDirection.Output;
vParameter.Size = 80000;
vCommand.Parameters.Add(vParameter);
vCommand.ExecuteNonQuery();
vString
= (Oracle.DataAccess.Types.OracleString)vParameter.Value;
vTriggerBody = vString.Value;
---------------------------------------------------------
I will try again,
If you see the code and notice some cause, please tell me.
Best regards,
yu-yu
|
|
|
|
|
What is the Oracle Error you are receiving ?
Usually, Oracle errors has this pattern.. ORA-#####. Need the number to check what is happening.
Free your mind...
|
|
|
|
|
Hi
I found the error cause.
It is the volume of trigger_body.
The procedure parameter of trigger_body is LONG.
This style miss, I think.
Because I tried to get short volume trigger_body, succeeded it.
But I think about it. Do you know Toad ? This is database tool.
As a this tool case, this can get heavy trigger_body without procedure.
Best regards,
yu-yu
|
|
|
|
|
Yes, I've used Toad. But I really prefer SQL Navigator.
This style miss, I think.
Because I tried to get short volume trigger_body, succeeded it.
I don't understand what you are saying here friend. Sorry 
Free your mind...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
