September 29, 2009 at 4:02 pm
I'm sure all of that is possible.
Check out System.Data.SqlClient.SqlDataReader - have a look at the members FieldCount, GetDataTypeName and GetFieldType, GetName.
They're also pretty much wrapped up into the call GetSchemaTable - but I just don't happen to like DataTable objects - they eat too much memory for my liking.
Hope that helps. 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 4:03 pm
Here's a link to the member list
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader_members.aspx
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 11:31 pm
Matt Whitfield (9/29/2009)
I'm sure all of that is possible.
I'm pretty sure it's not.
SqlDataReader members give you metadata about data stream ALREADY RETURNED by the query/procedure.
They cannot give you any info about data which WILL BE RETURNED by a call to any SP.
As already been said - procedures may return different recordsets depending on different parameter settings.
And some procedures may depend on parameters stored in database - so you cannot even predict which parameter that procedure will take.
Generally speaking, procedures are not detrministic in terms of metadata, as well as actual data.
That's why SQL Server as well as any other service has no means to tell what procedures will return before the output is actually returned.
_____________
Code for TallyGenerator
September 30, 2009 at 1:40 am
Sergiy (9/29/2009)
That's why SQL Server as well as any other service has no means to tell what procedures will return before the output is actually returned.
Apart from SET FMTONLY ON, of course. It's not ideal though, I believe I stated that pretty much in my first post.
Sergiy (9/29/2009)
SqlDataReader members give you metadata about data stream ALREADY RETURNED by the query/procedure.
SqlDataReader gives you meta data whether there are rows or not. As I said before, it's not ideal, mostly for the reasons you mention. However, I personally would say that stored procedures which return different result sets based on parameters and/or data present aren't necessarily ideal either.
Also, given that the requirement for the CLR proc was to create a table into which to be able to do EXEC INTO, it's a reasonably safe bet that the stored procedure returns one result set, which is of a static nature in terms of columns and data types. Which fits exactly with using SET FMTONLY ON, executing the procedure, and inspecting the meta data that's returned as a result.
I should have mentioned the limitations previously, rather than just saying 'it's not ideal' - so thanks for clarifying it.
Edit -> having checked it out a bit further, SqlCommand makes it a bit easier by providing CommandBehaviour.SchemaOnly...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 30, 2009 at 8:48 am
Matt Whitfield (9/30/2009)
Sergiy (9/29/2009)
That's why SQL Server as well as any other service has no means to tell what procedures will return before the output is actually returned.Apart from SET FMTONLY ON, of course. It's not ideal though, I believe I stated that pretty much in my first post.
Including SET FMTONLY ON.
You need to actually run procedure and get a resultset (empty or not - does not matter, there must be a resultset returned) to let FMTONLY bring you any result.
To cut the emty talking I can offer you to test you theories on such procedures as:
sp_helpdb, sp_dboption, sp_helpuser, sp_helplogins, etc.
May be you'd consider these procedures not ideal, but they are system procedures, shipped with SQL Server and any tool which could serve your task would need to work with such procedures as well.
If you can define what should be a correct output for the query GetMetaData 'sp_helpuser' then we'd help you to build GetMetaData.
_____________
Code for TallyGenerator
September 30, 2009 at 8:57 am
Wow, you need to have some pork chops and chill a bit. Breathe deeply, it's only a forum. By the way, empty has a p in it 😀 Sorry couldn't resist!!
Like I said, it's not an ideal solution. (<- That's three times now). But, clearly it's an OK solution for the OP, seeing as it seems that is how the tool he has used works. Given that, it would certainly be possible to create a simple CLR proc which returned the DDL for a table which could hold the result of such a stored procedure.
As for the system stored procs - that's just another of the limitations of it. Big deal. There's plenty of stuff with limitations in SQL Server.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 30, 2009 at 4:25 pm
Matt Whitfield (9/30/2009)
Like I said, it's not an ideal solution. (<- That's three times now). But, clearly it's an OK solution for the OP, seeing as it seems that is how the tool he has used works.
You provided some kind of solution?
Did I miss something?
_____________
Code for TallyGenerator
September 30, 2009 at 4:28 pm
Sergiy (9/30/2009)
You provided some kind of solution?Did I miss something?
?? If you're asking did I give enough info to the OP to achieve what was asked for, then yes. If you're asking did I go ahead and write the code - then no I didn't. Edit -> If you're asking did I somehow get around the inherent limitations of SET FMTONLY ON, then the answer (polite version) is 'of course not'.
Depending on which of those three you're talking about:
The first - as I said previously - the OP seems ok working within the constraints of what SET FMTONLY ON provides.
The second - I wouldn't want to offer it in one of my products - because of the very reasons we've been talking about, therefore I don't really want to write the code.
The third - if that's what you meant, then you really haven't been reading what I'm writing, which makes the whole conversation rather pointless really.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 30, 2009 at 7:15 pm
Matt Whitfield (9/30/2009)
?? If you're asking did I give enough info to the OP to achieve what was asked for, then yes.
If we look up the first post on this thread we'll read this:
I want to write some ADO code automatically from the definition of my (many) stored procedures.
This depends on being able to query the metadata tables so that I can generate the column definitions returned from the stored procedure.
So, I wonder - which part of your info gives a hand in solving this problem?
Everything you provided works with recordsets ALREADY RETURNED by procedures HAVE BEEN RUN.
OP asked for gettiing metadata for a procedure BEFORE RUNNING IT, "from the definition of my (many) stored procedures" (see above).
If I missed the solution for this problem please point me what I overlooked.
P.S. And don't worry about the wording. Consider having this conversation on BBQ with plenty of beer on the table - no matter how wrong I think you are it cannot make you beer any worse. 🙂
_____________
Code for TallyGenerator
October 1, 2009 at 12:30 am
Matt,
Looks like this will work. I didn't know that SET FMTONLY stopped the actual execution, but I guess it makes sense.
I have the same problem as Seth though, I can't see at the moment how to process the output. Maybe if I use isql in batch and direct the output to a text file, that will do the job?
October 1, 2009 at 1:22 am
Richard
Have some code. The important bit is the CommandBehaviour.SchemaOnly parameter to the SqlCommand.ExecuteReader call.
Also note that SqlDataReader.GetSchemaTable() will return you a lot more information.
Thirdly - note that, as discussed - there are limitations. Just so you are aware! 😀
using (TextWriter tw = new StreamWriter("c:\\metadata.txt"))
{
using (SqlConnection sqlConn = new SqlConnection("-- connection string goes here"))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand("EXEC --insert name of proc here", sqlConn))
{
using (SqlDataReader sdr = sqlCmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
for (int i = 0; i < sdr.FieldCount; i++)
{
tw.WriteLine("Column " + i.ToString());
tw.WriteLine("Name: " + sdr.GetName(i));
tw.WriteLine("SQL Type: " + sdr.GetDataTypeName(i));
tw.WriteLine(".NET Type: " + sdr.GetFieldType(i).Name);
tw.WriteLine("-----------------------------");
}
}
}
}
}
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
October 1, 2009 at 7:34 am
Matt Whitfield (10/1/2009)
Have some code. The important bit is the CommandBehaviour.SchemaOnly parameter to the SqlCommand.ExecuteReader call.
So, to use FMTONLY, it'd be... this?
using (TextWriter tw = new StreamWriter("c:\\metadata.txt"))
{
using (SqlConnection sqlConn = new SqlConnection("-- connection string goes here"))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand("SET FMTONLY ON EXEC --insert name of proc here SET FMTONLY OFF", sqlConn))
{
using (SqlDataReader sdr = sqlCmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
for (int i = 0; i < sdr.FieldCount; i++)
{
tw.WriteLine("Column " + i.ToString());
tw.WriteLine("Name: " + sdr.GetName(i));
tw.WriteLine("SQL Type: " + sdr.GetDataTypeName(i));
tw.WriteLine(".NET Type: " + sdr.GetFieldType(i).Name);
tw.WriteLine("-----------------------------");
}
}
}
}
}
October 1, 2009 at 7:45 am
quote
So, to use FMTONLY, it'd be... this?
using (SqlCommand sqlCmd = new SqlCommand("SET FMTONLY ON EXEC --insert name of proc here SET FMTONLY OFF", sqlConn))
/quote
I image not. I have tried it and the CommandBehavior.SchemaOnly is enough to prevent it executing normally.
So that's really neat, thanks for the code Matt, I'm going to build it into a proper 'ADO generator' for C++, which hopefully will save me hours, even days, of coding 🙂
October 1, 2009 at 7:56 am
As Richard says (and I did in a previous post), CommandBehaviour.SchemaOnly does SET FMTONLY ON behind the scenes for you.
Glad it will save you some work - just remember - it won't work for all stored procs... I can't find much definition for the limitations of SET FMTONLY, so I'm afraid a lot of it will be trial and error. I think most of the pitfalls have been mentioned by Sergiy though...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
October 1, 2009 at 1:14 pm
Matt Whitfield (10/1/2009)
As Richard says (and I did in a previous post), CommandBehaviour.SchemaOnly does SET FMTONLY ON behind the scenes for you.
Nice, I wondered if it might do that. And yes, 3 seconds of googling would have told me that without asking the question :blush:.
The query returns column information only. When using SchemaOnly, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply