SQL Database comparison

[dougancil]

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 what I'm trying to do is to compare two databases on the same server to see if any new data is entered into tcms_members.db o.memberdata compared to msbtotal.dbo.me mberdata and if so, the new data is written to a table (in the msbtotal database) called "newclients ." The data is checked via the ID field and if there is any new data in the tcms_members.db o.memberdata, it will be evident by there being a new ID number. The query runs fine and I tried adding "sample" data into the database to have it inserted into the new client table and that sample data wasnt added BUT I also didn't receive any errors. I can provide the sample data if necessary but I was wondering if anyone can see anything wrong with my query that may cause this? The server is a SQL 2000 server and I am running the query as the admin on the server, just as an fyi.

Thank you

Doug

[gpl]

A quick debugging question - do you get any results when you run just the select statement ?

[ck9663]

And...

If it's a simple check of record existence, an EXISTS() function might be faster...

Happy Coding!!!

~~ CK

[dougancil]

CK,

Does EXISTS work in SQL 2000? Also, I have ran just the select statement and it does in fact locate the "sample data."

Here's the error I get when I add the insert statement back into the query:

Server: Msg 208, Level 16, State 1, Procedure newcustomers, Line 4
Invalid object name 'msbtotal.dbo.m embers'.

I've checked all of the SP on this server and there is no procedure called "newcustome rs." Can anyone offer any advice as to why my server is telling me that there's a procedure that doesnt really exist.

Thanks

Doug

[gpl]

EXISTS is part of the basic SQL language, so yes it does work

You are trying to alter a procedure called members in the database msbtotal

as it doesnt exist, it cannot alter it -- look again at the code you have -- if you cannot see it, then post it for us to debug.

I suspect you have

ALTER  PROCEDURE msbtotal.dbo.members AS

instead of

ALTER  PROCEDURE YourProcedure AS

[dougancil]

QPL ... the code that I posted on my first entry is straight from my sql server. Please notice line 6.

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

[ck9663]

Your actual procedure name is YourProcedure ? Or you change it to newcustomers?

Fix that first and let's see...

~~ CK

[dougancil]

The procedure is called YourProcedure. I don't have a stored procedure called newcustomers. I've searched the entire server and every database on that server. There is no procedure called newcustomers.

[ck9663]

Since you don't have a parameter, you may actually run this:

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

from your Query window. See if it returns any record or gives an error.

~~ CK

[dougancil]

Ck,

That does not give an error.

[ck9663]

Now try this:

ALTER  PROCEDURE YourProcedure 
AS
   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

~~ CK

[dougancil]

CK,

That ran fine with no errors.

[ck9663]

So, your proc has been created. Try running it.

EXEC YourProcedure

~~ CK

[dougancil]

Ck,

That procedure did run now we just need the insert (or exist statement)

[ck9663]

One at a time, we're trying to isolate your problem...

Now, try to put the insert command...

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

Then run your proc...


~~ CK