Database

8-Jun-10 15:02

I suspect you checked append when selecting the destination (in SSMS). When restoring, via SSMS, you can select the backup you want to restore. I am surprised that restoring both set doubles the size of your database though, I would expect it to overwrite.

Never underestimate the power of human stupidity
RAH

Re: Database Size

It_tech8-Jun-10 21:26

It_tech

8-Jun-10 21:26

Thanks so much for your response.

I did run the database tuning advicer and sql profiler a couple of weekes ago.Can this increase my transaction log files termendeously.At the moment the log files is about 1.1GB,Is there any tool i can use to get the history of this log file in the last few months?

Other wise ,How can i shrink the logfile? Is there any other tool i can use which can tell me why database has grown by double size in only few weeks.

Many thanks.

Re: Database Size

Mycroft Holmes8-Jun-10 22:35

unresolved external symbol sqlcxt

8-Jun-10 22:35

Yes you can shrink the log file - look for shrink in BOL.

If you have changed your logging method from simple to full then that will certainly make a big difference.

Never underestimate the power of human stupidity
RAH

ravi1204868-Jun-10 1:02

ravi120486

8-Jun-10 1:02

Hi,
I am getting following error:

error LNK2001: unresolved external symbol "void __cdecl sqlcxt(void * *,unsigned int *,struct sqlexd *,*struct sq*lcxp const* )" (?sqlcxt@@YAXPEAPEAXPEAIPEAUsqlexd@@PEBUsqlcxp@@@Z)

But after adding extern "C" in the PL/SQL code

extern "C"
{
 void sqlcxt (void **, unsigned int *,+
 struct sqlexd *, const struct sqlcxp *);+
 void sqlcx2t(void **, unsigned int *,+
 struct sqlexd *, const struct sqlcxp *);+
 void sqlbuft(void **, char *);+
 void sqlgs2t(void **, char *);+
 void sqlorat(void **, unsigned int *, void *);+
}

I am getting the following error:

error LNK2001: unresolved external symbol sqlcxt

Please Help!!!!!!

I am using Oracle 9i client on x64 platform.

Re: unresolved external symbol sqlcxt

Chris Meech8-Jun-10 7:14

Chris Meech

8-Jun-10 7:14

There should be a 'lib' directory under the folder where the Oracle 9i client is installed. Add that directory to the directories that are searched during linking. 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]

Triggers

Morgs Morgan7-Jun-10 3:28

Morgs Morgan

7-Jun-10 3:28

Hi guys,
I would like to create a trigger that must execute at a certain time.e.g.
'2010-06-08 12:00:00'
I know there are stuff such as insert, update, and delete triggers. But is there
a way that i can set a time when that trigger should fire....other than using sql jobs

Please anyone help me.
Thanks, Morgs

Mycroft Holmes7-Jun-10 4:33

7-Jun-10 4:33

MorgSim wrote:
But is there
a way that i can set a time when that trigger should fire

Not as far as I know. A trigger, by definition, is activated by an event, how are you going to activate the trigger if you don't use a job. You are trying to use the wrong tool for the wrong job. Just use a procedure and call it using a job like it is designed to!

Never underestimate the power of human stupidity
RAH

Morgs Morgan7-Jun-10 5:42

Morgs Morgan

7-Jun-10 5:42

Ok thanks guys, i was also actually not sure about this. i will try some other logic instead

dan!sh 7-Jun-10 5:16

dan!sh

7-Jun-10 5:16

You do not need a trigger here. All you need is a job that executes a stored procedure or a set of queries at the mentioned time.

Need help with an oracle UPDATE [modified]

Tim Carmichael7-Jun-10 5:49

Tim Carmichael

7-Jun-10 5:49

If you don't want to use a SQL job, use a scheduled task instead; it is executed outside of the control of the SQLServer Agent Service.

Not necessarily pretty, but provides a different level of control.

Tim

Kalyan_A6-Jun-10 23:02

6-Jun-10 23:02

I have two tables with the following structure.

Table 1

Name Field1

Table 2

ID Name Field1

1    n1     NULL
2    n2     NULL	
3    n3     NULL

Usually, Table1.Name and Table1.Field1 have a 1..1 relationship. however in very-rare circumstances they do tend to have 1..n

I need to update Table2.Field1 with Table1.Field1; In SQL Server we could write the following update statement to satisfy our business conditions.

UPDATE Table2 SET Table2.Field1 = T1.Field1<br />
	FROM Table1 t1, Table2 t2<br />
	WHERE t1.Name = t2.Name;<br />

In oracle, we were having the below statement.

UPDATE Table2 SET Field2 = (SELECT t1.FIELD1 FROM Table1 t1, Table2 t2<br />
	WHERE t1.Name = t2.Name);

