You need to pivot the 'phone numbers.

Also it looks as though some of your outer joins should be nested.

Search for both of these.

If you want tested code you need to...

https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

The tally table works, as long as the use of the numbers is consistent, and scans the table once.

UNION ALL scans the table four times.

Making the test rig more user friendly:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #PO
(
ProductCategory varchar(5) NOT NULL
,ProductSubCategory varchar(5) NOT NULL
,[Total Orders Placed in North America] [int] NULL
,[Total Orders...

It looks like you actually need to unpivot. This can be done by joining to some numbers, to increase the rows, and then use a CASE.

If you can be bothered...

Make sure your default db is not MyDBName and use:

WITH ROLLBACK IMMEDIATE

Thanks.

Incidentally I just tried running the following:

DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;
PRINT @@ERROR;

which gave a result of 2706.

ie When EXTEND_LOGICAL_CHECKS are included in CHECKDB then...

There are no error messages. If I just run:

DECLARE @SQL nvarchar(MAX)
,@RetVal int;

SET @SQL = 'DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;'
EXEC @RetVal = sp_executesql @SQL;

then I...

In relational databases, relations (tables) are unordered sets.

It looks are though you just want to copy the id from TableA to TableB:

SET IDENTITY_INSERT TableB ON;

INSERT INTO TableB...

Does the stored procedure insert/update/delete tables from more than one database?

If if does then you will need to look at the login permissions in each database.

• This reply was modified 5 years, 6 months ago by Ken McKelvey.
• This reply was modified 5 years, 6 months ago by Ken McKelvey.

You need to understand Logical Query Processing. There are plenty of articles on this. eg:

https://www.sqlservercentral.com/blogs/sql-server-logical-query-processing

FROM; with JOIN, APPLY etc; is always evaluated first.

I suspect the Oracle view, XXRCR.XXRCR_CUR_EMPS_EVER_V, has a non-deterministic way of obtaining ORGANIZATION_FULL_NAME. ie The problem is likely to be with how the Oracle view is defined and so has...

-- *** Test Data ***
CREATE TABLE #t
(
YourDateStamp datetime NOT NULL PRIMARY KEY
);
INSERT INTO #t
VALUES ('20200418'),('20200425'),('20200518'),('20200525'),('20200618'),('20200625');
-- *** End Test Data ***

SELECT T.YourDateStamp
FROM #t T
CROSS APPLY
(
VALUES( DATEADD(day, 20, DATEADD(month,...

Using FOR XML PATH with .value is the normal approach for this. See just before 'Using Common Language Runtime' here:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

or maybe create a view where the datetime2's are cast as datetime and use the view in Access.

• This reply was modified 5 years, 7 months ago by Ken McKelvey.
• This reply was modified 5 years, 7 months ago by Ken McKelvey.