Re: SQL Server deadlocks - Database Discussion Boards

Member 1115415113-Dec-15 6:42

13-Dec-15 6:42

I'm dealing with a strange (for me) situation in SQL Server.
I'm having a process that locks the same table twice. If the same time that the first process is locking the table a second process tries to lock the same table a deadlock happens immediately. Is this normal?
I wrote a script and run it twice from different connections.
Here is the script:

SQL

begin tran myTran
create table anyTable (anyColumn varchar(10), primary key (anyColumn))

declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1

print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())	--remove for the second process (victim)
update anyTable set anyColumn = anyColumn												--remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())		--remove for the second process (victim)

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	print 'Waiting. Time = ' + convert(varchar(40), @exec)
	set @exec = getdate()
end

print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())

drop table anyTable

commit tran myTran

Re: SQL Server deadlocks

Kornfeld Eliyahu Peter13-Dec-15 7:17

Kornfeld Eliyahu Peter

13-Dec-15 7:17

Are you executing this exact code twice?

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: SQL Server deadlocks

Member 1115415113-Dec-15 19:41

Member 11154151

13-Dec-15 19:41

Not this exact code twice. But similar yes. The only difference is the three lines on top which are commented for the process I would like to be the deadlock victim.
These lines:
print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
update anyTable set anyColumn = anyColumn --remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
The only alternative that I can think of is to check if the table is locked by my SPID before try to lock it for the second time.

Thanks for your posts. If can anyone come with any idea I would appreciate it.

Re: SQL Server deadlocks

Kornfeld Eliyahu Peter13-Dec-15 20:23

Kornfeld Eliyahu Peter

13-Dec-15 20:23

It was much better two post the code you have problem with - including the way you are running it...
The reason I say this, is that I took your code and immediately saw that it can not be run twice as every instance will try to create a table, and that can be a serious problem...
Other thing is the transaction - do not create a transaction if you need not (and from the sample you had here you need not, as you are updating the very same table twice)...

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: SQL Server deadlocks

Member 1115415113-Dec-15 20:36

Member 11154151

13-Dec-15 20:36

You are right. The second process should run something like this:
begin tran myTran

declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end

print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())

commit tran myTran

But the reason that I lock the same table twice is much more complicated that you think. This is not a simple process. The two locks are coming from different updates. I have just tried to make it simple.

Thanks any way.

Re: SQL Server deadlocks

Member 1115415114-Dec-15 0:00

Member 11154151

14-Dec-15 0:00

The situation is much more complex than I thougth it was.
The deadlock happened between the primary key of the table and the index of the same table. I really do not know why. A workaround that I found is to use SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK) insted of UPDATE EDEFADDERS SET ID = ID.
Here is the scripts that you need to reproduced it. (I believe complete and correct!!!)
Script to create the table:

SQL

CREATE TABLE EDEFADDERS(
	id varchar(32) NOT NULL,
	adderkind int NOT NULL,
	description varchar(30) NOT NULL,
	formula text NOT NULL,
	transkind int NOT NULL,
	addedset varchar(255) NULL,
	removedset varchar(255) NULL,
	adderdecs int NOT NULL,
PRIMARY KEY CLUSTERED (id ASC) 
)

CREATE NONCLUSTERED INDEX EDEFADDERS_I1 ON EDEFADDERS (adderkind ASC)

