problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciatedYou didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegroups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated|||thanks for all of your help, this got me going into the right
direction again.

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed
."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/defaul...kb;en-us;818899
http://support.microsoft.com/defaul...kb;en-us;829444
http://support.microsoft.com/defaul...kb;en-us;812995
http://support.microsoft.com/defaul...kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step fail
ed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>sql

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SC
SC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default...b;en-us;818899
http://support.microsoft.com/default...b;en-us;829444
http://support.microsoft.com/default...b;en-us;812995
http://support.microsoft.com/default...b;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
http://support.microsoft.com/default.aspx?scid=kb;en-us;829444
http://support.microsoft.com/default.aspx?scid=kb;en-us;812995
http://support.microsoft.com/default.aspx?scid=kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem returning SQL data

I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.

Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:

(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...

Related Links:
John Doe interviewed by Susan Smith, 123 News

What it should say in the link is this: Mark Crisp's blog (as text link)

When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Company\Company.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.

IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number

strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"

rs.Open strSQL,con 'open a connection to the database

%>

<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

<!-- END OF THE GUESTS AND LINKS SECTION -->

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).

What am I missing?Is you asp code showing anything ? As you are referencing the column e.g. GuestName, it should not display anything for this attribute as it does not exists in the recordset. The attribute is renamed (in your query to Expr3, so you either will have to delete the AS Expr3 part or rename the reference of the recordset, e.g. rs("Expr3").

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...sql

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...

Problem removing trailing spaces

Hi,
I am very new to SQL and made a dumb mistake when setting up my
database. I have some small tables I used for drop-down-lists in a web
page I designed. I used a datatype of CHAR. Well, I should have used
VARCHAR as it is causing me problems now.
I have redesigned the site so the drop-down-lists are not pulled from
the SQL table. The problem is that every record created with one of
these dropdown values has trailing spaces. In other words, if the field
was CHAR with length 10 when we store Canada, it is really
Canada<space><space><space><space>.
I have found one way to fix this:
UPDATE mytable
SET country='Canada' WHERE country='Canada'
This has the effect of removing the spaces. I guess I could also use:
UPDATE mytable
SET country='Canada' WHERE country LIKE '%Canada%'
This is WAY too tedious to do for every value of every column I have
this problem with. Is there a way to iterate through the database a
remove ALL trailing spaces?
I appreciate the help!So you have changed the datatype in the table and want to get rid of the tra
iling spaces in the
varchar column?
UPDATE tblname
SET col = RTRIM(col)
Pls test above first, just in case...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>|||I read about RTRIM but didn't realize it could be used that way. I will
test it. Thanks!
Tibor Karaszi wrote:
> So you have changed the datatype in the table and want to get rid of the t
railing spaces in the
> varchar column?
> UPDATE tblname
> SET col = RTRIM(col)
> Pls test above first, just in case...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> <mattmerc@.bellsouth.net> wrote in message
> news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...|||First, in Enterprise Manager you need to redefine the Char(10) column as
VarChar(30), becuase Char columns cannot be trimmed. The rtrim() function
will returns a string with trailing spaces trimmed. You will probably want
to trim every country in the table, so there is no need to specify a where
clause.
UPDATE mytable SET country = rtrim(country)
http://sqlcourse.com/create.html
http://sqlcourse.com/update.html
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>|||You can also use the RTRIM() function, but this still require you to update
every column. Although it might be easier to drop, recreate the tables
using varchar, and reload them.
--Brian
(Please reply to the newsgroups only.)
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>

Problem propagating identity fields for multiple tables in SQLXML Bulkload

I am having a problem importing XML documents into SQL using SQLXML Bulkload with an annotated XSD schema. I have three, hierarchical tables in a SQL 2000 DB: tblBatches -> tblRecords -> tblDetails. The XML document I want to import does not contain identity fields. So I am using '.KeepIdentity = False' in my ActiveX VB Script, so SQL Server will generate the key fields for me. When I run the script, the message I get is 'No data was provided for column 'record_ID' on table 'tblDetails', and this column cannot contain NULL values.' So, basically, it's not inserting the matching foreign key generated for tblRecords into tblDetails.
I have been playing around with this for several days, and if I remove all reference to the third table in the hierarchy, tblDetails, from my XSD schema, the data imports and SQL successfully inserts the identity fields for me for the first two tables. If it will insert the identity fields for two tables, why is it having so much trouble doing it for three? I thought SQLXML 3.0 SP2 was supposed to fix the identity propagation problem. I am using SQLXML 3.0 SP2, Windows 2000, VS.NET 2002, and SQL Server 2000.
I am pasting my VB code, XSD schema, and XML document below. I will also attach copies of them, if that is easier.
Here is the VB code I am using to run the bulkload:
Dim BulkLoad As SQLXMLBulkLoad3
Try
'Create the new XML Bulk Load object
BulkLoad = New SQLXMLBulkLoad3()
'Set the connection string.
BulkLoad.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;User ID=mmsuser;Initial Catalog=MMS;Data Source=NDEWITT\NetSDK;"
'Log any errors.
BulkLoad.ErrorLogFile = "C:\TestSQLErrorLog.xml"
'Have it automatically add the identity fields.
BulkLoad.KeepIdentity = False
BulkLoad.CheckConstraints = True
BulkLoad.Transaction = False
'And perform the data import.
BulkLoad.Execute("C:\TestMMSSchemaNETxsd.xsd", _
"C:\TestXDRNoIdentity.xml")
'Clean up.
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
BulkLoad = Nothing
End Try
Here is my XSD schema:
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msch="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BatchRec" parent="tblBatches" parent-key="batch_ID" child="tblRecords" child-key="batch_ID" />
<sql:relationship name="RecDetail" parent="tblRecords" parent-key="record_ID" child="tblDetails" child-key="record_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="statement_batch" sql:relation="tblBatches" sql:key-fields="batch_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="print_batch_key" type="xs:string" />
<xs:element name="print_batch_type_value" type="xs:string" />
<xs:element name="client_program_key" type="xs:string" />
<xs:element name="print_item_quantity" type="xs:int" />
<xs:element name="start_date" type="xs:date" />
<xs:element name="end_date" type="xs:date" />
<xs:element name="client_key" type="xs:string" />
<xs:element name="client_name" type="xs:string" />
<xs:element name="program_key" type="xs:string" />
<xs:element name="program_name" type="xs:string" />
<xs:element name="return_address_line_1" type="xs:string" />
<xs:element name="return_address_line_2" type="xs:string" />
<xs:element name="return_city" type="xs:string" />
<xs:element name="return_state" type="xs:string" />
<xs:element name="return_zip" type="xs:string" />
<xs:element name="miles_synonym" type="xs:string" />
<xs:element name="statement_synonym" type="xs:string" />
<xs:element name="member_id_synonym" type="xs:string" />
<xs:element name="member_synonym" type="xs:string" />
<xs:element name="redemption_http" type="xs:string" />
<xs:element name="redemption_phone" type="xs:string" />
<xs:element name="print_logo" type="xs:string" />
<xs:element name="run_date" type="xs:date" />
<xs:element name="custom_message" type="xs:string" />
<xs:element name="summary_text" type="xs:string" />
<xs:element name="format_type" type="xs:string" />
<xs:element name="envelope_type" type="xs:string" />
<xs:element name="paper_type" type="xs:string" />
<xs:element name="statement_record" sql:relation="tblRecords" sql:relationship="BatchRec" sql:key-fields="record_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member_id" type="xs:string" />
<xs:element name="first_name" type="xs:string" />
<xs:element name="last_name" type="xs:string" />
<xs:element name="address_line_1" type="xs:string" />
<xs:element name="address_line_2" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="state" type="xs:string" />
<xs:element name="zip" type="xs:string" />
<xs:element name="country_id" type="xs:string" />
<xs:element name="employer" type="xs:string" />
<xs:element name="begin_balance" type="xs:int" />
<xs:element name="award_activity" type="xs:int" />
<xs:element name="redeem_activity" type="xs:int" />
<xs:element name="expired_miles" type="xs:int" />
<xs:element name="adjusted_miles" type="xs:int" />
<xs:element name="end_balance" type="xs:int" />
<xs:element name="unvested_balance" type="xs:int" />
<xs:element name="detail_record" sql:relation="tblDetails" sql:relationship="RecDetail" sql:key-fields="detail_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="effective_date" type="xs:date" />
<xs:element name="activity_type" type="xs:unsignedByte" />
<xs:element name="activity_description" type="xs:string" />
<xs:element name="activity_miles" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MMSSchema" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="statement_batch" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a sample XML document I want to import:
<statement_batch>
<print_batch_key>609167</print_batch_key>
<print_batch_type_value>Member Statement</print_batch_type_value>
<client_program_key>857148</client_program_key>
<print_item_quantity>108</print_item_quantity>
<start_date>02-13-2004</start_date>
<end_date>03-04-2004</end_date>
<client_key>317229</client_key>
<client_name>CARLSON SYSTEMS</client_name>
<program_key>857143</program_key>
<program_name>Carlson Systems -The Green Mile</program_name>
<return_address_line_1>3475 Piedmont Rd. NE</return_address_line_1>
<return_address_line_2>Suite 300</return_address_line_2>
<return_city>Atlanta</return_city>
<return_state>GA</return_state>
<return_zip>30305</return_zip>
<miles_synonym>Points</miles_synonym>
<statement_synonym>Statement</statement_synonym>
<member_id_synonym>Participant ID</member_id_synonym>
<member_synonym>Participant</member_synonym>
<redemption_http>www.webrewards.com</redemption_http>
<redemption_phone>Merchandise- (877) 690-2080; Travel- (800) 210-8539</redemption_phone>
<print_logo>http://www.webrewards.com/carlson_sy...sonSystems.jpg</print_logo>
<run_date>03-04-2004</run_date>
<custom_message><![CDATA[
<BR>
<BR>]]></custom_message>
<summary_text>
</summary_text>
<format_type>STATEMENT_DETAIL_LOGO</format_type>
<envelope_type>PISTOL_STATEMENT</envelope_type>
<paper_type>STATEMENT_OFFSET_GRAY</paper_type>
<statement_record>
<member_id>859953</member_id>
<first_name>MICHAEL</first_name>
<last_name>STAPLES</last_name>
<address_line_1>4300 DELLWOOD LANE</address_line_1>
<address_line_2>
</address_line_2>
<city>MOUND</city>
<state>MN</state>
<zip>55364</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>313152</begin_balance>
<award_activity>21924</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>335076</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>176</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>265</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1344</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>235</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>3103</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>5435</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>154</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>66</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>500</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>597</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1014</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>756</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>56</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>490</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>212</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1875</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>256</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>240</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>202</activity_miles>
</detail_record>
</statement_record>
<statement_record>
<member_id>859954</member_id>
<first_name>M</first_name>
<last_name>LEGAULT</last_name>
<address_line_1>13156 BITTERSWEET ST NW</address_line_1>
<address_line_2>
</address_line_2>
<city>COON RAPIDS</city>
<state>MN</state>
<zip>55448</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>91188</begin_balance>
<award_activity>5569</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>96757</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>80</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>105</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>891</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1543</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
</statement_record>
</statement_batch>
Any help will be greatly appreciated. Thanks!I discovered your post at Midtown Computer Systems Enterprise web site about SQLXMLBulkLoad and identity columns
in 3 hierarchical tables. I have having similar problems as the ones you described in your post. Were you able to find
a solution? If so, can you share it here? Thanks.sql

Problem Primary Key will not be created?

I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>
You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:

> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.

Problem Primary Key will not be created?

I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:

> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.

problem on alter table

I would like to disable triggers of all tables. How can I do that? I can get
the names of table and trigger from sysobjects. But how can I
programmatically to replace the table name and trigger name from the ALTER
TABLE command?
ALTER TABLE table1 DISABLE TRIGGER trigger1
Thanks.Hi
-- to diable all
EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
GO
-- to enable all
EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
GO
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:7A084773-C0CC-4543-99A8-B563C44EDF7F@.microsoft.com...
> I would like to disable triggers of all tables. How can I do that? I can
get
> the names of table and trigger from sysobjects. But how can I
> programmatically to replace the table name and trigger name from the ALTER
> TABLE command?
> ALTER TABLE table1 DISABLE TRIGGER trigger1
> Thanks.|||Thanks Dimant.
It works OK but I cannot find this store procedures on SQL Book Online. Why?
Thank you.
"Uri Dimant" wrote:

> Hi
> -- to diable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
> GO
> -- to enable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
> GO
>
> "joeau" <joeau@.discussions.microsoft.com> wrote in message
> news:7A084773-C0CC-4543-99A8-B563C44EDF7F@.microsoft.com...
> get
>
>|||Hi
Its not documented from MS. Actually it may have some changes in the future
version of the SQL Server , so I'd not recommend you to use it in
production.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK94OOrMFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Hi
> -- to diable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
> GO
> -- to enable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
> GO
>
> "joeau" <joeau@.discussions.microsoft.com> wrote in message
> news:7A084773-C0CC-4543-99A8-B563C44EDF7F@.microsoft.com...
> get
ALTER
>

Problem of running sp through VB

Hi,
I have a sp running well manually in SQL analyzer. This sp is using BCP to
archive data from SQL tables to datafiles.
The problem is if the sp is run through VB and a table has lot of data, the
process will stop after exec bcp(datafile is created) and doesn't go through
other part of the sp. VB part doesn't get any error. If all the tables dealt
with don't have too many data, it works well.
Any suggesting will be appreciatedYou might want to increase your CommandTimeout to greater than 30 seconds.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:7E219AF2-2036-4D10-8FCD-029D22A9B367@.microsoft.com...
> Hi,
> I have a sp running well manually in SQL analyzer. This sp is using BCP to
> archive data from SQL tables to datafiles.
> The problem is if the sp is run through VB and a table has lot of data,
> the
> process will stop after exec bcp(datafile is created) and doesn't go
> through
> other part of the sp. VB part doesn't get any error. If all the tables
> dealt
> with don't have too many data, it works well.
> Any suggesting will be appreciated|||I set CommandTimeout = 0. If it's timeout error, VB will get error message,
right?
"oj" wrote:

> You might want to increase your CommandTimeout to greater than 30 seconds.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:7E219AF2-2036-4D10-8FCD-029D22A9B367@.microsoft.com...
>
>|||Yes. "0" means indefinitely. Also, be aware that SQL will wait indefinitely
for any external calls via xp_cmdshell until it returns. If there is
something wrong at the system level and xp_cmdshell hangs, SQL would not
know about it.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E7298BD1-4236-42CC-9245-34BBA05FA5B0@.microsoft.com...
>I set CommandTimeout = 0. If it's timeout error, VB will get error message,
> right?
> "oj" wrote:
>|||xp_cmdshell works fine because datafile was created. However it stop after
that without any error.
"oj" wrote:

> Yes. "0" means indefinitely. Also, be aware that SQL will wait indefinitel
y
> for any external calls via xp_cmdshell until it returns. If there is
> something wrong at the system level and xp_cmdshell hangs, SQL would not
> know about it.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:E7298BD1-4236-42CC-9245-34BBA05FA5B0@.microsoft.com...
>
>|||Can you use BULK INSERT instead BCP?
AMB
"Tom" wrote:
> xp_cmdshell works fine because datafile was created. However it stop after
> that without any error.
> "oj" wrote:
>|||Tom,
Try downloading ntpmon/ntfilmon from sysinternals to see what's going. Also,
use "-e" flag of bcp to capture any eror.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:4BCB7A0C-25F7-4FEE-9495-09D58D796E5F@.microsoft.com...
> xp_cmdshell works fine because datafile was created. However it stop after
> that without any error.
>|||Can I use BULK INSERT to copy data from SQL table to datafile?
"Alejandro Mesa" wrote:
> Can you use BULK INSERT instead BCP?
>
> AMB
> "Tom" wrote:
>|||Sorry, thought you were importing.
AMB
"Tom" wrote:
> Can I use BULK INSERT to copy data from SQL table to datafile?
> "Alejandro Mesa" wrote:
>|||There is no any error in error file. I wander if there is any difference
between running a sp in SQL query analyzer and through VB.
"oj" wrote:

> Tom,
> Try downloading ntpmon/ntfilmon from sysinternals to see what's going. Als
o,
> use "-e" flag of bcp to capture any eror.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:4BCB7A0C-25F7-4FEE-9495-09D58D796E5F@.microsoft.com...
>
>

Problem of casting to timestamp

Hello,
During my work I have found following problem. All tables that I am using
have column RecTimeStamp (timestamp). I do the selection based on last
remembered timestamp. For some reasons my last timestamp is saved as decimal
number (Int64), so I do some casting to timestamp in my queries. Once the DB
global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
following example:
select cast (cast (2147483648 as bigint) as timestamp)
result: 0x0000000080000000 - OK
select cast (2147483648 as timestamp)
result: 0x0A00000100000080 - MISSMATCH
select cast (2147483647 as timestamp)
result: 0x000000007FFFFFFF - OK
Is this a bug of MSSQL server?
Note: If I do no casting and simply use for example select top 1000 … where
RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
of them has timestamp < maximum value of smallint and half of them higher,
I’ll get no 1000 records but only that lower half of them.
Michal Valenta,
Can you tell us the data type of that column?
SQL Server has timestamp data type but it has nothing to do with datetime
data type. it is used for row versioning.
select getdate(), current_timestamp, @.@.dbts;
AMB
"Michal Valenta" wrote:

> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 … where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> of them has timestamp < maximum value of smallint and half of them higher,
> I’ll get no 1000 records but only that lower half of them.
|||Alejandro Mesa,
The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
it has nothing to do with DateTime type. ;)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> Can you tell us the data type of that column?
> SQL Server has timestamp data type but it has nothing to do with datetime
> data type. it is used for row versioning.
> select getdate(), current_timestamp, @.@.dbts;
>
> AMB
> "Michal Valenta" wrote:
|||Michal,
I have to admit that I am fogged as to why you want to convert timestamp to
anything. Timestamp is a nearly meaningless 8-byte incrementing number.
(The only meaning is that more recently changed rows have a higher timestamp
value than previously changed rows.)
Just in case you are expecting something different, remember that timestamp
was also called rowversion for a while (a better name) and contains no time.
Then I see your WHERE clause. Actually, you should never care how the
Binary(8) converts to compare with the interger value 0. Simply order by the
timestamp without a comparison, unless you allow the timestamp to be
nullable. If that is the case then, you may want:
WHERE RecTimeStamp IS NOT NULL
For your comparison to evaluate as you apparently intended:
WHERE RecTimeStamp > 0x00
RLF
"Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as
> decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the
> DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 .
> where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> half
> of them has timestamp < maximum value of smallint and half of them higher,
> I'll get no 1000 records but only that lower half of them.
|||Michal Valenta,
See if this helps.
System.Data.SqlTypes Namespace
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Alejandro Mesa,
> The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> it has nothing to do with DateTime type. ;)
> "Alejandro Mesa" wrote:
|||Russell,
I have read the help for MSSQL and I've found a table which describes what
types are convertable and I am just doing something that might be possible.
The reason why I am using this column and its type is simple. As you wrote
about versioning. My clients are storing the last timestamp (version) from a
table as a decimal number Int64. They are asking server giving this version
mark if something new is present to download. The timestamp is not nullable
and contains DB-blobal number (i think binary(8)) incrementing by one. I am
selecting top 1000 records which has this mark higher then given and for next
step (if return reaches 1000 records) I use the last downloaded timestamp.
This is functional system and i am using it more then three years for geting
all new and updated rows from DB. In the past I was always converting the
number by my C# code to timestamp format and pasting it to select. As I saw
the help, I started to use casting, that is simple. I know several steps how
to fix my problems and I already did, but what I do not understand is the
mechanism of casting using MSSQL, because it seems to be a bug in it. Try
those small selects as I wrote in example. So my problem and question is: is
that behaviour I described ok? I think it is not and I supose there is a bug
in MSSQL. Don't you think?
As you wrote about using > 0x00 you could have the same problems as i
described in a NOTE of my first posting.
Anyway thanks for trying to help, I am still hoping someone from MS could
know more about it... ;)
M.V.
"Russell Fields" wrote:

> Michal,
> I have to admit that I am fogged as to why you want to convert timestamp to
> anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> (The only meaning is that more recently changed rows have a higher timestamp
> value than previously changed rows.)
> Just in case you are expecting something different, remember that timestamp
> was also called rowversion for a while (a better name) and contains no time.
> Then I see your WHERE clause. Actually, you should never care how the
> Binary(8) converts to compare with the interger value 0. Simply order by the
> timestamp without a comparison, unless you allow the timestamp to be
> nullable. If that is the case then, you may want:
> WHERE RecTimeStamp IS NOT NULL
> For your comparison to evaluate as you apparently intended:
> WHERE RecTimeStamp > 0x00
> RLF
>
> "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
> news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
>
>
|||Alejandro,
thank you for trying to help, but this is not an answer that I am looking
for, for some more information please see my reply posting to Russell Fields.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> See if this helps.
> System.Data.SqlTypes Namespace
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
>
> AMB
>
> "Michal Valenta" wrote:
|||Michal Valenta,
I have to admit that this is not the way I have used this feature in SQL
Server. I have used it when pulling rows to the client side, manipulating
them and pushing them back to the database but checking if the timestamp
column are the same. If the timestamp column changed at the database side,
then the data is not the latest. this is nothing else than using optimistic
concurrency.
I think that datetime data type could fit better for what you are trying to
accomplish, because you are not comparing row based, instead you are
comparing set based.
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Russell,
> I have read the help for MSSQL and I've found a table which describes what
> types are convertable and I am just doing something that might be possible.
> The reason why I am using this column and its type is simple. As you wrote
> about versioning. My clients are storing the last timestamp (version) from a
> table as a decimal number Int64. They are asking server giving this version
> mark if something new is present to download. The timestamp is not nullable
> and contains DB-blobal number (i think binary(8)) incrementing by one. I am
> selecting top 1000 records which has this mark higher then given and for next
> step (if return reaches 1000 records) I use the last downloaded timestamp.
> This is functional system and i am using it more then three years for geting
> all new and updated rows from DB. In the past I was always converting the
> number by my C# code to timestamp format and pasting it to select. As I saw
> the help, I started to use casting, that is simple. I know several steps how
> to fix my problems and I already did, but what I do not understand is the
> mechanism of casting using MSSQL, because it seems to be a bug in it. Try
> those small selects as I wrote in example. So my problem and question is: is
> that behaviour I described ok? I think it is not and I supose there is a bug
> in MSSQL. Don't you think?
> As you wrote about using > 0x00 you could have the same problems as i
> described in a NOTE of my first posting.
> Anyway thanks for trying to help, I am still hoping someone from MS could
> know more about it... ;)
> M.V.
> "Russell Fields" wrote:

Problem of casting to timestamp

Hello,
During my work I have found following problem. All tables that I am using
have column RecTimeStamp (timestamp). I do the selection based on last
remembered timestamp. For some reasons my last timestamp is saved as decimal
number (Int64), so I do some casting to timestamp in my queries. Once the D
B
global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
following example:
select cast (cast (2147483648 as bigint) as timestamp)
result: 0x0000000080000000 - OK
select cast (2147483648 as timestamp)
result: 0x0A00000100000080 - MISSMATCH
select cast (2147483647 as timestamp)
result: 0x000000007FFFFFFF - OK
Is this a bug of MSSQL server?
Note: If I do no casting and simply use for example select top 1000 … wher
e
RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, hal
f
of them has timestamp < maximum value of smallint and half of them higher,
I’ll get no 1000 records but only that lower half of them.Michal Valenta,
Can you tell us the data type of that column?
SQL Server has timestamp data type but it has nothing to do with datetime
data type. it is used for row versioning.
select getdate(), current_timestamp, @.@.dbts;
AMB
"Michal Valenta" wrote:

> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as decim
al
> number (Int64), so I do some casting to timestamp in my queries. Once the
DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it b
y
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 … wh
ere
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, h
alf
> of them has timestamp < maximum value of smallint and half of them higher,
> I’ll get no 1000 records but only that lower half of them.|||Alejandro Mesa,
The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
it has nothing to do with DateTime type. ;)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> Can you tell us the data type of that column?
> SQL Server has timestamp data type but it has nothing to do with datetime
> data type. it is used for row versioning.
> select getdate(), current_timestamp, @.@.dbts;
>
> AMB
> "Michal Valenta" wrote:
>|||Michal,
I have to admit that I am fogged as to why you want to convert timestamp to
anything. Timestamp is a nearly meaningless 8-byte incrementing number.
(The only meaning is that more recently changed rows have a higher timestamp
value than previously changed rows.)
Just in case you are expecting something different, remember that timestamp
was also called rowversion for a while (a better name) and contains no time.
Then I see your WHERE clause. Actually, you should never care how the
Binary(8) converts to compare with the interger value 0. Simply order by the
timestamp without a comparison, unless you allow the timestamp to be
nullable. If that is the case then, you may want:
WHERE RecTimeStamp IS NOT NULL
For your comparison to evaluate as you apparently intended:
WHERE RecTimeStamp > 0x00
RLF
"Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as
> decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the
> DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 .
> where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> half
> of them has timestamp < maximum value of smallint and half of them higher,
> I'll get no 1000 records but only that lower half of them.|||Michal Valenta,
See if this helps.
System.Data.SqlTypes Namespace
http://msdn.microsoft.com/library/d...atasqltypes.asp
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Alejandro Mesa,
> The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> it has nothing to do with DateTime type. ;)
> "Alejandro Mesa" wrote:
>|||Russell,
I have read the help for MSSQL and I've found a table which describes what
types are convertable and I am just doing something that might be possible.
The reason why I am using this column and its type is simple. As you wrote
about versioning. My clients are storing the last timestamp (version) from a
table as a decimal number Int64. They are asking server giving this version
mark if something new is present to download. The timestamp is not nullable
and contains DB-blobal number (i think binary(8)) incrementing by one. I am
selecting top 1000 records which has this mark higher then given and for nex
t
step (if return reaches 1000 records) I use the last downloaded timestamp.
This is functional system and i am using it more then three years for geting
all new and updated rows from DB. In the past I was always converting the
number by my C# code to timestamp format and pasting it to select. As I saw
the help, I started to use casting, that is simple. I know several steps how
to fix my problems and I already did, but what I do not understand is the
mechanism of casting using MSSQL, because it seems to be a bug in it. Try
those small selects as I wrote in example. So my problem and question is: is
that behaviour I described ok? I think it is not and I supose there is a bug
in MSSQL. Don't you think?
As you wrote about using > 0x00 you could have the same problems as i
described in a NOTE of my first posting.
Anyway thanks for trying to help, I am still hoping someone from MS could
know more about it... ;)
M.V.
"Russell Fields" wrote:

