Creating text files for each row of the ouput

Question

Creating text files for each row of the ouput

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Here's how to do it... you will need to replace "dbname" with the actual name of the database. You will need to do like wise with "servername". Once you've tested it, you will need to change "tstPerson" with the actual name of your table (ie... Person)...

...and, yes... I tested it on my machine...

--========================================================================================
--      This section of code is just a test setup.
--========================================================================================
--===== Suppress the autodisplay of rowcounts for appearance and speed
    SET NOCOUNT ON

--===== Create a test table for the demo
 CREATE TABLE tstPerson 
        (
        ID_Number NCHAR(27) PRIMARY KEY,
        ForeNames NVARCHAR(32),
        Surname   NVARCHAR(40),
        Notes     NVARCHAR(2048)
        )

--===== Declare some test variables just to hold some notes
DECLARE @ShortText NVARCHAR(2048)
    SET @ShortText = 'This is text less than 100 characters and will not be included.'
DECLARE @LongText  NVARCHAR(2048)
    SET @LongText  = 'This is text that is more than one hundred characters and will '
                   + 'be included in the output because of the criteria for more than '
                   + '100 characters for purposes of this test.'

--===== Populate the test table with the data given and some notes
 INSERT INTO tstPerson 
        (ID_Number,ForeNames,Surname,Notes)
 SELECT '002054.2003801645150037.001','Nicorescu','Alina',@ShortText UNION ALL
 SELECT '002054.2003801732590187.001','Bularca Ionela','Bianca',@LongText UNION ALL
 SELECT '002054.2003801736180077.001','Bruno','Antonio',@ShortText UNION ALL
 SELECT '002054.2003805417190443.001','BIESZKE-WIERZBA','JOANNA',@LongText UNION ALL
 SELECT '002054.2003805716460903.001','Suarsana I','Kadek',@LongText

--========================================================================================
--      This section of the code is the demo of what I think you need to do
--========================================================================================

--===== Declare local variables
DECLARE @Cmd VARCHAR(8000) --Holds the necessary OSQL command to create the file
DECLARE @PersonCount INT   --Number of records inserted into the temp table for output
DECLARE @CurrentPerson INT --Person in the temp table being worked on

--===== Create a temp table to store people in for output to files
     -- This replaces the cursor but you could still use one if you want
     IF OBJECT_ID('TempDB..#People') IS NOT NULL
        DROP TABLE #People

 CREATE TABLE #People
        (
        RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        ID_Number VARCHAR(27)
        )

--===== Populate the temp table with people that have notes over 100 character long
 INSERT INTO #People
        (ID_Number)
 SELECT CAST(ID_Number AS VARCHAR(27))
   FROM tstPerson WITH (NOLOCK)
  WHERE LEN(Notes) > 100
  ORDER BY ID_Number

    SET @PersonCount = @@ROWCOUNT

