ASAP Help Needed Need Sql Guru To Help With Massive Script Issue

May 3, 2007

I need some help I have this massive sql script the problem is I tried to put it in to the query string box in my sql reports and it will not take it this script will run if I break it up but I think it is to large is there a sql guru out there that can show me how to reduce the size of this script maybe by using an out parameter to a stored proceedure. I just dont know what to do and need to produce the report from this script.  Below is the entire script

SELECT
 'Prior Year All ' as 'qtr',
 COUNT(JOB.JOBID) AS 'transcount',
 COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
 SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
 SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
 AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
 SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
 SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
 SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
 SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
 JOB.JURISDICTION,
                        PAYER.PAY_GROUPNAME,
                        PAYER.PAY_COMPANY,
                        PAYER.PAY_CITY,
                        PAYER.PAY_STATE,
                        PAYER.PAY_SALES_STAFF_ID,
                        JOB.PATIENTID,
                        JOB.INVOICE_DATE,
                        JOB.JOBOUTCOMEID,
                        JOB.SERVICEOUTCOME,
                        INVOICE_AR.INVOICE_NO,
                        INVOICE_AR.INVOICE_DATE AS Expr1,
                        INVOICE_AR.AMOUNT_DUE,
                        INVOICE_AR.CLAIMNUMBER,
                        PATIENT.LASTNAME,
                        PATIENT.FIRSTNAME,
                        PATIENT.EMPLOYERNAME,
                        JOB_OUTCOME.DESCRIPTION,
                        SERVICE_TYPE.DESCRIPTION,
                        PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

            (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS  'CompletedSuccessfullyItems',

             (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',
                        
                         (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

                         (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

                         (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',


                        (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',

                        (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',

                         (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',

                         (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',

                        (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'

FROM JOB
                  INNER JOIN INVOICE_AR
                                 ON JOB.JOBID = INVOICE_AR.JOBID
                  LEFT OUTER JOIN PAYER
                                ON PAYER.PAYERID = JOB.PAYERID
                 LEFT OUTER JOIN STATES
                                ON JOB.JURISDICTION = STATES.INITIALS
                LEFT OUTER JOIN PATIENT
                                ON PATIENT.PATIENTID = JOB.PATIENTID
                LEFT OUTER JOIN JOB_OUTCOME
                                ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                LEFT OUTER JOIN SERVICE_TYPE
                                ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
               LEFT OUTER JOIN PAT_SERVICES_HISTORY
                                ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
                (INVOICE_AR.AMOUNT_DUE > 0)
AND
                (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate))
AND
                 (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12))
AND
                (PAYER.PAY_GROUPNAME like '%' + @Company + '%')
AND
                (INVOICE_AR.INVOICE_NO like '%T')
  

GROUP BY
                        JOB.JURISDICTION,
                        PAYER.PAY_GROUPNAME,
                        PAYER.PAY_COMPANY,
                        PAYER.PAY_CITY,
                        PAYER.PAY_STATE,
                        PAYER.PAY_SALES_STAFF_ID,
                        JOB.PATIENTID,
                        JOB.INVOICE_DATE,
                        JOB.JOBOUTCOMEID,
                        JOB.SERVICEOUTCOME,
                        INVOICE_AR.INVOICE_NO,
                        INVOICE_AR.INVOICE_DATE,
                        INVOICE_AR.AMOUNT_DUE,
                        INVOICE_AR.CLAIMNUMBER,
                        PATIENT.LASTNAME,
                        PATIENT.FIRSTNAME,
                        PATIENT.EMPLOYERNAME,
                        JOB_OUTCOME.DESCRIPTION,
                        SERVICE_TYPE.DESCRIPTION,
                        PAT_SERVICES_HISTORY.TRANSPORT_TYPE

UNION ALL

SELECT
 'Current Year 2007 All ' as 'qtr',
 COUNT(JOB.JOBID) AS 'transcount',
 COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
 SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
 SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
 AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
 SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
 SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
 SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
 SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
 JOB.JURISDICTION,
                        PAYER.PAY_GROUPNAME,
                        PAYER.PAY_COMPANY,
                        PAYER.PAY_CITY,
                        PAYER.PAY_STATE,
                        PAYER.PAY_SALES_STAFF_ID,
                        JOB.PATIENTID,
                        JOB.INVOICE_DATE,
                        JOB.JOBOUTCOMEID,
                        JOB.SERVICEOUTCOME,
                        INVOICE_AR.INVOICE_NO,
                        INVOICE_AR.INVOICE_DATE AS Expr1,
                        INVOICE_AR.AMOUNT_DUE,
                        INVOICE_AR.CLAIMNUMBER,
                        PATIENT.LASTNAME,
                        PATIENT.FIRSTNAME,
                        PATIENT.EMPLOYERNAME,
                        JOB_OUTCOME.DESCRIPTION,
                        SERVICE_TYPE.DESCRIPTION,
                        PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

            (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startDate) and DATEADD(@enddate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS  'CompletedSuccessfullyItems',

             (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startdate) and DATEADD(@enddate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',
                        
                         (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@enddate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

                         (SELECT COUNT(JOB.JOBOUTCOMEID)
                         FROM JOB
                                  INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
                                  LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
                                  LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
                                  LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
                                  LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
                                  LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
                                  LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
                        WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
                                     (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startdate) and DATEADD(@enddate)) AND
                                     (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
                                     (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND
                                     (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

           &nb

View 8 Replies


ADVERTISEMENT

Guru Of Guru Needed -- Gag Zues

Mar 21, 2002

Is it possible to change table design without running an alter table SQL Script??

For example, change the following :

Table_A ( column1 int identity(1,1) <<----BEFORE
, column2 varchar(10) null
, column3 char(20) not null
)



to



Table_A ( column1 int identity(1,1) <<----AFTER
, column2 char(255) not null
, column3 varchar(20) null
)


Is it possible to change sysobjects and syscolumns or other system objects for this to occur??

I know this is not recommended but if I had to, what are the steps required to do this??


Thanks very much

View 1 Replies View Related

SQL Licensing - Help Needed ASAP

Mar 31, 2000

Hi all,
Does anyone knows a trick to change the licensing of a SQL server 7 from a Per server to a Per seat Licensing without re-installing SQL.

Thanks,
Joanna

View 2 Replies View Related

Replication Help Needed ASAP

Mar 30, 2007

SQL Server 2000

Ran sp_removedbreplication @dbname = 'FooDatabase'
Did NOT want to do that!
Have the replication scripted out
but getting errors when trying to run the script to recreate.
All Push

I tried
sp_droppublication 'Foopubname'
sp_MSload_replication_status

Error 21776: [SQL-DMO] the 'publication name' was not found in the
TransPublications collection. I cannot create a new publication of the same
name.

View 1 Replies View Related

Help With Stored Procedure Needed ASAP!

Oct 12, 2004

Hello,
I'm new to SQL. I wrote this Stored Procedure but it does not work. When I'm executing it I get these errors:

Server: Msg 170, Level 15, State 1, Procedure Test, Line 12
Line 12: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 15
Line 15: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 19
Line 19: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 24
Line 24: Incorrect syntax near '='

Can anyone please help me with that?
Thank you in advance.




CREATE PROCEDURE Test As
BEGIN TRANSACTION
Select PVDM_DOCS_1_5.DOCINDEX1, TableTest.DOCINDEX2, TableTest.DOCINDEX3, TableTest.DOCINDEX4
From PVDM_DOCS_1_5, TableTest

WHERE TableTest.DOCINDEX1 = PVDM_DOCS_1_5.DOCINDEX1

AND (TableTest.DOCINDEX2 Is NULL or TableTest.DOCINDEX2 ='' OR TableTEst.DOCINDEX3 Is NULL or TableTest.DOCINDEX3 ='' or TableTest.DOCINDEX4 is NULL or TableTEst.DOCINDEX4 = '');


IF TableTest.DOCINDEX2 is NULL or TableTest.DOCINDEX2 = ''
UPDATE TableTest.DOCINDEX2 = DOCINDEX2
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF

If TableTest.DOCINDEX3 is NULL or TableTest.DOCINDEX3 = ''
UPDATE TableTest.DOCINDEX3 = PVDM_DOCS_1_5.DOCINDEX3
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF

If TableTest.DOCINDEX4 is NULL or TableTest.DOCINDEX4 = ''
UPDATE TableTest.DOCINDEX4 = PVDM_DOCS_1_5.DOCINDEX4
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF;

DELETE PVDM_DOCS_1_5 WHERE DOCINDEX1 = DOCINDEX1

IF (@@ERROR <> 0) GOTO on_error

COMMIT TRANSACTION
-- return 0 to signal success
RETURN (0)


on_error:
ROLLBACK TRANSACTION
-- return 1 to signal failure
RETURN (1)
GO

View 2 Replies View Related

Injection Attack - Guru Needed.

Jan 19, 2007

Hello all,

I have a question on whether the following stored precedure would be open to an SQL Injection attack. Assume that a string query would be passed to the SP.

I am told that because the password parameter is only varchar(8) that it is safe.

Can someone prove this wrong?

Thanks....

I have added sample code below.

CREATE TABLE [dbo].[JB_Test](
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Name] DEFAULT (''),
[Email] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Email] DEFAULT (''),
[Password] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Password] DEFAULT (''),

) ON [PRIMARY]
GO

Insert dbo.JB_Test (Name, Email, Password )
values ('John', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Paul', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Geroge', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Ringo', 'asdf@asdf.com', '2345')

GO

Create procedure dbo.JB_Test_Login

@Username varchar(100),
@Password varchar(8)

AS

select Name
from dbo.JB_Test
where Name = @Username
and Password = @Password
GO


--Clean Up Your Mess
--Drop procedure dbo.JB_Test_Login
--GO

--Drop Table dbo.JB_Test
--GO


JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

View 20 Replies View Related

Massive Inserts

Oct 15, 2004

Currenlty I have huge amounts of data going into a table.
I'm sending an xmldoc and using openxml with a cursor to seed them.

the question I have is whether to let duplicate keyed data rows bounce
and then check @@error and then do an update on the nokeyed field
or
to do a select on the keyed field and then do an insert or update based on the
selects results.

Speed is my goal.

View 3 Replies View Related

Massive .bak File

Feb 6, 2007

Rather than posting twice, I thought I would put both issues I'm having in one. Our server is Windows Server 2003 and we're running SQL Server 2005.

The first issue is this: We have several databases and I have scheduled their backups to run nightly which works just fine. A couple weeks ago, one of the databases .bak file grew from about 500MEG to 2GB overnight. Then, just a few days ago, it went from 2GB to 3.5GB. There is nothing unusual going on in the live db that would warrant such an increase in the .bak file. All the dbs are in the same backup job schedule but this is the only one affected. Additionally, I had autogrowth enabled on all the dbs but today disabled it for this particular db. Any ideas?

The second issue is my tempdb.mdf file on my C drive. It will go from just a few hundred KB's to 4.5GB overnight consuming most of what is left on my C drive. I'm afraid I'm in for a system crash if it continues. I have to stop SQL Server and restart it to clear the size. Is there a way to move the location of the tempdb.mdf file to my F drive?

I don't know if these two issues are related or not but certainly would like to hear from someone.

Sorry, in advance, for the large post.

Dave

View 20 Replies View Related

Massive Delete In DB

Sep 6, 2005

Hello,I have a huge database (2 GB / month) and after a while it is becomingnon-operational (time-outs, etc.) So I have written an SQL sentence(delete) that can reduce around 60% of the db size without compromisingthe application data needs. The problem is that when I execute it, thedb does reduce its size 60%, but the transaction log increases at thesame rate. Can I execute the sentence in a "commit" or"transaction" mode so to impede the SQL Server write in the log?Thanks for the help!Antonio

View 6 Replies View Related

SQL 7.0 Massive Row Locking Performance

Mar 3, 2000

When updating large sets a row at a time the performance is lacking in comparison to 6.5. When using PeopleSoft which uses cursors with a begin transaction with a loop inside and a commit after the loop completes, SQL 6.5 with Page locking could handle a 300,000 row transaction in 3-4 hours. 7.0 took 17.5 hours. The difference is 6.5 used 50,000 locks and 7.0 used 300,000 locks.

Does anybody have solution short of rewriting PeopleSoft ?

View 2 Replies View Related

Massive TRN File, But Small DB

Apr 5, 2006

Hi Everyone,

We have a large and active MSSQL 2000 database. Recently, after a rebuild of the server, we had a problem with the SQL service SQLSERVERAGENT. The service could not start as the service account lost local permission to the registry. During this time, all of the data being sent to the database from our application accumulated into the database .ldf file. By the time we were able to get the service restarted, our .ldf file was approx. 28 Gigs. When the service restarded, the .ldf file shrunk down to regular size,about 40 megs, and the .trx tlog file grew up to 28 gigs for that specific period (new file every hour).

The problem is, the database file (database.mdf) stayed about the same as it was before the service was restarted. When the .ldf transfered to the .trn none of the 28 gigs of data got stored in the database. What does this mean? Perhaps with the service stopped the application using the db saw problems and did not commit the data making it all useless? Or is it possible that the data in the .trn log just needs to be forced to commit to the .mdf???

Is there any way to verify the data in the 28 gig .trn file and figure out if we should get it stored to the database? If yes, how would we go about verifying it, and after that how would we force it to commit to the .mdf file? Am I on the right track here or is it not as I see it??

Thanks!
Mike

View 4 Replies View Related

General Question About Massive SP Use

Jul 2, 2006

Hi

Would you say that it's ok for a web site code to make ALL of it's access to a db through SP and views? And I mean everything including inserting new records and updating others with no use with SQL in the code.

The advantage would be very strict control over the access, but in order to achieve this it would take many many SP and views to cover all types of actions, can you think about a disadvantage except all the work creating those SP?? what about the server resources and performance? how demanding it would be?

Thanks,
Inon.

View 7 Replies View Related

Execute Massive SQL Statement

Jan 3, 2006

Hello,

I thought this was a neat solution I came up with, but I'm sure it's
been thought of before. Anyway, it's my first post here.

We have a process for importing data which generates a SELECT statement
based on user's stored configuration. Since the resulting SELECT statement
can be massive, it's created and stored in a text field in a temp table.

So how do I run this huge query after creating it? In my tests, I was
getting a datalength > 20000, requiring 3 varchar(8000) variables in
order to use the execute command. Thing is, I don't know how big it could
possibly get, I wanted to be able to execute it regardless.

Here's what I came up with, it's very simple:

Table is named #IMPORTQUERY, one field SQLTEXT of type TEXT.


>>
declare @x int, @s varchar(8000)

select @x = datalength(sqltext) / 8000 + 1, @s = 'execute('''')' from #importquery

while @x > 0
select @s = 'declare @s' + cast(@x as varchar) + ' varchar(8000) ' +
'select @s' + cast(@x as varchar) +
'=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery ' +
replace(@s,'execute(','execute(@s' + cast(@x as varchar) + '+')
, @x = @x - 1

set @s = 'declare @x int set @x=1 ' + @s

execute(@s)
<<

At the end, I execute the "@s" variable which is SQL that builds and
executes the massive query. Here's what @s looks like at the end:

>>
declare @x int set @x=1
declare @s1 varchar(8000)
select @s1=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
declare @s2 varchar(8000)
select @s2=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
declare @s3 varchar(8000)
select @s3=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
execute(@s1+@s2+@s3+'')
<<

View 4 Replies View Related

Massive Amoutns Of Reading

Jul 23, 2005

Our database server has started acting weird and at this point I'm eithertoo sleep deprived or close to the problem to adequately diagnose the issue.Basically to put it simply... when I look at the read disk queue length, thedisks queues are astronomical.normally we're seeing a disk queue length of 0-1 on the disks that containthe DB data and index. (i.e non clustered indexes are on a disk of theirown).Writes are just fine.Problem is, all our databases are on the same drive, and I can't seem tonail down which DB, let alone which table is the source of all our reads.Now, to really make things weirder.. during the busier times of the daytoday (say 1:00 PM to 4:00 PM) things were fine.At 4:20 PM or so it was like someone hit a switch and read disk queue lengthjumped from 0-1 up to 100-200+... with spikes up to 1500 for a split secondor so.What's the best way folks know to nail down this?Thanks.----

View 9 Replies View Related

Massive Slowdown With Query

Dec 29, 2007

If I remove the TOP 200 this query returns about 2.5 million rows. It combines a lot of records and turns it into much more programmer friendly results. The query slowed down from 2 seconds to about 13 seconds as it has grown from about 10k to the now couple of million.



Code Block

SELECT TOP 200 *
FROM
(
SELECT
[UserProfile].[UserId]
,[aspnet_Users].[UserName]
,[City]
,[State]
,[RoleName]
,[ProfileItemType].[Name] AS pt_name
,[ProfileItem].[Value]
FROM
[UserCriteria]
,[aspnet_Users]
,[aspnet_Roles]
,[aspnet_UsersInRoles]
,[Location]
,[ProfileType]
,[ProfileTypeItem]
,[ProfileItem]
INNER JOIN [UserProfile]
ON [ProfileItem].[ProfileId] = [UserProfile].[ProfileId]
INNER JOIN [ProfileItemType]
ON [ProfileItem].[ProfileItemTypeId] = [ProfileItemType].[ProfileItemTypeId]
WHERE [UserProfile].[UserId] IN (
SELECT [UserCriteria].[UserId]
FROM [UserCriteria]
WHERE
Zipcode IN (
SELECT [Zipcode]
FROM [ZipcodeProximitySQR] ('89108' , 150))
)

AND [UserProfile].[UserId] = [aspnet_Users].[UserId]
AND [UserCriteria].[UserId] = [UserProfile].[UserId]
AND [Location].[Zipcode] = [UserCriteria].[Zipcode]
AND [aspnet_UsersInRoles].[UserId] = [aspnet_Users].[UserId]
AND [aspnet_UsersInRoles].[RoleId] = [aspnet_Roles].[RoleId]
) AS t
PIVOT
(
MIN([Value])
FOR pt_name IN ([field1],[field2]],[field3]],[field4]])
) AS pvt
ORDER BY RoleName DESC, NEWID()





The line: FOR pt_name IN ([field1],[field2]],[field3]],[field4]]) I change the values from the long names to read field1, field2... because it was irrelevant but confusing because of the names.

Here is the showplan text



Code Block
|--Sequence
|--Table-valued function(OBJECT:([aous].[dbo].[ZipcodeProximitySQR].[PK__ZipcodeProximity__5E54FF49]))
|--Top(TOP EXPRESSION:((200)))
|--Stream Aggregate(GROUP BY:([aous].[dbo].[UserCriteria].[UserId], [aous].[dbo].[aspnet_Users].[UserName], [aous].[dbo].[Location].[City], [aous].[dbo].[Location].[State], [aous].[dbo].[UserCriteria].[Birthdate], [aous].[dbo].[aspnet_Roles].[RoleName]) DEFINE:([Expr1039]=MIN(CASE WHEN [aous].[dbo].[ProfileItemType].[Name]=N'height' THEN [aous].[dbo].[ProfileItem].[Value] ELSE NULL END), [Expr1040]=MIN(CASE WHEN [aous].[dbo].[ProfileItemType].[Name]=N'bodyType' THEN [aous].[dbo].[ProfileItem].[Value] ELSE NULL END), [Expr1041]=MIN(CASE WHEN [aous].[dbo].[ProfileItemType].[Name]=N'hairColor' THEN [aous].[dbo].[ProfileItem].[Value] ELSE NULL END), [Expr1042]=MIN(CASE WHEN [aous].[dbo].[ProfileItemType].[Name]=N'eyeColor' THEN [aous].[dbo].[ProfileItem].[Value] ELSE NULL END)))
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join)
| |--Sort(ORDER BY:([aous].[dbo].[UserCriteria].[UserId] ASC, [aous].[dbo].[Location].[City] ASC, [aous].[dbo].[Location].[State] ASC, [aous].[dbo].[UserCriteria].[Birthdate] ASC, [aous].[dbo].[aspnet_Roles].[RoleName] ASC))
| | |--Hash Match(Inner Join, HASH:([aous].[dbo].[UserCriteria].[Zipcode])=([Expr1043]), RESIDUAL:([Expr1043]=[aous].[dbo].[UserCriteria].[Zipcode]))
| | |--Hash Match(Inner Join, HASH:([aous].[dbo].[ProfileItemType].[ProfileItemTypeId])=([aous].[dbo].[ProfileItem].[ProfileItemTypeId]))
| | | |--Index Scan(OBJECT:([aous].[dbo].[ProfileItemType].[ProfileTypes]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([aous].[dbo].[UserProfile].[ProfileId]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([aous].[dbo].[UserProfile].[UserId]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([aous].[dbo].[UserProfile].[UserId]))
| | | | | |--Hash Match(Inner Join, HASH:([aous].[dbo].[UserProfile].[UserId])=([aous].[dbo].[aspnet_UsersInRoles].[UserId]), RESIDUAL:([aous].[dbo].[UserProfile].[UserId]=[aous].[dbo].[aspnet_UsersInRoles].[UserId]))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([aous].[dbo].[UserCriteria].[UserId]))
| | | | | | | |--Stream Aggregate(GROUP BY:([aous].[dbo].[UserCriteria].[UserId]))
| | | | | | | | |--Nested Loops(Left Semi Join, WHERE:([aous].[dbo].[UserCriteria].[Zipcode]=[Expr1044]))
| | | | | | | | |--Clustered Index Seek(OBJECT:([aous].[dbo].[UserCriteria].[UserCriteria]), SEEK:([aous].[dbo].[UserCriteria].[UserId] < {guid'E3D72D56-731A-410E-BCB1-07A87A312137'} OR [aous].[dbo].[UserCriteria].[UserId] > {guid'E3D72D56-731A-410E-BCB1-07A87A312137'}), WHERE:([aous].[dbo].[UserCriteria].[Male]=(1) AND [aous].[dbo].[UserCriteria].[SeekingMale]=(0)) ORDERED FORWARD)
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1044]=CONVERT_IMPLICIT(nvarchar(5),[aous].[dbo].[ZipcodeProximitySQR].[Zipcode],0)))
| | | | | | | | |--Clustered Index Scan(OBJECT:([aous].[dbo].[ZipcodeProximitySQR].[PK__ZipcodeProximity__5E54FF49]))
| | | | | | | |--Clustered Index Seek(OBJECT:([aous].[dbo].[UserProfile].[UserProfileIds]), SEEK:([aous].[dbo].[UserProfile].[UserId]=[aous].[dbo].[UserCriteria].[UserId]) ORDERED FORWARD)
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([aous].[dbo].[aspnet_Roles].[RoleId]))
| | | | | | |--Clustered Index Scan(OBJECT:([aous].[dbo].[aspnet_Roles].[aspnet_Roles_index1]))
| | | | | | |--Index Seek(OBJECT:([aous].[dbo].[aspnet_UsersInRoles].[aspnet_UsersInRoles_index]), SEEK:([aous].[dbo].[aspnet_UsersInRoles].[RoleId]=[aous].[dbo].[aspnet_Roles].[RoleId]) ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([aous].[dbo].[UserCriteria].[UserCriteria]), SEEK:([aous].[dbo].[UserCriteria].[UserId]=[aous].[dbo].[UserProfile].[UserId]) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([aous].[dbo].[aspnet_Users].[_dta_index_aspnet_Users_5_37575172__K2_K1_K4_3]), SEEK:([aous].[dbo].[aspnet_Users].[UserId]=[aous].[dbo].[UserProfile].[UserId]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([aous].[dbo].[ProfileItem].[_dta_index_ProfileItem_5_1714105147__K2_K1_K3_4]), SEEK:([aous].[dbo].[ProfileItem].[ProfileId]=[aous].[dbo].[UserProfile].[ProfileId]) ORDERED FORWARD)
| | |--Compute Scalar(DEFINE:([Expr1043]=CONVERT_IMPLICIT(nchar(5),[aous].[dbo].[Location].[Zipcode],0)))
| | |--Index Scan(OBJECT:([aous].[dbo].[Location].[CityLocation]))
| |--Clustered Index Scan(OBJECT:([aous].[dbo].[ProfileType].[PKProfileTypeProfileTypeId]))
|--Clustered Index Scan(OBJECT:([aous].[dbo].[ProfileTypeItem].[ProfileTypeItem]))




Here is a link to the execution plan from Microsoft SQL Server management Studio.
http://epi.cc/BasicUserSearch.zip

There are no table scans, but the Hash Match from the inner join is pretty bad.

Can anyone give me a pointer or two?

View 1 Replies View Related

SQL Guru's Please Help

Apr 7, 2004

I would like to create a command that will make a new logical field that is true or false depending on weather field is a certian value for example

col1,col2,col3

col1 is firstname
col2 is lastname
col3 is numeric


SELECT col1,col2,(if col3=6)=true AS Boolean
FROM table


i know this code doesnt work but it is a representation of what i would like to happen

View 2 Replies View Related

Need Help Asap

Jun 30, 2004

Hi Folks,

I'm having a bit of trouble updating my script. I have a
script used for MS Access and I need to transfer it to be
compatible with MS SQL Server. When I run the script it
gives the error
''ODBC driver does not support the requested
properties.''
This is the line of the script
that I have to change to work with sql:
''oRS.Open SqlTxt , oConn'' and the sql text is:
''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
Could anybody please help me out in solving this problem?

Cheers!

View 1 Replies View Related

Any SQL Guru's Out There?

Jul 23, 2005

Hello, this probably isnt the best place to ask but I can't find a moresuitable sql newsgroup so I hope y'all dont mind too much.I have 2 tables; Cellar and ColourCELLAR contains the wine name, its year and the no.of bottles.Wine Year BottlesChardonnay 87 4Fume Blanc 87 2Pinot Noir 82 3Zinfandel 84 9COLOUR contains wine name and it's colourWine ColourChardonnay WhiteFume Blanc WhitePinot NoirRedZinfandel RoseThis is from a past exam paper btwOne of the questions was:Write the sql to count how many white wines there are in the table cellar.The solution that the lecturers included is:SELECT count(wine)FROM cellarWHERE colour='White'Now i havent' been able to try out this sql yet but to me that looks wrong.My solution would be:SELECT count(wine)FROM cellarWHERE cellar.wine = colour.wine and colour.colour='White'Can anyone tell me which one is correct, and if mine isn't correct then whyisn't it?Thanks

View 3 Replies View Related

Deleting Massive Data From A Table

Jan 20, 2014

I have to delete a ton of data from a SQL table. I have a unique identifier called the version. I would like to use if not in these versions then delete. I tried to using the statement below, but learned the hard way that it created an error this is the message I got....

Msg 9002, Level 17, State 4, Line 3...

The transaction log for database 'MonthEnds' is full due to 'ACTIVE_TRANSACTION'.

I was reading about truncate, I am not sure how I would do this or how I would setup the statement.

Delete Products
where versions were not in (('48459CED-871F-4971-B888-5083990332BC','D550C8D3-58C7-4C74-841D-1C1675F19AE3','C77C7817-3F04-4145-98D3-37BB1610DB35',
'21FE83FA-476D-4604-80EF-2ED57DEE2C16','F3B50B81-191A-4D71-A406-011127AEFBE1','EFBD48E7-E30F-4047-909E-F14DCAEA4181','BD9CCC41-D696-406B-
'C8BEBFBC-D362-4D0F-A555-B281FC2B3023','EFA64956-C2CF-41FC-8E21-F060597DAFCB','77A8DE56-6F7F-4490-8BED-AA6809B947EF','0F4C1E5F-B689-4DCB-

[code]....

View 2 Replies View Related

SSIS Using MASSIVE Amounts Of Memory

Feb 8, 2008

Hi,

I have a series of SSIS packages, all of which are ultimately executed by a parent package.

I'm consitently getting "OutOfMemory" errors when working with the packages which is temporarily solved by closing Visual Studio and re-opening the package(s)... This solution is short lived however as the OutOfMemory error occurs quite quickly after re-opening, often after doing nothing other than altering a variables default value and attempting to save the package.

The average size of the packages in question (.dtsx files) is around 7,000kb with the largest being 12,500kb. The total size of all the solution's packages is ~75,000kb.

The Processes tab in Task Manager shows a Mem Usage counter for devenv.exe *32 of around 20,000kb when Visual Studio is first opened however, when a single ~6,000kb dtsx file is opened this counter jumps to +300,000kb and when the entire solution is opened (When the parent package is executed), the Mem Usage counter for devenv.exe *32 is a massive +800,000kb!!!

Is this normal SSIS behaviour or do I have a major problem? Any tips or suggestions as to how to resolve this issue would be gratefully received.

FYI, "SELECT @@VERSION" gives me "Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) "

My Server is Windows Server 2003 R2 Enterprise x64 SP2 with 8GB of RAM.

Thanks in advance.

Leigh.

View 7 Replies View Related

A2k To Sql Server 7.0!! Help ASAP..

Apr 4, 2001

Hello All,

I've created access project using upsizing wizard. Project is created ok on slq server 7.0(ie tables, stored proc, views etc.) but when i open tables or forms in A2k it doesn't allow me to enter or modify data. Though all permissions are Ok.

Could anyone please let me know what could be wrong?

Thanks in advance!!

View 1 Replies View Related

Question To Guru...

Oct 30, 2000

One of of server was restarted... In the early hours... And i found this has a resson in event viewer..

Does anybody knows what this means..

The computer has rebooted from a bugcheck. The bugcheck was: 0x0000007f (0x00000008, 0x00000000, 0x00000000, 0x00000000). Microsoft Windows NT [v15.1381]. A dump was saved in: C:WINNTMEMORY.DMP.


Thank you

Jessi.

View 1 Replies View Related

Question For SQL GURU??

Aug 28, 2000

Hi,

Below is the insert statement... it insert a new record by selecting the max ID and add plus 1 to the existing value.. with in the same statement..

My question is can i get the inserted value of NewID into a VARIABLE with in the same insert statement.. I dont want to pass another sql statement to select the MAX ID..

INSERT INTO tbTest(ID,EName)
SELECT (SELECT MAX(ID)+1 FROM TEST),'BRAIN'


Thanks
Jessi..

View 1 Replies View Related

Need DTS/VBScript Guru

Nov 9, 2005

I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.

While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.

Anyone out there able to successfully change a connection object inthis manner??

View 1 Replies View Related

HELP! SQL Guru Is Out Of Town!!!

May 12, 2006

I got the following error on my database when I ran DBCC CHECKDB

Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'PhoneCall' (ID 254623950). Missing or invalid key in index 'PhoneCall0' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:98383:16) identified by (RID = (1:98383:16) ) has index values (PhoneCallID = 46361).

This error is on a couple of different indexii?? What do I do? Any help will be appreciated. Thank you

View 5 Replies View Related

SQL Guru Wanted

Aug 2, 2006

Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.

Thanks in advance

View 20 Replies View Related

Interview With The Guru

Mar 7, 2008

My boss asked me to interview 2 people on Monday for a programmer/developer position. They say they are gurus, but my boss wants me to find out for sure. I thought a question about bitwise operations would be good, maybe one about semi-joins. Are there any questions you can think of that will spot the phony pretty quick?

Thanks,

Jim

View 12 Replies View Related

Error :40 Pls Help ASAP

Aug 1, 2007

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I am using SQL server Express 2005 on my local machine. And ASP.net 2.0 with C#.

View 13 Replies View Related

BULKINSERT - Any GURU ?

Jan 2, 2008



Hi,

I am trying to import comma delimitted text files which contain data for any particular country.

I have created SSIS package to import data into sql server table. table structure is exactly same as text file except one additional column which is to identify the CountryID. Text files we recieved does not contain CountryID column.

I am using BULK INSERT Task in SSIS to import the data as text files are quite big (up to 1 GB). during processing I do determine the country id for that text file as file name contain countrycode which can be use for lookup and get the countryid.

cyn_bills_20071208_032242.txt

CYN is the country code and country table has got country id for it.

I am using Format file with BULK INSERT task as table has additional column which does not present in source file.
currently after inserting all rows into table i have to run an UPDATE statement to update the value of CountryID column which is very expensive some time taking more then 30 min for (30 million rows)

Can anyone tell me how to insert an expression when using BULK INSERT command / Task in SSIS. there must be a way to insert litterals using Bulk Insert.

Thanks in Advance.
Furrukh Baig

View 6 Replies View Related

Massive DTS Delete/import: Logging Problems

Feb 14, 2005

Hey all,

We are using SQL Server 7 on Win 2k and there are some DTS packages set up which empty some large tables (delete from) and then import some datafiles.
The imported files are about 13 GB and during the process the log file gets to about 10GB and then runs out of disk space.

Is there a trick to empty a table without logging it? (a la LOAD Replace from Null in DB2)?
How can I go about keeping the log file size down during this operation?

I think the DB is set to autocommit, the trunc log on chkpt. is set on as is the select into/bulk copy (altho I'm reasonable sure we arent availaing of the bulk copy for the import).

Help? :)

View 2 Replies View Related

Massive Data Import, How To Avoid Dublicates?

Dec 4, 2006

Hello,

I am currently working on a project where I have to import a huge amount of data from CSV files into a database.
I don't want to have dublicate keys in my table, but my CSV file contains them. That means the line more at the end of the file contains the mor up to date information that I have to store.

I try to fix this problem since serveral weeks, but my algorithm is very slow and blocks all other processes on the server. At the moment I am copying all records into a temp table that occure more than once in the CSV file. After that I am running through this table line by line and check if the key already exists in the target table and then either make an insert or an update.

Does somebody know a better process?

I hope somebody can help me... :(

View 5 Replies View Related

Delete Records From A Massive Table (heap)

May 21, 2013

So I've stumbled across an audit table on one of our systems that has reached a hearty 180M rows in size.

The table is a heap (no indexes whatsoever).

Each record has a datetime value indicating when it was created.

I need to delete everything that was created prior to the last 6 months; what is my best plan of attack?

View 12 Replies View Related

Massive UPDATE And SELECT TOP 1 QUERIES, Slowing Down...

Apr 10, 2007

Background

SQL Server 2005 Standard 9.0.1399 64bit

Windows 2003 64-bit

8gb RAM

RAID-1 70gb HD 15K SCSI (Log Files, OS)

RAID-10 1.08TB HD 10K SCSI (Data Files)

Runs aproximately _Total 800 Transaction/Second

We deliver aproximately 70-80 million ad views / day



8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites

All 8 servers talking to the one SQL server via a private network (server backbone).



In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:



select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits



and



UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;



Where the hits number is incremented by one each time that is selcted for that keyword ID.



Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.



Any ideas on why this is happening?



Regards,

Joe







View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved