MS SQL(2000) table export to excel

I′m looking for a script (sql transaction, or function) to export MS
SQL(2000) Table to formated excel sheet (cells width, text format, ...).how about using the DTS
to export that to excel
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Milo?,
You also can use BCP command. See Books Online.
Regards,
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Thank You Jose,
DTS export ist very easy, for my situation is the best, but it has litlle
problem with text encoding.
Milo?
?Jose G. de Jesus Jr MCP, MCDBA" nap_sal (nap_sala):
> how about using the DTS
> to export that to excel
>
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Milo?" wrote:
>sql

MS SQL Server import/export wizard

Hi everyone,
Plese help me out. I import one database from one server to another. This process able to copy all the tables and functions but it copied only few stored procedures.
so tell me how to copy or import remaining stored procedures?You could always right click on the database, go toall tasks and hit generate script and select the sps you need scripted and then run said script on the new server.|||Or better still

try using the Copy Database Wizard instead of the Import/Export Data Wizard

:-) GW

MS SQL export / import data

Hi there,

I need to dump some data from a db by using MS Query. I noticed couple of issues during my attempts to do so. However I tried to run a query like:

SELECT table.column
INTO OUTFILE '/tmp/result.txt'
FROM table

which generated an error message:
Didn't expect "OUTFILE" after the SELECT column list.

Next I tried was:

SELECT table.column
FROM table
INTO OUTFILE '/tmp/result.txt'

generated another error:
Syntax error in FROM clause.

I also tried to define not only relative path to te result file but absolute path as well but it didn't help me a lot :-(
Could anybody help me what is theright syntx of this statement or is there some special way of MS Query to dump or export or write rows from the database into a file ?

Every suggestion will be higky appreciated

Tanks in advancewhy not try select col1name1, colname2, ... from tablename
then highlight the results, copy it and paste in a text editor eg notepad.
You can then save it and eventually open with excel.

MS SQL database Transfer

How can I transfer an online MS SQL Database with all data to a local machine MS SQL server. I am using Import Export Tool provided by MS SQL Enterprise manager but the referential integraity breaks (Primary and secondary key relationship). Thus the Data concurrency is lost and the database becaomes of no us.

Most of our database projects have Stored procs, Views, Triggers and contain data. Sometimes we have to take back up of the running database.

The same problem comes when we try to install an offline database with data online. We have to run the scripts through Query Analyser on the online server to install database tables, procs, triggers etc and then repopulate the database once again.

But if you see mySQL one can import the complete database with all data using phpmyAdmin tool. Most hosts provide this tool so becomes easier.

Though MS SQL has a database restoration option but 90% hosts do not allow you to automatically generate back up and download. You have to request them and then they generate the backup and upload to web server folder for us to download and restore.

Can anyone explain a solution to this daily problem..axaya

This is the DTS import/export wizard. Have you tried the "Copy objects and data Between SQL Server databases" option ?? This should copy all objects when doing the export. this copied all constraints, indexes...

Hope that helps :-)|||Any other way. DTS takes huge time and connection fails.|||How about detaching and attaching the DB, check out the link.

http://support.microsoft.com/kb/224071/

MS SQL 2000 full export (not backup)

Hi Everybody,

I am kind of new to MS SQL server databases. I like to take a full export at database level. When I use DTS wizard, it did allow me to take one table at a given time. I have 1000's of table in my database. Manually doing so is not possible. Should i call the 'bcp' command line utility 1000 times to collect the table data to 1000 different flatfiles or is there any provision to take export of all the 1000 tables in one single command/tool.

Many thanks.You should be able to use DTS to export the entire database at one time. Just right click on the database (in EM), click on all tasks and select export data.

Specify your source server/database, your target server/database and then select either copy tables and views or copy objects. Copy objects is more useful when you are trying to recreate the database structure. Copy data just moves over any tables.

Regards,

hmscott|||Thanks for your reply scott. my export is just a part of the work, because, i have to drop the entire instance and recreate it including 'master' database due to collation name change. So, i cannot export it to another database under this instance or to a different instance (due to space restriction). Hence my initial interest is to go with a flat (text) file. Is it still possible ?

Thanks|||Or...

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF
GO|||Yurk. That's the second post I've seen today from someone needing to change the collation on their server.

I think for this you will sincerely want a second database server available as the target for your export. A developer license is cheap ($50?). Put it on a handy box and then away you go.

Short of that, you might be able to work out some combination of backup, re-install, restore (to different DB name), script off db, change collation specified in script, run script to create new DB and then use DTS to copy the data from the restored DB to the new DB.

Am I making sense? It IS getting close to 5:00 here. At 5:00, the value of my responses declines by at least 75%.

Regards,

hmscott|||Or just do what Brett (BCP King) Kaiser suggests.

Brett, is it any coincidence that your initials BK might stand for 'BCP King'?

regards,

hmscott|||Got a chuckle out of that one...

You can point that code at ANY database...

I think you'll need a DELETE in there for table a...I originally had it as a temp table (still want to delete a temp, so that's no excuse...just poor coding habit)

Oh and the table is to log the events for each bcp...

Let us know if it works out...just make sure you got plent of drive space...

I wouldn't do this over a network either...

just copy the files when they're done...|||Many Thanks for both of you (Scott and Brett).
I am browsing Brett's script. I am sure that, it will be a big help to get the export work done successfully. Hope I will get my next 15 instances converted from Latin1_General_CI_AS to SQL_Latin1_General_CI_AS ASAP. Thank you very much.|||15 Instances! On 1 box?

Wow

:eek: