Kim Crosser (10/19/2015)

---

Recently I have been using 5000 rows as a reasonable tradeoff between speed and system resources, but when I saw the poster doing 50,000 rows in a single...

If deleting, say, 90% of the table then worth considering "copying" the 10% to be retained into a new temporary table, and dropping old table / renaming temporary table. ...

IDENTITY_INSERT can only be ON for one table at a time. If it is ON for a different table, already / as part of a previous command that did not...

When we program a loop to bulk delete rows we adjust the Batch Size according to how long the iteration takes. We increase batch size by 20% if the...

We um-and-ah about having a subfolder for each database.

Whenever we need to port all the backups for all databases, and all log backups since the last FULL, to a different...

fernando.arias (10/15/2015)

---

--- But it gave me this message:

Msg 336, Level 15, State 1, Line 5

Incorrect syntax near 'Fernhl'. If this is intended to be a common table expression, you need...

peter-757102 (10/15/2015)

---

The App writes a new file using a filename that contains a timestamp and UUD. Then another asynchronous process pulls those files in, batches them and takes care of...

How does the APP write to a text file, for later bulk-import, in a multi-user friendly way? Isn't it going to have to open the file, lock it, read...

The error messages seem confusing to me (in light of your description). Wouldn't be the first time that a cascade of error messages has obscured the real issue ...,...

I presumed that "DATABASE" in your message "Description: "Cannot open database "DATABASE" requested by...

I know nothing about this, just making some observations.

Elapsed time was 16.942 seconds - doesn't seem to tie with a Login Timeout of either 10 or 30 seconds?

"Cannot open database...

Our accounts department (all of two people, both part time 🙂 ) uses an accounting package that requires the user to do backups / restores. Its definitely not using...

Might be worth adding your ID to make the Clustered Index unique (and then explicitly declaring the Clustered Index as UNIQUE) ... otherwise SQL will be adding a uniquifier value...

P.S. I presume the Clustered Index Key (i.e. on POLICY_TRANSACTION_BKEY) in your table [F_POLICY_TRANSACTION] is unique / PKey?

Nope! :crying:

I'd suggest trying this to help with debugging:

SELECT TOP (0) IDENTITY(int,1 ,1) as ID, POLICY_TRANSACTION_BKEY

INTO #TEMP

FROM [F_POLICY_TRANSACTION]

-- Create a clustered index for efficiency

CREATE [highlight="#ffff11"]UNIQUE[/highlight]...