|
Can you post separated parts here , i will tell you where you went wrong.
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/
|
|
|
|
|
Am new to oracle...can u just help me to convert the given below sql query to oracle?
create PROCEDURE [dbo].[PEAP_SelectEmployeesByAppraiser]
( @AppraiserID varchar(12) )
AS
SET NOCOUNT ON;
Create Table #PEAP_SelectEmployeesByAppraiser_All
(EmployeeID varchar(6) null, SurNameFirstName varchar(150) )
Insert into #PEAP_SelectEmployeesByAppraiser_All
SELECT emp_code AS EmployeeID, + emp_code + ' ' + l_name + ' ' + f_name AS SurNameFirstName FROM
emp_master WHERE (Appraiser = @AppraiserID)
Insert into #PEAP_SelectEmployeesByAppraiser_All
SELECT emp_code as EmployeeID, + emp_code + ' ' + l_Name + ' ' + f_Name AS SurNameFirstName FROM emp_master
WHERE (Reporting_To = @AppraiserID) and isnull(Appraiser,'') = ''
and emp_code not in (select employeeid collate database_default from #PEAP_SelectEmployeesByAppraiser_All)
Select Distinct EmployeeID, SurNameFirstName from #PEAP_SelectEmployeesByAppraiser_All
|
|
|
|
|
AS i said before i dont do oracle, am not familiar with Oracle.
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/
|
|
|
|
|
ok..thanks 4 ur kind help.
|
|
|
|
|
Welcome
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 am working on a MVP project using WPF in C#. I have the task to trace every change in our Business Objects in the DataBase, that means, if someone changes a property of the Business Object and stores it to the DB, another table should log what the user has done. What is the easiest way to achieve this? Are there some events which are thrown when a table is updated? How can I handle this in my C# code, respective in the Business Object Class?
Any suggestions would be appreciated!
|
|
|
|
|
If it is schema changes you want to track then you would use a DDL trigger[^]
|
|
|
|
|
And if i only want to track changes in tables, I would have to use DML, right?
|
|
|
|
|
You would use normal triggers or if you are running SQL Server 2005 or later you could use the built-in auditing.
|
|
|
|
|
The problem is, I can't use the DB Triggers because we want to store which User (of our Application) made the changes, and the Triggers won't deliver this information.
|
|
|
|
|
You can get the user that made the changes by using the SUSER_SNAME() function and you can get the name of the machine that they are logged into by using the function HOST_NAME().
|
|
|
|
|
Thanks for the hint, but I need the user of our application, not the DB user 
|
|
|
|
|
The only way to audit changes by user of the application would be to modify your application.
|
|
|
|
|
Yes you are right.
Is there a possibility to get the information which field(s) were inserted/updated/deleted from the DataBase after executing the respective Stored Procedures?
|
|
|
|
|
Dear All,
I Use Oracle DB
I would like to ask you that I have one table that has records from 010000000 until 010999999
I want select query only start from 010203000 until 720000 numbers.
Please help me
Socheat
|
|
|
|
|
Just add a where condition to your query.
select ....
from ...
where id >= 720000 and id <= 10203000
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I am presenting two solutions for your problem.
1) What Ashfield has already presented
2)
SELECT *
FROM tblName
WHERE RECORDNUMBER BETWEEN 010203000 and 720000
ORDER BY RECORDNUMBER ASC
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi,
I've created a dll in visual basic.net 2005. i want to call the dll in my sql server 2005 stored procedure. (note sql server 2005 is upgraded from sql server 2000) so its not supporting creating assembly in sql server 2005.
my vb.net code,
Imports System
Imports System.Reflection
Imports System.IO
<assembly: System.Reflection.AssemblyKeyFile("getpsftpkey.snk")>
<assembly: AssemblyVersion("1.0.0")>
Namespace nscreatetxt
Public Class clscreatetxt
Public Sub New()
End Sub 'New
Public Sub createtxt()
Dim SW As StreamWriter
SW = File.CreateText("\\file1\softusers\ps_prakash\MyTextFile.txt")
SW.WriteLine("Hello Mom")
SW.WriteLine("Hello GrandMom")
SW.Close()
End Sub 'createtxt
End Class 'clscreatetxt
End Namespace 'nscreatetxt
my stored procedure code:
-- Scratch variables used in the script
DECLARE @retVal INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)
---2146232576
-- Initialize the COM component.
EXEC @retVal = sp_OACreate '{3045481F-66C0-3EE5-BE5C-2DA49E90AFCA}', @comHandle OUTPUT
print @retVal
IF (@retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
-- Release the reference to the COM object
EXEC sp_OADestroy @comHandle
i created the dll, generated strong name, registered in gac and using regasm i registered in registry after that i executed the above sql statements its returning some negative value.
my questions is
1) can i call a dll (which is created in visual studio 2005) inside a stored procedure of sql server 2005
2) can i use sp_oacreate procedures in sql server 2005
3) send me detail code how to dll in sql server 2005 stored procedures
Thanks & Regards,
P.Prakash
|
|
|
|
|
San_Sadhan wrote: can i call a dll (which is created in visual studio 2005) inside a stored procedure of sql server 2005
Do you know what a SQL Server is? 
Manas Bhardwaj
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Hi,
Thanks for ur reply. i dont know much sql server but my higher official want like that. could you please explain me, what exactly your trying to say. is that method is wrong or i dont want to this thing in sql server.
sorry for late response.
Regards,
P.Prakash.
|
|
|
|
|
Hi,
I have a column named posted date,
Depending on the above column I need to count No of records per year and
month wise asc.
I done by using group by function but am not able to get jan --- desc
order.
Result ie:MonthName,Year,Count columns
|
|
|
|
|
Show your code what you did so far.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi,
I have below records in my table 'Sales'
ID SalesName LastUpdated
-------------------------------------------------
1 S1 2009-07-16 13:27:23.890
2 S2 2009-08-16 13:32:58.127
3 S3 2009-08-16 13:33:01.987
4 S4 2009-08-16 13:34:21.733
5 S5 2009-09-16 13:32:40.703
6 S6 2011-01-16 13:32:23.703
7 S7 2011-02-16 13:32:23.703
8 S8 2011-02-16 13:32:23.703
9 S9 2011-03-16 13:32:23.703
10 S10 2011-07-16 13:32:23.703
--------------------------------------------------
Below query gives me the count of sales for different months on yearly basis.
SELECT DISTINCT MonthCount = (SELECT COUNT(DATEPART("mm",LastUpdated)) FROM Sales
WHERE DATEPART("yy",LastUpdated) = DATEPART("yy",p.LastUpdated)
AND DATEPART("mm",LastUpdated) = DATEPART("mm",p.LastUpdated)) ,
"Month" = DATENAME(Month,LastUpdated),"Year"=DATEPART("yy",p.LastUpdated)
FROM Sales p ORDER BY DATEPART("yy",p.LastUpdated)
Here is my desired result:
MonthCount Month Year
-----------------------------
1 July 2009
1 September 2009
3 August 2009
1 January 2011
1 July 2011
1 March 2011
2 February 2011
|
|
|
|
|
I have several servers. Server 1 is SQL Server 2000, Servers 2 and 3 are SQL Server 2005. I have linked 2 (2005) to 3 (2005) and 1 (2000) to 2 (2005). I have two windows logins that are in the sysadmin role on all servers. Neither login is an owner of a database. Both logins are members of the builtin/administrators group. Login A can connect to server 1 (2000) in mgmt studio and see all the databases on server 2 (2005). Login B connects to server 1 (2000) in mgmt studio but can only see one database on server 2 (2005). But both can connect to server 2 (2005) and see all the databases in the linked server 3. Server 1 (the SS2000 box) has a number of windows logins mapped to the sa, and one sql login mapped to a local login on server 2. When I change the "...be made using this security context" to sa, it instead inserts the local login.
I probably didn't explain this very well, but it has given me a migraine. Any idea what's going on here?
My other signature is witty and insightful.
|
|
|
|
|
goodideadave wrote: I probably didn't explain this very well
I don't think it is possible to explain your problem clearly.
I have a number of cardinal rules I apply when building a solution one of them is absolutely NO linked servers, now I remember why.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
