Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Server
> 2005. It also has SP1 applied to it. I was just checking the jobs, making
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>> Last month we upgraded our SQL Server 2000 production database to SQL
>> Server
>> 2005. It also has SP1 applied to it. I was just checking the jobs,
>> making
>> certain that everything was running OK. We have 7 jobs defined, when I
>> clicked on the View Job History link on each of the job's property page,
>> I
>> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
>> nothing
>> else mentioned. Here is the text of the error message:
>> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>>
>> Going to the link didn't help me.
>> So, how do I find out what's wrong and fix it, please?
>> Rod
>>
>>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> > Hi Rod
> >
> > Does this happen on every job? If it does then how did you upgrade the
> > jobs?
> >
> > You may want to manually see what is in the sysjobhistory table for that
> > job.
> >
> > You may want to use SQL Profiler to see what SQL is being called and check
> > that there is no errors.
> >
> > Try running DBCC CHECKDB on MSDB.
> >
> > If that does not work try scripting the job and creating it as a different
> > one, then disable the original.
> >
> > John
> >
> > "Rod" wrote:
> >
> >> Last month we upgraded our SQL Server 2000 production database to SQL
> >> Server
> >> 2005. It also has SP1 applied to it. I was just checking the jobs,
> >> making
> >> certain that everything was running OK. We have 7 jobs defined, when I
> >> clicked on the View Job History link on each of the job's property page,
> >> I
> >> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
> >> nothing
> >> else mentioned. Here is the text of the error message:
> >>
> >> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> >>
> >>
> >> Going to the link didn't help me.
> >>
> >> So, how do I find out what's wrong and fix it, please?
> >>
> >> Rod
> >>
> >>
> >>
> >>
> >>
>
>
Thursday, March 29, 2012
Getting a syntax error involving Microsoft.SqlServer.SmoEnum
Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job
.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Serv
er
> 2005. It also has SP1 applied to it. I was just checking the jobs, makin
g
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothi
ng
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...[vbcol=seagreen]
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
>
>
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job
.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Serv
er
> 2005. It also has SP1 applied to it. I was just checking the jobs, makin
g
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothi
ng
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...[vbcol=seagreen]
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
>
>
Getting a stored procedures return value -- URGENT !
We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>
getting a stored procedures code
I don't know if this is possible. However, what i am attempting to do is
using C#'s window forms. I open up an excell sheet stored in my windows
form. The excel sheet stores names of the stored procedures in that
database. I want to know if it's possible to click on that stored
procedure to open up a link to display the code of that stored procedure of
course in a read only mode.
using C#'s window forms. I open up an excell sheet stored in my windows
form. The excel sheet stores names of the stored procedures in that
database. I want to know if it's possible to click on that stored
procedure to open up a link to display the code of that stored procedure of
course in a read only mode.
any suggestions...Two possible options: Query the syscomments table (See Books Online for
details) or use the Script method in the SQLDMO API (a COM API, not
..NET).
--
David Portas
SQL Server MVP
--|||using sp_helptext <Stored Procedure Name>
enables you to see the stored procedure code
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------
*** Sent via Developersdex http://www.developersdex.com ***sql
getting a store procedure's result
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.c om...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
|||Use output parameters for SQL procedure from which you wish to assign the
values to the variables. (More information look for: "Returning Data Using a
Return Code" in SQL books online)
HTH
Saleem@.sqlnt.com
"Silvio" wrote:
> Hey guys,
> I have a store procedure that returns a recorset. Here's an example:
> create procedure ABC as
> --some code here that works with @.x and @.y.. and then the last line
> of the proc:
> SELECT @.x,@.y
> That procedure has been used only in a vb code, so they consume the
> result with no problem. Now I need to call that procedure within a
> different proc, and I need to get back the final values of @.x and @.y.
> Is there a way to get these results back in a variable as I call the
> store proc?
> Thanks,
>
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.c om...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
|||Use output parameters for SQL procedure from which you wish to assign the
values to the variables. (More information look for: "Returning Data Using a
Return Code" in SQL books online)
HTH
Saleem@.sqlnt.com
"Silvio" wrote:
> Hey guys,
> I have a store procedure that returns a recorset. Here's an example:
> create procedure ABC as
> --some code here that works with @.x and @.y.. and then the last line
> of the proc:
> SELECT @.x,@.y
> That procedure has been used only in a vb code, so they consume the
> result with no problem. Now I need to call that procedure within a
> different proc, and I need to get back the final values of @.x and @.y.
> Is there a way to get these results back in a variable as I call the
> store proc?
> Thanks,
>
getting a store procedure's result
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.com...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.com...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
Getting a step in a job to start another job in another database...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
NikThere are no jobs at the database level. Did you mean, starting a job on
another server?
All you need is the job name, and pass it to msdb..sp_start_job. If the job
is on a different server, then you could add a linked server to that server
and call ServerName.msdb..sp_start_job
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
Nik|||Spot on thanks. Its a job on another server that I am
trying to start. I have taken a look at books online to
see the syntax, might give you another shout here if I get
stuck!
Thanks a bunch,
Nik
starting a job on
msdb..sp_start_job. If the job
server to that server
a job to start another job in another database please...'
Cheers,
NikThere are no jobs at the database level. Did you mean, starting a job on
another server?
All you need is the job name, and pass it to msdb..sp_start_job. If the job
is on a different server, then you could add a linked server to that server
and call ServerName.msdb..sp_start_job
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
Nik|||Spot on thanks. Its a job on another server that I am
trying to start. I have taken a look at books online to
see the syntax, might give you another shout here if I get
stuck!
Thanks a bunch,
Nik
quote:
>--Original Message--
>There are no jobs at the database level. Did you mean,
starting a job on
quote:
>another server?
>All you need is the job name, and pass it to
msdb..sp_start_job. If the job
quote:
>is on a different server, then you could add a linked
server to that server
quote:
>and call ServerName.msdb..sp_start_job
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
>news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
>Hi, can anyone help me by telling me how to get a step in
>a job to start another job in another database please...'
>Cheers,
>Nik
>
>.
>
Subscribe to:
Posts (Atom)