estimate table spaces

hi guys,
sp_spaceused 'table1'
I have two ntext columns in 'table1',
by default, is sp_spaceused calculating space for ntext too?Hi Britney
All columns are included. You can see this for yourself:
use pubs
go
select * into newtitles from titles
go
exec sp_spaceused newtitles, @.updateusage= true
go
alter table newtitles add info ntext
go
update newtitles set info = replicate(title, 100)
go
exec sp_spaceused newtitles, @.updateusage= true
go
HTH
Kalen Delaney
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:emryCsOpFHA.1044@.tk2msftngp13.phx.gbl...
> hi guys,
>
> sp_spaceused 'table1'
> I have two ntext columns in 'table1',
> by default, is sp_spaceused calculating space for ntext too?
>
>|||When NTEXT column is NULL, how come it still takes some spaces?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODCxkYQpFHA.3828@.TK2MSFTNGP12.phx.gbl...
> Hi Britney
> All columns are included. You can see this for yourself:
> use pubs
> go
> select * into newtitles from titles
> go
> exec sp_spaceused newtitles, @.updateusage= true
> go
> alter table newtitles add info ntext
> go
> update newtitles set info = replicate(title, 100)
> go
> exec sp_spaceused newtitles, @.updateusage= true
> go
> HTH
> Kalen Delaney
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:emryCsOpFHA.1044@.tk2msftngp13.phx.gbl...
>
>|||Kevin
LOB data (type text, ntext and image) is by default stored on separate pages
outside the data rows. As soon as you update any rows with LOB data to
anything, even null, SQL Server will allocate at least 2 additional pages to
start keeping track of that data.
FYI, for ANY fixed length data column, NULLs will take space. So a char(100)
that contains NULL will take the full 100 bytes.
HTH
Kalen Delaney
www.solidqualitylearning.com
"kevin" <pearl_77@.hotmail.com> wrote in message
news:%23OP4VFYpFHA.3380@.TK2MSFTNGP12.phx.gbl...
> When NTEXT column is NULL, how come it still takes some spaces?
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODCxkYQpFHA.3828@.TK2MSFTNGP12.phx.gbl...
>
>

escaping a string

hi guys

please help me. i've never user stored procs before but here is my problem.

this is only what i am allowed to display but it shows my problem

declare @.suite varchar(10),@.company varchar(1)
set @.suite = 'brutus'

set @.company = 'A'
exec ('insert into tot (SuiteName,Company) values ('+@.suite+','+@.company+')')

when i exec the query it says that "brutus" is an invalid column name. i know that i need to insert a extra ' but i don't know how or what is the escape character. please help me.

Hi,

this should be accomplishedby this one here:

exec ('insert into tot (SuiteName,Company) values ('''+@.suite+''','''+@.company+''')')

But I would rather prefer not using dynamic sql in this case:

insert into tot (SuiteName,Company) values (@.suite,@.company)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

thanks that helps but when i do this

1. exec ('alter table tot add '+@.UnitT+' varchar(3) DEFAULT '' WITH VALUES')

2. alter table tot add @.UnitT varchar(3) DEFAULT ' ' WITH VALUES

not one work. in 1 i get the same problem and in 2 it tells met there is a problem with syntax before @.unit

|||

No, therefore you would need dynamic SQL, that will not function with the second opion I posted.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK THANKS

i used your first solution with the ''' multiple quotes and it works perfect. thanks

ERwin Triogger Bug

Hi, guys...new guy here. Let me introduce:

I'm a technical writer for B&N.com and I've been using ERwin
Datamodeler 4, release 4.1.2771 to create data dictionaries. I'm not a
DBA, though I'm slowly picking stuff up.

I discovered something yesterday that from my perspective is quite
serious and I wanted to verify that it is indeed an issue.

I have bunches of tables and for some tables, I have
triggers...anywhere from 1 to 6 of them for a table. Yesterday, I
noticed that triggers appeared in SQL Query Analyzer that didn't
appear in ERwin and after a long conversation with CA tech support, it
was determined that ERwin doesn't properly support showing all
triggers for a table when more than one trigger for that table
performs the same action; insert, uipdate, or delete. When it finds
more than one, it only shows one of them and ignores the others.

As anyone found this to be true? There is a kludge workaround...use
SQL Enterprise to script out the database, then perform a reverse
engineer on the script rather than the database, but that sucks.

Has anyone else noticed this?

John PosadaJohn Posada (jposada@.book.com) writes:
> I have bunches of tables and for some tables, I have
> triggers...anywhere from 1 to 6 of them for a table. Yesterday, I
> noticed that triggers appeared in SQL Query Analyzer that didn't
> appear in ERwin and after a long conversation with CA tech support, it
> was determined that ERwin doesn't properly support showing all
> triggers for a table when more than one trigger for that table
> performs the same action; insert, uipdate, or delete. When it finds
> more than one, it only shows one of them and ignores the others.
> As anyone found this to be true? There is a kludge workaround...use
> SQL Enterprise to script out the database, then perform a reverse
> engineer on the script rather than the database, but that sucks.

I don't use ERwin, but PowerDesigner from Sybase. Whether PowerDesigner
can handle this situation nicely, I don't know, because I don't have
triggers in PowerDesigner, and I never have more than one trigger
per action for the same table.

However, unless the ERwin documentation clearly says that ERwin handles
this, I find it difficult to call it a bug. Sounds more like a limitation
to me. To make an analogy: in PowerDesigner you can only have one table
constraint per table.

As for having more than one trigger per action for a table, I am inclined
to say that just because this feature is there, does not mean that you
have to use it. Since the execution order is undefined, this seems
somewhat risky to me. (Unless you have one INSETEAD OF and one AFTER
trigger for the same action.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John Posada (jposada@.book.com) writes:
> Second, about it not being a bug, I'm not asking ERwin to allow me to
> create more than one trigger...that situation already exists. I'm only
> asking it to show me an accurate representation of what it is.

If ERwin does not have the capabilities to create more than one trigger
on a table, I would expect it to be able to display more than one either.

I did an experiment with PowerDesigner and reverse-engineered a table
which have a load of table constraints. (As I mentioned, PD does not
support more than one table constraint per table.) And sure enough,
only one of the constraints made it into PD.

So, unless you can find some documentation that says that ERwin handles
multiple triggers on a table, my conclusion is that you have run into a
limitation of the tool, and the best suggestion for you would be to
use what even forum for enhancement requestions that CA provides.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, I'm working on it.

John Posada

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

error18272

Hello guys, any help with this one. Am I the only one who have this problem?;-)
Try creating the directory that it's looking for as
indicated in the error message. In your case, it's looking
for the existence of the directory F:\MSDATA\backup
-Sue
On Wed, 12 May 2004 12:11:05 -0700, "dk"
<anonymous@.discussions.microsoft.com> wrote:

>Hello guys, any help with this one. Am I the only one who have this problem?;-)

error18272

Hello guys, any help with this one. Am I the only one who have this problem?
';-)Try creating the directory that it's looking for as
indicated in the error message. In your case, it's looking
for the existence of the directory F:\MSDATA\backup
-Sue
On Wed, 12 May 2004 12:11:05 -0700, "dk"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hello guys, any help with this one. Am I the only one who have this problem?;-)[/
vbcol]