How to change table names (Table, Table1, Table2, etc.) returned from SP

Hi all,
How do I customize table names returned from the stored procedure?
For example, I have a stored procedure that is something like this:
SELECT * FROM Employee
SELECT * FROM Employer
SELECT * FROM HealthInsurance
This SP returns multiple tables to the VB.NET application. Now, the names of
the tables retuned in the dataset are: Table, Table1, and Table2. Is there a
way to customize those 3 names so the Table is "Employee", Table1 is
"Employer", Table2 is "HealthInsurance"?
I would then get the Employee data by calling:
ds.Tables("Employee")
instead of:
ds.Tables("Table")
Thanks for your time
Goran Djuranovic
hi Goran,
Goran Djuranovic wrote:
> Hi all,
> How do I customize table names returned from the stored procedure?
> For example, I have a stored procedure that is something like this:
> SELECT * FROM Employee
> SELECT * FROM Employer
> SELECT * FROM HealthInsurance
> This SP returns multiple tables to the VB.NET application. Now, the
> names of the tables retuned in the dataset are: Table, Table1, and
> Table2. Is there a way to customize those 3 names so the Table is
> "Employee", Table1 is "Employer", Table2 is "HealthInsurance"?
> I would then get the Employee data by calling:
> ds.Tables("Employee")
> instead of:
> ds.Tables("Table")
you have to customize the in memory dataset provided by ADO.Net client side,
naming your datatable accordingly to your needs as "TableX" is the default
provided by ADO.Net when no personal provided value is available, so the
TableMapping method come to hand like
Dim da As New OleDbDataAdapter(strSQL, strConn)
da.TableMappings.Add("Tabl1e", "Customers")
Dim ds As New DataSet()
da.Fill(ds)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to change db schema name in DSV?

We have number of cubes which built under ‘abc’ database schema, but recently database schema has changed to ‘xyz’ (table names still are same), so every dimension tables, fact table need delete not change when refresh in DSV (BI Studio SQL server2005). If delete tables in DSV, means rebuild dimensions, and cube in the BI Studio. Is there a way to change schema name in DSV instead of rebuild cube from scratch?

Thanks in advance for advice.

Hello!

Open your DVS and select xml source and directly replace DbSchemaName="abc" with DbSchemaName="xyz".

Hth,

Radim
|||Great! It works.

Thank you so much Radim.
sql

how to change column names in the grid view programatically?

I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer. If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?

Here's my post to the SQL devs to give you an idea what I'm trying to do.

I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3...Week26. Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption".
fldfieldno Fldcaption
---- -----
11 9/07/2007
12 9/14/2007
13 9/21/2007

So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptions
where fldfieldno = 11

That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.

My current query looks like this:
SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,

So can I do something like the following?
SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...

Hi tomhirt,

So can I do something like the following? SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...

You cannot do that . Column alias can only be constant string.

Note what you need to modify is only gridview column header texts. So,you can create another query clause againstlkpdatecaptionsand assign the resultset to be your gridview column header texts.

Hope my suggestion helps

|||

ok, I think I understand the concept, but I could use some examples or pointers.

|||

It's very easy to use the approach i suggested above.

What you all need to do is to: build a connection to yourlkpdatecaptionsdatabase and fetch the data (use the smae parameters as you did when you get the first table). As to modify the datagridview column headers, see msdn documnet:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.datacontrolfield.aspx

|||

I know it was a long weekend and I'm brain dead this morning, but I don't see any type of example where a data connection is looped to assign values to column header text.

|||

sqlcommand cmd=new sqlcommand("Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where XXXXXX")// this will retrun a collection of weeks.using(sqlconnection con=new sqlconnection("your con-string")){ cmd.connection=con; con.Open(); sqldatareader rder; rder=cmd.ExecuteReader();int tmp=0;while(rder.Read()) { yourdatagrid.columns[tmp].HeaderText=rder[tmp].ToString(); tmp++; } }

Hope my suggsestion helps|||

Perfect!!! That's what I was missing. Thanks!!!!

How to bring out column names with bcp?

Hello, everyone:

I used bcp to generate a txt file as:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM Test..Orders" queryout "c:/test.txt " -U tester -P tester -c'

It works fine except without column names of table. Does any one have idea that can bring out column names from table?

Thanks

ZYTColumn names is not one of the arguments of bcp.

You could do a "select * from <table> where 0=1" and save the output to a txt file, or script out the table schema and carry that across to the new location.|||Or you could use a union query with the column headings as literal strings in the row.|||Or you could use a union query with the column headings as literal strings in the row.I was going to suggest that. The main problem is that you then need to convert all data to character... which then means you need to be a little clever if you need to order your export for whatever reason.

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

I thought Mladen on SQLTeam had blogged something more sophisticated but I can't find it.|||I was going to suggest that. The main problem is that you then need to convert all data to character... which then means you need to be a little clever if you need to order your export for whatever reason.

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

I thought Mladen on SQLTeam had blogged something more sophisticated but I can't find it.

I am honored|||Lol - no offense. I mean I think he had some complicated automation code - I've never tried it. My own method, as it happens, is virtually identical to yours.|||Never [played with it, but my guess is you could use com automation..good luck|||This the way I export my "headers" in specific situation but you can modify it so it works for you as well.

DECLARE @.tmp varchar(1000), @.fld_list varchar(8000), @.loop int, @.i int, @.select varchar(2000), @.val_list varchar(8000)

............... YOUR CODE HERE ................

SET @.i=(SELECT COUNT(*)
FROM information_schema.columns
WHERE TABLE_NAME='ut_RptQryProdTemp')

SET @.loop = 1
SET @.fld_list = ''
SET @.val_list = ''
WHILE (@.loop <= @.i)
BEGIN
SELECT @.select = 'SELECT ''['' + COLUMN_NAME + ' + '''],''' + ' AS F
INTO ##fld_list
FROM information_schema.columns
WHERE TABLE_NAME=''ut_RptQryProdTemp'' AND ORDINAL_POSITION=' + convert(varchar(3),@.loop)
EXEC (@.select)
SELECT @.tmp = (SELECT REPLACE(F, ',', '') + ' AS ' + REPLACE(F, 'PRODOUT],', '],') + '' FROM ##fld_list)
SELECT @.val_list = @.val_list + @.tmp
SELECT @.fld_list = @.fld_list + REPLACE(STUFF(@.tmp, 1, 0, 'CAST('), ' AS [', ' AS varchar) AS [')
DROP TABLE ##fld_list
SET @.loop = @.loop + 1
END
SET @.fld_list = LEFT(@.fld_list, LEN(@.fld_list) - 1)
SET @.val_list = LEFT(@.val_list, LEN(@.val_list) - 1)
SET @.val_list = REPLACE(@.val_list, ',[', ',''')
SET @.val_list = REPLACE(@.val_list, '] AS', ''' AS')
SET @.val_list = REPLACE(@.val_list, 'PRODOUT', '')
SET @.val_list = STUFF(@.val_list, 1, 1, '''')

EXEC ('SELECT ' + @.val_list + ' UNION ALL SELECT ' + @.fld_list + ' FROM ut_RptQryProdTemp ORDER BY _DEP, _FAM, _STYLE, _COLOR, [_SIZE]')

As I said this is for a very specific situation, but if you're good with sql syntax you should be able to modify it.

good luck!|||Never [played with it, but my guess is you could use com automation..good luck

Any docs on COM AUTOMATION please ?|||Hello, everyone:

Thanks for all replies.

UNION cannot be used in bcp statement as SELECT part. I tried and got error. Pootle got a great idea. I did it and worked. Creat a view with all varchar column. Insert column names as first row. Then insert data from table. bcp to txt from view. This query will run automatically. The txt file name is changed by date. No problem on file name in bcp.

ZYT