|
|
I have following query:
Insert table2(field2,field3,field4)
select field2,field3,field4 from table1
My field1 is identity. I wanted to insert data and increment identity by 1 for spesific field2 value.
I was able to do if I inserted by 1 record:
SET IDENTITY_INSERT field1 ON
DECLARE @rID int
SELECT @rID = max(field1) + 1 from table2 where field2 = @field2
insert referral_main (field2,field3,field4)
values (field2,field3,field4)
SET IDENTITY_INSERT field1 OFF
Please help me do for more then 1.
|
|
|
|
|
OK, what happens if I have already inserted the following:
field1 field2 field3 field4
------ ------ ------ ------
1 A B C
2 A D E
3 B C E and I try to run your query setting @field2 to 'A'? I already have a row where field1 is 3. If there's a primary key (or a UNIQUE constraint) on field1, the INSERT will fail.
Do not use IDENTITY in cases where you need to manipulate values like this. It's just a simple way to get a unique identifier for a row which otherwise cannot be identified uniquely - if it needs some higher meaning, you need to think more closely about your data.
Also, you need to think about locking for this query. What happens if two processes execute this procedure simultaneously? If the situation happens as follows:
Process 1 Process 2
--------- ---------
SELECT - @rID = 4
SELECT - @rID = 4
INSERT - success
INSERT - failureone of your inserts will fail. This is even more likely if running on a multiprocessing-capable server (either a server with multiple processors, or potentially on a server with hyperthreading enabled).
If you need to impose an ordering on results, it's better to use ORDER BY in a SELECT. You can always order by multiple columns - for example, ORDER BY field2, field1 to get results in the order of insertion. IIRC, IDENTITY will always add new rows with higher identifiers than are currently present in the table, so long as you haven't reseeded the column with DBCC CHECKIDENT.
|
|
|
|
|
Is there a way, without buying a third party tool, to monitor SQL Server programatically using stored procedure calls or even via straight ODBC/JDBC calls? 
I need to gain access to a SQL Server 2000 box's information such as how much a given database has grown, how much space is available for it to grow, etc., and I need to be able to do this from a web application.
If there is a way to do gain acces to this sort of information via a stored procedure or ODBC/JDBC, then I'm in business!
Thanks. 
Darrin
|
|
|
|
|
Lookup sp_helpdb in the SQL Server Books Online. It provides exactly what you are looking for.
OldFartRant: Is it really so long ago that the sp_help series of sprocs typed into an ISQL session were the primary means of ad-hoc administration? Kids and their GUI tools these days... when we were young we had to walk a mile up hill in the snow to school, both ways.
--
-Blake (com/bcdev/blake)
|
|
|
|
|
Thanks. I really appreciate it.
|
|
|
|
|
Is there a way to make DAO programs "transportable"? When I compile programs and move them to a machine other than the one where they were compiled, I often get a "DAO/DB" error.
How can I make my programs work with other versions of the JET engine?
Richard
|
|
|
|
|
(1) Search in MSDN for "Redistributing Microsoft Visual C++ 6.0 Applications". Read subitem "Redistributing Database Support Files".
(2) Search in MSDN for "AfxDaoInit 0x0601".
The topic is handled in the "Q+A: C++" section of the January 2000 issue of Microsoft Systems Journal.
|
|
|
|
|
Thanks for the response. You helped me a bunch!
Dick
|
|
|
|
|
I use the Express OLE DB Library for the database interaction.I open database connection by using above mentioned library and also close the database connection by close method of the library. But when I go to SQL Enterprise Manager, then there in process list it is showing those connections in sleeping state. It is a memory leak. All these connections remain in sleep state until we close the application in which we are using Express OLEDB Library. I also try this by making local objects of connection and record set and by deleting the pointer to these objects. I want to close these sleeping state connections while my application remains open to avoid this memory leak of sleeping state database connections.Is there any method to kill the sleeping database connections of SQL.I am thankful in advance if anyone help me to resolve this issue of SQL.
|
|
|
|
|
Many libraries support connection pooling - where the library maintains a pool of open connections, so that a new connection requested by the application opens quickly.
Consult your library's documentation for advice on disabling or managing the size of the connection pool.
|
|
|
|
|
Is there any way to kill sleeping database connections of SQL programmatically because library documentation,there is no way to manage the size of connection pool.
|
|
|
|
|
When I run a query in my program, I got this exception
"General network error. Check your network documentation."
After I tried it again and again, I found that sometimes it works, sometimes it has the exception.
My network is absolutely ok. I don't think there's a problem with network. I guess it's a bug or something in ADO.NET
Anyone knows why?
Thanks in advance.
|
|
|
|
|
|
hello all of u
me and some other guy r doing a RDBMS from scratch both for our personal use and for a competition. wanna know what kinda strategy is better for storing relvars . do they all have to be dobne in a single file as access does or do they have to be done like oracle . also that what kind of information to store abt the database
i mean meta data like users, queries, views, i/o requests, indices etc
i need feedback from experienced DBMS users
thnx
|
|
|
|
|
Your typical DBMS user hasn't got a clue how the database is implemented - that's the job of the DBMS 
I recommend Inside SQL Server 2000 by Kalen Delaney (MS Press) for some information on how a DBMS is implemented.
|
|
|
|
|
Hi
I have used this for the slq statem
SELCT FirtName, LastName, ID,From Coustomer WHERE FirstName LIKE '[A-Z]'
C#
private void button1_Click(object sender, System.EventArgs e)
{
oleDbConnection1.Open();
oleDbDataAdapter2.SelectCommand.Parameters["LastName"].Value = textBox1.Text;
dataSet11.Clear();
oleDbDataAdapter2.Fill(dataSet11)
oleDbConnection1.Close();
}
But when I run it gave a and error
can u help me anyone
Harrison Brock
|
|
|
|
|
You're not going to get anywhere with SQL unless you spell statements correctly.
When posting questions, you really need to copy and paste code accurately (ideally formatting with the <pre> tag), and report error messages exactly.
I can currently see at least four errors with your SELECT statement, which (assuming that the data schema is spelled correctly) should look more like
SELECT FirstName, LastName, ID
FROM Customer
WHERE FirstName LIKE '[A-Z]' Note spelling of SELECT, FirstName, Customer, and the removal of the comma before FROM.
|
|
|
|
|
Well, I am new to C++. For past some time I have been facing a problem.
I have a stored procedure residing on the IBM DB2 database v 7.2.
This stored procedure has one input parameter and two output parameters. All these three parameters are of type datetime.
Whenever I call SQLBindParameter function, the return code is -1. I am unable to figure out why.
Will be grateful to anyone who can show me the right path. Here is the code snippet:
-----------------------------------------------------------------------
SQLHANDLE hStmt = NULL;
DATE_STRUCT dsCurDate,dsIPEffDate,dsPrExpDate;
SQLINTEGER cbDate = 0;
if(!OpenConnection(MakeConnectionString())) return FALSE;
SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_STMT,m_pDatabase->m_hdbc,&hStmt);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
return FALSE;
}
ret = SQLPrepare(hStmt,(SQLCHAR*)"CALL BSCPROC.PCI040(?,?,?)",SQL_NTS);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//Input parameter
ret=
SQLBindParameter(hStmt , //Statement handle
1 , //Parameter number
SQL_PARAM_INPUT, //Parameter direction
SQL_C_TYPE_DATE, //SQL C-datatype
SQL_TYPE_DATE , //SQL datatype
0 , //size
0 , //Decimal digits
&dsCurDate , //The input data
0 , //Input data length
&cbDate);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//First output parameter
ret=
SQLBindParameter(hStmt , //Statement handle
2 , //Parameter number
SQL_PARAM_OUTPUT , //Parameter direction
SQL_C_TYPE_DATE , //SQL C-datatype
SQL_TYPE_DATE , //SQL datatype
0 , //size
0 , //Decimal digits
&dsIPEffDate , //The input data
0 , //Input data length
&cbDate);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//Second output parameter
ret=
SQLBindParameter(hStmt , //Statement handle
3 , //Parameter number
SQL_PARAM_OUTPUT , //Parameter direction
SQL_C_TYPE_DATE , //SQL C-datatype
SQL_TYPE_DATE , //SQL datatype
0 , //size
0 , //Decimal digits
&dsPrExpDate , //The input data
0 , //Input data length
&cbDate);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//Specify input parameter
dsCurDate.year = 2003;
dsCurDate.month= 10;
dsCurDate.day = 5;
ret = SQLExecute(hStmt);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
CloseConnection();
return TRUE;
|
|
|
|
|
i'm buying a book in order to learned sql server, my first choice is "SYBEX mastering SQL server 2000", can anyone give his/her comments regarding this book, and may i also ask for other book titles?! thank you;P 
nevhile.net
|
|
|
|
|
|
|
The following is the code written in win32 console application using ADO
in C++.
on execution i get the error as COleSafeArray is an undeclared identifier....
is there any need to incluide any headre files for this??
when i tried including AFXDISP.H...it says windows.h is already included and mfc can't include windows.h again.
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 2);
// Fill the fields name now
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("Name")) );
lArrayIndex[0] = 1;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("HTML")) );
// Create an array of values to be added
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, );
//Fill the values in each field
lArrayIndex[0] = 0;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("RK")) );
lArrayIndex[0] = 1;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("myHTML")) );
// call Addnew of recordset now
pRecordset->AddNew( vaFieldList, vaValueArray );
pRecordset->Close();
ranjani
|
|
|
|
|
This is more of a C++ problem.
If you're not currently using MFC, adding it in only to support COleSafeArray is very much a sledgehammer/walnut combination. However, if you must do it, it's best to put the MFC headers into the precompiled header file (probably called StdAfx.h).
MFC is very dependent on the order in which its own and the Windows headers are included, so you must remove any #include <windows.h> line, or at least ensure that #include <afx.h> appears before it.
Alternatively, you could use SAFEARRAY directly, using SafeArrayCreate or SafeArrayCreateVector, or try searching for a different wrapper class. ATL 7.x has CComSafeArray, which is a template class.
|
|
|
|
|
i'm thinking of learning sql server, but i'm afraid that my efforts of learning sql server 2000 will be wasted when yukon is released. Will there be a massive change from 2000 to yukon?
nevhile.net
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
