Creating Text Files Using Bcp

Jan 23, 2004

I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.

Help is appreciated.

I created a stored procedure with the below
declare @filename varchar(50),
@bcpcommand varchar(2000)

set @filename = 'c:
eportmedia.txt'
print @filename
set @bcpcommand = 'bcp "select * from table" queryout "'+ @filename -U -P'
exec master..xp_cmdshell @bcpCommand

View 10 Replies

Problems Importing Text Files With Double-quotes As Text Qualifier

Jul 14, 2006

I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:

The column delimiter for column "Column 1" was not found.

Any ideas on how to resolve this issue will be greatly appreciated.Thankspcp

View 15 Replies View Related

Linked Server To Text Files: Is Possible To Detect Changes Made To Those Files? (SQL Server 2005)

Sep 3, 2007

Hi gurus,

I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).

Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?

Thanks a lot in advance!

View 1 Replies View Related

Creating Log Files

Aug 22, 2000

Hello,
what is the procedure needed to create a log file that will log all events such as "what table was updated? when was it updated? who (nt user account) updated it? what record in the table was updated? what was the old value? new value?what caused an odbc error?....
I use access as a front end.
I would really appreciate an answer.
Thank you for your time,
Joe!

View 1 Replies View Related

Creating TXT Files

Jan 22, 2004

Can I use SQL Server to create a txt file on the c drive. Can sql server do nay kind of file handling. Is there a command that i can use in my background job to automatically create a txt file that will show data from a particular table.

Never done it before .. Any ideas...

Help is appreciated.

Thanks

View 3 Replies View Related

SP__SaveDTS Not Creating Dts Files

Mar 11, 2005

When I try and run SP__SaveDTS, all I get is page after page of this. Anyone know how to fix this?

Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):

Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E <Use trusted connection instead of /U /P>
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also specified)
/~R Repository Database Name <uses default if blank; loads package from repository database>

Package operation (overrides stored Package settings):
/~A Global Variable Name:typeid=Value <may quote entire string (including name:typeid)>
/~L Log file name
/~W Write Completion Status to Windows Event Log <True or False>

DTSRun action (default is to execute Package):
/!X <Do not execute; retrieves Package to /F filename>
/!D <Do not execute; drop package from SQL Server (cannot drop from Storage File)>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y and /!X)>

Notes:
~ is optional; if present, the parameter is hex text of encrypted value (0x313233...)
Whitespace between command switch and value is optional
Embedded whitespace in values must be embedded in double-quotes
If an option is specified multiple times, the last one wins (except multiple /A)

NULL

View 10 Replies View Related

Files Not Creating On Win2003

Oct 29, 2006

I have created a package which extracts data from a table and creates several files. This works fine on my PC (WinXP SP2), but when i deploy it to the server it doesn't work. I have also tried to create the package from scratch on the server as the administrator and it still has the same problem. Anyone have any ideas?

View 7 Replies View Related

Publication Creating Bcp Files On Server

Mar 24, 2006

using publication from server one to server two. On server one, I have several directories with bcp files. Can the older files and directories be deleted?

View 1 Replies View Related

Dealing With .dll Files After Creating Assembly

Apr 10, 2006

Hi
I want to use CLR for developing database object such as stored procedures.
I have read the "Getting Started with CLR Integration" from MSDN help and successfully create my first procedure.
I create an assembly in SQLServer2005 with this code:

CREATE ASSEMBLY helloworld from 'c:helloworld.dll' WITH PERMISSION_SET = SAFE

My questions are :
How should I deal with helloworld.dll after creating Assembly?
Can I delete this file?
What should I do for uploading my application on the webserver?
Should I upload any .dll file?

View 3 Replies View Related

Integration Services :: Creating Files From Their Binary Representations In DB

Apr 29, 2015

We run std 2008 r2, I need to recreate flat files from their varbinary(max) equivalents in our db. I have a mix of excel, pdf, word etc to recreate. Will ssis be a good tool for doing this? I'm wondering what transform(s) would be involved.

Perhaps I need to cast to varchar 1st and then land the data but if I recall correctly there is a maximum record length in ssis destination flat file rows. And I'm thinking I would have to map the varbinary (or cast equiv) to a row in the destination once for each file created.

View 4 Replies View Related

T-SQL And Text Files

Sep 26, 2006

I was asked at work to create an audit log to track user changes to our SQL Server 2005 database. My plans were to use a trigger to store the changes in a database table. I was just told that rather then storing these changes the database, that I should write these changes to a text file. I am having problems finding a good example of how to write to a text file in T-SQL. Does anyone have an example of how to do this? Thanks

