Global Temporary Tables

Hi guys...
This SP works:
/ ****************************************
***************/
Create Procedure tmpMF1
as
Create Table ##fun (line varchar(1000));
insert into ##fun (line) values ('Hello');
exec Master..xp_cmdshell 'bcp "select line from ##fun" queryout
"c:\fun.txt" -c -S"sstr1" -T';
drop table ##fun;
return 0;
/ ****************************************
***************/
Is there a way to set the table name ##fun to a different name every time
the SP is executed? I need to compensate for multiple users.
Thanks,
ForchHere's one way. Note that all usual warning about dynamic SQL applies:
-- Create Procedure tmpMF1
-- as
DECLARE @.uq char(36), @.sql varchar(2000)
SELECT @.uq = NEWID()
SET @.sql = 'Create Table "##fun' + @.uq + '" (line varchar(1000))'
EXEC(@.sql)
SET @.sql = 'insert into "##fun' + @.uq + '" (line) values (''Hello'')'
EXEC(@.sql)
SET @.sql = 'exec Master..xp_cmdshell ''bcp "select line from [##fun' + @.uq +
']" queryout "c:\fun' +
@.uq + '.txt" -c -S"TIBOR" -T'''
EXEC(@.sql)
SET @.sql = 'drop table "##fun' + @.uq + '"'
EXEC (@.sql)
--return 0;
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:E1221071-14C5-4FCF-8E4C-F53C847D8FDC@.microsoft.com...
> Hi guys...
> This SP works:
> / ****************************************
***************/
> Create Procedure tmpMF1
> as
> Create Table ##fun (line varchar(1000));
> insert into ##fun (line) values ('Hello');
> exec Master..xp_cmdshell 'bcp "select line from ##fun" queryout
> "c:\fun.txt" -c -S"sstr1" -T';
> drop table ##fun;
> return 0;
> / ****************************************
***************/
> Is there a way to set the table name ##fun to a different name every time
> the SP is executed? I need to compensate for multiple users.
> Thanks,
> Forch