Problem with locking in Selects

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

Regards

Bjrn(bjornsuneandersen@.gmail.com) writes:

Quote:

Originally Posted by

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.
>
The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.
>
I have also checked if there are any locks holding the other SPs back
- there isn't any.
>
So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.

--
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

Problem with Link server from sql 2005 to sql 2005 - Openquery doesnt works

I have created a linked server on which following query works fine.

EXECUTE ('SELECT TOP 10 * FROM dummyOBJECTS') AT [REMOTE]

but the same statement executed with openquery

select * from openquery([remote],'select top 10 * from dummyObjects') returns following error.

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "remote" supplied inconsistent metadata for a column. The column "dummyObjectID" (compile-time ordinal 1) of object "select top 10 * from dummyobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Hi ck!

Could you remove all columns except dummyObjectID and run this again?

If this reproes, could you reply with a sequence of "CREATE TABLE" and "INSERT" statements that will allow me to repro this on my machine?

Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary
> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>
|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>
|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>
|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>

Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance this
>>sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
>> My example stored procedure and plain sql are totally equivilent - one
>> works the other does not.
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Dan,
>> you are not making sense to me. % means "any string of zero or more
>> characters". WHERE branch LIKE '%%' is equivilent to not having the
>> WHERE clause at all. % is not a "wild card character." My example
>> stored procedure and plain sql are totally equivilent - one works the
>> other does not.
>> Gary
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>>
>

problem with like [^]

SET NOCOUNT ON
declare @.tab table(vc varchar(2000))
insert into @.tab select '10.1'
insert into @.tab select '10.1.1'
insert into @.tab select '10.1.2'
insert into @.tab select '10.1.1.1'
insert into @.tab select '10.1.1.1.1'
The problem is that i need to select only 10.1.1 and 10.1.2 when i put input
as 10.1
If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
I tried this but not working
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'try this
SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"aneeshattingal" wrote:

> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>
>|||aneeshattingal wrote:
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
Drop the '%' from your query:
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]'|||Sorry.. that won't work for 10.1.12.. Wrong solution.
You will have to do it the long way :)
SELECT * FROM @.Tab WHERE
vc not like '10.1.%[.]%'
and vc like '10.1.%'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> try this
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "aneeshattingal" wrote:
>|||Thank guys , both worked ..
"aneeshattingal" <aneeshattingal@.hotmail.com> wrote in message
news:u0zMeYkjGHA.3408@.TK2MSFTNGP05.phx.gbl...
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put
> input as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>

problem with japanese character 俱

Hi All,

Create a table , and put in some row 子ど and 俱

and fire a query something like SELECT * FROM mytable WHERE (myColumn = '子ど')

it gives me right result.

But if i fire

SELECT * FROM mytable WHERE (myColumn = '俱')

It does not return any result for the same even if myColumn has '俱'.

Surprisnly if i fire query like "SELECT * FROM mytable" it

correctly dispalys 俱.

What's the reason for the same ? Why does it is not able to search me on this japanese character(俱).Collation is Japanese_Unicode_CI_AS

Regards,

Sunil

Hi Sunil,

you wil have to indicate that the string is unicode in your query:

SELECT * FROM mytable WHERE (myColumn = N'俱')

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens for your quick response.

I have one query why it was working without N in 子ど but not in 俱

Regards,
Sunil

problem with japanese character 俱

Hi All,
Create a table , and put in some row Xど and 俱
and fire a query something like SELECT * FROM mytable WHERE
(myColumn = 'Xど')
it gives me right result.
But if i fire
SELECT * FROM mytable WHERE (myColumn = '俱')
It does not return any result for the same even if myColumn has '俱'.
Surprisnly if i fire query like "SELECT * FROM mytable" it
correctly dispalys 俱.
What's the reason for the same ? Why does it is not able to search me on
this japanese character(俱).Collation is Japanese_Unicode_CI_AS
Regards,
Sunil
Please don't multi-post. If you really need to post in multiple
newsgroups, you should cross-post (post only one time, but with both
newsgroups in the To: field).
I've responded to this question in the
microsoft.public.sqlserver.programming newsgroup.
Razvan

problem with images

