Topic: ‘SQL’

You have created a stored procedure, tested it, and figured out how to call it from X++. However, when you execute your code the procedure doesn’t work as expected. Your calculations are inconsistent.

Execution feedback is one reason that will cause inconsistent, wrong calculations when your stored procedures are called from X++. Feedback messages interrupt your X++ execution of your SQL stored procedure. For example, if you have a have a While Loop that updates five data rows only three might be updated because the execution was interrupted, by the feedback, before your Loop got to row four.

Read the rest of this entry »

While snowed in at home today, I took a call from a client who was running into this little nugget of weirdness.

While updating the warehouse, the job failed.  Running the SSIS master package showed the error in detail: Arithmetic overflow converting IDENTITY to Type Int.  This occurred during the ItemTransactions component of the Item Daily Quantities update. 

My first reaction was to clear the ItemDailyQuantity table, reset LastUpdate and re-run the package.  So I did, but I cleared the table with a DELETE statement rather than a TRUNCATE statement (very important).  I got the same error when I re-ran the package.  After some digging on the web, I figured out the problem:

The ROWID of the ItemDailyQuantity table is an INT.  The client had a sufficiently large volume of transactions that the Identity value for that column exceeded the storage capacity of an INT (2,147,483,647).  If I had truncated the table, it would have reset the identity column value of the table back to 1, but, since I deleted it, it didn’t.  Truncating the table and rerunning the package made everything work okay.

