|
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?
| |
Share this Question
|
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? | |
|
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? | |
|
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? | |
|
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? | |
|
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?
| |
|
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?
| |
|
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?
| |
|
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?
| |
|
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?
| |
|
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?
| | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 7273
- replies: 10
- date asked: Nov 20 '05
|
