sqlserverdoubt - Database Discussion Boards

Re: SQL Case problem

si_695-Jun-07 4:36

si_69

5-Jun-07 4:36

DOH !!!

select mydatetime,
CASE
WHEN datepart(hour,mydatetime) > 11 THEN 'PM'
ELSE 'AM'
as exp1
from mytable

Re: SQL Case problem

Krish - KP5-Jun-07 17:34

Krish - KP

5-Jun-07 17:34

CASE statemet to be terminated with END
which is missing in your select query

Regards
KP

Re: SQL Case problem

Ilya Verbitskiy5-Jun-07 4:42

Ilya Verbitskiy

5-Jun-07 4:42

Hi,

Try this code:

select mydatetime,
'exp1' =
case
when datepart(hour,mydatetime) > 11 THEN 'PM'
else 'AM'
end
from mytable

Reporting Services Multilingual ?

ArunkumarSundaravelu5-Jun-07 3:08

ArunkumarSundaravelu

5-Jun-07 3:08

Hi
We are using reporting service,
How to change the reporting services column header,headers...etc
into different language, in c# we use resource file and use this
resource file to change different language(multilingual Option).
How can change these into reporting services.

Regards
Arunkumar Sundaravelu

Error in executing stored procedure:not a valid identifier

Kusumasrinivas5-Jun-07 0:24

Kusumasrinivas

5-Jun-07 0:24

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @sqlstr as varchar(max)

set @sqlstr='SELECT Site.siteid as siteid,'

set @sqlstr=@sqlstr+ 'Site.Sitename as sitename, '

set @sqlstr= @sqlstr+ 'Customer.customerid,'

set @sqlstr= @sqlstr+ 'Customer.customername as CustomerName,'

set @sqlstr= @sqlstr+ 'Site.City as City,'

set @sqlstr= @sqlstr+ 'site.Address as Address,'

set @sqlstr =@sqlstr+ 'Site.state , '

set @sqlstr= @sqlstr+ 'Country.countryid as countryid,'

set @sqlstr= @sqlstr+ 'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @sqlstr= @sqlstr+ 'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @sqlstr= @sqlstr+ 'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @sqlstr= @sqlstr+ 'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @sqlstr= @sqlstr+ 'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @sqlstr= @sqlstr+@whereClause

--

--set @sqlstr=@sqlstr+' WHERE GSUStatus.GSUStatusID=' +@GSUStatusID

--if @BusinessUnitID <> 0

--set @sqlstr=@sqlstr+'and site.BusinessUnitID ='+@BusinessUnitID

--if @CountryID <> 0

--set @sqlstr=@sqlstr+'and site.countryid='+@CountryID

--if @CustomerID <> 0

--set @sqlstr=@sqlstr+'and site.customerid='+@CustomerID

--if @SystemTypeID <> 0

--set @sqlstr=@sqlstr+'and site.SystemTypeID='+@SystemTypeID

--if @SiteName <> ''

--set @sqlstr=@sqlstr+'and site.Sitename like ' + @SiteName

--if @Address <> ''

--set @sqlstr=@sqlstr+'site.Address like '+ @Address

--if @City <> ''

--set @sqlstr=@sqlstr+'site.City like '+ @City

--if @State <> ''

--set @sqlstr=@sqlstr+'and site.state like '+ @State

print @sqlstr

exec @sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Re: Error in executing stored procedure:not a valid identifier

andyharman5-Jun-07 0:59

andyharman

5-Jun-07 0:59

Try:
exec(@sqlstr)

Re: Error in executing stored procedure:not a valid identifier

P_pk9-Jun-07 0:15

P_pk

9-Jun-07 0:15

may be you have definied only one parameter and in the execution statement you have give two parameters

PPK

sqlserverdoubt

saravanan054-Jun-07 23:40

saravanan05

4-Jun-07 23:40

Hi fiends

i have login table in sqlserver2000.

1000 data include in that tabe

now i change the system.create login table.

how can i include all the data in easy way..

can u help me

saravanan

Re: sqlserverdoubt

Krish - KP5-Jun-07 17:25

Krish - KP

5-Jun-07 17:25

I'm not getting you.
is that you are trying to copy rows from one table to another.

Regards
KP

index order

Shajeel4-Jun-07 21:24

Shajeel

4-Jun-07 21:24

hi
i have created the index in order col1, col2, now if in my application or on query analyzer i write the script

select * from table1 where col2 = 'value 2' and col1 = 'value 1'

will the index be used or not as order is changed in my srcipt. i want to know about both sql server and oracle.

Regards
Shajeel

Re: index order

Pete O'Hanlon4-Jun-07 22:25

Pete O'Hanlon

4-Jun-07 22:25

This sounds suspiciously like homework to me, but I'll answer it anyway.

If you have created two indexes, then they will most likely be used. Note that the usage depends entirely on the query execution plan that the DB server determines is the best usage. Suppose that there are only 2 possible values in col2, then the engine may determine that it needs to do a sequential scan because there is not enough differentiation in the data to allow it to perform an indexed scan. Similarly, if there are only a few rows in the table then it may perform a sequential scan because the engine has determined that the row count is too few to allow it to index search efficiently.

As always, the best way to find out the answer is to try it out yourself.

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

Re: index order

Shajeel4-Jun-07 22:48

Shajeel

4-Jun-07 22:48

i think i explained it wrong i have not created two indexes, i have created only one index with two columns like

CREATE UNIQUE INDEX Index1
ON table1 (col1, col2)
so in order col1 is first and col2 is second. in where i used
where col2 = 'val 2' and col1 = 'val 1' i.e. filter in wrong order.

Regards
Shajeel

Re: index order

andyharman4-Jun-07 23:05

andyharman

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?

Re: 0about update statement

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

andyharman

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

Veeeeeeeeeery slow queries in MS SQL 2005

3fonov4-Jun-07 3:51

3fonov

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!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.