• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2725
  • Last Modified:

SQL and ODBC in C#

Currently, I have tables in one database that uses a SqlConnection, and tables in another database that uses OdbcConnection.  I need to write some sort of code for a C# program that will allow data from the tables in the SqlConnection to be copied to tables in the OdbcConnection.  At the moment, I've called the SqlConnection conn, and the OdbcConnection conn2. I've tried to write some SQL command:

string select_op = "CREATE TRIGGER op_trig " +
      "ON conn.op_temp " +
      "FOR INSERT " +
      "AS " +
      "INSERT INTO conn2.operation (code, name, etc etc) " +
      "SELECT code, name, etc etc" +
      "FROM inserted " +
      "DELETE FROM conn.op_temp";

The problem is the first error gives "Invalid object name 'conn.op_temp'", the next errors in conn2.operation and conn.op_temp. How can I go about getting the data from a table in one database on a SqlServer to another table on another database on a different server (Ingres in this case)?
0
pikapi
Asked:
pikapi
  • 10
  • 7
1 Solution
 
martinvCommented:
I'm afraid that this cannot be done this way.

1) triggers runs on the server. Server don't care about which other connection is created by client application. The sql server know about client's SqlConnection, but don't know anything about it's OleDbConnection.

2) SqlConnection and OleDbConnections are totally independent. Even two different OleDbConnections cannot tranfsfer databa between them.

I would try following approaches.

1) If you want to use triggers try to setup "linked server". It connects external database to the server so you can access it from the triggers and other T-SQL code. It doesn't work with all oledb providers, but works good for example with Oracle.

See doc for sp_addlinkedserver and read this article
http://www.microsoft.com/sql/techinfo/tips/administration/addingoracle.asp

than you can use something like INSERT INTO ....  SELECT .. FROM externalserver.externaltable

2) Second option is to transfer data by hand. Load data from the oledbconnection to the databaset or datareader. Than open sql connection and cycle through all dataset lines and create SQL Insert statemensts.

I can post code for converting dataset to buch of T-SQL insert statements but if it's feasible I would prefer first option.
0
 
pikapiAuthor Commented:
How do I apply the linked server commnad to my SqlServer (I'm using the free desktop SqlServer from Microsoft's webpage)? thanks...
0
 
martinvCommented:
Easiest way is to connect to your desktop SQL Server with Enterprise Manager from full MS SQL Server. Than goto security/linked server/add new

If you don't have an access to the Enterprise Manager tool use command line query tool called osql.exe which comes with msde.

Than use
 exec sp_addlinkedserver ...  

command.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
pikapiAuthor Commented:
when i type in osql.exe into the command line i get "Error: No user selected. Try with -u or -e switches"... was I meant to type in something else with that command?
0
 
pikapiAuthor Commented:
also, once a linked server has been set up, will i be able to do:

string select_op = "CREATE TRIGGER op_trig " +
     "ON op_temp " +
     "FOR INSERT " +
     "AS " +
     "INSERT INTO myLinkedServerName.operation (code, name, etc etc) " +
     "SELECT code, name, etc etc" +
     "FROM inserted " +
     "DELETE FROM op_temp";

where op_temp is part of the original SQLServer and operation is the linked server i got from the odbc connection? am i understanding the concept of a linked server correctly?
0
 
martinvCommented:
for create trigger: yes, you will be able to do it

for osql:
try osql -? for all command line switches

you must supply logon info:

osql -S servername -P password -d databasename

than you can enter commands. Command is sent to the database when you type "GO". E.g. to list all table names in the database try

select name from sysobjects where xtype='U'
go



0
 
pikapiAuthor Commented:
thanks, i got the osql command to work...and it seems to be working fine - i get this output: "1> 2> 1> 2> 3> 4> 5> 6> (1 row affected) (1 row affected)"

The problem with the trigger is, when my program tries to run, it pops up an error "Invalid object name 'fish.operation'". The SQL command I'm running with osql is:

USE master
GO
EXEC sp_addlinkedserver
@server = 'fish',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'fish'

please help me!!
0
 
pikapiAuthor Commented:
huh? now i'm even more confused... i tried the osql command the way you said... and... i now get the error that "the server 'fish' already exists"??? but the program is still saying that fish.operation is an invalid object name?
0
 
pikapiAuthor Commented:
i'm reading over some of those docs again, and i wonder if the problem lies in the fact that the remote server uses Ingres and my local server uses SQLServer??maybe?
0
 
martinvCommented:
as for ingress vs ms sql:  it can be the source for the problem.

you are specifiyng @provider MSDASQL which is for externals MS SQL Servers.

