Creating an asynchrous output

Are there any good examples creating an asychronous output. I need to be able to output the values from the input as well as adding 7 new columns to each row.

http://msdn2.microsoft.com/en-us/library/ms136133.aspx

The answers are available, you just need to search for them. I found the link above by typing "asynchronous component code ssis" into Google.

-Jamie

|||

Also, be aware that just because you need to add columns to a row does not mean that you need to use an async output. You need to use an async output only if you need to hold onto data longer that the ProcessInput call or if you need to generate more rows going out than coming in. Generally, if one of those 2 conditions don't apply then you can use a sync output, which I would highly recommend because it is significantly simpler.

HTH,

Matt

|||

Matt David wrote:

Also, be aware that just because you need to add columns to a row does not mean that you need to use an async output. You need to use an async output only if you need to hold onto data longer that the ProcessInput call or if you need to generate more rows going out than coming in. Generally, if one of those 2 conditions don't apply then you can use a sync output, which I would highly recommend because it is significantly simpler.

HTH,

Matt

And it executes quicker!

creating a view with columns not in another table

Hello,

I'm somewhat of a newbie and I need to create a view with a column
that is not derived from any other tables. What I want is for this
field to be an auto-increment field or some kind of UID. Can anyone
quickly tell me how to do this.

Here is my dilemma. I need to pull data from a unique data set from a
table which does not have a primary key, and none exists in its data.
Please tell me how to put this data in a view(or another table) so
that I can pull it out with unique records. It doesnt matter what the
unique field consists of.

ThanksAdd newid() to your select statement.

Select newid(),

This will create a UID for each line returned by the select statement.

margraft@.yahoo.com wrote in message news:<1db47099.0411231136.50d722c7@.posting.google.com>...
> Hello,
> I'm somewhat of a newbie and I need to create a view with a column
> that is not derived from any other tables. What I want is for this
> field to be an auto-increment field or some kind of UID. Can anyone
> quickly tell me how to do this.
> Here is my dilemma. I need to pull data from a unique data set from a
> table which does not have a primary key, and none exists in its data.
> Please tell me how to put this data in a view(or another table) so
> that I can pull it out with unique records. It doesnt matter what the
> unique field consists of.
> Thanks|||> Select newid(),
> This will create a UID for each line returned by the select statement.

If you do twice the request, the UID wont be the same... So it removes a lot
of the interest of the UID. The best thing to do would ne to add a primary
key to the table...

--
Vincent|||(margraft@.yahoo.com) writes:
> I'm somewhat of a newbie and I need to create a view with a column
> that is not derived from any other tables. What I want is for this
> field to be an auto-increment field or some kind of UID. Can anyone
> quickly tell me how to do this.
> Here is my dilemma. I need to pull data from a unique data set from a
> table which does not have a primary key, and none exists in its data.
> Please tell me how to put this data in a view(or another table) so
> that I can pull it out with unique records. It doesnt matter what the
> unique field consists of.

Using newid() as Mike suggested will give you a 16-byte binary value,
which may be somewhat difficult to manage. But it's certainly the
easiest method.

If you want an integer number, the best is probably to write a multi-step
table function where you select the data into a table with an IDENTITY
column, and then return that table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a View help

i have a table which has 2 columns 1 'report' 2 'part'

now in my 'report' cloumn i have # with 6 digits ex. '111111' and 'part' has '1, 2, 3, 4,..to 50'

i want to create a view that will put them together in format like this:

1111110001

1111110002

1111110003 .. and on

it needs to be in 10 digits.

is there anyway i can create a View or may be a column in the table which can create the #'s in this format.

Try this:

SELECTRIGHT('00000'+Convert(nvarchar(6),[report]),6)+RIGHT('00000'+CAST([part]asNvarchar(6)), 4)AS TenDigitsString

FROM CAG

|||

Thanx limno ur example worked out.

creating a table with select into

Hi,
how can i create a new table from a select statement? i use select into, but
my select query has case columns and query analyzer says that i did not
specified a column for them. how do i do it?
thanks in advance for your helpGive the column a name. Make this:
SELECT somecolum, CASE when x then y else z END
INTO #table
FROM table
look like this:
SELECT somecolum, CASE when x then y else z END AS SomeNewColumn
INTO #table
FROM table
or this:
SELECT somecolum, 'SomeNewColumn' = CASE when x then y else z END
INTO #table
FROM table
Keith
"Jose" <norespondais@.almailpor.favor> wrote in message
news:uNmJgiTDFHA.1396@.tk2msftngp13.phx.gbl...
> Hi,
> how can i create a new table from a select statement? i use select into,
but
> my select query has case columns and query analyzer says that i did not
> specified a column for them. how do i do it?
> thanks in advance for your help
>sql

creating a table column that only takes data from another table.

I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?

You could create a trigger on the table or a rule?|||The term which applies to your situation is called "referential integrity". To enforce referential integrity in your situation, you could use aFOREIGN KEY constraint in your table which limits the possibilities in the State column to only those values in the 'states' table.sql

creating a table column that contains long string

I have made a database inside a C# project using project --> Add New Item --> SQL Database.

I have made its tables and define the columns, but I need a data type that allows inserting a long string , as a news paper .

When I use the 'text' data type , the inserted document can't entered as a whole, only subdocument can be entered.

I need also to know how can I create a table that contains Objects .

of any created class.

Thanks,

Aya.

The text type can hold up to 2GB of data. Should be enough no?

Could you show us your insert procedure?

|||

Ok thanks.

I just detect an error in my inserting process.

But I need to know whether I can create a table that contains Objects or not.

And also if I can create a table that contains lists or not.

Thanks,

Aya.

|||

Hi,

You should be aware that the 'text' datatype is being deprecated, please look at varchar(max)/nvarchar(max) as a replacement, which should give you a better user experience in general.

Could you define what do you mean by Objects and Lists? Maybe there are other ways to achieve what you are trying to do. Please elaborate.

Thanks!

-Mat

Creating a table (like a pivottable in Excel)...how?

I have a table in my DB with the following columns: PlayerID, RoundNum, Score.

PlayerID RoundNum Score
1 1 10
1 2 10
1 3 10

Any scoring for my game is going to end up in this table. However, I would like to display the score standings with a player's name at the far left, and with each Round as a column:

R1 R2 R3
Player1 10 10 10

Can any of the SQL gurus tell me if this is possible, and how it can be done? Thanks!

http://forums.asp.net/1194798/ShowPost.aspx|||That is exactly what I was looking for. Thanks for a) giving the inital explanation and b) pointing me to it.

creating a SP that add columns into a table

Hi all,

i want to create a stored procedure that will add columns into a table but i have a problem using a parameter in this context, for example:

- SP --

-- PARAMS --

declare @.TableName nvarchar(30)

declare @.colName nvarchar(30)

declare @.colType nvarChar(30)

-- Actual Code

alter table @.TableName

add @.colName @.colType

it looks as though the paramater cannot be used in this context or i should do something in order to make sense of this code.. please help me :)

Z

You have to use Dynamic SQL here..

- SP --

-- PARAMS --

declare @.TableName nvarchar(30)

declare @.colName nvarchar(30)

declare @.colType nvarChar(30)

-- Actual Code

Declare @.SQL as Varchar(1000);

Select @.SQL = 'alter table ' + @.TableName + ' add ' + @.colName + ' ' + @.colType

Exec (@.SQL)

|||

Hi Zacky,

Here is a possible solution:

create procedure AddColumnToTable
(
@.tableName varchar(50),
@.columnName varchar(50),
@.dataType varchar(50)
)
as
begin
declare @.sql varchar(4000)
set @.sql = 'ALTER TABLE ' + @.tableName + ' ADD ' + @.columnName + ' ' + @.dataType
exec sp_sqlexec @.sql
end

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Sorry MandiD, I didn't see that you posted an answer while I was creating mine.

|||

What kind of error you are getting here..

R u missed any code ..reposting the code again

