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.dbo.memberdata compared to msbtotal.dbo.memberdata 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.dbo.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.members'.

I've checked all of the SP on this server and there is no procedure called "newcustomers." 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

[dougancil]

CK,

here's the error when I run my proc:

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

[ck9663]

Did you run it this way:

 
EXEC YourProcedure 
 
 

[dougancil]

I did. Just like that.

[ck9663]

And you run it in a new query window, not being called inside another proc? Are you in the same DB? Are you calling any other proc? What version of ms sql server are you using?

~~ CK

[dougancil]

Nope, the query is ran in a new query window. I am in the same db. I am not calling any other proc and this is running on a SQL 2000 server.

[ck9663]

The only remaining thing that I can think of is to list the column names that you are populating on the INSERT clause and the corresponding column name you are trying to insert to the table on the SELECT clause.

You might also want to create a copy of newclients table with a different name and insert it to that table instead.

Are you using any CALCULATED columns? Functions? Triggers? Constraint? etc?

~~ CK

[dougancil]

I can try to create a new table and insert it into that but in answer to your other question, I'm not using any other triggers, or constraints or any calculations.

Doug

[dougancil]

CK,

I created a new table (newclients2) and inserted the data. For whatever reason that worked. It seems that the table "newclients" had something wrong with it, but I've fixed that and it seems to work correctly.

Thanks for your help.

Doug