If you want to avoid this issue, you could set the column ROWID to be a BIGINT or you can run the script located in this blog post (http://vyaskn.tripod.com/sql_server_check_identity_columns.htm) to figure out which tables are likely to cause the problem.

Good on ya,

Dwight

While building a custom Smartlist using a SQL view and Smartlist Builder on a “Linked Server” I ran into an Error.  Ever seen this one before?  I’m sure some of you out there have since it has been around for a while.

Since the ANSI_NULLS and ANSI_WARNINGS are hard coded as Off; consequently, the view created could not connect to the SQL data.

Adding the following to the beginning of the code to turn ANSI_NULLS, QUOTED_IDENTIFIER and ANSI_WARNINGS on… 

 And add to the end of your code

Corrected the issue.

Microsoft has released a hotfix to resolve the issue with pop-up windows appearing hidden when using the TS RemoteApp feature.

The Terminal Service RemoteApp (TS RemoteApp) feature is new with Windows Server 2008 and Windows Server 2008 R2.  When Microsoft Dynamics GP was configured with the TS RemoteApp feature, certain pop-up windows would not appear and Microsoft Dynamics GP would appear to “hang” or “lock up”.   Because of this issue, Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010 were not supported with the TS RemoteApp feature.

CLICK HERE for link to Hotfix.

CLICK HERE for PartnerSourcs link.

Learn how to resolve your security challenges in this I.B.I.S. webinar. Using a Microsoft Dynamics® GP Security-related SQL View with SmartList Builder and Excel.

Walk through creating a SmartList from a SQL view, creating a connection to a SQL view from Excel and reporting on GP 10.0 security.

Disover how to create a SQL view on the Dynamics GP tables related to system security and utilizing Microsoft Excel for pivot reporting on the data.

Title: Microsoft Dynamics® GP Excel Reporting: Focus on Security

Speaker: David Duncan, I.B.I.S.Consultant

Mark Polino came up with a handy Excel spreadsheet for referencing SQL tables in Dynamics GP 10.  To download your own copy click tables in Dynamics GP 10. 

Thank You!  Mark for providing this spreadsheet.  I have already used it several times.

Start with the basics in this I.B.I.S. webinar, SQL Reporting Services Basic Report Design.

Learn how to get started in Reporting Services, design and create reports, connect with Microsoft Dynamics GP data, and save time with plenty of tips and tricks.

Title: Dynamics GP – SQL Reporting Services Basic Report Design

Speaker: Gehard Venter, I.B.I.S. Senior Consultant

This post was submitted by Christina Belding who is a Managing Consultant here at I.B.I.S., Inc.   She provides a real-life scenario of what many of us come across out in the field and a very useful script to locate specific columns in a database. 

Christina’s story…I had to find every table where a specific field was being stored quickly.  My client needed to make a decision on whether or not they should change some core behavior or locate another field to be used, however, we weren’t sure how widespread our field truly was.  Did it have a history table associated with it?  Were there a lot of transactional tables?  Hmmmm….

We ran the following script (change the CALLNBR field to whatever the physical name of the field is you’re looking for) and voila!  We determined in GP10, the CALLNBR (call number field in field service) was being referenced in 54 tables.

select  

       on sysobjects.id = syscolumns.id

where syscolumns.name like ‘CALLNBR’ 

order by 1

from syscolumns

You can then go into the resource descriptions within GP and get more information on the specific tables listed.

Take advantage of free tools, tips and useful scripts shared in this I.B.I.S. webinar, SQL Server Tips for Microsoft Dynamics® GP.

From its support administrator console to table reference tools and scripts for finding duplicate transactions to entire database search capability, discover the true power of SQL Server.

Learn tips on how to run SQL queries from both the Support Debugging Tool and Admin Console; run through some common queries, and learn how to copy and paste headers from Management Studio.

Title: SQL Server Tips – Microsoft Dynamics® GP

Speaker: Mark Polino, I.B.I.S. Senior consultant and Microsoft MVP

On Friday, January 23, Microsoft announced significant changes to its business intelligence strategy.  You can find the detail behind this at http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx as well as a number of great blog entries.

To help our customers and partners, I want to summarize those changes then talk about how they will impact you.

Here are the announced changes:

1. Performance Point Monitoring and Analytics will be downgraded into MOSS Enterprise CALS:  This is the part of PPS that handled all dashboarding and KPI’s.  It is the very visual, very sexy part of the product that really helped it sell and also the part that worked the best.  If you own MOSS Enterprise, you now get M&A for free.
2. Performance Point SP3 Release in July/Planning Discontinued:  Planning is the portion of PPS that is the hardest to implement and was intended to replace Forecaster and Enterprise Reporting.  MS will release a performance update service pack (SP3) and then will stop development of new features.  As of July, when SP3 releases, PPS will cease to exist as a stand alone product.
3. Management Reporter To Be Managed by Dynamics:  Management reporter, the FRx replacement reporting tool in PPS, will be given to the Dynamics team to own and enhance.
4. Enterprise Reporting is Back on Support: All previous announcement of Enterprise Reporting’s support discontinuation (planned for Jan 2010) are revoked and its back on the price list.
5. Proclarity Desktop:  Gone.  Not much more comment other than that.

Why did they decide to do this?  Answering solely for myself:

1. The planning module was definitely a v1 release and required significant technical and programming expertise to implement.  However, the Dynamics channel already had good forecasting tools (Forecaster, ER) and wasn’t willing/able to adopt PPS quickly solely for that.  The channel that was adopting it (infrastructure and MOSS partners) weren’t very adept at financial planning and forecasting, so an automatic channel disconnect was setting in.
2. PPS was expensive.  In this environment, it wasn’t going to get good play.  In addition, customers with Enterprise Agreements were feeling "nickled and dimed" over the additional cost in the EA for PPS.
3. MS truly believes in "Democratizing BI", especially in the role Excel and SQL play in this space.  By pushing core BI functionality back into MOSS, Excel and SQL, they allow the widest possible audience to buy and use these features.
4. Lastly, I truly believe someone finally said "Hey, don’t you think the Dynamics team should be working on the finance stuff?".

So, what does this mean to the MS customer?

1. If you own PPS Planning, stick with it, but start looking for a transition in 2012 or thereabouts.
2. If you own PPS  and only use it for M&A, renegotiate your EA when it comes up to lower the cost of it by the PPS piece that got added.  Other than that, you are in the catbird seat.  Good job!
3. If you were looking at PPS for planning, kill the search and talk to your reseller about alternatives.
4. If you were looking at PPS for M&A, continue.  Having it in MOSS Enterprise is great news!
5. If you were looking at Forecaster, FRx, Enterprise Reporting, stop being fearful of the decision.  The products are great and will be supported into the foreseeable future.

As to Management Reporter, we don’t know what’s going to happen to that.  It was billed as the next-gen FRx, but its feature set is, IMHO, pretty crappy still.  I think it will be enhanced significantly by the Dynamics team and become a truly awesome product, but don’t look for short term results.

MS promises it will deliver more roadmap information at 2009 Convergence in New Orleans.  Will have our usual large team there, so we’ll update you after the conference.