Here is a query that uses INFORMATION_SCHEMA that will run fine on 2000 and 2005. (Option 1 from ColdCoffee).

SELECT t.TABLE_CATALOG,

t.TABLE_SCHEMA,

...

Double-post...direct comments here: http://www.sqlservercentral.com/Forums/Topic1086425-148-1.aspx

What version of SSIS are you running? What OS? I know you posted in the 2005 SSIS Forum but we do not have a 2008 Forum and I saw you...

Cross-post, direct comments here: http://www.sqlservercentral.com/Forums/Topic1086425-148-1.aspx

Yes, it can be done without a cursor or loop. Whether you should use dynamic SQL will depend on what will consume the results. Is this a one-off request or...

TVFs may be a bit more flexible from a programming perspective especially if you're interested in capturing the results in a temp table (re: limitations on INSERT...EXEC).

If you choose TVFs,...

Sounds like a fun project...and that you're approaching it carefully and in your own time. That is a breath of fresh air. It sounds like SSMA has helped you get...

Disclaimer: my approach is shaped by my lack of awareness of an SSIS-specific tool/component that can do what you're asking so read with that in mind.

Initially I thought of writing...

chetanr.jain (3/30/2011)

---

I have apperaed for the same exam in last oct and got 980.

i have gone through the Self study guide of Microsoft for 70-432. Read out that book fully.

There...

I meant a motivation to use synonyms, specifically. Have you considered using views to accomplish the same end result?

Something like this:

CREATE VIEW dbo.account_data

AS

SELECT *

FROM dbo.Bi001 ;

GO

I am happy you were able to find a workable solution.

In the spirit of completely avoiding temp tables, hopefully improving performance, would something like this work?

SELECT a.ContactID AS...

Your connection string was slightly flawed. Try this:

IF OBJECT_ID(N'tempdb..#MyTable') > 0

DROP TABLE #MyTable ;

/* notice:

1. use SQLNCLI10 as the provider if you're accessing SQL 2008

2....

Do you have multiple CPUs in your server? And if so, what is your server's MAXDOP setting?

If SQL Server decided to employ parallel worker threads to execute your UPDATE then...

Is there a specific motivation in your organization towards using synonyms over views containing SELECT * FROM SCHEMA_NAME.TABLE_NAME in this scenario?

What are you trying...