Matriz SQL

Hi, I have the following problem
In a DB exist this inf:
(I can use many tables... and/or many columns)
-->
1 2 3 4
5 6 7 8
9 a b c
d e f g
<--
In need write a select sentence that move in "circle" all info, for
example
5 1 2 3
9 a 6 4
d b 7 8
e f g cDoes this help:
http://spaces.msn.com/drsql/Blog/cns!80677FB08B3162E4!908.entry
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||What is your table structure and how are you getting this output to begin
with?
Post DDL and an explanation of how the original data is generated/selected.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, this sounds like a class assignment. If so, what sort of class is it?
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||the DB has not been created by now, the table structure can be anyone,
4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
Not class assignment.|||Can you explain the situation/application for this logic? It will certainly
help in determining a valid approach. Also, I am very curious as to how
this might be useful in a real world situation.
Anyway, if your matrix will always be 4x4, you can try this table setup. It
involves two tables, one storing the data with its original location in the
matrix (as columns and rows) and a second storing the matrix shift (original
cell and new cell). We use a case statement with an agregate function to
produce the matrix, and we join to the matrix shift table to determine the
new location. To shift the cells more than once we could join to the matrix
shift table as many times as we need to rotate the matrix values. We can
permanently update the values at each position in the matrix if needed.
Lastly, we could change the Value stored in the Data table to a FK pointing
to another table with as many columns as we need. This would still really
only work a column at a time, but you could use any column you wanted in the
matrix.
On SQL 2005, you could probably use the pivot/unpivot functions to
accomplish this.
/*
Create our table/view structure which has data in rows and columns (matrix
format)
*/
CREATE TABLE #tmpData (ColNum integer, RowNum Integer, Value varchar(10)
primary key(ColNum,RowNum))
go
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,1,'A');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,1,'B');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,1,'C');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,1,'D');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,2,'E');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,2,'F');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,2,'G');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,2,'H');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,3,'I');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,3,'J');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,3,'K');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,3,'L');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,4,'M');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,4,'N');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,4,'O');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,4,'P');
go
/*
Display data in the matrix format
*/
select
max(case when ColNum=1 then Value end) as Col1
,max(case when ColNum=2 then Value end) as Col2
,max(case when ColNum=3 then Value end) as Col3
,max(case when ColNum=4 then Value end) as Col4
from #tmpData
group by RowNum;
/*
Create our matrix mapping, showing how the matrix cells will move
*/
Create table #tmpMatrixShift
(ColNum1 integer,RowNum1 integer,ColNum2 integer,RowNum2 integer);
go
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,1,2,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,1,3,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,1,4,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,1,4,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,2,4,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,3,4,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,4,3,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,4,2,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,4,1,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,4,1,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,3,1,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,2,1,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,2,3,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,2,3,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,3,2,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,3,2,3);
go
/*
Join our data table to the matrix mapping table in order to get the new cell
locations
Display data in the matrix format
*/
select
max(case when b.ColNum2=1 then a.Value end) as Col1
,max(case when b.ColNum2=2 then a.Value end) as Col2
,max(case when b.ColNum2=3 then a.Value end) as Col3
,max(case when b.ColNum2=4 then a.Value end) as Col4
from #tmpData a
inner join #tmpMatrixShift b
on a.colnum = b.colnum1
and a.rownum = b.rownum1
group by b.RowNum2;
DROP TABLE #tmpData;
DROP TABLE #tmpMatrixShift;
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148328455.125472.120050@.j55g2000cwa.googlegroups.com...
> the DB has not been created by now, the table structure can be anyone,
> 4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
> Not class assignment.
>|||I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
=BF?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||Have you tried the approach I posted? As long as you are fixed at 4 columns
and 4 rows in the matrix, I believe it should do what you want. However,
some of the more math-intensive folks may be able to come up with an
algorithm that is more effective.
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||The link I posted shows a pretty interesting method as well... But it looks
like the OP just wants the answer handed to him without investing any of his
own thought.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you tried the approach I posted? As long as you are fixed at 4
> columns
> and 4 rows in the matrix, I believe it should do what you want. However,
> some of the more math-intensive folks may be able to come up with an
> algorithm that is more effective.
> "Alejandro" <jalejandro0211@.gmail.com> wrote in message
> news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
> I have a program in the office (very very old) don't have objects of
> this program, but this take a sentence select from .ini to execute,
> this program transform a values returned (contability) and calculate
> some, I can modify that select to --select transformed tables...
> ?...
> Well I do this
> CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
> COL2 VARCHAR(10) NOT NULL ,
> COL3 VARCHAR(10) NOT NULL ,
> COL4 VARCHAR(10) NOT NULL );
> insert into matrix values ('1','2','3','4');
> insert into matrix values ('5','6','7','8');
> insert into matrix values ('9','10','11','12');
> insert into matrix values ('13','14','15','16');
> now?
>|||Your link looked more like a cross tab solution, flipping the columns and
rows. I tried to apply it to this situation, but couldn't think of how to
do it, since the cells are being rotated rather than flipped. I am still
trying to think of a valid application for this sort of thing...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23$qSMGqfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> The link I posted shows a pretty interesting method as well... But it
looks
> like the OP just wants the answer handed to him without investing any of
his
> own thought.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
However,
>

Math function

Hi All

I'm trying to find a math function (if it exists) in SQL Server. If it
doesnt exist, then maybe someone can tell me what its called so I can
do a bit more reading on it

Basically I want to do this:

Parameter Components
1 1
2 2
3 1, 2
4 4
5 1, 4
6 2, 4
7 3, 4
8 8
9 1, 8
and so on

I'd like to be able to call a function and it would return true or
false like so

functionname(1, 9) = true
so 1 is a component of 9

functionname(2, 9) = false
so 2 is not a component of 9

functionname(4, 5) = true
so 4 is a component of 5

If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,
I'd appreciate it!

Thanks in advance

SamOn 27 Aug 2004 06:00:50 -0700, Samuel Hon wrote:

> Hi All
> I'm trying to find a math function (if it exists) in SQL Server. If it
> doesnt exist, then maybe someone can tell me what its called so I can
> do a bit more reading on it
> Basically I want to do this:
> Parameter Components
> 1 1
> 2 2
> 3 1, 2
> 4 4
> 5 1, 4
> 6 2, 4
> 7 3, 4
> 8 8
> 9 1, 8
> and so on
> I'd like to be able to call a function and it would return true or
> false like so
> functionname(1, 9) = true
> so 1 is a component of 9
> functionname(2, 9) = false
> so 2 is not a component of 9
> functionname(4, 5) = true
> so 4 is a component of 5
> If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,
> I'd appreciate it!
> Thanks in advance
> Sam

Your example seems a little fuzzy, but it looks like you are saying that
the "components" of an integer n are the largest power of 2 <= n, and the
remainder when that power of 2 is subtracted from n.

If this is the case, then your function in VB.NET could be:

Public Function IsComponent(C as Integer, P as Integer) as Boolean
Dim L as integer = LargestPowerOfTwo(P)
If (C > 0) and ( (C = L) or (C = P-L) ) Then
Return True
Else
Return False
End If
End Function

Public Function LargestPowerOfTwo(X as Integer) As Integer
Dim I as Integer = 1
If X < 1 Then
Return 0
End if
Do While I*2 <= X
I = I * 2
Loop
Return I
End Function

This could easily be ported to any other language, including T-SQL should
you need it there.|||Hi Ross

Thanks for the reply

I found that the 'technique' I'm looking for is bitwise comparison.
Change the numbers to bits, and then compare

Sam

master.mdf recovery

Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.Hi
9003: The LSN %S_LSN passed to log scan in database '%.*ls' is invalid.
You have corruption. SQL server might have been in the middle of writing to
the DB when the power outage occured. This can corrupt the DB.
Without a backup of Master, you are high and dry. Your only option is the
run the Rebuild Wizard (Rebuildm.exe), but you loose all your user logins.
Look up the topic in BOL.
Regards
Mike
"Radis Bill" wrote:
> Is it possible to recover master database from problematic master.mdf and/or
> problematic mastlog.ldf? No backups exist. The error that appeared in event
> log is
> Error:9003, Severity:20, State 1. and right after that: "Cannot recover
> master database.Exiting" . The reason I am posting this is that I know there
> is something starnge going on because there was no hard disk failure and the
> problem appeared after power loss of the server. But scan disk did not report
> any problem when the server rebooted after the power loss. So maybe the file
> is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.|||From the console, you might be able to start sqlservr with the -T3607
option. The recovers non of the databases, system nor users. This may
allow you to export the configuration contexts from the system tables from
within the master database. If you do this, then there is a chance that you
would have all the information necessary to replace the content on a rebuilt
master. However, THIS IS A COMPLICATED SITUATION. CALL MS PSS FOR
ASSISTANCE.
Sincerely,
Anthony Thomas
"Radis Bill" <Radis Bill@.discussions.microsoft.com> wrote in message
news:63EFB560-DFC7-4D36-B331-62846758B058@.microsoft.com...
Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not
report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in
advance.

master.mdf recovery

Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.
Hi
9003: The LSN %S_LSN passed to log scan in database '%.*ls' is invalid.
You have corruption. SQL server might have been in the middle of writing to
the DB when the power outage occured. This can corrupt the DB.
Without a backup of Master, you are high and dry. Your only option is the
run the Rebuild Wizard (Rebuildm.exe), but you loose all your user logins.
Look up the topic in BOL.
Regards
Mike
"Radis Bill" wrote:

> Is it possible to recover master database from problematic master.mdf and/or
> problematic mastlog.ldf? No backups exist. The error that appeared in event
> log is
> Error:9003, Severity:20, State 1. and right after that: "Cannot recover
> master database.Exiting" . The reason I am posting this is that I know there
> is something starnge going on because there was no hard disk failure and the
> problem appeared after power loss of the server. But scan disk did not report
> any problem when the server rebooted after the power loss. So maybe the file
> is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.
|||From the console, you might be able to start sqlservr with the -T3607
option. The recovers non of the databases, system nor users. This may
allow you to export the configuration contexts from the system tables from
within the master database. If you do this, then there is a chance that you
would have all the information necessary to replace the content on a rebuilt
master. However, THIS IS A COMPLICATED SITUATION. CALL MS PSS FOR
ASSISTANCE.
Sincerely,
Anthony Thomas

"Radis Bill" <Radis Bill@.discussions.microsoft.com> wrote in message
news:63EFB560-DFC7-4D36-B331-62846758B058@.microsoft.com...
Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not
report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in
advance.

master.mdf recovery

Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not repor
t
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in advance
.Hi
9003: The LSN %S_LSN passed to log scan in database '%.*ls' is invalid.
You have corruption. SQL server might have been in the middle of writing to
the DB when the power outage occured. This can corrupt the DB.
Without a backup of Master, you are high and dry. Your only option is the
run the Rebuild Wizard (Rebuildm.exe), but you loose all your user logins.
Look up the topic in BOL.
Regards
Mike
"Radis Bill" wrote:
[vbcol=seagreen]
> Is it possible to recover master database from problematic master.mdf and/
or
> problematic mastlog.ldf? No backups exist. The error that appeared in even
t
> log is
> Error:9003, Severity:20, State 1. and right after that: "Cannot recover
> master database.Exiting" . The reason I am posting this is that I know the
re
> is something starnge going on because there was no hard disk failure and t
he
> problem appeared after power loss of the server. But scan disk did not rep
ort
> any problem when the server rebooted after the power loss. So maybe the fi
le
> is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.[/vbc
ol]|||From the console, you might be able to start sqlservr with the -T3607
option. The recovers non of the databases, system nor users. This may
allow you to export the configuration contexts from the system tables from
within the master database. If you do this, then there is a chance that you
would have all the information necessary to replace the content on a rebuilt
master. However, THIS IS A COMPLICATED SITUATION. CALL MS PSS FOR
ASSISTANCE.
Sincerely,
Anthony Thomas
"Radis Bill" <Radis Bill@.discussions.microsoft.com> wrote in message
news:63EFB560-DFC7-4D36-B331-62846758B058@.microsoft.com...
Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not
report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in
advance.

