|
I have one doubt.
Can stored procedure retun bulk data through procedure itself.
I have studied in net that using OUTPUT parameters we can get multiple values from stored proceudre...is there any other way to get the data??
G. Satish
|
|
|
|
|
There are 2 ways to return data from a SP.
1) OUTPUT params
2) SELECT statements (of which you can have multiple select statements in the same sp)
|
|
|
|
|
|
SELECT distinct u.domain,u.username, count(d.downloadid) as DocumentCount
FROM primaryusertable AS u inner join tbluseraccessinfo a on(u.userid=a.userid and a.courntryid=51 )
right join download d on(u.userid = d.userid and CDate( d.downloadutctime) <now())
group by u.username, u.domain
it is not working giving syntax error.
i could not use inner and right join together in access database
|
|
|
|
|
Do you need a space between your on and your bracket?
|
|
|
|
|
SELECT DISTINCT u.domain, u.username, count(d.downloadid) AS DocumentCount
FROM primaryusertable AS u RIGHT JOIN download AS d ON u.userid=d.userid
WHERE CDate([d.downloadutctime])>= CDate(#2/2/2008#) And CDate([d.downloadutctime])<=CDate(#2/15/2008#)
GROUP BY u.username, u.domain;
by this query i am not getting date equal including
it is only > and < no =.
|
|
|
|
|
Not sure why that doesn't work, but you could always try doing this:
WHERE (CDate([d.downloadutctime])> CDate(#2/2/2008#) And CDate([d.downloadutctime])<CDate(#2/15/2008#)) OR
CDate([d.downloadutctime])= CDate(#2/2/2008#) OR CDate([d.downloadutctime])=CDate(#2/15/2008#)
|
|
|
|
|
Hi
I m using this procedure and want to insert data in multiple table.
First it insert data in ST_STUDENT which auto generates the 'Identity' value of 'Student_Code' which i want to use in other tables for 'Student_Code' field.
I m using this line for getting the last generated 'Student_Code' but it gives me this error
'Expects Parameter '@STUDENT_CODE" WHICH WAS NOT SUPPLIED"
what is wrong in my procedure.
Kindly Tell me
alter Procedure SP_INSERT_STUDENT_COMPLETEINFO_BYAGENT
@STUDENT_CODE varchar(4),
@FIRST_NAME VARCHAR(50),
@LAST_NAME VARCHAR(50),
@EMAIL_ADDRESS VARCHAR(50),
@HOME_PHONE VARCHAR(50),
@HOMEEXT VARCHAR(10),
@OFFICE_PHONE VARCHAR(50),
@OFFICEEXT VARCHAR(50),
@MOBILE VARCHAR(50),
@HIGHEST_EDUCATION_LEVEL VARCHAR(50),
@DOB DATETime,
@COUNTRY_OF_RESIDENCE VARCHAR(50),
@COUNTRY_OF_NATIONALITY VARCHAR(50),
@US_SECURITY_NUMBER VARCHAR(50),
@GENDER VARCHAR(20),
@MARITAL_STATUS VARCHAR(20),
@ETHNIC_GROUP VARCHAR(20),
AS
INSERT INTO ST_STUDENT(FIRST_NAME,LAST_NAME,EMAIL_ADDRESS,HOME_PHONE,HOMEEXT,OFFICE_PHONE,OFFICEEXT,MOBILE)
VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL_ADDRESS,@HOME_PHONE,@HOMEEXT,@OFFICE_PHONE,@OFFICEEXT,@MOBILE)
SET @STUDENT_CODE =(SELECT @@IDENTITY)
Insert into ST_STUDENTBIODATA(HIGHEST_EDUCATION_LEVEL,DOB,COUNTRY_OF_RESIDENCE,COUNTRY_OF_NATIONALITY,
US_SECURITY_NUMBER,GENDER,MARITAL_STATUS,ETHNIC_GROUP,STUDENT_CODE)
VALUES(@HIGHEST_EDUCATION_LEVEL,@DOB,@COUNTRY_OF_RESIDENCE,@COUNTRY_OF_NATIONALITY,@US_SECURITY_NUMBER,@GENDER,@MARITAL_STATUS,
@ETHNIC_GROUP,@STUDENT_CODE)
GO
Regards
|
|
|
|
|
You have a comma just before your AS.
|
|
|
|
|
has anyone here used DB2Connect to call a DB2 stored procedure. I'm having trouble registering the input/output parameters.
here is my code
Dim conn As New DB2Connection("DATABASE=DB2DSST;UID=c132;PWD=*******;")
conn.Open()
Dim trans As DB2Transaction = conn.BeginTransaction()
Dim cmd As DB2Command = conn.CreateCommand()
Dim procName As String = "MG1006SP"
Dim procCall As String = "CALL MG1006SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
cmd.Transaction = trans
cmd.CommandType = CommandType.Text
cmd.CommandText = procCall
' Register input-output and output parameters for the DB2Command
cmd.Parameters.Add("DB2_ID", "DSST")
cmd.Parameters.Add("GRP_ID", "19 ")
cmd.Parameters.Add("LN1_ADDR", DB2Type.Char, 55)
cmd.Parameters.Add("LN2_ADDR", DB2Type.Char, 55)
cmd.Parameters.Add("CITY_NM", DB2Type.Char, 30)
cmd.Parameters.Add("ST_CD", DB2Type.Char, 2)
cmd.Parameters.Add("ZIP5_CD", DB2Type.Char, 5)
cmd.Parameters.Add("ZIP4_CD", DB2Type.Char, 4)
cmd.Parameters.Add("INFO_CHG_DT", DB2Type.Date, 4)
cmd.Parameters.Add("AIBRETRN", DB2Type.Integer, 4)
cmd.Parameters.Add("AIBREASN", DB2Type.Integer, 4)
cmd.Parameters.Add("SQLCODE", DB2Type.Integer, 4)
cmd.Parameters.Add("SQLSTATE", DB2Type.Char, 5)
cmd.Parameters.Add("SQLMESSAGE", DB2Type.VarChar, 1320)
cmd.Parameters.Add("APPL_RTN_CD", DB2Type.Char, 5)
cmd.Parameters.Add("APPL_MESSAGE", DB2Type.VarChar, 500)
' Call the stored procedure
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
|
|
|
|
|
We use DataDirect Connect for .NET[^] to connect to DB2.
We simply set the CommandText property to the name of the stored procedure and set CommandType to CommandType.StoredProcedure.
For an example of using IBM's data provider - which appears to use the same scheme - see the SpClient.vb sample[^].
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
HI,
Can any one suggest me how to do the following.
I am having a two table, table1,table 2. table 2 fkey depends on table 1 primary key(auto generated). My doubt is if i copy a row with in table1, a new primary key is generated,how to retrive this new auto generated primary key, because using this new primary key i have to copy the rows with in table 2. for eg:
table 1:
id name
1 aaa
2 aaa
table 2:
id fid age
1 1 23
2 2 23
please suggest me
thanks in advance
Know is Drop, Unknown is Ocean
|
|
|
|
|
You haven't mentioned which Database/version you use. If you're using SQL Server 2005, you can get the last inserted identity value using Scope_Identity() function.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hi,
Thanks for your reply
we are using SQL 2000. is there any other alternate?
please suggest me
Know is Drop, Unknown is Ocean
|
|
|
|
|
SCOPE_IDENTITY() works in SQL 2000.
You should really consider reading documentation and trying things for yourself before asking questions on forums.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I am having some problems calling stored functions from my c# code, sometimes it works and sometimes it doesn't. When it doesnt I get an error message
"parameter '?9' not found in the collection"
I dont have a parameter with the name '9' in the function I am trying to call! it does it when I call some functions but not others. Another function I call it says it can't find parameter '?50' again I don't have such a parameter
Anyone got any ideas? I have a feeling I'm missing something obvious
///
/// This method executes a stored function
///
/// <param name="functionName" />Name of the function
/// <param name="parameters" />A list of parameters to be passed into the function
/// <returns>The result of the function
public object executeFunction(string functionName, List<parameter> parameters)
{
//this is the variable that will contain the return value from the function
DbParameter returnParam = factory.CreateParameter();
try
{
conn.Open(); //open the connection
//start a transaction
DbTransaction trans;
trans = conn.BeginTransaction();
DbCommand command = factory.CreateCommand(); //create a command
command.Transaction = trans; //set the transaction for the command
command.CommandText = functionName; //set the command text to the name if the function
command.Connection = conn; //set the commands connection
command.CommandType = CommandType.StoredProcedure; //set the type to stored procedure
//add each of the input parameters to the command
foreach (Parameter a in parameters)
{
DbParameter param = factory.CreateParameter();
param.ParameterName = a.name;
param.Value = a.value;
command.Parameters.Add(param);
}
//initialise the return parameter and add it to the command
returnParam.ParameterName = "?rv";
command.Parameters.Add(returnParam);
returnParam.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery(); //execute the function
trans.Commit(); //finish the transaction
conn.Close(); //close the connection
}
catch (Exception ex)
{
throw new Exception("the following exception was thrown: \n" + ex.Message + "\n\nyou probably have an error in your parameters");
}
finally
{
conn.Close();
}
return returnParam.Value; //return the result
}
|
|
|
|
|
Hi,
I have the same kind of error with MySQL and the ADO .Net connector. When i call the stored procedure from my C# program, I get an exception telling me that a parameter '@,' is missing in my parameter collection and of course i don't have such a parameter in the definition of the stored procedure.
Does anyone have an idea about this ?
Thanks,
Stephane
|
|
|
|
|
Hi all,
can you please let me know how to recover a database script?
Thank you
modified on Monday, February 18, 2008 9:28 AM
|
|
|
|
|
Google: SQL decryptor
There are many products that handle this task.
|
|
|
|
|
Hi all,
I have designed an .rdl report in my server(SSRS).
I have a client program which will render this report and export.
How do I pass connection parameters to the report dynamically?
I tried using report parameters. I can pass only server name and database name.
I want to pass User id and password too.
Please help
Thanks in advance
Senthil
|
|
|
|
|
for user name,passward,servername, u can set using connection string....
and just using dataset....try this
Rajendran.AL
|
|
|
|
|
is there any way to set the password while somebody try to access the sql server 2000 database .Like in the Mirosoft Access database , i can set the security , to prevent the malacious access.
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
modified on Monday, February 18, 2008 1:28 AM
|
|
|
|
|
Yes. SQL Server has a good security system. You can create users and set passwords and access rights for them.
|
|
|
|
|
actually it is an intranet application i am working now-a-days.Problem is sometimes an unklnown person comes to server and open the enterprise manager , this is what i want to prevent , i want to prevet executing the sql server enterprise manager.
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Sonia Gupta wrote: i want to prevet executing the sql server enterprise manager.
You can't prevent enterprise manager from opening. You can create new users and give permission to access the database. Deny permission to access the database for all other users. This will prevent other person accessing database.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
