Failed to insert.

Hi!
I am trying to insert into a sql server 2000 enterprise edition database from a .net application. but I keep getting the error server doesnot exist or access denied. I can connect to the server from query analyzer and sql manager. The database is running on windows 2003. The only thing I don't see is the ASPNET sys account not on the login database. I don't know how to add that.

Here is the connection string that I have always used and worked before.

Network Library=DBMSSOCN; Data Source=DEVELOPERS,1433; Initial Catalog=CMLTODB;User ID=sa;Password=password

Thanks,

You are hardcoding a username and password thus the ASPNET user does not need to be in the log in database.
Your connection string looks good. Some things to consider:
- Can you ping DEVELOPERS from your ASP.NET box?
- Is your SQL server in mixed or Windows Auth mode? It needs to be in mixed?
Check this article for a complete list of possible issues that cause this error:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328306
Jason
|||

Hi Jason!
I will check the link you provided, but I even put the application on the same server as the database. It still give me the same error. I changed my connection string to use the local server. It did not help either.

My database authentication is already in mixed mode.

Thanks,

Mostafa

|||

Try this url it has visual walk through, assuming your application is not handling money transactions. Hope this helps.http://duhnetnuke2.net/Default.aspx?tabid=64

Kind regards,

Gift Peddie

Failed to enable constraints

I have to admit I'm pretty new to ASP.net.

I'm trying to insert a row of data into my SQLExpress database table "Prets"

I'm getting the user_ID from the table "Membres"

Here's the code I have:

Imports System.Web.UI.Page
Imports System.Security.Principal
Imports WiseNetTableAdapters

PartialClass membre_sec_Pret_demande_pret
Inherits System.Web.UI.Page

Protected Sub submit_btn_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles submit_btn.Click
Dim UserAs String = My.User.Name
Dim pretAdapterAs New PretsTableAdapter
Dim membreAdapterAs New MembresTableAdapter
pretAdapter.AjouterPret(Convert.ToInt32(membreAdapter.GetUserIDbyName(User)), Convert.ToDecimal(montant_txt.Text), Convert.ToString(raison_txt.Text))
End Sub
End Class

And here's the error I get at runtime:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Source Error:

Line 1610: }Line 1611: WiseNet.MembresDataTable dataTable = new WiseNet.MembresDataTable();Line 1612: this.Adapter.Fill(dataTable);Line 1613: return dataTable;Line 1614: }

Source File:c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wisenetindustries\afc388c7\16067f12\App_Code.tyietv41.0.cs Line:1612

Stack Trace:

[ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.] System.Data.DataTable.EnableConstraints() +1820725 System.Data.DataTable.set_EnforceConstraints(Boolean value) +39 System.Data.DataTable.EndLoadData() +138 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +218 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +318 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +221 System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +162 System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +107 WiseNetTableAdapters.MembresTableAdapter.GetUserIDbyName(String nom) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wisenetindustries\afc388c7\16067f12\App_Code.tyietv41.0.cs:1612 membre_sec_Pret_demande_pret.submit_btn_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\WiseNetIndustries\membre_sec\Pret\demande_pret.aspx.vb:12 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Make sure you have not insert duplicate values on the primary key column, you may take a look at this post:

http://forums.asp.net/thread/1213814.aspx

sql

Failed to convert parameter value from a String to a Guid?

Hi all,

I have setup my database that have 3 columns:

1. Primary Key

2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.

3. Another Foreign key with just an int.

I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:

SELECT aspnet_Users.UserId, t_music.MUSIC_TITLE
FROM t_user_viewed
JOIN aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId
JOIN t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FK
WHERE aspnet_Users.UserId = @.UserId

Any help would be greatly appreciated,

Kenny.

You need to post definition for @.UserId parameter.

|||

Can you please elaborates? I'm new to asp.net as well as ms-sql.

Thank you,

Kenny.

|||

Sounds like you need to cast the string value to GUID on the @.UserId when you call the select function and pass the @.UserId value.

ctype(StringValueGUID, GUID)

Burl

|||

Where do I cast the string value? In the DAL (dataset) or where I have my ObjectDataSource doing the select? Sorry for my lack of knowledge on asp.net and mssql.

Thanks,

Kenny.

|||

How are you passing the GUID to the ObjectDataSource for the select? Wherever that value comes from, its in a string type and needs to be cast as a GUID type. Can you post some code?

Burl

|||

I've created a Dataset and setup my table, then I've created a BLL that have this code to get the fields:

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}

In my ObjectDataSource, I chose the above method (GetSongsByUserPK) and have the parameter source as a session. Here is the code for the ObjectDataSource:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>

The SELECT query is the same as my first post... is there anything else I need to do?

Thank you very, very much.

Kenny.

|||

Well,

I've made some minor adjustment to my code and it's working now, however, the SELECT statement that I wrote in the DataSet didn't return the proper table. Here are the changes:

1. In my DataSet, I have setup the table called t_user_viewed that will store the primary key of t_music database as foreign key, and the UserId from the aspet_users as another foreign key. Here is the select statement:

SELECT *
FROM t_music a
JOIN t_user_viewed b on a.MUSIC_PK = b.MUSIC_PK_FK
JOIN aspnet_Users c ON b.UserID = c.UserId
WHERE b.UserID = @.UserId

So when I run this code, and enter the UserId, it should return the information from table "t_music" (with all information such as song title, artist, etc...) instead.

2. This is the code for the SongsBLL.cs class, to get the above select statement:

private t_user_viewedTableAdapter _t_user_viewed = null;

protected t_user_viewedTableAdapter Adapter2
{
get
{
if (_t_user_viewed == null)
_t_user_viewed = new t_user_viewedTableAdapter();

return _t_user_viewed;
}
}

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}

3. Here is the ObjectDataSource that was generated by VS:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL" OldValuesParameterFormatString="original_{0}">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>

4. Here is the GridView that was generated automatically when I select the DataSourceID to the above ObjectDataSource:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="VIEWED_PK"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="VIEWED_PK" HeaderText="VIEWED_PK" InsertVisible="False"
ReadOnly="True" SortExpression="VIEWED_PK" />
<asp:BoundField DataField="MUSIC_PK_FK" HeaderText="MUSIC_PK_FK" SortExpression="MUSIC_PK_FK" />
<asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
</Columns>
</asp:GridView>

Apparently, the GridView & the ObjectDataSource only pick up the t_music_viewed table columns, instead of the "t_music" columns that I specified in my Select statement.

Any idea?

Thank you very much,

Kenny.

|||

I've solved it by building my SELECT query in the "t_music" table instead.

THANK YOU!

Kenny.

|||

I've solved it by building my SELECT query in the "t_music" table instead.

THANK YOU!

Kenny.

sql

Failed insert via Enterprise Manager

Hey guys..

Trying to insert a row via Enterprise Manager I get "object does not exist" error.. However it works on another server?!?

The table name is aa.aabc .. So i know what the problem is.. it thinks that aa is the owner.. I can insert using query analyzer by using the following syntax: insert into [aa.aabc] ... but it fails using: insert into aa.abc .. for obvious reasons..

I'm guessing it has something to do with the differences in the server config between the 2 servers as to why one server you can insert via enterprise manager and the other you cant..

The collation is different as the ANSI warning and padding in the connection info..

Can anyone else help me fix this problem, so that I can insert using enterprise manager?

CheersINSERT into EM Where?|||Databases --> Databasename --> Tables --> Tablename --> Return all rows.. Then you can scroll to the bottom row and insert in a similar way to access..

THanks

Failed insert query

Hi

I'm trying to port some data from one database table to another
database table on the same server.

This is the query I am using:

-->
INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)
<---

This is the error I'm getting:

-->
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_ContactEvents_Contacts'. The conflict occurred in database
'newdatabase', table 'Contacts', column 'ContactID'.
The statement has been terminated.
<---

There is a relationship between the contacts table (Primary key
ContactID) and the contactsevent (foreign key ContactID) table. I guess
the error being flagged up here is that some contacts don't exist in
the new database, therefore referential intergretory won't allow it
being copied. I thought I could get around this using:
"WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)"
Note I've also tried:
"WHERE Exists (SELECT * FROM newdatabase.dbo.contacts)"

What am I doing wrong?

Many Thanks!

AlexYour subquery always evaluates to TRUE, so it's not filtering the data
- you need to link it to the outer table (see "Correlated Subqueries"
in Books Online):

...
from olddatabase.dbo.contactevents o
WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

Simon|||Simon thanks...!

I checked books online, thanks for the reference.
Looking at the conditional statement you gave me:

WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

... could you please clarify what 'o'' and 'n'' are?

I've tried now tried the below statement, which makes for sense to me
after your advice, unfortunately I still get the same error:

WHERE EXISTS
(SELECT ContactID FROM newdatabase.dbo.contacts
WHERE ContactID IN (SELECT ContactID FROM olddatabase.dbo.contacts))

I guess I still haven't got the hang of it!

Cheers!