This statement works for Table1 having 1..1 relationship between Table1.Name and Table1.Field1
For 1..n relationship the ORACLE version fails with the following error - ORA-01427: single-row subquery returns more than one row

The SQLServer sql works for both the cases 1..1 and 1..n; given the nature of the join.

Could someone guide me with appropriate sql for the above issue?
I am looking for a bulk update statement rather than using PL/SQL to go through each recrod in Table2.

Thanks for your help.
Kalyan

modified on Monday, June 7, 2010 10:55 AM

Mycroft Holmes6-Jun-10 23:33

6-Jun-10 23:33

Fairly obvious, your sub select statement is returning 2 rows for n2, this is illegal. try putting DISTINCT in there

UPDATE Table2 SET Field2 = (SELECT DISTINCT t1.FIELD1 FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name);

Never underestimate the power of human stupidity
RAH

Kalyan_A6-Jun-10 23:41

6-Jun-10 23:41

Thanks for your reply.

I do know that multiple values is what is causing this issue.
Using DISTINCT would not work as the values being returned are distinct already. It would still return 2 rows for n2

This issue is due to ORACLE not supporting the same syntax as SQLSERVER. (as far as i know)

I am more comfortable with SQLServer than ORACLE and hence was checking if anyone else came across this issue.

Kalyan

Mycroft Holmes7-Jun-10 0:53

7-Jun-10 0:53

Sorry, looking at it from the wrong end.

What do you expect the table2 field2 (missing from design given) to contain b or c and what does it contian when you use SQL Server, I'm betting tt has c.

Does Oracle not support update with a join, try using INNER JOIN style instead of the tables in the where clause (horrible format)

No matter how you work it your data structue sucks. the relationship is screwed and I consider Oracle correct in spitting the dummy.

Never underestimate the power of human stupidity
RAH

Kalyan_A7-Jun-10 5:12

7-Jun-10 5:12

you are right - (for now) its not important as to which value gets updated. The SQLServer version of the script simply updates the t2.Field with the last found record. So for 'n2' t2.Field1 will always end-up with value 'c'
I was looking for an equivalent in ORACLE so that it will always update with the last row found.

The actual issue is much bigger and i was looking for a quick-fix without introducing a major change.
Looks like we need to address the issue with the current design before spending any further time on this issue,

Thanks for your inputs.

Cheers
Kalyan

David Skelly7-Jun-10 2:50

David Skelly

7-Jun-10 2:50

I'm confused. Given the example data you have given above, what value would you expect to see in Table2.Field1 for row id 2? It could be either b or c. Which one do you want and how would you distinguish between them?

If you don't care, then you could just say SELECT MAX(t1.FIELD1). That would give you one value.

As far as I can see with your current SQL Server statement you have no control over which value goes in there, so I'm guessing it's not important to you.

Kalyan_A7-Jun-10 5:11

7-Jun-10 5:11

RTF to TEXT

Sebastian T Xavier6-Jun-10 20:45

6-Jun-10 20:45

I have a RTF field in my SQL 2005 table, I need to convert it to Text and display it. After a quick research I got the following method... create function dbo.RTF2TXT(@in varchar(8000)) RETURNS varchar(8000) AS BEGIN

DECLARE @object int DECLARE @hr int DECLARE @out varchar(8000)

-- Create an object that points to the SQL Server EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT EXEC @hr = sp_OADestroy @object return @out

END GO

select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1 aaa}')

But Here I am getting only NULL as result... What could be the issue, please suggest

Thanks

Mycroft Holmes6-Jun-10 23:29

6-Jun-10 23:29

Well if the line wrap has not deceived me then the function does nothing because it is commented out.

You seem to have 4 EXEC strung together in a single line that is commented out!

Do you have any idea what you are doing, sp_OACreate is an OLE Automation procedure. I can't use it because of security permissions so I would not be surprised if that will also kill this.

Never underestimate the power of human stupidity
RAH

Sebastian T Xavier6-Jun-10 23:40

6-Jun-10 23:40

Thanks for your response.
I don't think, the line wrap will create issues. I have removed those comments & tried it.
Actually I was trying to convert the Rich text type string to plain text while
fetching records.
Can you suggest any better approach to convert a string from rich text to plain text in sql server.

Thanks once again

Sebastian

Sebastian T Xavier6-Jun-10 23:43

6-Jun-10 23:43

Hello, One more thing

can't we use following code to allow execution of sp_OACreate

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Thanks

Sebastian

J4amieC6-Jun-10 23:42

J4amieC

6-Jun-10 23:42

sp_OACreate is the devil.

This is NOT something that a database should be doing - load the RTF data to your business layer and convert it there.

Sebastian T Xavier6-Jun-10 23:51