Tuesday, March 27, 2012
@@Identity being over-written by Insert Trigger in stored procedure.
I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).
I have been using the @.@.Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an after insert Trigger has been added to the table being updated.
Now the @.@.Identity is returning the identity value of the trigger that was called instead of the original table insert.
Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).
Thank in advance.
Eamon.Look at SCOPE_IDENTITY and/or IDENT_CURRENT
Regards,
hmscott|||Thank you very much hmscott. SCOPE_IDENTITY works a treat!!!
Sunday, March 25, 2012
Replication timeout
I changed the 'sa' password and after that I got the following errors in the subscription job history:
1-Connecting to Subscriber 'SAONT045\REPORT'
2-Login failed for user 'sa'.
3-The process could not connect to Subscriber 'SAONT045\REPORT'. The step failed.
4-The job failed. The Job was invoked by User sa. The last step to run was step 1 (Run agent.).
What can I do??
Are there anything that I can do to solve that problem without recreate the replication again?
Thanks in advance
Manuel Rodriguesthis is just a hunch but is the sa password the same on both the publisher and subscriber.|||Sounds like you need to check security settings on the Distribution agent. I usually set it to impersonate using Windows authentication, since both SQLAgent services are running under the same Windows account (easier to maintain).|||Make sure the credentials used by SQL serer agent successfully logs into subscriber
Masanam
Thursday, February 9, 2012
(Simple) SQL question regarding P/F keys and inserting data...
I'm a newbie trying to understand the concept of referential integrity and
dealing with Primary and Foreign Keys. I'm sure mine is a simple problem...
I've created 3 tables as follows:
MemberTable
--
1 Member ID (Primary key)
2 Member Name
3 etc...
FoodsTable
--
1 Food ID (Primary key)
2 Food Name
MemberFoods
--
1 Member ID (Foreign key)
2 Food ID (Foreign key)
Now, I've just learned about referential integrity and all that, and this is
my first foray into creating primary/foreign keys, linking tables, etc. (so
I'm assuming the above tables are fine). What I don't understand is this:
if I have a (checkbox) form from which members can choose their favorite
foods, how do I insert that data? For example:
Food Form
--
Which of these are your favorite foods:
1) Steak
2) Chicken
3) Potatoes
4) etc...
If I want to insert, let's say, 'Steak' and 'Chicken', do I have to first do
a query on my Foods table to retrieve the Food ID's for Steak and Chicken,
and then do an "insert" using those ID's into my MemberFoods table? (I am
using the actual Food Names (not ID's) as values on my form.) If so, can
someone please share the SQL for retrieving and then inserting?
I don' t know...maybe I'm overcomplicating the issue, but it just seems like
a lot of extra work to maintain a "linking table" of foreign keys.
Thanks for the help.Replied in microsoft.public.sqlserver.programming
Please don't multi-post.
--
David Portas
----
Please reply only to the newsgroup
--