Question regarding size of varchar field

Hi,
I am using MSDE together with Enterprise Manager.
I have a table with a field nameddescription.
This field will be filled by a web forms's textbox web control.
The textbox'smaxsize attribute is set to "3000" characters.
What size do I have to adjust for my DB fielddescription?
Is the size of3000 in Enterpise Manager equal to3000 characters for the textbox?
I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.Yes, you should set the width of your varchar column to 3000. This unit of measurement is actually bytes, but each character takes 1byte to store, so in effect the column can hold 3000 characters..
|||

I'm answering a question you didn't ask, but maxsize doesn't work if your textbox is multi-line. I just assumed it would be if you allowed that much in it. If you want to limit a multi-line textbox, you need to use a regular expression validator to do it.

|||Thanks for letting me know - and you are right... the texbox is indeed multi-line.
Maybe you can answer my question I have asked in another thread addressing a regular expression issue I am currently faced with - I am still waiting for some helpers there ...
This is the thread:
http://forums.asp.net/937464/ShowPost.aspx|||One more point is if you will use unicode (nvarchar or nchar data type), then physical size for a character will be doubled which means 2 bytes for a character.
If you run
sp_help TableName
you will see "Length" column which keeps physical length of column in bytes

Question regarding a Sql Query

Hello,

i have this query:
declare @.SubCompanyCode VARCHAR(6)
declare @.ProgramCode VARCHAR(10)
declare @.AttendeeTypeOption INT
DECLARE @.AttendeeTypeOptionOP CHAR(2)

set @.SubCompanyCode= 'A011'
set @.ProgramCode = 'ACL52B'
set @.AttendeeTypeOption= ''

If (@.AttendeeTypeOption<>'') SELECT @.AttendeeTypeOptionOP = 'EQ' ELSE SELECT @.AttendeeTypeOptionOP = ''

SELECT

M.City AS MeetingCity,
M.State AS MeetingState,
CASE
WHEN MA.AttendeeType = 1 THEN 'Participants'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'

END AS AttendeeType,
Count(A.AttendeeID) as NoofRSVP
FROM
Programs P
INNER
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @.SubCompanyCode AND
P.ProgramCode = @.ProgramCode AND

CASE @.AttendeeTypeOptionOP
WHEN '' THEN 1
WHEN 'EQ' THEN
CASE

WHEN MA.AttendeeType = 1 THEN 1

ELSE 0
END
END=1
GROUP BY

M.City,
M.State,
MA.AttendeeType
ORDER BY
MA.AttendeeType

this query returns all 4 rows even though there is no matching meetingID in MeetingAttendees table. I have to modify this query (or write another query) in such a away that if I run it for "Participants" only means MA.AttendeeType = 1 (I will pass @.AttendeeTypeOption=1 for this case) then NoofRSVP should show #of participants and if MA.AttendeeType <> 1 then NoofRSVP should show 0 and AttendeeType will be empty (means it should ignore 'Speaker/Faculty').

Thanks for all your help...Your question is unclear, and there are some odd things in your code.

For instance, why are you left-joining the Regions table and then not referencing it anywhere?

And this clause in your WHERE statement: AND CASE @.AttendeeTypeOptionOP
WHEN '' THEN 1
WHEN 'EQ' THEN CASE
WHEN MA.AttendeeType = 1 THEN 1
ELSE 0
END
END = 1...could be written more simply like this: AND (@.AttendeeTypeOptionOP = ''
OR (@.AttendeeTypeOptionOP = 'EQ' AND MA.AttendeeType = 1))

Now, we don't know what you mean by "this query returns all 4 rows", because we don't know what you data looks like or how many records there are in each table. But since you are using an outer join on the MeetingAttendees table you can expect the query not to consider missing records in that table.

Question on using ROW_NUMBER to remove duplicate rows in SQL 2005

Hi all--Given a table with the following definition in SQL Server 2005 SP2:

[DBA_AUDIT_SESSION](

[INSTANCE_NAME] [varchar](16) NULL,

[HOST_NAME] [varchar](64) NULL,

[OS_USERNAME] [varchar](255) NULL,

[USERNAME] [varchar](30) NULL,

[USERHOST] [varchar](128) NULL,

[TERMINAL] [varchar](255) NULL,

[TIMESTAMP] [datetime] NOT NULL,

[ACTION_NAME] [varchar](28) NULL,

[LOGOFF_TIME] [datetime] NULL,

[LOGOFF_LREAD] [float] NULL,

[LOGOFF_PREAD] [float] NULL,

[LOGOFF_LWRITE] [float] NULL,

[LOGOFF_DLOCK] [varchar](40) NULL,

[SESSIONID] [float] NOT NULL,

[RETURNCODE] [float] NOT NULL,

[CLIENT_ID] [varchar](64) NULL,

[SESSION_CPU] [float] NULL,

[Import_Date] [datetime] NULL

I am trying to remove duplicate rows using the Import_Date and SESSIONID fields as my identifying fields. Since I am new to the ROW_NUMBER() function, I am having problems with aggregate querying while trying to build up to a CTE and resolve this issue. For example, when I query with this:

SELECT ROW_NUMBER() OVER (order BY SESSIONID asc) AS ROWID, instance_name, sessionid, Import_date

FROM dba_audit_session

group by sessionid, instance_name, import_date

HAVING COUNT(sessionid)>1

I get zero rows, or a complaint about fields not being coupled with an aggregate function or not in a GROUPED BY clause if I use an * with the ROW_NUMBER() function here. Any thoughts about how I should proceed?

Thanks in advance,

Jonathan

I am not saying it will fix your code but it is the place to start, all aggregate functions in SQL Server ignores NULLs except COUNT(*); it counts all rows of the table. Run a search for COUNT(*) in the BOL. Hope this helps.|||Hey,

I am a noob.. but.. have a look at this..

http://support.microsoft.com/kb/139444

NOx
|||

Hmmm, that query worked fine for me.

What do you get when you run this:

Code Snippet

SELECT instance_name, sessionid, Import_date, count(*) as tally

FROM dba_audit_session

group by sessionid, instance_name, import_date

HAVING COUNT(sessionid)>1

|||You can delete all but the latest (in Import_Date, SESSIONID order) for each instance_name this way:

Code Snippet

with T_ranked as (
select
*,
rank() over (
partition by instance_name
order by Import_Date desc, SESSIONID desc
) as rk

from dba_audit_session
)
delete from T_ranked
where rk > 1

Steve Kass
Drew University
http://www.stevekass.com
|||

Hi--Found this one, liked how it got to the basics, but wanted to try working with SQL Server 2005's row_number() function based on this article:

http://www.databasejournal.com/features/mssql/article.php/3572301

The author of this article makes it look easy, but looks at a specific row in a resultset instead of a range of data.

|||

Use the following query. It will help you.

Code Snippet

;WITH CTE

as

(

SELECT

ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID

, instance_name

, sessionid

, Import_date

FROM

dba_audit_session

)

--Select * From CTE

Delete From CTE Where ROWID > 1

|||What if we don't know which rows are duplicates, meaning.. imagine a table with say 10000 rows and by a simple query you find out there are about 100 duplicate records.

What's the approach you would take in deleting these records?

Thanks
|||

Thank you both, Steve and Manivannan, for the tips on how to handle these with CTEs. These are exactly what I was looking for, and more. I've got several instances' worth of data to play with, so the partitioning by sessionid and the ranking by instance_name in both examples will come in handy. I will try them both out with sample data and let you know how they work out.

- Jonathan

|||

Hi Steve--I populated the test table with two copies of each row from two data sources, so I could check how your formula works. I just tried your ranking formula, substituting "select *" instead of the DELETE FROM statement so I could see the final result set before deletion of duplicates. The result set returns over 2.4 million rows, looking like this sample of duplicate data:

InstanceOS_UsernameHostSessionidImport_Date Rank

dwsmithhost11272912007-07-10 15:18:45.267 2

dwsmithhost11272912007-07-10 15:10:42.867 3

…

From what I can tell, the CTE as originally written will remove all but one row from the ranked and sorted data. Is there a way to tweak this CTE so that it checks for the sessionid of the row preceding the currently checked row for a duplicate entry and then removing the duplicate entry if one is found? (I tried tweaking it, but started to get into trouble with aggregate query messages when I attempted to do selects where the delete statement is written.)

Thanks in advance--Jonathan

|||

Hi--I think we're halfway there. I looked at your code, ran it with a sample of my data, and came up with a conclusion that I have an extra condition to handle. Given rowid > 1, I have to look at the Import_date and sessionid, compare the values from both rows, and keep the row given the latest Import_date. So, for example, if I have data like this:

1 dw 1053976 2007-07-10 15:10:42.867
1 dw 1053976 2007-07-10 15:18:45.267
1 dw 1053977 2007-07-10 15:10:42.867
1 dw 1053977 2007-07-10 15:18:45.267

...

I have to find the latest entry using a query like this:

SELECT

ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID,

instance_name,

sessionid,

Import_date

FROM dba_audit_session

where Import_Date < (select max(a.Import_Date) from DBA_AUDIT_SESSION as a

where a.sessionid = DBA_AUDIT_SESSION.sessionid);

Am I looking at another CTE for this?

|||

Hi--I think this is a good start point, and found I have an extra condition to meet.Given data like this:

Rowid instance_name sessionidImport_date

1 dw 1053976 2007-07-10 15:10:42.867

1 dw 1053976 2007-07-10 15:18:45.267

1 dw 1053977 2007-07-10 15:10:42.867

1 dw 1053977 2007-07-10 15:18:45.267

I have to keep the latest Import_date and remove prior entries.Given this query, I can make the selection:

SELECT ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID,

instance_name,

sessionid,

Import_date

FROM dba_audit_session

where Import_Date < (select max(a.Import_Date) from DBA_AUDIT_SESSION as a

where a.sessionid = DBA_AUDIT_SESSION.sessionid)

Do I need to nest this into a second CTE?

|||

Hi--I think this is a good start point, and found I have an extra condition to meet.Given data like this:

Rowid instance_name sessionidImport_date

1 dw 1053976 2007-07-10 15:10:42.867

1 dw 1053976 2007-07-10 15:18:45.267

1 dw 1053977 2007-07-10 15:10:42.867

1 dw 1053977 2007-07-10 15:18:45.267

I have to keep the latest Import_date and remove prior entries.Given this query, I can make the selection:

SELECT ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID,

instance_name,

sessionid,

Import_date

FROM dba_audit_session

where Import_Date < (select max(a.Import_Date) from DBA_AUDIT_SESSION as a

where a.sessionid = DBA_AUDIT_SESSION.sessionid)

Do I need to nest this into a second CTE?

|||I think you're missing the point. With rank(), you don't need any of this < (select max...) business. Just set the partition by and order by lists correctly so that the rows you want to keep are exactly the ones that get ranked #1.

Here's a wizard:

1. Fill in the blank. I want to keep one row for each .
2. Fill in the blank. The row I want to keep is first if I order by __.

Answer to 1 is your partition by list; answer to 2 is your order by list. My guess now is you want one row for each (instance_name, sessionid) combination, so it might be this (sessionid was in the order by list in my first try)

with T_ranked as (
select
*,
rank() over (
partition by instance_name, sessionid
order by Import_Date desc
) as rk

from dba_audit_session
)
delete from T_ranked
where rk > 1

If this still isn't it, it would be a huge help if you gave your desired answer along with sample data from which you want that answer.

SK
sql

Question on correct syntax for stored procedure

I have an ASP.NET datagrid in which I am passing the following:
PartNumber (which is actually a varchar)
PartType (varchar)
The PartType is just the description. In the PartType table I have two field
s:
ptID (what I actually need to access)
Description (what is being passed to the stored procedure)
What I want to do is insert into the Parts table the PartNumber (no problem)
and the ptID.
When I call existing records I use the following SQL statement:
SELECT p.PartNumber, p.ID as ptID, pt.description FROM parts p INNER JOIN
PartTypes pt on pt.ID =p.PartTypeID WHERE p.PartNumber = @.PartID;
What I am trying to figure out is where in the stored procedure do I fit the
inner join? This is what I have thus far:
@.PartNumber varchar (15),
@.PartType varchar (100),
AS
if NOT EXISTS (Select * from Parts where PartNumber = @.PartNumber)
BEGIN
insert into Parts
(PartNumber, PartTypeID)
VALUES
(
@.PartNumber
@.PartType <--but I don't actually want the PartType, I want the PartTypeID
)
END
How do I do this? Let me know if you need more info. Thanks!Leckey,
You can fire the query you have mentioned just before the insert statement
which
you have given in your stored procedure. Query would be something like
SELECT p.PartNumber, @.PART_ID = p.ID as ptID, pt.description FROM parts p
INNER JOIN
> PartTypes pt on pt.ID =p.PartTypeID WHERE p.PartNumber = @.PartID;
@.PART_ID need to be declared within the SP as an integer type variable. Now
you can this
@.PART_ID in your insert statement as the second param.
Hope this helps.
Regards,
Joe.
"leckey" <u23706@.uwe> wrote in message news:62b346eec9d0a@.uwe...
> I have an ASP.NET datagrid in which I am passing the following:
> PartNumber (which is actually a varchar)
> PartType (varchar)
> The PartType is just the description. In the PartType table I have two
fields:
> ptID (what I actually need to access)
> Description (what is being passed to the stored procedure)
> What I want to do is insert into the Parts table the PartNumber (no
problem)
> and the ptID.
> When I call existing records I use the following SQL statement:
> SELECT p.PartNumber, p.ID as ptID, pt.description FROM parts p INNER JOIN
> PartTypes pt on pt.ID =p.PartTypeID WHERE p.PartNumber = @.PartID;
> What I am trying to figure out is where in the stored procedure do I fit
the
> inner join? This is what I have thus far:
> @.PartNumber varchar (15),
> @.PartType varchar (100),
> AS
> if NOT EXISTS (Select * from Parts where PartNumber = @.PartNumber)
> BEGIN
> insert into Parts
> (PartNumber, PartTypeID)
>
> VALUES
> (
> @.PartNumber
> @.PartType <--but I don't actually want the PartType, I want the PartTypeID
> )
> END
> How do I do this? Let me know if you need more info. Thanks!

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
--
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> hi,
>> I have a t-sql script as follows,
>> Declare @.sql varchar(1000)
>> set @.sql = select count(*) from table1
>> exec (@.sql)
>> I want to know if recordcount = 0
>> then go line1
>> else go line2
>> How do I assign the return value(recordcount)
>> Thanks
>>
>

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>