--===== This loop replaces the cursor fetches... you could still use a cursor if you want
    SET @CurrentPerson = 1
  WHILE @CurrentPerson <= @PersonCount
  BEGIN

        --===== Dynamically create the necessary BCP command to create 1 file per ID_Number
         SELECT @Cmd = 'BCP '
                     + '"SET NOCOUNT ON SELECT ID_Number,ForeNames,SurName,Notes FROM dbname.dbo.tstPerson WHERE ID_Number = '''+ID_NUMBER+'''" '
                     + 'QUERYOUT "C:\Person_Notes\'+ID_Number+'" '
                     + '-S"servername" -T -c' + CHAR(13)
           FROM #People
          WHERE RowNum = @CurrentPerson

        --===== Display the command
          PRINT @Cmd

        --===== Execute the command
           EXEC Master.dbo.xp_CmdShell @Cmd, NO_OUTPUT

    SET @CurrentPerson = @CurrentPerson + 1
    END

--===== All done... display the directory contents with newest files first
   EXEC Master.dbo.xp_CmdShell 'Dir C:\Person_Notes /O-D'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 2

Hi Jeff

Below is the ouput i get when i run only the lower part of the code you have sent (not the test):

Volume in drive C has no label.

Volume Serial Number is 302F-4488

NULL

Directory of C:\Person_Notes

NULL

18/09/2006 10:24 <DIR> ..

18/09/2006 10:24 <DIR> .

0 File(s) 0 bytes

2 Dir(s) 35,117,867,008 bytes free

NULL

The path (c:\person_Notes) exists in the server. Wondering what is causing this error.

Also the test in the beginning of the script is working until inserting the data into the tstperson (select * from tstperson returns rows). But when you do a select * from #people (once you have created the temp table and inserted the data from tstperson), it returns nothing! But the actual code works with select * from #people returning rows without the test bit! I'm really baffled at this...

Anyways, any ideas as to what might be causing the above error?

Thanks a zillion for your help Jeff.

Vijay

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 3

Jeff

any luck? I have to finish this today! Your code will work once we eliminate the error i mentioned above.

With my code, i get the below error:

Copy direction must be either 'in', 'out' or 'format'.

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n native type] [-c character type] [-w wide character type]

[-N keep non-text native] [-V file format version] [-q quoted identifier]

[-C code page specifier] [-t field terminator] [-r row terminator]

[-i inputfile] [-o outfile] [-a packetsize]

[-S server name] [-U username] [-P password]

[-T trusted connection] [-v version] [-R regional enable]

[-k keep null values] [-E keep identity values]

[-h "load hints"]

NULL

Thanks

Vijay

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Yes... where is the C:\Person_Notes directory... on the server or on your desktop?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 5

Jeff

It is on the server...

Do i have to mention it as \\support_svr1\path?

Thanks

Vijay

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 6

Jeff

I tried changing the path and made sure the path exists. I also made sure the user(sa) has write permissions to that folder.

But still no luck....

Thanks

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Shouldn't have to because the server should be able to see it's own drives. Dunno what's wrong because the code I wrote works fine on my server.

Also, dunno what kind of changes you made to the code after my suggested code... you might want to post the newly offending code so we can check for phat-phingers and the like...

Sorry I haven't been able to fix this one remotely...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

User "sa" does not exist is OS environment.

Check out which user starts SQL Server service on your machine. And make sure that user can access that folder.

_____________
Code for TallyGenerator

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 9

Jeff

The below is the code i have used. This is exactly the same as the one you have sent except that i've changed the table name from tstperson to person.

I checked the permissions on the folder and all things possible. Still no luck!

--DBCC DROPCLEANBUFFERS

--DBCC FREEPROCCACHE

--Holds the necessary OSQL command to create the file

DECLARE @Cmd VARCHAR(8000)

--Number of records inserted into the temp table for output

DECLARE @PersonCount INT

--Person in the temp table being worked on

DECLARE @CurrentPerson INT

--Create a temp table to store people in for output to files

IF OBJECT_ID('TempDB..#People') IS NOT NULL DROP TABLE #People

CREATE TABLE #People (RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ID_Number NCHAR(27))

--Populate the temp table with people that have notes over 1000 character long

INSERT INTO #People (ID_Number)

SELECT CAST(ID_Number AS NCHAR(27))FROM Person WITH (NOLOCK) WHERE LEN(Notes) > 1000 ORDER BY ID_Number

--select * from #people

SET @PersonCount = @@ROWCOUNT

--This loop replaces the cursor fetches...

SET @CurrentPerson = 1

WHILE @CurrentPerson <= @PersonCount

BEGIN

--Dynamically create the necessary BCP command to create 1 file per ID_Number

SELECT @Cmd = 'BCP'+ '"SET NOCOUNT ON SELECT ID_Number,ForeNames,SurName,Notes FROM NCLREMOTE_SP6D.dbo.Person WHERE ID_Number = '''+ID_NUMBER+'''"' + 'QUERYOUT "c:\Person_Notes' + ID_NUMBER +'"' + '.txt -S "SUPPORT_SVR1" -T -c'

+ CHAR(13)FROM #People WHERE RowNum = @CurrentPerson

--Display the command

--PRINT @Cmd

--Execute the command

EXEC Master.dbo.xp_CmdShell @Cmd, NO_OUTPUT

SET @CurrentPerson = @CurrentPerson + 1

END

DROP TABLE #people

--All done... display the directory contents with newest files first

EXEC Master.dbo.xp_CmdShell 'Dir c:\Person_Notes /O-D'

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

I'll try running the code you posted back when I get home from work, tonight. Looks identical... dunno by more than casual glance why you are getting the BCP error...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

Hmmm.... the code is NOT exactly the same... you added .Txt to the file name and messed up on some spacing (ie. BCP SET came out as BCPSET in your code as did other words)... copy and paste the following over the BCP command in your code...

--Dynamically create the necessary BCP command to create 1 file per ID_Number         
SELECT @Cmd = 'BCP '+ '"SET NOCOUNT ON SELECT ID_Number,ForeNames,SurName,Notes FROM NCLREMOTE_SP6D.dbo.Person WHERE ID_Number = '''
+ID_NUMBER+'''"' + ' QUERYOUT "c:\Person_Notes\' + ID_NUMBER + '.txt" -S "SUPPORT_SVR1" -T -c' 
  + CHAR(13)FROM #People WHERE RowNum = @CurrentPerson

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 12

Vijay,

I ran the query that Jeff send and it works. You need to change the database Name after "FROM" CLAUSE and the SErverName after "-S". Jeff's code assumes that your windows login has rights to connect to the server. If u don't then u need to provide SQL User Name and password as well.

Run this in query analyser and copy the command text to command shell and try running the command that will point u in the right direction.

Thanks

Sreejith

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

Try the corrections I made to the BCP first (my last post)... and since you must be running this as an "SA" user to even use the xp_CmdShell, the -T (trusted user option) should be just fine... for goodness sake, do not hardcode the user name and password in the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 14

> for goodness sake, do not hardcode the user name and password in the code.

Is it your every day pray?

It's definetely mine.

Unfortunately it did not help much by now.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 15

Serqiy, my old friend... I'm sometimes amazed at the things people do that will either violate the security of the server, violate the integrity of the data, or just absolutely crush the performance of a server. I've found that "praying" about this just doesn't help because, as it turns out, it's not a prayer hall... it's a pool hall I'm starting to get a bit cynical in my old age

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)