'master..xp_smtp_SendMail'.

Hi,
What is the SQL Server 2005 version of:
'master..xp_smtp_SendMail'.
Apparently, this sp does not exist in 2005 - and causes our job to
fail.
Thank youxp_smtp_sendmail is from a third party provider (kind of) at
http://www.sqldev.net/xp/xpsmtp.htm
So no...you won't find that extended stored procedure built
into SQL Server 2005. The closest thing in 2005 is using DB
Mail and using sp_send_dbmail.
-Sue
On 6 Sep 2006 14:19:17 -0700, tootsuite@.gmail.com wrote:
>Hi,
>What is the SQL Server 2005 version of:
>'master..xp_smtp_SendMail'.
>Apparently, this sp does not exist in 2005 - and causes our job to
>fail.
>Thank you|||<DIV><tootsuite@.gmail.com> wrote in message
news:1157577557.082293.219110@.m73g2000cwd.googlegroups.com...</DIV>> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
Database Mail
http://msdn2.microsoft.com/en-us/library/ms175887.aspx
sp_send_dbmail
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
David|||If you want to use master..xp_smtp_SendMail in SQL 2005, you'll have to
recreate it from scratch as this is a custom built sp by someone on
SQLDev.Net. You can use the 2000 .dlls (I did it myself) but you'll have to
save them in C:\Program Files\Microsoft SQL Server\90\DTS\Binn for it to work.
Big note here. If you switch from this to DBMail, read the instructions for
DBMail VERY CAREFULLY. You'll have to make changes to the variable names.
I.E., instead of @.TO, DBMail uses @.Recipients. If you don't change
everything over, your job will still fail.
Hope this helps.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"tootsuite@.gmail.com" wrote:
> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>|||tootsuite@.gmail.com wrote:
> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
That is a third-party utility that allows SQL 2000 to send mail through
an SMTP server. It's no longer needed in SQL 2005, as the built-in
DBMail capability can do the same thing. Be sure to read the
documentation on DBMail.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

'master..xp_smtp_SendMail'.

Hi,
What is the SQL Server 2005 version of:
'master..xp_smtp_SendMail'.
Apparently, this sp does not exist in 2005 - and causes our job to
fail.
Thank youxp_smtp_sendmail is from a third party provider (kind of) at
http://www.sqldev.net/xp/xpsmtp.htm
So no...you won't find that extended stored procedure built
into SQL Server 2005. The closest thing in 2005 is using DB
Mail and using sp_send_dbmail.
-Sue
On 6 Sep 2006 14:19:17 -0700, tootsuite@.gmail.com wrote:

>Hi,
>What is the SQL Server 2005 version of:
>'master..xp_smtp_SendMail'.
>Apparently, this sp does not exist in 2005 - and causes our job to
>fail.
>Thank you|||<DIV><tootsuite@.gmail.com> wrote in message
news:1157577557.082293.219110@.m73g2000cwd.googlegroups.com...</DIV>> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
Database Mail
http://msdn2.microsoft.com/en-us/library/ms175887.aspx
sp_send_dbmail
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
David|||If you want to use master..xp_smtp_SendMail in SQL 2005, you'll have to
recreate it from scratch as this is a custom built sp by someone on
SQLDev.Net. You can use the 2000 .dlls (I did it myself) but you'll have to
save them in C:\Program Files\Microsoft SQL Server\90\DTS\Binn for it to wor
k.
Big note here. If you switch from this to DBMail, read the instructions for
DBMail VERY CAREFULLY. You'll have to make changes to the variable names.
I.E., instead of @.TO, DBMail uses @.Recipients. If you don't change
everything over, your job will still fail.
Hope this helps.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"tootsuite@.gmail.com" wrote:

> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>|||tootsuite@.gmail.com wrote:
> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
That is a third-party utility that allows SQL 2000 to send mail through
an SMTP server. It's no longer needed in SQL 2005, as the built-in
DBMail capability can do the same thing. Be sure to read the
documentation on DBMail.
Tracy McKibben
MCDBA
http://www.realsqlguy.com