Hello,
In MSSQL2000, I am able to transfer files from one db to other.
using addTask -> export data.
In MSSQL2005, I am not able to transfer files from one db to other.
Please advise.
Thanking You.
Regards,
Edward
Hi all
How to transfer oracle table to SQL 2005.
Thanks
Hi,
In SQL Server 2005 Express I currently have 2 databases which I€™m working on, my problem is I want to transfer a table from one of the databases to the other but don't know how to go about achieving this? I want the table structure and all the info held within the table transferred across. Any ideas are welcome.
I am just starting out with MSSQL, but have previous experience with MySQL and PGSQL. What I'm trying to do is do a nightly dump of data from a proprietary DB into MSSQL. Access to the prop DB is through an ODBC driver.
I already have a DTS script that will dump a specific table, but every time it runs it just appends the data to the end of the table.
What I am looking for is a way to download a list of tables, upload them into the MSSQL DB, and if something failed, rollback to the state before the data transfer and somehow alert of the fact.
Any help will be greatly appreciated!
I have been working with a MySql 2005 database and a MySql database. I am using a MySql database in my C# application, and the MySql database is on-line. My application is generating queries and with those pieces of information I am updating the on-line data base.
It is working for €œtext€? information but I need to add some images, too. I see two possibilities:
1. I can either use an binary or image field in my MySql database and send this information on-line (but I don€™t know if the fields are compatible MsSql - MySql)
2. Or I can produce a folder with the images and upload that folder, in this case I have the path information in my databases.
Can you give me other possible suggestions about how to solve this problem?
DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?
I have tried several approaches.
A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
C: I have also tried opening the Database, and back up file through Server Management Studio. without success.
D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.
I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.
Mark Roach
I am new to SQL Server.
I have a fox pro database that is part of a vendor's product. I want to link some of the individual tables in the foxpro database in my SQL database.
Can someone point me to a tutorial or example on how to do this?
Thanks!
Thanks for the replies,
As per the querys:
------------------------
1) I am trying to use the Database of my host (hosting server of datapacket). They have a support for MS-SQL-2000. Therefore surely the server is there. After my CPanel configuration i got the Database Server IP (67.122.X.X), Port - 1433, SQL Server user and pasword.
2) In my local machine SQL Server 2000 is installed. Where the databse and DNS is configured.
Now, from this local machine how can i transfer the data to the host machine (remote SQL Server).
Simpliy copying Files *.MDG and *.LDf files it is not working. And i don't have access to the remote SQL-SERVER as it is in the host machine of datapacket. I can only configure it throuch there Control Panel and throuch FTP can copy the required files in there machine. I have a user in that remote MS-Sql server that all i have.
As per my understanding after the control panel configuration the database and the DNS is created in the remote Sql-Server of datapacket. Now all i have to do is transfer the data & tables. HOW TO DO THAT?????
I have tried using Export but it fails to connet the remote database.
Thanks in Advance
Dipya
How can I query (using TSQL) the data space used by a table in SQL Server 2005?
This is possible using SQL Serve Management studio. I can right click on table name and check the proeprties. But I want to write a TSQL script to check disk space used by all the individual tables in the database. How can I do that?
Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Thanks
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedHi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
Hi!
What is the best way to migrate MSSQL 2005 Express -> MSSQL 2005 Srv?
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?
View 3 Replies View RelatedWhat is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
Hi,
for some reason, when I've installed visual studio 2005, I didn't install also SQL server.
Than I've updated VS with Service Pack 1 (thank god, I was trying unsucesfully for about 2 weeks :/ ).
Than I want to install MSSQL 2005 and an error came up:
Message displayed to user
SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
SQL Server Setup has detected that the following required component is not installed: Microsoft Windows Installer 3.1. To proceed, download and install the Windows Installer 3.1 Redistributable from http://go.microsoft.com/fwlink/?LinkId=50380 , and then run SQL Server Setup again.
1. The http link leads to main page of MS Download Centre.
2. I have Installer 3.1. (version of file c:windowssystem32msiexec.exe said so)
3. I really do not have any beta version of any mentioned SW. I never had.
So I think the issue could be, that installer is checking for not updated version of Visual Studio.
(I was searching through net/msdn and seems no one had this problem (maybe wrong keywords, though) ).
I'm running setup.exe from CD1.
Uninstalling of VS Service Pack 1 is not an option. I really don't want to spent another week by installing it.
Please tell that there is some another way :) .
Thanks a lot.
Here are some logs:
SQLSetup0001_COMP_Datastore.xml
<Datastore>
<Scope Type="SetupBootstrapOptionsScope" Id="2336">
<Property Id="QuietSwitchPresent">0</Property>
<Property Id="LogSwitchPresent">0</Property>
<Property Id="ErrorReporting">0</Property>
<Property Id="DeleteManifestFileMode">1</Property>
<Property Id="MultiInstanceEnabled">0</Property>
<Property Id="ComponentUpdateOnly">0</Property>
<Property Id="ReducedUI">0</Property>
<Property Id="PatchedSetup">0</Property>
<Property Id="LocalSetup">0</Property>
<Property Id="InitiateReboot">0</Property>
<Property Id="HostSetup">1</Property>
<Property Id="RemoteMode">0</Property>
<Property Id="Remote">0</Property>
<Property Id="LoosenedSccChecks">{ }</Property>
<Property Id="DisabledSccChecks">{ }</Property>
<Property Id="LocalRebootPending">1</Property>
<Property Id="CmdLine">********</Property>
<Property Id="InstallMediaPath">Y:</Property>
</Scope>
<Scope Type="SetupStateScope" Id="">
<Property Id="machineName">COMP</Property>
<Property Id="logDirectory">C:Program FilesMicrosoft SQL Server90Setup BootstrapLOG</Property>
<Property Id="logSummaryFilename">C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt</Property>
<Property Id="logSequenceNumber">1</Property>
<Property Id="primaryLogFiles">{ ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_COMP_Core.log"], ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt"] }</Property>
<Property Id="watsonFailedAction">PerformSCCAction2</Property>
<Property Id="watsonFailedActionErrorCode">87</Property>
<Property Id="watsonFailedFunction">sqls::PerformSCCAction::perform</Property>
<Property Id="watsonFailedFunctionErrorCode">87</Property>
<Property Id="watsonSourceFileAndLineNo">setupsqlsetupactions.cpp@1390</Property>
<Property Id="watsonModuleAndVersion">setup.exe@2005.90.1399.0</Property>
<Property Id="watsonMsi">None</Property>
<Property Id="watsonMsiAndVersion">None</Property>
<Property Id="watsonSourceFile">setupsqlsetupactions.cpp</Property>
</Scope>
</Datastore>
SQLSetup0001_COMP_Core.log
Microsoft SQL Server 2005 Setup beginning at Wed Mar 14 18:23:08 2007
Process ID : 2336
Y:setup.exe Version: 2005.90.1399.0
Running: LoadResourcesAction at: 2007/2/14 18:23:8
Complete: LoadResourcesAction at: 2007/2/14 18:23:8, returned true
Running: ParseBootstrapOptionsAction at: 2007/2/14 18:23:8
Loaded DLL:Y:xmlrw.dll Version:2.0.3604.0
Complete: ParseBootstrapOptionsAction at: 2007/2/14 18:23:8, returned true
Running: ValidateWinNTAction at: 2007/2/14 18:23:8
Complete: ValidateWinNTAction at: 2007/2/14 18:23:8, returned true
Running: ValidateMinOSAction at: 2007/2/14 18:23:8
Complete: ValidateMinOSAction at: 2007/2/14 18:23:8, returned true
Running: PerformSCCAction at: 2007/2/14 18:23:8
Complete: PerformSCCAction at: 2007/2/14 18:23:8, returned true
Running: ActivateLoggingAction at: 2007/2/14 18:23:8
Complete: ActivateLoggingAction at: 2007/2/14 18:23:8, returned true
Delay load of action "DetectPatchedBootstrapAction" returned nothing. No action will occur as a result.
Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchPatchedBootstrap__2336" returned false.
Running: PerformSCCAction2 at: 2007/2/14 18:23:8
Error: Action "PerformSCCAction2" threw an exception during execution.
Return Code: 87
Message displayed to user
SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
SQL Server Setup has detected that the following required component is not installed: Microsoft Windows Installer 3.1. To proceed, download and install the Windows Installer 3.1 Redistributable from http://go.microsoft.com/fwlink/?LinkId=50380 , and then run SQL Server Setup again.
Class not registered.
Running: UploadDrWatsonLogAction at: 2007/2/14 18:23:9
Message pump returning: 87
Hello, im shure this must have been up before and i apologize for that. But i wonder if there is a way to convert the SQL server express databases to MSSQL 2005 databses?
View 3 Replies View RelatedHow can i transfer table records in a SQL 2005 express db (housed locally) to another SQL 2005 db (live)? I'm using the stripped down, free version Management Studio Express and can't find an "import table" function. Any information or links to information is appreciated.
regards,
dj
Dear,
i have a huge msaccess 2003 DB i want to transfer it to SQL 2005 , i found tool to imigrant oracle (and i used it) but i can't find solution to imugrant msaccess ??????
thanks
avner
I need to transfer data from one table to another.I will be using the SQL Query Analyzer to do this.This is not a simple transfer of data to the same structured tables.These tables are completely different, for the most part.For instance, I will be selecting certain fields of one table.....SELECT fldOne, fldTwo FROM someTableI need to take this information, one row at a time and input it into a different type table.So, something like this to insert into the other table...INSERT INTO otherTable( fld1, fld2) VALUES( value1, value2 )I've looked around for a sample to achieve this, but may have overlooked it?Anyone have a link to show this or a quick sample?Thanks all,Zath
I created a schema, Admin. I have to transfer a table from the dbo schema to the admin schema. I keep getting an error that I do not have permission or the table does not exist.
Simply looking for confirmation here - is my syntax correct?
ALTER SCHEMA Admin TRANSFER MyShop.Addresses;
(MyShop is the Database, Addresses is the table)
NOTE: When I created the schema, I did not create an inner table. The syntax for that was simply CREATE SCHEMA Admin;
My problem is very simple and that is that I'm trying to copy some tables between databases, but these tables are in different schemas.
let's say I have
dbo.tableA
sch1.tableA
sch2.tableA
sch3.tableA
And I just want to copy let's say sch1.tableA to a Different DB.
If I use Transfer SQL Server Object task and select the table and save the package and try to open the task again, all the tables with name TableA will be selected!! it seems like although it does show the schema ( when I am selecting the table manually ) but it doe snot store the schema detail in the tablelist collection property of the task.
Would please recommend any other way to achieve this.
Many Thanks in advance
I've got a DB2 database that contains a lot of tables. I need to extract the data from some of them and put them in a SQL Server database. The number of tables needed may change, so I need an easy way of controlling this.
So, I created a lookup table in the target SQL Server DB that lists the tables I want with any selection criteria needed.
I have a For Each loop that goes through every row in the table. using the current row, it deletes the contents of the destination table (done and working), and then tries to load the source into it.The destination OLE DB uses a variable for the table name to insert into.
The problem I've got is whilst I can set up the OLE DB Source to use a SQL command as variable, I have to do it in the advanced editor or in the properties, as the normal editor gives an OLE DB Error. This is probably due to the fact that the variable has nothing in it at this point. I've turned EvaluateExternalMetaData off to avoid any design time errors, but when attempting to run I still get :
Code Snippet
[DTS.Pipeline] Error: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.
Which I understand, as there are none until runtime. How do I get around this problem of not having any columns to map until I know which table I am processing?
Thx
Rob
Hello,
I am trying to transfer a database from one server to another using the Import Export wizard in SSIS and I am consistantly getting this error on 2 different tables so far.
- Execute the transfer with the TransferProvider. (Error)
Messages
* ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of
"output column "ErrorCode" (79)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
This error message is beyond cryptic and when I click on the link it sends me to a web page the just tells me that there is no information available for my current issue. I am transfering the tables to an empty database so I do not understand why I am receiving this error. I have to say that I am not impressed with SSIS at all. I know alot of developers think it's the best thing since sliced bread, however either I am doing something wrong or Microsoft needs to come out with a service pack that fixes these bugs...
Any help would be appreciated...
Thanks,
David
i got an XML source and 1 OLE DB destination
i got an xml file
<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France
</Address>
<Age>
27
</Age>
</Student>
</Node>
and a XML schema file
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
i create the OLED destination on a table that contains
1. Name varchar (20)
2. Address varchar(50)
3. Age bigInt null
but i got the following errors
Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Inside the OLEDB destination,
near the table selection, I click new
it has this script :
CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)
Hi,
I want to write a Procedure to transfer a column from Table1 to Table2.
I mean Table1.Last Trade to transfer to Table2.Prev Close.
INPUT:
Table1
Symbol Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
1127
770.2
113.9
903.2
1844.2
391
263.1
215.85
672.5
634.55
Table2
Symbol Prev Close
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
Null
Null
Null
Null
Null
Null
Null
Null
Null
Null
OUTPUT:
Symbol Prev Close
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
1127
770.2
113.9
903.2
1844.2
391
263.1
215.85
672.5
634.55
I am new to
View 4 Replies View RelatedI am trying to copy a database from sql2000 to sql2005 but it is giving me the following error:
To connect to this server you must use SQL Server Management studio or SQL server management objects (SMO)
this can be done or servers are not compatible?
I got 2 SQL servers. One is legacy SQL 6.5 and the other one is SQL 2005. They are installed in different machine. How can I copy or migrate the old 6.5 version databases to SQL 2005 server?
Someone said I should upgrade the 6.5 to version 2000 first. Is it the only method? I didn't have SQL 2000 install cdrom and license. How can I do that?
Please help.
View 3 Replies View Related