Monday, February 27, 2012

HELP ME...........

It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:

> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>

help me....

hi everyone

i got 2 questions plz

1- how to use the datadiff function ....plz write the code

2- is there a Trim function or statment that cuts the unneeded spaces before and after the word

thanx ...

Hi there,
1. http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_5vxi.asp?frame=true
samples imcluded :-)
2. What do you mean by unneeded ? You can trail characters, by using the function which I wrote some time ago:
CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
--Coded by Jens Suessmeyer, 2005 Available on http://www.sqlserver2005.de
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @.intCount int
SET @.intCount = 0

WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)

RETURN @.strValue
END

Could be called by: Select dbo.fn_removetrailingchars (SomeColumn,CHAR(32),1) FROM Sometable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

thanx for answering

but what i mean is a function or starment Called trim which removes the spaces ....and i think it should be used in the insertion

for an example lets say wa have a string " hi " i want to remove the spaces before and after hi

so how can i use it ?

|||Hi,

if you don't want to use my function, simply use LTRIM(RTRIM(Expression))

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

help me...

below is my sql query statement... hope u understand
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do?
Message posted via http://www.droptable.com
There is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via droptable.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do?
> --
> Message posted via http://www.droptable.com
>
|||ejoeyz_85 via droptable.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do?
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca
|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:

>ejoeyz_85 via droptable.com wrote:
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca

help me...

below is my sql query statement... hope u understand
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do'
--
Message posted via http://www.sqlmonster.comThere is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via SQLMonster.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do'
> --
> Message posted via http://www.sqlmonster.com
>|||ejoeyz_85 via SQLMonster.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do'
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:
>ejoeyz_85 via SQLMonster.com wrote:
>> below is my sql query statement... hope u understand
>> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
>> Period_of_monitoring, m.Ongoing_maintenance,
>> m.Savings_for_inverter_replacement, m.Monitoring, m.
>> Total_anual_maint_and_monitor
>> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
>> WHERE p.Performance_id=m.MonitoringCost_id and
>> pv.PV_id=p.Performance_id and
>> pv.PV_id=m.MonitoringCost_id and
>> u.User_id =p.Performance_id and
>> u.User_id =pv.PV_id and
>> u.User_id = m.MonitoringCost_id
>> when i execute this query, it cannot executed because i got this error
>> message...
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the varchar value 'ejoy' to data type int.
>> what should i do'
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca

Help me. "recbase.cpp"

Hello~
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.
jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>

Help me. "recbase.cpp"

Hello~
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>

Help me. "recbase.cpp"

Hello~
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>

help me, i am using MS-VWD2005

Hi, If some one can help me, i am using MS-VWD2005, i want to connect with the DB by using "Conncetionstring" and "Open" and "do while EOF" and not to use the tools & controls in the VWD.. Thanks

Is this what are you trying to achieve:

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM TestTable";
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// do whatever you want...
}
connection.Close();

|||

string cnStr ="" ;//your connection stringstring qry ="Select paymentdate from tblPayments"; SqlConnection con =new SqlConnection(cnStr); con.Open(); DataSet ds =new DataSet(); SqlDataAdapter da =new SqlDataAdapter(qry, con); da.Fill(ds);// Loop through the rows of the only table in the DataSetforeach( DataRow dataRowin ds.Tables[0].Rows) {//say do something as below Response.Write(dataRow["paymentdate"].ToString()); }

Help me!emergency!

dear:

can anyone tell how to connect to Microsoft Mobile Server 2005 using System.Data.SqlceConnection or using System.oledbConnection connect to Microsoft Access DataBase in Pocket Pc or windows CE program?

dose there any minimum requirements i need to install?would you like to list a list for me

thanks alot.

If you are using Visual Studio 2005 that has Smart Device programming support, you are ready to develop against SQL Server Mobile 2005.

If not, then this link describes the installation for SQL Server Mobile 2005: http://msdn2.microsoft.com/en-us/library/ms171938(SQL.90).aspx

For developing SQLMobile applications using Visual Studio 2005 you can refer:

http://msdn2.microsoft.com/en-us/7bx62e55(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/kckakk5w(VS.80).aspx

SQL Mobile programming references to help you out:

http://msdn2.microsoft.com/en-us/library/ms174628(SQL.90).aspx. This link walks your through writing a smart device application that creates a new SQL Mobile database.

http://msdn2.microsoft.com/en-us/library/ms174462(SQL.90).aspx: This link is the SQL Server Mobile .NET programming reference. The documentation for System.Data.SqlServerCe classes can help you program to connect and access SQL Mobile in a device application.

Please let me know if this can help you get started.

help me!beginner create database password

I've learn DotNetNuke.I don't know how to create passID and userID for database sqlserver2005 express.someone help me.Thanks!thanks!You can do that by using the appropiate TSQL command, like sp_AddLogin and so forth, but as a beginner I would suggest you to use the SQL Server Management Express which has a graphical interface to administer the server instance. You will have to create a user and map the user to the appropiate database. There you will have to either grant him a role or dedicated permissions on the securables (objects) within the database.

http://msdn.microsoft.com/vstudio/express/sql/download/

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

can you help me how step by step I have to do:

right click database(example I've created database name"dotnetnuke")?

then choose properties->choose permissions?

Or can you tell me TSQL command...?

Thank you very much!

|||

I've created new users name"dnnusers" and set some permissions but I've recieved some instructions:

"Grant failed for Database 'DotNetNuke'. (Microsoft.SqlServer.Smo)"why?

Help me!!!

sSQL = "INSERT into order ( Idea , [pass], [pass1], [brands], [models], [attention], [address], [town], [code], [county], [country], [dp1], [p2], [fx], [email]) values ('" & i & "', '" & pass & "', '" & pass1 & "', '" &_
brands & "', '" & models & "', '" & attention & ", '" & address & "', '" & town & "', '" & code & "', '" & county & "', '" & country & "', '" & dp1 & "', '" & p2 & ", '" & fx & ",'" & email & "')"
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Inetpub\wwwroot\worldcar\car.mdb "
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
connection.Mode = 0
'Open the connection to the database
connection.Open(sConnString)
connection.execute(sSQL)<<<<line44

response.redirect ("E:\car1\welcome.htm")
'Done. Close the connection object
connection.Close

this is my asp coding and i keep on getting this problem

Syntax error in INSERT INTO statement.
/worldcar/add_to_database1.asp, line 44

It looks like the code pasted above is missing snigle-quotes at a few places:

...

& attention & ", '"

...

& p2 & ", '"

& fx & ",'"

If firther troubleshooting is needed, I suggest you print the sSQL string before the failing line to examine the string for correctness.

As a side-note, I would recommend looking into parameters instead of string concatenation to avoid exposure to SQL-injection.

Help me!!!

response.redirect ("E:\car1\welcome.htm")
'Done. Close the connection object
connection.Close

this is my asp coding and i keep on getting this problem

Syntax error in INSERT INTO statement.
/worldcar/add_to_database1.asp, line 44

It looks like the code pasted above is missing snigle-quotes at a few places:

...

& attention & ", '"

...

& p2 & ", '"

& fx & ",'"

If firther troubleshooting is needed, I suggest you print the sSQL string before the failing line to examine the string for correctness.

As a side-note, I would recommend looking into parameters instead of string concatenation to avoid exposure to SQL-injection.

Help me! The log file for database is full

Hi,
Im working with a sql server 2000 bd and i have a bd with simple recovery model. Each day i have the next error:

"The log file for database x is full. Backup the transaction log for the database to free up some log space"

I tried to limit the transaction log file to 500Mb but then I have this error. I have done the reduction manually of transaction log file but the next day i have got the same error. If i dont try to limit, this file grows a lot of (1GB) and then i havent got enough disk space. Can you help me, please?

Thanks a lot.
MemupiI will offer you 2 solutions

1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

Hope this answers your question|||I have tested the value of this option using the following select:
SELECT DATABASEPROPERTY ('Northwind', 'IsTruncLog')

and the returned value was '1'.

Also if i see the options of database i can see that the option "autoshrink" also is set.

Then, i dont know what is the problem.

Thanks a lot.

Originally posted by dbabren
I will offer you 2 solutions

1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

Hope this answers your question|||OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.|||I have executed loginfo query and the result is only one active segment. But, this segment is the last. Then, the shrink is not effective?? What can i do at this point?

But if i'd execute a manual command to shrink only the log transaction directly from Sql enterprise, the log file would be shorter. What is the reason? I can test this point.

On the other hand, i have limited the file to 500Mb. What happens if i execute a big transaction and i dont have enough space in the log file to save all ? Can i have this problems? When i didnt limite the space of log file i didnt have any error (the problem of space disk, of course).

Originally posted by dbabren
OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.|||where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space|||I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

Can i remove the transaction log or config to not use?

Originally posted by dbabren
where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space|||Or how can i change the transaction file to another disk? Then i could not limit the log.

Originally posted by memupi
I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

Can i remove the transaction log or config to not use?|||Can't not use a transaction log - not possible

You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.|||OK. Thanks for all. You has helped me a lot.

Originally posted by dbabren
Can't not use a transaction log - not possible

You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.|||Hi

since ur drive is running out of space.
u can free up some space in which the log file is already present
else look out for another which is free of space
1. create a folder to store logfiles
2. go to the properties of the job that is taking the backup of log
3. go to edit and change the location (drive) from previous to present drive where there is enough space

hope it will work

help me! read text file in sql

HELLO,

please help me, how to read txt file and then insert it to sql table.

i have file like this name.txe

<...
012301231923123902132003
23423i4u23490342342342343
...>
I have 4 column and want to fractionise this text '012301231923123902132003'
and put in to table.

Best regards

RobertHi

If you want to use DTS see the following tutorial
http://www.sqldts.com/default.aspx?6,107,276,7,1

John
"Robert K" <rkloma@.hotmail.com> wrote in message
news:bhap7a$c3n$1@.news.onet.pl...
> HELLO,
> please help me, how to read txt file and then insert it to sql table.
> i have file like this name.txe
> <...
> 012301231923123902132003
> 23423i4u23490342342342343
> ...>
> I have 4 column and want to fractionise this text
'012301231923123902132003'
> and put in to table.
>
> Best regards
> Robert|||See response in microsoft.public.sqlserver.programming

--
- Anith
( Please reply to newsgroups only )|||Hi,

You can use the xp_cmdshell to read the contents of a text file.
You can look at the sample at
http://www.kodyaz.com/articles/read...p_cmdshell.aspx

Eralper
http://www.kodyaz.com

help me! my mssql 2000 server (with sp3a) is keeping full loading my cpu non-stop now!

my mssql never be used much
and i had a firewall to blocked the port of mssql to internet

but i don't that why tonight the sql server non-stop keep full loading the cpu suddenly until now!
please help meSQL Server 2000 SP3a is nothing but 10.5 megs of Transact code, if you send me an email with the in my profile I can help you with it. That is some of the most clean and close to ANSI SQL code I have seen from Microsoft. You may want to ignore some of the questions Windows ask you answer only those relevant to you. I write ANSI SQL, Transact-SQL, Java and C#. Hope this help.

Kind regards,
Gift Peddie|||do u have any msn messenger then?|||No I don't do messengers, what I can tell you is there are 25 files and Windows will only execute the files applicable to you because only 11 files were executed on my Windows 2003 Enterprise Server that was not part of a network. But with SP3a I was able to install Reporting Services and Analysis Services without problem and I had Visual Studio 2003 on the box before SP3a. Hope this help.

Kind regards,
Gift Peddie

Help Me! How can I use SQL Server Subreports in Visual Web Developer Express Edition

Hi,

I use Visual Web Developer 2005 Express Edition. Anybody help me how can i use reports and subreports with parameters in it. I am new to ASP.NET. Please give me a sample. Thanks in advance.

Take a look at this article "Adding a Subreport with Parameters", it might be able to help you.

http://msdn2.microsoft.com/en-US/library/aa337490.aspx

Hope this helps.

Jarret

help me! :(

We get the following error: Connection Failed:
SQLState: 'HY000' SQL Server Error:
1060 [microsoft][ODBC SQL Server Driver]Connot generate
SSPI Context Anybody ...
client: nt4 workstation
server: Windows 2000 , sql server 7.xCheck the following Knowledge Base article:
Missing Windows NT LM Security Support Provider registry key
causes "Cannot Generate SSPI Context" error message when you
connect to SQL Server
http://support.microsoft.com/?id=269541
-Sue
On Fri, 2 Apr 2004 08:25:51 -0800, "amichaud"
<anonymous@.discussions.microsoft.com> wrote:

>We get the following error: Connection Failed:
>SQLState: 'HY000' SQL Server Error:
>1060 [microsoft][ODBC SQL Server Driver]Connot generate
>SSPI Context Anybody ...
>
>client: nt4 workstation
>server: Windows 2000 , sql server 7.x|||Quick fix (some of the time) is to make sure all the computer clocks are in
synch.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"amichaud" <anonymous@.discussions.microsoft.com> wrote in message
news:1768401c418cf$29c3c810$a501280a@.phx
.gbl...
> We get the following error: Connection Failed:
> SQLState: 'HY000' SQL Server Error:
> 1060 [microsoft][ODBC SQL Server Driver]Connot generate
> SSPI Context Anybody ...
>
> client: nt4 workstation
> server: Windows 2000 , sql server 7.x|||Easiest solution is to change the client to use Named pipes.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Help me!

Hi Master!
I'm students in NIIT. I need info of Data management Strategies of SQL
Server to write Project and I dont' know Find Topic where. Pls send to me
Everything like i talk above.
I speak Eng not good, beacause I'm VietNamese. So sorry!!
Pls send back faster, i very need it.
Thanks for your Help!!
Hi
"Inside Microsoft SQL Server 2000" by Kalen Delaney
Hardcover: 1088 pages
Publisher: Microsoft Press; Book & CD edition (November 15, 2000)
Language: English
ISBN: 0735609985
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>
|||http://www.microsoft.com/technet/its.../smfstomg.mspx
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>

help me!

now i got this error. please help me. i think it is the error of IIS.

Server Error in '/' Application.

String or binary data would be truncated.The statement has been terminated.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: String or binary data would be truncated.The statement has been terminated.
Source Error:

The source code that generated this unhandled exception can only beshown when compiled in debug mode. To enable this, please follow one ofthe below steps, then request the URL: 1. Add a "Debug=true" directive at the top of the file that generated the error. Example: <%@. Page Language="C#" Debug="true" %> or: 2) Add the following section to the configuration file of your application: <configuration> <system.web> <compilation debug="true"/> </system.web> </configuration> Note that this second technique will cause all files within a givenapplication to be compiled in debug mode. The first technique willcause only that particular file to be compiled in debug mode. Important:Running applications in debug mode does incur a memory/performanceoverhead. You should make sure that an application has debuggingdisabled before deploying into production scenario.

Apparently your application is trying to store a value into the database that is larger than the target field, as indicated by the detail:
Exception Details:System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

Try adding the debug statement to your page to help with tracking down exactly where this is taking place.

|||to say the truth: this is no myproblem. this may be the problem of a website. this is an entertainmentside which has download song link. when i download, i face thisproblem. but, when i download it from other computer in the samenetwork, it is ok. So, this is not the problem of website. So, this ismy computer's problem. can u give some advice on that situation?
regards,

HELP ME!

Hi, locking for the answer to these questions:
1. How many rows contained in table PERSON
2. How many rows contained in teble CAR
Following table contain info about persons. Table do contain a lot of
rows!
CREATE TABLE Person
{
PersonID int NOT NULL IDENTITY(1,1)
PersNumber char(11) NOT NULL,
Name1 varchar(50) NOT NULL,
Name2 varchar(50) NOT NULL,
ShoeSize int NOT NULL,
Address varchar(50) NOT NULL,
Zip varchar(10) NOT NULL,
City varchar(50) NOT NULL
}
Further more, this table containg cars connected to persons in table
above.
CREATE TABLE Car
{
CarID int NOT NULL IDENTITY(1,1),
RegNr varchar(8) NOT NULL,
PersonID int NULL
}
Following SELECT statements are executed:
SELECT *
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
(1037854 rows is affected)
SELECT PersNumber, COUNT(*)
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
GROUP BY PersNumber
HAVING COUNT(*) > 1
(132892 rows are affected)
SELECT PersNumber, COUNT(*)
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
GROUP BY PersNumber
HAVING COUNT(*) > 2
(0 rows are affected)
SELECT COUNT(DISTINCT P. PersonID), COUNT (DISTINCT C.CarID)
FROM Person P FULL OUTER JOIN Car C ON P.PersonID = C.PersonID
WHERE C.CarID IS NULL OR P.PersonID IS NULL
-- --
198898 114388
(1 rows are affected)
Now...the answers to this!
1. How many rows contained in table PERSON
2. How many rows contained in teble CAR
Thanks to all gurus taking time solving this. Please, if you know this
- try to explain your solution!
Thanks again!!
/Markselect count(*) from Car
go
select count(*) from person
--
current location: alicante (es)
"zekevarg" wrote:

> Hi, locking for the answer to these questions:
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
> Following table contain info about persons. Table do contain a lot of
> rows!
> CREATE TABLE Person
> {
> PersonID int NOT NULL IDENTITY(1,1)
> PersNumber char(11) NOT NULL,
> Name1 varchar(50) NOT NULL,
> Name2 varchar(50) NOT NULL,
> ShoeSize int NOT NULL,
> Address varchar(50) NOT NULL,
> Zip varchar(10) NOT NULL,
> City varchar(50) NOT NULL
> }
> Further more, this table containg cars connected to persons in table
> above.
> CREATE TABLE Car
> {
> CarID int NOT NULL IDENTITY(1,1),
> RegNr varchar(8) NOT NULL,
> PersonID int NULL
> }
> Following SELECT statements are executed:
> SELECT *
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> (1037854 rows is affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 1
> (132892 rows are affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 2
> (0 rows are affected)
> SELECT COUNT(DISTINCT P. PersonID), COUNT (DISTINCT C.CarID)
> FROM Person P FULL OUTER JOIN Car C ON P.PersonID = C.PersonID
> WHERE C.CarID IS NULL OR P.PersonID IS NULL
> -- --
> 198898 114388
> (1 rows are affected)
>
> Now...the answers to this!
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
>
> Thanks to all gurus taking time solving this. Please, if you know this
> - try to explain your solution!
> Thanks again!!
> /Mark
>|||Ok, that answer would have been a bright one only when having
connection to stated tables. In my case i dont. It should be able to
answer only with information above.
Thats the tricky part!
Thanks anyway! :)|||If you want to impress you teacher, tell him/her that answers cannot be
given from the information provided. There are no constraints on these
tables so no assumptions can be made about cardinality. I believe that
primary key, foreign key and unique constraints would all be needed in order
to answer the questions based on query results.
Hope this helps.
Dan Guzman
SQL Server MVP
"zekevarg" <markussteen@.chello.se> wrote in message
news:1142331724.403236.70490@.j52g2000cwj.googlegroups.com...
> Hi, locking for the answer to these questions:
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
> Following table contain info about persons. Table do contain a lot of
> rows!
> CREATE TABLE Person
> {
> PersonID int NOT NULL IDENTITY(1,1)
> PersNumber char(11) NOT NULL,
> Name1 varchar(50) NOT NULL,
> Name2 varchar(50) NOT NULL,
> ShoeSize int NOT NULL,
> Address varchar(50) NOT NULL,
> Zip varchar(10) NOT NULL,
> City varchar(50) NOT NULL
> }
> Further more, this table containg cars connected to persons in table
> above.
> CREATE TABLE Car
> {
> CarID int NOT NULL IDENTITY(1,1),
> RegNr varchar(8) NOT NULL,
> PersonID int NULL
> }
> Following SELECT statements are executed:
> SELECT *
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> (1037854 rows is affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 1
> (132892 rows are affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 2
> (0 rows are affected)
> SELECT COUNT(DISTINCT P. PersonID), COUNT (DISTINCT C.CarID)
> FROM Person P FULL OUTER JOIN Car C ON P.PersonID = C.PersonID
> WHERE C.CarID IS NULL OR P.PersonID IS NULL
> -- --
> 198898 114388
> (1 rows are affected)
>
> Now...the answers to this!
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
>
> Thanks to all gurus taking time solving this. Please, if you know this
> - try to explain your solution!
> Thanks again!!
> /Mark
>|||Actually, we should have more than enough information here to determine how
many rows are in each table. Table persons has an implied unique
constraint, and we don't need to know the constraints on table car in order
to answer the question.
The data tells us how many:
Cars are owned by a person
Persons have more than one car
Persons have more than two cars
Cars are not owned by a person
Persons do not own a car
All you need to do is add or subtract those values in order to arrive at the
answer.
Not that I am going to outright give the answer, there is somethign to be
said for actually doing your own homework.
This should be enough help to get you in the right direction.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uBfCkv2RGHA.4608@.tk2msftngp13.phx.gbl...
> If you want to impress you teacher, tell him/her that answers cannot be
> given from the information provided. There are no constraints on these
> tables so no assumptions can be made about cardinality. I believe that
> primary key, foreign key and unique constraints would all be needed in
order
> to answer the questions based on query results.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zekevarg" <markussteen@.chello.se> wrote in message
> news:1142331724.403236.70490@.j52g2000cwj.googlegroups.com...
>|||Wrong, sorry. It's possible to answer only with given info.
Use affected rows as hint.|||Jim Underwood wrote:

> Actually, we should have more than enough information here to determine ho
w
> many rows are in each table. Table persons has an implied unique
> constraint, and we don't need to know the constraints on table car in orde
r
> to answer the question.
There are no constraints. The tables have IDENTITY columns but that
doesn't mean they have keys. Dan is right. On the information given
there is no way to be sure how many rows in each table.
In particular if PersonID isn't unique then the first two queries may
contain duplicates and so we can't be sure of the number of rows in the
base tables. The FULL JOIN query on the other hand only tells us the
number of distinct values, not the number of rows.
An good example of why keys are important.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||PersonID is an IDENTITY field, therefore it is unique.
I agree that identity does not qualify as a constraint/PK by general DBMS
terms, however we know that in SQL Server IDENTITY is always unique. This
is why I referred to it a an IMPLIED unique constraint.
The example is an academic one, a test of logic and DBMS knowledge, not an
ansi standards test.
If the PersonID was not an IDENTITY field then you would be correct, but by
definition IDENTITY is unique, no matter how much you or I may disapprove of
its use here. I would much prefer to see unique constraints explicitly
defined, but that does not change the fact that one has been implicitly
created by SQL Server, even if it is proprietary.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142349407.116675.165460@.v46g2000cwv.googlegroups.com...
> Jim Underwood wrote:
>
how
order
> There are no constraints. The tables have IDENTITY columns but that
> doesn't mean they have keys. Dan is right. On the information given
> there is no way to be sure how many rows in each table.
> In particular if PersonID isn't unique then the first two queries may
> contain duplicates and so we can't be sure of the number of rows in the
> base tables. The FULL JOIN query on the other hand only tells us the
> number of distinct values, not the number of rows.
> An good example of why keys are important.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Jim Underwood wrote:
> PersonID is an IDENTITY field, therefore it is unique.
> I agree that identity does not qualify as a constraint/PK by general DBMS
> terms, however we know that in SQL Server IDENTITY is always unique. This
> is why I referred to it a an IMPLIED unique constraint.
Rubbish!
CREATE TABLE T1 (x INT IDENTITY);
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1 (x) VALUES (1);
INSERT INTO T1 (x) VALUES (1);
SET IDENTITY_INSERT T1 ON;
GO
CREATE TABLE T2 (x INT IDENTITY);
INSERT INTO T2 DEFAULT VALUES;
DBCC CHECKIDENT (T2,RESEED,0);
INSERT INTO T2 DEFAULT VALUES;
GO
SELECT x FROM T1;
SELECT x FROM T2;
Result:
x
--
1
1
(2 row(s) affected)
x
--
1
1
(2 row(s) affected)
As for testing DBMS knowledge, I'll bet that plenty of people reading
this can testify to experience of non-unique IDENTITY columns.
Certainly I have known of real examples.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||My humble apologies.
I have indeed shown my ignorance in this regard.
Thank you for setting me straight.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142351997.595277.210040@.p10g2000cwp.googlegroups.com...
> Jim Underwood wrote:
DBMS
This
> Rubbish!
> CREATE TABLE T1 (x INT IDENTITY);
> SET IDENTITY_INSERT T1 ON;
> INSERT INTO T1 (x) VALUES (1);
> INSERT INTO T1 (x) VALUES (1);
> SET IDENTITY_INSERT T1 ON;
> GO
> CREATE TABLE T2 (x INT IDENTITY);
> INSERT INTO T2 DEFAULT VALUES;
> DBCC CHECKIDENT (T2,RESEED,0);
> INSERT INTO T2 DEFAULT VALUES;
> GO
> SELECT x FROM T1;
> SELECT x FROM T2;
> Result:
> x
> --
> 1
> 1
> (2 row(s) affected)
> x
> --
> 1
> 1
> (2 row(s) affected)
> As for testing DBMS knowledge, I'll bet that plenty of people reading
> this can testify to experience of non-unique IDENTITY columns.
> Certainly I have known of real examples.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

HELP ME!

Help me!

Hi Master!
I'm students in NIIT. I need info of Data management Strategies of SQL
Server to write Project and I dont' know Find Topic where. Pls send to me
Everything like i talk above.
I speak Eng not good, beacause I'm VietNamese. So sorry!!
Pls send back faster, i very need it.
Thanks for your Help!!Hi
"Inside Microsoft SQL Server 2000" by Kalen Delaney
Hardcover: 1088 pages
Publisher: Microsoft Press; Book & CD edition (November 15, 2000)
Language: English
ISBN: 0735609985
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thanh Hai" wrote:
> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>|||http://www.microsoft.com/technet/itsolutions/cits/mo/smf/smfstomg.mspx
"Thanh Hai" wrote:
> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>

Help me!

Hi Master!
I'm students in NIIT. I need info of Data management Strategies of SQL
Server to write Project and I dont' know Find Topic where. Pls send to me
Everything like i talk above.
I speak Eng not good, beacause I'm VietNamese. So sorry!!
Pls send back faster, i very need it.
Thanks for your Help!!Hi
"Inside Microsoft SQL Server 2000" by Kalen Delaney
Hardcover: 1088 pages
Publisher: Microsoft Press; Book & CD edition (November 15, 2000)
Language: English
ISBN: 0735609985
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>|||http://www.microsoft.com/technet/it...f/smfstomg.mspx
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>

Help Me Write This Query

Sorry for my newbieness:
I have a table (Cases) where Case_Number is the unique id field.
Another table (CommentsTable) contains these fields
Case_Number
Comment_Date
Comment
As users enter comments to the database, the comment gets a date/time
stamp (Comment_Date) and is recorded in the Comment field, and the case
number for that comment is also recorded.
I want my query to return fields from the Cases table, but also return
the last comment from the CommentsTable. I know I need to use the
Case_Number and MAX(Comment_Date) then return the Comment for that max
date and that case number, and I'm pretty sure this will be a sub
query, I just can't figure out how to put it all together.
Could somebody get me going in the right direction?
TIA,
todTod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
>
select * from CommentsTable ct where not exists(select 1 from
CommentsTable ct1
where ct.Case_Number = ct1.Case_Number and ct.Comment_Date <
ct1.Comment_Date)|||Tod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
> As users enter comments to the database, the comment gets a date/time
> stamp (Comment_Date) and is recorded in the Comment field, and the case
> number for that comment is also recorded.
> I want my query to return fields from the Cases table, but also return
> the last comment from the CommentsTable. I know I need to use the
> Case_Number and MAX(Comment_Date) then return the Comment for that max
> date and that case number, and I'm pretty sure this will be a sub
> query, I just can't figure out how to put it all together.
> Could somebody get me going in the right direction?
> TIA,
> tod
Untested:
SELECT C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases AS C
JOIN dbo.CommentsTable AS T
ON C.Case_Number = T.Case_Number
WHERE T.Comment_Date =
(SELECT MAX(Comment_Date)
FROM dbo.CommentsTable
WHERE Case_Number = T.Case_Number);
Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
could still get more than one row per Case_Number.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This might be neater if you are returning a single case. The Case_Number is
stored in the @.CaseNumber variable in this example:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_Date DESC
If you wish to remove time adjustments from the equation (e.g. daylight
saving adjustments) then you could add an IDENTITY column to the
CommentsTable table (Comment_ID) and use the following:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_ID DESC
Chris
"David Portas" wrote:

> Tod wrote:
> Untested:
> SELECT C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases AS C
> JOIN dbo.CommentsTable AS T
> ON C.Case_Number = T.Case_Number
> WHERE T.Comment_Date =
> (SELECT MAX(Comment_Date)
> FROM dbo.CommentsTable
> WHERE Case_Number = T.Case_Number);
> Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
> could still get more than one row per Case_Number.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanx. I think this is closest to what I need.
Chris Howarth wrote:
> This might be neater if you are returning a single case. The Case_Number i
s
> stored in the @.CaseNumber variable in this example:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_Date DESC
>
> If you wish to remove time adjustments from the equation (e.g. daylight
> saving adjustments) then you could add an IDENTITY column to the
> CommentsTable table (Comment_ID) and use the following:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_ID DESC
> Chris
>
> "David Portas" wrote:
>

Help me write sql script

Hi I need to create query which would calculate weekly change of some values. There is table with values for every day. At the end of week I need to calculate % of change. It is something like this:

SELECT ((LastFridayValue - PreviousFridayValue) / PreviousFridayValue) * 100 from myTable.

or it could be something like this:

(LastValue - FirstValue) / FirstValue * 100 from top 5 values from my table order by ID DESC.

Please help me translate this into real sql query :)

Could you provide your table structure with some data example like last two weeks?

Thanks

|||There are two related tables. First table tblValues has structure like this:

ItemID

DayID

Value

261

1086,8986

262

1110,3700

263

1110,3700

264

1167,9900

265

1121,2900

266

1121,2900

267

1100,9600

268

1100,9600

269

1061,1000

270

1061,1000

271

985,6700

272

918,1300

273

908,5200

274

908,5200

and tblDate has structure like this

DayID

Date

261

28.1.2007

262

29.1.2007

263

30.1.2007

264

31.1.2007

265

1.2.2007

266

2.2.2007

267

3.2.2007

268

4.2.2007

269

5.2.2007

270

6.2.2007

271

7.2.2007

272

8.2.2007

273

9.2.2007

274

10.2.2007

|||

try this I hope it will point you in good direction:

createtable #test1(ItemIDint,

DayID

int,Valuenumeric(12,4))

insert

into #test1

Values

(1, 261, 1086.8986)

insert

into #test1

Values

(2, 262, 1110.3700)

insert

into #test1

Values

(3, 263, 1110.3700)

insert

into #test1

Values

(4, 264, 1167.9900)

insert

into #test1

Values

(5, 265, 1121.2900)

insert

into #test1

Values

(6, 266, 1121.2900)

insert

into #test1

Values

(7, 267, 1100.9600)

insert

into #test1

Values

(8, 268, 1100.9600)

insert

into #test1

Values

(9, 269, 1061.1000)

insert

into #test1

Values

(10, 270, 1061.1000)

insert

into #test1

Values

(11, 271, 985.6700)

insert

into #test1

Values

(12, 272, 918.1300)

insert

into #test1

Values

(13, 273, 908.5200)

insert

into #test1

Values

(14, 274, 908.5200)

create

table #days(DayIDint,

Date

datetime)

insert

into #days

Values

(261,convert(datetime,'28.1.2007',104))

insert

into #days

Values

(262,convert(datetime,'29.1.2007',104))

insert

into #days

Values

(263,convert(datetime,'30.1.2007',104))

insert

into #days

Values

(264,convert(datetime,'31.1.2007',104))

insert

into #days

Values

(265,convert(datetime,'1.2.2007',104))

insert

into #days

Values

(266,convert(datetime,'2.2.2007',104))

insert

into #days

Values

(267,convert(datetime,'3.2.2007',104))

insert

into #days

Values

(268,convert(datetime,'4.2.2007',104))

insert

into #days

Values

(269,convert(datetime,'5.2.2007',104))

insert

into #days

Values

(270,convert(datetime,'6.2.2007',104))

insert

into #days

Values

(271,convert(datetime,'7.2.2007',104))

insert

into #days

Values

(272,convert(datetime,'8.2.2007',104))

insert

into #days

Values

(273,convert(datetime,'9.2.2007',104))

insert

into #days

Values

(274,convert(datetime,'10.2.2007',104))

select

day1 [WeekDay],(cc.value-dd.value) [difference],*from(select day1,max(dayID) cur,MIN(dayID) prevfrom(selectdatepart(weekday,date) day1, dayIDfrom #days

where

date>dateadd(day,-13,getdate()))aa

group

by day1)aa

left

join #test1 ccON cc.dayid=aa.cur

left

join #test1 ddON dd.dayid=aa.prev

select

*from #test1

drop

table #days

drop

table #test1

Help me write my first Update Trigger (sql svr 2000)

can someone help me write a Trigger? I have never written a trigger. This is for SQL Server 2000

Table FOO:
----
ID (numberic counter)
Status (Char)
etc..

Table BAR:
----
ID
Status
DateUpdated (getdate())

Whenever the Status in Table FOO is updated, I need to INSERT a new record into BAR with the ID and Status

~LeCREATE TRIGGER FOO_Update ON [FOO]
FOR Insert, Update
AS
Insert into Bar
(ID,
Status,
DateUpdated)
Select ID,
Status,
Getdate()
From inserted

...but you should really think of just adding the DateUpdated field to FOO with a default of getdate() for new records and having the trigger update it:

CREATE TRIGGER FOO_Update ON [FOO]
FOR Update
AS
Update FOO
set DateUpdated = Getdate()
From FOO
inner join inserted on FOO.ID = inserted.ID

blindman|||B.E.A.U.-tiful

Works Perfectly!

Thank you very very much!

~Le

Help me write a Search function please

Hi all,

I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item...

For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item.

Any help would be greatly appreciated,

Thank you all,

Kenny.

Try something like this:

SELECTFROM YourTableWHERE (@.SongTitleISNULL OR SongTitle = @.SongTitle)AND (@.ArtistISNULL OR (originalArtist = @.ArtistOR Performer = @.Artist ))

|||

ndinakar,

Thank you very much, here is what I put for my SqlDataSource, but it doesn't appear to work... Nothing returned when I try to search, using just the artist, the song, or both for the input:

<asp:SqlDataSource ID="DSResults" runat="server" ConnectionString="<%$ ConnectionStrings:notesnhacConnectionString1 %>"
SelectCommand="SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER] FROM [t_music] WHERE (@.MUSIC_TITLE IS NULL OR [MUSIC_TITLE2] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST IS NULL OR [MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')">
<SelectParameters>
<asp:QueryStringParameter Name="MUSIC_TITLE" QueryStringField="title" Type="String" />
<asp:QueryStringParameter Name="MUSIC_ARTIST" QueryStringField="artist" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

|||

If you run thre query in your query analyzer with some values for the parameters does it work as expected? If not, post some sample data from your table and the query you used so we can better understand why its not working.

|||

Here is what I put on the Query window of SQL Manager Studio Express:

DECLARE @.MUSIC_TITLE NVARCHAR(100)
DECLARE @.MUSIC_ARTIST NVARCHAR(100)

SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]
FROM [t_music]
WHERE (@.MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST IS NULL OR [MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')

SET @.MUSIC_TITLE = 'Everytime'
SET @.MUSIC_ARTIST = 'Cascada'

I've tried to use just @.MUSIC_TITLE, or just @.MUSIC_ARTIST, and both... each time it return ALL the records in the table... But if I execute the same select statement in VS 2005, it returns nothing... In the table, there is a song called "Everytime We Touch" performed by Cascada.

|||

Are you setting the values after running the SELECT? post some sample data from your table so I can test it on my machine..

|||

sorry. you need to use OR instead of AND in your WHERE clause.

|||

As you can see I have the SET @.MUSIC_TITLE = 'Everytime' in the previous post... Here are some sample data:

MUSIC_TITLE MUSIC_ORIGINAL_SINGER MUSIC_PERFORMER

Everytime We Touch Cascada Cascada

Dancing Queen ABBA Purity

Heaven Bryan Adam

Hotel California Eagles Eagles

Thank you very much for your help,

Kenny.

|||

Changed the "AND" to "OR" have helped, but I have some problem:

1. If I don't enter anything for the "Artist" or in the "Title" box (meaning only of the box have input), no records returned even if I type part of the name of the song/artist, or even the full name of the song/artist.

2. For example if I have two songs that have some similiar words, i.e. "Hotel", in the Title box I typed "Hotel" and in the Artist box, I typed "Eagles", which should return 1 record only that contain both Hotel & Eagles in the record... But instead it returns all the songs with "Hotel" in it.

Thanks again,

Kenny.

|||

I think we had it right the first time itself. AND should work.

|||

Dinakar,

You are right, the "AND" should work... The problem is that it only works if both the Title and Artist text box have values in them... Leaving one or the other blank does not return any results even if they should be... Any help?

Thank you,

Kenny.

|||

HEre's a sample I set up. I was able to get both records when I used "hotel" for music_title and left the second field blank.

Declare @.ttable (col1int identity, MUSIC_TITLEvarchar(50), MUSIC_ORIGINAL_SINGERvarchar(50), MUSIC_PERFORMERvarchar(50))insert into @.tvalues ('Everytime We Touch','Cascada','Cascada')insert into @.tvalues ('Dancing Queen','ABBA','Purity')insert into @.tvalues ('hotel','Bryan Adam',null)insert into @.tvalues ('Hotel California','Eagles','Cascada')DECLARE @.MUSIC_TITLENVARCHAR(100)DECLARE @.MUSIC_ARTISTNVARCHAR(100)SET @.MUSIC_TITLE ='hotel'SET @.MUSIC_ARTIST =nullSELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM @.t--where ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')WHERE (@.MUSIC_TITLEISNULL OR [MUSIC_TITLE]LIKE'%' + @.MUSIC_TITLE +'%')AND (@.MUSIC_ARTISTISNULL OR ([MUSIC_ORIGINAL_SINGER]LIKE'%' + @.MUSIC_ARTIST +'%'OR [MUSIC_PERFORMER]LIKE'%' + @.MUSIC_ARTIST +'%'))

|||

I tried the code above in SQL Manager Studio and they seems to work as expected... but somehow it doesn't work with my SqlDataSource! I don't know what else I have to do...

Thank you very, very much for your help Dinakar.

Kenny.

|||

I think that it doesn't work with SqlDataSource because the empty string (when nothing was entered in either one of the textbox) doesn't mean null in ADO.NET... Can anyone help me convert this to a code-behind, or help me convert that empty string to null? I would love to put this in code-behind file but like I said in my first post, I'm very new to ASP.NET.

Thanks,

Kenny.

|||

Unfortunately I dont do any .NET code so I cant help you there. Perhaps you can create a new post and someone might help you there. Posts with 0 replies have a better chance of being "looked at" than the ones with 12 replies.

Help me with this SELECT FROM OPENXML

The following script doesnt return any records. Why ?
----
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
----You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
>

Help me with this SELECT FROM OPENXML

The following script doesnt return any records. Why ?
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----
|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:

Help Me with This Query!

Hi All,

I have this query and I want to show single entry on the every records, I already use Group By, but it doesn't solve the problem.

Please help to find the problem.

=============================================================================================

SELECT EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal,SUM(TotalOrder)as TotalOrderFROM

(SELECT

e.OfficeCode,

(SELECTDISTINCT OfficeFROM OfficeCode ocWHERE oc.Code= e.OfficeCode)as Office,

e.OfficeCode+'-'+ e.EmployeeCodeas EmployeeCode,

(SELECT FirstName+' '+ LastNameFROM Person pWHERE p.PersonID= e.PersonID)as EmployeeName,

InvoiceTotal=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENSUM(o.OrderFee)ELSE 0END,

TotalOrder=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENCount(o.OrderID)ELSE 0END

FROM Employee e

LEFTJOIN oms.[OrderAssignment] oaON e.EmployeeID= oa.EmployeeID

LEFTJOIN oms.[Order] oON o.OrderID= oa.OrderID

LEFTJOIN OfficeCode ocON oc.Code= e.OfficeCode

WHERE e.OfficeCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.OfficeCode,','))AND

e.IsManager='True'AND

e.DivisionCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.DivisionCode,','))

