|
sallam 2 all;
im getting return value 0 instead of getting rec
my stored proc is
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procCaseCategorySearch]
(
-- @CheckStatus BIT,
@Case VARCHAR,
@CaseNo VARCHAR,
@Category INT,
@CategoryID INT,
@FromDate DATETIME,
@ToDate DATETIME
)
AS
-- if @CheckStatus =1
--begin
-- true logic
IF(@Case! ='' )
BEGIN
SELECT CCSI.CS_RECEIVED_DATETIME,CCSI.CS_CASE_NO,CCSI.CS_CASE_TITLE, CCSI.CS_BILLING_COMPLIANCE_ID,
CCSI.CS_CASE_DETAIL,CCSI.CS_MAIL_ID, CCSI.CS_CASE_FROM_REP, CCSI.CS_USER_NAME,
CCSI.CS_NUMBER_OF_TIMES_CALLED,CCSI.CS_CASE_TYPE_ID,CCSI.PRIORITY_LEVEL, CCC.CS_CATEGORY_NAME,
PM.LAST_NAME + ',' + ISNULL(PM.FIRST_NAME,'') + ' [ '+ ISNULL(PM.EMAIL_ADDRESS,'') + ' ]' AS NAME,
ISNULL(PM.EMAIL_ADDRESS,'') AS PATIENT_EMAIL, CCP.CS_PRACTICE_CODE,
CCP.CS_PROVIDER_CODE,CCP.CS_PATIENT_ACCOUNT, CCP.CS_CLAIM_NO,CCP.CS_INSURANCE_CODE,
CCP.CS_RESPONSE_DATE , CCP.CS_REP_PHONE,CCP.CS_REP_PHONE_TYPE , CCP.CS_REP_EMAIL,CCP.CS_SEND_MAIL_TO,
CCP.CS_IS_SEND, CCP.CS_CREATED_DATE, CCP.CS_CREATED_BY, CCP.CS_MODIFIED_BY, CCP.CS_MODIFIED_DATE,
CCP.SHOW_ON_WEB,CCP.CS_CASE_STATUS ,CCP.CS_AUTHORIZE,CCP.CS_BILLING_COMPLIANCE , LP.PROVIDER_CODE ,
ISNULL(LP.PROVID_LNAME,'') + ',' + ISNULL(LP.PROVID_FNAME,'') AS PROVIDERNAME,
ISNULL(LP.EMAIL_ADDRESS,'') AS EMAIL_ADDRESS_PROVIDER, LPI.PRACTICE_CODE ,
ISNULL(LPI.PRAC_NAME,'') AS PRACTICENAME, ISNULL(LPI.EMAIL_CONTACT_PERSON,'') AS PRACTICE_EMAIL,
LI.INSNAME_DESCRIPTION, PT.PHONE_TYPE, LCT.CS_CASE_TYPE_DESCRIPTION
FROM CS_CUSTOMER_SUPPORT_INFO
CCSI LEFT OUTER JOIN CS_CASE_CATEGORIES CCC ON CCSI.CS_CASE_CATEGORY = CONVERT(VARCHAR(10),
CCC.CS_CATEGORY_ID) LEFT OUTER JOIN CS_CASE_PROGRESS CCP ON CCSI.CS_CASE_NO = CCP.CS_CASE_NO
LEFT OUTER JOIN PATIENT PM ON CONVERT(VARCHAR,PM.PATIENT_ACCOUNT) = CCP.CS_PATIENT_ACCOUNT
LEFT OUTER JOIN PROVIDERS LP ON LP.PROVIDER_CODE = CONVERT(BIGINT,CCP.CS_PROVIDER_CODE)
LEFT OUTER JOIN PRACTICES LPI ON LPI.PRACTICE_CODE = CONVERT(BIGINT,CCP.CS_PRACTICE_CODE)
LEFT OUTER JOIN INSURANCE_NAMES LI ON LI.INSNAME_ID = CONVERT(BIGINT,CCP.CS_INSURANCE_CODE)
LEFT OUTER JOIN PHONE_TYPES PT ON PT.PHONE_CODE = CCP.CS_REP_PHONE_TYPE LEFT OUTER JOIN CS_CASE_TYPES LCT
ON CCSI.CS_CASE_TYPE_ID = LCT.CS_CASE_TYPE_ID
WHERE CCSI.CS_CASE_NO LIKE +@CaseNo+ '%'
AND isnull(ccsi.cs_deleted,0)=0 AND ISNULL(CCP.CS_DELETED,0)=0 AND
ISNULL(CCC.CS_DELETED,0)=0 AND ISNULL(LCT.CS_DELETED,0)=0
END
--end
--else
--begin
-- false logic
ELSE IF(@Category!='')
BEGIN
SELECT CCSI.CS_RECEIVED_DATETIME,CCSI.CS_CASE_NO,CCSI.CS_CASE_TITLE, CCSI.CS_BILLING_COMPLIANCE_ID,
CCSI.CS_CASE_DETAIL,CCSI.CS_MAIL_ID, CCSI.CS_CASE_FROM_REP, CCSI.CS_USER_NAME,
CCSI.CS_NUMBER_OF_TIMES_CALLED,CCSI.CS_CASE_TYPE_ID,CCSI.PRIORITY_LEVEL, CCC.CS_CATEGORY_NAME,
PM.LAST_NAME + ',' + ISNULL(PM.FIRST_NAME,'') + ' [ '+ ISNULL(PM.EMAIL_ADDRESS,'') + ' ]' AS NAME,
ISNULL(PM.EMAIL_ADDRESS,'') AS PATIENT_EMAIL, CCP.CS_PRACTICE_CODE,
CCP.CS_PROVIDER_CODE,CCP.CS_PATIENT_ACCOUNT, CCP.CS_CLAIM_NO,CCP.CS_INSURANCE_CODE,
CCP.CS_RESPONSE_DATE , CCP.CS_REP_PHONE,CCP.CS_REP_PHONE_TYPE , CCP.CS_REP_EMAIL,CCP.CS_SEND_MAIL_TO,
CCP.CS_IS_SEND, CCP.CS_CREATED_DATE, CCP.CS_CREATED_BY, CCP.CS_MODIFIED_BY, CCP.CS_MODIFIED_DATE,
CCP.SHOW_ON_WEB,CCP.CS_CASE_STATUS ,CCP.CS_AUTHORIZE,CCP.CS_BILLING_COMPLIANCE , LP.PROVIDER_CODE ,
ISNULL(LP.PROVID_LNAME,'') + ',' + ISNULL(LP.PROVID_FNAME,'') AS PROVIDERNAME,
ISNULL(LP.EMAIL_ADDRESS,'') AS EMAIL_ADDRESS_PROVIDER, LPI.PRACTICE_CODE ,
ISNULL(LPI.PRAC_NAME,'') AS PRACTICENAME, ISNULL(LPI.EMAIL_CONTACT_PERSON,'') AS PRACTICE_EMAIL,
LI.INSNAME_DESCRIPTION, PT.PHONE_TYPE, LCT.CS_CASE_TYPE_DESCRIPTION
FROM CS_CUSTOMER_SUPPORT_INFO
CCSI LEFT OUTER JOIN CS_CASE_CATEGORIES CCC ON CCSI.CS_CASE_CATEGORY = CONVERT(VARCHAR(10),
CCC.CS_CATEGORY_ID) LEFT OUTER JOIN CS_CASE_PROGRESS CCP ON CCSI.CS_CASE_NO = CCP.CS_CASE_NO
LEFT OUTER JOIN PATIENT PM ON CONVERT(VARCHAR,PM.PATIENT_ACCOUNT) = CCP.CS_PATIENT_ACCOUNT
LEFT OUTER JOIN PROVIDERS LP ON LP.PROVIDER_CODE = CONVERT(BIGINT,CCP.CS_PROVIDER_CODE)
LEFT OUTER JOIN PRACTICES LPI ON LPI.PRACTICE_CODE = CONVERT(BIGINT,CCP.CS_PRACTICE_CODE)
LEFT OUTER JOIN INSURANCE_NAMES LI ON LI.INSNAME_ID = CONVERT(BIGINT,CCP.CS_INSURANCE_CODE)
LEFT OUTER JOIN PHONE_TYPES PT ON PT.PHONE_CODE = CCP.CS_REP_PHONE_TYPE LEFT OUTER JOIN CS_CASE_TYPES LCT
ON CCSI.CS_CASE_TYPE_ID = LCT.CS_CASE_TYPE_ID
WHERE CCC.CS_CATEGORY_ID LIKE +@CategoryID+ '%'
AND (CCSI.CS_Created_Date between '''+ @FromDate +''' AND '''+ @ToDate +''')
AND isnull(ccsi.cs_deleted,0)=0 AND ISNULL(CCP.CS_DELETED,0)=0
AND ISNULL(CCC.CS_DELETED,0)=0 AND ISNULL(LCT.CS_DELETED,0)=0
END
--end
any suggestions??????????
|
|
|
|
|
First of all install SQL Assistant programm and format this query by pressing CTRL+F11 then query will looks properly rendered.
Better check joins on query and data on tables if they match with each other.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
SELECT
CCSI.CS_RECEIVED_DATETIME,
CCSI.CS_CASE_NO,
CCSI.CS_CASE_TITLE,
CCSI.CS_BILLING_COMPLIANCE_ID,
CCSI.CS_CASE_DETAIL,
CCSI.CS_MAIL_ID,
CCSI.CS_CASE_FROM_REP,
CCSI.CS_USER_NAME,
CCSI.CS_NUMBER_OF_TIMES_CALLED,
CCSI.CS_CASE_TYPE_ID,
CCSI.PRIORITY_LEVEL,
CCC.CS_CATEGORY_NAME,
PM.LAST_NAME + ',' + ISNULL(PM.FIRST_NAME, '') + ' [ ' + ISNULL(PM.EMAIL_ADDRESS,
'') + ' ]' AS NAME,
ISNULL(PM.EMAIL_ADDRESS, '') AS PATIENT_EMAIL,
CCP.CS_PRACTICE_CODE,
CCP.CS_PROVIDER_CODE,
CCP.CS_PATIENT_ACCOUNT,
CCP.CS_CLAIM_NO,
CCP.CS_INSURANCE_CODE,
CCP.CS_RESPONSE_DATE,
CCP.CS_REP_PHONE,
CCP.CS_REP_PHONE_TYPE,
CCP.CS_REP_EMAIL,
CCP.CS_SEND_MAIL_TO,
CCP.CS_IS_SEND,
CCP.CS_CREATED_DATE,
CCP.CS_CREATED_BY,
CCP.CS_MODIFIED_BY,
CCP.CS_MODIFIED_DATE,
CCP.SHOW_ON_WEB,
CCP.CS_CASE_STATUS,
CCP.CS_AUTHORIZE,
CCP.CS_BILLING_COMPLIANCE,
LP.PROVIDER_CODE,
ISNULL(LP.PROVID_LNAME, '') + ',' + ISNULL(LP.PROVID_FNAME, '') AS PROVIDERNAME,
ISNULL(LP.EMAIL_ADDRESS, '') AS EMAIL_ADDRESS_PROVIDER,
LPI.PRACTICE_CODE,
ISNULL(LPI.PRAC_NAME, '') AS PRACTICENAME,
ISNULL(LPI.EMAIL_CONTACT_PERSON, '') AS PRACTICE_EMAIL,
LI.INSNAME_DESCRIPTION,
PT.PHONE_TYPE,
LCT.CS_CASE_TYPE_DESCRIPTION
FROM
CS_CUSTOMER_SUPPORT_INFO CCSI
LEFT OUTER JOIN CS_CASE_CATEGORIES CCC ON CCSI.CS_CASE_CATEGORY = CONVERT(VARCHAR(10), CCC.CS_CATEGORY_ID)
LEFT OUTER JOIN CS_CASE_PROGRESS CCP ON CCSI.CS_CASE_NO = CCP.CS_CASE_NO
LEFT OUTER JOIN PATIENT PM ON CONVERT(VARCHAR, PM.PATIENT_ACCOUNT) = CCP.CS_PATIENT_ACCOUNT
LEFT OUTER JOIN PROVIDERS LP ON LP.PROVIDER_CODE = CONVERT(BIGINT, CCP.CS_PROVIDER_CODE)
LEFT OUTER JOIN PRACTICES LPI ON LPI.PRACTICE_CODE = CONVERT(BIGINT, CCP.CS_PRACTICE_CODE)
LEFT OUTER JOIN INSURANCE_NAMES LI ON LI.INSNAME_ID = CONVERT(BIGINT, CCP.CS_INSURANCE_CODE)
LEFT OUTER JOIN PHONE_TYPES PT ON PT.PHONE_CODE = CCP.CS_REP_PHONE_TYPE
LEFT OUTER JOIN CS_CASE_TYPES LCT ON CCSI.CS_CASE_TYPE_ID = LCT.CS_CASE_TYPE_ID
WHERE
CCSI.CS_CASE_NO LIKE +@CaseNo + '%' AND ISNULL(ccsi.cs_deleted, 0) = 0 AND ISNULL(CCP.CS_DELETED,
0) = 0 AND ISNULL(CCC.CS_DELETED,
0) = 0 AND ISNULL(LCT.CS_DELETED,
0) = 0
You tell me which is more readable.
I can't see anything that would stop the data being retrieved, have you checked that there is actually data in the table matching the criteria.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Very much readable 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
diyaa_08 wrote:
IF(@Case! ='' )
BEGIN
do stuff
END
ELSE IF(@Category!='')
BEGIN
do stuff
END
I'm not really an expert on stored procedures, but what will this do if @Case is '' and @Category is ''? In that case, neither IF block will get executed. What would the stored proc return in that scenario?
|
|
|
|
|
Oh yeah, and this:
AND isnull(ccsi.cs_deleted,0)=0
Is there an advantage to doing that rather than a simple IS NULL test?
I'm not saying it's wrong, I'm saying I don't understand whether one is better than another.
|
|
|
|
|
Hello, This is Vikash Gohil.
I have a Software which is using MSDE Database (SQL2000).
When the Software is run for the first time, it checks for the database name and if not found attaches the MDF and LDF files that are present in the "<Application Path>\Data" folder.
I don't face any problem in this setup.
Now I want to move from MSDE (SQl2000) to SQLExpress (SQL2005).
I Created the MDF and LDF Files, Installed SQLExpress and My Software.
After this I Copied the MDF and LDF Files in "<Application Path>\Data" Folder.
The problem is, when I start the Software the database files are attached, but it shows below message:
"Failed To Update Database "<DatabaseName>", the Database is Read-Only."
And the software does not start.
I want to know why this happens?
I do not face such problems in MSDE.
I have Searched for this on net and found that the SqlExpress should have rights for the folder where MDF and LDF Files Exists. But I don't know how to Set rights for my "Application Path\Data" Folder.
I am totally new to SQL 2005.
Please someone can help me in this matter.
Any help would be greatly appreciated.
Awaiting a reply, Thanks in Advance.
|
|
|
|
|
Check the dumb things first.
Are the files/folder read only
Does your app have permissions on the folder (some companies set admin rights to the program files folder)
Have you set up the user correctly
Is your connection string correctly formatted
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello, MyCroft.
Thanks for the reply.
Are the files/folder read only?
When I put the same files in the folder where system database files are present, i.e. Master, Model, etc., and then manually attach these files using Sql express Management studio, then there is no problem.
The problem of Read-Only occurs only when data files are present in some other folder.
Does your app have permissions on the folder?
During Installation of the software, we have set the permissions as Everyone, so I don't think that is the problem. Also we have been using this with MSDE the same way and there are no problems.
Have you set up the user correctly?
We Use default 'sa' user for making connections.
Also the ConnectionString is absolutely proper as it works fine with MSDE.
|
|
|
|
|
Ok so the dumb ones are covered. It still looks like 1 is the cause of the problems. If you move to another folder and attach the files does the connection work OK then. I don't use express but with SQL Server the server instance has an identity/credentials and these are used to access the file system. I would presume permissions on the data/log folders is set up on install of the database. Other folders may need specific permissions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I have one Excel sheet "Student.xls", which has different worksheet as "Sheet1","Sheet2"..like that.
Consider now "Sheet1"; it has 3 columns - "Name", "Age", "Sex". All these columns has row entries.
Now I have one access database, "alldata.mdb". It has One table "Record" which has 3 fields as same as column name of Excel sheet-- "Name", "Age", "Sex".
I want to populate this access database with the entries of Excel sheet from "Sheet1".
Can someone let me know how to do this?
I tried this:
// first connected xl datasheet:
Dim filename As String = "C:\Student.xls"
Dim con As String
con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;"
Dim connection As OleDbConnection = New OleDbConnection(con)
connection.Open()
The above Excel connection was succesful, but how to import the data from this excel sheet into the access database.
Please suggest.
Thanks,
R.S.
|
|
|
|
|
Select * FROM [Sheet1$]
or however your query looks like.
Enter the data into a dataset. Open a new connection to the alldata.mdb and copy the data from the dataset.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Hi,
I have copied the data in a new dataset "ds1". Please tell me how to copy this "ds1" contents into Access Database. I am trying with following:
Dim da2 As New OleDbDataAdapter
Dim conn As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand()
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\alldata.mdb;")
cmd = New OleDbCommand("SELECT * FROM Denver", con)
da2 = New OleDbDataAdapter(cmd)
da1.Fill(ds1, "Denver")
But not able to populate the excel sheet.
Can You guide me how to do this??
Thanks,
R.S.
|
|
|
|
|
Sorry By mistake.... I made the statement
"But not able to populate the excel sheet."
What i was:
"But Not able to populate the Access Database".
Thanks
R.S.
|
|
|
|
|
hi dearest brothers/sisters
i have created trigger in MSSQL SERVER.
CREATE TRIGGER IDx on Table2 after INSERT
AS
insert into Table2 (RefNo) select RefNo from Table1
i need Table2 Foreign key RefNo to be filled no of times which means
eg. table2 may need RefNo to be filled in three rows.
currently i get single row others kept NULL values
help me plz
thanks
so much of happy ending...
|
|
|
|
|
|
Thanks Mohsiul Haque . its really helpful and learned some.
so much of happy ending...
|
|
|
|
|
Triggers are EVIL. Although this is a valid use of a trigger I would suggest using a normal stored proc and surrounding the inserts with a transaction.
Begin Tran
insert parent record
get scope_identity for the inserted record
begin a loop
insert the number of records in the child tables
end the loop
commit the transaction
return the id to the calling method
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks to Mycroft Holmes . its really helpful !
so much of happy ending...
|
|
|
|
|
A query that used to take milliseconds is now taking 3 seconds to run
The table only has 7000 records and I rebuilt the one index that it uses
I am thinking if I drop/create the table the problem may get fixed, but the table has several dependent views, etc. Will each dependent item need to be created again?
Thanx,
>>>-----> MikeO
|
|
|
|
|
|
In MS SQL Server Management Studio,
1) create a new query
2) paste the query in the query window
3) under the Query menu select the option to "Include Actual Execution Plan"
4) Execute the query.
Check out the execution plan results and you want to avoid "scans", this means that it is reading the table or index sequentially.
Also under the Query menu, choose the "Include Client Statistics" and check those values for anything that may seem out-of-whack.
Pleasse reply to this posting so that I know if this was helpful.
david
|
|
|
|
|
I followed the steps in your reply. Nothing looks to be wrong with the query. It even appears to execute normally in Management Studio. When I execute it in the code on the client though it takes about three seconds.
The client app has been running successfully for about four years. Now it is getting bogged down by two queries. Both do field sums. Any ideas what could change on a server or connection that would slow down a SUM operation?
This is a close approximation of the query that is taking so long:
SELECT SUM(WorkSecs) as WorkTime, SUM(RunSecs) as RunTime
FROM tblData WHERE Machine = 1 and Operator = 1
Thanx,
>>>-----> MikeO
|
|
|
|
|
I need to know how to archive data from particular database when it is growing above 1GB.Also it has to be restored when it was needed.Any Idea
|
|
|
|
|
For most of the time Archiving database can be done by using SQL Jobs, I think at first you need to create a stored procedure which will fetch the data and dump into the archive database and then just call it from SQL Jobs with a given schedule.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
