|
Hi,
I have this sql procedure that i need to run once every day.
Can anyone please tell me how can i do that.
Thanks
|
|
|
|
|
checkout documentation for sp_help_job and related stored procedures
|
|
|
|
|
Hi,
when using SQL Server 2000 one has the option of creating scriptfiles for all database objects, such as tables, sp's, views, users, roles, etc.
I need to write a tool which can perform the same thing and I need a starting point. Are there any ready made classes or libs out there? What SQL system-procedures are used to collect this information?
Any pointing appreceated.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
All the information you need is in the system tables. These all start with 'sys' and exist in each database; The master database has extra tables that govern the whole server. The details of each of the system tables can be found in the books online.
There is also the INFORMATION_SCHEMA views which are on the master database. They use the system tables interally, so if you want to see how the systemp tables work then looking at the source for these views is quite useful.
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Yes, Colin, it does help indeed (at leaster after recovering from the first shock)! Thanks very much!
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Matthias Steinbart wrote:
at least after recovering from the first shock
Shock?! What was shocking? 
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Not your answer, I just had a look at the system tables and estimated the amount of work it'll take to get a grip of this... 
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
True, there are a hell of a lot of self referencial joins. I once ended up with one SELECT with about 8 joins - 3 of which were to sysobjects. Luckily I wasn't doing anything too complex. However, like I said, check out the definitions for the INFORMATION_SCHEMA views on the master database as they help a lot if you want to see examples of how it all hangs together.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
I have a lot of this done as part of a database build tool I am developing over on GotDotNet...
Check out the code in the "SQL Server Data schema providor" classes - shows how to list tables, views, users, user groups, stored procs and the fields, permissions, indexes etc. that apply to them.
|
|
|
|
|
CREATE PROCEDURE pr_InsertDictTable
@intID int,
@vchrValue nvarchar(50),
@vchrWesBarCode nvarchar(10),
@vchrTableName varchar(20)
AS
declare @sql varchar(1000)
set @sql = 'INSERT INTO ' + @vchrTableName + ' (Id, Name, PDACode) VALUES ( '+@intID+','+@vchrValue+','+@vchrWesBarCode+')'
print @sql
GO
Any problem in this procedure?
======
You need a head to program. Cool, fast and sharp.
|
|
|
|
|
set @sql = 'INSERT INTO ' + @vchrTableName + ' (Id, Name, PDACode) VALUES ( '+@intID+','+@vchrValue+','+@vchrWesBarCode+')'
->
set @sql = 'INSERT INTO ' + @vchrTableName + ' (Id, Name, PDACode) VALUES ( '+ CAST( @intID as varchar )+','+@vchrValue+','+@vchrWesBarCode+')'
|
|
|
|
|
Yes, plenty.
1. It's pointless. All you're doing is printing a string. Why not just generate the string at the caller?
2. variable @sql is not needed. you could just "print 'insert into ... "
3. It won't work. You need to CONVERT(varchar(10),@intid) before you can use it as a varchar.
4. You're mixing nvarchar and varchar. and implicitly casting nvarchar to varchar, thus negating the whole point of using nvarchar in the first place.
5. I think hungarian notation in a database is evil.
6. The main point of stored procedures is that they're compiled and hence more efficient. You're not leveraging that efficiency at all here.
jon
#include <beer.h>
|
|
|
|
|
Hi,
I am writing a few extended stored procedures for SQL Server database. A few of the functionality limitations for which I could find any answers are as below,
- How can we pass data that is larger than 255 characters to the extended stored procedure as parameter? Similarly is there a way to pass the long data results back (basically strings with length > 255)
- How can we use the resultset returned by the extended stored procedure as a virtual table in the T-SQL functions/procedures?
Has anyone else come across these limitations? Kindly share any possible logical solution to overcome these situations.
Thanks in anticipation 
Anil
|
|
|
|
|
Hi All,
I am writing a multithreaded application in Visual C++ 6.0, which uses ADO to connect to either an access or sql database.
My question is, since many of my threads want access to the database, for both reading and writing, what do I need to do to thread safe it? Can I just have one global _ConnectionPtr and allow all the threads to use this, or do I need to wrap it in a section?
Many Thanks,
|
|
|
|
|
How can we update or change the content of a text file using triggers? If anyone have solved this problem earlier please help me.
Thanks
|
|
|
|
|
I have created a regular dataset using the following statement:
[CODE]
DataSet myDS = new DataSet();
. //initializing code to create the neccessary
. //tables, columns and constraints
.
[/CODE]
but then when I created a datagrid and attempt to assign my dataset and datamember with my dataset and datatable respectively via the properties menu, the editor failed to recognize my dataset!
I have tried to check my dataset and verified that it's okay.
What puzzles me are the following questions
Firstly, why doesn't the controls recognize the dataset?
If I have used the Visual Studio.net's GUI editor to create the dataset and define the tables and columns there, the datasource property actually recognizes it. Further investigation reveals that it is a class that inherits from dataset(correct me if I'm wrong). Therefore, if I intend to code my own dataset of this sort, what should I do and what interface should I derive from?
I thank you for your patience for reading up to this point. And I really do appreciate if anyone here could help.
My thanks in advance.
 The confused one
|
|
|
|
|
I beleive it has to be added to your form to be seen by the wizards. If you use a custom dataset this can be a pain. Depending on the application, you can build your datasets into a dll and then add them as a component on your toolbar where you then drop the on your form and that seems to work well. Not sure of other methods to make them see them.
I think it would be a great feature if there was some attribute that could be added to any collection that would allow the wizard to see it and add it to a form without having to go through hoops. For me, I usually put all my datasets and data managers into a Data abstraction layer, so most of the time the wizards are useless unless I use the dll method above.
Rocky <><
www.HintsAndTips.com - Now with GMail Queue
www.MyQuickPoll.com - 2004 Election poll is #33
www.GotTheAnswerToSpam.com
"We plan for the future, we learn from the past, we live life in the present!"
|
|
|
|
|
Hi,
I've written a stored procedure which should return a specific value and has an additional OUTPUT parameter. Here goes the code:
<br />
CREATE PROCEDURE dbo.Update_AddresseInfo<br />
<br />
(<br />
@UserID int,<br />
@DateModified DateTime,<br />
@ElementID int,<br />
@Street varchar(50),<br />
@City varchar(50),<br />
@ZIP varchar(50),<br />
@NewDateModified DateTime OUTPUT<br />
)<br />
<br />
AS<br />
declare @RetVal int<br />
declare @DateNow DateTime<br />
set @DateNow = GetDate()<br />
<br />
-- do some stuff <br />
<br />
UPDATE dbo.AdresseInfo SET<br />
Street = @Street, <br />
ZIP = @ZIP, <br />
City = @City, <br />
DateModified = @DateNow<br />
WHERE<br />
ID = @ElementID<br />
<br />
if @@ERROR = 0<br />
begin<br />
set @NewDateModified = @DateNow<br />
set @Retval = 0<br />
end<br />
else<br />
begin<br />
set @Retval = 2<br />
end<br />
<br />
-- log this event<br />
exec LogEvent @UserID, 1, 1<br />
<br />
select @Retval<br />
RETURN <br />
Unfortunately the NewDateModified does not contain what I expect, eventhough I've created a SqlParameter object with the correct Direction. I guess the problem is somewhere in my SQL code.
Any help is greatly appreceated.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
At a guess, @@ERROR isn't being set to 0.
Have you tried setting the value outside the IF to see if it gets returned?
Michael
CP Blog [^]
|
|
|
|
|
Hi Michael,
thanks for your response. Yes, I've thought about this as well. But even putting it outside the IF doesn't yield correct results. I'm posting an extract of the code which is actually executing the SP here. Maybe I'm just blind...
<br />
int nResult = 0;<br />
DateTime dtResult = new DateTime(0);<br />
SqlCommand cmd = new SqlCommand(ProcName, Open());<br />
<br />
try {<br />
cmd.CommandType = System.Data.CommandType.StoredProcedure;<br />
<br />
-- do stuff and and all params...<br />
cmd.Parameters.Add(new SqlParameter("ElementID", ElementID));<br />
cmd.Parameters.Add(new SqlParameter("DateModified", DateModified));<br />
<br />
SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult);<br />
prmNewDtModif.Direction = System.Data.ParameterDirection.Output;<br />
cmd.Parameters.Add(prmNewDtModif);<br />
<br />
nResult = (Int32) cmd.ExecuteScalar();<br />
<br />
}<br />
<br />
I've just put the stuff in which seems to be relevant. Variable names are selfexplanatory I guess.
Any ideas?
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Matthias Steinbart wrote:
SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult);
I'm not au-fait with all the ins and outs of ADO.NET but shouldn't the Parameter name match the stored procedure?
i.e @NewDateModified rather than NewDateModified
Michael
CP Blog [^]
|
|
|
|
|
Post your ADO code where you get NewDateModified out of the command object...
DTrent
|
|
|
|
|
Hi DTrent!
Thanks for your reply. Your sole proposal has answered my question ("out of the command object..."???  )
I assumed, that the value gets automatically assigned once the SqlCommand.ExecuteScalar() call returns. I didn't know that I have to retrieve the SqlParameter.Value property and assign it then to the variable which I passed to that parameter. Seems kind of weird to me, in the end I'm asking why am I assigning the variable in the first place?
For those interested, here the code:
<br />
int nResult = 0;<br />
DateTime dtResult = new DateTime(0);<br />
SqlCommand cmd = new SqlCommand(ProcName, Open());<br />
<br />
try {<br />
cmd.CommandType = System.Data.CommandType.StoredProcedure;<br />
<br />
-- do stuff and and all params...<br />
cmd.Parameters.Add(new SqlParameter("ElementID", ElementID));<br />
cmd.Parameters.Add(new SqlParameter("DateModified", DateModified));<br />
<br />
SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult);<br />
prmNewDtModif.Direction = System.Data.ParameterDirection.Output;<br />
cmd.Parameters.Add(prmNewDtModif);<br />
<br />
nResult = (Int32) cmd.ExecuteScalar();<br />
<br />
}<br />
<br />
<br />
<br />
<br />
if (dtResult < dtSomething) {<br />
}<br />
Thanks for your help!
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Does anyone know how to automate a trace that constrains on the DBID?
I have created two scripts, one to create a job that is sceduled to start when the sql agent starts. It contains one step that executes the stored procedure that contains the trace definition and creates and starts the trace on the target server and database.
The problem is when I constrain on the dbid everything gets created and the job starts successfully but nothing is written to the trace file. The only events captured by this trace are login, logout and failed logins. The scripts work if I remove the dbid constraint.
D
dsa
|
|
|
|
|
Hello, not even sure if this is the right forum for this question but here
goes..
I'm writing a NET Data Provider for our DBMS and got everything working
except one thing. Populating a DataGrid..
All other parts of the Data Provider framework are all interface-based and
therefore rather easy to implement/override as needed, but the DataTable
isn't!
So when my TTDataTable (derived from DataTable) gets notified over TCP/IP
that a cell has changed it's value and I update the DataTable using
something like:
Rows[updateRow.RowId][cell.Ordinal] = cell.Value == null ? DBNull.Value : cell.Value;
Sooner or later the application will throw an exception somwhere in
System.Windows.Forms.dll whith no callstack at all, very ennoying.. 
This happens I _think_ is because the DataTable implementation does not do
something like this when firing the event:
<br />
<someevent>Handler eventTarget = <someevent>;<br />
if ( eventTarget != null )<br />
{<br />
if ( eventTarget.Target is ISynchronizeInvoke )<br />
{<br />
ISynchronizeInvoke target = eventTarget.Target as ISynchronizeInvoke;<br />
target.BeginInvoke( eventTarget, new object[]{ this<,more params if needed> } );<br />
}<br />
else<br />
{<br />
eventTarget( this, e );<br />
}<br />
One solution would be to throw the event myself in the OnRowChanged method,
but unfortunatly the onRowChangedDelegate (and also the rest of the event)
seems to be private, so...
Another solution would be that I have totally missunderstood how to properly
deriving from a DataTable and populating/updating it from another thread and
if so, I really hope someone here can show me how.
Or *gasp* could it be a bug/bad coding/feature from MS? o.O
BTW when not adding the TTDataTable to a grid but instead simply sinking the
events and tracing the events using Debug.WriteLine or to a console, it
works, thats why I think it's a "updating a form from the wrong
thread"-bug..
I have tried this
<br />
public class TTDataTable : System.Data.DataTable<br />
{<br />
.<br />
.<br />
.<br />
private void Process( UpdateRow updateRow )<br />
{<br />
System.Diagnostics.Debug.WriteLine( "UpdateRow" );<br />
foreach ( CellInformation cell in updateRow.Cells )<br />
{<br />
Rows[updateRow.RowId][cell.Ordinal] = cell.Value == null ? DBNull.Value : cell.Value;<br />
}<br />
}<br />
}<br />
and
<br />
protected override void OnRowChanged(System.Data.DataRowChangeEventArgs e)<br />
{<br />
System.Data.DataRowChangeEventHandler eventTarget = onRowChangedDelegate;<br />
if ( eventTarget != null )<br />
{<br />
if ( eventTarget.Target is ISynchronizeInvoke )<br />
{<br />
ISynchronizeInvoke target = eventTarget.Target as ISynchronizeInvoke;<br />
target.BeginInvoke( eventTarget, new object[]{ this, e } );<br />
}<br />
else<br />
{<br />
eventTarget( this, e );<br />
}<br />
}<br />
}<br />
Problem with the above is this error: D:\Projects\Abaris\TrueTime Solutions
AB\TT 7.0\CLR\Abaris\TrueTimeSolutions\Clients\Data
Provider\TTDataTable.cs(139): 'System.Data.DataTable.onRowChangedDelegate'
is inaccessible due to its protection level
Or in a nutshell, as a .NET Data Provider, how do I make it possible to
display data from our eventbased Realtime DBMS in a plain vanilla DataGrid?
I know I can also write our own datagrid, but why reinvent the wheel...
TIA/
Michael Hulthin
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
