SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 sp_executesql question
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

pithhelmet
Posting Yak Master

183 Posts

Posted - 02/21/2008 :  10:18:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 02/21/2008 :  10:34:35  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Yes you can use FOR XML. But why you need sp_executesql here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 02/21/2008 :  11:52:14  Show Profile  Reply with Quote

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.


Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 02/21/2008 :  11:55:23  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 02/21/2008 :  13:32:36  Show Profile  Reply with Quote
ok -

so how

use the example and make it work in sql 2000
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/21/2008 :  13:50:35  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 02/22/2008 :  08:42:23  Show Profile  Reply with Quote
>>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.]
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 02/22/2008 :  08:53:40  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/22/2008 :  09:48:44  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 02/22/2008 :  09:53:39  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 02/22/2008 :  11:55:36  Show Profile  Reply with Quote
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


Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/22/2008 :  11:59:32  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 02/22/2008 12:06:00
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 02/22/2008 :  14:03:27  Show Profile  Reply with Quote
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!!

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/22/2008 :  15:24:38  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 02/22/2008 16:07:22
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 02/25/2008 :  09:30:04  Show Profile  Reply with Quote

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.




Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/25/2008 :  09:55:21  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 02/25/2008 10:12:00
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/25/2008 :  10:00:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/25/2008 :  10:06:47  Show Profile  Reply with Quote
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



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/25/2008 :  10:21:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh man...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56402



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/25/2008 :  10:34:43  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 02/25/2008 :  10:50:21  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000