Begin
declare @.TableName nvarchar(30)
declare @.colName nvarchar(30)
declare @.colType nvarChar(30)
declare @.SQL as NVarchar(1000);
-- Actual Code
--Select @.TableName='Sample', @.colName='newCol1', @.colType='Int'

Select @.SQL = N'alter table ' + @.TableName + ' add ' + @.colName + ' ' + @.colType
Exec (@.SQL)
--or use
--Exec sp_executesql @.SQL
End

|||

thanks looks great!!

Z

Creating a generic package to import a variable number of columns

Hi,

We are building an application with

a database that contains Jobs. These Jobs have properties like Name, Code etc.

and some custom properties, definable by the application admin. For bulk import

of Jobs, we want to allow the import of an Excel sheet with the columns Name,

Code and a variable amount of columns. If the header names of these columns in

the Excel sheet match the name of a custom property in the system we want to add

the value of that cell into the database as property

value.

In our Data Flow of our Import

Package in SSIS we added an Excel Source that points to a test excel sheet with

the Name and Code columns and – for this example - 3 custom property columns

(Area, Department, Job Family). When we configure the Excel Source in the Excel

Source Editor, we have the option to select the Columns from the Available

External Columns table. But here lays the problem, we do not know at design

time, what custom property columns to expect. We DO expect the Name and Code

columns, but the rest is uncertain at design-time.

That raises the question: Is there

some way to select all of any incoming columns (something like a SELECT * in

T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is

being generated at design-time would need to be updated at run-time to reflect

the variability of the columns that might be encountered while reading the excel

sheet.

Then, we thought, we could add a Script

Component to our data flow that passes some kind of DataSet (or DataReader) in

which we can walk through the columns ourselves? But then still, we miss the

option to include ANY of the columns while reading an Excel sheet (or any other

datasource by the looks of it)

We are aware of the option of

optional columns in combination with the RaggedRight option, but it seems that

we would have to put all of the columns of a row in just one column and then

extract all the columns later with Derived Columns. But then, since the source

import file is being prepared by an application admin, we want don’t want to

burden him with this horrendous task of putting everything in one

column.

We would like to have some way of

iterating through all the columns, either in a Script Component or maybe with a

Pivot/Unpivot mechanism.

Does anyone have any suggestions? Are there other options we should have considered?

The metadata of the pipeline is fixed at design-time. You cannot change the columns at runtime.

-Jamie

|||

Since you're importing from Excel, you may be able to define a dataflow that reads the maximum amount of columns you anticipate ever having in one Excel file. The Excel files with less columns would return empty strings for the non-existent columns.

Haven't tried this, but it might work.

K

Creating a Dimension Table from a 3-key table

Hi All,

I have a situation with a table that was created for a transactional

system with a 3 columns key. The table is similar to the following:

country state city description
1 12 21 City A from country 1 and state 12
1 13 21 City A from country 1 and state 13
2 14 22 City B from country 2 and state 14
2 15 22 City B from country 2 and state 15

Now I'm trying to create a dts package that would allow me to build a

city dimension table with unique codes (keys) for each city. What kind of

transformation should I use to translate the old codes (based on the

country-state-city key) into the new ones and preserving the data

integrity?

Thanks,

Ignaciodoesn't that defeat the purpose of building a cube?

What's it going to be for?

How are you going to go after the data?|||This cube is going to show sales history since 2002. The table where data is being pulled could contain data as the following:

cust_id year country state city amount_cash amount_credit
525 2002 1 12 21 8500 3200
714 2002 1 13 21 3250 775

Let's say I create a fact table with fk and measures only. If I would like to know city totals, it looks like cities sharing the same id will be aggregated, when in fact they shouldn't. And in the other hand, cities that are geographically shared by different states will add up correctly. Maybe I need to further analyze this, but what first puzzled me was the city table with no unique id.

Thanks for your thoughts!

Ignacio

Creating a cube...and some ?s

1. Create a table. The table must contain four columns of your choice and at least ten rows. Create a meaningful example of your own. The last column in the table must be a quantity. Provide output showing your CREATE TABLE and INSERT statements. Also include output showing their successful execution.

2. Create a ROLL-UP query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows. Next use the TRANSCT SQL help function of SQL Server and write definitions of the following SQL statements:
a. IS NULL-
b. GROUPING-
c. AS-

3. Create a CUBE query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.

4. Create the following CUBE queries using the table you created in problem #1. Provide output showing your SELECT statements and the resulting output rows.
a. A CUBE query with Grouping used to distinguish Null values.
b. A CUBE query showing a multidimensional cube.
c. A CUBE query created using a view. For this problem you must not only create your view but query it and display the results.

5. Create one example of a query using COMPUTE and one example of a query using COMPUTE BY. These queries should use the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.I have no clue how to do this|||Sounds Like Homework to me .

Better gear unp for some lashing from sundialsvcs (http://www.dbforums.com/showthread.php?threadid=979777)

Creating a column.

Is there a way for me to create a column in SQL?

What Im looking to do is create a view with columns from a specific table. In addition to this I would like to append a column to the view which would contain data based on the data from a pre-existing column.

Table A
Column 1
Column 2
Column 3

View A
Column 1
Column 2
Column 3
Column 4

Now here is the Row structure

Column 1 Column 2 Column 3 Column 4
Test Test A ON
Test Test B OFF

So if Column 3 contains "A" as its field data than Column 4 will contain "ON" and if Column 3 contains "B" than Column 4 will contain "OFF"

Is this possible?

TIA,

Stue.

SELECT Col1,Col2,Col3, Case Col3 When 'A' Then 'ON' Else 'OFF' End From tbl_YourTable

|||

This is definitely possible, use something like the select statement below to create the view:

SELECT Column1, Column2, Column3, Column4 =CASE Column3WHEN'A'THEN'ON'ELSE'OFF'ENDFROM TableA ...

I haven't worked with SQL Server Express so I don't know if CASE is supported. You could alternatively use temp tables etc, but the above statement is probably the least TSQL-intensive. 

|||

Thanks guys!

Just what I needed!

Stue

|||

Chris Pebble:

I haven't worked with SQL Server Express so I don't know if CASE is supported. You could alternatively use temp tables etc, but the above statement is probably the least TSQL-intensive. 

Chris,

As a FYI to you and others that may have the same question... CASE is supported. I am using this code on a Express server.

Thanks again,

Stue

Creating A Boolean Computed Column

Consider the following columns:

EmpID, Status, TermDate

I want a computed column called OkToDisable as a bit value: True if the TermDate is any day before today.

The logic is if DATEDIFF(day, TermDate, GETDATE()) is greater than 0 then the column should be true, otherwise false.

I cant figure out how to do this... SQL keeps complaining that it can't validate the formula.

I was able to create a computed colum from 'DATEDIFF(day, TermDate, GETDATE())' that shows the number of days past the term date, but if I change it to: 'DATEDIFF(day, TermDate, GETDATE()) > 0' it says it can't validate the formula.

Can't you create boolean computed fields?

Thanks.

J

There is no boolean type in T-SQL. The [bit] type is an number type. You can do what you want with CASE:

CASE WHEN DATEDIFF(day, TermDate, GETDATE()) > 0 THEN 1 ELSE 0 END

Steve Kass
Drew University
http://www.stevekass.com
|||

Bit is a boolean type, no? )

I had tried the CASE but I couldn't get it to work properly... It helps when you use the proper syntax. (I had left off the END statement)

Then I had to use a cast, otherwise it was an integer.

Thanks for the reply.

J

Creating "columns" from transaction data

Hi,
I have a transaction table that basically has the following fields
RecId, PeriodId, Quantity (a single RecId can have multiple records, i.e.
quantities in multiple periods)
I need to convert an entire table of these records to one that looks like
this...
RedId , P1Qty, P2Qty, P3Qty, P4Qty etc...
Which has one row per RecId and places the quantity (quantities) in the
appropriate "period" column(s) based on the value(s) of "Period" in
the transaction file for each record.
I've done this before in Access, using the IIF function for each of P1...P4
columns (IIF(Period = 1, Quantity, 0), IIF(Period = 2, Quantity, 0) for
each of the columns of the derived table I was making. This doesn't seem to
work for SQL Server. IIF exists, but I can't get the computed columns to
work properly (Syntax error near "=").
So, I've thought about...
1) Use CreateTable to create my derived table with periods as columns,
2) Write a series of INSERT queries that reads the transaction file for
each possible individual value for "Period" and populates the appropriate
column in the derived table
3) Sum the derived table on every column by RecId
4) Run the whole batch as the SelectCommand of my DataAdapter. The last
command in the batch is Select * from DerivedTable and this is the table
that the DataSet gets.
There has to be a better way to do this?
Thanks.
BBMYou can use CASE expressions instead of IIF. But why would you ever create a
table like this? What you are asking for is a report not a table. Any
reporting tool will construct a cross tab report for you.
David Portas
SQL Server MVP
--|||Do you have any idea what First Normal Form is? You might want to
learn about RDBMS before you write any code.|||Thanks David, CASE was just what I'm looking for.
In this instance, this result set is used as one of the tables in
multi-table DataSet used on a fairly complex display.
Thanks again.
BBM
"David Portas" wrote:

> You can use CASE expressions instead of IIF. But why would you ever create
a
> table like this? What you are asking for is a report not a table. Any
> reporting tool will construct a cross tab report for you.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yes, in fact I do. I simplified the underlying data structure in my questio
n
to hopefully make it easier to reply to. I was only using the "extra" table
,
because I couldn't figure out how to get the result set I wanted in one pass
.
Thanks for your response anyway.
"--CELKO--" wrote:

> Do you have any idea what First Normal Form is? You might want to
> learn about RDBMS before you write any code.
>

Creatiing a link to download an attachment

I have a database where one of the fields is a large binary object. I would
like to create a report where one of the columns shows a link to the
"attachment". When the user clicks on the link, they should be able to
download the raw binary data (with the mime type of my choosing). Can this
be done?
Thanks,
SteveYou would need to put a URL into the table cell as a hyperlink and then on
your web server, process that URL by obtaining the attachment from the
database and returning it. There is no native support for this in Reporting
Services.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Stephen Walch" <swalch@.proposion.com> wrote in message
news:%23WNOOaQkEHA.644@.tk2msftngp13.phx.gbl...
> I have a database where one of the fields is a large binary object. I
would
> like to create a report where one of the columns shows a link to the
> "attachment". When the user clicks on the link, they should be able to
> download the raw binary data (with the mime type of my choosing). Can
this
> be done?
> Thanks,
> Steve
>
>

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.

CREATE VIEW - script to automate column names?

Hi,

I'm trying to create views on all my existing tables and for that I'd
like to create a script or so.
I don't want to specify the '*' for the columns in the create view
statement. I prefer to specify the column names.
I have the column names int sys.columns table but Do not know how to
handle them to have a statement like that:

CREATE VIEW myVIEW
WITH SCHEMABINDING
AS
SELECT col1name, col2name, col3name, etc...
from sys.columns
...???....

Anyone can help?

thx,

ChrisOn 12 Mar 2007 04:44:56 -0700, "clir" <christophe.leroquais@.gmail.com>
wrote:

Use a cursor to loop over the column names, all the while
concatenating a string variable. In the end, execute that string
(sp_executesql) and your view will be created.

-Tom.

Quote:

Originally Posted by

>Hi,
>
>I'm trying to create views on all my existing tables and for that I'd
>like to create a script or so.
>I don't want to specify the '*' for the columns in the create view
>statement. I prefer to specify the column names.
>I have the column names int sys.columns table but Do not know how to
>handle them to have a statement like that:
>
>CREATE VIEW myVIEW
>WITH SCHEMABINDING
>AS
SELECT col1name, col2name, col3name, etc...
from sys.columns
...???....
>
>
>Anyone can help?
>
>thx,
>
>Chris