Home | Forums | Contact | Search | Syndication  
 [login] [create account]   Sunday, April 21, 2024 
How to Push out "Pass Through" SQL to Remotes  
Description:  Ever wish there was an easier way to get views created in Remotes? Looking to push out a non-SLXOLEDB compliant Transact SQL statement? This article details a VBScript function to do just this.

Category:  Architect How To Articles
Author:  Gene Underwood
Submitted:  11/24/2005
Article has been read 10204 times

Rating: - 5.0 out of 5 by 3 users

How to Push out "Pass Through" SQL to Remotes

Ever wish there was an easier way to get views created in Remotes? Looking to push out a non-SLXOLEDB compliant Transact SQL statement? Use the following function in a VB Script and it work on any remote that falls under the following rules:

  1. The remote is using msdb (MSSQL) as the database platform.
  2. The remote was attached via the standard SLX attach database utility

The ExecuteMySQL Function

Function ExecuteMySQL(strSQL, strSQLObject, strObjectType, strAction)

'        ADO constants and variables

Const adCmdText = 1
Const adCmdTable = 2
Const adCmdStoredProc = 4
Const adCmdUnknown = 8
Const adOptionUnspecified = 1
Const adExecuteNoRecords = 128
Const adExecuteRecord = 512
Const adChar = 129
Const adDate = 7
Const adParamInput = 1
Const adParamOutput = 2

Dim sADOConnStr
Dim oADOConn
Dim rs
Dim lstrSQL

Const SQLUserId = "sysdba"
Const SQLPwd = "masterkey"
Const SQLServerName = ",1433"
Const SQLSourceDBName = "Slxremote"

'        Establish ADO connection to Saleslogix remote database

    sADOConnStr = "Provider=SQLOLEDB;Data Source=" & SQLServerName & ";Initial Catalog=" & SQLSourceDBName & _
                  ";user id=" & SQLUserId & ";password=" & SQLPwd & ";"
    Set oADOConn = CreateObject("ADODB.Connection")
    oADOConn.ConnectionString = sADOConnStr
    oADOconn.CommandTimeOut = 0

    lstrSQL = "select Name from dbo.sysobjects where id = object_id(N'" & Ucase(strSQLObject) & "') and " & _
              "OBJECTPROPERTY(id, N'Is" & strObjectType & "') = 1"

    Set rs=oADOConn.execute(lstrSQL)
    If rs.EOF = False And strAction = "Create" Then
        lstrSQL = "Drop " & strObjectType & " " & strSQLObject
        oADOconn.execute lstrSQL
        oADOconn.Execute strSQL
        If rs.EOF = True And strAction = "Create" Then
            oADOconn.Execute strSQL
        End If
    End If

    If rs.EOF = False And strAction = "Drop" Then
        oADOconn.Execute strSQL
    End If 

    If strAction = "Execute" And strSQL <> "" Then
        oADOconn.Execute strSQL
    End If 

    If rs.EOF = True Then
        ExecuteMySQL = 0
        ExecuteMySQL = 1
    End If

    Set rs = Nothing
    Set oADOConn = Nothing
 End Function

Using the Code

Sub Main

    If UCase(Application.BasicFunctions.SystemInfoFor("DataBaseNames")) = "SLXREMOTE" Then
        strSQLObject = "sysdba.MYVIEW"
        strSQL = ""
        strObjectType = "view"
        strAction = "Execute" 

        If ExecuteMySQL(strsql, strSQLObject, strObjectType, strAction) < 1 Then
            strSQLObject = "sysdba.MYVIEW"
            strSQL = "Create View sysdba.myview as Select Accountid as RecordId from sysdba.Account " & _
                     "UNION ALL Select ContactId as RecordId from sysdba.Contact"
            strObjectType = "view"
            strAction = "Create"
            intNoAction = ExecuteMySQL(strsql, strSQLObject, strObjectType, strAction) 
        End If
    End If
End Sub


About the Author

Gene Underwood
(SalesLogix Business Partner)
Best Buy, Inc.

amitriptyline 10mg

amitriptyline 50mg chinavisum-service.de buy amitriptyline

Just an SLX lackey :-)

View online profile for Gene Underwood

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
Please log in to rate article.

Comments & Discussion you must log-in to add comments. [login here] 
Author Article Comments and Discussion
Stephen Redmond

Re: How to Push out "Pass Through" SQL to Remotes
Posted: 1/26/2006 6:15:35 AM
An interesting tack. Here is another suggestion, using the SalesLogix provider:


Try it and see. It definitely works on the server and I can only assume that it will sync to remotes.

One caveat about creating views like this - they break security. Accessing data via a view will give users access to all the data in that view - even if it belongs to another team. Even if the SECCODEID is included in the view appears to make no difference.

Bob (RJ)Ledger

slxdeveloper.com Forum Top 10 Poster!

Re: How to Push out "Pass Through" SQL to Remotes
Posted: 5/28/2006 6:54:51 PM
Now that SP4 (v6.2.4) is out you no longer have to do this.. The provider now supports CREATE and DROP. This was implemented to support new functionality comming in Scorpion (v7.0)... and it syncs.

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...

 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
page cache (param): 4/21/2024 6:23:39 PM