Can Sql Server Read An Outside File And Insert The Contents Into The Database?

Aug 8, 2000

hello!

i'm an sql server beginer. i was wondering if some of you guys can help
me out. i need for the sql server to be able to read an outside file (just text) and be able to run a script that will insert it in the database. it's a dcc output file. we've tried running this script:

DROP TABLE tests
go
DECLARE @SQLSTR varchar(255)
SELECT @SQLSTR = 'ISQL -E -Q"dbcc checkdb(master)"'
CREATE TABLE tests (Results varchar(255) NOT NULL)
INSERT INTO tests EXEC('master..xp_cmdshell ''ISQL -E -Q"dbcc checkdb(master)"''')


and it's running good but the problem is the results of the dbcc here did not come from a file but directly after executing the dcc command. is there a way to do it?

thank's for the help!

View 1 Replies


ADVERTISEMENT

Need A SP, XP Or USP To Read The Contents Of A File (i.e. Any OS Or SQL Server Logs )

Jun 11, 2002

hello,
I am working on some maintenance process. I need a Stored Procedure to reda the contents of a file. (i.e. OS or SQL server log files). plz help me in sort out this problem.

regards,
venkat.

View 2 Replies View Related

Any SP , XP Or UPS To Read Contents Of All OS And SQl Server Logs (plz Urgent)

Jun 11, 2002

Hello,
I am working on some maintenance work. I need a Stored procedure to read all contents of OS and SQL server log. It has to accept filename as IN parameter.
Please help me to solve this problem.

regards,
vnk.

View 3 Replies View Related

Which Is The Best Way To Read From A Txt File And Insert Data Into Sql Server?

Mar 21, 2008

Hi, I was wondering which is the best way to read data from a txt file and insert each row into sql.
OLE DB Command could be? It will be necesary to work with variables?
My txt file will have a defined width (if it is necessary to know). I will have many rows with many columns. I have to map eah column from the txt file to it's corresponding column in sql server and insert data into it for each row.
Thanks for your help!

View 7 Replies View Related

Read Data From A Csv File And Insert It In A Sql Server Databse Table

May 25, 2007

Hai Everbody,
           for me in my project i want to read data from a csv file and insert it in a sql server databse table.The csv file may contain n number of columns,but i want only certain columns from that, and insert it in the database table.How to achieve this. Plz help me it is urgent. Thanks in advance.
        Thanks and regards
               Biju.S.G

View 1 Replies View Related

How To Read A Remote CSV File Into SQL SERVER Using Bulk Insert Command..

Mar 24, 2008

Hi,
I have load a CSV file into one of the table in sql server 2005 using bulk insert command. But the csv file in remote system.
Please help me.....

View 1 Replies View Related

How Read CSV File In Other Server Using Bulk Insert Command In Sql Server In Different Server...

Mar 24, 2008

Hi All,
 Please some one help me...
I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server..
 how do i insert the file....
please help me.....
 

View 1 Replies View Related

SQL Tools :: BCP And BULK INSERT To Copy Complete Database Contents?

Jan 29, 2015

Script: [URL] ....

I need to move all of the contents of one database into anther with the same schema, and it looks like this might be just what I need. But it is from 2007, so I wonder if it is still current?

Also, having tried to run it on another database to generate the script that will actually do the copying, I have a few questions. It looks like it generates statements to import the data twice. For example:

BULK INSERT [TaPerfGDB].[dbo].[i1]
   FROM 'C:Tempi1.Dat'
   WITH (FORMATFILE = 'C:Tempi1.FMT',
         BATCHSIZE = 1000000,
         ERRORFILE = 'C:TempBI_i1.ERR',
         TABLOCK);

And a little later:

INSERT INTO [TaPerfGDB].[dbo].[i1]
    SELECT *
      FROM  OPENROWSET(BULK  'C:Tempi1.Dat',
      FORMATFILE='C:Tempi1.Xml'
      ) as t1;

That does not really make any sense to me. It also generates statements like this:

bcp "[TaPerfGDB].[dbo].[GDB_GEOMNETWORKS]" format nul -n -CRAW -f "C:TempGDB_GEOMNETWORKS.fmt" --S"PGALLUCC-M7" -T

What is the deal with the double hyphen by the servername? Won't it just see that as a comment? It can be easily fixed, but I am just suprised that it is still there after all these years. My purpose in doing this is a desparate attempt to salvege a database that sits on a server with multiple drive errors. These prevent backups, so I cannot just restore the database on the new server. That is why I want to try an approach that goes table by table, so that at least all the tables which are not touched by the drive errors can be moved.

It is a 3 TB database running on SQL Server 2008 R2 std. ed.

View 2 Replies View Related

Read The Contents Of CD..(urgent)

Apr 17, 2002

Hai.
Here i am sending my query.This is urgent to my job.Please give me solution as
early as possible in SQL server.

Thanks.
My query is..
Check for CD drive on your system.If drive available ,display the name of the CD and read the contents of the CD,and search for given file in CD.
I want search this on local machine and on remote system.

Thanks.

with regards.
laxma P.reddy