I have a problem with images being displayed in a report when I select the
image source coming from the web.
The URL I am specifing is
http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
In the layout i can see the image, in the preview i cannot nor in the
deployed report
when i navigate to this page in a browser the image is shown ok
http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
I have downloaded service pack 1 which is supposed to resolve this problem
Can anyone help?
Thanks
RichardWell after much searching through the net for similar problems I finally
found the answer.
My page
http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
returns a progressive jpeg with the content type image/pjpeg
Report services cannot handle this content type. As soon as I changed it to
image/jpeg all works well
Microsoft - This is a bug and it took me at least 8 hours of stress and hair
pulling!
Thanks
Richard
"Richard Wilde" <XXXXinfo@.rippo.co.ukXXXX> wrote in message
news:OJ9IOykHFHA.3628@.TK2MSFTNGP15.phx.gbl...
> I have a problem with images being displayed in a report when I select the
> image source coming from the web.
> The URL I am specifing is
> http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
> In the layout i can see the image, in the preview i cannot nor in the
> deployed report
> when i navigate to this page in a browser the image is shown ok
> http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
> I have downloaded service pack 1 which is supposed to resolve this problem
> Can anyone help?
> Thanks
> Richard
>

problem with If Exist select

Have patience, I'm just a script kiddie. I'm trying to write a
vbsscript that queries a sql 2005 database to see if a record exists
and if so update some values and if it doesn't then insert an entry. On
the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
getting the following error:
Char: 11
Error: Syntax error
Code: 800a03ea
Can anyone tell me what I'm doing wrong.
Thanks.
Set objCon = CreateObject("adodb.connection")
objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
uid=something; pwd=something")
on error resume Next
If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
& ")"
Else
strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
& _
"values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
strProc & ", " & strMem & ")"
End If
if err.number <> 0 then
msgbox err.description
end if
on error resume next
objCon.Execute(strSQL)
if err.number <> 0 then
msgbox err.description
end if
objCon.Closemcgrew.michael@.gmail.com wrote:
> Have patience, I'm just a script kiddie. I'm trying to write a
> vbsscript that queries a sql 2005 database to see if a record exists
> and if so update some values and if it doesn't then insert an entry. On
> the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
> getting the following error:
> Char: 11
> Error: Syntax error
> Code: 800a03ea
> Can anyone tell me what I'm doing wrong.
> Thanks.
>
> Set objCon = CreateObject("adodb.connection")
> objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
> uid=something; pwd=something")
> on error resume Next
> If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
> & ")"
> Else
> strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
> & _
> "values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
> strProc & ", " & strMem & ")"
>
instead of using if exits (select...........)
u should open a recordset of this SQL query and check condition....
let say if Rst as recorset..
then ur statement should be like this
if Rst.eof then
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
'" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
strMem
& ")"
else
....|||Better yet, put stored procedures in your database and call them from VB.
Pass criteria using the parameters objects. Dynamic SQL can get you into
all kinds of problems.
Also, you can include all of this logic in a single stored procedure and
make one call to the database, rather than having the VB app checking all of
this logic on the client. You need to look at the type of logic you need to
enforce and determine whether to do this on the client app or the database,
but it is worth considering.
SQL Injection and Parameters:
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.sqlservercentral.com/col...qlinjection.asp
Dynamic SQL in stored procedures:
http://www.sommarskog.se/dynamic_sql.html
"SQL-Star (Rajeev Shukla)" <dreams.alot@.gmail.com> wrote in message
news:1145465365.635163.158830@.e56g2000cwe.googlegroups.com...
> mcgrew.michael@.gmail.com wrote:
> instead of using if exits (select...........)
> u should open a recordset of this SQL query and check condition....
> let say if Rst as recorset..
> then ur statement should be like this
> if Rst.eof then
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
> '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
> strMem
> & ")"
> else
> ....
>

Problem with Grouping

Hi,
I have 2 tables from which I need to get 2 figures:
1. Divide the Total of Table2 by the Total of Table1 and multiple by 10 as
in the top select and this yields the correct results.
2. Display the Amount using the same formula as above per each Id and here
is where I fail....when I sum theAmount returned from this result set I do
not get the number I get from the first result set which
is -2.4129432084474347 and is correct.
-- This select yields the correct result
select
sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
full join Table2 on Table1.id = Table2.id
order by 1
-- The Amount grouped per each Id seems incorrect
select
Table1.Id
,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
inner join Table2 on Table1.id = Table2.id
group by Table1.Id
order by 1
/*
create table Table1 (Id int, Amount float)
create table Table2 (Id int, Amount float)
insert Table1 select 0, 2466940.7630629078
insert Table1 select 1, 1619341.4993436863
insert Table1 select 2, 294424.12812010606
insert Table1 select 3, 35224.9308604404
insert Table1 select 4, 5816.581840630568
insert Table1 select 5, 9909.2411025063448
insert Table1 select 6, 552720.34837997227
insert Table1 select 7, 20845.780113921814
insert Table1 select 8, 249466.69869616581
insert Table1 select 9, 223489.19351831
insert Table2 select 0, -26748.78485354947
insert Table2 select 1, -444083.44694001391
insert Table2 select 2, -190871.26271638702
insert Table2 select 3, -62978.717071003601
insert Table2 select 4, -4810.138640776684
insert Table2 select 5, -9915.2079038903303
insert Table2 select 6, -305657.70221188507
insert Table2 select 7, -17519.425886078694
insert Table2 select 8, -189198.31576409994
insert Table2 select 9, -70070.519106139123
--DELETE FROM Table1
--DELETE FROM Table2
--drop table Table1
--drop table Table2
*/The behavior you see is correct. The problem is mathematical.
Lets look at a bit of algebra. Your first query creates two sums and
then performs division on the two resuts. This is the equivelent to
the algebraic equation:
(a + b + c) / (x + y +z)
The second query pairs numbers from each set, performs division, then
sums the results:
(a / x) + (b / y) + (c / z)
These are NOT EQUIVELENT to each other. Plug in some numbers.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 16:50:53 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>Hi,
>I have 2 tables from which I need to get 2 figures:
>1. Divide the Total of Table2 by the Total of Table1 and multiple by 10 as
>in the top select and this yields the correct results.
>2. Display the Amount using the same formula as above per each Id and here
>is where I fail....when I sum theAmount returned from this result set I do
>not get the number I get from the first result set which
>is -2.4129432084474347 and is correct.
>
>-- This select yields the correct result
>select
> sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
>from Table1
>full join Table2 on Table1.id = Table2.id
>order by 1
>-- The Amount grouped per each Id seems incorrect
>select
> Table1.Id
> ,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
>from Table1
>inner join Table2 on Table1.id = Table2.id
>group by Table1.Id
>order by 1
>
>/*
>create table Table1 (Id int, Amount float)
>create table Table2 (Id int, Amount float)
>insert Table1 select 0, 2466940.7630629078
>insert Table1 select 1, 1619341.4993436863
>insert Table1 select 2, 294424.12812010606
>insert Table1 select 3, 35224.9308604404
>insert Table1 select 4, 5816.581840630568
>insert Table1 select 5, 9909.2411025063448
>insert Table1 select 6, 552720.34837997227
>insert Table1 select 7, 20845.780113921814
>insert Table1 select 8, 249466.69869616581
>insert Table1 select 9, 223489.19351831
>insert Table2 select 0, -26748.78485354947
>insert Table2 select 1, -444083.44694001391
>insert Table2 select 2, -190871.26271638702
>insert Table2 select 3, -62978.717071003601
>insert Table2 select 4, -4810.138640776684
>insert Table2 select 5, -9915.2079038903303
>insert Table2 select 6, -305657.70221188507
>insert Table2 select 7, -17519.425886078694
>insert Table2 select 8, -189198.31576409994
>insert Table2 select 9, -70070.519106139123
>--DELETE FROM Table1
>--DELETE FROM Table2
>--drop table Table1
>--drop table Table2
>*/
>|||Well,
The reason is what Roy said. But if you want that total sum too,
then you can try this query. The column with the ID null will have the total
u wanted.Hope this helps.
select
Table1.Id
,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
inner join Table2 on Table1.id = Table2.id
group by Table1.Id
with rollup
order by 1|||Thanks, I see.
Any way to achieve what I need?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
> The behavior you see is correct. The problem is mathematical.
> Lets look at a bit of algebra. Your first query creates two sums and
> then performs division on the two resuts. This is the equivelent to
> the algebraic equation:
> (a + b + c) / (x + y +z)
> The second query pairs numbers from each set, performs division, then
> sums the results:
> (a / x) + (b / y) + (c / z)
> These are NOT EQUIVELENT to each other. Plug in some numbers.
> Roy Harvey
> Beacon Falls, CT
> On Thu, 27 Apr 2006 16:50:53 +0200, "yan" <yanive@.rediffmail.com>
> wrote:
>|||I have no idea what you need, as the only information provided is
contradictory.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 21:34:49 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>Thanks, I see.
>Any way to achieve what I need?
>
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
>|||What is it that you need?
What do these numbers represent and what is it you are trying to calculate?
You could do several things, each one is going to give you different
numbers:
Post a more complete explanation of what you need, along with DDL, sample
data, and desired results, and we will be able to help you.
For an explanation of what I am talking about :
http://www.aspfaq.com/etiquette.asp?id=5006
"yan" <yanive@.rediffmail.com> wrote in message
news:uzq9ihiaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> Thanks, I see.
> Any way to achieve what I need?
>
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
as
here
>|||The 2 tables are actually temp tables calculated as part of a report and
contain Totals.
The Id column represnets groups along with each groups amount n the Amount
column.
I need to show the great total (which is -2.4129432084474347) from these
tables which is the formula given in my first post (Table2/Table1*10) and
which yields a correct figure and also the result per each group using the
same formula.
If I run the following select I get the numbers bellow which when I sum I
get a differnrt number than the great total, this is what I had doubts
about :
select Table1.id, Table2.Amount / Table1.Amount *10 AS Amount
from Table1 inner join Table2 on Table1.id = Table2.id
order by 1
Id, Amount
--
0 -0.10842897103187299
1 -2.7423705692715177
2 -6.4828675535221034
3 -17.879017937756206
4 -8.2696999244065026
5 -10.006021451413144
6 -5.530060601310776
7 -8.4043033123900095
8 -7.5841110959074793
9 -3.1352978639836735
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:gp3252d2pdhahoriu1mlqpt43krt7bpbeg@.
4ax.com...
>I have no idea what you need, as the only information provided is
> contradictory.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 21:34:49 +0200, "yan" <yanive@.rediffmail.com>
> wrote:
>|||As Roy stated in his first post, these are mathematically different
calculations.
10 / 2 = 5
20 / 10 = 2
50 / 5 = 10
(10+20+50) / (2+10+5) does not equal (5+2+10)
80 / 17 = 4.7
You cannot do it both ways and get the same answer. Explain the numbers,
what they mean individually, why you are deviding one by the other, and what
the final numbers are supposed to represent.
"yan" <yanive@.rediffmail.com> wrote in message
news:eJCpuziaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> The 2 tables are actually temp tables calculated as part of a report and
> contain Totals.
> The Id column represnets groups along with each groups amount n the Amount
> column.
> I need to show the great total (which is -2.4129432084474347) from these
> tables which is the formula given in my first post (Table2/Table1*10) and
> which yields a correct figure and also the result per each group using the
> same formula.
> If I run the following select I get the numbers bellow which when I sum I
> get a differnrt number than the great total, this is what I had doubts
> about :
> select Table1.id, Table2.Amount / Table1.Amount *10 AS Amount
> from Table1 inner join Table2 on Table1.id = Table2.id
> order by 1
> Id, Amount
> --
> 0 -0.10842897103187299
> 1 -2.7423705692715177
> 2 -6.4828675535221034
> 3 -17.879017937756206
> 4 -8.2696999244065026
> 5 -10.006021451413144
> 6 -5.530060601310776
> 7 -8.4043033123900095
> 8 -7.5841110959074793
> 9 -3.1352978639836735
>
> --
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:gp3252d2pdhahoriu1mlqpt43krt7bpbeg@.
4ax.com...
10
I
>|||Thank you, you both pointed out my mistake. I knwo what I have to do from
here on.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:er4k94iaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> As Roy stated in his first post, these are mathematically different
> calculations.
> 10 / 2 = 5
> 20 / 10 = 2
> 50 / 5 = 10
> (10+20+50) / (2+10+5) does not equal (5+2+10)
> 80 / 17 = 4.7
> You cannot do it both ways and get the same answer. Explain the numbers,
> what they mean individually, why you are deviding one by the other, and
> what
> the final numbers are supposed to represent.
>
> "yan" <yanive@.rediffmail.com> wrote in message
> news:eJCpuziaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> 10
> I
>|||On Thu, 27 Apr 2006 22:07:22 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>I need to show the great total (which is -2.4129432084474347) from these
>tables which is the formula given in my first post (Table2/Table1*10) and
>which yields a correct figure
You have already demonstrated that you can calculate that number.

> and also the result per each group using the
>same formula.
And you can calculate that number for each group.

>If I run the following select I get the numbers bellow which when I sum I
>get a differnrt number than the great total, this is what I had doubts
>about :
"when I sum...". Adding up all those numbers is meaningless. If you
need the number that results from calculating based on all the rows
together, generate that number in a different SELECT.
Roy

Problem with GROUP BY/COMPUTE : error message 8120

Hi,
I have this query ( it is Ok with Sybase SQLServer)
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
I want to migrate it under MS SQLServer 2000, but I have this error message
:
Serveur : Msg 8120, Niveau 16, tat 1, Ligne 1
Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
If I delete the last line, the query is Ok :
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
The problem is with COMPUTE claude, but I can't understand why.
please help me to solve this.
thanks in advance
regards
LaurentWhat kind of error or warning appears when you drop that line? Apparently
syntax for COMPUTE is fine.
"Laurent CLAUDEL" wrote:

> Hi,
> I have this query ( it is Ok with Sybase SQLServer)
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
>
> I want to migrate it under MS SQLServer 2000, but I have this error messag
e
> :
> Serveur : Msg 8120, Niveau 16, état 1, Ligne 1
> Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> If I delete the last line, the query is Ok :
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> The problem is with COMPUTE claude, but I can't understand why.
> please help me to solve this.
> thanks in advance
> regards
> Laurent
>
>|||if I drop the last line (COMPUTE), there is no more error.
"Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
> What kind of error or warning appears when you drop that line? Apparently
> syntax for COMPUTE is fine.
> "Laurent CLAUDEL" wrote:
>|||But I want a sum by Year, so i have to keep the COMPUTE clause
"Laurent CLAUDEL" <laurent.claudel@.steria.com> a crit dans le message de
news: OhOqg8Y1FHA.1108@.TK2MSFTNGP14.phx.gbl...
> if I drop the last line (COMPUTE), there is no more error.
> "Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
> B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
>|||I suggest you don't use COMPUTE / COMPUTE BY unless it's essential to
maintain Sybase compatibility. COMPUTE is legacy stuff that was
deprecated long ago. Take a look at CUBE / ROLLUP in Books Online -
it's a much more powerful feature.
David Portas
SQL Server MVP
--sql

Problem with GROUP BY syntax and expression

I am really struggling with this code and would appreciate knowing how
to group by the expression (constants) in the SELECT clause:
DECLARE @.LO INT
DECLARE @.HI INT
DECLARE @.StartDate varchar(10)
DECLARE @.EndDate varchar(10)
SELECT @.StartDate = '01/01/2005'
SELECT @.EndDate = '06/30/2005'
SELECT @.LO = 250
SELECT @.HI = 333
SELECT
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDate
, JumboAmount = SUM(JumboAmount)
, JumboMortgages = SUM(JumboMortgages)
, JumboFIXMortgages = SUM(JumboFIXMortgages)
, JumboFIXAmount = SUM(JumboFIXAmount)
, JumboARMMortgages = SUM(JumboARMMortgages)
, JumboARMAmount = SUM(JumboARMAmount)
FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDateRemove the aliases from the GROUP BY.
GROUP BY
StateCD
,CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
,'JumboRange:' + Convert(varchar(4),@.LO) + '-' + Convert(varchar(4),@.HI)
,@.StartDate
,@.EndDate
"JJA" <johna@.cbmiweb.com> wrote in message
news:1123780031.845579.256660@.o13g2000cwo.googlegroups.com...
> I am really struggling with this code and would appreciate knowing how
> to group by the expression (constants) in the SELECT clause:
> DECLARE @.LO INT
> DECLARE @.HI INT
> DECLARE @.StartDate varchar(10)
> DECLARE @.EndDate varchar(10)
> SELECT @.StartDate = '01/01/2005'
> SELECT @.EndDate = '06/30/2005'
> SELECT @.LO = 250
> SELECT @.HI = 333
> SELECT
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
> , JumboAmount = SUM(JumboAmount)
> , JumboMortgages = SUM(JumboMortgages)
> , JumboFIXMortgages = SUM(JumboFIXMortgages)
> , JumboFIXAmount = SUM(JumboFIXAmount)
> , JumboARMMortgages = SUM(JumboARMMortgages)
> , JumboARMAmount = SUM(JumboARMAmount)
> FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
> ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
> Z.ZipCode
> GROUP BY
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
>|||I would use a table expression, like this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
select t.*, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
drop table t
Besides, what's the point of grouping by both CountyCD and CountyName?
If you group by only by StateCD and CountyCD, the query might run much
faster. The rest columns could be retrieved after grouping by, like
this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
create table s(i int, sname char(5))
insert into s values(1,'One')
insert into s values(2,'Two')
select s.sname, t.sumd, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
join s on s.i=t.i
sname sumd Criteria
-- -- --
One 3.0000 Some text here
Two 7.0000 Some text here
drop table t
drop table s|||Thank you so much. Great idea...I now have it working per your
suggestion.