> Michal,
> I have to admit that I am fogged as to why you want to convert timestamp t
o
> anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> (The only meaning is that more recently changed rows have a higher timesta
mp
> value than previously changed rows.)
> Just in case you are expecting something different, remember that timestam
p
> was also called rowversion for a while (a better name) and contains no tim
e.
> Then I see your WHERE clause. Actually, you should never care how the
> Binary(8) converts to compare with the interger value 0. Simply order by t
he
> timestamp without a comparison, unless you allow the timestamp to be
> nullable. If that is the case then, you may want:
> WHERE RecTimeStamp IS NOT NULL
> For your comparison to evaluate as you apparently intended:
> WHERE RecTimeStamp > 0x00
> RLF
>
> "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in messa
ge
> news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
>
>|||Alejandro,
thank you for trying to help, but this is not an answer that I am looking
for, for some more information please see my reply posting to Russell Fields
.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> See if this helps.
> System.Data.SqlTypes Namespace
> http://msdn.microsoft.com/library/d...atasqltypes.asp
>
> AMB
>
> "Michal Valenta" wrote:
>|||Michal Valenta,
I have to admit that this is not the way I have used this feature in SQL
Server. I have used it when pulling rows to the client side, manipulating
them and pushing them back to the database but checking if the timestamp
column are the same. If the timestamp column changed at the database side,
then the data is not the latest. this is nothing else than using optimistic
concurrency.
I think that datetime data type could fit better for what you are trying to
accomplish, because you are not comparing row based, instead you are
comparing set based.
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Russell,
> I have read the help for MSSQL and I've found a table which describes what
> types are convertable and I am just doing something that might be possible
.
> The reason why I am using this column and its type is simple. As you wrote
> about versioning. My clients are storing the last timestamp (version) from
a
> table as a decimal number Int64. They are asking server giving this versio
n
> mark if something new is present to download. The timestamp is not nullabl
e
> and contains DB-blobal number (i think binary(8)) incrementing by one. I a
m
> selecting top 1000 records which has this mark higher then given and for n
ext
> step (if return reaches 1000 records) I use the last downloaded timestamp.
> This is functional system and i am using it more then three years for geti
ng
> all new and updated rows from DB. In the past I was always converting the
> number by my C# code to timestamp format and pasting it to select. As I sa
w
> the help, I started to use casting, that is simple. I know several steps h
ow
> to fix my problems and I already did, but what I do not understand is the
> mechanism of casting using MSSQL, because it seems to be a bug in it. Try
> those small selects as I wrote in example. So my problem and question is:
is
> that behaviour I described ok? I think it is not and I supose there is a b
ug
> in MSSQL. Don't you think?
> As you wrote about using > 0x00 you could have the same problems as i
> described in a NOTE of my first posting.
> Anyway thanks for trying to help, I am still hoping someone from MS could
> know more about it... ;)
> M.V.
> "Russell Fields" wrote:
>

Problem of casting to timestamp

Hello,
During my work I have found following problem. All tables that I am using
have column RecTimeStamp (timestamp). I do the selection based on last
remembered timestamp. For some reasons my last timestamp is saved as decimal
number (Int64), so I do some casting to timestamp in my queries. Once the DB
global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
following example:
select cast (cast (2147483648 as bigint) as timestamp)
result: 0x0000000080000000 - OK
select cast (2147483648 as timestamp)
result: 0x0A00000100000080 - MISSMATCH
select cast (2147483647 as timestamp)
result: 0x000000007FFFFFFF - OK
Is this a bug of MSSQL server?
Note: If I do no casting and simply use for example select top 1000 â?¦ where
RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
of them has timestamp < maximum value of smallint and half of them higher,
Iâ'll get no 1000 records but only that lower half of them.Michal Valenta,
Can you tell us the data type of that column?
SQL Server has timestamp data type but it has nothing to do with datetime
data type. it is used for row versioning.
select getdate(), current_timestamp, @.@.dbts;
AMB
"Michal Valenta" wrote:
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 â?¦ where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> of them has timestamp < maximum value of smallint and half of them higher,
> Iâ'll get no 1000 records but only that lower half of them.|||Alejandro Mesa,
The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
it has nothing to do with DateTime type. ;)
"Alejandro Mesa" wrote:
> Michal Valenta,
> Can you tell us the data type of that column?
> SQL Server has timestamp data type but it has nothing to do with datetime
> data type. it is used for row versioning.
> select getdate(), current_timestamp, @.@.dbts;
>
> AMB
> "Michal Valenta" wrote:
> > Hello,
> >
> > During my work I have found following problem. All tables that I am using
> > have column RecTimeStamp (timestamp). I do the selection based on last
> > remembered timestamp. For some reasons my last timestamp is saved as decimal
> > number (Int64), so I do some casting to timestamp in my queries. Once the DB
> > global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> > following example:
> >
> > select cast (cast (2147483648 as bigint) as timestamp)
> > result: 0x0000000080000000 - OK
> > select cast (2147483648 as timestamp)
> > result: 0x0A00000100000080 - MISSMATCH
> > select cast (2147483647 as timestamp)
> > result: 0x000000007FFFFFFF - OK
> >
> > Is this a bug of MSSQL server?
> >
> > Note: If I do no casting and simply use for example select top 1000 â?¦ where
> > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> > of them has timestamp < maximum value of smallint and half of them higher,
> > Iâ'll get no 1000 records but only that lower half of them.|||Michal,
I have to admit that I am fogged as to why you want to convert timestamp to
anything. Timestamp is a nearly meaningless 8-byte incrementing number.
(The only meaning is that more recently changed rows have a higher timestamp
value than previously changed rows.)
Just in case you are expecting something different, remember that timestamp
was also called rowversion for a while (a better name) and contains no time.
Then I see your WHERE clause. Actually, you should never care how the
Binary(8) converts to compare with the interger value 0. Simply order by the
timestamp without a comparison, unless you allow the timestamp to be
nullable. If that is the case then, you may want:
WHERE RecTimeStamp IS NOT NULL
For your comparison to evaluate as you apparently intended:
WHERE RecTimeStamp > 0x00
RLF
"Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as
> decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the
> DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 .
> where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> half
> of them has timestamp < maximum value of smallint and half of them higher,
> I'll get no 1000 records but only that lower half of them.|||Michal Valenta,
See if this helps.
System.Data.SqlTypes Namespace
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
AMB
"Michal Valenta" wrote:
> Alejandro Mesa,
> The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> it has nothing to do with DateTime type. ;)
> "Alejandro Mesa" wrote:
> > Michal Valenta,
> >
> > Can you tell us the data type of that column?
> >
> > SQL Server has timestamp data type but it has nothing to do with datetime
> > data type. it is used for row versioning.
> >
> > select getdate(), current_timestamp, @.@.dbts;
> >
> >
> > AMB
> >
> > "Michal Valenta" wrote:
> >
> > > Hello,
> > >
> > > During my work I have found following problem. All tables that I am using
> > > have column RecTimeStamp (timestamp). I do the selection based on last
> > > remembered timestamp. For some reasons my last timestamp is saved as decimal
> > > number (Int64), so I do some casting to timestamp in my queries. Once the DB
> > > global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> > > following example:
> > >
> > > select cast (cast (2147483648 as bigint) as timestamp)
> > > result: 0x0000000080000000 - OK
> > > select cast (2147483648 as timestamp)
> > > result: 0x0A00000100000080 - MISSMATCH
> > > select cast (2147483647 as timestamp)
> > > result: 0x000000007FFFFFFF - OK
> > >
> > > Is this a bug of MSSQL server?
> > >
> > > Note: If I do no casting and simply use for example select top 1000 â?¦ where
> > > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> > > of them has timestamp < maximum value of smallint and half of them higher,
> > > Iâ'll get no 1000 records but only that lower half of them.|||Russell,
I have read the help for MSSQL and I've found a table which describes what
types are convertable and I am just doing something that might be possible.
The reason why I am using this column and its type is simple. As you wrote
about versioning. My clients are storing the last timestamp (version) from a
table as a decimal number Int64. They are asking server giving this version
mark if something new is present to download. The timestamp is not nullable
and contains DB-blobal number (i think binary(8)) incrementing by one. I am
selecting top 1000 records which has this mark higher then given and for next
step (if return reaches 1000 records) I use the last downloaded timestamp.
This is functional system and i am using it more then three years for geting
all new and updated rows from DB. In the past I was always converting the
number by my C# code to timestamp format and pasting it to select. As I saw
the help, I started to use casting, that is simple. I know several steps how
to fix my problems and I already did, but what I do not understand is the
mechanism of casting using MSSQL, because it seems to be a bug in it. Try
those small selects as I wrote in example. So my problem and question is: is
that behaviour I described ok? I think it is not and I supose there is a bug
in MSSQL. Don't you think?
As you wrote about using > 0x00 you could have the same problems as i
described in a NOTE of my first posting.
Anyway thanks for trying to help, I am still hoping someone from MS could
know more about it... ;)
M.V.
"Russell Fields" wrote:
> Michal,
> I have to admit that I am fogged as to why you want to convert timestamp to
> anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> (The only meaning is that more recently changed rows have a higher timestamp
> value than previously changed rows.)
> Just in case you are expecting something different, remember that timestamp
> was also called rowversion for a while (a better name) and contains no time.
> Then I see your WHERE clause. Actually, you should never care how the
> Binary(8) converts to compare with the interger value 0. Simply order by the
> timestamp without a comparison, unless you allow the timestamp to be
> nullable. If that is the case then, you may want:
> WHERE RecTimeStamp IS NOT NULL
> For your comparison to evaluate as you apparently intended:
> WHERE RecTimeStamp > 0x00
> RLF
>
> "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
> news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> > Hello,
> >
> > During my work I have found following problem. All tables that I am using
> > have column RecTimeStamp (timestamp). I do the selection based on last
> > remembered timestamp. For some reasons my last timestamp is saved as
> > decimal
> > number (Int64), so I do some casting to timestamp in my queries. Once the
> > DB
> > global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> > by
> > following example:
> >
> > select cast (cast (2147483648 as bigint) as timestamp)
> > result: 0x0000000080000000 - OK
> > select cast (2147483648 as timestamp)
> > result: 0x0A00000100000080 - MISSMATCH
> > select cast (2147483647 as timestamp)
> > result: 0x000000007FFFFFFF - OK
> >
> > Is this a bug of MSSQL server?
> >
> > Note: If I do no casting and simply use for example select top 1000 .
> > where
> > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> > half
> > of them has timestamp < maximum value of smallint and half of them higher,
> > I'll get no 1000 records but only that lower half of them.
>
>|||Alejandro,
thank you for trying to help, but this is not an answer that I am looking
for, for some more information please see my reply posting to Russell Fields.
"Alejandro Mesa" wrote:
> Michal Valenta,
> See if this helps.
> System.Data.SqlTypes Namespace
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
>
> AMB
>
> "Michal Valenta" wrote:
> > Alejandro Mesa,
> >
> > The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> > it has nothing to do with DateTime type. ;)
> >
> > "Alejandro Mesa" wrote:
> >
> > > Michal Valenta,
> > >
> > > Can you tell us the data type of that column?
> > >
> > > SQL Server has timestamp data type but it has nothing to do with datetime
> > > data type. it is used for row versioning.
> > >
> > > select getdate(), current_timestamp, @.@.dbts;
> > >
> > >
> > > AMB
> > >
> > > "Michal Valenta" wrote:
> > >
> > > > Hello,
> > > >
> > > > During my work I have found following problem. All tables that I am using
> > > > have column RecTimeStamp (timestamp). I do the selection based on last
> > > > remembered timestamp. For some reasons my last timestamp is saved as decimal
> > > > number (Int64), so I do some casting to timestamp in my queries. Once the DB
> > > > global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> > > > following example:
> > > >
> > > > select cast (cast (2147483648 as bigint) as timestamp)
> > > > result: 0x0000000080000000 - OK
> > > > select cast (2147483648 as timestamp)
> > > > result: 0x0A00000100000080 - MISSMATCH
> > > > select cast (2147483647 as timestamp)
> > > > result: 0x000000007FFFFFFF - OK
> > > >
> > > > Is this a bug of MSSQL server?
> > > >
> > > > Note: If I do no casting and simply use for example select top 1000 â?¦ where
> > > > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> > > > of them has timestamp < maximum value of smallint and half of them higher,
> > > > Iâ'll get no 1000 records but only that lower half of them.|||Michal Valenta,
I have to admit that this is not the way I have used this feature in SQL
Server. I have used it when pulling rows to the client side, manipulating
them and pushing them back to the database but checking if the timestamp
column are the same. If the timestamp column changed at the database side,
then the data is not the latest. this is nothing else than using optimistic
concurrency.
I think that datetime data type could fit better for what you are trying to
accomplish, because you are not comparing row based, instead you are
comparing set based.
AMB
"Michal Valenta" wrote:
> Russell,
> I have read the help for MSSQL and I've found a table which describes what
> types are convertable and I am just doing something that might be possible.
> The reason why I am using this column and its type is simple. As you wrote
> about versioning. My clients are storing the last timestamp (version) from a
> table as a decimal number Int64. They are asking server giving this version
> mark if something new is present to download. The timestamp is not nullable
> and contains DB-blobal number (i think binary(8)) incrementing by one. I am
> selecting top 1000 records which has this mark higher then given and for next
> step (if return reaches 1000 records) I use the last downloaded timestamp.
> This is functional system and i am using it more then three years for geting
> all new and updated rows from DB. In the past I was always converting the
> number by my C# code to timestamp format and pasting it to select. As I saw
> the help, I started to use casting, that is simple. I know several steps how
> to fix my problems and I already did, but what I do not understand is the
> mechanism of casting using MSSQL, because it seems to be a bug in it. Try
> those small selects as I wrote in example. So my problem and question is: is
> that behaviour I described ok? I think it is not and I supose there is a bug
> in MSSQL. Don't you think?
> As you wrote about using > 0x00 you could have the same problems as i
> described in a NOTE of my first posting.
> Anyway thanks for trying to help, I am still hoping someone from MS could
> know more about it... ;)
> M.V.
> "Russell Fields" wrote:
> > Michal,
> >
> > I have to admit that I am fogged as to why you want to convert timestamp to
> > anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> > (The only meaning is that more recently changed rows have a higher timestamp
> > value than previously changed rows.)
> >
> > Just in case you are expecting something different, remember that timestamp
> > was also called rowversion for a while (a better name) and contains no time.
> >
> > Then I see your WHERE clause. Actually, you should never care how the
> > Binary(8) converts to compare with the interger value 0. Simply order by the
> > timestamp without a comparison, unless you allow the timestamp to be
> > nullable. If that is the case then, you may want:
> > WHERE RecTimeStamp IS NOT NULL
> >
> > For your comparison to evaluate as you apparently intended:
> > WHERE RecTimeStamp > 0x00
> >
> > RLF
> >
> >
> > "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
> > news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> > > Hello,
> > >
> > > During my work I have found following problem. All tables that I am using
> > > have column RecTimeStamp (timestamp). I do the selection based on last
> > > remembered timestamp. For some reasons my last timestamp is saved as
> > > decimal
> > > number (Int64), so I do some casting to timestamp in my queries. Once the
> > > DB
> > > global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> > > by
> > > following example:
> > >
> > > select cast (cast (2147483648 as bigint) as timestamp)
> > > result: 0x0000000080000000 - OK
> > > select cast (2147483648 as timestamp)
> > > result: 0x0A00000100000080 - MISSMATCH
> > > select cast (2147483647 as timestamp)
> > > result: 0x000000007FFFFFFF - OK
> > >
> > > Is this a bug of MSSQL server?
> > >
> > > Note: If I do no casting and simply use for example select top 1000 .
> > > where
> > > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> > > half
> > > of them has timestamp < maximum value of smallint and half of them higher,
> > > I'll get no 1000 records but only that lower half of them.
> >
> >
> >