Database

19-Dec-06 9:12

Hi - I just have a general SQL server question I'm hoping someone can help answer.

I have a table with a column of datatype TEXT called NOTE. I am trying to trim those records in the text field to say, 10000 characters where datalength(NOTE) > 10000.

for example, normally i would do something like 'update notetable set note = LEFT(note, 10000) where length(note) > 10000', but of course i can't do this with this datatype.

This datatype is a pain..Does anyone know the syntax for this?

Thanks!

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

PIEBALDconsult19-Dec-06 9:28

PIEBALDconsult

19-Dec-06 9:28

Convert to/from VARCHAR?

Marcus Spitzmiller19-Dec-06 9:35

19-Dec-06 9:35

No - it'll still ultimately reside in the same column - I just need to trim it.

any thoughts?

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

Eric Dahlvang20-Dec-06 3:51

Eric Dahlvang

20-Dec-06 3:51

I don't see why what PIEBALDconsult suggested will not work:

select cast(mytextcolumn as varchar(1000)) as MyTextColumnTrimmed from mytesttable

Try it out.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Marcus Spitzmiller20-Dec-06 5:25

20-Dec-06 5:25

I guess I misunderstood the suggestion.

However, I did:

select cast(NOTE as varchar(16349)) as NOTE
from S_NOTE_ACCNT
WHERE datalength(NOTE) > 16349

and I get:

The size (16349) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

So really, I need to trim the field to 16349, but apparently I can't this way...any thoughts?

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

Michael Potter20-Dec-06 10:26

Michael Potter

20-Dec-06 10:26

I don't have the data to test with but, I do believe that SUBSTRING works with the TEXT & NTEXT data types.

UPDATE
    noteTable
SET
    note = SUBSTRING(note,0,10000)
WHERE
    DATALENGTH(note) > 10000

Marcus Spitzmiller20-Dec-06 12:12

Questions about various DB connection methods

20-Dec-06 12:12

Thanks, that works. - Apparently I didn't look hard enough. I guess I didn't realize that substring existed. I tried substr and when that didn't work, I guess I thought that that function didn't exist.

Thanks again!

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

Jay Legue19-Dec-06 8:26

Jay Legue

19-Dec-06 8:26

Hello All,

I am looking to get some guidance and feed back from the community as to the prevalence and usage of the following technologies:

ODBC Descriptor support
OLE DB support
External Linked Server support
Data Link support for Data Transformation Services

Specifically I am hoping to find out when each technology should be used and the types of applications that use them. Additionally, I am wondering if there are alternatives to these technologies and why they may have been used instead.

Any guidance/feed back is appreciated.

Thanks,

---Jay

How do I format this...

Steven J Jowett19-Dec-06 4:43

Steven J Jowett

19-Dec-06 4:43

I have a stored procedure that can select data from several tables/views. One two of the parameters are DateTime fields. The SQL code is as follows :-

EXEC(N'SELECT * FROM ' + @objectName + ' WHERE DepotId = ' + @DepotId + ' AND [TIME] >= ' + @StartTime + ' AND [TIME] <= ' + @FinishTime + ' AND Intake = ' + @Intake + ';')

I am getting a error regarding the time parameters. I need to know how to format these for the where clause to work.

I have tried enclosing in double-quotes e.g. [TIME] <= "' + @StartTime + '" AND ...

I have also tried enclosiing hashes e.g. [TIME] <= #' + @StartTime + '# AND ...

neither works, please help

Steve Jowett

Re: How do I format this...

Steven J Jowett19-Dec-06 5:38

Steven J Jowett

19-Dec-06 5:38

I've sorted it. I should have done it like this...
[TIME] <= ''' + @StartTime + ''' AND ...

Steve Jowett

User defined function within another User Defined function

VK-Cadec19-Dec-06 3:41

VK-Cadec

19-Dec-06 3:41

Hi - As the subject lines suggests - can we do this in SQL 2005?

suppose I have a function_A - does certain calculations.

CREATE another_function_B<br />
(@driver int)<br />
RETURNS int<br />
AS<br />
BEGIN<br />
 declare @output as int<br />
   -- checks some certain type of conditions and then executes function_A<br />
   select @output = dbo.function_A(@driver)<br />
 return  @output<br />
END

Re: User defined function within another User Defined function

Colin Angus Mackay19-Dec-06 4:21

Colin Angus Mackay

19-Dec-06 4:21

I don't see why not. Why didn't you just try it and see?

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Re: User defined function within another User Defined function

VK-Cadec19-Dec-06 5:35

VK-Cadec

19-Dec-06 5:35

I did - it didn't get any results, which it should have. that is why decided to post a message.

I tried to debug the 'function' by using print statements but looks like that doesn't work. How could we write some debug statement?

Re: User defined function within another User Defined function

ed^nrgc20-Dec-06 6:29

ed^nrgc

20-Dec-06 6:29

Verify each item outside the UDF. That way you will see if it actually returns data.

link the foxpro(.dbf) table with mssql

Guru_yogi19-Dec-06 0:41

Guru_yogi

19-Dec-06 0:41

hi i have one table like table.dbf & i want to link this table to mssql
pls help me for that

Gurudatta B. Shelke

accessing sql server 2000 databse in LAN

abhijitw18-Dec-06 23:40

abhijitw

18-Dec-06 23:40

i have databse designed in sql server 2000
i want to access that database in LAN
plz help me in this matter

abhijit

Colin Angus Mackay19-Dec-06 0:21

Colin Angus Mackay

19-Dec-06 0:21

abhijitw wrote:
i have databse designed in sql server 2000
i want to access that database in LAN

In the Server element in the connection string just specify the name of the machine that the SQL Server is installed.

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

abhijitw19-Dec-06 17:48

abhijitw

19-Dec-06 17:48

yes sir i did the same.But, i got the following mesege:
SQL server does not exit or access denied
plz help me

abhijit

Paul Conrad19-Dec-06 19:15

Paul Conrad

19-Dec-06 19:15

abhijitw wrote:
ut, i got the following mesege:
SQL server does not exit or access denied

(1) Make sure the server name is correct in the connection string, or
(2) Check that the server is running, or
(3) Check any firewall issues

Hope this helps you out Smile | :)

Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon