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 VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?

> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo
> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?

> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?

> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?
> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Does anybody know why this statement wont run on a schedule for the
server agent job. I ran it as a TSQL statement and tried it as a
stored procedure and both failed. this is what I'm trying to run
below...
----
--
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked
server.
--
----
Declare
@.Table_Name varchar(255),
@.Sql Nvarchar(500)
----
-- Create a temporary table for storing result of sp_tables_ex
----
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)
----
-- Populate Temporary table with the results of the sp_tables_ex
command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
----
Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'
----
-- Create cursor for selecting the Table names from the linked server
----
Declare crsLinkedServerTables Cursor For
Select table_name
>From #tLinkedServerTables
Where Table_type = 'Table'
and table_name like 'CF%'
and table_name not like 'CF*%'
and table_name not like 'CFDAILY%' --test 'public.CF%'
-- Open the cursor defined above
Open crsLinkedServerTables
Fetch Next From crsLinkedServerTables Into @.Table_Name
While @.@.Fetch_Status = 0 Begin -- 0 = more records to process
-- Your Update process goes here... Need to use dynamic SQL to
create the
-- Query.
Set @.Sql = 'Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.' + @.table_name + ''') as Data Where
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''
print @.Sql
exec (@.Sql) /* Better to use sp_executesql, but this is here as
an example and should work */
-- Move to the next record
Fetch Next From crsLinkedServerTables Into @.Table_Name
End
----
-- Clean up (drop temp tables, remove cursors)
----
select * from #tLinkedServerTables
drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables
This is what it shows in the job history when I run it as a TSQL
statement and it fails.
... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
[Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
[SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_... The step failed.
I also tried to make a stored procedure and I get an error when I
execute it in a job as well. My job run statement exec [SCH CF
UPDATE]. This is the error I get below.
Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
[Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
[Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
= 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and then
reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.Hi Mike
"mike11d11" wrote:
> Does anybody know why this statement wont run on a schedule for the
> server agent job. I ran it as a TSQL statement and tried it as a
> stored procedure and both failed. this is what I'm trying to run
> below...
> ----
> --
> -- Following is a simple procedure for creating a temporary table
> -- that has the table_name of each of the user tables in the linked
> server.
> --
> ----
> Declare
> @.Table_Name varchar(255),
> @.Sql Nvarchar(500)
> ----
> -- Create a temporary table for storing result of sp_tables_ex
> ----
> Create Table #tLinkedServerTables
> (
> Table_Cat varchar(255),
> Table_schem varchar(255),
> Table_Name varchar(255),
> Table_Type varchar(255),
> Remarks varchar(255)
> )
> ----
> -- Populate Temporary table with the results of the sp_tables_ex
> command
> -- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
> -- valid linked server (already defined)
> ----
> Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'
> ----
> -- Create cursor for selecting the Table names from the linked server
> ----
> Declare crsLinkedServerTables Cursor For
> Select table_name
> >From #tLinkedServerTables
> Where Table_type = 'Table'
> and table_name like 'CF%'
> and table_name not like 'CF*%'
> and table_name not like 'CFDAILY%' --test 'public.CF%'
> -- Open the cursor defined above
> Open crsLinkedServerTables
> Fetch Next From crsLinkedServerTables Into @.Table_Name
> While @.@.Fetch_Status = 0 Begin -- 0 = more records to process
> -- Your Update process goes here... Need to use dynamic SQL to
> create the
> -- Query.
> Set @.Sql = 'Update WorkList SET [Tickler Last Action DT] => Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
> OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.' + @.table_name + ''') as Data Where
> Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''
> print @.Sql
> exec (@.Sql) /* Better to use sp_executesql, but this is here as
> an example and should work */
> -- Move to the next record
> Fetch Next From crsLinkedServerTables Into @.Table_Name
> End
> ----
> -- Clean up (drop temp tables, remove cursors)
> ----
> select * from #tLinkedServerTables
> drop table #tLinkedServerTables
> Close crsLinkedServerTables
> Deallocate crsLinkedServerTables
>
>
>
>
>
> This is what it shows in the job history when I run it as a TSQL
> statement and it fails.
>
> ... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
> Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
> [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
> Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
> [SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
> DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
> FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID => WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] => Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
> OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID => WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] => Data.[Last_... The step failed.
>
> I also tried to make a stored procedure and I get an error when I
> execute it in a job as well. My job run statement exec [SCH CF
> UPDATE]. This is the error I get below.
> Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
> [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
> [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
> as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
> = 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
> ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
> ensures consistent query semantics. Enable these options and then
> reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.
>
Have you tried SELECT @.@.OPTIONS in the script to see if ANSI_NULLS and
ANSI_WARNINGS are set? If not try setting them.
John|||It's in the error message you got:
Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and
then reissue your query.
Set the ansi settings in the job script, something like:
SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO
<your job stuff here>
Or try recreating your stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourSP...etc.
-Sue
On 26 Jan 2007 11:48:53 -0800, "mike11d11"
<mike11d11@.yahoo.com> wrote:
>Does anybody know why this statement wont run on a schedule for the
>server agent job. I ran it as a TSQL statement and tried it as a
>stored procedure and both failed. this is what I'm trying to run
>below...
>----
>--
>-- Following is a simple procedure for creating a temporary table
>-- that has the table_name of each of the user tables in the linked
>server.

Running Transact-SQL script from SQL server agent with a different account

Hi,
I whised to run a T-SQL script as a SQL server Job that uses a linked
server to connect to a remote DB.
When I run the stored procedure in Query Analyser (with a specified
user) everything works fone but on SQL Agent I have the following
error:
Access to the remote server is denied because the current security
context is not trusted
How can I specify with wich user should the step run? The "run as" list
on Job Step window is disabled. How to use the new credential?
(The user exists on local and remote server).
ThanksHi
You don't say if your SQL Agent Service is running under a domain account or
not? At a guess you are using LOCALSYSTEM? See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
John
"pedro.f.silva@.gmail.com" wrote:
> Hi,
> I whised to run a T-SQL script as a SQL server Job that uses a linked
> server to connect to a remote DB.
> When I run the stored procedure in Query Analyser (with a specified
> user) everything works fone but on SQL Agent I have the following
> error:
> Access to the remote server is denied because the current security
> context is not trusted
> How can I specify with wich user should the step run? The "run as" list
> on Job Step window is disabled. How to use the new credential?
> (The user exists on local and remote server).
> Thanks
>|||It is using LOCALSYSTEM, I think.
But I would like to use a domain account just for this job. Is it
possible?
Why cannot i use "run as" on job step?
I thought that could be done creating a credential and a proxy but SQL
server agent doesn't allow proxys on T-SQL steps.
What can I do?
John Bell escreveu:
> Hi
> You don't say if your SQL Agent Service is running under a domain account or
> not? At a guess you are using LOCALSYSTEM? See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Hi,
> >
> > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > server to connect to a remote DB.
> > When I run the stored procedure in Query Analyser (with a specified
> > user) everything works fone but on SQL Agent I have the following
> > error:
> >
> > Access to the remote server is denied because the current security
> > context is not trusted
> >
> > How can I specify with wich user should the step run? The "run as" list
> > on Job Step window is disabled. How to use the new credential?
> >
> > (The user exists on local and remote server).
> >
> > Thanks
> >
> >|||Hi
With a T-SQL step there is a run as user on the avanced options if you are
running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
John
"pedro.f.silva@.gmail.com" wrote:
> It is using LOCALSYSTEM, I think.
> But I would like to use a domain account just for this job. Is it
> possible?
> Why cannot i use "run as" on job step?
> I thought that could be done creating a credential and a proxy but SQL
> server agent doesn't allow proxys on T-SQL steps.
> What can I do?
> John Bell escreveu:
> > Hi
> >
> > You don't say if your SQL Agent Service is running under a domain account or
> > not? At a guess you are using LOCALSYSTEM? See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > server to connect to a remote DB.
> > > When I run the stored procedure in Query Analyser (with a specified
> > > user) everything works fone but on SQL Agent I have the following
> > > error:
> > >
> > > Access to the remote server is denied because the current security
> > > context is not trusted
> > >
> > > How can I specify with wich user should the step run? The "run as" list
> > > on Job Step window is disabled. How to use the new credential?
> > >
> > > (The user exists on local and remote server).
> > >
> > > Thanks
> > >
> > >
>|||I had noticed that there was a "run as" user on advanced options but it
didn't work.
I was expecting it (no working) since there's no place to specify the
user password. How could it run with that identify if I hadn't
specified the password? But there's no place to do it!
John Bell escreveu:
> Hi
> With a T-SQL step there is a run as user on the avanced options if you are
> running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > It is using LOCALSYSTEM, I think.
> >
> > But I would like to use a domain account just for this job. Is it
> > possible?
> >
> > Why cannot i use "run as" on job step?
> >
> > I thought that could be done creating a credential and a proxy but SQL
> > server agent doesn't allow proxys on T-SQL steps.
> >
> > What can I do?
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > You don't say if your SQL Agent Service is running under a domain account or
> > > not? At a guess you are using LOCALSYSTEM? See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > server to connect to a remote DB.
> > > > When I run the stored procedure in Query Analyser (with a specified
> > > > user) everything works fone but on SQL Agent I have the following
> > > > error:
> > > >
> > > > Access to the remote server is denied because the current security
> > > > context is not trusted
> > > >
> > > > How can I specify with wich user should the step run? The "run as" list
> > > > on Job Step window is disabled. How to use the new credential?
> > > >
> > > > (The user exists on local and remote server).
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I would suggest that you create an account to use for a service account and
change the service account to be that.
John
"pedro.f.silva@.gmail.com" wrote:
> I had noticed that there was a "run as" user on advanced options but it
> didn't work.
> I was expecting it (no working) since there's no place to specify the
> user password. How could it run with that identify if I hadn't
> specified the password? But there's no place to do it!
>
> John Bell escreveu:
> > Hi
> >
> > With a T-SQL step there is a run as user on the avanced options if you are
> > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > It is using LOCALSYSTEM, I think.
> > >
> > > But I would like to use a domain account just for this job. Is it
> > > possible?
> > >
> > > Why cannot i use "run as" on job step?
> > >
> > > I thought that could be done creating a credential and a proxy but SQL
> > > server agent doesn't allow proxys on T-SQL steps.
> > >
> > > What can I do?
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > not? At a guess you are using LOCALSYSTEM? See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > server to connect to a remote DB.
> > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > user) everything works fone but on SQL Agent I have the following
> > > > > error:
> > > > >
> > > > > Access to the remote server is denied because the current security
> > > > > context is not trusted
> > > > >
> > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > on Job Step window is disabled. How to use the new credential?
> > > > >
> > > > > (The user exists on local and remote server).
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>|||Thanks John.
That works if I want all my Jobs to run with the same user.
Since I only have one it's ok :) - SQL server agent runs with the
account I need to invoke the Stored Procedure.
It would be nice however to be able to run different T-SQL Jobs with
different users.
John Bell escreveu:
> Hi
> I would suggest that you create an account to use for a service account and
> change the service account to be that.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > I had noticed that there was a "run as" user on advanced options but it
> > didn't work.
> >
> > I was expecting it (no working) since there's no place to specify the
> > user password. How could it run with that identify if I hadn't
> > specified the password? But there's no place to do it!
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > With a T-SQL step there is a run as user on the avanced options if you are
> > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > It is using LOCALSYSTEM, I think.
> > > >
> > > > But I would like to use a domain account just for this job. Is it
> > > > possible?
> > > >
> > > > Why cannot i use "run as" on job step?
> > > >
> > > > I thought that could be done creating a credential and a proxy but SQL
> > > > server agent doesn't allow proxys on T-SQL steps.
> > > >
> > > > What can I do?
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > >
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > server to connect to a remote DB.
> > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > error:
> > > > > >
> > > > > > Access to the remote server is denied because the current security
> > > > > > context is not trusted
> > > > > >
> > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > >
> > > > > > (The user exists on local and remote server).
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
I don't know what your job is doing or why it needs to communicate with the
remote server, but it seems to be your only option with your current
implementation. If you used SQL 2005 there are more alternatives.
John
"pedro.f.silva@.gmail.com" wrote:
> Thanks John.
> That works if I want all my Jobs to run with the same user.
> Since I only have one it's ok :) - SQL server agent runs with the
> account I need to invoke the Stored Procedure.
> It would be nice however to be able to run different T-SQL Jobs with
> different users.
>
> John Bell escreveu:
> > Hi
> >
> > I would suggest that you create an account to use for a service account and
> > change the service account to be that.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > I had noticed that there was a "run as" user on advanced options but it
> > > didn't work.
> > >
> > > I was expecting it (no working) since there's no place to specify the
> > > user password. How could it run with that identify if I hadn't
> > > specified the password? But there's no place to do it!
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > It is using LOCALSYSTEM, I think.
> > > > >
> > > > > But I would like to use a domain account just for this job. Is it
> > > > > possible?
> > > > >
> > > > > Why cannot i use "run as" on job step?
> > > > >
> > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > >
> > > > > What can I do?
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > >
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > server to connect to a remote DB.
> > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > error:
> > > > > > >
> > > > > > > Access to the remote server is denied because the current security
> > > > > > > context is not trusted
> > > > > > >
> > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > >
> > > > > > > (The user exists on local and remote server).
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||I am using SQL Server 2005 :)
Which alternatives do I have?
John Bell escreveu:
> Hi
> I don't know what your job is doing or why it needs to communicate with the
> remote server, but it seems to be your only option with your current
> implementation. If you used SQL 2005 there are more alternatives.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Thanks John.
> >
> > That works if I want all my Jobs to run with the same user.
> >
> > Since I only have one it's ok :) - SQL server agent runs with the
> > account I need to invoke the Stored Procedure.
> >
> > It would be nice however to be able to run different T-SQL Jobs with
> > different users.
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > I would suggest that you create an account to use for a service account and
> > > change the service account to be that.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > I had noticed that there was a "run as" user on advanced options but it
> > > > didn't work.
> > > >
> > > > I was expecting it (no working) since there's no place to specify the
> > > > user password. How could it run with that identify if I hadn't
> > > > specified the password? But there's no place to do it!
> > > >
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > It is using LOCALSYSTEM, I think.
> > > > > >
> > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > possible?
> > > > > >
> > > > > > Why cannot i use "run as" on job step?
> > > > > >
> > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > >
> > > > > > What can I do?
> > > > > >
> > > > > > John Bell escreveu:
> > > > > > > Hi
> > > > > > >
> > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > >
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > server to connect to a remote DB.
> > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > error:
> > > > > > > >
> > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > context is not trusted
> > > > > > > >
> > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > >
> > > > > > > > (The user exists on local and remote server).
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Look at the using extensions to the EXECUTE command. But first read
http://www.sommarskog.se/grantperm.html
John
"pedro.f.silva@.gmail.com" wrote:
> I am using SQL Server 2005 :)
> Which alternatives do I have?
> John Bell escreveu:
> > Hi
> >
> > I don't know what your job is doing or why it needs to communicate with the
> > remote server, but it seems to be your only option with your current
> > implementation. If you used SQL 2005 there are more alternatives.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Thanks John.
> > >
> > > That works if I want all my Jobs to run with the same user.
> > >
> > > Since I only have one it's ok :) - SQL server agent runs with the
> > > account I need to invoke the Stored Procedure.
> > >
> > > It would be nice however to be able to run different T-SQL Jobs with
> > > different users.
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > I would suggest that you create an account to use for a service account and
> > > > change the service account to be that.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > I had noticed that there was a "run as" user on advanced options but it
> > > > > didn't work.
> > > > >
> > > > > I was expecting it (no working) since there's no place to specify the
> > > > > user password. How could it run with that identify if I hadn't
> > > > > specified the password? But there's no place to do it!
> > > > >
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > It is using LOCALSYSTEM, I think.
> > > > > > >
> > > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > > possible?
> > > > > > >
> > > > > > > Why cannot i use "run as" on job step?
> > > > > > >
> > > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > > >
> > > > > > > What can I do?
> > > > > > >
> > > > > > > John Bell escreveu:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > > >
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > > server to connect to a remote DB.
> > > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > > error:
> > > > > > > > >
> > > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > > context is not trusted
> > > > > > > > >
> > > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > > >
> > > > > > > > > (The user exists on local and remote server).
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>sql

