If you’re scripting and looking to retrieve data from a relational or non-relational database this post should help. Whether you’re retrieving data from Microsoft or another database provider you will need the following API to interface with the database(s) : (A)ctiveX (D)ata (O)bjects

I put (2) examples together. . . 1st w/ Microsoft SQL Connection. . . 2nd w/ LDAP which will build on what you learn from the SQL example.

IMPORTANT – You are required to know connection credentials and be familiar with the data schema. If you don’t, consult with your database admin to gather these requirements.

Here is a VB Script example for connecting to a Microsoft SQL Server v2005 via ADODB API, and then retrieving the row output with a record set (rs) array. You can extend this in the user-defined sections (Highlighted in Blue)

Details of the following code :

  1. You will need access to the server’s file system via FSO (FileSystem Object). With FSO you can create new or access existing files. Do this by setting the FSO variable to the following.
  2. Declare the connection (con), command (com) and record set (rs) variables via DIM statement
  3. Set the connection variable (var)
  4. Set the command var
  5. Set the con properties : Provider. Connection String.
  6. Open the con
  7. Set com property to con : ActiveConnection
  8. Set com property with SQL query : CommandText
  9. Execute com and set results to rs array; cycle through records to rs array until end-of-file (EOF) is met; then end loop via Wend
  10. Close rs array

 VB Script for Microsoft SQL ADO Connection

Set fso = CreateObject(“Scripting.FileSystemObject”)
Dim con, com, rs

Set con = CreateObject(“ADODB.Connection”)
Set com = CreateObject(“ADODB.Command”)
con.Provider = “SQLOLEDB”
con.ConnectionString = “Driver={SQL Server};Server=[server name];Database=[database name];UID=[username];PWD=[password]
con.Open

Set com.ActiveConnection = con
   com.CommandText = “Select [field 1], . . . , [field n] from [table]”

Set rs = com.Execute

rs.MoveFirst

counter = 1

While NOT rs.EOF

[user defined logic]

rs.MoveNext

counter = counter + 1

Wend

rs.close

Once you have the data stored within the record set you can begin building your vbscript logic to determine what you want to do with the data stored within the record set (rs). This should get you rolling. . . If you have any questions about the above script or need help moving to the next step, let me know.

VB Script for Lightweight Directory Access Protocol Connection

Const LDAPSource = “LDAP://

Dim con, com, rs
    Set con = CreateObject(“ADODB.Connection”)
    Set com = CreateObject(“ADODB.Command”)
    con.Provider = “ADsDSOObject”
  con.Properties(“User ID”) = “[username]”
  con.Properties(“Password”) = “[password]
    con.Open

Set com.ActiveConnection = con
        com.CommandText = “Select [field 1], . . . , [field n] from ‘” & LDAPSource & “‘ where [condition(s)]

[ . .  record set logic . . ]

Don’t forget to close the record set logic . . .

Advertisements

I’m working with a situation that a production database was populating a notification table for a downstream process which would delete the data post-process. Here’s what would happen. . .Once a set of application rules inserted the formatted variables into respective fields a downstream process will retrieve the data, deliver notifications, and then re-query the database for that row and delete it.

So if you’re trying to retrieve the row(s) prior to the down stream process deleting it – you can use the following logic to continue looking until it finds data in the table. I did this to copy a production data row and then insert the results into a test/dev environment. This approach helped avoid impact of production processes to force data output.

NOTE : I initially built this without a timing mechanism so it would continually run until one or more rows returned – then a select statement follows to a specific set of fields from the table. To help matters, I introduced a delay in the loop, to avoid maxing out a CPU on the SQL Server, at a (5) second interval. . .

declare @count int
SET @count = 0

WHILE @count = 0
BEGIN
 set @count = (Select count(*) from [table name])
 waitfor delay ‘0:0:5’
END

Select * from [table name]

Thanx – J

Looking to consume SAS data via Sharepoint ? Look no further. SAS recently released v1.1 webparts for Microsoft Sharepoint. Allowing sharepoint site owners to present SAS Dashboards and Analytix on your site(s).

You have (2) webparts included :

  1. BI Dashboard Web Part
  2. Stored Process Web Part

If I understood the SAS Admin correctly, in order to use the BI Dashboard Web Part the company should own the SAS BI Dashboard Server. I would like to see this functionality and hear the advantages of this web part (e.g. drill downs, interactive behaviors, etc) since I do not have the luxury to try this.

However, the Store Procedure web part appears to be working great with the exception of one known problem – RSA Key Errors. Yet to determine if the issue is IIS or Sharepoint related. I’m not convinced this is not a SAS issue per se.

In the post-install config process you are instructed by SAS to modify key values in the webpartssetup.bat script which change the xml settings defined in the web.config file located at x:\inetpub\ . . * . . .\virtualdirectories\80 – After configuring the *.bat file, you execute it to the settings.

. . . Back to Sharepoint you pick/create your site collection – add your web part(s), modify shared webpart, and you should see the server settings reflected from the userdefined settings you defined for the web.config. In the web part control panel I choose an available web part and then exited edit page mode.

Over time. . . say daily . . . I try with a standard user account – to access the site and the web parts render an error saying :

An error occurred while attempting to access the Stored Process Web Application. Details : Failed to decrypt using provider ‘RsaProtectedConfigurationProvider’. Error message from the provider : The RSA key container could not be opened. (web.config line xyz)

 I’m not convinced that this is a SAS related issue, but how the RSA key is to contact Microsoft and decrypted the web.config settings? If our SAS admin touches the sharepoint site, he gets this error. If I used a sysadmin account to touch the sharepoint site – it seems to repair the underlining issue (again unknown) that will allow webpart(s) to stop failing. . . but tomorrow – back to the same error.

I’m scratching my head. . . Any thoughts SAS or Microsoft? Anyone?

Thanx – J

If you’re an administrator of BMC Service Desk Express 9.x , you might find these SQL Statements helpful for retrieving the following SDE ticket types.

–INCIDENT Type

SELECT COUNT(*) FROM _SMDBA_._TELMASTE_

–PROBLEM Type

SELECT COUNT(*) FROM _SMDBA_._PROBMGMT_

–CHANGE Type

SELECT COUNT(*) FROM _SMDBA_._CHANGE_

–WORK ORDER Type

SELECT COUNT(*) FROM _SMDBA_._WORKORD_

Expand upon the statements as you need to introduce JOIN or WHERE Clauses. . . The above will get you started anyway. If you need help – just ask.

Thanx – J

 

Incase you’re looking to find a value tucked within the database but can’t remember the table, let alone the field, you can introduce this ‘SEARCHALLTABLES’ stored procedure (see the following). It will take a search string as an input parameter, goes to search the following column types.

IMPORTANT NOTE : this will only search “ALMOST ALL” tables. It will search the tables that all users that have current access to the database. User created tables & System Tables are excluded from this procedure.

char, varchar, nchar – owned by ALL users within the current database. If you want to modify the DATA_TYPEs you can introduce those in the WHILE LOOP of the Stored Procedure.

Look for this –> AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’ , [new data type] )

Here’s the SQL Procedure

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ”
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)
), ‘IsMSShipped’
       ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

Once the STORED PROC is created you can execute the following . . .

EXEC SearchAllTables ‘[search string]’
GO