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

Re: Storing images, documents etc in a database

Henry Minute17-Dec-10 13:51

Henry Minute

17-Dec-10 13:51

If there is any chance that this could move to SqlServer 2008, you might consider FileStream Storage[^].

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

SSIS package converting varchar(MAX) to nvarchar(50)

raghvendrapanda15-Dec-10 22:36

raghvendrapanda

15-Dec-10 22:36

hy,
I have created an ssis package which tranfers data from one database to another,first i drop the tables and then recreat it before inserting data.
I have a cloumn which has a datatype of varchar(MAX) and on re creation becomes nvarchar(50).
what should i do to avoid it.

Thanks & Regards,
Raghvendra Panda

\r\n ignored when executing queryies using ADO

Fayu14-Dec-10 13:01

Fayu

14-Dec-10 13:01

Hi,

I am having an issue executing a create procedure query to Oracle 10g. I am building the query in C++ (Exhibit A) and executing it using ADO (Exhibit B). In most systems, which I do not manage, the query is build and executed on oracle correctly (see Exhibit D). However, I have this one Oracle server that is, for some odd reason, ignoring the new line feeds (\r\n) (see Exhibit C).

Is there a setting in Oracle that I am over looking?

Your help is appreciated. Thanks in advance. Let me know if you need any more info.

EXHIBIT A - Code in C++ to build query:

CString cstrTmp;cstrTmp = 
"CREATE OR REPLACE PROCEDURE \"DoSomething\" \r\n" 
"(\r\n" " pTxt nvarchar2\r\n" ")\r\n" 
"as\r\n" 
"\r\n" 
"vXml XmlType;\r\n" 
"vTempCounter int;\r\n" 
"\r\n" "begin\r\n" 
"     --my comment\r\n"
"     dbms_output.put_line('test'); 
"end;\r\n"

EXHIBIT B - This is the code that executes the query above to the database (in c++):

//where cBuffer is the query above
if(pCommand == NULL) 
{ 
     pCommand.CreateInstance(__uuidof(Command)); 
     pCommand->CommandType = adCmdText; 
} 
pCommand->CommandText= _bstr_t(*cBuffer); 
pCommand->CommandTimeout = m_pConn->CommandTimeout; 
pCommand->ActiveConnection = m_pConn; 
pCommand->Execute(NULL, NULL, adExecuteNoRecords); 
pCommand = NULL;

EXHIBIT C - This is how the query is seen in the procedure - In one single line. \r\n is ignored:

CREATE OR REPLACE PROCEDURE "DoSomething"(pTxt nvarchar2)asvXml XmlType;vTempCounter int;BEGIN     --myComment     dbms_utput.put_line('test');END;

EXHIBIT D - It should show up like this after it is created on the database:

CREATE OR REPLACE PROCEDURE "DoSomething"
(pTxt nvarchar2)
asv
Xml XmlType;
vTempCounter int;
BEGIN 
     -- my comment
     dbms_output.put_line('test');
END;

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

Chris Meech15-Dec-10 3:04

Chris Meech

15-Dec-10 3:04

For the one Oracle server that produces your Exhibit C, is that a Windows server or some other flavour OS that is hosting Oracle.

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 5:23

Fayu

17-Dec-10 5:23

Its a Windows 2008 machine.

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

Chris Meech17-Dec-10 9:39

Chris Meech

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 | :)

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