Runtime build sql in stored procedures with output param Q?

Hi

I'm trying to make this to work and need help

Here my SP and I'm building sql with output param.

Alter PROCEDURE lpsadmin_getSBWReorderDollars
(
@.out decimal(10,2) output,
@.sType varchar(20),
@.dSearchDateFrom datetime,
@.dSearchDateTo datetime,
@.sOrderType char(1)
)
AS
DECLARE @.sql as nvarchar(4000)

SELECT @.sql = 'SELECT @.out = SUM(Price*Quantity)
FROM PortraitReOrderOrder jcpre
INNER JOIN Orders jcpor
ON OrderID = OrderID
WHERE jcpor.Archive = 0
AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),
@.dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),
@.dSearchDateTo, 101) + ''''

IF @.sOrderType <> 0
SELECT @.sql = @.sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeName
FROM OrderTypes WHERE OrderTypeID = ' + @.sOrderType + ')'
IF @.sType = 'Active'
SELECT @.sql = @.sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 '
IF @.sType = 'Shared'
SELECT @.sql = @.sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 '
Print @.sql
EXECUTE sp_executesql @.sql

It gives me an error message
Must declare the variable '@.out'.

Please help[posted and mailed, please reply in news]

Lepa (lepa71@.netscape.net) writes:
> I'm trying to make this to work and need help
> Here my SP and I'm building sql with output param.
> Alter PROCEDURE lpsadmin_getSBWReorderDollars
> (
> @.out decimal(10,2) output,
> @.sType varchar(20),
> @.dSearchDateFrom datetime,
> @.dSearchDateTo datetime,
> @.sOrderType char(1)
> )
> AS
> DECLARE @.sql as nvarchar(4000)
> SELECT @.sql = 'SELECT @.out = SUM(Price*Quantity)
> FROM PortraitReOrderOrder jcpre
> INNER JOIN Orders jcpor
> ON OrderID = OrderID
> WHERE jcpor.Archive = 0
> AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),
> @.dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),
> @.dSearchDateTo, 101) + ''''
> IF @.sOrderType <> 0
> SELECT @.sql = @.sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeName
> FROM OrderTypes WHERE OrderTypeID = ' + @.sOrderType + ')'
> IF @.sType = 'Active'
> SELECT @.sql = @.sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 '
> IF @.sType = 'Shared'
> SELECT @.sql = @.sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 '
> Print @.sql
> EXECUTE sp_executesql @.sql
> It gives me an error message
> Must declare the variable '@.out'.

Now, think of this: you call another stored procedure to execute your
SQL. Normally, in T-SQL, can a stored procedure refer to a variable
declared in another stored procedure? So why would this be possible
here?

The whole point with sp_executesql is that you can pass parameters to
it, both input and outupt. So you don't have deal with cumbersome
conversion, but you can write it right, by means or parameters to
the dynamic SQL.

Look here, for an example:
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||1) Dynamic SQL is never used in an application. it means that the
procedure is so poorly designed OR written that you cannot figure out
what it does until run time.

2) Never put the data_type in a prefix. This is SQL and not BASIC or
FORTRAN.

3) There is no such thing as a "type_id" or "type_name"; this is basic
data modeling. Those terms are like adjectives and they need a noun
to make sense -- type of what attribute? Identifier for what entity?
Name for what entity?

4) Why use the proprietary PATINDEX() instead of the portable,
standard LIKE predicate?

5)Is the @.search_type really CHAR(20)? That will hard to type
correctly!

6) The encoding scheme for types of orders is awkward. Having to pull
out substrings to get meaningful parts is usually a sign of an
overloaded code -- it measures more than one independent attribute.

7) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Here is my best guess at a re-write:

CREATE PROCEDURE GetsBWReorderDollars
( @.search_type CHAR(6), -- what was used
@.searchdatefrom DATETIME,
@.searchdateto DATETIME,
@.order_type CHAR(1))
AS
SELECT SUM (price * quantity)
FROM PortraitReorderOrders AS R1,
Orders AS O1
WHERE R1.order_id = O1.order_id
AND O1.archive = 0
AND R1.created_date BETWEEN @.searchdatefrom AND @.searchdateto
AND @.order_type <> 0
AND CASE WHEN @.search_type = 'active'
AND access_code LIKE '%SHR%'
THEN 1
WHEN @.search_type = 'shared'
AND access_code NOT LIKE '%SHR%'
THEN 1 ELSE 0 END = 1
--the following predicate makes no sense due to inconsistent
AND SUBSTRING (order_type, 1, 3)
= (SELECT order_type_name
FROM OrderTypes
WHERE order_type = @.order_type;sql

Running the same SQL on several servers

I frequently do updates to Stored Procedures (and other types of SQL queries
too) on serveral server installations across our LAN/WAN.
Currently, I connect to each server in SSMS and run the code in a new query
window for each, which is a bit of a pain. Is there a way I can run the
script in one query window, but run it several times - each pointing to
different server. For example, is there an equivalent to the USE DB_NAME
statement, that reflects the server as well?"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OG0O3eRSGHA.1608@.TK2MSFTNGP09.phx.gbl...
>I frequently do updates to Stored Procedures (and other types of SQL
>queries too) on serveral server installations across our LAN/WAN.
> Currently, I connect to each server in SSMS and run the code in a new
> query window for each, which is a bit of a pain. Is there a way I can run
> the script in one query window, but run it several times - each pointing
> to different server. For example, is there an equivalent to the USE
> DB_NAME statement, that reflects the server as well?
>
Right-click on the query window and choose Connection\Change Connection, or
use SqlCmd.
David

Running Stored Procedures on Linked servers from Microsoft SQL server

Hi,
I am trying to invoke storedprocedures on a linked server from MS SQL SERVER and it fails to recognise the object as a SP rather it gives me a message saying that the object does not have any columns. The Linked server is a SYBASE server. I can access all the tables and views but not the procedures. By the way, I have set the following server options on for the linked sybase server --
RPC OUT
RPC
But the Use Remote Collations is not turned on.

Please help!!!!!!!!!!!!!!!

Regards

JCI ran into something similar before when trying to execute SPs on a linked server (although they both were MS SQL Server)...

I remember having to use a fully qualified procedure name when I executed it:

EXEC LinkedServerName.DbName.DbOwner.SPName xxx,xxx,...

I dunno if this is applicable with your situation as I have never linked to another db platform other than from MS SQL Server to MS Access. I've use Sybase ASE before...but never in a linked scenario...

Kael|||I got your email stating that using a fully qualified name didn't work.

An idea I have is maybe examining the parameters that were used for the sp_addlinkedserver stored proc when the link was made from Sybase to MS SQL Server. Try dropping the linked server and recreating it.

Also, you can try using the OpenQuery function in the FROM clause on SQL Server rather than using a fully qualified name.

Are you using the OLE DB Provider for Sybase SQL Server (or named something like that) as the data provider when the linked server SP was used? Maybe try updating the DLL for that provider. I'd use OLE DB rather than a generic provider since it will expose more functionality.

Good luck!

Kael

running stored procedures from SQL Agent

I'm trying to understand some behavior I'm seeing running a stored procedure
as a TSQL jobstep in a SQL Agent job. If I run the sp from query analyzer or
osql, it completes as expected, but in the process may generate a few 3604
errors (duplicate keys ignored on insert). If any errors other than 3604 are
generated, the procedure aborts.
When running this as a job step, this 3604 error causes the step to fail,
which I can understand. To try to work around this problem, in this stored
procedure I write out the status codes to a table each time. If the job
reports failure, I set the next job step to search this table of status
codes for any that are non-zero and not 3604. If none are found, this
jobstep reports success, and the job completes with success, otherwise the
job fails. The problem is, the first job stop does not actually run to
completion; it process a few times through its main loop and then seems to
just abort. Looking at the generated log file I see no indication of errors.
Profiler tells me that the first job step completed, but I can tell from the
logfile that it did not. It then goes onto run the second step, which
completes with success.
Any advice as to why this is happening would be greatly appreciated. I know
that error handling is somewhat limited in T-SQL, and that I cannot suppress
the error completely. I have a fair amount of experience working with SQL
Agent and jobs and have never run into a problem like this where a procedure
behaves differently when run as a job step.
Thanks for any advice.
-Gary
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
Well, why not re-write the INSERT statement so that duplicate keys are not
inserted at all? Or, better yet, why bother having a primary key at all if
you are just going to insert redundant data and ignore the duplicate?
I imagine the main problem is that data is coming from BCP or BULK INSERT.
My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you insert
into a work table and then perform an insert/update combination on the
primary table, after you've cleaned up the data.
Back to the original problem, while msg 3604 is technically a warning and
not an error, some applications are still going to view it as an error, and
there is not much you can do about that...
A
|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>
|||> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
Of course, it's late on Friday, and my brain is fried. Some of this stuff
I'm saying today doesn't make sense to my dogs, never mind me.
|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>
|||Thanks for the response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OzX0Rj49FHA.2936@.tk2msftngp13.phx.gbl...
> Well, why not re-write the INSERT statement so that duplicate keys are not
> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
I think that may be my only choice. I was hoping to avoid this as there are
existing tables that are very large and I fear that all the speed I've
gained by doing BULK INSERT (over the previous way, which was done in vb,
but at least the warning could be suppressed) will be lost having to search
the existing data for anything in the work table. But I suppose that's what
indexes are for. I'm already creating a temp table to store the data before
it gets to it's final destination table, maybe it won't be too bad.

> I imagine the main problem is that data is coming from BCP or BULK INSERT.
> My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you
> insert into a work table and then perform an insert/update combination on
> the primary table, after you've cleaned up the data.
Yes. This is really a band-aid for some older code that created indexes with
this option. Unfortunately these are on what are probably the biggest tables
in the database.
Thanks again for the advice,
-Gary
|||I've got SP3. I did see that there was a bug fixed in SP1 related to this.
What really surprises me is that it behaves differently when run as a job
step. All I do is "exec <sp>".
Thanks,
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNhK3n49FHA.1484@.tk2msftngp13.phx.gbl...
> BTW, what version are you at? There was a hotfix for this issue a while
> back:
> http://support.microsoft.com/?id=295032
>
|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes
|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes
|||> I've got SP3. I did see that there was a bug fixed in SP1 related to this.
> What really surprises me is that it behaves differently when run as a job
> step. All I do is "exec <sp>".
Right, but there is more to SQL Agent than just calling your code... this is
NOT apples to apples. For starters, it uses a different library to connect
to the server than, say, QA or VB would. It also executes as a different
user, in most cases, unless your job is owned by SA and you routinely
connect via QA or VB using SA (shame, shame).
I am sure there are long-winded discussions on google and probably some good
information in Books Online about all the nitty-gritty differences between
code you execute and code SQL Agent executes. The short answer is that they
are not the same.
A

running stored procedures from SQL Agent

I'm trying to understand some behavior I'm seeing running a stored procedure
as a TSQL jobstep in a SQL Agent job. If I run the sp from query analyzer or
osql, it completes as expected, but in the process may generate a few 3604
errors (duplicate keys ignored on insert). If any errors other than 3604 are
generated, the procedure aborts.
When running this as a job step, this 3604 error causes the step to fail,
which I can understand. To try to work around this problem, in this stored
procedure I write out the status codes to a table each time. If the job
reports failure, I set the next job step to search this table of status
codes for any that are non-zero and not 3604. If none are found, this
jobstep reports success, and the job completes with success, otherwise the
job fails. The problem is, the first job stop does not actually run to
completion; it process a few times through its main loop and then seems to
just abort. Looking at the generated log file I see no indication of errors.
Profiler tells me that the first job step completed, but I can tell from the
logfile that it did not. It then goes onto run the second step, which
completes with success.
Any advice as to why this is happening would be greatly appreciated. I know
that error handling is somewhat limited in T-SQL, and that I cannot suppress
the error completely. I have a fair amount of experience working with SQL
Agent and jobs and have never run into a problem like this where a procedure
behaves differently when run as a job step.
Thanks for any advice.
-Gary> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
Well, why not re-write the INSERT statement so that duplicate keys are not
inserted at all? Or, better yet, why bother having a primary key at all if
you are just going to insert redundant data and ignore the duplicate?
I imagine the main problem is that data is coming from BCP or BULK INSERT.
My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you insert
into a work table and then perform an insert/update combination on the
primary table, after you've cleaned up the data.
Back to the original problem, while msg 3604 is technically a warning and
not an error, some applications are still going to view it as an error, and
there is not much you can do about that...
A|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
Of course, it's late on Friday, and my brain is fried. Some of this stuff
I'm saying today doesn't make sense to my dogs, never mind me.|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||Thanks for the response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OzX0Rj49FHA.2936@.tk2msftngp13.phx.gbl...
> Well, why not re-write the INSERT statement so that duplicate keys are not
> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
I think that may be my only choice. I was hoping to avoid this as there are
existing tables that are very large and I fear that all the speed I've
gained by doing BULK INSERT (over the previous way, which was done in vb,
but at least the warning could be suppressed) will be lost having to search
the existing data for anything in the work table. But I suppose that's what
indexes are for. I'm already creating a temp table to store the data before
it gets to it's final destination table, maybe it won't be too bad.

> I imagine the main problem is that data is coming from BCP or BULK INSERT.
> My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you
> insert into a work table and then perform an insert/update combination on
> the primary table, after you've cleaned up the data.
Yes. This is really a band-aid for some older code that created indexes with
this option. Unfortunately these are on what are probably the biggest tables
in the database.
Thanks again for the advice,
-Gary|||I've got SP3. I did see that there was a bug fixed in SP1 related to this.
What really surprises me is that it behaves differently when run as a job
step. All I do is "exec <sp>".
Thanks,
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNhK3n49FHA.1484@.tk2msftngp13.phx.gbl...
> BTW, what version are you at? There was a hotfix for this issue a while
> back:
> http://support.microsoft.com/?id=295032
>|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I've got SP3. I did see that there was a bug fixed in SP1 related to this.
> What really surprises me is that it behaves differently when run as a job
> step. All I do is "exec <sp>".
Right, but there is more to SQL Agent than just calling your code... this is
NOT apples to apples. For starters, it uses a different library to connect
to the server than, say, QA or VB would. It also executes as a different
user, in most cases, unless your job is owned by SA and you routinely
connect via QA or VB using SA (shame, shame).
I am sure there are long-winded discussions on google and probably some good
information in Books Online about all the nitty-gritty differences between
code you execute and code SQL Agent executes. The short answer is that they
are not the same.
A

running stored procedures from SQL Agent

I'm trying to understand some behavior I'm seeing running a stored procedure
as a TSQL jobstep in a SQL Agent job. If I run the sp from query analyzer or
osql, it completes as expected, but in the process may generate a few 3604
errors (duplicate keys ignored on insert). If any errors other than 3604 are
generated, the procedure aborts.
When running this as a job step, this 3604 error causes the step to fail,
which I can understand. To try to work around this problem, in this stored
procedure I write out the status codes to a table each time. If the job
reports failure, I set the next job step to search this table of status
codes for any that are non-zero and not 3604. If none are found, this
jobstep reports success, and the job completes with success, otherwise the
job fails. The problem is, the first job stop does not actually run to
completion; it process a few times through its main loop and then seems to
just abort. Looking at the generated log file I see no indication of errors.
Profiler tells me that the first job step completed, but I can tell from the
logfile that it did not. It then goes onto run the second step, which
completes with success.
Any advice as to why this is happening would be greatly appreciated. I know
that error handling is somewhat limited in T-SQL, and that I cannot suppress
the error completely. I have a fair amount of experience working with SQL
Agent and jobs and have never run into a problem like this where a procedure
behaves differently when run as a job step.
Thanks for any advice.
-Gary> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
Well, why not re-write the INSERT statement so that duplicate keys are not
inserted at all? Or, better yet, why bother having a primary key at all if
you are just going to insert redundant data and ignore the duplicate?
I imagine the main problem is that data is coming from BCP or BULK INSERT.
My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you insert
into a work table and then perform an insert/update combination on the
primary table, after you've cleaned up the data.
Back to the original problem, while msg 3604 is technically a warning and
not an error, some applications are still going to view it as an error, and
there is not much you can do about that...
A|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
Of course, it's late on Friday, and my brain is fried. Some of this stuff
I'm saying today doesn't make sense to my dogs, never mind me.|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||Thanks for the response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OzX0Rj49FHA.2936@.tk2msftngp13.phx.gbl...
>> I'm trying to understand some behavior I'm seeing running a stored
>> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from
>> query analyzer or osql, it completes as expected, but in the process may
>> generate a few 3604 errors (duplicate keys ignored on insert). If any
>> errors other than 3604 are generated, the procedure aborts.
> Well, why not re-write the INSERT statement so that duplicate keys are not
> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
I think that may be my only choice. I was hoping to avoid this as there are
existing tables that are very large and I fear that all the speed I've
gained by doing BULK INSERT (over the previous way, which was done in vb,
but at least the warning could be suppressed) will be lost having to search
the existing data for anything in the work table. But I suppose that's what
indexes are for. I'm already creating a temp table to store the data before
it gets to it's final destination table, maybe it won't be too bad.
> I imagine the main problem is that data is coming from BCP or BULK INSERT.
> My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you
> insert into a work table and then perform an insert/update combination on
> the primary table, after you've cleaned up the data.
Yes. This is really a band-aid for some older code that created indexes with
this option. Unfortunately these are on what are probably the biggest tables
in the database.
Thanks again for the advice,
-Gary|||I've got SP3. I did see that there was a bug fixed in SP1 related to this.
What really surprises me is that it behaves differently when run as a job
step. All I do is "exec <sp>".
Thanks,
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNhK3n49FHA.1484@.tk2msftngp13.phx.gbl...
> BTW, what version are you at? There was a hotfix for this issue a while
> back:
> http://support.microsoft.com/?id=295032
>|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I've got SP3. I did see that there was a bug fixed in SP1 related to this.
> What really surprises me is that it behaves differently when run as a job
> step. All I do is "exec <sp>".
Right, but there is more to SQL Agent than just calling your code... this is
NOT apples to apples. For starters, it uses a different library to connect
to the server than, say, QA or VB would. It also executes as a different
user, in most cases, unless your job is owned by SA and you routinely
connect via QA or VB using SA (shame, shame).
I am sure there are long-winded discussions on google and probably some good
information in Books Online about all the nitty-gritty differences between
code you execute and code SQL Agent executes. The short answer is that they
are not the same.
A|||Is it only my posts that are coming in pairs?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eqN3K849FHA.3608@.TK2MSFTNGP09.phx.gbl...
>> I fear that all the speed I've gained by doing BULK INSERT (over the
>> previous way, which was done in vb, but at least the warning could be
>> suppressed) will be lost having to search the existing data for anything
>> in the work table.
> Well, don't you think SQL Server is doing all that work anyway? How else
> would it know to raise all those msg 3604's?
> Anyway, I came across a potential workaround, and that is to fire your
> BULK INSERT from CMDExec and OSQL instead of directly from within the job
> step. Sounds like this will interpret the warning correctly and will
> prevent SQL Agent from barfing, though it will make your job step slightly
> more complex.
> Credit to Tibor, though I didn't research further to find the origin of
> this solution:
> http://tinyurl.com/73yes
>|||That's pretty much the answer I was looking for.
Thanks again.
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O$ZMc949FHA.2320@.TK2MSFTNGP11.phx.gbl...
>> I've got SP3. I did see that there was a bug fixed in SP1 related to
>> this. What really surprises me is that it behaves differently when run as
>> a job step. All I do is "exec <sp>".
> Right, but there is more to SQL Agent than just calling your code... this
> is NOT apples to apples. For starters, it uses a different library to
> connect to the server than, say, QA or VB would. It also executes as a
> different user, in most cases, unless your job is owned by SA and you
> routinely connect via QA or VB using SA (shame, shame).
> I am sure there are long-winded discussions on google and probably some
> good information in Books Online about all the nitty-gritty differences
> between code you execute and code SQL Agent executes. The short answer is
> that they are not the same.
> A
>|||That works. Thank You!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eqN3K849FHA.3608@.TK2MSFTNGP09.phx.gbl...
>> I fear that all the speed I've gained by doing BULK INSERT (over the
>> previous way, which was done in vb, but at least the warning could be
>> suppressed) will be lost having to search the existing data for anything
>> in the work table.
> Well, don't you think SQL Server is doing all that work anyway? How else
> would it know to raise all those msg 3604's?
> Anyway, I came across a potential workaround, and that is to fire your
> BULK INSERT from CMDExec and OSQL instead of directly from within the job
> step. Sounds like this will interpret the warning correctly and will
> prevent SQL Agent from barfing, though it will make your job step slightly
> more complex.
> Credit to Tibor, though I didn't research further to find the origin of
> this solution:
> http://tinyurl.com/73yes
>

running stored procedures from SQL Agent

I'm trying to understand some behavior I'm seeing running a stored procedure
as a TSQL jobstep in a SQL Agent job. If I run the sp from query analyzer or
osql, it completes as expected, but in the process may generate a few 3604
errors (duplicate keys ignored on insert). If any errors other than 3604 are
generated, the procedure aborts.
When running this as a job step, this 3604 error causes the step to fail,
which I can understand. To try to work around this problem, in this stored
procedure I write out the status codes to a table each time. If the job
reports failure, I set the next job step to search this table of status
codes for any that are non-zero and not 3604. If none are found, this
jobstep reports success, and the job completes with success, otherwise the
job fails. The problem is, the first job stop does not actually run to
completion; it process a few times through its main loop and then seems to
just abort. Looking at the generated log file I see no indication of errors.
Profiler tells me that the first job step completed, but I can tell from the
logfile that it did not. It then goes onto run the second step, which
completes with success.
Any advice as to why this is happening would be greatly appreciated. I know
that error handling is somewhat limited in T-SQL, and that I cannot suppress
the error completely. I have a fair amount of experience working with SQL
Agent and jobs and have never run into a problem like this where a procedure
behaves differently when run as a job step.
Thanks for any advice.
-Gary> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
Well, why not re-write the INSERT statement so that duplicate keys are not
inserted at all? Or, better yet, why bother having a primary key at all if
you are just going to insert redundant data and ignore the duplicate?
I imagine the main problem is that data is coming from BCP or BULK INSERT.
My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you insert
into a work table and then perform an insert/update combination on the
primary table, after you've cleaned up the data.
Back to the original problem, while msg 3604 is technically a warning and
not an error, some applications are still going to view it as an error, and
there is not much you can do about that...
A|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
Of course, it's late on Friday, and my brain is fried. Some of this stuff
I'm saying today doesn't make sense to my dogs, never mind me.|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||Thanks for the response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OzX0Rj49FHA.2936@.tk2msftngp13.phx.gbl...
> Well, why not re-write the INSERT statement so that duplicate keys are not
> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
I think that may be my only choice. I was hoping to avoid this as there are
existing tables that are very large and I fear that all the speed I've
gained by doing BULK INSERT (over the previous way, which was done in vb,
but at least the warning could be suppressed) will be lost having to search
the existing data for anything in the work table. But I suppose that's what
indexes are for. I'm already creating a temp table to store the data before
it gets to it's final destination table, maybe it won't be too bad.

