Database Discussion Boards

mvyjim13-Aug-07 8:12

13-Aug-07 8:12

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

Re: MSSQL output order

Giorgi Dalakishvili13-Aug-07 8:24

Giorgi Dalakishvili

13-Aug-07 8:24

SELECT * FROM Owners WHERE OwnerID = 7
union
SELECT * FROM Owners WHERE OwnerID = 3
union
SELECT * FROM Owners WHERE OwnerID = 21

#region signature
my articles
#endregion

Re: MSSQL output order

mvyjim13-Aug-07 10:56

mvyjim

13-Aug-07 10:56

Thanks for the help. But I didn't get consistent results until I changed 'union' to 'union all'.

Jim

Day of week

kibromg13-Aug-07 5:22

kibromg

13-Aug-07 5:22

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

Re: Day of week

DerekFL13-Aug-07 5:48

DerekFL

13-Aug-07 5:48

SELECT DATENAME(dw, GETDATE())

Dave Evans

Re: Day of week

kibromg13-Aug-07 6:32

kibromg

13-Aug-07 6:32

Thanks Dave.
It worked.

Keep the good work.

CLR WebService Access

DerekFL13-Aug-07 5:21

DerekFL

13-Aug-07 5:21

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

sqlserver2005 [modified]

saravanan0513-Aug-07 1:56

saravanan05

13-Aug-07 1:56

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

Re: sqlserver2000

Pete O'Hanlon13-Aug-07 2:04

Pete O'Hanlon

13-Aug-07 2:04

Differences[^]
Tutorials[^]

Deja View - the feeling that you've seen this post before.

Unexpected existing transaction --> error

Jagadeesh Jupalli12-Aug-07 23:14

Jagadeesh Jupalli

12-Aug-07 23:14

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

Re: Unexpected existing transaction --> error

andyharman13-Aug-07 4:29

andyharman

13-Aug-07 4:29

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".

Re: Unexpected existing transaction --> error

Jagadeesh Jupalli13-Aug-07 8:08

Jagadeesh Jupalli

13-Aug-07 8:08

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 Smile | :)

The mistake was here
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction))

Thanks again for support,
I am Smile | :)

how to generate autonumber in ceratin sequence

biswa4712-Aug-07 22:11

biswa47

12-Aug-07 22:11

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)

Re: how to generate autonumber in ceratin sequence

Blue_Boy12-Aug-07 23:53

Blue_Boy

12-Aug-07 23:53

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

IF EXISTS?

Illegal Operation12-Aug-07 21:16

Illegal Operation

12-Aug-07 21:16

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

Re: IF EXISTS?

Blue_Boy12-Aug-07 21:29

Blue_Boy

12-Aug-07 21:29

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

Re: IF EXISTS?

Pete O'Hanlon12-Aug-07 22:31

Pete O'Hanlon

12-Aug-07 22:31

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.

Map one table to another in MSSQL

big_nige12-Aug-07 11:25

big_nige

12-Aug-07 11:25

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

Re: Map one table to another in MSSQL

Colin Angus Mackay12-Aug-07 12:01

Colin Angus Mackay

12-Aug-07 12:01

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

Re: Map one table to another in MSSQL

big_nige12-Aug-07 12:30

big_nige

12-Aug-07 12:30

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

Re: Map one table to another in MSSQL

Colin Angus Mackay12-Aug-07 14:21

Colin Angus Mackay

12-Aug-07 14:21

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.

Re: Map one table to another in MSSQL

big_nige12-Aug-07 14:55

big_nige

12-Aug-07 14:55

Many thanks Colin,

Will give the views a go and let you know how I get on.

Regards,
Nigel

Re: Map one table to another in MSSQL

big_nige14-Aug-07 12:18

big_nige

14-Aug-07 12:18

thanks Colin - the views worked a charm!!

Still had some tidy up work as one module was built in the US and the other here in Australia - so the date formats were different, but otherwise a simple solution to what was potentially a nightmare.

Oh the joys of contract programming... Laugh | :laugh:

Cheers mate.

Nigel

Vista & SQL Server

mehrdadc4812-Aug-07 7:46

mehrdadc48

12-Aug-07 7:46

Hi.
Which version(s) of Microsoft SQL Server 2005 can be installed on windows vista?

Best wishes

Re: Vista & SQL Server

Colin Angus Mackay12-Aug-07 12:01

Colin Angus Mackay

12-Aug-07 12:01

Express and Developer. All other editions (as far as I'm aware) require a server class OS.

Last Visit: 31-Dec-99 18:00 Last Update: 22-May-24 5:49

Refresh

ᐊ Prev 1...1454 1455 1456 145714581459 1460 1461 1462 1463 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.