ASP. NET vs. PL/SQL

Hey guys, I am trying to rewrite an application with an Active Server Pages front-end that calls Oracle WebDB v2.2 reports. The data is all stored in an Oracle 9i database. What are some of the benefits and drawbacks of redesigning the application using an ASP .NET or PL/SQL
implementation. The redesigned application should still retrieve the data from the Oracle 9i database. Any help with this is greatly appreciated.When it comes to database development PL/SQL has the advantage since it is very flexible with Oracle and it seperates the business logic from the interface. But it lacks presentation in a nice way.

ASP.NET on the other hand can be developed to display data in very nice way. Performance wise PL/SQL would be better since it directly runs in the database server.

If the presentation of the report is not an issue then go for PL/SQL, or else you can go for ASP.NET

But remember to eveluate your skill level also on both and decide on one.

ASP, AS400 (iSeries), and stored procedures.

Does anyone have any info on how to call a stored procedure with asp classic
? Is it possible? I'm running client access v5r2.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Yes it's possible. It's pretty much like calling any stored procedure. I've
done this using both the IBM Client Access ODBC driver as well as the IBM
Client access OLE DB Provider. The Data Access components need to be
installed on the system running the ASP scripts.
Mike O.
"ryadex" <ryadex@.hotmail.com> wrote in message
news:eHQZfJt1DHA.4032@.tk2msftngp13.phx.gbl...

quote:

---

> Does anyone have any info on how to call a stored procedure with asp

---

classic? Is it possible? I'm running client access v5r2.

quote:

---

>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &

---

ASP.NET resources...sql

ASP sp execution returning closed recordset

Can anybody tell me why a) when running a stored proc from an asp page to
return a recordset the command succeeds if the sp queries an existing table
directly, but b) if the stored proc populates results into a different
table, temporary table, global temp table, or table variable, then queries
one of these, the asp page reports that the recordset object is closed. If
using a table, I have set grant, select, update, delete permissions for the
asp page user account, so it doesn't appear to be a permissioning issue. If
run in Query Analyser the sp runs fine of course.

Abridged asp code is as follows:
StoredProc = Request.querystring("SP")
oConn.ConnectionString = "Provider=SQLOLEDB etc"
oConn.Open
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandText = StoredProc
oCmd.CommandType = adCmdStoredProc
oCmd.Parameters.Refresh
'code here that populates the parameters of the oCmd object correctly
Set oRs = Server.CreateObject("ADODB.Recordset")
With oRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
'execute the SP returning the result into a recordset
.Open oCmd
End With
' Save data into IIS response object
Response.ContentType = "text/xml"
oRs.Save Response, adPersistXML
'the line above fails with stored procs from example B below, reporting "not
allowed when object is closed", but works with example A

SP Example A - this one works fine
Create Proc spTestA AS
SELECT ID FROM FileList
GO

SP Example B - this one doesn't work from ASP but runs fine in QA
Create Proc spTestB AS
DECLARE @.Results Table (ID TinyInt)
INSERT INTO @.Results SELECT ID FROM FileList
SELECT ID FROM @.Results
GO

I can see the SP executing using profiler when the asp page is called for
both sp's above, so it doesn't appear to be a problem with the execution.
It's something to do with returning the result set from the table variable.

Thanks,

Robin Hammond"Robin Hammond" wrote:

<snip
> SP Example B - this one doesn't work from ASP but runs fine in QA
> Create Proc spTestB AS
> DECLARE @.Results Table (ID TinyInt)
> INSERT INTO @.Results SELECT ID FROM FileList
> SELECT ID FROM @.Results
> GO

<snip
Robin,

The problem is that you're getting back a closed recordset with "records
affected" info from SQL Server: using the NextRecordset method in ADO will
get the actual recordset you're looking for. A good rule of thumb is to
watch the output from a stored proc in QA: anytime you see a resultset or a
message about records affected, then you know this could pop up.

A more efficient solution (and the one I prefer) if you don't need any data
back but the result of the SELECT is to use SET NOCOUNT...

Create Proc spTestB AS
SET NOCOUNT ON
DECLARE @.Results Table (ID TinyInt)
INSERT INTO @.Results SELECT ID FROM FileList

SET NOCOUNT OFF
SELECT ID FROM @.Results
GO

Craigsql

ASP Jscript to obtain Recordset & o/p params

Does anyone know how to obtain the returned recordsets and output parameters
from a stored procedure by using ASP Jscript?
e.g.: I've got a sproc,
create proc testsp
@.strSN varchar(30), --<-- as i/p param for serial #
@.iProdNum int output --<-- as o/p param for a specific reason
as
select * from Inventory
...
set @.iProdNum= (an integer for o/p param)
Therefore, I wrote:
<%@.LANGUAGE="JAVASCRIPT"%>
<!--#include virtual="/Connections/cnInv.asp" -->
<%
var cmdGetItem = Server.CreateObject("ADODB.Command");
cmdGetItem.ActiveConnection = MM_cnInv_STRING;
cmdGetItem.CommandText = "dbo.testsp";
cmdGetItem.CommandType = 4;
cmdGetItem.CommandTimeout = 0;
cmdGetItem.Prepared = true;
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.RETURN_VALUE",
3, 4,4));
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.strSN", 200,
1,30, String(Request.Form("txtSN"))));
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.iProdNum", 3,
2,4));
var oRst=cmdGetItem.Execute();
// now --> oRst <-- holds the returned recordset by (select * from
Inventory)
// but neither --> cmdGetItem.Parameters.Item("@.RETURN_VALUE").Value <--
// nor --> cmdGetItem.Parameters.Item("@.iProdNum").Value <-- contains
nothing
%>
However, I only get the recordset returned by (select * from inventory) but
nothing from @.iProdNum by my ASP Jscript.
Thanks,
LeonardPADO will return output parameters in a separate recordset. You'll need to
use the NextRecordset method after retrieving the SELECT results. I don't
know JScript but try something like the following after processing the query
results:
oRst = oRst.NextRecordset;
Hope this helps.
Dan Guzman
SQL Server MVP
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:eEEeVsqNFHA.3760@.TK2MSFTNGP12.phx.gbl...
> Does anyone know how to obtain the returned recordsets and output
> parameters
> from a stored procedure by using ASP Jscript?
> e.g.: I've got a sproc,
> create proc testsp
> @.strSN varchar(30), --<-- as i/p param for serial #
> @.iProdNum int output --<-- as o/p param for a specific reason
> as
> select * from Inventory
> ...
> set @.iProdNum= (an integer for o/p param)
> Therefore, I wrote:
> <%@.LANGUAGE="JAVASCRIPT"%>
> <!--#include virtual="/Connections/cnInv.asp" -->
> <%
> var cmdGetItem = Server.CreateObject("ADODB.Command");
> cmdGetItem.ActiveConnection = MM_cnInv_STRING;
> cmdGetItem.CommandText = "dbo.testsp";
> cmdGetItem.CommandType = 4;
> cmdGetItem.CommandTimeout = 0;
> cmdGetItem.Prepared = true;
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.RETURN_VALUE",
> 3, 4,4));
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.strSN", 200,
> 1,30, String(Request.Form("txtSN"))));
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.iProdNum", 3,
> 2,4));
> var oRst=cmdGetItem.Execute();
> // now --> oRst <-- holds the returned recordset by (select * from
> Inventory)
> // but neither --> cmdGetItem.Parameters.Item("@.RETURN_VALUE").Value <--
> // nor --> cmdGetItem.Parameters.Item("@.iProdNum").Value <-- contains
> nothing
> %>
> However, I only get the recordset returned by (select * from inventory)
> but
> nothing from @.iProdNum by my ASP Jscript.
> Thanks,
> LeonardP
>|||Dan Guzman wrote:
> ADO will return output parameters in a separate recordset.
Really? I thought you had to use the Parameters collection ...
Are you sure?
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You are right that the parameters collection contains the output values.
What I mean is that the preceding recordset(s) need to be processed before
output parameter values can be retrieved from the collection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:ORIZDhsNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Dan Guzman wrote:
> Really? I thought you had to use the Parameters collection ...
> Are you sure?
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>|||Leonard Poon wrote:
> Does anyone know how to obtain the returned recordsets and output
> parameters from a stored procedure by using ASP Jscript?
>
ADO is ADO - whether it's being called by vbscript or jscript.
To retrieve output or return parameters, you need to either close the
recordset that is returned by your procedure, or retrieve the last record in
that recordset. The output and return parameter values are not sent until
the resultset is completely sent. My practice is to use GetRows to read the
data into an array and close the recordset, allowing me to access the output
and return parameter values. But this is slightly awkward in jscript, whose
arrays are not multidimensional. There is a a way to use GetRows in
jscript - do a google search for jscript and getrows to see how this is
done.
To suppress any extra resultsets from being generated by informational
messages, it is a good practice to use "SET NOCOUNT ON" at the beginnning of
all your procedures.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ASP error with stored procedure

SQL Server 2000 on Windows 2003 SP1 and IIS6
I'm getting the following error from my ASP page:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Formal parameter '@.mname' was defined as OUTPUT but the actual parameter
not declared OUTPUT.
However, the formal parameter '@.mname' was used in a previous stored
procedure accessed by the same command object and that parameter has been
been deleted prior to the call.
Here's the DDL for the procedure:
CREATE PROCEDURE InsertAndIdentifyNewStarter
@.peopleID INTEGER,
@.newstarterID BIGINT OUTPUT
AS
INSERT INTO newstarter (peopleID)
VALUES (@.peopleID)
SELECT @.newstarterID = SCOPE_IDENTITY()
GO
And the code snippet that's giving the problem:
Dim cmd, ln
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = "Provider=" & DBPROVIDER & ";" & CONNECTIONSTRING
.CommandType = adCmdStoredProc
.CommandText = "InsertAndIdentifyPerson"
.Parameters.Append .CreateParameter("@.fname", adVarChar, _
adParamInput, 100, firstname)
.Parameters.Append .CreateParameter("@.mname", adVarChar, _
adParamInput, 100, middlename)
.Parameters.Append .CreateParameter("@.sname", adVarChar, _
adParamInput, 100, lastname)
.Parameters.Append .CreateParameter("@.fullname", adVarChar, _
adParamInput, 200, firstname & " " & lastname)
.Parameters.Append .CreateParameter("@.datestarted", adDBDate, _
adParamInput, 10, DBStartDate)
.Parameters.Append .CreateParameter("@.enabled", adInteger, _
adParamInput, 10, 1)
.Parameters.Append .CreateParameter("@.existsinaccounts", _
adInteger, adParamInput, 10, 0)
.Parameters.Append .CreateParameter("@.loggedon", adInteger, _
adParamInput, 10, 0)
.Parameters.Append .CreateParameter("@.itstatus", adInteger, _
adParamInput, 10, 5) ' 5 = New Starter
.Parameters.Append .CreateParameter("@.pID", adInteger, _
adParamOutput, 10)
.Execute ln, , adExecuteNoRecords
pID = .Parameters("@.pID")
' Delete the parameters so that we can reuse the command object
While .Parameters.Count > 0
.Parameters.Delete 0
Wend
' Create a newstarter record for this person
.CommandText = "InsertAndIdentifyPerson"
.Parameters.Append .CreateParameter("@.peopleID", adBigInt, _
adParamInput, 10, pID)
.Parameters.Append .CreateParameter("@.newstarterID", _
adBigInt, adParamOutput, 10)
.Execute ln, , adExecuteNoRecords
' ** The preceding line produces the error **
newstarterID = .Parameters("@.newstarterID")
End With
Set cmd = Nothing
Any ideas what I'm doing wrong or what the problem is?
TIA,
Geoff> CREATE PROCEDURE InsertAndIdentifyNewStarterd">
> snip
> .CommandText = "InsertAndIdentifyPerson"
It looks to me like you are executing the wrong proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"Geoff Lane" <geoff@.nospam.gjctech.co.uk> wrote in message
news:Xns97EA7C83661FBgjctcswxnsrt@.207.46.248.16...
> SQL Server 2000 on Windows 2003 SP1 and IIS6
> I'm getting the following error from my ASP page:
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Formal parameter '@.mname' was defined as OUTPUT but the actual parameter
> not declared OUTPUT.
> However, the formal parameter '@.mname' was used in a previous stored
> procedure accessed by the same command object and that parameter has been
> been deleted prior to the call.
> Here's the DDL for the procedure:
> CREATE PROCEDURE InsertAndIdentifyNewStarter
> @.peopleID INTEGER,
> @.newstarterID BIGINT OUTPUT
> AS
> INSERT INTO newstarter (peopleID)
> VALUES (@.peopleID)
> SELECT @.newstarterID = SCOPE_IDENTITY()
> GO
> And the code snippet that's giving the problem:
> Dim cmd, ln
> Set cmd = Server.CreateObject("ADODB.Command")
> With cmd
> .ActiveConnection = "Provider=" & DBPROVIDER & ";" & CONNECTIONSTRING
> .CommandType = adCmdStoredProc
> .CommandText = "InsertAndIdentifyPerson"
> .Parameters.Append .CreateParameter("@.fname", adVarChar, _
> adParamInput, 100, firstname)
> .Parameters.Append .CreateParameter("@.mname", adVarChar, _
> adParamInput, 100, middlename)
> .Parameters.Append .CreateParameter("@.sname", adVarChar, _
> adParamInput, 100, lastname)
> .Parameters.Append .CreateParameter("@.fullname", adVarChar, _
> adParamInput, 200, firstname & " " & lastname)
> .Parameters.Append .CreateParameter("@.datestarted", adDBDate, _
> adParamInput, 10, DBStartDate)
> .Parameters.Append .CreateParameter("@.enabled", adInteger, _
> adParamInput, 10, 1)
> .Parameters.Append .CreateParameter("@.existsinaccounts", _
> adInteger, adParamInput, 10, 0)
> .Parameters.Append .CreateParameter("@.loggedon", adInteger, _
> adParamInput, 10, 0)
> .Parameters.Append .CreateParameter("@.itstatus", adInteger, _
> adParamInput, 10, 5) ' 5 = New Starter
> .Parameters.Append .CreateParameter("@.pID", adInteger, _
> adParamOutput, 10)
> .Execute ln, , adExecuteNoRecords
> pID = .Parameters("@.pID")
> ' Delete the parameters so that we can reuse the command object
> While .Parameters.Count > 0
> .Parameters.Delete 0
> Wend
> ' Create a newstarter record for this person
> .CommandText = "InsertAndIdentifyPerson"
> .Parameters.Append .CreateParameter("@.peopleID", adBigInt, _
> adParamInput, 10, pID)
> .Parameters.Append .CreateParameter("@.newstarterID", _
> adBigInt, adParamOutput, 10)
> .Execute ln, , adExecuteNoRecords
> ' ** The preceding line produces the error **
> newstarterID = .Parameters("@.newstarterID")
> End With
> Set cmd = Nothing
> Any ideas what I'm doing wrong or what the problem is?
> TIA,
> --
> Geoff|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:elcHsWflGHA.5044@.TK2MSFTNGP02.phx.gbl:

> It looks to me like you are executing the wrong proc.
D'Oh! That'll teach me to cut-and-paste code :(
Thanks - you are correct!
Geoff

ASP cannot run stored proc until the web user has run the proc in Query Analyzer

I have an ASP that has been working fine for several months, but it
suddenly broke. I wonder if windows update has installed some security
patch that is causing it.

The problem is that I am calling a stored procedure via an ASP
(classic, not .NET) , but nothing happens. The procedure doesn't work,
and I don't get any error messages.

I've tried dropping and re-creating the user and permissions, to no
avail. If it was a permissions problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.

I finally got it to work again, but this is not a viable solution for
our production environment:

1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.

After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.central_scrutinizer,

Does the ASP page hang, timeout or just return with no results? If it hangs
or times out, this may be happening:

1. The command is executed from the ASP page.
2. SQL Server needs to allocate more disk space (data or log) and performs
an autogrow of a large amount of disk space. This could take an extended
period of time.
3. The user cancels the ASP page or the page times out.
4. SQL Server cancels the transaction and also cancels the need for
additional disk space.
5. If steps 1-4 are repeated, you get the same result.
6. A user runs the stored procedure in QA allowing it to finish.
7. SQL Server allocates the addional space.
8. The ASP page now runs fine because the space has been allocated (until
the next time the database needs to allocate more space).

-- Bill

1. We would run the stored
"central_scrutinizer" <cbellur@.hotmail.comwrote in message
news:1172185833.914945.318670@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

>I have an ASP that has been working fine for several months, but it
suddenly broke. I wonder if windows update has installed some security
patch that is causing it.
>
The problem is that I am calling a stored procedure via an ASP
(classic, not .NET) , but nothing happens. The procedure doesn't work,
and I don't get any error messages.
>
I've tried dropping and re-creating the user and permissions, to no
avail. If it was a permissions problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.
>
I finally got it to work again, but this is not a viable solution for
our production environment:
>
1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.
>
After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.
>

ASP - Stored Procedure best practice?

Hello. I was wondering if anyone would recommend or point me to a best
practice method in programming a search field on an .asp page that calls a
SQL Server stored procedure to do 'LIKE' searches? The only method that
seems to accommodate this that comes to my mind as of now is using dynamic
sql in the stored procedure but hear that it's not good practice using
dynamic sql.
Thanks in advance.
JYou can pass the comparison string in as a parameter in a parameterized
query. If you want a "wild-card search", just append '%' to the end of the
string before you pass it in. Also it's a good idea to *not* pre-pend '%'
to the front of the search string.
"J" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23otfgI$OHHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hello. I was wondering if anyone would recommend or point me to a best
> practice method in programming a search field on an .asp page that calls a
> SQL Server stored procedure to do 'LIKE' searches? The only method that
> seems to accommodate this that comes to my mind as of now is using dynamic
> sql in the stored procedure but hear that it's not good practice using
> dynamic sql.
> Thanks in advance.
> J
>|||Hi Mike.
Do you know of a recommended method in doing this for a form with multiple
fields which allows a user to specify more criteria on multiple fields?
Seems like building the dynamic sql statement in the stored procedure with
the specified field parameters is the only method I can think of to do
multiple criteria searching but I guess I still would like to avoid.
Thanks for your info and reply. Much appreciated.
J
"Mike C#" <xyz@.xyz.com> wrote in message
news:ee266d$OHHA.1248@.TK2MSFTNGP02.phx.gbl...
> You can pass the comparison string in as a parameter in a parameterized
> query. If you want a "wild-card search", just append '%' to the end of
> the string before you pass it in. Also it's a good idea to *not* pre-pend
> '%' to the front of the search string.
> "J" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:%23otfgI$OHHA.4484@.TK2MSFTNGP02.phx.gbl...
>|||J (IDontLikeSpam@.Nowhere.com) writes:
> Do you know of a recommended method in doing this for a form with multiple
> fields which allows a user to specify more criteria on multiple fields?
> Seems like building the dynamic sql statement in the stored procedure with
> the specified field parameters is the only method I can think of to do
> multiple criteria searching but I guess I still would like to avoid.
I discuss a whole bunch of solutions in both dynamic and static SQL, as
well as some very interesting hybrids in an article on my web site:
http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Gonna take a look at this. Thanks Mike. Have a good weekend.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Gonna take a look at this. Thanks Mike. Have a good weekend.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sorry Erland. Didn't notice it was from someone else. Much thanks to you
also.
Take care.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Asking for User input mid-execution

Is it possible to ask for user input in the middle of a stored procedure execution?

What I have is a single procedure that is going to do some deletes, and I want to receive confirmation from the user that they're ok with actually going through with the delete.

The procedure is being run from a Query Analyzer-type interface, and I have no access to tie proper code into the application this is being run from (hence why I'm trying to add some user-based conditional logic).

Thanks in advance for any assistance!1

No.

ASC/DESC as SP Keywords?

Can I do something like this:
CASE WHEN @.orderBy = 'ASC' THEN ASC ELSE DESC END
So I can order by asc or desc depending on a stored procedure parameter?"Chris Ashley" <chris.ashley2@.gmail.com> wrote in message
news:1137076942.622670.97210@.g44g2000cwa.googlegroups.com...
> Can I do something like this:
> CASE WHEN @.orderBy = 'ASC' THEN ASC ELSE DESC END
> So I can order by asc or desc depending on a stored procedure parameter?
Unless someone can see something wrong with this:
declare @.orderBy char(3)
set @.orderBy = 'asc'
select .. from table order by
case @.orderBy when 'asc' then colName end asc,
case @.orderBy when 'des' then colName end desc|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:O5vTIi4FGHA.3976@.TK2MSFTNGP11.phx.gbl...
> "Chris Ashley" <chris.ashley2@.gmail.com> wrote in message
> news:1137076942.622670.97210@.g44g2000cwa.googlegroups.com...
> Unless someone can see something wrong with this:
> declare @.orderBy char(3)
> set @.orderBy = 'asc'
> select .. from table order by
> case @.orderBy when 'asc' then colName end asc,
> case @.orderBy when 'des' then colName end desc
This article explains it a lot better:
http://www.aspfaq.com/show.asp?id=2501

AS400 stored procedure

Has anyone been able to call an AS400 stored procedure from Reporting
Services?On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
> Has anyone been able to call an AS400 stored procedure from Reporting
> Services?
Does AS400 support ODBC? If so, you could create an ODBC connection
via a datasource and access it that way.
Regards,
Enrique Martinez
Sr. Software Consultant|||Of course the AS400 supports ODBC. (more to the point, DB2 does)
It also supports OLE-DB, and there are some interesting differences between
the drivers. The "base" driver set will be IBM's Client Access.
One answer for Reporting Services in particular *might* be web service calls
into the AS400. However, I've always done it using linked servers, and I
link twice (once with each driver), using whichever one appears to be better
for the task.
However there are definitely some problems with calling an RPG stored proc
using the drivers -- see
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y
The solution appears to be as follows (quoting from that thread):
>>on iSeries, always worked for me to have RPG program execute the SP, have
>>MSSQL statement execute the RPG pgm with a 'CALL rpgPgmName' always used
>>client access oledb driver
Note: he doesn't execute the stored proc directly *AND* he uses OLE-DB,
*NOT* ODBC. The ODBC driver -- even if patched -- tends to be less
up-to-date and capable. OTOH the ODBC sometimes does a better job of
"transparent" translation between charsets <shrug>. That is why I said
"interesting".
Note also that other people make drivers besides IBM and some of them (while
expensive) may be better able to do both these things.
I would expect the .NET-specific driver to be even more up-to-date, and
possibly better behaved. To find out about accessing AS400 data from
different environments, see:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafydynamicsqlclient.htm
HTH,
>L<
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1176899717.222877.18910@.n59g2000hsh.googlegroups.com...
> On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>
> Does AS400 support ODBC? If so, you could create an ODBC connection
> via a datasource and access it that way.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||This is how I called the AS400 stored procedure in the Query string :
CALL GSSSQLLIB.SPTEST2 ('01', 'SUBCAT', '1070401', '1070410')
I get the following error:
An error occurred while retrieving the parameters in the query.
SQL0104: Token 01 was not valid. Valid tokens: FOR WITH FETCH ORDER UNION
EXCEPT OPTIMIZE.....
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:%23UDEWjcgHHA.3412@.TK2MSFTNGP02.phx.gbl...
> Of course the AS400 supports ODBC. (more to the point, DB2 does)
> It also supports OLE-DB, and there are some interesting differences
> between the drivers. The "base" driver set will be IBM's Client Access.
> One answer for Reporting Services in particular *might* be web service
> calls into the AS400. However, I've always done it using linked servers,
> and I link twice (once with each driver), using whichever one appears to
> be better for the task.
> However there are definitely some problems with calling an RPG stored proc
> using the drivers -- see
> http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y
> The solution appears to be as follows (quoting from that thread):
>>on iSeries, always worked for me to have RPG program execute the SP, have
>>MSSQL statement execute the RPG pgm with a 'CALL rpgPgmName' always used
>>client access oledb driver
> Note: he doesn't execute the stored proc directly *AND* he uses OLE-DB,
> *NOT* ODBC. The ODBC driver -- even if patched -- tends to be less
> up-to-date and capable. OTOH the ODBC sometimes does a better job of
> "transparent" translation between charsets <shrug>. That is why I said
> "interesting".
> Note also that other people make drivers besides IBM and some of them
> (while expensive) may be better able to do both these things.
> I would expect the .NET-specific driver to be even more up-to-date, and
> possibly better behaved. To find out about accessing AS400 data from
> different environments, see:
> http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafydynamicsqlclient.htm
>
> HTH,
>
>>L<
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1176899717.222877.18910@.n59g2000hsh.googlegroups.com...
>> On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>> Does AS400 support ODBC? If so, you could create an ODBC connection
>> via a datasource and access it that way.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||I finally figured it:
1) Use ODBC Connection string:
DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
figure how to work with OLEDB.
2) Use Command type "Text" and not "Stored Procedure" with the following
Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401', '1070407')
"John Doe" <jdoe@.msn.com> wrote in message
news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Has anyone been able to call an AS400 stored procedure from Reporting
> Services?
>|||Yup, when you do a CALL it's not a stored proc any more. That makes sense.
And, like I said, you pays your money and you takes your choice between the
OLEDB and ODBC drivers! I can never tell which one is going to be the better
"buy" for a given situation until I try both <g>.
Unless you're saying that you couldn't get the OLEDB connection to work at
all? In which case, I can tell you how I do it, if that will help. I have
to look at the settings every time, I can never remember them <g>.
First I should say that I do everything with linked servers, so I'm coming
across from TSQL code, not .NET code. I wrap the procedure I want in TSQL
(in 2005 you can do this with either a view or a sproc, if I remember
correctly in 2000 I could only use linked servers in sprocs but I might be
wrong).
The performance is often far better if you do it this way. It's just a
little more work to use OPENQUERY() -- you have to double the argument
delimiters, etc -- but along with better performance I think it's a bit more
maintainable.
>L<
"John Doe" <jdoe@.msn.com> wrote in message
news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>I finally figured it:
> 1) Use ODBC Connection string:
> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
> figure how to work with OLEDB.
> 2) Use Command type "Text" and not "Stored Procedure" with the following
> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
> '1070407')
>
> "John Doe" <jdoe@.msn.com> wrote in message
> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>|||Just a heads up for lurkers about linked databases. The four part naming
under SQL Server 2000 is very dangerous and pretty much useless. It takes
almost nothing for it to decide to bring the whole table local for
processing. Openquery works well and as it should. Four part naming has
improved substantially in SQL 2005, still be careful though. Looking at the
query plan will tell you what percentage is taking place remotely.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
> Yup, when you do a CALL it's not a stored proc any more. That makes
> sense.
> And, like I said, you pays your money and you takes your choice between
> the OLEDB and ODBC drivers! I can never tell which one is going to be the
> better "buy" for a given situation until I try both <g>.
> Unless you're saying that you couldn't get the OLEDB connection to work
> at all? In which case, I can tell you how I do it, if that will help. I
> have to look at the settings every time, I can never remember them <g>.
> First I should say that I do everything with linked servers, so I'm coming
> across from TSQL code, not .NET code. I wrap the procedure I want in TSQL
> (in 2005 you can do this with either a view or a sproc, if I remember
> correctly in 2000 I could only use linked servers in sprocs but I might be
> wrong).
> The performance is often far better if you do it this way. It's just a
> little more work to use OPENQUERY() -- you have to double the argument
> delimiters, etc -- but along with better performance I think it's a bit
> more maintainable.
>>L<
> "John Doe" <jdoe@.msn.com> wrote in message
> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the following
>> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
>> '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>|||Thanks for the tip! I have almost never used 4-part naming, and I can't
remember if that was because of performance testing or because it just
didn't buy me anything.
(FWIW I don't use OPENDATASOURCE either, except for to solve one specific
problem involving linking directly to an Excel spreadsheet -- the current
loc of the spreadsheet is stored in a table, etc.)
I don't use aliases much (yet) in 2005, either. Any feelings either way on
perf there?
>L<
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Just a heads up for lurkers about linked databases. The four part naming
> under SQL Server 2000 is very dangerous and pretty much useless. It takes
> almost nothing for it to decide to bring the whole table local for
> processing. Openquery works well and as it should. Four part naming has
> improved substantially in SQL 2005, still be careful though. Looking at
> the query plan will tell you what percentage is taking place remotely.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be the
>> better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to work
>> at all? In which case, I can tell you how I do it, if that will help. I
>> have to look at the settings every time, I can never remember them <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I want
>> in TSQL (in 2005 you can do this with either a view or a sproc, if I
>> remember correctly in 2000 I could only use linked servers in sprocs but
>> I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the following
>> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
>> '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>|||I haven't used aliases either.
If you are in SQL 2005 you might want to take a look at 4 part naming. It is
soooo much easier than handling all those quotes. It is actually useable in
SQL 2005 (as I stated earlier, stay away from 4 part with SQL 2000).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:uABKI%231gHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Thanks for the tip! I have almost never used 4-part naming, and I can't
> remember if that was because of performance testing or because it just
> didn't buy me anything.
> (FWIW I don't use OPENDATASOURCE either, except for to solve one specific
> problem involving linking directly to an Excel spreadsheet -- the current
> loc of the spreadsheet is stored in a table, etc.)
> I don't use aliases much (yet) in 2005, either. Any feelings either way
> on perf there?
>>L<
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
>> Just a heads up for lurkers about linked databases. The four part naming
>> under SQL Server 2000 is very dangerous and pretty much useless. It takes
>> almost nothing for it to decide to bring the whole table local for
>> processing. Openquery works well and as it should. Four part naming has
>> improved substantially in SQL 2005, still be careful though. Looking at
>> the query plan will tell you what percentage is taking place remotely.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
>> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be
>> the better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to work
>> at all? In which case, I can tell you how I do it, if that will help.
>> I have to look at the settings every time, I can never remember them
>> <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I
>> want in TSQL (in 2005 you can do this with either a view or a sproc, if
>> I remember correctly in 2000 I could only use linked servers in sprocs
>> but I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the
>> following Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT',
>> '1070401', '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>>
>|||>>is
> soooo much easier than handling all those quotes.
Yeah, but. As I said, anybody do any perf comparisons?
As far as how hard it is to handle the quotes... I'd still have to do it
with aliases, just one layer less. Because I have to decide at runtime
which alias (or linked server) to use, and build an "outer" statement from
there...
>L<
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23GpRMK2gHHA.4064@.TK2MSFTNGP02.phx.gbl...
>I haven't used aliases either.
> If you are in SQL 2005 you might want to take a look at 4 part naming. It
> is soooo much easier than handling all those quotes. It is actually
> useable in SQL 2005 (as I stated earlier, stay away from 4 part with SQL
> 2000).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
> news:uABKI%231gHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Thanks for the tip! I have almost never used 4-part naming, and I can't
>> remember if that was because of performance testing or because it just
>> didn't buy me anything.
>> (FWIW I don't use OPENDATASOURCE either, except for to solve one specific
>> problem involving linking directly to an Excel spreadsheet -- the current
>> loc of the spreadsheet is stored in a table, etc.)
>> I don't use aliases much (yet) in 2005, either. Any feelings either way
>> on perf there?
>>L<
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
>> Just a heads up for lurkers about linked databases. The four part naming
>> under SQL Server 2000 is very dangerous and pretty much useless. It
>> takes almost nothing for it to decide to bring the whole table local for
>> processing. Openquery works well and as it should. Four part naming has
>> improved substantially in SQL 2005, still be careful though. Looking at
>> the query plan will tell you what percentage is taking place remotely.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
>> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be
>> the better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to
>> work at all? In which case, I can tell you how I do it, if that will
>> help. I have to look at the settings every time, I can never remember
>> them <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I
>> want in TSQL (in 2005 you can do this with either a view or a sproc, if
>> I remember correctly in 2000 I could only use linked servers in sprocs
>> but I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the
>> following Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT',
>> '1070401', '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>>
>

AS400 Data Pull Fails as Job

When I execute the package in Debug mode, it works.

I then import the package into SQL Server 2005 and it is showing under Stored Packages --> MSDB - AS400Package

I can right click and Run Package from there and it works fine.

When I set the package up to run as a job, it fails. What I have noticed is that the Sign On that I use in the package shows that there was in "invalid login" after the job fails.

I hope I have this post in the right place and hope I have left enough information behind to help. If not let know what else I need to post. I have only been using Business Intelligence Development for a couple days and SQL Server 2005 for about a week or so. All of the SQL DTS packages that I have created seem to be working fine with their jobs as well as my stored procedures.

Thanks for any help you can give me.

I have covered how to run your SSIS packages as a Job in the thread below the error means SQL Server Agent does not have the permissions to run the package because when you run it manually it runs in the context of your account when you run it as a Job it runs in the context of the Agent. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1905695&SiteID=1

|||

Thank you for the response. I did some more research off your link and found some more links that will help those undestand the proxy portion of the post.

Create Credential

https://msdn2.microsoft.com/en-us/library/ms189522.aspx

Add Proxy

https://msdn2.microsoft.com/en-us/library/ms188763.aspx

Grant Login to Proxy

https://msdn2.microsoft.com/en-us/library/ms187338.aspx

Grant Subsystem to Proxy

http://msdn2.microsoft.com/en-us/library/ms186760.aspx

A couple more questions

Code Snippet

CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'RettigB',
SECRET = 'sdrlk8$40-dksli87nNN8';

The IDENTITY is supposed to be the account that is executing the Job and Secret is the password for that account?

I will not be able to test this out for a couple days, but there is something I don't understand. How is creating a proxy and logins going to ensure that the password for my AS400 account gets passed to the AS400 system correctly. Judging from the message in Client Access after logging in with the account that the job is sending across the password as either blank or incorrectly.

Thanks for the help and I look forward to trying this out. I hope this works.

|||

The proxy is for SQL Server Agent to use a clone of your account to run the package nothing more anything more is complication where none exist and it works. All the stored procedures are created by Microsoft for you in the links I gave you. The links you found comes with confusions ignore them use the stored procedure from the MSDN links with the instructions in code project is it very simple but it works.

|||

Using the Code Project sample with a few minor changes

Code Snippet

USE master;

CREATE CREDENTIAL Vicksburg400 WITH IDENTITY = 'myDomain\myAccount', secret = 'WindowsLoginPassword';

USE msdb;

EXEC dbo.sp_add_proxy @.proxy_name = 'VBurg400', @.credential_name = 'Vicksburg400' ;

EXEC dbo.sp_grant_login_to_proxy @.login_name = N'djvmsql', @.proxy_name = N'VBurg400';

EXEC dbo.sp_grant_proxy_to_subsystem @.proxy_name = 'VBurg400', @.subsystem_name = N'SSIS package execution';

I get the following the message.

'djvmsql' is a member of sysadmin server role and cannot be granted to or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.

I then tried to remove the sysadmin role from the account and run just the "sp_grant_login_to_proxy" again but it gave the message "Permission to proxy already granted". Meanwhile, the job is still failing. I am absolutely done for tonight. Thanks for the help so far. I appreciate your time.

|||

My mistake a few things have changed check to verify the proxy account runs then you need to enable xp_cmdshell with the surface area configuration tool, Microsoft finally documented it but also disabled it by default. Another thing is make sure the Agent is running with the correct account not the local systems account in configuration manager. Running DTS/SSIS packages to move data from AS400 with the Agent and xp_cmdshell have worked for more than 8 years, if yours is not running troubleshoot your environment. I would not add the credential, new complications Microsoft added to a very simple task and remember not to define password for your package it add complication this process needs to be very simple. I know it works because this process works in high security companies like banks and Pharmaceutical manufacture, the key is the Agent needs Admin level permissions which you create with the proxy.

http://msdn2.microsoft.com/en-us/library/ms187901.aspx

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

|||

Caddre wrote:

check to verify the proxy account runs

Done

Caddre wrote:

then you need to enable xp_cmdshell with the surface area configuration tool,

Done

Caddre wrote:

Another thing is make sure the Agent is running with the correct account not the local systems account in configuration manager.

Agent is running with our domain administrator account.

Caddre wrote:

I would not add the credential, new complications Microsoft added to a very simple task

It would not allow me to create the proxy without adding a Credential to it.

Caddre wrote:

and remember not to define password for your package it add complication this process needs to be very simple. I know it works because this process works in high security companies like banks and Pharmaceutical manufacture, the key is the Agent needs Admin level permissions which you create with the proxy.

If you are referring to the dtsx package I am not sure how I can execute the package without specifying a password in the connection manager. The Connection Manager Type in the properties reads "ADO.NET: System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral,"

I have changed some items from other post to coincide with what you posted. One example is adding diamondjacksvm\Administrator to the proxy and etc... Here is the log from the failed job.

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
08/05/2007 18:51:00,ImpotHISLogon,Error,0,DJVMWEB01,ImpotHISLogon,(Job outcome),,The job failed. The Job was invoked by Schedule 3 (Every Hour). The last step to run was step 1 (Execute ImportHIS.dtsx).,00:00:01,0,0,,,,0
08/05/2007 18:51:00,ImpotHISLogon,Error,1,DJVMWEB01,ImpotHISLogon,Execute ImportHIS.dtsx,,Executed as user: diamondjacksvm\Administrator. The package execution failed. The step failed.,00:00:01,0,0,,,,0

One thing I still don't understand is how come I am getting Failed Login messages when I login into the AS400 with the account being used to import the data in the package. I run the job 3 times (3 fails) it locks out my AS400 account.

Thanks for your continued patience and help on this issue. If it will help, I am more than willing to send screen shots.

|||

Hi,
Sorry I took so long the first two links below deals with other issues like driver version and permissions because you said the connection to AS400 is closing that is not good. Please check out the DB2 driver link in the article see if you need it.

The last link comes with creating a configuration file if you don't have one because I read in another forum configuration file fixes the problem with IBM Iseries. Hope this helps.

http://msdn2.microsoft.com/en-us/library/bb332055.aspx

http://support.microsoft.com/kb/918760/
http://technet.microsoft.com/en-us/library/ms141747.aspx

|||

Just to clarify, it was the 2nd link that helped me out. What I ended up doing was logging on as administrator to the local server and building the package. I have my personal login already setup as a SQL Server login with rights to run Jobs but it was still failing. I did not change the way the package itself was built. The only difference I can see between the way I was running it before and now is that the 2nd package was built with Administrator. Let me see if I can clarify.

1rst Package - Job Failed

SQL Server Agent running as <domainname>\Administrator

Package Built with <domainname>\mmanuel

Proxy AS400 has both logins above setup as principals

Job run as AS400 Proxy

2nd Package - Job Succeeded

SQL Server Agent running as <domainname>\Administrator

Package Built with <domainname>\Administrator

Proxy AS400 has both logins above setup as principals

Job run as AS400 Proxy

Caddre - Thank you so much for your help, assistance, knowledge and patience. I very much appreciate it and look forward to reading more post from you.

|||I am glad I could help and thanks for posting the final solution it will help others.

AS/400 to SQL Server 2000 Replication

Any suggestions on how to replicate from AS/400 to SQL Server 2000?

Data is stored on a AS/400, but applications use a SQL Server 2000 DB. Currently, DTS packages drop the SQL DB, rebuild the tables from a script, copy the data, and then rebuild the indexes as a nightly batch job. Is there a better way to do this? Also is there a clean way to replicate daily transactions as well?

Hi

Replicating from AS400 to SQL Server is not currently supported by replication. (Although it is possible the other way round SQL Server to AS400 DB2 via HIS).

Thanks

Nabila Lacey

AS credentials confusion

I've been experimenting with the different options for connecting to
Analysis Server.
At one point I selected "Credentials stored securely in the report
server" and put in a User Name ("dummyUser") and a Password.
Then I decided to try "Credentials are not required". When I did
this, the report still worked. I looked in Profiler, and saw that
dummyUser was still there as the NTUserName.
So I thought that maybe the connection was cached, and went home for
the weekend. But I try it this morning and it still works the same
way.
Any ideas about what's happening?On Aug 28, 8:55 am, cowznofsky <jhco...@.yahoo.com> wrote:
> I've been experimenting with the different options for connecting to
> Analysis Server.
> At one point I selected "Credentials stored securely in the report
> server" and put in a User Name ("dummyUser") and a Password.
> Then I decided to try "Credentials are not required". When I did
> this, the report still worked. I looked in Profiler, and saw that
> dummyUser was still there as the NTUserName.
> So I thought that maybe the connection was cached, and went home for
> the weekend. But I try it this morning and it still works the same
> way.
> Any ideas about what's happening?
This definitely looks like a bug, since if I try a new datasource and
use "Credentials are not required" it fails, as expected. But once a
valid User Name has been used, and then the option is switched back to
"Credentials are not required", it still works.

Article on when to use Stored Procs?

Does anybody know of some good articles or whitepapers on when to use stored
procs and when not to use them? Thanks in advance.
DaveHere is a compact artilce along with some good discussion:
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
"Dave L" wrote:

> Does anybody know of some good articles or whitepapers on when to use stor
ed
> procs and when not to use them? Thanks in advance.
> Dave
>|||> Does anybody know of some good articles or whitepapers on when to use
> stored
> procs and when not to use them? Thanks in advance.
Thus saith the LORD GOD: "Thou shalt use stored procedures for everything,
and for everything shalt thou use them."
Seriously. Here are some:
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
http://www.devx.com/tips/Tip/13175
http://www.csharpfriends.com/Articl...px?articleID=78
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

Arrays in a Stored Procedure

Can any one help me with a sample code, which can take an array of

elements as one of it's parameters and get the value inserted into a table in a

stored procedure.

Thanks in advance

vnswathi.

SQL dosen't support array, so how do you want to passing an 'array' of elements as parameters to a stored procedure? Maybe you can use a string that delimits the elements of an array by some character(s) and then split the string into a set of values that can be inserted into a table. If so, the key point is how to split the string with specific delimitor, and you can take a look at this post:

http://forums.asp.net/thread/1300724.aspx

Array params to stored procedures?

This is not obvious to me...

As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...

Ok, this meanswhen you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos)you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...

Is this correct?
Is there any other way to approach the problem?

Thanks a lot. -julioCorrect. There is no such things as array in TSQL|||You can easily get arround this by passing in XML|||Thanks pkr, i'll look at that...

Cheers to both. -julio

Array parameters in stored procedures in SQL 2005?

Hi,
I am not sure if I have understood what is happening with the new SQL server
2005.
Will we be using .Net classes instead of stored procedures?
Will I be able to use an array or list type of parameter for my queries with
SQL server 2005? If yes, how would I do this?
Thanks,
Morten> Will we be using .Net classes instead of stored procedures?
Well this is an option available with us. Not that this is the only way. The
T-SQL style still exists nevertheless.

> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
AFAIK, this is still not possible.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Morten" <morten@.imano.nospam> wrote in message
news:%23L9KEr6AFHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am not sure if I have understood what is happening with the new SQL
server
> 2005.
> Will we be using .Net classes instead of stored procedures?
> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
>
> Thanks,
> Morten
>|||>> Will I be able to use an array or list type of parameter for my
queries with SQL server 2005? <<
The short answer is no. But the real qustion is why do you want to
make SQL less relational instead of more standardized?
The way this is done in Standard SQL is with a table constructor:
BEGIN
DELETE FROM Parmlist;
INSERT INTO Parmlist
VALUES (a1), (a2), .., (an);
CALL Foobar (...);
END:
Then you use the parameter table in the query or other statement in the
usual manner.

Array Parameter

Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?

Yes, there is. In fact there is a sample at http://www.codeplex.com/MSFTEngProdSamples called "Array Parameter" which you can browse or download along with the other engine database samples. This sample contains code for passing an array to a CLR stored procedure using a CLR UDT. But be careful in SQL Server 2005 as you are limited in size to 8000 bytes. A future version of SQL Server is expected to relax that constraint. The other option which doesn't have that problem is to encode your data in XML and pass it using an XML parameter and rehydrate the objects in the CLR stored procedure.

Array List in WebService

Hi,

How i can get the FileNames that are stored in Web Server.

Dim strFiles As String = Server.MapPath("~/UploadedFiles/")

Dim dirinfo As New DirectoryInfo(strFiles)

dirinfo.GetFiles("*.doc")

The above Method Iam writing in Web Service. These files will display in a DataGrid in Windows Application.

Is this question for SSIS?

Thanks.

Array As parameters to procedure

Hi friends,
Please help me I want to pass an Aarry parameters from asp.net to sqlserver stored procedure. Is it possible, if yes how.

regards,
Asad Mahmood

Hi,

SQL Server doesn't seem to have any array-like parameter so what you can do is that create a CSV (comma separated values) out from your data and pass that to the procedure (which could take that as varchar(8000) etc depending on the needed length).

In the proc you could parse this CSV into say a temp table containing values as integers, if you use a function.Here is an example of such function.

|||It will be easier if you are using Arraylist but try this link for how to do it. Hope this helps.
http://www.sommarskog.se/arrays-in-sql.html