I have been tasked with pulling data from a server, but I need to replicate the results of what is currently running. What used to happen is the SQL file would run the stored procedure, and at the end of the procedure is the BCP i have at the end of the python script now(it is still included here in the SP-just incase).

Assuming the values below were real, is it possible to run the sql file with python-which would call the stored procedure on that target server, then using those results somehow run a BCP against them to create the output files? The servers are Trusted with each other-if that helps....

Database Name = TestDatabase

Stored Procedure = ExtractData

Python Script = RunThisPlease.py

Python Script-as is, does NOT work as it cannot read the data returned from the SQL file + stored procedure into the following BCP command

#############RunThisPlease.py
import pyodbc
import os
import re
import subprocess
import bcp
import sys
fd = open('D:\PathTo\SQLScript.sql', 'r')
print("Reading File")
sqlFile = fd.read()
fd.close()
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=11.111.111.11;'
                      'Database=TestDatabase;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()
values = cursor.execute(sqlFile)
command = 'bcp select BATDAT, BATSEQ,SEQNBR,DIH,RCPDAT, ProviderID, ProviderName, TotalBilledAmount, ClaimStatus, ClaimSource, LOBCompany, GroupName, CASE WHEN Messagecodes is NULL THEN  ' + '''' + '''' + ' 'ELSE Messagecodes END from ##openpendreportingdata order by rowid queryout \\rootpath\to\target\directory\ThisOutputFileNameShouldMatchTheStoredProcedureName.txt -c -t, -T -S'

How can i run this command above? it LOOKS ok, but will fail because of temp table ##openpendreportingdata

SQL file that has been calling the stored procedure(ran from the same server as the target database):

SQLscript.sql:
USE [TestDatabase]
DECLARE              @return_value int
EXEC      @return_value = [dbo].[ExtractData]
                                @filelocation = N'\\rootpath\to\target\directory\'
SELECT  'Return Value' = @return_value

Actual Stored Procedure

ExtractData
USE [TestDatabase]
GO
/****** Object:  StoredProcedure [dbo].[ExtractData]    Script Date: 6/2/2023 5:28:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ExtractData] @filelocation VARCHAR(200)
/*****
Date            Issue           Developer                   Change Description
----            -----           ---------                   ------------------
09/16/2020      RHPD-4674       DevName     Add LOBC and Group to the select layout after the claim source
Changes include
1) Top 5 Open Messages (Sorted using that table mentioned in the scope document 0-99)
2) Ordered by receive date (ascending) then by claim number
3) Only includes Claims not posted (<> 'P')
4) Columns
   1) Batdat
   2) Batseq
   3) Seqnbr
   4) DIH (Current Date - Received Date)
   5) Received Date
   6) Provider ID
   7) Provider Name
   8) Total Billed Amount
   9) Claims Status
   10) Claim Source
   11) LOBC                         **RHPD-4674 (CP)
   12) GroupName                    **RHPD-4674 (CP)
   11) Message Codes (1 through 5)
*****/
AS
DECLARE 
@QueryStmt VARCHAR(8000),
@filename VARCHAR(100),
@dbname VARCHAR(100);
SET NOCOUNT ON
Begin
If  @filelocation is null
 set @filelocation = 'c:\testing\'
 
SELECT @dbname = DBNAME FROM [dbo].[SYSFIL]
 
If  @filename is null 
set @filename = @dbname + '- ExtractData - ' + CAST(CONVERT(nvarchar(10),  GETDATE(), 112) as varchar(30))  + '.csv'
IF OBJECT_ID (N'tempdb.dbo.#openpendtempmsgtable', N'U') IS NOT NULL
 Drop table #openpendtempmsgtable
 
IF OBJECT_ID (N'tempdb.dbo.##openpendreportingdata', N'U') IS NOT NULL
Drop table ##openpendreportingdata
SELECT    Distinct 
                CLAIMTYPE,
                BATDAT,
                BATSEQ,
                SEQNBR, 
                RCPDAT,
                DIH,
                ProviderId,
                ProviderName,
                TotalBilledAmount,
                ClaimStatus,
                ClaimSource,
                LOBCompany,                             --**RHPD-4674 (CP)
                GroupName,                              --**RHPD-4674 (CP)
                MSGCOD,
                messagePriority, 
                rowid
   INTO #openpendtempmsgtable
   FROM  
   (
   (Select 
    DISTINCT 'U' CLAIMTYPE,
                        F.BATDAT,
                        F.BATSEQ,
                        F.SEQNBR,
                        F.RCPDAT,
                        datediff(day, Convert(date,F.RCPDAT), Convert(date, getdate())) DIH,
                        P.PRVCOD ProviderId,
                        REPLACE (P.DSCRPT, ',', ' ') ProviderName,
                        F.TOTCLA TotalBilledAmount,
                        F.STSCOD ClaimStatus,
                        CASE
                            WHEN F.EDI_FLAG = '8' THEN 'HIPAA 837'
                            WHEN F.EDI_FLAG = 'M' THEN 'Manual Entry'
                            WHEN F.EDI_FLAG = 'U' THEN 'Upload'
                            WHEN F.EDI_FLAG = 'UH' THEN 'Historical Upload'
                            WHEN F.EDI_FLAG = 'UP' THEN 'Paid Upload'
                            WHEN F.EDI_FLAG = 'N' THEN 'NCPDP 5.1'
                            WHEN F.EDI_FLAG = '8E' THEN '837 Estimator'
                            WHEN F.EDI_FLAG = 'NR' THEN 'NCPDP RTS'
                            WHEN F.EDI_FLAG = '8O' THEN 'Other 837'
                            ELSE 'Unknown'
                        END ClaimSource,
                        --**RHPD-4674 (CP)
                        REPLACE (L.DESCRP, ',', ' ') LOBCompany,
                        REPLACE (G.DSCRPT, ',', ' ') GroupName,
                        case when (prirty is null or prirty = '') then 999 else convert(integer, prirty) end messagePriority,                       
                        ROW_NUMBER() OVER(Partition by F.BATDAT,F.BATSEQ, F.SEQNBR ORDER BY F.BATDAT, F.BATSEQ,F.SEQNBR,case when (prirty is null or prirty = '') then 999 else convert(integer, prirty) End, C.MSGCOD  ASC) rowid,
                        C.MSGCOD
    from   UMCFIL F WITH (NOLOCK)
        Left join dbo.Facfil p
            ON  f.prvnbr = p.prvcod
        INNER JOIN (select distinct BATDAT, BATSEQ, SEQNBR, MSGCOD, STSCOD from CLMMSG WITH (NOLOCK)) C
            ON C.BATDAT = F.BATDAT AND C.BATSEQ = F.BATSEQ AND C.SEQNBR = F.SEQNBR
        inner join dbo.MCDFIL m on m.msgcod = c.msgcod
        --**RHPD-4674 (CP)
        left join dbo.LOBCMP L on F.LOBCMP = L.LOBCMP
        left join dbo.GRPFIL G on F.GRPNBR = G.GRPNBR
    Where C.STSCOD = 'O'  AND F.STSCOD <> 'P'
  )
  union all
  (Select 
              DISTINCT 'H' CLAIMTYPE,
                        F.BATDAT,
                        F.BATSEQ,
                        F.SEQNBR,
                        F.RCPDAT,
                        datediff(day, Convert(date,F.RCPDAT), Convert(date, getdate())) DIH,
                        P.PRVCOD ProviderId,
                        Replace(case when P.namfor = 'S' then P.lstnam + P.fstnam + P.midnam
                             when P.namfor = 'L' then rtrim(P.lstnam) + ' ' + P.fstnam
                             else P.lstkey
                        end, ',', ' ') as ProviderName,
                        F.TOTCLA TotalBilledAmount,
                        F.STSCOD ClaimStatus,
                        CASE
                            WHEN F.EDI_FLAG = '8' THEN 'HIPAA 837'
                            WHEN F.EDI_FLAG = 'M' THEN 'Manual Entry'
                            WHEN F.EDI_FLAG = 'U' THEN 'Upload'
                            WHEN F.EDI_FLAG = 'UH' THEN 'Historical Upload'
                            WHEN F.EDI_FLAG = 'UP' THEN 'Paid Upload'
                            WHEN F.EDI_FLAG = 'N' THEN 'NCPDP 5.1'
                            WHEN F.EDI_FLAG = '8E' THEN '837 Estimator'
                            WHEN F.EDI_FLAG = 'NR' THEN 'NCPDP RTS'
                            WHEN F.EDI_FLAG = '8O' THEN 'Other 837'
                            ELSE 'Unknown'
                        END ClaimSource,
                        --**RHPD-4674 (CP)
                        REPLACE (L.DESCRP, ',', ' ') LOBCompany,
                        REPLACE (G.DSCRPT, ',', ' ') GroupName,
                        case when (prirty is null or prirty = '') then 999 else convert(integer, prirty) end messagePriority,
                        ROW_NUMBER() OVER(Partition by F.BATDAT,F.BATSEQ, F.SEQNBR ORDER BY F.BATDAT, F.BATSEQ,F.SEQNBR,case when (prirty is null or prirty = '') then 999 else convert(integer, prirty) End , C.MSGCOD ASC) rowid,
                        C.MSGCOD
    from HMCFIL F WITH (NOLOCK)
        Left join dbo.PHYfil p
            on  f.prvnbr = p.prvcod
        INNER JOIN (select distinct BATDAT, BATSEQ, SEQNBR, MSGCOD,STSCOD from CLMMSG WITH (NOLOCK)) C
            ON C.BATDAT = F.BATDAT AND C.BATSEQ = F.BATSEQ AND C.SEQNBR = F.SEQNBR
        inner join dbo.MCDFIL m on m.msgcod = c.msgcod
        --**RHPD-4674 (CP)
        left join dbo.LOBCMP L on F.LOBCMP = L.LOBCMP
        left join dbo.GRPFIL G on F.GRPNBR = G.GRPNBR
    Where C.STSCOD = 'O' AND F.STSCOD <> 'P'
    ) 
 ) msg
        
--Query to Consolidate the message codes
select * 
INTO ##openpendreportingdata
from
(
select 'BATCHDATE' as BATDAT,
       'BATCHNUM' as BATSEQ,
       'BATCHSEQ' as SEQNBR,
       'RECEIVEDATE' as RCPDAT,
       'DIH' as DIH,
       'PROVIDERID' as ProviderId,
       'PROVIDERNAME' as ProviderName,
       'TOTALBILLEDAMOUNT' as TotalBilledAmount,
       'CLAIMSTATUS' as ClaimStatus,
       'CLAIMSOURCE' as ClaimSource,
       --**RHPD-4674 (CP)
       'LOBCOMPANY' as LOBCompany,
       'GROUPNAME' as GroupName,
       'MSG 1,MSG 2,MSG 3,MSG 4,MSG 5' as Messagecodes,
       0 rowid
union
SELECT DISTINCT CAST(CONVERT(nvarchar(10),  BATDAT, 101) as varchar(30)) as BATDAT,
                CAST(BATSEQ AS varchar(12)) as BATSEQ,
                CAST(SEQNBR AS varchar(12))  as SEQNBR,
                CAST(CONVERT(nvarchar(10),  RCPDAT, 101) as varchar(30))  AS RCPDAT,    
                CAST(DIH AS VARCHAR(10)) as DIH,
                CASE WHEN ProviderId is NULL THEN ' ' ELSE ProviderId END as ProviderId ,
                CASE WHEN ProviderName is NULL THEN ' ' ELSE ProviderName END as ProviderName,
                CAST(TotalBilledAmount AS VARCHAR(12)) as TotalBilledAmount,
                ClaimStatus as ClaimStatus,
                CAST(ClaimSource AS VARCHAR(17)) as ClaimSource,
                --**RHPD-4674 (CP)
                CASE WHEN LOBCompany is NULL THEN ' ' ELSE LOBCompany END as LOBCompany ,               --
                CASE WHEN GroupName is NULL THEN ' ' ELSE GroupName END as GroupName,
                  substring(
        (
            Select ','+a.msgcod  AS [text()]
            From #openpendtempmsgtable a
            Where b.BATDAT = a.BATDAT
            and  b.BATSEQ = a.BATSEQ
            and  b.SEQNBR = a.SEQNBR
            and a.rowid <= 5
            ORDER BY a.rowid
            For XML PATH ('')
        ), 2, 1000) Messagecodes,
        row_number() OVER( ORDER BY RCPDAT, BATDAT, BATSEQ, SEQNBR) rowid
FROM #openpendtempmsgtable b with (nolock)
GROUP BY  CLAIMTYPE,
                        BATDAT,
                        BATSEQ,
                        SEQNBR,
                        RCPDAT,
                        DIH,
                        ProviderId,
                        ProviderName,
                        TotalBilledAmount,
                        ClaimStatus,
                        ClaimSource,
                        --**RHPD-4674 (CP)
                        LOBCompany,
                        GroupName
) opr
/*
Code here is the code that needs to be replaced:
select @QueryStmt = 'bcp "select BATDAT, BATSEQ,SEQNBR,DIH,RCPDAT, ProviderID, ProviderName, TotalBilledAmount, ClaimStatus, ClaimSource, LOBCompany, GroupName, CASE WHEN Messagecodes is NULL THEN  ' + '''' + '''' + ' ELSE Messagecodes END from ##openpendreportingdata order by rowid" queryout "' + @filelocation + @filename + '" -c -t, -T -S'
select @QueryStmt
exec master..xp_cmdshell @QueryStmt
*/
End

1条答案

按热度按时间

0s0u357o1#

As it turns out, the solution involved the calling server not having the needed permissions on the target server to run the BCP command. It does have a trusted connection for sql and stored procedures, but not for windows commands. The solution ended up involving running both bcp queries as select statements, taking the results from a fetchall(), then formatting it to match what the BCP was doing. Please forgive my formatting, im not sure how to make the code block appear and kept getting hit with single lines....

query = "USE [DBNAME] DECLARE @return_value int EXEC @return_value = [Dbname].[dbo].[ExtractData] @filelocation ='N\\rootfilepath\path\to\filelocation\' SELECT 'Return Value' = @return_value"

query2 = "select BATDAT, BATSEQ,SEQNBR,DIH,RCPDAT, ProviderID, ProviderName, TotalBilledAmount, ClaimStatus, ClaimSource, LOBCompany, GroupName, CASE WHEN Messagecodes is NULL THEN \' + '''' + '''' + \' ELSE Messagecodes END from ##temporarytable order by rowid"

cursor.execute(query)

r = cursor.execute(query2)

delimiter = ","

df = r.fetchall()

cursor.close()

outputfile = open(filename, "a")

res = ''

for ele in df:
        data = str(ele).replace("'","")
        data = str(data).replace('"',"")
        data = data.rstrip(")")
        data = data.lstrip("(")
        datas = data.split(",")
        newd = ""
        for d in datas:
            tempd = d.lstrip()
            newd = newd + tempd + ","
        newd = newd.rstrip(",")
        outputfile.write(newd + "\n")
    outputfile.close()`

展开查看全部

赞(0）回复(0）举报 2023-06-28

SQL Server Is it possible to run a stored procedure with python, then run a BCP command against the results?

1条答案

相关问题

热门标签

最新问答