I need to export my data using DTS into a csv text file but with fixed
record length
ie: "data ","more data ","data "
and so on - is there an easy way to do this? - when you setup the
destination text file it appears to be a choice between one and the other.
Regards
James Bevan
Hi
I built a new sql server database and want to export its content to a .sql file. How to do it? (EM, QA ?) Thanks
I have a SQL database with about 600 tables. Is there an easy way to export all tables into individual excel files?
Thanks,
Ken Nicholson
Sara Lee Corporation
Hi, I'm new to SQL sever and am using SQL Server Management Studio Express 2005.
I've written my query that returns too many rows to be looked at Excel, and so wish to view and perform my calculations in SAS.
However, I don't know how to export the data I am getting in to SAS. I have tried using the Results to File option, but when I try import this into SAS it doesn't like the data.
Is there some option I need or is there anyway I can export the data to SAS.
Thanks a lot.
Nesu.
Hi,I have a bunch of stored procedures (some using temp tables) which giveout tables which are then used in crystal to give out reports. Thesestored procs are run dynamically depending on values users select onformsSome people instead of reports want Comma Seperated Files (CSVs). I amtrying to find a good way of outputting to csv with using either sqlserver or ASP. I tried two ways but none of them were ideal1st methodfirst way i tried was creating a record set in asp and then using thefollowing to output the data in a comma delimited row and writing tofile.Response.Write RecordSet.GetString(,,", ",vbCrLf,"")The problem with the above was with data sets greater than 10,000records the processing time increases exponentially because of memoryusage ( i found other people with the same problem)So i tried the following2nd methodI used bcp using the master.dbo.xp_cmdshell command to output thecsv. The problem with that is that the bcp executes the stored procthree times and also doesnot give out column headers. So i had to runthe stored proc once in ASP and get the headers from the record set andstore it to a text file and then run the stored proc in bcp and sendthe output to a csv file. Then merge the two files into a third file.The problem with this method is that it takes around 4 times as long torun as just the stored proceedure and also i have to create 3 filesinstead of one.I would appreciate any suggestions you have to output the csv from astored proc in sql server. Note users have to do this dynamically whenthey submit a form so cant use the query analyzer or enterprise managertools.Thanks for your time and help:)
View 4 Replies View RelatedI have a report which is just a simple table with only the detail row (which then as about a dozen columns)
I have uploaded this to report manger. The report generates correctly and I export to CSV.
But when I open the CSV file there are Headings at the top. I want to know is there any way of getting rid of there headers so it is jus the data which shows.
(The headers seem to be the name of the textbox column in reporting services)
I'm not sure if this is possible or not, still new to RS. I need to export a file in CSV format but a vendor needs the very first character to be a '~'. I've tried adding that to the first column name without sucess and RS won't allow me to name it with the ~ in the properties box. Any ideas? Thanks
View 1 Replies View RelatedHi,
is there a way of exporting a database structure and not the data in it?
Thanks.
i have created a SQL Server DB in a Web Site. (Not a SQL Server Project) so its a DB in a Web Site. how do i export the Database so that i can use it with other Projects?
View 4 Replies View RelatedWhen using the DTS to copy a db from my local MSSQL to the remote server (connecting via IP address) everything is copied ok except the diagram laying out the relations.
The table relations *are* copied because the damn thing works as expected also on the remote server.
I figured out that the diagrams are saved in the sysfile 'dtproperties' but when copying using the DTS query tool from local to remote this table is not visible.
I need to remove some relations on certain tables when bulk loading new data and was just wondering how I got to copy the diagram (as opposed to creating a new diagram on the remote server and adding relations manually).
Thanks a lot for any ideas,
Niels
Can DTS be used for exporting XML to files?
Thanks for your help.
Les
hi all,
I am getting problem while importing data from excel file.
I am bale to do the same with flat files. But when i do with excel files
its throwing error : format error.
pls help me in this regard.
and How to export data into export files from query analyser..
I have DTS package which exports the data from myTable to myXLSheet - works fine, except this appends data to the existing myXLSheet.
Is there any way of creating a new sheet or clearing out the existing one first???
Thanks
We just installed another SQL Server and we need to export data from one server to the other
inorder to set up replication. When I tried to export the database from the one server to the other
I got the following error:
The license for the installation of MS SQL Server on your source and destination connection
does not permit the use of DTS to transer data.
Is this because of the setting we used to install the server?
Any help would be greatly appreciated. Also if you could direct me to additional documention
to deal with this, that would be great.
Thank you.
Lynn
I created a DTS task to transfer data from a SQL7.0 table to a SQL6.5 table. This trasfer is really slow.
The transfer the other way is amazingly fast.
Any ideas ??
Thank you in advance.
Hi all. Im tryin to export (DTS) my some SQL server tables, many of which contain 'bit' datatypes. However, when DTS/SQL Serv. moves these bit datatypes out, it changes bit values to True/False values - which makes sense - however these are all going to plugin to web frontends where the SQL specifies condtions like: "if column1 = 0 then" etc..
Is there anyway to get SQL server to export bit datatypes as just numeric values of 0/1?
My company just purchased another company and I was tasked with extracting the data from the ACT! database that the purchased company was using for it's Contact Management to import into our CRM. The problem is that all of the contact data is in FoxPro .dbf files that I can extract from, but all of the sales and history data appears to be in unfamiliar file formats that I can not get to from the SQL QA, EM or Access. When I open them up in notepad, the data is a mess. The ACT export feature only appears to export this same Contact data which is pretty useless without everything else. There are third party plug ins that claim to be able to export all of the data, but I have only seen one that I really like and I do not really want to throw down $250 for something we are going to use once for 15 minutes. The trial version only lets you dump about 100 records. I googled the heck out of this and I could'nt find a solution.
Any ideas would be greatly appreciated.
Hello everybody,
I was wondering if there is a way to export and recode data at the same time with SQL.
For example I have gender information coded as 1 or 2 in my table and I need to upload the information to a different application that needs M or F. Is there a way to export to a new table and recode at the same time ?
I'm still pretty new to it.
Thank you.
-Seb
SQL Server 2000.
Hey there,
I'm trying to set up a test environment. My projuction environemtn has a bunch of Scheduled Jobs which run either stored procedures or DTS jobs.
I've exported the SP's and the Scheduled jobs but I'm wondering if there is any way to migrate the DTS jobs?
Cheers...
I would like to export data to a excel file using a stored procedure. I'm not sure how to go about this or if it is even possible. Can someone point me to a link to show me how to do this?
View 1 Replies View Relatedhow to import data into sql and export from sqlserver
is there any options are there or else any command directly type and execute
automatically data will dump into new one
Hi all,
I recently found out that there is no easy way to distribute a MSSQL 2005 database to MSSQL Server 2000. Most forums that I have read say to use the DTS facility to import the data into the SQL 2000, however this is not an option (for a variety of reason I won't go into).
The next best option in my mind is to script the entire database, including the data into one or a few script files then run them on the SQL 2000 server to recreate the database. Unfortunately, exporting the data, what I thought would be a fundamental feature, isn't part of SQL 2005.
So does anybody know of a good (free) scripting program that will allow me to export the entire database from a server? I've tried:
- Free program from the CodeProject.com (program dies when there's more than 5000 lines) http://www.codeproject.com/dotnet/ScriptDatabase.asp
-SQL Scripter www.sqlscripter.com (Doesn't script table which don't have primary keys, and produces a script for each object instead of just a single file)
I'd probably like a program which only creates a single script, as the database has over 200 tables and I don't want to have to go through this process everytime I need to distribute the DB (which will be often).
The only other option I can think of is a program which converts an SQL 2005 backup to a 2000 version.
Thanks for your help!
I need to export various tables (over 5k records) to CSV format to import the data to a CRM (a web based customer relationship management).
The problem is my nvarchar fields have commas in them. So I cannot map the fields correctly and some characters are not displayed properly.
What is the best procedure?
I also tried to export to Excel then CSV but it uses ; as delimiter which is not compatible.
Hi Guys,
In Sql Server Management Studio, when I have run a query and I want to export to file, how do I include the headers (field Names) in the first row to be exported also.
Thanks for your help,
Steve.
Steve
I am exporting data via the "for xml auto,elements" clause.
The data is bcp:ed out to textfiles.
The recipients are now complaining that there are no line breaks between the tags.
i.e. they want it more "nicely formatted".
So my question is basically, is it possible to handle this request nicely from the server side?
I basically just do:
bcp "select blah,blah from tbl for xml auto,elements" queryout "D:..." -w -T -t -r
Any way to get line breaks???
rockmoose
Hi,
Ive installed and imported a db into SQL2005 Express. I now need to export the db to an excel file but I cannot find the import/export feature. Ive tried navigating to the db within the SMSE inteface and right clicking but there is no options available to carry out the task.
Any ideas guys, stuck on this one
DW
Guys,
I'm trying to export data from SQL. Can anyone help. I have commented my script below and have managed to create a table, insert using 'bulk insert' - now I want to export the data out. I'm getting an error message saying 'cannot use the output option when passing to a stored procedure'.
comment
-----------
drop table for re-runs !
-----------
drop table JET
go
comment
------------
create a table to match the .txt file importing
----------
create table JET
( [USER] char( 25),
[DESC] char (20),
SYSDATE datetime,
SYSVALUE MONEY ,
POSTDATE datetime,
POSTVALUE MONEY,
GLCODE CHAR (20)
)
comment
---------------
import using pipe delimited .txt file
--------------
bulk insert JET from 'D:Documents and SettingsmpeetSQL Test.txt'
with
(
fieldterminator = '|',
firstrow = 2
)
comment
-------
check results
-------
select *
from JET
NEW BATCH
bcp JET out 'D:Documents and SettingsmpeetSQL Testexport.txt'
I want extract the data to a .txt file would anyone know the syntax?
Cheers
Michael
I have several dbs I export to Access each month and have not had this problem before. When I try exporting now, I get the message "cannot convert between unicode and non-unicode string data types". I know I can change the data type from varchar to nvarchar to correct this but there are dozens and dozens of tables in these dbs and changing all of them is not an option. Any suggestions first to how to accomplish the export and second why this is suddenly happening. I run SQL Server 2005 Standard and keep my updates current on my OS's and applications.
Thanks,
Dave
I am using sql server 2005
I want export data from Excel to new SQL Server table,
select *
into mytable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:sample1.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
But i am getting following error message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Does any one know how to name the tabs in reporting services when you export the date to excel. example I want sheet1 to be Summary. Can this be done in rporting services?
View 2 Replies View RelatedI'm very new to SQL - please bear with me.
1. I need to be able to export data from a database to an excel sheet (I have written the query and tested it works, so I don't need to know this stage :^)). What is the best way of doing this? Could you send me a link of a howto doc?
2. Once the data is export to excel, it then needs to be manipulated so that a final sheet is created. During the manipulation I need to add the values of certain columns to give me an end result.
Should I use excel to manipulate the data or can sql add the values of certain columns and then export to an excel sheet?
What's the best way please.
Many thanks
When I am trying to export a table from SQL Server Enterprise (usingEnterprise Manager), I am gettin errors at the end. It is saying the'Employees' table does not exist. Please help.
View 1 Replies View RelatedI have an asp site connected to an MS-SQL database, I need to exportthe database and import it in to a MySQL database? How would I be ableto do this? My only way of communicating with the MS-SQL database isvia asp scripts.Thank YouBen SagalPS. i have no problem fixing the SQL code manually to be compatiblewith MySQL.
View 1 Replies View Related