sp_executesql question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp_executesql question

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-02-21 : 10:18:24

Hi everyone -

I need to run sp_executesql with a query, the query will be in the form of a select statement...

select * from tablename

I need to place the select results into a string that can be parsed on the caller machine application.

Is there a serializer that is built into SQL 2k that i can use?
or should i just append a FOR XML RAW to the query line and use the XML string???

thanks
tony

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 10:34:35

Yes you can use FOR XML. But why you need sp_executesql here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-02-21 : 11:52:14

This is what i am thinking....

DECLARE @SQL nvarchar(300)
DECLARE @Params nvarchar (200)
DECLARE @DOC XML

SET @SQL = N'SET @DOCOUT = (SELECT * FROM clnmas FOR XML RAW)'
SET @Params = N'@DOCOUT XML OUTPUT'
EXEC sp_executesql @SQL, @Params, @DOCOUT = @DOC OUTPUT
SELECT @DOC

(This was code lifted from another coder)

it looks like exactly what i am after....

but it fails on the
DECLARE @DOC XML
line

Server: Msg 2715, Level 16, State 3, Line 1
Column or parameter #-3: Cannot find data type XML.
Parameter '@DOC' has an invalid data type.

Does anyone have any idea why it is failing....

To answer your question, one of the parameters is the line to be executed (select * from table)

and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 11:55:23

XML is not a datatype in SQL Server 2000. You can store output XML as a varchar data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-02-21 : 13:32:36

ok -

so how

use the example and make it work in sql 2000

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-21 : 13:50:35

What is the "the caller machine application" ? What language is it written in? It cannot accept a standard result set from a database?

- Jeff
http://weblogs.sqlteam.com/JeffS

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-02-22 : 08:42:23

>>What is the "the caller machine application" ?
It is a VB application

>>What language is it written in?
VB

>>It cannot accept a standard result set from a database?
Yes it can, but that is not the point of the task
[and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-22 : 08:53:40

Since SQL Server 2000 does not have built-in XML data type, you will have to return the result set to calling application and perform necessary operations on result set there, as Jeff said.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-22 : 09:48:44

A VB application is much more equipped to serialize data than T-SQL! If your exercise is to do things the most complicated way possible, then I suppose that makes sense, but if the exercise is to do something cleanly and efficiently and simply, then return a standard result set from your database and let your VB application do the work of formatting and converting that data. It is always a good idea to use hammers to pound nails and screwdrivers to turn screws and not vice-versa, right?

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-22 : 09:53:39

quote:
It is always a good idea to use hammers to pound nails and screwdrivers to turn screws and not vice-versa, right?

Perfect analogy !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-02-22 : 11:55:36

did you guys misread it or not read it at all???

[and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.]

encrypt them
encrypt them
encrypt them
encrypt them
encrypt them
encrypt them

and send them back to the caller

maybe i didn't say it enough times....

[and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.]

encrypt them
encrypt them
encrypt them
encrypt them
encrypt them
encrypt them

and send them back to the caller

[and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.]

encrypt them
encrypt them
encrypt them
encrypt them
encrypt them
encrypt them

and send them back to the caller

[and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.]

encrypt them
encrypt them
encrypt them
encrypt them
encrypt them
encrypt them

and send them back to the caller

[and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.]

encrypt them
encrypt them
encrypt them
encrypt them
encrypt them
encrypt them

and send them back to the caller

did you get the meaning yet???

[and i need to perform the query, and take the results of the query, encrypt them and send them back to the caller.]

encrypt them
encrypt them
encrypt them
encrypt them
encrypt them
encrypt them

and send them back to the caller

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-22 : 11:59:32

That's great. You still have a bad design. You should be sending back encrypted DATA not an encrypted STRUCTURE.

Should I cut and paste that 20 times in the post for you?

Maybe if you provide specifics and don't just repeat things over and over like a child you might get better help?

- Jeff
http://weblogs.sqlteam.com/JeffS

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-02-22 : 14:03:27

or maybe, just maybe, you could read the first few posts.
Then go back and read them again, and you will see, the specs were given, you didn't read them.

As to the design, it might be bad design, I happen to agree, it is a very poor design, but the powers that be want it that way, and they sign the checks, so i do as asked and cash my check at the end of the week.

thanks for the posts!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-22 : 15:24:38

If you think a one-sentence general description of what you are trying to do is a "spec", then I don't know what to say other than "good luck". You did not even come close to providing enough info for us to help you out. i.e., Is the XML document itself encoded? Or just CDATA? What schema are you using? What output are you looking for before or after it is encoded? What tables are you querying -- just a few, or all of them? Is it always a flat list of values, or can it hierarchical? Is ALL of data encoded, or just certain fields? What protocol is the VB app using to retrieve this "encrypted XML string" -- ADO? Something else? is it VB6,VBA or VB.NET? Why can't the VB app accept an encrypted record set? Can the data in the database itself be encrypted instead and have the app encode/decode it (a much more standard, simpler and more common way to do this)? I could go on and on ....

It's too bad, there are definitely those here that have the skill and experience to help you out, myself included, but it doesn't look like with your attitude your going to get any help here. Best of luck.

- Jeff
http://weblogs.sqlteam.com/JeffS

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-02-25 : 09:30:04

Jeff, thanks for you thoughts....

one work come to mind when i read your post....

BLOVIATE

http://dictionary.reference.com/wordoftheday/archive/2001/06/22.html

Please stop trying to make a mountain out of the simple request.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-25 : 09:55:21

Just trying to help you, dude, and point out that you haven't given us enough info, and there probably is a much easier and simpler and more standard way to do what you want. You said you gave us your "spec", and, unfortunately, in this silly little world of computers and programming that we work in, those pesky little computers tend to demand pretty precise instructions when we tell it what to do.

Again, good luck.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 10:00:35

quote:
Originally posted by pithhelmet

Does anyone have any idea why it is failing?

In SQL Server 2000, continue to use NVARCHAR variables than XML variables as suggested many times above.
SQL Server 2000 does not support XML variables. SQL Server 2005 does.

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-25 : 10:06:47

quote:
Originally posted by pithhelmet

Jeff, thanks for you thoughts....

one work come to mind when i read your post....

BLOVIATE

http://dictionary.reference.com/wordoftheday/archive/2001/06/22.html

Please stop trying to make a mountain out of the simple request.

Something about vinegar and honey?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 10:21:52

Oh man...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56402

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-25 : 10:34:43

What is the word for "throwing a temper tantrum when people who offer to try to help you for free ask for some simple information"?

- Jeff
http://weblogs.sqlteam.com/JeffS

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-02-25 : 10:50:21

Jeff, the word you are looking for is "Asshattery".

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources