ClassNotFoundException

Hi all,
I am new to java. I have installed SQL Server JDBC Driver SP3 on my
computer.
When I run the statement Class.forName("..."); , I get the
ClassNotFoundException.
I have read the following article
http://support.microsoft.com/default...;en-us;313100.
And I am sure that I have my CLASSPATH variable is like the following:
CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for
JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for
JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for
JDBC\lib\mssqlserver.jar
But the exception still occurs. Even when I copied the 3 jar files into
c:\j2sdk\lib, it also didn't work.
Anyone can help me?
Thank you!
Hi Charlie,
Are you sure that the driver classes are accesible from your calling class,
ie, have you imported the driver package?
Ian
"Charlie Tong" wrote:

> Hi all,
> I am new to java. I have installed SQL Server JDBC Driver SP3 on my
> computer.
> When I run the statement Class.forName("..."); , I get the
> ClassNotFoundException.
> I have read the following article
> http://support.microsoft.com/default...;en-us;313100.
> And I am sure that I have my CLASSPATH variable is like the following:
> CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for
> JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for
> JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for
> JDBC\lib\mssqlserver.jar
> But the exception still occurs. Even when I copied the 3 jar files into
> c:\j2sdk\lib, it also didn't work.
> Anyone can help me?
> Thank you!
>
>
|||It works now.
Thanks Ian!
"Ian555" <Ian555@.discussions.microsoft.com> wrote in message
news:CFED38BD-C33C-4336-97E3-624D5C5513DD@.microsoft.com...[vbcol=seagreen]
> Hi Charlie,
> Are you sure that the driver classes are accesible from your calling
> class,
> ie, have you imported the driver package?
> Ian
> "Charlie Tong" wrote:

Clarification on the ROWCOUNT statement

Hi All,
I just wanted to get some clarification on the ROWCOUNT statement in T-SQL.
I'm presently using it to limit the number of rows returned in SELECT based
SPs.
However I'm just curious as the effect of this keyword on the global scope
of SQL operations.
Does setting ROWCOUNT to 25 in one stored procedure effect other SPs running
?
Or is it only specific to the SP which set it?
Also...if it does only effect the code in the specific SP which set the
ROWCOUNT why is it recommended/required to set it back to zero when you are
done?
I have tested this and is "seems" to have no effect on other code running,
but I just want to make sure before I create considerable headaches in my
application.
Thanks for any feedback.
John RossitterJohn,
You can just use the TOP clause with an ORDER BY clause to ommit the need of
setting ROWCOUNT.
From SQL BOL:
It is recommended that DELETE, INSERT, and UPDATE statements currently using
SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
DELETE, INSERT, or UPDATE.
The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
DELETE statements against remote tables and local and remote partitioned
views.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in
> T-SQL.
> I'm presently using it to limit the number of rows returned in SELECT
> based
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs
> running?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you
> are
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||The ROWCOUNT setting does not bleed to other connections and it is also loca
l to inside the
procedure. I still always type a comment to reset it to 0 the very same mome
nt as I type the setting
to non-zero. Just in case someone else add some code to the procedure later.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in T-SQL
.
> I'm presently using it to limit the number of rows returned in SELECT base
d
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs runni
ng?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you ar
e
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||Hi Jerry,
I can't use TOP because I need a dynamic number of rows selected.
Which is why I'm using ROWCOUNT directive instead.
To clarify what I have done, is in one of my tables set a MaxRows threshold.
Now when any of my SELECT based SPs execute the code looks something like
this:
====================================
DECLARE @.TOP int;
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
SET ROWCOUNT @.TOP
SELECT ....
SET ROWCOUNT 0
=====================================
This way I can control the overall performance of all of my queries.
I just want to make sure that the ROWCOUNT setting does not have an impact
on any other code running in SQL.
For example lets say that the above SP was running, and then another started
in the middle of it's execution. Should I expect to see the ROWCOUNT clause
effect the 2nd query, or does it only effect the code which it's currently
executing.
I hope that helps clarify my question.
Thanks,
John Rossitter
"Jerry Spivey" wrote:

> John,
> You can just use the TOP clause with an ORDER BY clause to ommit the need
of
> setting ROWCOUNT.
> From SQL BOL:
> It is recommended that DELETE, INSERT, and UPDATE statements currently usi
ng
> SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
> DELETE, INSERT, or UPDATE.
> The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
> DELETE statements against remote tables and local and remote partitioned
> views.
> HTH
> Jerry
> "John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in messag
e
> news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
>
>|||Ok. I'm not aware of any issues. My testing confirms the same results - no
side affects. However that being said, I would recommend appending a SET
ROWCOUNT 0 to your code for completeness.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:9596F2C7-649D-4EDD-98B3-A7C321FD4038@.microsoft.com...
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows
> threshold.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another
> started
> in the middle of it's execution. Should I expect to see the ROWCOUNT
> clause
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>|||Hi
You can use top in dynamic queries like this
DECLARE @.TOP int;
DECLARE @.VAR varchar(200)
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
select @.VAR = 'SELECT TOP' + CAST(@.TOP as varchar(10) + ' from your table
name...'
exec(@.VAR)
--
Regards
R.D
--Knowledge gets doubled when shared
"John Rossitter" wrote:
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows threshol
d.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another start
ed
> in the middle of it's execution. Should I expect to see the ROWCOUNT claus
e
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>

Circular Reference?

I get this error when I ran the below statement what did I do wrong?

"The definition of MonthRange set contains a circular reference"

WITH SET [MonthRange] AS

{

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].PrevMember

:

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

}

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([MonthRange], [Industry].&[Subprime])

[Dim Originationasofmm] is a regular dimension with members like 200101, 200102, 200103 so on.

What I wanted to do (Note that I do not have a Time Dimension in the cube, just a dimension that simulates this, so would this work just as well? )

Can I have a generic set that would take as input current member and give 3 or 6 or 12 rolling months?

(this is assuming that I cannot switch to using time dimension anytime soon and just have to use a regular dimension for now?)

For example,

given 200101 and say 3 for 3 months rolling period, I would get 200012, 200101, 200102

and

given 200101 and say 7 for 6 months rolling period, I would get 200010, 200011, 200012, 200101, 200102, 200103, 200104

Try this version:

WITH

SET [MonthRange] AS

LastPeriods(3,

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] as

Aggregate([MonthRange])

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],

[Industry].&[Subprime])

|||

Hi Deepark,

This query works! but as soon as I add any other measure below (1 or more) to the query, only column that has data would be [Closing Balance] and all other measures are NULL.

Is it because of some SCOPING issue in the calculation?

--Query only returns data for [Closing Balance], all other measures are NULL when they should have data.

WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange])

SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO

Calculations:

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Bankruptcy] Then
([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Bankruptcy],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Foreclosure] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D BankRuptcy]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Bankruptcy]);
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Foreclosure] Then
([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Foreclosure],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Bankruptcy] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1; */

CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D Foreclosure]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Foreclosure]);
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null
When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])
When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[Def OTS]);
SCOPE(ROOT([Dim OTS]));
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = 1;
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;

|||

Well, I was able to reproduce this behavior in Adventure Works by creating a calculated measure with default value of NULL (as above); then just assigning it a constant value. But setting the new SP2 SCOPE_ISOLATION property seems to solve it - like:

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

by the way, the code that works look like this:

--so if I want another period, I just replace # 3 and [200101] with appropriate member chosen by the user.

--I wonder if I finally do have a Time dimension, would using CurrentMember work so I don't have to hard-code [200101]?

WITH SET [MonthRange] AS

LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

SELECT {

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1

FROM [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])

GO

--this one also works, so if I want another rolling period, I just replace the lag #

WITH SET [MonthRange] AS

{[Dim Originationasofmm].&[200612].lag(1):[Dim Originationasofmm].&[200612].lag(-1)}

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

select

{

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

([Dim Asofmm].FIRSTchild:[Dim Asofmm].[200612]) on 1

from [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])

chr(10) in an insert statement.

Can someone help me parse this oracle insert statement into SQL Server please?

insert into notify_template (subject, body, priority)
values
(
'
Budgeted time exceeded for task [18040]
',
'
Actual Time exceeds Budgeted time for the following task'
|| chr(10) || chr(10) ||
' Task Name : [18040]
Root Program Name : [18049]
Actual Days : [2000000100]
Budgeted Days : [2000000101]
Estimated Time : [2000000102]'
|| chr(10) || chr(10) ||
' The following resource(s) in the above task have exceeded the budgeted time:'
|| chr(10) || chr(10) ||
' [2000000784]'
|| chr(10) || chr(10) ||
' Click here : [12865]
',
2)Got it...

+ CAST(CHAR(10) AS VARCHAR) + CAST(CHAR(10) AS VARCHAR) +
for || chr(10) || chr(10) ||

Choosing the most frequent

There must be a way to do this simply. We're running SQL Server 2000. I'm looking for some generic SQL statement that I can apply.

If I have a table with a person column and a location column and multiple records for the same person / locatioin combination, how do I select the person with the location they most frequently visited? Say George visits Mexico 5 times, and the Bahamas twice and costa rica once. I would have 8 records in my table for George. The data looks something like this:

George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Bahamas
George/Bahamas
George/Costa Rica
Ben/Brazil
Ben/Brazil
Ben/Peru

The results would be:

George/Mexico
Ben/Brazil

Thanks!

Myles

With SQL Server 2005, it's fairly simple. Here's an example using the Northwind sample database, since you didn't provide your table definition:

with T(CustomerID, EmployeeID, rk) as (
select
CustomerID,
EmployeeID,
row_number() over (partition by CustomerID order by count(*) desc)
from Northwind..Orders
group by CustomerID, EmployeeID
)
select
CustomerID,
EmployeeID
from T
where rk = 1

If you want to include ties for most frequent location per person, use rank() instead of row_number().

With SQL Server 2000, it's a little less efficient - the same query would look like this:
select
CustomerID,
EmployeeID
from Northwind..Orders as O1
where CustomerID like 'A%'
group by CustomerID, EmployeeID
having count(*) = (
select max(ct) from (
select count(*) as ct
from Northwind..Orders as O2
where O2.CustomerID = O1.CustomerID
group by CustomerID, EmployeeID
) as Cts
)

Steve Kass
Drew University
www.stevekass.com|||

create table visits
(
visit varchar (200)
)
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values('George/Mexico')
insert visits(visit) values('George/Mexico')
insert visits(visit) values ('George/Bahamas')
insert visits(visit) values ('George/Bahamas')
insert visits(visit) values ('George/Costa Rica')
insert visits(visit) values ('Ben/Brazil')
insert visits(visit) values('Ben/Brazil')
insert visits(visit) values ('Ben/Peru')

select left(visit,(CHARINDEX('/',visit,0)-1))AS person,
right(visit,len(visit)-1-len(left(visit,(CHARINDEX('/',visit,0)-1)))) as places
into #tempx
from visits

select * from #tempx

select person , places, count(*) as counts into #tempy from #tempx
group by person, places
order by person,counts desc

