| Author |
 Topic  |
|
pithhelmet
Posting Yak Master
183 Posts |
 Posted - 02/21/2008 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/21/2008 : 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 - 02/21/2008 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/21/2008 : 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 - 02/21/2008 : 13:32:36
|
ok -
so how
use the example and make it work in sql 2000
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 02/21/2008 : 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 - 02/22/2008 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/22/2008 : 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
USA
7423 Posts |
Posted - 02/22/2008 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/22/2008 : 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 - 02/22/2008 : 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
USA
7423 Posts |
Posted - 02/22/2008 : 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
|
Edited by - jsmith8858 on 02/22/2008 12:06:00 |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 02/22/2008 : 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
USA
7423 Posts |
Posted - 02/22/2008 : 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
|
Edited by - jsmith8858 on 02/22/2008 16:07:22 |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 02/25/2008 : 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
|
Edited by - jsmith8858 on 02/25/2008 10:12:00 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/25/2008 : 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 02/25/2008 : 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
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
Topic |
|
sp_executesql question
