March 7, 2011 at 10:16 am
I need to provide some documentation for a .net web service I'm providing. These are largely made up of stored procedures pulling data from a sql views. I have alll of the Red Gate tools which are great, but the Sql doc tool does not seem to provide the underlying datatype for the fields. Can anyone provide an easy way to provide the field list with data types?
March 7, 2011 at 12:34 pm
What about your wsdl file? That has everything in there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2011 at 12:42 pm
I thought it might, but no. Not the fields in the dataset which come from the view inbeaded in a stored procedure.
March 7, 2011 at 12:50 pm
Since this is .net and you sending this data from views I will assume your are using a DataTable to hold the data? You could debug the code and look at the DataTable.
something like
string output = string.Empty;
foreach (DataColumn col in myDataTable.Columns)
{
output += col.ColumnName + "\t" + col.DataType.ToString() + "\r";
}
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2011 at 12:55 pm
Not a bad way to get it done. Thank You.
March 7, 2011 at 12:58 pm
Thanks. Admittedly not the way i would write it if it wasn't a one time thing. Appending strings and such. UGH!!! Hope this helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 10:25 am
The col.datatype gets me a string but no max length. Is it possible to get max length?
March 8, 2011 at 10:35 am
Of course there really isn't a max length on System.String in .net. Well there is but it is totally huge. You are now trying to get the field size of underlying tables from a view.
You could use
select sc.* from sysobjects so
join syscolumns sc on sc.id = so.id
where so.name = 'your view name here'
You will have to look at the xtype column to determine the datatype and the length column. Remember that if the column is nchar, nvarchar that the max length is half of the length because it takes 2 bytes to store each character. I found a decent listing of xtypes as related to columns here. Maybe this will get you in the right direction?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 10:38 am
This is better. It will directly get you the datatype name.
select t.name, sc.* from sysobjects so
join syscolumns sc on sc.id = so.id
join systypes t on t.xtype = sc.xtype
where so.name = 'your view here'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 10:42 am
That's what I was starting do. Sql is the best place for this, then I will get the field lenght from the database. I'm a sql guy trying to do everything in .net. There are too many ways to skin a cat. If I'm correct, I can just create a stored procedure to dump all of the specifics of the view out to a gridview for the customer.
Thanks again for your help.
Tom Brown
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply