|
if you need return output parameter from stored procedure you must to set word out beside parameter
for example
declare @Time1 datetime
declare @time2 datetime
set @Time1 = '1/1/2001'--if this output parameter set out beside it
set @Time2 = '1/1/2001'
==============================================
exec dbo.Usp_Rpt_Proc1 @time1 out ,@Time2 out
but this solve must used if the two output parameter
CREATE PROCEDURE dbo.Usp_Rpt_Proc1 (@StartDate DateTime output,@EndDate DateTime output)
===============================================
exec dbo.Usp_Rpt_Proc1 @time1 ,@Time2 out
but this solve must used if the one output parameter
CREATE PROCEDURE dbo.Usp_Rpt_Proc1 (@StartDate DateTime ,@EndDate DateTime output)
====================================================
exec dbo.Usp_Rpt_Proc1 @time1 ,@Time2 ,@output out
but this solve must used if the one output parameter
CREATE PROCEDURE dbo.Usp_Rpt_Proc1 (@StartDate DateTime ,@EndDate DateTime ,@output datatype output)
if you need any thing reply
with regards
Rami Abd Alhalim
|
|
|
|
|
Hi Guru's
I created a web application which is intended for multi users, I come up with an idea that every user who sign ups will have their own sets of tables instead of one row on the table with relations to other table, I'm just wondering whats the limit count of tables can a database (MSSQL2005) hold and is it a good advice to propagate table.
regards
Dom
|
|
|
|
|
dont know the limit but that sounds like a horrible idea! GOOD LUCK!
|
|
|
|
|
|
Hi,
In MS SQL 2005 the ID column of Sysobjects table is Int. It means that you can create Max of integer. The integer datatype can hold value up to 2^31.
|
|
|
|
|
Thanks for the nice feed from you guys, I guess I'm going to push it through though just to see how it goes when hits a thousand sign ups (day dream mode). I'll let you know when things start to mash up (couple of years I guess).
regards
Dom
|
|
|
|
|
I have created one table with DATETIME datafield, but its allowing me to
store date only in 'mm/dd/yyyy' format so, how do i change the format of this field to 'dd/mm/yyyy'
I hope you understood the problem....
Thanks. 
Prashant C
|
|
|
|
|
Just copy pasting online help ....
SET DATEFORMAT
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.
Syntax
SET DATEFORMAT { format | @format_var }
Arguments
format | @format_var
Is the order of the dateparts. Can be either Unicode or DBCS converted to Unicode. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.
Remarks
This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.
The setting of SET DATEFORMAT is set at execute or run time and not at parse time.
Permissions
SET DATEFORMAT permissions default to all users.
Examples
This example uses different date formats to handle date strings in different formats.
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
GO
SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '98/31/12'
SELECT @datevar
GO
SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '98/12/31'
SELECT @datevar
GO
Regards
KP
|
|
|
|
|
When I do the following SQL command to fill a GridView:
SELECT * FROM Owners WHERE OwnerID = 7 OR OwnerID = 3 OR OwnerID = 21 .....
I want the rows to be in the same order as in the WHERE section (7, 3, 21...)
Is this possible?
Jim
|
|
|
|
|
SELECT * FROM Owners WHERE OwnerID = 7
union
SELECT * FROM Owners WHERE OwnerID = 3
union
SELECT * FROM Owners WHERE OwnerID = 21
|
|
|
|
|
Thanks for the help. But I didn't get consistent results until I changed 'union' to 'union all'.
Jim
|
|
|
|
|
Hi all,
I have a datetime column on my table( ADEDATE type datetime)
Select id, ADEDATE from dbo.Tablename
I would like to get the day of the week for the result of the selecet statement.
the result of the select statement will be similar to ..
12,Monday
13,sunday
14,saturday
Which function shall i use for this task?
Thanks
|
|
|
|
|
SELECT DATENAME(dw, GETDATE())
Dave Evans
|
|
|
|
|
Thanks Dave.
It worked.
Keep the good work.
|
|
|
|
|
I need to access a billing webservice from SQL. I createde a new c# class project and made a web refrence to the web service "ProdBilling".
Here is the code of my assembly
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace PaymentProc
{
public class PaymentProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ChargeCard(int account, int amount)
{
string Response;
ProdBilling.Service serv = new ProdBilling.Service();
Response = serv.ChargeCard(account, amount);
SqlContext.Pipe.Send(Response);
}
}
}
I then ran WSDL
wsdl /o:PaymentProc.cs /n:PaymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx
Then compliled
csc /target:library PaymentProc.cs
and added the assembly
CREATE ASSEMBLY PaymentProc from 'D:\ProdCode\PaymentProc.dll' WITH
PERMISSION_SET = UNSAFE
I cannot figure out how to refrence the chargecard method
I have tried
CREATE PROCEDURE PaymentProc
@Account int,
@Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
It seems wsdl.exe put all this serialization code
namespace PaymentProc {
using System.Diagnostics;
using System.Web.Services;
using System.ComponentModel;
using System.Web.Services.Protocols;
using System;
using System.Xml.Serialization;
/// <remarks>
[System.CodeDom.Compiler.GeneratedCodeAttribute("wsdl", "2.0.50727.42")]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Web.Services.WebServiceBindingAttribute(Name="ServiceSoap", Namespace="http://ProdWeb1/PaymentProc")]
public partial class PaymentProc : System.Web.Services.Protocols.SoapHttpClientProtocol {
private System.Threading.SendOrPostCallback ChargeCardOperationCompleted;
/// <remarks>
public PaymentProc()
{
this.Url = "http://ProdWeb1/PaymentProc/PaymentProc.asmx";
}
/// <remarks>
public event ChargeCardCompletedEventHandler ChargeCardCompleted;
/// <remarks>
[System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ProdWeb1/PaymentProc/ChargeCard", RequestNamespace="http://ProdWeb1/PaymentProc", ResponseNamespace="http://ProdWeb1/PaymentProc", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
public string ChargeCard(int account, int amount) {
object[] results = this.Invoke("ChargeCard", new object[] {
account,
amount});
return ((string)(results[0]));
}
.................
When I run
CREATE PROCEDURE PaymentProc
@Account int,
@Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
Method, property or field 'ChargeCard' of class 'PaymentProc.PaymentProc' in assembly 'PaymentProc' is not static.
Any ideas?
Dave Evans
|
|
|
|
|
Hi friends
what is differnce b/w sqlserver2005 and sqlserver2000
what is the advantage of sqlserver2005
plz give goodtutorial link for sqlserver2005
regards
saravanan
-- modified at 8:04 Monday 13th August, 2007
|
|
|
|
|
|
Hi,
My code worked fine before i placed "SqlTransaction" command in my code. After placing "SqlTransaction" command in my code it is showing "Unexpected existing transaction." Please tell me where I am going wrong.
My Code:
protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString))
{
con.Open();
using (SqlConnection destinationConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString))
{
destinationConnection.Open();
using (SqlTransaction transaction = destinationConnection.BeginTransaction())
{
SqlCommand DelCmd = new SqlCommand("delete from mfund_data", con);
DelCmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = "mfund_data";
try
{
bulkCopy.WriteToServer(CreateDataTableFromFile());
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Response.Write(ex.Message);
}
}
}
destinationConnection.Close();
}
con.Close();
}
}
Regards,
Jagadeesh
|
|
|
|
|
Hi Jagadeesh
According to this[^], you need to pass your SqlTransaction object into the SqlBulkCopy constructor.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Thank you Andy,
Your link helped me a lot in fixing the issue. Actually while googling in the morning i came accross this page but I ignored the crytical part of it.
By going through the same page aging now @ mid night helped me very much 
The mistake was here
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction))
Thanks again for support,
I am
|
|
|
|
|
i have a table in sqlserver2005 named emp.
where the coloumns of the emp table are as follows.
empid int,
empname varchar(20),
empaddrs varchar(20),
empDOB dateandtime,
empsex char(1),
i want to genertae empid as autogenertaed sequencitial number.
where the format of the auto genertaed number for empid will be -yyyy-sex-nnnn
where 1st for places are the year of his dob.
5th place would be the first letter of his/her sex
and last 4 place that is from 6th to 10th would be number (means 0001 ,0002)
example of auto generated number are-1983M001,1985F0002,2005M0003...and so on..
can u tel me how i wil do it in SQLSERVER2005.
(i know only how to generate auto num.by setting its identity to true.but dont know what to write in the formula filed so that i can genrate the above type of auto generated number)
|
|
|
|
|
select convert(varchar,year(getdate()))+ <br />
(select top 1 cast(empsex as varchar) from emp )+<br />
(select top 1 cast((empid+1) as varchar) from emp order by id desc)
I Love SQL
|
|
|
|
|
Hi,
I need some help. I have a DB layout like this:
Table FirstNames
Table LastNames
Table Users
Table firstnames and Lastnames are linked to Table Users. What I need to do is the following. If someone adds his/her FirstName and LastName the ID from those tables gets inserted into the Users. The ID's are auto incremented.
Table FirstNames
1 Fritz
Table LastNames
1 Blignaut
Table Users
1
1
If someone adds his name and it's the same as Fritz than the ID "1" should be added to the Users Table.
Thanks!
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
After you insert values in Table FirstNames and LastNames then insert max id into users table.
Example
declare @FNID as int<br />
set @FNID = (select max(ID) from firstnames)<br />
declare @LID as int<br />
set @LNID = (select max(ID) from lastnames)<br />
<br />
Insert into users (firstnameID,LastNameID) values (@FNID,@LNID)
or use triggers
I Love SQL
|
|
|
|
|
One way to do this would be:
DECLARE @FID INT
DECLARE @LID INT
SET NOCOUNT ON
SELECT @FID = [ID] FROM FirstNames WHERE FirstName = 'Fritz'
IF @FID IS NULL
BEGIN
INSERT INTO FirstNames(FirstName) VALUES ('Fritz')
SET @FID = Scope_Identity()
END
SELECT @LID = [ID] FROM LastNames WHERE LastName = 'Blignaut'
IF @LID IS NULL
BEGIN
INSERT INTO LastNames(LastName) VALUES ('Blignaut')
SET @LID = Scope_Identity()
END
INSERT INTO Users VALUES (@FID, @LID)Are you sure that you really want to normalize down to this level? You are going to be doing an awful lot of looking up on information which is going to be wasteful in system processing terms.
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
