Database

Re: 0about update statement

4-Jun-07 23:05

Hi Shajeel

It shouldn't matter which way round you have the where-clause, because SQL-Server (and Oracle) are able to recognise that the index is potentially applicable (because both RDBMS products use a cost-based optimiser). The main criteria as-to whether the index will be used will be how selective the index is (i.e. how many records does SQL-Server think will be returned from the query based on its statistics).

Regards
Andy

0about update statement

Nilesh Mitra4-Jun-07 20:12

Nilesh Mitra

4-Jun-07 20:12

I have faced a problem to write a update statement in SQL server 2005.Is it possible to use the alias name for a table in the update statement? If not then why?

WoutL4-Jun-07 20:55

WoutL

4-Jun-07 20:55

I'me not sure if it is possible in SQL 2005, but it is in SQL 2000. You query should look like this:

<br />
Update tableName<br />
Set ...<br />
From tableName alias ...<br />

Wout Louwers

How to disable and monitor user connection

Bob Vila4-Jun-07 14:09

Bob Vila

4-Jun-07 14:09

Hi all--I tried searching for this, but it was hard to know what to search on...

I have two users, say User and Update. Update comes in once a day and does a bunch of stuff, but I need to make sure it doesn't do anything until any existing Users close their connections (or else data will probably be compromised).

And second, I need to ensure that while Update is doing its thing, all other users will have their connection requests denied. Then after Update is finished, he'll restore connection abilities to the Users.

Can anyone tell me how to accomplish this? Or maybe point me in the right direction?

Thanks a lot,
John

Re: How to disable and monitor user connection

leckey4-Jun-07 17:40

leckey

4-Jun-07 17:40

Does this have to be done during the day or can you set a logoff time and run the Update overnight?

__________________
Bob is my homeboy.

Add only form in access - disable combobox drop

Dan Neely4-Jun-07 9:43

Dan Neely

4-Jun-07 9:43

I have a DataSheet form that has Adds Allowed and Edits Disabled. For cells of existing records that are using comboboxes to select from a lookup table the combobox still drops although the user can't make a new selection and then close it. Is there a setting that will keep the CB from dropping in the same way that no new text can be typed into textfields at all.

--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer

Character Encoding in .DBF

tg20044-Jun-07 9:11

tg2004

4-Jun-07 9:11

Hi.

Just wondering if anyone had any advice.
I am trying to read a text field from a .dbf file, the text field is 'encrypted' by a simple increase in the character code.

ie. 0 becomes n, 1 becomes m, by adding to the (int) value of the character.

I have tried using both an OleDB connection and ODBC connection DataReaders to import the data into a textbox so that the user can see the information before choosing to unscramble back to cleartext.

I have no problem populating the textbox but some characters are not being imported with the same encoding as in the file.

If I open the .DBF in a hex editor then I get :

79 77 77 78 80 7D 77 7F 77 7D 79 79 77 7F 7E

I have added a Diagnostic write to show what is in the text box:

79 77 77 78 C7 7D 77 7F 77 7D 79 79 77 7F 7E

As you can see, the fifth character value (hex) 80 is brought in as (hex) C7.

It seems to do this both when trying the OleDB and ODBC connection types for a DBF file.

Is it possible that I am missing an encoding option in the connection string or in the Data Reader?

Thanks in advance.
Tony.

MSDE Problems

Brendan Vogt4-Jun-07 6:49

Brendan Vogt

4-Jun-07 6:49

Hi,

I have been using SQL Server 2000 for some time now, and I had to install SQL Server 2000 again yesterday. The instance that I created for SQL Server 2000 was MYSERVER\MYINSTANCE. The instance for SQL Server 2005 is MYSERVER.

So I tried to install MSDE that is on the SQL Server 2000 CD, and then I got the following error:

The instance name specified is invalid.

What is this, and how do I correct it?? Can I use SQL Server 2000 and MSDE and SQL Server 2005??

Is there a future for MSDE?? Is there a version that comes with SQL Server 2005??

Regards
ma se

Re: MSDE Problems

Paul Conrad4-Jun-07 7:06

Paul Conrad

4-Jun-07 7:06

ma se wrote:
Is there a future for MSDE?? Is there a version that comes with SQL Server 2005?

It is SQL 2005 Express.

ma se wrote:
Can I use SQL Server 2000 and MSDE and SQL Server 2005?

I think as long as the instance names are different, you can.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

checking the datatype of a column in a table

steve_rm4-Jun-07 6:00

steve_rm

4-Jun-07 6:00

Hello,

I have the following sql statement

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)<br />
<br />
    ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

Re: checking the datatype of a column in a table

Arun.Immanuel4-Jun-07 6:53

Arun.Immanuel

4-Jun-07 6:53

It seems like the the converted values will be in the format "yyyy/mm/dd"
and those which have to be converted will be in the format "dd/mm/yyyy".

So check the records which has 3rd character '/' and then covert those values alone.

In the update clause , include a where condition as follows:

WHERE SUBSTRING([Date],3,1)='/'

Hope that helps you.

Regards,
Arun Kumar.A

Re: checking the datatype of a column in a table

andyharman4-Jun-07 6:58

Veeeeeeeeeery slow queries in MS SQL 2005

4-Jun-07 6:58

For SQl-Server 2000, the following should work:

if exists(select sc.*
        from sysusers su
        inner join sysobjects so
        on so.uid = su.uid
        inner join syscolumns sc
        on sc.id = so.id
        where su.name = 'dbo'
        and so.name = 'OtherCall'
        and so.xtype = 'U'
        and sc.name 'Date'
        and sc.usertype = 2)
begin
    --your update
    --your alter-table
end

It should also work with SQL-Server 2005, but you ought to use the new system-catalog views.

Regards
Andy

3fonov4-Jun-07 3:51

4-Jun-07 3:51

Hello all!
I've got some problem. When i'm executing about 10 stored procedures it's tooks 5 second to complete.
Here is procedures code:

ALTER PROCEDURE [dbo].[update_source]<br />
-- Add the parameters for the stored procedure here<br />
@internal_id int,<br />
@parent_id int,<br />
@sstate tinyint,<br />
@title nvarchar(50),<br />
@rng decimal(4,2),<br />
@lat dbo.latitude,<br />
@lon dbo.longtitude,<br />
@course dbo.angle,<br />
@speed dbo.speed,<br />
@stype xml<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
DECLARE @source_id int;<br />
DECLARE @state int;<br />
<br />
SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id);<br />
IF @source_id IS NULL<br />
BEGIN<br />
INSERT INTO sources<br />
(internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon)<br />
VALUES<br />
(@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000);<br />
<br />
SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id);<br />
END<br />
ELSE<br />
SET @state = (SELECT sstate FROM sources WHERE source_id = @source_id);<br />
UPDATE sources SET<br />
parent_id = @parent_id,<br />
updated = getutcdate(),<br />
sstate = @sstate,<br />
title = @title,<br />
rng = @rng,<br />
course = @course,<br />
speed = @speed,<br />
stype = @stype,<br />
lat = @lat*100000000,<br />
lon = @lon*10000000<br />
WHERE source_id = @source_id;<br />
<br />
<br />
<br />
INSERT INTO source_states<br />
(source_id, sstate, acquired)<br />
VALUES<br />
(@source_id, @sstate, getutcdate());<br />
<br />
END

and i'm execute it like this:

<br />
EXEC update_source 2190074, 5, 1, N'2190074', 0,57.7386783333333,10.57468,0,0,N'';<br />
EXEC update_source 2573335, 5, 1, N'2573335', 0,63.8,9.73333333333333,0,0,N'';<br />
.....<br />
.....<br />
.....<br />
EXEC update_source 2573105, 5, 1, N'2573105', 0,59.6166666666667,10.5166666666667,0,0,N'';<br />
EXEC update_source 2573235, 5, 1, N'2573235', 0,60.4,5.35,0,0,N'';

I think that mistake not in my code but somewhere in server configuration. But i can't find it out.
Thanks a lot!

andyharman4-Jun-07 6:13

4-Jun-07 6:13

Which columns have indexes or primary keys specified? I would expect a clustered primary-key on the "internal_id" column and a unique index (or unique constraint) on the "source_id" column.

Regards
Andy

3fonov4-Jun-07 7:01

4-Jun-07 7:01

Yes it is. And it doesn't depends on table size or data size.
When it's executed in Managment Studio with Client statistics everything is zero.
Thank you for quick reply

andyharman4-Jun-07 7:28

4-Jun-07 7:28

If you run your 10 stored procedure calls from a Management Studio query then how long does it take?

If it takes a long time then I would be looking at a configuration issue (you should be able to comfortably hit between 30-50 calls per second).
o What other indexes do you have on that table?
o Do you have any triggers?
o What else is happening on the database server?
o Are you data files held in a compressed folder?
o Is the transaction log being written to a separate disk?

If it is really quick through the SQL-Server tools then I would be looking at your front-end code.
o Are you using the native SqlClient connection?
o Are you reusing your dbcommand object (using parameters).
o Are you opening a separate connection for each call?
o Is the database on the same server? Do you have network problems?

Hope that helps.
Andy

3fonov4-Jun-07 22:52

4-Jun-07 22:52

Thanks for help but...
I try to run this on clean installation on empty tables and got this in Profiler: Print Screen.
There is no triggers, compressed folders, and so on.
Theres is selected a bit more complicated procedure, but as i think it must not take 3 seconds to run got it indexes or not.
The same routines on MySQL server takes zero time and CPU perfomance.
Thanks for advises.

Pete O'Hanlon4-Jun-07 23:30

Pete O'Hanlon

4-Jun-07 23:30

First of all, is source_id an autoincrement column? If it is, don't reselect it - use the Scope_Identity() function to retrieve it in your insert, e.g.

INSERT INTO sources
(internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon)
VALUES
(@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000);
SET @source_id = SCOPE_IDENTITY()

If it's a uniqueidentifier column, select the GUID yourself and then insert it as part of the insert statement.

Secondly, why are you performing the SELECT @state = select in the update portion? I can't see anywhere that you are using this in your procedure.

Third, you are performing a calculation in your query. I would consider either moving it outside the query, or using a computed column in the table.

Deja View - the feeling that you've seen this post before.

3fonov4-Jun-07 23:51