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?
10  8757 
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?
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?
"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?
"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?
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?
"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?
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?
"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?
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?
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?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: kackson |
last post by:
Hi.
I created a simple view with the following statements:
CREATE VIEW dbo.VIEW1
AS
SELECT dbo.VIEW_ALL.ID,
dbo.VIEW_ALL.Code,
Another.dbo.OTHER_VIEW.Label as SpecialCode
FROM ...
|
by: MD |
last post by:
I am trying to create a dynamic SQL statement to create a view.
I have a stored procedure, which based on the parameters passed calls
different stored procedures. Each of this sub stored procedure...
|
by: T Tran via SQLMonster.com |
last post by:
Hi Gurus,
I'm a beginner. Would you please tell me if it's possible to create a view
having a calcuated column based on the condition of the column on the sql
table.
create view vwImaging AS...
|
by: Mike Schuler |
last post by:
Hello,
I thought I knew sql, but when I send this as an argument of a
Statement.execute():
create view mike.unnamed1(WORKDEPT) as select f1.WORKDEPT as
\"WORKDEPT\"\r\nfrom MIKE.EMPLOYEE...
|
by: dermot |
last post by:
I have an MS Access ADP project linked to a SQL Server database.
When a particular user tries to create a view she gets an error saying
'ADO error 'create view' does not allow specifying the...
|
by: dermot |
last post by:
I have an MS Access ADP project linked to a SQL Server database.
When a particular user tries to create a view she gets an error saying
'ADO error 'create view' does not allow specifying the...
|
by: Karsten |
last post by:
Hi,
I want to create dynamic reports with C# and MS SQL-Server 2005
Reporting Services.
Since the reporting service uses a data-table or data-view, I want to
create a view dynamically.
How...
|
by: cognosqueen |
last post by:
I need to create a view of a sql table, but change the data types. I know
the syntax below is not correct, and can't figure out if it is wrong or if
you just can't do this. I have only created...
|
by: alessandro menchini |
last post by:
Hello,
First of all: i apologize for my english...i'm still learning...
If i create a view it seems that DB2 manage this view like an
updateable view.
But I need to create a view "read only",...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
