|
Which database system?
Doesn't SQL server use DatePart?
|
|
|
|
|
In MS SQL server you can use MONTH(mydate)
|
|
|
|
|
Have your Google powers abandoned you? looking for SQL date functions, you would easily get this page[^] that summarizes it all.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
you can simply use select Month(getdate()) to display month in number.
|
|
|
|
|
Dear Alll,
i have the following:
Set t = classdb.OpenRecordset("select unit_price from contract_details where item_classification_id = '" & Me.Text12.Value & "'";)
when it runs it give me this error : DataType mismatch in criteria expression
but the item_classification_id is a number feild (using access 2007), and Me.text12.value returns an integer. i have checked it 100's of time, it drives crazy...
may be am missing a thing , am not that good in coding, any clues ???
Regards.
0 will always beats the 1.
|
|
|
|
|
You are enclosing the value obtained from the Text12 textbox in single quotes. That's correct for a text value but not for a numeric value - hence your data mismatch error.
Modify your query as follows:
Set t = classdb.OpenRecordset("select unit_price from contract_details where item_classification_id = " & Me.Text12.Value & ";")
|
|
|
|
|
thanks aloooooot
regards,
0 will always beats the 1.
|
|
|
|
|
Hello,
Our production and development databases are on the same server, due to the complexity of the system. is there a way, that I could not have the production server active, when I open the database server? I do not want to accidently connect to this.
Thanks!
|
|
|
|
|
Can you create a different password for the production server database?
|
|
|
|
|
Depending on what DB server you are using, you can also assign different users to the databases.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Both instances of databases are on the same server, this is sql2005. Once you add a login, it is by default to all the databases that exists on that server, right?
|
|
|
|
|
I'm not an expert on SQL2005, but check the GRANT SQL keyword. You should be able to create a user and grant that user access to only a single DB.
I don't think there's any way to restrict the administrator from accessing a db, though.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
You can create a login on a SQL server, but you still need to associate it to a User in a database and grant that user specific rights in that database.
For example, I created a user, pm_user, which is valid in the PM database, but has no access to the "Sandbox" database. When I try to execute the following SQL,
Use Sandbox
GO
I get the following error message:
The server principal "pm_user" is not able to access the database "Sandbox" under the current security context.
This shows that a login is not the same as a user and with a single login, you can restrict the databases the user has access to.
Hope this helps.
David
|
|
|
|
|
vanikanc wrote: Both instances of databases are on the same server, this is sql2005. Once you add a login, it is by default to all the databases that exists on that server, right?
Wrong. A login to a server does not automatically grant access to databases on that server.
|
|
|
|
|
vanikanc wrote: Our production and development databases are on the same server, due to the complexity of the system. is there a way, that I could not have the production server active, when I open the database server? I do not want to accidently connect to this.
Obviously the best solution is to use two different servers.
That precludes other problems as well such as run away queries that cause CPU overload, or timeouts due to long running processes.
Myself I test against the database server which runs on my development box.
But as noted by others very careful use of users and permissions would entirely preclude overlap.
|
|
|
|
|
Hello!
Im trying to set up a Continuous Query Notification between our oracle database and my c# service, but can´t seem to get it to work. This is the method I have for making the connection:
private void SubscribeToTable(string tableName)
{
Log.Write("Add listener: " + tableName);
OracleConnection con = DataBase.GetOpenConnection();
OracleDependency dependency = new OracleDependency();
dependency.QueryBasedNotification = false;
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "Select * from " + tableName;
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
dependency.AddCommandDependency(cmd);
cmd.Notification.IsNotifiedOnce = false;
cmd.AddRowid = true;
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
And when I run this I get a row in user_change_notification_regs with these values:
42 4 net8:
and this is the callback event:
static void dependency_OnChange(object sender, OracleNotificationEventArgs eventArgs)
{
Log.Write("Table has been Changed");
Log.Write(eventArgs.Source.ToString());
Log.Write(eventArgs.Info.ToString());
Log.Write(eventArgs.Source.ToString());
Log.Write(eventArgs.Type.ToString());
}
My problem is the callback-event never runs. Has anyone else had this problem?
|
|
|
|
|
Hello,
Have you solved this issue?
I am having a similar problem. I used a OCI sample code from ORACLE site. My OCI client was able to register the subscription. However, the subscription was removed from USER_CHANGE_NOTIFICATION_REGS as soon as I updated the table that is registered for subscription. And, my client callback function never got called.
Btw, I used oracle client 11.2.0.1.0 and my db is running 11.2.0.1.0. The sample code was compiled on Linux Centos 5.5.
I checked my user privilege and it has CHANGE_NOTIFICATION. I also check "job_queue_processes" and "dml_locks", both of them have non-zero values.
|
|
|
|
|
Hello!
Sadly I never solved it. My subscription was also removed eventhough I set it up to stay (cmd.Notification.IsNotifiedOnce = false;), and I can see with network sniffer that something is sent to my computer from databasecomputer, but my application never registers the callback. I also tried with samplecode from oracle
(http://www.oracle.com/technetwork/issue-archive/win06odp-099708.html[^] and
http://www.devart.com/dotconnect/oracle/docs/Devart.Data.Oracle~Devart.Data.Oracle.OracleDependency.html[^])
but I experienced same problem with that.
I used oracle client 11.2.0.2.0 and my db is running 11.2.0.1.0.
I´ve shelved this issue for a while but figured I'd give it another go in a few weeks. If you or anyone else solve this issue I would very much appreciate a reply here, and I will of course do the same.
/regards
modified on Thursday, February 10, 2011 3:53 AM
|
|
|
|
|
Thanks for the reply.
If I solve this issue, I will post it here.
|
|
|
|
|
Hi,
May I know what Oracle edition that you have?
Is it Enterprise or Standard edition?
Mine is standard edition. I am trying to find out if publish/subscribe is supported in standard edition.
|
|
|
|
|
Hello,
I managed to get my subscription working. Here is what I have done on my test environment.
My client uses oracle-instant-client 11.2.0.1. My db server is running Oracle 11.2.0.1 release. Both client and server are on Centos OS. Previously, I have client on Centos and server on Solaris.
I have done quite few tests with combination of different client, server and their OS. I found that the Oracle version and the OS platform contributed to the issue that I had.
As a preliminary quick test, I also ran my client on the same box where my db server was running. In my case, this worked fine.
Hopefully, this will help.
Cheers,
sw
|
|
|
|
|
Hello,
we have created a job to automatically run re-indexing on some of the indexes, on a weekly basis. The outcome, is that some of the indexes are taking a longer time the second time around. We have collected data for 2 weeks now and running some analysis. Is there something like re-indexing too often not a good idea? They indexes are used quite often, so the plan for once a week re-indexing worked out.
thanks!
|
|
|
|
|
Let's take a step back and see what is motivating you to re-index ? Re-indexing a table which does not change much (insert,update,delete) is a waste of time.
Maybe you should consider using a threshold, like "fragmentation" to determine when you should re-index. Something similar to disk defragmenter; it analyzes your disk, then recommends you to either defrag or not defrag.
Look at this article ...
http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx[^]
|
|
|
|
|
How to row lock in SQL Server 2005. I execute a sql for row locking and that is
SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'
it work fine but in this case row is lock for update not for selection. I just want to know how to lock a row as a result another user can not see that row when issue a SQL in SQL Server. please guide me. thanks
tbhattacharjee
|
|
|
|
|
Bad news there, I'm afraid. You can't rowlock an row for a select in SQL Server 2005 so that others can't see it. Even the xlock hint won't cut it for you because it works as a page lock rather than a row lock in SQL Server 2005 and, if SQL Server determines that the underlying rows haven't changed, it will allow the read of the data.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
