Checksum Not Working
Jul 23, 2005
I'm developing a stored procedure to run an update based on values
entered into a .Net web form. I want to capture the chceksum of the
row when it is displayed on the form then validate that when the update
is exec'd. Simple enough logic, eh? The problem is when I try to use
the checksum(*) function, SQL server yells at me and says that it isn't
recognized. I'm using SQL Server 7, so wtf? I am not the admin of the
server and I'm skirting around SQL Server Enterprise Manager and using
any free utils, MS Access, and Visual Studio to maintain this db.
Thanks
Alex Jamrozek
View 7 Replies
ADVERTISEMENT
Feb 29, 2008
do you have to store the checksum from the task in order to verify change? Here is what I did and it seems it's not going to work
Lookup transformation that
selects matching fields and returns a t-sql with the binary_checksum(fields)
Checksum transformation returns the checksum of same inbound fields
conditional split passes on changed records to the update
getting all the records everytime and should not be happening.
is there a way to make this work or do I have to store the checksum
View 8 Replies
View Related
Jun 11, 2008
Hi
What is the purpose of checksum function???
RKNAIR
View 3 Replies
View Related
Feb 6, 2004
Hi,
Can anyone provide me with the syntax for comparing rows of two tables using binary checksum? The tables A and B have 8 & 9 columns respectively. The PK in both cases is Col1 & Col2. I want checksum on Columns 1 to 8.
Thanks
View 6 Replies
View Related
Feb 6, 2004
Please execute the script below to understand the problem -
---
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
--id & col1 make up the PK.
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')
select *
from test
select *
from test2
--The rows are identical.
--Script A
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
Thanks
drop table test
drop table test2
go
--
View 3 Replies
View Related
Jun 7, 2006
Hello All,
How to use CHECKSUM function and how it is useful?
Thanks
Sanjeev
View 1 Replies
View Related
May 29, 2007
Hi,I'd like advices about an idea I add to resolve a problem. thanks toyou in advance for yours answers.I have a database with tables that I load with flat file. The size ofeach table is 600 Mb. The flat file are the image of an applicationand there is no updated date or created date on any table. So mytables are just a copy of the data from the flat file.Now I'd like to create an History Table. So I have to determine whichlines changed and which one did'nt.As I don't have any date on my row the only answer I had unil know wasto check each column on each row to see if any data changed. If thedata changed I add a new line in my history date.My idea is to add a checksum column in both table on all columns. Toknow if any data change I just have to check my PK + my checksumcolumn.Do you think that is a good idea ? Is checksum a quick function ornot ?.Thanks.--K
View 3 Replies
View Related
Jan 24, 2008
Hi,
I heard that page checksum enabled will reports errors occured in the log. That's good.
Currently we have DBCC PHYSICAL_ONLY run alone and CHECKTABLE on group of tables on different days. A suggestion came from a person is to turn off 'DBCC CHECK TABLE' and run only when checksum reports an error and continue running CHECKDB WITH PHYSICAL_ONLY as before.
Is this suggestion a best practice? Please also write few lines to say why it is wrong or wright.
Thanks and best regards
Priw
View 1 Replies
View Related
Sep 24, 2007
Hi,
I recently researched on the CHECKSUM & CHECKSUM_AGG functions in T-Sql and found them really useful. However, I was skeptical that there are chances of these functions returning the same values for non-identical inputs. I just got on to the forums and found more than one unhappy folks writing about their experience with these functions.
I am designing a large database (warehouse) and found these functions tempting to implement for the sake of
using CHECKSUM for
- indexing long character fields
- multiple colums of the same table that would involve in a join and use the new checksum field instead
using CHECKSUM_AGG for
- I bulkcopy flat file soruce data into a character field of a table and to ensure that I am not loading the same file multiple times, I plan to use CHECKSUM_AGG( CHECKSUM( [FlatFileRecord] ) ) and verify that no two loads have the same output.
Can some body suggest if I can trust these methods for my purpose?
Many thanks in advance!!
Thanks,
Harish
View 5 Replies
View Related
Feb 18, 2008
it sounds like a column can be added to each row in a table that is the checksum or binary_checksum of an expression. How many bytes do each of these occupy? Does the answer depend on the number and/or length of items in the expression?
View 4 Replies
View Related
Dec 18, 2007
Hi:
i am using checksum in my etl process for this i have a checksum field to calculate the values in my table
the column is a computed column and it has a property for persistence .
what decision should i take should i make it persisted ot not what is the industry standard.
Can you please expalin how this property would affect the behaviour of the column
will this property affect me in any thing like indexes . please let me what step should i take should i make the column persisted or not .
Please let me know.
Thanks,
View 3 Replies
View Related
May 11, 2006
Morning Campers,
I have two tables src_monthly_terrrier and src_weekly_terrier. Both of these tables consists of 10+ columns. As the table names probably suggest, I import weekly data into one and monthly data into another.
All the source data comes from an Excel spreadsheet via straight Import Data procedure. The only guaranteed change on a weekly and monthly basis is that one of the columns in each table named src_date will obviously have the data value for whichever month or week's data it relates to.
I understand that through 'SQL Server Business Intelligence Development Studio' I can create an 'Intergrated Services' package that will import the spreadsheet details for me. I might be going the long way around this, but it was my intention to bring in all the data and then run a couple of 'INSERT INTO' Stored Procedures.
My biggest issue / vunerability I have is that there is no error checking of the data on the way in to ensure that it has not already been imported. What I was thinking I could do to resolve this was to create a Checksum field comprising of a number of different columns (incl src_date) and then somehow write something that will look at the values of each intended imported row and then work out whether a duplicate checksum was found in the target table and then rejected the import routine as Duplicate Data Found (or something similar) and move onto the next stored procedure.
My problem is two fold, one I have no idea how to create said checksum and two no idea where to begin on coding a procedure etc that looks to see if the value already exists etc etc.
I have looked up checksum creation on the net and there appears to be plenty of resource to explain how to create one, so I guess my main question is, Where do I start when it comes to writing some code that will do the check of the checksum before the importation routine begins (or at least the Insert Into procedures.
I would truly appreciate anyone's help on this. In the meanwhile I am off to learn how to create them.
I would like to add, if anyone sees this as a bad idea, then please speak up.
Thanks in Advance
View 1 Replies
View Related
Mar 19, 2004
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
Karam
View 7 Replies
View Related
Feb 11, 2015
what is the use of check sum in sql server:
ex: RESTORE DATABASE [XXX] FROM DISK = 'XX.BAK' with check sum
View 1 Replies
View Related
Aug 13, 2007
How much of a performance impact will using Cheksum have over Torn Page Detection for Page Verify Recovery? Thanks
View 3 Replies
View Related
Mar 13, 2007
Looking for some clarification on the CHECKSUM option of the BACKUP command.
If the the CHECKSUM option is specified in the backup, will the backup fail if CHECKSUM finds bad values (or at least raise an error)? Or, is it only reported when doing a RESTORE VERIFYONLY?
Thank you.
View 3 Replies
View Related
Aug 22, 2006
With this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70328
I started to thinkn about Microsoft really calculated checksum value.
This code is 100% compatible with MS original. That is, the result is identical.
You can use it "as is", or you can use it to see that MS function does not produce that unique values one could expect.
With text/varchar/image data, call with SELECT BINARY_CHECKSUM('abcdefghijklmnop'), dbo.fnPesoBinaryChecksum('abcdefghijklmnop')
With integer data, call with SELECT BINARY_CHECKSUM(123), dbo.fnPesoBinaryChecksum(CAST(123 AS VARBINARY))
I haven't figured out how to calculate checksum for integers greater than 255 yet.CREATE FUNCTION dbo.fnPesoBinaryChecksum
(
@Data IMAGE
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ SUBSTRING(@Data, @Index, 1),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
ENDActually this is an improvement of MS function, since it accepts TEXT and IMAGE data.CREATE FUNCTION dbo.fnPesoTextChecksum
(
@Data TEXT
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ ASCII(SUBSTRING(@Data, @Index, 1)),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
END
Peter Larsson
Helsingborg, Sweden
View 6 Replies
View Related
Jul 25, 2007
Hi,
We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.
We are using following type of inside the trigger.
UPDATE [dbo].[Hospital]
SET
[HospitalID]= I.[HospitalID],
[Name]= I.[Name],
[HospitalNumber]= I.[HospitalNumber],
[ServerName] = I.[ServerName],
[IsAuthorized]= I.[IsAuthorized],
[IsAlertEnabled]= I.[IsAlertEnabled],
[AlertStartDate]= I.[AlertStartDate],
[AlertEndDate]= I.[AlertEndDate],
[IsTraining]= I.[IsTraining],
[TestMessageInterval]= I.[TestMessageInterval],
[DelayAlertTime]= I.[DelayAlertTime],
[IsDelayMessageAlert]= I.[IsDelayMessageAlert],
[IsTestMessageAlert]= I.[IsTestMessageAlert],
[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],
[IsWANDownAlert]= I.[IsWANDownAlert],
[IsWANUpAlert]= I.[IsWANUpAlert],
[CreateUserID]= Hospital.[CreateUserID],
[CreateWorkstationID]= Hospital.[CreateWorkstationID],
[CreateDate]= Hospital.[CreateDate] ,
/* record created date is never updated */
[ChangeUserID]= suser_name(),
[ChangeWorkstationID]= host_name(),
[ChangeDate]= getdate() ,
/* Updating the record modified field to now */
[CTSServerID]= I.[CTSServerID]
FROM inserted i
WHERE
i.[HospitalID]= Hospital.[HospitalID]
AND binary_checksum(
Hospital.[HospitalID],
Hospital.[Name],
Hospital.[HospitalNumber],
Hospital.[ServerName],
Hospital.[IsAuthorized],
Hospital.[IsAlertEnabled],
Hospital.[AlertStartDate],
Hospital.[AlertEndDate],
Hospital.[IsTraining],
Hospital.[TestMessageInterval],
Hospital.[DelayAlertTime],
Hospital.[IsDelayMessageAlert],
Hospital.[IsTestMessageAlert],
Hospital.[IsUnAuthorizedMessageAlert],
Hospital.[IsWANDownAlert],
Hospital.[IsWANUpAlert]) !=
binary_checksum(
I.[HospitalID],
I.[Name],
I.[HospitalNumber],
I.[ServerName],
I.[IsAuthorized],
I.[IsAlertEnabled],
I.[AlertStartDate],
I.[AlertEndDate],
I.[IsTraining],
I.[TestMessageInterval],
I.[DelayAlertTime],
I.[IsDelayMessageAlert],
I.[IsTestMessageAlert],
I.[IsUnAuthorizedMessageAlert],
I.[IsWANDownAlert],
I.[IsWANUpAlert]) ;
Here is the checksum example which produces same results for two different input.
DECLARE @V1 VARCHAR(10)
DECLARE @V2 VARCHAR(10)
SELECT @V1 = NULL, @V2=NULL
SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@V1,@V2,0,30,180,1,0,1,1,1),
binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@V1,@V2,0,30,180,1,1,1,1,1)
Lookat the two binary_checksum above, they are different and should not match, but they both return same value.
Can someone please provide some info on these.
View 4 Replies
View Related
Mar 22, 2007
For detecting delta records, I'm a big fan of SQLIS' checksum transform. I'm having difficulty in it's install on my current machine, however. After the installation and the new transform is added to my DataFlow toolbox... I can't open the UI for the transform to define the checksum. Instead, I get the following error:
===================================
Could not load file or assembly 'Microsoft.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft Visual Studio)
------------------------------
Program Location:
at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransformUI.Edit(IWin32Window parentWindow, Variables variables, Connections connections)
at Microsoft.DataTransformationServices.Design.DtsComponentDesigner.StartComponentUI(Boolean startGenericUI)
Anyone have any suggestions? Thanks in advance.
View 2 Replies
View Related
Jul 20, 2007
I am using the Konesans Checksum transformation ( http://www.sqlis.com/21.aspx ) to detect changes in my big (many columns, type 2 SCD) dimensional table.
But I am running into collossions
The checksum transformation, sometimes misses a small change in the record, for instance when a certain flag is set or unset. Is there a more robust checksum generator? Of any other suggestions on to solve this?
thx
View 12 Replies
View Related
Aug 30, 2006
Does anyone know how to detect the CHECKSUM setting of the PAGE_VERIFY database option (2005 only)?
BOL (ALTER DATABASE) includes the following statement:
PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
The current setting of this option can be determined by examining the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.
However, there is no column named page_verify_option in the view sys.databases, and DATABASEPROPERTYEX('IsTornPageDetectionEnabled') does not discriminate between the settings CHECKSUM and NONE (it returns 0 for both)!
View 1 Replies
View Related
Aug 14, 2014
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0
It's not specific to -7077, for example:
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0
View 0 Replies
View Related
Aug 10, 2015
I'm trying to load data from old SQL server 2000 to new SQL server 2014. I need to do a checksum to check if all the source data is loaded in the target database(SQL server 2014). I've created the insert statement for the same which works. I need to use checksum to make sure all the source rows are loaded in the target table. I haven't done checksum before.
Here is my insert statement:
INSERT INTO [Test].[dbo].[Order_tab]
([rec_id]
,[date_loaded]
,[Name1]
,[Name2]
,[Address1]
,[Address2]
[code]....
View 2 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Feb 23, 2008
Original suggestion for my problem was - Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000. What am I missing?
using SQL 2000 server
SELECT LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE (LinksInfo.L_ID IN (SELECT ZL_ID FROM Location_Zip))
ZL_ID is not a primary key in Location_Zip
View 2 Replies
View Related
Mar 25, 2008
Hello again,
I think im missing something here, i just cant find out what it is.
I have a temp table:
CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME)
Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id):
SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id'
SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id
I need to get the tempID from the tempSearch table in order to compare it with @first_id
When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.
View 1 Replies
View Related
Jun 17, 2004
Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciated
View 1 Replies
View Related
Apr 25, 2006
I am using the following C#...
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
loader.ConnectionString = "PROVIDER=SQLOLEDB; Server=(local); database=TestDB; user id=test; password=password";
loader.ErrorLogFile = "C:log.txt";
loader.SchemaGen = true;
loader.SGDropTables = true;
loader.CheckConstraints = true;
loader.Execute(
"C:xsd.xsd"/* this file below */,
"C:xml.xml"
);
To generate tables in the database using the following xml schema...
(file: xsd.xsd...)
My problems is that it doesn't generate the tables and I am following the SQLXML2.0 documentation as closely as possible.
Does anyone know what I have to do to get it to generate the tables? Running it doesn't even produce an error.
Cheers,
Matt.
View 1 Replies
View Related
May 3, 2002
OK so I'm using dynamic sql to enable me to have a variable for the order by, but now it's giving me the following error
Syntax error converting character string to smalldatetime data type.
Any help is very much appreciated
Code follows
CREATE PROCEDURE spBattingAve1
@cid datetime,
@cid1 datetime,
@sid nvarchar(10)
AS
DECLARE @query nvarchar(4000)
SET @query = 'SELECT MAX(t_batting.runs) AS BestRuns,
COUNT(t_batting.dnb) AS DidNotBat, t_player.surname, t_player.firstname,
t_batting.player_id,
Sum(t_batting.runs) as SumOfruns, COUNT (*) AS Games,
COUNT (t_batting.notout) as nout,
(Sum(runs)/(COUNT(*)-(COUNT(notout)+COUNT(dnb)))) as AverageRun,
(COUNT(*)-COUNT(dnb)) AS Innings,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 50 AND runs <100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overfifty,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overton
FROM t_batting
INNER JOIN t_game ON t_batting.game_id = t_game.game_id
INNER JOIN t_player ON t_batting.player_id = t_player.player_id
WHERE t_batting.player_id = t_player.player_id
AND t_game.date >= '+@cid+' And t_game.date <= '+@cid1+'
GROUP BY t_batting.player_id, t_player.surname, t_player.firstname, t_player.player_id
HAVING (COUNT(*)-(COUNT(notout)+COUNT(dnb))) <> 0
order by '+@sid+'
DESC'
exec (@query)
View 1 Replies
View Related
Jun 21, 2001
In SQL 7.0, when i use the below query in query analyzer, I got an error message. why?
======================
Set Identity_insert on
UPDATE Table1
SET no = 3
WHERE no = 4
Set Identity_insert off
go
Error message
=============
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'off'.
View 2 Replies
View Related
May 1, 2006
Hi,
I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)
Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:
1) the boot.ini line:
multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB
2) max server memory (MB) = 3000
3) min server memory (MB) = 3000
4) awe enabled = 0
5) @@version = 8.00.2039 which is SQL Server 2000 SP4
Has anyone seen this before? Any ideas on how to troubleshoot this?
View 1 Replies
View Related
Oct 16, 2006
Hi all:
Because me not this functioning the following one query.
simply me not the function respects min, because?, that I am doing badly?
Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime
Thanks.:confused:
View 6 Replies
View Related
