Problem returning a timestamp column inside an TSQL Transaction

I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@.LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @.LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@.NewLastChanged"):

1select @.TimestampCheck = LastChangedfrom ADD_Addresswhere AddressId=@.AddressId23if @.TimestampCheckisnull4begin5RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1)-- AddressId does not exist.6return -17end8 else if @.TimestampCheck <> @.LastChanged9begin10RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)11return -112end131415Begin Tran Address1617Update ADD_Address18set StreetNumber= @.StreetNumber, AddressLine1=@.AddressLine1, StreetTypeId=@.StreetTypeId, AddressLine2=@.AddressLine2, AddressLine3=@.AddressLine3, CityId=@.CityId, StateProvidenceId=@.StateProvidenceId, ZipCode=@.ZipCode, CreateId=@.CreateId, CreateDate=@.CreateDate19where AddressId= @.AddressId2021select @.error_code =@.@.ERROR, @.AddressId= scope_identity()2223if @.error_code = 024begin25commit tran Address2627select @.LastChanged = LastChanged28from ADD_Address29where AddressId = @.AddressId3031if @.LastChangedisnull32begin33RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)34return -135end36if @.LastChanged = @.TimestampCheck37begin38RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)39return -140end41return 0

I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?

Thank you,

jspurlin 

No need for a transaction for that really. Just go about it a different way:

Update {fields} FROM {table} WHEREAddressID=@.AddressID ANDLastChanged=@.LastChanged

Then get the number of records affected, and the error codes.

if there were no errors, and no rows were affected then you can either raise a generic error ('record changed or does not exist'), or you can go and look and see which of the two (AddressID,LastChanged) didn't exist, although there is the possability the data may change between the update and when you go and try to figure out why it failed, in which case you may get an incorrect error message (Saying it didn't exist, when it was only changed or saying it was changed when it didn't exist).

Problem Rendering Multi Column

I've added a link to a sample on how my test-column report renders.

http://www.leboeuf.be/MSDN/Dagelijkse%20Verkoop%20Merk.pdf

Any idea what's happening, while normally previewing the report (column by page) this problem doesn't occur .... The rendering is the same to TIFF

http://www.leboeuf.be/MSDN/Dagelijkse%20Verkoop%20Merk.tif

Any suggestion on what is the problem and even better, if there is a solution.

Found the problem, the page width exceeded the normal page width op an A4 page, obviously that gives a rendering problem, as soon i dropped 1 column redering was 'normal'

Problem referencing a column in a Table Variable in a query.

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @.THeader" . @.THeader is the name of the variable table and the error occurs with such references as @.THeader.ApplyAmt, @.THeader.TransactionHeaderID, etc.

declare @.THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @.THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @.THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

Update @.THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @.THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @.THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @.THeader.ApplyAmt
FROM @.THeader, mtblTransactionHeader
WHERE @.THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table

Thanks in advance

smHaig

Try adding an alias to @.THeader

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = t.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = t.ApplyAmt
FROM @.THeader t, mtblTransactionHeader
WHERE t.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID -- put the values back into original table

Problem passing columns as parameter!

Dear friens,

I need to pass a column as a parameter in my query. I did this:

ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '

EXEC @.SQL

END

END

THE COMAND EXECUTE SUCESSFULLY IN QUERY ANALISER, BUT IN ASP.NET 2.0 CLIENT RETURNS THE FOLLOWING ERROR:

Server Error in '/WS_GestaoDesktop' Application.

---

The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

Source Error:

Line 3269: }

Line 3270: dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable dataTable = new dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable();

Line 3271: this.Adapter.Fill(dataTable);

Line 3272: return dataTable;

Line 3273: }

The problem is based here:

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

if you want to pass a static value use

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ''' + @.Direccao + ''')

if you want to pass a identifier use

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ' + @.Direccao + ')

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Dear Friend,I changed as you told, but the error still there! :-(

CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,

dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,

dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='+ @.Direccao +')

AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '

EXEC @.SQL

END

END

ERROR:

Msg 203, Level 16, State 2, Procedure GD_SP_GET_UsersByDIR_COD, Line 49

The name 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

|||

change the following things..

CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,

dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,

dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')

AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXEC @.SQL

END

END

|||

Manid,

I changed as you told, but the error still there! :-(

Regards.

|||I guess you are either trying to change another procedure than you are executing or you will have to provide the whole snippet of code via mail or something to make it reproducable.

HTH, Jens K. Suessmeyer.

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

Dear Jens,

I created a more simple query, based in the same problem:

CREATE PROCEDURE TEMP

AS

DECLARE @.SQL varchar(8000)

DECLARE @.prmFieldName varchar(20)

DECLARE @.prmFieldValue varchar(20)

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT New_Computername

FROM HARDWARE

WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXEC @.SQL

The logical is the some of other queries, but in this one I can't executed it, because returns the following error:

Msg 2812, Level 16, State 62, Procedure TEMP, Line 15

Could not find stored procedure 'SELECT New_Computername

FROM HARDWARE

WHERE UserID ='C095122' '.

(1 row(s) affected)

If you can put this query with column parameter workink good, my problem probably is resolved for the other queries.

Thanks!

|||Sorry and blame on me for not seeing this, you will have to wriite the exec as follows:

EXEC(@.SQL)

HTH, jens K. Suessmeyer.

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

Dear friends,

I created a new simple query to try to resolve the problem of passing a column as parameter, using sp_executesql, but still return error. :-(

ALTER PROCEDURE TEMP

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

DECLARE @.SQL varchar(8000)

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = N'SELECT New_Computername

FROM HARDWARE

WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXECUTE sp_executesql @.SQL;

ERROR:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'.

|||

Dear Friends,

I found the solution for my problem.

I must use EXECTUTE sp_executesql @.SQL in spite of EXEC @.SQL, and I must use nvarchar(4000) in spite of varchar(8000).

The final Result:

ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(30),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL nvarchar(4000)

SET @.SQL = N'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')

AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXECUTE sp_executesql @.SQL

END

END

THANKS FOR ALL YOUR IMPORTANT SUPPORT!!!

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
=============================
CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GO
Do not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:

> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity()
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccurent,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================
> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================
> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
============================= CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GODo not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:
> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity()
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccurent,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
=============================
CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GODo not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:

> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity(
)
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists
in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccuren
t,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================
> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================
> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

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

Problem need help

ok the criteria with hath i have the problem looks like this

SELECT DISTINCT table1.col1,...,table1.columnN,.....TableM. Column X
FROM Tables1, Table M
INNER JOIN Table1
ON (table1.col1 = tableN.colZ) , Tablex
WHERE (table1.col1 = tableN.colZ)
AND (table1.col1 LIKE '.........')
:)
in this section '......' i have to introduce the next statment editbox text or other component externaly introduced datavalue wich is al integer value asstring
:eek:yess it is exactly whath i want to use an earlier or exactly the last data introduced in the table to wiew all the modification iff necesary, and to bee able to list somme modified data if it is needed.
thanks bekause sommebody sad to mee thath i have to write somme special procedure to do this thing.|||yess the ideea is good, but it gives me back somme empty cells and i'm sure thath i have data in the tables|||the code is the folowing:
"SELECT DISTINCT Contractdetailat.NRCONTRACT, Contractdetailat.DINDATA,
Contractdetailat.PINALADATA, Contractdetailat.PUNCTDELUC,
Contractdetailat.COMPONENTE, Contractdetailat.PRETUNITAR,
Contractdetailat.NRBUC, Contractdetailat.PRETTOTAL,
Firma.DENUMIREFI, Firma.NRREGCOM, Firma.CODFISCAL,
Firma.SEDIULSOCI, Firma.JUDETUL, Firma.CODIBAN,
Firma.BANCA, Firma.PUNCTDELUC, Firma.NRCONTRACT,
Delegati.NUMELEDELE, Delegati.BI_CI_, Delegati.SERIA,
Delegati.ELIBERATPO, Delegati.MIJLOCTRAN,
Delegati.NRMASINA, Delegati.DATAEXPED
FROM "D:\ISTI\28.11.2006\contractdetailat.dbf" Contractdetailat
INNER JOIN "D:\ISTI\28.11.2006\firma.dbf" Firma
ON (Contractdetailat.NRCONTRACT = Firma.NRCONTRACT) , "D:\ISTI\28.11.2006\delegati.dbf" Delegati
WHERE (Contractdetailat.NRCONTRACT = Firma.NRCONTRACT)
AND (Contractdetailat.NRCONTRACT like'?"&intrari.nrcontract.text&"*')"
now the rezult soud be
at nrconract i have 25 contracts with the afferent next rows and i don't need all of it listed in the same sheet in one sheet i need listig of only one contract that is tha ideea.
thanks.
by the way the program is one man creation i'm vorking solo and it is werry hard to work solo|||the return rezult is zipp !!!!!
it is a blank datatable
I'm sure there is somme data on it because it when i select all it can be displayd in a data gird

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob