Got a weird one for ya....

Question

Got a weird one for ya....

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24692 More actions · Answer 1

Quick eyeballing of that, to me it looks like it is more likely:

SELECT custid as newCustID, classid as newClassID, [name] as newName, billname as newBillname, team as newUser3
        FROM Customer  where Customer.CustID not in (select CustID from  dummyCustomer)

that is making things unhappy, no? you already said that Customer is not on that database but a linked server and then you are calling it directly instead of using the 4 part naming?

Also, sorry I missed that "return" in the SP. it was a lot of text and I must have missed that.

EDIT - should clarify - the above is from the CROSS JOIN on your first INSERT.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Though so. Here's the problem.

FROM dbo.sfaCUSTOMER cross join (SELECT custid as newCustID, classid as newClassID, [name] as newName, billname as newBillname, team as newUser3 FROM Customer where Customer.CustID not in (select CustID from dummyCustomer)) newCust

It's in the SELECT portion of all of those inserts. The above is from the first, then there's

FROM dbo.sfasoaddress cross join (SELECT custid as newCustID, [name] as newName FROM Customer where Customer.CustID not in (select CustID from dummysoaddress)) newCust

and finally

FROM dbo.sfacustomeredi cross join (SELECT custid as newCustID FROM Customer where Customer.CustID not in (select CustID from dummycustomeredi)) newCust

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lk4772 Ten Centuries Points: 1030 More actions · Answer 3

bmg002 - Monday, June 26, 2017 3:50 PM
Quick eyeballing of that, to me it looks like it is more likely:
SELECT custid as newCustID, classid as newClassID, [name] as newName, billname as newBillname, team as newUser3
        FROM Customer  where Customer.CustID not in (select CustID from  dummyCustomer)
that is making things unhappy, no? you already said that Customer is not on that database but a linked server and then you are calling it directly instead of using the 4 part naming?
Also, sorry I missed that "return" in the SP. it was a lot of text and I must have missed that.
EDIT - should clarify - the above is from the CROSS JOIN on your first INSERT.

O
M
G!
How the hell did I miss that??? The weird part is, it's the exact same code as what is in Production just changing the database names to point to the test ones...but Production is working? I've just double checked the Production job and it has the same thing - single name for the table.....Thank you sooo much! To everyone that helped and took the time to look!!

Mr. Brian Gale SSC-Insane Points: 24692 More actions · Answer 4

Glad we could help (although I think the majority of the thanks goes to Gail on this one). She asked for all of the information AND she found it being used 3 times in the code. I just looked for the first occurance.

And missing things like that is very easy when you have that type of SQL query to work with. I've had to work with some "all the SQL is on 1 line" stored procedures that are brutal to read and understand properly.
I highly recommend some form of TSQL formatter (I use RedGate SQL Prompt, but that guy isn't free. I have also used Poor Man's T-SQL Formatter, but it is not as powerful as SQL Prompt). It makes code manipulation and modification a lot more simple.

NOTE - I do not work for RedGate, I just really like that tool. Makes code verification process a lot more simple as I can format it to the company standard and it is a lot easier to work with.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

lk4772 Ten Centuries Points: 1030 More actions · Answer 5

Totally agree! I've been trying to when I have to go in and look at the sproc to update to a format I can read but just haven't had the time yet you get to them all 🙁

Thanks again to EVERYONE! 😀