Bcp Copying,but No File

Jun 1, 2004

DECLARE @query NVARCHAR(2000)
SELECT @query = 'bcp "SELECT * from dbname..tablename" queryout c: est.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @query

I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL

and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.

Could somebody please help me

View 1 Replies


ADVERTISEMENT

Copying XML File Into SQL

Aug 17, 2006

Hi, I have been having fun and games for well over a week now trying to get an xml file copied into an sql file, but still have no joy. I and using the bulk copy to do this and think I am close to solving it but just need a final push in the write direction.
In green below is the full source code, I think that the trouble is with the try statement part here:

 
            Try
                bulk.WriteToServer(xd)
 
This comes up with the error :
Unable to cast object of type 'System.Xml.XmlDocument' to type 'System.Data.IDataReader'
 
Basically how do I get the XML data into a reader (or IDataReader) format so that the writetoserver command can interpret it
 
I would be so greatful if someone could help resolve this it is becoming increasingly more frustrating
  Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objXML
Dim objXSL
' destination table

Dim connectionString As String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionString
Dim myNewConnection As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connectionString)
myNewConnection.Open()

Dim productreader As XmlReader
Dim bulk As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(myNewConnection)
bulk.DestinationTableName = "Product"
Dim productcount As Integer = 0

Dim settings As New System.Xml.XmlReaderSettings()
settings.IgnoreWhitespace = True
settings.IgnoreComments = True

Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml"))
Using reader As XmlReader = XmlReader.Create(xs, settings)
While (reader.Read())
If (reader.NodeType = XmlNodeType.Element And "product" = reader.LocalName) Then
productcount += 1
End If
End While
End Using
Response.Write(String.Format("found {0} products!", productcount))

Using bulk
bulk.DestinationTableName = "Product"

Dim xd As New System.Xml.XmlDocument()
xd.Load("C:Documents and SettingsSimonMy DocumentsVisual Studio 2005WebSitesWebSite1App_Dataxmlfile.xml")
Dim xr As New System.Xml.XmlTextReader(Server.MapPath("~/App_Data/xmlfile.xml"))

bulk.ColumnMappings.Add("TDProductId", "TDProductId") ' map First to first_name

bulk.ColumnMappings.Add("name", "name") ' map Last to last_name

bulk.ColumnMappings.Add("description", "description") ' map Date to first_sale

bulk.ColumnMappings.Add("imageUrl", "imageUrl") ' map Amount to sale_amount

bulk.ColumnMappings.Add("productUrl", "productUrl") ' map UserID from Session to user_id

bulk.ColumnMappings.Add("price", "price")
bulk.ColumnMappings.Add("currency", "currency")
bulk.DestinationTableName = "Affilaite_Product_new"

Try
bulk.WriteToServer(xd)
Catch ex As Exception
Response.Write(ex.Message)
Finally
myNewConnection.Close()
bulk.Close()
xd = Nothing

End Try

End Using


End Sub
 

View 2 Replies View Related

Copying SQL, Mdf File / Please Help

Nov 27, 2004

I have a SQL db on my server in internet
the server and my DB is online
I want to copy mdf file to my computer via FTP but I get an error
that file is in use and I can`t copy it

I wanted to know is there any way that i copy information in that MDF fle
to another DB on my computer

Its very important please help me

thanks all

View 5 Replies View Related

Copying File From URL

Oct 5, 2007



Hi,

How can we copy a file, which is located at an URL like


http://www.siv.org/offi/download/Animfile/Anim.zip ?


I just need to copy this to my network.

Thanks,
Siva.

View 11 Replies View Related

Problem Copying SQL DB File

Nov 2, 2006

Hi,

I have a VB project which uses SQL Server 2005 Express. When the project first runs, it copies its DB files from the data directory to a directory created on the C: drive. This works fine.

In a later part of the application, it is necessary to copy the DB file to another location e.g. memory stick so that it can be used to merge data onto another PC running the same application.

The data merging works too.

My problem is, despite the fact that it worked okay this morning, I am now unable to copy the DB into a second location (memory stick or location on local disc). I get an exception stating that the file is in use.

I have no other applications running (I shut down Visual Studio and was running the installed version).

What is more, the form that implements the file copying, first closes down all other forms in the application to ensure that nothing is connected to the DB.

Furthermore, the form that performs this task has no DB connections whatsoever - it simply allows the user to select a location, then creates a directory and copies the DB files to it.

If I copy/paste the code plus the FolderBrowserDialog into the first form that copies the original database out of the data directory - it works!

The code for the whole form is below - I would be hugely grateful for any ideas ...

 

Public Class Export

Private Sub Export_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub NextBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextBtn.Click

'Close all other windows to ensure that the databases are not in use.

Dim Counter As Integer = My.Application.OpenForms.Count

Do While Counter > 1

For Each f As Form In My.Application.OpenForms

If Not (f.GetType Is GetType(Export)) Then

f.Close()

Exit For

End If

Next

Counter -= 1

Loop

 

' Set the Help text description for the FolderBrowserDialog.

Me.FolderBrowserDialog.Description = _

"Select a location to export the data to e.g. a memory stick. Or, you can export to a local directory and then burn to CD or e-mail to another user."

' Do not allow the user to create New files via the FolderBrowserDialog.

Me.FolderBrowserDialog.ShowNewFolderButton = False

' Default to the My Documents folder.

Me.FolderBrowserDialog.RootFolder = Environment.SpecialFolder.MyComputer

 

Dim result As DialogResult = FolderBrowserDialog.ShowDialog()

Dim foldername As String

If result = DialogResult.OK Then

foldername = FolderBrowserDialog.SelectedPath

Try

If Not My.Computer.FileSystem.DirectoryExists(foldername & "ClubSecExportMerge") Then

My.Computer.FileSystem.CreateDirectory(foldername & "ClubSecExportMerge")

MsgBox("Merge Directory created.")

End If

My.Computer.FileSystem.CopyFile("C:ClubsecClubSecDB.mdf", foldername & "ClubsecExportMergeMergeDB.mdf", True)

My.Computer.FileSystem.CopyFile("C:ClubsecClubSecDB_log.ldf", foldername & "ClubsecExportMergeMergeDB_log.ldf", True)

MsgBox("Database merge files successfully copied to " & foldername & " ClubSecExportMerge.", MsgBoxStyle.OkOnly, "Operation successful")

 

Catch ex As Exception

MsgBox("There was an error." & Chr(13) & "Export of database was not achieved." & Chr(13) & ex.ToString, MsgBoxStyle.OkOnly, "Operation failed.")

Exit Sub

End Try

Else ' Cancel button pressed

MsgBox("No path selected. Data not exported", MsgBoxStyle.OkOnly, "Operation cancelled.")

End If

Dim home As New HomeScreen

home.Show()

Me.Close()

End Sub



End Class

View 5 Replies View Related

Copying Database Backup File

Nov 6, 2003

This is a pretty basic question, but I've generated a backup file for one of my SQL 2000 databases, and I need to copy the backup file to another computer so I can burn it on CD. Whenever I try to copy the file, I get an error message saying that the file is in use by another process and can't be copied. I have tried detaching the database, shutting down Enterprise Manager, stopping the MSSQL service, but all to no avail. There must be a way to do this, but I can't get it to work. Does anyone have any ideas?

View 3 Replies View Related

Master File Not Accessible For Copying

Sep 23, 2006

Re SQL Server 2005 ExpressWhen I attempt to copy the master file for backing up, it is being used byanother application, even after shutting down the PC completely and poweringit up again. What can I do to make back-ups?Zach

View 2 Replies View Related

Copying A Sql Server Database File

Mar 12, 2008

I am developing a .NET 2.0 application that uses Sql Express as its backend.
I use OleDb Provider to connect to Sql express.

One of the options in the app allows the Admin to create a Backup of the database. As part of this process, I write a log entry to one of the tables in the database, commit & close the connection, and then copy the physical database(.mdf) file to some pre-determined location using the following instruction (in VB):
My.Computer.FileSystem.CopyFile("Path to Database File", "Destination Path")

It throws an exception saying that the file is in use. Upon checking, I found that the file has been locked-up by the Sql process.
I ensured that my app closes all connections to the database, that there is none other trying to connect to it, and then again tried to execute the above instruction in my app, again raising the same error.

However, when the app terminates, then I can easily copy the databse using explorer. I also used the OleDb method ReleaseObjectPool(), but the problem persisted.

So, how can I copy the database in such a situation from within my app??

View 3 Replies View Related

Integration Services :: SSIS Copying Populated Excel File?

Jul 9, 2015

I am experiencing unexpected behaviour when copying an Excel file.

An Excel file from TemplateDir to WorkingDir.

Excel file is then populated in WorkingDir, ok Copy File task copies Excel File from WorkingDir  to FinalDir, however non populated version of file is copied.

However, if I stop SSIS and and execute Copy Task only,  the populated version of file is copied.

It seems SSIS has a kind of lock on Excel file in WorkingDir.

how lock can be released to enable Copy of populated file.

View 6 Replies View Related

SQL Server 2005 Agent - Error Copying A File Onto The Network...

Jan 10, 2008

Hi,

I have an SSIS package designed to copy across a file from local machine to a location on the network.

The SSIS package works fine when I run it directly run it in the Designer, ie, it copies over the file to the network.

But when I run the package through the Job server agent, it fails giving me a validation error on the network path..

Does it have to do with the permissions of the User account used here? Could anyone please provide any information on this....


Thanks

View 14 Replies View Related

Doing BCP In AdvenutreWorks:Copying Data From A File To A Table-SQLState=08001,NativeError=2 && SQLState=HYT00,NativeError=0,Why?

Jan 25, 2008

Hi all,
I copied the following code from MSDN2 bcp Utility:
bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c
and executed it in my C:Program FilesMicrosoft SQL Server90ToolsBinn>.
I got the following errors:

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]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.


Please help and tell me what wrong with this BCP execution or with the database, and how to make this BCP working.

Thanks,
Scott Chang

View 3 Replies View Related

Copying DBs

Jul 8, 2004

What is the Syntax for a statement that can copy one MS SQL DB to another Database (on a different server). Or is this even possible?

View 3 Replies View Related

Need Help In Copying Sps

Jul 31, 2007

Dear friends

i want to copy all stored procedures in one drives. if i do manually it will take whole day. i have to change udd length in all sps.so please anybody give sugessions

View 1 Replies View Related

Tables Copying

Dec 24, 2007

 
i have sql local database in the application . I want to copy the table from one local database to another. here the detination table is already created with
one field which is incremental and other field is image and some other fields are text. any solutions on how to do it
 

View 3 Replies View Related

Copying A Database

Mar 27, 2004

Hi all,
I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!

Vik!

View 4 Replies View Related

Copying The Database

Aug 18, 2004

Hi,

I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.

Thanks in advance,
Uday

View 2 Replies View Related

Help-for Copying Database

Oct 11, 2004

hi,
i m new 4 sql server.
i create one app. in asp.net.

i create one database in sql server.
now,
i want to make setup for my app.
so,
i want copy database and put on c:/..../wwwroot/app folder.

how can i do this?

one more things,
i create database using enterprise manager.
i want to see my database file.
where it is stored?

somebody help me.

it's urgent.
thanks in advance.

View 1 Replies View Related

Copying From One Column To Another

Dec 22, 2004

It's all in the same table
How do I copy something from one column to another, but limiting it to only the first "x" characters?

so if i wanted to copy just 'ABC' instead of 'ABCDEFG' how could i do that?

thanks in advance

View 5 Replies View Related

Copying Data From PRD To TST

Apr 28, 2006

I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also. Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.What is the easiest way to do so?

View 6 Replies View Related

DB Question: Copying ID

Jun 10, 2006

Hi,
I'm trying to insert one value (an order) into the table Order and (via a for-loop) all the products in that order in the table Product, hence, one order can have multiple products (and must have at least one). I have an automatically increased value for the OrderID as the primary key for Order, and I have a foreign key named OrderID in the Product table. So far, I _think_ everything's logically correct.
However, I don't understand how to retrieve the OrderID to be able to insert it in the Product table upon insertion. I guess this is done all the time, but the only solution I can think of is to make a new SQL Command, asking for the just created OrderID to use it in the SQL Command for the products' for-loop. I'm sure that's a bad idea. :-)
Can I use relationships or so to make this automatically updated (that is, to have the Product table "check for" the OrderID and insert the OrderID upon insertion of the Product row(s))?
I hope this is clear to you. Thanks in advance for all help!
Pettrer

View 1 Replies View Related

Copying Logins

May 6, 2002

Hello List,

Folks, I need a help here. We are running our production database on SQL Server 7.0. We are in a process of switching the production database server to another server hardware. I was wondering, How can we copy the Logins/Passwords from my old server to the new one. Well this is very important to have all the old logins and passwords on the new server to have anybody login to it. I know, Doing Export will copy the logins. But would it allow the same old password to be on the new server?

Please help me here.

View 1 Replies View Related

DTS Not Copying All Records From SQL

Jul 20, 2001

I have a SQL Table called Consumers that contains 495,037 records.
The record size is about 350 bytes and it has one field that is a varchar (255). I am using DTS to copy this file from SQL (7.0) to Access (97). Last week this file had about 216,000 records and the DTS Process worked okay. Today, it has 495,037 records and the DTS Process appears to lose some records (about 26,000) during the copy. In the DTS Package the SQL query is just a straight SELECT statement with no WHERE condition.

During the DTS Process the SQL Consumers Table is being copied to and Access Table. The error I am getting during the DTS Process is "Error at Destination for Row # 468608".

When I open the Access Table it shows 468,608 as the actual number of rows in the Access Table and shows ConsumerID 675820 (primary key, identity field) for this particular record.

When I link to the SQL Table via Access the Record # 468,608 shows ConsumerID 643852 and the last record shows Record # 495,037 and ConsumerID 675820.

This tells me that some records are not being copied over during the DTS Process. The last record on the SQL Table is on the Access Table. So there are probably 26,429 (495,037 on SQL Table minus 468,608 on Access Table) that are not on the Access Table.

The DTS Select Query does not have a Where Condition. Do you think some records are being lost because of the size of the SQL Table?

There is an Advance Tab on the DTS Properties Page; there are some
options like 'Insert Commit Size', Fetch Buffer Size', 'Use Fast Load', 'Keep NULLS', and 'Check Constraints' that I
need to take a further look at. Have you used any of these options and do you think any particular one may help
my problem?

There is also and 'Exception File Name' option. I will give this a try next week to see if it will write the problem
records to disk.

Thanks for your help, Kevin

View 1 Replies View Related

Copying VIEW From One Db To Another

Aug 23, 2001

I have been trying to copy a VIEW from one database to another, but SQL 7 doesn't let me. I use the DTS wizard and it looks like it happns but then it doesn't appear, and I have tried to refresh. Can someone please tell me what I am doing wrong?

TIA,
Bruce David

View 1 Replies View Related

Copying DTS Package

Sep 24, 2001

Hello,

This may seem like an easy question, but I have a DTS package that I need to copy to another SQL server. Is this done easily? Can it be done (i think so?).

Thanks in advance,

Jim

View 1 Replies View Related

Copying DAT Files In 6.5

Mar 30, 2000

I have an 8 GB SQL 6.5 database that I need to move to another server. I know I can create the devices and the database on the new server exactly as they were created on the old server then replace the new DAT files with the old (after shuting down SQL Server). The issue I am running into is that the sort order and character sets on the two servers are different. Will this have an effect if I copy the DAT files? Am I better off using BCP? I'd hate to use BCP because of the time that it will take to move the files.

Thanks for you help

View 4 Replies View Related

DTS Moving/copying

Jul 23, 1999

Does anyone know how to move or copy DTS Package?
That I need to do:
I would have to move database from one server to the other
and last thing I want to do is recreate DTS packages from
scrach.
I could not find any way of transfering DTS packages.

Any help greatly appreciated

View 3 Replies View Related

Copying A Table From One Db To Another

Mar 5, 2001

I have 2 databases on the same sql server. Both have the same tables. I need to copy the contents of tableA from the source database to TableA on the destination database. Can I do this using a SQL script or some kind of script, and if so how and what would the syntax be.

please reply via e-mail also...res2100@yahoo.com

View 4 Replies View Related

Copying Objects

Mar 5, 2003

Is it possible to copy tables/indexes/data from one db to another (on the same server) while specifying a new owner & w/o logging?

View 11 Replies View Related

Copying Tables

Dec 22, 2004

hi,



I have a database called marketing in it i have a table called products and right now there are five products in the table with product_id as 8003,8004,8005,8006,8007 i want to create the same table in the database but my product_id should start from 1 and i only want three products from the old table to be copied into the new table any idea how to make this happen.

thanks,

belord

View 1 Replies View Related

Copying A Row From One Table To Another

Mar 18, 2005

Hi, I'm pretty new to SQL so am still learning the syntax etc.

I have 2 tables, one called Railways and another called Sheet1$ (an import from Excel).

In Railways, I have 5 Fields:

ID
First_Name
Last_Name
File
Full_Name

At the moment, all are populated with data (300 records) but Full_Name is showing values of <NULL>.

I want to populate Full_Name with the Data in Sheet1$ which has the field Full_Name (which is populated with the data I need)

What is the query I need to run to get SQL to copy the data from Sheet1$ (Full_Name) into Railways (Full_Name) - replacing all of the <NULL> values?

I've tried using the import wizard but without success.

Thanks in advance.

View 5 Replies View Related

Copying SQL Diagrams

Feb 26, 2002

Is there a way to export a database diagram? No preference as to the format of the exported file.

Thanks!

View 1 Replies View Related

Copying DTS Packages

Jun 11, 2001

Is there a way to copy saved DTS packages from one server to another?

Thanks!

Ellen

View 6 Replies View Related

COPYING A DB From One Server To Another

Feb 15, 2005

I was trying to find the fastest way to COPY a 50G DB from our production server to our test server. I was testing this on our test server and had a simple question.

Attach/Detach is out since I can only move not copy a DB using this method.
export/import, bcp and DTS take too long and are not indented for this anyways.

so, I was left with Backup and restore. so, I tried that on two different test servers. Took very long. so, I decided to be brave and try the following.

1. Take DB1 that's running on server1 OFFLINE
2. Manually copy all datafiles and logfiles from Server1 to server2.
3. Attach DB1 on server2.

It came up great. No complaints whatsoever. So basically, it's the same as attach/detach but instead of detaching the DB I took the Db offline and copied the files over. So my question...

Is this supported?
Any chance of corruption on either of the servers?
If a DB is offline, is copying datafiles and logfiles supported (I am bit worried if I might corrupt data in production).
Do people use this method to COPY databases or do they stick with BACKUP and RESTORE?

PS: Are there any other ways to do this?

Thanks so much.

View 5 Replies View Related







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