embedded control characters

Beyond my control: I am finding control characters (likely tab) is
making its way into address fields of our operational system. This is
messing me up when I load the data into our warehouse w/ BCP (fields
get shifted).
Is the a nifty way to strip control characters from data?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> Beyond my control: I am finding control characters (likely tab) is
> making its way into address fields of our operational system. This is
> messing me up when I load the data into our warehouse w/ BCP (fields
> get shifted).
> Is the a nifty way to strip control characters from data?

UPDATE tbl
SET col = replace(col, char(9), ' ')
WHERE col LIKE '%' + char(9) + '%'

You could have to nest replace, if there are more characters you want
to kill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

email to sql server - how?

I am developing support ticket system and i want to know who's going to take care of the email sent to support@.domain.com, who's going to generate the ticket number, send automatic email and add the entry to the sql server?

You can create a .NET application that watches the mailbox,

OR,

you can explore using the SQL 2005 Service Broker service.

SQL Server 2005 DOES NOT receive email -and that is VERY GOOD, since it would be a security nightmare.

|||

One very easy way is to use VBA in Outlook to loop through the new mails and process them. Look in the newsgroup for outlook and there'll loads of examples. Maybe Exchange will let you do something similar to create a clientless server based solution, but i don;t know exchange very well.

Regards, Gert-Jan

sql

email to sql server - how?

I am developing support ticket system and i want to know who's going to take care of the email sent to support@.domain.com, who's going to generate the ticket number, send automatic email and add the entry to the sql server?

You can create a .NET application that watches the mailbox,

OR,

you can explore using the SQL 2005 Service Broker service.

SQL Server 2005 DOES NOT receive email -and that is VERY GOOD, since it would be a security nightmare.

|||

One very easy way is to use VBA in Outlook to loop through the new mails and process them. Look in the newsgroup for outlook and there'll loads of examples. Maybe Exchange will let you do something similar to create a clientless server based solution, but i don;t know exchange very well.

Regards, Gert-Jan

email task and file system deployment

does email task only work with sql server deployment? I have an email task in my ssis package and i want go for file system deployment.

thanks,

kushpaw

It should work on both deployments.
I use file system deployment and get all emails successfully

EMail Delivery in Reporting Services 2000

Does Reporting Services 2000 have a email Delivery system for report subscriptions ? if so is there any documentation to back this up.

All i am finding is 2005 information

Any help would be great , Thanks in advance

NEver mind i think i found it in the books online.

Thanks anyway

email being sent, but no message

I have setup an email notifications system, that basically takes each
row from a table and sents out an email according to the data in that
row. The emails get sent, with the subject being filled as expected.
Only problem is that sometimes there is no message.

Here is the stored procedure that is being called every hour to send
the emails:

CREATE PROCEDURE dbo.RemindersSendEmails AS

--Cursor
DECLARE RemindersCursor CURSOR FOR
SELECT *
FROM RemindersTodaysAndUnsent

--Values for cursor
DECLARE
@.I_Reminder_ID bigint,
@.I_Notice_ID bigint,
@.V_Reminder_Text varchar(250),
@.SDT_Reminder_Date smalldatetime,
@.V_Email varchar(50),
@.I_Reminder_Type bigint,
@.SDT_Reminder_Sent smalldatetime,
@.I_Attempts_Made int,
@.V_Notice_Type varchar(50),
@.I_Notice_Period int,
@.V_Period_Description varchar(50),
@.I_Project_ID bigint,
@.V_Notice_Ref varchar(10)

--values for sending the mail
DECLARE @.NEWLINE varchar(2)

OPEN RemindersCursor

FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
--INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
--@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
--@.I_Project_ID, @.V_Notice_Ref

SET @.NEWLINE = char(10)

--PRINT 'start'

WHILE @.@.FETCH_STATUS = 0
BEGIN

DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
int

SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
Notice Ref ' + RTRIM(@.V_Notice_Ref)

SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
@.I_Project_ID)) + @.NEWLINE +
'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
RTRIM(@.V_Period_Description) + @.NEWLINE +
'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
@.NEWLINE +
'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
'Email sent to: ' + @.V_Email + @.NEWLINE +
'Number of attempts made at sending this email (once every hour): '
+ CONVERT(varchar(4), @.I_Attempts_Made)
--@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
@.I_Notice_Period,

PRINT 'subject = ' + @.Subject
PRINT 'message = ' + @.EmailMessage

SET @.V_Email = LTRIM(RTRIM(@.V_Email))

EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
@.message = @.EmailMessage,
@.subject = @.Subject

--PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
@.Status)

--If send mail is a success
IF (@.Status = 0)
BEGIN
UPDATE Reminders
SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
@.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END
--Else send mail failed
ELSE
BEGIN
UPDATE Reminders
SET I_Attempts_Made = @.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END

-- Get the next reminder
FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
END

--PRINT 'End'

CLOSE RemindersCursor
DEALLOCATE RemindersCursor
GOJust a guess-- I'd check to see if any of your variables are NULL the SET
statement that builds your @.EmailMessage variable. In that case maybe your
entire @.EmailMessage variable is getting set to NULL?

"Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> I have setup an email notifications system, that basically takes each
> row from a table and sents out an email according to the data in that
> row. The emails get sent, with the subject being filled as expected.
> Only problem is that sometimes there is no message.
> Here is the stored procedure that is being called every hour to send
> the emails:
> CREATE PROCEDURE dbo.RemindersSendEmails AS
> --Cursor
> DECLARE RemindersCursor CURSOR FOR
> SELECT *
> FROM RemindersTodaysAndUnsent
> --Values for cursor
> DECLARE
> @.I_Reminder_ID bigint,
> @.I_Notice_ID bigint,
> @.V_Reminder_Text varchar(250),
> @.SDT_Reminder_Date smalldatetime,
> @.V_Email varchar(50),
> @.I_Reminder_Type bigint,
> @.SDT_Reminder_Sent smalldatetime,
> @.I_Attempts_Made int,
> @.V_Notice_Type varchar(50),
> @.I_Notice_Period int,
> @.V_Period_Description varchar(50),
> @.I_Project_ID bigint,
> @.V_Notice_Ref varchar(10)
> --values for sending the mail
> DECLARE @.NEWLINE varchar(2)
> OPEN RemindersCursor
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> -- @.I_Project_ID, @.V_Notice_Ref
> SET @.NEWLINE = char(10)
> --PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> int
> SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> Notice Ref ' + RTRIM(@.V_Notice_Ref)
> SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> @.I_Project_ID)) + @.NEWLINE +
> 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> RTRIM(@.V_Period_Description) + @.NEWLINE +
> 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> @.NEWLINE +
> 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> 'Email sent to: ' + @.V_Email + @.NEWLINE +
> 'Number of attempts made at sending this email (once every hour): '
> + CONVERT(varchar(4), @.I_Attempts_Made)
> --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> @.I_Notice_Period,
> PRINT 'subject = ' + @.Subject
> PRINT 'message = ' + @.EmailMessage
> SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> @.message = @.EmailMessage,
> @.subject = @.Subject
> --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> @.Status)
> --If send mail is a success
> IF (@.Status = 0)
> BEGIN
> UPDATE Reminders
> SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
> --Else send mail failed
> ELSE
> BEGIN
> UPDATE Reminders
> SET I_Attempts_Made = @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
>
> -- Get the next reminder
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> END
> --PRINT 'End'
> CLOSE RemindersCursor
> DEALLOCATE RemindersCursor
> GO|||"MissLivvy" <XeveryidiwantistakenX@.yahoo.com> wrote in message
news:jeRqd.3625$u81.3215@.newsread3.news.pas.earthl ink.net...
> Just a guess-- I'd check to see if any of your variables are NULL the SET
> statement that builds your @.EmailMessage variable. In that case maybe your
> entire @.EmailMessage variable is getting set to NULL?

Ah good one.

This got me once. I forgot abuot it.

> "Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
> news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> > I have setup an email notifications system, that basically takes each
> > row from a table and sents out an email according to the data in that
> > row. The emails get sent, with the subject being filled as expected.
> > Only problem is that sometimes there is no message.
> > Here is the stored procedure that is being called every hour to send
> > the emails:
> > CREATE PROCEDURE dbo.RemindersSendEmails AS
> > --Cursor
> > DECLARE RemindersCursor CURSOR FOR
> > SELECT *
> > FROM RemindersTodaysAndUnsent
> > --Values for cursor
> > DECLARE
> > @.I_Reminder_ID bigint,
> > @.I_Notice_ID bigint,
> > @.V_Reminder_Text varchar(250),
> > @.SDT_Reminder_Date smalldatetime,
> > @.V_Email varchar(50),
> > @.I_Reminder_Type bigint,
> > @.SDT_Reminder_Sent smalldatetime,
> > @.I_Attempts_Made int,
> > @.V_Notice_Type varchar(50),
> > @.I_Notice_Period int,
> > @.V_Period_Description varchar(50),
> > @.I_Project_ID bigint,
> > @.V_Notice_Ref varchar(10)
> > --values for sending the mail
> > DECLARE @.NEWLINE varchar(2)
> > OPEN RemindersCursor
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > -- @.I_Project_ID, @.V_Notice_Ref
> > SET @.NEWLINE = char(10)
> > --PRINT 'start'
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> > int
> > SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> > Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> > Notice Ref ' + RTRIM(@.V_Notice_Ref)
> > SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> > @.I_Project_ID)) + @.NEWLINE +
> > 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> > 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> > RTRIM(@.V_Period_Description) + @.NEWLINE +
> > 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> > 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> > @.NEWLINE +
> > 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> > 'Email sent to: ' + @.V_Email + @.NEWLINE +
> > 'Number of attempts made at sending this email (once every hour): '
> > + CONVERT(varchar(4), @.I_Attempts_Made)
> > --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> > @.I_Notice_Period,
> > PRINT 'subject = ' + @.Subject
> > PRINT 'message = ' + @.EmailMessage
> > SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> > EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> > @.message = @.EmailMessage,
> > @.subject = @.Subject
> > --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> > @.Status)
> > --If send mail is a success
> > IF (@.Status = 0)
> > BEGIN
> > UPDATE Reminders
> > SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> > @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > --Else send mail failed
> > ELSE
> > BEGIN
> > UPDATE Reminders
> > SET I_Attempts_Made = @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > -- Get the next reminder
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > END
> > --PRINT 'End'
> > CLOSE RemindersCursor
> > DEALLOCATE RemindersCursor
> > GO

e-mail about a virus

My e-mail says I have a virus called Mydoom@.MMviruses
it was from System anti virus administrator, it said
mentioned Postmaster@.atlasdev.com. Is this real? What
should I do? Miigwech(thanks)That's the new one that's been going around this week:
http://www.cnn.com/2004/TECH/intern...26/mydoom.worm/
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||for the latest on MyDoom, see http://www.microsoft.com/security/.
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply only to the newsgroup.
"nancy" <anonymous@.discussions.microsoft.com> wrote in message
news:5ac701c3e5d0$bcdf3990$a101280a@.phx.gbl...

quote:

---

> My e-mail says I have a virus called Mydoom@.MMviruses
> it was from System anti virus administrator, it said
> mentioned Postmaster@.atlasdev.com. Is this real? What
> should I do? Miigwech(thanks)

---

eliminating redundant data

edit: this came out longer than I thought, any comments about anything
here is greatly appreciated. thank you for reading

My system stores millions of records, each with fields like firstname,
lastname, email address, city, state, zip, along with any number of user
defined fields. The application allows users to define message templates
with variables. They can then select a template, and for each variable
in the template, type in a value or select a field.

The system allows you to query for messages you've sent by specifying
criteria for the variables (not the fields).

This requirement has made it difficult to normalize my datamodel at all
for speed. What I have is this:

[fieldindex]
id int PK
name nvarchar
type datatype

[recordindex]
id int PK
...

[recordvalues]
recordid int PK
fieldid int PK
value nvarchar

whenever messages are sent, I store which fields were mapped to what
variables for that deployment. So the query with a variable criteria
looks like this:

select coalesce(vm.value, rv.value)
from sentmessages sm
inner join variablemapping vm on vm.deploymentid=sm.deploymentid
left outer join recordvalues rv on
rv.recordid=sm.recordid and rv.fieldid=vm.fieldid
where coalesce(vm.value, rv.value) ...

this model works pretty well for searching messages with variable
criteria and looking up variable values for a particular message. the
big problem I have is that the recordvalues table is HUGE, 1 million
records with 50 fields each = 50 million recordvalues rows. The value,
two int columns plus the two indexes I have on the table make it into a
beast. Importing data takes forever. Querying the records (with a field
criteria) also takes longer than it should.

makes sense, the performance was largely IO bound.

I decided to try and cut into that IO. looking at a recordvalues table
with over 100 million rows in it, there were only about 3 million unique
values. so I split the recordvalues table into two tables:

[recordvalues]
recordid int PK
fieldid int PK
valueid int

[valueindex]
id int PK
value nvarchar (unique)

now, valueindex holds 3 million unique values and recordvalues
references them by id. to my suprise this shaved only 500mb off a 4gb
database!

importing didn't get any faster either, although it's no longer IO bound
it appears the cpu as the new bottleneck outweighed the IO bottleneck.
this is probably because I haven't optimized the queries for the new
tables (was hoping it wouldn't be so hard w/o the IO problem).

is there a better way to accomplish what I'm trying to do? (eliminate
the redundant data).. does SQL have built-in constructs to do stuff like
this? It seems like maybe I'm trying to duplicate functionality at a
high level that may already exist at a lower level.

IO is becoming a serious bottleneck.
the million record 50 field csv file is only 500mb. I would've thought
that after eliminating all the redundant first name, city, last name,
etc it would be less data and not 8x more!

-
Gordon

Posted Via Usenet.com Premium Usenet Newsgroup Services
------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
------------------
http://www.usenet.comNo database vendor, version, platform... Why are you using tables as indexes
when you can use indexes as indexes? Normalizing a data model generally
slows it down. Denormalizing generally speeds things up.

"gordy" <gordy@.dynamicsdirect.com> wrote in message
news:40be960f$1_1@.Usenet.com...
> edit: this came out longer than I thought, any comments about anything
> here is greatly appreciated. thank you for reading
> My system stores millions of records, each with fields like firstname,
> lastname, email address, city, state, zip, along with any number of user
> defined fields. The application allows users to define message templates
> with variables. They can then select a template, and for each variable
> in the template, type in a value or select a field.
> The system allows you to query for messages you've sent by specifying
> criteria for the variables (not the fields).
> This requirement has made it difficult to normalize my datamodel at all
> for speed. What I have is this:
> [fieldindex]
> id int PK
> name nvarchar
> type datatype
> [recordindex]
> id int PK
> ...
> [recordvalues]
> recordid int PK
> fieldid int PK
> value nvarchar
> whenever messages are sent, I store which fields were mapped to what
> variables for that deployment. So the query with a variable criteria
> looks like this:
> select coalesce(vm.value, rv.value)
> from sentmessages sm
> inner join variablemapping vm on vm.deploymentid=sm.deploymentid
> left outer join recordvalues rv on
> rv.recordid=sm.recordid and rv.fieldid=vm.fieldid
> where coalesce(vm.value, rv.value) ...
> this model works pretty well for searching messages with variable
> criteria and looking up variable values for a particular message. the
> big problem I have is that the recordvalues table is HUGE, 1 million
> records with 50 fields each = 50 million recordvalues rows. The value,
> two int columns plus the two indexes I have on the table make it into a
> beast. Importing data takes forever. Querying the records (with a field
> criteria) also takes longer than it should.
> makes sense, the performance was largely IO bound.
> I decided to try and cut into that IO. looking at a recordvalues table
> with over 100 million rows in it, there were only about 3 million unique
> values. so I split the recordvalues table into two tables:
> [recordvalues]
> recordid int PK
> fieldid int PK
> valueid int
> [valueindex]
> id int PK
> value nvarchar (unique)
> now, valueindex holds 3 million unique values and recordvalues
> references them by id. to my suprise this shaved only 500mb off a 4gb
> database!
> importing didn't get any faster either, although it's no longer IO bound
> it appears the cpu as the new bottleneck outweighed the IO bottleneck.
> this is probably because I haven't optimized the queries for the new
> tables (was hoping it wouldn't be so hard w/o the IO problem).
> is there a better way to accomplish what I'm trying to do? (eliminate
> the redundant data).. does SQL have built-in constructs to do stuff like
> this? It seems like maybe I'm trying to duplicate functionality at a
> high level that may already exist at a lower level.
> IO is becoming a serious bottleneck.
> the million record 50 field csv file is only 500mb. I would've thought
> that after eliminating all the redundant first name, city, last name,
> etc it would be less data and not 8x more!
> -
> Gordon
> Posted Via Usenet.com Premium Usenet Newsgroup Services
> ------------------
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ------------------
> http://www.usenet.com|||> No database vendor, version, platform... Why are you using tables as indexes
> when you can use indexes as indexes? Normalizing a data model generally
> slows it down. Denormalizing generally speeds things up.

