|
I have done something similar. My solution is to generate the Primary Key using a SP.
CREATE TABLE dbo.Sequence
(
TableName varchar(30) NOT NULL,
NextVal int NOT NULL
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.Sequence') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.Sequence >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.Sequence >>>'
go
CREATE PROCEDURE GetSequence (
@tbl varchar(30))
AS
DECLARE @nextval int
BEGIN
BEGIN TRAN
UPDATE Sequence
SET NextVal = NextVal + 1
WHERE TableName = @tbl
IF @@rowcount = 1
SELECT @nextval = NextVal
FROM Sequence
WHERE TableName = @tbl
COMMIT TRAN
SELECT @nextval
END
Hope this helps.
|
|
|
|
|
Thank you for the reply.
At this shop we don’t like using SP’s. The best reason I can come up with is that the boss simply doesn’t trust MS enough to let SQL2k handle such things. But that is a cool bit of code thanks.
We have come up with this solution on the software side. We will no longer care if there is a multiple or Phantom read from the key table, the function will take what ever key it gets; however, on the insert, if a phantom read has occurred, we will catch the Duplicate PK error generated by sql and go back and grab another new Key. I know that I specified that we needed a serial key but this solution will be nearly 100% serial and for the times that the sequins is broken there will only be, statistically, one or two missed. And that won’t hurt us. The reason we need serial is to make DB maintenance easer.
Simply put one error try again.
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
Sounds like you also don't trust SQL2k to generate the PK as an identity either, if you did the simple solution would be to make the PK field an Integer identity type with an increment of 1...eliminating the need for the PK table and the round trip to get the pk.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Agreed. I dont trust SQL2k to generate the PK as an identity. I have seen in some situations when the Server crashes enormous identity gaps are created. The only solution in this case is to BCP out the data, drop and recreate the table abd then BCP in. It has happened twice with me. So I dont use identity columns anymore.
Live Life King Size
Alomgir Miah
|
|
|
|
|
Any recommendations on Data Modeling software that is open source/free?
<signature>
It's good to be alive,
Josef Wainz
Programmer Analyst
|
|
|
|
|
Can anyone tell me how to insert NULL value to visual foxpro table with
DataSet? Have posted this question for a long time without an answer. 
Urgent..
Mei.
|
|
|
|
|
Are you saying that DBNull.Value does not work?
|
|
|
|
|
Yes, I tried DBNull.Value, SqlDateTime.Null,"",NULL," "....
All of them failed.
|
|
|
|
|
Are you sure the field is nullable?
|
|
|
|
|
I think the field is nullable
as I can leave it intact (blank) without problems while
inserting values to other fields in the same row
when opening it with Access .
It seems you got loads of experience.
Is that possible to talk to you of
this problem on phone?
Mei.
0044 792 531 8977
|
|
|
|
|
I am trying to bulk insert data from a text file to a temp table. The bulk insert is unable to recognise the linefedd character and tries to insert complete data in one column.
Can anyone help me solve this problem.
|
|
|
|
|
Try and alter the line break from {CR}{LF} to {CR} or {LR}. Some Unix files can have strange formating for new carriage returns.
Failing that if you can use anything as row delimiter such as |||@@@|||, as long as you can alter the orginal text file.
|
|
|
|
|
Thanks for ths reply, but none of them worked. It works when downloaded through command prompt ftp command, but not when downloaded using our ftp code written in VB.net.
|
|
|
|
|
Hi,
I wonder which is better using direct sql statments directly in the code or writing stored procedures inside the SQL Server database? What are the advantages and disadvantages of each?
Thank you
|
|
|
|
|
stored procedures are compiled and operated much faster than free line code.
Store procedures are also more secure
Store procedure also let you modify the back end business logic without effecting your web or client applicated.
Store procedures can be unit tested as a seperate business logic.
Store procedure can return scaler values, tables , or action as action code.
Store procedures can have error trapping and rollbacks if multiple tables need to be updates.
Don't use free line SQL unless you can avoid it.
|
|
|
|
|
Stored Procedures are better in my opinion.
Here are a couple of reasons:
SPs mean that you can revoke the permissions on direct table access pushing everything through SPs. This means the only actions that an application can perform are only the actions the SPs permit.
SPs can do additional checking of the data. This allows the database to weed out potentially dangerous data as part of a layered security approach. e.g. The application weeds out potentially harmful data first, but if that security layer should be compromised somehow the database itself has a layers of security to prevent dangerous actions - this includes logic within the SPs
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Show him your article colin. 
<italic>Work hard, Work effectively.
|
|
|
|
|
Hello everyone,How do i use c# control sqlserver backup and restore? Where side this knowledge can be found? Can I find it in msdn? Tell me! Thank you!
|
|
|
|
|
There are SQL commands to BACKUP[^] and RESTORE[^] databases. You can find them in MSDN quite easily.
Next, all you need to do is create a string with the appropriate command in it and use SqlCommand. Like this:
SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand(myCommandString, myConnection);
myCommand.CommandTimeout = 3600;
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
It is as easy as that. You need to fill in the connection string and the command string with the appropriate values. If your backup or restore is likely to take more than one hour you might want to change the CommandTimeout as well.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thats a good solution. But if you want you can use SQL DMO to achieve this.
Just add a reference to sqldmo.dll and make method calls on it to backup and restore.
Public Function DB_Backup(ByVal nServer_Name As String, _
ByVal nDB_Name As String, _
ByVal nDB_Login As String, ByVal nDB_Password As String, _
ByVal nBack_Dev As String, ByVal nBack_Set As String, _
ByVal nBack_Desc As String) As Boolean
' nServer_Name = SQL server name
' nDB_Name = Database name
' nDB_Login = Login name
' nDB_Password = Password
' nBack_Dev =Backup device name
' nBack_Set = Backup set name
' nBack_Desc = Backup discription
Dim oBackup As SQLDMO.Backup
On Error GoTo ErrorHandler
Set oBackup = CreateObject("SQLDMO.Backup")
If Connect_SQLDB(nServer_Name, nDB_Login, nDB_Password) Then
oBackup.Devices = "[" & nBack_Dev & "]"
oBackup.Database = nDB_Name
oBackup.BackupSetName = nBack_Set
oBackup.BackupSetDescription = nBack_Desc
oBackup.SQLBackup oSQLServer
oSQLServer.DisConnect
DB_Backup = True
End If
Exit Function
ErrorHandler:
DB_Backup = False
End Function
Private Function Connect_SQLDB(ByVal nServer_Name As String, _
ByVal nDB_Login As String, _
ByVal nDB_Password As String) As Boolean
' nServer_Name = SQL server name
' nDB_Login = Login name
' nDB_Password = Password
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
On Error GoTo ErrorHandler
Connect_SQLDB = False
oSQLServer.Connect nServer_Name, nDB_Login, nDB_Password
Connect_SQLDB = True
Exit Function
ErrorHandler:
oSQLServer.DisConnect
Connect_SQLDB = False
End Function
Also check this
http://www.codersource.net/csharp_sqldmo_sqlserver.aspx
|
|
|
|
|
Also
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo\unzip_sqldmo.exe
unzip archive and go to
E:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo\vb\BackupDevice
or
E:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo\vb\VerifyBackup
VB6,C#
|
|
|
|
|
My Environment: MySQL Database, ADO, MSVC6, MS DataGrid.
Datagrid is being used to display data from three tables, joined by their primary keys.
Problem: User wants to be able to edit the data in the grid itself. When he does, and tried to move to another record, the grid displays the message "Insufficient Key Column Information for updating or refreshing". User is not willing to have me bring up the row data in a dialog to edit.
Even Bigger Problem: I need to keep this user happy!
Schema:
Table_1: ID (AutoNumber, PK), Field_A (Varchar), Field_B (Varchar)
Table_2: ID (Autonumber, PK), Field_C (Varchar), Field_D (Integer)
Table_3: ID (Autonumber, PK), Field_E (Double)
SQL for Grid: SELECT Table_1.ID, Table_1.Field_A, Table_2.Field_C, Table_3.Field_E FROM (Table_1 INNER JOIN Table_2 ON Table_1.ID=Table_2.ID) INNER JOIN Table_3 ON Table_1.ID=Table_3.ID ORDER BY Table_1.Field_A
MS KB: There is no duplication of field names except ID. ID is always the primary key. The ID field is not being updated. Field_A is.
ADO: I have tried using both adUseClient and adUseServer as the cursor location, for both the connection object and the recordset object. Has not made any difference.
Can I set this up so that enough key column information is available for the update? Is this even possible?
|
|
|
|
|
From MSDN:
When updating an ActiveX Data Objects (ADO) recordset, an error occurs if the recordset, based on a SHAPE command, joins two tables where the primary key of one table has the same name as a column name of the other table. The following error appears:
Run-time error '-2147467259 (80004005)':
Insufficient key column information for updating or refreshing.
You can try renaming the primary keys.
|
|
|
|
|
Actually, I had tried that already, it did not work. I am beginning to think this is something so deep inside Microsoft Cursor engine, that there may not be an easy work-around. The SQL I used was not a SHAPE sql anyway.
My solution so far has been to switch to a disconnected recordset, and intercept update notifications from the grid. Then I have to write some code for a Lazy Writer to actually update the database with the changes from the grid.
|
|
|
|
|
I have a Access database that I wrote a VB.net program for. I want to set it up so I can seach it using the indexof method. I gotten as far as the listbox,inputbox and the button, but I'm pretty much running around in circles with the code. Anyone know how to do this?
BINARY
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