View 2 Replies View Related

Read SSIS Package Contents Into SQL DB

Feb 1, 2008

Hi,

We have an extensive set of SSIS packages that run daily to build our datawarehouse. We have run into a bit off a maintenance issue which I am hoping someone can help me with. The SSIS packages execute stored procedures. What I would like to know is, is there a quick way to determine what SSIS package is running a certain stored procedure?

My first approach was to load the packages into a table in a database using various xml functions. Basically, the table would contain the package name, and then the stored procedures that package executes. I have run into some difficulties with this though. Has anyone tried anything similar or perhaps know of a tool which could provide similar functionality?

Some help would be much appreciated.

View 3 Replies View Related

Read Text File From SQL Server, Read Its Content, And Load It In RichTextBox (Related Component: Context.Response.BinaryWrite(), And StreamReader)

Nov 26, 2007

OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.

View 1 Replies View Related

Read Data From Text File And Insert To Sql Table

Mar 28, 2008

hi my friends
i want read data from text file,and write to my table in sql,
please help me

M.O.H.I.N

View 6 Replies View Related

Read A File Name From Network Folder Automatically For A BULK INSERT

Jul 18, 2007

Hi again all,

Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.

ddave

View 1 Replies View Related

BULK INSERT From A Text (.csv) File - Read Only Specific Columns.

Apr 23, 2008

I am using Microsoft SQL 2005, I need to do a BULK INSERT from a .csv I just downloaded from paypal. I can't edit some of the columns that are given in the report. I am trying to load specific columns from the file.

bulk insert Orders
FROM 'C:Users*******DesktopDownloadURL123.csv'
WITH
(
FIELDTERMINATOR = ',',
FIRSTROW = 2,

ROWTERMINATOR = ''
)

So where would I state what column names (from row #1 on the .csv file) would be used into what specific column in the table.



I saw this on one of the sites which seemed to guide me towards the answer, but I failed.. here you go, it might help you:

FORMATFILE [ = 'format_file_path' ]

Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:

The data file contains greater or fewer columns than the table or view.


The columns are in a different order.


The column delimiters vary.


There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.

View 12 Replies View Related

SQL Server 2008 :: SSIS - Package To Zip Contents Of A Folder Into One ZIP File

Aug 12, 2015

I have a requirement to create a package that takes all files in a given folder and adds them to a single archive (.zip) file. I've tried several methods using 7zip and while I can create archives for every file in the directory I can't seem to get them into a single .zip file.

View 9 Replies View Related

Transact SQL :: Making Server Database Read / Write From Read Only

Jan 12, 2012

i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.

View 11 Replies View Related

Integration Services :: Dataflow Task Read CSV File And Insert Data To Table

Apr 29, 2015

I've a dataflow task on For Each Loop container at control flow of SSIS package. This For Each Loop container reads the CSV files from the specified location one by one, and populates a variable with current file name. Note, the tables where I would like to push the data from each CSV file are also having the same names as CSV file names.On the dataflow task, I've Flat File component as a source, this component uses the above variable to read the data of a particular file.

Now, here my question comes, how can I move the data to destination, SQL table, using the same variable name?I've tried to setup the OLE DB destination component dynamically but it executes well only for first time. It does not change the mappings as per the columns of the second CSV file. There're around 50 CSV files, each has different set off columns in it. These files needs to be migrated to SQL tables using the optimum way.

Which is the best way to setup the Dataflow task for this requirement?Also, I cannot use Bulk insert task here as we would like to keep a log of corrupted rows.

View 10 Replies View Related

Transact SQL :: Read Data From CSV Files And Insert Into DataBase

May 6, 2015

I have a requirement to

a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.

This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.

View 9 Replies View Related

Read DataBase File

Jan 17, 2002

Hi,
I received a SQL Server 6.5 database (.dat and .Log files). I'm currently running 7.0. Is there any way to read/import this data?
I tried to do this:
I have installed SQL Server 6.5. And After install, I have created a database with the same names (.dat an .log) and make it bigger than the first .dat and .log files.
Then, I have stoped the SQL server service, have deleted the .DAT and .LOG files that i have created, and have copied the first .DAT and .LOG in their place. I have Started the SQL server service and the database was beeing suspected. I stil unable to read data.
Can you help me?

View 1 Replies View Related

SQL DataBase MDF File Read Only

Nov 22, 2007



Dear All

I have some problem, I User Visual Web Developer 2005 Express to Create DateBase Files, when I copy the code and Database file to Server then user remote client to insert records and update, but it alert the error :

'/telecom' 應用程�中發生伺�器錯誤。


Failed to update database "D:TELECOMWORKSAPP_DATAPRODUCT.MDF" because the database is read-only.
æ??è¿°: 在執行目å‰? Web è¦?求的é?Žç¨‹ä¸­ç™¼ç”Ÿæœªè™•ç?†çš„ä¾‹å¤–æƒ…å½¢ã€‚è«‹æª¢é–±å †ç–Šè¿½è¹¤ä»¥å?–得錯誤的詳細資訊,以å?Šåœ¨ç¨‹å¼?碼中產生的ä½?置。

例外詳細資訊: System.Data.SqlClient.SqlException: Failed to update database "D:TELECOMWORKSAPP_DATAPRODUCT.MDF" because the database is read-only.



How to do this care. thanks

View 1 Replies View Related

SQL Server 2008 :: Insert The File In Other Database?

May 21, 2015

I work with sql server 2008 on a database.we have export schema and datas with the command export datas

click rigth on database => tasks => generate scripts => select all object => click advanced => select type of data to script => schema and data

Now we have a file with all datas and schema That's perfect ...But how i can insert the file in a other database?ok i can copy paste all datas in management studio and press f5 but when i do this the management studio fail because the size of the file is > 200 mega !

View 3 Replies View Related

Read MS SQL 2000 Backup File Without Restoring The Database

Sep 6, 2007

I want to read certain data from the backup file of MS SQL 2000 without restoring the database backup file. Based on the data selected I will manage the further functionality such as restoration of the backup file.
Is it possible to read some data from MS SQL 2000 backup file without restoring? If yes then please let me know how?

View 8 Replies View Related

How Read File CSV File In Remote Server Using Bulk

Mar 24, 2008

Hi All,

I need to read a csv file, which is in remote server using SQl Bulk Insert Command.

Can I read a file Which is in remote server using BULK INSERT.

Thank you.......

View 1 Replies View Related

Read Cvs In C# And Insert Into Sql Server

Jun 5, 2006

Hi, Anybody has an  example of reading a csv comma delimited file and insert the fields into a datatable?Thanks

View 2 Replies View Related

How To Create INSERT Query For Contents Of Table

Oct 5, 2007

Using Sql Server 2005 Express and Management Studio I need to create
a SQL insert statement for the contents of a table (FullDocuments) so
that I can run the query on another server with that same table schema
(FullDocuments) and the contents will automatically be inserted into
the new instance of the FullDocuments table.In Management Studio
I have used "Script Table as" for the create table query.  The
second instance of FullDocuments has been created on the remote
server.  Now how do I generate an insert query for the contents of
FullDocuments so that the contents can be moved/inserted to the new
instance of the table?Thanks for any help provided.  

View 10 Replies View Related

Get Contents Of Xml File

Jul 5, 2007

Is it possible is SSIS to get the contents of an xml file and pass it on to a stored procedure?
Thanks

View 1 Replies View Related

Checking Contents Of Column And Replacing Contents If First Character Is A Letter

Jun 25, 2007

Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing.  SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)  '" + date + "'"Many thanks in advance! 

