September 22, 2010 at 9:32 am
I have the following query:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE YourProcedure AS
insert into msbtotal.dbo.newclients
SELECT tcms_members.dbo.memberdata.* FROM tcms_members.dbo.memberdata left outer join
msbtotal.dbo.memberdata on tcms_members.dbo.memberdata.id =
msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
null
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and if I remove the "insert" statement, this query works fine, as soon as I add the insert statement back, I receive the following error:
Server: Msg 208, Level 16, State 1, Procedure newcustomers, Line 4
Invalid object name 'msbtotal.dbo.members'.
I am running my query as admin on the server and do not have a procedure called newcustomers.
Can anyone suggest a reason why I'm getting this error?
Thank you
Doug
September 22, 2010 at 9:38 am
Does the table 'msbtotal.dbo.members' exist?
The SQL logic requires the table and will likely throw that error if the table does not exist.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 22, 2010 at 10:28 am
That table does in fact exist. Again, if I run the select query without the insert statement, it runs just fine. It's only when I include the "insert" part of the query, that I get this error back.
September 22, 2010 at 10:34 am
doug 40899 (9/22/2010)
That table does in fact exist. Again, if I run the select query without the insert statement, it runs just fine. It's only when I include the "insert" part of the query, that I get this error back.
oops. My mistake; I was looking at the wrong table.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 22, 2010 at 10:38 am
Is msbtotal.dbo.newclients a view that references the members table?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 22, 2010 at 10:41 am
Alvin,
I have no idea ... how would I go about checking that?
September 22, 2010 at 10:59 am
The most likely suspect is a trigger on the table you're inserting into.
September 22, 2010 at 11:07 am
Spencer,
How can I find a trigger? I built the table "newclients" specifically for this task. It was pretty much a "default" set up (except for the table and column names of course)
September 22, 2010 at 11:53 am
Don't you have a DBA you could ask?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 22, 2010 at 11:57 am
Alvin,
For all intents and purposes, I am the DBA. We run a small network here and we really don't have a DBA so I have to not only maintain the network but wear the dba hat as well.
September 22, 2010 at 12:34 pm
Alvin,
This was fixed by creating a new table called Newclients2 and having data inserted into that table. For whatever reason, when I tried doing this with the newclient table, this wouldnt work. I have since corrected the problem. My one question though is everytime I run the insert, into the newclient2 table, if duplicate data is already there, it will not check for that. Can you offer a suggestion to check the new client database as well to make sure that I'm not replicating data there too.
Thank you
Doug
September 22, 2010 at 12:45 pm
doug 40899 (9/22/2010)
Alvin,This was fixed by creating a new table called Newclients2 and having data inserted into that table. For whatever reason, when I tried doing this with the newclient table, this wouldnt work. I have since corrected the problem. My one question though is everytime I run the insert, into the newclient2 table, if duplicate data is already there, it will not check for that. Can you offer a suggestion to check the new client database as well to make sure that I'm not replicating data there too.
Thank you
Doug
You're not going to like my suggestion. Hire someone who knows what he or she is doing, before you end up with a mess you cannot handle.
Would you take a long trip with a driver that doesn't know how to drive? Then why run a business that uses SQL Server without having someone on board that knows what they're doing?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 22, 2010 at 12:53 pm
doug 40899 (9/22/2010)
Alvin,This was fixed by creating a new table called Newclients2 and having data inserted into that table. For whatever reason, when I tried doing this with the newclient table, this wouldnt work. I have since corrected the problem. My one question though is everytime I run the insert, into the newclient2 table, if duplicate data is already there, it will not check for that. Can you offer a suggestion to check the new client database as well to make sure that I'm not replicating data there too.
Thank you
Doug
The duplicate should be able to be avoided through the use of a good primary key.
As for the trigger, you should be able to expand the table and see an option for triggers. It is there that you would find a trigger if it existed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 12:57 pm
Hold on.
We don't know if the problem was caused by a trigger, a view, or anything else.
We also don't know what problems might be caused by using a new table.
Please, be careful before offering suggestions based on assumptions.
What Jason just posted will help look for a trigger, but we don't know if that is the problem.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 22, 2010 at 1:31 pm
Alvin Ramard (9/22/2010)
Hold on.We don't know if the problem was caused by a trigger, a view, or anything else.
We also don't know what problems might be caused by using a new table.
Please, be careful before offering suggestions based on assumptions.
What Jason just posted will help look for a trigger, but we don't know if that is the problem.
Agreed
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply