Help with a delete query

Hi

i need help with this task i can't think how to do better.
I have a table with MyField1, MyField2, Cost, SomeOtherField

For each couple of MyField1, MyField2 i have several rows.
I need to delete some rows so that:
- for each couple of MyField1, MyField2 i have at most @.MaxNumber rows remaining and
- for each pair MyField1,MyField2 the Cost in the remaining rows is never more than a certain coefficient @.K times the cost of the least costly row of the pair (each MyField1,MyField2 has its own minimum cost).

I did this easily with a Cursor but i needed better performances.
I tried with this:

delete from MyTable from MyTable as p1 where
0 = (select (1 + sign(@.MaxNumber -1-count(*))) * (1 + sign(min(p2.Cost) * @.K- p1.Cost) )
from MyTable p2 where p2.MyField1 = p1.MyField1 and p2.MyField2 = p1.MyField2 and p2.Cost< p1.Cost)

What's happening here ? for each row i count how many rows are there with the same MyFields that have cost less than @.K*MinValue , i multiply the two difference between the value i need and the real value and only when one of these values are zero i know that the product is zero and so i can choose the row to be deleted. Note that the double "FROM" in the query is no mistake.
This is terribly involuted, it works better than the cursor but i am sure there must be a better way to do this !!

Could You please help me ?

Thankx

Wentuhi,

can you post a sample data?|||

Try This...

Code Snippet

Delete

From mytable

From mytable as Main

Join

(

Select p1.*

From mytable p1

Join mytable p2 Onp2.MyField1 = p1.MyField1

and p2.MyField2 = p1.MyField2

and p2.Cost< p1.Cost

Group By

p1.MyField1, p1.MyField2, p1.Cost

Having

(1 + sign(@.MaxNumber -1-count(*))) * (1 + sign(min(p2.Cost) * @.K- p1.Cost)) =0

) as Sub

OnSub.MyField1 = Main.MyField1 and Sub.MyField2 = Main.MyField2 and Sub.Cost = Main.Cost

|||

Manni's query looks better; nonetheless, I put together a mockup. The mockup used the SMALL_ITERATOR and DBO.RAND() objects that can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

The table definition that I used is:

create table dbo.myTable
( rid integer,
myField1 integer,
myField2 integer,
cost numeric (9,2),

constraint pk_myTable primary key (myField1, myField2, cost, rid)
)
go

insert into dbo.myTable
select iter,
1 + (iter/35),
5*dbo.rand(),
33*dbo.rand() + 33*dbo.rand()
+ 34*dbo.rand()
from small_iterator (nolock)
where iter <= 40
select * from dbo.myTable

And the query is:

--select * from myTable where myField1 = 1 and myField2 = 1 order by cost
/*
rid myField1 myField2 cost
-- -- --
8 1 1 32.97
6 1 1 42.40
25 1 1 44.39
3 1 1 50.12
34 1 1 56.03
5 1 1 58.37
17 1 1 64.41
13 1 1 65.66
24 1 1 75.05
*/

declare @.maxRows integer set @.maxRows = 4
declare @.boundary numeric (6,2) set @.boundary = 1.5

;with theMinCost as
( select myField1,
myField2,
@.boundary * min(cost) as maxCost
from myTable
group by myField1, myField2
), seqTable as
( select row_number () over
( partition by myField1, myField2
order by cost, rid
) as seq,
myField1,
myField2,
cost,
rid
from myTable
), candidates as
( select a.myField1,
a.myField2,
a.seq,
a.cost,
b.maxCost,
a.rid
from seqTable a
inner join theMinCost b
on a.myField1 = b.myField1
and a.myField2 = b.myField2
and ( a.seq > @.maxRows or
a.cost > b.maxCost
)
)

--select * from theMinCost where myField1=1 and myField2=1
/*
myField1 myField2 maxCost
-- -- -
1 1 49.4550
*/

/*
select * from candidates
where myField1 = 1
and myField2 = 1

myField1 myField2 seq cost maxCost rid
-- -- -- -- --
1 1 4 50.12 49.4550 3
1 1 5 56.03 49.4550 34
1 1 6 58.37 49.4550 5
1 1 7 64.41 49.4550 17
1 1 8 65.66 49.4550 13
1 1 9 75.05 49.4550 24
*/

