Help optimising a stored proc

Hi I have the following procedure that accepts two CSV lists of values,
the first list contains primary key numbers, and the second values to
update.
When the list gets over about 200 items, I am getting intermittent
Timout errors.
Currently just over 500,000 records in the table.
Is there a way to optimise the performace of the update? Or is theer an
easier way if the input can be provided in a single list
e.g 2245=1,2257=2,3367=2 instead of
2245,2257,3367 and 1,2,2
Thanks!
CREATE Procedure dbo.UpdateResults
@.RegistrationIDs Varchar(8000),
@.Results Varchar(4000)
AS
UPDATE tblRegistrations
SET Result
= (SELECT A.Value FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value )
WHERE EXISTS (SELECT *
FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value)
CREATE FUNCTION dbo.Split
(
@.List varchar(8000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(
Id int identity(1,1),
Value nvarchar(150)
)
AS
BEGIN
While (Charindex(@.SplitOn,@.List)>0)
Begin
Insert Into @.RtnValue (value)
Select
Value =
ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
Set @.List =
Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
End
Insert Into @.RtnValue (Value)
Select Value = ltrim(rtrim(@.List))
Return
ENDI would try using temporary tables instead of table variables. I have seen
some strange stuff happen when trying to join two table variables together,
or joining a table-valued function together with a real base table.
Move the logic of the dbo.split funtion inside the stored procedure, take
the input csv strings and write them into a temporary table, then process
from that temp table.
"hals_left" wrote:

> Hi I have the following procedure that accepts two CSV lists of values,
> the first list contains primary key numbers, and the second values to
> update.
> When the list gets over about 200 items, I am getting intermittent
> Timout errors.
> Currently just over 500,000 records in the table.
> Is there a way to optimise the performace of the update? Or is theer an
> easier way if the input can be provided in a single list
> e.g 2245=1,2257=2,3367=2 instead of
> 2245,2257,3367 and 1,2,2
> Thanks!
>
> CREATE Procedure dbo.UpdateResults
> @.RegistrationIDs Varchar(8000),
> @.Results Varchar(4000)
> AS
> UPDATE tblRegistrations
> SET Result
> = (SELECT A.Value FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value )
> WHERE EXISTS (SELECT *
> FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value)
>
> CREATE FUNCTION dbo.Split
> (
> @.List varchar(8000),
> @.SplitOn nvarchar(5)
> )
> RETURNS @.RtnValue table
> (
> Id int identity(1,1),
> Value nvarchar(150)
> )
> AS
> BEGIN
> While (Charindex(@.SplitOn,@.List)>0)
> Begin
> Insert Into @.RtnValue (value)
> Select
> Value =
> ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
> Set @.List =
> Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
> End
> Insert Into @.RtnValue (Value)
> Select Value = ltrim(rtrim(@.List))
> Return
> END
>|||Hi There,
What Mark suggested is right but you may like to try this .
UPDATE T1
SET Result = A.Value
>From FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
Join tblRegistrations T1 On T1.RegistrationID=B.Value
and remove the split function If possible .
With Warm regards
Jatinder Singh
Mark Williams wrote:
> I would try using temporary tables instead of table variables. I have seen
> some strange stuff happen when trying to join two table variables together
,
> or joining a table-valued function together with a real base table.
> Move the logic of the dbo.split funtion inside the stored procedure, take
> the input csv strings and write them into a temporary table, then process
> from that temp table.
> --
> "hals_left" wrote:
>|||Thanks Jatinder, that works nice on a small test and is much simpler
SQL.
I'l test in on the real database and see how it compares to the
original
I dont really see how can I remove the split function, unless I pass
the data 1 record at a time in a loop ...

Help on SQL query

I have a following table...

id col2
1 A
1 B
2 A
3 A
3 B
4 B
5 A
5 B
5 C

How can I retrieve records where ONLY col2 = "A"?? In this scenario, I will only want to retrieve the record id = 2, because id 2 only has col2 = A. I don't want other records because B and/or C exists.

Thanks!Try this:

select * from tabA ta
group by id
having count(col2) = 1

Thanks.
Pat|||pat, that won't work for two reasons: first, the non-aggregate columns in the SELECT are not all included in the GROUP BY as they should be, and second, you aren't specifically selecting col2 values of 'A'
select id
from thetable A
where col2 = 'A'
and not exists
( select 1
from thetable
where id = A.id
and col2 <> 'A' )|||Oh yes...

SQL Server has the restriction where we need to specify all columns in the select list in a group by clause. I was probably writing the query in a 'Sybase' style :-)...|||Im kinda confused with the query your wanting to achive, from the looks of it it would just be

select * from tablea
where col2 = 'A'

Not sure exactly if you were looking for something else, be a little more clear on it and I can help ya out :).|||Hi

Is it possible that

id col2
1 A
1 B
2 A
2 A * <-- ;-)
3 A
3 B
4 B
5 A
5 B
5 C
5 A * <-- ;-) and this also

will exist? just tell coz i am trying ot find a similar soln|||I think that R937 gave SAC11585 a clear answer|||Thanks for all the help. R937's solution is exactly what I wanted.|||Originally posted by pathakpr
Oh yes...

SQL Server has the restriction where we need to specify all columns in the select list in a group by clause. I was probably writing the query in a 'Sybase' style :-)...

This is not SQL Server restriction, but rather level 2 ANSI-compliant style. I doubt Sybase would allow you to do anything different...IT WILL NOT! Just tested it! Try it for yourself, but don't blame SQL Server ;)|||what pat may have been thinking is "mysql style"

mysql does not enforce that restriction

mysql lets you write crap like --

select salesman, salesdate, sum(salesamount)
from salestable
group by salesman

i cannot tell you how many times i've seen new mysql people fall into this trap and then wonder why their results are weird

i blame mysql for the "feature"

see 12.7.3 GROUP BY with Hidden Fields (http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html)

Help on SP: Question Reposted

Hi All,
Here is my complete codes of the two sps. I am still without any luck.
Could anyone see any errors in the following code?
As always I am thankful for your help.
best regards,
mamun
SP1:
CREATE Procedure sp_FTPNotify_New (@.logId int)
As
declare @.recips varchar(255)
declare @.msg varchar(250)
declare @.sub varchar(75)
DECLARE @.cmd varchar(56)
Declare @.txtPtr varbinary(16)
declare @.new int, @.old int
declare @.txt varchar(255)
declare @.email varchar(50)
declare @.newfilename varchar(255)
declare @.oldfilename varchar(255)
declare @.renamestring varchar(255)
declare @.fieldposition int
declare @.NewTarget varchar(255)
declare @.newprefix varchar(20)
declare @.FailedFlag varchar(255)
declare @.LogTime datetime
Select @.NewTarget=3DTarget , @.newprefix =3D
rtrim(rtrim(convert(char,logtime,12))+co
nvert(char,LogID)),
@.FailedFlag =3D Operation,@.LogTime =3D LogTime
from FTPLogs l , FTPNotify n
where LogID =3D @.logId and
lower(l.username) =3D lower(n.username)
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) =3D ']'
break
END
if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
=3D'created'
begin
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.NewTarget,@.fieldposition,1) =3D '.'
break
END
Select
@.NewTarget=3DrTrim(substring(@.NewTarget,
1,@.fieldposition-1))+rtrim(@.newpref=
ix=AD+rTrim(substring(@.NewTarget,@.fieldp
osition,255)))
Select @.msg =3D 'This is an automatically generated FTP notification
message:'
delete from texttab
INSERT into texttab
select 'This message was generated on ' +
convert(varchar(25),getdate()) + char(13) + replicate ('_', 45) +
char(13)
select
@.recips =3D ' + rtrim(notify) + ',
@.txt =3D
'The file from ' + rtrim(description) + ' has arrived. This
file is
located on the server W2K3-S1 ' +
'under the DATA' + rtrim(l.username) + ' directory.' +
char(13) +
char(13) +
'The file name: ' + rtrim(@.NewTarget) + char(13) +
'Date Received: ' + convert(varchar(25),LogTime) + char(13) +
'File Size: ' + convert(char(20), BytesRecvd) ,
@.sub =3D 'FTPLog Notification from " + rtrim(description) + ',
@.newfilename =3D rtrim(@.NewTarget),
@.oldfilename =3D rtrim(l.username)+''+ rtrim(Target)
from FTPLogs l , FTPNotify n
where LogID =3D @.logId and
lower(l.username) =3D lower(n.username)
select @.txtptr =3D textptr(c1) from texttab
UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
SELECT @.cmd =3D 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec master.dbo.xp_sendmail
@.recipients =3D ' + @.recips + ',
@.message =3D ' + @.msg + ',
@.query =3D '+ @.cmd + ',
@.subject =3D ' + @.sub + ',
@.no_header =3D 'TRUE', @.width =3D 2500
delete from texttab
select @.renamestring =3D 'rename \\W2k3-S1\data' + rtrim(@.oldfilename) +
' '+ rtrim(@.newfilename)
print @.renamestring
declare @.result int
declare @.querystring char(200)
EXEC @.result =3D master.dbo.xp_cmdshell @.renamestring
if (@.result =3D 1)
begin
select @.querystring =3D'SELECT logid,substring(username,1,20)
username,logtime,bytesrecvd,substring(ta
rget,1,50) filename FROM
ftplogs.dbo.ftplogs where logid =3D '+ convert(char,@.logId)
exec master.dbo.xp_sendmail @.recipients =3D 'ma...@.inc.com',
@.query =3D '" + @.querystring + " ' ,
@.subject =3D'Failed Rename',
@.message =3D'The following file could not be renamed.',
@.attach_results =3D 'FALSE', @.width =3D 250
end
end
Else
if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition)) =3D
'closed'
begin
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.NewTarget,@.fieldposition,1) =3D '.'
break
END
Select
@.NewTarget=3DrTrim(substring(@.NewTarget,
1,@.fieldposition-1))+rtrim(@.newpref=
ix=AD+rTrim(substring(@.NewTarget,@.fieldp
osition,255)))
Select @.msg =3D 'This is an automatically generated FTP notification
message:'
delete from texttab
INSERT into texttab
select 'This message was generated on ' +
convert(varchar(25),getdate()) + char(13) +
replicate ('_', 45) + char(13)
select
@.recips =3D ''' + rtrim(notify) + ''',
@.txt =3D char(13)+
'THE ATEMPTED FTP FILE TRANSFER TO SERVICES '+ char(13)
+'ON ' +UPPER(convert(varchar(25),LOGTIME)) + ' FROM ' +
upper(rtrim(description)) + char(13)
+'WAS NOT SUCCESSFULLY RECEIVED.'+ char(13)+ char(13)
+'IF NECESSARY PLEASE CONTACT THE APPROPRIATE PARTY' + char(13)
+'TO HAVE THE FILE RESENT.' ,
--@.sub =3D 'FTPLog Notification ALERT from ' +
rtrim(description) + '''
@.sub =3D 'FTPLog Notification ALERT from ' + rtrim(description)
+ ''
from FTPLogs l , FTPNotify n
where LogID =3D @.logId and
lower(l.username) =3D lower(n.username)
select @.txtptr =3D textptr(c1) from texttab
UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
SELECT @.cmd =3D 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec master.dbo.xp_sendmail
@.recipients =3D " + @.recips + ",
@.message =3D " + @.msg + ",
@.query =3D "+ @.cmd + ",
@.subject =3D " + @.sub + ",
@.no_header =3D 'TRUE', @.width =3D 2500
delete from texttab
end
GO
SP2:
CREATE Procedure sp_MailNotify_New
As
Declare @.id int
Declare @.CStatus int
Declare @.fieldposition int
declare @.FailedFlag varchar(255)
Declare C_getLog cursor for
select LogId,Operation from FTPLogs where notified =3D 0 order by LogID
Open C_getLog
Fetch Next from C_getLog into @.id,@.FailedFlag
select @.CStatus =3D @.@.FETCH_STATUS
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) =3D ']'
break
END
select @.FailedFlag =3D
lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
while (@.CStatus <> -1 and (@.FailedFlag =3D 'closed' or @.FailedFlag =3D
'created'))
begin
select 'THE ID is ' + convert(char(8),@.id)
execute sp_FTPNotify_new @.id
update FTPLogs set notified =3D 1 where LogId =3D @.id
Fetch Next from C_getLog into @.id,@.FailedFlag
select @.CStatus =3D @.@.FETCH_STATUS
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) =3D ']'
break
END
select @.FailedFlag =3D
lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
end
update FTPLogs set notified =3D 1 where LogId <=3D @.id
Close C_getLog
Deallocate C_getLog
GO=20
In the scheduled jobs: exec exec sp_MailNotify_NewHi
A quick view of your code in QA gives me an impression that the string is
not properly quoted here.. you may check meanwhile:
rtrim(description) + '''
@.sub = 'FTPLog Notification ALERT from ' + rtrim(description)
+ ''
should probably be:
rtrim(description) + ''
@.sub = 'FTPLog Notification ALERT from ' + rtrim(description)
+ ''
and also post error msg by executing it manually so as to better help you
Regards
R.D

> Hi All,
> Here is my complete codes of the two sps. I am still without any luck.
> Could anyone see any errors in the following code?
> As always I am thankful for your help.
> best regards,
> mamun
>
> SP1:
>
> CREATE Procedure sp_FTPNotify_New (@.logId int)
> As
> declare @.recips varchar(255)
> declare @.msg varchar(250)
> declare @.sub varchar(75)
> DECLARE @.cmd varchar(56)
> Declare @.txtPtr varbinary(16)
> declare @.new int, @.old int
> declare @.txt varchar(255)
> declare @.email varchar(50)
> declare @.newfilename varchar(255)
> declare @.oldfilename varchar(255)
> declare @.renamestring varchar(255)
> declare @.fieldposition int
> declare @.NewTarget varchar(255)
> declare @.newprefix varchar(20)
> declare @.FailedFlag varchar(255)
> declare @.LogTime datetime
>
> Select @.NewTarget=Target , @.newprefix =
> rtrim(rtrim(convert(char,logtime,12))+co
nvert(char,LogID)),
> @.FailedFlag = Operation,@.LogTime = LogTime
> from FTPLogs l , FTPNotify n
> where LogID = @.logId and
> lower(l.username) = lower(n.username)
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.FailedFlag,@.fieldposition,1) = ']'
> break
>
> END
> if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
> ='created'
> begin
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.NewTarget,@.fieldposition,1) = '.'
> break
>
> END
> Select
> @.NewTarget=rTrim(substring(@.NewTarget,1,
@.fieldposition-1))+rtrim(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,255)))
> Select @.msg = 'This is an automatically generated FTP notification
> message:'
> delete from texttab
> INSERT into texttab
> select 'This message was generated on ' +
> convert(varchar(25),getdate()) + char(13) + replicate ('_', 45) +
> char(13)
> select
> @.recips = ' + rtrim(notify) + ',
> @.txt =
> 'The file from ' + rtrim(description) + ' has arrived. This
> file is
> located on the server W2K3-S1 ' +
> 'under the DATA' + rtrim(l.username) + ' directory.' +
> char(13) +
> char(13) +
> 'The file name: ' + rtrim(@.NewTarget) + char(13) +
> 'Date Received: ' + convert(varchar(25),LogTime) + char(13) +
> 'File Size: ' + convert(char(20), BytesRecvd) ,
> @.sub = 'FTPLog Notification from " + rtrim(description) + ',
> @.newfilename = rtrim(@.NewTarget),
> @.oldfilename = rtrim(l.username)+''+ rtrim(Target)
> from FTPLogs l , FTPNotify n
> where LogID = @.logId and
> lower(l.username) = lower(n.username)
> select @.txtptr = textptr(c1) from texttab
> UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
> SELECT @.cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
> exec master.dbo.xp_sendmail
> @.recipients = ' + @.recips + ',
> @.message = ' + @.msg + ',
> @.query = '+ @.cmd + ',
> @.subject = ' + @.sub + ',
> @.no_header = 'TRUE', @.width = 2500
> delete from texttab
> select @.renamestring = 'rename \\W2k3-S1\data' + rtrim(@.oldfilename) +
> ' '+ rtrim(@.newfilename)
> print @.renamestring
> declare @.result int
> declare @.querystring char(200)
> EXEC @.result = master.dbo.xp_cmdshell @.renamestring
> if (@.result = 1)
> begin
> select @.querystring ='SELECT logid,substring(username,1,20)
> username,logtime,bytesrecvd,substring(ta
rget,1,50) filename FROM
> ftplogs.dbo.ftplogs where logid = '+ convert(char,@.logId)
> exec master.dbo.xp_sendmail @.recipients = 'ma...@.inc.com',
> @.query = '" + @.querystring + " ' ,
> @.subject ='Failed Rename',
> @.message ='The following file could not be renamed.',
> @.attach_results = 'FALSE', @.width = 250
> end
> end
> Else
>
> if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition)) =
> 'closed'
> begin
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.NewTarget,@.fieldposition,1) = '.'
> break
>
> END
> Select
> @.NewTarget=rTrim(substring(@.NewTarget,1,
@.fieldposition-1))+rtrim(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,255)))
> Select @.msg = 'This is an automatically generated FTP notification
> message:'
> delete from texttab
> INSERT into texttab
> select 'This message was generated on ' +
> convert(varchar(25),getdate()) + char(13) +
> replicate ('_', 45) + char(13)
> select
> @.recips = ''' + rtrim(notify) + ''',
> @.txt = char(13)+
> 'THE ATEMPTED FTP FILE TRANSFER TO SERVICES '+ char(13)
> +'ON ' +UPPER(convert(varchar(25),LOGTIME)) + ' FROM ' +
> upper(rtrim(description)) + char(13)
> +'WAS NOT SUCCESSFULLY RECEIVED.'+ char(13)+ char(13)
> +'IF NECESSARY PLEASE CONTACT THE APPROPRIATE PARTY' + char(13)
> +'TO HAVE THE FILE RESENT.' ,
> --@.sub = 'FTPLog Notification ALERT from ' +
> rtrim(description) + '''
> @.sub = 'FTPLog Notification ALERT from ' + rtrim(description)
> + ''
>
> from FTPLogs l , FTPNotify n
> where LogID = @.logId and
> lower(l.username) = lower(n.username)
> select @.txtptr = textptr(c1) from texttab
> UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
> SELECT @.cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
> exec master.dbo.xp_sendmail
> @.recipients = " + @.recips + ",
> @.message = " + @.msg + ",
> @.query = "+ @.cmd + ",
> @.subject = " + @.sub + ",
> @.no_header = 'TRUE', @.width = 2500
> delete from texttab
> end
>
> GO
>
> SP2:
>
> CREATE Procedure sp_MailNotify_New
> As
> Declare @.id int
> Declare @.CStatus int
> Declare @.fieldposition int
> declare @.FailedFlag varchar(255)
> Declare C_getLog cursor for
> select LogId,Operation from FTPLogs where notified = 0 order by LogID
> Open C_getLog
> Fetch Next from C_getLog into @.id,@.FailedFlag
> select @.CStatus = @.@.FETCH_STATUS
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.FailedFlag,@.fieldposition,1) = ']'
> break
>
> END
> select @.FailedFlag =
> lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
> while (@.CStatus <> -1 and (@.FailedFlag = 'closed' or @.FailedFlag =
> 'created'))
> begin
> select 'THE ID is ' + convert(char(8),@.id)
> execute sp_FTPNotify_new @.id
> update FTPLogs set notified = 1 where LogId = @.id
> Fetch Next from C_getLog into @.id,@.FailedFlag
> select @.CStatus = @.@.FETCH_STATUS
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.FailedFlag,@.fieldposition,1) = ']'
> break
>
> END
>
> select @.FailedFlag =
> lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
> end
> update FTPLogs set notified = 1 where LogId <= @.id
> Close C_getLog
> Deallocate C_getLog
>
> GO
>
> In the scheduled jobs: exec exec sp_MailNotify_New
>|||Hi mamun
I would have expected:
@.sub = 'FTPLog Notification from " + rtrim(description) + ',
to be
@.sub = 'FTPLog Notification from "' + rtrim(description) + '"',
or
@.sub = 'FTPLog Notification from ' + rtrim(description),
:)
Other things that may be useful are
1. Using Charindex instead of looping through the strings to find a characte
r
2. Writing the code to copy with spaces in the file name
3. Add error handling
4. Make sure that your rename has worked before you send the email.
John
"microsoft.public.dotnet.languages.vb" wrote:

> Hi All,
> Here is my complete codes of the two sps. I am still without any luck.
> Could anyone see any errors in the following code?
> As always I am thankful for your help.
> best regards,
> mamun
>
> SP1:
>
> CREATE Procedure sp_FTPNotify_New (@.logId int)
> As
> declare @.recips varchar(255)
> declare @.msg varchar(250)
> declare @.sub varchar(75)
> DECLARE @.cmd varchar(56)
> Declare @.txtPtr varbinary(16)
> declare @.new int, @.old int
> declare @.txt varchar(255)
> declare @.email varchar(50)
> declare @.newfilename varchar(255)
> declare @.oldfilename varchar(255)
> declare @.renamestring varchar(255)
> declare @.fieldposition int
> declare @.NewTarget varchar(255)
> declare @.newprefix varchar(20)
> declare @.FailedFlag varchar(255)
> declare @.LogTime datetime
>
> Select @.NewTarget=Target , @.newprefix =
> rtrim(rtrim(convert(char,logtime,12))+co
nvert(char,LogID)),
> @.FailedFlag = Operation,@.LogTime = LogTime
> from FTPLogs l , FTPNotify n
> where LogID = @.logId and
> lower(l.username) = lower(n.username)
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.FailedFlag,@.fieldposition,1) = ']'
> break
>
> END
> if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
> ='created'
> begin
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.NewTarget,@.fieldposition,1) = '.'
> break
>
> END
> Select
> @.NewTarget=rTrim(substring(@.NewTarget,1,
@.fieldposition-1))+rtrim(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,255)))
> Select @.msg = 'This is an automatically generated FTP notification
> message:'
> delete from texttab
> INSERT into texttab
> select 'This message was generated on ' +
> convert(varchar(25),getdate()) + char(13) + replicate ('_', 45) +
> char(13)
> select
> @.recips = ' + rtrim(notify) + ',
> @.txt =
> 'The file from ' + rtrim(description) + ' has arrived. This
> file is
> located on the server W2K3-S1 ' +
> 'under the DATA' + rtrim(l.username) + ' directory.' +
> char(13) +
> char(13) +
> 'The file name: ' + rtrim(@.NewTarget) + char(13) +
> 'Date Received: ' + convert(varchar(25),LogTime) + char(13) +
> 'File Size: ' + convert(char(20), BytesRecvd) ,
> @.sub = 'FTPLog Notification from " + rtrim(description) + ',
> @.newfilename = rtrim(@.NewTarget),
> @.oldfilename = rtrim(l.username)+''+ rtrim(Target)
> from FTPLogs l , FTPNotify n
> where LogID = @.logId and
> lower(l.username) = lower(n.username)
> select @.txtptr = textptr(c1) from texttab
> UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
> SELECT @.cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
> exec master.dbo.xp_sendmail
> @.recipients = ' + @.recips + ',
> @.message = ' + @.msg + ',
> @.query = '+ @.cmd + ',
> @.subject = ' + @.sub + ',
> @.no_header = 'TRUE', @.width = 2500
> delete from texttab
> select @.renamestring = 'rename \\W2k3-S1\data' + rtrim(@.oldfilename) +
> ' '+ rtrim(@.newfilename)
> print @.renamestring
> declare @.result int
> declare @.querystring char(200)
> EXEC @.result = master.dbo.xp_cmdshell @.renamestring
> if (@.result = 1)
> begin
> select @.querystring ='SELECT logid,substring(username,1,20)
> username,logtime,bytesrecvd,substring(ta
rget,1,50) filename FROM
> ftplogs.dbo.ftplogs where logid = '+ convert(char,@.logId)
> exec master.dbo.xp_sendmail @.recipients = 'ma...@.inc.com',
> @.query = '" + @.querystring + " ' ,
> @.subject ='Failed Rename',
> @.message ='The following file could not be renamed.',
> @.attach_results = 'FALSE', @.width = 250
> end
> end
> Else
>
> if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition)) =
> 'closed'
> begin
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.NewTarget,@.fieldposition,1) = '.'
> break
>
> END
> Select
> @.NewTarget=rTrim(substring(@.NewTarget,1,
@.fieldposition-1))+rtrim(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,255)))
> Select @.msg = 'This is an automatically generated FTP notification
> message:'
> delete from texttab
> INSERT into texttab
> select 'This message was generated on ' +
> convert(varchar(25),getdate()) + char(13) +
> replicate ('_', 45) + char(13)
> select
> @.recips = ''' + rtrim(notify) + ''',
> @.txt = char(13)+
> 'THE ATEMPTED FTP FILE TRANSFER TO SERVICES '+ char(13)
> +'ON ' +UPPER(convert(varchar(25),LOGTIME)) + ' FROM ' +
> upper(rtrim(description)) + char(13)
> +'WAS NOT SUCCESSFULLY RECEIVED.'+ char(13)+ char(13)
> +'IF NECESSARY PLEASE CONTACT THE APPROPRIATE PARTY' + char(13)
> +'TO HAVE THE FILE RESENT.' ,
> --@.sub = 'FTPLog Notification ALERT from ' +
> rtrim(description) + '''
> @.sub = 'FTPLog Notification ALERT from ' + rtrim(description)
> + ''
>
> from FTPLogs l , FTPNotify n
> where LogID = @.logId and
> lower(l.username) = lower(n.username)
> select @.txtptr = textptr(c1) from texttab
> UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
> SELECT @.cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
> exec master.dbo.xp_sendmail
> @.recipients = " + @.recips + ",
> @.message = " + @.msg + ",
> @.query = "+ @.cmd + ",
> @.subject = " + @.sub + ",
> @.no_header = 'TRUE', @.width = 2500
> delete from texttab
> end
>
> GO
>
> SP2:
>
> CREATE Procedure sp_MailNotify_New
> As
> Declare @.id int
> Declare @.CStatus int
> Declare @.fieldposition int
> declare @.FailedFlag varchar(255)
> Declare C_getLog cursor for
> select LogId,Operation from FTPLogs where notified = 0 order by LogID
> Open C_getLog
> Fetch Next from C_getLog into @.id,@.FailedFlag
> select @.CStatus = @.@.FETCH_STATUS
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.FailedFlag,@.fieldposition,1) = ']'
> break
>
> END
> select @.FailedFlag =
> lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
> while (@.CStatus <> -1 and (@.FailedFlag = 'closed' or @.FailedFlag =
> 'created'))
> begin
> select 'THE ID is ' + convert(char(8),@.id)
> execute sp_FTPNotify_new @.id
> update FTPLogs set notified = 1 where LogId = @.id
> Fetch Next from C_getLog into @.id,@.FailedFlag
> select @.CStatus = @.@.FETCH_STATUS
> select @.fieldposition=0
> WHILE @.fieldposition < 225
> BEGIN
> select @.fieldposition = @.fieldposition+1
>
> if substring(@.FailedFlag,@.fieldposition,1) = ']'
> break
>
> END
>
> select @.FailedFlag =
> lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
> end
> update FTPLogs set notified = 1 where LogId <= @.id
> Close C_getLog
> Deallocate C_getLog
>
> GO
>
> In the scheduled jobs: exec exec sp_MailNotify_New
>

HELP on SELECT Statement

I have following table record
Year ModelYr Type
1999 1994 2
1999 1999 3
2000 1999 4
2001 2000 2
1999 1996 4
2000 1996 5
2000 1998 3
2001 2001 2
Here are the rules:
1. In display output, it will have the following column name:
Year TypeA TypeB
2. "ModelYr" is equal or less than "Year"
3. if ("Year" - "ModelYr") < 2 then
TypeA = "Type"
else
TypeB = "Type"
3. The output should be "Group by Year Order by Year"
4. For each "Year", the each type will be SUM UP.
So the expected output will be:
Year TypeA TypeB
1999 6 3
2000 7 5
2001 4 0
Could you kindly advise how to make the SELECT statement to achieve the
output above ? I'm really not too sure.
Any help is very much appreciated.
Thank you.
Regards.Something like this? Note that my output came out a little different from
your specified output:
Year | TypeA | TypeB
1999 | 6 | 3
2000 | 5 | 7
2001 | 0 | 4
CREATE TABLE #Widgets ([Year] INT NOT NULL,
ModelYr INT NOT NULL,
Type INT NOT NULL,
PRIMARY KEY ([Year], ModelYr, Type));
INSERT INTO #Widgets([Year], ModelYr, Type)
SELECT 1999, 1994, 2
UNION SELECT 1999, 1999, 3
UNION SELECT 2000, 1999, 4
UNION SELECT 2001, 2000, 2
UNION SELECT 1999, 1996, 4
UNION SELECT 2000, 1996, 5
UNION SELECT 2000, 1998, 3
UNION SELECT 2001, 2001, 2
SELECT [Year], SUM(CASE WHEN [Year] - [ModelYr] > 2 THEN Type ELSE 0 END) AS
TypeA,
SUM(CASE WHEN [Year] - [ModelYr] <= 2 THEN Type ELSE 0 END) AS TypeB
FROM #Widgets
WHERE ModelYr <= [Year]
GROUP BY [Year]
ORDER BY [Year]
DROP TABLE #Widgets
"magix" <magix@.asia.com> wrote in message news:449bef96_2@.news.tm.net.my...
>I have following table record
> Year ModelYr Type
> 1999 1994 2
> 1999 1999 3
> 2000 1999 4
> 2001 2000 2
> 1999 1996 4
> 2000 1996 5
> 2000 1998 3
> 2001 2001 2
> Here are the rules:
> 1. In display output, it will have the following column name:
> Year TypeA TypeB
> 2. "ModelYr" is equal or less than "Year"
> 3. if ("Year" - "ModelYr") < 2 then
> TypeA = "Type"
> else
> TypeB = "Type"
> 3. The output should be "Group by Year Order by Year"
> 4. For each "Year", the each type will be SUM UP.
> So the expected output will be:
> Year TypeA TypeB
> 1999 6 3
> 2000 7 5
> 2001 4 0
>
> Could you kindly advise how to make the SELECT statement to achieve the
> output above ? I'm really not too sure.
> Any help is very much appreciated.
> Thank you.
> Regards.
>

