Is it possible to modify column data type of view

The SQL Server Management Studio shows the data type of each column of views. I am wondering how SQL server determines the types since my SQL code of views does not specifiy data types for any columns.

I am much more interested in knowing whether the data types can be modified. Could anyone offer some hint?

Thanks,

hz

SQL Server uss the underlying schema information to do so unless you don′t specify a different data type than the source data type (liek within CONVERT). You can change the resulting data type in the view e.g. via CONNVERT(VARCHAR(10),GETDATE(),112), which was a datetime before and a varchar afterwards.

HTH, Jens Suessmeyer.|||

Jens, thanks a lot! That is exactly what I was looking for.

hz

Is it possible to change the data type during an sql update statement?

Hello,

I have a SQL update statement that updates some user names, however, the user names exceed the length of the data type. Currently, for the column username the data type is set to nvarchar (8).

How can I change that to nvarchar(10) in a SQL Update statement?

Thanks in advance.

You can't put in more data than there is space to store it. If the column holds 8 characters, then you cannot put 10 characters in that same space.

You would need to change the table definition to increase the space allocated for the UserNames.

|||

Thanks for the reply...

I figured that would be the case, but what I need to do is change the data type to 10 before I can update the user names. I also tried using Alter table:

ALTER TABLE table_name
MODIFY column_name column_type;

For some reason, SQL Express did not like the MODIFY function...

Thanks in advance...

|||

Refer to Books Online, Topic: "Alter Table"

Modify is not a T-SQL keyword. You need to use ALTER.

Code Snippet

ALTER TABLE MyTable
ALTER COLUMN MyColumn varchar(10)

You might wish to consider bumping it up a bit more -just in case you later find more is required.

Here is Books Online for SQL Server Express:

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F

|||

That worked!

I greatly appreciate your response, and for providing me with the SQL links.

Thanks Arnie!

|||It does not work if MyColumn has a relationship with other table. In that case how can I do that? thanks|||

First, remove (or DROP) the PK-FK relationship,

ALTER the column,

then re-establish the relationship.

Is it possible to capture an OUT type parameter from a PL/SQL stored procedure?

When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter namedNewId to my ASP.NET server. I'd like to capture this in the VB.NET code.

1createorreplace procedure WriteName(FirstNamein varchar2, LastNamein varchar2,NewIdout pls_integer)is
2
3NameId pls_integer;
4
5begin
6
7 select name_seq.nextvalinto NameIdfrom dual;
8
9insert into all_names(id, first_name, last_name)
10values(NameId, FirstName, LastName);
11
12NewId := NameId;
13
14end WriteName;

1<asp:SqlDataSource
2 ID="SqlDataSaveName"
3 runat="server"
4 ConnectionString="<%$ ConnectionStrings:ConnectionString%>"
5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName%>"
6 SelectCommand="WRITENAME"
7 SelectCommandType="StoredProcedure">
8 <SelectParameters>
9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />
10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />
11 </SelectParameters>
12</asp:SqlDataSource>

This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameterNewId returned from Oracle. 

1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty)

If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.

This Select looks an awfully lot like an Insert, doesn't it? :)

My most honest suggestion is that you skip the SqlDataSource altogether for this kind of task. The SqlDataSource has some merit when it comes to binding databound controls to a datasource, but in this case it's a just simple insert from two textboxes.

In other words, use a simple OracleCommand, with a defined output parameter.

If you really want to use the SqlDataSource, then you should first use its Insert method, and corresponding InsertCommand etc. Next, define

<asp:Parameter Name="NewId" Direction="ReturnValue" />

and subscribe to the Inserted event of the SqlDataSource, where you'll be able to retrieve the value from the SqlDataSourceStatusEventArgs.

|||

Hi, thanks for replying. Yes, the example is overly simplified and an INSERT would have been better - I just used it to highlight what I was trying to achieve and that was to accept a returned value from the PL/SQL proc back in ASP.NET. However I think that you have answered my questions to thank you for showing me the Direction="ReturnValue" which is the bit I was missing.

Is it possible that I change the data's type when I create the DSV in the Visual Studio 2005?

Hello everyone:

Recently, I was responsible for creating a analysis services project with the SQL Server Business Intelligence Development Studio in Visual studio 2005. I encountered a error that the data type is not supported when I tried to add the table from a remote Oracle server. I can not modify the data type in the oracle server. Is it possible that I change the data's type when I create the DSV in the Visual Studio 2005? (if I have known the table's name)

Help me Please! tell me the steps or some references if it is possible

Thanks

---

While I don't think you can change the data type of the column within the DSV, what you can do is add a named calculation to the table within the DSV and use a snippet of Oracle to cast the underlying column to whatever data type you want. The new named calculation would then inherit that data type.

For example (using SQL Server here, not Oracle), if I had a table with a column named ZIPCODE of type INT but I wanted to treat the column as VARCHAR within my SSAS solution, I could add a new named calculation to the table within the DSV (right-click on the table in the DSV and select New Named Calculation), call it something like ZIPCODE_STR and then use the T-SQL snippet CONVERT(VARCHAR(10), ZIPCODE). This new column then behaves like any other within the DSV and within the rest of my solution -- and has a datatype of System.String with a length of 10.

HTH,

Dave Fackler

|||

Thank you very much

Now I did not install SQL Server 2005 and VS on current computer, I will try it later. I think you suggest is very well. But I have a question: you said I could add a new name calculation to the table within the DSV. Does it equal that the table (at least one column's datatype doesn't not match the SSAS's rule) have been added into the DSV?

The problem I encounter is that I can not add that table to the DSV because of the unsuited the datatype for the column. Is there any other suggestion to me?

Thanks^_^

|||

Sorry, I missed the point that you can't add the table to the DSV because of the data type...

In that case, instead of adding a named calculation to the table (as you are correct in thinking that the table must be present within the DSV to do this), add a named query instead. Just right-click on the design surface of the DSV and select New Named Query.

This will bring up a dialog box that you can use to enter any PL/SQL select statement (given you are using Oracle) returning whatever columns (converted to whatever datatypes you need) from one or more tables. Very similar to creating a view in the Oracle database. Just give the named query a name and it will then appear as a table within the DSV. So, in your case, you'd likely want to select all the relevant columns that you need, converting the one with the unsupported datatype to another datatype (just be sure to alias the column once you convert it as a named query must return named columns so that the DSV can understand the metadata returned by the query).

HTH,

Dave Fackler

|||

I think this response is I need. Thank~ you very much.

I will try it later.

Thanks^_^

Winnie

Is it possible that I change the data's type when I create the DSV in the Visual Studio 200

Hello everyone:

Recently, I was responsible for creating a analysis services project with the SQL Server Business Intelligence Development Studio in Visual studio 2005. I encountered a error that the data type is not supported when I tried to add the table from a remote Oracle server. I can not modify the data type in the oracle server. Is it possible that I change the data's type when I create the DSV in the Visual Studio 2005? (if I have known the table's name)

Help me Please! tell me the steps or some references if it is possible

Thanks

---

While I don't think you can change the data type of the column within the DSV, what you can do is add a named calculation to the table within the DSV and use a snippet of Oracle to cast the underlying column to whatever data type you want. The new named calculation would then inherit that data type.

For example (using SQL Server here, not Oracle), if I had a table with a column named ZIPCODE of type INT but I wanted to treat the column as VARCHAR within my SSAS solution, I could add a new named calculation to the table within the DSV (right-click on the table in the DSV and select New Named Calculation), call it something like ZIPCODE_STR and then use the T-SQL snippet CONVERT(VARCHAR(10), ZIPCODE). This new column then behaves like any other within the DSV and within the rest of my solution -- and has a datatype of System.String with a length of 10.

HTH,

Dave Fackler

|||

Thank you very much

Now I did not install SQL Server 2005 and VS on current computer, I will try it later. I think you suggest is very well. But I have a question: you said I could add a new name calculation to the table within the DSV. Does it equal that the table (at least one column's datatype doesn't not match the SSAS's rule) have been added into the DSV?

The problem I encounter is that I can not add that table to the DSV because of the unsuited the datatype for the column. Is there any other suggestion to me?

Thanks^_^

|||

Sorry, I missed the point that you can't add the table to the DSV because of the data type...

In that case, instead of adding a named calculation to the table (as you are correct in thinking that the table must be present within the DSV to do this), add a named query instead. Just right-click on the design surface of the DSV and select New Named Query.

This will bring up a dialog box that you can use to enter any PL/SQL select statement (given you are using Oracle) returning whatever columns (converted to whatever datatypes you need) from one or more tables. Very similar to creating a view in the Oracle database. Just give the named query a name and it will then appear as a table within the DSV. So, in your case, you'd likely want to select all the relevant columns that you need, converting the one with the unsupported datatype to another datatype (just be sure to alias the column once you convert it as a named query must return named columns so that the DSV can understand the metadata returned by the query).

HTH,

Dave Fackler

|||

I think this response is I need. Thank~ you very much.

I will try it later.

Thanks^_^

Winnie

Is it possible pass to procedure open cursor from other procedure

No. A procedure can not have a input parameter of type cursor. You can call
a
second procedure that has an output parameter of type cursor.
Example:
use northwind
go
create procedure proc1
@.c cursor varying output
as
set nocount on
set @.c = cursor local fast_forward for select orderid from dbo.orders
open @.c
go
create procedure proc2
as
set nocount on
declare @.c cursor
declare @.i int
exec proc1 @.c output
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c into @.i
if @.@.error != 0 or @.@.fetch_status != 0 break
print @.i
end
close @.c
deallocate @.c
end
go
exec proc2
go
drop procedure proc2, proc1
go
Try to find a set-based solution first, leave cursors as the last tool in
your pocket.
AMB
"JB via webservertalk.com" wrote:

> Is it possible pass to procedure open cursor from other procedure ?
> Any suggestions will be appreciated
> --
> Message posted via http://www.webservertalk.com
>thank you.
What divantages of using cursor rather then temp table?
Message posted via http://www.webservertalk.com