How to upload a content of a table into sql-file (the set of INSERTs)
using standard MSSQL utilities?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I am rewritting our DTS that upload tables in FoxPro to SQL Server using SSIS. I am finding the that SSIS is way slower. My uploads using DTS take just 7 minutes where doing the same thing in SSIS is taking 1 hour 15 minutes.
View 2 Replies View RelatedHow to upload a content of a table into sql-file (as a set of INSERTs)using standard utilities?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View Related
I use to have Enterprise Manager and had no problem uploading new table.
But I downloaded 2005 SQL Server Management Studio Express adn I can't figure out how to upload...
How do you upload new tables to my database using 2005 SQL?
Thanks
I do have a comma separated text file.
"firstname","lastname","ssn","dob"
"firstname1","lastname1","ssn1","dob1"
The text file can have 50000 records. What is the best way to upload the file into a table. The table structure looks like:
Table1:
Fname varchar(50)
lname varchar(50)
ssn varchar(50)
dob varchar(50)
Any thoughts???
We receive emails every day that we would like to load into a table in sql 2005. Anyone know a way to do this?
View 6 Replies View Relatedi have a MSSQL 2000 database with about 30 tables in it. On one of those tables i've defined an full text index on an image field. In this table are around 500 records with binary files.
it functioned well for a time but now when i try to upload a file into the table this goes extremely slow (300 KB takes over 3 minutes).
i tried disabling "change tracking" but this didn't help a thing
adding blobs to other tables (without fulltext index on it) still goes fast.
what could be a reason that the uploading goes so slow??
Im trying to upload 1000 txt files into one table in SQL. I'm using the following query, to upload one txt file at a time:
bulk insert [dbo].AAA_2013_2015
from 'dataserverSQL Data FilesSQL_EMELIZFC x Bloque Detallada201308 Detalle FacturasFACT_BLOQ_AGO13 (4).txt'
with (firstrow = 2,
lastrow = ???,
fieldterminator = ';',
rowterminator = '0x0A')
I'm trying that the query skip the last row because gives me the following error:
Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 17. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
know a command to skip the last row, something like lastrow= all-1...or something like that.
I also executed using MAXERRORS command...like this:
bulk insert [dbo].AAA_2013_2015
from 'dataserverSQL Data FilesSQL_EMELIZFC x Bloque Detallada201308 Detalle FacturasFACT_BLOQ_AGO13 (15).txt'
with (firstrow = 2,
fieldterminator = ';',
MAXERRORS = max_errors,
rowterminator = '0x0A')
does not recognize MAXERRORS command, also tried to put a number of error instead of max_errors.
Hi Guys,
I have one question, must be simple for you guys. I want to upload .pdf document into the SQL server database.
I have one question, that which data type I should use. Should I use TEXT or IMAGE.
Could you please tell me which data type I should use and why?
Thanks for your help in advance.
how to upload data from excel sheet to table in sql server is there any command for that
View 1 Replies View RelatedI used upload image feature and below is the code that I used. The image is stored in a folder. I want to save the image in sql database. I create the database which is “database�, and I create a table which is “user�. User table has auto “id� field and “image� field. I want to save the image in the image field. I need your help to do the other codes.
I am using asp.net with VB
This is what I did so far:
============================
Partial Class upload
Inherits System.Web.UI.Page
Dim strFileName As String
Protected Sub btnupload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnupload.Click
UploadImage()
End Sub
Sub UploadImage()
If Not (fileupload1.PostedFile Is Nothing) Then 'Check to make sure we actually have a file to upload
Dim strLongFilePath As String = fileupload1.PostedFile.FileName
Dim intFileNameLength As Integer = InStr(1, StrReverse(strLongFilePath), "")
strFileName = Mid(strLongFilePath, (Len(strLongFilePath) - intFileNameLength) + 2)
Select Case fileupload1.PostedFile.ContentType
Case "image/pjpeg", "image/jpeg" 'Make sure we are getting a valid JPG image
FileUpload1.PostedFile.SaveAs(Server.MapPath(" estimages") & strFileName)
lbstatus.Text = strFileName & " was uploaded successfully to: " & Server.MapPath(" estimages") & strFileName
Case Else
'Not a valid jpeg image
lbstatus.Text = "Not a valid jpeg image"
End Select
InsertToDatabase()
End If
End Sub
End class
I am using the article, "
Storing Binary Files Directly in the Database Using ASP.NET 2.0 By Scott Mitchell "
http://aspnet.4guysfromrolla.com/articles/120606-1.aspx
I have tried to modify the code and my datatable to replicate the function, but I am getting an error, "MIMEType" is not a SQL Parameter
An SqlParameter with ParameterName 'Type' is not contained by this SqlParameterCollection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IndexOutOfRangeException: An SqlParameter with ParameterName 'Type' is not contained by this SqlParameterCollection.Source Error:
Line 53:
Line 54: 'Specify the values for the MIMEType and ImageData parameters
Line 55: e.Command.Parameters("Type").Value = MIMEType
Line 56:
Line 57: 'Load FileUpload's InputStream into Byte arraySource File: E:homeDefaultetances.ushtdocshomeadminaddNews.aspx Line: 55
Code: Protected Sub AddNewsBut_Click(ByVal sender As Object, ByVal e As System.EventArgs)
If Page.IsValid Then
InsertDs.Insert()
Response.Redirect("~/news/default.aspx")
End If
End Sub
Protected Sub InsertDs_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)
'Reference the FileUpload control
'Make sure a file has been successfully uploaded
If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then
Label1.Text = "If there is no photo of this event, please use a stock photo if you have none available. Else, please upload a photo of the event."
e.Cancel = True
Exit Sub
End If
'Make sure we are dealing with a JPG or GIF file
Dim extension As String = Path.GetExtension(UploadedFile.PostedFile.FileName).ToLower()
Dim MIMEType As String = Nothing
Select Case extension
Case ".gif"
MIMEType = "image/gif"
Case ".jpg", ".jpeg", ".jpe"
MIMEType = "image/jpeg"
Case ".png"
MIMEType = "image/png"
Case Else
'Invalid file type uploaded
Label1.Text = "Please use the correct file type: .jpg, .jpe, .gif, or .png."
e.Cancel = True
Exit Sub
End Select
'Specify the values for the MIMEType and ImageData parameters
e.Command.Parameters("Type").Value = MIMEType
'Load FileUpload's InputStream into Byte array
Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte
UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
e.Command.Parameters("Photo").Value = imageBytes
End Sub Please help.
I created a website a year ago. I remember I need to download something from Microsoft (some kind of sql admin thing), which allows me to convert my database in the Express Visual Web developer to sql file, then I can copy those sql commands into my web hosting company's sql admin window to upload the database online. I forgot what things I should download from Microsoft? Please help.
View 1 Replies View RelatedI am building this project and for all the data i put in to the DB Title,Author, etc.... ineed to also add a picture of the book. I terms on space what is the best way to do this? In the DB? Or ref a folder? If so does anyone have sample code for each.
ThanksMike
Hi, I am trying to upload a file to database.
I have used the following code, every loads good to the database apart from the image, does it go anywhere?
I have created a column in the table called 'FileUploadAdvert' and made it an image?
Any ideas where I'm going wrong?
ThanksGordon
Protected Sub btnAdvertSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdvertSubmit.ClickDim dashDataSource As New SqlDataSource()dashDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DashConnectionString1").ToString()
dashDataSource.InsertCommandType = SqlDataSourceCommandType.Text
dashDataSource.InsertCommand = "INSERT INTO tblAdvert (AdvertOwner, AdvertName, TopLeftH, TopLeftV, Height, Width, ToolTip, WebLink, AcceptTerms, DateTimeStamp, IPAddress) VALUES (@AdvertOwner, @AdvertName, @TopLeftH, @TopLeftV, @Height, @Width, @ToolTip, @WebLink, @AcceptTerms, @DateTimeStamp, @IPAddress)"dashDataSource.InsertParameters.Add("AdvertOwner", txtName.Text)
dashDataSource.InsertParameters.Add("AdvertName", txtCompName.Text)dashDataSource.InsertParameters.Add("TopLeftH", DropDownAccross.Text)
dashDataSource.InsertParameters.Add("TopLeftV", DropDownDown.Text)dashDataSource.InsertParameters.Add("Height", DropDownHeight.Text)
dashDataSource.InsertParameters.Add("Width", DropDownWidth.Text)dashDataSource.InsertParameters.Add("ToolTip", txtOver.Text)
dashDataSource.InsertParameters.Add("Weblink", txtURL.Text)dashDataSource.InsertParameters.Add("AcceptTerms", CheckBoxAgree.Checked)
dashDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString)dashDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now)
If Not FileUploadAdvert.PostedFile Is Nothing ThenDim filepath As String = FileUploadAdvert.PostedFile.FileName
Dim pat As String = "\(?:.+)\(.+).(.+)"Dim r As Regex = New Regex(pat)
'runDim m As Match = r.Match(filepath)
Dim file_ext As String = m.Groups(2).Captures(0).ToString()Dim filename As String = m.Groups(1).Captures(0).ToString()
Dim file As String = filename & "." & file_ext
'save the file to the server FileUploadAdvert.PostedFile.SaveAs(Server.MapPath(".") & file)
lblStatus.Text = "File Saved to: " & Server.MapPath(".") & fileEnd If
Dim rowsAffected As Integer = 0
Try
rowsAffected = dashDataSource.Insert()Catch ex As Exception
Server.Transfer("Register_Fail.aspx")
Finally
dashDataSource = Nothing
End Try
If rowsAffected <> 1 ThenServer.Transfer("Register_Fail.aspx")
ElseServer.Transfer("Register_Complete.aspx")
End If
End Sub
Can anyone give me an example of how to Upload a file to SQL in asp.net 2.0 using VB?
Thanks
Randy
We have to upload the database from the production server to the ineternet server. We are making a backup and restoring the databse on the internet sever, this requiers somebody on the serrver to restore the database manually. Is there any other way? Can i overwrite or update the new design the database on the server in any other way? Thanks.
View 1 Replies View Relatedi am loading xml files present in a folder to sql server. i am doing this by means of ActiveX script (VB). i am passing every xml file and its schema definition file as parameter. later this script automatically create a table in script and loads the the data to database. for every xml in that foleder there is only one xsd.--------this was my intention.
but when i run the DTS script error is coming like "Error source: schema mapping, unable to upload xsd file".
is that always necessary to add
(xlmns:sql "urn:schemas-microsoft-com:mapping-schema") in its schema definition file.
Hello!!
i need to upload a csv file to sql server, i am trying to load using bcp or bulk load, but the files has null columns, so i guess, that's why the program is displaying error.
my table is as follows:
PKCOLUMN TYPESIZENULLABLE
-----------------------------------------------
PKObject_idint 4NO
SO varchar 4NO
FISY smallint 2NO
Segment_idvarchar 3NO
SubSeg_idvarchar 3NO
Line_idvarchar 4NO
Level smallint 2NO
Type varchar 1NO
WWB_id varchar 3YES
SC_id varchar 6YES
PL_id varchar 9YES
PS_id varchar 12YES
PSS_id varchar 18YES
Material_idvarchar 18YES
WWCC_id varchar 2YES
THE OBJECT ID IS THE IDENTIFIER, SO IT SHOULD UPLOAD AUTOMATICALLY
MY FILE IS SOMETHING LIKE THIS:
SOFYSEGSUBSRNIVTIPOWWBSCPLPSPSSMATWWCC
45002008100110R16E102102120102120159102120159BBT102120159BBT100111300081NULL
45002008100110R16E102102120102120159102120159BBT102120159BBT100111300084NULL
45002008100110R16E102102705102705453102705453CIO102705453CIO100282300180NULL
45002008100110R16E102102705102705453102705453CIQ102705453CIQ100284300182NULL
45002008100110R16E102102705102705453102705453CIR102705453CIR100285300183NULL
45002008100110R16E102102120102120167102120167CIF102120167CIF100255300256NULL
45002008100110R16E102102705102705453102705453CJB102705453CJB103026300279NULL
45002008100110R16E102102705102705453102705453CIP102705453CIP100283300726NULL
45002008100110R16E102102120102120167102120167BED102120167BED100249300922NULL
45002008100110R16E102102120102120164102120164BDM102120164BDM103231301189NULL
45002008100110R16E102102120102120159102120159BBT102120159BBT100114301730NULL
45002008100110R16E102102443102443542102443542DFJ102443542DFJ102704301731NULL
45002008100110R16E102102705102705453102705453CIP102705453CIP100283301882NULL
45002008100110R14E102102120102120167102120167CIINULLNULLNULL
45002008100110R16E102102120102120159102120159BBT102120159BBT100111302346NULL
45002008100110R16E102102120102120159102120159BBT102120159BBT100111302347NULL
45002008100110R16E102102120102120159102120159BBT102120159BBT100111302349NULL
the first colums(identifier/object id) doesnt appear in the file, since it is supposed to be set automatically.
the last column is a null value.
Do you have any ideas??
why is it displayiing me an error?
which is the easiest way to upload the file??
thanx in advance
If I need to upload a single value from some singular query to the localvariable it may be like this:set @var = (select F from T where...)Next, what to do if I need more than one?Is it necessary to create a cursor, then open it, fetch and so on? Or may besomething likeset @var1, @var2 = (select F1, F2 from T where...)Thank you.
View 2 Replies View RelatedHI
Can anyone tell me how to upload a xml file in a table.I have uploaed text,csv,xel file but never xml.
just want to know how to upload, and when,why should we use xml file for data
thanks
sandipan
Hello
I am using Microsoft SQL Server Management Studio Express and want to insert a row into a table. The table (Pictures) has the following columns: ID (int), Filename (varchar), Data (varbinary).
INSERT INTO Pictures(ID, Filename, Data) VALUES
(12, 'photo.jpg', C:photo.jpg);
The statement above does not work. How do you do it?
Thank you
I have done an bulk upload and I would like to start doing incremental uploads. I just want to upload only the new records that have been added to my data source ( free foxpro tables ). Can anybody point me to an example or info to accomplish this.
Thanks,
Sergio
Simple question: How do I import/upload/insert data into a new database using MS SQL Server Management Studio Express, from a source like MSAccess or another SQL DB? I currently have a sample sql db (sample.bak), but unless I originally had it in MS SQL Server Management Studio Express it doesn't appear to want to restore it.
View 1 Replies View RelatedOkay, I am still really new at this so nobody laugh if this seems simple stupid. I created a little web page and while working on the project created a new database by right clicking on my app and clicking "Add New item" then choosing SQL Database. I added tables and everything was working just dandy on my machine. I FTP'd the files up to my web server and am now getting an error. SQL is installed on the server, but I have a couple of questions.1. Does SQL have to be installed on the server if you create one of these databases by doing the Add New Item button?2. After Uploading it to a server with SQL installed, the database shows in my App Data folder, but gives me an error. What needs done to move this database to the server besides uploading it?Here is my error:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
View 11 Replies View RelatedDownloaded and installed MSDE and just finished creating a
website using the Community Starter Kit. I'm used to working with Access
Databases and this is my first experience with anything SQL. Can someone
provide my with some guidance as to what I need to do to upload the MSDE
Database to a server. Can I just upload the files like an Access Database
or is this more involved. Thanks for you help.
Can someone please direct me to a discussion on which version of SQL Server allows a file to be stored in a db field and how this is accomplished as well as how the file is then retrieved with asp.net VB from the DBthanksMilton
View 16 Replies View RelatedHi there,
My problem is that the upload works in testing for our asp.net site only for dbo. Being mindful of security, I would prefer not use this account to execute all sp_/sql. One solution could be using impersonation only in the content management system where the uploading is done, this is code in web.config:
<location path="Manage.aspx"> <system.web> <identity impersonate="true" userName="dbo" password="****" /> <authorization> <deny users="?" /> </authorization> </system.web> </location>
To do this I would have to change the dbo password as set up by previous employee, not a real dba so not sure of the implications (enough permissions to be dangerous though;). Is the above impersonation ok, or should I redo logins security in sql? Thanks.
Please help me... How can i upload image into my sql database in a basic way... thanks....
View 6 Replies View RelatedHello..I have around 460 rows of data in my dataset. I have to upload them into an empty table that already exists in SQL server. Can somebody help me with it?
View 7 Replies View RelatedHi all, I am using ixwebhosting.com for a host and they say they are using sql 2005 servers, well any ways I have sql 2005 express edition, and sql 2000, I am able to export data from mysql2005 express using my 2000, but am having issues tryig to upoad the data to the 2005 sql server on the web host company. Any ideas on how to fix this or a workaround would be nice.Thanks All
Jonny
hi,
i want to ask how to save the path of the upload file (not the image) to the database?
ex : client upload from his/her computer, the path is c:/Program files/image.jpg
then the upload file is saved on the web server in location c:/intepub/wwwroot/imageGallery
then i just want to save the path c:/intepub/wwwroot/imageGallery/image1.jpg at my sqlserver
so that everytime an image is req, it's just search the path and display it
and the performance is better when i use path or save the image as binary file in sql server?
do know articles or link about this?
thank you again,
erick
I'm working on a web application that uses SQL Server 2000. Once I'm ready to upload my website to a production server, I need to know how to upload my SQL Server database files to the server. Are there actual files that can be uploaded? I've only deployed a website that works with MySQL to a production server, and there were no files to upload... the database had to be created on the server, and then the data itself had to be exported to text files (on my computer), then imported/uploaded to the production server using SQL statements...
How do I do this?
Thanks~