By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Create View with ADO.NET

P: n/a
Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name 'FOO' in view
'BAR' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain't catchin' it!!!

Any ideas?
Nov 20 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Zack,
What does your code look like, specifically the CREATE VIEW statement
itself?

Can you print the CREATE VIEW statement, just before you execute the
ExecuteNonQuery method?

Hope this helps
Jay

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db*************************@posting.google.co m...
Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name 'FOO' in view
'BAR' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain't catchin' it!!!

Any ideas?

Nov 20 '05 #2

P: n/a
The error is in your CREATE VIEW command, not in ADO.NET. Try run the CREATE VIEW command in Query Analyzer

"Zack Sessions" wrote:
Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name 'FOO' in view
'BAR' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain't catchin' it!!!

Any ideas?

Nov 20 '05 #3

P: n/a
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<#5**************@TK2MSFTNGP11.phx.gbl>...
Zack,
What does your code look like, specifically the CREATE VIEW statement
itself?

Can you print the CREATE VIEW statement, just before you execute the
ExecuteNonQuery method?
WHile attempting to debug this issue, I added a Debug.WriteLine to
dump the SQL command to create the view which I used to cut and paste
into a Query Analyzer window. A sample create view command would look
like:

CREATE VIEW viewname AS
SELECT col1,
col2,
col3 as NewName,
col4,
col5 as NewName,
col6
FROM tablea

Note the error is that col3 and col5 both have the same alias name.
When you run this command in the Query Analyzer window, you get the
error you expect, that there are two fields with the same name. When I
try to run the command from VB.NET using ADO.NET, no error is
returned, ie, the Catch in a Try/Catch is not raised.










"Zack Sessions" <zc********@visionair.com> wrote in message
news:db*************************@posting.google.co m...
Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name 'FOO' in view
'BAR' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain't catchin' it!!!

Any ideas?

Nov 20 '05 #4

P: n/a
"Rulin Hong" <Ru*******@discussions.microsoft.com> wrote in message news:<E2**********************************@microso ft.com>...
The error is in your CREATE VIEW command, not in ADO.NET. Try run the CREATE VIEW command in Query Analyzer
As I mentioned in my original post, I have tried to run the create
view command that is being generated in a Query Analyzer window. That
is how I determined what particular error was being raised. My problem
is when I try to create the same view in VB.NET with ADO.NET, the
Catch in a Try/Catch is not being raised when it should be.

"Zack Sessions" wrote:
Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name 'FOO' in view
'BAR' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain't catchin' it!!!

Any ideas?

Nov 20 '05 #5

P: n/a
Zack,
Because of the severity level of the you are getting back an InfoMessage is
used instead of an Exception.

You need to handle the SqlConnection.InfoMessage event, and act accordingly.

Private Sub OnInfoMessage(ByVal sender As Object, ByVal e As
SqlInfoMessageEventArgs)
Debug.WriteLine(e.ToString)
' consider throwing an exception here.
End Sub

Dim connection As New SqlConnection(connectionString)

AddHandler connection.InfoMessage, AddressOf OnInfoMessage

Const cmdText As String = "CREATE VIEW viewname AS" & _
" SELECT col1," & _
" col2," & _
" col3 as NewName," & _
" col4," & _
" col5 as NewName," & _
" col6" & _
" FROM tablea"
Dim viewCommand As New SqlCommand(cmdText, connection)
Try
connection.Open()
viewCommand.ExecuteNonQuery()
Finally
connection.Close()
End Try

Hope this helps
Jay

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<#5**************@TK2MSFTNGP11.phx.gbl>...
Zack,
What does your code look like, specifically the CREATE VIEW statement
itself?

Can you print the CREATE VIEW statement, just before you execute the
ExecuteNonQuery method?


WHile attempting to debug this issue, I added a Debug.WriteLine to
dump the SQL command to create the view which I used to cut and paste
into a Query Analyzer window. A sample create view command would look
like:

CREATE VIEW viewname AS
SELECT col1,
col2,
col3 as NewName,
col4,
col5 as NewName,
col6
FROM tablea

Note the error is that col3 and col5 both have the same alias name.
When you run this command in the Query Analyzer window, you get the
error you expect, that there are two fields with the same name. When I
try to run the command from VB.NET using ADO.NET, no error is
returned, ie, the Catch in a Try/Catch is not raised.










"Zack Sessions" <zc********@visionair.com> wrote in message
news:db*************************@posting.google.co m...
Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name 'FOO' in view
'BAR' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain't catchin' it!!!

Any ideas?

Nov 20 '05 #6

P: n/a
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<#X**************@tk2msftngp13.phx.gbl>...
Zack,
Because of the severity level of the you are getting back an InfoMessage is
used instead of an Exception.

You need to handle the SqlConnection.InfoMessage event, and act accordingly.

Private Sub OnInfoMessage(ByVal sender As Object, ByVal e As
SqlInfoMessageEventArgs)
Debug.WriteLine(e.ToString)
' consider throwing an exception here.
End Sub

Dim connection As New SqlConnection(connectionString)

AddHandler connection.InfoMessage, AddressOf OnInfoMessage

Const cmdText As String = "CREATE VIEW viewname AS" & _
" SELECT col1," & _
" col2," & _
" col3 as NewName," & _
" col4," & _
" col5 as NewName," & _
" col6" & _
" FROM tablea"
Dim viewCommand As New SqlCommand(cmdText, connection)
Try
connection.Open()
viewCommand.ExecuteNonQuery()
Finally
connection.Close()
End Try

Hope this helps
Jay
Sorry for the delay in responding, been on vacation. This looks
promising. I'll give it a try. Thanks for your response.

Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<#5**************@TK2MSFTNGP11.phx.gbl>...
Zack,
What does your code look like, specifically the CREATE VIEW statement
itself?

Can you print the CREATE VIEW statement, just before you execute the
ExecuteNonQuery method?


WHile attempting to debug this issue, I added a Debug.WriteLine to
dump the SQL command to create the view which I used to cut and paste
into a Query Analyzer window. A sample create view command would look
like:

CREATE VIEW viewname AS
SELECT col1,
col2,
col3 as NewName,
col4,
col5 as NewName,
col6
FROM tablea

Note the error is that col3 and col5 both have the same alias name.
When you run this command in the Query Analyzer window, you get the
error you expect, that there are two fields with the same name. When I
try to run the command from VB.NET using ADO.NET, no error is
returned, ie, the Catch in a Try/Catch is not raised.










"Zack Sessions" <zc********@visionair.com> wrote in message
news:db*************************@posting.google.co m...
> Has anyone tried to create a SQL7 view using the CREATE VIEW command
> and ADO.NET? If so, is there a trick in trapping a SQL error when
> trying to create the view? I have a VB.NET app that, amoung other
> things, can create views based on existing tables and a table of
> column name equivalents. If I accidently introduce a column name
> equivalent that is the same as another column, I end up with a CREATE
> VIEW SQL statement that is in error, if you run it in a query analyzer
> window you get an error like the following:
>
> Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
> Column names in each view must be unique. Column name 'FOO' in view
> 'BAR' is specified more than once.
>
> The ExecuteNonQuery is being done inside a Try/Catch but the Catch
> ain't catchin' it!!!
>
> Any ideas?

Nov 20 '05 #7

P: n/a
Zack,
Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?
As I stated, at least tried to state, its because of the severity level of
the message you are getting back from SQL server.

http://msdn.microsoft.com/library/de...ssageTopic.asp

Hope this helps
Jay

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om... "Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<#X**************@tk2msftngp13.phx.gbl>...
Zack,
Because of the severity level of the you are getting back an InfoMessage is used instead of an Exception.

You need to handle the SqlConnection.InfoMessage event, and act accordingly.
Private Sub OnInfoMessage(ByVal sender As Object, ByVal e As
SqlInfoMessageEventArgs)
Debug.WriteLine(e.ToString)
' consider throwing an exception here.
End Sub

Dim connection As New SqlConnection(connectionString)

AddHandler connection.InfoMessage, AddressOf OnInfoMessage

Const cmdText As String = "CREATE VIEW viewname AS" & _
" SELECT col1," & _
" col2," & _
" col3 as NewName," & _
" col4," & _
" col5 as NewName," & _
" col6" & _
" FROM tablea"
Dim viewCommand As New SqlCommand(cmdText, connection)
Try
connection.Open()
viewCommand.ExecuteNonQuery()
Finally
connection.Close()
End Try

Hope this helps
Jay


Sorry for the delay in responding, been on vacation. This looks
promising. I'll give it a try. Thanks for your response.

Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:<#5**************@TK2MSFTNGP11.phx.gbl>...
> Zack,
> What does your code look like, specifically the CREATE VIEW

statement > itself?
>
> Can you print the CREATE VIEW statement, just before you execute the
> ExecuteNonQuery method?

WHile attempting to debug this issue, I added a Debug.WriteLine to
dump the SQL command to create the view which I used to cut and paste
into a Query Analyzer window. A sample create view command would look
like:

CREATE VIEW viewname AS
SELECT col1,
col2,
col3 as NewName,
col4,
col5 as NewName,
col6
FROM tablea

Note the error is that col3 and col5 both have the same alias name.
When you run this command in the Query Analyzer window, you get the
error you expect, that there are two fields with the same name. When I
try to run the command from VB.NET using ADO.NET, no error is
returned, ie, the Catch in a Try/Catch is not raised.










>
> "Zack Sessions" <zc********@visionair.com> wrote in message
> news:db*************************@posting.google.co m...
> > Has anyone tried to create a SQL7 view using the CREATE VIEW command > > and ADO.NET? If so, is there a trick in trapping a SQL error when
> > trying to create the view? I have a VB.NET app that, amoung other
> > things, can create views based on existing tables and a table of
> > column name equivalents. If I accidently introduce a column name
> > equivalent that is the same as another column, I end up with a CREATE > > VIEW SQL statement that is in error, if you run it in a query analyzer > > window you get an error like the following:
> >
> > Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
> > Column names in each view must be unique. Column name 'FOO' in view > > 'BAR' is specified more than once.
> >
> > The ExecuteNonQuery is being done inside a Try/Catch but the Catch
> > ain't catchin' it!!!
> >
> > Any ideas?

Nov 20 '05 #8

P: n/a
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<uG**************@TK2MSFTNGP12.phx.gbl>...
Zack,
Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?
As I stated, at least tried to state, its because of the severity level of
the message you are getting back from SQL server.


I think you misunderstood my question. If the CREATE VIEW has a severe
enough error in the syntax to cause the command to completely fail,
how come just an info message is being raised and not a more severe
error? Seems like the severity level of this particular error should
be higher since the command failed to complete.

http://msdn.microsoft.com/library/de...ssageTopic.asp

Hope this helps
Jay

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<#X**************@tk2msftngp13.phx.gbl>...
Zack,
Because of the severity level of the you are getting back an InfoMessage is used instead of an Exception.

You need to handle the SqlConnection.InfoMessage event, and act accordingly.
Private Sub OnInfoMessage(ByVal sender As Object, ByVal e As
SqlInfoMessageEventArgs)
Debug.WriteLine(e.ToString)
' consider throwing an exception here.
End Sub

Dim connection As New SqlConnection(connectionString)

AddHandler connection.InfoMessage, AddressOf OnInfoMessage

Const cmdText As String = "CREATE VIEW viewname AS" & _
" SELECT col1," & _
" col2," & _
" col3 as NewName," & _
" col4," & _
" col5 as NewName," & _
" col6" & _
" FROM tablea"
Dim viewCommand As New SqlCommand(cmdText, connection)
Try
connection.Open()
viewCommand.ExecuteNonQuery()
Finally
connection.Close()
End Try

Hope this helps
Jay


Sorry for the delay in responding, been on vacation. This looks
promising. I'll give it a try. Thanks for your response.

Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
> "Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:<#5**************@TK2MSFTNGP11.phx.gbl>... > > Zack,
> > What does your code look like, specifically the CREATE VIEW statement > > itself?
> >
> > Can you print the CREATE VIEW statement, just before you execute the
> > ExecuteNonQuery method?
>
> WHile attempting to debug this issue, I added a Debug.WriteLine to
> dump the SQL command to create the view which I used to cut and paste
> into a Query Analyzer window. A sample create view command would look
> like:
>
> CREATE VIEW viewname AS
> SELECT col1,
> col2,
> col3 as NewName,
> col4,
> col5 as NewName,
> col6
> FROM tablea
>
> Note the error is that col3 and col5 both have the same alias name.
> When you run this command in the Query Analyzer window, you get the
> error you expect, that there are two fields with the same name. When I
> try to run the command from VB.NET using ADO.NET, no error is
> returned, ie, the Catch in a Try/Catch is not raised.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> >
> > "Zack Sessions" <zc********@visionair.com> wrote in message
> > news:db*************************@posting.google.co m...
> > > Has anyone tried to create a SQL7 view using the CREATE VIEW command > > > and ADO.NET? If so, is there a trick in trapping a SQL error when
> > > trying to create the view? I have a VB.NET app that, amoung other
> > > things, can create views based on existing tables and a table of
> > > column name equivalents. If I accidently introduce a column name
> > > equivalent that is the same as another column, I end up with a CREATE > > > VIEW SQL statement that is in error, if you run it in a query analyzer > > > window you get an error like the following:
> > >
> > > Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
> > > Column names in each view must be unique. Column name 'FOO' in view > > > 'BAR' is specified more than once.
> > >
> > > The ExecuteNonQuery is being done inside a Try/Catch but the Catch
> > > ain't catchin' it!!!
> > >
> > > Any ideas?

Nov 20 '05 #9

P: n/a
Zack,
I did not misunderstood you.

Think about what I stated & what the link says:

You are getting an InfoMessage event, the InfoMessage event is raised for
certain levels. Ergo?
I agree I would expect a "failed" CREATE VIEW to be a higher level, your
original code suggests it is:
> > > > Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
> > > > Column names in each view must be unique. Column name 'FOO' in
I will try to find more info, as this seems to be a bug in either what SQL
Server is returning or how ADO.NET is interpreting it... Of course it may be
as simple as a connection setting...

While I'm looking, you may try asking "down the hall" in the
microsoft.public.dotnet.framework.adonet newsgroup or possible even one of
the SQL Server programming newsgroups, as I would expect (hope) they would
have more experience with executing commands and getting exceptions vs
informessages back...

Hope this helps
Jay
"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<uG**************@TK2MSFTNGP12.phx.gbl>...
Zack,
Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?


As I stated, at least tried to state, its because of the severity level of the message you are getting back from SQL server.


I think you misunderstood my question. If the CREATE VIEW has a severe
enough error in the syntax to cause the command to completely fail,
how come just an info message is being raised and not a more severe
error? Seems like the severity level of this particular error should
be higher since the command failed to complete.

http://msdn.microsoft.com/library/de...ssageTopic.asp
Hope this helps
Jay

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in
message news:<#X**************@tk2msftngp13.phx.gbl>...
> Zack,
> Because of the severity level of the you are getting back an
InfoMessage is
> used instead of an Exception.
>
> You need to handle the SqlConnection.InfoMessage event, and act

accordingly.
>
> Private Sub OnInfoMessage(ByVal sender As Object, ByVal e As
> SqlInfoMessageEventArgs)
> Debug.WriteLine(e.ToString)
> ' consider throwing an exception here.
> End Sub
>
> Dim connection As New SqlConnection(connectionString)
>
> AddHandler connection.InfoMessage, AddressOf OnInfoMessage
>
> Const cmdText As String = "CREATE VIEW viewname AS" & _
> " SELECT col1," & _
> " col2," & _
> " col3 as NewName," & _
> " col4," & _
> " col5 as NewName," & _
> " col6" & _
> " FROM tablea"
>
>
> Dim viewCommand As New SqlCommand(cmdText, connection)
> Try
> connection.Open()
> viewCommand.ExecuteNonQuery()
> Finally
> connection.Close()
> End Try
>
> Hope this helps
> Jay

Sorry for the delay in responding, been on vacation. This looks
promising. I'll give it a try. Thanks for your response.

Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?

>
> "Zack Sessions" <zc********@visionair.com> wrote in message
> news:db**************************@posting.google.c om...
> > "Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in

message
news:<#5**************@TK2MSFTNGP11.phx.gbl>...
> > > Zack,
> > > What does your code look like, specifically the CREATE VIEW

statement
> > > itself?
> > >
> > > Can you print the CREATE VIEW statement, just before you execute

the > > > ExecuteNonQuery method?
> >
> > WHile attempting to debug this issue, I added a Debug.WriteLine to
> > dump the SQL command to create the view which I used to cut and paste > > into a Query Analyzer window. A sample create view command would look > > like:
> >
> > CREATE VIEW viewname AS
> > SELECT col1,
> > col2,
> > col3 as NewName,
> > col4,
> > col5 as NewName,
> > col6
> > FROM tablea
> >
> > Note the error is that col3 and col5 both have the same alias name. > > When you run this command in the Query Analyzer window, you get the > > error you expect, that there are two fields with the same name. When I > > try to run the command from VB.NET using ADO.NET, no error is
> > returned, ie, the Catch in a Try/Catch is not raised.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > >
> > > "Zack Sessions" <zc********@visionair.com> wrote in message
> > > news:db*************************@posting.google.co m...
> > > > Has anyone tried to create a SQL7 view using the CREATE VIEW

command
> > > > and ADO.NET? If so, is there a trick in trapping a SQL error when > > > > trying to create the view? I have a VB.NET app that, amoung other > > > > things, can create views based on existing tables and a table of > > > > column name equivalents. If I accidently introduce a column name > > > > equivalent that is the same as another column, I end up with a

CREATE
> > > > VIEW SQL statement that is in error, if you run it in a query

analyzer
> > > > window you get an error like the following:
> > > >
> > > > Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
> > > > Column names in each view must be unique. Column name 'FOO' in

view
> > > > 'BAR' is specified more than once.
> > > >
> > > > The ExecuteNonQuery is being done inside a Try/Catch but the Catch > > > > ain't catchin' it!!!
> > > >
> > > > Any ideas?

Nov 20 '05 #10

P: n/a
Zack,
Checking BOL (SQL Server Books On-Line).

Error: 4506
Severity: 10
Description: Column names in each view or function must be unique. Column
name '%.*ls' in view or function '%.*ls' is specified more than once.

InfoMessage is raised for messages of severity 10 or less, ergo you get the
InfoMessage.

I don't know SQL Server well enough to know how or if you can change or even
if you should change the severity of the above message. I'm still looking...

Hope this helps
Jay
"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<uG**************@TK2MSFTNGP12.phx.gbl>...
Zack,
Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?


As I stated, at least tried to state, its because of the severity level of the message you are getting back from SQL server.


I think you misunderstood my question. If the CREATE VIEW has a severe
enough error in the syntax to cause the command to completely fail,
how come just an info message is being raised and not a more severe
error? Seems like the severity level of this particular error should
be higher since the command failed to complete.

http://msdn.microsoft.com/library/de...ssageTopic.asp
Hope this helps
Jay

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db**************************@posting.google.c om...
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:<#X**************@tk2msftngp13.phx.gbl>...
> Zack,
> Because of the severity level of the you are getting back an
InfoMessage is
> used instead of an Exception.
>
> You need to handle the SqlConnection.InfoMessage event, and act

accordingly.
>
> Private Sub OnInfoMessage(ByVal sender As Object, ByVal e As
> SqlInfoMessageEventArgs)
> Debug.WriteLine(e.ToString)
> ' consider throwing an exception here.
> End Sub
>
> Dim connection As New SqlConnection(connectionString)
>
> AddHandler connection.InfoMessage, AddressOf OnInfoMessage
>
> Const cmdText As String = "CREATE VIEW viewname AS" & _
> " SELECT col1," & _
> " col2," & _
> " col3 as NewName," & _
> " col4," & _
> " col5 as NewName," & _
> " col6" & _
> " FROM tablea"
>
>
> Dim viewCommand As New SqlCommand(cmdText, connection)
> Try
> connection.Open()
> viewCommand.ExecuteNonQuery()
> Finally
> connection.Close()
> End Try
>
> Hope this helps
> Jay

Sorry for the delay in responding, been on vacation. This looks
promising. I'll give it a try. Thanks for your response.

Can you tell me why an infomessage is being raised and not an
exception if the create view command contains an error?

>
> "Zack Sessions" <zc********@visionair.com> wrote in message
> news:db**************************@posting.google.c om...
> > "Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in

message
news:<#5**************@TK2MSFTNGP11.phx.gbl>...
> > > Zack,
> > > What does your code look like, specifically the CREATE VIEW

statement
> > > itself?
> > >
> > > Can you print the CREATE VIEW statement, just before you execute

the > > > ExecuteNonQuery method?
> >
> > WHile attempting to debug this issue, I added a Debug.WriteLine to
> > dump the SQL command to create the view which I used to cut and paste > > into a Query Analyzer window. A sample create view command would look > > like:
> >
> > CREATE VIEW viewname AS
> > SELECT col1,
> > col2,
> > col3 as NewName,
> > col4,
> > col5 as NewName,
> > col6
> > FROM tablea
> >
> > Note the error is that col3 and col5 both have the same alias name. > > When you run this command in the Query Analyzer window, you get the > > error you expect, that there are two fields with the same name. When I > > try to run the command from VB.NET using ADO.NET, no error is
> > returned, ie, the Catch in a Try/Catch is not raised.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > >
> > > "Zack Sessions" <zc********@visionair.com> wrote in message
> > > news:db*************************@posting.google.co m...
> > > > Has anyone tried to create a SQL7 view using the CREATE VIEW

command
> > > > and ADO.NET? If so, is there a trick in trapping a SQL error when > > > > trying to create the view? I have a VB.NET app that, amoung other > > > > things, can create views based on existing tables and a table of > > > > column name equivalents. If I accidently introduce a column name > > > > equivalent that is the same as another column, I end up with a

CREATE
> > > > VIEW SQL statement that is in error, if you run it in a query

analyzer
> > > > window you get an error like the following:
> > > >
> > > > Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
> > > > Column names in each view must be unique. Column name 'FOO' in

view
> > > > 'BAR' is specified more than once.
> > > >
> > > > The ExecuteNonQuery is being done inside a Try/Catch but the Catch > > > > ain't catchin' it!!!
> > > >
> > > > Any ideas?

Nov 20 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.