Recently Updated To Sql 2005 From Sql 2000 Now Recieving Locking Problems
Apr 24, 2008
Good afternoon everyone,
I hope that I am not asking a question which has been answered many times in the past, I did a search but could not find an answer. These may be beginner questions, so bear with me as I am something of a beginner.
This week our organization updated from SQL 2000 to SQL 2005. We are using a Microsoft Access front end for this data and the information is entered through forms into the correct tables. When our researchers are enter the information they also have to sometimes search existing information to ensure they do not duplicate what is already in there. For this we use several access queries. This has worked well on the SQL 2000 server without any issues. However, now that we are on 2005 we are starting to recieve ODBC Call fail errors and errors which specify that they are a result of a deadlock. My first question is, are the ODBC call fail errors possibly locks which caused the ODBC connection to time out? My second question is why am I recieving these errors in 2005 but not 2000?
Thanks in advance for any help
- Hitz
View 1 Replies
ADVERTISEMENT
Sep 30, 2004
Hello all,
I am trying to change values of two tables in my sql server 2000 database. When one of the tables is modified in some way like adding/updating a record, I need a trigger procedure to copy this new data from the first table to the second. Problem is, how do I get the newly inserted or updated data from the first table? How do I specify that I only want the data which caused the trigger to execute? Anyone know?
Thanks,
Bob
View 1 Replies
View Related
Jul 20, 2005
Hello,I am having trouble installing Evaluation copy of SQL Server on mynotebook, which has XP, P4 processor,1G Ram, and 14 G harddisk space.After I run the Setup.exe, I get the files files extracted in theSQLEVAL directory.As per the instruction, I need to run Autorun.exe file. For somereason, when I try to run this file, a new windows pops up, which isblank black screen with cursor flying around in the window. The titleof the window isC:windowssystem32wowexec.exeThis is how far I have reached while trying to install the database. Iwould really appreciate if someone could provide some suggestions.VS
View 2 Replies
View Related
Jan 3, 2007
SQL 2000 to SQL 2005 Upgrade Error - Database Down - Help Appreciated
I am upgrading a SQL 2000 standard database server to SQL 2005 standard on a windows 2003 server
I am logged in as domain admin and started the upgrade as 'sa'
The upgrade stops with the error:
Service MSSQLSERVICE can not be started. Verify you have sufficient privilages to start system services. The error code is (17185)
The error log shows:
2007-01-04 15:59:38.77 Server Authentication mode is MIXED.
2007-01-04 15:59:38.79 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2007-01-04 15:59:38.79 Server (c) 2005 Microsoft Corporation.
2007-01-04 15:59:38.79 Server All rights reserved.
2007-01-04 15:59:38.79 Server Server process ID is 4188.
2007-01-04 15:59:38.79 Server Logging SQL Server messages in file 'F:SQLDataMSSQLlogERRORLOG'.
2007-01-04 15:59:38.79 Server This instance of SQL Server last reported using a process ID of 2980 at 1/4/2007 3:56:58 PM (local) 1/4/2007 2:56:58 AM (UTC). This is an informational message only; no user action is required.
2007-01-04 15:59:38.79 Server Registry startup parameters:
2007-01-04 15:59:38.79 Server -d F:SQLDataMSSQLdatamaster.mdf
2007-01-04 15:59:38.79 Server -e F:SQLDataMSSQLlogERRORLOG
2007-01-04 15:59:38.79 Server -l F:SQLDataMSSQLdatamastlog.ldf
2007-01-04 15:59:38.79 Server Command Line Startup Parameters:
2007-01-04 15:59:38.79 Server -s MSSQLSERVER
2007-01-04 15:59:38.79 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-01-04 15:59:38.79 Server Detected 4 CPUs. This is an informational message; no user action is required.
2007-01-04 15:59:38.83 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2007-01-04 15:59:39.02 Server Using the static lock allocation specified in the locks configuration option. Allocated 20000 Lock blocks and 20000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-01-04 15:59:39.02 Server Multinode configuration: node 0: CPU mask: 0x0000000a Active CPU mask: 0x0000000a. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2007-01-04 15:59:39.04 Server Multinode configuration: node 1: CPU mask: 0x00000005 Active CPU mask: 0x00000005. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2007-01-04 15:59:39.04 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-01-04 15:59:41.04 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-01-04 15:59:41.04 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-01-04 15:59:41.04 spid7s Starting up database 'master'.
2007-01-04 15:59:41.05 spid7s 1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2007-01-04 15:59:41.07 spid7s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2007-01-04 15:59:41.07 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-01-04 15:59:41.08 spid7s SQL Trace ID 1 was started by login "sa".
2007-01-04 15:59:41.08 spid7s Starting up database 'mssqlsystemresource'.
2007-01-04 15:59:41.11 spid7s Using 'dbghelp.dll' version '4.0.5'
2007-01-04 15:59:41.11 spid7s ***Stack Dump being sent to F:SQLDataMSSQLLOGSQLDump0035.txt
2007-01-04 15:59:41.11 spid7s SqlDumpExceptionHandler: Process 7 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2007-01-04 15:59:41.11 spid7s * *******************************************************************************
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * BEGIN STACK DUMP:
2007-01-04 15:59:41.11 spid7s * 01/04/07 15:59:41 spid 7
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * Exception Address = 78144D3A Module(MSVCR80+00014D3A)
2007-01-04 15:59:41.11 spid7s * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2007-01-04 15:59:41.11 spid7s * Access Violation occurred reading address 0000001E
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * MODULE BASE END SIZE
2007-01-04 15:59:41.11 spid7s * sqlservr 01000000 02BA7FFF 01ba8000
2007-01-04 15:59:41.11 spid7s * ntdll 7C800000 7C8BFFFF 000c0000
2007-01-04 15:59:41.11 spid7s * kernel32 77E40000 77F41FFF 00102000
2007-01-04 15:59:41.11 spid7s * MSVCR80 78130000 781CAFFF 0009b000
2007-01-04 15:59:41.11 spid7s * msvcrt 77BA0000 77BF9FFF 0005a000
2007-01-04 15:59:41.11 spid7s * MSVCP80 7C420000 7C4A6FFF 00087000
2007-01-04 15:59:41.11 spid7s * ADVAPI32 77F50000 77FEBFFF 0009c000
2007-01-04 15:59:41.11 spid7s * RPCRT4 77C50000 77CEEFFF 0009f000
2007-01-04 15:59:41.11 spid7s * USER32 77380000 77411FFF 00092000
2007-01-04 15:59:41.11 spid7s * GDI32 77C00000 77C47FFF 00048000
2007-01-04 15:59:41.11 spid7s * CRYPT32 761B0000 76242FFF 00093000
2007-01-04 15:59:41.11 spid7s * MSASN1 76190000 761A1FFF 00012000
2007-01-04 15:59:41.11 spid7s * Secur32 76F50000 76F62FFF 00013000
2007-01-04 15:59:41.11 spid7s * MSWSOCK 71B20000 71B60FFF 00041000
2007-01-04 15:59:41.11 spid7s * WS2_32 71C00000 71C16FFF 00017000
2007-01-04 15:59:41.11 spid7s * WS2HELP 71BF0000 71BF7FFF 00008000
2007-01-04 15:59:41.11 spid7s * USERENV 76920000 769E3FFF 000c4000
2007-01-04 15:59:41.11 spid7s * opends60 333E0000 333E6FFF 00007000
2007-01-04 15:59:41.11 spid7s * NETAPI32 71C40000 71C97FFF 00058000
2007-01-04 15:59:41.11 spid7s * SHELL32 7C8D0000 7D0D2FFF 00803000
2007-01-04 15:59:41.11 spid7s * SHLWAPI 77DA0000 77DF1FFF 00052000
2007-01-04 15:59:41.11 spid7s * comctl32 77420000 77522FFF 00103000
2007-01-04 15:59:41.11 spid7s * psapi 76B70000 76B7AFFF 0000b000
2007-01-04 15:59:41.11 spid7s * instapi 48060000 48069FFF 0000a000
2007-01-04 15:59:41.11 spid7s * sqlevn70 4F610000 4F7A0FFF 00191000
2007-01-04 15:59:41.11 spid7s * SQLOS 344D0000 344D4FFF 00005000
2007-01-04 15:59:41.11 spid7s * rsaenh 68000000 6802EFFF 0002f000
2007-01-04 15:59:41.11 spid7s * AUTHZ 76C40000 76C53FFF 00014000
2007-01-04 15:59:41.11 spid7s * MSCOREE 61FB0000 61FF4FFF 00045000
2007-01-04 15:59:41.11 spid7s * ole32 77670000 777A3FFF 00134000
2007-01-04 15:59:41.11 spid7s * msv1_0 76C90000 76CB6FFF 00027000
2007-01-04 15:59:41.11 spid7s * iphlpapi 76CF0000 76D09FFF 0001a000
2007-01-04 15:59:41.11 spid7s * kerberos 62220000 62277FFF 00058000
2007-01-04 15:59:41.11 spid7s * cryptdll 766E0000 766EBFFF 0000c000
2007-01-04 15:59:41.11 spid7s * schannel 76750000 76776FFF 00027000
2007-01-04 15:59:41.11 spid7s * COMRES 77010000 770D5FFF 000c6000
2007-01-04 15:59:41.11 spid7s * XOLEHLP 622E0000 622E5FFF 00006000
2007-01-04 15:59:41.11 spid7s * MSDTCPRX 622F0000 62367FFF 00078000
2007-01-04 15:59:41.11 spid7s * msvcp60 780C0000 78120FFF 00061000
2007-01-04 15:59:41.11 spid7s * MTXCLU 62370000 62388FFF 00019000
2007-01-04 15:59:41.11 spid7s * VERSION 77B90000 77B97FFF 00008000
2007-01-04 15:59:41.11 spid7s * WSOCK32 71BB0000 71BB8FFF 00009000
2007-01-04 15:59:41.11 spid7s * OLEAUT32 77D00000 77D8BFFF 0008c000
2007-01-04 15:59:41.11 spid7s * CLUSAPI 62390000 623A1FFF 00012000
2007-01-04 15:59:41.11 spid7s * RESUTILS 623B0000 623C2FFF 00013000
2007-01-04 15:59:41.11 spid7s * DNSAPI 76ED0000 76EF8FFF 00029000
2007-01-04 15:59:41.11 spid7s * winrnr 76F70000 76F76FFF 00007000
2007-01-04 15:59:41.11 spid7s * WLDAP32 76F10000 76F3DFFF 0002e000
2007-01-04 15:59:41.11 spid7s * rasadhlp 76F80000 76F84FFF 00005000
2007-01-04 15:59:41.11 spid7s * security 62800000 62803FFF 00004000
2007-01-04 15:59:41.11 spid7s * dbghelp 671B0000 672C7FFF 00118000
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * Edi: 62FA0040: 62FA0028 671AFFF0 00000000 00000000 00000000 00000000
2007-01-04 15:59:41.11 spid7s * Esi: 0000001E:
2007-01-04 15:59:41.11 spid7s * Eax: 0416C05E: C0400096 C0400416 00000416 000062FA 00000000 00000000
2007-01-04 15:59:41.11 spid7s * Ebx: 0416C040: 0105F270 00002000 00000005 00000000 3368C1E0 00000000
2007-01-04 15:59:41.11 spid7s * Ecx: 0105B010: 458B0046 01F88348 00D3850F 458B0000 89C23B50 0F342444
2007-01-04 15:59:41.11 spid7s * Edx: 00000000:
2007-01-04 15:59:41.11 spid7s * Eip: 78144D3A: 24FFA5F3 144E5495 C78B9078 000003BA 04E98300 E0830C72
2007-01-04 15:59:41.11 spid7s * Ebp: 33D6E770: 33D6E790 01C65B97 62FA0040 0000001E 0416C040 0416C6DC
2007-01-04 15:59:41.11 spid7s * SegCs: 0000001B:
2007-01-04 15:59:41.11 spid7s * EFlags: 00010212: 0020006D 00690046 0065006C 005C0073 006F0043 006D006D
2007-01-04 15:59:41.11 spid7s * Esp: 33D6E768: 62FA0040 33D6E7A4 33D6E790 01C65B97 62FA0040 0000001E
2007-01-04 15:59:41.11 spid7s * SegSs: 00000023:
2007-01-04 15:59:41.11 spid7s * *******************************************************************************
2007-01-04 15:59:41.11 spid7s * -------------------------------------------------------------------------------
2007-01-04 15:59:41.11 spid7s * Short Stack Dump
2007-01-04 15:59:41.13 spid7s 78144D3A Module(MSVCR80+00014D3A)
2007-01-04 15:59:41.13 spid7s 01C65B97 Module(sqlservr+00C65B97)
2007-01-04 15:59:41.13 spid7s 01CA43B5 Module(sqlservr+00CA43B5)
2007-01-04 15:59:41.13 spid7s 01CA452E Module(sqlservr+00CA452E)
2007-01-04 15:59:41.13 spid7s 0217D3BD Module(sqlservr+0117D3BD)
2007-01-04 15:59:41.13 spid7s 0217B896 Module(sqlservr+0117B896)
2007-01-04 15:59:41.13 spid7s 0100889F Module(sqlservr+0000889F)
2007-01-04 15:59:41.13 spid7s 010089C5 Module(sqlservr+000089C5)
2007-01-04 15:59:41.13 spid7s 010086E7 Module(sqlservr+000086E7)
2007-01-04 15:59:41.13 spid7s 010D764A Module(sqlservr+000D764A)
2007-01-04 15:59:41.13 spid7s 010D7B71 Module(sqlservr+000D7B71)
2007-01-04 15:59:41.13 spid7s 010D746E Module(sqlservr+000D746E)
2007-01-04 15:59:41.13 spid7s 010D83F0 Module(sqlservr+000D83F0)
2007-01-04 15:59:41.13 spid7s 781329AA Module(MSVCR80+000029AA)
2007-01-04 15:59:41.13 spid7s 78132A36 Module(MSVCR80+00002A36)
2007-01-04 15:59:41.13 spid7s Stack Signature for the dump is 0x2BEDE9E0
2007-01-04 15:59:41.27 spid7s External dump process return code 0x20000001.
External dump process returned no errors.
2007-01-04 15:59:41.27 spid7s Error: 17185, Severity: 16, State: 1.
2007-01-04 15:59:41.27 spid7s Unable to update password policy.
2007-01-04 15:59:41.27 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Any clues?
Cheers
Paul
View 3 Replies
View Related
Aug 20, 2007
We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.
View 1 Replies
View Related
Jun 15, 2001
Hi,
is it possible from SQL to inform notification of trigger execution in VB using DMO ( or some other method ? )
Any help appreciated.
Thanks
View 1 Replies
View Related
Feb 16, 2007
I'm beginner...
I need recieve e-mail by Service broker.
What can I do it?
(example if possible).
Thanks!
View 4 Replies
View Related
Dec 11, 2000
Does anyone know if there is an updated Books Online for 2000 published anywhere?? Or even a more recent copy of the 7.0? MS has probably made corrections
to them.
Also, I'm looking for all the free info about DTS for 2000. Not anything that MS publishes, more of a third party evaluation.
Thanks in advance,
Adam
View 1 Replies
View Related
Jul 20, 2005
Hi,We have encountered deadlock on a table which is used to generatesequential numbers for different categories eg typical entriesCategory ValueTRADE_NO 1456JOB_NO 267.....The applications reference the relevant category applicable to themand updatethe Value accordingly. This is table is very small, occupying 1 page.However, it has no index as it was not seen to be appropriate for atable this size.However, can someone please advise whether1. An index is required for row level locking2. If an index on a table as small as above is likely to reduce thedeadlock rate.Also, please consider the following but which I am not sure isrelevant for above query.We noted that when we migrated the database concerned from SQL 6.5 toSQL 2000, using DTS, that the database was NOT strictly in SQL 2000format for non clustered indexes (NC) ie the clustered key was notpart of the NC index until the clustered index was rebuilt.Given this should I just rebuild this table with a fake index and dropit thereafter.We are aware of the different techniques used to avoid deadlocks (egtables accessed in same order etc) and have , as much as possible,implemented those practices.I thank you in advance for any help you may be able to offer.ThanksPuvendran
View 12 Replies
View Related
Jul 15, 2003
Insert or update statements seems to be locking entire page or table rather than locking
the corresponding row to be inserted or updated.
lets assume the table with 3 rows.
scenario 1)
In transaction A, I'm updating the 3'rd row. and in another transaction B I'm reading
row 1.
Transaction B seems to be waiting for transaction A to finish before returning the select results.
Scenario 2)
In transaction A, I'm inserting new row (4'th row) and in another transaction B I'm reading
row 1.
here as well trasaction B does not return the row 1 unless transaction A is complete.
Select operation is blocked due to insert.
Ideally in both the scenarios , read operation should have returned the results without waiting
for update/insert to finish. As the read is being done on different rows than that of being updated
or inserted.
I have tried both the insert/update as well as select queries with all the possible locking hints
such as ROWLOCK, READCOMMITED, UPDLOCK etc...
The only way select query returns the row without blocking is by using the NOLOCK locking hint. But then this is
not the proper solution as it gives us the dirty read.
Please suggest me any solution or workaround for above issue.
View 1 Replies
View Related
Oct 30, 2007
Hello all,
Hope all of us know that DST Patch update will be on 4th November,07.
Here my query is how to retrieve the DST patch updated date in sqlserver 2000?
any queries on this?
Would any one help me on this?
Thanks in advance,
View 6 Replies
View Related
May 14, 2002
Hi, I wonder if anyone can shed some light on this issue -
We have a client/server program for MSSQL7 and MSSQL2000.
When running client/server - we have our own broker, etc,
the program ends up locking itself on SQL2000; this is not a problem on SQL7.
We noticed that it seems some system tables locking tempdb.
We also noticed that SQL2000 no longer has insert-into/bulkcopy option like SQL7. But, here we don't have temporary tables at all. It's just that we found the lockings in tempdb caused by some sysobjects, sysindexes, etc.
Also, this does not happen to all the systems we have. Some of our machine does not have the self-lock problem; most of the machines have this problem though.
If we don't run client/server, then the program works fine.
Same logic, the difference is when we use client/server logic to connect to the remote SQLServer 2000 server, the locking might happen on most of our machines.
Anyone is familiar with tempdb and locking issues, please provide some hint??? Is it possible that there are some system parameters that we can set to avoid the problem. We haven't found out why some of our machines do not have problem, while most of the machines have this locking problem.
The program is written using ODBC.
Many thanks in advance.
HIBA International
View 2 Replies
View Related
Nov 28, 2005
We are running a 3rd party ETL tool to populate a denormailized version of a production database for reports. Everything works fine 95% of the time. However there is a semi-rare occurence of the ETL tool hanging up. The norm is for the tool to take about 5 times longer than usual, but it still works. Over the weekend however it through an error saying:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
The reports are run through Crystal using stored procs and are all basically select statements
So my question(s) are the following:
1. What kind of lock would a report put on a table (select statement)
2. Would it make sense to change stored procs to use WITH NOLOCK?
3. Or is something else going on?
Your thoughts would be greatly appreciated.
View 5 Replies
View Related
Jul 20, 2005
HiIs it possible to force row level locking in one or more tables insome database. We have some problems when SQL Server decides to choosepage- or table-level locking.We are using SQL Server 2000.Best regardsAarno
View 1 Replies
View Related
Jan 2, 2008
Hi, I wonder where I can see last update over a table. Maybe sqlserver write some information about every table.
For example there are 300 tables in a database an I want to execute some query to see the last tables updated (list of tables updated > 2008-01-02 )
Hope to be clear,
Best regards
Ariel
View 4 Replies
View Related
Mar 10, 2008
Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
View 1 Replies
View Related
Mar 8, 2006
In a multi-user environment, I would like to get a list of Idsgenerated, similar to:declare @LastId intselect @LastId = Max(Id) From TableManiaINSERT INTO TableMania (ColumnA, ColumnB)SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15--get entries just addedSELECT * FROM TableMania WHERE Id > @LastIdThe above works fine, except I'm assuming it will not work in amulti-user environment. Is there any way to get the set of Ids thatwere just added in the previous statement (similar to @@IDENTITY)without doing all of this in a serializable transaction or making atemp table of every single Id before the insert statement?
View 41 Replies
View Related
May 29, 2008
currently i m developing a web application with using sql server 2005
and i was testing yesterday a sql update query with sql server management studio.
in my update query i forgot to put where condition and now all the rows of table are updated. is there any solution to undo this and retrieve all rows back?
Regards Selena
View 6 Replies
View Related
Jan 30, 2008
Hi,
I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem?
Br,
Riika
View 6 Replies
View Related
Jan 29, 2008
Hi pardon my ignorance but I wonder if someone could answer a few questions for me.
I am writing a program which will be used by perhaps upto 100 users at a time. The program sits on any number of PCs and loads user specific data to a given PC according to who has logged on to windows on that PC.
A number of data items loaded from the user table have to be unique as they are usernames for other systems that my program simplifies access to.
So when a user logs on to my program for the first time a row is created for them in the user table (indexed by a GUID and their unique network name). The other unique fields are left blank and the user is given an opportunity to fill these details in.
Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.
The problem of course is that if between the program downloading the user table into a local datatable, checking the values are unique and then actually writing them someone else writes the same data into their row then 2 users end up with the same data - which shouldn't be allowed. i.e. 2 users can't have the same user name for the other software.
How can I solve this problem with locking? Once the user table is downloaded and in a locel datatable presumably the table is no longer locked so another user could write data to the table.
I acutally think this is going to be a pretty rare occourance but I still want to try to cover all eventualities.
I suspect the problem is the way my program is going about the checking.
Should I use an SQL insert statement like??
If exists(SELECT username from users where username=@username)
BEGIN
RAISEERROR("Username already exists")
END
ELSE
BEGIN
INSERT etc
If so I guess this will simplify my code. Is this the correct thing to do? And then just trap the errors that arise if a duplicate does arise?
Also some more general questions.
1)I presume 2 users simultaneously looking up data from 2 different rows in a table doesn't lock the table so one search fails? I use the code below having set up a command to run a stored procedure to search for a user by their network name.
Dim lclRowRet As SqlDataReader
lclRowRet = LoadUserCommand.ExecuteReader(CommandBehavior.SingleRow)
lclRowRet.Read()
2) I presume writing data to my user table a row at a time will also not cause a lock. I create a command object with all the row values in and then do a command.executenonquery()
As a rule I close all my connections as soon as I'm done with them.
Many thanks for your help in advance.
nik
View 5 Replies
View Related
Sep 20, 2007
I've got a big problem that I'm trying to figure out:I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.I don't need this for an individual ID select, I need it applied to every record from the table.My address table has some columns that look like:
[AddressID] [int][LocationID] [int][Type] [nvarchar](10)[Address] [varchar](50)[City] [varchar](50)[State] [char](2)[Zip] [varchar](5)[AddDate] [datetime][EditDate] [datetime]AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.How can I do this efficiently with perfomance in mind???Thank you in advance for any and all replies...
View 5 Replies
View Related
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
View 2 Replies
View Related
Dec 3, 2013
I need a SQL query which lists out directory path from which the database has been restored recently ( i.e backup path ).....
View 2 Replies
View Related
Apr 6, 2008
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View 3 Replies
View Related
Dec 18, 2007
I have created a database and used visual basic 2005.net2.0 to build the application. I used the data wizard. it created datatables and then I bound it to the contols on the form. The program is published to the server and each instance loads from the server and executes. When it stops, the instance is destructed from the host pc. The pc's are large ram and very small hard drive. These were the specs of the job. now when each pc loads an instance of the program, they can all load and edit the same exact record. I tried to bust it and I got on the same record and changed first name on one and last name on the other. the second one overwrote the first name edit. All of the data handling is done inside of the dataset designer and it does not allow edits. ( actually you can edit, but the next time you save it, or run it, it will rewrite what you added and it will be gone.) is there anything I can add to the form vb to tell it to lock the current record?
View 3 Replies
View Related
Aug 18, 2006
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
View 1 Replies
View Related
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
View 2 Replies
View Related
Apr 22, 2008
Hello,
How do I clear the Recently Used Server list in SQL Server Management Studio? I am talking about the "Server Name" field that is part of the "Connect To Server" drop down when you initially open SQL Server Management Studio.
Thanks.
View 1 Replies
View Related
Jul 19, 2007
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
View 8 Replies
View Related
May 19, 2008
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies
View Related
Mar 13, 2006
Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
View 1 Replies
View Related
Apr 30, 2008
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
View 4 Replies
View Related
Dec 18, 2007
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
Thanks in advance.
View 3 Replies
View Related
