32 bit CLR-code and 64 bit SQLServer

Hello,
is it possible to deploy CLR-code developed with a 32 bit VS.NET on a
32 bit machine to a 64 bit SQLServer?
Thanks
Tobias"Paletti" <Tobias@.Olbort.de> wrote in news:1135757756.405122.308810
@.o13g2000cwo.googlegroups.com:

> is it possible to deploy CLR-code developed with a 32 bit VS.NET on a
> 32 bit machine to a 64 bit SQLServer?
As long as the 32 bit is CLR 2.0 and built with the AnyCPU flag (or the 64-
bit CPU flag).
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

2col. with data in diff. sequence ref. same column in different table

I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!

I'm having trouble creating a view that will give me the informaiton that i need correctly. I listed all the tables and the view that I tried but it's not working I dont think i have the view right.

Here is some info to help you understand what I'm trying to get:

Examples of the data that I'm having trouble with
only conscerns two of the tables

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name

1 = john
2 = mike
3 = sam
4 = jim
5 = tony

TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID

and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also

NOTICE the two have different sequence.

I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID

output would give this as example

(intdriverID 1) john would have alt driverId 5 tony

I can't create a select statement that will give me both names at the same time.

Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.

Any suggestions would be greatly appreciated.

CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)

CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)

CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)

every column below is a foreign key to other tables

CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)

Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form

CREATE VIEW V_SchedualedRoutes AS

SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDSELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName) -- remove )
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI) --change TDrivers.intDriverI to TDrivers.intDriverID
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteID

Try again :)|||Thankyou for your help

2147217887 Error when sending to SQL 2005

I have a Visual Basic 6.0 app that I developed initially using MS SQL Server
7, then MS SQL
Server 2000 and now MS SQL Server 2005. I seem to get this error more often
in 2005 that the older versions. Googling does not seem to give any help.
What are the causes of this error? What can I do to avoid it?
The error happens when I use a command like:
DataRecord3.Fields("PSI_Fifteen") = CStr(PSI_Fifteen)
If I resend the command after the error it always works.
Thanks,
Bob HillerBob and Sharon Hiller (aoklans@.tir.com) writes:
> I have a Visual Basic 6.0 app that I developed initially using MS SQL
> Server 7, then MS SQL Server 2000 and now MS SQL Server 2005. I seem to
> get this error more often in 2005 that the older versions. Googling does
> not seem to give any help. What are the causes of this error? What can I
> do to avoid it?
> The error happens when I use a command like:
> DataRecord3.Fields("PSI_Fifteen") = CStr(PSI_Fifteen)
> If I resend the command after the error it always works.
That seems to be very informative message "Multiple-step OLE DB operation ge
nerated errors. Check each OLE DB status value, if available. No work was do
ne."
That is, there are several possible reasons for the error, or more
exactly, it can be about anything.
One problem may be that the input data is too long for the field. Another
might that there is no metadata available to make it possible to assign
the field a value. The fact that you say that you get this more often
with SQL 2005 indicates this. See
http://lab.msdn.microsoft.com/produ...59-17b0073cfa26
for such a case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

2005: Business Intelligence Development Studio?

Hello,
I am learning SQLServer 2005 Express Edition. I wanted to learn Report
Designer which - according to my tutorial book - is part of Business
Intelligence Development Studio. I can't find it in Internet. Could
you help me plase?
Thank you very much!
/RAM/It only comes with Express Advanced Services and not Express, they are both
free.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"R.A.M." <r_ahimsa_m@.poczta.onet.plwrote in message
news:p2f6b2d20jnmn9mfdvsh8504p5d4aml0s0@.4ax.com...

Quote:

Originally Posted by

Hello,
I am learning SQLServer 2005 Express Edition. I wanted to learn Report
Designer which - according to my tutorial book - is part of Business
Intelligence Development Studio. I can't find it in Internet. Could
you help me plase?
Thank you very much!
/RAM/
>

|||On Tue, 11 Jul 2006 07:11:36 +0100, "Tony Rogerson"
<tonyrogerson@.sqlserverfaq.comwrote:

Quote:

Originally Posted by

>It only comes with Express Advanced Services and not Express, they are both
>free.

I have just installed Express Edition Advanced Services. I see no
Business Intelligence application. What should I do?
Please help.
/RAM/|||I have installed also SQLEXPR_TOOLKIT.EXE. Now I see "SQL Server
Business Intelligence Development Studio" in the Start menu with a
link to "C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\devenv.exe" - but I do not have such file. What should I
do? Please help
/RAM/

2005 TRY-CATCH BLOCKS - QUESTION

Hi,
I have some questions about the new TRY-CATCH block syntax in SQL
Server 2005.

>From an article online:
Not all errors can be trapped by a TRY/CATCH block. The first types of
errors that are not trapped are compile errors, like a syntax error
within a batch of T-SQL statements. Another type of error is deferred
name resolution errors created by statement level recompilations. If a
process is terminated by a KILL command then a TRY/CATCH block does not
capture this error. Client interrupt requests or broken client
connections are also not trapped by the TRY/CATCH block. For errors
that are not trapped, SQL Server 2005 passes control back to the
application immediately, without executing any CATCH block code.
1.) After reading this, I am confused. For non-trappable errors, does
this mean that statements that occurred before the error are committed,
then control is passed back to the application? Or does it rollback
everything, then pass control to the application?
2.) Does every single DML statement require its own TRY-CATCH block,
just like you check for @.@.ERROR after every stmt (in previous versions
of SQL Server)?
3.) Also, how can you return an error code from within the CATCH block?
I can't seem to find this information.
THANKS MUCH1. Unless included in a TRANSACTION, statements before the error that
completed without error are most likely, 'committed'.
2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
block. The TRY block may have multiple statements. So it is not necessary to
test the @.@.ERROR value after each separate statement execution as was
previously the case.
3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
@.RETURN value of the stored procedure. UNLESS, of course, you have created
your own custom error messages/number and you use RAISERROR to pass that
back to the client.
CATCH
RETURN error_number()
END
The .NET application can capture the @.RETURN parameter and evaluate the
value, making decisions as appropriate. @.RETURN = 0 signifies no error.
For more information, see:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
TRY CATCH has no transactional semantics. It's purely about
control-of-flow.

> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
>
No. That's really the whole point of TRY/CATCH. You can have one bit of
error handling code get the errors for multiple TSQL statements, including
any invoked procedure.

> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
You don't return "error codes" any more. If you want to signal the calling
code or client that something went wrong, use RAISERROR.
David|||In addition to the other posts, many of these thing are easy to try. The fir
st one, for example.
Already answered by David: TRY-CATCH is only about flow control. If batch is
terminated after BEGIN
TRAN so that no COMMIT or ROLLBACK was never executed, you are left with an
open transaction:
begin try
BEGIN TRAN
SELECT * FROM lklklk
COMMIT TRAN
END TRY
BEGIN catch
ROLLBACK
RAISERROR('My user error', 16, 1)
END CATCH
PRINT 'After the whole lot'
GO
SELECT @.@.TRANCOUNT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g2000cwa.googlegroup
s.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
Thanks
Tibor Karaszi wrote:
> In addition to the other posts, many of these thing are easy to try. The =
first one, for example.
> Already answered by David: TRY-CATCH is only about flow control. If batch=
is terminated after BEGIN
> TRAN so that no COMMIT or ROLLBACK was never executed, you are left with =
an open transaction:
> begin try
> BEGIN TRAN
> SELECT * FROM lk=F6lk=F6lk
> COMMIT TRAN
> END TRY
> BEGIN catch
> ROLLBACK
> RAISERROR('My user error', 16, 1)
> END CATCH
> PRINT 'After the whole lot'
> GO
> SELECT @.@.TRANCOUNT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g=
2000cwa.googlegroups.com...[vbcol=seagreen]|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O4wHG#m4GHA.3960@.TK2MSFTNGP02.phx.gbl...
> 1. Unless included in a TRANSACTION, statements before the error that
> completed without error are most likely, 'committed'.
> 2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
> block. The TRY block may have multiple statements. So it is not necessary
> to test the @.@.ERROR value after each separate statement execution as was
> previously the case.
> 3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
> @.RETURN value of the stored procedure. UNLESS, of course, you have created
> your own custom error messages/number and you use RAISERROR to pass that
> back to the client.
> CATCH
> RETURN error_number()
> END
> The .NET application can capture the @.RETURN parameter and evaluate the
> value, making decisions as appropriate. @.RETURN = 0 signifies no error.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/ms175976.aspx
>
IMO this is a very bad plan. Unless the .NET application is coded "just so"
it will never no that the command failed. IMO you should always use
RAISERROR to signal failure to the client. In the case of a .NET client,
this will cause a SqlException to be thrown, and invoke .NET's Try/Catch
exception handling.
David|||<tootsuite@.gmail.com> wrote in message
news:1159391039.519006.292410@.i42g2000cwa.googlegroups.com...
Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
...
TRY blocks control-of-flow constructs specially designed for error handling.
In TSQL they don't actually _perform_ the error handling, or rollbacks: You
have to code that in the CATCH block. But you should absolutely use them
instead of checking @.@.ERROR after each statement.
David

2005 TRY-CATCH BLOCKS - QUESTION

Hi,
I have some questions about the new TRY-CATCH block syntax in SQL
Server 2005.

>From an article online:
Not all errors can be trapped by a TRY/CATCH block. The first types of
errors that are not trapped are compile errors, like a syntax error
within a batch of T-SQL statements. Another type of error is deferred
name resolution errors created by statement level recompilations. If a
process is terminated by a KILL command then a TRY/CATCH block does not
capture this error. Client interrupt requests or broken client
connections are also not trapped by the TRY/CATCH block. For errors
that are not trapped, SQL Server 2005 passes control back to the
application immediately, without executing any CATCH block code.
1.) After reading this, I am confused. For non-trappable errors, does
this mean that statements that occurred before the error are committed,
then control is passed back to the application? Or does it rollback
everything, then pass control to the application?
2.) Does every single DML statement require its own TRY-CATCH block,
just like you check for @.@.ERROR after every stmt (in previous versions
of SQL Server)?
3.) Also, how can you return an error code from within the CATCH block?
I can't seem to find this information.
THANKS MUCH
1. Unless included in a TRANSACTION, statements before the error that
completed without error are most likely, 'committed'.
2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
block. The TRY block may have multiple statements. So it is not necessary to
test the @.@.ERROR value after each separate statement execution as was
previously the case.
3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
@.RETURN value of the stored procedure. UNLESS, of course, you have created
your own custom error messages/number and you use RAISERROR to pass that
back to the client.
CATCH
RETURN error_number()
END
The .NET application can capture the @.RETURN parameter and evaluate the
value, making decisions as appropriate. @.RETURN = 0 signifies no error.
For more information, see:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>
|||<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
TRY CATCH has no transactional semantics. It's purely about
control-of-flow.

> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
>
No. That's really the whole point of TRY/CATCH. You can have one bit of
error handling code get the errors for multiple TSQL statements, including
any invoked procedure.

> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
You don't return "error codes" any more. If you want to signal the calling
code or client that something went wrong, use RAISERROR.
David
|||In addition to the other posts, many of these thing are easy to try. The first one, for example.
Already answered by David: TRY-CATCH is only about flow control. If batch is terminated after BEGIN
TRAN so that no COMMIT or ROLLBACK was never executed, you are left with an open transaction:
begin try
BEGIN TRAN
SELECT * FROM lklklk
COMMIT TRAN
END TRY
BEGIN catch
ROLLBACK
RAISERROR('My user error', 16, 1)
END CATCH
PRINT 'After the whole lot'
GO
SELECT @.@.TRANCOUNT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>
|||Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
Thanks
Tibor Karaszi wrote:
> In addition to the other posts, many of these thing are easy to try. The =
first one, for example.
> Already answered by David: TRY-CATCH is only about flow control. If batch=
is terminated after BEGIN
> TRAN so that no COMMIT or ROLLBACK was never executed, you are left with =
an open transaction:
> begin try
> BEGIN TRAN
> SELECT * FROM lk=F6lk=F6lk
> COMMIT TRAN
> END TRY
> BEGIN catch
> ROLLBACK
> RAISERROR('My user error', 16, 1)
> END CATCH
> PRINT 'After the whole lot'
> GO
> SELECT @.@.TRANCOUNT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g=
2000cwa.googlegroups.com...[vbcol=seagreen]
|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O4wHG#m4GHA.3960@.TK2MSFTNGP02.phx.gbl...
> 1. Unless included in a TRANSACTION, statements before the error that
> completed without error are most likely, 'committed'.
> 2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
> block. The TRY block may have multiple statements. So it is not necessary
> to test the @.@.ERROR value after each separate statement execution as was
> previously the case.
> 3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
> @.RETURN value of the stored procedure. UNLESS, of course, you have created
> your own custom error messages/number and you use RAISERROR to pass that
> back to the client.
> CATCH
> RETURN error_number()
> END
> The .NET application can capture the @.RETURN parameter and evaluate the
> value, making decisions as appropriate. @.RETURN = 0 signifies no error.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/ms175976.aspx
>
IMO this is a very bad plan. Unless the .NET application is coded "just so"
it will never no that the command failed. IMO you should always use
RAISERROR to signal failure to the client. In the case of a .NET client,
this will cause a SqlException to be thrown, and invoke .NET's Try/Catch
exception handling.
David
|||<tootsuite@.gmail.com> wrote in message
news:1159391039.519006.292410@.i42g2000cwa.googlegr oups.com...
Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
....
TRY blocks control-of-flow constructs specially designed for error handling.
In TSQL they don't actually _perform_ the error handling, or rollbacks: You
have to code that in the CATCH block. But you should absolutely use them
instead of checking @.@.ERROR after each statement.
David

2005 drivers don't like my select statement

Hello,
When I used the MS Sqlserver 2000 jdbc drivers my java code that had the
following query worked fine...
the query looks like
IF EXISTS (SELECT * FROM sysobjects WHERE id =
object_id('db_hash_admin_advice_CoopSinkInterchang e')) SELECT 1 ELSE
SELECT 0
Now with the 2005 drivers..I get the following exception
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
the keyword 'SELECT'.
at
com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDatabaseError(Unknown
Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPacke ts(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.ge tNextResult(Unknown
Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.se ndExecute(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.do ExecuteQuery(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerCallableStat ement.executeQuery(Unknown
Source)
....
....
.....
I don't think the query has a problem as it executes fine in query analyser.
should this be logged as a bug?
I am currently usinf SQLServer 2000.
Regards
chhil
Mc,
It definitelly sounds like a bug, but I have not been able to reproduce your
problem, do you have a standalone repro for this?
Here is what I have tried:
import java.sql.*;
public class test{
public static void main(String[] args) {
try{
java.lang.Class.forName("com.microsoft.sqlserver.j dbc.SQLServerDriver");
Connection connection1 =
java.sql.DriverManager.getConnection(connectionStr ing);
System.out.println("Connected!");
Statement statement1 = connection1.createStatement();
try{
statement1.execute("IF EXISTS (SELECT * FROM sysobjects WHERE
id = object_id('db_hash_admin_advice_CoopSinkInterchang e')) SELECT 1 ELSE
SELECT 0");
}catch(Exception e){
e.printStackTrace();
}
System.out.println("done");
}catch(Exception ex){
ex.printStackTrace();
}
}
}
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"mc" <incorrect@.hotmail.com> wrote in message
news:%23DFK1S5jFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Hello,
> When I used the MS Sqlserver 2000 jdbc drivers my java code that had the
> following query worked fine...
> the query looks like
> IF EXISTS (SELECT * FROM sysobjects WHERE id =
> object_id('db_hash_admin_advice_CoopSinkInterchang e')) SELECT 1 ELSE
> SELECT 0
> Now with the 2005 drivers..I get the following exception
> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
> keyword 'SELECT'.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDatabaseError(Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.IOBuffer.processPacke ts(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.ge tNextResult(Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.se ndExecute(Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.do ExecuteQuery(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerCallableStat ement.executeQuery(Unknown
> Source)
> ...
> ...
> ....
> I don't think the query has a problem as it executes fine in query
> analyser.
> should this be logged as a bug?
> I am currently usinf SQLServer 2000.
> Regards
> chhil
|||Hello Angel,
The difference is I use a CallableStatement to get a resultset back...
Snippet below...
CallableStatement stmt = null;
ResultSet rs = null;
try
{
stmt = con.prepareCall(
"IF EXISTS (SELECT * FROM sysobjects WHERE id =
object_id('"+table_name+"')) SELECT 1 ELSE SELECT 0 ");
rs = stmt.executeQuery();
if (rs.next())
{
if (rs.getInt(1) == 1)
return true;
}
return false;
}
finally
{
if (stmt != null)
stmt.close();
if (rs != null)
rs.close();
}
Angel Saenz-Badillos[MS] wrote:
> Mc,
> It definitelly sounds like a bug, but I have not been able to reproduce your
> problem, do you have a standalone repro for this?
> Here is what I have tried:
> import java.sql.*;
> public class test{
> public static void main(String[] args) {
> try{
> java.lang.Class.forName("com.microsoft.sqlserver.j dbc.SQLServerDriver");
> Connection connection1 =
> java.sql.DriverManager.getConnection(connectionStr ing);
> System.out.println("Connected!");
> Statement statement1 = connection1.createStatement();
> try{
> statement1.execute("IF EXISTS (SELECT * FROM sysobjects WHERE
> id = object_id('db_hash_admin_advice_CoopSinkInterchang e')) SELECT 1 ELSE
> SELECT 0");
> }catch(Exception e){
> e.printStackTrace();
> }
> System.out.println("done");
> }catch(Exception ex){
> ex.printStackTrace();
> }
> }
> }
>
|||Hello Angel,
The difference is I use a CallableStatement...
Snippet below...
CallableStatement stmt = null;
ResultSet rs = null;
try
{
stmt = con.prepareCall(
"IF EXISTS (SELECT * FROM sysobjects WHERE id =
object_id('"+table_name+"')) SELECT 1 ELSE SELECT 0 ");
rs = stmt.executeQuery();
if (rs.next())
{
if (rs.getInt(1) == 1)
return true;
}
return false;
}
finally
{
if (stmt != null)
stmt.close();
if (rs != null)
rs.close();
}
Angel Saenz-Badillos[MS] wrote:
> Mc,
> It definitelly sounds like a bug, but I have not been able to reproduce your
> problem, do you have a standalone repro for this?
> Here is what I have tried:
> import java.sql.*;
> public class test{
> public static void main(String[] args) {
> try{
> java.lang.Class.forName("com.microsoft.sqlserver.j dbc.SQLServerDriver");
> Connection connection1 =
> java.sql.DriverManager.getConnection(connectionStr ing);
> System.out.println("Connected!");
> Statement statement1 = connection1.createStatement();
> try{
> statement1.execute("IF EXISTS (SELECT * FROM sysobjects WHERE
> id = object_id('db_hash_admin_advice_CoopSinkInterchang e')) SELECT 1 ELSE
> SELECT 0");
> }catch(Exception e){
> e.printStackTrace();
> }
> System.out.println("done");
> }catch(Exception ex){
> ex.printStackTrace();
> }
> }
> }
>
|||I was able to repro the issue with the callable statement and have entered a
bug so that we can take a look at this asap. I would still recomend entering
a bug throught the feedback center so that you can track the bug going
forward.
Thank you for taking the time to report this issue.
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"mc" <incorrect@.hotmail.com> wrote in message
news:um$FXRekFHA.3300@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hello Angel,
> The difference is I use a CallableStatement to get a resultset back...
> Snippet below...
>
> CallableStatement stmt = null;
>
> ResultSet rs = null; try
> {
> stmt = con.prepareCall( "IF EXISTS (SELECT * FROM sysobjects WHERE id =
> object_id('"+table_name+"')) SELECT 1 ELSE SELECT 0 ");
> rs = stmt.executeQuery(); if (rs.next())
> {
> if (rs.getInt(1) == 1)
> return true;
> } return false;
> }
> finally
> {
> if (stmt != null)
> stmt.close();
> if (rs != null)
> rs.close();
> }
>
> Angel Saenz-Badillos[MS] wrote:
|||Looked at MSDN product feedback and did not find anything JDBC releated,
should this be logged against sql server 2005?
Regards mc
Angel Saenz-Badillos[MS] wrote:
> I was able to repro the issue with the callable statement and have entered a
> bug so that we can take a look at this asap. I would still recomend entering
> a bug throught the feedback center so that you can track the bug going
> forward.
> Thank you for taking the time to report this issue.
|||Sorry, I should have been more explicit:
Entering a bug:
Go to http://lab.msdn.microsoft.com/produc...k/default.aspx
Product/Technology:
SQL Server 2005
Version:
SQL Server 2005 Community Technology Preview June 2005 - Developer
Edition
Product Language:
English
Category:
JDBC Driver
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"mc" <incorrect@.hotmail.com> wrote in message
news:%238UetJikFHA.1372@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Looked at MSDN product feedback and did not find anything JDBC releated,
> should this be logged against sql server 2005?
> Regards mc
> Angel Saenz-Badillos[MS] wrote:
|||To make sure it gets routed correctly it would also help if you placed "JDBC
2005" on the title
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"Angel Saenz-Badillos[MS]" <angelsa@.online.microsoft.com> wrote in message
news:evwIH5ikFHA.3148@.TK2MSFTNGP09.phx.gbl...
> Sorry, I should have been more explicit:
> Entering a bug:
> Go to http://lab.msdn.microsoft.com/produc...k/default.aspx
> Product/Technology:
> SQL Server 2005
> Version:
> SQL Server 2005 Community Technology Preview June 2005 -
> Developer Edition
> Product Language:
> English
> Category:
> JDBC Driver
>
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
> "mc" <incorrect@.hotmail.com> wrote in message
> news:%238UetJikFHA.1372@.TK2MSFTNGP10.phx.gbl...
>
|||Hi,
I have created a bug report.
Regards MC
Angel Saenz-Badillos[MS] wrote:
> To make sure it gets routed correctly it would also help if you placed "JDBC
> 2005" on the title
>

2005 Database Diagram

Hi,
I've noticed problems when trying to print out a database diagram from SQL
Server 2005. It used to work OK in 2000 but now you get a greyed out area at
the bottom of your table and it doesn't look clear. Basically it doesn't
appear the same way it appears on the screen.
It may just the driver but I've tried it with pdffactory and looks the same
in pdf. Can anyone suggest something. Thanks.
Panos.Panos Stavroulis. wrote:
Quit spamming the NG.

2005 Copy database objects from one server to another

I just upgraded to SQLServer 2005 Standard. I do development in a database
on one server. When a solution is completely tested, I move it to the
operational database on a different server. With SQLServer 2000, I could
just open the .adp file and do File->Get External Data->Import to move the
objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same every time.
Thanks!
Jer
Jerry
You can script out the objects defenition and run it on destination server.
You may want to keep your object's version on VSS to take over deployment
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:53AA0759-AEEE-4EA9-AAFE-87902B11A8E8@.microsoft.com...
>I just upgraded to SQLServer 2005 Standard. I do development in a database
> on one server. When a solution is completely tested, I move it to the
> operational database on a different server. With SQLServer 2000, I could
> just open the .adp file and do File->Get External Data->Import to move the
> objects. That does not work with 2005.
> What is the recommended method for moving objects from one database to
> another? Obviously, the objects that changed will not be the same every
> time.
> Thanks!
> Jer

2005 Copy database objects from one server to another

I just upgraded to SQLServer 2005 Standard. I do development in a database
on one server. When a solution is completely tested, I move it to the
operational database on a different server. With SQLServer 2000, I could
just open the .adp file and do File->Get External Data->Import to move the
objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same every tim
e.
Thanks!
JerJerry
You can script out the objects defenition and run it on destination server.
You may want to keep your object's version on VSS to take over deployment
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:53AA0759-AEEE-4EA9-AAFE-87902B11A8E8@.microsoft.com...
>I just upgraded to SQLServer 2005 Standard. I do development in a database
> on one server. When a solution is completely tested, I move it to the
> operational database on a different server. With SQLServer 2000, I could
> just open the .adp file and do File->Get External Data->Import to move the
> objects. That does not work with 2005.
> What is the recommended method for moving objects from one database to
> another? Obviously, the objects that changed will not be the same every
> time.
> Thanks!
> Jer

2005 Copy database objects from one server to another

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.
Thanks!
JerJer (jerry.w.black@.saic.com) writes:

Quote:

Originally Posted by

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.

The recommended method is to have all SQL objects under version control
and build all install kits from the version-control system.

At http://www.red-gate.com you can find SQL Compare, which many people
use for this sort of thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jer wrote:

Quote:

Originally Posted by

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.
Thanks!
Jer

A better tool for handling changes via source control is DB Ghost from
Innovartis (http://www.dbghost.com/ggroups) - Red Gate's tools do not
work with source control and thus provide no audit trail whatsoever so
Erland's advice is slightly misleading (sorry Erland, this isn't a
personal attack you just need to understand what Red Gate's tool's
capabilities and limitations are).

Regards,

Malcolm (www.innovartis.co.uk)