Permission Levels For MSDE, ASAP

Dec 17, 2004

We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.

I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.

Any ideas how how to make MSDE let anyone connect? Thanks in advance!

View 10 Replies


ADVERTISEMENT

MSDE - Grant Permission?

Mar 13, 2006

Hi all,

If I would like to create a user with a db_owner permission & some permissions such as sp_addlogin, sp_adduser etc.

How can I do? Would you mind to give me some examples?

Many thanks.

View 3 Replies View Related

MSDE [SQL 2000] -- Server Balks When Web Page Loads -- Select Permission Denied

Oct 28, 2006

Hi folks,

I'm having trouble getting off the ground with the Web application walkthrough "Walkthrough: Creating a Web Application Using Visual C# or Visual Basic" in VS.NET Pro 2002 [Academic] documentation. After a bit of fishing around, and consulting the MS Knowledge Base, I got the pubs database installed. I also got the connection to work well enough that the dataset would fill in the IDE.

The problem is that when I try to run the web form, either from the IDE debug menu, or by accessing the .aspx file on localhost using Firefox, I get the error:
SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.
showing in the browser.
My understanding is that this page is running as ASPNET, and I did already carry out the recommended commands to enable access:
C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -Q "sp_grantlogin 'MY-MACHINE-NAMEASPNET'"
C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -d Pubs -Q "sp_grantdbaccess 'MY-MACHINE-NAMEASPNET'"
both of which commands returned successfully. Any suggestions as to what else I should do to get the necessary permissions to actually display the data in my browser? Does the IIS user account need permission also?

Thanks for any insight into this vexing problem. I must say that along the way, I have had some fun exploring the osql comand-line tool. Using the -E switch, I have been able to run select and upgrade queries, but this is all pretty much fishing in the dark. I would like to get back to actually working with the walthroughs in the Visual Studio documentation.

Thanks,

Joseph

View 4 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

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

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

SQL Server - Permission Issues : Execute Permission Denied On Object 'SprocName'

Dec 13, 2005

I have an application that uses Integrated Windows authentication. My Web.config looks like below
<add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" />
When users try to access my application, they get the below error:
Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo'
The Only way I  could get rid off the error is if I set DBO permissions for the user group on the databse.
Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO.
Thanks,
 

View 2 Replies View Related

Query Question, Need Help ASAP!

Jul 21, 2007

Hello Fellow Members,
Background: 
Hopefully someone out there can help me. Here is the situation. I have a documents table which stores "documents". A document can either be a folder or a actually document(word, excel ...). This is determined by a field in the table called 'Type' which is either a '0' or '1'The table stores the following data for each 'document'--------------------------------------------------------------------------------DocID - intDocument - Actual document in bytesLeafName - Actual document nameType - Determines type of document (0 - document, 1- folder)DirName - Which folder the document resides in (can be null)
Problem:I want to create a query which navigates through the entire table and deletes all documents. For example, if a user has a folder called (Temp) and that folder has 30 documents and another folder called (Temp2) that also has documents. Now the user wants to delete (Temp). So what I want the query to do is go through the database and delete then entire folder (Temp) and all of its contents associated with it.
Question:How can I traverse through the table to delete the documents?All of your help would be greatfull.

View 7 Replies View Related

Named Pipes...ASAP Please

May 21, 2001

Hi,

How do I find out on which path is the SQl server lisytening to on named pipes when I look at the registry?

Thanks,
Ganesh

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!!! ASAP!! Procedures And Triggers

Oct 16, 2005

I need to create a procedure to output mean and deviation AND then I also need to create a trigger. Below is the table 'Account' I made.. but the procedure and trigger I made below that is not correct. Can someone revise this for me? I just can't figure it out.

Here is the 'Account' table I made earlier:

create table Account(Account_Number varchar(6)NOT NULL PRIMARY KEY, Branch_Name varchar(12)NOT NULL REFERENCES Branch (Branch_Name), Balance money)

insert into Account values(10101, 'First Bank', 5500)

insert into Account values(20202, 'US Bank', 3550)

insert into Account values(30303, 'Commerce', 7550)

-------------------------------------------------------------------------

Now I need to output the Mean and Standard Deviation of the above Account. So I tried this code but I'm not sure if its right since when I Analyze it, some values come out as 'null'.

CREATE PROCEDURE project2question2 AS

DECLARE @Mean MONEY

DECLARE @Deviation MONEY

SET @Mean = (SELECT avg(cast(Balance as float)) as mean from Account)

SET @Deviation = (SELECT Balance, STDEV(Balance) st_deviation

FROM Account

GROUP BY Balance)

-------------------------------------------------------------------------

The second question was to create a Trigger that would output the name "Account" and the time of change/update to it. So I had this so far, but I KNOW its not right, so can anyone revise this for me? I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out.

Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)

CREATE TRIGGER trigger_ex2

ON ActionHistory

AFTER INSERT, UPDATE

AS

BEGIN

UPDATE ActionHistory

SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)

SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)

-------------------------------------------------------

If someone could give me these codes I would be very grateful. I know you guys are the SQL wizzards. Thanks. (by the way, I use SQL 2000 edition)

- SOnia

View 1 Replies View Related

Design Advice - Need ASAP

Nov 27, 2007

Hello Everyone. Im sorry for this urgent post, but have critical issue that needs a solution quick. So for my issue. I am adjusting our sales order tables to handle a couple different scenarios. Currently we have 2 tables for sales orders

SALESORDERS
------------
SORDERNBR int PK,
{ Addtl Header Columns... }

SALESORDERDETAILS
-------------------
SODETAILID int,
SORDERNBR int FK,
PN varchar,
SN varchar(25),
{ Addtl Detail Columns ... }


Currently the sales order line item is serial number specific. I need to change the tables to be able to handle different requests like :

Line Item Request ( PN, QTY )
Line Item Request ( SN )
Line Item Request ( PN, GRADE, QTY )
ETC.

I am thinking i need to create a new table to hold the specifics for a particular line item. Maybe like this :

SALESORDERSPECS
----------------
SOSPECID int,
SODETAILID int FK,
SPECTYPE varchar, IE : SN, PN, GRADE. { one value per row }
SPECVALUE varchar IE : GRADE A

Im thinking i would need to rename the SALESORDERDETAILS table to SALESORDERITEMS. SALESORDERITEMS would just contain header info like
SalePrice,
Warranty,
Etc...

Then rename SALESORDERSPECS to SALESORDERDETAILS...

Anyone understand what im trying to do? If you need more info please ask. You can also get a hold of me through IM.

Thanks!

JayDial








JP Boodhoo is my idol.

View 3 Replies View Related

Question About Installation. Please Help. ASAP

Apr 11, 2007

My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. Please help.

View 1 Replies View Related

Neebie Needing Help Asap Restoring Sql

Jun 25, 2007

I am having  problem which is getting worst by the moment. I had a power failure which my battery backup fail while running a large report in my SSRS all of a sudden I started getting reportservertempdb.dbo.persistedstream error. I could not get my reports to run through the iis webservice. I could get them to run from within my reportbuilder. I was told to reload my sql and restore it but I can not get my sql to successfully reinstall I am using the sql2005 dev ed. It either gives me a name instance issue or fails the database, report server and notifaction portion of the reload. I am not sure why it will not reload. any assistance would be great.

View 1 Replies View Related

Getting Newly Created Id Number(asap)

Nov 8, 2007

 hello, i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.it works like:insert into table() values()select @id = (newly created id number)  from tableinsert into table1() values(@id) something like that.    

View 2 Replies View Related

Need Help ASAP... Stored Procedures And SQLDataSource

Apr 16, 2008

Hello all, I am working on a tight deadline with a massive project. This project uses stored procedures for the database work. All help is insanely appreciated!
 There are 5 different SP's for writing to the database, all separate sections.
 The problem is when I try to write with 5 sets of Insert parameters in my SQL data source it throws back  "Procedure or function has too many arguments specified."
 When I remove them all and just leave the ones that procedure will use, it works fine. Examples below. There is a stored procedure for all number sets such as (SP1 - @Num1, @MaxBS1, @MinBS1) (SP2 - @Num2, @MinBS2, @MaxBS2)
 This works fine because its the "1's" that are being written (Num1, MinBS1, MaxBS1, Etc.):1 <InsertParameters>
