I for one am having fun 😉 ...sounds like you are almost there JimS...keep us posted!

Try it this way:

USE BackupDatabase

GO

SET IDENTITY_INSERT TableA ON

DELETE FROM PrimaryDatabase..TableA

OUTPUT Deleted.PrimaryKey

INTO TableA (PrimaryKey)

WHERE 1 = 1

SET IDENTITY_INSERT TableA...

db_denydatareader may not be enough depending on what level of access the AD Group affords them. If they have permissions to create or drop tables then db_denydatareader will not prevent...

JimS-Indy (4/6/2011)

---

Just tried that. Exceeds nvarchar(max) limit....

Probly need to execute one at a time.

On second thought. It simply didn't print the whole command. Maybe it's a limitation of the print...

You'll want to copy the text from the output window containing the DROP FK commands, then in a new query window, paste the content and make a proc out of...

JimS-Indy (4/5/2011)

---

Oops, sorry. I figured that one out. I was asking about the script to remove and replace the FKs. It produces two tables, one after the other. Do I...

Given that you'll only be generating the schema once I was thinking you would just run the FK script in SSMS, copy the output to a new query window and...

I am not sure if that is configurable in SSMA but you can always rename then in bulk after the initial implementation.

Here is a script that deals with standardizing FK...

It's a chicken/egg problem...how will you run a query against an instance of SQL Server on a given hostname if you do not know what instances exist on that...

I would start by running the Upgrade Advisor against the 2000 server (during low utilization or off hours) to see what recommendations it makes and fully address each: http://msdn.microsoft.com/en-us/library/ms144256.aspx

Here too...

This will likely perform much better for you since you'll guarantee that all processing will take place on the remote server:

EXEC('delete from lsdb1.dbo.table1 where year(column1) >= 2011') AT [ls1];

In short, yes.

Some random thoughts...

- Try surrounding your identifiers that contain numbers with square brackets:

delete from [ls1].[lsdb1].[dbo].[table1] where year([column1]) = 2011

If that does not work try recreating your Linked Server without any...

You also mentioned allowig exec on all SPs...for that you can grant EXEC to a user at the schema or database level but this will also allow said user to...

I would use a Script Task to read line one of the file and then use a function to manipulate the workflow towards or away from processing the file per...