How can I retrieve a function return value from an executed string?

Hello!

Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?

I tried the following, but it did not work:

declare @.Receptor as sql_variant

set @.Receptor=execute('select ' + @.Function + '(10)')

Thanks a lot in advance.The function is not completely qualified (DB name, owner)
Try:

declare @.Receptor as sql_variant
set @.Receptor = database_name.dbo.Function(@.in_param_value)

A user defined function can be used like any other pre-defined function

Originally posted by EMoscosoCam
Hello!

Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?

I tried the following, but it did not work:

declare @.Receptor as sql_variant

set @.Receptor=execute('select ' + @.Function + '(10)')

Thanks a lot in advance.sql

How can I programatically run the snapshot agent?

How can I run the snapshot agent of a publication from within a custom job in
one step?
I have a snapshot publication that will execute on demand. A custom job will
initiate this process, but the custom job needs to be able to wait until the
snapshot agent is finished as it will do many other things after. I know I
can start the snapshot agent by using the sp_start_job sproc but this will
simply start the process and will not wait until it finishes.
One option I know I have is to copy the 3 steps below which are inside the
auto-generated snapshot job and add them to my custom job as 3 steps.
StepName
1.Snapshot Agent startup message.
2.Run agent.
3.Detect nonlogged agent shutdown.
However, I would prefer to perform all of this in one step to simplify
things which is why I am asking if any of you know if this is even possible.
Thanks for any assistance you can provide.
P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
- Johnny
Johnny,
I have achieved the same result by appending my step to the end of the
snapshot job's steps, which is essentially the same as what you suggest.
This was the only way I could ensure the correct order of processing, apart
from polling the state of the job. So, I'd recommend you reproducing the job
steps in your new task.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul. This confirms reproducing the steps is the best way to go. This
will end up being a 10+ step job.
Johnny
"Paul Ibison" wrote:

> Johnny,
> I have achieved the same result by appending my step to the end of the
> snapshot job's steps, which is essentially the same as what you suggest.
> This was the only way I could ensure the correct order of processing, apart
> from polling the state of the job. So, I'd recommend you reproducing the job
> steps in your new task.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

How can I programatically run the snapshot agent?

How can I run the snapshot agent of a publication from within a custom job in
one step?
I have a snapshot publication that will execute on demand. A custom job will
initiate this process, but the custom job needs to be able to wait until the
snapshot agent is finished as it will do many other things after. I know I
can start the snapshot agent by using the sp_start_job sproc but this will
simply start the process and will not wait until it finishes.
One option I know I have is to copy the 3 steps below which are inside the
auto-generated snapshot job and add them to my custom job as 3 steps.
StepName
1.Snapshot Agent startup message.
2.Run agent.
3.Detect nonlogged agent shutdown.
However, I would prefer to perform all of this in one step to simplify
things which is why I am asking if any of you know if this is even possible.
Thanks for any assistance you can provide.
P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
- Johnny
You can use the commands within run agent. You will not get a start up
message, and if it gpf's you will not know your agent has died.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> How can I run the snapshot agent of a publication from within a custom job
in
> one step?
> I have a snapshot publication that will execute on demand. A custom job
will
> initiate this process, but the custom job needs to be able to wait until
the
> snapshot agent is finished as it will do many other things after. I know I
> can start the snapshot agent by using the sp_start_job sproc but this will
> simply start the process and will not wait until it finishes.
> One option I know I have is to copy the 3 steps below which are inside the
> auto-generated snapshot job and add them to my custom job as 3 steps.
> Step Name
> 1. Snapshot Agent startup message.
> 2. Run agent.
> 3. Detect nonlogged agent shutdown.
>
> However, I would prefer to perform all of this in one step to simplify
> things which is why I am asking if any of you know if this is even
possible.
> Thanks for any assistance you can provide.
> P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
> - Johnny
>
|||Thanks Hilary.
What does "gpf's" mean? I havent seen that before. Do you instead recommend
I copy all 3 steps for a more solid solution?
P.S - We bought your book already btw. It's great!
Johnny
"Hilary Cotter" wrote:

> You can use the commands within run agent. You will not get a start up
> message, and if it gpf's you will not know your agent has died.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> in
> will
> the
> possible.
>
>

How can I programatically run the snapshot agent?

How can I run the snapshot agent of a publication from within a custom job in
one step?
I have a snapshot publication that will execute on demand. A custom job will
initiate this process, but the custom job needs to be able to wait until the
snapshot agent is finished as it will do many other things after. I know I
can start the snapshot agent by using the sp_start_job sproc but this will
simply start the process and will not wait until it finishes.
One option I know I have is to copy the 3 steps below which are inside the
auto-generated snapshot job and add them to my custom job as 3 steps.
Step Name
---
1. Snapshot Agent startup message.
2. Run agent.
3. Detect nonlogged agent shutdown.
However, I would prefer to perform all of this in one step to simplify
things which is why I am asking if any of you know if this is even possible.
Thanks for any assistance you can provide.
P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
- JohnnyYou can use the commands within run agent. You will not get a start up
message, and if it gpf's you will not know your agent has died.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> How can I run the snapshot agent of a publication from within a custom job
in
> one step?
> I have a snapshot publication that will execute on demand. A custom job
will
> initiate this process, but the custom job needs to be able to wait until
the
> snapshot agent is finished as it will do many other things after. I know I
> can start the snapshot agent by using the sp_start_job sproc but this will
> simply start the process and will not wait until it finishes.
> One option I know I have is to copy the 3 steps below which are inside the
> auto-generated snapshot job and add them to my custom job as 3 steps.
> Step Name
> ---
> 1. Snapshot Agent startup message.
> 2. Run agent.
> 3. Detect nonlogged agent shutdown.
>
> However, I would prefer to perform all of this in one step to simplify
> things which is why I am asking if any of you know if this is even
possible.
> Thanks for any assistance you can provide.
> P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
> - Johnny
>|||Thanks Hilary.
What does "gpf's" mean? I havent seen that before. Do you instead recommend
I copy all 3 steps for a more solid solution?
P.S - We bought your book already btw. It's great!
Johnny
"Hilary Cotter" wrote:
> You can use the commands within run agent. You will not get a start up
> message, and if it gpf's you will not know your agent has died.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> > How can I run the snapshot agent of a publication from within a custom job
> in
> > one step?
> >
> > I have a snapshot publication that will execute on demand. A custom job
> will
> > initiate this process, but the custom job needs to be able to wait until
> the
> > snapshot agent is finished as it will do many other things after. I know I
> > can start the snapshot agent by using the sp_start_job sproc but this will
> > simply start the process and will not wait until it finishes.
> >
> > One option I know I have is to copy the 3 steps below which are inside the
> > auto-generated snapshot job and add them to my custom job as 3 steps.
> >
> > Step Name
> > ---
> > 1. Snapshot Agent startup message.
> > 2. Run agent.
> > 3. Detect nonlogged agent shutdown.
> >
> >
> > However, I would prefer to perform all of this in one step to simplify
> > things which is why I am asking if any of you know if this is even
> possible.
> >
> > Thanks for any assistance you can provide.
> >
> > P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
> >
> > - Johnny
> >
>
>

How can I programatically run the snapshot agent?

How can I run the snapshot agent of a publication from within a custom job i
n
one step?
I have a snapshot publication that will execute on demand. A custom job will
initiate this process, but the custom job needs to be able to wait until the
snapshot agent is finished as it will do many other things after. I know I
can start the snapshot agent by using the sp_start_job sproc but this will
simply start the process and will not wait until it finishes.
One option I know I have is to copy the 3 steps below which are inside the
auto-generated snapshot job and add them to my custom job as 3 steps.
Step Name
---
1. Snapshot Agent startup message.
2. Run agent.
3. Detect nonlogged agent shutdown.
However, I would prefer to perform all of this in one step to simplify
things which is why I am asking if any of you know if this is even possible.
Thanks for any assistance you can provide.
P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
- JohnnyYou can use the commands within run agent. You will not get a start up
message, and if it gpf's you will not know your agent has died.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> How can I run the snapshot agent of a publication from within a custom job
in
> one step?
> I have a snapshot publication that will execute on demand. A custom job
will
> initiate this process, but the custom job needs to be able to wait until
the
> snapshot agent is finished as it will do many other things after. I know I
> can start the snapshot agent by using the sp_start_job sproc but this will
> simply start the process and will not wait until it finishes.
> One option I know I have is to copy the 3 steps below which are inside the
> auto-generated snapshot job and add them to my custom job as 3 steps.
> Step Name
> ---
> 1. Snapshot Agent startup message.
> 2. Run agent.
> 3. Detect nonlogged agent shutdown.
>
> However, I would prefer to perform all of this in one step to simplify
> things which is why I am asking if any of you know if this is even
possible.
> Thanks for any assistance you can provide.
> P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
> - Johnny
>|||Thanks Hilary.
What does "gpf's" mean? I havent seen that before. Do you instead recommend
I copy all 3 steps for a more solid solution?
P.S - We bought your book already btw. It's great!
Johnny
"Hilary Cotter" wrote:

> You can use the commands within run agent. You will not get a start up
> message, and if it gpf's you will not know your agent has died.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> in
> will
> the
> possible.
>
>

How can I programatically run the snapshot agent?

How can I run the snapshot agent of a publication from within a custom job in
one step?
I have a snapshot publication that will execute on demand. A custom job will
initiate this process, but the custom job needs to be able to wait until the
snapshot agent is finished as it will do many other things after. I know I
can start the snapshot agent by using the sp_start_job sproc but this will
simply start the process and will not wait until it finishes.
One option I know I have is to copy the 3 steps below which are inside the
auto-generated snapshot job and add them to my custom job as 3 steps.
StepName
1.Snapshot Agent startup message.
2.Run agent.
3.Detect nonlogged agent shutdown.
However, I would prefer to perform all of this in one step to simplify
things which is why I am asking if any of you know if this is even possible.
Thanks for any assistance you can provide.
P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
- Johnny
You can use the commands within run agent. You will not get a start up
message, and if it gpf's you will not know your agent has died.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> How can I run the snapshot agent of a publication from within a custom job
in
> one step?
> I have a snapshot publication that will execute on demand. A custom job
will
> initiate this process, but the custom job needs to be able to wait until
the
> snapshot agent is finished as it will do many other things after. I know I
> can start the snapshot agent by using the sp_start_job sproc but this will
> simply start the process and will not wait until it finishes.
> One option I know I have is to copy the 3 steps below which are inside the
> auto-generated snapshot job and add them to my custom job as 3 steps.
> Step Name
> 1. Snapshot Agent startup message.
> 2. Run agent.
> 3. Detect nonlogged agent shutdown.
>
> However, I would prefer to perform all of this in one step to simplify
> things which is why I am asking if any of you know if this is even
possible.
> Thanks for any assistance you can provide.
> P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
> - Johnny
>
|||Thanks Hilary.
What does "gpf's" mean? I havent seen that before. Do you instead recommend
I copy all 3 steps for a more solid solution?
P.S - We bought your book already btw. It's great!
Johnny
"Hilary Cotter" wrote:

> You can use the commands within run agent. You will not get a start up
> message, and if it gpf's you will not know your agent has died.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> in
> will
> the
> possible.
>
>
sql

how can I prevent all jobs start up at the same time?

If I create a job which execute automatically every hour start from 00:00
end to 23:00, does it mean it will start up at the exact 00:00,
01:00.....23:00?
if the answer is true, then if I create many (very very many) jobs which
start automatically in the same rule, will the sql server agent start every
job in the same time? All start up at the exact o'clock?
If the answer is true too, how can I prevent all jobs start up at the same
time?> If I create a job which execute automatically every hour start from 00:00
> end to 23:00, does it mean it will start up at the exact 00:00,
> 01:00.....23:00?
Yes.

> if the answer is true, then if I create many (very very many) jobs which
> start automatically in the same rule, will the sql server agent start ever
y
> job in the same time? All start up at the exact o'clock?
Almost (there may be small differences, usually less than one second).

> If the answer is true too, how can I prevent all jobs start up at the same
> time?
Make only one job with many steps (one step for each job that you would
have created).
Razvan|||if I create every job execute automatically every hour (and everyday) BUT
begin from a random minute (not always from 00:00), can it prevent all jobs
start up at the same time?

> Almost (there may be small differences, usually less than one second).
>|||what is the problem if no. of jobs start at the same time.
If you have any problem you can schedule it some of them at start at
00:00 and repeat after every 1 hour
some start at 00:10 and repeat after every 1 hour so it will run
01:10,02:10 and so on...
Jobs will not run at randomly time from your start time.
Or create one job with multiple steps.
Regards
Amish
*** Sent via Developersdex http://www.examnotes.net ***|||Thanks, I got it.
"Amish Shah" <shahamishm@.gmail.com>
'?:egDI6mRFGHA.532@.TK2MSFTNGP15.phx.gbl...
> what is the problem if no. of jobs start at the same time.
> If you have any problem you can schedule it some of them at start at
> 00:00 and repeat after every 1 hour
> some start at 00:10 and repeat after every 1 hour so it will run
> 01:10,02:10 and so on...
> Jobs will not run at randomly time from your start time.
> Or create one job with multiple steps.
>
>
> Regards
> Amish
> *** Sent via Developersdex http://www.examnotes.net ***|||You could consider a mechanism using sp_start_job from one job to start
another.
I'm wondering why you have so many jobs...But anyhow you could even
use a table to hold the names of jobs and parameterise what starts so
you read the top un-run out a table and mark it as run...|||There is a limited number of threads that Agent will spawn for each job subs
ystem. The value is
specified in the registry (s and you will find). It is probably not suppo
rted to change the
value, unless you find some article (Books Online or KB) that discusses this
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:ONwlAmQFGHA.2040@.TK2MSFTNGP14.phx.gb
l...
> If I create a job which execute automatically every hour start from 00:00
end to 23:00, does it
> mean it will start up at the exact 00:00, 01:00.....23:00?
> if the answer is true, then if I create many (very very many) jobs which s
tart automatically in
> the same rule, will the sql server agent start every job in the same time?
All start up at the
> exact o'clock?
> If the answer is true too, how can I prevent all jobs start up at the same
time?
>|||I see. Thank you for telling me about that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
'?:urZFVrTFGHA.2696@.TK2MSFTNGP14.phx.gbl...
> There is a limited number of threads that Agent will spawn for each job
> subsystem. The value is specified in the registry (s and you will
> find). It is probably not supported to change the value, unless you find
> some article (Books Online or KB) that discusses this.
> --

how can I obtain message_id?

Hi all!

How can I obtain message_id when I SEND the message, before I execute RECEIVE statement?

Thanks a lot for the help!

Are you sending to a service and then wanting to recieve from it straight after in the same code?
What are you trying to achieve with this code?

|||

Hi, SimonS_! Thank you for fast response.

I'm send the message in one procedure:

create procedure [dbo].[proc_Client]

as

begin

set nocount on;

declare @.conversation_handle uniqueidentifier

begin dialog conversation @.conversation_handle

FROM SERVICE [My Service1]

TO SERVICE [My Service2]

ON CONTRACT [Custom Comtract]

