|
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.
|
|
|
|
|
Hi all,
I have two ASP.NET modules that have been built at different times that I want to use together. there are only 4 tables that need to be merged, but both modules use stored procedures and Crystal reports extensively - so I am looking as to whether it is possible in MSSQL 2005 to map or alias the fields in one table to another, and so avoid a rather painful refactoring task. I have listed the tables and the corresponding fields below for greater clarity - the order and orderItems tables are the one's I need to keep.
orders(table) ==> cart(table)
OrderID(int PK identity) ==> CartID(int PK identity)
EntryDate(DateTime) ==> SaleDate(DateTime)
userID(uniqueidentifier) ==> CustomerName(varchar)
PartnerID(int) ==> PartnerID(int)
TransactionID(varchar) ==> ReceiptNo(varchar)
(no corresponding field) ==> CustomerEmail(varchar)
orderItems(table) ==> CartItem(table)
ItemID(int PK identity) ==> CartItemID(int PK identity)
OrderID(int) ==> CartID(int)
ProductID(int) ==> ProductID(int)
SellPrice(money) ==> SalePrice(money)
Many thanks in advance,
Nigel
|
|
|
|
|
Would VIEWs solve your problem?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Thanks for the reply Colin,
Must admit a limited understanding of table views - so I am not sure.
If I used a view, wouldn't I still have to refactor all the stored procedures and reports that use the cart and cartitems tables to use the view instead??
If I have to do that I am still in the same predicament.
Or is it possible to create a table view with the exact name of the original table (deleting the tables of course) and the SP's and reports will still pick up the data?
Cheers,
Nigel
|
|
|
|
|
big_nige wrote: Or is it possible to create a table view with the exact name of the original table (deleting the tables of course) and the SP's and reports will still pick up the data?
If you are renaming the original tables and create views with the original table names then it should work. You may have to recompile the stored procedures to get it to pick up the changes.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