View 2 Replies View Related

DTS For 100 Text Files

Jan 16, 2004

Hello DBA's:

I want to upload 100 text files to a single table on SQL Server 2000.
Records from these text files would be selected on the basis of a where clause.

What would be the best way of accomplishing this? Using DTS, I can do only one file at a time. Is there a faster approach.

Thanks

Vivek

View 9 Replies View Related

Help With Text Files

Oct 9, 2007

Hi,

I have a table T1 with three Cols.
Code VARCHAR(10),
INISetting TEXT,
TSystem VARCHAR(10).

Right now I are storing the INI Files directly in the database.
I would like to move this to text files in to a SAN box and just store the Pointers in the IniSetting Column and name the text files
some thing like Code.txt

When I do a SELECT Code , INISetting , TSystem FROM T1.

I want to read the Contents as is from the Text files stored in the SAN Box?.

Can some one help me with this.

Thanks
Venu

View 5 Replies View Related

Creating A Text File Using Ssis

Nov 28, 2007

Hi,

A short description of my prob :

I am looping through a set of files and on each loop i process the file and move it to another folder. I am using File System task to do so and variables with destination path and name. It works fine.

Requirement :

However now I want that after processing the file, instead of moving it, I create an empty text file at the destination containing the file name. I want to do this with minimum effort. Can anyone suggest me the way.

thanks.

View 1 Replies View Related

Importing Text Files

Apr 10, 2001

Hi,

I want to import multiple text files into a single table. I know I have to use BCP or DTS. But, I want import all files at once, instead of one at a time. And the file names are in sequence, viz. file1, file2, file3 etc.
Can anybody tell me, How I can achieve this.

Thanks

View 4 Replies View Related

READING TEXT FILES

Oct 24, 2001

I NEED TO READ A TEXT FILE INTO A SQL SERVER 6.5 TABLE. THE FILE HAS VARIABLE LENGTH FIELDS AND THE FIELDS ARE SEPARATED BY PLUS SIGNS ("+"). ANY IDEAS ? THANKS FOR YOUR TIME.

View 1 Replies View Related

Exporting Text Files

May 21, 2002

I would like to export SQLServer data from a table to a text file within a stored procedure that is fired from an after insert trigger. In Oracle the is a UTL_FILE Package that does this. Is there any way to do this in a SQLServer stored procedure.

View 1 Replies View Related

Importing Text Files

Jul 7, 2000

Has anyone written a SQL script to import ASCII comma or space delimited?

I am trying to load a PROGRESS exported file (Unix) into SQL SERVER 6.5 (NT).

Much appreciated,

Nickd

View 1 Replies View Related

Import Text Files

Aug 30, 1999

Hi ppl,
I'm trying to import a fixed lenght text file into a sql 7.0 table using the SQL command "BULK INSERT".

The text file is fixed length and end with a carriage return and linefeed. Fields aren�t delimited.

Wich definition of the "FIELDTERMINATOR" should i use ?

I need some Help here!!!
Thanks.

View 1 Replies View Related

Importing Text Files

Sep 19, 2001

I have to import 18000 text files into a sql database.
Each file contains 10 fields and around 5000 records.
I am currently doing this with DTS.

What I am wondering is this: Is DTS the most efficient i.e. quickest way to import all this data. Bearing in mind there is about 90 million records to import in all.

I would appreciate the benefit of somebody elses experience when dealing with this type of thing.

Cheers,

Brookesy

View 2 Replies View Related

Text Files To SQL Table ...

Mar 30, 2006

Hello!, I have two applications that import data from text files to Access 97 tables. One of this applications is in Access 97 and the other in VB6, know they are requesting me to import this text information to a SQL table. I do not have any experience with SQL Server, can some one give a idea of what I could do to accomplish this?, I would appreciate a lot anyone's help!, thanks! ...

View 4 Replies View Related

Building Text Files

Jan 12, 2004

Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.

I have a table set up with my parameters and output file names.

Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?

Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).

PS: The file data cannot include cilumn headings

TIA -
bpd

View 3 Replies View Related

Loop Through All Text Files

May 11, 2008

Hi All
I have a situation in my SSIS Project where I have a folder in which contains 10 text files, what I need to do Loop through all these files and select the data from those text files and insert them in SQL Database table. I will be able to get the names of those file using For Each Loop Container but I don’t have idea how to insert the data in SQL table using for each loop container. I don’t want to use multiple data flow task. Any help will really appreciate
Many thanks

View 1 Replies View Related

Import Text Files

Apr 11, 2007

hi friends ,

Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables . I have to create a tool to automate the porting using C# .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .

Thanks $ Regards

Nevin

View 1 Replies View Related

Import Text Files

Apr 11, 2007

hi friends ,

Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .

Thanks $ Regards

Nevin

View 5 Replies View Related

Error Creating Mdf - Connection To SQL Server Files (*.mdf) Require SQL Express 2005 To....

Apr 11, 2006

Connection to SQL server files (*.mdf) require SQL express 2005 to function properly. please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251

I AM GOING TO RIP MY HAIR OUT WITH THIS PROBLEM. I have reinstalled both sql server express 2005 and VWD about 5 times with the same problem. please, please, please someone throw me a bone here and help me resolve this problem.
When I create a new EMPTY website and I rightclick on my website in the solution explorer and choose add item, I chooe SQL Database, I give it a name 'database.mdf' and click add. I get the following message:
you are attempting to add a database to an asp.net application. for a database to be gfenerally consumable in your site, it should be placed inside the 'app_data' folder. would you like to place the database inside the 'app_data' folder? I click YES (I know this message is normal)
then I get the following message:
Connection to SQL server files (*.mdf) require SQL express 2005 to function properly. please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251
I can add anything else but this damn mdf file.
thanks for all your help in advance.

View 2 Replies View Related

Creating Date Time Stamped Files Names For Backup File

Nov 13, 2007

Hi All,

any suggestion on creating date time stamped files names for backup file.

I want to create new file for daily backups with date time stamp so i can use Maintenance plans to clean older files in each 4 month cycle.

thanks,

View 8 Replies View Related

Creating A Auto Increment Which Contains Numbers && Text

May 25, 2005

Hi,Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-ABC000001 ABC000002ABC000003Etc, EtcI am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?Regards,Brett

View 4 Replies View Related

Error When Creating Full Text Indexing

Apr 5, 2000

Hi,

I am just starting to study for the sql 7.0 admin test. Using the sybex book and bol I have run accross an error that I can not seem to find any support for. I am trying to do a full text index and I receive the following area: "An unknown full text failure (80004005)occured in function EnumCatalog on full text catalog".

I have looked on the website high and low and cant find anything on this error. Can someone give me some possible input to what the problem is?

thanks,

Jerry

jarnone@bellsouth.net

View 2 Replies View Related

Creating Text File From Stored Procedure

Sep 9, 2005

Greetings,I have a sp that dumps text into a textfile but I am having troublecreating the textfile.EXEC master.dbo.xp_cmdShell '\servernamed$The Filesubfilename.dat'The directory "The File" has a space in it. I've tried putting thecarat ^ before the space, and putting double quotes...but I keepgetting this error'\servernamed$The' is not recognized as an internal or externalcommand, operable program or batch file.If I do EXEC master.dbo.xp_cmdShell '"\servernamed$TheFilesubfilename.dat"' I get the same thing.If I do EXEC master.dbo.xp_cmdShell '""\servernamed$TheFilesubfilename.dat""' I get'"\servernamed$The Filesubfilename.dat"'is not recognized as an internal or external command, operable programor batch file.Does anybody see what I am doing wrong?

View 3 Replies View Related

Problems Creating Full Text Index

May 17, 2008

Hi all,

Im new to sql and very interreseted in the Full text features, however when im trying to execute the following query:

USE Updater

CREATE FULLTEXT INDEX ON dbo.Servers (ServerName)

KEY INDEX ServerID

ON UpdaterCatalog

WITH CHANGE_TRACKING AUTO

GO

Where ServerID = Int NOT NULL IDENTITY and ServerName = VarChar(255) NOT NULL and UpdaterCatalog is just created

I get the following error:

Msg 7653, Level 16, State 1, Line 3

'ServerID' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key.

I cant seem to figure out why this wont work since unless im mistaking, both fields are legal.

Note that creating an index on any other table doesn't work either.

Im running Sql server standard edition (32 bits) on VISTA Ultimate X64

Thanks in advance,

Koen

View 8 Replies View Related

Importing Text Files To Sql Server Using Asp.net/Vb.net

Sep 20, 2004

Hi,
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.

Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.

View 5 Replies View Related

Help Needed With Importing Text Files

Jun 6, 2005

I have a load (180,000+) of text files whose contents need to go into a SQL server database.Whats the best way of doing this? Using a c# console program and if so, using FileStream or StreamReader? Or using a feature of SQL server itself. The text files themselves are less that 1k and are literally less than 200 characters.The problem is, I've tried a WinForm and although I can detect what files are there, as soon as I attempt to open one for reading, everything stops working and won't insert anything to the database.

View 1 Replies View Related