|
Useing sp_OACreate procedure, create a CDO message object
This requires NO extra configurations at SQL Server
Regards
KP
|
|
|
|
|
|
Hi
I have permission to a databae that is replicated.. i want to have a trigger on that database table.. is it possible? will the update/insert trigger will work.? my database is updated every 5 to 10 mins..
Please help me out..
Jes
|
|
|
|
|
Is there any method of passing parameter to a scheduled job of SQL Server?
-----Have A Nice Day-----
|
|
|
|
|
Have the job reference a table. Set the column(s) in the table to whatever parameters you want.
|
|
|
|
|
hi all i have the Following Query
<br />
if Exists(select * from sde.Property_Backup p1<br />
where substring(p1.func_key,1,5)='GEOSS' <br />
aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key,<br />
p2.Attrib_code<br />
from sde.Property_Backup p2<br />
where p2.func_key = 'PV000000'<br />
And p1.lis_key = p2.lis_key<br />
And P2.aCTIVE =1))<br />
begin<br />
<br />
Update sde.Property_Backup p3<br />
set p3.Attrib_code = p12.Attrib_code<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS' <br />
And P1.aCTIVE =1<br />
end<br />
<br />
And i get the Following Error when run it,
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'p3'.
what is wrong
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Vuyiswa wrote: Update sde.Property_Backup p3
set p3.Attrib_code = p12.Attrib_code
Why are you including sde.Property_Backup? If p3 is a table, it would be right to write:
Update p3 set p3.Attrib_code = p12.Attrib_code.
|
|
|
|
|
hi
Thanks for your reply
i wrote it like this
<br />
if Exists(select * from sde.Property_Backup p1<br />
where substring(p1.func_key,1,5)='GEOSS' <br />
aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key,<br />
p2.Attrib_code<br />
from sde.Property_Backup p2<br />
where p2.func_key = 'PV000000'<br />
And p1.lis_key = p2.lis_key<br />
And P2.aCTIVE =1))<br />
begin<br />
<br />
Update p2<br />
set p2.Attrib_code = p1.Attrib_code<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS' <br />
And P1.aCTIVE =1<br />
end<br />
<br />
<br />
and It gives me an Error <br />
<br />
Msg 208, Level 16, State 1, Line 10
Invalid object name 'p2'.
/pre>.
the Problem happenes on the Update.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
How is your table called? p2? If p2 is the name of your table try the following update statement:
<br />
UPDATE p2 SET p2.Attrib_code = p1.Attrib_code <br />
INNER JOIN p1 ON p1.lis_key = p2.lis_key<br />
WHERE Substring(p2.func_key,1,5) = 'GEOSS' AND P1.active = 1
|
|
|
|
|
Thanks i have Used a Cursor to achieve this
<br />
DECLARE @CUR_LIS_KEY VARCHAR(20),<br />
@CUR_ATTRIB_CODE VARCHAR(12)<br />
<br />
DECLARE PARENT_CURSOR CURSOR FOR<br />
SELECT LIS_KEY, ATTRIB_CODE<br />
FROM SDE.PROPERTY_Summary P1<br />
WHERE EXISTS(SELECT 1 <br />
FROM SDE.PROPERTY_Summary P2 <br />
WHERE P2.FUNC_KEY LIKE 'GEOSS%' AND<br />
P2.LIS_KEY = P1.LIS_KEY And p2.Attrib_code is Null<br />
And p2.Archive_Date is Null <br />
<br />
) <br />
<br />
OPEN PARENT_CURSOR<br />
FETCH NEXT FROM PARENT_CURSOR<br />
INTO @CUR_LIS_KEY, @CUR_ATTRIB_CODE<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
UPDATE SDE.PROPERTY_Summary<br />
SET ATTRIB_CODE = @CUR_ATTRIB_CODE<br />
WHERE FUNC_KEY LIKE 'GEOSS%' AND<br />
LIS_KEY = @CUR_LIS_KEY<br />
<br />
FETCH NEXT FROM PARENT_CURSOR<br />
INTO @CUR_LIS_KEY, @CUR_ATTRIB_CODE<br />
END<br />
<br />
DEALLOCATE PARENT_CURSOR<br />
<br />
<br />
<br />
Thanks for your Help
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
hi all
i will try by all means to Explain my Problem. i have records that Mach with a Field named "Lis_key" ,and differ in a Field named "Func_key".
Lis_key =========Attrib_code===============Func_key======
======================================================
01424545 1212033993 PV000000
01424545 Null GEOSS001
01424545 Null GEOSS002
01424545 Null GEOSS003 .
Now from the Above table, i call records that have Func_key "Parents" and Records that has Func_key "Children". and in my table there are many Parents that have no Children. Am interested in those that have Children. As you can see the Attrib_code of Children is Null, i have this query that Find the Parents that has Children. and its like this
[CODE]
select p1.Attrib_code,p1.Lis_key
from sde.property_Backup p1
where p1.func_key = 'PV000000'
and exists (select 1
from sde.property_Backup p2
where p2.lis_key = p1.lis_key
and substring(p2.func_key,1,5)='GEOSS' And
P1.aCTIVE =1)
[/CODE]
Lis_key ========Attrib_code========Func_key======
=================================================
01424545 1212033993 PV000000
01424545 1215035993 PV000000
01424545 3599345445 PV000000
01424545 5035934544 PV000000 .
and now as you above, table this Parents have Children with a Fun_key that is like "GEOSS", and they are null. i want to Copy a Attrib_code of a Parent to a Child as long as the Lis_key as the same. and the Final results should be like this
Lis_key ========Attrib_code=========Func_key======
=================================================
01424545 1212033993 PV000000
01424545 1212033993 GEOSS001
01424545 1212033993 GEOSS002
01424545 1212033993 GEOSS003 .
No more Nulls for Attrbi_code for GEOSS, So i need an update Statement for the Children.
Thanks
Please Help
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I have a table with 5 columns of client information:
Id
FirstName
LastName
SocialSecurityNumber
AppointmentDate
A client can have more than 1 record.
I need a list of unique clients (Select Distinct FirstName, LastName, SocialSecurityNumber From Clients) which of course works fine.
The problem is that I need the Id field in the query so that I can join the results with other tables, and of course when I add the Id field to the query I get duplicates.
I am fairly new to SQL. I know how to write code that will loop through a datatable or whatever, but I really want to find a way to do this with a simple SQL query. Any help would be appreciated.
Thanks.
|
|
|
|
|
You really need to normalise the table - get rid of the appointment date field so that there's only one record per client, otherwise you don't know which of the possible IDs to join onto the other tables.
|
|
|
|
|
Remove the AppointmentDate column, then create a second table that stores just the appointments and an identifier. Then use a Join statement.
"A client can have more than 1 record" is about the worst thing a database can say.
|
|
|
|
|
Johnny
I STRONGLY advise you to get a book on database design, find a beginners book and at least get the principles. You are committing one of the most basic errors in data design and if you do not get the basics you are going to create a nightmare for yourself and the poor sod who has to fix it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is it posible to write a trigger for a SQL db that fires on a date change?
i need to log the communications between an app and a palm scaner, but this would become one huge table within months. the idea is to only keep the latest 30 days of records in that table. so the trigger has to fire every day, check the date on the entries and delete it if its over 30 days old.
Thanx
Harvey Saayman - Junior C# and SQL Programmer
Think big and kick ass
|
|
|
|
|
you shuld look into using a windows service that would run on the server that has that database. that Service should run at say 12am each night and delete the records as necessary
hope it helps
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
yeah we thaught of that, doing it programmaticly. but it would be sweet if a SQL trigger could handel that for us.
Thanx man
Think big and kick ass
|
|
|
|
|
Use an SQL job to call a stored proc?
|
|
|
|
|
We are trying to create a series of XML scripts from a Mysql database query. We are having two issues that we are not smart enough to figure out & could use some help;
1) We need to save each row of the query result as a separate xml file i.e. running the query may generate 100 results - each row being a full XML string which needs to be saved as a separate file. Is there a method to accomplish that goal?
2) Embedded in the XML script are DOS file paths with back slash separation. When we save the query result, we end up with double back slashes because Mysql uses the back slash as the esc char. Is it possible to get a single back slash w/o needing to externally edit the file?
Thanks for any suggestions!
|
|
|
|
|
Hi all..
i want to know how to take a backup for database in sql server.
thanks in advance..
siri
|
|
|
|
|
|
Hi
friends
I want to select two date from sys.
1st date is 1-April-2007
where Day 1st= is fixed
Month April is also fixed
But Year 2007 is the current year-1
and the 2nd date is 31-Dec-2007
where day 31st/30th/28th/29th is the last day of the previous month.means current month -1
and Year 2007=Current year-1 for the month of January and for the other months its fixed that is 2008 menas
the current year.
well i am using SQL SERVER 2005.pLZ help me out.
|
|
|
|
|
I think this will do it - it looks a bit horrendous and I'm sure there must be an easier way 
declare @start datetime, @end datetime, @month varchar(10)<br />
<br />
set @month = 'feb'<br />
<br />
select @start = convert(datetime, '01-Apr-' + convert(varchar,datepart(year,dateadd(year,-1,getdate()))))<br />
select @start<br />
<br />
select @end = convert(datetime,dateadd(day,-1,dateadd(year,1,convert(datetime, '01-' + @month + convert(varchar,datepart(year,dateadd(year,-1,getdate())))))))<br />
select @end
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If we delete or update a row in a table; the no of changed row shows as n rows affect.
Here is the example.
Ex: update TableName set Column1='N' where Column2=2
Output: 4 rows affected
In this i need to get the value 4. How to fetch that value.
Can anyone help me out 
Regards,
LEE
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
