field + nullfield = null

I have two fields in different tabels. I have a calcualated field in a view
that goes something like this:
mato.pmainT.product + mato.woodtype.short
It seemed to work OK, but I found if mato.woodtype.short is null, it won't
display mato.pmainT.product either!
I would like it to display mato.pmainT.product. How can this be done?
Also, if this is the wrong newsgroup to ask this type of question in, please
tell me where to go. However, I would really appreciate an answer to my
question as well.
Matthew
I got it figured out. I set the null field to a blank string. I didn't
know there was a difference.
Matthew
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:uY$hA1HYEHA.644@.tk2msftngp13.phx.gbl...
> I have two fields in different tabels. I have a calcualated field in a
view
> that goes something like this:
> mato.pmainT.product + mato.woodtype.short
> It seemed to work OK, but I found if mato.woodtype.short is null, it won't
> display mato.pmainT.product either!
> I would like it to display mato.pmainT.product. How can this be done?
> Also, if this is the wrong newsgroup to ask this type of question in,
please
> tell me where to go. However, I would really appreciate an answer to my
> question as well.
> Matthew
>
|||Hi
You can use the ISNULL function to convert your null field to a value in the
view. If you field is still nullable there is nothing stopping someone
inserting a new row with a null value in it. The behaviour
is also dependent on the CONCAT_NULL_YIELDS_NULL setting. Check out books
online for more information.
John
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O%23hc8$HYEHA.3568@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I got it figured out. I set the null field to a blank string. I didn't
> know there was a difference.
> Matthew
> "Matthew" <turn.deletethis@.alltel.net> wrote in message
> news:uY$hA1HYEHA.644@.tk2msftngp13.phx.gbl...
> view
won't
> please
>

field + nullfield = null

I have two fields in different tabels. I have a calcualated field in a view
that goes something like this:
mato.pmainT.product + mato.woodtype.short
It seemed to work OK, but I found if mato.woodtype.short is null, it won't
display mato.pmainT.product either!
I would like it to display mato.pmainT.product. How can this be done?
Also, if this is the wrong newsgroup to ask this type of question in, please
tell me where to go. However, I would really appreciate an answer to my
question as well.
MatthewI got it figured out. I set the null field to a blank string. I didn't
know there was a difference.
Matthew
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:uY$hA1HYEHA.644@.tk2msftngp13.phx.gbl...
> I have two fields in different tabels. I have a calcualated field in a
view
> that goes something like this:
> mato.pmainT.product + mato.woodtype.short
> It seemed to work OK, but I found if mato.woodtype.short is null, it won't
> display mato.pmainT.product either!
> I would like it to display mato.pmainT.product. How can this be done?
> Also, if this is the wrong newsgroup to ask this type of question in,
please
> tell me where to go. However, I would really appreciate an answer to my
> question as well.
> Matthew
>|||Hi
You can use the ISNULL function to convert your null field to a value in the
view. If you field is still nullable there is nothing stopping someone
inserting a new row with a null value in it. The behaviour
is also dependent on the CONCAT_NULL_YIELDS_NULL setting. Check out books
online for more information.
John
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O%23hc8$HYEHA.3568@.TK2MSFTNGP10.phx.gbl...
> I got it figured out. I set the null field to a blank string. I didn't
> know there was a difference.
> Matthew
> "Matthew" <turn.deletethis@.alltel.net> wrote in message
> news:uY$hA1HYEHA.644@.tk2msftngp13.phx.gbl...
> view
won't[vbcol=seagreen]
> please
>sql

Feedback regarding Feedback on the Feedback Center

Just a note to the MS guys...

I'm fully supportive of the product Feedback Center initiative and the subsequent withdrawal of sqlwish.

Problem is, if nobody ever gives us feedback on the things that we submit then it simply is a glorified version of sqlwish with no added value.

I apologise if there are plans afoot to address to offer feedback to the things we put up there but I (and others) have been freely submitting bugs and suggestions for a few months now without hearing anything back and I'm beginnign to wonder why we bother.

Even a simple "This is a good idea and will be considered for Katmai" or "This is a terrible idea now go and stick your head back in the sand" would be better than a cut and pasted response which is just about all I've seen so far.

Comments?

-Jamie

I agree I am unclear as to the purpose of the feedback centre. We appreciate that 2005 is done an dusted therefore if an issue is suggestion is now being closed as won't fix or by design that is implying it won't be fixed for the next 4 years. If something has missed SP1 and is therefore (one hopes) being considered for Katmai then it should have an appropriate status.

An example is the Tabs issue. http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=0010c553-d46e-42d0-b548-38b5f9ea79aa

Raised against CTP in June, A fix put in for RTM. Not sufficient in my book, the issue is reopene dby Erland and closed again saying a DCR has been raised but not in the feedback centre. This suggests that next version stuff is not being taken from product feedback centre.

This confusion is further exaserpated by a user group meeting on wednesday ran by members of the User Experience bods from the SQL Team. They are really wanting feedback from users, but currently I am not sure were to direct feedback.

|||

Any comment?

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not

exist" by any method other than checking to see if it exists first.

The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not exist" by any method other than checking to see if it exists first.
The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.

|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Failure...SQL Server does not allow remote connections

Hi all,
We've got a product which is three tier,
using a .NET Client -> .NET Server which in turn connects to a Sql
Server 2005 Instance.
On top of this we also have a Windows Service which runs nightly
processes also written in .NET
and connects directly to the SQL Server instance.
It's all working pretty well, except that in some installations the
Nightly Processes service
is failing to connect to the SQL Server instance.
We're getting the following message:
__________________________________________________ _______________
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections
........
__________________________________________________ _______________
This has us confused. The nightly processes is using an indentical
connection string
to the .NET Server. The .NET application is connecting fine
everytime.
The service and the application server are sitting on the same machine
as the SQL Server instance,
so no remote connection should be needed.
The problem seems to happen only on Windows Server Machines/SQL Server
installations vs WinXP/SQLExpress, however that could be unrelated.
Any thoughts appreciated.
Cheers
Chris.
Do ou use TCP port ?
If yes do you use autopmatic selection of port number ?
If yes it is preferable to fixed a tcp port to connect to SQL server, using
autoamtic selction of port might affect such connection trouble
Hope it helps
serge
"theinvisibleGhost" wrote:

> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> __________________________________________________ _______________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> ........
> __________________________________________________ _______________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>
|||Not as far as I'm aware. It's certainly not referenced in the
connection string?
|||See my whitepaper on connecting... it might turn up some issues that you had
not considered.
http://betav.com/blog/billva/2006/06/getting_and_staying_connected_1.html
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"theinvisibleGhost" <theinvisibleGhost@.yahoo.com> wrote in message
news:1178703769.427072.296430@.q75g2000hsh.googlegr oups.com...
> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> __________________________________________________ _______________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> .......
> __________________________________________________ _______________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>
|||Cheers Bill,
I've taken a good look through your page.
I think the most likely scenario might be that the service
is running the local-system account and this may not
have access for some reason, the SQL services are running
under a different windows logon.
The SQL Server instance is running in mixed mode security.
I know where to find the permissions, how can I find out if "Local
System"
has access?
Cheers
Chris
|||OK i've checked and LocalSystem already has Connect SQL access,
so I don't think it's that...
Hmmmm...

Failure...SQL Server does not allow remote connections

Hi all,
We've got a product which is three tier,
using a .NET Client -> .NET Server which in turn connects to a Sql
Server 2005 Instance.
On top of this we also have a Windows Service which runs nightly
processes also written in .NET
and connects directly to the SQL Server instance.
It's all working pretty well, except that in some installations the
Nightly Processes service
is failing to connect to the SQL Server instance.
We're getting the following message:
________________________________________
_________________________
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections
.......
________________________________________
_________________________
This has us confused. The nightly processes is using an indentical
connection string
to the .NET Server. The .NET application is connecting fine
everytime.
The service and the application server are sitting on the same machine
as the SQL Server instance,
so no remote connection should be needed.
The problem seems to happen only on Windows Server Machines/SQL Server
installations vs WinXP/SQLExpress, however that could be unrelated.
Any thoughts appreciated.
Cheers
Chris.Do ou use TCP port ?
If yes do you use autopmatic selection of port number ?
If yes it is preferable to fixed a tcp port to connect to SQL server, using
autoamtic selction of port might affect such connection trouble
Hope it helps
serge
"theinvisibleGhost" wrote:

> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> ________________________________________
_________________________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> ........
> ________________________________________
_________________________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>|||Not as far as I'm aware. It's certainly not referenced in the
connection string?|||See my whitepaper on connecting... it might turn up some issues that you had
not considered.
http://betav.com/blog/billva/2006/0...onnected_1.html
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"theinvisibleGhost" <theinvisibleGhost@.yahoo.com> wrote in message
news:1178703769.427072.296430@.q75g2000hsh.googlegroups.com...
> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> ________________________________________
_________________________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> .......
> ________________________________________
_________________________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>|||Cheers Bill,
I've taken a good look through your page.
I think the most likely scenario might be that the service
is running the local-system account and this may not
have access for some reason, the SQL services are running
under a different windows logon.
The SQL Server instance is running in mixed mode security.
I know where to find the permissions, how can I find out if "Local
System"
has access?
Cheers
Chris|||OK i've checked and LocalSystem already has Connect SQL access,
so I don't think it's that...
Hmmmm...