Executing SQL Queries via SharePoint Web Services

Can you really execute native SQL Queries from SharePoint Web Services?

Microsoft SharePoint is great for building enterprise systems tying together various data sources.  If the information you are looking for is in a SharePoint List or Document Library, it is straightforward to call the built-in Web Services to query or manipulate that data.  Through custom Web Parts, you can run server-side code and easily retrieve data that lives outside SharePoint.

But what if you don’t have the access to run Server-Side code?  How can you get to the data that lives in SQL Server from your client-side web application?  You can’t call external XML web services because of the Same Origin Policy Restrictions.  True, you can work around this if you have access to a JSONP web service, and some browsers and servers are starting to support CORS to allow limited cross-site access.  But if you don’t have access to the server, can’t control the browser environment and no JSONP web services are available, you aren't out of luck.  I'll show you how to get SharePoint to execute the SQL Queries on your behalf and return the results to your web browser.  With a few tweaks, this same technique can also be used to access arbitrary XML Web Services.  In a later article, I'll expand this example to do just that.

A Word of Warning:

There is a downside to this approach.  Since you will be using the SharePoint Server as a proxy, the SQL logs will show the connection coming from the SharePoint Server.  Also, you must either pass in a username/password with the web service call or use a guest SQL account.  You will have to consider the security impact of either approach carefully.

How does it work?

You will be using the WebPartPages.GetDataFromDataSourceControl method.  This method is intended to be used by SharePoint Designer to render data during page design and is very sparsely documented.  According to MSDN, it takes two string parameters: dscXml and contextUrl.  That is the extent of the MSDN documentation.

In order to make it easier to work with, I created a helper function called SqlQuery that accepts a Server name, Database name, User name, password, Sql Query and a callback function.  Pass in the proper parameters and your callback will be executed with the results.  You can paste the source code below into the HTML source of a Content Editor Web Part on a page on your SharePoint Server to test.  Note, I am using JQuery to simplify the AJAX calls and form interaction.

Source Code:

    <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.2.min.js" type="text/javascript"></script>

    <script type="text/javascript">
        $(document).ready(function () {

            //SqlQuery function - proxies a SQL Server call through sharepoint web services and executes your callback with the results.
            function SqlQuery(server, database, user, password, query, callback) {
                var soapMessage = ["<?xml version='1.0' encoding='utf-8'?>"];
                soapMessage.push("<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>");
                soapMessage.push("  <soap:Body>");
                soapMessage.push("      <GetDataFromDataSourceControl xmlns='http://microsoft.com/sharepoint/webpartpages'>");
                soapMessage.push("          <dscXml>&lt;asp:SqlDataSource runat=&quot;server&quot;  __designer:commandsync=&quot;true&quot; ProviderName=&quot;System.Data.SqlClient&quot;  ConnectionString=&quot;");
                soapMessage.push("Data Source=" + server + ";User ID=" + user + ";Password=" + password + ";Initial Catalog=" + database + ";&quot; ");
                soapMessage.push(" SelectCommand=&quot;" + query + " &quot;/&gt;</dscXml>");
                soapMessage.push("          <contextUrl></contextUrl>");
                soapMessage.push("      </GetDataFromDataSourceControl>");
                soapMessage.push("  </soap:Body>");
                soapMessage.push("</soap:Envelope>");
                var message = soapMessage.join("");
                var thisSite = window.location.href.split(window.location.pathname).shift(); 
                $.ajax({
                    url: thisSite + "/_vti_bin/webpartpages.asmx",
                    beforeSend: function (xhr) { xhr.setRequestHeader("SOAPAction", "http://microsoft.com/sharepoint/webpartpages/GetDataFromDataSourceControl"); },
                    type: "POST",
                    dataType: "xml",
                    contentType: "text/xml; charset=\"utf-8\"",
                    data: message,
                    complete: function (xData, status) {
                        if (status == "success") {
                            var rows = $($.parseXML($(xData.responseXML).find("GetDataFromDataSourceControlResult").prop("text"))).find("Row");
                            callback(rows, status);
                        } else {
                            callback(xData, status);
                        }
                    }
                });
            };
            //Set up some variables to interact with the form.
            var executeButton = $("#executeQuery"), serverInput = $("#server"), databaseInput = $("#database"), userNameInput = $("#userName"), passwordInput = $("#password"), queryInput = $("#query");


            //When the user clicks the button, execute the query, parse and display the results.
            executeButton.click(function () {
                window.status = "calling SQL Query";

                SqlQuery(serverInput.val(), databaseInput.val(), userNameInput.val(), passwordInput.val(), queryInput.val(), function (rows, status) {
                    window.status = status;
                    if (status == "success") {
                        var output = ["<ol>"];

                       //rows is now a jquery object with the response from the sql query
                        rows.each(function (i, element) {
                            var row = [];
                            for (var j = 0; j < element.attributes.length; j++) {
                                var attribute = element.attributes[j];
                                row.push("'" + attribute.nodeName + "'='" + attribute.value + "'");
                            }
                            output.push("<li>" + row.join() + "</li>");
                        });
                        $("#output").html(output.join("") + "</ol>");
                    } else {
                        $("#output").html($(rows.responseXML).text());
                    }
                });
            });
        });
    
    </script>

<!-- And the form to let the user interact with the SqlQuery function. -->

    <h1>
        SQL Query Demo</h1>
    <label for='userName'>
        User Name:</label>
    <input type='text' id='userName' value='SQLUser' />
    <label for='password'>
        Password:</label>
    <input type='password' id='password' value='********' />
    <label for='server'>
        Server:</label>
    <input type='text' id='server' value='SERVERNAME' />
    <label for='database'>
        Database:</label>
    <input type='text' id='database' value='Food' />
    <label for='query'>
        Query:</label>
    <textarea id='query' rows='5' cols='80'>SELECT TOP 10 [NDB_No] ,[Seq] ,[Amount] ,[Msre_Desc] ,[Gm_Wgt] FROM [Food].[dbo].[WEIGHT]</textarea>
    <input type='button' id='executeQuery' value='Execute Query' />
    <h1>
        Output</h1>
    <div id='output'>
    </div>

Example:

Really???? Dynamics GP Report Writer is the Best Report Writer in the World?

Wow!  I never thought I'd have an opportunity to jump between Polino and Musgrave in a squabble but I just can't resist any longer.  Maybe they'll break out the sumo suits at Tech Conference this year and they can settle this debate once and for all.

I think Dave is just trying to get a rise out of Mark but I'll chime in anyway.  Like most, I lean towards the school of thought that Dynamics GP RW should NOT even be up for consideration as a the best in the world.  BUT, that's not to say it can't be great.

You can pretty much do anything you want with GP Report Writer as along as you have 1) intimate knowledge of the inner workings of GP and 2) know how to customize reports using VBA.  Dave demonstrates some of the advanced capabilities in his latest post on how Dynamics GP Report Writer is the greatest Report Writer in the World.  The problem is, there aren't many that would have come up with such a solution.

One cannot be a great Report Writer these days unless just about anyone can write reports with it.  Just about everyone is willing to stipulate that you cannot expect canned reports from any ERP system to meet all of your company's reporting and analytics requirements.  That said, the first question asked by most GP prospects about reporting capabilities is; "How difficult is it for ME to write new reports to my specifications?".  If not for SQL Server Reporting Services, and even Crystal Reports, this would be a much harder sell relying on Dyanmics GP RW alone.  Additionally, existing reports often rely heavily on temporary Dexterity tables that can make even seemingly simple changes to existing reports a challenge.

Finally, Dynamics GP has evolved so that RW is meant to be only one of many report writers available to GP customers.  GP does ship with a vast library of canned RW reports that most find to be extremely valuable in addition to a growing library of SSRS Reports.  Some companies hardly have to customize reports at all or develop new ones from scratch.  But, when you need to you can do so with RW or you can look to other widely available and commonly known tools such as SSRS and/or Crystal among others.

You can voice your opinion on the subject in Mark's latest Facebook poll.

We should celebrate the fact that we deliver and work with a system in Dynamics GP that ships with world class reports "out of the box" and a variety of common tools that enable you to customize or develop new reports from scratch on your own.

Cancelling Dynamics GP Contract Lines

If you ever need to cancel Contract Lines (Field Service Contract Administration Module Contract Lines that is) outside of GP try this:


DECLARE @RC int
DECLARE @CONSTS smallint
DECLARE @CONTNBR char(11)
DECLARE @LNSEQNBR numeric(19,5)
DECLARE @CANCELDATE datetime


-- TODO: Set parameter values here.


EXECUTE @RC = [dbo].[SVC_Cancel_Contract_Line] 
   @CONSTS
  ,@CONTNBR
  ,@LNSEQNBR
  ,@CANCELDATE
GO

The team that developed the Field Service Series made it quite easy to leverage some of the same stored procedures called by GP to perform various functions such as this.

Row by row analysis and execution with Cursors

Being able to do row by row data analysis and manipulation with SQL could be something that's missing from your toolbox.  Whether you're converting data or developing a customization, cursors can prove to be quite useful.  I use this template often.  Loops are generally preferred to cursors for better performance but I still go to cursors often.

This example will pass all of the ACTINDX and ACTNUMST values from the GL00105 table into a cursor called curCursorName and select the variable values, displaying them in the SMS query results pane, before going to the next.

All you have to do is 1) declare a variable for each value you need to select into your cursor, 2) replace the select statement with the data set on which you need to do row by row analysis or manipulation, 3) write you own logic for the EXECUTE THE LOGIC section, and 4) replace the variables in the FETCH sections with your own.  Remember, the values must be selected into the cursor in the same order the variables are fetched. 

--DECLARE THE VARIABLES

DECLARE @ACTINDX INT,

@ACTNUMST VARCHAR(50)

--DECLARE THE CURSOR

DECLARE curCursorName cursor fast_forward for

-- SELECT THE VARIABLES INTO THE CURSOR

SELECT ACTINDX, ACTNUMST

FROM GL00105 T with (nolock)

--OPEN THE CURSOR

OPEN curCursorName

--FETCH A RECORD FROM THE CURSOR

FETCH next from curCursorName

into @ACTINDX, @ACTNUMST

WHILE (@@FETCH_STATUS = 0)

BEGIN

--EXECUTE THE LOGIC

select @ACTINDX, @ACTNUMST

--GET THE NEXT RECORD

FETCH next from curCursorName

into @ACTINDX, @ACTNUMST

END

--CLOSE AND DEALLOCATE THE CURSOR

CLOSE curCursorName

DEALLOCATE curCursorName

Reconciling SOP Batches in Dynamics GP

Yesterday I posted a SQL proc that would reconcile SOP Batches and delete any that were empty.  I realized that was somewhat incomplete after learning about a problem another client was having with missing batch headers.  That also is not for everyone since there was not an option to NOT delete empty batches.

To that end, I have posted a new SQL proc that will reconcile SOP Batch Totals, optionally delete any empty batches, and add any missing batch headers.  If this sounds like something that would be useful to you, feel free to download the proc here, load it on your GP company database, and then simply run the following to execute it:

EXEC [dbo].[SACi_sp_GP_SOP_Batch_Reconcile] 'Sales Entry', 1

The second parameter will drive whether or not empty batches are deleted.  Pass a 1 to delete empty batches and a 0 to not delete empty batches.

This blog is provided "AS IS" with no warranties, and confers no rights.

Deleting Empty SOP Batches Made Easy

I took a support call recently in which a client explained that they had accumulated hundreds of empty SOP Batches that they wanted to delete.  In response, I produced a simple proc that will scroll through the "Sales Entry" Batches and, like Check Links on Sales Work, will update the Number of Transactions and Batch Total on each.  This proc goes one step further and deletes any Batches for which the Number of Transactions is 0.  Of course, there are many reasons why you might want to keep some of your Batches even though they are empty so this might not be for everyone.

If this sounds like something that would be useful to you, feel free to download the proc here, load it on your GP company database, and then simply run the following to execute it:

EXEC [dbo].[SACi_sp_GP_SOP_Batch_Cleanup] 'Sales Entry'

You could schedule a SQL job and run this to clean up your SOP Batches periodically or add a push button to a form in GP for ad-hoc execution from within the application.

This blog is provided "AS IS" with no warranties, and confers no rights.

Generating charts in SQL Server Reporting Services

SQLServerCentral.com has a nice post up with step-by-step instructions on generating charts in SQL Reporting Services.  With Dynamics GP moving more and more towards SQL Reporting Services as the standard reporting tool this is worth a look.

Publishing User Specific Data from Dynamics GP using SSRS

We recently finished a SQL Server Reporting Services engagement for a client requiring that the Sales Order Processing data displayed in reports be filtered by Salesperson.  That alone is simple enough; you could simply add the Salesperson or Territory as a parameter on the SSRS Report, right?  The challenge here was that reps could never be permitted to view another Salesperson's data.  So, it couldn't be quite that simple.  This was compounded only slightly by the requirement that, logically, sales managers needed to see all of the data for their Territories.  So, depending on whether a Salesperson was a Rep or a Territory Manager the results would vary.

This client had attempted several different ways to meet their requirements only to find that the simplest method turned out to be the most effective both in terms of delivering results and controlling costs.

"Make everything as simple as possible, but not simpler." - Albert Einstein

To do this, start by simply assigning a network login to each corresponding Salesperson in Salesperson Maintenance:

Now, we wrote stored procedures that would accept the network login as a parameter, evaluate whether the user was a Sales Rep or a Territory Manager, and then return the required data to which the user was assigned.  In this example, the stored procedure will return the Top 10 Customers based on Invoice Document Amounts.  You can download the sample stored procedure here to see exactly how we did that and use as a template to create your own.

Next, configure your dataset in Visual Studio to simply call the stored procedure using parameters from the report.

Next, after building the SSRS Report in Visual Studio, configure the Report Parameters...

... to make the @WINLOGIN parameter Hidden, so users cannot change this value when running the report, and Default the value to the Global User!UserID.

Now, when running the report Users will be prompted to select or enter a Cut Off Date but will not be given the option to select the Salesperson or User for which they want the report to display data.  That value is automatically passed to the stored procedure to deliver user specific results back to the report.

Scheduling Jobs in SQL 2005

After posting Automatic Aging for GP a request to explain how to schedule such a script to run unassisted was posed. Leveraging SQL Server Agent Jobs to schedule tasks that can execute a myriad of different types of events is a powerful way to improve productivity and automate processes among other things. On top of that, it's incredibly simple!

In this example, I'm going to use SQL 2005. The process would vary depending on which version of SQL Server you are running. In SQL Management Studio expand the SQL Server Agent and right click on Jobs to create a new Job.

In the New Job Window, General Tab enter a name for your job, specify the owner and category, and enter a description if you'd like. I don't recommend using sa or individual user accounts as job owners. It's best to setup service accounts specifically for this purpose.

Next, Select the Steps Page and click the New button to create a new step. You can setup jobs with many steps of varied types. In this example, we'll create only one that will execute the rmAgeCustomer stored procedure. If you have multiple GP company databases you can setup a separate job to run RM Aging in each. Give the step a name, select Transact-SQL script (T-SQL) as the type, and specify the GP company database aginst which the SQL statement will run; in this case TWO.

Next, select the Schedule Page to specify when the job will run. In this example, the job will run every morning at 3 AM. This way, receivables are aged each morning before the business day begins.

Finally, setup alerts to write to the event log on success and notify your GP Administrator or DBA when the job fails.

This is a very simplistic example of using the SQL Server Job Agent. There are many best practices on managing jobs you should consider. The purpose of this post isn't to cover all of those but rather help introduce those new to GP & SQL Server to the concepts.

Automatic Aging for GP

The question came up on the Public GP Newsgroup about automating customer aging in GP. I recalled doing this for some customers in the past and found this method works well. GP calls a stored procedure called rmAgeCustomer to execute the Receivables Aging Process. You can do the same and schedule this to run periodically without user intervention. Run a DEXSQL.log when running that routine to trap this yourself.

Below is the SQL that will age all customers, all statement cycles, and all balance types as of the current date. I recommend you test this before deploying in a production environment.

DECLARE @O_iErrorState int, @I_dAgingDate datetime

select @I_dAgingDate = convert(varchar(10), GetDate(), 102)

EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 0, 0, '', @O_iErrorState OUT

SELECT @O_iErrorState

Table Index Optimization to Improve Dynamics GP Performance

I recently touched up some code I wrote for a client back in 2003. For them, Bob and I integrated GP Depot Management Work Order Entry/Update with Sales Order Processing (Document Number = Work Order Number) for quoting and invoicing repair work and after sales service. Part of this customization enabled them to inquire on their quotes and orders directly from Work Order Entry/Update.

The code simply queries the Sales Order Processing Work (SOP10100) and Sales Order Processing History (SOP30200) tables where the ORIGNUMB = Work Order Number and then opens the Sales Order Processing Document Inquiry, sets the From and To Document Number, and sets the Unposted or History radio button accordingly. This saves the service center personnel significant time when researching work order detail for customers which increases customer satisfaction.

After 6+ years they've accumulated a little history in SOP. The code behind the inquiry buttons placed on Work Order Entry/Update window was taking too long to run. With customer service agents on the phone with customers, waiting more than a few seconds for the system to return data was too long. It didn't take long to realize that there wasn't an index on the SOP30200.ORIGNUMB column. The impact of adding that index was phenomenal reducing wait time from 10+ seconds to what seems like just milliseconds.

To create a new index in SQL Server Management Studio:

1. Right Click on the table and click Design from the menu.
2. From the Table Design menu option select Indexes/Keys.
3. Click Add to create a new index.

4. Select the column(s) on which you want to create the index.

5. Name your indexes consistently so that you can query all them out of sysobjects later when you need to.

6. Set other properties as needed.

There is a lot of great information widely available about when and how to create new indexes. I strongly recommend you educate yourself and engage an expert to optimize your indexes.

Of course, you can use T-SQL to load new indexes on your tables. Here's a sample:

CREATE NONCLUSTERED INDEX IX_SOP30200_ORIGNUMB ON dbo.SOP30200
(
ORIGNUMB
)

WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Remember that when you upgrade GP, it's likely that you will need to reload your indexes as the upgrade process will drop the old tables and therefore your indexes. Plan your upgrades accordingly and make sure you script your indexes before you do. Otherwise, GP will generally react positively to the new indexes.

Get the Next NOTEINDX

This came up for me 2x this week alone on 2 different projects. Just about every time you have to insert a record into a GP Table you have to get the next note index. While it's a simple thing, it may not be obvious exactly how to do it.

Run this against your COMPANY database to increment and return the next note index from the DYNAMICS.dbo.SY01500 table:

--Declare some variables
DECLARE @I_sCompanyID smallint,
@O_mNoteIndex numeric(19,5),
@O_iErrorState int

--Get the CompanyID
select @I_sCompanyID = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()

--Get and increment the next note index
exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output

--Print the Next Note Index
print(@O_mNoteIndex)
--Print the Error State
print(@O_iErrorState)

GP Self Service - Locked Transactions

I neglected to mention another potential good reason to Customize GP; control your support costs, increase your IT resource capacity, and increase user satisfaction. There are routine problems that are easily resolved but often require the assistance of a system administrator. For example, records locked by other users, orphaned TempDB.dbo.DEX_LOCK records, can be a nuisance. This typically grows more common as the concurrent user count increases in Citrix environments. These records are necessary to prevent multiple users from accessing the same transactions at the same time. However, when they are not properly cleared they prevent users from accessing transactions even though another user may not be.

In this example, I added a Push Button to Sales Transaction Entry using Modifier.

Then, I created 2 stored procedures; one to return the userid that has locked the transaction so the user can attempt to resolve this problem on their own ...

... and the other to remove the locking record.


Next, add the Sales Transaction Entry Window, the Document No. field, and the Unlock Trx button to your VBA Project.

Open the VBA Editor to set a Reference in the Microsoft_Dynamics_GP Project to the Microsoft Active X Data Objects 2.x Library. This is required to access the database directly through VBA.

Finally, paste the following code behind the Sales Transaction Entry Window. If some pieces of this are foreign to you try to take advantage of Mariano's VBA workshop this week to learn more and check back as I take a deeper dive in the future into some of these concepts. My apologies for the formatting of this code. I'll work on that.

Private Sub UnlockTrx_AfterUserChanged()

'Declare the variables for the message boxes
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default

'Declare the variables to make the connection to the database
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

On Error GoTo Proc_Error:

'Prompt the user for the Document Number they want to unlock
Msg = "Enter the Document Number you want unlocked." ' Set prompt.
Title = "Unlock SOP Trx" ' Set title.
MyString = InputBox(Msg, Title, Default)

'Set the variables required to make the connection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Query the database to get user that has the document locked
cmd.CommandText = "exec uSp_SELLockedDocUser '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & _
"', '" & cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
If rst.EOF = False Then
'Inform the user and give them the option to unlock the document or cancel
Msg = "This document is locked by " & (RTrim(rst.Fields("userid"))) & ". Continue Unlocking?"
Style = vbOKCancel + vbCritical ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'Unlock the document if requested by the user
If Response = vbOK Then
cmd.CommandText = "exec uSp_DELClearLockedDoc '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & "', '" & _
cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
End If
Else
'Inform the user that the record is not locked
Msg = "This document is not currently locked by another user."
Style = vbOKOnly + vbInformation ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UnlockTrx_AfterUserChanged"

Procedure_Exit:

End Sub

Simple Table Backups with T-SQL

I was working on a project recently with a very experienced and respected GP consultant. He has taught me many things but I was able to show him something very simple that made his life much easier. Simple table level backups with T-SQL.

The following will select all of the data in SOP10100 into a new SOP10100_Bkup_03012009 table:

select *
into SOP10100_Bkup_03012009
from SOP10100

Remember that inserts, updates, and deletes often fire off events that could alter data in other tables. This won't backup those dependent tables so be mindful of the potential that a simple update to one table could affect data on many others.

Custom Business Alerts

GP does a good job of giving you the ability to create business alerts to keep you informed of events that have or may occur based on conditions in your database. Sometimes, the need to create an alert outside of the functionality in GP does, amazingly, come up. Just today a post at http://groups.google.com/group/microsoft.public.greatplains/topics and a response by Polino @ DynamicsAccounting.net drove me to create a sample business alert using only T-SQL that you could use as a starting point to developing your own.

This alert, if scheduled to run periodically, will e-mail a list of users that have been logged into GP for longer than 12.5 hours or 750 minutes. It's pretty simple!

IF EXISTS
(
select datediff(mi,logindat+logintim, getdate()) as DURATION,--convert(datetime, convert(varchar(15), GetDate(), 114), 114) - LOGINTIM as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750
)
BEGIN

DECLARE @SQL varchar(8000)

SET @SQL = 'select datediff(mi,logindat+logintim, getdate()) as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750'

print @SQL

EXEC master.dbo.xp_sendmail @recipients = 'youralias@yourcompany.com',
@subject = 'Users Logged in beyond limit',
@message = 'Attached is a list of users that have been logged in beyond the limit',
@query = @SQL,
@attach_results = 'TRUE',
@width = 250
END

Who has that record locked?

I love the GP Newsgroup. Helping others with their problems helps me learn more about GP. Here's what I taught myself today in response to a newsgroup post.

Run this against your company database to select the users which users have SOP Documents locked:

select s.SOPNUMBE, a.USERID

from tempdb.dbo.DEX_LOCK l

inner join DYNAMICS.dbo.ACTIVITY a

on l.session_id = a.SQLSESID

inner join SOP10100 s

on l.row_id = s.DEX_ROW_ID

and l.table_path_name = DB_NAME() + '.dbo.SOP10100'

Find tables, with data, that have a specific column

We're going to change some Item Numbers in GP. I know, I can use PS Tools to do this but to use that you have to turn off your replication. Long story short, I don't wanna.

Anyway, I had to figure out which tables to update so I wrote a query that would return to me all of the tables, with data, that have an ITEMNMBR column:

select distinct o.Name
from SysColumns c
inner join SysObjects o
on c.id = o.id
inner join SysIndexes i
on c.id = i.id
where c.name = 'ITEMNMBR'
and o.xtype = 'u'
and rowcnt <> 0

I figured that some other SQL Hacks out there might find this useful.

Deleting Empty Batches in SOP

Schedule this script to run periodically against your company database to delete empty SOP batches automatically. It will check to verify that there aren't any transactions in the batch and that there is not a batch activity record first.

DECLARE @INTERID varchar(10),
@CMPNYNAM varchar(31)

SET @INTERID = DB_Name()
SELECT @CMPNYNAM = CMPNYNAM from DYNAMICS.dbo.SY01500 where INTERID = @INTERID

DELETE SY00500
where BCHSOURC = 'Sales Entry'
and BACHNUMB not in (select BACHNUMB from SOP10100)
and BACHNUMB not in (select BACHNUMB from DYNAMICS.dbo.SY00800 where CMPNYNAM = @CMPNYNAM and TRXSOURC = 'Sales Transaction Entry')

SQL Nugget - Shortcut to create INSERT into

Here's a SQL nugget I love to use when writing stored procs, triggers, and the like. Copy and paste this into query analyzer and replace %TableName% with the name of the Table in which you want to insert records. Execute it to return the INSERT into statement complete with default values for every field in the table. Paste the results into your object and populate the fields with your values. This can be a real time save for developers and when doing data conversions.

A slick cat we'll call Mo gave this to me. I can't take credit for it.

set nocount on
DECLARE @sTableName varchar(128)set @sTableName = '%TableName%'
select @sTableName as sTableName into #tmpTableName
DECLARE @lTableID intset @lTableID = NullSELECT @lTableID = [ID] from sysobjects where (objectproperty(id, N'IsTable') = 1) and (id = object_id(@sTableName))
if (@lTableID is Null)begin print 'Table not found! Aborting.' returnend
SELECT [name], xtype, prec, scale, colorder, isnullable into #tmpColumns from syscolumns where ([id] = @lTableID) and (colstat = 0) order by colorder
alter table #tmpColumns ADD lRowID int not null identity, -- add an identity column to the temp table sDefault varchar(40) -- add a column to store the default that we want to enter for new rowsGO
UPDATE #tmpColumns set sDefault = case xtype when 34 then ''' ''' -- image when 35 then ''' ''' -- text when 36 then Null -- unique identifier when 48 then '0' -- tinyint when 52 then '0' -- smallint when 56 then '0' -- int when 58 then '''1/1/1900''' -- smalldatetime when 59 then '0.0' -- real when 60 then '0' -- money when 61 then '''1/1/1900''' -- datetime when 62 then '0.0' -- float when 99 then ''' ''' -- ntext when 104 then '0' -- bit when 106 then '.00' -- decimal when 108 then '0.0' -- numeric when 122 then '0.0' -- smallmoney when 165 then '0' --'convert(varbinary, '' '')' -- varbinary when 167 then ''' ''' -- varchar when 173 then '0' --'convert(binary, '' '')' -- binary when 175 then ''' ''' -- char when 189 then Null -- timestamp when 231 then ''' ''' -- nvarchar when 239 then ''' ''' -- nchar endDELETE #tmpColumns where sDefault is Null--select sDefault, xtype, [name] from #tmpColumns order by colorder, lRowID

DECLARE cur insensitive scroll cursor for select sDefault, [name] from #tmpColumns order by colorder, lRowIDOPEN cur
declare @sDefault varchar(40), @sName varchar(128)declare @sWork varchar(200)declare @sWork2 varchar(100)
select top 1 @sWork2 = sTableName from #tmpTableNameprint 'INSERT into ' + @sWork2print ' ('
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sName
FETCH next from cur into @sDefault, @sName -- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
print @sWorkendprint ' )'
print 'select'
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sDefault set @sWork2 = @sName
-- get the next record from the cursor FETCH next from cur into @sDefault, @sName
-- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
set @sWork = @sWork + char(9) + '-- ' + @sWork2 print @sWorkend
CLOSE curDEALLOCATE CUR
DROP table #tmpColumnsDROP table #tmpTableName