delete from myTable
from myTable a
inner join candidates b
on a.myField1 = b.myField1
and a.myField2 = b.myField2
and a.rid = b.rid

select * from myTable where myField1 = 1 and myField2 = 1 order by cost

/*
rid myField1 myField2 cost
- -- -- --
8 1 1 32.97
6 1 1 42.40
25 1 1 44.39
*/

|||Wentu,

If you are using SQL Server 2005, try this. It may be efficient and relatively easy to understand and maintain. (I'm using Kent's table structure.)

Code Snippet

with WithMins(rid, myField1, myField2, minCost, cost) as (
select
rid,
myField1,
myField2,
min(cost) over (
partition by myField1, myField2
) as minCost,
cost
from myTable
), Cheaps(rid, myField1, myField2, cost, minCost, n) as (
select
rid,
myField1,
myField2,
cost,
minCost,
row_number() over (
partition by myField1, myField2
order by myField1, myField2, cost
-- cost <= @.K*minCost rows are numbered lowest
) as n
from WithMins
)
delete from Cheaps
where cost > @.K*minCost
or n > @.MaxNumber

Steve Kass
Drew University
http://www.stevekass.com
|||:-) (Admiring the work)|||Hi all

let me understand all Your codes, try them , and i'll let You know.
I want to thankx all of You for Your efforts, time and help !
c ya soon

Wentu

Help with "IN" in stored procedure.

I have a stored procedure which is performing a search against a "task"
table. I would like to pass in a variable called @.strAssignedTo which
will be a comma delimeted list of employee ID's ie: "5,6,10". So my SQL
without this variable would be something like:

WHERE intAssignedTo IN (5,6,10)

but when I try to do:

WHERE intAssignedTo IN (@.strAssignedTo)

I get an error saying "cannot convert strAssignedTo to an integer"

What's the proper way to do this? THANKS[posted and mailed, please reply in news]

(erich93063@.gmail.com) writes:
> I have a stored procedure which is performing a search against a "task"
> table. I would like to pass in a variable called @.strAssignedTo which
> will be a comma delimeted list of employee ID's ie: "5,6,10". So my SQL
> without this variable would be something like:
> WHERE intAssignedTo IN (5,6,10)
> but when I try to do:
> WHERE intAssignedTo IN (@.strAssignedTo)
> I get an error saying "cannot convert strAssignedTo to an integer"

Which is because there is no macro expansion going on here. Keep in
mind that you can say things like

WHERE x IN (@.a, @.b, @.c)

Hm, there is actually a macro expansion going on here, since the above
is internally rewritten to

WHERE x = @.a OR x = @.b OR x = @.c

Anyway, for how to this, have a look at
http://www.sommarskog.se/arrays-in-...ist-of-integers.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply but I couldnt make heads or tails of that
article. It looked like I needed to make a user defined function maybe?
(iter_intlist_to_table )? which I did but then when I tried to do:

SELECT ...
FROM tblTask
WHERE ...
AND intAssignedTo IN
(iter_intlist_to_table(@.strAssignedTo))

I got an error saying that the function "iter_intlist_to_table" did not
exist. I'm fairly new to stored procedures and SQL in general so that
article was a bit over my head.|||Erich93063 (erich93063@.gmail.com) writes:
> Thanks for your reply but I couldnt make heads or tails of that
> article. It looked like I needed to make a user defined function maybe?

Yes, that is what the gist of. (There are other methods, but using a
UDF is the best.)

> (iter_intlist_to_table )? which I did but then when I tried to do:
> SELECT ...
> FROM tblTask
> WHERE ...
> AND intAssignedTo IN
> (iter_intlist_to_table(@.strAssignedTo))
> I got an error saying that the function "iter_intlist_to_table" did not
> exist. I'm fairly new to stored procedures and SQL in general so that
> article was a bit over my head.

Well, it is easier, if you mimick the example in the article, rather
than trying your own syntax. That's a table-valued function, and
you use a table-valued function just like you use a table. The example
uses JOIN, although in retrospect, I should probably have used EXISTS
instead:

CREATE PROCEDURE get_product_names_iter @.ids varchar(50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
WHERE EXISTS (SELECT *
FROM iter_intlist_to_table(@.ids) i
WHERE P.ProductID = i.number)
go

But that's a matter of style only.

Some parts of the article, for instance all the performance tests, are
certainly above novice level. But that is also why I gave a direct
link to a function and example on how to use it.

I suggest that you copy the function, and play with the example in
the article. No way is better to learn, than getting your hands on it
yourself. May take little longer for the actual task at hand, but next
time you need it, you know it better.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is a common Newbie question. This shows that you don't know what
a scalar parameter is. This is MUCH worse than not knowing SQL. This
is a fundamental programming concept that you should learn in the first
week of any language class.

1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter;
it is not code. This is not just an SQL problem; this is a basic
misunderstanding of programming of principles.

2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.

You can then write:

SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);

Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice.

3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array", like this in SQL/PSM
(translate into your local dialect):

CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM ( VALUES (p1), (p2), .., (pN))
AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions>)
AND <more predicates>;
<more code>;
END;

3) The right way! You load the Parmlist table with values so that each
value is validated by the SQL engine, subject to more constraints and
you have no SQL injection problems. A good optimizer will not need the
SELECT DISTINCT, just a SELECT.

Help Using VBScript in an ActiveX task in DTS Package

Hi,

Thanks for reading.

I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part

I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.

Can any one offer suggestions on the best way to do this.

I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.

Thanks in advance,
SteveThere are a couple easy ways to do this that I can think of:

1) Open the file up before import and delete the last record, then import to SQL Server.
2) Import to a temporary table that has a IDENTITY field in it, then delete the row with the highest value, then import to normal table.
3) If you want to delete the last line because it's an abnormal line (not a suitable record to go into the db), then just allow a certain number of errors. This way it'll basically error out without inserting the line.

The 1st solution needs the VBScript you're looking for. The problem I think with that is that the TextStream Object that you're looking for is a forward only object. This means that you'd have to open it, read each line at a time keeping track of which line you were on with some sort of local variable, then identify when you've reached the end of the file. Then you'd have to close the file, open it again, then read the file till you got to the last line (which you'd now know was the last line because of your local variable(s) that you initialized last time. Then you could delete that line. Here's the link for documentationhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjtextstream.asp

The 2nd solution doesn't involve any VBScript, and would probably be simpler to explain and troubleshoot.

The 3rd solution was just a possible guess at what you're trying to do.

David

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this?
Any help would be appreciated.
Thanks
Vishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this?
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help sending email

My code below works fine when run from my pc (changed all the values for
obvious reasons). The code is placed inside a DTS task via VBS scripting.
But when I try to run directly from the server where sqlserver is installed,
the script fails.

I have SMTP running, but there is no outlook installed.

Can someone please advise what I am missing.
Thanks
Bob

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "send@.test.com"
objEmail.To = "receive@.test.com"
objEmail.Subject = "TEST SUBJECT"
objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send

set objEmail = nothingHi B

One thing you might try is change

> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
to
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = "2"

quotes around the 2.

I spent several hours a week ago trying the very same thing and that made
all the difference.

Also is \\server\test.csv accessible from the server you are running this
on?

Here is the full text of the DTS Package I wrote. Note that I don't think
all the fields you included are necessary.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
email_alert "dchristo@.yahoo.com", "George_Bush@.whitehouse.gov","Test
Subject", "Test Body"
Main = DTSTaskExecResult_Success
End Function

Sub email_alert(strTo, strFrom, strSubject, strBody)
Dim iConf 'As CDO.Configuration
Dim imsg 'As CDO.Message
Dim flds

Set imsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

Set flds = iConf.Fields

'The http://schemas.microsoft.com/cdo/configuration/ namespace defines
the majority of fields used to set configurations for various CDO objects.
We set and update the following three fields (SendUsing, SMTP_SERVER, and
TimeOut) of the Configuration object:

With flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
"2"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp-server.mn.rr.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
.Update
End With

Set imsg.Configuration = iConf
imsg.To = strTo
imsg.From = strFrom
imsg.Subject = strSubject
imsg.TextBody = strBody
imsg.AddAttachment "c:\log\myfile.txt"
imsg.Send
End Sub
--
-Dick Christoph

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
> installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Follow-up to my original post below.

Is it possible for the "objEmail.To" to lookup the values from a sqlserver
table?

At the moment, I type the email address separated by a semi-colon.

TIA~

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Hi B,

Well not directly but you could create an ADODB Command, Connection and
Recordset and use the command to return you a recordset from the Database
that would have 1 or many email addresses that you could concatenate
together and stick in the objEmail.To field.

--
-Dick Christoph
"B" <no_spam@.no_spam.com> wrote in message
news:c4mdnfssT45ImT7ZnZ2dnUVZ_r-dnZ2d@.rcn.net...
> Follow-up to my original post below.
> Is it possible for the "objEmail.To" to lookup the values from a sqlserver
> table?
> At the moment, I type the email address separated by a semi-colon.
> TIA~
>
> "B" <no_spam@.no_spam.com> wrote in message
> news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
>> My code below works fine when run from my pc (changed all the values for
>> obvious reasons). The code is placed inside a DTS task via VBS
>> scripting.
>> But when I try to run directly from the server where sqlserver is
> installed,
>> the script fails.
>>
>> I have SMTP running, but there is no outlook installed.
>>
>> Can someone please advise what I am missing.
>> Thanks
>> Bob
>>
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "send@.test.com"
>> objEmail.To = "receive@.test.com"
>> objEmail.Subject = "TEST SUBJECT"
>> objEmail.AddAttachment "\\server\test.csv"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusing") = 2
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserver") = "SERVER_NAME"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpauthenticate") = 1
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusername") = "username"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendpassword") = "userpwd"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>> set objEmail = nothing
>>
>>

Help scripting backup devices

Hi guys

I am relatively new to SQL Server admin and I have been handed a task of creating backup devices for a particular SQL Server which has 204 databases.

Rather than go through and create Full, Incramental and Transaction Log backup devices for each database is ther anyway of doing the following

Create a script to go through and for each database name in 'sysdatabases'

create a folder e.g \sqldata\backup\%databasename%

and within each folder create a backup device called

%databasename%_full
%databasename%_inc
%databasename%_log

Also all these databases are running in 'Simple' recovery mode so obviously I need to change this to 'Full' to enable incramental and log backups - is this possible using the same script.

Hope someone can help as the thought of doing all of this individually for each database scares me silly!!! :)

Thanks in advance for any help

Hanleynot sure if this is an option but could you create a database maintenance plan inwhich the option 'All databases' is set and does the backup?|||I could do that but I need to create the backup devices first, that is my problem, I need to automate the creation of 3 backup devices for each database (240 of them)

:)|||This is 100% untested, but it should give you an idea or two:DECLARE @.cDb sysname

DECLARE zDb CURSOR FOR SELECT
sd.name
FROM master.dbo.sysdatabases AS sd

OPEN zDb
FETCH zDb INTO @.cDb

WHILE 0 = @.@.fetch_status
BEGIN
EXECUTE ('EXECUTE master.dbo.xp_cmdshell ''mkdir z:\sqldata\backup\'
+ @.cDb + '''')

EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_full'', ''z:\sqldata\backup\' + @.cDb + '_full.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_inc'', ''z:\sqldata\backup\' + @.cDb + '_inc.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_log'', ''z:\sqldata\backup\' + @.cDb + '_log.dmp''')

EXECUTE ('ALTER DATABASE ' + @.cDb + ' SET RECOVERY FULL')

FETCH zDb INTO @.cDb
END

CLOSE zDb
DEALLOCATE zDb-PatP|||Pat P

Thanks very much for that

I'll give it a try on Monday

Much appreciated

:)|||Pat P

Script was successful, exactly what I was looking for.

Many thanks

Hanley

:) :)