|
sujithkumarsl wrote: several SELECT as well as INSERT operation are running at same time
So there may be blocking on the table anyway. The only way to really find out is to test under real life load.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
In SQL Studio Manager, under the Query menu, choose the option, "Display Estimated Execution Plan" while testing your Select / Insert logic to verify that the indexes you have created are actually being used.
If an index is not being used, you may want to recosider whether or not you really need it.
david
|
|
|
|
|
Hello,
may somebody here had asked it before, but i didn't find any answer which helps me yet.
So, can somebody tell me, if i need for this software-architecture an license for mySQL?
I have one DB-Server and one "working-Server", where the Server-Application is installed. The Clients communicate with the working Server and so only the working Server has an DB-Connection.
Here a little sketch:
[DB-SERVER]*------DB-Connection----*[working-Server]------TCP-Connection-----[Client]
|---------------Server-Side-----------------------||-----------Client-Side----------|
The DB-Server is a MySQL-DB and i connect from the working Server to the DB-Server with the .NET Communicator for MySQL. Well a few people says, that i need a License, because i use the .NET Communicator, other people say, that i don't need any license, because i don't give any code or assembly from MySQL to the Customer/Client.
It's like a PHP-Webpage from the architecture, and there, the customer or the user get only the webpage and the connection to the DB holds the PHP-Server, and there... no one has to pay to MySQL.
So what's now the right answer... Do i need a license or not. Because, i could ask MySQL directly, but i think, that they would say, that i need one
Maybe for your info, my project is not open-source and i don't think, that i sell the client-app (or the user-accounts) but what if i do it?
And if i need a MySQL License, which "really" open DB could you prefer? PostgreSQL should be great...
Thanks
|
|
|
|
|
Hi I am writing the following SQL command in the MS Access and it is working in access. When I add it to the command string in C# to execute it with the OleDbSqlDataAdapter I get a syntax error. Can someone help please?
SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));
I used \" only in the C# part
|
|
|
|
|
jonhbt wrote: SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));
try this for your string:
"USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = 'I' AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));"
|
|
|
|
|
Nothing happened U tried it that way before and still the same error. The error is in second select statement because when i removed that part it worked. So if there is a way I can replace that sql part it would solve it. The funny thing is that when running that in the access queries it works
|
|
|
|
|
LOL... that would have been pertinent information beforehand...
The second query (or more correctly - subquery) is failing because you are referencing a table that doesn't exist in the query... Consider saving the subquery as a view/query then referencing the saved view/query instead.
|
|
|
|
|
can you relate me to an example please of how this is done. Thanks
|
|
|
|
|
Save this as a query, call it something like qryMaxLogID
SELECT MAX(LOGID) as MaxLogID, UserID FROM CHECKINOUT
group by UserID
where CHECKTYPE = 'I'
Order by UserID;
Then, reference it in your existing query like this:
"select USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) inner join qryMaxLogID on userinfo.UserID = qryMaxLogID.UserID inner join INNER JOIN CHECKINOUT as main ON qryMaxLogID.MaxLogID = main.LogID;"
|
|
|
|
|
Replace CHECKTYPE = \"I\" with CHECKTYPE = 'I'
Niladri Biswas
|
|
|
|
|
i tried but the same syntax error
|
|
|
|
|
What I know is that after creating a Database Role and granting it execute permissions on specific sprocs, this is supposed to automatically grant the required permissions on any underlying tables/views/UDFs etc.
So why is it that on two specific tables, my application throws an exception because the DB needs explicit select/insert/update permissions and is unsatisfied with the permissions for the sprocs?
And another thing, if I wanted to generate the scripts for the permissions that I have set for the DB roles, how would I do that? All I can find is the generation for the role itself.
If the post was helpful, please vote!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
i just want to know is there anyway to remove field definition from my below x-query. please tell me is it possible exclude field definition from my query. if yes then please give me a sample.
SELECT CustomerID, CompanyName, City, Country INTO #Customers
FROM OPENXML(@iDoc,'/root/customer',2)
WITH (CustomerID NCHAR(5) 'custid',
CompanyName NVARCHAR(40) 'custname',
City NVARCHAR(15) 'city',
Country NVARCHAR(15) 'country')
tbhattacharjee
|
|
|
|
|
How to select data with alternative row.
eg:
i have a table T1 values -
name flag queue
A....y....1
B....x....2
C....x....3
D....x....4
E....x....5
i want the result like following
B
C
A
D
E
A
Insert the flag 'y' after every 2 row.
Without using stored procedure. I want to bind it to a gridview
Thankyou
YPKI
|
|
|
|
|
I may be missing something, but there does not appear to be any obvious link between the data you have shown and the sequence you require as the outcome, except for the order you have shown them in. Is the pattern 2, 3, 1, 4, 5, 1, 6, 7, 1 the only criteria?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi, I am presenting the solution in C#. It's a web application
What I have done?
Step 1:
One database table named AlternateTable with the same structure as yours
Name Flag Queue
A Y 1
B X 2
C X 3
D X 4
E X 5
Step 2:
I have a class file which is basically fetching the record from the database table AlternateTable
public class DBConnection
{
string connectionPath = "Data Source=SUMERUSYS135\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True";
string command = string.Empty ;
public DBConnection()
{
}
public DataTable FetchRecord()
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlConnection connection = new SqlConnection(@connectionPath);
command = "Select * from AlternateTable";
connection.Open();
SqlCommand sqlCom = new SqlCommand(command, connection);
adapter.SelectCommand = sqlCom;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
return table;
}
}
Step 3: Drag & drop a gridview in .aspx page
Step 4: I am using datatable as my datasource for the gridview.
Since you want to place the first row after every two alternate rows, so the logic will go like this
Algo: Place alternate row
Step 1: Create a custom datatable(say CustomDt) with the column
names that are to be exposed.
Step 2: Get all the records from the database and place it
inside a datatable(say OriginalDt).
Step 3: Loop thru the second record of the datatable(OriginalDt)
till the end.
Step 4: Check if a even row has come or not from the OriginalDt.
If it is true, then add the first record of the
datasource to the Custom Datatable after adding the even
row from the Original Data Table.
Else, add the other records of the original datasource to
the Custom Datatable.
Step 5: Make the Custom Datatable as the source for the gridview
Step 5: Put the above algo into action
protected void Page_Load(object sender, EventArgs e)
{
DBConnection objdbconnection = new DBConnection();
DataTable OriginalDt = objdbconnection.FetchRecord();
#region Final Custom Datatable
DataTable CustomDt = new DataTable();
CustomDt.Columns.Add("Name");
CustomDt.Columns.Add("Flag");
CustomDt.Columns.Add("Queue");
#endregion
for (int i = 1; i < OriginalDt.Rows.Count; i++)
{
if (i % 2 == 0)
{
CustomDt.Rows.Add(OriginalDt.Rows[i][0], OriginalDt.Rows[i][1], OriginalDt.Rows[i][2]);
CustomDt.Rows.Add("A", "Y", "1");
}
else
{
CustomDt.Rows.Add(OriginalDt.Rows[i][0], OriginalDt.Rows[i][1], OriginalDt.Rows[i][2]);
}
}
grdAlternateRow.DataSource = CustomDt;
grdAlternateRow.DataBind();
}
N.B.~ As you can see that I have hardcoded CustomDt.Rows.Add("A", "Y", "1");. As per your statement, I found it to be constant. If you need it in dynamically, you have to apply a different logic.
Hope it helps.
Vote me please
Niladri Biswas
modified on Tuesday, June 23, 2009 2:13 AM
|
|
|
|
|
WITH ABC
AS
(SELECT NAME,FLAG,QUEUE,ROW_NUMBER() OVER(ORDER BY NAME) AS [RN] FROM T1)
SELECT * FROM ABC WHERE RN % 2<>0
|
|
|
|
|
Hi man,
I hope that by mistake you replied to me instead of replying to the original poster.
Second, the user expects the answer not to be in stored proc.
But your solution is nice
Niladri Biswas
|
|
|
|
|
I have a project (web application) where i have to show hierarchical data in form of a tree.For example A has two children B(left) and C(right). B has two children D(left) and E(right). C has two children F(left) and G(right) and so on... My problem is I dont know the exact SQl query to count the number of all the subsequent children for a particular member. Like "how many children A has to his left or right (depthwise)" also i need to show the initial 15 members in form of a tree. If I do recursing for every node using C# code(which I am using) and then filling the form and going on it is going to take forever. So if someone knows the proper SQL query please help me. Or if anyone has any idea?Please tell me.Thanks
|
|
|
|
|
I assume that you have searched for this, but on the off-chance that you've missed it, here[^] is a link to a relevant article. It also has some interesting links in it.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I have a datagridview that takes data from a dataset from a table with an ID that is an identity specification number that is supposed to automatically give the next number. When I open a new line it should give me the next number but it doesn't. What am I missing?
|
|
|
|
|
I'm guessing that you won't get a number until the record is saved.
I are troll
|
|
|
|
|
Datagridview is not an SQL table. Until the Record is Saved and the Grid is Refreshed you will not get an Identity number. If you made a gridview to generate the number , it does not mean the Database will do the same thing. If your Application is a multi-user application you will have problems e.g lets say a user assumes that the ID is the 10 and the user user does an insert somewhere and faster that the user that assumed it was 10 and the other user will get the identity of 10 and the assumer will get 11.
Hope it helps
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi,
I do agree.. you can add an external col into your grid.
Md. Marufuzzaman
|
|
|
|
|
In some grids I get the right number, in one grid I get a minus number which saves ok afterwards but in the grid I'm working on now I don't get any number at all
|
|
|
|