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, 4 months ago by Ken McKelvey.
• This reply was modified 5 years, 4 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, 5 months ago by Ken McKelvey.
• This reply was modified 5 years, 5 months ago by Ken McKelvey.

If you do not need the datetime2 columns in Access you could try hiding them so they do not appear in SELECT *.

eg

ALTER TABLE YourTable ALTER COLUMN...

Looking at this again you are going to have to derive SysStartTime and SysEndTime from HistoryDate in price_history.

ps If you already have the start and end times you can use those instead of SysStartTime and SysEndTime but if they are not datetime2 you will have to change...