Database

Re: I want your idear and solving !!!

23-Feb-09 18:11

Its easiest to use CASE[^]. So your query would be like:

SELECT EmpID, 
       [Date], 
       CASE Reason 
          WHEN 'Fi' THEN 'Fire'
          WHEN 'Rt' THEN 'Retired'
          ...
       END AS Reason
FROM dbo.tblEmpStop

The need to optimize rises from a bad design.My articles[^]

Golden Jing23-Feb-09 21:44

Golden Jing

23-Feb-09 21:44

Thanks your for your help.
But when i run it show message like:

The Query Designer does not support the CASE SQL construct.

But it was work... Smile | :)

Thanks you again..

VB.Net

Cannot use temporary table twice

MarcelloTurnbull23-Feb-09 7:37

23-Feb-09 7:37

I get the next error message from MS SQL 2000:

"There is already an object named '#Teste' in the database."

DECLARE @x int

SET @x = 5

IF @x > 1

SELECT IDField1
INTO #Teste
FROM Table1

ELSE

SELECT IDField2
INTO #Teste
FROM Table1

(...)

The snippet code above is just an example. I cannot use another temporary table name because the following code must reference #Teste only.

Do you know a solution ?

Thanks

Marcello Turnbull

Ennis Ray Lynch, Jr.23-Feb-09 8:08

Ennis Ray Lynch, Jr.

23-Feb-09 8:08

Where is your create and drop statement for the temp table? Last I heard a temp table was supposed to be local to the scope of the procedure and it was an error to not global to the entire db. Also, you can look into the possibility of using the new table variable type in SQL Server.

Need custom software developed? I do C# development and consulting all over the United States.

A man said to the universe:
"Sir I exist!"
"However," replied the universe,
"The fact has not created in me
A sense of obligation."
--Stephen Crane

Wendelius23-Feb-09 8:40

23-Feb-09 8:40

You seem to use a local temporary table. When such table is created it exists until you either:
- disconnect from the database
- drop the table using DROP TABLE command

So now you create the table in your SELECT ... INTO statement and if you don't drop it somewhere later, you encounter that error when you run the code block the second time.

The need to optimize rises from a bad design.My articles[^]

MarcelloTurnbull23-Feb-09 8:46

23-Feb-09 8:46

I agree there is no DROP statement but it is not enough to fix the problem.

Thanks

Marcello Turnbull

Wendelius23-Feb-09 8:49

23-Feb-09 8:49

Don't quite understand. Your problem is that you try to create a table that already exists. So you must either drop it or modify your code so that you don't create the table if it already exists.

The need to optimize rises from a bad design.My articles[^]

MarcelloTurnbull23-Feb-09 9:08

23-Feb-09 9:08

After you answer I added the DROP TABLE in the beginning of the code but it has not worked. I must not modify the code following the snippet.

Thank you

Marcello Turnbull

Wendelius23-Feb-09 9:14

23-Feb-09 9:14

Could you post the whole code? Also do you receive the same error or something else?

The need to optimize rises from a bad design.My articles[^]

MarcelloTurnbull23-Feb-09 9:29

23-Feb-09 9:29

DROP TABLE #Teste

DECLARE @x int
SET @x = 5

IF @x > 1
SELECT IDField1
INTO #Teste
FROM Table1

ELSE

SELECT IDField2
INTO #Teste
FROM Table1

(...)

Buddy, the error message is the same.

Thanks

Marcello Turnbull

Wendelius23-Feb-09 9:47

23-Feb-09 9:47

That's odd. For some reason the parser interpets that the table is going to be created twice (of course that's not gonna happen since you have IF ... ELSE structure).

However, could you use the following:

DROP TABLE #Teste;
CREATE TABLE #Teste (
   IDField1 int
);
DECLARE @x int;
SET @x = 5;
IF (@x > 1)
BEGIN
   INSERT INTO #Teste
   SELECT IDField1
   FROM   Table1;
END;
ELSE 
BEGIN
   INSERT INTO #Teste
   SELECT IDField1
   FROM   Table1;
END;

The need to optimize rises from a bad design.My articles[^]

MarcelloTurnbull25-Feb-09 2:31

25-Feb-09 2:31

Buddy

I apologize for I could not reply yesterday but it was holiday here Wink | ;-)

Snapshots[^]

It worked so thank you very much

Marcello Turnbull

Wendelius25-Feb-09 4:42

SQL 2000 Code/Update Deployment

25-Feb-09 4:42

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

cjb11023-Feb-09 2:23

cjb110

23-Feb-09 2:23

I'm looking for some views/ideas on the following problem that I currently face:

We have a SQL 2000 Application with all the back end code written in SQL, controlled by a VB GUI.
Due to the nature of the app we have fairly frequent code changes to make.

These changes are implemented by a third party, as I do not have access to the production systems.

At the moment I generate lots of .sql files for the various tables, views, sp's etc that are changing and they are just run through QA one at a time.

My problem is finding a more reliable way of deploying these changes, as lots of files means one could be missed etc or ran twice which might present 'non' error errors.

Do people use batch files and the command line? or are third party tools the norm?

Re: SQL 2000 Code/Update Deployment

Wendelius23-Feb-09 8:45

SQL Server 2000: Login failed for user ' ' [modified]

23-Feb-09 8:45

I believe that how changes are gathered varies a lot.

But in your case, why do you separate different modifications to different files instead of gathering all modifications to a single file? That way you could ensure that everything is run (only once) and that the execution order is correct (sometimes this can be an issue for example with foreign keys, triggers etc). This script cold be run using osql.

The need to optimize rises from a bad design.My articles[^]

Nada Adel23-Feb-09 1:07

Nada Adel

23-Feb-09 1:07

I'm trying to connect to my sql 2000 database "mydatabase" using JDBC for SQL Server 2000, but I failed.

I'm using IReport to connect to the database and I added the driver for sql server 2000. I made sure it runs under mixed mode (i.e: windows and sql server authentication)
I created a login user (ASPNET) user and granted permissions to him on the database (without any passwords).

The url that I use for connection is jdbc:microsoft:sqlserver://localhost:1433/mydatabase
username:ASPNET and I don't enter any passwords.

error: Login failed for ASPNET. The user is not associated with a trusted sql server connection.

When I don't enter any username or password, he tries to connect using my computer's credentials but also fails.
I don't know what's wrong.

I tried everything but I failed to connect to it.
I even installed sql server 2000 sp3 on the computer but to no avail.

Am I missing something?
How am I supposed to connect to the database?

sorry for taking so long..I hope someone could help me out.

modified on Monday, February 23, 2009 7:27 AM

Rob Philpott23-Feb-09 1:37

Rob Philpott

23-Feb-09 1:37

I think what you're seeing here is a trusted connection issue. You're not connecting to Sql Server using a username and password but instead using your windows account. You'll need to add the domain account as a user to SqlServer.

Also, its worth just making sure your account isn't locked (3 wrong log-ins etc) as this produces exactly what you describe.

Regards,
Rob Philpott.

Nada Adel23-Feb-09 2:33

Nada Adel

23-Feb-09 2:33

ok, I've just created a login user with my computer name and its domain and I specified the authentication as Windows Authentication coz otherwise a password will be required and I set the default database as "mydatabase".
This time the error is: cannot establish connection. Cannot open database requested by login. Login failed for user domain/username

Should I restart sql server or what?

Rob Philpott23-Feb-09 2:57

Rob Philpott

23-Feb-09 2:57

I think its your domain and the logged on account you want to add, not the computer name. You'll then need to add that user to the Sql Server so they can access the server, and add the permission to the database you're using as well.

Regards,
Rob Philpott.