Create View with ADO.NET

[Zack Sessions]

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]]

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

 

[Guest]

The error is in your CREATE VIEW command, not in ADO.NET. Try run the CREATE VIEW command in Query Analyzer

 

[Zack Sessions]

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]

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.

 

[Jay B. Harlow [MVP - Outlook]]

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

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in message

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]

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?

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in message

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.

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]]

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/d...lClientSqlConnectionClassInfoMessageTopic.asp

Hope this helps
Jay

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in message

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?

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in

message

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.





























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 Sessions]

Zack,

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/d...lClientSqlConnectionClassInfoMessageTopic.asp

Hope this helps
Jay

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in message

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?

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in

message

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.





























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]]

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:

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

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/d...lClientSqlConnectionClassInfoMessageTopic.asp

Hope this helps
Jay

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in

message

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?


"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in

message

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.





























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?

[/QUOTE]

 

[Jay B. Harlow [MVP - Outlook]]

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

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/d...lClientSqlConnectionClassInfoMessageTopic.asp

Hope this helps
Jay

"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in

message

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?


"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in

message

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.





























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?

[/QUOTE]