select a.person,b.places, a.counts from
(
selecT person, max(counTs)as counts from #tempy group by person )
as
a
join

(
select * from #tempy
) as b
on a.person=b.person and a.counts=b.counts

--drop table visits
--drop table #tempx
--drop table #tempy

|||Thank you joeydj!

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:

> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to dis
k.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty page
s
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
>
>

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:
> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty pages
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> > Do I understand correctly that iIf I execute the CHECKPOINT statement from
> > query analyzer for a selected database, all uncommitted transactions in
> > the
> > transaction log are physically written to the database at that time?
> >
> > If so then do I need to manually truncate the log at another time to
> > reduce
> > it's size. Because I'm assuming the CHECKPOINT does not automatically do
> > that.
> >
> > Thanks for the clarification.
>
>

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.
No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.
|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:

> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty pages
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
>
>

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
I believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>
|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:

checking the SELECT statement for an SqlDataAdapter

I'm trying to pass a querystring to an SqlDataAdapter object. To check if the query is a valid SELECT statement, I simply use a try-catch. But dispite the try-catch it still accepts valid INSERT statements. However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong?

 Here is my code:

try{ my_conn = conn_open(); da =new SqlDataAdapter(query, my_conn); da.Fill(result.resultDataset); my_conn.Dispose();}catch (Exception e){ result.errMsg ="Database Error: " + e.Message; result.success =false;}

Kehil

Kehil:

However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong?

How do you know the parameterlist makes the SqlDataAdapter accpets only SELECT statement or SP? Actually when you create a SqlDataAdapter with a SqlCommand, the SqlCommand is used as SqlDataAdapter.SelectCommand, but this does not mean the SqlCommand must be a SELECT statement. If you want to validate SqlDataAdapter.SelectCommand, you need to validate the SqlCommand.CommandText to make sure it doesn't cotain any words like INSERT (but if there is a column named "INSERT" returned by a SELECT query, the statement will also considered invalid). So, such validation seems to make no sense.

Checking the datatype of a columns

Hello,

I have the following sql statement

Code Snippet

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

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

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

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

Many thanks for any help,

Steve

If you're intention is to ultimately convert the datatype, what's preventing you from initially setting the datatype as datetime?

I know this doesn't answer your question, but I'm curious.

Adamus

|||Hello,

The table was created initially with a nvarchar. This is a live database and the dates that have already been entered have to be formated in order for the alter column will work.

If the customer runs this script more than once, I don't want to have to execute the alter statement again.

Many thanks,

Steve
|||

There's really not a problem if that statement executes EVEN if the datatype has been previously changed.

However, if you need to:

Code Snippet

IF NOT EXISTS
( SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME = 'OtherCall'
AND COLUMN_NAME = 'Date'
AND DATA_TYPE = 'datetime'
)
)
BEGIN
UPDATE OtherCall

SET [Date] = convert( nvarchar(50), convert( datetime, [Date], 103), 111)

ALTER TABLE OtherCall ALTER COLUMN [Date] datetime

END

|||

Try the below SQL Statement, this is my version of solution there may be other ways to

' Returns row if the column is already converted to the required data type

IF EXISTS (select c.name, c.xtype from sysobjects o, syscolumns c where o.id = c.id and o.name = tablename and c.xtype= 61(xtype value of datatime datatype, can be found in systypes table in master database) and c.name =column name)
BEGIN
// The column is already converted to datatime format. In your case this step will be blank
END
ELSE

BEGIN

// The column is not in datatime datatype so we need to convert it. Below is your code for conversion

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

END

Checking is updation successfull

I am updating a table in store procedure. I want to check whether the statement has successfully updated the table or not. i know in mySql i can handle it using ROW_COUNT() function. but how can i do it in MS-SQL?You can use @.@.ROWCOUNT for getting number of rows affected by the last statement.|||Hey, thanks man... you saved my time on Google :)

Checking if an entry exists in a database

I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)

I had written a simple example here.

Below code has simple example on it:

<%

@.PageLanguage="VB" %>

<%

@.ImportNamespace="System.Data" %>

<%

@.ImportNamespace="System.Data.SqlClient" %>
<scriptrunat="server">
ProtectedSub btnCheckData_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)TryDim HasdataAsStringDim myConnectionAsNew SqlConnection("server=server\sqlserver;database=db;User Id=uid;Password=swd;")Dim myCommandAsNew SqlCommand( _"Select 'X' from preview Where facility='" & TextBox1.Text &"'", myConnection)

myCommand.Connection.Open()

Hasdata = myCommand.ExecuteScalar()

myCommand.Connection.Close()

myCommand.Connection.Dispose()

If Hasdata ="X"Then
' Do if data exist process here
Response.Write("Data exit")
Else
' Do if data not exist process here
Response.Write("Data not exist")
EndIfCatch exAs Exception

Response.Write(

"Error:" & ex.Message)ExitSubEndTryEndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div> <asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/><br/><asp:ButtonID="btnCheckData"runat="server"OnClick="btnCheckData_Click"Text="CheckData"/></div></form>

</

body>

</

html>

I have used executescalar and checked data is there or not in above example. If you see select, in that I am taking input from textbox for where clause, I am checking if that value is in DB or not, if not we won't get any data, but if the value is there, then select returns 'X'. So by basing on that I am checking the If condition as below.