Alex|||You query return all rows from olddatabase.dbo.contactevents, without
checking for the existance in newdatabase.dbo.contacts.

It aslo not advisible to use exists as it is ineffecient.

you can try this query:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
00:00',ISNULL(Notes,'')
from olddatabase.dbo.contactevents
INNER JOIN newdatabase.dbo.contacts
ON newdatabase.dbo.contacts.ContactID =
olddatabase.dbo.contactevents.ContactID

please let me know if u have any questions

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||o and n are table aliases - instead of typing out the full table name
every time, it's easier to use an alias, and it often makes the code
more readable (see "Using Table Aliases" in Books Online). As for your
query, try this:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents o
WHERE EXISTS (
SELECT *
FROM newdatabase.dbo.contacts n
WHERE o.ContactID = n.ContactID
)

Or you may find this clearer:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE ContactID IN (
SELECT ContactID
FROM newdatabase.dbo.contacts
)

I suspect that your query is mixing these two forms.

Simon|||Simon and Chandra

Thank you very much for your help!

Alex|||On Thu, 04 Aug 2005 12:17:11 GMT, Chandra wrote:

>You query return all rows from olddatabase.dbo.contactevents, without
>checking for the existance in newdatabase.dbo.contacts.
>It aslo not advisible to use exists as it is ineffecient.

Hi Chandra,

EXISTS inefficient? This is the first time that I hear that. In fact, I
always hear the opposite that it is very efficient since it'll stop
searching as soon as the first match is found, whereas other techniques
have to process all the data.

Can you post a repro script (or point me to one somewhere on the web)
that shows how EXISTS is less efficient than any of it's equivalents?

>you can try this query:
>INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
>ContactID, DateEntered, EventDate, Description)
>select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
>00:00',ISNULL(Notes,'')
>from olddatabase.dbo.contactevents
>INNER JOIN newdatabase.dbo.contacts
>ON newdatabase.dbo.contacts.ContactID =
>olddatabase.dbo.contactevents.ContactID

It's highly probably that this query will work, but you can't be totally
sure. As the OP didn't post the DDL for the table, you can't be totally
sure that the join to newdatabase.dbo.contacts will never result in more
than one row. And if it ever does, then your query will either insert
duplicates in newdatabase.dbo.contactevents, or (if a key is properly
declared) result in a primary key violation.

I'd definitely use EXISTS in this case. And I'd change the dates to an
unambiguous format ('20050101' or '2005-01-01T00:00:00').

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

failed insert because of duplicate rows and still locks held by application

Hi,
we are using SQL Server 2005.
We have a problem because of an application which tries to
insert a duplicate row in a table with primary key. This insert fails
of course but the locks are not released within this transcation.

This is not locking up on MS SQL Server 2000. But we can see the lock is still applied there.

Can someone help me on this?

Thanks,

Hmmm. What locks are held by the INSERT? Is this a stored procedure you are calling?

Can you post the code you are calling? It sounds like you might be in a Serializable isolation level, perhaps, and an explicit transaction is being started but not cleared. Are you using connection pooling?

|||We are not using connection pooling. The application is written in 'C' langugage and we are using Standard ODBC drivers provided by Microsoft. There are no explicit locking done for this insert query. After failure if I commit or rollback the lock gets released. I can see locks are also held in MS SQL Server 2000 also but they are not blocking other users to accress that perticular table. I have not chnaged isolation level in both (MS SQL Server 2005 and MS SQL Server 2000). The same application also runs with Oracle 9i and on failure of insert statment dos not locking oracle table. I am trying to figure out what locking mechanisum is changed in MS SQL Server 2005. Do I need to change any default settings in MS SQL Server 2005 which will not lock the table on failure of insert because of duplicate rows.|||

Try looking at the output of this query:

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,

request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then