running total from stored proc

i need to return the number of rows from a query as something
eg select ID,column from table where column = something
then return ID,column,expr1 (which is the total of records returned)
is this possible ?
my query looks like
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')mark
You can look at an OUTPUT parameters in stored procedure (see BOL)
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.ret INT
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
GROUP BY DateLeadReceived ,LeadLoggedBy,Type
RETURN @.ret
"mark" <mark@.remove.com> wrote in message
news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
> i need to return the number of rows from a query as something
> eg select ID,column from table where column = something
> then return ID,column,expr1 (which is the total of records returned)
> is this possible ?
> my query looks like
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
>
>
>|||Uri
What's the difference between using return @.var or using output paramter ?
"Uri Dimant" wrote:

> mark
> You can look at an OUTPUT parameters in stored procedure (see BOL)
>
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.ret INT
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
> GROUP BY DateLeadReceived ,LeadLoggedBy,Type
> RETURN @.ret
>
> "mark" <mark@.remove.com> wrote in message
> news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
>
>|||Well , a difference as you said RETURN (unlike OUTPUT) statement terminates
the batch and none of the statements within SP are executed. RETURN
statement is usually used to reterun an error number or @.@.idenitity value.
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||One is a return value and the other is output parameters. For a return value
, it can only be int and
most of us only use this to communicate success/error. You can have several
output parameters and
they can be of almost any datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||that code didnt seem to work, but ive solved the problem like this
thanks
mark
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
declare @.total int
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
select @.total=@.@.rowcount
select @.total as totalcolumn
GO

Running total count in stored procedure

in my procedure, I want to count the number of rows that have errored
during an insert statement - each row is evaluated using a cursor, so
I am processing one row at a time for the insert. My total count to
be displayed is inside the cursor, but after the last fetch is called.
Wouldn't this display the last count? The problem is that the count is
always 1. Can anyone help?

here is my code,

... cursor fetch
begin ... cursor
if error then:
begin

INSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,
CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,
STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,
PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,
USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,
CUSTINDICATOR, DT_LOADED)
VALUES(@.ERRORNUM, @.ERRORDESC,
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY,
@.STATE, @.POSTALCODE, @.CONTACT, @.PHONE, @.SALESREPCODE,
@.PRICELEVEL, @.TERMSCODE, @.DISCPERCENT, @.TAXCODE,
@.USERCOMMENT, @.CURRENCY, @.EMAILADDRESS, @.CUSTOMERGROUP,
@.CUSTINDICATOR, @.DTLOADED)

SET @.ERRORCNT = @.ERRORCNT + 1

END --error

--
FETCH NEXT FROM CERNO_US INTO
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY, @.STATE,
@.POSTALCODE, @.CONTACT,@.PHONE,@.SALESREPCODE, @.PRICELEVEL,@.TERMSCODE,
@.DISCPERCENT, @.TAXCODE, @.USERCOMMENT, @.CURRENCY,@.EMAILADDRESS,
@.CUSTOMERGROUP, @.CUSTINDICATOR, @.DTLOADED
--
IF @.ERRORCNT > 0
INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,
STATUS)
VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @.ERRORCNT, 'FAILED
INSERT/UPDATE')

END -- cursor
CLOSE CERNO_US
DEALLOCATE CERNO_USTracey (tracey.lemer@.itsservices.com) writes:
> in my procedure, I want to count the number of rows that have errored
> during an insert statement - each row is evaluated using a cursor, so
> I am processing one row at a time for the insert. My total count to
> be displayed is inside the cursor, but after the last fetch is called.
> Wouldn't this display the last count? The problem is that the count is
> always 1. Can anyone help?

As I read your code, you insert a row into PROCEDURE_RESULTS as soon as
@.ERRORCNT is > 0, that is 1.

I don't know what you mean with "be displayed is inside the cursor, but
after the last fetch is called" - that makes no sense to me. You don't
really display the data what I can see, and you should just as well
insert into PROCEDURE_RESULTS after you have dealloacated the cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

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
>