You can find some talk about setting up linked servers to ingres databaze here: http://dbforums.com/arch/41/2003/5/786023 
(unfortunately it proves that it's possible but is not providing any pointers to how to do it)

I have no experience with ingres, so it's hard to me to help you with this one. Try posting pointer to this question to SQL Server forum (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/)

BTW - you may need to create system ODBC datasource named 'fish'.
BTW2: - before adding linked server again call sp_dropserver
BTW3: - sp_addlinkedserverlogin sets username and password for inges server.

(search msdn.microsoft.com for those stored procedures)

0
 
pikapiAuthor Commented:
thanks :) ... this may be stupid question, but how do I create a system ODBC datasource?
0
 
pikapiAuthor Commented:
also, would you mind posting code for converting dataset to T-SQL insert statements please?? I might end up doing that if I can't get hte linked server stuff to work... thanks for all your help!!!
0
 
martinvCommented:
Here it goes... It's tested for converting datasets taken from Oracle to SQL Server.

            /// <summary>
            /// Returns set of "INSERT INTO xxx VALUES (yyy)" for all
            /// dataset tables.
            /// </summary>
            /// <param name="ds"></param>
            /// <returns></returns>
            public string ToSqlInserts(DataSet dataset)
            {
                  string output = "";
                  foreach( DataTable table in dataset.Tables )
                  {
                        output += "\n" +  (string) ToSqlInserts( table ) + "\n";
                  }
                  return output;
            }
            
            public string ToSqlInserts(DataTable table)
            {
                  string output="";
                  string tableName = table.TableName.ToUpper();
                  string columnList = "";
                  string valueList = "";
                  
                  output += "---------------------------------\n";
                  output += "-- " + tableName + "\n";
                  output += "---------------------------------\n";
                  
                  if (UseIdentityInsert (table))                  
                        output += "SET IDENTITY_INSERT " + tableName + " ON \n\n";
                  
                  // create column list
                  foreach( DataColumn col in table.Columns )
                  {
                        if (columnList.Length > 0)
                              columnList += ", ";
                        
                        if (UseQuotedIdentifiers)
                              columnList += "[" + col.Caption.ToUpper() + "]";
                        else
                              columnList += col.Caption.ToUpper();
                  }
                  
                  // add values                  
                  foreach( DataRow row in table.Rows )
                  {
                        output += "insert into " + tableName + " ( " + columnList + ") \n";
                        valueList = "";
                        for( int i = 0; i < table.Columns.Count; i ++ )
                        {
                              if (valueList.Length >0)
                                    valueList+=", ";
                                    
                              valueList += "" + ObjectToSqlValueString( row[i] );
                        }
                        output += "VALUES (" + valueList + ")\n";
                        output += "GO\n\n";
                  }
                  
                  if (UseIdentityInsert(table))
                        output += "SET IDENTITY_INSERT " + tableName + " OFF \n\n";
                  
                  return output;
            }
            private string ObjectToSqlValueString(object o)
            {
                  switch (o.GetType().ToString())
                  {
                              // ---------------
                              // DATETIME
                              // ---------------
                        case "System.DateTime":
                        {
                              // convert tu universal date format
                              DateTime dtValue = (DateTime) o;
                              return
                                    "'" +
                                    ((int)dtValue.Year)  .ToString().PadLeft(4,'0') +
                                    ((int)dtValue.Month) .ToString().PadLeft(2,'0') +
                                    ((int)dtValue.Day)   .ToString().PadLeft(2,'0') + " " +
                                    ((int)dtValue.Hour)  .ToString().PadLeft(2,'0') + ":" +
                                    ((int)dtValue.Minute).ToString().PadLeft(2,'0') + ":" +
                                    ((int)dtValue.Second).ToString().PadLeft(2,'0') +
                                    "'";
                        }
                        
                              // ---------------
                              // string
                              // ---------------
                        case "System.String":
                        {
                              String strValue = (String) o;
                              return "'" + strValue.Replace("'","''") + "'";
                        }

                              // ---------------
                              // boolean
                              // ---------------
                        case "System.Boolean":
                        {
                              System.Boolean b = (System.Boolean) o;
                              if (b)
                                    return "1";
                              else
                                    return "0";
                        }

                              // ---------------
                              // null
                              // ---------------
                        case "System.DBNull":
                              return "NULL";
                  
                              //HACK: float should be always with "."
                        case "System.Decimal":
                        case "System.Double":
                        {
                              string result = o.ToString();
                              //HACK - it's for performance reason. Cleaner solution would use
                              //localization to EN.
                              return result.Replace(",",".");
                        }

                              // ---------------
                              // other values
                              // ---------------      
                        case "System.Int16":   // integer
                        case "System.Int32":   // integer
                        default:
                              return o.ToString();
                  }
            }
0
 
martinvCommented:
System datasource:

go to control panel and open "Data sources (ODBC)" applet, than select "System DSN" tab.
0
 
pikapiAuthor Commented:
control panel where?
0
 
martinvCommented:
Windows controls panel.

Start menu/Settings/Control panel

If you are using Windows XP you need to switch to "Classic view" - link on the left - to view all avaliable applets
0
 
pikapiAuthor Commented:
oh okay...i thought that might be it... but how do I view the applets (i'm in wndows 2000)?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now