Yep, except you'll get an error unless you remove the SET IDENTITY_INSERT ON/OFF statements as you're no longer inserting an explicit value into the Identity field (Transaction_Number).

Lowell - http://www.sqlservercentral.com/Forums/Topic1393096-392-1.aspx

Thanks

toddasd (12/6/2012)

---

We know from your other post that tblTransactions.Transaction_Number is an IDENTITY field. You are attempting to insert an Transaction_Number back into the IDENTITY field that was just created. It...

Transaction_Number is your PK (assuming the table definition is the same as in your other post) - you can't have duplicate values in it.

Hi Joe,

Your problem is #tmptbl2 is only present in the scope of the SQL executed via EXEC (@SQL1-4), and is out of scope when you try to select from it.

You...

Have you configured max server memory in SQL Server?

SQL loads data into RAM and will use as much memory as you allow it to.

Thanks Brian,

Are you wanting to put the next Transaction_Number Identity value into the Transaction_Number field?

And by next, do you mean next free Identity value or Transaction_Number + 1?

Cheers

Gaz

Hi, what's the datatype of the Week field?

Thanks

What's the definition of the tblTransactions table - I'm assuming you don't actually have a field in the table called @@IDENTITY?

And if I'm reading your question right, you want...

Apologies, misread the question. Perry's command is what you need.

No problem.

Still getting to grips with Powershell and very little of it is common sense so far 🙂

The hardest bit has been linking all the individual steps together - once...

If it's one result, you can get it like this:

$lastBackupFile.physical_device_name

This can be used in other methods, e.g.:

($lastBackupFile.physical_device_name).SubString(0, 30)

If it's multiple rows you can do something similar but include the row...

Run this on whichever is the principle at the time:

ALTER DATABASE dbname SET PARTNER FAILOVER

They'll need to be synchronized first though.

Powershell

Nice question on what quoted identifiers can let you get away with, shame about the extra ';' which means the table isn't created. 🙂

Check in Reporting Services properties (Parameters tab) that the parameter Data Type is DateTime - I'm not sure that RS overwrites the parameter types/defaults of an existing report.

Cheers