Showing posts with label context. Show all posts
Showing posts with label context. Show all posts

Monday, March 26, 2012

How to use UPDATETEXT within the context of inserting a new row

All -

I'm trying to insert a row into a table. One of the columns in this table is a text field. I can't figure out how to use UPDATETEXT to insert data into this text field for a brand new row. The examples I see all require a text pointer to the column. However - I haven't even inserted the row - so how would I create a text pointer?

Here's the example from Books Online -

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @.ptrval 88 1 'b' GO

How would I create the text pointer (TEXTPTR) for a record that hasn't even been inserted yet?

Incidentally - my issues would be solved if the data for this text column was always less than 8K (then I could use varchar(8000) - but that isn't always the case.

Help!

I'm assuming that you're running SS2000.

UPDATETEXT operates on tables.

If the data you want to manipulate isn't in a table, where is it?

|||

Note that UPDATETEXT is going away in a future version of SQL Server. Use varchar(max) if this is a 2005 database and then use the .WRITE construct in the UPDATE statement.

Well, you can just use a text parameter to a stored procedure and just pass in all of the text... That is the simplest answer. You will have to create the row first, and create the text pointer:

drop table test
go
create table test
(
testId int,
value text
)
go
insert into test(testId)
values (1)
go

Update test
set value = 'foo'
where testId = 1
and value is null

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(value)
FROM test
WHERE testId = 1


UPDATETEXT test.value @.ptrval null null 'b'
GO

select *
from test

Returns:

testId value

--

1 foob

|||Thanks so much - this seemed to work. And yes I am stuck with SQL Server 2000 right now. It seems to me so clunky to update text fields this way - so I'll be real happy once I migrate to 2005.

Thanks again for the help.

Monday, March 19, 2012

How to use Reporting Services without enforcing SSL?

Hello,

I have a reporting services application that needs to run reporting services without a user context. However, it will not pass the SSL check. I am using a self-signed certificate that is in the trusted certificates store. What I would really like to do is run reporting services without enforcing SSL (just not allow port 80 connections from the firewalll for security) and merely allow SSL connections when they come in so that any local programs can use reporting services without the need for SSL. As far as I know, Reporting Services (ISAPI filter?) is somehow checking the http headers and not allowing, at least an anonymous, a connection *unless* SSL is forced (WHY?). If my app redirects using the https://FQDN or http://localhost to reporting services it fails the SSL check.

Is there a way around this or can simply do something with the certificate store, like trusted certificates or publishers, so that my local programs can pass the SSL check?

You can use the Reporting Services Configuration tool to set whether the Report Server requires SSL. See the doc: http://msdn2.microsoft.com/en-us/library/ms345223.aspx. Does this answer your question?

-Albert