SQL Adding a User account: stored

Showing posts with label stored. 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

Sunday, March 25, 2012

Advice for grouping stored procedures?

I am preparing to upgrade a fairly large application, so I'm looking for some advice before I begin my design. I have quite a bit of experience in Oracle, but I am required to use SQLServer for data storage in this application. When using Oracle, it is easy to group related stored procedures into packages. In SQLServer; however, I don't seem to have that option. Any opinions on a good way to group code for easy maintenance and navigation (i.e. in Management Studio - I'd prefer not to see a list of several hundred stored procedures)? I've already thought about naming conventions, but I'd prefer a solution that is more like the Oracle package solution.In SQL 2005 you can create a project and store all scripts for procs in it also you can use Schemas to group your database objects and rights to them.

Good Luck.sql

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

Tuesday, March 20, 2012

Advantages of Table variables over Temp tables

What are the advantages of using a table type variables over a
temporary table. Isn't the data stored in the temp db in the case of
table type variables? if not, where is the data stored?An article on this here:
http://www.aspfaq.com/show.asp?id=2475
David Portas
SQL Server MVP
--|||You can read following article written by Mike Gunderloy, describing
usage and advantages with respect to Table variable and temporary
tables.
<http://developer.com/db/article.php/10920_3414331_1>
-Hari Sharma
India|||http://toponewithties.blogspot.com/...nd.h
tml
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<balacr@.gmail.com> wrote in message
news:1127109967.631131.113870@.g14g2000cwa.googlegroups.com...
> What are the advantages of using a table type variables over a
> temporary table. Isn't the data stored in the temp db in the case of
> table type variables? if not, where is the data stored?
>

Advantages of Stored Procedures?

Hi,
Generally I write all my SQL in Stored Procedures instead of using adhoc queries. But I dont feel good about stored procedures when I come across situations like this.

Lets say that I have a stored procedure something like this

CREATE PROCEDURE dbo.proc_MYSP
@.CaseID char(10)
AS
SELECT * FROM TABLE1WHERE CASEID = @.CASEID

Suppose in future if the field CASEID is changed to char(20) then I need to change the declaration of CaseID in all my stored procedures that take CaseID as input parameter. If I write adhoc queries then I need not worry about this. Is there any effective solution for a situation like this.

Thank you.>>If I write adhoc queries then I need not worry about this...

you will have that prob no matter if you use stored procs or adhoc queries. you'd still need to make the changes whereever req'd. its just less pain to make the changes in the stored procs than asp pages. business logic is best kept at the backend. its like a rule.

hth|||mndinkar, I seriously hope you meant this as a joke. See:

::you will have that prob no matter if you use stored procs or adhoc queries. you'd still need
::to make the changes whereever req'd. its just less pain to make the changes in the stored
::procs than asp pages. business logic is best kept at the backend. its like a rule.

There is a couple of things that need to be ccorrected here.

First, you seriously oversimplify. See, we do pretty complex apps. I use dynamic SQL. Still I haeve no sql at all in any ASP page. Basically: dude, get real. THere is a lot you can make between writing SP's and putting the SQL into asp pages. Stuff like working with classes. Or, if you want to be primitive, stuff like centralising all your SQL statements in one class.

Second, basically, a SP like the one shown above - nothing more than a select - givesyou nothing but adds. It is another layer that has to be maintained, and it is one that is badly integrated into the development environment (integration with VSS, for example, or the requirements for debugging). How can maintaining more than you have be less painfull? When you gain nothing and only loose?

Third, the statement that business logic per se is best kept at the backend is bordering on irresponsibility. The complete school of modern software architecture goes against putting business logic into the data store. Multi tiered applications are the modern concept of how to make software, and no, they are not about dumping all your business logic into the database. Quite the contrary.

Don't get met wrong, there is nothing wqrong with using SP's iin certain situations. Just when you advice to use them - no. They just cost you.|||Take a look at this, it's a very good cool headed approach to the issues.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/storedprocsnetdev2.asp|||And for a good hot-headed discussion on the issue, see Rob Howard's classic blog post and repliesDon't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome) And of course Frans Bouma's rebuttal blog postStored procedures are bad, m'kay?.

I at least thought it was interesting reading.

Terri|||those were interesting links terri. got to know a few things. thanks.

Advantages and disadvantages of stored proc encryption?

Security is good for me as for developer. But what are advantages and
di

vantages of stored proc encryption?
May be encrypted sp's are executing slow?Though encryption is good from security point of view, SQL Server 2000
stored procs can be easily decrypted. In terms of performance there's no
difference, as the execution plan will be the same. But if you do encrypt,
make sure you have the source readily available.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
Security is good for me as for developer. But what are advantages and
di

vantages of stored proc encryption?
May be encrypted sp's are executing slow?|||...also, troubleshooting in the production environment becomes a bit more d
ifficult. Like execute
the proc, compare the source (which you don't have) to the execution plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:udzTFmxnFHA.3540@.TK2MSFTNGP10.phx.gbl...
> Though encryption is good from security point of view, SQL Server 2000
> stored procs can be easily decrypted. In terms of performance there's no
> difference, as the execution plan will be the same. But if you do encrypt,
> make sure you have the source readily available.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> di

vantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>
>|||Oleg,
better is upload the code and its revisions to VSS
"Narayana Vyas Kondreddi" wrote:

> Though encryption is good from security point of view, SQL Server 2000
> stored procs can be easily decrypted. In terms of performance there's no
> difference, as the execution plan will be the same. But if you do encrypt,
> make sure you have the source readily available.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> di

vantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>
>|||Thank you All.
By the way,
what about encryption in sql2005 editions?
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> di

vantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>|||The encryption is trivial to break with freely available tools.
Encrypted code has very little to do with real security. It's pretty
much a cosmetic feature that *may* limit low-level meddling by
incompetent users but won't protect you against smart hackers.
David Portas
SQL Server MVP
--

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 much
Hi
"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 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 i
s
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 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

ADSI and Reporting Services Permission issue.

I have a stored proc that calls on a view in sql server. The view is
actually my Active Directory LDAP query. I have already created a
linked server and account for my user name jkim.
I have a report that polls data from this stored proc. After I set the
datasource which uses an account "sqluser", I try to the run the report
and I get this:
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Query execution failed for data set 'DispatchActivity'.
(rsErrorExecutingCommand) Get Online Help
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
The data source uses the account "sqluser" which is an account that's
stored in the report server. I'm pretty sure this is what's causing
the error. Does 'sqluser' have to be an actual Active Directory user
for it to have access to the ADSDSOObject?
I don't know if I'm making any sense.nevermind, i figured it out.
i didn't check the "use as windows authentication" checkbox in the
datasource.

ADP revision numbers

I have an ADP connected to an SQL 2000 database that works fine on my
machine,
Opening the ADP show lists of stored procedures ala
up_ListPrograms
but, when I copy it to another computer, and open the ADP with the
same connection properties
all of the stored procedures and views have a ?revision? number after
them ala
up_ListPrograms,1

this breaks all my forms, reports etc.
anyone know if this can be controlled somehow?

ThansBruce,

I feel your pain. I know exactly what your are talking about but can't
remember what I did that broke and fixed it for sure.

Hmm, this may be a long shot, but you might want to run "cliconfg" and see
if your using the same default protocol on the client as it is on your dev
unit. Using Named Pipes might be what is causing it. If it is, you can
actually go into the Server Network Utility on the SQL Server and set your
default protocol to TCP/IP and your clients will follow that.

Also, I use integrated/domain security so I can deploy generic ADE's to my
users. Then, when they open, their windows credentials are used to auth
with Sql.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
news:2cd7rv030dvl7oqo23idekbvmcpp7valv0@.4ax.com...
> I have an ADP connected to an SQL 2000 database that works fine on my
> machine,
> Opening the ADP show lists of stored procedures ala
> up_ListPrograms
> but, when I copy it to another computer, and open the ADP with the
> same connection properties
> all of the stored procedures and views have a ?revision? number after
> them ala
> up_ListPrograms,1
> this breaks all my forms, reports etc.
> anyone know if this can be controlled somehow?
> Thans|||Thanks, turns out the problem machine did not have
all the Service Packs up-to-date. Fixed now.

On Mon, 17 Nov 2003 14:39:32 GMT, "Jerry Boone"
<jerry@.antech.biz.nospam> wrote:

>Bruce,
>I feel your pain. I know exactly what your are talking about but can't
>remember what I did that broke and fixed it for sure.
>Hmm, this may be a long shot, but you might want to run "cliconfg" and see
>if your using the same default protocol on the client as it is on your dev
>unit. Using Named Pipes might be what is causing it. If it is, you can
>actually go into the Server Network Utility on the SQL Server and set your
>default protocol to TCP/IP and your clients will follow that.
>Also, I use integrated/domain security so I can deploy generic ADE's to my
>users. Then, when they open, their windows credentials are used to auth
>with Sql.

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 fill recrodset with stored proc

I have a ADODB recordset problem. I want to use a stored proc to retrieve a
recordset. Code goes something like:
Set SQLCmd = New ADODB.Command
Set SQLCmd.ActiveConnection = SQLConn
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = SQLConn
rs.CursorType = adOpenForwardOnly
SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
SQLCmd.CommandType = adCmdStoredProc
'*** Set up parameters
SQLCmd.Parameters.Refresh
SQLCmd.Parameters(1) = "aaa"
…
SQLCmd.Parameters(6) = 0
Set rs = SQLCmd.Execute
The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
Pre tests:
I know the connection is active because when I call
SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
I have copied the parameters into the sql query analyzer and records are
returned
The problem is that the recordset is not being filled
Can you tell me what I am missing please.
Thanks in advance,
Stewart RogersDo you have "Set NoCOunt On"
at the beginning of your Stored Proc '
If Not, add it and try again...
"Datasort" wrote:

> I have a ADODB recordset problem. I want to use a stored proc to retrieve
a
> recordset. Code goes something like:
>
> Set SQLCmd = New ADODB.Command
> Set SQLCmd.ActiveConnection = SQLConn
> Set rs = New ADODB.Recordset
> Set rs.ActiveConnection = SQLConn
> rs.CursorType = adOpenForwardOnly
>
> SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
> SQLCmd.CommandType = adCmdStoredProc
> '*** Set up parameters
> SQLCmd.Parameters.Refresh
> SQLCmd.Parameters(1) = "aaa"
> …
> SQLCmd.Parameters(6) = 0
> Set rs = SQLCmd.Execute
> The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
> Pre tests:
> I know the connection is active because when I call
> SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
> I have copied the parameters into the sql query analyzer and records are
> returned
> The problem is that the recordset is not being filled
> Can you tell me what I am missing please.
> Thanks in advance,
> Stewart Rogers
>|||try this:
Dim rs As Adodb.Recordset
Set rs = new ADODB.Recordset
SQLConn.usp_Rpt_PFrom_SetWhereTableBooks "aaa", rs
I bet that works.
Greg Jackson
Portland, OR|||That did it ... Thanks!!! Can you tell me why it works with nocount on?
"CBretana" wrote:
> Do you have "Set NoCOunt On"
> at the beginning of your Stored Proc '
> If Not, add it and try again...
>
> "Datasort" wrote:
>|||Datasort...
Yes, what's going on is that SQL Server sends the Record count information
ahead of the actual recordset... and ADO is not smart enough to tell the
difference, so it's trying to "Read" the record count info as your recordset
,
and failing... It couldn' be coded to figure this out, because ADO 2.x added
functionality to handle multiple recordsets in one call to the server.
There's a method (on the RecordSet Object) in ADO called .NextRecordset, tha
t
moves to the next one in the sequence, until it retruns null when you call i
t
on the last one.
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||And, obviously, adding Set NoCount On, removes this information from the TDS
(Tabular Data Stream), and then all that's sent backto ADO Is the actual
recordset...
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||Odd, horribly odd!
That sounds like a bug since the record count is not returned (or was not
returned) as a recordset, it is returned as a message AFAIK. Further, the
record count is returned after the requested recordset, not before since it
doesn't know the count until it has returned the rerdorset.
So, I would prefer to see this regarded as a BIG BAD BUG!
Comments?
- Tim
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C6A14044-735D-4DD7-802D-1BB719A4A40F@.microsoft.com...
> Datasort...
> Yes, what's going on is that SQL Server sends the Record count information
> ahead of the actual recordset... and ADO is not smart enough to tell the
> difference, so it's trying to "Read" the record count info as your
> recordset,
> and failing... It couldn' be coded to figure this out, because ADO 2.x
> added
> functionality to handle multiple recordsets in one call to the server.
> There's a method (on the RecordSet Object) in ADO called .NextRecordset,
> that
> moves to the next one in the sequence, until it retruns null when you call
> it
> on the last one.
> "Datasort" wrote:
>|||this is common and well documented behavior in ado
GAJ|||So its a "feature" then?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23nc7lqYKFHA.3420@.tk2msftngp13.phx.gbl...
> this is common and well documented behavior in ado
>
> GAJ
>

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:Dispaly Limited Records

Hi all;
qrymillcgetbytype_app ( stored query in MS-Access) in my code :

MS-ACCESS Part:
query defination
PARAMETERS prmMillToolType Long;
SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length]
FROM MILLC
WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True))
ORDER BY MILLC.ID;

C++ PART
bstrSQL contain stored query name "qrymillcgetbytype_app"
hr = piRecordSet->put_Source(bstrSQL);
piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown)

it opens fine
but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 "

it displays the same output. Where clause won't work?
Pls help me out.

It sounds like you want to further refine the results of the qrymillcgetbytype query...In this case, I wouldn't expect appending an additional where clause to the query name to be parsed correctly. One suggestion that might work is to use something like:

SELECT * FROM qrymillcgetbytype_app WHERE <your where clause here>

|||

Hi ;

Thanks for reply.

Well previous code was functional in case of DAO.

When I do Creating a recordeset by DAO.

but that Code should also be functional for the case of ADO?

Waiting for your response.

Thanks in Advance Again.

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

ADO.NET Transaction Fails to update Database after .Commit()

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives?? Anyone else ever heard of such a thing. Please help, before I loose my sanity.I may need to be more specific about the problem, so here goes. I am running an ADO.NET SQLTransaction that is calling 3 different stored procedures. The first proc Inserts data into a MailMessage Table and returns the @.@.IDENTITY of the Insert. The next set of stored procedures are called in a loop to Insert into a MailboxMessageAddressees table that has a foreign key of the MailMessages table and the ID of the Addressee. This loop can run 1-50+ times to insert all the Addressees. In SQL Profiler, I can trace the connection I made to a database and it starts the transaction, executes all stored procedures, and then COMMITS. I recieve no errors in my transaction and no errors occur on SQLServer. But the data is never inserted into the database. Its almost like the ADO.NET Transaction didn't take place, or SQL Server is rolling back the transaction on its own without throwing any errors that I can see.

Needless to say, I have closed the connection and disposed of the command and connection. Is there something else I need to send to SQL Server other than COMMIT to let it know that I have completed my transaction. This is really perplexing because I can manually execute all the stored procedures in Entrerprise Manager. I get every statement executed from the SQL Profiler when I trace the Transaction and put it in Query Analyzer and it works fine, and all the data shows up in SQL Server. Is there something I am missing?

Here is the actual code, maybe someone can clue me into what I might be missing.

/// <summary>
/// Mail_DAL.AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,Wargame_ID)
/// ->Executes a ExecuteNonQuery using the Stored Procedure Mail_DeleteGroupAddressees and an ExecuteNonQuery using stored
/// procedure Mail_AddGroupAddressee to insert all addressees not in the Group.
/// </summary>
/// <param name="Mailbox_ID">Int: The Mailbox associated with the MailGroup.</param>
/// <param name="subject">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="body">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="recipientTo">ArrayList: An ArrayList of the To addresses Mailbox_IDs for the group.</param>
/// <param name="recipientCc">ArrayList: An ArrayList of the CC addresses Mailbox_IDs for the group.</param>
/// <param name="groupTo">ArrayList: An ArrayList of the To group addresses Mailbox_IDs for the group.</param>
/// <param name="groupCc">ArrayList: An ArrayList of the CC group addresses Mailbox_IDs for the group.</param>
/// <param name="files">ArrayList: An ArrayList of Files if there are any.</param>
/// <param name="Wargame_ID">Int: The Wargame_ID of any file to be put in Files</param>
public void AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,int Wargame_ID)
{
//Get To Addresses from Groups
OpenProcCommand(Conn,"Mail_GetCurrentGroupAddressees");
SqlCommand cmd = ProcCommand;
param = new SqlParameter();
SqlDataReader dr;
Int32 holder = 0;
if(groupTo.Count > 0)
{
foreach(Object item in groupTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientTo.Contains(holder))
{
recipientTo.Add(holder);
}
}
dr.Close();
}
}

//Get CC Addresses from Groups
if(groupCc.Count > 0)
{
foreach(Object item in groupCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientCc.Contains(holder))
{
recipientCc.Add(holder);
}
}
dr.Close();
}
}

//Get BCC Recipients for this Maillbox
ArrayList recipientAll = new ArrayList();
recipientAll.Add(Convert.ToInt32(Mailbox_ID));
foreach(Object item in recipientTo)
{
recipientAll.Add(item);
}
foreach(Object item in recipientCc)
{
if(!recipientAll.Contains(item))
{
recipientAll.Add(item);
}
}
string allRecipients = "";
foreach(Object item in recipientAll)
{
allRecipients += item + ",";
}
allRecipients = allRecipients.Substring(0,allRecipients.Length-1);
ArrayList recipientBcc = new ArrayList();
cmd.CommandText = "Mail_GetBccAddressees";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Addressees",SqlDbType.VarChar,1000);
param.Value = allRecipients;
dr = cmd.ExecuteReader();
//Add any Bcc Recipients to Bcc Recipients ArrayList
while(dr.Read())
{
recipientBcc.Add(Convert.ToInt32(dr["Addressee"].ToString()));
}
dr.Close();

// Start a local transaction.
SqlTransaction myTrans = Conn.BeginTransaction();
// Enlist the command in the current transaction.
cmd.Transaction = myTrans;

try
{
//Insert Message into Database
cmd.CommandText = "Mail_AddNewMessage";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MsgFrom",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.MsgSubject",SqlDbType.VarChar,100);
param.Value = subject;
param = cmd.Parameters.Add("@.MsgBody",SqlDbType.Text);
param.Value = body;
int msg_ID = Convert.ToInt32(cmd.ExecuteScalar());

//Add To Recipients
cmd.CommandText = "Mail_AddMessageAddressees";
foreach(Object item in recipientTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 1;
cmd.ExecuteNonQuery();
}

//Add CC Recipients
foreach(Object item in recipientCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 2;
cmd.ExecuteNonQuery();
}

//Add BCC Recipients
foreach(Object item in recipientBcc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 3;
cmd.ExecuteNonQuery();
}

//Add message to senders Mailbox in SentItems
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 4;
cmd.ExecuteNonQuery();

//Add files if there are any.
foreach(HttpPostedFile myfile in files)
{
//Get the file name
string FileName = myfile.FileName;
int strLoc = FileName.LastIndexOf("\\");
FileName = FileName.Remove(0,strLoc+1);

// Get size of uploaded file
int nFileLen = myfile.ContentLength;

// Allocate a buffer for reading of the file
byte[] myData = new byte[nFileLen];

// Read uploaded file from the Stream
myfile.InputStream.Read(myData, 0, nFileLen);
File file = new File();
int file_ID = file.UploadFile(cmd,3,FileName,nFileLen,myfile.GetType().ToString(),Wargame_ID,myData);

//Add the file message association
cmd.Parameters.Clear();
cmd.CommandText = "Mail_AddMessageFile";
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.File_ID",SqlDbType.Int);
param.Value = file_ID;
cmd.ExecuteNonQuery();
}
//Commit Transaction
myTrans.Commit();
}
catch (SqlException sqlex)
{
// Specific catch for deadlock
if (sqlex.Number != 1205)
{
myTrans.Rollback();
}
throw(sqlex);
}
catch(InvalidOperationException ex1){
string ex2 = ex1.ToString();
}
catch (Exception ex)
{
myTrans.Rollback();
throw (ex);
}
finally
{
myTrans.Dispose();
Conn.Close();
Conn.Dispose();
}

}|||First try to run a simple transaction with one stored proc and minimal .net code. This will make it easier to pinpoint your problem. Could be memory problem on server or some other potential problem you are not aware of.

This may not be a problem but I have encountered this one before. Are you returning more than one id with the @.@.IDENTITY call (i.e more than one insert command in a single transaction)? If you are you need to reset the sql connection or the same id is returned each time you call it if my memory serves me correctly.

Also, you can check the error status within a stored proc and return it to .net. I have used this before to pinpoint a problem.

Cheers

Mo|||Thanks Mo for the insight, unfortunatly I still have the same problems. Funny thing is I created a single stored procedure to do all the inserts, thinking that would solve the problem, but alas nothing. Getting different results, but same outcome. The database executes the procedure, which inserts data into the database, but for some reason after about 3 min or so the rows are rolled back. Running sp_lock I can see that the process that executed the command has a lock on all the tables, but it never gives them up. After that timeout, they are all rolled back. Is there some reason anyone can think of where this would happen. I can execute the stored procedure on the database using query analyzer and the data goes in fine and stays in there. Another thing to note, after the web application executes the stored procedure I cannot view the data in the tables through Enterprise manager. I can't even view the current activity, it sits there for a while then returns a 1222 Error. This has become a nightmare. Thanks

Chris|||Could be a bug between sql server and .net ?? Are both upto date with service packs?

Have you looked on http://support.microsoft.com

Very good area for problem solving, used it many times.

Tuesday, March 27, 2012

Advice on DTS Package in Replication

Advice on ALTER TABLE statement.

Sunday, March 25, 2012

Advice for grouping stored procedures?

Thursday, March 22, 2012

Adventureworks BillofMaterials

Tuesday, March 20, 2012

Advantages of Table variables over Temp tables

Advantages of Stored Procedures?

Advantages and disadvantages of stored proc encryption?

Monday, March 19, 2012

Advanced Transact_SQL

Advanced Transact_SQL

Advanced Transact_SQL

Advanced Stored procedure...

Thursday, March 8, 2012

ADSI and Reporting Services Permission issue.

ADP revision numbers

ADP Parameterized list box stored proecedure /function

Saturday, February 25, 2012

ADODB fill recrodset with stored proc

ADODB Command (Stored Procedure)

ADODB Command

ADO:Dispaly Limited Records

ADO/SQL error -2147217900 (80040e14)

ADO.NET Transaction Fails to update Database after .Commit()

SQL Adding a User account

Blog Archive

About Me