Yech. Tuning dynamic queries can be one of the more troublesome issues. However, there is a way to start.

Get the SQL Statement that would be generated by the...

To replace the output, you use it in the SELECT (as shown). If you wanted to replace data in the table, you would need to use it with an...

Unfortunately, this question is ambiguous. If you mean:

"How can I remove al CRLFs from a COLUMN in my TABLE"

or

"How can I remove al CRLFs from a COLUMN in the...

Try using REPLACE, e.g.

SELECT REPLACE(MyField, 'Once Upon A Time, ', '') As TruncatedData

FROM MyTable

Looking over the query, I see several indications that this one "just grew", instead of being designed.

Below, I have cleaned up the query by

  1) removing the...

And you are absolutely correct, John. Gapitis can be... well, not deadly, but certainly a head cold for your database.

The Identity is fine and, if in a UNIQUE index without nulls allowed, is effectively a PK. As noted, if your DB is exceptionally busy (so that you might...

Also, note that the default logical filenames for database 'testing' are testing_data and testing_log, not test_data and test_log, no matter what the backup file is called. Changing your script...

Of course, I never noticed the "Or is there some other way to do it" portion of the post 😯

Perhaps, but the questioner specifically requested a method using DatePart. I use this when generating a YYYYMMDDHHNN strings, in preference to stripping formatting out of a CONVERTed datetime, because...

Yes, there is. This code will do what you need:

SELECT

Cast(Datepart(yyyy, GetDate()) AS char(4)) +

Right('0' + Cast(DatePart(mm, GetDate()) AS Varchar(2)), 2) +

Right('0' + Cast(DatePart(dd, GetDate()) AS Varchar(2)), 2)

Personally (since you said that the joins either use TENANCYID, SUPPLIERID, or EMPLOYEEID, I would add a non-unique index on each one, rather than creating a primary key on all...

Since there is no 64 bit MSDASQL provider, you have two choices:

1) Set up a 32 bit connection for 32 bit apps. See

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=381384&SiteID=1

for more information...

Another possibility is that the process that splits the index pages as needed has consumed all available memory at that point, and has to use the swap file for completing...

Yes, ODBC does support JOIN statements, and VB5 can handle them. This is not the issue.

Most likely, the issue is a misunderstanding of how the OUTER JOIN would...