(select object_name(object_id) from sys.partitions

where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

and this:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,

der.command as commandType,

execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,

der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

to see if you have a statement executing and

See what kinds of locks are being held and what the isolation level is. It is probably just not being disconnected and has a transaction open. Then try profiler to see exactly what is being sent. There is likely a setting that you need to set, but without seeing the code this is not easy to do.

|||

Thanks for reply.

The first query is giving follwing result:

Login Tran_Isolation re_ses resource_type mode type Status Owner Table

sasi ReadCommitted 56 PAGE IX LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 RID X LOCK GRANT TRANSACTION 72057594243186688
sasi ReadCommitted 56 PAGE IX LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 OBJECT IX LOCK GRANT TRANSACTION ACLS6999

The second query is not returning any rows.

|||I think you are going to have to use profiler and trace the activity on your server for this process. Post the queries if you can and then we can see what might be wrong.

Fail to get Return_Value in stored procedure NullValueReference

I have a stored procedure that insert into a table and return a value indicating if the insert really occured.
If the a insert occurs then it returns the identity of that insert.

I have slimmed down my code here, so you can see.
My problem is that a NullValueReference exceptions occurs when trying to reference the returnvalue.
Is it not possible to get a return value along with a recordset ?

publicint RegisterCustomer(Customer customer)

{

SqlCommand myCommand =newSqlCommand("ins_RegistrerKunde", myConnection);

myCommand.CommandType =CommandType.StoredProcedure;

SqlParameter Epost =newSqlParameter("@.Epost",SqlDbType.VarChar, 25);

SqlParameter Passord =newSqlParameter("@.Passord",SqlDbType.VarChar, 25);

SqlParameter ReturnValue =newSqlParameter("@.RETURN_VALUE",SqlDbType.Int, 4);

ReturnValue.Direction =ParameterDirection.ReturnValue;

Epost.Value = customer.Email;

Passord.Value = customer.Password;

myCommand.Parameters.Add(ReturnValue);

myCommand.Parameters.Add(Epost);

myCommand.Parameters.Add(Passord);

myConnection.Open();

SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.SingleRow);

int isRegistered = (int)myCommand.Parameters["@.RETURN_VALUE"].Value;

myConnection.Close();

if (isRegistered == 1)

{

result.Read();

returnConvert.ToInt32(result["Kunde_ID"]);

result.Close();

}

returnConvert.ToInt32(isRegistered);

}

//Stored Procedure ins_RegistrerKunde
create procedure%PROC% (@.Epost varchar(25), @.Passord varchar(25))
as
begin
IF EXISTS( SELECT EpostFROM tbl_KundeWHERE Epost = @.Epost)
RETURN 0
ELSE
INSERT INTO tbl_Kunde( Epost, Passord)
VALUES (@.Epost, @.Passord)
SELECT @.@.IDENTITY AS Kunde_ID
RETURN 1
end
go

Output parameters and return values can be accessed only after a datareader is closed. My suggestion to you is to use an outputparameter for the @.@.IDENTITY value, and to then use an ExecuteNonQueryinstead of an ExecuteReader.

Also, instead of @.@.IDENTITY, use SCOPE_IDENTITY(), as this will returnthe the last Identity value in the current scope. SeeIdentity Crisis andUnderstanding Identity Columns for an explanation.|||

I see.

Thank you.

Facing some problem in Instead of Trigger for MultiRow Insert

This is the Trigger which is not working properly during Update, no any
record is going to be updated so pls help.

I am updating the requisition table when any update in quantity in
podetails table

CREATE TRIGGER trig_updateRequistion ON ERP.DBO.TranPurchaseOrderDetail

INSTEAD OF UPDATE

AS

IF UPDATE(Quantity)

BEGIN

Update RequisitionSlipDetail
set RequisitionSlipDetail.PoQuantity =

(Select PoQuantity from RequisitionSlipDetail where
ItemCode=(Select CAST(i.ItemCode as nvarchar(20)) from inserted as i)
and
RSlip_No=(Select CAST(i.RSlip_No as int) from inserted as i)
)
-

((Select Quantity from TranPurchaseOrderDetail where Purchase_OrderNo
=
(Select CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i))

- (Select CAST(i.Quantity as int) from inserted as i))

where RequisitionSlipDetail.ItemCode = (Select CAST(i.ItemCode as
nvarchar(20)) from inserted as i) and RequisitionSlipDetail.RSlip_No =
(Select CAST(i.RSlip_No as int) from inserted as i)

Update TranPurchaseOrderDetail set
TranPurchaseOrderDetail.Quantity =
(Select CAST(i.Quantity as int) from inserted as i)
where TranPurchaseOrderDetail.Purchase_OrderNo = (Select
CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.ItemCode = (Select CAST(i.ItemCode as
nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.PurchaseDetailId =
(Select PurchaseDetailId from TranPurchaseOrderDetail where
Purchase_OrderNo = (Select CAST(i.Purchase_OrderNo as nvarchar(20))
from inserted as i))

ENDsantoshborfalkar (santosh.borfalkar@.gmail.com) writes:

Quote:

Originally Posted by

This is the Trigger which is not working properly during Update, no any
record is going to be updated so pls help.
>
I am updating the requisition table when any update in quantity in
podetails table

It's very difficult to tell what might be wrong without any knowledge of
your tables or the business rules.

But I noted a few things that appears ood.

Quote:

Originally Posted by

CREATE TRIGGER trig_updateRequistion ON ERP.DBO.TranPurchaseOrderDetail
INSTEAD OF UPDATE
AS
IF UPDATE(Quantity)

So if the Quantity columns is not mentioned in the SET clause, then you
will not perform any update at all?

Quote:

Originally Posted by

Update RequisitionSlipDetail
set RequisitionSlipDetail.PoQuantity =
>
(Select PoQuantity from RequisitionSlipDetail where
ItemCode=(Select CAST(i.ItemCode as nvarchar(20)) from inserted as i)

This may work, if only one row at a time is updated, but it will fail
with an error if many rows are updated. Recall that triggers fire once
per statement, not once per row.

Quote:

Originally Posted by

Update TranPurchaseOrderDetail set
TranPurchaseOrderDetail.Quantity =
(Select CAST(i.Quantity as int) from inserted as i)
where TranPurchaseOrderDetail.Purchase_OrderNo = (Select
CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.ItemCode = (Select CAST(i.ItemCode
as
nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.PurchaseDetailId =
(Select PurchaseDetailId from TranPurchaseOrderDetail where
Purchase_OrderNo = (Select CAST(i.Purchase_OrderNo as nvarchar(20))
from inserted as i))

I don't understand this casting business. Why cast the columns of "inserted"
when they are the same as in the target table? A simplified version of
the above could be:

Update TranPurchaseOrderDetail
set Quantity = i.Quantity
FROM TranPurchaseOrderDetail T
JOIN inserted i ON T.Purchase_OrderNo = i.Purchase_OrderNo
AND T.ItemCode = i.ItemCode
and T.PurchaseDetailId =

Quote:

Originally Posted by

(Select PurchaseDetailId from TranPurchaseOrderDetail where
Purchase_OrderNo = (Select CAST(i.Purchase_OrderNo as nvarchar(20))
from inserted as i))

I did not rewrite the last bit, because, frankly, I don't understand what
it's supposed to mean. It just looks strange.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Facing problem with execute function

The execute function is not taking the arabic chars in..or nvarchars in

what i did is execute(@.sql)
where @.sql is a nvarchar

Assuming it as a insert statement to a table the chars are appearing as ?
where as the chars when inserted using a plain insert query comes clearly in arabic

set @.sql=insert into test values('arabic chars');
insert into test values('arabic chars'); works good plainly
but when called through execute it flops
execute (@.sql)
I even tried by inserting the @.sql value to the DB the query formed is shown that it is in arabic.

i tried using this but syntax error occured
EXECUTE sp_executesql @.stmt =N' (@.sqls)',
@.Farms = N'@.sqls nvarchar(max)',
@.sqls = @.strintest

The above is working only when we give the query

with dynamic values to be passed in

You have to ensure the value string shoule be prefixed with N.

(example) all the quires are working properly..

Code Snippet

insert Into testarabic values(N'???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????')

Exec ('insert Into testarabic values(N''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')')

Declare @.SQL as NVarchar(1000);

Select @.SQL=N'insert Into testarabic values(N''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')'

Exec (@.SQL)

The following quires will fail,

Code Snippet

insert Into testarabic values('???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????')

Exec ('insert Into testarabic values(''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')')

Declare @.SQL as Varchar(1000);

Select @.SQL=N'insert Into testarabic values(N''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')'

Exec(@.SQL)

Extremely weird SQL problem

Figure this...
INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
says ...
648842 rows affected
however...
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
only returns 267564
in the Query Analyzer window.
And to top it all off... about 30 minutes after I
noticed it and investigated... it stopped happening.
Now both queries above affect the same number of rows.
This is the 4th time this has happened.
I haven't found any patters in log files or memory
settings or data types of fields.. .etc.
It's a brand new SQL Server 2000 Enterprise Edition.
6gb ram. Dual 3.06 ghz 1mb cache, Perc4/Dc Raid 10,
on 5x145gb scsi drives.
This problem wasn't happening on standard edition
that we migrated from about a month ago.
Any help would be greatly appreciated."Nick" <anonymous@.discussions.microsoft.com> wrote:
> Figure this...
> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> says ...
> 648842 rows affected
> however...
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> only returns 267564
> in the Query Analyzer window.
> And to top it all off... about 30 minutes after I
> noticed it and investigated... it stopped happening.
--
Hi Nick,
I would update statistics, free procedure cache and rebuild indexes if I see
inconsistent results like this. I will also review execution plans for any
discrepancies.
Hope this helps,
--
Eric Cárdenas
SQL Server support