SSMS reports a different error depending on where code is executed

Stephen Cena

CSI 9.01.12 on prem. Windows Server 2012 and SQL Server 2014.

I've never seen this before and I'm worried there may be a deeper issue. I have a user that submitted a ticket a report is dying. Error message, yadda yadda yadda. so I start looking. I find the SP being called and decide to call it directly to see if maybe there is a data error. I copy out the report parameters, find the right SP and create the command. This is where it gets REALLY weird:

If I run the query in SSMS right on the SQL Server, I get this error:

If I run the exact same query from SSMS running remotely but connected to the SQL Server, I get this error:

What on earth is happening here? I've never seen this happen before. The only recent change to the systems is I replaced SSMS 19.x with SSMS 20.x.  The query I'm running is as follows:

DECLARE @Infobar InfobarType
, @Site SiteType;
SELECT @Site = MIN(site) FROM dbo.parms_mst;
EXEC [dbo].[SetSiteSp] @Site = @Site, @Infobar = @Infobar OUTPUT;

EXEC dbo.Rpt_PurchaseOrderSp @pPoType = N'RB', -- nvarchar(20)
@pPoStat = N'PO', -- nvarchar(20)
@pPoitemStat = N'PO', -- nvarchar(20)
@pPostFlag = 0, -- ListYesNoType
@pPODate = N'2024-04-17 00:00:00.00', -- GenericDateType
@pRoundPlaces = 3, -- GenericIntType
@pPrintItemVI = N'IV', -- nvarchar(20)
@pPrPoTxt = 1, -- ListYesNoType
@pPrPoBlnNote = 0, -- ListYesNoType
@pPrPoLineNote = 1, -- ListYesNoType
@pPrPoBlnDesc = 0, -- ListYesNoType
@pPrPoLineDesc = 1, -- ListYesNoType
@pPrPONote = 1, -- ListYesNoType
@pTransDomCurr = 0, -- ListYesNoType
@pPrintEuro = 0, -- ListYesNoType
@pStartPoNum = N' 221987', -- PoNumType
@pEndPoNum = N' 221987', -- PoNumType
@pStartPoLine = NULL, -- PoLineType
@pEndPoLine = NULL, -- PoLineType
@pStartPoRElease = NULL, -- PoReleaseType
@pEndPoRelease = NULL, -- PoReleaseType
@pStartDueDate = NULL, -- DateType
@pEndDueDate = NULL, -- DateType
@pStartvendor = NULL, -- VendNumType
@pEndVendor = NULL, -- VendNumType
@pStartorderDate = NULL, -- DateType
@pEndOrderDate = NULL, -- DateType
@pReviewPrint = N'R', -- nvarchar(1)
@pPODateOffset = NULL, -- DateOffsetType
@pStartDueDateOffset = NULL, -- DateOffsetType
@pEndDueDateOffset = NULL, -- DateOffsetType
@pStartOrderDateOffset = NULL, -- DateOffsetType
@pEndOrderDateOffset = NULL, -- DateOffsetType
@ShowInternal = 1, -- ListYesNoType
@ShowExternal = 1, -- ListYesNoType
@IncludeBlnsWOReleases = NULL, -- ListYesNoType
@FmSessionId = N'', -- nvarchar(255)
@pPrintManufacturerItem = NULL, -- ListYesNoType
@UserID = NULL, -- TokenType
@TaskID = NULL, -- TaskNumType
@BGSessionId = N'', -- nvarchar(255)
@pSite = N'QVI', -- SiteType
@pPrintDrawingNumber = 0, -- ListYesNoType
@pPrintDeliveryIncoTerms = 0, -- ListYesNoType
@pPrintEUCode = 0, -- ListYesNoType
@pPrintOriginCode = 0, -- ListYesNoType
@pPrintCommodityCode = 0, -- ListYesNoType
@pPrintHeaderOnAllPages = 0, -- ListYesNoType
@pPrintAuthorizationSignatures = 1, -- ListYesNoType
@pPrintItemOverview = NULL, -- ListYesNoType
@ProfileExists = NULL, -- ListYesNoType
@UseProfile = 1, -- ListYesNoType
@Method = NULL, -- OutputMethodType
@Destination = NULL -- DestinationType

0911060553270289.pdf

unknown

The first error is a data type conversion issue, on line 1201 of the stored procedure you're calling, likely some insert into a temp table based on the second error, one of the columns on that temp table is expecting a UNIQUEIDENTIFIER type, but the data being given is an NVARCHAR.

You'll have to look at the definition for Rpt_PurchaseOrderSp for more info, it could be as simple as one of the values being provided to your stored procedure is the wrong type.

Usually RowPointer or SessionIds are GUIDs

It could also be a SessionContext issue, you should avoid using EXEC SetSiteSp if you're going to run stored procedures, it can be fine for simple view calls, but when it comes to these large 2000+ line stored procedures you're going to want to use

    EXEC dbo.InitSessionContextWithUserSp

        @ContextName= 'Any name here to identify session',

        @SessionID  = @session_idOUTPUT,

        @Site       =  'Site here',

        @UserName= 'your SL user here'

   
    --execute code here
    
    --Call this before leaving otherwise you'll create an orphaned session.

    EXEC dbo.CloseSessionContextSp @SessionID= @session_id

Because if you don't you can hijack another user's session and produce weird behavior.

0911060553270289.pdf