Database Discussion Boards

Re: \r\n ignored when executing queryies using ADO

Chris Meech17-Dec-10 9:39

17-Dec-10 9:39

Since it is Windows, it's more probable that it is not dropping your escape characters. I was just wanting to see if some flavour of Unix was involved and somehow along with translating the characters, it was also dropping them. Not sure what else you can look at, though. Sorry. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: \r\n ignored when executing queryies using ADO

Fayu17-Dec-10 15:26

Fayu

17-Dec-10 15:26

My first thought was unix as well. The user swears that it's a windows. Is there a query I can run to figure out what is the server is running on?

Replace with integer (1,2,3...) Problem in SQL Query [modified]

Varun Sareen14-Dec-10 0:40

Varun Sareen

14-Dec-10 0:40

Dear Friends,

I have a query in which there is a sub query that is fetching a no of rows (single column); it may be one,two..upto six (not more than that). It is using for xml path( '' ) feature of SQL server in order to display the values as comma separated values.

The query is as follows:-

SQL

select distinct (select top 1 CLAIMMASTERID from ClaimMaster where encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6') ,EVC.EncounterID,EVC.CPT,(select (select ICDCode + ',' as [text()] from VisitTypeICDCPT soi where soi.VisitTypeID = t.VisitTypeID and VisitTypeID=VTI.VisitTypeID and encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6' order by VisitTypeID for xml path( '' )) from (select distinct VisitTypeID from VisitTypeICDCPT where VisitTypeID=VTI.VisitTypeID and encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6') as t )DiagnosisCode,EVC.Fee,convert(varchar(10),'2010-12-14 00:00:00.000',101) from EncounterVisitCode EVC inner join VisitTypeICDCPT VTI on EVC.encounterid=VTI.encounterid where EVC.encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6'

Here the sub query(starting from :-(select (select ICDCode...)) which is fetching the data from the database and the where "Diagnosis Code" is the column name. The data query is fetching is like this:- 004.12,001.20,...upto six values (may be less).

Here i want the values to be replaced by integer values depending upon the no of values being fetched by sub-query i.e., 1,2, if values in no. are two, 1,2,3, if values in no. are three and so on...

I am not able to find any way out to solve this problem. If someone could help me resolve this problem i would be very grateful to him/her.

Thanks

Varun Sareen

modified on Friday, December 17, 2010 6:53 AM

Re: Replace with integer (1,2,3...) Problem in SQL Query

Hiren solanki14-Dec-10 0:57

Hiren solanki

14-Dec-10 0:57

Please wrap your query in <pre lang="sql"><pre> TAG. so that we can see something from that mess.

Regards,
Hiren.
Microsoft Dynamics CRM

My Recent Article: - Way to know which control have raised PostBack[^]

Re: Replace with integer (1,2,3...) Problem in SQL Query

Corporal Agarn14-Dec-10 3:26

Corporal Agarn

14-Dec-10 3:26

I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries.

Re: Replace with integer (1,2,3...) Problem in SQL Query

Jörgen Andersson14-Dec-10 10:04

Jörgen Andersson

14-Dec-10 10:04

djj55 wrote:
I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries.

Agreed!

But reformatting takes 10 seconds, so if someone else wants to give it a shot:

SELECT DISTINCT (SELECT TOP 1 claimmasterid 
                 FROM   claimmaster 
                 WHERE  encounterid = '87B6D11C-3680-44A5-B1DB-AB16B3907AA6'), 
                evc.encounterid, 
                evc.cpt, 
                (SELECT (SELECT icdcode + ',' AS [text()] 
                         FROM   visittypeicdcpt soi 
                         WHERE  soi.visittypeid = t.visittypeid 
                                AND visittypeid = vti.visittypeid 
                                AND encounterid = 
                                    '87B6D11C-3680-44A5-B1DB-AB16B3907AA6' 
                         ORDER  BY visittypeid 
                         FOR XML PATH( '' )) 
                 FROM   (SELECT DISTINCT visittypeid 
                         FROM   visittypeicdcpt 
                         WHERE  visittypeid = vti.visittypeid 
                                AND encounterid = 
                                    '87B6D11C-3680-44A5-B1DB-AB16B3907AA6') AS t 
                ) 
                diagnosiscode, 
                evc.fee, 
                CONVERT(VARCHAR(10), '2010-12-14 00:00:00.000', 101) 
FROM   encountervisitcode evc 
       INNER JOIN visittypeicdcpt vti 
         ON evc.encounterid = vti.encounterid 
WHERE  evc.encounterid = '87B6D11C-3680-44A5-B1DB-AB16B3907AA6'

"When did ignorance become a point of view" - Dilbert

Re: Replace with integer (1,2,3...) Problem in SQL Query

Varun Sareen16-Dec-10 0:32

Varun Sareen

16-Dec-10 0:32

Dear djj55,

I have formatted the query. Please go through it and if you have any answer to it then kindly let me know.

Thanks & Regards

Varun Sareen

Re: Replace with integer (1,2,3...) Problem in SQL Query

Hiren solanki16-Dec-10 1:54

Hiren solanki

16-Dec-10 1:54

I think you haven't applied lang="sql" Yet. It's not indented.

Regards,
Hiren.

My Recent Article: - Way to know which control have raised PostBack
My Recent Tip/Trick: - Remove HTML Tag, get plain Text

Re: Replace with integer (1,2,3...) Problem in SQL Query

Varun Sareen17-Dec-10 0:56

Varun Sareen

17-Dec-10 0:56

Dear Hiren,

I have made the changes as required.

Kindly provide me with some answer, if you have.

Thanks

Varun Sareen

Re: Replace with integer (1,2,3...) Problem in SQL Query

Hiren solanki17-Dec-10 1:16

Hiren solanki

17-Dec-10 1:16

You can use ROW_NUMBER a Inbuilt ranking function in SQL to get only row numbers.

See MSDN[^].

Regards,
Hiren.

My Recent Article: - Way to know which control have raised PostBack
My Recent Tip/Trick: - Remove HTML Tag, get plain Text

Re: Replace with integer (1,2,3...) Problem in SQL Query

Corporal Agarn17-Dec-10 3:38

Corporal Agarn

17-Dec-10 3:38

As stated before:
"I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries."

Sorry

PMT function IN ORACLE OR SQL

ShaktisinhRathod13-Dec-10 23:05

ShaktisinhRathod

13-Dec-10 23:05

Dear Sir,
Greetings Of The Day,
PMT function which Calculates the payment for a loan based on constant payments and a constant interest rate.
I Need To Create PMT function In oracle.
Can anyone guide me for the same.
Thanks

Re: PMT function IN ORACLE OR SQL

Hiren solanki13-Dec-10 23:23

Hiren solanki

13-Dec-10 23:23

You could have search Google, having a lots of result on it.

See THIS[^].

Regards,
Hiren.
Microsoft Dynamics CRM

My Recent Article: - Way to know which control have raised PostBack[^]

The permissions granted to user 'domain\user' are insufficient for performing this operation. (rsAccessDenied)

vinu.111112-Dec-10 23:44

vinu.1111

12-Dec-10 23:44

hi... to all

i've two system one's domain name is sam and my domain name is ram..

i've deployed my reports in ram system i'e in my system URL (http://ram/reports)

it prompt for user name and password.. and i'll give windows crediantial which works fine..

now when i enter the same URL in sam's system it will again prompt for user name

and password if i give my windows crediantial then it work.. but if i give his crediantial the following

error is displayed " (The permissions granted to user 'domain\user' are insufficient for performing this operation. (rsAccessDenied)"...

now how to create his user name that is sam and password ..?

when i try to create a new role assignment by giving [domainname\sam] then [rsunknown] error is displayed..

and i have tried to create a new user account in my system only..

and when i create a new role it has created without any error..

but when i run the URL (http://ram/reports) prompts for uname and pwd if i enter sam and pwd

again the same error is displayed...

please help me..

Merge Replication Conflict

Abdul Rahman Hamidy12-Dec-10 22:12

Abdul Rahman Hamidy

12-Dec-10 22:12

Dear All,
I configured merge replication between 2 Servers which works OK, but all the time I am getting conflict only in one of my table. more than 1000 records conflicts. I Googled but no solution which lets to do remove these conflicts,
Is there any way to resolve conflicts automatically without going to VIEW Conflicts in Publication and basically what causes the conflict only in that table.

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

read & update using reader

Jassim Rahma12-Dec-10 3:00

Jassim Rahma

12-Dec-10 3:00

I have the following to read from database:

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_send_pending_sms", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_reader = sql_command.ExecuteReader();

after reading I want to update every record when send to set the field is_sent to True?

how can i do this? do I have to create another reader in the middle? can you please guide..

Thanks

Re: read & update using reader

RaviRanjanKr12-Dec-10 3:26

RaviRanjanKr

12-Dec-10 3:26

jrahma wrote:
after reading I want to update every record when send to set the field is_sent to True?

how can i do this? do I have to create another reader in the middle? can you please guide..

A little bit confusing about your question whatever,

In case of save,update and delete
you need to execute Transact SQL statement against connection.

Smile | :)

modified on Sunday, December 12, 2010 10:17 AM

Re: read & update using reader

Not Active12-Dec-10 3:50

Not Active

12-Dec-10 3:50

RaviRanjankr wrote:
need to execute Transact SQL statement against connection

Well, DAH!! Of course you do, but how does this answer the question?

I know the language. I've read a book. - _Madmatt

Re: read & update using reader

RaviRanjanKr12-Dec-10 4:04

RaviRanjanKr

12-Dec-10 4:04

Mark Nischalke wrote:
how does this answer the question?

Ok its my fault but can you explain what extra I have to mention here.

Re: read & update using reader

Not Active12-Dec-10 5:13

Not Active

12-Dec-10 5:13

RaviRanjankr wrote:
what extra I have to mention here

You provide enough to answer the question that was asked. Roll eyes | :rolleyes:

Take some time to read other responses around the forums and learn.

I know the language. I've read a book. - _Madmatt

Re: read & update using reader

RaviRanjanKr12-Dec-10 18:56

RaviRanjanKr

12-Dec-10 18:56

Thanks for your support and nice idea. Big Grin | :-D

Re: read & update using reader

MDNadeemAkhter13-Dec-10 17:34

MDNadeemAkhter

13-Dec-10 17:34

Re: read & update using reader

ShilpaKumari13-Dec-10 7:33

ShilpaKumari

13-Dec-10 7:33

Good sot, You need to elobrate it how they achieve

Re: read & update using reader

Not Active12-Dec-10 3:48

Not Active

12-Dec-10 3:48

You have been around here long enough to know to format code snippets properly. Edit your post to correct this.

How do you expect the database to know what operations have occurred after retrieving the data? You have to tell it, with a SQL statement of some type.

I know the language. I've read a book. - _Madmatt

Re: read & update using reader

PIEBALDconsult12-Dec-10 4:52

PIEBALDconsult

12-Dec-10 4:52

If you use stored procedures (and you shouldn't), don't prefix the names with "sp"[^].

When I implement a system like that, sending email or whatever, I put a Status field in the table to indicate ( 0=New , 1=Sending , 2=Sent ). When I select the New rows, I also set the Status to Sending, then when I'm done I set the Sending rows to Sent.

"UPDATE Email SET Status=1 WHERE Status=0 ; SELECT * FROM Email WHERE Status=1"

Send the emails.

"UPDATE Email SET Status=2 WHERE Status=1"

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.