help on SELECT

Help on creating correct select query on the following table where customer = multi-race

( that is, customers that have value ‘1’ on more than one race category)

Thanks!

CustomerID

Black

AmIndian

Asian

White

PacIslander

Hispanic

NoRaceDisc

32501

1

1

32677

1

35062

1

1

35261

1

36490

1

41026

1

41412

1

42488

1

1

1

45471

1

47083

1

1

50066

1

Okay, first off, you should probably change your table structure if you get the chance. The way you designed things, you actually have to go through a schema change if you ever want to add a new race. I suggest you go to a table structure that has a table for race types, and another table which contains your customer_id and the race_id. In this case, your query would look something like this:

select customer_id, count(*)
from customer_races
group by customer_id
having count(*) > 1

In your current table structure, it gets a lot more complicated. If you cannot change the table structure, I'd suggest you follow a method similar to the one I outlined above. Create a temp table / table variable with the following structure...

declare table @.customer_race_count (
customer_id int, race_count int)

Then, you'll have to construct a series of statements like this...

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where black is not null

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where white is not null

then do something similar to this...

select customer_id, count(*)
from race_count
group by customer_id
having count(*) > 1

I hope that helps!

|||oh yeah, if you're using sql 2005, you might be able to take advantage of pivot / unpivot, but I've been working all day and no longer have the brain power to conjure some sample code for that.|||

well, if the race category is a numerical value (ie int) of some sort, this would work:

select CustomerID from MyTable
where (coalesce(Black,0) + coalesce(AmIndian,0) + coalesce(Asian,0) + coalesce(White,0) + (PacIslander,0) + (Hispanic,0)) > 1

If this is 2005, you may want to look at PIVOT

|||

First off, I agree completely with the person who said change the table structure. This should be a very easy query, but it isn't like you have it.

Second, if not numbers, or values aren't actually null, just change to something like

case when Black = '1' then 1 else 0 end +
case when AmIndian = '1' then 1 else 0 end + ...

and you can handle any datatype

|||

Thanks to all that responded.
I did change the structure. Created RaceTypeID 1(White),2(Black),3(Asian),4(AmIndian),and 5(Hispanic). a customer can supply more than 1 race type id so in the race table there can be multiple instances of rows with the same customerid but different race type id.

now i run a query as follows:

SELECT DISTINCT
dbo.Customers.CustomerID
,CASE WHEN EXISTS
(SELECT DISTINCT
dbo.Customers.CustomerID,
COUNT(*)
FROM dbo.Customers
INNER JOIN dbo.Race ON dbo.Customers.CustomerID = dbo.Race.CustomerID
GROUP BY dbo.Customers.CustomerID
HAVING COUNT (*) > 1) THEN 'yes' ELSE 'no' END as MultiRace
FROM dbo.Customers
WHERE
dbo.Customers.LastName NOT LIKE 'test'
AND dbo.Customers.LastName NOT LIKE 'training%'

in my result set i am getting 'yes' on all customers though only 7 are actually muti-race. Help please?

|||

Try this...

select
Customers.CustomerId,
CASE WHEN d_Race.CustomerID IS NOT NULL THEN 'YES'
ELSE 'NO'
END As MultiRace
FROM Customers
LEFT OUTER JOIN
(select customerid, count(*)
from race
group by customerid
having count(*) > 1) AS d_Race
ON d_Race.Customer_Id = Customers.Customer_Id

|||thank you very much...i only had to assign a column name for the count (*) and i finally was able to get the result i wanted. thanks a lot for all your help and to others who pitched in as well.|||whoops, yeah, you're right. I didn't alias that column. That's what happens when you develop pseudo-code. :) Glad I was able to steer you in the right direction.

Help on script

Can anyone please help me with a script to do the following in SQL Server 2005?

I would like to grant a login (already exists in the database) to have SELECT only permission on a specific database with a specific table.

Any help is appreciated. Thanks!

You can try this script

use [database]
GRANT select on [table] to [user]

details can be found on

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a760c16a-4d2d-43f2-be81-ae9315f38185.htm

help on query needed please

I have the following problem. I will be very thankful if someone could tell me the solution:
I need to display ename, mgr, sal, grade and dname through an sql query. The data should be taken from four tables. I will be thankful if any one can help me.Hi,

you havent mentioned anything abt the tables.
Anywayz... a sample query would be like
select ename, mgr, sal, grade, dname
from table1, table2 , table3 , table4 where
condition

The conditon is what is important and I cannot guess
it without the tables

Good luck
Usha

help on query

I need to select all messages for user X from all users, except those who user X has blocked in tblBlockList.
I have the following query, but it returns a row for each time UserCodeBlocked is not equal to UserCodeSender...

What I need is just to select all messages for a user EXCEPT when they are from a user in the blocklist...
I have tried the keyworddistinct but that ofcourse doesnt work since Row_number makes every row unique...
tblMessages contains the actual message
tblUsersandmessages relates users and messages

select ROW_NUMBER() OVER (ORDER BY a.SentDateTime DESC) as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTime
FROM tblMessages a
INNER JOIN tblUsersAndMessages b ON (a.MessageID=b.MessageID)
INNER JOIN tblUserData c ON (a.UserCodeSender=c.UserCode)
RIGHT JOIN tblBlockList bl ON bl.UserCodeBlocked<>a.UserCodeSender
WHERE b.UserCode=5

did you try a left join instead of a right join? You want the data for the message, not for the block list.

select ROW_NUMBER()OVER (ORDER BY a.SentDateTimeDESC)as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTimeFROM tblMessages aINNERJOIN tblUsersAndMessages bON (a.MessageID=b.MessageID)INNERJOIN tblUserData cON (a.UserCodeSender=c.UserCode)LEFTOUTER JOIN tblBlockList blON bl.UserCodeBlocked<>a.UserCodeSenderWHERE b.UserCode=5

If this doesn't help, please post the table structures and/or relationship diagram

|||tblBlockList
UserCodeBlocker int 'person who has blocked someone
UserCodeBlocked int 'person being blocked

tblMessages
UserCodeSender int
MessageID int
MessageTitle
...

tblUsersAndMessages
UserCode int
MessageID int
...sql

help on query

Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one quarter
- 1995-06-30)
I want to write a query against this table to find out the missing quarter's
year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000If you use a calendar table, you can join the two tables to find the
missing rows. The calendar table should have every quarter from every
year.
David Gugick
Quest Software
www.imceda.com
www.quest.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one
quarter- 1995-06-30)
I want to write a query against this table to find out the missing
quarter's year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000|||if you have and @.@.identity this will give you above which you are missing
quarter.
that should be in the order.Try this
SELECT p1.IDNO
FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
p.IDNO = P1.IDNO + 1 )) > 3
Regards
R.D
"David Gugick" wrote:

> If you use a calendar table, you can join the two tables to find the
> missing rows. The calendar table should have every quarter from every
> year.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I don't know if this is durable without cursor.
> I have following record set: TABLEA have one column "Date"
> All the dates are in order DESC.
> Assuming every year need to have 4 quarters,
> however in this example 1995 year only have 3 quarters ( missing one
> quarter- 1995-06-30)
> I want to write a query against this table to find out the missing
> quarter's year, in this case, it's 1995
> how can I do that?
>
> Date
> --
> 1999-12-31 00:00:00.000
> 1999-09-30 00:00:00.000
> 1999-06-30 00:00:00.000
> 1999-03-31 00:00:00.000
> 1998-12-31 00:00:00.000
> 1998-09-30 00:00:00.000
> 1998-06-30 00:00:00.000
> 1998-03-31 00:00:00.000
> 1997-12-31 00:00:00.000
> 1997-09-30 00:00:00.000
> 1997-06-30 00:00:00.000
> 1997-03-31 00:00:00.000
> 1996-12-31 00:00:00.000
> 1996-09-30 00:00:00.000
> 1996-06-30 00:00:00.000
> 1996-03-31 00:00:00.000
> 1995-12-31 00:00:00.000
> 1995-09-30 00:00:00.000
> 1995-03-31 00:00:00.000
> 1994-12-31 00:00:00.000
> 1994-09-30 00:00:00.000
> 1994-06-30 00:00:00.000
> 1994-03-31 00:00:00.000
> 1993-12-31 00:00:00.000
> 1993-09-30 00:00:00.000
> 1993-06-30 00:00:00.000
> 1993-03-31 00:00:00.000
> 1992-12-31 00:00:00.000
> 1992-09-30 00:00:00.000
> 1992-06-30 00:00:00.000
> 1992-03-31 00:00:00.000
> 1991-12-31 00:00:00.000
> 1991-09-30 00:00:00.000
> 1991-06-30 00:00:00.000
> 1991-03-31 00:00:00.000
> 1990-12-31 00:00:00.000
> 1990-09-30 00:00:00.000
> 1990-06-30 00:00:00.000
> 1990-03-31 00:00:00.000
>|||I Mean IDENTITY COLUMN. If you dont have one, you can generate on the fly.
"R.D" wrote:
> if you have and @.@.identity this will give you above which you are missing
> quarter.
> that should be in the order.Try this
> SELECT p1.IDNO
> FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
> where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
> p.IDNO = P1.IDNO + 1 )) > 3
> Regards
> R.D
> "David Gugick" wrote:
>

Help on Oracle RDB Migration to SQL Server.

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Thanks & Regards,
Chandra MohanOn Thu, 07 Aug 2003 04:45:54 -0700, Chandra Mohan wrote:

> Hi,
> We need help on following things,
> 1. Inputs on creating comments on the columns & Tables of a SQL
> Database & generating the sql script of that.

