Can not insert input field with length gt 128

I got this problem that really puzzled me. I used a stored procedure to
insert records into SQL. When the field of input string contained more than
128 characters, it gave me this message:
The identifier that starts with 'Today is a nice day..........Good bye'
is too long. Maximum length is 128.
But here I am not sending in an identifier name, I am sending in the actual
input which is more than 128 characters long. The input field in the data
base is defined as varchar(1000).
Any ideas?What is the size on the parameter declaration in the stored procedure? I'll
bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:D4B955D4-C318-4E38-BB9F-77B439561881@.microsoft.com...
>I got this problem that really puzzled me. I used a stored procedure to
> insert records into SQL. When the field of input string contained more
> than
> 128 characters, it gave me this message:
> The identifier that starts with 'Today is a nice day..........Good bye'
> is too long. Maximum length is 128.
> But here I am not sending in an identifier name, I am sending in the
> actual
> input which is more than 128 characters long. The input field in the data
> base is defined as varchar(1000).
> Any ideas?|||> What is the size on the parameter declaration in the stored procedure?
> I'll bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
Actually, it sounds like the call he is making to the database doesn't
properly encapsulate this string. It seems SQL Server has mistaken the
string for an identifier (otherwise the error would be "string or binary
data would be truncated"). This usually happens when someone uses " instead
of ' for delimiting a string, but there are other possibilities as well.
"pelican", how about showing us the actual code you are using, instead of
vaguely describing it -- then we don't have to guess. I suppose DDL for the
table you are trying to INSERT into wouldn't hurt either (see
http://www.aspfaq.com/5006).|||Good point, probably an embedded ' in the input somewhere that wasn't
properly escaped by the app. Interesting that it would pick 128 as the
cut-off size, however. I would definitely need to see the code for the SP
and the input string.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:u6KSE6AUFHA.2712@.TK2MSFTNGP09.phx.gbl...
> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses "
> instead of ' for delimiting a string, but there are other possibilities as
> well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for
> the table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>|||> Good point, probably an embedded ' in the input somewhere that wasn't
> properly escaped by the app. Interesting that it would pick 128 as the
> cut-off size, however.
That is the maximum size for an identifier. I don't think it is an errant
', it just received this string that it thinks is supposed to be a column,
and is saying, WHOA, this column name is bigger than 128, hold on. It could
be 129 or 8000 characters... the same error will get kicked out.|||Thank you all so much! "AB - MVP" is right, once I changed the double quote
(") to single ('), it worked fine! In the stored procedure, the size on the
parameter is varchar(1000). I did not realize that the "" sign will make SQ
L
believe I was passing an identifier instead of the actually content.
You are my life saver...
"AB - MVP" wrote:

> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses " inste
ad
> of ' for delimiting a string, but there are other possibilities as well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for th
e
> table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>
>|||I read about the identifier length in the help section. It said the same
thing as AB-MVP pointed out, that 128 is the maximum length per row for an
identifier. I just never dreamed that a " will do it.
Thanks a lot!|||Yeah it's called a "quoted identifier" and can be circumvented by using
"parameterized queries".
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:6F3F8CAB-1EBD-45A6-BF74-4DC272EA1804@.microsoft.com...
> Thank you all so much! "AB - MVP" is right, once I changed the double
> quote
> (") to single ('), it worked fine! In the stored procedure, the size on
> the
> parameter is varchar(1000). I did not realize that the "" sign will make
> SQL
> believe I was passing an identifier instead of the actually content.
> You are my life saver...
> "AB - MVP" wrote:
>|||Yep that's what they get for slapping a SQL statement together with +'s
instead of using parameterized queries.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:uGWbvCBUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> That is the maximum size for an identifier. I don't think it is an errant
> ', it just received this string that it thinks is supposed to be a column,
> and is saying, WHOA, this column name is bigger than 128, hold on. It
> could be 129 or 8000 characters... the same error will get kicked out.
>

Can not delete data from View

I have a view is made from three tables by UNION ALL.
When I tried to delete some records by using 'Delete from My_New_View_2004..
.', I get error:
Server: Msg 4440, Level 16, State 9, Line 1
UNION ALL view is not updatable
Why and how to fix this problem so I can delete data from view?
Thanks for help.
-GeorgeA view containing a UNION is not updateable unless it can meet the criteria
for a Partitioned View. See the Partitioned Views topic in Books Online for
details.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sICdnfm2y_xb7_7dRVn-ig@.giganews.com...
> A view containing a UNION is not updateable unless it can meet the
criteria
> for a Partitioned View. See the Partitioned Views topic in Books Online
for
> details.
Alternatively, if you are running SQL 2000 you can have an INSTEAD OF DELETE
trigger that deletes from the underlying tables
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004

Can LOG PI or Log Explorer recover deleted records BEFORE

installation of their softwares? If there has NEVER been a backup of
the Database (.mdf), Recovery Model Set to Full, and there has never
been a backup of the Log (.ldf) files?

Using the trial software for both, this was unclear.

Thank you for your time,

bd"bd420" <bd420@.hotmail.com> wrote in message
news:1136940831.527840.99440@.o13g2000cwo.googlegro ups.com...
> installation of their softwares? If there has NEVER been a backup of
> the Database (.mdf), Recovery Model Set to Full, and there has never
> been a backup of the Log (.ldf) files?

My understanding is that as long as you have NOT truncated your log file the
answer should be yes.

> Using the trial software for both, this was unclear.
> Thank you for your time,
> bd|||bd420 (bd420@.hotmail.com) writes:
> installation of their softwares? If there has NEVER been a backup of
> the Database (.mdf), Recovery Model Set to Full, and there has never
> been a backup of the Log (.ldf) files?
> Using the trial software for both, this was unclear.

I believe the answer is no. The key here is that you have never taken a
backup of the database. As long as you have done that, the log works as
if you were in simple recovery. After, a log is supposed to be applied
to a database back, so if there isn't any...

But hadn't it been for this fine detail, the answer would be yes. That
is, there is a full backup taken some time, and you are running in
full recovery.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

can i/should i worry about fragmentation in catalog files?

Hello all,
I have an application that grew to 1.5 million records and saw poor
performance for a variety of reasons, but one huge 'doh' reason was that I
didn't think to pre-allocate the database and the poor database file was
fragmented as it grew from its default size in increments When we moved to a
new server I pre-allocated the file and made sure it was contiguous.
Is there a correllary with full-text indexing? Are there full-text catalog
files I can or should be creating as contiguous files, or should i be
running defrag on them?
thanks,
john
John,
Yes, there is. It is call "Master Merge" and smaller index files (shadow
files) are merged together at Midnight (controlled via registry key). While
this is not directly controllable in SQL Server 2000, can be managed via the
following TSQL code in SQL Server 2005:
Reorganize the full-text catalog using ALTER FULLTEXT CATALOG REORGANIZE. It
is important to do this before performance testing because it results in a
master merge of the full-text indexes in that catalog.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23lsVUjy0FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I have an application that grew to 1.5 million records and saw poor
> performance for a variety of reasons, but one huge 'doh' reason was that I
> didn't think to pre-allocate the database and the poor database file was
> fragmented as it grew from its default size in increments When we moved to
> a
> new server I pre-allocated the file and made sure it was contiguous.
> Is there a correllary with full-text indexing? Are there full-text catalog
> files I can or should be creating as contiguous files, or should i be
> running defrag on them?
> thanks,
> john
>
|||Thank you for your response. Unfortunately this is SQL Server 2000. Does it
make enough of a difference that its worth defragging the individual catalog
files with something like www.defragmentor.com?
john
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23A7C4vy0FHA.560@.TK2MSFTNGP12.phx.gbl...
> John,
> Yes, there is. It is call "Master Merge" and smaller index files (shadow
> files) are merged together at Midnight (controlled via registry key).
While
> this is not directly controllable in SQL Server 2000, can be managed via
the
> following TSQL code in SQL Server 2005:
> Reorganize the full-text catalog using ALTER FULLTEXT CATALOG REORGANIZE.
It[vbcol=seagreen]
> is important to do this before performance testing because it results in a
> master merge of the full-text indexes in that catalog.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:%23lsVUjy0FHA.612@.TK2MSFTNGP10.phx.gbl...
I[vbcol=seagreen]
to[vbcol=seagreen]
catalog
>
|||You're welcome, John,
I suspected that you were using SQL Server 2000 as the SQL 2005 info was
just FYI as Microsoft now recognizes this as a 'tunable' knob... As for
using Defragmentor or other utilities, in a word: No. The FT Catalog files
are managed by the "Microsoft Search" (mssearch.exe) service and are
compressed internally, so these utilities will do more harm than good.
For SQL Server 2000, the Master Merge" and smaller index files (shadow
files) are merged together at Midnight, so you may want to monitor your
server's app log for this recorded event at midnight.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OFouh$y0FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Thank you for your response. Unfortunately this is SQL Server 2000. Does
> it
> make enough of a difference that its worth defragging the individual
> catalog
> files with something like www.defragmentor.com?
> john
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23A7C4vy0FHA.560@.TK2MSFTNGP12.phx.gbl...
> While
> the
> It
> I
> to
> catalog
>

Can I tell how often the clustered index pages are spliting?

Greetings

For performance reasons I am trying to determine how often the pages in
the index split due to inserts. Are there any records in the 'sys' tables,
etc. Thanx"Robert" <stop.spam@.boeing.com> wrote in message news:<Hsw479.Mnx@.news.boeing.com>...
> Greetings
> For performance reasons I am trying to determine how often the pages in
> the index split due to inserts. Are there any records in the 'sys' tables,
> etc. Thanx

I don't think this is stored in any system table, but you can use the
Performance Monitor counter SQLServer:AccessMethods, Page Splits/sec
to capture the information.

Simon|||Many, many thanx. I found it.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402102355.4236cb34@.posting.google.c om...
> "Robert" <stop.spam@.boeing.com> wrote in message
news:<Hsw479.Mnx@.news.boeing.com>...
> > Greetings
> > For performance reasons I am trying to determine how often the pages
in
> > the index split due to inserts. Are there any records in the 'sys'
tables,
> > etc. Thanx
> I don't think this is stored in any system table, but you can use the
> Performance Monitor counter SQLServer:AccessMethods, Page Splits/sec
> to capture the information.
> Simon

Can I store the result set of sp_who command in a table

Hi,
I want to loop through the records of the result set of sp_who.
Can this be done? How it can be done?
Regards,
Sevugan.C

code:

create table #tmpSpWho
(
spid smallint
,ecid smallint
,status nchar(30)
,loginame nchar(128)
,hostname nchar(128)
,blk char(5)
,dbname nchar(128)
,cmd nchar(16)
)
insert into #tmpSpWho
exec sp_who

HTH,
Cristian Lefter, SQL Server MVP
"Sevugan" <
Sevugan@.discussions.microsoft.com>
wrote in message
news:41413280-9A1A-481D-9FC8-31642E546811@.microsoft.com...
>
Hi,
>
I want to loop through the records of the result set of sp_who.
>
Can this be done? How it can be done?
>
>
Regards,
>
>
Sevugan.C|||You can create a temporary table to grab the result from the sp sp_who2.
Example:
use northwind
go
create table #t1 (
spid int,
status varchar(255),
login sysname,
hostname sysname,
blkby sysname,
dbname varchar(128),
command varchar(255),
cputime int,
diskio int,
lastbatch varchar(35),
programname varchar(128),
spid_1 int
)
go
insert into #t1
exec sp_who2
go
select
*
from
#t1
order by
login
go
drop table #t1
go
AMB
"Sevugan" wrote:

> Hi,
> I want to loop through the records of the result set of sp_who.
> Can this be done? How it can be done?
> Regards,
> Sevugan.C|||If you are wanting to do this on the client side, you can call sp_who into
an ADO recordset the same as any stored procedure.
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:41413280-9A1A-481D-9FC8-31642E546811@.microsoft.com...
> Hi,
> I want to loop through the records of the result set of sp_who.
> Can this be done? How it can be done?
> Regards,
> Sevugan.C|||Create TABLE #WHO
(
spid int,
ecid int,
Status Nvarchar(20),
LoginName Nvarchar(30),
Hostname Nvarchar(30),
blk int,
dbname Nvarchar(30),
cmd Nvarchar(255)
)
Insert #who
EXEC sp_who
Select * from #who
Create cursor to loop through data with whatever you want to do with the dat
a.
Hope this helps.
"Sevugan" wrote:

> Hi,
> I want to loop through the records of the result set of sp_who.
> Can this be done? How it can be done?
> Regards,
> Sevugan.C

Can I recover?

An update statement was made against a table that did not include a 'where'
clause and hence all the records were updated and basically destroying all
the data.
There is no recent backup of the database ( the last one was in February).
Is there anyway to get back to the the way the table was before the update?
Thanks for any help,
Patrick
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--> There is no recent backup of the database ( the last one was in February).
WHOOPS!

> Is there anyway to get back to the the way the table was before the
> update?
http://www.aspfaq.com/2449|||"Patrick Nolan" <nolanpa@.bcsd.com> wrote in message
news:1126123461_16397@.spool6-east.superfeed.net...
> An update statement was made against a table that did not include a
> 'where'
> clause and hence all the records were updated and basically destroying all
> the data.
> There is no recent backup of the database ( the last one was in February).
> Is there anyway to get back to the the way the table was before the
> update?
> Thanks for any help,
> Patrick
>
Ouch!!!
You can purchase one of several well-known products that Aaron listed, or if
you know about the time that the update took place *AND* you have
transaction logs, then you can do a Point in Time restore.
Check the BOL for specifics on this.
Rick Sawtell
MCT, MCSD, MCDBA|||It depends on the recovery model you're using. If you're using the full
recovery model, then you can probably use a log reader. If you're using the
simple recovery model, then you might be out of luck.
"Patrick Nolan" <nolanpa@.bcsd.com> wrote in message
news:1126123461_16397@.spool6-east.superfeed.net...
> An update statement was made against a table that did not include a
'where'
> clause and hence all the records were updated and basically destroying all
> the data.
> There is no recent backup of the database ( the last one was in February).
> Is there anyway to get back to the the way the table was before the
update?
> Thanks for any help,
> Patrick
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
=--|||Rick..how is your solution accomplished? I did not think this was possible
without a DB backup. I just want to understand in case I ever need it too.
Thanks..
"Rick Sawtell" wrote:

> "Patrick Nolan" <nolanpa@.bcsd.com> wrote in message
> news:1126123461_16397@.spool6-east.superfeed.net...
> Ouch!!!
> You can purchase one of several well-known products that Aaron listed, or
if
> you know about the time that the update took place *AND* you have
> transaction logs, then you can do a Point in Time restore.
> Check the BOL for specifics on this.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:644AA6A1-220F-4A59-84AE-70FA438E6157@.microsoft.com...
> Rick..how is your solution accomplished? I did not think this was
> possible
> without a DB backup. I just want to understand in case I ever need it
> too.
> Thanks..
> "Rick Sawtell" wrote:
>
Check the BOL for:
RESTORE LOG ... WITH STOPAT
Rick Sawtell
MCT, MCSD, MCDBA|||Rick, thanks, but I am still . Looked at the STOPAT, but still
thought a DB restore would be needed first, which was indicated by BOL. Can
the restore log command be accomplished without restoring a DB backup? Just
trying to learn all I can. thanks again..
"Rick Sawtell" wrote:

> "NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
> news:644AA6A1-220F-4A59-84AE-70FA438E6157@.microsoft.com...
> Check the BOL for:
> RESTORE LOG ... WITH STOPAT
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Maybe you should also look into preventing such... khm... situations.
For instance - doing it step by step (in transaction):
begin tran
-- do your modifications here
-- check results
-- then either:
rollback tran -- if something went wrong
-- or:
commit tran -- if all is well in the world of data storage
ML
p.s. oh, and yeah - backup.|||If the one that you made on Feb was a Full Database Backup and the recovery
mode has been setup with "Full", I believe you can backup the current
Transaction Log and then you can still do a "Point In Time" restore.
Ed
"Patrick Nolan" wrote:

> An update statement was made against a table that did not include a 'where
'
> clause and hence all the records were updated and basically destroying all
> the data.
> There is no recent backup of the database ( the last one was in February).
> Is there anyway to get back to the the way the table was before the update
?
> Thanks for any help,
> Patrick
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||> Rick, thanks, but I am still . Looked at the STOPAT, but still
> thought a DB restore would be needed first, which was indicated by BOL.
Correct. So your only option is if the db has been in full recovery mode sin
ce your last db backup
(not likely) and you now do a log backup. Or use a log reader tool. Some add
itional info at
http://www.karaszi.com/SQLServer/in...eral_times.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:DA40AC62-F241-4356-A5EB-83B83ABF80F9@.microsoft.com...
> Rick, thanks, but I am still . Looked at the STOPAT, but still
> thought a DB restore would be needed first, which was indicated by BOL. C
an
> the restore log command be accomplished without restoring a DB backup? Ju
st
> trying to learn all I can. thanks again..
> "Rick Sawtell" wrote:
>