Can I Insert/Update Large Text Field To Database Without Bulk Insert?

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

You can't just use a plain old update statement and set the column = a parameter of the correct datatype?

|||

How do you indicate that a SqlParameter is of type nvarchar(max)? Any numeric length up to 4000 is easy, but beyond that I've come up empty.

|||

When I add a parameter to a command, I use the AddWithValue method instead of the Add method. That way I don't have to type in the datatype and the length, I just pass it text and it works.

It's possible that it will truncate on you using that method, but I've used it with ntext and long text values before

|||

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar)

or

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar,-1)

|||

cmd.Parameters.AddWithValue("@.Blobby",myTextBox.Text)

(or any other object's value instead of myTextBox)

|||

I normally don't recommend AddWithValue because it can cause some problems when it's unclear what the conversions (if any) should be. This comes into play when the result to be passed could possibly be a nvarchar or a more specific data type (integers, dates). Under certain circumstances, .NET decides to send the data to SQL Server as a nvarchar, and when it gets there, it realizes that it needs to be converted to a more specific data type, but the information needed to do the conversion correctly (because of culture formatting) isn't available on the server, or it uses the servers culture rather than the culture of the running page.

Using .Add with a specified datatype insures that the data conversion is done by .NET before sending the parameter on to SQL Server.

Can I insert into the same table a new row with the "old" row field value?

I'm trying to insert values into the same database from itself.
Essentially, I want to add another row to the table for each row with
reg_cat_id = 3. But in this row, I want the original registration_id
to show up in the new row.
Here is my syntax below - this generates an error:
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
'05/05/2007')
SELECT REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
FROM Registration_Category t1
WHERE (REG_CAT_ID = 3)
ORDER BY REGISTRATION_ID
Any suggestions?On 30 Mar 2006 14:40:12 -0800, Dee wrote:
(snip)
>Here is my syntax below - this generates an error:
>INSERT INTO Registration_Category
> (REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
>VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
>'05/05/2007')
> SELECT REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
> FROM Registration_Category t1
> WHERE (REG_CAT_ID = 3)
> ORDER BY REGISTRATION_ID
>Any suggestions?
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, t1.REGISTRATION_ID, 'test',
'20070505', 'Y', '20070505')
FROM Registration_Category AS t1
WHERE REG_CAT_ID = 3
Hugo Kornelis, SQL Server MVP

Can I insert data from a report with RS2005?

Hi All,
Here's my problem:
I need to pull some items into a table report. I have the items grouped
by a key field (AlertID) and a detail the the user can drilldown to for
each item. I'm having trouble with the linking, I try to link this
report to another report that takes the parameters and runs a stored
proc to insert the data for the item (Alert History). Each report works
fine on by itself, but I can't get them to link in anyway, I've tried
Jump to Report and Jump to URL.
Any suggestions would be greatly appreciated.
Thanks,
Damien Johnston
P.S. I also need know if there is a writable textbox and a checkbox
control available in RS?In general this is a bad idea (trying to write data). Some issues you would
have to deal with are: multi-user, cleaning up data when done, etc.
Given what you say I don't see why you need to do this. If your stored
procedure can figure out the data to insert then why can't you just have a
stored procedure returning the appropriate data to the report. Instead of
drill down you should be using drill through. Drill through is much more
efficient. Have a field that you highlight and color blue. Users understand
that means to click on it. Then use jump to report to call up the report
with the detail information.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dij0674" <dij0674@.hotmail.com> wrote in message
news:1161026120.846569.113660@.e3g2000cwe.googlegroups.com...
> Hi All,
> Here's my problem:
> I need to pull some items into a table report. I have the items grouped
> by a key field (AlertID) and a detail the the user can drilldown to for
> each item. I'm having trouble with the linking, I try to link this
> report to another report that takes the parameters and runs a stored
> proc to insert the data for the item (Alert History). Each report works
> fine on by itself, but I can't get them to link in anyway, I've tried
> Jump to Report and Jump to URL.
> Any suggestions would be greatly appreciated.
> Thanks,
> Damien Johnston
> P.S. I also need know if there is a writable textbox and a checkbox
> control available in RS?
>|||Bruce--Thanks for the reply, I'll look at alternative methods to solve
the problem.
Here are the details of what's required and what I have in place:
I have a database monitoring app the alerts when an event occurs (like
profiler)
I have specific events that need to be trapped and a historical record
needs to be kept of the original event along with any updates to an
event history field. These events are stored in a SQL DB
I need to be able to allow certain users to access this via some sort
of form/webpage, and update the events they are responsible for with
documentation, such as an email or an uploaded file.
I need to be able have a single parent to many children relationship
for the events. For example, event 1 can be chosen as the parent for
events 2,3,4,5,6 and event 1's event history will become the history
of the child events.
I sure I can get the SQL side of things, stored procs and table design.
But I'm struggling with a front end for my users; I was hoping that
RS had the ability to function in this way.
Any suggestions would be greatly appreciated.
Thanks Again,
Damien Johnston
Bruce L-C [MVP] wrote:
> In general this is a bad idea (trying to write data). Some issues you would
> have to deal with are: multi-user, cleaning up data when done, etc.
> Given what you say I don't see why you need to do this. If your stored
> procedure can figure out the data to insert then why can't you just have a
> stored procedure returning the appropriate data to the report. Instead of
> drill down you should be using drill through. Drill through is much more
> efficient. Have a field that you highlight and color blue. Users understand
> that means to click on it. Then use jump to report to call up the report
> with the detail information.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "dij0674" <dij0674@.hotmail.com> wrote in message
> news:1161026120.846569.113660@.e3g2000cwe.googlegroups.com...
> > Hi All,
> >
> > Here's my problem:
> > I need to pull some items into a table report. I have the items grouped
> > by a key field (AlertID) and a detail the the user can drilldown to for
> > each item. I'm having trouble with the linking, I try to link this
> > report to another report that takes the parameters and runs a stored
> > proc to insert the data for the item (Alert History). Each report works
> > fine on by itself, but I can't get them to link in anyway, I've tried
> > Jump to Report and Jump to URL.
> >
> > Any suggestions would be greatly appreciated.
> >
> > Thanks,
> >
> > Damien Johnston
> >
> > P.S. I also need know if there is a writable textbox and a checkbox
> > control available in RS?
> >

Can I hide data fields in a Chart?

I have a Bar/Line Chart with two data fields. Field #1 is displayed as a bar,
Field #2 a line. I would like to allow the user to choose via a parameter if
the line is displayed.
I tried the following expression for the Field:
=iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
But rather than hiding the line, it makes all values zero with the line
still showing.
Is there some way to hide the line?
Thanks.You may want to try:
=iif(Parameters!ShowTrend.Value=True, Fields!Trend.Value, Nothing)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bill" <bill@.discussions.microsoft.com> wrote in message
news:745AF66D-CC9D-4D91-845C-FD4227C08E59@.microsoft.com...
> I have a Bar/Line Chart with two data fields. Field #1 is displayed as a
bar,
> Field #2 a line. I would like to allow the user to choose via a parameter
if
> the line is displayed.
> I tried the following expression for the Field:
> =iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
> But rather than hiding the line, it makes all values zero with the line
> still showing.
> Is there some way to hide the line?
> Thanks.
>|||Show/hide is not supported in charts.
But why don't you try a dynamic series grouping and filter those series
groupings that you don't want show (the Grouping&Sorting dialog for every
chart grouping has a filter tab where you can define expressions to filter
data). This should also take care of the chart legend issue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bill" <bill@.discussions.microsoft.com> wrote in message
news:6B4516F9-D415-4522-838F-BCBAF0789F30@.microsoft.com...
> Thanks for the reply, but "Nothing" gives me the same results as "False"
...
> the line shows-up on bottom with zero values.
> I can get the effect of hiding (sort of) by setting color to transparent.
> But the data series still displays in the legend box.
> Is there no way to dynamically let the user specify what data elements
they
> want to show/hide on a chart?
> Bill
> "Robert Bruckner [MSFT]" wrote:
> > You may want to try:
> > =iif(Parameters!ShowTrend.Value=True, Fields!Trend.Value, Nothing)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "bill" <bill@.discussions.microsoft.com> wrote in message
> > news:745AF66D-CC9D-4D91-845C-FD4227C08E59@.microsoft.com...
> > > I have a Bar/Line Chart with two data fields. Field #1 is displayed as
a
> > bar,
> > > Field #2 a line. I would like to allow the user to choose via a
parameter
> > if
> > > the line is displayed.
> > >
> > > I tried the following expression for the Field:
> > >
> > > =iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
> > >
> > > But rather than hiding the line, it makes all values zero with the
line
> > > still showing.
> > >
> > > Is there some way to hide the line?
> > >
> > > Thanks.
> > >
> > >
> >
> >
> >

Can I encrypt a field of a table using stored procedure?

Can I encrypt a field of a table using stored procedure in SQL Server 2005 Express Edition? And also, how to decrypt it through stored procedure?

Using the encryption functions.

In Books Online, look up the topic: Encryption, sub-topic: Functions.

You can download a version of Books Online for SQLServer Express from:

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx

Can I do this by MDX?

My fact table has a field QtyReleased. I would like to know the number of
records with QtyReleased > 1000.
Can we do this in SQL 2005 by MDX?
GuangmingI believe you can, but do you really need to do this with MDX? Why cant you
handle this on transactional side? Just make a field that contains 1 if
QtyReleased > 1000 and 0 if not. Then all you have to do is make a measure
from that field with a simple sum aggregate.
If you really need to do this with MDX look at the count functions..
MC
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:6AAD5BCE-C77C-4D7E-BC0A-328628576F49@.microsoft.com...
> My fact table has a field QtyReleased. I would like to know the number of
> records with QtyReleased > 1000.
> Can we do this in SQL 2005 by MDX?
> Guangming
>|||MC is right, if you want a "record" count, add a calculated field on the
relational side and create a simple measure in the cube.
If however you want to calculate which hierarchy members have
QtyReleased > 1000, you would do this via MDX.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||I think the problem is coming from QtyReleased and Measure Count in same
measure dimention (fact table).
The rule is changing (QtyReleased > 1000 - > QtyReleased > 1500). I would
like to do it by MDX.
Count() does not take any condition like IIF. I tried both and not working.
Do you have any more thoughts?
Thanks,
Guangming
"Darren Gosbell" wrote:

> MC is right, if you want a "record" count, add a calculated field on the
> relational side and create a simple measure in the cube.
> If however you want to calculate which hierarchy members have
> QtyReleased > 1000, you would do this via MDX.
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>|||I still think MC's original suggestion is probably what you are after.
What you do is to create a view over your fact table and use the view in
your cube instead of using the fact table directly.
The view would look something like the following:
SELECT
..
<column list>
..
, CASE WHEN QtyReleased > 1000 THEN 1 ELSE 0 END AS QtyOverThreashold
FROM FactTable
Then you create a simple sum based measure over the QtyOverThreashold
column from the view.
If still think you need to do this in MDX (and you may - it's hard to
fully understand your situation over a newsgroup ). Then you need to
use the FILTER() function to give you the IIF type logic.
Assuming that you want to know the number of products with QtyReleased >
1000 the MDX would look something like this...
COUNT(
FILTER(
DESCENDANTS([Products].CurrentMember,,LEAVES)
,[Measures].[QtyReleased] > 1000
)
)
This gets the leaf level descendants of the currently selected member of
the product dimension and counts the number of members with QtyReleased
> 1000
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||> I tried the Count() one, which did not work. In my case
> [Measures].[QtyReleased] , and [Measures].[MeasureCount] a
re on Fact table
> as Measures, not as Dimensions.
> So I guess it does not work.
>
In that case just go with my first suggestion of creating a view with a
calculated column and create a measure in the cube off that.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can I do this by MDX?

My fact table has a field QtyReleased. I would like to know the number of
records with QtyReleased > 1000.
Can we do this in SQL 2005 by MDX?
Guangming
I believe you can, but do you really need to do this with MDX? Why cant you
handle this on transactional side? Just make a field that contains 1 if
QtyReleased > 1000 and 0 if not. Then all you have to do is make a measure
from that field with a simple sum aggregate.
If you really need to do this with MDX look at the count functions..
MC
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:6AAD5BCE-C77C-4D7E-BC0A-328628576F49@.microsoft.com...
> My fact table has a field QtyReleased. I would like to know the number of
> records with QtyReleased > 1000.
> Can we do this in SQL 2005 by MDX?
> Guangming
>
|||MC is right, if you want a "record" count, add a calculated field on the
relational side and create a simple measure in the cube.
If however you want to calculate which hierarchy members have
QtyReleased > 1000, you would do this via MDX.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||I think the problem is coming from QtyReleased and Measure Count in same
measure dimention (fact table).
The rule is changing (QtyReleased > 1000 - > QtyReleased > 1500). I would
like to do it by MDX.
Count() does not take any condition like IIF. I tried both and not working.
Do you have any more thoughts?
Thanks,
Guangming
"Darren Gosbell" wrote:

> MC is right, if you want a "record" count, add a calculated field on the
> relational side and create a simple measure in the cube.
> If however you want to calculate which hierarchy members have
> QtyReleased > 1000, you would do this via MDX.
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
|||I still think MC's original suggestion is probably what you are after.
What you do is to create a view over your fact table and use the view in
your cube instead of using the fact table directly.
The view would look something like the following:
SELECT
...
<column list>
...
, CASE WHEN QtyReleased > 1000 THEN 1 ELSE 0 END AS QtyOverThreashold
FROM FactTable
Then you create a simple sum based measure over the QtyOverThreashold
column from the view.
If still think you need to do this in MDX (and you may - it's hard to
fully understand your situation over a newsgroup ). Then you need to
use the FILTER() function to give you the IIF type logic.
Assuming that you want to know the number of products with QtyReleased >
1000 the MDX would look something like this...
COUNT(
FILTER(
DESCENDANTS([Products].CurrentMember,,LEAVES)
,[Measures].[QtyReleased] > 1000
)
)
This gets the leaf level descendants of the currently selected member of
the product dimension and counts the number of members with QtyReleased
> 1000
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||> I tried the Count() one, which did not work. In my case
> [Measures].[QtyReleased] , and [Measures].[MeasureCount] are on Fact table
> as Measures, not as Dimensions.
> So I guess it does not work.
>
In that case just go with my first suggestion of creating a view with a
calculated column and create a measure in the cube off that.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can I determine insert order without an explicit field

I want to be able to find the differences between the before and after
values in a table as updates occur. I thought an easy way to do this
would be to create another table with an identical structure and then
use an update trigger to insert the deleted and inserted rows into
that alternate table. I know what order the rows are in the table
since I will put them in there but how can I, without a time value
column, know which one was inserted into the table first?You can't. It would be very easy to add a column, e.g., InsertDate, with a
default of CURRENT_TIMESTAMP. In your alternate table you wouldn't need a
default on it.
HTH
Vern Rabe
"Computer User" wrote:

> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?
>|||ordering is an aspect of data selection, so you need some sort of
ordering column to indicate time based data.
there's no such thing intrinsically in a sql table as a row number, so
you really don't know what order the rows are in the table.
why wouldn't you want a datetime stamp column?
if you care that data was changed, wouldn't you want to know when it
changed?
you'll probably also want an indicator for which row it was
[deleted/inserted]
Computer User wrote:
> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?|||On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:

>ordering is an aspect of data selection, so you need some sort of
>ordering column to indicate time based data.
>there's no such thing intrinsically in a sql table as a row number, so
>you really don't know what order the rows are in the table.
>
I know that selection usually includes an "order by" clause, but the
data must be in the db in some order.

>why wouldn't you want a datetime stamp column?
>if you care that data was changed, wouldn't you want to know when it
>changed?
>
In this instance, I don't care when the data was changed, only that it
was. A web application is supposed to send an email to an
administrator showing db modifications. Having a "before" row and an
"after" row would make this easy.

>you'll probably also want an indicator for which row it was
>[deleted/inserted]
>
If I knew the order I would know which row it was because I will
insert the deleted row before the inserted row.|||email notifications aren't necessarily terribly reliable.
I find it advisable to have a screen ( as well ) where you can see
notifications.
If you want to be sure of the order then I suggest writing to a log
file would be better than a table.
The order that data is in will not be useful otherwise.
I would recommend creating a table which has a bunch of fields for
before and the same again for after.
Plus your primary (unique ) key, a datestamp and change indicator (
Insert, Update, Delete ).
Write this with your trigger.
What I'd do with it then depends on how dynamic the data is.
I would hope that it's not very dynamic of all this is almost certainly
a complete waste of time.
Anyhow.
Stick a screen on the front of your app that the administrator only
sees with the changes from yesterday and today presented on it.
Use the timestamp to drive the selection.|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
It's in the database in some order, true. But there is no guarantee of
the order in which the server will retrieve rows, unless you impose an
ordering. It is *entirely* up to the server in what order it returns a
set of rows, and the order you receive them in may depend on server
version, patches, number of processors, *workload*, *data volumes*,
*indexes* and *statistics*. (the * ones are ones likely to change just
in the day-to-day use of a database). So if you need to retrieve data
in an order based on when it was inserted, you best record that
information.
In general, for small tables, your data will be returned to you in the
order determined by the clustered index (if it exists), or the order in
which data was inserted (if no clustered index). However, this is for
very small tables (I think as soon as you start using two pages, the
server can start reordering the rows as it sees fit, but not sure)
Damien|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
>
no, it's not. it's wherever the dbms put it. it could be in order, it
might not be, even for clustered indexes.
there is no intrinsic row number or insertion order. if you want one,
you have to add one.

> In this instance, I don't care when the data was changed, only that it
> was. A web application is supposed to send an email to an
> administrator showing db modifications. Having a "before" row and an
> "after" row would make this easy.
>
so what's the problem with adding a column that will help you?
"i don't care when the data was changed..." - famous last words :)

> If I knew the order I would know which row it was because I will
> insert the deleted row before the inserted row.
if you really do not care and can honestly say that you will never care
when the data was changed, then you could add an identity column to your
auditing table.
your better bet would be a single row with before and after values for
each column being audited.

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> table
> do
>|||Well, if you have a varchar(300), and you change it to varchar(200), you
will lose some data in any column that had more than 200 characters...
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> What did you mean by "but if you went the other way, you could lose data"
> ?|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"[vbcol=seagreen]
>|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.
4ax.com...
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
>
table[vbcol=seagreen]
do[vbcol=seagreen]
>|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"[vbcol=seagreen]
>sql

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.
Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>
|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>
|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> table
> do
>
|||If you alter the column to a smaller size, you could lose
data.
-Sue
On Tue, 27 Jul 2004 17:05:22 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Thank you.
>What did you mean by "but if you went the other way, you could lose data" ?
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
>
|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"
>
|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.4ax.com... [vbcol=seagreen]
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
table[vbcol=seagreen]
do
>
|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A
|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"
>

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> > Can I change a field/column width in a script ? For example, I have a
> table
> > where field COL1 is a varchar(200). The table already has data in it. I
> > would like to increase the width of field COL1 to be varchar(300). Can I
> do
> > this in a script ? Thank you.
> >
> >
>|||If you alter the column to a smaller size, you could lose
data.
-Sue
On Tue, 27 Jul 2004 17:05:22 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Thank you.
>What did you mean by "but if you went the other way, you could lose data" ?
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
>> Assuming no keys or constraints reference the column:
>> ALTER TABLE tablename
>> ALTER COLUMN Col1 VARCHAR(300)
>> You won't lose any data if you do this (but if you went the other way, you
>> could).
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
>> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
>> > Can I change a field/column width in a script ? For example, I have a
>> table
>> > where field COL1 is a varchar(200). The table already has data in it. I
>> > would like to increase the width of field COL1 to be varchar(300). Can I
>> do
>> > this in a script ? Thank you.
>> >
>> >
>>
>|||Well, if you have a varchar(300), and you change it to varchar(200), you
will lose some data in any column that had more than 200 characters...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> What did you mean by "but if you went the other way, you could lose data"
> ?|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.4ax.com...
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
> >Can I change a field/column width in a script ? For example, I have a
table
> >where field COL1 is a varchar(200). The table already has data in it. I
> >would like to increase the width of field COL1 to be varchar(300). Can I
do
> >this in a script ? Thank you.
> >
>|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>

can I bind a Label control to a SqlDataSource to display one field from a table?

Hi,

What's the 'new' declarative way to fetch one field from a SQL table and display the result in a Label control?

I have a users table with a fullname field. I want to query the table and retrieve the fullname where the usertable.username = Session("loggedinuser")

I can make the SqlDataSource ok that has the correct Select query, but not sure how to bind the label to that.

thanks,
BruceThe only way I know of, and I hope there is a better way, but I don't know it is to put a formview control on the page. Bind the formview to the datasource, and place the label within the formview, then databind the label to the fullname column.|||Thanks Motley,

I did end up doing that approach. It works ok, just feels a little cumbersome..but maybe it's the correct way for now.

I just feel like there's a missing control that lets you easily bind to a singe output parameter from a query/stored procedure.

-Bruce|||I agree bruce, but unfortunely I lost my inventation to the meeting where the MS team discussed this|||If you're wanting to retrieve the value from a return (output) parameter - that is possible. Take a look at the SqlDataSource controls Selected event. In that event, after the select has taken place, you can retrieve the output paramter's value...

Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected
Me.txtWhatEver.Text = e.Command.Parameters("parameterNameHere").Value
End Sub

This might help too... http://www.asp.net/QuickStart/util/srcview.aspx?path=~/aspnet/samples/data/RetValAndOutputParams.src

I do wish there were a way to retrieve data other than what can be returned in an output parameter... like a field in a DataRow for example. The data controls (like a FormView) are doing this somehow... so why can't I get access to that some functionality in code-behind?

Can I add records to or delete them from an sql database throught Crystal Report

Hello. I would like to know if its possible to add records to an sql database field or delete from it through Crystal Reports? Basically, I wish to setup a html fill in form configured to crystal reports whereby I can pull out results and then if required I could add or delete any record.

Is this possible?

Please help.I dont think it is possible in CR
See if you find solution here
www.businessobjects.com|||Hello, Madhi. Thank you for your reply to my question (and any other questions I posted on the forum). I have found a way I can add and delete records from crystal reports to an sql database using sql commands and crystal formulas (via the business objects website). But its all very basic stuff as yet - good stuff, nevertheless.

I will look begin to look further into this. Thank you for you help.

Regards
Saf|||saf,
if you can please walk me throught how did you get it from that website. There is no forums or anything right? Thanks|||Here is the link for the Business Objects tutorial that I followed:

http://support.businessobjects.com/library/kbase/articles/c2011950.asp

It works. Please follow up on the link.

Can Grow crystal report option - does not grow.

Hi.

I am connecting my report file to the domino server. I have a string in the crystal report which is mapped to a text field. the "Can Grow" option is checked but still this string on the report is truncating the data after some 240+ characters.

Any of you have any idea? Your help would be highly appreciatedCheck whether the field has any junk data|||Hi Madhi.

Thanks for your reply. I have checked that there is no junk data in the field...its all simple text...but still the field does not display all of my data in the report. Any idea?.

thanks again for your help.|||Did you try to expand the filed?|||Yes. I expanded the field width. My field is available in the Details Section. I have increased the width but still the complete information does not show up and blank space is displayed from the point the information is truncated.

Can datetime type store milliseconds

Hi,

I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...

Anyone knows how to store datetime value with milliseconds in the SQL database?

Thanks

All --

FYI, I found the issue that was causing me trouble.

It is a simple matter of display in Enterprise Manager.

When viewing data in Enterprise Manager using the GUI by right-clicking on a Table name and choosing "Return all rows" we see the data...

11/09/2006 15:38:14