If Hasdata ="X"Then
' Do if data exist process here
Response.Write("Data exit")
Else
' Do if data not exist process here
Response.Write("Data not exist")
EndIf

So as you asked if data is there you can keep it in True condition or if not there then you can keep in false condition.

Hope it helps!

|||Thanks for the help. When I run the code, I get an error telling me I need to cast to string (I'm using c#). And when I fix that line to this: rescheck = (string)cmd.ExecuteScalar(); It says: Invalid column name 'Owner'

Any ideas on how to fix this?

Thanks|||

Here is C# code:

<%@. Page Language="C#" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

protected void btnCheckData_Click(object sender, EventArgs e)
{
try
{
String Hasdata;
SqlConnection myConnection = new SqlConnection(@."server=Sreebrain\SreeSQL;database=SriTest;User Id=sa;Password=srikog1!;");
SqlCommand myCommand = new SqlCommand( "Select 'X' from preview Where facility='" + TextBox1.Text + "'", myConnection);

myCommand.Connection.Open();
Hasdata = (string)myCommand.ExecuteScalar();
myCommand.Connection.Close();
myCommand.Connection.Dispose();

if (Hasdata == "X")
{
// Do if data exist process here
Response.Write("Data exit");
}
else
{
// Do if data not exist process here
Response.Write("Data not exist");
}
}
catch (Exception ex)
{
Response.Write("Error:" + ex.Message);

}
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="btnCheckData" runat="server" OnClick="btnCheckData_Click" Text="CheckData" /></div>
</form>
</body>
</html>

|||

Ok, You got invalid column means, In your select statement you might given a column name as owner, that field could not be in your Table.

Ex: Select 'X' from preview

Instead of column name give 'X' there so that you can check for 'X' when you got the result back.
Also make sure in your where clause you given the disered field as you wanted.

If you still get error after you do these changes, please post the code, so that I can invistigate the problem.

Hope this time it will make you clear!

|||Thanks for all the help. You were right on the money. Everything's fixed :)|||

I have 2 labels with visibility set to FALSE, I want to display 1 label when the entry exists and the other 1 when the entry does not exist in my DB

your code looks like it should work, yet I am getting some exceptional error

here the error

Object reference not set to an instance of an object.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 61: If Hasdata = "X" Then
Line 62: Label12.Visible = True
Line 63: Else
Line 64: Label9.Visible = True
Line 65: End If

Source File:D:\Visual Studio 2005\Projects\WebApplication11\WebApplication11\admin\Createu.aspx.vb Line:63

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
 WebApplication11.Createu.TextBox1_TextChanged(Object sender, EventArgs e) in D:\Visual Studio 2005\Projects\WebApplication11\WebApplication11\admin\Createu.aspx.vb:63
 System.Web.UI.WebControls.TextBox.OnTextChanged(EventArgs e) +105
 System.Web.UI.WebControls.TextBox.RaisePostDataChangedEvent() +134
 System.Web.UI.WebControls.TextBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
 System.Web.UI.Page.RaiseChangedEvents() +137
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4778

---

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

and heres the code

Protected Sub TextBox1_TextChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles TextBox1.TextChangedTry Dim HasdataAs String Dim myConnectionAs New SqlConnection("Server=localhost;UID=SA;PWD=****;database=knowledge")Dim myCommandAs New SqlCommand("Select'X' from usr Where Uid='" & TextBox1.Text & "'", myConnection) myCommand.Connection.Open() Hasdata = myCommand.ExecuteScalar() myCommand.Connection.Close() myCommand.Connection.Dispose() If Hasdata = "X" Then Label12.Text = True Else Label9.Visible = True End If Catch ex As Exception Response.Write("Error:" & ex.Message)Exit Sub End Try End Sub
 

Checking if a stored procedure exists

Is there a way using a SQL Statement to see if a stored procedure exists in
SQL Server?The following will work for SPs, tables, or basically any database object:
if object_id('MyProc') is not null
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:e3NuzSLtFHA.1048@.TK2MSFTNGP10.phx.gbl...
> Is there a way using a SQL Statement to see if a stored procedure exists
> in SQL Server?
>|||Select 'Exists' where OBJECT_ID(Nameof thsp) IS NOT NULL
Select * from INFORMATION_SCHEMA.Routines where Routine_name =
'nameofyoursp'
HTH, Jens suessmeyer.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:e3NuzSLtFHA.1048@.TK2MSFTNGP10.phx.gbl...
> Is there a way using a SQL Statement to see if a stored procedure exists
> in SQL Server?
>|||THANKS!!!
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:uxi1AhLtFHA.236@.TK2MSFTNGP11.phx.gbl...
> Select 'Exists' where OBJECT_ID(Nameof thsp) IS NOT NULL
> Select * from INFORMATION_SCHEMA.Routines where Routine_name =
> 'nameofyoursp'
> HTH, Jens suessmeyer.
>
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:e3NuzSLtFHA.1048@.TK2MSFTNGP10.phx.gbl...
>

checking for null statement in sql

Hi Everyone,

I have a query

select name, address, city from table1

I want to append address +'-' + city in this query . I want to show hyphen only if both address and city are not null. If name is null then I don't want to show hyphen. How can I get around this problem.

Any help will be appreciated.

Thanks,

Vinki

It depends on how you're displaying it -

Also - will there be mulitple records returned?

You could keep the query the way it is - but, based on what is returned, with each row, write a function to do that for you, display-wise only.

|||If you want to do it within your query then look at the CASE statement -see here for details. The idea is you do something like:

SELECT Name, CASE Address + City WHEN NULL THEN NULL ELSE Address + ' - ' + City END FROM Table1

You'll need to put in more conditions, but that's the basic syntax.

Another alternative is to look at user-defined functions.|||I'd suggest this:

SELECT Name, Address + ISNULL(Address + City,'','-') + City FROM Table1

I don't understand this part of your requirement, though:

vinki wrote:

If name is null then I don't want to show hyphen

Checking for @@ERROR after a SELECT inside a transaction?

Is it normal practice to check for @.@.ERROR after a SELECT statement that retrieves data from a table OR we should only check for @.@.ERROR after a DELETE/INSERT/UPDATE type of statement?The SQL statement is inside a transaction.SELECT statements can return an error. Just do a find on this page for "error".

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

Checking field within a selected record (**)

Hello,
I have sql statement that is selecting multiple invoice records and doing
multiple calculations.
Now, I need to look in each invoice record for Item_detail, any direction on
how I would do that would be appreciated?
Thanks.Hi
Can you post DDL+ sample data+ expected result?
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
> on
> how I would do that would be appreciated?
> Thanks.|||is there a message board or something where we can put this as a disclaimer
:)|||That would be nice. Along with a post explaining how to return a
concatenated list of values for a single column in multiple rows, and a
"BEWARE OF --CELKO--" sign.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> is there a message board or something where we can put this as a
disclaimer :)|||Oh come on... We all love Celko... LOL
Grant
Who gives a {censored} if I am wrong.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> That would be nice. Along with a post explaining how to return a
> concatenated list of values for a single column in multiple rows, and a
> "BEWARE OF --CELKO--" sign.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> disclaimer :)
>|||of course we love him as long as we are not in the firing end :)
--
"Grant" wrote:

> Oh come on... We all love Celko... LOL
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>
>|||Just to explain what is meant by DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
on
> how I would do that would be appreciated?
> Thanks.|||Did I ask for something peculiar?
I thought this was a helping board.
"Omnibuzz" wrote:
> of course we love him as long as we are not in the firing end :)
> --
>
>
> "Grant" wrote:
>|||Sorry, we got a little off topic, it happens sometimes. Nothign at all odd
for what you asked for, only that it was very vague, and we could give a
hundred answers, most of which would be completely unrelated to what you are
trying to do. You did not include enough information for us to give you a
good answer. Please see my other post and respond with the needed
information.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:5B83C9D2-7A2C-46DD-B0DA-39D1B6483548@.microsoft.com...
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>
and a|||Oops.. sorry.. we thought we'll keep the thread alive till you get back with
the ddls and sample data.
--
"ITDUDE27" wrote:
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>

CHECKDB causes tempdb to grow

We run a DBCC CHECKDB statement against each of our
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan Jones
DBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones