BCP Utility .
Aug 6, 2002
Hello ,
I want to know how can i can use the bcp coomand to import a text file into a SQL database table . The text file contains info. like servername , date , time , logininfo and description .
The problem is i cannot import that file into the table through data import wizard . The wizard is not able to separate the fields properly and the import takes place with mixed up data in different columns . The separator in the text file is colon . I even tried fixed length with manual column separation .
The text in the text file are not aligned properly as these are log files generated by the system which i am trying to import in a table .
So how can use the bcp command to specify the column length properly and seperate the fields uniformly so that the data gets imported neatly .
Any thoughts how could the command look like ?
Thank you very much .
Anita.
View 1 Replies
ADVERTISEMENT
Aug 2, 2001
Hi,
I used bcp utility to import and export data between databases. These exported data is it read only? If so how can I change the parameter so that we could edit as well.
Any suggestions will be helpful. Thank you!
View 1 Replies
View Related
Oct 8, 2002
I'm trying to use the bcp command to bcp out a single table in a database, what is the best way to do this in the query analyzer? Thanks in advance.
View 1 Replies
View Related
Jul 27, 2004
Hi all,
I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:
AppointmentKey
SurgerySlotKey
PatientKey
Cancelled
Continuation
Deleted
TimeArrived
I would like to import only two of these fields into a table called tbl_Appointments e.g.
AppointmentKey
TimeArrived
I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?
Thanks
View 8 Replies
View Related
Jan 20, 2004
In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \indiadbftprootCLIENT_BRANCH_UPLOADranchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r -c -t "," -q'
View 1 Replies
View Related
Feb 28, 2004
Hello,
I am using the DTS utility available with Enterprise Manager in MSSQL server 2000. I can transfer the tables and views without any issues but when i try to transfer the stored procedures it always gives an error. I have tried transferring individual objects too but it does'nt works. The error given is "the user <username> cannot perform the following action". Any help is appreciated.
Thanks in Advance
View 4 Replies
View Related
Jun 2, 2008
Hello, I need to create a stored procedure that creates a csv file. this is the code I have so far.
Script is like
CREATE PROCEDURE dbo.GetQuestionInfoCSV AS
BEGIN
declare @sql varchar(8000)
SET @sql = 'bcp "SELECT * FROM dbo.ISO_table" queryout C:GetCSV.CSV -c -t, -T -S MANCHESTSQLEXPRESS'
print @sql
EXEC master..xp_cmdshell @sql
END
and I get these errors on execution
SQLState = 42S02, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'dbo.ISO_table'.
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
NULL
Any help would much appreciated.
View 2 Replies
View Related
Nov 29, 2005
Greetings,Just wanted to know if there is any parameter in BCPutility that can ignore triggers, indexes and constraint defined for atable?any help will be greatly appreciatedTIA
View 2 Replies
View Related
Nov 30, 2007
I'm using SQL 2005 to export data. I would like to use the bcp utilityto export data to an Excel file.I have to generate quite a few files and the names are dynamic. Theideal would be to loop through records in a stored procedure to createa file name to use in the bcp. My question is how can I use the bcpfrom a stored procedure? I know how to run it from the command prompt.Is there a way to control the command prompt from a stored procedure?Thanks all
View 2 Replies
View Related
Apr 9, 2001
Hello everyone,
I have a question about dtsrun hoping someone have an answer. Does anyone know if there is a switch that I can turn onoff so that it will suppress the output result when I execute the dtsrun ulitity?
This is what I ran:
master..xp_cmdshell 'dtsrun /Sclancy /E /N ExpDispute'
I get the following result back after it's done:
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...DTSRun:Executing...DTSRun OnStart: Copy Data from Results to [Transplus_TestArchive].[dbo].[SG_DISPUTE] Step DTSRun OnProcess: Copy Data
from Results to [Transplus_TestArchive].[dbo].[SG_DISPUTE] Step; 14 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 14 DTSRun OnFinish: Copy Data from Results to [Transplus_TestArchive].[dbo].[SG_DISPUTE] Step DTSRun:
Package execution complete.
(6 row(s) affected)
Thank you in advance for any help.
Mai Nguyen
View 2 Replies
View Related
Apr 19, 1999
Hello All!
I would like to have the SQL server to mail me the alert to my email account.
The mail server is not in our domain.
So how would I set this up so that the alert can be emailed.
Thanks in advance for ur help.
Venkat
View 1 Replies
View Related
Jan 19, 2001
I am currently in the process of transfering data from an existing SQL 6.5 server to new box. Both boxes have SQL 6.5 with SP4. I having a problem with a paticular database transfer. The Database/Object transfer utility starts and then about 20% in it says that it is complete. The transfer creates all the objects in the new server but does not transfer the data. I have manually transferred the data but I am curious if anyone else has had this problem or if this is a known bug. Just curious. Thanks
View 2 Replies
View Related
Aug 6, 2001
Hi,
All the wanted to know whether the ISQL utility is optional during the sqlserver setup??? Is it possible to install sql server without installing ISQL utility??
Thanks in advance,
Asha
View 1 Replies
View Related
May 31, 1999
hi....
ok...all i want to do is take a global variable and pass it to another procedure using a wrapper... what am i missing...
if i call the 2nd procedure from the first it works fine.... but if i use the wrapper to call both procedures...and yes before you ask i am calling the first procedure that creates the variable before the second which uses it...
it doesn't see it... says it has no value...what am i missing.. please help...
kim
View 5 Replies
View Related
Jan 28, 2001
I am trying to do BCP from a comma separator .txt file to upload it into two tables. That is I want the first row (4 columns of header details ) into one table and from the second row (21 columns each of detail rows) to be uploaded into another table. I have written a batch file to upload the header details by giving the first and last column as 1.It is working fine. But when I try to upload the detail rows into another table by giving the first row as 2, the bcp is not able to read from the second row. It skips the second row and reads from the third row. This happens for all separators like pipeline separators also.So to avoid this I have to give blank 21 separators(i.e. , or |) in the second row to avoid the loss of data. Please provide a solution for this, as I cannot insist my customer to provide me a text file with blank comma in the second row.
Thanks and Regards,
S.Raghuraman
View 1 Replies
View Related
Jul 28, 2004
using osql utility i have converted a table in database to excel format. I have also chinese data in my database . The problem is while converting the data into excel format ,the chinese data is not comming in excel sheet.
Does anybody have an idea about this .
For converting i have used
declare @x varchar(300)
set @x = 'osql -S Servername -U username -P passwd -q "select * from northwind..region order by cand_index" -w 3000 -s "," -o C:directoryfilename.csv'
Thanks
View 2 Replies
View Related
Aug 14, 2004
Hi all
I am using osql utility in sql server 2000 to convert data in database to excel sheet. My datas contain both english and chinese . While converting to excel sheet english datas are comming but the datas in chinese is comming like (?) this. Does any body has any idea to solve this problem. is there any way to set font in osql utility.
set @x = 'osql -S servername -U username -P password -q "select * from
northwind..region" -w 3000 -s "," -o C:Downloaddownload.csv'
This is what i have used.
Note: I tried giving chinese font in excel still it is not working.
Its really urgent pls reply
thanks
View 2 Replies
View Related
Apr 22, 2008
The following webpage desribes the rs utility:-
http://msdn2.microsoft.com/en-us/library/ms162839.aspx
And it says that rs supports the optional parameter, -ltimeout
Can someone tell me what this timeout does/means?
We are passing in a VB.NET script file that reads an XML file that executes a series of reports. Does the timeout apply to the whole VB.NET script file, or to the individual execution of each report?
Can you also tell me, what happens if it times-out? Does SQL Server kill the SQL job generating the report? Or does it just disconnect the client from the server leaving the job to continue generating the report?
Thanks
View 1 Replies
View Related
Dec 9, 2007
Hi guys,
I'm trying to use the bcp utility to transfer data from an SQL Server table (CUSTOMERS) to a text file (test.txt).
Here is what I have:
DECLARE @FileName varchar(50)
SET @FileName = 'C: est.txt'
EXEC bcp BAirwaysDB..CUSTOMERS out @FileName -S<servername> -T
only I keep getting the error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
and when running:
bcp BAirwaysDB..CUSTOMERS out C: est.txt -S<servername> -T
on the command line I get:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'RIVERAMelanie'.
SQLState = 42000, NativeError = 4060
Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database "BAirways" requested by the login. The login failed.
Can you see where I'm going wrong with this? Any feedback would be really appreciated.
Thank you
Butterfly82
View 7 Replies
View Related
Aug 18, 2005
I have been successful at setting up three or four DTS packages to runusing the DTSrun command line utility and the Windows scheduler untilnow. The latest package gives me the following error no matter what Itry.Error: -2147217355 (80041035); Provider Error: 0 (0)Error string: General error -2147217355 (80041035).Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 705I googled the error but got only two hits, only one of which actuallydiscussed the error. That one talked about the security and ID contextof the system agent but I can't even run the package from the commandline, never mind using the Windows chron. The DTS command line I'musing looks like this:DTSRun /S MyServerInstance /R DBName /N MyDTSPacakgeName /EVariations included /U MyUserName /P MyPassWord and a couple others.Next step would have been to use /!Y and /!C to create an encoded batchfile to use for the Windows scheduler.Any help would be most appreciated.Randy
View 4 Replies
View Related
Feb 22, 2007
I'm getting some errors when I run rs.exe from command line with regard to Parameters, and was hoping I might get some help here. I'm somewhat new to RS. I'm running SQL 2000 and RS 2000 on a Windows 2003 Server.
Here is my script:
***********************************************************
' File: RunReport.rss
Dim format as string = "Excel"
Dim fileName as String = "C:Export2.xls"
Dim reportPath as String = "/APSAgentCourseCompletionByUserGroup"
Public Sub Main()
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "ms-excel"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
' Report Parameters
Dim parameters(5) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "User Group"
parameters(0).Value = "66179312-f341-4df7-a855-79c5b8abaf9f"
parameters(1) = New ParameterValue()
parameters(1).Name = "Start Date"
parameters(1).Value = "12/22/2006"
parameters(2) = New ParameterValue()
parameters(2).Name = "End Date"
parameters(2).Value = "2/17/2007"
parameters(3) = New ParameterValue()
parameters(3).Name = "Course Name"
parameters(3).Value = "AA2B1AA0-E78A-4F84-834F-8EAAB75BEBD3"
parameters(4) = New ParameterValue()
parameters(4).Name = "Status To Include"
parameters(4).Value = "0"
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
'End of script
***********************************************************
As you can see, right now I've got all 5 report parameters hard-coded.
Here is the command line that I'm using as well as the error that I receive:
rs.exe -i c:RunReport.rss -s http://localhost/reportserver -t
Unhandled exception: The value of parameter 'Parameters' is not valid. Check the documentation for information about valid values.
Any insight that anyone can provide is greatly appreciated.
Thanks!
Ray
View 2 Replies
View Related
Feb 1, 2008
Hi,
I want to know is it possible to export column data to a http folder ? When I executed the following bcp command
exec master.dbo.xp_cmdshell 'bcp "SELECT code from Table" queryout http://[server]:[portno]/Sample1.aspx -T -c'
the system throws an error.
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
Is it possible such a bcp command ?
Thanks
Maju
View 2 Replies
View Related
Nov 14, 2006
Hi All,
I have a Requirement where I want to backup my ReportServer key evey night to a file. Not sure if there is already any utility available on the internet but when I went to createa simple utility I was stuck where I dont have a way to run the rskeymgmt command silently.
Does anybody know of such a utility which can be scheduled on a nightly basis or alternatively is there a way where one can run the RsKeymgmt utility from the command prompt with out being prompted for the "Are you sure want to extract the key from the Report Server? Yes (y)/ No (n)"
Cheers,
siaj
View 6 Replies
View Related
Feb 25, 2008
Hi Friedns
How can i wil go command line Utility in SSIS Packages any command is there plz help me
View 6 Replies
View Related
Aug 14, 2006
In my transactional push replication I had differences, which were reported correctly by the utility tbalediff.
I created with the tablediff utility all necessary statements to correct them.
Afterwards I ran tablediff again to check if everything is ok.
It wasn't, I got a lot of mismatches.
The reason for those mismatches are columns from type "float". It looks like that float values are not generated correctly in insert statements. We had differences in the values and from that point on we have mismatches.
Does anyone has an idea how to get rid of those reported mismatches?!?!
I will report this also as a bug.
Regards
Norbert
View 11 Replies
View Related
Apr 22, 2006
ok. so, i've gotten the bcp utility to work and now i'd like to string all the prompts together and place them in a job to be scheduled to autorun. i don't have much experience in this arena and appreciate any help or suggestions. I'd love to be able to run (from the command prompt) a file takes care of synchronizing all my tables.
I have an additional concern. I'd like to retain the integrity of my primary keys but I notice that if i delete the information then add new, my primary key starts where it leaves off (in other words, if del. then synch. a table with 6 records, after the synch. my primary key increments starting at7). Is there a way, using the bcp util. to update records based on the primary key, copy the key verbatim, or a way to configure the table to help out with this? I know if i drop and recreate the tables as part of the job, i'm good to go but is there away around that step?
View 1 Replies
View Related
Apr 9, 2007
What SQL Server 2005 feature do I need to install to get the tablediff.exe utility installed?
Thanks.
View 1 Replies
View Related
Apr 9, 2007
What feature do I have to install in SQL Server 2005 to get the tablediff.exe installed?
Thans.
View 3 Replies
View Related
Apr 9, 2007
What SQL Server 2005 feature do I need to install to get the TableDiff utility? I have the developer edition.
Thanks.
View 1 Replies
View Related
Nov 8, 2007
Last month , I have made a utility named sqlce viewer to help me manage sqlce 3.5 beta version databases .
The location is https://sourceforge.net/projects/sqlceviewer/
And now , I encounter a strange problem , need some help .
After user install this utility or run it standalone execute file .There has a error message poped before see the login in form . Even user has installed .net framework version 3.5 .
And if user has installed visual studio 2008 beta2 ,this problem will not existed .
This utility depend on these components :
1)WPF
2).net framework 3.5 beta
I don't know what's the reason clearly .
Anyone could give some advice are warm welcome.
View 2 Replies
View Related
Nov 9, 2000
I'm getting errors in EM when trying to connect to one of my registered servers. When the Client Network utility is using TCP/IP as the default protocol I get:
A connection could not be established to xxxxx - General network error. Check your network
documentation. ConnectionOpen (connect())...
But if I switch to use Named Pipes I connect just fine.
This worked just fine up until last week.. We're going through Tokenring to Ethernet conversions.. Is this a bigger network issue?
Thanks..
View 1 Replies
View Related
Dec 7, 2000
Can anyone tell me how to enable bcp utility. Because anytime I try to run BCP I get the following error messages: (BCP not enable). Also how can I get to SQL Server command prompt? I look forward to hearing from anyone.
View 2 Replies
View Related
Dec 5, 2001
(SQL Server7, NT4)
Were upgrading our server with SP3. Do the end users using the SQL server client network utility need SP3?
View 2 Replies
View Related
