Exporting Database for Importing on Another Server

This may be a simple question with an obvious answer, but I'm fairly new to databases and I can't seem to find the right procedure.

I created a database on my machine. If I want to copy the information from this database (columns, tables, and entries specifically) from my machine to another SQL server on a different machine, what is the best way to do this without manually re-entering all of the information?

Is there a simple export/import of a database for transferring it from one server to another?

Thanks,

~Josh Graber

Using Backup and Restore is often considered the 'best' way to move a database from one server to another.

Check in Books Online about using Restore with the [with MOVE] option.

|||

there are many method which u can use for this purpose....

(a) Backup/Restore

(b) Detach /attach

(c) copy database wizard

u can read about these method in BOL ...

Madhu

Exporting data from SQL Server to Excel from a Stored Procedure

I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.

I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.

Any ideas

ThanksI'd use the Data | Get External Data | New Database Query menuitem in Excel. When you get to MS-Query, don't select any tables. Use the SQL button and type in the EXECUTE command for the stored procedure. Save this query and then the users can simply execute it!

-PatP

Exporting Data

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?I think that DTS (http://msdn.microsoft.com/data/technologyinfo/sqlserver/SQLKeyTechs/default.aspx?pull=/library/en-us/dnsql2k/html/dts_overview.asp) would be a better choice.

-PatP

Export XML data - stored procedure

I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...
Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better...

e.g Sample XML file required...
<Person>
<Name>Raymond</Name>
<NickName>The Legend</NickName>
</Person>
<Person>
<Name>Peter</Name>
<NickName>The King</NickName>
</Person>

sp_configure'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure'Web Assistant Procedures', 1;
GO
RECONFIGURE
GO
sp_makewebtask @.outputfile='C:\MyExportFile.xml',
@.query='SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS',
@.templatefile='C:\Template.tpl'

Try these two links for code samples including generating the XML file from a database table, this is assuming you are using SQL Server 2000. If you are using SQL Server 2005 all the code you need is in the BOL (books online) because XML is native to SQL Server 2005. Hope this helps.

http://forums.asp.net/1026295/ShowPost.aspx

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

export to XML

I'm on a shared server with no DTS capabilities. Is it possible to use a
stored procedure to export data from an SQL recordset into an XML file on a
remote server? i.e. I would need to create http://mydomain/myfile.xml
Anyone have sample code?
thanksYour web site appears to be down. Have a look at for xml explicit. This can
be used in a stored procedure to do exactly what you are looking for.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shank" <shank@.tampabay.rr.com> wrote in message
news:uupZSE$DGHA.3000@.TK2MSFTNGP14.phx.gbl...
> I'm on a shared server with no DTS capabilities. Is it possible to use a
> stored procedure to export data from an SQL recordset into an XML file on
> a remote server? i.e. I would need to create http://mydomain/myfile.xml
> Anyone have sample code?
> thanks
>|||I have the below code that generates XML.
How do I get this into an XML file on a remote server?
Example: http://www.mydomain.com/mytestfile.xml (ficticious domain name)
Does this need to be done through ASP?
Or can I go direct from SQL to an XML file? (preferred)
thanks
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[xTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xTest]
GO
CREATE TABLE [dbo].[xTest] (
[SkuNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Descrip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku1', 'Descrip1',
'Type1')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku2', 'Descrip2',
'Type2')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku3', 'Descrip3',
'Type3')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku4', 'Descrip4',
'Type4')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku5', 'Descrip5',
'Type5')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku6', 'Descrip6',
'Type6')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku7', 'Descrip7',
'Type7')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku8', 'Descrip8',
'Type8')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku9', 'Descrip9',
'Type9')
SELECT 1 as Tag, NULL as Parent,
SkuNo AS [Stock!1!SkuNo],
Descrip AS [Stock!1!Descrip],
Type AS [Stock!1!Type]
FROM xTest
FOR XML EXPLICIT|||Hi
Look at http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp and the
sp_makewebtask solution although this is really only suitable for smaller
files.
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:uupZSE$DGHA.3000@.TK2MSFTNGP14.phx.gbl...
> I'm on a shared server with no DTS capabilities. Is it possible to use a
> stored procedure to export data from an SQL recordset into an XML file on
> a remote server? i.e. I would need to create http://mydomain/myfile.xml
> Anyone have sample code?
> thanks
>

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - or char(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - orchar(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to Excel using DTS

I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)

what is the best way to do this? DTS, BCP

thanks
FOCELBrett will be along shortly to offer up a much cleaner solution using BCP and two lines of code (one of which will call a stored procedure to mix up a margarita). In the meantime, may I humbly offer up:

You can create a package with a global variable indicating which SP to run. Have three transformations defined in the package and determine which transformation to execute in a VBScript (using the global variable).

I'm not sure what will happen to the other transformations (the two that are not executed). That may require more testing...

You can look for more hints at http://www.sqldts.com.

Regards,

hmscott

Export to Excel - Change the Sheet Name

I need to change the sheetname with the field value retured by the stored
procedure.
Say the stored procedure returns 10 rows, when exporting the report , the
report should contain 10 sheets with the sheetname(field 1 value).
How can I do this.
Balaji
--
Message posted via http://www.sqlmonster.comSheet renaming isn't supported in RS SP1. I've seen a couple of threads
where people have created document maps to work around this 'limitation'.
Do a search in the newsgroup for 'document maps'.
--
Adrian M.
MCP
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee446645890e4ae9a42b10c0d60a07e0@.SQLMonster.com...
>I need to change the sheetname with the field value retured by the stored
> procedure.
> Say the stored procedure returns 10 rows, when exporting the report , the
> report should contain 10 sheets with the sheetname(field 1 value).
> How can I do this.
> Balaji
> --
> Message posted via http://www.sqlmonster.com

Export to Excel

Hi there I know its possible to export data from a gridview to excel. I'm looking to export data directly from a stored procedure at the click of a button.

Somebody suggested using the following:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from table-name
When I tried executing the above lines of codes I got the following error message:
"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."
If anyone has any idea whats wrong ... plssssssssssssssssssss ... let me know ...
Thanks in advance.Hi - I use vbscript client side for this:
<BUTTON id="exportbutton" onclick="BLOCKED SCRIPTexport_onclick()" name="exportbutton" type="button" value="Button">Export</BUTTON>
<script language="vbscript">
Sub export_onclick()
Dim sHTML, oExcel, oBook
sHTML = document.all.items("mydatagrid").outerhtml
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
oBook.HTMLProject.RefreshDocument
oExcel.Visible = true
oExcel.UserControl = true
End Sub
</script>

Hope that helps!
Cheers, Mark|||Hi Matait
Thanks for your reply.
But wont there be a problem if scripting is disabled on the browser ?
|||

You cannot use OPENROWSET because Excel is not a RDBMS(relational database management system) and yes you can run queries in Excel version XP/2003 but the queries are Access SQL not ANSI SQL because SQL Server rejects most. That said try the thread below for code sample and links for Asp.net 1.1 convert it for Asp.net 2.0. Hope this helps.

http://forums.asp.net/921410/ShowPost.aspx

|||

Hi Caddre

Thanks for your reply. That should work for me :)
Cheers

Export table to MAccess from SQL Store Procedure

Hi people.

I need some help to export a table to Microsoft Access using a Store Procedure.

My Store Procedure will pupolate a temporary table with some information. After that I want that the store procedure export this table to Access.

Is there any script or command that can do it? And how can i use it into a Store Procedure?

Please, if possible give a example.

thanks in advance
:confused:DTS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dts_overview.asp) is almost certainly what you want.

-PatP|||How can i do it?? can you give a example??|||Follow the link I provided. There are several hundered examples provided there!

-PatP

Export Stored procedure to Access Error

When trying to export the output of a stored procedure to an Access database
,
I get the error
"Syntax Error in field definition"
at the first step of the process (output table creation)
The procedure runs fine in QA, and has the following general structure:
...
select * into #MyTempTable
from ...
...
select * ] this is what I need
from ... ] to be the output
join #MyTempTable on ... ] of the procedure
... ]
Could the use of a temporary table be the cause of the problem?Try using SET NOCOUNT ON at the beginning of the procedure.
ML
http://milambda.blogspot.com/|||I did and still get the same error
"ML" wrote:

> Try using SET NOCOUNT ON at the beginning of the procedure.
>

Export Stored Procedure SQL Script

We usually keep an "installation " table , when we save out stored
procedures they are ordered by the stated execution order. (customised
script)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
> I just use the 'Generate SQL Script' from EM to export stored procedure.
> How do I export the Stored Procedure SQL Script in to a file so in an
order
> so that they can be created successfully ?
> I got an error when execute the scripts:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp1'. The stored procedure will still be
> created.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp2'. The stored procedure will still be
> created.
> ...............
> ...............
> How do I get around that ?
>I just use the 'Generate SQL Script' from EM to export stored procedure.
How do I export the Stored Procedure SQL Script in to a file so in an order
so that they can be created successfully ?
I got an error when execute the scripts:
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp1'. The stored procedure will still be
created.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp2'. The stored procedure will still be
created.
...............
...............
How do I get around that ?|||We usually keep an "installation " table , when we save out stored
procedures they are ordered by the stated execution order. (customised
script)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
> I just use the 'Generate SQL Script' from EM to export stored procedure.
> How do I export the Stored Procedure SQL Script in to a file so in an
order
> so that they can be created successfully ?
> I got an error when execute the scripts:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp1'. The stored procedure will still be
> created.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp2'. The stored procedure will still be
> created.
> ...............
> ...............
> How do I get around that ?
>

Export Stored Procedure SQL Script

I just use the 'Generate SQL Script' from EM to export stored procedure.
How do I export the Stored Procedure SQL Script in to a file so in an order
so that they can be created successfully ?
I got an error when execute the scripts:
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp1'. The stored procedure will still be
created.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp2'. The stored procedure will still be
created.
...............
...............
How do I get around that ?We usually keep an "installation " table , when we save out stored
procedures they are ordered by the stated execution order. (customised
script)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
> I just use the 'Generate SQL Script' from EM to export stored procedure.
> How do I export the Stored Procedure SQL Script in to a file so in an
order
> so that they can be created successfully ?
> I got an error when execute the scripts:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp1'. The stored procedure will still be
> created.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp2'. The stored procedure will still be
> created.
> ...............
> ...............
> How do I get around that ?
>|||Thanks.
I reordered some of the sp based on the depenancies.
However, I still got error in one of the sp in which a recursion occurrs:
CREATE PROCEDURE dbo.recur_sp
@.parent_id int
as
........
if exists(select 1 from category where parent_id=@.id)
exec recur_sp @.id
select @.row = @.row + 1
end
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'recur_sp'. The stored procedure will still be
created.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:ELCdnem5EM1sDgLZRVnyvQ@.bt.com...
> We usually keep an "installation " table , when we save out stored
> procedures they are ordered by the stated execution order. (customised
> script)
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
>> I just use the 'Generate SQL Script' from EM to export stored procedure.
>> How do I export the Stored Procedure SQL Script in to a file so in an
> order
>> so that they can be created successfully ?
>> I got an error when execute the scripts:
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp1'. The stored procedure will still be
>> created.
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp2'. The stored procedure will still be
>> created.
>> ...............
>> ...............
>> How do I get around that ?
>>
>|||That "error" is normal for recursive procs. It's just a warning and can be
safely ignored.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eSD7sVYmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Thanks.
I reordered some of the sp based on the depenancies.
However, I still got error in one of the sp in which a recursion occurrs:
CREATE PROCEDURE dbo.recur_sp
@.parent_id int
as
........
if exists(select 1 from category where parent_id=@.id)
exec recur_sp @.id
select @.row = @.row + 1
end
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'recur_sp'. The stored procedure will still be
created.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:ELCdnem5EM1sDgLZRVnyvQ@.bt.com...
> We usually keep an "installation " table , when we save out stored
> procedures they are ordered by the stated execution order. (customised
> script)
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
>> I just use the 'Generate SQL Script' from EM to export stored procedure.
>> How do I export the Stored Procedure SQL Script in to a file so in an
> order
>> so that they can be created successfully ?
>> I got an error when execute the scripts:
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp1'. The stored procedure will still be
>> created.
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp2'. The stored procedure will still be
>> created.
>> ...............
>> ...............
>> How do I get around that ?
>>
>