> I imagine the main problem is that data is coming from BCP or BULK INSERT.
> My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you
> insert into a work table and then perform an insert/update combination on
> the primary table, after you've cleaned up the data.
Yes. This is really a band-aid for some older code that created indexes with
this option. Unfortunately these are on what are probably the biggest tables
in the database.
Thanks again for the advice,
-Gary|||I've got SP3. I did see that there was a bug fixed in SP1 related to this.
What really surprises me is that it behaves differently when run as a job
step. All I do is "exec <sp>".
Thanks,
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eNhK3n49FHA.1484@.tk2msftngp13.phx.gbl...
> BTW, what version are you at? There was a hotfix for this issue a while
> back:
> http://support.microsoft.com/?id=295032
>|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I've got SP3. I did see that there was a bug fixed in SP1 related to this.
> What really surprises me is that it behaves differently when run as a job
> step. All I do is "exec <sp>".
Right, but there is more to SQL Agent than just calling your code... this is
NOT apples to apples. For starters, it uses a different library to connect
to the server than, say, QA or VB would. It also executes as a different
user, in most cases, unless your job is owned by SA and you routinely
connect via QA or VB using SA (shame, shame).
I am sure there are long-winded discussions on google and probably some good
information in Books Online about all the nitty-gritty differences between
code you execute and code SQL Agent executes. The short answer is that they
are not the same.
Asql

Running Stored procedure as....

I need to use SET IDENTITY_INSERT on several tables in one
of the Stored Procedures. The stored procedure is
executed by an application user that can not be given DBO,
db_ddladmin or any higher permissions. This makes the SET
statement fail as the user does not have the need
authority.
My question: Is there a way that I can have this
particular stored procedure run under higher authority?
That is who ever runs this stored procedure it will always
execute under the authority of a fixed given user.Sorry that functionality is not availble in the current version of SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Running Store procedures

Hello there
I have some procedure that i would like to build store procedure for it
If two users who which their name is dbo and run the same store procedure at
the same time, and the store procedure using temp tables. will it cause
problems?
and if so what is the way to deal with these problems?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilHi Roy,
Do you mean the SP will create temp tables for working storage, or that the
temp tables are created outside of the procedure as a global temp table.
Basically, standard temp tables are created using the users connection and
do not overlap with other logged in users. For instance, create table
#mytable creates a table using the users current connection.
Hope this helps
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uIlQW4t2FHA.3228@.TK2MSFTNGP15.phx.gbl...

> Hello there
> I have some procedure that i would like to build store procedure for it
> If two users who which their name is dbo and run the same store procedure
> at
> the same time, and the store procedure using temp tables. will it cause
> problems?
> and if so what is the way to deal with these problems?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Whell Phil
The temp tables are created on the store procedure and when the SP ends they
are destroyed
The problem is that all my users are DBO. So my question is: when two users
which their name is DBO, Running the same procedure at the same time on
diffrent data, what will happen?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
news:eCm8k#t2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi Roy,
> Do you mean the SP will create temp tables for working storage, or that
the
> temp tables are created outside of the procedure as a global temp table.
> Basically, standard temp tables are created using the users connection and
> do not overlap with other logged in users. For instance, create table
> #mytable creates a table using the users current connection.
> Hope this helps
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uIlQW4t2FHA.3228@.TK2MSFTNGP15.phx.gbl...
>
procedure
>|||Hi Roy,
Should not be a problem since the client application they use will open a
new connection per user session. So if two users login as fred, each will
get a different connection to the SQL Server. To check is out do something
like this in QueryAnalyser.
Select tempdb and issue select * from sysobjects where type="U" order by
name
You will see what I mean from this the way that the temp tables are actually
structured within the tempdb database.
Phil
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OoTI8Lu2FHA.3744@.TK2MSFTNGP10.phx.gbl...
> Whell Phil
> The temp tables are created on the store procedure and when the SP ends
> they
> are destroyed
> The problem is that all my users are DBO. So my question is: when two
> users
> which their name is DBO, Running the same procedure at the same time on
> diffrent data, what will happen?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
> "Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
> news:eCm8k#t2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> the
> procedure
>|||> If two users who which their name is dbo and run the same store procedure
> at
> the same time, and the store procedure using temp tables. will it cause
> problems?
No, each user will get a #temp table in their own scope.
If you look at tempdb..sysobjects you will see names like:
#tempname___________________________4536
7
#tempname___________________________4239
7
(Too lazy to check the exact format of the trailing number/string, but it's
something like that.)
Even better might be reducing or eliminating the need for #temp tables in
the first place.
A