GROUPBY e.OfficeCode, e.EmployeeCode, e.PersonID, o.InvoiceDT, o.OrderFee, o.OrderID)as tmp

GROUPBY EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal, TotalOrder

=================================================================================================

Thanks in advance.

Anyone please...|||I got it. The problem is I put Group By TotalOrder. If I take out the group by totalOrder, it will works just fine. Thanks.

Help me with this query please.

tblOrgSystem

OrgSystemID OrgSystem
1 USA
2 CANADA

tblOrgSystemNode

OrgSystemID OrgNodeID OrgNode
1 3 Manistique
1 4 Houston
1 7 M-Sales
1 8 M-IT
1 10 H-Sales
1 11 H-IT
2 5 Toronto
2 6 Ontario
2 13 T-Sales
2 14 T-IT
2 16 O-Sales
2 17 O-IT

tblOrgSystemNodeParent

OrgSystemID OrgNodeId OrgNodeParentID
1 3 3
1 4 4
1 7 3
1 8 3
1 10 4
1 11 4
2 5 5
2 6 6
2 13 5
2 14 5
2 16 6
2 17 6

I so far have this query.

select tblOrgSystem.OrgSystem, tblOrgSystemNode.OrgNode,
OrgNodeParentID from
tblOrgSystemNodeParent join tblOrgSystem on tblOrgSystem.OrgSystemID =
tblOrgSystemNodeParent.OrgSystemID join tblOrgSystemNode on
tblOrgSystemNode.OrgNodeID = tblOrgSystemNodeParent.OrgNodeID

This Query gives me this result:
OrgSystem OrgNode OrgNodeParentID
USA Manistique 3
USA Houston 4
USA M-Sales 3
USA M-IT 3
USA H-Sales 4
USA H-IT 4
CANADA Toronto 5
CANADA Ontario 6
CANADA T-Sales 5
CANADA T-IT 5
CANADA O-Sales 6
CANADA O-IT 6

I want this result instead!!!!

OrgSystem OrgNode OrgNodeParentID
USA Manistique Manistique
USA Houston Houston
USA M-Sales Manistique
USA M-IT Manistique
USA H-Sales Houston
USA H-IT Houston
CANADA Toronto Toronto
CANADA Ontario Ontario
CANADA T-Sales Toronto
CANADA T-IT Toronto
CANADA O-Sales Ontario
CANADA O-IT Ontario

I cant figure out if I need another join or some kind of union to get
the name of the nodes instead ot the ID...
Also After I figure the query out I need to generate an XML document
that has a heiracheal structure that matches the 'organizational'
structure of the tables...I figured the query out, simple actually:S

select tblOrgSystem.OrgSystem, A.OrgNode,
B.OrgNode from
tblOrgSystemNodeParent join tblOrgSystem on tblOrgSystem.OrgSystemID =
tblOrgSystemNodeParent.OrgSystemID join tblOrgSystemNode A on
A.OrgNodeID = tblOrgSystemNodeParent.OrgNodeID
join tblOrgSystemNode B on
B.OrgNodeID = tblOrgSystemNodeParent.OrgNodeID

However I still can't figure the XML portion of this out, I would like
it to look like this:

USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc...

I want to bind this to a treeview so it can be easily navigated.|||rhaazy (rhaazy@.gmail.com) writes:

Quote:

Originally Posted by

I figured the query out, simple actually:S
>
select tblOrgSystem.OrgSystem, A.OrgNode,
B.OrgNode from
tblOrgSystemNodeParent join tblOrgSystem on tblOrgSystem.OrgSystemID =
tblOrgSystemNodeParent.OrgSystemID join tblOrgSystemNode A on
A.OrgNodeID = tblOrgSystemNodeParent.OrgNodeID
join tblOrgSystemNode B on
B.OrgNodeID = tblOrgSystemNodeParent.OrgNodeID
>
However I still can't figure the XML portion of this out, I would like
it to look like this:
>
USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc...

That does not look like XML to me.

Quote:

Originally Posted by

I want to bind this to a treeview so it can be easily navigated.

Maybe treeviews eat XML documents for lunch, but this a newsgroup about
server software, and we don't know about GUIs. At least I don't.

So please post the XML document you want as output. For a tested query,
it's also helps if you post CREATE TABLE statements for you tables, and
sample data as INSERT statements. (And my experience of FOR XML ELEMENT
is limited, so I need to test before I make a fool of myself in public.)

Also, please specify which version of SQL Server you are using.

Hopefully this format is possible...
Erland Sommarskog wrote:

Quote:

Originally Posted by

rhaazy (rhaazy@.gmail.com) writes:

Quote:

Originally Posted by

I figured the query out, simple actually:S

However I still can't figure the XML portion of this out, I would like
it to look like this:

USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc...

>
That does not look like XML to me.
>

Quote:

Originally Posted by

I want to bind this to a treeview so it can be easily navigated.

>
Maybe treeviews eat XML documents for lunch, but this a newsgroup about
server software, and we don't know about GUIs. At least I don't.
>
So please post the XML document you want as output. For a tested query,
it's also helps if you post CREATE TABLE statements for you tables, and
sample data as INSERT statements. (And my experience of FOR XML ELEMENT
is limited, so I need to test before I make a fool of myself in public.)
>
Also, please specify which version of SQL Server you are using.
>
--
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

|||rhaazy (rhaazy@.gmail.com) writes:

Quote:

Originally Posted by

><root>
><USA>
<Manistique>
<M-Sales/>
<M-IT/>
</Manistique>
<Houston>
<H-Sales/>
<H-IT/>
</Houston>
></USA>
><CANADA>
<Toronto>
etc...
></root>
>
Hopefully this format is possible...

It does not seem to be. When I look at the examples for FOR XML EXPLICIT
and FOR XML PATH, they don't lead to that result. And indeed the XML
document above is sort of funny. A more normal document would be:

etc.

--
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|||The problem with this is that there is no upper and lower, the number
or depth if you will of orgnodes is limitless... This is what my major
problem is, figuring out how to get this sort of format (i was giving
more less a psuedo definition of what I need) when say for example
M-Sales gets has a child added to it via the database admin.

Quote:

Originally Posted by

Quote:

Originally Posted by

Hopefully this format is possible...

>
It does not seem to be. When I look at the examples for FOR XML EXPLICIT
and FOR XML PATH, they don't lead to that result. And indeed the XML
document above is sort of funny. A more normal document would be:
>
>
<ROOT>
<OrgSystem Name="USA">
<UpperOrgNode Name="Manistique">
<LowerOrdNode Name="M-Sales"/>
<LowerOrdNode Name="M-IT"/>
</UpperOrgNode>
>
etc.
>
--
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

|||rhaazy (rhaazy@.gmail.com) writes:

Quote:

Originally Posted by

The problem with this is that there is no upper and lower, the number
or depth if you will of orgnodes is limitless... This is what my major
problem is, figuring out how to get this sort of format (i was giving
more less a psuedo definition of what I need) when say for example
M-Sales gets has a child added to it via the database admin.

One thing is clear, you can never get the answer you are looking for,
if you only hand out tidbits of information. For instance, some posts
back I asked which version of SQL Server you are using, but you never
told us.

Really what is the best way to describe a hierachical structure of
any depth in XML, I don't know. You may want talk to the people in
microsoft.public.sqlserver.xml.

--
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|||I apologize for my inability to explain what I mean in such a technical
manner I am not accustomed to. I am an intern and with little
programming experience have managed to work 4 very intense months of
not only programming but designing application software for a major
petroleum company. I have only 4 months of real software development,
however I feel this 4 months has truely given me a head start in my
professional career.

In reference to the version of SQL question, the reason I didn't
respond is because I merely wanted what you clearly re-stated for me in
your last post... -_-'

Also as I have mentioned clearly before, I appreciate everyone's help
in my learning process and can only suggest one thing; try to be more
curtious to someone who may not be familiar with programming, the
internet, or even computers in general! I appreciate the "tough love"
in some cases, but most it only causes frustration (and the occasional
laugh off with my co-worker).

Again, thanks everyone for all your help, I always know where to go
when I need anything answered.

Quote:

Originally Posted by

|||rhaazy wrote:

Quote:

Originally Posted by

In reference to the version of SQL question, the reason I didn't
respond is because I merely wanted what you clearly re-stated for me in
your last post... -_-'

That sort of information often affects the answer (even if it happened
not to do so in this particular case). Giving the info and having it
turn out to be irrelevant is much better than omitting it and having
it turn out to be relevant.

Monday, February 27, 2012

HELP ME...........

help me....

help me...

help me...

Help me. "recbase.cpp"

Help me. "recbase.cpp"

Help me. "recbase.cpp"

help me, i am using MS-VWD2005

Help me!emergency!

help me!beginner create database password

Help me!!!

Help me!!!

Help me! The log file for database is full

help me! read text file in sql

help me! my mssql 2000 server (with sp3a) is keeping full loading my cpu non-stop now!

Help Me! How can I use SQL Server Subreports in Visual Web Developer Express Edition

help me! :(

Help me!

help me!

now i got this error. please help me. i think it is the error of IIS.

Server Error in '/' Application.

String or binary data would be truncated.The statement has been terminated.

HELP ME!

HELP ME!

Help me!

Help me!

Help Me Write This Query

Help me write sql script

Help me write my first Update Trigger (sql svr 2000)

Help me write a Search function please

Help me with this SELECT FROM OPENXML

Help me with this SELECT FROM OPENXML

Help Me with This Query!

Help me with this query please.

sql server codes

Blog Archive

About Me