SQL Adding a User account: procedure

Showing posts with label procedure. Show all posts

Tuesday, March 27, 2012

Advice on DTS Package in Replication

Is it advicable to use DTS package as a tranformation media in transactional
replication or should transformation be done at Stored Procedure level on
Subscriber?
Under what circumstances it will be a good idea to use DTS package.
Thank you very much.
only use transformable subscription with hetergeneous subscribers. For SQL
Server subscribers there are other ways (stored procedures being one of
them) of transforming the data in route which offer far greater performance.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:C9B7E873-C6A2-40CC-8EE5-0DAC1D1A7508@.microsoft.com...
> Is it advicable to use DTS package as a tranformation media in
> transactional
> replication or should transformation be done at Stored Procedure level on
> Subscriber?
> Under what circumstances it will be a good idea to use DTS package.
> Thank you very much.

Advice on ALTER TABLE statement.

Can I use an ALTEER TABLE statement in a stored procedure, I need to drop a
named constraint. Getting complie error.
ThanksTim Gains wrote:
> Can I use an ALTEER TABLE statement in a stored procedure, I need to
> drop a named constraint. Getting complie error.
>
> Thanks
You can use alter table from within a procedure (not sure why you woudl
want to). Maybe you're using dynamic sql and should be using either an
EXEC statement or sp_executesql. What is the reason for doing this from
a stored procedure (you can't inherit alter table rights with a
procedure grant on execute). It seems that if you're not using dynamic
sql for this in the procedure as some type of admin functionality (e.g.
to alter table ddl from your own procedure for admins), then you might
be better served by just executing the alter table as a batch without a
procedure. Can you explain why you need the procedure and post your
code?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Thursday, March 22, 2012

Adventureworks BillofMaterials

In this stored procedure the quantity of the parent is not included. It

should multiply down the tree. Can someone explain how this recursive

function work?

Moving to t-SQL forum.

Mike

Monday, March 19, 2012

Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very muchHi
"elisacarli" wrote:
> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment is
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:
> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>

Advanced Stored procedure...

Hello,

(Excuse my english, i'm a french man)

I want to make a stored procedure on that model :

CREATE Procedure SGCP_GetEleveArgs
(
@.Arg1 nVarChar,
@.Val1 nVarChar
)
AS
SELECT *
FROM Eleve
WHERE @.Arg1 = @.Val1

So you understand that i want to pass my argument (@.Arg1) as a field of a column.
but it gives :

WHERE 'Nom' = 'Dupont'

but i want to have :

WHERE Nom = Dupont

for it to work.
Have you an idea to solve that problem ?

ThanxYou will want to use the EXEC function in SQL:


EXEC('select * from someTable WHERE ' + @.arg1 + ' = ''' + @.Val1 + '''')

I have not written SQL for a long time so the quote inside string part might be wrong.|||Exact :) ,

it seems to be the only one solution...

Thanx a lot

______________
Mik|||You could autogenerate the procs. For example, if you've got a table with the cols you could create

MyProcTableName1 @.Arg2
MyProcTableName2 @.Arg2
MyProcTableName3 @.Arg2

Then the client simply appends the first arg to the name...
CommandText = "MyProc" + TableName + strArg1

You get the idea...|||After research,

i found that we lose the advantages of stored procedure using EXEC because we don't use most important with stored procedure : precompilation of the command.

So I will not make like that

thanx|||Take a look at sp_executesql. This will used a compiled execution plan in some cases. There's really not any other dynamic way other than creating a dynamic SQL statement. Depending on the number of fields that could be searched, you could just put in several similar queries and use the appropriate one based on the field name. Could be cumbersome, but not bad if it's only a few.


if @.arg1 = 'field1'
select ... from table1 where field1 = @.arg2if @.arg1 = 'field2'
select ... from table1 where field2 = @.arg2

You could also use a CASE statement:


Select ...
from table1
where
case @.arg1
 when 'field1' then field1
 when 'field2' then field2
 end = @.arg2

Might work. One problem is that if your stored procedure compiles an execution plan, it may use the wrong one. For example, the first time you run it, you search on field1. If you run it again, it may use the execution plan optimized for field1, but you're searching field2. You may get just as good results using dynamic SQL. It will create a new execution plan each time, but one that's optimized for the current query, not the last one.

Bon chance! (Only 5 years of French classes a LONG time ago.)

Thursday, March 8, 2012

ADP Parameterized list box stored proecedure /function

I am trying to populate a list box (MS Access - don't ask it is what my boss wants) with a table-valued function or a stored
procedure. The list box is used to navigate among records that populate the
form, so the same query can be used for the form and the list box thereby,
theoretically, reducing calls to the DB. The records are filtered on two
fields, we'll call them type (GUID) and name (nvarchar), whereby the values
are in two different controls on the form.

The form is using a function, fActiveCompanies, and the imput parameter
propety is completed. The form is able to navigate through the records with
no problem.

I am having difficulty create a SMOOTH method of populating the list box.
currently i am using a stored procedure that is called in VB and populates
the list box with a list of values.

I would prefer to not use a list of values and set the row source type to
Table/View/Stored Procedure.

Please point me in the correct direction for populaitng hte list box not
using a list of values... thank you

Access 2003 - Access Data Projects (ADP) - ADO
SQL Server 2000 (production) SQL Server Express 2005 (devlopment)

my current code for populating the list box is below.

Dim str As String: str = ""
Dim lst As String: lst = ""

Dim rds As ADODB.Recordset
Set rds = New ADODB.Recordset

Forms!frmcompanies!lstCompanies.RowSource = lst
str = "EXEC spActiveCompanies @.pIdTypeCompany='" &
Nz(Forms!frmcompanies!cboTblTypeEntity, "%") & "', @.pNameLegal='" &
Nz(Forms!frmcompanies!txtNav, "%") & "'"

rds.Open str, CurrentProject.Connection

Do Until rds.EOF

If lst = "" Then
lst = """" & rds(0) & """;""" & rds(1) & """"
Else
lst = lst & ";""" & rds(0) & """;""" & rds(1) & """"
End If

rds.MoveNext
Loop

Forms!frmcompanies!lstCompanies.RowSource = lst
Forms!frmcompanies!lstCompanies.Requery

rds.Close: Set rds = Nothing

Forms!frmcompanies.Requery

i mis-read one document, the parameters must be the same as the controls...

field = @.ComboBoxControlName

Saturday, February 25, 2012

ADODB Command (Stored Procedure)

Hi!

I already sent this to the ACCESS newsgroup. But since I do not know really
which side is really causing the problem, I have decided to send this
inquiry
to this newsgroup also, if I may.

Below is the environment of the application:
a. MS Access 2003 application running on Windows XP
b. SQL Server 2000 - backend running MS Server 2003 OS

Below is the code that is giving me an error:

Dim com As ADODB.Command

Set com = New ADODB.Command

With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "sp_Recalculate"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ItemNumber") = ItemNum
.Execute ' This is where it hangs up...

TotalItems = .Parameters("@.TotalInStock")
TotalCost = .Parameters("@.TotalCost")

End With

Set com = Nothing

and the store procedure is:

CREATE PROCEDURE DBO.sp_Recalculate
@.ItemNumber nvarchar(50),
@.TotalInStock int = 0,
@.TotalCost money = 0
AS

BEGIN
SET @.TotalInStock = (
SELECT Sum([Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)

SET @.TotalCost = (
SELECT Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)

END

When the process goes to the ".Execute" line, it hangs up for a long time
then gives me an error message "Everflow". I have been trying to solve
this issue but do not have an idea for now of the cause.

Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just fine.
I placed a SELECT statement to view the result of the stored procedure.
It gives the correct values.

Can anyone have ideas or similar problems?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

.Execute ' This is where it hangs up...
>
TotalItems = .Parameters("@.TotalInStock")
TotalCost = .Parameters("@.TotalCost")
>
End With
>
Set com = Nothing
>
and the store procedure is:
>
CREATE PROCEDURE DBO.sp_Recalculate

Do not use the sp_ prefix in your procedures. This prefix is reserved
for system procedures, and SQL Server will first look for these in master.
I don't think this explains why your process hangs, but I nevertheless
wanted to point it out.

Quote:

Originally Posted by

@.ItemNumber nvarchar(50),
@.TotalInStock int = 0,
@.TotalCost money = 0
AS

Judging from the code, the parameters @.TotalInStock and @.TotalCost
should be declared as OUTPUT. Right now your procedure is only a no-op.

Also, I can't see in you code that you create these parameters when you
call the procedure. You need to do that; you cannot just refer the
parameters after the call.

Quote:

Originally Posted by

BEGIN
SET @.TotalInStock = (
SELECT Sum([Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)
>
SET @.TotalCost = (
SELECT Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)
>
END

Rewrite as

SELECT @.TotalInStock = Sum([Quantity in Stock]),
@.TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber

That will slash the execution time in half.

Quote:

Originally Posted by

I guess you mean "Overflow"? That sounds like VB message to me, which
would indicate that you are using the wrong data type for TotalItems.
(Check that you did not mistakenly declare it as Integer.) Then again,
it does not seem that you would get anything back from the procedure
at all. But maybe that is the problem? You get some unintialised junk?

Quote:

Originally Posted by

Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just
fine.

And it completes in how long time?

--
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|||I timed the execution: 1 minute. Then it gives me the overflow error.

I double checked the code especially the declarations, and they seem to
okay. The "TotalItems" var in the calling method uses "long" as its data
type. The "@.TotalInStock" OUTPUT var in the stored procedure is declared as
"int". They seem to be okay.

Is there an issue between the number of open connections to the database?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9945EF2273D83Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

> .Execute ' This is where it hangs up...
>>
> TotalItems = .Parameters("@.TotalInStock")
> TotalCost = .Parameters("@.TotalCost")
>>
> End With
>>
> Set com = Nothing
>>
>and the store procedure is:
>>
>CREATE PROCEDURE DBO.sp_Recalculate

>
Do not use the sp_ prefix in your procedures. This prefix is reserved
for system procedures, and SQL Server will first look for these in master.
I don't think this explains why your process hangs, but I nevertheless
wanted to point it out.
>

Quote:

Originally Posted by

> @.ItemNumber nvarchar(50),
> @.TotalInStock int = 0,
> @.TotalCost money = 0
>AS

>
Judging from the code, the parameters @.TotalInStock and @.TotalCost
should be declared as OUTPUT. Right now your procedure is only a no-op.
>
Also, I can't see in you code that you create these parameters when you
call the procedure. You need to do that; you cannot just refer the
parameters after the call.
>

Quote:

Originally Posted by

>BEGIN
> SET @.TotalInStock = (
> SELECT Sum([Quantity in Stock])
> FROM [Inventory Products]
> WHERE [Item Number] = @.ItemNumber)
>>
> SET @.TotalCost = (
> SELECT Sum([Cost] * [Quantity in Stock])
> FROM [Inventory Products]
> WHERE [Item Number] = @.ItemNumber)
>>
>END

>
Rewrite as
>
SELECT @.TotalInStock = Sum([Quantity in Stock]),
@.TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber
>
That will slash the execution time in half.
>

Quote:

Originally Posted by

>When the process goes to the ".Execute" line, it hangs up for a long time
>then gives me an error message "Everflow". I have been trying to solve
>this issue but do not have an idea for now of the cause.

>
I guess you mean "Overflow"? That sounds like VB message to me, which
would indicate that you are using the wrong data type for TotalItems.
(Check that you did not mistakenly declare it as Integer.) Then again,
it does not seem that you would get anything back from the procedure
at all. But maybe that is the problem? You get some unintialised junk?
>

Quote:

Originally Posted by

>Below is my finding:
>a. When I run the stored procedure in the SQL analyzer, it works just
>fine.

>
And it completes in how long time?
>
>
>
>
--
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

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I timed the execution: 1 minute. Then it gives me the overflow error.

Does it run for one minute in QA as well?

Quote:

Originally Posted by

I double checked the code especially the declarations, and they seem to
okay. The "TotalItems" var in the calling method uses "long" as its
data type. The "@.TotalInStock" OUTPUT var in the stored procedure is
declared as "int". They seem to be okay.

And the data type for TotalCost is?

Quote:

Originally Posted by

Is there an issue between the number of open connections to the database?

No, that has nothing to do with it.

There were a couple of more issues with your code that I pointed out,
but you did not comment these. The code you posted will not work for
reasons I've already detailed.

It may be that you did not post the actual code, but just scribbled down a
sketch and introduced a few errors along the way. But in that case, I don't
know what you are doing, so I cannot say more than I've already said.

--
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|||I made th necessary changes. Below are the current scripts for both the
calling method and the
store procedure. Variable "ItemNum" is being passed as a string parameter to
the recalculate
method.

'**************************** This is the script in the calling method
*************************
Dim TotalCost As Currency, TotalItems As Long, AvgCost As Currency
Dim CurDB As Database, Inv As Recordset, InvP As Recordset, SQLStmt As
String, SQLStmt2 As String

Dim com As ADODB.Command

Set com = New ADODB.Command

With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "sp_Recalculate"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ItemNumber") = ItemNum
.Execute

TotalItems = .Parameters("@.TotalInStock")
TotalCost = .Parameters("@.TotalCost")
End With

Set com = Nothing

================================================== ==============

'*************************** This is the current script in the stored
procedure *******************
CREATE PROCEDURE DBO.sp_Recalculate
@.ItemNumber nvarchar(50),
@.TotalInStock int = 0 OUTPUT,
@.TotalCost money = 0 OUTPUT
AS
SELECT @.TotalInStock = Sum([Quantity in Stock]),
@.TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber
GO

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns994667A846B71Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I timed the execution: 1 minute. Then it gives me the overflow error.

>
Does it run for one minute in QA as well?
>

Quote:

Originally Posted by

>I double checked the code especially the declarations, and they seem to
>okay. The "TotalItems" var in the calling method uses "long" as its
>data type. The "@.TotalInStock" OUTPUT var in the stored procedure is
>declared as "int". They seem to be okay.

>
And the data type for TotalCost is?
>

Quote:

Originally Posted by

>Is there an issue between the number of open connections to the database?

>
No, that has nothing to do with it.
>
There were a couple of more issues with your code that I pointed out,
but you did not comment these. The code you posted will not work for
reasons I've already detailed.
>
It may be that you did not post the actual code, but just scribbled down a
sketch and introduced a few errors along the way. But in that case, I
don't
know what you are doing, so I cannot say more than I've already said.
>
>
--
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

|||I also changed the stored procedure name to "procRecalculate".

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns994667A846B71Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I timed the execution: 1 minute. Then it gives me the overflow error.

>
Does it run for one minute in QA as well?
>

Quote:

Originally Posted by

>
And the data type for TotalCost is?
>

Quote:

Originally Posted by

>Is there an issue between the number of open connections to the database?

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I made th necessary changes. Below are the current scripts for both the
calling method and the store procedure. Variable "ItemNum" is being
passed as a string parameter to the recalculate method.

Do you still get the overflow error, or does it work alright now?

Dim com As ADODB.Command
Dim MyItemNumber As String, MyTotalInStock As Long, MyTotalCost As
Currency

Set com = New ADODB.Command

MyItemNumber = ItemNum
MyTotalInStock = 0
MyTotalCost = 0

With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "procRecalculate"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("ItemNumber", adVarChar,
adParamInput, MyItemNumber)
.Parameters.Append .CreateParameter("TotalInStock", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.Append .CreateParameter("TotalCost", adCurrency,
adParamOutput, MyTotalCost)
.Execute

End With

Set com = Nothing

If IsNull(MyTotalInStock) Then MyTotalInStock = 0
If IsNull(MyTotalCost) Then MyTotalCost = 0

TotalItems = MyTotalInStock
TotalCost = MyTotalCost

=============================================
CREATE PROCEDURE DBO.procRecalculate
@.ItemNumber nvarchar(50),
@.TotalInStock int = 0 OUTPUT,
@.TotalCost money = 0 OUTPUT
AS
SET NOCOUNT ON

SELECT @.TotalInStock = Sum(Cast([Quantity in Stock] As int)),
@.TotalCost = Sum(Cast([Cost] * [Quantity in Stock] As money))
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber

SET NOCOUNT OFF
GO

Unfortunately, I still get the same error. But this time, I get it in a
second.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9946E6B747AD9Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I made th necessary changes. Below are the current scripts for both the
>calling method and the store procedure. Variable "ItemNum" is being
>passed as a string parameter to the recalculate method.

>
Do you still get the overflow error, or does it work alright now?
>
>
>
--
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

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

This is the current scripts of the application:
>...
.Parameters.Append .CreateParameter("ItemNumber", adVarChar,
>adParamInput, MyItemNumber)
.Parameters.Append .CreateParameter("TotalInStock", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.Append .CreateParameter("TotalCost", adCurrency,
adParamOutput, MyTotalCost)

Here is an error: .CreateParameter takes five parameter of which the
fourth is the size, and the fifth is the value. Thus you need to insert
an extra comma after adParamInput, adParamOutput.

Quote:

Originally Posted by

Unfortunately, I still get the same error. But this time, I get it in a
second.

Well, at least some progress. :-)
--
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

ADODB Command

Hi,
I am trying to use an ADODB.Command to execute a stored procedure with som
parameters but gets the following error:
ADOError Number:-2147217900
ADOError Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Rad 1:
Felaktig syntax nra 'lsc_getAllMatchesToLab'.
ADOError Source:Microsoft OLE DB Provider for ODBC Drivers
ADOError SQLState:37000
ADOError Native:170
In SQL Profiler I get this SQL text:
exec sp_executesql N' lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0,
0', N'@.P1 float,@.P2 float,@.P3 float,@.P4 float',
7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
2.500000000000000e+001
Trying to use this in Query Analyzer givs the followin error:
Server: Msg 170, Level 15, State 1, Line 1
Rad 1: Felaktig syntax nra 'lsc_getAllMatchesToLab'.
(Line 1: Syntax error near' lsc_getAllMatchesToLab')
If I run the stored procedure in Query Analyzer not using sp_executesql and
no variables as parameters as follows, I get an OK result table:
lsc_getAllMatchesToLab 1, 2,
7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
2.500000000000000e+001, 0, 0
Is this just a simple error from my side?
Thanks for any help
Anders NilssonYou are executing a Stored Proc right?
instead of this
exec sp_executesql N' lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0,
0', N'@.P1 float,@.P2 float,@.P3 float,@.P4 float',
7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
2.500000000000000e+001
do this in your code
exec lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0, 0
http://sqlservercode.blogspot.com/
"Anders Nilsson" wrote:

> Hi,
> I am trying to use an ADODB.Command to execute a stored procedure with som
> parameters but gets the following error:
> ADOError Number:-2147217900
> ADOError Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Rad 1:
> Felaktig syntax n?ra 'lsc_getAllMatchesToLab'.
> ADOError Source:Microsoft OLE DB Provider for ODBC Drivers
> ADOError SQLState:37000
> ADOError Native:170
> In SQL Profiler I get this SQL text:
> exec sp_executesql N' lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0,
> 0', N'@.P1 float,@.P2 float,@.P3 float,@.P4 float',
> 7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
> 2.500000000000000e+001
> Trying to use this in Query Analyzer givs the followin error:
> Server: Msg 170, Level 15, State 1, Line 1
> Rad 1: Felaktig syntax n?ra 'lsc_getAllMatchesToLab'.
> (Line 1: Syntax error near' lsc_getAllMatchesToLab')
> If I run the stored procedure in Query Analyzer not using sp_executesql an
d
> no variables as parameters as follows, I get an OK result table:
> lsc_getAllMatchesToLab 1, 2,
> 7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
> 2.500000000000000e+001, 0, 0
> Is this just a simple error from my side?
> Thanks for any help
> Anders Nilsson
>
>|||Anders Nilsson wrote:
> Hi,
> I am trying to use an ADODB.Command to execute a stored procedure
> with som parameters but gets the following error:
> ADOError Number:-2147217900
> ADOError Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Rad 1: Felaktig syntax nra 'lsc_getAllMatchesToLab'.
> ADOError Source:Microsoft OLE DB Provider for ODBC Drivers
> ADOError SQLState:37000
> ADOError Native:170
>
Why didn't you show us your ADO code? It's extremely difficult to diagnose
your problem without seeing the code that produced the symptom. If you show
us your procedure's CREATE statement, particularly, the section between
CREATE PROCEDURE lsc_getAllMatchesToLab ... AS (IOW, the parameter
declarations), and let us know what programming language you are using,
somebody should be able to diagnose your problem.
Bob Barrows
Nothing to do with your problem, but I would use the native SQL Server OLE
DB provider rather than the ODBC driver.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

ADO/SQL error -2147217900 (80040e14)

With the below procedure I want to run a stored procedure on a SQL-server 2000, but for some reason I get run-time error -2147217900 (80040e14), stating that the stored procedure doesn't exist.

As it does exist, the connection is open, and running the code in query analyzer works, I center the problem down to the database being used.

But whats wrong? The user and database has been added to the system dsn AccessSQL, user and passwords works fine in query analyzer.. yet for some reason it apparently sends the ado command to the wrong database, and thus can't find the stored procedure.

Thanks in advance,

Trin

Dim forbindelse As New ADODB.Connection
Dim kommando As New ADODB.Command
Dim pam1, pam2 As ADODB.Parameter
Dim forbindelseDSN As String
Dim strSQLland As String
Dim strSQLtid As String

forbindelse.Open "DSN=AccessSQL;APP=Track Exporter;Database=Gramex_UDV;Network=DBMSSOCN;", "aaaa", "tttt"
kommando.CommandText = "TRACK_EXPORT_sp_opdatland"
kommando.CommandType = adCmdStoredProc
kommando.ActiveConnection = forbindelse
Set pam1 = kommando.CreateParameter("land", adChar, adParamInput, 2)
Set pam2 = kommando.CreateParameter("org", adChar, adParamInput, 4)
kommando.Parameters.Append pam1
kommando.Parameters.Append pam2

pam1.Value = "'" & kombo_land & "'"
pam2.Value = "'" & kombo_org & "'"

kommando.Execute

forbindelse.Close
Set kommando = Nothing
Set forbindelse = NothingFYI I've attempted with a different connectstring:

forbindelse.Open "provider=SQLOLEDB;Data source=GRAMEXFIN;initial catalog=Gramex_UDV;User ID=AccessBruger;Password=gramex"

Same error...

I've rechecked that the login has execute permission for the stored procedure...|||Problem solved - Turns out the database was setup to use full qualifiers for the object name.

In this case the stored procedure was to be adressed with its dbo... ket.TRACK_EXPORT... etc. etc..

/Trin

Friday, February 24, 2012

ADO.net or TSQL Transactions

Hi all
Should implement a transaction in both the stored procedure AND in ADO.net
code or is doing it in one or the other good enough to protect against
concurrency and atomicity problems?
Thanks
Simon
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
> If you have complex explicit transactions, your best bet is going to
> be to implement them in your stored procedure(s) in T-SQL both in
> terms of performance and simplicity. Implementing them in client code
> may cause more round trips and not be as performant. Call a single
> stored procedure that executes the transactions and returns
> success/failure information in output parameters. See the BEGIN
> TRANSACTION and related topics in SQL Books Online for more
> information.
>
> --Mary
>
> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> <sh856531@.microsofts_free_email_service.com> wrote:
>
>>Hi everyone,
>>
>>Can anyone tell me, if I use and ADO transaction object to execute say 10
>>stored procedures, and the stored procedures are themselve quite quite
>>long
>>and multistaged, do I need to use transaction statements inside the
>>individual procedures to avoid potential concurrency issues, or am I
>>protected from this by virtue of the ADO transaction object.
>>
>>The reason I ask is, it could be the case that the ADO.net transaction
>>simply ensures that the stored procedures operate in an all or nothing
>>manner. This may mean that within a complicated multi staged stored
>>procedure information could become corrupted because the relevent multi
>>staged code *inside* the procedure isn't transacted.
>>
>>I hope that make sense. My query pertains to SQL Server but I'm guessing
>>the
>>same would be true of any db that supports transactions and SProcs.
>>
>>Thanks
>>
>>Simon
>>
>Simon Harvey wrote:
> Hi all
> Should implement a transaction in both the stored procedure AND in
> ADO.net code or is doing it in one or the other good enough to
> protect against concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
>> If you have complex explicit transactions, your best bet is going to
>> be to implement them in your stored procedure(s) in T-SQL both in
>> terms of performance and simplicity. Implementing them in client code
>> may cause more round trips and not be as performant. Call a single
>> stored procedure that executes the transactions and returns
>> success/failure information in output parameters. See the BEGIN
>> TRANSACTION and related topics in SQL Books Online for more
>> information.
>> --Mary
>> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
>> <sh856531@.microsofts_free_email_service.com> wrote:
>> Hi everyone,
>> Can anyone tell me, if I use and ADO transaction object to execute
>> say 10 stored procedures, and the stored procedures are themselve
>> quite quite long
>> and multistaged, do I need to use transaction statements inside the
>> individual procedures to avoid potential concurrency issues, or am I
>> protected from this by virtue of the ADO transaction object.
>> The reason I ask is, it could be the case that the ADO.net
>> transaction simply ensures that the stored procedures operate in an
>> all or nothing manner. This may mean that within a complicated
>> multi staged stored procedure information could become corrupted
>> because the relevent multi staged code *inside* the procedure isn't
>> transacted. I hope that make sense. My query pertains to SQL Server
>> but I'm
>> guessing the
>> same would be true of any db that supports transactions and SProcs.
>> Thanks
>> Simon
If you're talking about SQL running from a client as opposed to running
a single stored procedure, I think you have to go the SP route (in most
cases). Security aside, imagine running 20 SQL commands from a client on
the server in succession: each one requires a full round-trip to the
server which can slow things down. OTOH, a single SP is a single call.
While the slower client-side transaction runs, it holds locks on the
server, which in turn causes other transactions to wait on locked
resources which slows everyone down.
Plus, if you have to make an implementation change, you don't have to
deal with recompiling the app and distributing it to everyone.
--
David Gugick
Imceda Software
www.imceda.com|||A transaction has to be ATOMIC regardless of who starts it otherwise what
good is it. So two transactions are not better than one in this case. If
the outer one is Rolled back then ALL the nested ones are too. Sometimes it
makes sense to have trans in sp's so that if you call the sp by itself
everything inside it is all or nothing. But if you begin a tran from
outside a sp, everything from there on will be wrapped in that same tran.
Andrew J. Kelly SQL MVP
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:eOl8GlkzEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Should implement a transaction in both the stored procedure AND in ADO.net
> code or is doing it in one or the other good enough to protect against
> concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
>> If you have complex explicit transactions, your best bet is going to
>> be to implement them in your stored procedure(s) in T-SQL both in
>> terms of performance and simplicity. Implementing them in client code
>> may cause more round trips and not be as performant. Call a single
>> stored procedure that executes the transactions and returns
>> success/failure information in output parameters. See the BEGIN
>> TRANSACTION and related topics in SQL Books Online for more
>> information.
>> --Mary
>> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
>> <sh856531@.microsofts_free_email_service.com> wrote:
>>Hi everyone,
>>Can anyone tell me, if I use and ADO transaction object to execute say 10
>>stored procedures, and the stored procedures are themselve quite quite
>>long
>>and multistaged, do I need to use transaction statements inside the
>>individual procedures to avoid potential concurrency issues, or am I
>>protected from this by virtue of the ADO transaction object.
>>The reason I ask is, it could be the case that the ADO.net transaction
>>simply ensures that the stored procedures operate in an all or nothing
>>manner. This may mean that within a complicated multi staged stored
>>procedure information could become corrupted because the relevent multi
>>staged code *inside* the procedure isn't transacted.
>>I hope that make sense. My query pertains to SQL Server but I'm guessing
>>the
>>same would be true of any db that supports transactions and SProcs.
>>Thanks
>>Simon
>>
>|||Except that sometimes you need to do something and then do more things in
source code that depend on that something and so on. Unless you move your
entire application into the SP, you'll have no choice but to wrap the
transaction into an ADO.NET call. Apart from the fact that (in our case) it
may not be feasible to rewrite 2.5 million lines of code to accomodate SP
only transactions, not to mention that some of the older parts of the code
(that are being rewritten into .NET) use embedded SQL...
You'll just have to evaluate your application architecture if it already
exists, or decide how you will be accessing the data at all times in your
application of you are currently designing it. There's a time and place for
everything. But know this, if you begin a transaction in the SP and for
some reason need to execute source code that may depending on something from
a currently running transaction, and it attempts to start a transaction and
call an SP that starts its own transaction, you'll get an exception. So you
either do it one way or another but not both, unless you want to excercise
your patients and stamina.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ut0iiRlzEHA.824@.TK2MSFTNGP11.phx.gbl...
> Simon Harvey wrote:
> > Hi all
> >
> > Should implement a transaction in both the stored procedure AND in
> > ADO.net code or is doing it in one or the other good enough to
> > protect against concurrency and atomicity problems?
> >
> > Thanks
> >
> > Simon
> >
> > "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> > news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
> >> If you have complex explicit transactions, your best bet is going to
> >> be to implement them in your stored procedure(s) in T-SQL both in
> >> terms of performance and simplicity. Implementing them in client code
> >> may cause more round trips and not be as performant. Call a single
> >> stored procedure that executes the transactions and returns
> >> success/failure information in output parameters. See the BEGIN
> >> TRANSACTION and related topics in SQL Books Online for more
> >> information.
> >>
> >> --Mary
> >>
> >> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> >> <sh856531@.microsofts_free_email_service.com> wrote:
> >>
> >> Hi everyone,
> >>
> >> Can anyone tell me, if I use and ADO transaction object to execute
> >> say 10 stored procedures, and the stored procedures are themselve
> >> quite quite long
> >> and multistaged, do I need to use transaction statements inside the
> >> individual procedures to avoid potential concurrency issues, or am I
> >> protected from this by virtue of the ADO transaction object.
> >>
> >> The reason I ask is, it could be the case that the ADO.net
> >> transaction simply ensures that the stored procedures operate in an
> >> all or nothing manner. This may mean that within a complicated
> >> multi staged stored procedure information could become corrupted
> >> because the relevent multi staged code *inside* the procedure isn't
> >> transacted. I hope that make sense. My query pertains to SQL Server
> >> but I'm
> >> guessing the
> >> same would be true of any db that supports transactions and SProcs.
> >>
> >> Thanks
> >>
> >> Simon
> If you're talking about SQL running from a client as opposed to running
> a single stored procedure, I think you have to go the SP route (in most
> cases). Security aside, imagine running 20 SQL commands from a client on
> the server in succession: each one requires a full round-trip to the
> server which can slow things down. OTOH, a single SP is a single call.
> While the slower client-side transaction runs, it holds locks on the
> server, which in turn causes other transactions to wait on locked
> resources which slows everyone down.
> Plus, if you have to make an implementation change, you don't have to
> deal with recompiling the app and distributing it to everyone.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> Except that sometimes you need to do something and then do more
> things in source code that depend on that something and so on.
> Unless you move your entire application into the SP, you'll have no
> choice but to wrap the transaction into an ADO.NET call. Apart from
> the fact that (in our case) it may not be feasible to rewrite 2.5
> million lines of code to accomodate SP only transactions, not to
> mention that some of the older parts of the code (that are being
> rewritten into .NET) use embedded SQL...
> You'll just have to evaluate your application architecture if it
> already exists, or decide how you will be accessing the data at all
> times in your application of you are currently designing it. There's
> a time and place for everything. But know this, if you begin a
> transaction in the SP and for some reason need to execute source code
> that may depending on something from a currently running transaction,
> and it attempts to start a transaction and call an SP that starts its
> own transaction, you'll get an exception. So you either do it one
> way or another but not both, unless you want to excercise your
> patients and stamina.
>
> Thanks,
> Shawn
I thought the OP asked a simple question: Which is better to use in an
application SPs or embedeed SQL. The answer to _that_ simple question is
stored procedures. But that's not the question he asked. He asked
whether the transaction should be started in the app or on in the SP. I
think Andrew answered that question. But I stand by answer to a question
that was never asked :-)
David Gugick
Imceda Software
www.imceda.com|||I think the relevance, while not as direct as previous answers, is that "it
depends on how your code and workflow is organized". I'm not dissagreeing
with the "simple" answer, that you use transactions in the SP. However,
I've rarely encountered a business application that was designed in such a
way that all transactions were at the SP level. I was only offering a
difference perspective, a difference way of looking at things, another thing
to take into consideration. Nothing more.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u4zdwPmzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Shawn B. wrote:
> > Except that sometimes you need to do something and then do more
> > things in source code that depend on that something and so on.
> > Unless you move your entire application into the SP, you'll have no
> > choice but to wrap the transaction into an ADO.NET call. Apart from
> > the fact that (in our case) it may not be feasible to rewrite 2.5
> > million lines of code to accomodate SP only transactions, not to
> > mention that some of the older parts of the code (that are being
> > rewritten into .NET) use embedded SQL...
> >
> > You'll just have to evaluate your application architecture if it
> > already exists, or decide how you will be accessing the data at all
> > times in your application of you are currently designing it. There's
> > a time and place for everything. But know this, if you begin a
> > transaction in the SP and for some reason need to execute source code
> > that may depending on something from a currently running transaction,
> > and it attempts to start a transaction and call an SP that starts its
> > own transaction, you'll get an exception. So you either do it one
> > way or another but not both, unless you want to excercise your
> > patients and stamina.
> >
> >
> > Thanks,
> > Shawn
>
> I thought the OP asked a simple question: Which is better to use in an
> application SPs or embedeed SQL. The answer to _that_ simple question is
> stored procedures. But that's not the question he asked. He asked
> whether the transaction should be started in the app or on in the SP. I
> think Andrew answered that question. But I stand by answer to a question
> that was never asked :-)
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> I think the relevance, while not as direct as previous answers, is
> that "it depends on how your code and workflow is organized". I'm
> not dissagreeing with the "simple" answer, that you use transactions
> in the SP. However, I've rarely encountered a business application
> that was designed in such a way that all transactions were at the SP
> level. I was only offering a difference perspective, a difference
> way of looking at things, another thing to take into consideration.
> Nothing more.
>
> Thanks,
> Shawn
>
I agree with you Shawn. Unless you have strict standards (which are not
bad to have), most applications will have some embedded SQL, even if
using SPs is the standard. Although, I'm not sure if you have an SP
standard for an app that ending up with a mixed code base is good from a
maintenance and security standpoint. It's nice to know no one can access
your database, except through stored procs.
--
David Gugick
Imceda Software
www.imceda.com|||Hi all,
Thanks for your answers so far.
I'm still not sure about something. Are ADO.net transactions and TSQL
transactions essentially equivelent?
If I do a big stored procedure, locked through ADO.net, will all the the
rows be locked until the SP returns?
I'm worried that if I use ADO, the stored procedure still might cause a
concurrency problem. This is essentially my problem.
Thanks again
Simon|||Simon,
An ADO transaction (.net or otherwise) is nothing more than passing a BEGIN
TRAN to SQL Server. So it's always a SQL Server transaction and any rows
locked after the first Begin Tran is issued (regardless of where or by who)
on that connection will remain locked until the final commit (if nested) or
the first Rollback.
--
Andrew J. Kelly SQL MVP
"Simon" <sh856531@.microsofts_free_email_service.com> wrote in message
news:%23GAkzXyzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Thanks for your answers so far.
> I'm still not sure about something. Are ADO.net transactions and TSQL
> transactions essentially equivelent?
> If I do a big stored procedure, locked through ADO.net, will all the the
> rows be locked until the SP returns?
> I'm worried that if I use ADO, the stored procedure still might cause a
> concurrency problem. This is essentially my problem.
> Thanks again
> Simon
>|||Andrew J. Kelly wrote:
> Simon,
> An ADO transaction (.net or otherwise) is nothing more than passing a
> BEGIN TRAN to SQL Server. So it's always a SQL Server transaction
> and any rows locked after the first Begin Tran is issued (regardless
> of where or by who) on that connection will remain locked until the
> final commit (if nested) or the first Rollback.
>
I would add that if concurrency is a concern, letting SQL Server start
and end the transaction from within the SP will be faster than doing so
from .net because it will eliminate 2 additional round-trips to the
server.
--
David Gugick
Imceda Software
www.imceda.com|||I agree, you always want to keep the transactions as short as possable. I
was just trying to get the point across that there really is no such thing
as an ADO tran, it is really SQL Server that is managing it.
--
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23a4gWk0zEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
>> Simon,
>> An ADO transaction (.net or otherwise) is nothing more than passing a
>> BEGIN TRAN to SQL Server. So it's always a SQL Server transaction
>> and any rows locked after the first Begin Tran is issued (regardless
>> of where or by who) on that connection will remain locked until the
>> final commit (if nested) or the first Rollback.
> I would add that if concurrency is a concern, letting SQL Server start and
> end the transaction from within the SP will be faster than doing so from
> .net because it will eliminate 2 additional round-trips to the server.
> --
> David Gugick
> Imceda Software
> www.imceda.com

ADO Timeout & SP

Hi,
Can someone please tell me the expected behavior of a stored procedure
when called via an ADO command object.
My question is, if ADO hits a timeout, will the SP (updates a table)
rollback what it was doing?
Thanks in advance,
Dan.It will either rollback or commit, it won't leave the transaction
hanging, if that's what you're asking. Usually when ADO times out it's
when it's trying to connect, at which time none of your stored
procedures will be in call. If you want to examine what's going on on
the server, open a Profiler trace and step through your code a line at
a time.
-- Mary
MCW Technologies
http://www.mcwtech.com
On 21 Jan 2004 03:43:53 -0800, danielcoates@.yahoo.com (Dan Coates)
wrote:
>Hi,
>Can someone please tell me the expected behavior of a stored procedure
>when called via an ADO command object.
>My question is, if ADO hits a timeout, will the SP (updates a table)
>rollback what it was doing?
>Thanks in advance,
>Dan.

ADO Stream from Stored Procedure with JScript fails mysteriously

Hi all, I've seen a couple of references to this problem, but only one
solution which had no appreciable effect.
I have a stored procedure which uses FOR XML EXPLICIT. The stored
procedure works quite happily and I can access the DB perfectly well
using VBScript.
Being of a religious bent, I'd rather not use VBScript.
I have the following *working* function, ported directly from the
broken JScript version to VBScript. The Jscript version fails on
Execute telling me that my procedure wasn't expecting any parameters.
Handy that, 'cos it doesn't get any.
I've tried using oCmd.Execute(0,0,1024),
oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
all points in between.
The error varies amusingly if I give it a nonsensical constant, but
provided I stick to something sensible, I get an 0x80040E21 error.
Finally, there are no differences between the two versions other than
the obvious ones.
Any idea how I can get this working in JScript?
******** begin code ********
function fetchXML()
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.CommandType = 4
oCmd.CommandText = "prcMenu_FetchMenu"
set stmOut = Server.CreateObject("ADODB.Stream")
stmOut.Open()
set cn = getConnection()
oCmd.ActiveConnection = cn
oCmd.Properties("Output Stream") = stmOut
oCmd.Execute , , 1024
set cn = nothing
set oCmd = nothing
sOut = "<menu>"+stmOut.ReadText()+"</menu>"
set stmOut = nothing
fetchXML = sOut
End Function
******** end code *******
Cheers,
-- Bob
0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
operation generated errors. Check each status value. No work was done."
Check your stored procedure text.
This procedure works (MS SQL Server 2000, local, database pubs, Windows
security):
//----
var conn = new ActiveXObject("ADODB.Connection");
conn.Open("Provider=SQLOLEDB;Integrated Security=SSPI;Initial
catalog=pubs");
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;
cmd.CommandText = "SELECT * FROM authors FOR XML AUTO";
var stream = new ActiveXObject("ADODB.Stream");
stream.Open();
cmd.Properties("Output Stream") = stream;
cmd.Execute(null, null, 0x400);
WScript.Echo(stream.ReadText());
//----
//--
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE
"Bob Gregory" <bobgregory@.ppsltd.net> ??/?? ? ?? ??:
news:aba9c4c4.0407200859.7a64e94b@.posting.google.c om...
> Hi all, I've seen a couple of references to this problem, but only one
> solution which had no appreciable effect.
> I have a stored procedure which uses FOR XML EXPLICIT. The stored
> procedure works quite happily and I can access the DB perfectly well
> using VBScript.
> Being of a religious bent, I'd rather not use VBScript.
> I have the following *working* function, ported directly from the
> broken JScript version to VBScript. The Jscript version fails on
> Execute telling me that my procedure wasn't expecting any parameters.
> Handy that, 'cos it doesn't get any.
> I've tried using oCmd.Execute(0,0,1024),
> oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
> commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
> all points in between.
> The error varies amusingly if I give it a nonsensical constant, but
> provided I stick to something sensible, I get an 0x80040E21 error.
> Finally, there are no differences between the two versions other than
> the obvious ones.
> Any idea how I can get this working in JScript?
> ******** begin code ********
> function fetchXML()
> set oCmd = Server.CreateObject("ADODB.Command")
> oCmd.CommandType = 4
> oCmd.CommandText = "prcMenu_FetchMenu"
> set stmOut = Server.CreateObject("ADODB.Stream")
> stmOut.Open()
> set cn = getConnection()
> oCmd.ActiveConnection = cn
> oCmd.Properties("Output Stream") = stmOut
> oCmd.Execute , , 1024
> set cn = nothing
> set oCmd = nothing
> sOut = "<menu>"+stmOut.ReadText()+"</menu>"
> set stmOut = nothing
> fetchXML = sOut
> End Function
> ******** end code *******
> Cheers,
> -- Bob
|||"Viatcheslav V. Vassiliev" <msnewsgroup@.www-sharp.com> wrote in message news:<O63yoqobEHA.4092@.TK2MSFTNGP10.phx.gbl>...
> 0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
> operation generated errors. Check each status value. No work was done."
> Check your stored procedure text.
> This procedure works (MS SQL Server 2000, local, database pubs, Windows
> security):
<snip />
I submitted the system in VBScript thanks to deadline constraints so
this is now a purely academic issue. I know the stored proc is
absolutely fine, because I return useful results from VBScript with
the same procedure. The only difference is the language, and
presumably the underlying mechanics of speaking to COM therewith.
-- Bob

ADO Stored Procedure Question

For the stored procedure I have below (with part of the VB code) that runs in
Query Analyzer and returns a value, but when I run it from a VB DLL, I do not
get anything back in my output parameter and I do not get an error. Can
someone tell me what I am doing wrong?
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter("returnValue", adInteger, adParamReturnValue)
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
rs("site_id"))
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter("PropertyValue", adVarChar, adParamOutput,
25)
cmdImageProperty.Execute
Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue varchar(25)
Output) As
SELECT @.propertyValue = property_value
FROM site_properties
WHERE site_id = @.siteID AND property_type_id = 18
If @.propertyValue = ''
SELECT @.propertyValue = client_site_id FROM sites WHERE site_id = @.siteID
Return @.@.ErrorTry adding SET NOCOUNT ON to the beginning of your proc. This will suppress
DONE_IN_PROC messages that can cause issues with ADO apps.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:27532132-6943-441D-9117-80148A2E4036@.microsoft.com...
> For the stored procedure I have below (with part of the VB code) that runs
> in
> Query Analyzer and returns a value, but when I run it from a VB DLL, I do
> not
> get anything back in my output parameter and I do not get an error. Can
> someone tell me what I am doing wrong?
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter("returnValue", adInteger,
> adParamReturnValue)
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
> rs("site_id"))
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter("PropertyValue", adVarChar,
> adParamOutput,
> 25)
> cmdImageProperty.Execute
> Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue varchar(25)
> Output) As
> SELECT @.propertyValue = property_value
> FROM site_properties
> WHERE site_id = @.siteID AND property_type_id = 18
> If @.propertyValue = ''
> SELECT @.propertyValue = client_site_id FROM sites WHERE site_id = @.siteID
> Return @.@.Error|||I just tried this this morning and unfortunately it did not work. Funny thing
is...if I only have one select statement in my procedure, it works fine. I
don't want to have to run back and forth to SQL Server two times for a simple
lookup. Any other suggestions?
Also, if you don't mind me piggy backing on this one, I have another
question posted that seems it is being ignored. Can you please look for a
post on the 23rd with the subject "Update one column from another column and
table".
You've answered a few of my questions before...thanks a lot for your
continued help.
"Dan Guzman" wrote:
> Try adding SET NOCOUNT ON to the beginning of your proc. This will suppress
> DONE_IN_PROC messages that can cause issues with ADO apps.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:27532132-6943-441D-9117-80148A2E4036@.microsoft.com...
> > For the stored procedure I have below (with part of the VB code) that runs
> > in
> > Query Analyzer and returns a value, but when I run it from a VB DLL, I do
> > not
> > get anything back in my output parameter and I do not get an error. Can
> > someone tell me what I am doing wrong?
> >
> > cmdImageProperty.Parameters.Append
> > cmdImageProperty.CreateParameter("returnValue", adInteger,
> > adParamReturnValue)
> > cmdImageProperty.Parameters.Append
> > cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
> > rs("site_id"))
> > cmdImageProperty.Parameters.Append
> > cmdImageProperty.CreateParameter("PropertyValue", adVarChar,
> > adParamOutput,
> > 25)
> > cmdImageProperty.Execute
> >
> > Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue varchar(25)
> > Output) As
> >
> > SELECT @.propertyValue = property_value
> > FROM site_properties
> > WHERE site_id = @.siteID AND property_type_id = 18
> >
> > If @.propertyValue = ''
> > SELECT @.propertyValue = client_site_id FROM sites WHERE site_id = @.siteID
> >
> > Return @.@.Error
>
>|||In case anyone reads this and has the same issue. It seems that there is no
way to do this with the command object. I was able to accomplish this using a
recordset object and the method NextRecordSet. So in my stored procedure, I
am explicitly doing two select statements (I don't care at this point which
one is blank). Then in my VB app, I am checking the primary recordset that I
want and if it is blank, I then use the Set rs = rs.NextRecordSet to use the
next recordset that was returned, which has the item I want.
Since I am only returning one field (in this instance), If anyone knows how
to do this with a command object, I would really appreciate an example.
"Mike Collins" wrote:
> I just tried this this morning and unfortunately it did not work. Funny thing
> is...if I only have one select statement in my procedure, it works fine. I
> don't want to have to run back and forth to SQL Server two times for a simple
> lookup. Any other suggestions?
> Also, if you don't mind me piggy backing on this one, I have another
> question posted that seems it is being ignored. Can you please look for a
> post on the 23rd with the subject "Update one column from another column and
> table".
> You've answered a few of my questions before...thanks a lot for your
> continued help.
> "Dan Guzman" wrote:
> > Try adding SET NOCOUNT ON to the beginning of your proc. This will suppress
> > DONE_IN_PROC messages that can cause issues with ADO apps.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> > news:27532132-6943-441D-9117-80148A2E4036@.microsoft.com...
> > > For the stored procedure I have below (with part of the VB code) that runs
> > > in
> > > Query Analyzer and returns a value, but when I run it from a VB DLL, I do
> > > not
> > > get anything back in my output parameter and I do not get an error. Can
> > > someone tell me what I am doing wrong?
> > >
> > > cmdImageProperty.Parameters.Append
> > > cmdImageProperty.CreateParameter("returnValue", adInteger,
> > > adParamReturnValue)
> > > cmdImageProperty.Parameters.Append
> > > cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
> > > rs("site_id"))
> > > cmdImageProperty.Parameters.Append
> > > cmdImageProperty.CreateParameter("PropertyValue", adVarChar,
> > > adParamOutput,
> > > 25)
> > > cmdImageProperty.Execute
> > >
> > > Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue varchar(25)
> > > Output) As
> > >
> > > SELECT @.propertyValue = property_value
> > > FROM site_properties
> > > WHERE site_id = @.siteID AND property_type_id = 18
> > >
> > > If @.propertyValue = ''
> > > SELECT @.propertyValue = client_site_id FROM sites WHERE site_id = @.siteID
> > >
> > > Return @.@.Error
> >
> >
> >|||Mike, I ran the following code snippet and was able to retrieve the output
parameter without NextRecordset. I'm not sure what might be different in
your environment, though.
cmdImageProperty.Parameters.Append _
cmdImageProperty.CreateParameter( _
"returnValue", adInteger, adParamReturnValue)
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter( _
"siteID", adInteger, adParamInput, ,2)
cmdImageProperty.Parameters.Append _
cmdImageProperty.CreateParameter( _
"PropertyValue", adVarChar, adParamOutput,25)
cmdImageProperty.Execute
MsgBox cmdImageProperty.Parameters(2)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C70CC9A8-2DDC-43E1-970B-0C43E353DBFF@.microsoft.com...
> In case anyone reads this and has the same issue. It seems that there is
> no
> way to do this with the command object. I was able to accomplish this
> using a
> recordset object and the method NextRecordSet. So in my stored procedure,
> I
> am explicitly doing two select statements (I don't care at this point
> which
> one is blank). Then in my VB app, I am checking the primary recordset that
> I
> want and if it is blank, I then use the Set rs = rs.NextRecordSet to use
> the
> next recordset that was returned, which has the item I want.
> Since I am only returning one field (in this instance), If anyone knows
> how
> to do this with a command object, I would really appreciate an example.
> "Mike Collins" wrote:
>> I just tried this this morning and unfortunately it did not work. Funny
>> thing
>> is...if I only have one select statement in my procedure, it works fine.
>> I
>> don't want to have to run back and forth to SQL Server two times for a
>> simple
>> lookup. Any other suggestions?
>> Also, if you don't mind me piggy backing on this one, I have another
>> question posted that seems it is being ignored. Can you please look for a
>> post on the 23rd with the subject "Update one column from another column
>> and
>> table".
>> You've answered a few of my questions before...thanks a lot for your
>> continued help.
>> "Dan Guzman" wrote:
>> > Try adding SET NOCOUNT ON to the beginning of your proc. This will
>> > suppress
>> > DONE_IN_PROC messages that can cause issues with ADO apps.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> > news:27532132-6943-441D-9117-80148A2E4036@.microsoft.com...
>> > > For the stored procedure I have below (with part of the VB code) that
>> > > runs
>> > > in
>> > > Query Analyzer and returns a value, but when I run it from a VB DLL,
>> > > I do
>> > > not
>> > > get anything back in my output parameter and I do not get an error.
>> > > Can
>> > > someone tell me what I am doing wrong?
>> > >
>> > > cmdImageProperty.Parameters.Append
>> > > cmdImageProperty.CreateParameter("returnValue", adInteger,
>> > > adParamReturnValue)
>> > > cmdImageProperty.Parameters.Append
>> > > cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
>> > > rs("site_id"))
>> > > cmdImageProperty.Parameters.Append
>> > > cmdImageProperty.CreateParameter("PropertyValue", adVarChar,
>> > > adParamOutput,
>> > > 25)
>> > > cmdImageProperty.Execute
>> > >
>> > > Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue
>> > > varchar(25)
>> > > Output) As
>> > >
>> > > SELECT @.propertyValue = property_value
>> > > FROM site_properties
>> > > WHERE site_id = @.siteID AND property_type_id = 18
>> > >
>> > > If @.propertyValue = ''
>> > > SELECT @.propertyValue = client_site_id FROM sites WHERE site_id =>> > > @.siteID
>> > >
>> > > Return @.@.Error
>> >
>> >
>> >|||Thank you. I'm not sure what is different in my system yet, but I will
continue to look. I was wondering if it has anything to do with me setting
the connection's cursor location to client side. I was able to set up a
recordset, and return what I needed by expliciting running both select
statements and checking the recordset of each using NextRecordset. Not the
way I wanted, but it will work for now.
"Dan Guzman" wrote:
> Mike, I ran the following code snippet and was able to retrieve the output
> parameter without NextRecordset. I'm not sure what might be different in
> your environment, though.
> cmdImageProperty.Parameters.Append _
> cmdImageProperty.CreateParameter( _
> "returnValue", adInteger, adParamReturnValue)
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter( _
> "siteID", adInteger, adParamInput, ,2)
> cmdImageProperty.Parameters.Append _
> cmdImageProperty.CreateParameter( _
> "PropertyValue", adVarChar, adParamOutput,25)
> cmdImageProperty.Execute
> MsgBox cmdImageProperty.Parameters(2)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:C70CC9A8-2DDC-43E1-970B-0C43E353DBFF@.microsoft.com...
> > In case anyone reads this and has the same issue. It seems that there is
> > no
> > way to do this with the command object. I was able to accomplish this
> > using a
> > recordset object and the method NextRecordSet. So in my stored procedure,
> > I
> > am explicitly doing two select statements (I don't care at this point
> > which
> > one is blank). Then in my VB app, I am checking the primary recordset that
> > I
> > want and if it is blank, I then use the Set rs = rs.NextRecordSet to use
> > the
> > next recordset that was returned, which has the item I want.
> >
> > Since I am only returning one field (in this instance), If anyone knows
> > how
> > to do this with a command object, I would really appreciate an example.
> >
> > "Mike Collins" wrote:
> >
> >> I just tried this this morning and unfortunately it did not work. Funny
> >> thing
> >> is...if I only have one select statement in my procedure, it works fine.
> >> I
> >> don't want to have to run back and forth to SQL Server two times for a
> >> simple
> >> lookup. Any other suggestions?
> >>
> >> Also, if you don't mind me piggy backing on this one, I have another
> >> question posted that seems it is being ignored. Can you please look for a
> >> post on the 23rd with the subject "Update one column from another column
> >> and
> >> table".
> >>
> >> You've answered a few of my questions before...thanks a lot for your
> >> continued help.
> >>
> >> "Dan Guzman" wrote:
> >>
> >> > Try adding SET NOCOUNT ON to the beginning of your proc. This will
> >> > suppress
> >> > DONE_IN_PROC messages that can cause issues with ADO apps.
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > Dan Guzman
> >> > SQL Server MVP
> >> >
> >> > "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> > news:27532132-6943-441D-9117-80148A2E4036@.microsoft.com...
> >> > > For the stored procedure I have below (with part of the VB code) that
> >> > > runs
> >> > > in
> >> > > Query Analyzer and returns a value, but when I run it from a VB DLL,
> >> > > I do
> >> > > not
> >> > > get anything back in my output parameter and I do not get an error.
> >> > > Can
> >> > > someone tell me what I am doing wrong?
> >> > >
> >> > > cmdImageProperty.Parameters.Append
> >> > > cmdImageProperty.CreateParameter("returnValue", adInteger,
> >> > > adParamReturnValue)
> >> > > cmdImageProperty.Parameters.Append
> >> > > cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
> >> > > rs("site_id"))
> >> > > cmdImageProperty.Parameters.Append
> >> > > cmdImageProperty.CreateParameter("PropertyValue", adVarChar,
> >> > > adParamOutput,
> >> > > 25)
> >> > > cmdImageProperty.Execute
> >> > >
> >> > > Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue
> >> > > varchar(25)
> >> > > Output) As
> >> > >
> >> > > SELECT @.propertyValue = property_value
> >> > > FROM site_properties
> >> > > WHERE site_id = @.siteID AND property_type_id = 18
> >> > >
> >> > > If @.propertyValue = ''
> >> > > SELECT @.propertyValue = client_site_id FROM sites WHERE site_id => >> > > @.siteID
> >> > >
> >> > > Return @.@.Error
> >> >
> >> >
> >> >
>
>

Tuesday, March 27, 2012

Advice on DTS Package in Replication

Advice on ALTER TABLE statement.

Thursday, March 22, 2012

Adventureworks BillofMaterials

Monday, March 19, 2012

Advanced Transact_SQL

Advanced Stored procedure...

Thursday, March 8, 2012

ADP Parameterized list box stored proecedure /function

Saturday, February 25, 2012

ADODB Command (Stored Procedure)

ADODB Command

ADO/SQL error -2147217900 (80040e14)

Friday, February 24, 2012

ADO.net or TSQL Transactions

ADO.net or TSQL Transactions

ADO.net or TSQL Transactions

ADO Timeout & SP

ADO Stream from Stored Procedure with JScript fails mysteriously

ADO Stored Procedure Question

SQL Adding a User account

Blog Archive

About Me