Running SQL Server Stored Procedures through access

Hi,
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:

Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute

for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?lets see the code for the sp|||There is definitely a reason, although I can't see what it is yet.

My first guess would be an object ownership problem... I'd prefix any object names that don't contain a period (.) with "dbo." to make them valid "two part names" for SQL Server. This may not be your problem, but it is the best place I can think of to start.

-PatP|||I would explicitly dim your cn and cmd objects.

Dim cn as new ADODB.connection
Dim cmd as new ADODB.command

At the bottom of your function, make sure you destroy the objects

cn.close
Set cn = Nothing
Set cmd = Nothing

Try using a OLEDB connection instead of a DSN.

Are there any parameters for this stored procedure? I do not see them here.

Maybe also try fully qualifying the stored procedure name because you might be hitting the wrong proc.

[databasename].[owner (usually dbo)].procedurename.|||a quick check that has worked for me has been to create an access db PROJECT (*.adp)
set the datasource to your sql server and the db that you want to connect to and try to access your stored procedures
if it works you can assume (to a degree) that you have parity.

Running sql script from Setup deployment .NET

Posted - 04/21/2004 : 09:26:34
----
--
Friends
I want to run sql script for creating of database which
contains tables and stored procedures by setup and deployment project
in VS.NET. I have created customaction and passing the string
containing script to the ExecuteNonQuery() procedure to run the script
The problem is where ever GO statement is there in the script it
is giving problem. I read somewhere that
" Your script must not include any go statements. These are used only by Que
ry Analyzer or the osql.exe utility. If they are present in your script when
using it with ADO.NET (as in this demo), then the ExecuteNonQuery will fail
."
If i remove Go from the script then it is giving exception again
telling that "Incorrect syntax near the keyword Procedure"
where ever create Procedure statement is there
Please help me in this regard.Sudhapec,
from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and
CREATE VIEW statements cannot be combined with other statements in a batch.
Batches are delimited by the keyword GO in a script, hence your problem. I
guess you could create mult
iple scripts - one main one and a separate script for each of the above comm
ands. Another (easier) alternative would be to not use ADO.NET for this step
, but OSQL. You could have a batch file which runs osql with your complete s
cript.
HTH,
Paul Ibison|||Or, read the script, line by line, and each time you encounter GO, execute w
hat you have in the buffer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8781168A-8324-4DE5-ACDA-C975D4B21EEF@.microsoft.com...
> Sudhapec,
> from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREAT
E VIEW statements cannot
be combined with other statements in a batch. Batches are delimited by the k
eyword GO in a script, hence your
problem. I guess you could create multiple scripts - one main one and a sepa
rate script for each of the above
commands. Another (easier) alternative would be to not use ADO.NET for this
step, but OSQL. You could have a
batch file which runs osql with your complete script.
> HTH,
> Paul Ibison|||Below a link to a VB.Net code example that uses the technique Tibor
mentioned:
<http://groups.google.com/groups?hl=...threadm=mK%252c
.2084%24Cm3.67%40newsread3.news.pas.earthlink.net&rnum=8&prev=/groups%3Fas_
q
%3Dsqlcommand%2520go%26safe%3Dimages%26i
e%3DUTF-8%26oe%3DUTF-8%26as_uauthors
%3DDan%2520Guzman%26lr%3D%26hl%3Den>
Hope this helps.
Dan Guzman
SQL Server MVP
"sudhapec" <sudhapec.153vii@.mail.webservertalk.com> wrote in message
news:sudhapec.153vii@.mail.webservertalk.com...
> Posted - 04/21/2004 : 09:26:34
> ----
--
> Friends
> I want to run sql script for creating of database which
> contains tables and stored procedures by setup and deployment project
> in VS.NET. I have created customaction and passing the string
> containing script to the ExecuteNonQuery() procedure to run the script
> The problem is where ever GO statement is there in the script it
> is giving problem. I read somewhere that
> " Your script must not include any go statements. These are used only
> by Query Analyzer or the osql.exe utility. If they are present in your
> script when using it with ADO.NET (as in this demo), then the
> ExecuteNonQuery will fail."
> If i remove Go from the script then it is giving exception again
> telling that "Incorrect syntax near the keyword Procedure"
> where ever create Procedure statement is there
> Please help me in this regard.
>
> --
> sudhapec
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message190433.html
>