View 7 Replies View Related

[howto] View Contents Of Log File

Apr 7, 2004

How to view contents of log file?
i found sp_helplog
however

sp_helplog is no longer supported.

help please

View 1 Replies View Related

Config File Contents And Deployement

May 14, 2007

Hi all,

I have few config files with db passwords in them. I have to change passwords daily into that(as my system is in UAT phase now). I have deployed my packages on SQL Server.

My question is , do I need to deploy my packages daily after password updation, or it'll automatically taken care by the SQL Server ?



Thanks in Advance

syed

View 4 Replies View Related

Understanding The Contents Of A Checkpoint File

Aug 18, 2006

Hi,

I have a problem where my packahe isn't restarting from the point of failure and I'm tryig to understand why not.



The following line came from the checkpoint file:

<DTS:Container DTS:ContID="{36A685E1-049A-4E6C-9884-AADF8BE29577}" DTS:Result="0" DTS:PrecedenceMap="" />

Can anyone explain:

What DTS:Result & DTS:PrecedenceMap are used for?
What values can DTS:Result contain and what do they mean? [Today I have only seen "0".]
What values can DTS:PrecedenceMap contain and what do they mean? [Today I have only seen "Y" & "".]

Thanks

Jamie

View 1 Replies View Related

How Do I Insert Data From A Flat File Or .csv File Into An Existing SQL Database???

Mar 29, 2006

How do I insert data from a flat file or .csv file into an existing SQL database???

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)

insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @Event " & _

",[Year] = @Year " & _

",[Contract Loss] = @ConLoss " & _

",[Company Loss] = @CompLoss " & _

",[IndInsured Loss Prop] = @IndLossProp " & _

",[IndInsured Loss WC] = @IndLossWC " & _

",[Event Info] = @EventInfo", dbconn)

upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")



************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

View 6 Replies View Related

How Do I Insert Data From A Flat File Or .csv File Into An Existing SQL Database???

Mar 29, 2006



How do I insert data from a flat file or .csv file into an existing SQL database???

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)

insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @Event " & _

",[Year] = @Year " & _

",[Contract Loss] = @ConLoss " & _

",[Company Loss] = @CompLoss " & _

",[IndInsured Loss Prop] = @IndLossProp " & _

",[IndInsured Loss WC] = @IndLossWC " & _

",[Event Info] = @EventInfo", dbconn)

upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")



************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

View 3 Replies View Related

Read UNIX File From SQL Server

Feb 6, 2004

Is it possibel to run a DTS job to read a file from unix server?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved