need txt file,sp,SQL help. TIA.

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

SQL Server Development (2000)

need txt file,sp,SQL help. TIA.

Author

Topic

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-20 : 11:57:01

Hello, First of all thanks in advance for your help. What I need to do is to query the data from a table and have it sent to a txt file for a vendor. The tricky part it that the file must have a header with the rowcount of the data in it. I am rusty with my SP writing and SQL writing. How would you go about this? Can I just write a SP to have the header placed into a few fields in a temp table and have the table data into another temp table and combine them in a table and use DTS to send them to a file? I hope I haven't confuesd anyone. If that will work, I don't really know the syntax to do it. Thanks in advance.

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-20 : 15:16:31

Bump :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-20 : 15:24:26

You haven't provided your DDL and sample data, so it's hard to help you with a query.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-20 : 15:47:02

I am using SQL 2000. Lets say I have TableX with fields Fname and Lname. I need the query to pull all of the data in TableX and have it be in a .txt document with a header with the following format:

Description: bla bla
Source: test bla
BatchID: 1
Created: today
Version 4.1
RecordCound: 6

Doe John
Doe Jane
Bush George W.
Kerry John
YourLastName YourFirstName
Lname1 Fname!

Is that a better description? Thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-20 : 15:59:03

Here you go:



use pubs
go

create table tablex(fname nvarchar(256), lname nvarchar(256))
go

insert into tablex values('tara', 'duggan')
insert into tablex values('john', 'smith')
go

create view SomeView
as
select 'Description: bla bla' as Column1
union all
select 'Source: test bla' as Column1
union all
select 'BatchID: 1' as Column1
union all
select 'Created: today' as Column1
union all
select 'Version 4.1' as Column1
union all
select 'RecordCount: ' + convert(varchar(10), count(*)) as Column1
from tablex
union all
select '' as Column1
union all
select lname + ' ' + fname as Column1
from tablex
go

EXEC master.dbo.xp_cmdshell 'bcp "select * from pubs.dbo.SomeView" queryout "c:\somefile.txt" -c -Stduggan2 -T -r\r\n'

drop view SomeView
go
drop table tablex
go

c:\somefile.txt will be created on the database server as xp_cmdshell runs from the server and not from your client machine.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-21 : 14:12:07

Thanks Tara. Well. I ran the code and this is the result that I got. Can you explain the error to me? Thanks.

(1 row(s) affected)

(1 row(s) affected)

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
NULL

(5 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 14:18:02

You got disconnected from SQL Server for some reason.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-21 : 14:32:38

Can you tell me how to change the inserts from hard coded data to select the lname and fname from a different table (TableA)? Thanks. I promise I will not bother you too much more :)

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-21 : 14:37:28

I figured it out. Thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 14:38:03

The inserts are just so that I can show you how this is done. My code is designed so that you can copy and paste the entire thing into say Northwind database and run it.

So for your solution, you would just create a view that creates the result set like I have, then use xp_cmdshell to use bcp.exe to run the view.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-21 : 15:29:16

Okay. Thanks again. now.... :) In the header I am going to be giving the exact spacing for the lname and fname that is going to be displayed for the vendors system to read. Example: They lname is always 16 spaces. How do I force that in my results? I hope I didn't confuse you.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 16:24:40

Do you mean this:

select REPLICATE(' ', 16 - LEN(lname)) + lname
from tablex
go

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-21 : 16:35:21

Lets say that there is 16 spaces and their name only takes up 3. The first 3 should be their name and the next 13 should be spaces before the next field begins.
Pretend that "-" represents spaces

BOB-------------SMITH
VICKI-----------SMITH

Thanks again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 16:39:26

select fname + REPLICATE(' ', 16 - LEN(fname)) + lname
from tablex

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-21 : 16:48:33

THANK YOU SO SO SO MUCH!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources