All I want to do is create an XML document from sql server 2000 and have it written to a shared drive so I can read it from our webserver!
I know to produce on the fly such as SELECT * FROM VW_Web_Curr_Projs1 FOR XML AUTO, but how to write this result to an xml document
Or am I going about this the wrong way, any help please?|||
Take a look here
http://www.perfectxml.com/articles/XML/ExportSQLXML.asp
sqlHello,
I'm trying to export a database using the 'DTS Import/Export Wizard', I need to use the 'Microsoft OLE DB Provider for SQL Server', but it does not appear on the 'Data source' Drop-Down list of that Wizard.
What can I do to get that OLE DB Provider installed?
I'm running SQL Server 2000 Personal on a Windows XP Media Center PC and I have MSDAC 2.8 SP1 installed.
I also checked that I have the file sqloledb.dll on the 'C:\Program Files\Common Files\System\Ole db' directory.
I'll appreciate any help.
Thanks.
apply sql server sp4|||I already have SQL Server SP4.
I also tried to unregister and re-register sqloledb.dll and oledb32.dll
|||Anybody? I'm having the exact same problem. how do you "re-install" and register the standard data source items like "Microsoft OLE DB Provider for SQL Server"?I'm running XP Media Center SP2 with
SQL Enterprise Manager 2000 SP4.
I've re-registered the DLLs, etc. Where can the data source files physically be found?
I don't have much use for "Driver da Microsoft para arquivos textto (*txt,*csv)"!
I need my SQL Import/Export back! Help!
Thanks, all...
Hi
I have managed to add the DTS wizard as a tool and saved the routine. However, I do not know how to schedule my PC to automatically run this DTS procedure. I have tried to use scheduled tasks, but it does not seem to want to start.
Pls help.
It was absolutely amazing.
Just by typing in "SQL Server" and Excel in the search box on this page in the forums, I found these previous posting on the same topic...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1083746&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295895&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=970723&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304674&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=985930&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1083317&SiteID=1
There were more...
|||Hi Arnie,
Thanks for the suggestion, I discovered a a previous post you made. You suggested that you get scheduled tasks to run dtsrun.exe. I do not have DTSrun.exe and surely you would have to make the executable run only your specified saved DTS 'routines'. How would you do this?
Regards
George
|||Perhaps these will help guide you.
DTS - How to Run a DTS Package as a Scheduled Job
http://www.support.microsoft.com/?id=269074
DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111
Hi
Thank you for your help, but unfortunately the above solutions do not seem to work. Let me explain my situation more completely:
I have installed Microsoft SQL Server Express Edition with Advanced Services SP2 and the Toolkit. I have made the DTSWizard a tool in SQL management studio express I have transferred sample data into the database using the DTS tool. I have 'saved' the DTS transfer|||For command line details about executing a 'package', refer to Books Online, Topic: dtutil
Note that SSIS is a replacement for the DTS feature found in previous SQL
Server versions. SSIS packages can be created from scratch using SQL Server
Business Intelligence Development Studio.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<othellomy@.yahoo.comwrote in message
news:1172559059.922478.114550@.a75g2000cwd.googlegr oups.com...
Quote:
Originally Posted by
What are the ways to export/import data in/out of SQL Server 2005? I
used to use DTS for 2000 to do such thing and now I can't even find
the wizard in 2005!
>
Quote:
Originally Posted by
The import/export wizard is available from SQL Server Management Studio from
the Object Explorer. Right-click on the a database node and select Tasks
and then either Import or Export Data. The wizard will generate a SQL
Server Integration Services (SSIS) package that you can execute and/or save.
>
Note that SSIS is a replacement for the DTS feature found in previous SQL
Server versions. SSIS packages can be created from scratch using SQL Server
Business Intelligence Development Studio.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<othell...@.yahoo.comwrote in message
>
news:1172559059.922478.114550@.a75g2000cwd.googlegr oups.com...
>
>
>
Quote:
Originally Posted by
What are the ways to export/import data in/out of SQL Server 2005? I
used to use DTS for 2000 to do such thing and now I can't even find
the wizard in 2005!- Hide quoted text -
I’m using the Import\Export wizard to export the top 5 lines from a MS Sql table into a fixed format (“ragged”) file.But I want the first record to contain the column names of the exported fields so I selected the “Column names in the first data row” option of the “Choose a Destination” box. When I run the Package I get:
>>>
· Information 0x402090dc: Data Flow Task: The processing of file "C:\barkingdog\ExportWithheader.txt" has started (SQL Server Import and Export Wizard)
· Error 0xc0202095: Data Flow Task: Failed to write out column name for column "CustomerID".
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - ExportWithheader_txt" (49) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)
>>>
When I de-select the “Column names” option, the package works fine.Other than manually, how can I et the column names in output file?
TIA,
Barkingdog
That's very strange. I'm racking my brains as to what might be the problem but I can't think of anything!
Is there any other clue as to what is the problem?
-Jamie
|||The problem here is that when you include column names in the first data row, then you might need to modify the column width (for a ragged right file). For example, if you have a column CustomerID which is of type DT_STR(5), it will fail trying to write "CustomerID" in that space, which takes 10 characters. Use the Edit Transform button on configure Flat File Destination page of the Import Export Wizard to adjust the length specified for each fixed width column.
hope that helps.
|||Ranjetta,
I thought that that could be the case but I haven't had a chance to test it.
I also have to test if the "Import" wizard, with "column names in first row", has the same issue. My impression is no and perhaps the Export wizard is not as forgiving as the Import one. I will check.
Barkingdog
I am trying to export data from my local server to the hosting server. However I get errors when executing it:
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only column "ID".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination 7 - Batches" (497)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
Any idea?
I used to be able to Run Wizard in 2000 and transfer all the data to the server. It si the same database.. I had an option to recreate destination tables first...
What is the simplest way to do it in new 2005? I can't figure it out. Basicly I want my development SQL Database have in synch with production hosting SQl Server... It is not some rocket science... I should be able to run some kind of wizard... The one I run does not allow me to slect drop destination tables, this option is greyd out... Also it is on table level, not for all tables.
If I design SSIS package I would have to modify it everytime I add new tables to the laptop DB, so the Wizard slution like in SQL 2000 seems much better.
Since the wizard in 2000 worked for me I am considering to downgrade to SQL 2000, it is a shame I have to do it, because 2005 suposed to be better product, yet it does not meet my expectations in this matter.
I wonder how other users find SQL 2005 comparing SQL 2000 in practical use, not just in theory.
|||I'm very sorry, I can't understand your problem. From my point of view you can exactly do what you did with SQL 2000 with SQL 2005, too. Perhaps you have to explain it more detailed (step by step) what you did with 2000 and what you now do with 2005. I can drop and recreate tables without any problem. If you have identities, you had to (and still have to) click on "enable identity inserts"...
Or did you probably use the "copy database objects" wizzard? But I don't think so, that didn't fit to your description...
|||For some reason the option to recreate tables is greyed out in SQL 2005 wizard. I do not know why. Also this option is available on the table level. You have to click each table and change it. It used to be global setting for all tables in SQL 2000...|||RADIOGENERIS wrote:
You have to click each table and change it. It used to be global setting for all tables in SQL 2000...
Surely the ability to define different behaviour for each one of them is a good thing?
|||
Sure, but having an easy way to change the default behaviour would be something great, too?!
Perhaps something to suggest "officially"?
|||It was done this way in SQl 2000, why to remove this global option?
Also why is this option greyed out? Nobody can answer this question. What can be the reason?
Thanks,
|||
This option is designed to be used when the destination table is newly created. It would allow executing a saved package after the wizard creates the table, without modifications.
In your case (the table already exists) you would probably want to use the "Delete rows in destination table" option. We did not want to allow deleting tables that are not created by the wizard, since that may cause some unwished effects.
I agree that inability to set these options for more than one transfer at the time was a serious usability problem, and it has been addressed in SP1. The SP1 will allow bulk edits on all selected transfers and in addition to setting these options it would allow setting destination schemas to more than one destination table.
Thanks.
|||Bob,
Thank you for your explanation. I am so happy you guys have noticed that. I am waiting fo SP1, would you know when this will become available?
My suggestion is that the option to recreate table should be always available, it was done this way in SQL 2000 and it worked. Let the user decide what they want. They do not have to check this option... You could give them some warning once they check it. I guess deleting rows in a destination table would work for me for now.
My point is that SQL Server 2000 data export/import wizard was a very good tool, I am sure many people would agree with me. Why to remove some of it's great functionality? It was simple and sufficient and if you need more you would design a package. It creted views, stored procedures for you, all in one step. It was perfect for me, as I need to synchronize my laptop with hosting environment on regular basis. I develop new stored procedures, tables etc. at the end of day I would just run a wizard... Now the process is very time consuming.
Hopefully SP1 will adress those issues. Seems like SQL 2005 is a very good product, yet it needs few tweaks. Not everybody is SSIS expert :)
Also have you tried to open this package generated by Wizard in Visual Studio? It takes forever. I have 1.86 Ghz laptop with 1GB memory...
I think you guys are so ahead in technology that you may forget about simple use cases sometimes :)
Rafal
|||You have a good point Rafal. There's some very simple functionality in Enterprise Manager around scripting out objects that i am really missing.
If you want something to go into SP1 or the next version you should raise it at the Feedback Center: http://lab.msdn.microsoft.com/productfeedback/default.aspx
-Jamie
|||
Seems like I am not the first one who noticed those issues. I have found few suggestions submited already. Please take a look:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=9eb6c773-2dbb-4a27-b9d8-225d6ed4385a
|||I am not quite sure when SP1 will get out, my guess is sometime in the spring time.
You are welcome to post the feedback on the ways you wish the wizard to get improved. That will most certainly influence the work we choose to do on it. We do sometimes forget about simple use cases, but it is more often that we have to prioritize things we do to better utilize our resources. These priority lists are based on the feedback we hear from you guys. So again, do not hesitate to send it.
There is also one thing I wanted to point out about comparing DTS and SSIS import/export wizard. While they look similar, the used underlying components are a way different, and that had influence on some of the UI decisions we made.
Thanks.
Hi
I have a problem using SQL 2005 Export wizard.
I have 2 databases : DB1 and DB2
DB1 has X tables and Y views
DB2 has W tables and Z views
I want to export only few selected views from DB1 to DB2.
In Import and Export wizard, I select these views and execute the export-package. Completed Successfully.
And finally the problem:
All views that i exported from DB1, are not placed in DB2 as views, but as TABLES!
Why?
Waiting for your quick answer,
Martin
This is by design.
It may happen that some of the tables on which the view depends are not imported at all, which can cause the view definition to fail.
Thanks,
Kuntal
Hi
I have a problem using SQL 2005 Export wizard.
I have 2 databases : DB1 and DB2
DB1 has X tables and Y views
DB2 has W tables and Z views
I want to export only few selected views from DB1 to DB2.
In Import and Export wizard, I select these views and execute the export-package. Completed Successfully.
And finally the problem:
All views that i exported from DB1, are not placed in DB2 as views, but as TABLES!
Why?
Waiting for your quick answer,
Martin
This is by design.
It may happen that some of the tables on which the view depends are not imported at all, which can cause the view definition to fail.
Thanks,
Kuntal
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query
|||i am using order by in my query . Data is correct in Access but order is not correct.|||Dear All,
Thankyou for your replies.
I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.
Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query
|||i am using order by in my query . Data is correct in Access but order is not correct.|||Dear All,
Thankyou for your replies.
I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.
Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query
|||i am using order by in my query . Data is correct in Access but order is not correct.|||Dear All,
Thankyou for your replies.
I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.
Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.
Please give me the solutions.
Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.
This is generally true of all Relational Databases.
Did you try placing an index on the Access table?
|||True, I believe this more a call from Access 2003 settings to see how data is displayed.
Also check the code in Access about getting this data display.
|||Dear All,
I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.
Thank you all of you for your replies.
However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.
Please give me the solutions.
Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.
This is generally true of all Relational Databases.
Did you try placing an index on the Access table?
|||True, I believe this more a call from Access 2003 settings to see how data is displayed.
Also check the code in Access about getting this data display.
|||Dear All,
I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.
Thank you all of you for your replies.
However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.
Please give me the solutions.
Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.
This is generally true of all Relational Databases.
Did you try placing an index on the Access table?
|||True, I believe this more a call from Access 2003 settings to see how data is displayed.
Also check the code in Access about getting this data display.
|||Dear All,
I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.
Thank you all of you for your replies.
However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.
2. Script up a file by (right) clicking on the appropiate database or
the individual procedure and Select "Generate SQL Script"
HTH, Jens Suessmeyer.|||Jens, Thanks. I didn't see that option for some reason.
This has made recovery much simpler.
lq
You keep your code under version control, and install them from them
from that source. What's in the database, should be regarded as binaries
in my opinion.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
I would love to be in the position to do it that way, but in this case,
it's an inherited mess with stored procedures supporting literally a
dozen different upgrade versions and a couple of platforms as well!|||Erland,
I would love to be in the position to do it that way, but in this case,
it's an inherited mess with stored procedures supporting literally a
dozen different upgrade versions and a couple of platforms as well!
