SP to create a BCP format file
-
I need to create a different variant of a base format file for each of MANY databases. All that changes are the field sizes. How can I have a stored procedure write out the file?
My base format file is like:
<BCP.fmt>
7.0
12
1 SQLCHAR 0 4 "\t\"" 1 Match_Cluster
2 SQLCHAR 0 ### "\"\t\"" 2 Ven_ID
3 SQLCHAR 0 ### "\"\t\"" 3 VName
4 SQLCHAR 0 ### "\"\t\"" 4 Address1
5 SQLCHAR 0 ### "\"\t\"" 5 Address2
6 SQLCHAR 0 ### "\"\t\"" 6 City
7 SQLCHAR 0 ### "\"\t\"" 7 State
8 SQLCHAR 0 ### "\"\t\"" 8 Zip
9 SQLCHAR 0 ### "\"\t\"" 9 Phone
10 SQLCHAR 0 ### "\"\t\"" 10 Fax
11 SQLCHAR 0 ### "\"\t\"" 11 TaxID
12 SQLCHAR 0 10 "\"\r\n" 12 RecSource
</BCP.fmt>
I'm able to use SP_Columns to determine the numbers to plug in. But I keep getting errors like "Server: Msg 170, Level 15, State 1, Line 82
Line 82: Incorrect syntax near '12 SQLCHAR 0 10 "\"\r\n" 12 RecSource'" whenever I use either XP_CmdShell or SP_OAMethod to write the text file...
Robert
-
SOP kluge
1. create a select statement that returns the rows (as 1 string column) you're looking for (e.g. join to syscolumns, etc. to get relevant data-- 1 row per column per table) that is, a statement that gives you rows that look like: "4 SQLCHAR 0 ### "\"\t\"" 4 Address1"
2. create a ## temp table with an int col for row# and VARCHAR(1000) for output text
3. put the output into that temp table, insert any extra rows where needed (prefix suffix0
4. shell out (sp_cmdshell) and use BCP to BCP the text of that temp file into your designated format file
5. integrate all of these by using more select statements to do the entire database at the same time
Or, if you like, do it the way the next few responders are going to suggest: "use a cursor..."
-
Thanks for the feedback. It turns out to be a combination of "cannot pass that quote stuff to ECHO" and coding error (used parentheses around string with noting concatenated on one line)... Use parens only when needed (or put value into a variable, & then pass the variable) and Scripting.FSO...and viola it works!
-
I have used this myself. I'm no not recall where I picked it up from but, it is very helpful. Use it in Query Analyser and just cut and paste from the output.
set nocount on
declare @tblname sysname
set @tblname = 'Enter a table name here'
select '8.0' + char(13) + char(10) + char(13) + char(10) +
(select cast(max(b.colid) as varchar)
from sysobjects a join syscolumns b
on a.id = b.id and a.name = @tblname)
+ char(13) + char(10) + char(13) + char(10)
Select cast(y.colid as varchar) + space(8 - len(y.colid)) + 'SQLCHAR' + space(7) + '0' + space(7) +
cast(y.length as varchar) + space(9 - len(y.length)) +
case when y.colid = (select max(b.colid)
from sysobjects a join syscolumns b
on a.id = b.id
and a.name = @tblname)
then '"\r\n"' + space(4)
else '""' + space(8)
end +
cast(y.colid as varchar) + space(6 - len(y.colid)) + convert(varchar(48),y.name) +
space(50 - len(y.name)) + 'SQL_Latin1_General_CP1_CI_AS'
from sysobjects x join syscolumns y
on x.id = y.id
and x.name = @tblname
-
Jersey, I myself love working with base tables, but would it be easier to use the metadata reference in INFORMATION_SCHEMA.COLUMNS? I'm developing an Access app to work as sort of a progress tracker for my current SQL project and am finding it quite handy, thus I created the following view to assist:
CREATE VIEW dbo.vzTableColumnNames
AS
SELECT TOP 100 PERCENT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (table_name LIKE 'Ed%') AND (table_name NOT LIKE 'Edz%')
ORDER BY table_name, ordinal_position
The where is so that I only see my important tables, all else is fluff. Also, freely admitting that I'm not a BCP user or fan, I was a bit concerned that your script doesn't produce the same field sequence as the physical table. I recognized that there is a sequence number present, so it shouldn't make a diff, I just found it a bit confusing.
Just curious. Thanks!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Wayne:
Kudos for using the information schema provided by SQL server. I see your point about the provided script and that it maybe confusing. However, it is a example and can be adjusted for different enviroments.
Additionally, I have not come across an instance where the script did not return the correct physical order of a column within a table object.
Jersey
-
Jersey:
Hmmm. Here's the curious thing: I don't get the correct physical order when I run it! Here's a result:
-----------------------------------------
8.0
36
------------------------------------------------------------------------------------
36 SQLCHAR 0 1 "\r\n" 36 crlf
11 SQLCHAR 0 10 "" 11 EffectiveDate
21 SQLCHAR 0 2 "" 21 SchoolMailingState
22 SQLCHAR 0 5 "" 22 SchoolMailingZip
23 SQLCHAR 0 4 "" 23 SchoolMailingZipPlus4
10 SQLCHAR 0 60 "" 10 SchoolName
16 SQLCHAR 0 10 "" 16 SchoolPhoneNumber
12 SQLCHAR 0 1 "" 12 SchoolType
14 SQLCHAR 0 80 "" 14 SchoolWebAddress
5 SQLCHAR 0 2 "" 5 StateAgencyNumber
6 SQLCHAR 0 14 "" 6 StateLEAID
8 SQLCHAR 0 20 "" 8 StateSchoolID
13 SQLCHAR 0 1 "" 13 filler135
15 SQLCHAR 0 7 "" 15 filler216
31 SQLCHAR 0 30 "" 31 filler495
32 SQLCHAR 0 30 "" 32 filler525
33 SQLCHAR 0 50 "" 33 filler555
34 SQLCHAR 0 10 "" 34 filler605
35 SQLCHAR 0 80 "" 35 filler615
4 SQLCHAR 0 2 "" 4 FIPSStateCode
2 SQLCHAR 0 2 "" 2 FiscalYear
1 SQLCHAR 0 4 "" 1 ID
7 SQLCHAR 0 7 "" 7 NCESLEAID
9 SQLCHAR 0 12 "" 9 NCESSchoolID
3 SQLCHAR 0 4 "" 3 RecordNum
24 SQLCHAR 0 30 "" 24 SchoolLocationAddress1
25 SQLCHAR 0 30 "" 25 SchoolLocationAddress2
26 SQLCHAR 0 30 "" 26 SchoolLocationAddress3
27 SQLCHAR 0 30 "" 27 SchoolLocationCity
28 SQLCHAR 0 2 "" 28 SchoolLocationState
29 SQLCHAR 0 5 "" 29 SchoolLocationZip
30 SQLCHAR 0 4 "" 30 SchoolLocationZipPlus4
17 SQLCHAR 0 30 "" 17 SchoolMailingAddress1
18 SQLCHAR 0 30 "" 18 SchoolMailingAddress2
19 SQLCHAR 0 30 "" 19 SchoolMailingAddress3
20 SQLCHAR 0 30 "" 20 SchoolMailingCity
I'm not sure what the reason is, I don't see a logical grouping to explain it. As far as I recall, this table was created through a single create statement, I didn't add fields into the mix later.
I wonder if it could be service pack or sort order dependent?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Yes that is quite odd. I just ran it under the pubs db on my test server and here is the result. I removed the collation just to reduce the output.
What default collation of your db? Also what service pack are you on?
8.0
9
1 SQLCHAR 0 11 "" 1 au_id
2 SQLCHAR 0 40 "" 2 au_lname
3 SQLCHAR 0 20 "" 3 au_fname
4 SQLCHAR 0 12 "" 4 phone
5 SQLCHAR 0 40 "" 5 address
6 SQLCHAR 0 20 "" 6 city
7 SQLCHAR 0 2 "" 7 state
8 SQLCHAR 0 5 "" 8 zip
9 SQLCHAR 0 1 "\r\n" 9 contract
-
My database collation is SQL_Latin1_General_CP1_CI_AS.
@@VERSION on my development server is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
On my PC it's:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Collation is the same between the two installs, so my guess would be something in the service packs. Incidently, it runs in physical order on my PC, so there is definitely a difference.
I don't see it as a problem, or at least as a show stopper, you'd plainly see the issue when running your script and a simple order clause would fix it.
It is interesting though, ain't it? 🙂
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply