modify column output

using sql database, i have a smallmoney column. when i enter an amount, 50.00 for example, i have 50.0000 displayed. is there a way to only have 50.00 displayed?

same with the smalldatetime, is there a way to limit the display to "mm/dd/yyyy" without the "hh:mms am"

To get 2 decimal places (instead of 4) just use the ROUND() function to get rid of the 'extra' 2 digits.

As far as the date issue, have a look at the SQL function CONVERT in BOL. Basically something like this should return just the date portion:

Code Snippet

SELECT CONVERT(varchar(10), GETDATE(), 101)

|||

Formatting should be done on the client not on the server. if you have an additional presentation layer above the SQL Server, do you formatting work here, as SQL Server is mainly optmized / made for storing and retrieving data.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Modify column order

Hello,
I currently have a table with 2 primary keys, and 6
columsn following that. I want to add 2 new columns, that
will also be primary keys. I want to insert these new
columns in position 3 and 4.
Is there a way to do this without dropping the table and
recreating it in the correct column order ?
eg:
Table : dbo.TMCLASS has columns as follows
COUNTRYCODE (pk)
CLASS (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I want a new structure (PROPERTYTYE and SEQUENCENO added)
Table : dbo.TMCLASS
COUNTRYCODE (pk)
CLASS (pk)
PROPERTYTYPE (pk)
SEQUENCENO (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I am tryign to avoid dropping and recreating the table as
there is data at client sites.
Thanks,
AlisonDon't think so. I also don't know why you would want to. I see a few
people asking for this aesthetic change. If you qualify all your statements
instead of using * then column order is irrelevant. May look prettier in EM
or other tool but that is about it I thnk.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alison Bell" <abell@.cpaglobal.com> wrote in message
news:047f01c36552$898f9d40$a601280a@.phx.gbl...
> Hello,
> I currently have a table with 2 primary keys, and 6
> columsn following that. I want to add 2 new columns, that
> will also be primary keys. I want to insert these new
> columns in position 3 and 4.
> Is there a way to do this without dropping the table and
> recreating it in the correct column order ?
> eg:
> Table : dbo.TMCLASS has columns as follows
> COUNTRYCODE (pk)
> CLASS (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I want a new structure (PROPERTYTYE and SEQUENCENO added)
> Table : dbo.TMCLASS
> COUNTRYCODE (pk)
> CLASS (pk)
> PROPERTYTYPE (pk)
> SEQUENCENO (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I am tryign to avoid dropping and recreating the table as
> there is data at client sites.
> Thanks,
> Alison
>|||I don't think there is a way either. Reason for wanting
the specific order is to match "alltables" script
generated by ERwin for data transfer at a later date. It
just means modifying the generated script each time we
make a DB change, as ERwin places the columns in pos 3
and 4 (non modifyable).
Thanks,
Alison
>--Original Message--
>Don't think so. I also don't know why you would want
to. I see a few
>people asking for this aesthetic change. If you qualify
all your statements
>instead of using * then column order is irrelevant. May
look prettier in EM
>or other tool but that is about it I thnk.|||Problem being I must do this via SQL scripting. It is
part of an upgrade script being shipped to client sites.
>--Original Message--
>easiest way is use EM's (pet's tool) table designer. Sql
>2000 will drop and create with your requirements. You
>don't have to do anything.
>>--Original Message--
>>I don't think there is a way either. Reason for
wanting
>>the specific order is to match "alltables" script
>>generated by ERwin for data transfer at a later date.
It
>>just means modifying the generated script each time we
>>make a DB change, as ERwin places the columns in pos 3
>>and 4 (non modifyable).
>>Thanks,
>>Alison
>>--Original Message--
>>Don't think so. I also don't know why you would want
>>to. I see a few
>>people asking for this aesthetic change. If you
qualify
>>all your statements
>>instead of using * then column order is irrelevant.
May
>>look prettier in EM
>>or other tool but that is about it I thnk.
>>.
>.
>|||Then you have to re-create the table (this is what EM does under covers anyhow). There is no
functionality in TSQL (or ANSI SQL, as I remember) to change column order.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alison Bell" <abell@.cpaglobal.com> wrote in message news:0fe901c366bd$edc2e210$a001280a@.phx.gbl...
> Problem being I must do this via SQL scripting. It is
> part of an upgrade script being shipped to client sites.
> >--Original Message--
> >easiest way is use EM's (pet's tool) table designer. Sql
> >2000 will drop and create with your requirements. You
> >don't have to do anything.
> >
> >>--Original Message--
> >>I don't think there is a way either. Reason for
> wanting
> >>the specific order is to match "alltables" script
> >>generated by ERwin for data transfer at a later date.
> It
> >>just means modifying the generated script each time we
> >>make a DB change, as ERwin places the columns in pos 3
> >>and 4 (non modifyable).
> >>
> >>Thanks,
> >>Alison
> >>
> >>--Original Message--
> >>Don't think so. I also don't know why you would want
> >>to. I see a few
> >>people asking for this aesthetic change. If you
> qualify
> >>all your statements
> >>instead of using * then column order is irrelevant.
> May
> >>look prettier in EM
> >>or other tool but that is about it I thnk.
> >>
> >>.
> >>
> >.
> >

modify column in tsql

How can I modify a column in tsql in, say, an sp? For example, suppose I
have a column called 'eff'; it's a float and it allows null values. I want
to change it to be a column that does not allow nulls and which instead has
a default value of 0 (or 0.00). The following:
alter table defaulttable modify column eff float not null default 0.00
throws an error (incorrect syntax near the keyword 'column'.
Tx for any help.
Bernie YaegerThis is a multi-part message in MIME format.
--=_NextPart_000_02F6_01C36019.41D01270
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You wouldn't normally do this in a proc. The error is in the word ="modify":
alter table defaulttable alter column eff float not null default 0.00
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message =news:QERZa.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...
How can I modify a column in tsql in, say, an sp? For example, suppose =I
have a column called 'eff'; it's a float and it allows null values. I =want
to change it to be a column that does not allow nulls and which instead =has
a default value of 0 (or 0.00). The following:
alter table defaulttable modify column eff float not null default =0.00
throws an error (incorrect syntax near the keyword 'column'.
Tx for any help.
Bernie Yaeger
--=_NextPart_000_02F6_01C36019.41D01270
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You wouldn't normally do this in a =proc. The error is in the word "modify":
alter table defaulttable =alter column eff float not null default =0.00
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bernie Yaeger" =wrote in message news:QERZa=.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...How can I modify a column in tsql in, say, an sp? For example, suppose =Ihave a column called 'eff'; it's a float and it allows null =values. I wantto change it to be a column that does not allow nulls and which =instead hasa default value of 0 (or 0.00). The following: alter table =defaulttable modify column eff float not null default 0.00throws an error (incorrect =syntax near the keyword 'column'.Tx for any help.Bernie Yaeger

---

--=_NextPart_000_02F6_01C36019.41D01270--|||Tom
that will raise an error at the DEFAULT
This will work though
alter table defaulttable alter column eff float not null
ALTER TABLE defaulttable ADD CONSTRAINT ABC DEFAULT 0.00 for eff
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Hi Allan,
Tx again Allan for your continued assistance.
Bernie
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:Oy28J1DYDHA.652@.TK2MSFTNGP10.phx.gbl...
> Tom
> that will raise an error at the DEFAULT
> This will work though
>
> alter table defaulttable alter column eff float not null
> ALTER TABLE defaulttable ADD CONSTRAINT ABC DEFAULT 0.00 for eff
>
>
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_002C_01C36020.075903C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hi Tom,
Tx for your help.
Bernie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:e74RMrDYDHA.1580@.tk2msftngp13.phx.gbl...
You wouldn't normally do this in a proc. The error is in the word ="modify":
alter table defaulttable alter column eff float not null default 0.00
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message =news:QERZa.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...
How can I modify a column in tsql in, say, an sp? For example, =suppose I
have a column called 'eff'; it's a float and it allows null values. I =want
to change it to be a column that does not allow nulls and which =instead has
a default value of 0 (or 0.00). The following:
alter table defaulttable modify column eff float not null =default 0.00
throws an error (incorrect syntax near the keyword 'column'.
Tx for any help.
Bernie Yaeger
--=_NextPart_000_002C_01C36020.075903C0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Tom,
Tx for your help.
Bernie
"Tom Moreau" = wrote in message news:e74RMrDYDHA.1580=@.tk2msftngp13.phx.gbl...
You wouldn't normally do this in a =proc. The error is in the word "modify":

alter table defaulttable =alter column eff float not null default 0.00
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Bernie Yaeger" =wrote in message news:QERZa=.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...How can I modify a column in tsql in, say, an sp? For example, =suppose Ihave a column called 'eff'; it's a float and it allows null =values. I wantto change it to be a column that does not allow nulls and =which instead hasa default value of 0 (or 0.00). The following: alter table =defaulttable modify column eff float not null default 0.00throws an error (incorrect syntax near the keyword 'column'.Tx for any help.Bernie Yaeger

---

--=_NextPart_000_002C_01C36020.075903C0--sql

Modify a column from Nvarchar(50) to a DateTime

Hello,

I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.

This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)

However, I have to change this column to a DateTime without destroying the data.

Is there any easy way to write some script or use studio management to change this.

Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.

The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.

I get this following error message:
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any suggestions would be most grateful,

Thanks,

Steve

The error is because your data format is 'confusing' to SQL Server. The ISO standard is [ yyyy/mm/dd ]

First you need to change the data in the existing column to be of a format that will allow the column datatype to change.

This 'should' work for you (untested):

UPDATE MyTable

SET DateEmployed = convert( nvarchar(50), convert( datetime, DateEmployed, 103 ), 111 )

Then change the datatype.

Modify /concatenate varbinary data in SQL Server 2005

I am trying to store large files /images in SQL Server 2005.

I have used varbinay data type for the table column. At front end i am storing value in blob and inserting record to this table. but SQL Server does not allow to insert directly blob data to varbinary . So we need to convert blob data to varbinary using convert() function. After conversion, check the data ,it gives some junk values. How do i insert blob data into table with column data type as varbinary ?

2) We are trying to send blob data to SQL server 2005 in chunks. How to do it if column in table is varbinary ?

Please advice.

Regards

Rithik

You can check the following article to get the idea.

Insert BLOB from ADO.NET

http://support.microsoft.com/kb/309158

Learn more facts about BLOB when you design database

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true

|||

Hi,

Thanks for your advice.

We are using powerbuilder 10 as front end and sql server 2005 as database. We use updateblob statement to update blob data to varbinary column in SQL Server script as follows.

1)Script :

UpdateBlob r_test Set blob_data = :lblob_filedata Where file_id =21;

2)And we used stored procedure as follows:

CRAETE PROCEDURE [dbo].[R_UPLOAD_FILES_TO_DB]
(
@.V_SOURCE_DATA VARBINARY(MAX),
@.V_FILE_ID NUMERIC(8)
)
AS

UPDATE R_TEST
SET DATA = @.V_SOURCE_DATA WHERE FILE_ID = @.V_FILE_ID

Blob data can have text files,bitmap files ,or any other file having size less than 2 GB.

The Table R_test Design :

Create table r_test

(data varbinary(max),fle_id numeric(8))

But when we checked the data we found it's not same.

SQL Server varbinary column stored blob data in different format. Why the data appears different ?

Please advice

Regards

Rithik

|||I want to store binary data in database. I have byte array, but i am unable to insert that in database because i don't know how to insert variable name of type BYTE in sql insert query.

my code is as follow

dim byteArray() as byte

strSQL = "INSERT INTO TEMP(TEMP_NAME,TEMP_BIN) VALUES('SANSHARK',?)"

how do i pass my byteArray in ?sql

Modified Date For Stored Procedures

In SQL Server is there a way to know when a procedure was last
modified? I only see the "Create Date" column on the Enterprise
Manager.

Thanks Experts!jjone99 (jjone99@.hotmail.com) writes:
> In SQL Server is there a way to know when a procedure was last
> modified? I only see the "Create Date" column on the Enterprise
> Manager.

No, in SQL 2000 there is not.

This is addressed in the next version of SQL Server, currently in beta.

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

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

Mode column for Lock:Acquired event in profiler

Is there any article that have the list of available values and their explanations for MODE column of Lock:Acquired event in profiler?
Thanks.Hi Jeffrey
There seems to be a bug with the mode column not correctly reporting. If it
is working, you can find the values for lock mode in the docs for the
syslockinfo system table, for the column called req_mode.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeffrey" <anonymous@.discussions.microsoft.com> wrote in message
news:486B0DF0-14AF-4B6D-85E3-7117600E28D0@.microsoft.com...
> Is there any article that have the list of available values and their
explanations for MODE column of Lock:Acquired event in profiler?
> Thanks.|||Thanks. I've looked at the syslocks table, seems the value there is different from the values shown by profiler tool itself.
Which one is more accurate? What the bug it is? Is there any article about this?
Thanks.|||Please use syslockinfo instead of syslocks. I have reported the bug, because
I discovered this inaccuracy in Profiler about a month ago, but I have not
gotten an actual bug number yet, and no one has written a KB article about
it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeffrey" <anonymous@.discussions.microsoft.com> wrote in message
news:37261015-1C15-4237-8E3B-80152DFF7E31@.microsoft.com...
> Thanks. I've looked at the syslocks table, seems the value there is
different from the values shown by profiler tool itself.
> Which one is more accurate? What the bug it is? Is there any article about
this?
> Thanks.|||Hmmm, very interesting...
BTW, where I can know the level of the lock, i.e., page level or row level, etc and the source id of the lock?
Thank you very much.|||Again, you need to correlate the info from Profiler documentation with the
syslockinfo documentation. The page describing "Locks Data Columns" in
Profiler, tells you that the binary data column represents the locked
resource, which is the row, page or table. The value in the column will
match something in syslockinfo.rsc_type
"Locks Data Columns" also tells you that ObjectID column in Profiler is
populated to tell you the object that the lock was taken on.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeffrey" <anonymous@.discussions.microsoft.com> wrote in message
news:1DE6F8AB-B91A-4E2F-8FAC-377ECA4101EA@.microsoft.com...
> Hmmm, very interesting...
> BTW, where I can know the level of the lock, i.e., page level or row
level, etc and the source id of the lock?
> Thank you very much.

Missing Record - Phantom Record

Hi All,

Have come across something weird and am after some help.

Say i run this query where rec_id is a column of table arlhrl,

select * from arlhrl where rec_id >= 14260

This returns to me 2 records with rec_id's of 14260 and 14261

Then I run this query

select * from arlhrl where rec_id >= 14263

This returns 7 records with rec_ids of 14263 up.

How come the first query doesn't return the records returned by the
2nd query also?

If I select for 14262 no records are returned. It is like this is a
phantom record or has an end of file character in it.

I tried re-creating the indexes but to no avail. If anyone has any
ideas about what could be causing it or how to fix it it would be much
appreciated.

Thanks in advance,

AndrewAndrew wrote:
> Hi All,
> Have come across something weird and am after some help.
> Say i run this query where rec_id is a column of table arlhrl,
> select * from arlhrl where rec_id >= 14260
> This returns to me 2 records with rec_id's of 14260 and 14261
> Then I run this query
> select * from arlhrl where rec_id >= 14263
> This returns 7 records with rec_ids of 14263 up.
> How come the first query doesn't return the records returned by the
> 2nd query also?
> If I select for 14262 no records are returned. It is like this is a
> phantom record or has an end of file character in it.
> I tried re-creating the indexes but to no avail. If anyone has any
> ideas about what could be causing it or how to fix it it would be much
> appreciated.
> Thanks in advance,
> Andrew

Hi,

First, stupid question - is the field 'rec_id' of integer type?
Why i am asking is because i had a similar example myself when i started with my new job - i was
quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)

Second, what i'd do when i get into an unexplainable glitch:

SELECT * INTO <new table> FROM <your table
And try to query the records from the new table without setting any indexes - just as is - as you
know SELECT INTO just copies raw data without any underlying stuff.
See what you'll get.
From my experience there are a of of people who are allowed to mess with SQL databases but don't
have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
imagine what another person could do - believe me, i just got quite a few awsome examples within the
last month since i got this job :)

Let me know how it works!

Andrey|||Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
> Andrew wrote:
> > Hi All,
> > Have come across something weird and am after some help.
> > Say i run this query where rec_id is a column of table arlhrl,
> > select * from arlhrl where rec_id >= 14260
> > This returns to me 2 records with rec_id's of 14260 and 14261
> > Then I run this query
> > select * from arlhrl where rec_id >= 14263
> > This returns 7 records with rec_ids of 14263 up.
> > How come the first query doesn't return the records returned by the
> > 2nd query also?
> > If I select for 14262 no records are returned. It is like this is a
> > phantom record or has an end of file character in it.
> > I tried re-creating the indexes but to no avail. If anyone has any
> > ideas about what could be causing it or how to fix it it would be much
> > appreciated.
> > Thanks in advance,
> > Andrew
>
> Hi,
> First, stupid question - is the field 'rec_id' of integer type?
> Why i am asking is because i had a similar example myself when i started with my new job - i was
> quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
> comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>
> Second, what i'd do when i get into an unexplainable glitch:
> SELECT * INTO <new table> FROM <your table>
> And try to query the records from the new table without setting any indexes - just as is - as you
> know SELECT INTO just copies raw data without any underlying stuff.
> See what you'll get.
> From my experience there are a of of people who are allowed to mess with SQL databases but don't
> have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
> hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
> imagine what another person could do - believe me, i just got quite a few awsome examples within the
> last month since i got this job :)
> Let me know how it works!
> Andrey

Hi Andrey,

Thanks for your reply. I tried as you mentioned, inserting into new
table etc but to no avail. I did figure out what the problem was
though.

This particular table had been upsized from a foxpro table. One of the
columns in the foxpro table had a maximum value of numeric 9999.
Somehow, someone had tried to insert a value large than this so foxpro
put in ****. On the upsize, and I can only assume here, sql must have
thought 'hang on, you must mean infinity here' and put a bit-wise
pattern (1.#INF) for infinity into this particular column for the
record.

This only became evident when using Enterprise Manager and returning
all rows on the given table, it did display the record with the value
1.#INF in the column for the 'missing' record. As to why it displayed
in EM and not Query Analyser is anyone's guess, but surely the queries
that led me to this initial discovery shouldn't have behaved like
this!!??

posting

http://groups.google.com/groups?q=%...le .com&rnum=1

gives some ideas.

Thanks anyway,

Andrew|||Andrew wrote:

> Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
>>Andrew wrote:
>>
>>>Hi All,
>>>
>>>Have come across something weird and am after some help.
>>>
>>>Say i run this query where rec_id is a column of table arlhrl,
>>>
>>>select * from arlhrl where rec_id >= 14260
>>>
>>>This returns to me 2 records with rec_id's of 14260 and 14261
>>>
>>>Then I run this query
>>>
>>>select * from arlhrl where rec_id >= 14263
>>>
>>>This returns 7 records with rec_ids of 14263 up.
>>>
>>>How come the first query doesn't return the records returned by the
>>>2nd query also?
>>>
>>>If I select for 14262 no records are returned. It is like this is a
>>>phantom record or has an end of file character in it.
>>>
>>>I tried re-creating the indexes but to no avail. If anyone has any
>>>ideas about what could be causing it or how to fix it it would be much
>>>appreciated.
>>>
>>>Thanks in advance,
>>>
>>>Andrew
>>
>>
>>Hi,
>>
>>First, stupid question - is the field 'rec_id' of integer type?
>>Why i am asking is because i had a similar example myself when i started with my new job - i was
>>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
>>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>>
>>
>>Second, what i'd do when i get into an unexplainable glitch:
>>
>>SELECT * INTO <new table> FROM <your table>
>>
>>And try to query the records from the new table without setting any indexes - just as is - as you
>>know SELECT INTO just copies raw data without any underlying stuff.
>>See what you'll get.
>> From my experience there are a of of people who are allowed to mess with SQL databases but don't
>>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
>>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
>>imagine what another person could do - believe me, i just got quite a few awsome examples within the
>>last month since i got this job :)
>>
>>Let me know how it works!
>>
>>Andrey
>
> Hi Andrey,
> Thanks for your reply. I tried as you mentioned, inserting into new
> table etc but to no avail. I did figure out what the problem was
> though.
> This particular table had been upsized from a foxpro table. One of the
> columns in the foxpro table had a maximum value of numeric 9999.
> Somehow, someone had tried to insert a value large than this so foxpro
> put in ****. On the upsize, and I can only assume here, sql must have
> thought 'hang on, you must mean infinity here' and put a bit-wise
> pattern (1.#INF) for infinity into this particular column for the
> record.
> This only became evident when using Enterprise Manager and returning
> all rows on the given table, it did display the record with the value
> 1.#INF in the column for the 'missing' record. As to why it displayed
> in EM and not Query Analyser is anyone's guess, but surely the queries
> that led me to this initial discovery shouldn't have behaved like
> this!!??
> posting
> http://groups.google.com/groups?q=%...le .com&rnum=1
> gives some ideas.
> Thanks anyway,
> Andrew

Well, EM and QA might show you diferent results because they are using diferent methods of 'talking'
to sql server.
QA is using isql.com, precisely it's isqlw.com version, which is an old DB lib based way of connection.
EM, i guess, is using ODBC or OLEDB connection.

I also had a headache not long time ago, when i used sql console tools to make Python work with sql
server. I had a table with varcha fields which had around couple thousand characters of text each.

When i used isql.com to retreive those text records, text returned truncated, around 300 to 600
characters left.. SO i started using osql.com instead, and no headache.

So resume is - every time you're in doubt, use both EM and QA

PS. BTW, I didn't know sql server can store 'infinity' values. Thanks for the info!

WYGL,
Andrey|||Andrew wrote:

> Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
>>Andrew wrote:
>>
>>>Hi All,
>>>
>>>Have come across something weird and am after some help.
>>>
>>>Say i run this query where rec_id is a column of table arlhrl,
>>>
>>>select * from arlhrl where rec_id >= 14260
>>>
>>>This returns to me 2 records with rec_id's of 14260 and 14261
>>>
>>>Then I run this query
>>>
>>>select * from arlhrl where rec_id >= 14263
>>>
>>>This returns 7 records with rec_ids of 14263 up.
>>>
>>>How come the first query doesn't return the records returned by the
>>>2nd query also?
>>>
>>>If I select for 14262 no records are returned. It is like this is a
>>>phantom record or has an end of file character in it.
>>>
>>>I tried re-creating the indexes but to no avail. If anyone has any
>>>ideas about what could be causing it or how to fix it it would be much
>>>appreciated.
>>>
>>>Thanks in advance,
>>>
>>>Andrew
>>
>>
>>Hi,
>>
>>First, stupid question - is the field 'rec_id' of integer type?
>>Why i am asking is because i had a similar example myself when i started with my new job - i was
>>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
>>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>>
>>
>>Second, what i'd do when i get into an unexplainable glitch:
>>
>>SELECT * INTO <new table> FROM <your table>
>>
>>And try to query the records from the new table without setting any indexes - just as is - as you
>>know SELECT INTO just copies raw data without any underlying stuff.
>>See what you'll get.
>> From my experience there are a of of people who are allowed to mess with SQL databases but don't
>>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
>>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
>>imagine what another person could do - believe me, i just got quite a few awsome examples within the
>>last month since i got this job :)
>>
>>Let me know how it works!
>>
>>Andrey
>
> Hi Andrey,
> Thanks for your reply. I tried as you mentioned, inserting into new
> table etc but to no avail. I did figure out what the problem was
> though.
> This particular table had been upsized from a foxpro table. One of the
> columns in the foxpro table had a maximum value of numeric 9999.
> Somehow, someone had tried to insert a value large than this so foxpro
> put in ****. On the upsize, and I can only assume here, sql must have
> thought 'hang on, you must mean infinity here' and put a bit-wise
> pattern (1.#INF) for infinity into this particular column for the
> record.
> This only became evident when using Enterprise Manager and returning
> all rows on the given table, it did display the record with the value
> 1.#INF in the column for the 'missing' record. As to why it displayed
> in EM and not Query Analyser is anyone's guess, but surely the queries
> that led me to this initial discovery shouldn't have behaved like
> this!!??
> posting
> http://groups.google.com/groups?q=%...le .com&rnum=1
> gives some ideas.
> Thanks anyway,
> Andrew

And how did you get rid of that infinity value in the in field?|||I got rid of the infinity value using EM open table then typed in the
value I wanted.

Andrey <leyandrew@.yahoo.com> wrote in message news:<pir4d.28770$wV.19066@.attbi_s54>...
> Andrew wrote:
> > Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
> >>Andrew wrote:
> >>
> >>>Hi All,
> >>>
> >>>Have come across something weird and am after some help.
> >>>
> >>>Say i run this query where rec_id is a column of table arlhrl,
> >>>
> >>>select * from arlhrl where rec_id >= 14260
> >>>
> >>>This returns to me 2 records with rec_id's of 14260 and 14261
> >>>
> >>>Then I run this query
> >>>
> >>>select * from arlhrl where rec_id >= 14263
> >>>
> >>>This returns 7 records with rec_ids of 14263 up.
> >>>
> >>>How come the first query doesn't return the records returned by the
> >>>2nd query also?
> >>>
> >>>If I select for 14262 no records are returned. It is like this is a
> >>>phantom record or has an end of file character in it.
> >>>
> >>>I tried re-creating the indexes but to no avail. If anyone has any
> >>>ideas about what could be causing it or how to fix it it would be much
> >>>appreciated.
> >>>
> >>>Thanks in advance,
> >>>
> >>>Andrew
> >>
> >>
> >>Hi,
> >>
> >>First, stupid question - is the field 'rec_id' of integer type?
> >>Why i am asking is because i had a similar example myself when i started with my new job - i was
> >>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
> >>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
> >>
> >>
> >>Second, what i'd do when i get into an unexplainable glitch:
> >>
> >>SELECT * INTO <new table> FROM <your table>
> >>
> >>And try to query the records from the new table without setting any indexes - just as is - as you
> >>know SELECT INTO just copies raw data without any underlying stuff.
> >>See what you'll get.
> >> From my experience there are a of of people who are allowed to mess with SQL databases but don't
> >>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
> >>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
> >>imagine what another person could do - believe me, i just got quite a few awsome examples within the
> >>last month since i got this job :)
> >>
> >>Let me know how it works!
> >>
> >>Andrey
> > Hi Andrey,
> > Thanks for your reply. I tried as you mentioned, inserting into new
> > table etc but to no avail. I did figure out what the problem was
> > though.
> > This particular table had been upsized from a foxpro table. One of the
> > columns in the foxpro table had a maximum value of numeric 9999.
> > Somehow, someone had tried to insert a value large than this so foxpro
> > put in ****. On the upsize, and I can only assume here, sql must have
> > thought 'hang on, you must mean infinity here' and put a bit-wise
> > pattern (1.#INF) for infinity into this particular column for the
> > record.
> > This only became evident when using Enterprise Manager and returning
> > all rows on the given table, it did display the record with the value
> > 1.#INF in the column for the 'missing' record. As to why it displayed
> > in EM and not Query Analyser is anyone's guess, but surely the queries
> > that led me to this initial discovery shouldn't have behaved like
> > this!!??
> > posting
> > http://groups.google.com/groups?q=%...le .com&rnum=1
> > gives some ideas.
> > Thanks anyway,
> > Andrew
>
> And how did you get rid of that infinity value in the in field?

missing object warning when adding column to table with sp_repladd

Added a column to one table with sp_repladdcolumn in publisher/distributor.
This warning comes from every publication this table belongs to:
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_C245F32C38A611D525494BFC6FD140A7_pal'.
The stored procedure will still be created.
In use: Merge replication and static filtered publications referencing this
table.
Is it safe to continue and trust that everything works after this? What is
this _pal ending sp? What happens if it does not exist and what should we do
next?
Thanks - M
Its fine as long as this proc is present in all subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Untamo" <Untamo@.discussions.microsoft.com> wrote in message
news:0BC7CA5F-872E-4AB9-B1EB-F0ADCDD435B6@.microsoft.com...
> Added a column to one table with sp_repladdcolumn in
publisher/distributor.
> This warning comes from every publication this table belongs to:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_C245F32C38A611D525494BFC6FD140A7_pal'.
> The stored procedure will still be created.
> In use: Merge replication and static filtered publications referencing
this
> table.
> Is it safe to continue and trust that everything works after this? What is
> this _pal ending sp? What happens if it does not exist and what should we
do
> next?
> Thanks - M

Missing Information with Cross Tab Expert

I am using a Cross Tab Expert to display coverage information about auto insurance policies. In the SQL that is used, there is a Coverage Column and an Option Column. If the Coverage is Comprehensive and the Option is Full Glass, I am to print "Comprehensive - Full Glass", otherwise, print "Comprehensive". That works fine, if the first vehicle in the group has Full Glass.

Is there a way I can programatically add a new row one car has Full Glass, but the other doesnt, so row 1 will print "Comprehensive" and have the Car 1 value, and row 2 will print "Comprehensive - Full Glass" and have the Car 2 value?

Thanks!!
MichaelNot quite sure why you are a using a crosstab, but here goes:

Set up the crosstab by customer and, the next level lower, by car. Thus, if I had four cars, I would have four rows, sorted based on the type of coverage as you describe. If I had but two cars, both with the same level f coverage, only the two would print.