INSERT INTO EDEFADDERS VALUES ('088162994983574692C347CE326582BD', 0, 'Πληρ. Μετρητοίς', '"Συνολική Αξία"', 2, 41, , 0)
INSERT INTO EDEFADDERS VALUES ('0DCE2805DF7E4B4C875649128C16A14C', 0, 'Εισπρ. Εμβασμ.', '"Συνολική Αξία"', 1, 24, , 0)
INSERT INTO EDEFADDERS VALUES ('1072EB880FABF8459B99EC38058B6CA1', 2, 'Εισπράξεις Γραμ.', '"Συνολική Αξία"', 1, 22, , 0)
INSERT INTO EDEFADDERS VALUES ('142F006F40F0DA4C80ACEA477E2B5662', 0, 'Εκπτώσεις Χονδρ.', '"Αξία Εκπτωσης" + "Πιστωτικό Εκπτωσης" * "Καθαρή Αξία"', 5, 70,72,80, 73,74,83, 0)
INSERT INTO EDEFADDERS VALUES ('1D4E3250AFD52343A1680A4387BA1490', 0, 'Εισπρ. Μετρητοίς', '"Συνολική Αξία"', 1, 21, , 0)
INSERT INTO EDEFADDERS VALUES ('22A60A4EC35EC14E8AD82B97A5906572', 2, 'Εισπράξεις Επιτ.', '"Συνολική Αξία"', 1, 23, , 0)
INSERT INTO EDEFADDERS VALUES ('24B3B31AD780124B888C5D935966F679', 3, 'Επιστροφές Μετρ.', '"Συνολική Αξία"', 2, 49, , 0)
INSERT INTO EDEFADDERS VALUES ('273FED73284E5E4689BAA7F913173086', 0, 'Εκπτ. Ειδών Χ.', '"Αξία Εκπτωσης Είδους"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('3034E6BD63AF114A8DEB61B442C8D873', 0, 'Πωλήσεις Π.Υ. Λ.', '"Καθαρή Αξία"', 5, 84, , 0)
INSERT INTO EDEFADDERS VALUES ('349B9E68AE2D104689B6E2E67C618A8E', 0, 'Εκπτώσεις Π.Υ.Χ.', '"Αξία Εκπτωσης"', 5, 78, 79, 0)
INSERT INTO EDEFADDERS VALUES ('3708771B165CD74E81323BCBDE14CEC7', 2, 'Εισπράξεις Μετρ.', '"Συνολική Αξία"', 1, 21, , 0)
INSERT INTO EDEFADDERS VALUES ('38570F0087D3D94D838C4219E713F0FA', 0, 'Εισπρ. Πιστ.Καρτ', '"Συνολική Αξία"', 1, 26, , 0)
INSERT INTO EDEFADDERS VALUES ('3AC77B713D3FB546947FE018C7B2BF7E', 0, 'Πληρ. Εμβασμ.', '"Συνολική Αξία"', 2, 44, , 0)
INSERT INTO EDEFADDERS VALUES ('4259AB5FE2C25C4DA9C975E24F42BA4F', 0, 'Πωλήσεις Λιαν.', '"Καθαρή Αξία"', 5, 76,82, 77, 0)
INSERT INTO EDEFADDERS VALUES ('4AF5EDBA1B0BED4CB4085F3B8D567AA1', 0, 'ΠΩΛ.ΑΝΑ ΚΑΤΗΓΟΡΙ', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1' )) AND ( "Κατηγορία Είδους" = 01 ))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('4BD015E24E739C4292009DB18DA2AED3', 1, 'Σύν.Εκπτ.Πελατών', '"Αξία Εκπτωσης Πελάτη"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('5390C0D7C85DE44797C2AF36B46FD441', 0, 'Ποσοτ. Εκπτ. Χ.', '"Αξία Εκπτωσης Τιμοκαταλόγου"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('5986CE3CE649D14BA0E7DC9138DC24C9', 0, 'Πωλήσεις Χονδρ.', '"Καθαρή Αξία"', 5, 70,72,80, 73,74,83, 0)
INSERT INTO EDEFADDERS VALUES ('5A2A9016A3C57A4CB6FD3FD9D3B5E40E', 0, 'Πιστωτικά Εκπτ.', '"Καθαρή Αξία"', 5, 74, , 0)
INSERT INTO EDEFADDERS VALUES ('5AC130A23482C345B581091BA5504859', 0, 'Αθρ. Επιχ. 31', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1 - 2' )) AND ( "Κατηγορία Είδους" = 01 ))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('5B61F8A2D361014484234E50AEC99045', 0, 'Λοιπές Εξαγωγές', '"Καθαρή Αξία"', 5, 11, , 0)
INSERT INTO EDEFADDERS VALUES ('5D59097BF887F240801FFAF200B427A6', 0, 'Αγορές Χονδρικής', '"Καθαρή Αξία"', 4, 40,42, 43,44,49, 0)
INSERT INTO EDEFADDERS VALUES ('604F30B7A1CB434D9F7DADD59A9B9A74', 0, 'Εκπτώσεις Πελ. Χ', '"Αξία Εκπτωσης Πελάτη"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('641899F5AFF050479F9454573841EC8E', 3, 'Πληρωμές Αντικ.', '"Συνολική Αξία"', 2, 45, , 0)
INSERT INTO EDEFADDERS VALUES ('6523E3A73BAD414EA534954D1C21106E', 0, 'Πληρ. Γραμματ.', '"Συνολική Αξία"', 2, 42, , 0)
INSERT INTO EDEFADDERS VALUES ('666D15457C526B4FBF96539E20BBE802', 2, 'Εισπρ. Εμβασμ.', '"Συνολική Αξία"', 1, 24, , 0)
INSERT INTO EDEFADDERS VALUES ('706E587CF5A1E648939391046C5FA117', 3, 'Πληρωμές Επιταγ.', '"Συνολική Αξία"', 2, 43, , 0)
INSERT INTO EDEFADDERS VALUES ('714F6D3207A8BD4BACDAD6FCB86F774B', 1, 'Σύν.Ειδικών Εκπτ', '"Ειδική Εκπτωση"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('73C057D1EFC0C440AB42AB2E43E59ED7', 0, 'Εισπρ. Αντικατ.', '"Συνολική Αξία"', 1, 25, , 0)
INSERT INTO EDEFADDERS VALUES ('77D6C0A98A289F4F92FCBA462DC78449', 0, 'Ειδικές Εκπτ. Χ.', '"Ειδική Εκπτωση"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('7CC5CEDDAB8D8746AD7B1D7E315B75DE', 0, 'ΠΩΛΗΣΕΙΣ', '"Καθαρή Αξία"', 5, 70,72,76,78,82,84, 73,74,79,83, 0)
INSERT INTO EDEFADDERS VALUES ('82A8E1B3BF46D04EB1DC0CB178FE15BB', 2, 'Εισπρ. Αντικατ.', '"Συνολική Αξία"', 1, 25, , 0)
INSERT INTO EDEFADDERS VALUES ('8F8A505724081E4AB510EEE90A4F58C1', 1, 'Σύν.Εκπτ. Ειδους', '"Αξία Εκπτωσης Είδους"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('90499A1A8954D741A1C076735E4C4B6E', 3, 'Πληρωμές Εμβασμ.', '"Συνολική Αξία"', 2, 44, , 0)
INSERT INTO EDEFADDERS VALUES ('97CCD23F1432954EBABBE391B0945CE9', 0, 'Εκπτώσεις Λιαν.', '"Αξία Εκπτωσης"', 5, 76,82, 77, 0)
INSERT INTO EDEFADDERS VALUES ('9E0D358AECAE0A47BD0D27CE8A8FFCF3', 0, 'Εκπτώσεις Π.Υ.Λ.', '"Αξία Εκπτωσης"', 5, 84, , 0)
INSERT INTO EDEFADDERS VALUES ('A084AC5553C52544B4B053DF77817265', 2, 'Εισπρ. Πιστ. Κ.', '"Συνολική Αξία"', 1, 26, , 0)
INSERT INTO EDEFADDERS VALUES ('A0CDBA5347B1CD48B2119BE5C007F3C5', 0, 'Εισπρ. Επιταγών', '"Συνολική Αξία"', 1, 23, , 0)
INSERT INTO EDEFADDERS VALUES ('A329C05004756C4EBAACA1B5F269DE1F', 0, 'Αθρ. Επιχ. 32', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1 - 2' ))*( inset ( "Κατηγορία Είδους" , '1 - 2' )))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('B175031C0CA3364193E39C41FC29A262', 2, 'Επιστροφές Μετρ.', '"Συνολική Αξία"', 1, 29, , 0)
INSERT INTO EDEFADDERS VALUES ('B54944B81669EF498F3E2D55B5F8ECD5', 1, 'Σύν.Ποσοτ. Εκπτ.', '"Αξία Εκπτωσης Τιμοκαταλόγου"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('B6527AFBC4B345469C4B81D1B768504F', 0, 'Πληρ. Επιταγών', '"Συνολική Αξία"', 2, 43, , 0)
INSERT INTO EDEFADDERS VALUES ('BE5E6E822BF0D946918436C3F9F28737', 0, 'Αγορές Παρ.Υπηρ.', '"Καθαρή Αξία"', 4, 46, 47, 0)
INSERT INTO EDEFADDERS VALUES ('C5D6BFD587A11948949446161E501015', 3, 'Πληρωμές Μετρητ.', '"Συνολική Αξία"', 2, 41, , 0)
INSERT INTO EDEFADDERS VALUES ('CABA8202B260F046945AD56CCAD51A56', 0, 'Εκπτ. Τρ.Πληρ. Χ', '"Αξία Εκπτωσης Τρόπου Πληρωμής"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('CDEAFE4EF039DD4A9A7721A35BC949AA', 1, 'Σύν.Εκπτ.Τρ.Πληρ', '"Αξία Εκπτωσης Τρόπου Πληρωμής"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('CE97BE8509223145B274049AE6EABBF4', 0, 'Πωλήσεις Π.Υ. Χ.', '"Καθαρή Αξία"', 5, 78, 79, 0)
INSERT INTO EDEFADDERS VALUES ('D051DCEB86DFAB4BA3B564A1B4570CED', 3, 'Πληρωμές Γραμ.', '"Συνολική Αξία"', 2, 42, , 0)
INSERT INTO EDEFADDERS VALUES ('EC08552E3FDBA14189D39418D5571545', 0, 'Εισπρ. Γραμμ.', '"Συνολική Αξία"', 1, 22, , 0)
INSERT INTO EDEFADDERS VALUES ('FEB6C6CE84571F4A98558A515A822C47', 0, 'Πληρ. Αντικατ.', '"Συνολική Αξία"', 2, 45, , 0)

Script to run it twice (simultaneously) that will produce the deadlock:

begin tran myTran
 
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
update EDEFADDERS set ID = ID
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right('00' + rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	set @exec = getdate()
end
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
update EDEFADDERS set ID = ID
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
commit tran myTran

Script to workaround the problem:

begin tran myTran
 
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK)
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right('00' + rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	set @exec = getdate()
end
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK)
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
commit tran myTran

Sorry to anyone I mislead.

Re: SQL Server deadlocks

Richard Deeming14-Dec-15 2:50

Richard Deeming

14-Dec-15 2:50

Member 11154151 wrote:
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end

Rather than sitting in a tight loop waiting for a particular time, it would be better to use WAITFOR[^]. That will let SQL free up the processor to do other things, rather than constantly querying the current time and shouting "are we nearly there yet?" until the target time arrives.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server deadlocks

Member 1115415116-Dec-15 2:28

Member 11154151

16-Dec-15 2:28

The answer to my question was given to me by someone from Microsoft.
The reason why this deadlock happens is that the optimiser is choosing the second index for this SQL:
Update anyTable set anyColumn = anyColumn mak
So, it puts a lock in order to do the update and then SQL server is putting an exclusive lock to the primary key for the update and release the first lock of the second index.
At this time the second process is putting a lock for the second index and waits for the 1st process to finish in order to get an exclusive lock for the primary key.
The 1st process now is trying to reupdate the same table and to put a lock for the second index but this is already locked by the 2nd process (who waits the 1st).
So, the deadlock is inevitable.

need to consult about project discussion

divinity technologies10-Dec-15 22:14

divinity technologies

10-Dec-15 22:14

JavaScript

hello
                  i want some clarification on some database related issue can you please suggest us.
 
please find attachment
1.this excel sheet we have to upload in database  
2.how can be database table structure

in these excel sheet variable columns and variable rows will be there .

Re: need to consult about project discussion

Mycroft Holmes11-Dec-15 1:26

Mycroft Holmes

11-Dec-15 1:26

Hah hah your screwed, you will now spend the next 8 months working around edge cases on excel.

You are looking at one of the classic development nightmares. Using Excel as a datasource for a database application.

If the format is stable you can map the cells to fields in your table, a REALLY tedious job that will need to be repeated every time some fool of a use inserts a row or column.

Never underestimate the power of human stupidity
RAH

Creating Access database with VB6

Member 121949778-Dec-15 18:05

Member 12194977

8-Dec-15 18:05

I am creating a database in Access with 3 tables. Each table has different number of columns or fields. I get an error when creating more than 8 fields. If I comment out the other fields, then everything works great. Can anyone help? Code is as follows:

Dim FileName As String, strConn As String
Dim adoCat As ADOX.Catalog, adoTable As ADOX.Table
Dim tblCollection As Collection
Dim Script As Object

'check and/or build DB
Set Script = CreateObject("Scripting.filesystemobject")
If Script.FolderExists("C:\Business Pro") Then
'folder is in place...move on
If Dir("C:\Business Pro\BusinessPro.mdb") = "" Then
'dir not in place...build
lblPB.Caption = "No database found. Creating database..."
FileName = "C:\Business Pro\BusinessPro.mdb"
'create instance of ADOX-object
Set adoCat = New ADOX.Catalog
'creat instance of Collection-object
Set tblCollection = New Collection
Set adoTable = New ADOX.Table
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FileName & ";"
'create the db
adoCat.Create (strConn)
'name table
tblCollection.Add "Customer_Contacts"
With adoTable
.Name = "Customer_Contacts" 'name the table
.Columns.Append "CustID", adInteger 'create a field in primary key
.ParentCatalog = adoCat 'set Parent catalog
.Columns("CustID").Properties("AutoIncrement").Value = True
.Keys.Append "PrimaryKey", adKeyPrimary, "CustID" 'append primary key
'add rest of fields
.Columns.Append "FirstName", adWChar
.Columns.Append "LastName", adWChar
.Columns.Append "Address", adWChar
.Columns.Append "City", adWChar
.Columns.Append "State", adWChar
.Columns.Append "Zip", adLongVarWChar, 5
.Columns.Append "PhoneNumber", adLongVarWChar, 10
End With
'add table to mdb
adoCat.Tables.Append adoTable

'create 2nd table
Set adoTable = New ADOX.Table
'name table
tblCollection.Add "Labor_Rates"
With adoTable
.Name = "Labor_Rates" 'name the table
.Columns.Append "ID", adInteger 'create a field in primary key
.ParentCatalog = adoCat 'set Parent catalog
.Columns("ID").Properties("AutoIncrement").Value = True
.Keys.Append "PrimaryKey", adKeyPrimary, "ID" 'append primary key
'add rest of fields
.Columns.Append "dateFiled", adDate
.Columns.Append "NumberCode", adWChar
.Columns.Append "Description", adWChar
.Columns.Append "LaborRate", adWChar
.Columns.Append "Frequency", adWChar

End With

'add table to mdb
adoCat.Tables.Append adoTable

'create 3rd table
Set adoTable = New ADOX.Table
'name table
tblCollection.Add "New_Contract"
With adoTable
.Name = "New_Contract" 'name the table
.Columns.Append "CustID", adInteger 'create a field in primary key
.ParentCatalog = adoCat 'set Parent catalog
.Columns("CustID").Properties("AutoIncrement").Value = True
.Keys.Append "PrimaryKey", adKeyPrimary, "CustID" 'append primary key
'add rest of fields
.Columns.Append "dateFiled", adDate
.Columns.Append "ProjectCode", adWChar
.Columns.Append "Name", adWChar
.Columns.Append "Address", adWChar
.Columns.Append "City", adWChar
.Columns.Append "State", adWChar
.Columns.Append "Description", adWChar
.Columns.Append "LaborRate", adWChar
'error is here. when the last 2 columns are commented out, the code works fine. When uncommented, I get an error of file being too large...
' .Columns.Append "DaysOnJob", adLongVarWChar
' .Columns.Append "Remarks", adWChar

End With
'add table to mdb
adoCat.Tables.Append adoTable

'release objects from memory
Set adoTable = Nothing
Set tblCollection = Nothing
Set adoCat = Nothing

Else:
lblPB.Caption = "Database exists. Loading software..."
tmrLoad.Enabled = True
tmrLoad.Interval = 1000

End If
Else
'make directory
MkDir "C:\Business Pro"
'file is created. Create db...run through process again
chkForDB
End If
End Sub

Re: Creating Access database with VB6

Mycroft Holmes8-Dec-15 20:46

Mycroft Holmes

8-Dec-15 20:46

I'm afraid you are really pushing your luck trying to get support for a language that has not been supported for more than a decade. You should download and use the free VB.net express.

Never underestimate the power of human stupidity
RAH

Re: Creating Access database with VB6

Member 121949778-Dec-15 21:03

Member 12194977

8-Dec-15 21:03

I was just thinking of learning the .Net language and converting over and rewrite my whole program. I guess now is as good a time as any to do it. Thanks!

Re: Creating Access database with VB6

Mycroft Holmes9-Dec-15 1:23

Mycroft Holmes

9-Dec-15 1:23

I would recommend converting to c#, the learning curve will be marginally steeper but the learning resources are 2-3 time the amount of VB.net code.

Never underestimate the power of human stupidity
RAH

Re: Creating Access database with VB6

CHill609-Dec-15 5:29

CHill60

9-Dec-15 5:29

Some points that might help ...
You're not defining the maximum size of the text columns so they are all defaulting to 255.
Did you really want a Memo field for DaysOnJob?
For that matter, shouldn't LaborRate be adDouble not adWChar? Or an integer - linking to Labor_Rates.ID
Consider using the DAO library rather than the ADO extension library (which is not stable).

On the subject of using VB6 - now is definitely the time to stop using it! But don't assume that VB.NET is the natural progression into .NET. C# is really worth considering. If not then MacDonald publish a book for programmers transitioning from VB6 (The one I had was "The Book of Visual Basic 2005", but that's well out of date now).

Re: Creating Access database with VB6

Member 121949779-Dec-15 21:18

Member 12194977

9-Dec-15 21:18

Thanks for the pointers. I went about it all differently. I created the tables I need in Access 2007 and through code, I just copy the database to another directory. I found it to be less of a headache than creating all the tables through code. Thanks again Smile | :)

Re: Creating Access database with VB6

CHill6010-Dec-15 5:23

CHill60

10-Dec-15 5:23

Yes - much less of a headache! Smile | :)

One thing to watch out for .. if in the future you need to change the schema of the database after you have rolled it out to users you will need to consider how to amend existing data... but by then you will have upgraded to .net Laugh | :laugh:

How to update sequence in MySQL

Jassim Rahma8-Dec-15 4:10

Jassim Rahma

8-Dec-15 4:10

Hi,

I have order_tier database and I am adding tiers by checking what's the max number then add 1 to it. Now I have a problem when deleting because if the order has 4 tiers then it should have from 1 to 4, if the user deleted tier 4 then it will be 1 to 3, if the user deleted tier 2 then tier 3 should be 2 and 4 should be 3.. By this I mean it should have always a sequence without a gap in the middle and it should move from the bottom so if user deleted tier 6 of 7 tier's order then tier 7 will be 6, etc.

How can I implement this in using PL/SQL in MySQL?

SQL

DECLARE param_last_tier INT;

IF EXISTS (SELECT tier_number FROM order_tiers WHERE order_id = param_order_id) THEN
BEGIN
    SET param_last_tier = (SELECT MAX(tier_number) FROM order_tiers WHERE order_id = param_order_id) + 1;
END;
ELSE
BEGIN
    SET param_last_tier = 1;
END;
END IF;

INSERT INTO order_tiers (order_id, tier_type, tier_number, created_user)
VALUES (param_order_id, param_tier_type, param_last_tier, param_created_user);

Thanks,
Jassim[^]

Technology News @ www.JassimRahma.com

Re: How to update sequence in MySQL

CHill608-Dec-15 4:40

CHill60

8-Dec-15 4:40

I think what you are looking for is a way to emulate the sql row_number function within MySQL (or do you mean Oracle PL/SQL?)

This article[^] shows a method which I've used successfully in the past ... example posted here in case the link dies

SET @row_number = 0;
 
SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees
LIMIT 5;

As an aside, using Max() to determine the "next" number when inserting rows doesn't always work well in multi-user environments. You should have a look at auto_increment[^] instead

Giving access permissions to user webadminqa_iispool

indian1434-Dec-15 12:23

indian143

4-Dec-15 12:23

Hi All,

I want to check if the user exists on a database with domain and user registration "XXXCXXX-XXX\webxxxxxqa_iispool", if doesn't exists create one, if it exists then give user the roles to access the application db_owner or public on few databases on the server using user login properties by using the script.

I know we can do this by using Management Studio, is there any way to write it all as a script to run it when we have multiple databases. Any help is appreciated, link, suggestion or code snippet anything. Thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Giving access permissions to user webadminqa_iispool

Mycroft Holmes4-Dec-15 13:03

Mycroft Holmes

4-Dec-15 13:03

Anything you can do in SSMS can be done by script, you could put a stored proc in the Master database and run it from there. When you are in SSMS there is an icon to create a script on most actions, use this to jump start you requirements.

Most results from a google search of the operation you need will give you sample scripts that you can use, try these[^].

Never underestimate the power of human stupidity
RAH

Re: Giving access permissions to user webadminqa_iispool

Richard Deeming7-Dec-15 1:34

Richard Deeming

7-Dec-15 1:34

indian143 wrote:
... give user the roles to access the application db_owner ...

That's a very bad idea. The user should only have the permissions required by your application.

I assume your application doesn't need to modify the structure of tables, views, stored procedures, etc.? And it doesn't need to modify the users and permissions? So why give it those permissions?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

How to manage 1000 millions of record in Mysql database

Member 120698693-Dec-15 20:28

Member 12069869

3-Dec-15 20:28

I want to manage 10 major tables with 100 millions record each, each row have about 20 fields/colums, so how to manage these 10 interlinked tables.
Can MySql handle such large data?

Re: How to manage 1000 millions of record in Mysql database

Richard MacCutchan3-Dec-15 22:44

Richard MacCutchan

3-Dec-15 22:44

See http://tagide.com/blog/advice/how-to-handle-large-data-in-mysql/[^].

Re: How to manage 1000 millions of record in Mysql database

jschell4-Dec-15 12:39

jschell

4-Dec-15 12:39

Member 12069869 wrote:
Can MySql handle such large data?

Yes. Of course that doesn't mean your application can. Nor that you can't mess it up by failing to design correctly.

Member 12069869 wrote:
each row have about 20 fields/colums

Things that matter
- What is the maximum, average and variance for the row size (your data not db overhead.)
- What is the realistic growth rate? Realistic means that, not pie in the sky sales/marketing claims.
- What will the system be querying on most? Unless your growth rate is very big this is more relevant.
- Can data be aged? Thus will the above queries be mostly for the last month, or last three months versus spanning the entire data store

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.