regarding datetime in asp.net

hi ,

i had textbox which is used to enter datetime . in the database, the data type is date/time. now my requirement is when the textbox is empty. i want 2 store a default value. which value is suitable for datetime.

i had given as follows

if textbox12.text = "" then

textbox12.text = 5/5/2000 ---- it is storing this value.

end if

but if i want 2 update this date means it is not supporting . if i select current date using calender it was saved as 12/30/1899.

can anyone help me.

regards

m.arunachalam

You can try
textbox12.Text = DateTime.Today

regarding BLOB

Does sql server has the data type similar to BLOB (Binary larger
object)which is available to DB2. BLOB in DB2 can support up to 2 G
(variable-length data )

if it does have, which one offers better functionalities

any advice will be greatly appreciated!causacn@.gmail.com wrote:
> Does sql server has the data type similar to BLOB (Binary larger
> object)which is available to DB2. BLOB in DB2 can support up to 2 G
> (variable-length data )
> if it does have, which one offers better functionalities
> any advice will be greatly appreciated!

Binary and varbinary. How they compare to DB2's blob I don't know.|||Binary and varbinary are "fixed length" in the same sense as char and
varchar. If you want to store a large amount of binary data, you
probably want the "image" type.|||i just checked the sql server docs.
it seems image type is DB2 blobs equivalent|||(causacn@.gmail.com) writes:
> Does sql server has the data type similar to BLOB (Binary larger
> object)which is available to DB2. BLOB in DB2 can support up to 2 G
> (variable-length data )
> if it does have, which one offers better functionalities
> any advice will be greatly appreciated!

The "classic" answer in this regard is the image data type, with its
cousins text and ntext for character data.

But in the new version of SQL Server, SQL 2005, just recently released,
there is a new data type: varbinary(MAX) as well as varchar(MAX) and
nvarchar(MAX). If you are on SQL 2005, you should use the MAX data types,
as they are lot easier to deal with than image/text/ntext.

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

Reg: SqlServer Data Type

Hi
Can you please explain the difference between Text and nText data type in
SqlServer and at what situation we have to use this.
Siva.ntext is national text and it is used for storing unicode data.
text is used for storing non-unicode data
ntext occupies twice the size for storing the same data as in text
ntext supports all characters defined in unicode standard.|||and this is from BOL.. if you had not already seen it.
Using Unicode Data
The Unicode specification defines a single encoding scheme for most
characters widely used in businesses around the world. All computers
consistently translate the bit patterns in Unicode data into characters usin
g
the single Unicode specification. This ensures that the same bit pattern is
always converted to the same character on all computers. Data can be freely
transferred from one database or computer to another without concern that th
e
receiving system will translate the bit patterns into characters incorrectly
.
One problem with data types that use 1 byte to encode each character is that
the data type can only represent 256 different characters. This forces
multiple encoding specifications (or code pages) for different alphabets suc
h
as European alphabets, which are relatively small. It is also impossible to
handle systems such as the Japanese Kanji or Korean Hangul alphabets that
have thousands of characters.
Each Microsoft? SQL Server? collation has a code page that defines what
patterns of bits represent each character in char, varchar, and text values.
Individual columns and character constants can be assigned a different code
page. Client computers use the code page associated with the operating syste
m
locale to interpret character bit patterns. There are many different code
pages. Some characters appear on some code pages, but not on others. Some
characters are defined with one bit pattern on some code pages, and with a
different bit pattern on other code pages. When you build international
systems that must handle different languages, it becomes difficult to pick
code pages for all the computers that meet the language requirements of
multiple countries. It is also difficult to ensure that every computer
performs the correct translations when interfacing with a system using a
different code page.
The Unicode specification addresses this problem by using 2 bytes to encode
each character. There are enough different patterns (65,536) in 2 bytes for
a
single specification covering the most common business languages. Because al
l
Unicode systems consistently use the same bit patterns to represent all
characters, there is no problem with characters being converted incorrectly
when moving from one system to another. You can minimize character conversio
n
issues by using Unicode data types throughout your system.
In Microsoft SQL Server, these data types support Unicode data:
nchar
nvarchar
ntext
Note The n prefix for these data types comes from the SQL-92 standard for
National (Unicode) data types.
Use of nchar, nvarchar, and ntext is the same as char, varchar, and text,
respectively, except that:
Unicode supports a wider range of characters.
More space is needed to store Unicode characters.
The maximum size of nchar and nvarchar columns is 4,000 characters, not
8,000 characters like char and varchar.
Unicode constants are specified with a leading N: N'A Unicode string'.
All Unicode data uses the same Unicode code page. Collations do not control
the code page used for Unicode columns, only attributes such as comparison
rules and case sensitivity.|||SivaprakashShanmugam a écrit :
> Hi
> Can you please explain the difference between Text and nText data type in
> SqlServer and at what situation we have to use this.
NTEXT is encoded as UNICODE wich use 2 byte per chars
TEXT is encoded as ASCII wich use 1 byte per chars
This mean :
1) the amount of data is double while using NTEXT so the perfs whyle
retrieving data will be slower
2) only NTEXT an be use simultaneously by many alphabets like latin,
gr, cyrilic, hebrew, japanese, chinese...
A +

> Siva.
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven

refresh fields button does not work?

I have a dataset that calls a stored procedure. it's command type is set to
stored procedure.
The stored procedure returns 1 result set from a temp table (like select *
from #temp). In sql query analyzer it only returns 1 grid so I know it's not
returning multiple result sets.
when I hit the ! button I fill out my parameters and run the stored
procedure. It returns all the columns with some data. good. but the only
field I see is one called ID of type database field.
I hit the refresh fields button and nothing changes. The dataset is
returning 1 result set with the columns and data. WhyOWhy are the fields not
being filled out?What backend are you going against (SQL Server, OLEDB, ODBC?).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> I have a dataset that calls a stored procedure. it's command type is set
to
> stored procedure.
> The stored procedure returns 1 result set from a temp table (like select *
> from #temp). In sql query analyzer it only returns 1 grid so I know it's
not
> returning multiple result sets.
> when I hit the ! button I fill out my parameters and run the stored
> procedure. It returns all the columns with some data. good. but the only
> field I see is one called ID of type database field.
> I hit the refresh fields button and nothing changes. The dataset is
> returning 1 result set with the columns and data. WhyOWhy are the fields
not
> being filled out?|||I figured it out!
in my stored procedure I was doing this
create table [#whatever]
select * from #whatever
this runs fine from sql query analyzer but does not return a list of fields
in report designer gui.
but changing my stored procedure to
create table [#whatever]
select * from [#whatever]
fixed the problem. I now get the entire list of fields in the gui.
I'm not sure if I would call that a bug or not but it sure was anoying
trying to figure it out over the last several hours.
"Bruce L-C [MVP]" wrote:
> What backend are you going against (SQL Server, OLEDB, ODBC?).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > I have a dataset that calls a stored procedure. it's command type is set
> to
> > stored procedure.
> >
> > The stored procedure returns 1 result set from a temp table (like select *
> > from #temp). In sql query analyzer it only returns 1 grid so I know it's
> not
> > returning multiple result sets.
> >
> > when I hit the ! button I fill out my parameters and run the stored
> > procedure. It returns all the columns with some data. good. but the only
> > field I see is one called ID of type database field.
> >
> > I hit the refresh fields button and nothing changes. The dataset is
> > returning 1 result set with the columns and data. WhyOWhy are the fields
> not
> > being filled out?
>
>|||Hmmm, you must have had some special characters in it. I create temporary
tables and do a select * from it without have to put [] around it. I'll
remember that though since from time to time I have been unable to help
people who do not get the field list. Learn something new every day.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> I figured it out!
> in my stored procedure I was doing this
> create table [#whatever]
> select * from #whatever
> this runs fine from sql query analyzer but does not return a list of
fields
> in report designer gui.
> but changing my stored procedure to
> create table [#whatever]
> select * from [#whatever]
> fixed the problem. I now get the entire list of fields in the gui.
> I'm not sure if I would call that a bug or not but it sure was anoying
> trying to figure it out over the last several hours.
>
> "Bruce L-C [MVP]" wrote:
> > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > I have a dataset that calls a stored procedure. it's command type is
set
> > to
> > > stored procedure.
> > >
> > > The stored procedure returns 1 result set from a temp table (like
select *
> > > from #temp). In sql query analyzer it only returns 1 grid so I know
it's
> > not
> > > returning multiple result sets.
> > >
> > > when I hit the ! button I fill out my parameters and run the stored
> > > procedure. It returns all the columns with some data. good. but the
only
> > > field I see is one called ID of type database field.
> > >
> > > I hit the refresh fields button and nothing changes. The dataset is
> > > returning 1 result set with the columns and data. WhyOWhy are the
fields
> > not
> > > being filled out?
> >
> >
> >|||all right I take it back after more testing I can reproduce this behavior but
I was not aware it worked like this.
forget what I said before it was just a fluke I must have made a mistake
while testing it.
here is the reproducable behavior.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE
dbo.whatever
@.param_me int
as
-- end results temp table
create table [#whatever]
(
[key] int not null,
)
if (@.param_me is null)
begin
select 'how did you get here?'
end
else
begin
insert into [#whatever]([key])values(1)
insert into [#whatever]([key])values(2)
insert into [#whatever]([key])values(3)
end
select * from [#whatever]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create a dataset that calls whatever passing a param value
it looks like even though the select 'how did you get here?' never gets run
it somehow gets returned as the first result set?
bruce could you give this a whirl and see if you see the same result? I
would appreciate it thanks.
"Bruce L-C [MVP]" wrote:
> Hmmm, you must have had some special characters in it. I create temporary
> tables and do a select * from it without have to put [] around it. I'll
> remember that though since from time to time I have been unable to help
> people who do not get the field list. Learn something new every day.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > I figured it out!
> >
> > in my stored procedure I was doing this
> > create table [#whatever]
> > select * from #whatever
> >
> > this runs fine from sql query analyzer but does not return a list of
> fields
> > in report designer gui.
> >
> > but changing my stored procedure to
> > create table [#whatever]
> > select * from [#whatever]
> >
> > fixed the problem. I now get the entire list of fields in the gui.
> >
> > I'm not sure if I would call that a bug or not but it sure was anoying
> > trying to figure it out over the last several hours.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > > I have a dataset that calls a stored procedure. it's command type is
> set
> > > to
> > > > stored procedure.
> > > >
> > > > The stored procedure returns 1 result set from a temp table (like
> select *
> > > > from #temp). In sql query analyzer it only returns 1 grid so I know
> it's
> > > not
> > > > returning multiple result sets.
> > > >
> > > > when I hit the ! button I fill out my parameters and run the stored
> > > > procedure. It returns all the columns with some data. good. but the
> only
> > > > field I see is one called ID of type database field.
> > > >
> > > > I hit the refresh fields button and nothing changes. The dataset is
> > > > returning 1 result set with the columns and data. WhyOWhy are the
> fields
> > > not
> > > > being filled out?
> > >
> > >
> > >
>
>|||I think what is happening is that it is considering the first select to be
the first return result. You could temporarily remove that part, get your
field list and design your report and then put it back in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> all right I take it back after more testing I can reproduce this behavior
> but
> I was not aware it worked like this.
> forget what I said before it was just a fluke I must have made a mistake
> while testing it.
> here is the reproducable behavior.
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE
> dbo.whatever
> @.param_me int
> as
> -- end results temp table
> create table [#whatever]
> (
> [key] int not null,
> )
> if (@.param_me is null)
> begin
> select 'how did you get here?'
> end
> else
> begin
> insert into [#whatever]([key])values(1)
> insert into [#whatever]([key])values(2)
> insert into [#whatever]([key])values(3)
> end
> select * from [#whatever]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> create a dataset that calls whatever passing a param value
> it looks like even though the select 'how did you get here?' never gets
> run
> it somehow gets returned as the first result set?
> bruce could you give this a whirl and see if you see the same result? I
> would appreciate it thanks.
>
> "Bruce L-C [MVP]" wrote:
>> Hmmm, you must have had some special characters in it. I create temporary
>> tables and do a select * from it without have to put [] around it. I'll
>> remember that though since from time to time I have been unable to help
>> people who do not get the field list. Learn something new every day.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
>> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
>> > I figured it out!
>> >
>> > in my stored procedure I was doing this
>> > create table [#whatever]
>> > select * from #whatever
>> >
>> > this runs fine from sql query analyzer but does not return a list of
>> fields
>> > in report designer gui.
>> >
>> > but changing my stored procedure to
>> > create table [#whatever]
>> > select * from [#whatever]
>> >
>> > fixed the problem. I now get the entire list of fields in the gui.
>> >
>> > I'm not sure if I would call that a bug or not but it sure was anoying
>> > trying to figure it out over the last several hours.
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
>> > > message
>> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
>> > > > I have a dataset that calls a stored procedure. it's command type
>> > > > is
>> set
>> > > to
>> > > > stored procedure.
>> > > >
>> > > > The stored procedure returns 1 result set from a temp table (like
>> select *
>> > > > from #temp). In sql query analyzer it only returns 1 grid so I
>> > > > know
>> it's
>> > > not
>> > > > returning multiple result sets.
>> > > >
>> > > > when I hit the ! button I fill out my parameters and run the stored
>> > > > procedure. It returns all the columns with some data. good. but
>> > > > the
>> only
>> > > > field I see is one called ID of type database field.
>> > > >
>> > > > I hit the refresh fields button and nothing changes. The dataset
>> > > > is
>> > > > returning 1 result set with the columns and data. WhyOWhy are the
>> fields
>> > > not
>> > > > being filled out?
>> > >
>> > >
>> > >
>>|||Yes I think thatâ's become obvious; the first result set that is tripping the
refresh fields button up. Because if I give the select 'how did you get
here?' as error_column. I see error_column in the list of fields, even
though that result set did not get returned and the data for the other result
set is really being displayed.
It's just confusing. All of the tools I'm using query analyzer, query
designer don't return this result set when I run the stored procedure with a
param value other than null.
Whatever the refresh fields button does it's not refresh fields, it's should
be called "return first potential result field list regardless of what
columns are really returned"
It's not a show stopper there are lots of easy work a rounds. It just sucks
using a version 1 product with so many of these little time wasters. But I
already know you think rs is super duper, I don't think so just yet.
try using rs.exe to create a datasource, publish a report and set the
published report datasource in your rs.exe script. It does not work I opened
up a support case 2 weeks ago with microsoft and they are still trying to
figure out why it does not work. lots of little time wasters.
ok I'm done complaining now.
"Bruce L-C [MVP]" wrote:
> I think what is happening is that it is considering the first select to be
> the first return result. You could temporarily remove that part, get your
> field list and design your report and then put it back in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > all right I take it back after more testing I can reproduce this behavior
> > but
> > I was not aware it worked like this.
> >
> > forget what I said before it was just a fluke I must have made a mistake
> > while testing it.
> >
> > here is the reproducable behavior.
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > CREATE PROCEDURE
> > dbo.whatever
> > @.param_me int
> >
> > as
> >
> > -- end results temp table
> > create table [#whatever]
> > (
> > [key] int not null,
> > )
> >
> > if (@.param_me is null)
> > begin
> > select 'how did you get here?'
> > end
> > else
> > begin
> > insert into [#whatever]([key])values(1)
> > insert into [#whatever]([key])values(2)
> > insert into [#whatever]([key])values(3)
> > end
> > select * from [#whatever]
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > create a dataset that calls whatever passing a param value
> >
> > it looks like even though the select 'how did you get here?' never gets
> > run
> > it somehow gets returned as the first result set?
> >
> > bruce could you give this a whirl and see if you see the same result? I
> > would appreciate it thanks.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Hmmm, you must have had some special characters in it. I create temporary
> >> tables and do a select * from it without have to put [] around it. I'll
> >> remember that though since from time to time I have been unable to help
> >> people who do not get the field list. Learn something new every day.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> >> > I figured it out!
> >> >
> >> > in my stored procedure I was doing this
> >> > create table [#whatever]
> >> > select * from #whatever
> >> >
> >> > this runs fine from sql query analyzer but does not return a list of
> >> fields
> >> > in report designer gui.
> >> >
> >> > but changing my stored procedure to
> >> > create table [#whatever]
> >> > select * from [#whatever]
> >> >
> >> > fixed the problem. I now get the entire list of fields in the gui.
> >> >
> >> > I'm not sure if I would call that a bug or not but it sure was anoying
> >> > trying to figure it out over the last several hours.
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> >> > > message
> >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> >> > > > I have a dataset that calls a stored procedure. it's command type
> >> > > > is
> >> set
> >> > > to
> >> > > > stored procedure.
> >> > > >
> >> > > > The stored procedure returns 1 result set from a temp table (like
> >> select *
> >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> >> > > > know
> >> it's
> >> > > not
> >> > > > returning multiple result sets.
> >> > > >
> >> > > > when I hit the ! button I fill out my parameters and run the stored
> >> > > > procedure. It returns all the columns with some data. good. but
> >> > > > the
> >> only
> >> > > > field I see is one called ID of type database field.
> >> > > >
> >> > > > I hit the refresh fields button and nothing changes. The dataset
> >> > > > is
> >> > > > returning 1 result set with the columns and data. WhyOWhy are the
> >> fields
> >> > > not
> >> > > > being filled out?
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Glad you got it to work out. Yes I am a supporter of RS but I don't deny
there are things that need to get better (it is version 1). That said, I
think any product or development work has these types of frustrations. I
just spent a day mucking around with linked databases and finally found that
the issue comes down to the oledb/odbc provider for Sybase. So, day wasted
and figure out some other way to do it. I just try to get people to be able
to get done what they are trying to do.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:D3C917F1-06B6-4B03-92DC-7D6D261EFAC4@.microsoft.com...
> Yes I think that's become obvious; the first result set that is tripping
the
> refresh fields button up. Because if I give the select 'how did you get
> here?' as error_column. I see error_column in the list of fields, even
> though that result set did not get returned and the data for the other
result
> set is really being displayed.
> It's just confusing. All of the tools I'm using query analyzer, query
> designer don't return this result set when I run the stored procedure with
a
> param value other than null.
> Whatever the refresh fields button does it's not refresh fields, it's
should
> be called "return first potential result field list regardless of what
> columns are really returned"
> It's not a show stopper there are lots of easy work a rounds. It just
sucks
> using a version 1 product with so many of these little time wasters. But
I
> already know you think rs is super duper, I don't think so just yet.
> try using rs.exe to create a datasource, publish a report and set the
> published report datasource in your rs.exe script. It does not work I
opened
> up a support case 2 weeks ago with microsoft and they are still trying to
> figure out why it does not work. lots of little time wasters.
> ok I'm done complaining now.
>
> "Bruce L-C [MVP]" wrote:
> > I think what is happening is that it is considering the first select to
be
> > the first return result. You could temporarily remove that part, get
your
> > field list and design your report and then put it back in.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > > all right I take it back after more testing I can reproduce this
behavior
> > > but
> > > I was not aware it worked like this.
> > >
> > > forget what I said before it was just a fluke I must have made a
mistake
> > > while testing it.
> > >
> > > here is the reproducable behavior.
> > >
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > CREATE PROCEDURE
> > > dbo.whatever
> > > @.param_me int
> > >
> > > as
> > >
> > > -- end results temp table
> > > create table [#whatever]
> > > (
> > > [key] int not null,
> > > )
> > >
> > > if (@.param_me is null)
> > > begin
> > > select 'how did you get here?'
> > > end
> > > else
> > > begin
> > > insert into [#whatever]([key])values(1)
> > > insert into [#whatever]([key])values(2)
> > > insert into [#whatever]([key])values(3)
> > > end
> > > select * from [#whatever]
> > >
> > > GO
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > create a dataset that calls whatever passing a param value
> > >
> > > it looks like even though the select 'how did you get here?' never
gets
> > > run
> > > it somehow gets returned as the first result set?
> > >
> > > bruce could you give this a whirl and see if you see the same result?
I
> > > would appreciate it thanks.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Hmmm, you must have had some special characters in it. I create
temporary
> > >> tables and do a select * from it without have to put [] around it.
I'll
> > >> remember that though since from time to time I have been unable to
help
> > >> people who do not get the field list. Learn something new every day.
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
message
> > >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > >> > I figured it out!
> > >> >
> > >> > in my stored procedure I was doing this
> > >> > create table [#whatever]
> > >> > select * from #whatever
> > >> >
> > >> > this runs fine from sql query analyzer but does not return a list
of
> > >> fields
> > >> > in report designer gui.
> > >> >
> > >> > but changing my stored procedure to
> > >> > create table [#whatever]
> > >> > select * from [#whatever]
> > >> >
> > >> > fixed the problem. I now get the entire list of fields in the gui.
> > >> >
> > >> > I'm not sure if I would call that a bug or not but it sure was
anoying
> > >> > trying to figure it out over the last several hours.
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> > >> > > message
> > >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > >> > > > I have a dataset that calls a stored procedure. it's command
type
> > >> > > > is
> > >> set
> > >> > > to
> > >> > > > stored procedure.
> > >> > > >
> > >> > > > The stored procedure returns 1 result set from a temp table
(like
> > >> select *
> > >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> > >> > > > know
> > >> it's
> > >> > > not
> > >> > > > returning multiple result sets.
> > >> > > >
> > >> > > > when I hit the ! button I fill out my parameters and run the
stored
> > >> > > > procedure. It returns all the columns with some data. good.
but
> > >> > > > the
> > >> only
> > >> > > > field I see is one called ID of type database field.
> > >> > > >
> > >> > > > I hit the refresh fields button and nothing changes. The
dataset
> > >> > > > is
> > >> > > > returning 1 result set with the columns and data. WhyOWhy are
the
> > >> fields
> > >> > > not
> > >> > > > being filled out?
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >

Redudant Data on a table column...

Good mornig,

I want my table column to accept redudant data type, so what to do?

Regards

What do you mean by that ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

every day i got information from my client, like an xls file (MS EXCEL sheet), when trying to import this data on the database that i have created using SSIS, i got an message,.... the data that i want to import content redudant data. then i want to know what to do in order to import data from an .xls file, event this file content redudant data ( same data in differents field of a culomn) using SSIS or other methode.

my english is not correct but i do my best.

please help me.

Best regards.

|||Are you getting an error ? if yes, what is it ?

HTH, Jens Suessmeyer.

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

It's most likely that the fields in your table that you want to contain redundent data actually have a unique constraint on them so that they don't accept redundent data. This happens automatically if the field is a Primary Key.

If the problem field is not the Primary Key, then you may be OK removing the constraint, if it is the Primary Key, then it must be unique beause it's is being used to uniquely identify the record. In this case you would need to either find a different Primary Key (which must be unique) or put your redundent data in a differnt field.

If you data is very redundent, then you may need to consider normalizing it by putting it into multiple tables to eliminate redundent entries. SSIS can do this as part of the transformation step. You can find information about what database normalization is and how to do it. Consider starting here to learn more.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Reduce SQLServer Wait Time

Hi,
I ran waittype procedure to for 10 mins to check the sqlserver waittype and
time and got the following result
wait type
wait time percentage
----
-- -- --
***total***
7086.0 100.0
CXPACKET
3254.0 45.9
LATCH_EX
3034.0 42.8
.
..
I just want to reduce the above wait types,any idea. Currently the max
degree of parallelism is 0 .
SQL BOX has 2GB Memory and SQLServer is configured to use 1032MB fixed.
Env:
SQL2000 + SP3 /Windows 2000 Advance Server 2 Processors with Hyper-Threading
Technology.
thanksLatch_EX wait times usually indicate you are having a lot of memory pressure
and sql server has to spend a lot of time swapping data or procedure pages
in and out of memory. The cxpacket waits are most likely due to the fact
that your procs are too busy doing this and can't keep up with each other.
My guess is that the HT is getting in the way some. Bottom line is you can
probably use more ram but here are as few things to try and see if they
help. One is to turn off HT. Another is to change the MAXDOP at the server
level. Try 2 first and then 1 if that doesn't help. Why do you only have
1GB allocated to SQL Server if you have 2GB total? Try bumping it up a few
hundred MB and see if that helps some. How much procedure cache do you
have? Do you have lots of recompiles or plans that are not reused? If so
then work on plan reuse.
--
Andrew J. Kelly SQL MVP
"kris" <kpxus@.yahoo.com> wrote in message
news:OqCgT0obEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I ran waittype procedure to for 10 mins to check the sqlserver waittype
and
> time and got the following result
> wait type
> wait time percentage
> ----
--
> -- -- --
> ***total***
> 7086.0 100.0
> CXPACKET
> 3254.0 45.9
> LATCH_EX
> 3034.0 42.8
> .
> ..
> I just want to reduce the above wait types,any idea. Currently the max
> degree of parallelism is 0 .
> SQL BOX has 2GB Memory and SQLServer is configured to use 1032MB fixed.
> Env:
> SQL2000 + SP3 /Windows 2000 Advance Server 2 Processors with
Hyper-Threading
> Technology.
> thanks
>|||Andrew,
thanks for your suggestions, actually another application is running on the
sqlbox which is taking 150MB. I will change the settings as per your
suggestion and again i will check that.
thanks
kris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lys46obEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Latch_EX wait times usually indicate you are having a lot of memory
pressure
> and sql server has to spend a lot of time swapping data or procedure pages
> in and out of memory. The cxpacket waits are most likely due to the fact
> that your procs are too busy doing this and can't keep up with each other.
> My guess is that the HT is getting in the way some. Bottom line is you
can
> probably use more ram but here are as few things to try and see if they
> help. One is to turn off HT. Another is to change the MAXDOP at the
server
> level. Try 2 first and then 1 if that doesn't help. Why do you only have
> 1GB allocated to SQL Server if you have 2GB total? Try bumping it up a
few
> hundred MB and see if that helps some. How much procedure cache do you
> have? Do you have lots of recompiles or plans that are not reused? If so
> then work on plan reuse.
> --
> Andrew J. Kelly SQL MVP
>
> "kris" <kpxus@.yahoo.com> wrote in message
> news:OqCgT0obEHA.1248@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I ran waittype procedure to for 10 mins to check the sqlserver waittype
> and
> > time and got the following result
> >
> > wait type
> > wait time percentage
> ----
> --
> > -- -- --
> > ***total***
> > 7086.0 100.0
> > CXPACKET
> > 3254.0 45.9
> > LATCH_EX
> > 3034.0 42.8
> > .
> > ..
> > I just want to reduce the above wait types,any idea. Currently the max
> > degree of parallelism is 0 .
> > SQL BOX has 2GB Memory and SQLServer is configured to use 1032MB fixed.
> >
> > Env:
> > SQL2000 + SP3 /Windows 2000 Advance Server 2 Processors with
> Hyper-Threading
> > Technology.
> >
> > thanks
> >
> >
>|||Other apps on the same box compete for the same resources and will add
complexity to what is the root cause. That can increase the risk of HT
being an issue as well. Another thing I forgot to mention is that if your
having issues with HT you may want to consider Win2003 as it handles HT and
more much better than 2000.
Andrew J. Kelly SQL MVP
"kris" <kpxus@.yahoo.com> wrote in message
news:e71CvCqbEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> thanks for your suggestions, actually another application is running on
the
> sqlbox which is taking 150MB. I will change the settings as per your
> suggestion and again i will check that.
> thanks
> kris
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23lys46obEHA.1152@.TK2MSFTNGP09.phx.gbl...
> > Latch_EX wait times usually indicate you are having a lot of memory
> pressure
> > and sql server has to spend a lot of time swapping data or procedure
pages
> > in and out of memory. The cxpacket waits are most likely due to the
fact
> > that your procs are too busy doing this and can't keep up with each
other.
> > My guess is that the HT is getting in the way some. Bottom line is you
> can
> > probably use more ram but here are as few things to try and see if they
> > help. One is to turn off HT. Another is to change the MAXDOP at the
> server
> > level. Try 2 first and then 1 if that doesn't help. Why do you only
have
> > 1GB allocated to SQL Server if you have 2GB total? Try bumping it up a
> few
> > hundred MB and see if that helps some. How much procedure cache do you
> > have? Do you have lots of recompiles or plans that are not reused? If
so
> > then work on plan reuse.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "kris" <kpxus@.yahoo.com> wrote in message
> > news:OqCgT0obEHA.1248@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I ran waittype procedure to for 10 mins to check the sqlserver
waittype
> > and
> > > time and got the following result
> > >
> > > wait type
> > > wait time percentage
> >
> ----
> > --
> > > -- -- --
> > > ***total***
> > > 7086.0 100.0
> > > CXPACKET
> > > 3254.0 45.9
> > > LATCH_EX
> > > 3034.0 42.8
> > > .
> > > ..
> > > I just want to reduce the above wait types,any idea. Currently the max
> > > degree of parallelism is 0 .
> > > SQL BOX has 2GB Memory and SQLServer is configured to use 1032MB
fixed.
> > >
> > > Env:
> > > SQL2000 + SP3 /Windows 2000 Advance Server 2 Processors with
> > Hyper-Threading
> > > Technology.
> > >
> > > thanks
> > >
> > >
> >
> >
>