Convert data to text file using #temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Convert data to text file using #temp table

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2006-08-15 : 10:34:19

I am trying to make some computations on some data in a SQL table and extract it out as a text file. I have the SQL query in the following format.



Select Col1, Sum(Col2) Col2_Sum, Sum(col3) Col3_Sum
Into #temp
From TableA
Group by Col1

Select * from #temp

Drop table #temp

This executes fine when I run it in Query Analyzer but throws an error pointing at the 'group by' when I place it in the DTS. But if I remove the #Temp table from the query and have it as ...



Select Col1, Sum(Col2) Col2_Sum, Sum(col3) Col3_Sum
From TableA
Group by Col1

...it executes fine.

Any help will be appreciated. and please note that the computations is much more complicated and hence I will very likely need the #temp table.

Thank You.
PKS.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-15 : 10:36:40

Try putting it in a stored proc.
You can then use bcp to create the text file (need to add set fmtonly off before the call or populate a permanent table).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sahu74
Posting Yak Master

100 Posts

Posted - 2006-08-15 : 11:10:08

NR,
Thank you for your suggestion. But I do not have physical access to directory/folder on the SQL server. Do you think BCP will be possible without that? I am under the impression that it is not possible. Please correct me if I am wrong.

Thank you.
PKS.

quote:
Originally posted by nr

Try putting it in a stored proc.
You can then use bcp to create the text file (need to add set fmtonly off before the call or populate a permanent table).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-15 : 11:29:36

bcp runs on the server (if you run it using xp_cmdshell) - it would be the sql server service that needs access probably.

If you don't have access to the folder then you can't use dts (which is a client application - you could also run bcp on the client) in the designer. If you release it to the server then it will probably need the same permissions as bcp to access the folder.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sahu74
Posting Yak Master

100 Posts

Posted - 2006-08-15 : 11:36:54

Thank you.

PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources