Even the table variables get stored in Temp db, if you wanna check try this script

Use tempdb

select count(*) from sysobjects where type...

If your table(#temp) is really big , it is advicable to use Temp tables rather than table variable(@table)

Regards,

Sudheer

Just a thought, why cannt you put all your transformation logic in a stored procedure and return xml string, so that it can minimize round trip

Now varchar(max) holds up to 2GB. To know more abt it, you can check

http://msdn.microsoft.com/SQL/2005/2005Webcasts/OverviewQandA.aspx

HTH,

Sudheer

Hi,

For this you can check the link http://spaces.msn.com/members/sudheerpalyam/Blog/cns!1pKCMhBsSwPMevqFfdi-3JgQ!166.entry

I dn't think you have a straight forward solution for this.

I would advice you , to store the data in single database.

Yah i have seen the analysis service section, but i thought this place was more appropriate. Anyways thnx for help

I agree with Rick, linked server is too slow when you need to get enormous amount of data.

One thing i can suggest is, use Sql server Dts and get...

Hi,

I adivce u to check u r connection string. Alternatively u can look at http://www.connectionstrings.com/

HTH,

Sudheer

Hey,

i faced the same situation some time ago, but i converted the execute sql task to active x script and then used the global variables.

sudheer

Hi,

thnx for that but i have other prob with other table

my data is like this

PartnerId   NativeId  FirstName

2000           45         Sudheer

2000           45          SUdheer1

3000           46           Mytest

3000           46           Mytest1

4000           47           Mytest2

4000           47          Mytest3

.... and...

HI,

i m using sql server 2000 .

Error message is

SQLState = 22005, NativeError = 0

Error = [Microsoft][ODBC SQL Server Driver]Invalid

character value for cast spec

ification

and string i used for bcp is...

hi,

thnx for advice that worked fine

cheers,

sudheer

Yah u got my scenario right,thnx for ur advice

cheers,

sudheer