You should change SQL Server Agent to run as a domain user account - then give this account access only to the resources needed (eg, your network share).

sp_executesql is certainly the cleaner way to go - it will work on SQL 2000 & 2005.

Firewalls will often get in the way (as they should )

You could see if you can have a VPN set up to bypass...

Oh, and security issues are fairly important with dynamic SQL.  Any dynamic SQL is executed in the security context of the caller of the stored proc, not the owner of...

Your proc will need to execute some dynamic SQL - this is often frowned upon for general SQL queries (too many debates - hope it doesn't fire up here!) but...

The only way to guarantee it would be to have a trigger.  You could also have as one of your rules that all table access is via stored proc only...

Try

select  distinct    InvoiceGuid     

    ,CreditNoteID

           ,InvoiceHeaderID

           ,InvoiceBatchID

           ,BillToAccountName

           ,InvoiceNumber

into #test2

from #test

Note that "select ... into ..." is fairly nasty syntax.  You' be better off (from a purist point of view only...

After I submitted it occurred to me that your overall question was about (local) and resolving that.

For (local) without anything else in the servername, this would have no instance and...

Hmmm...  Well on the workstation your software would have to store the server's name & instance so that's an application issue.

As for on the server...  Do you just want to...

If I didn't know otherwise I'd say you lived down-under at the moment.  Our prime minister (who was in your neck of the woods recently - much publicised over here,...

Oh and I had seen that video - very cool!

Two ways.

1.

Create a temp table.  Then insert the results of your exec statement.

EG.

declare @results table(x int, y int)

insert into @results(x, y)

exec('select col1, col2 from myTable where 1=2')

Silly example but you get...

Run some of the following queries.  The results are given for a server named SERVER using a SQL Server Instance called INSTANCE.

select ServerProperty('ServerName')   - returns SERVER\INSTANCE

You could also look at

select...

I assume that you want the new employeeID to be different - fair assumption.

In that case, with standard and straightforward SQL, no can do unfortunately...