problem with group by and maximum

Hi guys,
i have the following table:

FIELD1 - FIELD2 - FIELD3
1 --- A --- 23
1 --- B --- 77 <<< i want to select this row
2 --- C --- 12
2 --- D --- 99 <<< and this one
2 --- E --- 17
3 ...

I need to select FIELD1 and FIELD2 where FIELD3 is a maximum of the group grouped by FIELD1!

If i leave out FIELD2 the following query works:
select FIELD1, max(FIELD3)
from TABLE
group by FIELD1

But i dont know how to get FIELD2 selected as well.

Any ideas?

Thanks in advance.
AlexSELECT *
FROM YourTable A
WHERE EXISTS
(
SELECT Date, Time, max(rev#)
FROM YourTable B
GROUP BY Date, Time
HAVING A.Date = B.Date AND A.Time = B.Time AND A.rev# = B.max(rev#))sql

problem with group by

hi im new in sql and i wonder if somone can help me here

im trying to make a select statmant on the northwind database and im trying to do group by at the end

this is my query

select * from employees

select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
group by 'senior'

whay cant i group somthing that i have aliased is there another whay

thk

Hi,

When you perform a group by, each column from the select clause must be:

included in the group by clause|||

Hi,

you will either have to repeat the whole expresion:

select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
group by e.lastname + ' ' + e.firstname,
e2.lastname + ' ' + e2.firstname

or use a subquery:

SELECT senior, officer
FROM
(
select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
) Subquery
group by senior,Officer
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Indeed you can use a subquery as Jens mentioned.
Just make sure that all the columns are or defined with an aggregate function or added in the group by clause.

So, for the second sample of Jens, you need to add officer to the group by clause or define it with an aggregate function:
SELECT senior, MAX(officer)
FROM
(
select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
) Subquery
group by senior,Officer

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

When Road is straight forward why need to get down by Road. It will be a faster because it is not using SubQuery.

SELECT
Senior = E.LastName + ' ' + E.FirstName,
Officer = E2.LastName + ' ' + E2.FirstName
FROM
Employees E
INNER JOIN Employees E2
ON E.ReportsTo = E2.EmployeeID
GROUP BY
(E.LastName + ' ' + E.FirstName),
(E2.LastName + ' ' + E2.FirstName)

|||

Because this was a way to show him how to use the aliases anyway.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Column aliases in the outer most query can only be used in the ORDER BY clause and it is returned to the client as part of the metadata. It cannot be used in other clauses (WHERE, GROUP BY or HAVING). This is according to ANSI SQL specifications. So you can either repeat the

Problem with Group By

Hi there,

I am a novice SQL Server Programmer. I have got a task to select multiple fields from multiple tables...I have successfully used joins to get the result. but I have got 2 questions

1. Is there any way of combining (concatenating) three strings as one in the query using "AS"

2. The final result had to be GROUPED by one particular field I am using in select statement...But, for every field in select statement, it lets me run the query without error, only if the field is included Group By clause.

Is there any way to avoid it?

yes, you can concatenate multiple strings into a single field with as...

select (f1+ f2 + f3) as singleString

and you to include any fields in the query in the group by clause...just put the field you are concerned with first. -- jp

|||

hi jp

Thanks for your reply...

your first answer fetched me correct results but regarding the Group By, I still have the problem :(

|||when you use group by you have to include all the fields from the query statement in the group by clause...|||

thank you jp..............

problem with group by

hello all,

i am using odbc connection and it works fine, but i'm having trouble with select statement using group by. i want to display selected fields from 4 different table and it will display by grouping events.t_id.

this my scripts

$query = " select events.t_id, events.e_status, events.e_assignedto, events.e_id, ";
$query .= " events.e_timestamp, tmpeid.e_id, category.c_name, ";
$query .= " ticket.t_summary, ticket.t_category, ";
$query .= " ticket.t_user, ticket.t_priority, ticket.t_timestamp_opened, ";
$query .= " ticket.t_id2, ticket.t_id, COUNT (*)";
$query .= " FROM events, tmpeid, category, ticket ";
$query .= " WHERE ticket.t_id = events.t_id ";
$query .= " AND events.e_id=tmpeid.e_id";
$query .= " GROUP BY events.t_id";
$query .= " HAVING COUNT(events.t_id) >= 1 ";

this is error msg that i've found

Warning: SQL error: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression , SQL state S1000 in SQLExecDirect in c:\apache\htdocs\scripts

can anybody solve for me???Hello,

the problem is, that you use GROUP BY AND COUNT and do not specifiy what Oracle has to do with all the other fields in your SELECT statement.
f.e

SELECT grade FROM scott.salgrade GROUP BY grade (works fine)

SELECT grade, COUNT(losal) FROM scott.salgrade GROUP BY grade
(also works fine, cause grade will be grouped and in every grouped record you will get a count of losal)

SELECT grade, COUNR(losal), hisal FROM scott.salgrade GROUP BY grade

(will raise an exception - cause Oracle does not know what to do with hisal in the grouped record)

a

SELECT grade, COUNR(losal), SUM(hisal) FROM scott.salgrade GROUP BY grade

(works also fine)

So ... what you have to do is to kick out all the fields that has no group or agregate command and run the statement again.

or ...

group every field in the list

Hope this helps

Manfred Peter
(Alligator Company)
http://www.alligatorsql.comsql

Problem with Group By

I have the following query and it's result is not according to my expectation.

SELECT PaymentTerms, VendorCode , count(HeaderId) FROM Tbl_FPOHeader
GROUP BY PaymentTerms, VendorCode

Both PaymentTerms, VendorCode are NVarchar(50) and HeaderId is int type.

I want the result to be sorted by PaymentTerms, but the result is Vendorcode.

If i use Order By PaymentTerms it is working fine, but I would like to get by using group by only.

Please help.

Thank you.

HI,

data in entities is just an unordered set of data unless you use an order statement. So you will have to use an order by clause. if not you might or might not get the results (by accident :-) ) in the prefered order.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem with GETDATE()

Hello All,

I have a problem as follows

if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.

I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.

could anybody let me know what is correct. Thanks for your help in advance.

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

even then i get the same date.

What are you trying to achieve? The amount of time it takes to run multiple Select GetDate() is very minor. We would be able to help you better if we knew what your goal was.

|||

Hi, mate

I just executed:

SELECTGETDATE()SELECT *FROM Table1SELECTGETDATE()

and the the two dates was different. (Table1 has 120 000 rows)

This means that the query is executing too fast (in less than a millisecond) and that is why you receive the same results.

|||

yeah... if u execute query select getdate() several times one after another u cant understand the difference of milliseconds. don't worry...

|||

Hi Diamsorn,

Thanks for the reply. but all i am trying to do was i have a history table and i have included modified date as a part of primary key and when i am trying to update my main table i am inserting a record into history table. eventhough i am doing it in different time system says it is a violation of primary key.

For eg. Table1 is having below columns

Column1 Column2 Column3 and Suppose Primary key is composite key of column1 and column2

I have HistoryTable having columns

Column1 Column2 modifieddate and Suppose Primary key is composite key of Column1,Column2 and Modifieddate. but when i am trying to update the table1, and though trigger i am capturing getdate() to fill modifieddate, then as it is not different it is giving error.

how to overcome this problem?

Gneralproblem

|||

Which table is giving the primary key violation error? Table1 or HistoryTable.

What is your purpose of having a composite primary key in your history table of column1, column2, and modified date?

I would move away from using a trigger to insert into your history table, and do your update/insert inside of a transaction in a stored procedure. Triggers are a maintenance nightmare and I avoid them personally at all costs.

|||

Hi Diamsorn,

History table is giving me error. As i have to update the same record in Table1 and track the changes in HistoryTable. As my operation is so fast and as it is caputring same date it is giving primary key violation.

I would appreciate if any way to handle this problem using Triggers.

Thanks,

GeneralProblem

Problem with FOR XML clause

I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.

<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>

Writing XML file using DataSet.WriteXml() method persists the same column as binary format

<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2

I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.

Thanks

Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm

For more details.

Jeff Derstadt - MSFT

Problem with FOR XML clause

I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.

<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>

Writing XML file using DataSet.WriteXml() method persists the same column as binary format

<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2

I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.

Thanks

Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm

For more details.

Jeff Derstadt - MSFT