2
3 <asp:SessionParameter SessionField="Jackalope" Name="Jackalope" />
4
5 <asp:ControlParameter ControlID="Textbox1" PropertyName="Text" Name="Num1" />
6
7 <asp:ControlParameter ControlID="MaskedTextbox1" PropertyName="Text" Name="MinBS1" />
8
9 <asp:ControlParameter ControlID="MaskedTextbox2" PropertyName="Text" Name="MaxBS1" />
10
11 <asp:ControlParameter ControlID="RadioButtonList1" PropertyName="SelectedValue" Name="Bonus1" />
12
13 <asp:ControlParameter ControlID="MaskedTextbox151" PropertyName="Text" Name="MinBonus1" />
14
15 <asp:ControlParameter ControlID="MaskedTextbox152" PropertyName="Text" Name="MaxBonus1" />
16
17 <asp:SessionParameter SessionField="UserId" Name="UserId" />
18
19 </InsertParameters>
20
 

This on the other hand will not work.. im guessing even though the stored procedure only contains the parameters it needs, it still try's to force them all in?
1      <InsertParameters>2             <asp:SessionParameter SessionField="Jackalope" Name="Jackalope" />3             <asp:ControlParameter ControlID="Textbox1" PropertyName="Text" Name="Num1" />4             <asp:ControlParameter ControlID="Textbox2" PropertyName="Text" Name="Num2" />5             <asp:ControlParameter ControlID="Textbox3" PropertyName="Text" Name="Num3" />6             <asp:ControlParameter ControlID="Textbox4" PropertyName="Text" Name="Num4" />7             <asp:ControlParameter ControlID="Textbox5" PropertyName="Text" Name="Num5" />8             <asp:ControlParameter ControlID="MaskedTextbox1" PropertyName="Text" Name="MinBS1" />9             <asp:ControlParameter ControlID="MaskedTextbox3" PropertyName="Text" Name="MinBS2" />10            <asp:ControlParameter ControlID="MaskedTextbox5" PropertyName="Text" Name="MinBS3" />11            <asp:ControlParameter ControlID="MaskedTextbox7" PropertyName="Text" Name="MinBS4" />12            <asp:ControlParameter ControlID="MaskedTextbox9" PropertyName="Text" Name="MinBS5" />13            <asp:ControlParameter ControlID="MaskedTextbox2" PropertyName="Text" Name="MaxBS1" />14            <asp:ControlParameter ControlID="MaskedTextbox4" PropertyName="Text" Name="MaxBS2" />15            <asp:ControlParameter ControlID="MaskedTextbox6" PropertyName="Text" Name="MaxBS3" />16            <asp:ControlParameter ControlID="MaskedTextbox8" PropertyName="Text" Name="MaxBS4" />17            <asp:ControlParameter ControlID="MaskedTextbox10" PropertyName="Text" Name="MaxBS5" />18            <asp:ControlParameter ControlID="RadioButtonList1" PropertyName="SelectedValue" Name="Bonus1" />19            <asp:ControlParameter ControlID="RadioButtonList2" PropertyName="SelectedValue" Name="Bonus2" />20            <asp:ControlParameter ControlID="RadioButtonList3" PropertyName="SelectedValue" Name="Bonus3" />21            <asp:ControlParameter ControlID="RadioButtonList4" PropertyName="SelectedValue" Name="Bonus4" />22            <asp:ControlParameter ControlID="RadioButtonList5" PropertyName="SelectedValue" Name="Bonus5" />23            <asp:ControlParameter ControlID="MaskedTextbox151" PropertyName="Text" Name="MinBonus1" />24            <asp:ControlParameter ControlID="MaskedTextbox153" PropertyName="Text" Name="MinBonus2" />25            <asp:ControlParameter ControlID="MaskedTextbox155" PropertyName="Text" Name="MinBonus3" />26            <asp:ControlParameter ControlID="MaskedTextbox157" PropertyName="Text" Name="MinBonus4" />27            <asp:ControlParameter ControlID="MaskedTextbox159" PropertyName="Text" Name="MinBonus5" />28            <asp:ControlParameter ControlID="MaskedTextbox152" PropertyName="Text" Name="MaxBonus1" />29            <asp:ControlParameter ControlID="MaskedTextbox154" PropertyName="Text" Name="MaxBonus2" />30            <asp:ControlParameter ControlID="MaskedTextbox156" PropertyName="Text" Name="MaxBonus3" />31            <asp:ControlParameter ControlID="MaskedTextbox158" PropertyName="Text" Name="MaxBonus4" />32            <asp:ControlParameter ControlID="MaskedTextbox160" PropertyName="Text" Name="MaxBonus5" />33            <asp:SessionParameter SessionField="UserId" Name="UserId" />34            </InsertParameters>
Which is being called by:
1       If Val(TextBox1.Text) >= 1 Then2                    Session("Jackalope") = "Environmental Engineer"3                    SqlDataSource1.InsertCommand = "ScreenD1"4                    SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.StoredProcedure5                    SqlDataSource1.Insert()6                End If 
Is there a way to keep all of my controls parameters centralized in one SQLDataSource and still call my Stored Procedures? 

View 1 Replies View Related

Select Statement Question - ASAP

Oct 19, 2001

Hello,

I want to write a query that returns the following
sum(cola) sum(colb) pct_change = A/B

How do I write the query such that the %_change handles the case
where B is zero?

Thanks
zack

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

Question About Installation. Please Help. ASAP Urgent~!

Apr 13, 2007

My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. In Surface Area Conforuration missing all the service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. I already try to install those package, no matter how i try to install those service package, it keep saying i didn't install any of them. Please help.

View 3 Replies View Related

PLEASE REPLY ASAP -- Importing Data Into SQL Server 7.0

Aug 11, 2000

I'm trying to import data into an SQL Server (7.0) and I'm wondering which Source (Microsoft Data Link, Microsoft ODBC Driver for Oracle, Microsoft ODBC Driver for SQL Server, etc.) -- I THINK we would use the SQL Server driver but I'm not sure... to use AND WHERE TO GO FROM THERE? So far, I get seem to get things to work in my favor. I appreciate any help :) The data I'm trying to import is from Microsoft Excell. If there is anything else you need to know, please email me at iami@iami.org Please provide email/forum-based technical support.

View 4 Replies View Related

Problem With Insert Into In Scheduled Job Pleas Help ASAP

Sep 26, 2007


Hello MSDN

I am using SQL 2005 and trying to INSTERT data in to a table
When I am using my command from SQL query windows it works fine,

INSERT INTO "tbl.FTPuploads" ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")
SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="G:DATAEDItoDB";Extended properties=Text')...uploaded#txt


But when I am trying to put that command in to a scheduled job I get this error


Executed as user: GWfmnlasa. Incorrect syntax near 'tbl.FTPuploads'. [SQLSTATE 42000] (Error 102). The step failed.

I have changed the command to this, I have removed the quotes from the table name.

INSERT INTO tbl.FTPuploads ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")
SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="G:DATAEDItoDB";Extended properties=Text')...uploaded#txt

And now I get this error

Executed as user: GWfmnlasa. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.

View 4 Replies View Related

DTS Execution Error In SQL Server 2000..please Help Asap

Sep 19, 2007



Hi,

when i execute a dts to copy one database from one server to another i am getting the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB 'SQL OLEDB' reported an error.Authentication Failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE/DB provider returned message: Invalid authorization specification]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDB Initialize: Initiliaze returned 0x80040e4d: Authentication failed.].

Please help me solve it.

View 1 Replies View Related

Insert Permission Denied After Granting Permission

Sep 19, 2007



SQL Server 2005 anomoly?
In SQL Server Management Studio I granted specific permissions to user "A" to do Select, Insert, Update, Delete on Table "B" -
When I logged on as User "A" and attempted the Insert imto table "B" I got the following error:
"Insert Permission Denied on Table B, Database C, Schema dbo"
Is this a problem with the dbo schema?

Then I went back and created a stored proccedure "D" with the exact same Insert statement inside the procedure. I granted User "A" execute permission on the stored procedure "D".
I then logged on as User A and executed Stored Procedure "D". No Problem - stored procedure executed fine with the Insert.
I attempted the Insert statement again - straight SQL - as User "A" and got the same error as above ("Insert Permission Denied.....")
Strange behavior - cannot do a SQL. Insert even though user has permissions but can execute a store procedure with the same Insert statement.
What gives?


View 2 Replies View Related

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 View Related

Need Help ASAP 3414 Error SQL 2005 Developer Edition

Jun 15, 2007

 Hello ,
This morning I was running a report in my ssrs and I had a power failure. My Machine rebooted and I can not connect to my database below is the error log can some one tell me what I need to do to recover I really do not want to have to reinstall everything if possible.
2007-06-15 07:59:49.88 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)  Oct 14 2005 00:33:37  Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2007-06-15 07:59:49.88 Server      (c) 2005 Microsoft Corporation.2007-06-15 07:59:49.88 Server      All rights reserved.2007-06-15 07:59:49.88 Server      Server process ID is 1600.2007-06-15 07:59:49.88 Server      Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.2007-06-15 07:59:49.90 Server      This instance of SQL Server last reported using a process ID of 880 at 6/14/2007 5:36:25 PM (local) 6/14/2007 9:36:25 PM (UTC). This is an informational message only; no user action is required.2007-06-15 07:59:49.90 Server      Registry startup parameters:2007-06-15 07:59:49.90 Server        -d C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf2007-06-15 07:59:49.90 Server        -e C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG2007-06-15 07:59:49.90 Server        -l C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf2007-06-15 07:59:50.01 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.2007-06-15 07:59:50.01 Server      Detected 1 CPUs. This is an informational message; no user action is required.2007-06-15 07:59:50.36 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.2007-06-15 07:59:50.46 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.2007-06-15 07:59:51.16 Server      The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality, please start this service.2007-06-15 07:59:51.25 Server      Database Mirroring Transport is disabled in the endpoint configuration.2007-06-15 07:59:51.29 spid5s      Starting up database 'master'.2007-06-15 07:59:51.70 spid5s      WARNING: did not see LOP_CKPT_END.2007-06-15 07:59:51.70 spid5s      Error: 3414, Severity: 21, State: 2.2007-06-15 07:59:51.70 spid5s      An error occurred during recovery, preventing the database 'master' (database ID 1) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.2007-06-15 07:59:51.70 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

View 1 Replies View Related

Levels In A Cube

Feb 24, 2004

I have just started working the 2047 OLAP and came arcross the Analysis Service Limits. It states that the levels in a cube has a limit of 256 and the leves per dimension is 64. I am confused.

What is the definition of (levels in a cube)! and how are the levels in a cube different from (levels per dimension)

View 1 Replies View Related

FOR XML Query With More Than 2 Levels Of Data

Mar 30, 2006

I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.

I'm using the Bush family in this example, these are the relationships:
- George SR
--- George JR
------ Jenna
------ Barbara
--- Jeb
------ Jeb JR
------ Noelle

These statements will create and populate the tables for the example with the above relationships:

SET NOCOUNT ON
DROP TABLE KK_Kids, DD_Dads, GG_Grandpas
CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20))
CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20))
CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Dad_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT PK_KK PRIMARY KEY (KK_Kid_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT FK_KK FOREIGN KEY (KK_Dad_Key) REFERENCES DD_Dads (DD_Dad_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR')
INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR')
INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' )
INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' )
INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' )
INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )


So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads.
I am getting this, which is not what I want:

- George SR
--- George JR
--- Jeb
------ Jenna
------ Barbara
------ Jeb JR
------ Noelle


SELECT 1 as Tag,
NULL as Parent,
GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName],
GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id],
NULL as [DD_Dads!2!DD_DadName],
NULL as [DD_Dads!2!DD_Dad_Key!id],
NULL as [DD_Dads!2!DD_Grandpa_Key!idref],
NULL as [KK_Kids!3!KK_KidName],
NULL as [KK_Kids!3!KK_Dad_Key!idref]
FROM GG_Grandpas
UNION ALL
SELECT 2 ,
1 ,
NULL ,
GG_Grandpa_Key ,
DD_DadName ,
DD_Dad_Key ,
DD_Grandpa_Key ,
NULL ,
NULL
FROM GG_Grandpas, DD_Dads
WHERE GG_Grandpa_Key = DD_Grandpa_Key
UNION ALL
SELECT 3 ,
2 ,
NULL ,
GG_Grandpa_Key ,
NULL ,
DD_Dad_Key ,
NULL ,
KK_KidName ,
KK_Dad_Key
FROM GG_Grandpas, DD_Dads , KK_Kids
WHERE GG_Grandpa_Key = DD_Grandpa_Key
AND DD_Dad_Key = KK_Dad_Key

FOR XML EXPLICIT


I've tried it all different ways, but no luck so far.
Any ideas?

View 5 Replies View Related

Results Are Returning On Different Row Levels?

Feb 26, 2014

How do I get my data to show starting at the first row instead of skipping down?

Refer to the attachment.

Code:
CREATE PROCEDURE [dbo].[uspReportData]
-- Add the parameters for the stored procedure here
@Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL,
@Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,

[code].....

View 1 Replies View Related

Transaction Isolation Levels

Feb 15, 2006

I am redesigning an application that distributes heldesk tickets to our50 engineers automatically. When the engineer logs into their window astored procedure executes that searches through all open tickets andassigns a predetermined amount of the open tickets to that engineer.Theproblem I am running into is that if 2 or more engineers log in at thesame time the stored procedure will distribute the same set of ticketsmultiple times.Originally this was fixed by "reworking" the way SQL Server handlestransactions. The original developer wrote his code like this:-----DECLARE @RET_STAT INTSELECT 'X' INTO #TEMPBEGIN TRANUPDATE #TEMP SET 'X' = 'Y'SELECT TOP 1 @TICKET_# =TICKET_NUMBER FROM TICKETS WHERE STATUS = 'O'EXEC @RET_STAT = USP_MOVE2QUEUE @TICKET_#, @USERIDIF @RET_STAT <> 0ROLLBACK TRANRETURN @RET_STATENDCOMMIT TRAN-----The UPDATE of the #TEMP table forces the transaction to kick off andlocks the row in table TICKETS until the entire transaction hascompleted.I would like to get rid of the #TEMP table and start using isolationlevels, but I am unsure which isolation level would continue to lockthe selected data and not allow anyone else access. Do I need acombination of isolation level and "WITH (ROWLOCK)"?Additionally, the TICKETS table is used throughout the application andI cannot exclusively lock the entire table just for the distributionprocess. It is VERY high I/O!Thanks for the help.

View 3 Replies View Related

Locks - Isolation Levels

May 22, 2006

Good morning,

I am trying to get my head around locking (row, table) and Isolation Levels. We have written a large .NET/SQL application and one day last week we had about two dozen people in our company do some semi "stress/load" testing of the app.

On quite a few occassions, a few of the users would receive the following error:

"Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

We are handling this on two fronts, the app and the database. The error handling in the app is being modified to capture this specific error and to retry the transaction.

However, from the database side, I am trying to find the most affective and efficient change to make regarding locking. I have been doing a lot of reading online and in BOL to get a better grasp of locking, but what I would really like is feedback from the community (forum) and get your thoughts on what changes I should make, if any, on the db side.

Thanks...

Scott

View 5 Replies View Related

Get The Names For Different Levels In A Table

Aug 20, 2007

hi

I've a table with coln names

ID
Name
ParentID
Level


I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4



How to write the query for getting the Names for different levels for corresponding ParentID....

Output should be like:-
Leaf Branch Trunk Root
Stem Branch Trunk Root

View 1 Replies View Related

Multiple Levels Of Partitioning In EE

Jun 4, 2007

Hi All,

we are building a DW for a company that operates in 10 countries with the home country being the major portion of the data......



Previous efforts have always had the data separated by schemas and so to ask a question about a specific country required the schema number to be provided.



I am proposing that the 10 schemas, and therefore 10x the number of tables, indexes etc, be removed in favour of using partitioning.



However, we want to partition by country and by periods...that is we would like to create monthly partitions as normal.



No matter how I read the documentation and test this out, it seems to me that this multiple levels of partitioning can only be achieved if I create a field on the table that is some manipultion of the key for the company reporting structure and the period. I think I can take the first, add 10M and then add the period key.



But I am unsure if the optimiser is going to do it's partition elimination properly on such a calculated field.



Has anyone attempted such a multi-level partitioning scheme in SQL Server? I am thinking people must have as one level of partitioning was seen to be too restrictive many years ago.....



Thanks in Advance for your comments.



Best Regards

Peter Nolan

View 9 Replies View Related







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