Datatype nvarchar(max) not accessible

Hi,

trying to input

create table T (c1 nvarchar(max));

in MS SQL Server Manangement Studio Express results in an error :

Fehler beim Analysieren der Abfrage. [ Token line number = 1,Token line offset = 30,Token in error = max ]

create table T (c1 nvarchar(4000));

is processed w/o errors.

I′ve installed SQL Server 2005 Express Ed. SP2

Microsoft SQL Server Management Studio Express 9.00.3042.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML 2.6 3.0 4.0 5.0 6.0

Microsoft .NET Framework 2.0.50727.42

Betriebssystem 5.1.2600

Any ideas?

Thanks in advance

Werner

This command should work without problems, and did on my computer. Could you check the error log to see if there is more informaiton about the failure there?

Mike

|||

Hi,

thanks for your reply. I think I found the reason.

Being a newcomer I accidently selected "SQL Server Everywhere" and generated a compact sdf-DB. This type of server doesn′t support nvarchar(max) (which is not mentioned in any documentation available in the net).

Setting up a new db (conversion of the sdf to mdf format is not possible (?)) fixed the problem.

btw, I can imagine using large datafields even on a PDA, so what′s the reason for not supporting this datatype (I do see a parallel to those guys of TI in the late 70s who invented the datetime format for PCs, saving 2 byte...)

Thanks anyway for your fast reaction and your help offered

Werner

Datasource Reader - Name for output column is blank.

Hi,

I have a problem using the odbc datasource reader to execute a sql command on a progress database. My query is something like:-

select max(id), sum(amount) from my_table

OR

select a, b, c, recid(my_table) from my_table

which produces external columns and output columns with no name. The progress sql doesn't support using aliases on column names and setting validateexternalmetadata to false and manually naming the input and output parameters in the 'Advanced Editor' doesn't seem to work either. I either get the error 'The name for output column "" is blank and columns can not be blank' or if I add my own column names in the input and output parameters it fails in the pre-execute phase saying it can't find a column in the datasource with name 'myalias'

I can get around the aggregate functions by transfering all the data and doing the aggregate on the local server but I also need to call functions such as recid() which I can't work around. SQL2000 DTS ignored these things and matched as best it could where SQL 2005 IS seems overly strict.

Has anyone encountered similar problems and does anyone have any ideas? I'm currently at a loss :(

Hi,

I don't know about 'Progress'. But just in a curiosity I am asking this. If you can't have a column alias how will you create a view using the statements that you have mentioned above. If there is any workaround there, u better try the same thing.

|||

At a stretch, it should be possible to create a script source which would execute this query and assign the results to columns in the data flow.

another possibility may be to use an Execute SQL task - if you're not returning too many rows.

However, are you sure column aliases are not supported? The Progress online documentation appears to suggest that it may be. there may be a way in progress to make this happen.

Donald

datalength doubling values

Am I missing something? I'm trying to return the size of the data contained in a varbinary(max) column, however it appears that the value being returned is double what it should be. Is this normal, or is there something else I need to do?

Thanks,

Devin

Edit: I'm not discounting that my data may be weird, but I wanted to cast my net as wide as possible.

Perhaps the field contains trailing blanks...|||

Why do you think this is double? Can you post a snippet of code that doesn't seem to make sense? Like:

set nocount on

declare @.test varbinary(max)

set @.test = 0x12

select datalength(@.test)

set @.test = 0x1234

select datalength(@.test)

Returns:

--

1

--

2

|||

Ignore this. I was using compression on the streams as I was putting them into the column. Apparently the framework's GZipStream class has a bug that causes it to mishandle files that already have compression in them (video, jpg, pdf) so that they end up larger.

DATALENGTH was reporting the correct size for the contents of the column.

Thanks,

Devin

Database Tuning Advisor - how does the max. space parameter work?

I think that the min. space required for physical design structures may be
reported incorrectly. Please see the thread at the following URL:
http://groups.google.ca/group/SQL-Se...f3426d5a?hl=en
No it is not.I have tried my best to explain in the other thread based
on my interpretation of your posting