sorry, I'm using MS SQL2000

How can I use indexes as indexes? I mean, in the example I posted, can
you give an example?

Posted Via Usenet.com Premium Usenet Newsgroup Services
------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
------------------
http://www.usenet.com|||Well, I don't know SQL Server, but in Oracle, you create an index using the
CREATE INDEX statement. I suspect it works the same or similar in SQL
Server.

Here's an Oracle example that creates an index called
"asearch_client_id_idx" on the client_id field in a table called
"alphasearch" owned by user "alphasearch":

CREATE INDEX ALPHASEARCH.ASEARCH_CLIENT_ID_IDX
ON ALPHASEARCH.ALPHASEARCH(CLIENT_ID);

Now, I am about to lie a little bit for simplicity's sake, but here goes...

When a query is executed that uses client_id as a search or sort criteria,
the Oracle optimizer will decide whether or not to use the index. If it
does, it looks up the values needed in the index, and retrieves their row
ids, which in turn are essentially pointers to the location of the data in
data blocks on disc, so it goes dirctly to that location on disc and
retrieves the data out of the blocks. It does not need to go logically into
the table. Note that what I refer to as row_id in Oracle may not be the same
concept in SQL Server.

Hope you get the general idea, and you should consult your documentation
about indexes.

"gordy" <gordy@.dynamicsdirect.com> wrote in message
news:40bf5ec1$1_1@.Usenet.com...
> > No database vendor, version, platform... Why are you using tables as
indexes
> > when you can use indexes as indexes? Normalizing a data model generally
> > slows it down. Denormalizing generally speeds things up.
> sorry, I'm using MS SQL2000
> How can I use indexes as indexes? I mean, in the example I posted, can
> you give an example?
> Posted Via Usenet.com Premium Usenet Newsgroup Services
> ------------------
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ------------------
> http://www.usenet.com|||> Well, I don't know SQL Server, but in Oracle, you create an index using the
> CREATE INDEX statement. I suspect it works the same or similar in SQL
> Server.
> Here's an Oracle example that creates an index called
> "asearch_client_id_idx" on the client_id field in a table called
> "alphasearch" owned by user "alphasearch":
> CREATE INDEX ALPHASEARCH.ASEARCH_CLIENT_ID_IDX
> ON ALPHASEARCH.ALPHASEARCH(CLIENT_ID);

wow, what a concept ;)

I appreciate the criticism.. after all that's the intent of my original
post, however, I would prefer it to be of the constructive variety.