send on conversation @.conversation_handle message type [//Messaging/CustomMsg]('message data');

and here I want to save the Service Broker message_ID in the my table

end conversation @.conversation_handle;

end

Then I have an Activation Procedure:

CREATE Procedure [proc_ms_Activation]

AS

BEGIN

SET NOCOUNT ON

WAITFOR (RECEIVE TOP(1) @.internal_SB_message_id = message_id, @.message_type_name = message_type_name, @.message_body = message_body, @.conversation_handle = conversation_handle FROM Queue1);

END

mesage_id I receive only in [proc_ms_Activation], but I need save this message_id in [proc_Client] procedure. Could you please help me?

|||

I still don't know why you need the message Id.

Are you trying to manage state ?

|||Yes, I'm try to manage message state, keep messages in database and found it, and I have additional logic - gathering statistics, track poison messages and etc|||

Two points,

Messages that come back to your initiator service use the same conversation group. This means that if you specify a conversation group you can use that to correlate messages you send to messages you receive.

In this situation generate a guid (newid()) and then use that for the conversation_group_id. Then you can store it in a table, If you want the target service to know this id then you will need to include it in the message.

|||

We didn't see a need to expose message_ids at the system level since apps that require tracking state at a per-message level would have primitives within the app itself for doing so. You could for example stick some cookie into the message sent by the initiator and relay it back with the response so that your app can corelate the response to the request. Depending on the app this may be a 'request ID', an 'order number', a 'service request ticket', etc.

Rushi

|||

The other way to refactor the app would be to think of a conversation as being the primitive for identifying a request. So each request would be sent on its own unique conversation. That way the app can store state with the conversation itself (and you could use conversation_handle or conversation_group_id* as the primary key in that case). Responses for a particular request would only come back on the same conversation and hence you can easily correlate them. In fact we provide locking at the conversation level to prevent multiple queue readers from accessing the same conversation at the same time.

Rushi

*: You WILL need to use conversation_groups if you want to relate multiple conversations together. Even if today you don't do that, conversation_group_id is a better way to key state than handles.

|||

Rushi, thanks a lot for the detailed explanation.

The best decision for me will use one-message-per-dialog conversation and send one message per conversation, then trace application logic on conversation_id.

Thank you,

Sveta.

|||You don't have to have one message per dialog for this to work. I would say you want 1 Dialog per business transaction, that way any responses to any messages are on the same conversation group. You might want to send two messages rather than having to combine data from different sources in one message

How can I ignore errors in script and resume execute this script? thx :)

How can I ignore errors in script and resume execute this script? thx :)IF you are talking about syntax errors in the script you can not..
SQL checks for the syntax of all items in a batch. If ALL the statements
syntax correctly, THEN the batch executes. You can make your batches smaller
by inserting go between statements.
If you are talking about SQL errors while executing a statement, you may
simply ignore the error... However some errors abort the batch and there is
not much (nothing) you can do about that ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>|||By the way...
One thing I used to do is in every batch update a row in a table which had
the version # of the batch... Each batch checked this version number to see
if the batch had to run or had already run... That way, I could fix the
offending batch within the script and just re-run the entire script... It
was a pain in the butt to keep up with , but it worked...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>

How can I ignore errors in script and resume execute this script? thx :)

How can I ignore errors in script and resume execute this script? thx :)IF you are talking about syntax errors in the script you can not..
SQL checks for the syntax of all items in a batch. If ALL the statements
syntax correctly, THEN the batch executes. You can make your batches smaller
by inserting go between statements.
If you are talking about SQL errors while executing a statement, you may
simply ignore the error... However some errors abort the batch and there is
not much (nothing) you can do about that ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>
|||By the way...
One thing I used to do is in every batch update a row in a table which had
the version # of the batch... Each batch checked this version number to see
if the batch had to run or had already run... That way, I could fix the
offending batch within the script and just re-run the entire script... It
was a pain in the butt to keep up with , but it worked...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>

How can I ignore errors in script and resume execute this script? thx :)

How can I ignore errors in script and resume execute this script? thx :)IF you are talking about syntax errors in the script you can not..
SQL checks for the syntax of all items in a batch. If ALL the statements
syntax correctly, THEN the batch executes. You can make your batches smaller
by inserting go between statements.
If you are talking about SQL errors while executing a statement, you may
simply ignore the error... However some errors abort the batch and there is
not much (nothing) you can do about that ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>|||By the way...
One thing I used to do is in every batch update a row in a table which had
the version # of the batch... Each batch checked this version number to see
if the batch had to run or had already run... That way, I could fix the
offending batch within the script and just re-run the entire script... It
was a pain in the butt to keep up with , but it worked...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>

how can I have this package execute in loop....I want to change every lines that are not c

declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')Without a problem description, including table DDL and sample data, a
meaningful response is not likely.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:eZTmg.15162$XT2.164998@.wagner.videotron.net...
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>

How can i get the process detais using TSQL instructions ?

