sql server import /export access in vc++ code
-
Hello All, I have requirement in a vc++ application, wherein i need to select a few tables from the database on remote sql server(in the same LAN ) and copy them to my local sql server database using VC++ with no direct user interaction with 'MS sql server Enterprise Manager'. Ofcourse GUI is provided for user to select the tables in the remote sql server database, that i can do in vc++. Please help me solve this problem. Thanks & Regards, Prax:)
-
Hello All, I have requirement in a vc++ application, wherein i need to select a few tables from the database on remote sql server(in the same LAN ) and copy them to my local sql server database using VC++ with no direct user interaction with 'MS sql server Enterprise Manager'. Ofcourse GUI is provided for user to select the tables in the remote sql server database, that i can do in vc++. Please help me solve this problem. Thanks & Regards, Prax:)
u do not need any user assistance while u r copying data from table to another, just create two databases variables and open both ODBCs then connect to both sender record set and receiver record set, start copying from sender to receiver, easy.... u can use your own overloaded RecordSet classes or simply and more than enough MFC RecordSet class...
-
u do not need any user assistance while u r copying data from table to another, just create two databases variables and open both ODBCs then connect to both sender record set and receiver record set, start copying from sender to receiver, easy.... u can use your own overloaded RecordSet classes or simply and more than enough MFC RecordSet class...
Hi Friend, From what i understood after reading ur mail, i wrote the following code for my application.................... //NOTE: 'CCustomer' is CRecordset derived class of 'Customer' table in SQL Server; 'GetConnectionStr' is my function which returns the CString on the Connection String. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> CDatabase m_dbMaster; CDatabase m_dbField; m_dbMaster.OpenEx(_T(GetConnectionStr(MASTERDSN)),CDatabase::openReadOnly) m_dbField.OpenEx(_T(GetConnectionStr(FIELDDSN)),CDatabase::noOdbcDialog) m_dbField.BeginTrans(); CCustomer m_recFieldCustomer(&m_dbField); CCustomer m_recMasterCustomer(&m_dbMaster); m_recMasterCustomer.Open(CRecordset::snapshot,_T("SELECT * FROM Customer"),CRecordset::readOnly); m_recFieldCustomer.Open(CRecordset::dynamic,"SELECT * FROM Customer",CRecordset::none); m_recFieldCustomer=m_recMasterCustomer;//Copying the Master recordset to Field recordset m_recMasterCustomer.Close(); m_recFieldCustomer.Close(); m_dbField.CommitTrans();//COMMIT TRANSACTION FOR THE FIELD DATABASE m_dbMaster.Close(); m_dbField.Close(); >>>>>>>>>>>>>>>>>>>>>> Result: When i try to Compile the above code it gives error : " 'CCustomer' : 'operator=' function is unavailable" If you think i need to change my code or u have any other issues , please let me know. i would clarify them. waiting for ur reply.......... Regards, Prax.
-
Hi Friend, From what i understood after reading ur mail, i wrote the following code for my application.................... //NOTE: 'CCustomer' is CRecordset derived class of 'Customer' table in SQL Server; 'GetConnectionStr' is my function which returns the CString on the Connection String. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> CDatabase m_dbMaster; CDatabase m_dbField; m_dbMaster.OpenEx(_T(GetConnectionStr(MASTERDSN)),CDatabase::openReadOnly) m_dbField.OpenEx(_T(GetConnectionStr(FIELDDSN)),CDatabase::noOdbcDialog) m_dbField.BeginTrans(); CCustomer m_recFieldCustomer(&m_dbField); CCustomer m_recMasterCustomer(&m_dbMaster); m_recMasterCustomer.Open(CRecordset::snapshot,_T("SELECT * FROM Customer"),CRecordset::readOnly); m_recFieldCustomer.Open(CRecordset::dynamic,"SELECT * FROM Customer",CRecordset::none); m_recFieldCustomer=m_recMasterCustomer;//Copying the Master recordset to Field recordset m_recMasterCustomer.Close(); m_recFieldCustomer.Close(); m_dbField.CommitTrans();//COMMIT TRANSACTION FOR THE FIELD DATABASE m_dbMaster.Close(); m_dbField.Close(); >>>>>>>>>>>>>>>>>>>>>> Result: When i try to Compile the above code it gives error : " 'CCustomer' : 'operator=' function is unavailable" If you think i need to change my code or u have any other issues , please let me know. i would clarify them. waiting for ur reply.......... Regards, Prax.
yes sure, it is obvious that operator = is not overloaded in CRecordSet, all what u have to do now, is to create one function where you assign the master recordset values to the receiver recordset for example your master table A has CustomerRef ,customerName etc... all what u have to do is ReceiverRecordSet.CustomerRef = MasterRecordset.CustomerRef make sure you are assigning the right fields... Voila... do not forget while (!masterRecordSet.ISEOF()) etc...
-
yes sure, it is obvious that operator = is not overloaded in CRecordSet, all what u have to do now, is to create one function where you assign the master recordset values to the receiver recordset for example your master table A has CustomerRef ,customerName etc... all what u have to do is ReceiverRecordSet.CustomerRef = MasterRecordset.CustomerRef make sure you are assigning the right fields... Voila... do not forget while (!masterRecordSet.ISEOF()) etc...
OK, i got what u meant to say... OK , is there any other way to move the entire table to the remote sql server in just one command .... like 1) using BCP utility & 2)using BULKINSERT... BUT here i am facing some issues .. 1) while using BCP utility , i am not able to handle any exceptions, and also i cannot include it between BEGINTRANSACTION --- COMMITTRANSACTION of CDatabase Object. it wil show up a console window when v use BCP. and if there is a network failure while tranfering data then the window wil remain constant , the user has to cancel it , then only next line of code will be executed. 2) when i am using BULKINSERT , which is meant to move DATA from a 'file' TO 'SQL SERVER', but i cannot use BULKINSERT to move data from 'SQL SERVER' to 'file'. SO that is half done with BULK INSERT. SO IF u have any idea for how to move data between two servers, similar to Import/Export wizard in SQL SERVER Enterprise manager , please help. Regards, Prax. -so radih9999, whats ur real name, what u do & where u dwell?
-
OK, i got what u meant to say... OK , is there any other way to move the entire table to the remote sql server in just one command .... like 1) using BCP utility & 2)using BULKINSERT... BUT here i am facing some issues .. 1) while using BCP utility , i am not able to handle any exceptions, and also i cannot include it between BEGINTRANSACTION --- COMMITTRANSACTION of CDatabase Object. it wil show up a console window when v use BCP. and if there is a network failure while tranfering data then the window wil remain constant , the user has to cancel it , then only next line of code will be executed. 2) when i am using BULKINSERT , which is meant to move DATA from a 'file' TO 'SQL SERVER', but i cannot use BULKINSERT to move data from 'SQL SERVER' to 'file'. SO that is half done with BULK INSERT. SO IF u have any idea for how to move data between two servers, similar to Import/Export wizard in SQL SERVER Enterprise manager , please help. Regards, Prax. -so radih9999, whats ur real name, what u do & where u dwell?
in CDatabase class there is one ExecuteSQL(CString) command... In CString u can say "Insert into targetTable (F1,F2,etc..) Select Master.F1,Master.F2 from Master" soooooooo simple... My Name is Rabih and I am lebanese with CCE grade and 4 years of C++,MFC,GDI+ etc... experience
-
Hello All, I have requirement in a vc++ application, wherein i need to select a few tables from the database on remote sql server(in the same LAN ) and copy them to my local sql server database using VC++ with no direct user interaction with 'MS sql server Enterprise Manager'. Ofcourse GUI is provided for user to select the tables in the remote sql server database, that i can do in vc++. Please help me solve this problem. Thanks & Regards, Prax:)
I did something like that before. What I did was create a DTS package in my SQLServer database and saved it. Then I called ShellExecute() on the Dtsrun.exe and sent it in the appropriate string for servername, password,etc. Here's an example (items in bold will have to be replaced by your appropriate values):
ShellExecute(NULL,"open","dtsrun.exe","/S SERVERNAME /U USERNAME /P PASSWORD /N PACKAGENAME",NULL,SW_HIDE);So I think I set up the export in SQLServer, then opted to save it as a package, then BAM! I think that was a lot easier. Plus, the server is doing the work for you. Who are all these people and what are they doing in my house?...Me in 30 years, inside a grocery store bdiamond :zzz:
-
I did something like that before. What I did was create a DTS package in my SQLServer database and saved it. Then I called ShellExecute() on the Dtsrun.exe and sent it in the appropriate string for servername, password,etc. Here's an example (items in bold will have to be replaced by your appropriate values):
ShellExecute(NULL,"open","dtsrun.exe","/S SERVERNAME /U USERNAME /P PASSWORD /N PACKAGENAME",NULL,SW_HIDE);So I think I set up the export in SQLServer, then opted to save it as a package, then BAM! I think that was a lot easier. Plus, the server is doing the work for you. Who are all these people and what are they doing in my house?...Me in 30 years, inside a grocery store bdiamond :zzz:
Hi Guys, The 'shellexecute' one is good , but how to call it from vc++ application, please give me step by step details for the whole process. AND can v handle exceptions for 'shell execute' and can it be kept between BEGIN & COMMIT TRANSACTION of CDatabase Object? And Ribah, how can v refer to the table(F1,F2...) name on a remote server in "INSERT INTO (.F1,F2.......)......." please explain. hey guys please take ur valuable time and help me one last time. if i can do this , my biggest task in my project wil be done. Waiting for ur reply.......... Regards Prax