cant get around @@TEXTSIZE

Hello all,

Having a problem getting around @.@.TEXTSIZE. Shared hosting, SQL Server 2000. @.@.TEXTSIZE Value appears to be 64000, or anyway that's what I get in a query from ColdFusion. Which would explain why some of my very long values in my text column (up to 125K) are being truncated on SELECT.

But when I crank up Management Studio and query for @.@.TEXTSIZE I get the full 2GB max. So why does a SELECT in Management Studio of a row with one of my very long values still return truncated results?

I know that my full content is indeed in the database; READTEXT Research.HTMLContent @.ptrval 124484 100 gets me the last 100 characters of the full content as expected.

Sticking SET TEXTSIZEs into my queries (in ColdFusion app or Management Studio) has no effect. Are there other settings that can behave this way that I should be looking into?

Any thoughts appreciated; I've got a busted production application and an unhappy client.Found the answer, which I'll post here for the benefit of future Googlers--

It wasn't a SQL Server issue, but a ColdFusion one. There is a ColdFusion setting, in ColdFusion Administrator under Edit Datasource, called "Enable retrieval of long text." I'm not sure whether it's on or off by default, but in my case it was off. In which case, retrieval of SELECTS of text is limited to a data size specified on the same screen, labeled something like "Long text buffer limit."

Behind the scenes it does appear that ColdFusion is manipulating @.@.TEXTSIZE with this long text buffer limit, since a SELECT @.@.TEXTSIZE sent via a ColdFusion query returns the limit value.

Cant delete single record? HELP!

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIAAny triggers on the table?|||Any triggers on the table?
No, no triggers on the table.|||Any triggles on this table?
No triggles either ;)|||what does the execution plan looks like for the delete statement?

Are there foreign key constraints on Invoices table ?
run something similar to the following:

select object_name(constid) as FkeyName,
object_name(fkeyid) as DependentTable from sysforeignkeys where rkeyid=object_id('Invoices')

Is anything locking Invoices table when you try the delete?

simas|||It won't even pull up an estimated execution plan. It just sits there doing nothing. There are several foreign keys, but I'm deleting from those tables prior to this delete, and they all delete without a hitch. It's just this table. I don't see anything locking the Invoices table.|||Have you ran a DBCC CHECKDB to see if there are any integrity issues?|||use <Your_Database_Name>
go
dbcc opentran
go

You have to have an open transaction that references this table. Oh, and do KILL your DELETE before you issue the DBCC. Make sure that any rollback (doubt it) completes before you fire DBCC.|||CHECKDB found 0 allocation errors and 0 consistency errors in database 'Accounting'.

Nice thought though.|||This is trying to pull a rabit out of the hat, but try to run UPDATE STATISTICS on the table. Also, try to drop and recreate the PRIMARY KEY index on that table. Then, see if you can delete the rows.

Also, I don't know why in the world I didn't suggest this earlier. In Query Analyzer before you do anything else, run: SELECT @.@.SPID. Set up a profiler to capture all activity using that SPID as a filter. In particular, look for SQL:Batch Started; SQL:Batch Completed; all errors, all recompiles, all locks and blocks; RPC:Started; RPC:Completed. See if you can see anything in the profiler that would lead to this problem.

Also, when this is running, if you run this in Query Analyzer do you get any results?

SELECT * FROM master..sysprocesses WHERE blocked <> 0|||UPDATE: DELETING THE PRIMARY KEY AND RECREATING IT WORKED. (no comment on how bad I think it is that this should ever have to be done, but it's fixed so I won't complain)

Mad Thanks go out to those that helped me out with this!

Special Thanks to Derrick! :D|||And you're sure you're on the latest service packs, critical updates, etc for Windows and SQL Server? Make sure the machine that has Query Analyzer on it has also been updated.|||SP3a on both, I'll have to check on the critical updates (since my boss thinks the internet is a fad). Thanks again for everything!

cant create object on sql server

When i try to deploy my project or the cube within it i first i get a message 'The server content appears out of date'

then in the error list it tells me 'user does not have permission to create object on [servername] or object does not exist.

Why is this?

Should i create a database first in sql managment studio with a particular name, the name of the project for example. I have tried different impersonation modes...

What login account does analysis services run under when it tries to connect to deploy?

Does it try to create an analysis services database?

Any advise or pointers would be greatly appreciated. Thanks alot.

Jules

The project deployment in BI Dev Studio is happening under your account. You need to have administrative rights ( in many cases ) to deploy your project.

By default all local machine Administrators are also administrators of Analysis Server.

Check check your project properties to see which server you deploy your project to.

You can also open SQL Management studio to see the state your database after your deployed it to Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cant create new database / CREATE DATABASE permission denied in database master (error 262

I am using SQL express and Visual web developer on windows Vista.

When I try to create a new database the following message appears.

CREATE DATABASE permission denied in database master (error 262)

I log on to my computer as an administrator.

Help appreciated

Prontonet

How do you login to sql server? Do you use sql authentication? If so, check the account you login with has appropriate permissions. Or are you using windows authentication?

|||

Can you login to database engine and read data anyway??

You need "CREATE DATABASE" permission to create new database. However, to make things easier, i would suggest you use sys_admin permission over your database.Open sql express and click "security"-->Logins, see if your login name is listed there. Rigth click your user name and select "properties", click "server roles" or "User Mappings" to see which role has been assigned to your account. If you are not a sys_admin/db_owner role, login agin using a dbo account(if you have one) and assign you account a sys_admin/dbo permission.

Hope my suggestion helps