Hi Guys,
What approach should I use to copy content of a text file.
Example of the text file's content:
Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90
In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.
Regards,
Lars
Hi Guys,
What approach should I use to copy content of a text file. Is BCP capable of doing this? How about SSIS?
Example of the text file's content:
Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90
In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.
Regards,
Lars
Hello:
I want to copy one table not whole database to text file. How to do it? Using DTS could not allow me to select the specific table.
Thanks,
Snow:rolleyes:
Hi all,need advice on the following task:copy the content of a big table from DB_A to DB_B in the same serverthe size of table:~ 7 million rows, ~ 9G in size, 1 clustered pk index, 13 nonclusteredindexcurrent practice:use DTS to copy the data, takes over 20 hours as-- first had to delete existing data of the table in DB_B-- then copy-- all these happen while all indexes are in place.I am trying to check what is the best or most efficient way to copythis kind of data and what wouldbe the expected time for such load.my machine: SQL 2000 Enterprise, 8-way P4, 12G RAM on a EMC Clarrion600 SAN.
View 2 Replies View RelatedHello everyone!I'm having a problem with inserting the content of a text file into a Sql Server 2005 database.I'm reading the text file into a dataset, and works fine. What I can't do is what I suspect is the simple part: Insert all the data into a table that has exactly the same configuration that the file. I've never worked with dataset's before, and I can't seem to find the answer to this!This is what I have done so far: Dim i2 As Integer Dim j As Integer Dim File As String = Server.MapPath("..DocsFactsFORM_MAN_V3_1.txt") Dim TableName As String = "Facts"
Dim delimiter As String = "9"
Dim result As DataSet = New DataSet() Dim s As StreamReader = New StreamReader(File) Dim columns As String() = s.ReadLine().Split(Chr(9)) result.Tables.Add(TableName) Dim strs1 As String() = columns For i2 = 0 To CInt(strs1.Length) - 1 Dim col As String = strs1(i2) Dim added As Boolean = False Dim [next] As String = ""
Dim i As Integer = 0 While Not added Dim columnname As String = String.Concat(col, [next]) columnname = columnname.Replace(Chr(9), "") If Not result.Tables(TableName).Columns.Contains(columnname) Then
result.Tables(TableName).Columns.Add(columnname)
added = True Else
i += 1
[next] = String.Concat("_", i.ToString()) End If End While Next i2 Dim strs2 As String() = s.ReadToEnd().Split(Chr(13) & Chr(10).ToString()) For j = 0 To CInt(strs2.Length) - 1 Dim items As String() = strs2(j).Split(Chr(9)) result.Tables(TableName).Rows.Add(items) Next j So now I have my dataset populated with all the information, but how can I insert it into the database?If anyone can help I would appreciate very, very much!Thank you Paula
I have a table with a column [FileData] of type TEXT or XML. I need to write the content of the column to a file, using sp_OAWrite for example, but the stored procedure who needs to read the content and write it to the file does not work because the limitation is no local variable for those types. What use is that datatype if i can not use it in procedures like this?
Anyone out there who could give me a tip on how to solve that?
Code Snippet
Hi there,
I'm struggeling for more than a week now with this problem, without a finding the solution.
I have two databases, MS Access and SQL Server 2005 Express Edition
Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)
I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...
Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?
Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).
the procedure goes like this;
' Create a connection to the MS Access Database
Dim connectionToAccess As New OleDbConnection(DBConnectionAccString)
strsql = "SELECT * FROM [TABELLA]"
connectionToAccess.Open()
Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)
Dim DsAccess As New DataSet("ACCESS")
DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella")
DaAccess.Fill(DsAccess, "Tabella")
' Create a connection to the SQL Database
Dim connectionToSQL As New SqlConnection(DBConnectionSQLString)
connectionToSQL.Open()
Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)
Dim DsSQL As New DataSet("SQL")
DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella")
DaSQL.Fill(DsAccess, "Tabella")
DaSQL.Update(DsSQL, "Tabella")
Note I tried also the following, withou a result;
DsSQL = DsAccess.Copy
DaSQL.Update(DsSQL, "Tabella")
Please is there someone who could respond !!???
Hi. I am currently building a blog application, and I need some advice... I have the post title, post date, post by, etc stored in a database table, however I was wondering whether I should store the actual post content in the database as well or in a text file. For example, if the posts get really long would it slow down database performance or would there not be much of a difference? Furthermore, if I wanted to keep the posts private, a text file would not be ideal as it can be accessed easily by surfers... What do you recommend?Thanks a lot
View 1 Replies View RelatedI get error reports in simple text files like the one below in relatively the same format. The only thing that varies is the number error reasons as there can be any number of error reasons for a file. Usually there is only one but there can be a handful. What is the best way to capture the error description and count of errors no matter how many there are? I want to take these items and update a table I have in sql server 2008r.
Report Message example:
************************************************** *****
Original File Name: some.file.YYYYMMDD.d.incr.02of02.1.dat
Source File ID: file02YYYYMMDD
File Receipt Date: 10/17/2014
Total records received: 1331136
Total records loaded: 1329987
ERROR REASONS
Error code: EBBW002 Error desc: Duplicate Record Total records: 1146
Error code: EABC001 Error desc: Invalid Length Record Total records: 1
Error code: ERRCM10 Error desc: Missing First Name Total records: 2
Total number of Errors encountered during the ODS update processing: 1149
************************************************** *****
I know oracle uses UTL_FILE procedures lik putline, but microsoft?
Thank you for trying to help.
Is it possible to Export a SQL passthrough query to a text file
Like
@A1 int
bcp '"select * from bank where id = @a1" query out ....... "
Or I have to use sth. like SQLDMO ???? But I dont know how to do it ..
Please Helpppppp
Edwin
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.
Hello there!
I installed and configured SQL Server 2005 reporting services. When I try to connect to it using SQL Server Management Studio, I get the following error:
Client found response content type of "text/html", but expected "text/xml".
What should be done to overcome this?
Does anyone have any idea about this?
Thanks in advance
Hemant
Hi,
In my VB .Net application , when I am trying to fire a SSRS report on my local machine . Its giving the error ".Client found response content type of 'text/html', but expected 'text/xml'."
The request failed with the error message:
--
<html><head><title>Error</title></head><body>The Local Security Authority cannot be contacted
</body></html>
--.
the error is being thrown at the code line...
reportviewer.serverReport.getParameters()
I have reportserver as
https://ReportServerforApplication.abc.com/abc_ReportServer
I have admin rights on my machine but still the problem persists. Am i missing some user group?
Could someone Please help me with this.
Thanks In Advance
Gaurav
Hi,
I am getting these errors from Report Manager. It seems that every time after the server has been idling for about 15 minutes, I would get this error message. I can click on other links and I am able to continue to use Report Manager.
Is there a timeout setting to help me resolve this or is this a bug with Report Manager? I just insitalled SP2 on the SQL Server and the Reporting Service.
Error message on browser:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.3042.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline} </style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3042.00" target="_blank">Get Online Help</a></li><ul> <li>For more information about this error navigate to the report server on the local server machine, or enable remote errors</li> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </ul> </body> --.
Any ideas?
Thanks,
-waslam
Hi,I have setup a report model and am ready to deploy it for the first time. I have had no issues deploying my report definitions so presumably this should be alright.However, trying to deploy it gives this error:
TITLE: Microsoft Semantic Model Designer
------------------------------
A connection could not be made to the report server http://localhost/ReportServer.
------------------------------
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content=" at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load()
at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName)
at Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location)
at Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation)
at Microsoft.ReportingServices.Library.Global.get_ConfigurationManager()
at Microsoft.ReportingServices.WebServer.Global.StartApp()
at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline}
</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) </li><ul>
<li>Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config' is denied.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.ReportingServices.SemanticQueryDesign)
I'd greatly appreciate any insight you could give me into fixing this problem.ThanksJohn
how do i specify the table destination only not the whole databasefrom a text.txt source?thanks,trint
View 2 Replies View Relatedfor MS SQL 2000
table [Users] :
[id_Users] [int] NOT NULL ,
[Name] [varchar] (25) NOT NULL,
[Alias] [varchar] (25) NULL
how can I copy the content of [Name] into the column [Alias] only if [Alias] is Empty ?
thank you
I need to make something that copies all tabels (structures + data) from a progress database into another mssql2000 database.
THANKS!
Hi,
I'm using SQL Server 2005 Management Studio Express.
I have a database used for an application and I need another copy of that database so I can fix some bugs.
I tried attaching the mdf to the second database but it gives an error.(a database is allready attached)
I need the 2 databases on the same server.
Thanks,
Medeea
I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code
date product_code
01/01/14 | BIC01 |
03/01/14 | |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | |
03/01/14 | CASH |
I though of using the LAG function but the table does not have any specific order.So after running the select, the table should look like:
date product_code
01/01/14 | BIC01 |
03/01/14 | BIC01 |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | CASH |
03/01/14 | CASH |
I have to copy a large (3000) amount of different tables from a Oracle machine into an
SQLServer machine.
I am able to do this using a (VB) script.
I use now several methods:
1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)
2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')
3) Also used OPENROWSET method (similar to 2)
For small tables this is fine, however for BIG tables (15M Rows/150Cols) the methods above are too slow.
If I compare the same copy action with a simple DTS, the DTS is 3 times faster.
Also, the DTS seems to bulk copy the data directly into the desired database while the
mentioned methods first fill the tempdb, then the transaction log of the desired database and
then finally the desired table (need very much extra space on your filesystem).
The total size of data is about 300GB.
Can anyone supply me with a simple example how to copy data from an Oracle table into a
SQLServer table in script (or SQL) that is as fast as the DTS and not filling my logfiles??
I read the bcp (which I use for import/export files) and bulk insert commands, but
I do not understand how to use them in this question.
how can I copy the content of the folder (including sub folders) from FTP location using ssis.
View 4 Replies View RelatedHello, I'm learning about asp .net and I've created a simple application with SQLServer integration. I have 3 text boxes where I can insert text and then use them as parameters for the Select query string of a SqlDataSource which is then bound to a DataGrid.My question is: Is there any default configuration I can use with the SqlDataSource which do not add a given field as a parameter to the WHERE clause if the text box content is empty ? Sure I can sweep through all my text boxes, check if they're empty and dynamically build the WHERE clause with correct parameters, but is theres a built-in, more elegant way to solve this ?Thanks in advance.Arashikage
View 2 Replies View RelatedHi,
I'm using Sql Express with full-text-search enabled.
The search worked great until last week, suddenly, I started to give the following error message:
The execution of a full-text query failed. "The content index is corrupt."
I've found this KB article:
http://support.microsoft.com/kb/938243
And followed the steps they suggested, but it changed nothing - I'm having the same problem.
Any idea, someone...?
Copy out all data from a DB table into/across delimited text file(s) ensuring that each text file size is no more than 3MB.Have created a SSIS solution where it achieves this requirement ..well sort of achieves the requirement ... Here it what the current solution (sparing the minute details) does in a nutshell & Problems with it:
1) Created a function (Script below) which finds the
maximum row size in bytes in a given DB table & uses it to calculate
how many rows can be copied out into a text file without exceeding 3MB size limit.
For instance: A DB table selected had 788 rows in total and this function for this particular table returned a value of 181 rows { select [dbo].[udf_GetRowPartitionNumber](‘<TableName>’)as #ofRowstoPartitionTableby --181} meaning in order to not exceed the requirement of 3MB per text file, we had to copy all the data from DB table across (create) 5 text files {Select
CEILING(788
[code]....
Hi,
i have a table with a nvarchar column,i want to send this column value as unicode content to customer mail box , but when i send it a mail with '?' customer receive , how can i accomplish this?
thanks
Hi There
I have a table at my initiator, i have a copy of this table at my target, every morning i want to refresh the table at the target with the data from the initiator via service broker.
What would be the best way to do this?
One major consideration would be that this table would be very large +- 1.6 million rows.
I was thinking of exporting the table to file like a csv, compressing the file and then sending it via service broker. Then at the target i would uncompress and bulk copy the data into the table at the target.
But can this be done?
How would one go about sending a file via service broker?
For example if i wanted to send a .csv or something via service broker how would i do it?
I know i can send binary data, but i would have to equate the msg to a file on disk, and how exactly would a recieve this file on the other side and make sure it went to disk at the target, i am guessing it is a mixture of xp_cmdshell and service broker.
Can this work?
Or is there a much simpler way? Any thoughts would be appreciated.
Thanx
Hi,
I'm new to SQL Server 2005 SSIS. I'm trying to do something very simple, but I cannot figure it out, PLEASE HELP!
I have a flat file, which I read and then insert the data in a database table, that works fine. The problem is that I don't want to insert duplicate records. For example; if I run the package again, it will appent to the table. What I need to do is that if the package runs again, check if the record already exist, based one two columns, date and hour, and do not insert the record.
Thank you,
Aldo
Hello,
I have an access database (access 95 Version7)dumping a delimited text file onto my server. I am then using DTS in SQL 2000 to import the file into a table.
My issue is that each time the DTS runs, it imports the whole text file each time, this is causing duplicate records.
So I created a transformation script as follows :
Function Main()
If DTSSource("counter") <= DTSDestination("counter") Then
Main = DTSTransformStat_SkipRow
Else
DTSDestination("counter") = DTSSource("counter")
DTSDestination("serialdata") = DTSSource("serialdata")
DTSDestination("serialdata1") = DTSSource("serialdata1")
DTSDestination("date") = DTSSource("date")
DTSDestination("time") = DTSSource("time")
Main = DTSTransformStat_OK
End If
End Function
The theory behind the If statement, is if it sees that the counter field is less than or equal to what is there, it will skip the record and move forward. For some reason this is not working.
Does anyone have a workaround or another solution to this problem
HI,
We are using SQLLDR to send data from SQL Server to Oracle database. This utility creates a log file. I want to know whether there is a provision to change the contents of the file. We use this utility in multiple SQL Servers to send the data to the same Oracle Server. Can we add the source server name in the file so that identifying which server does a particular log file belong to? How?
Help would be greatly appreciated. Thanks a lot..
Manoj Deshpande.
I have portions of data coming in as text files containing new records and updates of existing records. The solution I've figured out till yet is to import a portion of data into some intermediate table and then run a stored procedure to migrate the data into the real table.
Any ideas how to do this in a more efficient way?
Thanks in advance,
Asnate
I am familiar with the MySQL Load Data command to load an external ascii file into a database table, but am having trouble finding a T-Sql command that is equivalent without creating an executable...any help would be appreciated...
View 1 Replies View Related