Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Tuesday, May 21, 2019 
 
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
   
Stats: 
Article has been read 8393 times

Rating: - 5.0 out of 5 by 3 users
 

fiogf49gjkf0d
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 = "127.0.0.1,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.Open
    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
    Else
        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
    Else
        ExecuteMySQL = 1
    End If

    rs.Close
    oADOconn.Close
    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.

fiogf49gjkf0d

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
fiogf49gjkf0d
An interesting tack. Here is another suggestion, using the SalesLogix provider:

EXEC('CREATE VIEW vTEST AS SELECT * FROM ACCOUNT WHERE ACCOUNT LIKE ''A%''')

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.


Stephen
 
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
fiogf49gjkf0d
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 © 2019 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): 5/21/2019 2:41:55 PM