Field names

Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick
|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>
|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:

>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.

> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>

Field names

Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:

>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.

> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>

Field names

Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> Is there a preference in sql server 2005 as to how fields/objects are
>> named such as Client_Address, Client Address, Client-Address or
>> ClientAddress?
>> Also what are the reserved words that should be avoided in field names?
>> Thanks
>> Regards
>>
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
>> OK, well that was just an example. What about answers to my questions?
>> Regards
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>

feedback after printing

helloo all,
i am using client side printing to print my reports
i need after printing report to get a feedback if the report is printed or not
if client side printing will not help me is there are any way to do that ?
thanks
Bolosfrom web application
i had use the url access for the client side printing
so it return the activex of it as the resut
same as if u print form the report manager
if u have any ideas just tell me
"Mike Morisoli" wrote:
> Bolos, can I ask how you are managing the client side printing? Is this
> from IE or from a WinForm?
> Thanks, Mike
> "Bolos wageh" <Boloswageh@.discussions.microsoft.com> wrote in message
> news:6F5946AD-E430-41BE-91BE-D74C9DDE55DF@.microsoft.com...
> > helloo all,
> >
> > i am using client side printing to print my reports
> > i need after printing report to get a feedback if the report is printed or
> > not
> >
> > if client side printing will not help me is there are any way to do that
> > ?
> >
> > thanks
> > Bolos
>
>

February Books Online Available

If you're using the local client of Books Online, you can get the version released in February of 2007 here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

If you're using the Web to read Books Online, it is automatically updated. You can read that here:

http://msdn2.microsoft.com/en-us/library/ms130214.aspx

There is also a wealth of other information here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/learning/default.mspx

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

Fast Uploading Data from Client to SQL2000 Database

Does anyone know how to upload (bulk) data from a client (written in Excel VBA) to a remote SQL2000 database? Of coarse I tried "INSERT INTO" and rst.addnew but I noticed this is much, much slower as downloading from the same remote database.
Thanks.It tends to be faster if you can do this the other way round i.e. get SQL Server to load it from Excel rather than using Excel to load it into SQL Server.|||You can try to use BCP function or SQL DTS to import into the relevant table. This will automate your process and make it much faster. Sometimes, it depend on the indexing,size of a table or the design of the database for performance.|||BCP uses the filesystem which I cannot access from the client v.v.

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

Failure Audit Event ID 18456

We occasionally get the error Login Failure for 'Admin' (client: then IP address). The SQL 2005 server is set up to use windows authentication and no one uses an account called Admin. The people here use their windows logon to access the database. Any suggestions would be appreciated.

Mark Hartman

Hi Mark,

did you find a solution for this problem? I have the same issue but can't find any info on it. Would you please email me at iman.ahmadi@.state.or.us if you find any solution for this or already solved it?

Thanks

-Iman