How to create SQL script out of job in SQL Server ?
I know there is a way thru EM, but is there any stored procedure or any function that will return the SQL script of any job.
I want to script all jobs on my server just as a part backing up mechanism.
Thanks
Decasto.After starting Profiler, going into EM and scripting a job I noticed two procs being called...
msdb..sp_help_jobstep @.job_id = XXX
and
msdb..sp_help_jobschedule @.job_id = XXX
soooo I suppose you could step through msdb..sysjobs and process each job you find with one or both of the above procs.
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
How to schedule a Stored Procedure to run nightly in SQL Server
Hello.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.
Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.
sql
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.
Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.
sql
How to schedule a Stored Procedure to run nightly in SQL Server
Hello.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.|||Thanks very much. It is so simple I feel silly.
"Alejandro Mesa" wrote:
> Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
> use EM to do this. Open the server group, locate the server, open it, go to
> management, right click on jobs and select "New job".
>
> AMB
> "Dave Pylatuk" wrote:
> > Hello.
> >
> > What is the easiest way to schedule a stored procedure to run nightly at a
> > specified time ?
> >
> > Thanks in advance.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.|||Thanks very much. It is so simple I feel silly.
"Alejandro Mesa" wrote:
> Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
> use EM to do this. Open the server group, locate the server, open it, go to
> management, right click on jobs and select "New job".
>
> AMB
> "Dave Pylatuk" wrote:
> > Hello.
> >
> > What is the easiest way to schedule a stored procedure to run nightly at a
> > specified time ?
> >
> > Thanks in advance.
How to schedule a Stored Procedure to run nightly in SQL Server
Hello.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.
How to schedule a stored procedure to run everyday
Hi
I have a stored procedure in Seever 2005 that i want to run everynight .
Whats the best way to do this. (? windows scheduler)
Can some one give me directions how to do it
Thanks
Generally you would use SQL Server Agent and schedule a job to run the stored procedure every night. If you are using the Express edition, you would need to use Windows Schedule as SQL Agent is not available with that edition. If you look up Jobs in Books Online, you will find information on doing this. Check the how to topics to walk you through the steps for setting up and executing jobs: http://msdn2.microsoft.com/en-us/library/ms189880.aspx
-Sue
|||Ok i will give it a try and let you know
Thanks for your help
How to schedule a stored procedure
I'm new to sql server programing. Please help me to scedule a stored
procedure to run every hour. Thnak you all.No problem, just set up a new job with s step "TSQL" execution. You can
schedule that like any other jon in SQL Server.
procedure to run every hour. Thnak you all.No problem, just set up a new job with s step "TSQL" execution. You can
schedule that like any other jon in SQL Server.
EXEC SPSomeProcedure
HTH, jens Suessmeyer.|||Can u please illustrate it more ? How ro set up a new job ?|||MSDN is your friend:
http://msdn.microsoft.com/library/d...tomaem_19is.asp
HTH, jens Suessmeyer.|||"Ranjit" <mahalwarawa@.gmail.com> wrote in message
news:1135231814.534191.117420@.g44g2000cwa.googlegr oups.com...
> I'm new to sql server programing. Please help me to scedule a stored
> procedure to run every hour. Thnak you all.
Use the SQL Server Agent. Best bet in your case may be to look for it in
the Enterprise Manager GUI.
How to save the SP result?
Hi!
How can i save the resultant recordset generated by an Stored Procedure to
another table (with the same structure) ?
I think its some like INSERT * INTO tmptable FROM **Exec mySProc**
Can you help my?
Thanxs, Gabriel.Hi Gabriel
You're close...
You just don't need the * or the FROM.
Make sure the Stored Proc only returns one set of rows.
INSERT INTO tmptable
EXEC myProc
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gabriel South" <gsouth@.hotmail.com> wrote in message
news:Ore5y9iBFHA.2984@.TK2MSFTNGP11.phx.gbl...
> Hi!
> How can i save the resultant recordset generated by an Stored Procedure to
> another table (with the same structure) ?
> I think its some like INSERT * INTO tmptable FROM **Exec mySProc**
> Can you help my?
> Thanxs, Gabriel.
>
>
How can i save the resultant recordset generated by an Stored Procedure to
another table (with the same structure) ?
I think its some like INSERT * INTO tmptable FROM **Exec mySProc**
Can you help my?
Thanxs, Gabriel.Hi Gabriel
You're close...
You just don't need the * or the FROM.
Make sure the Stored Proc only returns one set of rows.
INSERT INTO tmptable
EXEC myProc
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gabriel South" <gsouth@.hotmail.com> wrote in message
news:Ore5y9iBFHA.2984@.TK2MSFTNGP11.phx.gbl...
> Hi!
> How can i save the resultant recordset generated by an Stored Procedure to
> another table (with the same structure) ?
> I think its some like INSERT * INTO tmptable FROM **Exec mySProc**
> Can you help my?
> Thanxs, Gabriel.
>
>
Monday, March 26, 2012
how to save a stored procedure with Management Studio?
Hi,
i can make and save a stored procedure in Visual Web Developer (via Database Explorer). It appears then in the list op stored procedure in Management Sudio.
But how to do the same in Management Studio? When i make a sp and i want to save it, Management Studio asks me a name, but put the file in a Projects directory in 'My documents'. It never appears in the list of sp.
Thanks
tartuffe
Simple execute the script. When you make a SP in SSMS, it generates the "Create" or "Alter" statement, so executing the script will not execute the stored procedure, it will create or alter it, accordingly.
|||Hey,
Try clicking execute instead of the save icon, as the two work differently. Management Studio uses Execute to actually execute against the database, where VWD uses the save button to perform that action, for whatever reason that may be.
|||Thanks, it works.
how to run this proc in SQL Analyzer?
hi, guys
I have a stored procedure, like that:
CREATE PROCEDURE [dbo].[ViewTitles]
@.UID int,
@.DateStart datetime,
@.DateEnd datetime,
........
When I run this query in SQL Analyzer like this:
ViewTitles 6165, '2006-01-29 10:00:00', '2006-02-29 10:00:00'
It alwasy shows:
Error converting data type varchar to datetime.
If I run it in code, asp.net, or report, no problem at all.
What is wrong with that?
Thanks.
What is the dateformat of the session when logged in through ISQLW? You can verify this by looking at output of DBCC USEROPTIONS. Look for set option "dateformat". In addition to this, you should one of the ISO 8601 formats for datetime literals that should be interpreted correctly irrespective of the language or dateformat settings. Specify the value instead like '2006-01-29T10:00:00'.
Friday, March 23, 2012
how to run the package trough the stored prcedure
hi focks;
through the jobs is possible it s working fine but
is it possible to run the packages through the stired procedure 2005
ok if possible how please help me
regards
koti
You need to use xp_cmdshell to launch DTEXEC.
How to run the crystal report
Hi !
i am new to the crystal report.
can u help me?
how to run the crystal report?
tell me the procedure detail
rgds
anitaHi!
1) Insert the Crystal Report Runtime Control Properties from Project Properties.
2) Add the New Crystal Report using Project Explorer. when u click right button it will be displayed.
3) Choose the Report type in Crystal Report Gallery
4)Click the Project Button and Select the ODBC or OLEDB for your database.
5) And follow the continued.
6) then run your project.
Dim Report As New CrystalReport1
Private Sub Form_Load()
Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault
End Sub
Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth
End Sub|||I have example code in my signature - "VB6/Crystal Report Ex."
i am new to the crystal report.
can u help me?
how to run the crystal report?
tell me the procedure detail
rgds
anitaHi!
1) Insert the Crystal Report Runtime Control Properties from Project Properties.
2) Add the New Crystal Report using Project Explorer. when u click right button it will be displayed.
3) Choose the Report type in Crystal Report Gallery
4)Click the Project Button and Select the ODBC or OLEDB for your database.
5) And follow the continued.
6) then run your project.
Dim Report As New CrystalReport1
Private Sub Form_Load()
Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault
End Sub
Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth
End Sub|||I have example code in my signature - "VB6/Crystal Report Ex."
How to run system stored procedure
Hi,
After deleting the Merge Publication from Replication, Publications,
there are still remains of the publication in the database. (the
rowguid column in every table and the shared sign on the database
icon) We would like to remove them without creating a new database. It
looks like running the sp_removedbreplication is the best option
here. We are trying to run the procedure but cannot find how to. Books
online not very helpful.
Please help!
Martin
Martin,
the syntax is exec sp_removedbreplication 'yourdatabasename'. The only thing
you need to be careful of is that it will remove all traces of replication -
if this database is used as a publisher for several publications and a
subscriber also, all this will be removed. Sometimes this procedure doesn't
remove all system objects, and Hilary Cotter has previously posted up his
own script (on http://www.ava.co.uk) that will do the extra bits, but I'd
try sp_removedbreplication first.
HTH,
Paul Ibison
|||in your publication database issue a sp_replicationdboption 'merge','false'
This will get rid of most of the merge replication metadata and unshare your
database is you don't have any transactional or snapshot publications
present there. To get rid of the transactional publications issue a
sp_replicationdboption 'PublicationDatabaseName', 'publish', 'false'
sp_replicationdboption 'PublicationDatabaseName', 'merge publish', 'false'
Getting rid of the Rowguid columns is more complex.
Try this
select 'alter table ['+sysusers.name+'].'+sysobjects.name +' drop column
rowguid'+char(13)+'GO' from sysobjects, sysusers, syscolumns where
sysobjects.type='u'
and sysobjects.uid=sysusers.uid
and syscolumns.name='rowguid'
and syscolumns.id=sysobjects.id
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:3hc9j09ufhe2v7nonkpmnrbafr4i5qk011@.4ax.com...
> Hi,
> After deleting the Merge Publication from Replication, Publications,
> there are still remains of the publication in the database. (the
> rowguid column in every table and the shared sign on the database
> icon) We would like to remove them without creating a new database. It
> looks like running the sp_removedbreplication is the best option
> here. We are trying to run the procedure but cannot find how to. Books
> online not very helpful.
> Please help!
> Martin
>
After deleting the Merge Publication from Replication, Publications,
there are still remains of the publication in the database. (the
rowguid column in every table and the shared sign on the database
icon) We would like to remove them without creating a new database. It
looks like running the sp_removedbreplication is the best option
here. We are trying to run the procedure but cannot find how to. Books
online not very helpful.
Please help!
Martin
Martin,
the syntax is exec sp_removedbreplication 'yourdatabasename'. The only thing
you need to be careful of is that it will remove all traces of replication -
if this database is used as a publisher for several publications and a
subscriber also, all this will be removed. Sometimes this procedure doesn't
remove all system objects, and Hilary Cotter has previously posted up his
own script (on http://www.ava.co.uk) that will do the extra bits, but I'd
try sp_removedbreplication first.
HTH,
Paul Ibison
|||in your publication database issue a sp_replicationdboption 'merge','false'
This will get rid of most of the merge replication metadata and unshare your
database is you don't have any transactional or snapshot publications
present there. To get rid of the transactional publications issue a
sp_replicationdboption 'PublicationDatabaseName', 'publish', 'false'
sp_replicationdboption 'PublicationDatabaseName', 'merge publish', 'false'
Getting rid of the Rowguid columns is more complex.
Try this
select 'alter table ['+sysusers.name+'].'+sysobjects.name +' drop column
rowguid'+char(13)+'GO' from sysobjects, sysusers, syscolumns where
sysobjects.type='u'
and sysobjects.uid=sysusers.uid
and syscolumns.name='rowguid'
and syscolumns.id=sysobjects.id
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:3hc9j09ufhe2v7nonkpmnrbafr4i5qk011@.4ax.com...
> Hi,
> After deleting the Merge Publication from Replication, Publications,
> there are still remains of the publication in the database. (the
> rowguid column in every table and the shared sign on the database
> icon) We would like to remove them without creating a new database. It
> looks like running the sp_removedbreplication is the best option
> here. We are trying to run the procedure but cannot find how to. Books
> online not very helpful.
> Please help!
> Martin
>
How to run my Curser more faster?
I have a stored procedure written for number of updates, inserts to the
database. This stored procedure takes too long to run. I have created few
indexes around few tables, but the response time is not that defferent.
THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
another stored procedure is called within this Curser. I found that lot of
time been taken to run this Curser. So I want to run/make it faster the
curser. So how should I improve the Curser to run more faster
I would greatly appreciate anyone's idea or suggesions.
thanks
raj
"raj" <raj@.discussions.microsoft.com> wrote in message
news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> I have a stored procedure written for number of updates, inserts to the
> database. This stored procedure takes too long to run. I have created few
> indexes around few tables, but the response time is not that defferent.
> THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
> another stored procedure is called within this Curser. I found that lot of
> time been taken to run this Curser. So I want to run/make it faster the
> curser. So how should I improve the Curser to run more faster
> I would greatly appreciate anyone's idea or suggesions.
Best Advice: Eliminate the Cursor.
But, without a DDL and some sample table folks here won't be able to help
you with that.
So, if you can, post your DDL and some data and see what folks can do.
> thanks
> raj
|||this is the part of the query takes too long to run
BEGIN
DECLARE CustCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT D.CustID FROM tblInv D INNER JOIN #Invoice I ON D.InvID =
I.InvID
OPEN CustCursor
FETCH NEXT FROM CustCursor INTO @.CustID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
EXEC sp_UpdateDunning @.CustID
IF @.@.Error <> 0
BEGIN
CLOSE CustCursor
DEALLOCATE CustCursor
ROLLBACK
RAISERROR('Error executing - > sp_UpdateDunning.', 16, 1)
RETURN
END
FETCH NEXT FROM CustCursor INTO @.CustID
END
CLOSE CustCursor
DEALLOCATE CustCursor
END
All the tables involved in this curser, indexes have been created.
raj
"Greg D. Moore (Strider)" wrote:
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> Best Advice: Eliminate the Cursor.
> But, without a DDL and some sample table folks here won't be able to help
> you with that.
> So, if you can, post your DDL and some data and see what folks can do.
>
>
>
|||The following approach would sacrify the transactional
opertion on the batch, but is likely the fast.
-- ===============
-- Run the script at QA
-- ===============
declare @.CustID int, @.cmd varchar(255)
delcare tmp_cur cursor for
select distinct D.CustID
from tblInv D inner join #Invoice I
on D.InvID = I.InvID
open tmp_cur
fetch next from tmp_cur into @.CustID
while @.@.fetch_status = 0
begin
select @.cmd = 'exec sp_UpdateDunning ' + convert
(varchar, @.CustID)
print @.cmd
print 'go'
fetch next from tmp_cur into @.CustID
end
close tmp_cur
deallocate tmp_cur
-- ===============
-- Run the result of script at QA
-- ===============
For example:
exec sp_UpdateDunning 1
go
exec sp_UpdateDunning 2
go
...
^^
Regards,
Alfred
>--Original Message--
>this is the part of the query takes too long to run
>----
>BEGIN
>DECLARE CustCursor CURSOR FAST_FORWARD FOR
>SELECT DISTINCT D.CustID FROM tblInv D
INNER JOIN #Invoice I ON D.InvID =
>I.InvID
>OPEN CustCursor
>FETCH NEXT FROM CustCursor INTO @.CustID
>WHILE (@.@.FETCH_STATUS <> -1)
>BEGIN
>EXEC sp_UpdateDunning @.CustID
>IF @.@.Error <> 0
>BEGIN
>CLOSE CustCursor
>DEALLOCATE CustCursor
>ROLLBACK
>RAISERROR('Error executing - >
sp_UpdateDunning.', 16, 1)
>RETURN
>END
>FETCH NEXT FROM CustCursor INTO @.CustID
>END
>CLOSE CustCursor
>DEALLOCATE CustCursor
>END
>----
--
>All the tables involved in this curser, indexes have been
created.[vbcol=seagreen]
>raj
>
>"Greg D. Moore (Strider)" wrote:
3E84EE32303A@.microsoft.com...[vbcol=seagreen]
updates, inserts to the[vbcol=seagreen]
run. I have created few[vbcol=seagreen]
not that defferent.[vbcol=seagreen]
stored procedure, and[vbcol=seagreen]
Curser. I found that lot of[vbcol=seagreen]
run/make it faster the[vbcol=seagreen]
more faster[vbcol=seagreen]
suggesions.[vbcol=seagreen]
won't be able to help[vbcol=seagreen]
what folks can do.
>.
>
database. This stored procedure takes too long to run. I have created few
indexes around few tables, but the response time is not that defferent.
THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
another stored procedure is called within this Curser. I found that lot of
time been taken to run this Curser. So I want to run/make it faster the
curser. So how should I improve the Curser to run more faster
I would greatly appreciate anyone's idea or suggesions.
thanks
raj
"raj" <raj@.discussions.microsoft.com> wrote in message
news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> I have a stored procedure written for number of updates, inserts to the
> database. This stored procedure takes too long to run. I have created few
> indexes around few tables, but the response time is not that defferent.
> THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
> another stored procedure is called within this Curser. I found that lot of
> time been taken to run this Curser. So I want to run/make it faster the
> curser. So how should I improve the Curser to run more faster
> I would greatly appreciate anyone's idea or suggesions.
Best Advice: Eliminate the Cursor.
But, without a DDL and some sample table folks here won't be able to help
you with that.
So, if you can, post your DDL and some data and see what folks can do.
> thanks
> raj
|||this is the part of the query takes too long to run
BEGIN
DECLARE CustCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT D.CustID FROM tblInv D INNER JOIN #Invoice I ON D.InvID =
I.InvID
OPEN CustCursor
FETCH NEXT FROM CustCursor INTO @.CustID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
EXEC sp_UpdateDunning @.CustID
IF @.@.Error <> 0
BEGIN
CLOSE CustCursor
DEALLOCATE CustCursor
ROLLBACK
RAISERROR('Error executing - > sp_UpdateDunning.', 16, 1)
RETURN
END
FETCH NEXT FROM CustCursor INTO @.CustID
END
CLOSE CustCursor
DEALLOCATE CustCursor
END
All the tables involved in this curser, indexes have been created.
raj
"Greg D. Moore (Strider)" wrote:
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> Best Advice: Eliminate the Cursor.
> But, without a DDL and some sample table folks here won't be able to help
> you with that.
> So, if you can, post your DDL and some data and see what folks can do.
>
>
>
|||The following approach would sacrify the transactional
opertion on the batch, but is likely the fast.
-- ===============
-- Run the script at QA
-- ===============
declare @.CustID int, @.cmd varchar(255)
delcare tmp_cur cursor for
select distinct D.CustID
from tblInv D inner join #Invoice I
on D.InvID = I.InvID
open tmp_cur
fetch next from tmp_cur into @.CustID
while @.@.fetch_status = 0
begin
select @.cmd = 'exec sp_UpdateDunning ' + convert
(varchar, @.CustID)
print @.cmd
print 'go'
fetch next from tmp_cur into @.CustID
end
close tmp_cur
deallocate tmp_cur
-- ===============
-- Run the result of script at QA
-- ===============
For example:
exec sp_UpdateDunning 1
go
exec sp_UpdateDunning 2
go
...
^^
Regards,
Alfred
>--Original Message--
>this is the part of the query takes too long to run
>----
>BEGIN
>DECLARE CustCursor CURSOR FAST_FORWARD FOR
>SELECT DISTINCT D.CustID FROM tblInv D
INNER JOIN #Invoice I ON D.InvID =
>I.InvID
>OPEN CustCursor
>FETCH NEXT FROM CustCursor INTO @.CustID
>WHILE (@.@.FETCH_STATUS <> -1)
>BEGIN
>EXEC sp_UpdateDunning @.CustID
>IF @.@.Error <> 0
>BEGIN
>CLOSE CustCursor
>DEALLOCATE CustCursor
>ROLLBACK
>RAISERROR('Error executing - >
sp_UpdateDunning.', 16, 1)
>RETURN
>END
>FETCH NEXT FROM CustCursor INTO @.CustID
>END
>CLOSE CustCursor
>DEALLOCATE CustCursor
>END
>----
--
>All the tables involved in this curser, indexes have been
created.[vbcol=seagreen]
>raj
>
>"Greg D. Moore (Strider)" wrote:
3E84EE32303A@.microsoft.com...[vbcol=seagreen]
updates, inserts to the[vbcol=seagreen]
run. I have created few[vbcol=seagreen]
not that defferent.[vbcol=seagreen]
stored procedure, and[vbcol=seagreen]
Curser. I found that lot of[vbcol=seagreen]
run/make it faster the[vbcol=seagreen]
more faster[vbcol=seagreen]
suggesions.[vbcol=seagreen]
won't be able to help[vbcol=seagreen]
what folks can do.
>.
>
Wednesday, March 21, 2012
How to run a stored procedure on SQL 2005 remote server?
hi,
i want to run one stored procedure on a remote SQL Server 2005, from
my machine.
can any one explain how to do this?
thanks
Kartheek
Hi Kartheek
"karthee" wrote:
> hi,
> i want to run one stored procedure on a remote SQL Server 2005, from
> my machine.
> can any one explain how to do this?
> thanks
> Kartheek
>
You don't say which edition of SQL Server 2005 you are using? If you have
SSMS then you can either connect directly to the server and run the
procedure, or create a linked server and run the procedure using four part
names or OPENQUERY etc. for more information check out books online. If you
have SQL Express you could use SQLCMD.
John
i want to run one stored procedure on a remote SQL Server 2005, from
my machine.
can any one explain how to do this?
thanks
Kartheek
Hi Kartheek
"karthee" wrote:
> hi,
> i want to run one stored procedure on a remote SQL Server 2005, from
> my machine.
> can any one explain how to do this?
> thanks
> Kartheek
>
You don't say which edition of SQL Server 2005 you are using? If you have
SSMS then you can either connect directly to the server and run the
procedure, or create a linked server and run the procedure using four part
names or OPENQUERY etc. for more information check out books online. If you
have SQL Express you could use SQLCMD.
John
How to run a stored procedure on SQL 2005 remote server?
hi,
i want to run one stored procedure on a remote SQL Server 2005, from
my machine.
can any one explain how to do this?
thanks
KartheekHi Kartheek
"karthee" wrote:
> hi,
> i want to run one stored procedure on a remote SQL Server 2005, from
> my machine.
> can any one explain how to do this?
> thanks
> Kartheek
>
You don't say which edition of SQL Server 2005 you are using? If you have
SSMS then you can either connect directly to the server and run the
procedure, or create a linked server and run the procedure using four part
names or OPENQUERY etc. for more information check out books online. If you
have SQL Express you could use SQLCMD.
John
i want to run one stored procedure on a remote SQL Server 2005, from
my machine.
can any one explain how to do this?
thanks
KartheekHi Kartheek
"karthee" wrote:
> hi,
> i want to run one stored procedure on a remote SQL Server 2005, from
> my machine.
> can any one explain how to do this?
> thanks
> Kartheek
>
You don't say which edition of SQL Server 2005 you are using? If you have
SSMS then you can either connect directly to the server and run the
procedure, or create a linked server and run the procedure using four part
names or OPENQUERY etc. for more information check out books online. If you
have SQL Express you could use SQLCMD.
John
How to run a stored procedure on SQL 2005 remote server?
hi,
i want to run one stored procedure on a remote SQL Server 2005, from
my machine.
can any one explain how to do this?
thanks
KartheekHi Kartheek
"karthee" wrote:
> hi,
> i want to run one stored procedure on a remote SQL Server 2005, from
> my machine.
> can any one explain how to do this?
> thanks
> Kartheek
>
You don't say which edition of SQL Server 2005 you are using? If you have
SSMS then you can either connect directly to the server and run the
procedure, or create a linked server and run the procedure using four part
names or OPENQUERY etc. for more information check out books online. If you
have SQL Express you could use SQLCMD.
John
i want to run one stored procedure on a remote SQL Server 2005, from
my machine.
can any one explain how to do this?
thanks
KartheekHi Kartheek
"karthee" wrote:
> hi,
> i want to run one stored procedure on a remote SQL Server 2005, from
> my machine.
> can any one explain how to do this?
> thanks
> Kartheek
>
You don't say which edition of SQL Server 2005 you are using? If you have
SSMS then you can either connect directly to the server and run the
procedure, or create a linked server and run the procedure using four part
names or OPENQUERY etc. for more information check out books online. If you
have SQL Express you could use SQLCMD.
John
How to run a stored procedure by schedule
Is there a way to run a stored procedure based on schedule like every 8p.m everyday?schedule a Job, in this job executes your Stored Procedure:
in the option tree of Enterprise management, you selects: Management -> SQL Server Agent -> Jobs, create a new job, in the second tab select :
Type = Transact-SQL Script
DataBase = the database of the Stored Procedure
Command = You Stored Procedure
In the next tab "schedule" select the option "Recurring" and press button "Change"
and it programs your schedule.
Note: The SQL Server Agent has to be working.
Monday, March 19, 2012
How to ROLLBACK TRANSACTION on client level
Suppose i connect to db (SQL Server) from the client (.NET) and call to
some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
and before COMMIT or RALLBACK an error happen that imidietly stop the
execution of stored procedure, In the client i cacth this error but what
about an open transaction ?
HOW to rollback in the client?
Message posted via http://www.webservertalk.comYou can wrap your sp inside another one, get @.@.trancount before calling
second sp and compare after the call.
create procedure dbo.proc1
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.error int
begin transaction
insert into t1 values(@.p1, @.p2)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 1.', 16, 1)
return 1
end
insert into t2 values(@.p1)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 2.', 16, 1)
return 1
end
else
commit transaction
return @.@.error
go
create procedure dbo.proc2
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.tc int
declare @.rv int
declare @.error int
set @.tc = @.@.transcount
exec @.rv = dbo.proc1 @.p1, @.p2
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.tc != @.@.trancount
rollback transaction
return @.error
go
Call proc2 from your client app, instead calling proc1.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"E B via webservertalk.com" wrote:
> Suppose i connect to db (SQL Server) from the client (.NET) and call to
> some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
> and before COMMIT or RALLBACK an error happen that imidietly stop the
> execution of stored procedure, In the client i cacth this error but what
> about an open transaction ?
> HOW to rollback in the client?
> --
> Message posted via http://www.webservertalk.com
>|||thanks. However i find somthing more intresting, in my app i'm using
ADO.NET so when i close a connection (conection to db) with ADO.NET method
close() it rolls back any pending transactions.
Message posted via http://www.webservertalk.com
some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
and before COMMIT or RALLBACK an error happen that imidietly stop the
execution of stored procedure, In the client i cacth this error but what
about an open transaction ?
HOW to rollback in the client?
Message posted via http://www.webservertalk.comYou can wrap your sp inside another one, get @.@.trancount before calling
second sp and compare after the call.
create procedure dbo.proc1
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.error int
begin transaction
insert into t1 values(@.p1, @.p2)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 1.', 16, 1)
return 1
end
insert into t2 values(@.p1)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 2.', 16, 1)
return 1
end
else
commit transaction
return @.@.error
go
create procedure dbo.proc2
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.tc int
declare @.rv int
declare @.error int
set @.tc = @.@.transcount
exec @.rv = dbo.proc1 @.p1, @.p2
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.tc != @.@.trancount
rollback transaction
return @.error
go
Call proc2 from your client app, instead calling proc1.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"E B via webservertalk.com" wrote:
> Suppose i connect to db (SQL Server) from the client (.NET) and call to
> some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
> and before COMMIT or RALLBACK an error happen that imidietly stop the
> execution of stored procedure, In the client i cacth this error but what
> about an open transaction ?
> HOW to rollback in the client?
> --
> Message posted via http://www.webservertalk.com
>|||thanks. However i find somthing more intresting, in my app i'm using
ADO.NET so when i close a connection (conection to db) with ADO.NET method
close() it rolls back any pending transactions.
Message posted via http://www.webservertalk.com
How to return value from Stored Procedure(SQL Server) into c
Hi,
Yes. Exactly we are using the ADODB parameters. Here with i have
attached the coded snippet.
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
adParamInput, 64, vstrSalesOrderNo));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
adParamInput,
200, vstrToolName));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
adParamInput, 64, vstrToolAddress));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
,
&viResult));
pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
pRs->Close();
_variant_t v;
v.vt = VT_BSTR;
v.bstrVal =_bstr_t("Output");
_variant_t vResult;
vResult = pCommand->Parameters->GetItem(v)->Value;
Here when we debug vResult, it shows VT_EMPTY.
Does this code has issues with this ?
Thanks in Advance,
Ravindran.Hi Ravi
i think u can try vResult = pCommand(0);
i am not sure if it works in VC, but this is the was i do in VB
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ravip" wrote:
> Hi,
> Yes. Exactly we are using the ADODB parameters. Here with i have
> attached the coded snippet.
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
> adParamInput, 64, vstrSalesOrderNo));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
> adParamInput,
> 200, vstrToolName));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
> adParamInput, 64, vstrToolAddress));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
> ,
> &viResult));
>
> pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
> pRs->Close();
> _variant_t v;
> v.vt = VT_BSTR;
> v.bstrVal =_bstr_t("Output");
> _variant_t vResult;
> vResult = pCommand->Parameters->GetItem(v)->Value;
> Here when we debug vResult, it shows VT_EMPTY.
> Does this code has issues with this ?
> Thanks in Advance,
> Ravindran.
>
>
Yes. Exactly we are using the ADODB parameters. Here with i have
attached the coded snippet.
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
adParamInput, 64, vstrSalesOrderNo));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
adParamInput,
200, vstrToolName));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
adParamInput, 64, vstrToolAddress));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
,
&viResult));
pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
pRs->Close();
_variant_t v;
v.vt = VT_BSTR;
v.bstrVal =_bstr_t("Output");
_variant_t vResult;
vResult = pCommand->Parameters->GetItem(v)->Value;
Here when we debug vResult, it shows VT_EMPTY.
Does this code has issues with this ?
Thanks in Advance,
Ravindran.Hi Ravi
i think u can try vResult = pCommand(0);
i am not sure if it works in VC, but this is the was i do in VB
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ravip" wrote:
> Hi,
> Yes. Exactly we are using the ADODB parameters. Here with i have
> attached the coded snippet.
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
> adParamInput, 64, vstrSalesOrderNo));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
> adParamInput,
> 200, vstrToolName));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
> adParamInput, 64, vstrToolAddress));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
> ,
> &viResult));
>
> pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
> pRs->Close();
> _variant_t v;
> v.vt = VT_BSTR;
> v.bstrVal =_bstr_t("Output");
> _variant_t vResult;
> vResult = pCommand->Parameters->GetItem(v)->Value;
> Here when we debug vResult, it shows VT_EMPTY.
> Does this code has issues with this ?
> Thanks in Advance,
> Ravindran.
>
>
How to return time & number format that has set in the regional setting using stored proce
How to return time & number format that has set in the regional setting using stored procedure.
Following is my sp for getting current date format from Sql Server.
if exists
(select*fromsysobjectswhereid =object_id(N'[HSP_GetDateFormat]')andOBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure[HSP_GetDateFormat]
create procedure
HSP_GetDateFormat(
@.strDateFormat nvarchar(64) out,
@.iErr
intout)
as
begin
set nocount on
set@.strDateFormat = (selectdateformat frommaster..syslanguageswherelangid = (selectvaluefrommaster..sysconfigureswherecomment = 'default language'))
set@.iErr = @.@.Error
set nocount off
end
Now, I want to know what would I write if I want to get currenttime &numberformat from Sql Server.
Hi,
From your description, it seems that you want to get the current date time in stored procedure, right?
TSQL has provided several built-in functions which is used for datetime and datetiem calculating. You can get the current date and time by using GetDate() function, also, you can use such functions like DATEADD,DATEDIFF to calculate the date and time. See the following codes:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))
Besides, if you want to output the value, just create a datetime typed variable and set it as OUTPUT, and then create a parameter in .NET side, which shares the same type, set the direction as OUTPUT, then you can receive the datetime from your stored procedure. As for the number type, you can use INT,FLOAT,DOUBLE and etc in your stored procedure.
Thanks.
Subscribe to:
Posts (Atom)