Computer name could not be obtained

i have stupid problem that had effect my application distripuotion which i think is related either to connection string or something of the security setting of sql server or iis , actually i do not know what to do: the details of the problem is a have multi connection strings in this form :

sqlconnectionPubs =

New Data.SqlClient.SqlConnection("Server=SALES-SERVER;Database=NajdiaServiceRequestDta; Asynchronous Processing=true; Integrated Security=True; Trusted_Connection=Yes;")

and the connection works just fine in my computer , even i put the database in the main server and still i can post data from my pc to the main server where my database exist but when i try to post from any other computer in the network it tells me : run time error : computer name could not be obtained.

I appreciat any help

thanks,

If this is SQLExpress I think you would have to have the connstring be

(SALES-SERVER\SqlExpress Instance name) the default would be

SALES-SERVER\SQLEXPRESS

|||

I still having the same problem that took time more than the time for creating the project and i am afraid that it is related to something in the network that i do not understand. so please keep sending me any new idea.

thanks

|||

w_inaim:

but when i try to post from any other computer in the network it tells me : run time error : computer name could not be obtained.

Where, exactly, are you getting this error? Is it when you try connecting to the database? Are all of your computers on the same domain? Do you have identity impersonate set to true?

sqlsql

complex sql statement

I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(select(CommissionRebate* Quantity)

from

Account A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Insert

into Transaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@.NetCommission

,

@.AccountNumber

)

Hi

Insert

intoTransaction

(

Payee

........

Transaction is the key word,you have to choose another table name.

|||Or always embed such object names in [] or "" to avoid "syntax error":

Insert
into [Transaction]

(

Payee...|||

Even with the table name changed I am still getting a subquery error

Subquery returned more than 1 value.

All I really want to do is run a calculation on ech row in the database...any ideas?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(

select

(CommissionRebate* Quantity)

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Insert

into TradeTransaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@.NetCommission

,

@.AccountNumber

)

Go

|||Obviously the error indicates?that?some?subquery?returns?more?than?1?value?while?you're?trying?to?assign?the?returned?values?to?a?single?variable.?This?is?a?bad?logic?as?there?is?no?array?in?T-SQL.?So?my?suggestion?would?be?to?use?some?aggregation?funciton?or?TOP?keyword?to?restrict?the?subquery?to?return?only?1?value, for example:

Set @.NetCommission =
(select top 1 (CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)

Or:

Set @.NetCommission =
(select max(CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)|||

This gets me the two values I need, but I then need to insert these two values into a different table... how would I go about doing this?

Select

sum(A.CommissionRebate* T.Quantity)As NetCommission, T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

|||

Ok this one works but it only inserts the first row and runs successfully, but does not go on to insert the other rows...

Declare

@.NetCommissiondecimal

Declare

@.AccountNumberVarchar(50)

Select

@.NetCommission=Sum(A.CommissionRebate* T.Quantity), @.AccountNumber= T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

Insert

into appTransaction

(

Payee

,

Payment

,

AccountNumber

)

Values

(

'Client Credit'

,

@.NetCommission

,

@.AccountNumber

)

|||If you want to insert multiple rows you need to use Cursor to skip through the rows to be inserted, or store the rows in a temple table so that you can?fetch rows from it?into?some?table?in?a?single?insert?statement.|||

I did this with the cursor idea, worked for a second and now I am getting this message: Error converting data type varchar to decimal.

DECLARE

appCursorCursor

For

Select

T.AccountNumber,sum(CommissionRebate)/count(*)*sum(Quantity)as NetCommission

from

cmsTrades T

Join

cmsCalcs con c.AccountNumber= T.AccountNumber

where

T.TradeDate=CAST(YEAR(getdate()) as varchar) + RIGHT('00'+CAST(MONTH(getdate()) as varchar), 2) + RIGHT('00'+CAST(DAY(getdate())-1 as varchar), 2)

and

c

.Month= 11

GROUP

BY T.AccountNumber

Open

appCursor

Declare

@.NetCommissiondecimal, @.AccountNumberVarchar(50)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

While

(@.@.Fetch_Status<>-1)

Begin

If

(@.@.Fetch_Status<>-2)

Insert

into appTransaction

(

Payee

,

Payment

,

AccountNumber

)

Values

(

'Client Credit'

,

@.NetCommission

,

@.AccountNumber

)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

End

Close

appCursor

DEALLOCATE

appCursor

Go

|||I found this:

DECLARE
appCursor Cursor For
Select
T.AccountNumber, sum(CommissionRebate) / count(*) * sum(Quantity) as NetCommission
from
cmsTrades T
.....
Fetch Next from appCursor Into @.NetCommission, @.AccountNumber

Shouldn't this be?Fetch Next from appCursor Into @.AccountNumber, @.NetCommission? The same order as the returned fields in the select query?|||

Forget the cursor. You want to stay away from those as much as possible. Keep with set-based logic; it's the SQL way! :-)

Use this sort of approach instead:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
CommissionRebate * Quantity,
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber

|||that works great, would there be an easy way to sum up the inserts by AccountNumber so instead of 36 inserts for one accountnumber I would have just 1 for each accountnumber for that day?|||

Yes, you might try this:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
SUM(CommissionRebate * Quantity),
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber
GROUP BY
A.AccountNumber

|||That did the trick perfectly...thank you!

Complex copy routine, mulitple tables and changing GUIDs

hello,

I have several tables that have guids as their primary keys and the tables are related as follows:

Table1 - primary key =ServiceNo (Guid), Filter Key =CampaignNo

Table2 - primary key = CostBasisNo (Guid), Foreign Key =ServiceNo (from Table1)

Table3 - primary key = UserId, Foreign Key =ServiceNo (from table1)

Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)

what I need to do is copy all records from Table1 where CampaignNo = @.CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.

The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1

This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's forSourceServiceNo and the relatedMemberServiceNo which all would have changed.

I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.

regards

DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

INSERT INTO Table1 (CampaignNo) -- and other columns as well, I guess
SELECT @.newCampaignNo FROM Table1 WHERE ServiceNo=@.oldCampaignNo

INSERT INTO Table2 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

INSERT INTO Table3 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

I suppose table4 will be quite easy as well, but you need to describe where the ServiceNo:s come from.

|||

Hi gunteman,

thank you for replying, I'm not sure that will do what I want.

In my db the CampaignNo will never equal the ServiceNo.

Maybe if I try and explain the problem another way and only use two related tables

Lets say I have:

Table1 where the primary key =ServiceNo, and other attributes including anon primary key = CampaignNo

My first task is to copy all the rows from Table1 where an input parameter called @.CampaignNo = CampaignNo,

So my statement would be something like:

INSERT INTO Table1 (ServiceNo, attribute1, attribute2,CampaignNo)
(SELECTNEWID(), attribute1, attribute2, @.CampaignNo from Table1 WHERE
CampaignNo = @.CampaignNo)

NEWID() of course will create a new uniqueidentifier.

now this works fine, I've tried this out using a temporary table and all is good.

my problem comes when I need to copy the contents of the related table, say Table2,

Lets say Table2 hasCostBasisNo (primary key), attribute1, attribute2,ServiceNo (foreign key from Table1)

now I need to copy rows from Table2 but where the ServiceNo = the old ServiceNo from Table1 and I guess this is where the list of service numbers again = @.CampaignNo.

I guess I could say something like

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2, ServiceNo)
(SELECTNEWID(), attribute1, attribute2, ServiceNo from Table2 WHERE
?)

and this is where I hit the problem, how do I write the INSERT statement for Table2 to copy it's own records but only where the ServiceNo = the old ServiceNo from Table1 and then insert the associated new ServiceNo.

hope that's made the issue a little clearer, the other tables don't really matter cos if I can solve this the others will follow.

regards

|||DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

DECLARE @.conversion TABLE
(
oldServiceNo uniqueidentifier,
newServiceNo uniqueidentifier
)

INSERT INTO @.conversion (oldServiceNo,newServiceNo)
SELECT ServiceNo,newid() FROM Table1 WHERE CampaignNo=@.oldCampaignNo

INSERT INTO Table1 (ServiceNo,attribute1, attribute2, CampaignNo)
SELECT newServiceNo, attribute1, attribute2, @.newCampaignNo FROM @.conversion c,Table1 t WHERE c.oldServiceNo=t.ServiceNo

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2,ServiceNo)
SELECT newid(), attribute1, attribute2, newServiceNo FROM Table1 @.conversion c,Table2 t WHERE c.oldServiceNo=t.ServiceNo

..and so on.|||

Yes, that does the job very nicely indeed - thank you, it's also given me a pointer to other tables I need to copy the contents of.

nice one!

complex and large triggers

If a business scenario related to data gathering for statistical
analysis merits large complex triggers. Thes triggers pre-populate
tables with data based on gathered business rules and make
use of nested stored procedures and cursors. What is then good
practice?
Are cursors to be kept small and lean?
Should I switch over to Sql Server 2005? (that's not really a question
that)
Anyone have any experience already with managed code when it comes to
embedding business logic into the database that they want to share?
regards,
Gerard"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1135804946.733346.3910@.z14g2000cwz.googlegroups.com...
> If a business scenario related to data gathering for statistical
> analysis merits large complex triggers.
Gerard,
May I ask what you mean by "If"? Can you rephrase the
statement/question?

> Thes triggers pre-populate
> tables with data based on gathered business rules and make
> use of nested stored procedures and cursors.
Ok, I understand what the triggers are doing (or, at least, I think
I do).
How does this correlate with the previous sentence?

> What is then good practice?
I will need more information on what you are trying to do.
The link http://www.aspfaq.com/etiquette.asp?id=5006,
is excellent when it comes to detailing how to provide
the information that will best enable others to answer
your questions.

> Are cursors to be kept small and lean?
Cursors should be used only when absolutely nothing else can be done
(usually for manipulating freshly imported data that has not yet
been "normalized").
Oh, and never put a cursor in a trigger.
Sincerely,
Chris O.|||On 28 Dec 2005 13:22:26 -0800, "Gerard" <g.doeswijk@.gmail.com> wrote:
>Anyone have any experience already with managed code when it comes to
>embedding business logic into the database that they want to share?
Over the last ten years or so, until recently, the standard
architecture has been to put business logic in a middle tier.
Now, it turns out that is almost impossible, as "business logic"
determines the data model of a database, much less even a standard set
of well-designed stored procedures. But in general, it was good
advice, at least on SQLServer. Why? Because TSQL was never the best
language for capturing complex (procedural) business logic.
Now, Oracle has always had PL/SQL, a much more procedural language,
and has had Java applets for several years. Now that SQLServer 2005
officially has .Net languages also available "inside" the database,
maybe that would be the tool to use to put more business logic into a
database.
So, "embed business logic" in the database by constructing a good
normalized data model, and be prepared to service a middle-tier with
well-constructed stored procedures to encapsulate fancy SQL. If you
also construct the "middle-tier" with C# inside of SQLServer 2005,
well and good, I guess. Have to get a chance to try it myself ...
Josh|||Cursors should be avoided whenever possible and practical. Set-based
operations usually perform much, MUCH faster than cursors for a number of
reasons: (1) index updates can be combined which reduces writes; (2)
triggers fire once per operation reducing processing overhead; (3)
transaction logging overhead is reduced as well. There are few
exceptions--mostly involving self-joins. Even then, you should try using
set-based operations first, and only after careful examination and analysis
replace contraindicated set-based code with a cursor. Also, if you must use
a cursor, avoid reads and writes within the fetch loop--that is, include
everything you need in the SELECT statement for the cursor, or process
several cursors with result sets in the same order in the same fetch loop,
and cache writes in temporary objects such as table variables so that they
can be flushed outside of the fetch loop. This minimizes the performance
impact of using a cursor.
In general, I prefer not to call stored procedures from within triggers.
Cursors are almost always bad, so whether you use them in a trigger or not
doesn't change that fact. What should be noted is that if you manipulate a
table within a cursor fetch loop, then the triggers on that table will fire
once per iteration. This can increase the probability of blocking and
deadlocks.
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1135804946.733346.3910@.z14g2000cwz.googlegroups.com...
> If a business scenario related to data gathering for statistical
> analysis merits large complex triggers. Thes triggers pre-populate
> tables with data based on gathered business rules and make
> use of nested stored procedures and cursors. What is then good
> practice?
> Are cursors to be kept small and lean?
> Should I switch over to Sql Server 2005? (that's not really a question
> that)
> Anyone have any experience already with managed code when it comes to
> embedding business logic into the database that they want to share?
> regards,
> Gerard
>|||> May I ask what you mean by "If"?
When a business scenario related to data gathering for statistical
analysis merits large complex triggers

> Oh, and never put a cursor in a trigger.
I think that was covered in this post already:
http://groups.google.com/group/micr...1783d773?hl=en&|||> Why? Because TSQL was never the best language for capturing complex (proc
edural) business logic.
I agree, there are better languages to do so such as C#. What I'm
referring to and curious about is those scenarios where there is no way
to get at the middle tier of an existing application (vendor locked)
and alternatives need to be sought using the database.
For instance, you have no control over the business logic tier other
then modifying the vendors stored procedures (highly undesirable when
it comes to upgrades/service packs etc.) and need to build custom
business logic based upon database events that are initiated by the
vendors applications.|||> In general, I prefer not to call stored procedures from within triggers.
Can you tell me why? Is that just because you like to have all your
logic in the one script or are there other reasons?
regards,
Gerard|||Gerard
Well , if a trigger gets fired actually SQL Server "opens" another
transaction to perform any DML in your case to call a stored procedure.
What is your SP is failed and for some reasons this trasaction is still
opened , so end-users will no be able to perform somethin till this one will
be completed . I prefer to keep a code within a trigger as small as
possible , and using triggers only for auditing. But it's my opinion
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1135861795.693314.17140@.z14g2000cwz.googlegroups.com...
> Can you tell me why? Is that just because you like to have all your
> logic in the one script or are there other reasons?
> regards,
> Gerard
>|||Gerard wrote:
> Can you tell me why? Is that just because you like to have all your
> logic in the one script or are there other reasons?
> regards,
> Gerard
I thought Brian explained that: Calling procs from a trigger implies
that you'll need to use a cursor (not always but typically that will be
the case). Cursor code is bad news for lots of reasons that have been
covered here many times.

> For instance, you have no control over the business logic tier other
> then modifying the vendors stored procedures (highly undesirable when
> it comes to upgrades/service packs etc.) and need to build custom
> business logic based upon database events that are initiated by the
> vendors applications.
This sounds like EAI (Enterprise Application Integration). While
triggers may form part of those solutions, more commonly other tools
are used such as messaging or integration software. Normally you can
add your own code to that integration process although some tools do
claim code-free solutions. Have you looked at third-party integration
tools? Do that before you decide to invent your own.
David Portas
SQL Server MVP
--|||> What is your SP is failed and for some reasons this trasaction is still op
ened
Could proper error handling around the calling of a store proc not take
care of that?