|
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
|
|
|
|
|
Many thanks Colin,
Will give the views a go and let you know how I get on.
Regards,
Nigel
|
|
|
|
|
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...
Cheers mate.
Nigel
|
|
|
|
|
Hi.
Which version(s) of Microsoft SQL Server 2005 can be installed on windows vista?
Best wishes
|
|
|
|
|
Express and Developer. All other editions (as far as I'm aware) require a server class OS.
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
|
|
|
|
|
I believe that SP2 is recommended for running SQL 2005 on vista
|
|
|
|
|
When I give random Search of 1000 records in particular table, the systems hangs after some 30 to 40 searches. Is there a Memory Problem. I use the Process Rowset function in teh demo1 code and retreived.
|
|
|
|
|
i have problem i can't attachment for database (the file you've specified is not a valid sql server database file )
how can solve this problem
Rami Abd alhalim
|
|
|
|
|
What is the file extension?
Rami Said Abd Alhalim wrote: (the file you've specified is not a valid sql server database file )
What part of that do you not understand?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
u must attach the .mdf file.
|
|
|
|
|
Hi
each time I try to add/Update something I receive the error below:
"The transaction log for database 'DatabaseName' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
any idea how to solve this
thanks alot
Shaimmaa
|
|
|
|