There is no direct support for Rdb-style comments in SQL Server. There is
an Extended Property facility, and SQL Enterprise Manager uses this to
allow you to annotate objects with comments. You could do the same thing
yourself with the system stored procedures for extended properties. Or
(and I've never looked at this) you could see if DMO has a way to
programmatically manipulate the same comments as SQL Enterprise Manager
uses. Then you could script calls to DMO.

> 2. Is it possible to call a .exe file in SQL server like following
> code in ORACLE
> create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
> language SQL;
> external
> name "CERT_VERIFY"
> location 'HOST_IMG:TEST_CALCS.EXE'
> with ALL logical_name translation
> language C
> GENERAL parameter style

Unfortunately not something similar to Rdb. Keep in mind that Rdb (on
VMS) runs as a run-time library in the user process. Thus, subject to a
little bit of security work to make sure you drop into user mode, its
pretty easy to run external logic. SQL Server runs as a central server
process, so it is far more difficult to safely run external logic.

Currently SQL Server has three mechanism for running external logic.
XP_CMDSHELL allows you to directly send a command or script to a command
shell. You could wrap a call to XP_CMDSHELL in a stored procedure to
simulate something like what you can code in Rdb, but its quite different.
Anyway, take a look and pay attention to the security requirements.
Second, you can write extended stored procedures to call code written in
C. Third, you can use the OLE Automation stored procedures to call OLE
Automation objects. This is probably the closest thing to the Rdb
capability since much software on Windows exposes its functionality via
OLE Automation.

> 3. We are using Rules for restricting data(now), We need inputs
> whether to use Check constraints or Rules.

Use Check constraints.

Hal

Help on global variable for insert statement

I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.

I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code to do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Here is all the code:

Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
'Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1.Description = "Execute SQL Task: undefined"
oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
oCustomTask1.ConnectionID = 1
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False

goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing

End Sub

This sentence is confusing: "I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls."

Do you mean that the table fields do not accept NULL values?

If I recall correctly, a textbox cannot contain a NULL value. At the minimum, it contains an 'empty string'.

In your code sniplet, you are inserting the value '1rowst' in the field [Test1], etc. There is no ambiguity about NULL values.

Perhaps I am missing something. Could you please expand upon your request so in order to clear up the confusion?

|||

I am sorry, lack of english.

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code do do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Thanks

|||

I'm still confused. Please help me clarify.

You wish to put all the contents of three textboxes into a single field in the table?

"Textbox1, 2, 3 Needs to insert values into the first column"

If that is the situation, why not just have one textbox -not three?

|||

No, sorry Arnie,

Textbox 1,4,7 go in column 1

TextBox 2, 5, 8 Go in column 2

TextBox 3, 6, 9 Go in column 3

I need my application to insert the values into the Database. If there is Nulls in the textbox go to the next record.

I think the code should go something like this:

If Textbox2 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

Elseif Textbox3 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

And

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text "', '" Textbox5.Text "', '" Textbox8.Text"')"

As you can see I am throwing a rock into the grass, because I am not % 100 sure if this is correct.

Thanks for the patience Arnie

|||

If I am understanding you correctly, it appears that you wish to load textbox values into the table depending upon the status of other textbox values.

Your use of the IF...ELSEIF structure may work just fine for your intentions. Something like this might work.

IF Textbox1.Text = '' Then

IF Textbox2.Text = '' Then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox3.Text + "', '" + Textbox6.Text + "', '" + Textbox9.Text + "')"

ELSE

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text + "', '" + Textbox5.Text + "', '" + Textbox8.Text + "')"

END IF

ELSE

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox1.Text + "', '" + Textbox4.Text + "', '" + Textbox7.Text + "')"

END IF

|||That was exactly what I needed. Thanks Arnie|||Of course you should be careful with this method since this is completely vulnerable to SQL injection attacks.

Just a friendly reminder.|||

Jon,

Thank you very much for that. I was not familiar with that at all. I just want to make sure. This is a big issue with web applications (wich mine is not). Is that correct?

Thanks

|||Yes it is, but it's completely possible from within windows forms applications as well. So it's definitely something to watch out for.

Help on filtering dataset issue and do I mean HELP!.

I have the following filter expression on a dataset.
Under the Filter tab on the dataset
Expression:
=iif(CStr(Parameters!Bill_ID.Value) <> "All",
CStr(Fields!Bill_ID.Value) = CStr(Parameters!Bill_ID.Value),True)
Operator:
=
Value:
True
My report parameter Bill_ID is String datatype.
My field value Bill_ID from my dataset is a varchar(15).
Yet, when I preview the report with a legitmate parameter value I
receieve this error message.
"The processing of filter expression for "data set" cannot be
performed. The comparison failed. Please check the data type returned
by the filter expression.
How can I get this to work. I spent hours trying different
combinations. What am I doing wrong!! Please respond.Add "=" in front of the True to make it a boolean instead of a string
literal:
=True
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1107572335.620117.23360@.o13g2000cwo.googlegroups.com...
> I have the following filter expression on a dataset.
> Under the Filter tab on the dataset
> Expression:
> =iif(CStr(Parameters!Bill_ID.Value) <> "All",
> CStr(Fields!Bill_ID.Value) = CStr(Parameters!Bill_ID.Value),True)
> Operator:
> => Value:
> True
> My report parameter Bill_ID is String datatype.
> My field value Bill_ID from my dataset is a varchar(15).
> Yet, when I preview the report with a legitmate parameter value I
> receieve this error message.
> "The processing of filter expression for "data set" cannot be
> performed. The comparison failed. Please check the data type returned
> by the filter expression.
> How can I get this to work. I spent hours trying different
> combinations. What am I doing wrong!! Please respond.
>|||Thank you Rob very much. I was going crazy trying to figure this out
on Friday.sql

Help on Emails & scheduled Jobs

Hi All,
The name of the Server was changed which in turn gave me the following
error when I tried to delete the jobs
Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server.
The job was not saved.
Knowing that it has to do with the Originator server in the sysjobs
table; I hacked the table ( now I think not a good idea ) and deleted
all the jobs that I wanted to delete and performed the same task on
sysjobservers.
Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted
jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .
Any help is appreciated.
Thank youcheck the sysjobsteps and the sysjobschedules tables. Since you have
deleted the entries from the sysjobs table, you will not be able to
tell by job_id.
But if you look into the command and step name (if you have given a
meaningful name when you create the step, that will be easier to spot
out the email notification steps) from the SYSJOBSTEPS table and the
name (if you have given a meaningful name when you schedule the job)
from the SYSJOBSCHEDULES table.
Obviousbly it is not advisable to modify the system tables directly but
since we have a bad start already, so ... find the records (email
notification step and schedule) and delete it from the tables (backup
the the table first).
Mel|||Hi
Thank you for the info.
I did see job steps and job schedules of the deleted jobs in the table.
So I wenty ahead and deleted them .
Now I have the data for only the job ( that I want ).
Hopefully this will stop emails being sent out (notifcation) for
deleted jobs.
Thank you again|||Hi All,
Removing the records from sysjobschedules and sysjobsteps did not help.
I am still receiving emails.
Please advice|||You mentioned the name of a server was changed, was it the target
server or the master server where the job was stored?
I believe that you have deleted the jobs on the MSX server (mentioned
in your post). It may be the target server not yet received the new
set of instruction (do nothing - jobs have deleted). Run the stored
procedure as below to enforce it to happen (on the master job server):
USE msdb
EXEC sp_resync_targetserver 'target server name'
sp_resync_targetserver deletes the current set of instructions for the
target server and posts a new set for the target server to download.
The new set consists of an instruction to delete all multiserver jobs,
followed by an insert for each job currently targeted at the server.
Mel|||If the above doesn't help.
Run this to see what is available for the target servers to download
from the master.
sp_help_downloadlist
To force a target server to poll the master server.
If you make changes to multiserver job definitions outside of SQL
Server Enterprise Manager (which it is in this case), you must post the
changes to the download list so that target servers can download the
updated job again. To ensure that target servers have the most current
job definitions, post an INSERT instruction after you update the
multiserver job:
EXECUTE sp_post_msx_operation 'INSERT', 'JOB', '<job id>'
Check BOL for more details for the above stored procedures.
Mel