Job error: converting datetime from char string?

Hello,

I'm trying to set up a Job in SQL Server 2000 that emails company employees if they haven't completed a timesheet in the last 24 hrs.

The only job step I have looks like:

declare
@.em varchar(100),
@.flname varchar(50),
@.ls datetime,
@.bdy varchar(500)

declare ts_cursor cursor for
SELECT FL_EMAIL, FL_NAME, LASTSHEET FROM F_AND_L WHERE dateadd(dd,1,LASTSHEET)>getdate()
open ts_cursor
fetch next from ts_cursor
into @.em, @.flname, @.ls

while @.@.FETCH_STATUS=0
begin
select @.bdy='Dear ' +@.flname+ ','+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
'Hello, this is an automatic email. The timesheet records seem to indicate'+
' that the last time you completed a timesheet was '+@.ls+ ' .'+CHAR(13)+CHAR(10)+
'Could you please complete one? Thanks :)'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
'Regards,'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
'The Intranet'
exec master.dbo.sendmail @.em,@.bdy,@.subject='Timesheets'
fetch next from ts_cursor
into @.em, @.flname, @.ls
end
close ts_cursor
deallocate ts_cursor

(I should add that this was cannibalised from an existing job and I've never done this before).

At the moment it returns an error:

"Executed as user: NT AUTHORITY\SYSTEM. Syntax error converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed."

What does this mean, and how do I fix it? I've run the select query in Query Analyser and it runs fine. My only thought is that it's the date format that's screwing it but I don't know how to check.

Any suggestions?

Ta,

Spud.The error is in the formating of your message string, you can only concatenate string variables.

select @.bdy='Dear ' +@.flname+ ','+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
'Hello, this is an automatic email. The timesheet records seem to indicate'+
' that the last time you completed a timesheet was '+@.ls+ ' .'+CHAR(13)+CHAR(10)+
'Could you please complete one? Thanks '+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
'Regards,'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+

Test this, it will error out

declare @.ls datetime

set @.ls = getdate()

print 'The time is ' + @.ls

However either of these will work

declare @.ls varchar(30)

set @.ls = getdate()

print 'The time is ' + @.ls

** OR **

declare @.ls datetime

set @.ls = getdate()

print 'The time is ' + CONVERT(VARCHAR(30),@.ls)

Job Activity Monitor

Hi,

I have a problem with Job Activity Monitor.

I have a SSIS which copies data from a table to a flat file. The connection string of this file is variable and the file is reused if not exists and is created if exists.

When I run the SSIS manually from my microsoft visual studio it works properly. However, when I run this SSIS from the Job Activity Monitor, I get the following error:

Message
Executed as user: REDCAM\adminsql2k5. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:41:10 AM Error: 2007-09-10 08:48:04.99 Code: 0xC020200E Source: Crear Historico Historico [1] Description: Cannot open the datafile "\\srvnfile\HISTORICOS\CAJEROS\OFI3210C01\OFI3210C01_2007-7X.txt". End Error Error: 2007-09-10 08:48:04.99 Code: 0xC004701A Source: Crear Historico DTS.Pipeline Description: component "Historico" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:10 AM Finished: 8:48:53 AM Elapsed: 462.234 seconds. The package execution failed. The step failed.

Any idea?

Thanks.

Does the SQL Server service account have access to \\srvnfile\HISTORICOS?|||

Yes the SQL Server service account have access to \\srvnfile\HISTORICOS.

|||

Did you find the resolution to this issue?

|||Had the same problem - found this ans in sql server central.

Are you running SQL Server agent using a local account on the SQL box? If so, you'll have problems copying the file out to a network share. If this is the case, try restarting SQL Server Agent using a domain account that has write access to that share. (Tim)

That wasn't my problem. I used a mapped drive. (my perspective) Don't do that.

Job Activity Monitor

Hi,

I have a problem with Job Activity Monitor.

I have a SSIS which copies data from a table to a flat file. The connection string of this file is variable and the file is reused if not exists and is created if exists.

When I run the SSIS manually from my microsoft visual studio it works properly. However, when I run this SSIS from the Job Activity Monitor, I get the following error:

Message
Executed as user: REDCAM\adminsql2k5. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:41:10 AM Error: 2007-09-10 08:48:04.99 Code: 0xC020200E Source: Crear Historico Historico [1] Description: Cannot open the datafile "\\srvnfile\HISTORICOS\CAJEROS\OFI3210C01\OFI3210C01_2007-7X.txt". End Error Error: 2007-09-10 08:48:04.99 Code: 0xC004701A Source: Crear Historico DTS.Pipeline Description: component "Historico" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:10 AM Finished: 8:48:53 AM Elapsed: 462.234 seconds. The package execution failed. The step failed.

Any idea?

Thanks.

Does the SQL Server service account have access to \\srvnfile\HISTORICOS?|||

Yes the SQL Server service account have access to \\srvnfile\HISTORICOS.

|||

Did you find the resolution to this issue?

JDBC with SQL Express

The java code :

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
"instance=SQLEXPRESS;databaseName=UPM;integratedSecurity=true;";
Connection con = DriverManager.getConnection(connectionUrl);

returns "connection refused" no matter what I do.

I've used the surface config tool to enable TCP, I've added sqlsrvr.exe to the firewall exceptions, I've tried a dozen or so variations on the connection string.

I can connect to the database using the Management Studio.

Any ideas what I'm missing ?

Thanks.

As you are using a trusted connection can your verify that you are using the correct user in both systems.

|||

I'm running into the same problem.

No problem connecting through VWD or SQL Management Studio...

The code:

String url =
"jdbc:sqlserver://localhost;" +
//"databaseName=TV;" +
"portNumber=1433;" +
"instanceName=./SQLEXPRESS;" +
"loginTimeout=0;" +
//"userName=Pierre;" +
//"serverName=PG" +
"integratedSecurity=true;";

con = DriverManager.getConnection( url );

Same bad result with all commented parameters.

The message:

The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect.

|||
This may be because by default MS SQL Server Express is configured to use dynamic TCP/IP ports, like named instances.

Go into the SQL SERVER CONFIGURATION MANAGER
open SQL SERVER 2005 Network Configuration
open Protocols for SQLEXPRESS
open TCP/IP properties
on the IP ADDRESSES tab, check at the bottom if "TCP Dynamic Ports" has a value.

if so, clear the value and leave that field blank. Then change "TCP Port" to 1433 or whatever port you decide.

jdbc driver getTables(String , String ,Sring ,String[] )can't get tables whose catalog is pure

i have a database named "1", then i want to get the tables which type is "TABLE" under this catalog but it can't be done.
Check the kode:
conn.setCatalog("\"1\"");
DatabaseMetaData meta = conn.getMetaData();
ResultSet tables = meta.getTables("\"1\"", "dbo","ttt" , new String [] {"TABLE"});
if i change the last sentence to :
ResultSet tables = meta.getTables("1", "dbo","ttt" , new String [] {"TABLE"}), it still can't be done.
it work when "ResultSet tables = meta.getTables(null, "dbo","ttt" , new String [] {"TABLE"})

I tested this with the SQL Server 2005 JDBC driver and it worked fine.

I created a database named ["1"].

Which SQL Server JDBC driver are you using?

Also try bracketizing the database name like so, this will probably make it work:

ResultSet tables = meta.getTables("[\"1\"]", "dbo","ttt" , new String [] {"TABLE"});

Matt

|||k, i am working under sqlserver 2000 , i change the kode as u do, but it still can't work fine.

steps:
create database through sql : "create database 1".
change the kode to :
ResultSet tables = meta.getTables("[\"1\"]", null,null, new String [] {"TABLE"});
but it still can't work.no table returned.
Have u any idear?

|||i also tried this:
conn.getCatalog() returns "1";
but meta.getCatalogs returns 1;
is it a bug?|||

If you created the database named 1, then use 1 and not "1", so:

ResultSet tables = meta.getTables("[1]", null,null, new String [] {"TABLE"});

Also, if you have SQL Server Profiler you can see what it sent to the SQL Server.

With the SQL 2005 driver I see:

exec sp_tables @.table_qualifier = '"1"', @.table_owner = 'dbo', @.table_name = null , @.table_type = '''TABLE'''

This might help you figure out what the SQL 2000 driver is doing wrong.

I have the SQL 2000 driver setup on my machine I will give it a quick test.

|||

Yes I tested with SQL 2000 JDBC driver and this failed, so it is a bug.

Works fine with SQL 2005 JDBC driver.

I reported this bug to the JDBC PM.

|||3X a lot.