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
> 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.
sql

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 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 W
ork
Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID
],
[Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Whe
re
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
[SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Acti
on
DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Wor
k_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_Dat
e],
[Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
[Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.C
FAB1')
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.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 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 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 procedure multiple times

I'm binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I'm using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?

newSqlCommand = New SqlCommand("getDistinctValues", newConn)
newSqlCommand.CommandType = CommandType.StoredProcedure

Dim ownrParam As New SqlParameter("@.owner_id", SqlDbType.Int)
Dim colParam As New SqlParameter("@.column_name", SqlDbType.VarChar)
newSqlCommand.Parameters.Add(ownrParam)
newSqlCommand.Parameters.Add(colParam)

ownrParam.Value = OwnerID

colParam.Value = "Make"
newConn.Open()
ddlMake.DataSource = newSqlCommand.ExecuteReader()
ddlMake.DataTextField = "distinct_result"
ddlMake.DataBind()
newConn.Close()

colParam.Value = "Model"
newConn.Open()
ddlModel.DataSource = newSqlCommand.ExecuteReader()
ddlModel.DataTextField = "distinct_result"
ddlModel.DataBind()
newConn.Close()

and so on for 9 columns…

First of all, reusing the connection will increase your performance significantly.

|||

Hi

if you create a stored procedure returning multiple result sets and load these result sets into the DataSet, you will avoid multiple roundtrips to SQL server.

-yuriy

|||

Under ADO.NET, only the first Open opens the database, the rest pick up a connection from a pool as closing the database connection does not actually close the connection but returns it to a pool. You still open and close in your code, but what happens behind the scenes is slightly different from previous access methods such as ADO.

|||

Thanks guys.

Tatworth, I believe you are saying that I'm reusing the same connection by doing it this way. In that case, is it an acceptable and reasonably efficient way to do things?

I need the DISTINCT values for these columns, so I can't simply select all the values for all 9 columns at once, unless I'm prepared to eliminate repeated values in code. Is it possible for a single stored procedure to return 9 sets of distinct values?

|||

>Tatworth, I believe you are saying that I'm reusing the sameconnection by doing it this way. In that case, is it an acceptable andreasonably efficient way to do things? yes

> need the DISTINCT values for these columns, so I can't simplyselect all the values for all 9 columns at once, unless I'm prepared toeliminate repeated values in code. Is it possible for a single storedprocedure to return 9 sets of distinct values?
It is possible as a stored procedure can return multiple recordsets but not practical as ASP.NET can only read the first recordset without extra code - so keep doing it the way you are doing it now.

I will be unable to access this site for some days, so I suggest you mark this thread as closed and open a new thread if you have further queries.

|||

There are multiple approaches to handling the above depending on your situation, and the data involved.

Normally, I would use a sqldatasource (9 of them), and bind the dropdowns to the datasources, and evaluate from there if necessary. The SqlDatasource control is nice because you can quickly and easily add caching if necessary (If you think that the ownerid will be called multiple times in a short period of time and the results should always remain the same for each call, then it'd be very helpful).

However, since you've gone down the approach of handling it manually, the only thing that is going to really buy you anything (other than coding any caching yourself), is to return the results in a single trip from the database. One such method would be to do something like return a single resultset that has 2 values, one being the column name, the other the value (one of the distinct values) likes:
col1 val1

col1 val2

col1 val3

col2 val1

col2 val2

Then you can retrieve this resultset from the database into a dataset. With the dataset, you can then derive a view that has a filter clause (Like a WHERE clause), and bind to the dataview.

Another approach, although I have not personally tested this is to use the datareaders nextresult property. Like:

conn.open

dim dr as sqldatareader=cmd.executereader

{Your code to prep control 1 here}

control1.datasource=dr

control1.databind

{Ending stuff for control 1 here}

dr.nextresult

{Your code to prep control 2 here}

control2.datasource=dr

control2.databind

{Ending stuff for contorl 2 here}

dr.nextresult

...

This approach would be extremely easy to modify your current code into, as you would just make need to make a master stored procedure like:

CREATE PROCEDURE dbo.sp_TheMaster ( @.ownerid int) AS

EXEC sp_OriginalProcedure @.ownerid,'column1'

EXEC sp_OriginalProcedure @.ownerid,'column2'

...

That would return the 9 resultsets that you need, although I'm not sure of it's performance, but I suspect it would be better.

|||

Thanks for your detailed response Motley. I considered the sqldatasource – I think it would be possible to use a single datasource for all 9 queries – but I chose the RP option only because it seems tidier to me (less 'markup'). I'm assuming there would be no performance difference?

As time is an issue, and as it seems that what I'm doing is not disastrous in performance terms, I'll leave it and monitor it when the site is uploaded.

Thanks all for your input.

running stored procedure every 30 minutes

is there a way to run a stored procedure every 30 minutes or so? its a
simple insert statement that i want to run.
thanks,
kam
Set up a SQL Server Agent Job
Via the Enterprise Manager for your database choose
Management -> Jobs
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam
|||Yes, look at SQL Server Agent in Books Online, it has a concept called
scheduled jobs that provide exactly this functionality. You can see
http://www.aspfaq.com/2403 for a very casual walk through the GUI.
http://www.aspfaq.com/
(Reverse address to reply.)
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam
|||> Via the Enterprise Manager for your database choose
> Management -> Jobs
Well, just to be explicit, the management node is at the server level, not
the database level.
A
|||Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
the sentence. Mr Granfield would kill me if he saw that run on sentence.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Well, just to be explicit, the management node is at the server level, not
> the database level.
> A
>
|||Actually, what I thought you were implying was that there was a
management/jobs section within each database within a specific server in
Enterprise Manager, when of course management is server-wide so it appears
at the same level as databases.
http://www.aspfaq.com/
(Reverse address to reply.)
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:#o2SE50yEHA.2692@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
> the sentence. Mr Granfield would kill me if he saw that run on sentence.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
not
>

running stored procedure every 30 minutes

is there a way to run a stored procedure every 30 minutes or so? its a
simple insert statement that i want to run.
thanks,
kamSet up a SQL Server Agent Job
Via the Enterprise Manager for your database choose
Management -> Jobs
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||Yes, look at SQL Server Agent in Books Online, it has a concept called
scheduled jobs that provide exactly this functionality. You can see
http://www.aspfaq.com/2403 for a very casual walk through the GUI.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||> Via the Enterprise Manager for your database choose
> Management -> Jobs
Well, just to be explicit, the management node is at the server level, not
the database level.
A|||Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
the sentence. Mr Granfield would kill me if he saw that run on sentence.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> > Via the Enterprise Manager for your database choose
> >
> > Management -> Jobs
> Well, just to be explicit, the management node is at the server level, not
> the database level.
> A
>|||Actually, what I thought you were implying was that there was a
management/jobs section within each database within a specific server in
Enterprise Manager, when of course management is server-wide so it appears
at the same level as databases.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:#o2SE50yEHA.2692@.TK2MSFTNGP10.phx.gbl...
> Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
> the sentence. Mr Granfield would kill me if he saw that run on sentence.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> > > Via the Enterprise Manager for your database choose
> > >
> > > Management -> Jobs
> >
> > Well, just to be explicit, the management node is at the server level,
not
> > the database level.
> >
> > A
> >
> >
>

running stored procedure every 30 minutes

is there a way to run a stored procedure every 30 minutes or so? its a
simple insert statement that i want to run.
thanks,
kamSet up a SQL Server Agent Job
Via the Enterprise Manager for your database choose
Management -> Jobs
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||Yes, look at SQL Server Agent in Books Online, it has a concept called
scheduled jobs that provide exactly this functionality. You can see
http://www.aspfaq.com/2403 for a very casual walk through the GUI.
http://www.aspfaq.com/
(Reverse address to reply.)
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||> Via the Enterprise Manager for your database choose
> Management -> Jobs
Well, just to be explicit, the management node is at the server level, not
the database level.
A|||Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
the sentence. Mr Granfield would kill me if he saw that run on sentence.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Well, just to be explicit, the management node is at the server level, not
> the database level.
> A
>|||Actually, what I thought you were implying was that there was a
management/jobs section within each database within a specific server in
Enterprise Manager, when of course management is server-wide so it appears
at the same level as databases.
http://www.aspfaq.com/
(Reverse address to reply.)
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:#o2SE50yEHA.2692@.TK2MSFTNGP10.phx.gbl...
> Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
> the sentence. Mr Granfield would kill me if he saw that run on sentence.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
not[vbcol=seagreen]
>