Correct Table Structure - Optional Values

Hello,

I have 3 optional text boxes. I don't know if the best way to set up
the table would be a field for each box, since this would leave gaps in
the table if the user only filled in one box. Is there a good method
to use?? This is kind of like storing check box values, in that there
could be multiple answers.traceyburger@.sw.rr.com wrote:
> Hello,
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

You don't give us much to go on but based off what you said, it sounds
as if the data that goes in these text boxes should be in their own table.

Zach|||(traceyburger@.sw.rr.com) writes:
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

What do you mean with gaps? With this miniscule information, it sounds
to me that the columns mapping to these text boxes should be nullable.
Thus if a user only enters value in one box, you store NULL in the other
columns.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Correct syntax for Substring?

I want to limit the length of a text box to 20 characters and using the
format below it works if the length is greater than 20 but gives an '#Error'
when less than 20. Is my syntax wrong?
=Iif(Fields!CustName.Value.ToString().Length() > 20,
Fields!CustName.Value.ToString().Substring(0,20), Fields!CustName.Value)
ThanksMike,
Instead of using the if statement, you could just use the Left()
function. This will return the left X characters of your string.
Try using =Left(Fields!CustName.Value, 20)
Regards,
Dan
Mike Harbinger wrote:
> I want to limit the length of a text box to 20 characters and using the
> format below it works if the length is greater than 20 but gives an '#Error'
> when less than 20. Is my syntax wrong?
> =Iif(Fields!CustName.Value.ToString().Length() > 20,
> Fields!CustName.Value.ToString().Substring(0,20), Fields!CustName.Value)
>
> Thanks|||Dan, as always, simple is best. I am still learning about functions so many
thanks !!
Chris
"Dan" <daniel.lenz@.qg.com> wrote in message
news:1145637948.118906.3030@.g10g2000cwb.googlegroups.com...
> Mike,
> Instead of using the if statement, you could just use the Left()
> function. This will return the left X characters of your string.
> Try using =Left(Fields!CustName.Value, 20)
> Regards,
> Dan
>
> Mike Harbinger wrote:
>> I want to limit the length of a text box to 20 characters and using the
>> format below it works if the length is greater than 20 but gives an
>> '#Error'
>> when less than 20. Is my syntax wrong?
>> =Iif(Fields!CustName.Value.ToString().Length() > 20,
>> Fields!CustName.Value.ToString().Substring(0,20), Fields!CustName.Value)
>>
>> Thanks
>sql

Copying Text Field

I am refreshing records on a target table from a similar source table (two
databases, same server) and having problems with the Text fields. I am usin
g
INSERTas follows:
delete from Trackpad4..Archived
set identity_insert Trackpad4..Archived on
insert into Trackpad4..Archived
(... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
from Trackpad..Archived
set identity_insert Trackpad4..Archived off
The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
is that a simple SELECT would return three rows but a rowcount of four. A
modification of the query picked up all the rows, but the DEL_RECIP_SIG fiel
d
was blank and none of the following fields displayed. Using null at least
allows the SELECTs to perform as expected and the later columns to be handle
d
properly.
My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
But the test displayed below, while it did populate the target text field,
again rendered the later columns apparently non-existent. Also, the
gobbledygook (some sort of encoded representation of a person's signature) i
n
the source and target DEL_RECIP_SIG fields don't match.
DECLARE @.ptrval_source varbinary(16)
, @.ptrval_target varbinary(16)
, @.Length_Source int
, @.Length_Target int
SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Source = DEL_RECIP_LEN
FROM trackpad..old_data
where PKG_NUM = 'W44287093174'
print 'Source'
print @.ptrval_source
print @.Length_Source
/*
update Trackpad4..Old_Data
set DEL_RECIP_SIG = 'placeholder'
where PKG_NUM = 'W44287093174'
*/
select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
FROM trackpad4..old_data
where PKG_NUM = 'W44287093174'
print 'Target'
print @.ptrval_target
print @.Length_Target
print 'source'
readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
print 'update'
UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
@.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
I tried copying the source table over through DTS, but target text fields
were empty. I was able to restore from a backup. All I can think is that
I'm not properly delineating the field going into the target record and its
violating SQL's internal rules for terminating fields and records.
Thanks for any insight,
KevinHi Kevin
You don't say what SQL Server versions you are using?
This was a problem with DTS!
http://support.microsoft.com/defaul...kb;en-us;257425
If you set the value for specific DEL_RECIP_SIG to NULL you will find out
the one that is causing this issue.
John
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>I am refreshing records on a target table from a similar source table (two
> databases, same server) and having problems with the Text fields. I am
> using
> INSERTas follows:
>
> delete from Trackpad4..Archived
> set identity_insert Trackpad4..Archived on
> insert into Trackpad4..Archived
> (... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
> select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
> from Trackpad..Archived
> set identity_insert Trackpad4..Archived off
>
> The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
> is that a simple SELECT would return three rows but a rowcount of four. A
> modification of the query picked up all the rows, but the DEL_RECIP_SIG
> field
> was blank and none of the following fields displayed. Using null at least
> allows the SELECTs to perform as expected and the later columns to be
> handled
> properly.
> My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
> But the test displayed below, while it did populate the target text field,
> again rendered the later columns apparently non-existent. Also, the
> gobbledygook (some sort of encoded representation of a person's signature)
> in
> the source and target DEL_RECIP_SIG fields don't match.
>
> DECLARE @.ptrval_source varbinary(16)
> , @.ptrval_target varbinary(16)
> , @.Length_Source int
> , @.Length_Target int
> SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Source = DEL_RECIP_LEN
> FROM trackpad..old_data
> where PKG_NUM = 'W44287093174'
> print 'Source'
> print @.ptrval_source
> print @.Length_Source
> /*
> update Trackpad4..Old_Data
> set DEL_RECIP_SIG = 'placeholder'
> where PKG_NUM = 'W44287093174'
> */
> select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
> FROM trackpad4..old_data
> where PKG_NUM = 'W44287093174'
> print 'Target'
> print @.ptrval_target
> print @.Length_Target
> print 'source'
> readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
> print 'update'
> UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
> @.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
>
> I tried copying the source table over through DTS, but target text fields
> were empty. I was able to restore from a backup. All I can think is that
> I'm not properly delineating the field going into the target record and
> its
> violating SQL's internal rules for terminating fields and records.
> Thanks for any insight,
> Kevin|||Hi, John,
Thanks for the reply. I'm using SQL 2000, SP3. I've tried this operation
in TSQL and DTS - same results. The UpdateText operation also didn't work -
seemingly the stored text data wasn't terminated properly, and following
fields in the record were not rendered at all. In any event, the target tex
t
did not resemble the source text.
If you can think of anything else, thanks in advance.
- Kevin
"John Bell" wrote:

> Hi Kevin
> You don't say what SQL Server versions you are using?
> This was a problem with DTS!
> http://support.microsoft.com/defaul...kb;en-us;257425
> If you set the value for specific DEL_RECIP_SIG to NULL you will find out
> the one that is causing this issue.
> John
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>
>

Copying Row Data within the same table

I have the following table:

Table name: RR

columns:

Subject (varchar (35), Null)

Topic (varchar (35), Null)

RD (text, null)

RR (text, null)

Picture (varchar (50), Null)

Video (varchar (50), Null)

RRID (int, Not Null)

TSTAMP (datetime, Null)

RRCount (int, Not Null)

This table stores common information used in resolving technical problems based on Subject and Topic. However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.

Example:

There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'. I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'. Highest current RRID = 5008

I cannot figure out how to write that query. I apologize in advance for the fact that this is, no doubt, a seriously beginner question.

Hi,

would be nice to have some DDL on hand to see your additional table information and some expected results, but anyway:

if you just want to copy these rows (I don′t know what you mean by RRID ?!) the easiest insert statement is:

INSERT INTO RR
(...collist....,Subject,Topic )
SELECT
...collist...,'Publisher02','Subcategory07'
FROM RR
WHERE = 'Publisher01' and
Topic = 'Subcategory03'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Copying one text column to another

Hi All
I have to copy text data from a table in one database to a table in a
different database, both table reside on the same server.
I tried two options which didn't work (see code below), and will be more
than gratefull for any help.
Thanks
Elie Grouchko
***************************************
I tried the following (didn't work):
UPDATE database2.dbo.table2
SET textcolumn2 = (SELECT database1.dbo.textcolumn1 FROM table1 WHERE
tablekey1 = @.tablekey1)
WHERE tablekey2 = @.tablekey2
***************************************
I also tried (also didn't work):
Select @.textcolumn1ptr = TextPtr(textcolumn1) FROM database1.dbo.table1
WHERE tablekey1 = @.tablekey1
Select @.textcolumn2ptr = TextPtr(textcolumn2) FROM database2.dbo.table2
WHERE tablekey2 = @.tablekey2
UpdateText
database2.dbo.table2.textcolumn2 @.textcolumn2ptr
Null
0
database1.dbo.table1.textcolumn1 @.textcolumn1ptrElie,
I think this will do what you want:
update database2.dbo.table2 set
textcolumn2 = T1.textcolumn1
from database1.dbo.table1 as T1
where T1.tablekey1 = database2.dbo.table2.tablekey2
and database2.dbo.table2.tablekey2 = @.tablekey2
Steve Kass
Drew University
Elie Grouchko wrote:

>Hi All
>I have to copy text data from a table in one database to a table in a
>different database, both table reside on the same server.
>I tried two options which didn't work (see code below), and will be more
>than gratefull for any help.
>Thanks
>Elie Grouchko
>***************************************
>I tried the following (didn't work):
> UPDATE database2.dbo.table2
> SET textcolumn2 = (SELECT database1.dbo.textcolumn1 FROM table1 WHERE
>tablekey1 = @.tablekey1)
> WHERE tablekey2 = @.tablekey2
>***************************************
>I also tried (also didn't work):
> Select @.textcolumn1ptr = TextPtr(textcolumn1) FROM database1.dbo.table1
> WHERE tablekey1 = @.tablekey1
> Select @.textcolumn2ptr = TextPtr(textcolumn2) FROM database2.dbo.table2
> WHERE tablekey2 = @.tablekey2
> UpdateText
> database2.dbo.table2.textcolumn2 @.textcolumn2ptr
> Null
> 0
> database1.dbo.table1.textcolumn1 @.textcolumn1ptr
>
>|||Great, it works
Thanks
:)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23V4SREbIFHA.2740@.TK2MSFTNGP12.phx.gbl...
> Elie,
> I think this will do what you want:
> update database2.dbo.table2 set
> textcolumn2 = T1.textcolumn1
> from database1.dbo.table1 as T1
> where T1.tablekey1 = database2.dbo.table2.tablekey2
> and database2.dbo.table2.tablekey2 = @.tablekey2
> Steve Kass
> Drew University
> Elie Grouchko wrote:
>sql

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copy/Paste behavior in SSMS

I realize that this seems odd, but I want to change the copy paste
behavior in SSMS.
Right now if you have no text selected in SSMS and do a control-c the
entire line is copied, but I want the copy to be null. Basicly I'm
automating some repetive functions and need to move only the selected
text to the clipboard. If no text is selected then want nothing.
Any ideas?
Thanks
William
wgbrown_nospam_@.gmail.com
How are you automating the tasks?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
<wgbrown@.gmail.com> wrote in message
news:1164146398.788232.175850@.k70g2000cwa.googlegr oups.com...
>I realize that this seems odd, but I want to change the copy paste
> behavior in SSMS.
> Right now if you have no text selected in SSMS and do a control-c the
> entire line is copied, but I want the copy to be null. Basicly I'm
> automating some repetive functions and need to move only the selected
> text to the clipboard. If no text is selected then want nothing.
> Any ideas?
> Thanks
> William
> wgbrown_nospam_@.gmail.com
>
|||Using Autohotkey - www.autohotkey.com. Its a scripting language to
automate tasks. Right now I've had problems pulling selected text
using its tools - some editors don't get along with it so I've resorted
to using the clipboard but the SSMS behavior is different that other
tools.
Thanks
William
Paul A. Mestemaker II [MSFT] wrote:[vbcol=seagreen]
> How are you automating the tasks?
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
> <wgbrown@.gmail.com> wrote in message
> news:1164146398.788232.175850@.k70g2000cwa.googlegr oups.com...
|||I'm not famliar with this utility at all. Can you give us an example of the
task you'd like to automate? Maybe we can find you a way to complete your
task using SQLCMD Mode in SSMS.
-Paul
<wgbrown@.gmail.com> wrote in message
news:1164179204.938292.85990@.k70g2000cwa.googlegro ups.com...
> Using Autohotkey - www.autohotkey.com. Its a scripting language to
> automate tasks. Right now I've had problems pulling selected text
> using its tools - some editors don't get along with it so I've resorted
> to using the clipboard but the SSMS behavior is different that other
> tools.
> Thanks
> William
>
> Paul A. Mestemaker II [MSFT] wrote:
>
|||Good Morning Paul.
There isn't one function but a bunch of little ones. Currently I'm
closing off certain characters like { [ ( " so I get {}, [], (), "" (I
work a lot in mdx). But I'd like to be able to reformat highlighted
text. Proper case words or such.
I was surprised about the copy behavior - I'd never noticed it.
Thanks
William
Paul A. Mestemaker II [MSFT] wrote:[vbcol=seagreen]
> I'm not famliar with this utility at all. Can you give us an example of the
> task you'd like to automate? Maybe we can find you a way to complete your
> task using SQLCMD Mode in SSMS.
> -Paul
> <wgbrown@.gmail.com> wrote in message
> news:1164179204.938292.85990@.k70g2000cwa.googlegro ups.com...
|||See if this helps with what you want?
http://www.red-gate.com/products/SQL_Refactor/index.htm
Andrew J. Kelly SQL MVP
<wgbrown@.gmail.com> wrote in message
news:1164215484.851461.150040@.h54g2000cwb.googlegr oups.com...
> Good Morning Paul.
> There isn't one function but a bunch of little ones. Currently I'm
> closing off certain characters like { [ ( " so I get {}, [], (), "" (I
> work a lot in mdx). But I'd like to be able to reformat highlighted
> text. Proper case words or such.
> I was surprised about the copy behavior - I'd never noticed it.
> Thanks
> William
>
> Paul A. Mestemaker II [MSFT] wrote:
>
|||I've looked at that and it looks really nice. As soon as I have time I
want to try it out. Much of my work is around MDX however, and the
tools are more limited. For good reason, for every person who does MDX
there must be 1000 (or more) sql programmers.
Thanks for the tip.
William
Andrew J. Kelly wrote:[vbcol=seagreen]
> See if this helps with what you want?
> http://www.red-gate.com/products/SQL_Refactor/index.htm
> --
> Andrew J. Kelly SQL MVP
> <wgbrown@.gmail.com> wrote in message
> news:1164215484.851461.150040@.h54g2000cwb.googlegr oups.com...

Copy text columns to nvarchar

Hi,

I have a problem, I have a table with a text type column and a
nvarchar(2000) type column on my MS SQL 2000 Server.

I know that the longest text in the text field is 1000 chars. I want to
copy the content the content of the text field into the nvarchar field.

I tried convert and cast but after the update there are only 255 chars
in the nvarchar field.

Best regards
MarcMarc Filthaut (usenet_delete_for_reply@.filthaut.com) writes:
> I have a problem, I have a table with a text type column and a
> nvarchar(2000) type column on my MS SQL 2000 Server.
> I know that the longest text in the text field is 1000 chars. I want to
> copy the content the content of the text field into the nvarchar field.
> I tried convert and cast but after the update there are only 255 chars
> in the nvarchar field.

I don't believe that. Add a len(nvarcharfield) to you query and you
will make a revelation.

The next step of this operation is to go Tools->Options->Results and
change the setting Max characters per column.

--
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|||Hi,

Erland Sommarskog wrote:
> Marc Filthaut (usenet_delete_for_reply@.filthaut.com) writes:
> The next step of this operation is to go Tools->Options->Results and
> change the setting Max characters per column.
Thanks this was one of the solutions.

Best regards
Marc