Solved

SQL and ODBC in C#

Posted on 2003-12-08
17
2,701 Views
Last Modified: 2012-08-13
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
Comment
Question by:pikapi
  • 10
  • 7
17 Comments
 
LVL 6

Expert Comment

by:martinv
Comment Utility
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
 

Author Comment

by:pikapi
Comment Utility
How do I apply the linked server commnad to my SqlServer (I'm using the free desktop SqlServer from Microsoft's webpage)? thanks...
0
 
LVL 6

Expert Comment

by:martinv
Comment Utility
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
 

Author Comment

by:pikapi
Comment Utility
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
 

Author Comment

by:pikapi
Comment Utility
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
 
LVL 6

Expert Comment

by:martinv
Comment Utility
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
 

Author Comment

by:pikapi
Comment Utility
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
 

Author Comment

by:pikapi
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:pikapi
Comment Utility
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
 
LVL 6

Expert Comment

by:martinv
Comment Utility
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
 

Author Comment

by:pikapi
Comment Utility
thanks :) ... this may be stupid question, but how do I create a system ODBC datasource?
0
 

Author Comment

by:pikapi
Comment Utility
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
 
LVL 6

Accepted Solution

by:
martinv earned 235 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:martinv
Comment Utility
System datasource:

go to control panel and open "Data sources (ODBC)" applet, than select "System DSN" tab.
0
 

Author Comment

by:pikapi
Comment Utility
control panel where?
0
 
LVL 6

Expert Comment

by:martinv
Comment Utility
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
 

Author Comment

by:pikapi
Comment Utility
oh okay...i thought that might be it... but how do I view the applets (i'm in wndows 2000)?
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now