Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarsko
g.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTest
Some errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest
|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is there something similar to SQL 2005 row_number() in sql mobile?

Hello,

I am wondering if somone could provide me with a sample SELECT for an
auto number query. For example I have a table called People with two columns first_name, and last_name. There isn't a unique id to correspond with the table but would like to dynamically make one during the return of the query.

I know this is possible in the full version of sql 2005 using

SELECT ROW_NUMBER() OVER (ORDER BY last_name), first_name, last_name from People

But within sql mobile is there a way?
So if there was 5 rows in the table it would return

1 John Alpha
2 John Beta
3 John Cat
4 John Delta
5 John Echo

John

No SQL Server Mobile/Everywhere Edition does not support Row_Number() and there is no similar function too :(

Thanks,

Laxmi

is there no mod operator in analysis services ?

I am missing a mod (modulo, %) operator in analysis services.

I need to find out if a number is even or not ?
(to return the value in the middle of a set) - and if the tupples in the set is event then the average of the two in the middle.

HANNES

Hi Hannes,

Unfortunately, the MDX language in AS 2000 lacked a "mod" operator, and I'm not aware of it being added in AS 2005, either. Of course, it would be great if someone could contradict; meanwhile, I use this technique:

A Mod B ==> A - (Int(A/B) * B)

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
ThanksYou could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks|||"Peter" wrote:
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database).
> What is a better way for me to handle this problem ? Should I edit the DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
>> I have created a package that just export a number of tables to an Access
>> Database and it works fine.
>> However, when I rerun the package, I get error message as it cannot
>> create
>> the tables (This is because they are already exists in the Access
>> database).
>> What is a better way for me to handle this problem ? Should I edit the
>> DTS
>> package to remove the "Create Table" step (It involves 23 tables) OR is
>> there any better way to create a package that can be reused ?
>> Thanks
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks
You could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks
|||"Peter" wrote:

> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database).
> What is a better way for me to handle this problem ? Should I edit the DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian
|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>
|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
ThanksYou could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks|||"Peter" wrote:

> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database
).
> What is a better way for me to handle this problem ? Should I edit the DT
S
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
>
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian