Jobbing!

I want to retrieve all those records from a column (that stores date
values) where in the date is 1 + today's date. For e.g. today is
20/09/2005. I want all those records where the date is 21/09/2005.
I want to send a reminder mail to all those records retrieved that they
have to make the payment latest by tomorrow which is the due date.
That's the reason why I am fetching all those records where the date is
1 + today's date. I am implementing this by creating a job & scheduling
it to run everyday at one particular time. This is the code:
---
DECLARE
@.getduedate varchar(20),
@.msg varchar(3000),
@.email varchar(100),
@.person varchar(50)
SET @.getduedate=(SELECT DDate FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1)))
IF (@.getduedate<>"")
BEGIN
SET @.email=(SELECT EMail FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1))
SET @.person=(SELECT Person FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1))
SET @.msg='To ' + @.person + ','
SET @.msg=@.msg + 'Your payment is due for tomorrow.'
EXEC master.dbo.xp_sendmail
@.recipients=@.email,
@.subject='Payment Due Date Reminder!',
@.message=@.msg
END
---
But the above generates the "Subquery returned more than 1 value" error
when more than 1 record matches the criteria. How do I resolve this?
Thanks,
Arpan
You have to do that in a loop if it contains more than one rows
(untested)
DECLARE
@.getduedate varchar(20),
@.msg varchar(3000),
@.email varchar(100),
@.person varchar(50),
@.RowCount int,
@.I INT
SET @.I = 0
CREATE TABLE #Mails
(
Counter INT identity(1,1),
DDate varchar(200),
EMail varchar(200),
Person varchar(200)
)
INSERT INTO #Mails(DDate,EMail,Person)
SELECT DDate,EMail,Person FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1)
SET @.Rowcount = @.@.Rowcount
WHILE @.I < @.RowCount
BEGIN
Select @.getduedate= DDate,
@.email = EMail,
@.person = Person,
@.msg = 'To ' + @.person + ',' + 'Your payment is
due for tomorrow.'
FROM #Mails
Where Counter = @.I
EXEC master.dbo.xp_sendmail
@.recipients=@.email,
@.subject='Payment Due Date Reminder!',
@.message=@.msg
END
HTH, jens Suessmeyer.|||Sorry should be :
WHILE @.I <= @.RowCount|||Thanks, Jens, for your help although a couple of minor issues gave me a
big headache :-)
There was no code to increment the variable @.l at the end of the WHILE
loop which was why it created an infinite loop!
Secondly the temp table #Mails is being created with an Identity column
initialized to 1 & incrementing by 1 for subsequent records but you
have initialized @.l to 0 which is why the job wasn't succeeding since
xp_sendmail wasn't getting any value for the mandatory @.email parameter
when @.l=0!
Anyways, thanks a lot once again for your help. I really appreciate the
efforts & time you have put in to help me out.
BTW, isn't there any other approach other than what you have shown (no
cursors......please)?
Regards,
Arpan

Job truncates text data type

I have a table with a text data type and when I run my sql manually,
everything works fine - the text column is completely filled with what
I need (it's html from a http post request via a stored procedure).
When I put the exact same SQL inside a job, and kick the job off (or
let the agent pick it up on the schedule), it truncates the text column
to a width of 498, rendering my later job steps useless b/c the
expected data isn't there.
Why would the job cutoff the text in this column?What is the syntax used in the proc? Are you issuing an update/insert
or a writetext/updatetext statement?

Job truncates text data type

I have a table with a text data type and when I run my sql manually,
everything works fine - the text column is completely filled with what
I need (it's html from a http post request via a stored procedure).
When I put the exact same SQL inside a job, and kick the job off (or
let the agent pick it up on the schedule), it truncates the text column
to a width of 498, rendering my later job steps useless b/c the
expected data isn't there.
Why would the job cutoff the text in this column?What is the syntax used in the proc? Are you issuing an update/insert
or a writetext/updatetext statement?

Job Schedule

I would like to view all the scheduled jobs , sorted by Next Run Date. When
I use Enterprise Manager and sort by that column, they are not sorted
correctly.
Suggestions?Hopefully following query can give you the required result.
select sj.name, sj.description,
case sj.enabled
when 0 then 'Enabled'
else 'Disabled' end as 'Job Status',
sjs.next_run_date,
right('000000' + convert(varchar(6),sjs.next_run_time),6) 'Next Run Time'
from msdb.dbo.sysjobs sj, msdb.dbo.sysjobschedules sjs
where sj.job_id = sjs.job_id
order by sjs.next_run_date, sjs.next_run_time
Please let us know whether this helped you or not?
"John Carlton" wrote:
> I would like to view all the scheduled jobs , sorted by Next Run Date. When
> I use Enterprise Manager and sort by that column, they are not sorted
> correctly.
> Suggestions?|||Yes, perfect. Thanks!
"Absar Ahmad" wrote:
> Hopefully following query can give you the required result.
> select sj.name, sj.description,
> case sj.enabled
> when 0 then 'Enabled'
> else 'Disabled' end as 'Job Status',
> sjs.next_run_date,
> right('000000' + convert(varchar(6),sjs.next_run_time),6) 'Next Run Time'
> from msdb.dbo.sysjobs sj, msdb.dbo.sysjobschedules sjs
> where sj.job_id = sjs.job_id
> order by sjs.next_run_date, sjs.next_run_time
> Please let us know whether this helped you or not?
> "John Carlton" wrote:
> > I would like to view all the scheduled jobs , sorted by Next Run Date. When
> > I use Enterprise Manager and sort by that column, they are not sorted
> > correctly.
> > Suggestions?

Job Schedule

I would like to view all the scheduled jobs , sorted by Next Run Date. When
I use Enterprise Manager and sort by that column, they are not sorted
correctly.
Suggestions?Hopefully following query can give you the required result.
select sj.name, sj.description,
case sj.enabled
when 0 then 'Enabled'
else 'Disabled' end as 'Job Status',
sjs.next_run_date,
right('000000' + convert(varchar(6),sjs.next_run_time),6) 'Next Run Time'
from msdb.dbo.sysjobs sj, msdb.dbo.sysjobschedules sjs
where sj.job_id = sjs.job_id
order by sjs.next_run_date, sjs.next_run_time
Please let us know whether this helped you or not?
"John Carlton" wrote:

> I would like to view all the scheduled jobs , sorted by Next Run Date. Wh
en
> I use Enterprise Manager and sort by that column, they are not sorted
> correctly.
> Suggestions?|||Yes, perfect. Thanks!
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Hopefully following query can give you the required result.
> select sj.name, sj.description,
> case sj.enabled
> when 0 then 'Enabled'
> else 'Disabled' end as 'Job Status',
> sjs.next_run_date,
> right('000000' + convert(varchar(6),sjs.next_run_time),6) 'Next Run Time'
> from msdb.dbo.sysjobs sj, msdb.dbo.sysjobschedules sjs
> where sj.job_id = sjs.job_id
> order by sjs.next_run_date, sjs.next_run_time
> Please let us know whether this helped you or not?
> "John Carlton" wrote:
>

Job Schedule

I would like to view all the scheduled jobs , sorted by Next Run Date. When
I use Enterprise Manager and sort by that column, they are not sorted
correctly.
Suggestions?
Hopefully following query can give you the required result.
select sj.name, sj.description,
case sj.enabled
when 0 then 'Enabled'
else 'Disabled' end as 'Job Status',
sjs.next_run_date,
right('000000' + convert(varchar(6),sjs.next_run_time),6) 'Next Run Time'
from msdb.dbo.sysjobs sj, msdb.dbo.sysjobschedules sjs
where sj.job_id = sjs.job_id
order by sjs.next_run_date, sjs.next_run_time
Please let us know whether this helped you or not?
"John Carlton" wrote:

> I would like to view all the scheduled jobs , sorted by Next Run Date. When
> I use Enterprise Manager and sort by that column, they are not sorted
> correctly.
> Suggestions?
|||Yes, perfect. Thanks!
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Hopefully following query can give you the required result.
> select sj.name, sj.description,
> case sj.enabled
> when 0 then 'Enabled'
> else 'Disabled' end as 'Job Status',
> sjs.next_run_date,
> right('000000' + convert(varchar(6),sjs.next_run_time),6) 'Next Run Time'
> from msdb.dbo.sysjobs sj, msdb.dbo.sysjobschedules sjs
> where sj.job_id = sjs.job_id
> order by sjs.next_run_date, sjs.next_run_time
> Please let us know whether this helped you or not?
> "John Carlton" wrote:
sql

JDBC: Retrieving stored procedures column information returns "-9" data type for nvarc

Hello,

I'm using the latest JDBC driver for SQL server 2005 (1.0.809.102).

Retrieving stored procedures column information returns "-9" data type for nvarchar:

public class driver {
public static void main(String[] args) throws java.lang.Throwable {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();

java.util.Properties properties = new java.util.Properties ();
properties.put("username", "sa");
properties.put("password", "sa");
properties.put("database", "GHTDB");

Connection con = DriverManager.getConnection("jdbc:sqlserver://POWERGH", properties);

ResultSet rs = con.getMetaData().getProcedureColumns("GHTDB", null, "SalesByCategory", null);

while (rs.next()) {
int i = rs.getInt("DATA_TYPE");
String s = rs.getString("TYPE_NAME");

System.out.println(i); // prints -9 for nvarchar
System.out.println(s);
}

}
}

Is this behavior documented anywhere and is by design?

Any help will be appreciated,
Regards,
Kosta

Kosta:

java.sql.Types has no value for any of the unicode text types so we return the SQL Server value for nvarchar (-9) rather than mis-reporting the type as a non-unicode varchar (12).

Both options here are bad, although, returning 12 in this case has the advantage of being wrong in the company of most other JDBC drivers, including our SQL Server 2000 JDBC driver.

If you think we should fix this, please file a bug at the product feedback center (http://lab.msdn.microsoft.com/productfeedback/) and we'll revisit the issue and make a change or doc it.

-shelby

Shelby Goerlitz

SQL Server Data Programmability -- JDBC

JDBC getBoolean/setBoolean

Hello
When I call stmt.setBoolean(columnIndex, true); The value of "1" in inserted to column.
However when I call rs.getBoolean(column) I get "false". If manually set column value to "true", the returned value of rs.getBoolean will be true.
Thx in advance...Ilja,
We have just shipped a QFE to fix this mess, you can get this by contacting product support directly with the information you have posted.

You can verify that the behavior is what you expect by using our latest community tech preview drop which contains this fix:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

Thanks,
Angel|||Hi thx for fast reply.
I think I found reason. Column type must be set to "BIT" in order of using booleans on programming side.
Anyway thx.|||

Using BIT certainly helps since this is the type that maps better to the java boolean type. Unfortunatelly for us the JDBC spec is very clear on the allowed conversions for JDBC drivers.

In the case of getboolean we need to support all of the numeric types and do data dependent conversion (conversions that require that the type contain values that can be converted into boolean) for all character based Sql Server types. Before the QFE I mention above we did not.

Glad to hear this worked out for you, good luck

Angel