...but, when we view the same data using Query Analyzer we see the data...

2006-11-09 15:38:13.907

...which is something quite different.

So, the value is stored with milliseconds but the value is NOT displayed in the same way using the GUI table-browser and Query Analyzer.

That was the crux of my issue.

(Imagine my surprise when I found out my code was actually working.)

:-)

Thank you.

-- Mark Kamoski

|||

If you want to store milliseconds you cannot use SmallDateTime because of limited resolution. The link below should take you in the right direction. Post again if you still have questions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187819.aspx

|||

Hi Caddre,

The datatype i am using is datetime and It's doesn't seem to allow me to add milliseconds to it. I am using sql 2000, but i don't think this have to do with SQL version.

|||

There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.

http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp

|||

thanks,

I managed to store milliseconds

INSERT INTO Table VALUES ({ts '2003-11-05 13:02:43.296'})

|||

The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.

|||

Motley:

The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.

I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?

I know that a SQL Sever 2000 datetime column can store 3 places of time accuracy, such as 12/30/2006 12:01:03.123, as noted here...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp

However, what is not clear is how to get a VS.NET 2003 DateTime variable value to insert into SQL Server datetime column with 3-places of accuracy.

I have tried several things, such as...

DateTime myDateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));

...but SQL Server always seems to trim the milliseconds.

What is the way to accomplish this?

Please advise.

Thank you.

-- Mark Kamoski

|||

Caddre:

There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.

http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp

I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?

The .NET data type DateTime has milliseconds but after I insert them into SQL Server the datetime column does not contain the milliseconds.

I am using ADO.NET, and OLEDB DataAdapter, and simply call Update passing a DataTable.Therefore, I just set the value in the DataTable as a .NET DateTime datatype with high millisecond precision and then call update. Unfortunately, the milliseconds are trimmed.

How can this be done?

|||

It is in the same area with the link you gave Motley. Read carefully and spend time with Katy kam’s blog she does this stuff for the BCL(base class library) team. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_1ckz.asp

http://blogs.msdn.com/kathykam/archive/2006/09/29/773041.aspx

|||

Mark,

If you tried the first post the link is to the general topic because the urls did not change, I have the SQL Server 2005 version but run a search for the topic in SQL Server 2000 BOL(books online) . Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190234.aspx

|||

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

|||

Motley:

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

Motley --

OK. that might work.

I appreciate the clarification.

However, now that I think about it, I want to have as much resolution as possible to the date-time value being stored.

As such, if I want to store the full 7-digits to the right of the decimal point, (the resolution that .NET provides), then I think that I need to store the date as an nvarchar in some known format that can easily be parsed. When retrieving such values, I will have to cast from the SqlServer nvarchar to a .NET DateTime. That will make SQL-based comparisons/mining tricky. However, once the data is in-memory, in .NET code, and properly cast back to a DateTime, such comparison/mining will be simple. That seems to be a decent solution, at least in my case. And so on.

If anyone has a better solution, then please post it.

Thank you.

-- Mark Kamoski

|||

Motley:

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

Please help.

When I go to SQL Server 2000 Enterprise Manager, select a table, and choose "Return All Rows", and then try to enter...

01/01/2001 01:01:01.123

...I get the following error...

The value you entered is not consistent with the data type or length of the column, or over grid buffer limit

...which is strange.

I can enter the data...

01/01/2001 01:01:01.123

...but it will NOT allow entry of milliseconds.

Why?

Oddly enough, it will allow...

update ProgramLog set DateTimeLogged = '01/01/2001 01:02:03.456'

...and it enters the milliseconds but (another interesting point) it rounded-up ".456" to ".457".

SideBar -- Note that SQL Server 2000 Books Online says: "datetime - Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table."

Please advise.

Thank you.

-- Mark Kamoski

Can Auto-increase field

There is a key in the datebase is auto-increase field.
I have a prolem of it. does it have upper boundary of auto-increase?if has, how I change to let it has not upper boundary

I assume you’re referring to identity columns. SQL Server ensures that a unique, incremental value is provided when a row is added.

The identity column’s value is limited only by the numeric limits of the type you choose and the seed and increment values you specify. Consider the following:

MyIdentity int identity (1, 1)

Values will be assigned beginning with 1 and increasing to an upper limit of 2,147,483,647. If you’re concerned about running out you have two options: (a) increase the size of the column (e.g. use bigint) or (b) use a seed value less than 1. For example, to get the most out of the identity column without increasing its size you can use the following:

MyIdentity int identity (-2147483648, 1)

If this is still too prohibitive then you may want to consider using the uniqueidentifier type.

Cheers,
Kenny Kerr

http://weblogs.asp.net/kennykerr/

can a textbox hold the value of more than 1 field? maybe use funct

Hello,
My question is if a textbox in a report can contain values from multiple
fields from the data source:
txt1.value
=Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
If this is doable, what is the method/correct method?
I can add multiple fields to one textbox in an MS Access Report. Can this
be done in a Reporting Services Report? I am thinking I could use a function
which would return the concatenated values of these fields as a string. What
would the code for that function look like?
Thanks,
RichI figured out my problem. I added some new fields to my dataset, but not to
the report. Gotta do that for them to compile without complaining.
"Rich" wrote:
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a function
> which would return the concatenated values of these fields as a string. What
> would the code for that function look like?
> Thanks,
> Rich|||You are correct, your format looks correct to me.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a
> function
> which would return the concatenated values of these fields as a string.
> What
> would the code for that function look like?
> Thanks,
> Rich|||Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
who can I report that too?
My actual project is using the reportviewer control that comes with VS2005
(it is almost the same as RS except doesn't require a server - and a few
other things). It works pretty good, but when I select a tractor feeding
printer (one of those older wide paper - dotmatrix like printers) if I tell
the layout to print landscape when using US STD Fanfold paper , the little
icon in the dialog display portrait and it prints portrait. Then if I tell
it Portrait when using the US STD Fanfold papter with tractor feed printer -
the icon displays landscapte and prints landscape. It is pretty obvious that
someone mixed up the options.
So I am not trying to be mr. picky, but when the end user uses my product,
it needs to work according to the standards. Who can I report this too?
Thanks,
Rich
"Steve MunLeeuw" wrote:
> You are correct, your format looks correct to me.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> > Hello,
> >
> > My question is if a textbox in a report can contain values from multiple
> > fields from the data source:
> >
> > txt1.value
> >
> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> >
> > If this is doable, what is the method/correct method?
> >
> > I can add multiple fields to one textbox in an MS Access Report. Can this
> > be done in a Reporting Services Report? I am thinking I could use a
> > function
> > which would return the concatenated values of these fields as a string.
> > What
> > would the code for that function look like?
> >
> > Thanks,
> > Rich
>
>|||http://connect.microsoft.com/SQLServer/Feedback
Yeah, dealing with different page sizes can be tricky from what I gather.
Luckily I haven't had to deal with that much. Adobe allows you to have
pages in both landscape and portrait in the same document I was asked if I
could do that the other day. I don't think I could.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D2173E9D-E9AF-410D-AFEE-E919AF360951@.microsoft.com...
> Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
> who can I report that too?
> My actual project is using the reportviewer control that comes with VS2005
> (it is almost the same as RS except doesn't require a server - and a few
> other things). It works pretty good, but when I select a tractor feeding
> printer (one of those older wide paper - dotmatrix like printers) if I
> tell
> the layout to print landscape when using US STD Fanfold paper , the little
> icon in the dialog display portrait and it prints portrait. Then if I
> tell
> it Portrait when using the US STD Fanfold papter with tractor feed
> printer -
> the icon displays landscapte and prints landscape. It is pretty obvious
> that
> someone mixed up the options.
> So I am not trying to be mr. picky, but when the end user uses my product,
> it needs to work according to the standards. Who can I report this too?
> Thanks,
> Rich
> "Steve MunLeeuw" wrote:
>> You are correct, your format looks correct to me.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
>> > Hello,
>> >
>> > My question is if a textbox in a report can contain values from
>> > multiple
>> > fields from the data source:
>> >
>> > txt1.value
>> >
>> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
>> >
>> > If this is doable, what is the method/correct method?
>> >
>> > I can add multiple fields to one textbox in an MS Access Report. Can
>> > this
>> > be done in a Reporting Services Report? I am thinking I could use a
>> > function
>> > which would return the concatenated values of these fields as a string.
>> > What
>> > would the code for that function look like?
>> >
>> > Thanks,
>> > Rich
>>

Can a new field be added to an existing table.

Can a new field be added to a table that already exists.

Thanks in advance."kjc" <ksitron@.elp.rr.com> wrote in message
news:Rqi2d.2380$3b.1942@.fe2.texas.rr.com...
> Can a new field be added to a table that already exists.
> Thanks in advance.

Yes - see ALTER TABLE and "Adding and Deleting Columns" in Books Online:

alter table dbo.MyTable add MyColumn int null

Simon|||Thanks a bunch

Simon Hayes wrote:
> "kjc" <ksitron@.elp.rr.com> wrote in message
> news:Rqi2d.2380$3b.1942@.fe2.texas.rr.com...
>>Can a new field be added to a table that already exists.
>>
>>Thanks in advance.
>>
>
> Yes - see ALTER TABLE and "Adding and Deleting Columns" in Books Online:
> alter table dbo.MyTable add MyColumn int null
> Simon

Can a field be made mandatory for just new records?

Hi,
Is there any way in SQL server 2000, of making a field mandatory in an
existing table (which already contains thousands of records), without
having to update all of the existing records?
In otherwords, can a field be made mandatory for just new records?
If not, is the only solution to code it into my front end application?
Thanks
ColinThis doesn't really make much sense to me, so far. What is the point of
making the column mandatory if you're not going to update the existing rows?
Is there some application limitation that requires a value? If so, why
would the limitation only be relevant on new rows? Can the application not
look at old rows? Why is it okay for an old row to be NULL and not for a
new row? Just trying to understand the logistics.
If you want only new rows to contain a value, then it is fairly trivial to
have your insert stored procedure (you are using stored procedures, right?)
make that parameter NOT optional, and return an error if it is NULL. (You
will probably want to slightly change your form appearance and/or
validation.) But you're not going to be able to enforce this at the table
level, as far as I can tell (but maybe if you detail your reasoning it may
spawn additional thought).
A
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1189685999.863746.93220@.g4g2000hsf.googlegroups.com...
> Hi,
> Is there any way in SQL server 2000, of making a field mandatory in an
> existing table (which already contains thousands of records), without
> having to update all of the existing records?
> In otherwords, can a field be made mandatory for just new records?
> If not, is the only solution to code it into my front end application?
> Thanks
> Colin
>|||On 13 Sep, 13:31, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> This doesn't really make much sense to me, so far. What is the point of
> making the column mandatory if you're not going to update the existing rows?
> Is there some application limitation that requires a value? If so, why
> would the limitation only be relevant on new rows? Can the application not
> look at old rows? Why is it okay for an old row to be NULL and not for a
> new row? Just trying to understand the logistics.
>
My FE application is written in Access 2003. I have a form which I use
to create Purchase Orders. This form has a sub form for PO Items. Due
to a change in company procedures, I need to add four fields to the
sub form which all require user input. However, this only applies to
new POs. There is no sense in going back through five years worth of
(20,000) existing POs to make sure that all four columns conform and
have the correct data.
> If you want only new rows to contain a value, then it is fairly trivial to
> have your insert stored procedure (you are using stored procedures, right?)
> make that parameter NOT optional, and return an error if it is NULL. (You
> will probably want to slightly change your form appearance and/or
> validation.) But you're not going to be able to enforce this at the table
> level, as far as I can tell (but maybe if you detail your reasoning it may
> spawn additional thought).
>
I'm not using a stored procedure on this form, but perhaps that's the
answer.
Thanks for your help
Colin|||> new POs. There is no sense in going back through five years worth of
> (20,000) existing POs to make sure that all four columns conform and
> have the correct data.
No, but you could update them all in one shot with some token value (e.g.
N/A) and then you could apply your constraint and prevent further rows from
being un-populated.
A|||<snip>
> to a change in company procedures, I need to add four fields to the
> sub form which all require user input. However, this only applies to
> new POs. There is no sense in going back through five years worth of
> (20,000) existing POs to make sure that all four columns conform and
> have the correct data.
Before you go further, why don't you step through the process of what is
expected when someone modifies a PO created before your change (regardless
of how it is accomplished). Will your front-end somehow "know" that the PO
was created before the requirement and will correctly "adjust" its
appearance and logic to account for this not-present and not-required data?
If you have difficulty answering that question, then you are in a bit of a
cart-before-horse situation since you need to define the business logic
first.
There is an alternative that will support your stated goal. Create a
dependent table (in a 1-0/1) relationship that contains your new columns.
Your existing rows will have no associated row in this new table, while any
orders created (and, perhaps, modified) after this change will (or at least
can) have a row.|||On Sep 13, 7:19 am, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi,
> Is there any way in SQL server 2000, of making a field mandatory in an
> existing table (which already contains thousands of records), without
> having to update all of the existing records?
> In otherwords, can a field be made mandatory for just new records?
> If not, is the only solution to code it into my front end application?
> Thanks
> Colin
I agree with Aaron - you most likely don't want do it. Yet it is
doable:
CREATE TABLE a(i INT)
INSERT a(i) VALUES(NULL)
GO
ALTER TABLE a WITH NOCHECK ADD CONSTRAINT a_i_notnull CHECK(i IS NOT
NULL)
-- creates OK
GO
INSERT a(i) VALUES(NULL)
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint
"a_i_notnull". The conflict occurred in database "FinancialDW", table
"dbo.a", column 'i'.
The statement has been terminated.
*/|||> I agree with Aaron - you most likely don't want do it. Yet it is
> doable:
Yes, of course. Why do I always forget NOCHECK? Probably because it's not
a very good practice for this kind of situation. :-)
A

Can a data region...

reference a parameter? For instance, I have a table control and in the detail field I have an expression.

=IIf(Parameters!Consignee.Value = True, "True","False")

But it doens't work.....any suggestions?

Michael

That should work - what happens when you try it? Did you make the data type Boolean?|||

I get "#Error" in the field.

All I did to get around this was to make a derived table in my main query that filters for the specific parameter, and then joined it back in appropriately. This made it a field instead of a parameter reference and it worked fine. Thanks for the response.

Michael