Running sql script from Setup deployment .NET

Posted - 04/21/2004 : 09:26:34
Friends
I want to run sql script for creating of database which
contains tables and stored procedures by setup and deployment project
in VS.NET. I have created customaction and passing the string
containing script to the ExecuteNonQuery() procedure to run the script
The problem is where ever GO statement is there in the script it
is giving problem. I read somewhere that
" Your script must not include any go statements. These are used only
by Query Analyzer or the osql.exe utility. If they are present in your
script when using it with ADO.NET (as in this demo), then the
ExecuteNonQuery will fail."
If i remove Go from the script then it is giving exception again
telling that "Incorrect syntax near the keyword Procedure"
where ever create Procedure statement is there
Please help me in this regard.
sudhapec
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message190433.html
Sudhapec,
from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. Batches are delimited by the keyword GO in a script, hence your problem. I guess you could create mult
iple scripts - one main one and a separate script for each of the above commands. Another (easier) alternative would be to not use ADO.NET for this step, but OSQL. You could have a batch file which runs osql with your complete script.
HTH,
Paul Ibison
|||Or, read the script, line by line, and each time you encounter GO, execute what you have in the buffer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8781168A-8324-4DE5-ACDA-C975D4B21EEF@.microsoft.com...
> Sudhapec,
> from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot
be combined with other statements in a batch. Batches are delimited by the keyword GO in a script, hence your
problem. I guess you could create multiple scripts - one main one and a separate script for each of the above
commands. Another (easier) alternative would be to not use ADO.NET for this step, but OSQL. You could have a
batch file which runs osql with your complete script.
> HTH,
> Paul Ibison
|||Below a link to a VB.Net code example that uses the technique Tibor
mentioned:
<http://groups.google.com/groups?hl=e...hreadm=mK%252c
..2084%24Cm3.67%40newsread3.news.pas.earthlink.net &rnum=8&prev=/groups%3Fas_q
%3Dsqlcommand%2520go%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_uauthors
%3DDan%2520Guzman%26lr%3D%26hl%3Den>
Hope this helps.
Dan Guzman
SQL Server MVP
"sudhapec" <sudhapec.153vii@.mail.webservertalk.com> wrote in message
news:sudhapec.153vii@.mail.webservertalk.com...
> Posted - 04/21/2004 : 09:26:34
> ----
--
> Friends
> I want to run sql script for creating of database which
> contains tables and stored procedures by setup and deployment project
> in VS.NET. I have created customaction and passing the string
> containing script to the ExecuteNonQuery() procedure to run the script
> The problem is where ever GO statement is there in the script it
> is giving problem. I read somewhere that
> " Your script must not include any go statements. These are used only
> by Query Analyzer or the osql.exe utility. If they are present in your
> script when using it with ADO.NET (as in this demo), then the
> ExecuteNonQuery will fail."
> If i remove Go from the script then it is giving exception again
> telling that "Incorrect syntax near the keyword Procedure"
> where ever create Procedure statement is there
> Please help me in this regard.
>
> --
> sudhapec
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message190433.html
>

Running sql script from Setup deployment .NET

Posted - 04/21/2004 : 09:26:34
----
Friends
I want to run sql script for creating of database which
contains tables and stored procedures by setup and deployment project
in VS.NET. I have created customaction and passing the string
containing script to the ExecuteNonQuery() procedure to run the script
The problem is where ever GO statement is there in the script it
is giving problem. I read somewhere that
" Your script must not include any go statements. These are used onl
by Query Analyzer or the osql.exe utility. If they are present in you
script when using it with ADO.NET (as in this demo), then th
ExecuteNonQuery will fail."
If i remove Go from the script then it is giving exception again
telling that "Incorrect syntax near the keyword Procedure"
where ever create Procedure statement is there
Please help me in this regard
-
sudhape
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message190433.htmSudhapec
from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. Batches are delimited by the keyword GO in a script, hence your problem. I guess you could create multiple scripts - one main one and a separate script for each of the above commands. Another (easier) alternative would be to not use ADO.NET for this step, but OSQL. You could have a batch file which runs osql with your complete script
HTH
Paul Ibison|||Or, read the script, line by line, and each time you encounter GO, execute what you have in the buffer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8781168A-8324-4DE5-ACDA-C975D4B21EEF@.microsoft.com...
> Sudhapec,
> from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot
be combined with other statements in a batch. Batches are delimited by the keyword GO in a script, hence your
problem. I guess you could create multiple scripts - one main one and a separate script for each of the above
commands. Another (easier) alternative would be to not use ADO.NET for this step, but OSQL. You could have a
batch file which runs osql with your complete script.
> HTH,
> Paul Ibison|||Below a link to a VB.Net code example that uses the technique Tibor
mentioned:
<http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=mK%252c
.2084%24Cm3.67%40newsread3.news.pas.earthlink.net&rnum=8&prev=/groups%3Fas_q
%3Dsqlcommand%2520go%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_uauthors
%3DDan%2520Guzman%26lr%3D%26hl%3Den>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sudhapec" <sudhapec.153vii@.mail.webservertalk.com> wrote in message
news:sudhapec.153vii@.mail.webservertalk.com...
> Posted - 04/21/2004 : 09:26:34
> ----
--
> Friends
> I want to run sql script for creating of database which
> contains tables and stored procedures by setup and deployment project
> in VS.NET. I have created customaction and passing the string
> containing script to the ExecuteNonQuery() procedure to run the script
> The problem is where ever GO statement is there in the script it
> is giving problem. I read somewhere that
> " Your script must not include any go statements. These are used only
> by Query Analyzer or the osql.exe utility. If they are present in your
> script when using it with ADO.NET (as in this demo), then the
> ExecuteNonQuery will fail."
> If i remove Go from the script then it is giving exception again
> telling that "Incorrect syntax near the keyword Procedure"
> where ever create Procedure statement is there
> Please help me in this regard.
>
> --
> sudhapec
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message190433.html
>