Hi Friends,
How can i get the process detais using TSQL instructions ?
i want the same information that i get when i execute the
following procedures
"Expand Management, and then expand Current Activity.
Click Process Info.
The current server activity is displayed in the details
pane."
Thank You
Johnny
Hi
You can use profiler to see what EM is doing and it would have come up with
he undocumented procedure:
EXEC sp_MSget_current_activity 51,1
John
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>
|||Hi,
To get information of a SPID then go with this:-
From Query Analyzer, Execute the below system procedure to get the SPID for
the user.
sp_who
After getting the sp_id use the below command to get the process detailsfor
the SPID
DBCC INPUTBUFFER(SPID)
Thanks
Hari
MCDBA
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>
|||select *
from master..sysprocesses
-Sue
On Fri, 18 Jun 2004 12:50:37 -0700, "Johnny Silvestre"
<johnny_silvestre@.yahoo.de> wrote:

>Hi Friends,
>How can i get the process detais using TSQL instructions ?
>i want the same information that i get when i execute the
>following procedures
>"Expand Management, and then expand Current Activity.
>Click Process Info.
>The current server activity is displayed in the details
>pane."
>Thank You
>Johnny

How can i get the process detais using TSQL instructions ?

Hi Friends,
How can i get the process detais using TSQL instructions ?
i want the same information that i get when i execute the
following procedures
"Expand Management, and then expand Current Activity.
Click Process Info.
The current server activity is displayed in the details
pane."
Thank You
JohnnyHi
You can use profiler to see what EM is doing and it would have come up with
he undocumented procedure:
EXEC sp_MSget_current_activity 51,1
John
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||Hi,
To get information of a SPID then go with this:-
From Query Analyzer, Execute the below system procedure to get the SPID for
the user.
sp_who
After getting the sp_id use the below command to get the process detailsfor
the SPID
DBCC INPUTBUFFER(SPID)
Thanks
Hari
MCDBA
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||select *
from master..sysprocesses
-Sue
On Fri, 18 Jun 2004 12:50:37 -0700, "Johnny Silvestre"
<johnny_silvestre@.yahoo.de> wrote:
>Hi Friends,
>How can i get the process detais using TSQL instructions ?
>i want the same information that i get when i execute the
>following procedures
>"Expand Management, and then expand Current Activity.
>Click Process Info.
>The current server activity is displayed in the details
>pane."
>Thank You
>Johnny

How can i get the process detais using TSQL instructions ?

Hi Friends,
How can i get the process detais using TSQL instructions ?
i want the same information that i get when i execute the
following procedures
"Expand Management, and then expand Current Activity.
Click Process Info.
The current server activity is displayed in the details
pane."
Thank You
JohnnyHi
You can use profiler to see what EM is doing and it would have come up with
he undocumented procedure:
EXEC sp_MSget_current_activity 51,1
John
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx
.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||Hi,
To get information of a SPID then go with this:-
From Query Analyzer, Execute the below system procedure to get the SPID for
the user.
sp_who
After getting the sp_id use the below command to get the process detailsfor
the SPID
DBCC INPUTBUFFER(SPID)
Thanks
Hari
MCDBA
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx
.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||select *
from master..sysprocesses
-Sue
On Fri, 18 Jun 2004 12:50:37 -0700, "Johnny Silvestre"
<johnny_silvestre@.yahoo.de> wrote:

>Hi Friends,
>How can i get the process detais using TSQL instructions ?
>i want the same information that i get when i execute the
>following procedures
>"Expand Management, and then expand Current Activity.
>Click Process Info.
>The current server activity is displayed in the details
>pane."
>Thank You
>Johnny

hOW CAN i GET THE dts PACKAGES TO WORK WITH MSDE

I have masde Installation which have to execute dts packages to connect
to my sql server and load the data to the SQL databases.
Would anyone have any experience with this and know how to make this
work?

Thanking you in anticipation.

Ajay GargDTS works with MSDE in exactly the same way as MSSQL. The only
difference is that MSDE has no graphical client tools, so you don't
have the DTS package designer, but I guess you're probably designing
your packages on MSSQL anyway.

If you have a specific problem with DTS, then you should give more
details about exactly what you're trying to do, what errors or
unexpected results you get and so on. There's also a DTS newsgroup:
microsoft.public.sqlserver.dts.

Simon