In my system, there are 'fields' and there are 'variables'. the user
creates the relationships between them whenever they send a message. in
order to search for messages by 'variable' values, sql needs a
relationship of its own to translate between them.

this has kept me from being able to use the obvious:
[records]
id,field1,field2,field3,...

because in a query for 'variable1', depending on the message it may have
to look at 'field3' or 'field4' for the value. this requirement is why I
have the tables I have now (recordindex, fieldindex and recordvalues).

I realize this makes for very large indexes.. and like you said, the
table itself is nothing more than a big index. This is the problem I'd
like to solve. In my original post I explained how I attempted to
eliminate redundant data, but I only eliminated 500mb (of 4gb) because
the majority of volume in this db isn't the data itself, but the index size.

Posted Via Usenet.com Premium Usenet Newsgroup Services
------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
------------------
http://www.usenet.com|||"gordy" <gordy@.dynamicsdirect.com> wrote in message
news:40bf9c49$1_1@.Usenet.com...
> > Well, I don't know SQL Server, but in Oracle, you create an index using
the
> > CREATE INDEX statement. I suspect it works the same or similar in SQL
> > Server.
> > Here's an Oracle example that creates an index called
> > "asearch_client_id_idx" on the client_id field in a table called
> > "alphasearch" owned by user "alphasearch":
> > CREATE INDEX ALPHASEARCH.ASEARCH_CLIENT_ID_IDX
> > ON ALPHASEARCH.ALPHASEARCH(CLIENT_ID);
> wow, what a concept ;)
> I appreciate the criticism.. after all that's the intent of my original
> post, however, I would prefer it to be of the constructive variety.

It wasn't criticism. I thought you really didn't know about RDBMS indexes,
being that you essentially created your own.

> In my system, there are 'fields' and there are 'variables'. the user
> creates the relationships between them whenever they send a message. in
> order to search for messages by 'variable' values, sql needs a
> relationship of its own to translate between them.
> this has kept me from being able to use the obvious:
> [records]
> id,field1,field2,field3,...
> because in a query for 'variable1', depending on the message it may have
> to look at 'field3' or 'field4' for the value. this requirement is why I
> have the tables I have now (recordindex, fieldindex and recordvalues).
> I realize this makes for very large indexes.. and like you said, the
> table itself is nothing more than a big index. This is the problem I'd
> like to solve. In my original post I explained how I attempted to
> eliminate redundant data, but I only eliminated 500mb (of 4gb) because
> the majority of volume in this db isn't the data itself, but the index
size.

The only similar situation I've seen like this (home-brew index constructs)
is with a document imaging system called FileNET. In that case, the vendor
actually created its own mini RDBMS to handle just these index/table
constructs. It was very fast, but, of course, proprietary.

It's hard to tell exactly what you are trying to do, though. Could you get
into the business requirements a bit? It would help me to understand what
you need to do. It get the feeling from the solution you came up with that
you are a programmer, not a DBA.

> Posted Via Usenet.com Premium Usenet Newsgroup Services
> ------------------
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ------------------
> http://www.usenet.com

EFS,SQL Server 2005 and Windows 2003

Has anyone used the Encrypting File System (EFS) capabilities of the OS to
encrypt the sql server 2005 data files? I am looking to do this but cannot
find any examples/instructions. Thanks.A little old but a good starting point nonetheless:
http://msdn2.microsoft.com/en-us/library/aa302434.aspx
Just remember to be consistent about what you use to perform the encryption
and the SQL Server service account both during setup and when you change
passwords. Getting locked out of your own DB sounds funny but really isn't.
joe.
"SQLUSER07" <SQLUSER07@.discussions.microsoft.com> wrote in message
news:F669E408-E3FC-41F6-8616-E95E5DA7A108@.microsoft.com...
> Has anyone used the Encrypting File System (EFS) capabilities of the OS to
> encrypt the sql server 2005 data files? I am looking to do this but cannot
> find any examples/instructions. Thanks.