|
I want to execute a stored procedure once per day.(i want to delete some records from a datatable).Is there any direct way do this?
Please help me out.
Thanks.
|
|
|
|
|
You can use SQL Server Agent. This link explains it all:
MSDN SQL Server Agent
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
Use a SQL job
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Ya. I'll do it.thanks in advanced.
|
|
|
|
|
Is there any way to do scheduling execution of sp using C#.
Any suggestions...
thanks
|
|
|
|
|
How do I add a column to multiple table when some of the table already have the column name I wish to add.
Thanks in advance,
Michael
|
|
|
|
|
Simple. You can't. If the column name exists in the table then you can't add it again, it wouldn't make sense.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I understand that if it is there I cannot add it again, however can I use a command such as "IF NOT EXIST" or something similar I have tried variations and can't seem to get it correct does anyone know how I can do this.
Michael
|
|
|
|
|
Assuming you are using sql server (2000 or 2005)
old way
if not exists (select 1 from syscolumns where name = 'column1' and id = object_id('table1'))<br />
begin<br />
alter table table1<br />
add column1 int<br />
end
or, using the views that come as standard
if not exists(select 1 from information_schema.columns where table_name = 'table1' and column_name = 'column1')<br />
begin<br />
alter table table1<br />
add column1 int<br />
end
Ensure you do a USE {database} first as these only work in the current database
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Could somebody kindly point me to an article about performing a join that excludes the results from another table? I believe that it is something resembling WHERE tblFirstTable.Key...NOT IN tblSecondTable.Key or something.
modified on Tuesday, January 29, 2008 5:19:13 PM
|
|
|
|
|
I have written a small query to illustrate how to do this.
As you were polite in asking for an article and not the code (to be honest it's quicker for me to write the code than find an article).
This example shows how to find all the animals in the zoo table that are not on the feeding table - I guess some animals are going to go hungry. 
<br />
select distinct z.animal<br />
from zoo z<br />
left join feeding f<br />
on z.animal= f.animal<br />
and f.animal is null<br />
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
I am running in a problem of not able to restore a differential database backup through a scheduled job. The Restoration command is :
declare @str nvarchar(4000)
declare @spid int
while exists (select spid from sysprocesses where dbid= db_id('SGOTA'))
begin
set @spid = (select min(spid) from sysprocesses where dbid= db_id('SGOTA'))
set @str= 'kill ' + convert(nvarchar,@spid)
execute sp_executesql @str
end
RESTORE DATABASE SGOTA from disk='d:\FTP\DB Backup\OTA\sg_Differential_Backup.BAK'
with standby ='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\UNDO_SGOTA.DAT'
The error I am receiving is:
Executed as user: NT AUTHORITY\SYSTEM. The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. [SQLSTATE 42000] (Error 4306) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I have taken a full backup earlier. Both the databases have a Bulk-Logged Recovery model and the destination database is in read-only mode.
I am in an urgent need of the solution. Kindly help.
-----Have A Nice Day-----
|
|
|
|
|
i hv created a database named Sample.....
In this database i have 3 tables named Department,Division,Location..All tables are having data also....nw i need to delete the data from all the three table at a time through query.....
is it posiible?.
if so,den what is the query for that?....
any kind of information is appreciatable...
please help me...
|
|
|
|
|
try over referential integrity using primaty and foreign key, with cascade delete oprion on tables
when u delete one row(Col001) from MyTableOne cascade delete will delete all rows in other tables that have referential integrity with table MyTableOne on Col001.
|
|
|
|
|
sorry i didnt get u.....am new to sqlserver.....can u pls tel me how to do this
|
|
|
|
|
from BookOnLine
Enforcing Referential Integrity Between Tables
Referential integrity between tables is enforced by default when you create a relationship in your database diagram. An enforced relationship ensures each value entered in a foreign key column matches an existing value in the related primary key column.
You can change the conditions under which referential integrity is enforced by editing the relationship's properties.
To change referential integrity options for a new relationship
In your database diagram, create a relationship. For details, see Creating a Relationship Between Tables.
In the Create Relationship dialog box, clear or select one or more of the options.
For information about the options available for SQL Server databases, see Database Designer Considerations for SQL Server Databases.
To change referential integrity options for an existing relationship
In your database diagram, select the relationship line.
Right-click the relationship line and select Properties.
Choose the Relationships tab.
Select the relationship from the Selected relationship list.
Clear or select one or more of the options.
For information about the options available for SQL Server databases, see Database Designer Considerations for SQL Server Databases.
The relationship is updated in the database when you save the diagram or either of the related tables.
|
|
|
|
|
Hi all,
I want to get a multicolumn selection but with the behavior of the DISTINCT cmd only on the first column, something like that
SELECT DISTINCT order_id, date FROM Orders WHERE .....
but the distinction should only refer on the "order_id" and not even on the "date" .
How does the sql-cmd looks like ?
thanks in advance
Frank
|
|
|
|
|
Can you give us a sample of what data exists in the table, and then what data you want after the query has been run? Because what you have doesn't seem to make sense...if your data looks like this:
order_id - date
1 - 1/1/2008
2 - 1/1/2008
2 - 1/2/2008
then which date are you going to want to display? If you distinct on only the order_id (which isn't really possible using a distinct, you would have to use a group by) then the SQL doesn't know which date you want...(which is why it's not possible using Distinct)....so please supply us some more info so we can help.
|
|
|
|
|
Hi Kschuler,
my problem is that I have to show the date and I have to show the order_id but only once.
I know, in most cases it doesn't makes sense. But for my application it doesn't matters which date has to be displayed when double entries exists. The central point is that all entries have to show a date multiple entries or not.
This is the crux.
Now I think there is no SQL solution for that problem.
Now I filter the recordset.
Thank you for your help
|
|
|
|
|
You can accomplish it with something like this:
SELECT order_id, MAX(date) FROM tablename GROUP BY order_id
The idea is that you will return one order_id and the largest date with that order_id. I know it would work for a number, I'm just not sure how a date reacts to the MAX() function...you could also use MIN if you want the smallest date...and if you did this
SELECT order_id, MAX(date), COUNT(*) FROM tablename GROUP BY order_id
It would also display how many dates exist for that order id.
So maybe you need to do some research on GROUP BY and how it affects date datatypes.
Hope this helps.
|
|
|
|
|
Great Kschuler,
it works !!!! thanks
It works because you are using agregat functions as a second and/or third parameter after the select.
That helps for the future.
Frank
|
|
|
|
|
This doesn't make sense. Either all of the data is distinct or none of it is. If you want a single order_id, then you can only retrieve one date otherwise this order will span multiple lines.
|
|
|
|
|
Hi all
I have the following the statements
SET @TempMenuId= 'SELECT @res1 = coalesce(@res1 + ',', '') + CAST([Tb001_Id] AS Varchar(8)) FROM [admin].[cm_tb_005] WHERE' + @Role_Idv +'=1'
EXEC @TempMenuId
Iam Passing @Role_Idv dynamicall.But its showing error message like this
The name 'SELECT @res1 = coalesce(@res1 + ',', ') + CAST([Tb' is not a valid identifier.
Please help me on this.
Regard's
Veeresh
i want to join this group
|
|
|
|
|
this is working !
DECLARE @IDNAL VARCHAR(36)
DECLARE @ID_STAVKE VARCHAR(36)
DECLARE @IDDOC VARCHAR(36)
DECLARE @IDVL VARCHAR(36)
DECLARE @IDLOK VARCHAR(36)
DECLARE @NEVALJA CHAR(1)
DECLARE @RECENICA AS VARCHAR(5000)
DECLARE @LOCAL_KONTO AS VARCHAR(36)
DECLARE @ID_FORMULE AS VARCHAR(36)
DECLARE @FORMULA AS VARCHAR(500)
SET @MySTRING='
UPDATE #A SET
#A.col001=#A.col001+COALESCE((SELECT ('+@FORMULA+') FROM #LOCAL_STAVKE
WHERE #LOCAL_STAVKE.VD='''+@IDDOC+'''
AND #LOCAL_STAVKE.KONTO= CASE '''+@LOCAL_KONTO+'''
WHEN '''' THEN #LOCAL_STAVKE.KONTO ELSE '''+@LOCAL_KONTO+''' END
AND #LOCAL_STAVKE.ID=#A.IDNALOGA ),0)'
EXECUTE(@MySTRING)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
